СУММЕСЛИ(интервал; критерий; сумм_интервал)

Значение1, значение2, ... - это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.

Интервал – заданный ряд данных.

Критерий – проверяемый критерий в заданном интервале (число, выражение, текст).

Пример 1.

Пусть ячейки A3:A6 содержат "яблоки", "апельсины", "персики", "яблоки" соответственно. СЧЁТЕСЛИ(A3:A6;"яблоки") равняется 2.

Пример 2.

Пусть ячейки B3:B6 содержат 32, 54, 75, 86 соответственно. СЧЁТЕСЛИ(B3:B6;">55") равняется 2.

Пример 3.

СЧЕТЕСЛИ(А1:А9; 85) – подсчитывает, сколько раз число 85 встречается в интервале А1:А9;

Пример 4.

СЧЕТЕСЛИ(А1:А9; “>85”) – подсчитывает, сколько раз в интервале А1:А9 встречаются числа, большие 85;

Пример 5.

СЧЕТЕСЛИ(А1:А9; “высший”) – подсчитывает, сколько раз в интервале А1:А9 встречается слово «высший».

Обратите внимание на то, что если в качестве критерия указываются не числовые значения, а текст или символы, то они заключаются в кавычки.

Функция РАНГ.РВ

Определение ранга (номера позиции) числа в списке других чисел (т.е. порядкового номера относительно других чисел в списке)

Синтаксис:

РАНГ.РВ(число; ссылка; порядок),

где число –число, для которого определяется ранг;

ссылка –массив или ссылка на список чисел, с которым сравнивается число;

порядок –число (0 или 1), определяющее способ ранжирования (в порядке убывания или возрастания).

Рассмотрим ряд статистических функций, встречающихся в простейших вычислениях.

Пример. Найти наименьшее значение цены на книгу «Гарри Поттер и дары смерти» среди магазинов города. Пусть в ячейках B3:B7 внесены значения цены (рисунок 6). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию МИН. Укажите исходные значения и нажмите .

Пример. Найти максимальное значение цены на книгу «Гарри Поттер и дары смерти» среди магазинов города. Пусть в ячейках B3:B7 внесены значения цены (рисунок 7). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию МАКС. Укажите исходные значения и нажмите .

Пример. Найти среднее значение цены на книгу «Гарри Поттер и дары смерти» в магазинах города. Пусть в ячейках B3:B7 внесены значения цены (рисунок 8). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию СРЗНАЧ. Укажите исходные данные и нажмите .

Рисунок 6. Нахождение минимального значения среди аргументов

Рисунок 7. Нахождение максимального значения среди аргументов

Рисунок 8. Нахождение среднего значения среди аргументов

Пример.Найти количество студентов, получающих стипендию. Пусть в ячейках B3:B7 внесены сведения о стипендии студентов группы (рисунок 9). Установите курсор в ячейку В9, вызовите Мастер функций и из категории Статистические выберите функцию СЧЕТ. Укажите исходные данные и нажмите . В ячейке В9 будет найдено искомое значение.

Рисунок 9. Нахождение количества числовых значений среди аргументов

Пример.Используем функцию РАНГ.РВ, которая присвоит номер места каждой марке автомобиля в зависимости от определенного параметра. Пусть в ячейки В3:В8 занесены значения расхода топлива на 100 км пробега (рисунок 10). Наилучшим будем считать автомобиль, имеющий минимальный расход. В ячейку С3 занесем формулу

=РАНГ.РВ(B3;$B$3:$B$8;1)

и скопируем ее в оставшиеся ячейки С4:С8. Аргументы в этой формуле означают: В3 – адрес ячейки, которой присваиваем в ячейке С3 номер искомого места; $B$3:$B$8 – блок ячеек, в который занесены все известные значения расхода топлива и среди которых мы выясняем ранг. Здесь используем абсолютную адресацию ($) для того, чтобы при копировании формулы из ячейки С3 адрес участвующих в вычислении ячеек В3:В8 не изменялся. Последний аргумент функции 1 указывает на то, что сравнение результатов происходит в порядке возрастания, т.е. наилучшим результатом считаем наименьший. Если поставим 0, то лучшим результатом будет наибольший, как, например, в случае с объемом двигателя (рисунок 11)

Рисунок10 Нахождение ранга числа в порядке возрастания значений

Рисунок11. Нахождение ранга числа в порядке убывания значений

Логические функции:

Функция ЕСЛИ

 Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется для условной проверки значений и формул и имеет следующий

 синтаксис:

ЕСЛИ(лог_выражение; значение_истина;значение_ложь)

Функция И

Возвращает значение ИСТИНА, если в результате вычисления всех аргументов получается значение ИСТИНА; возвращает значение ЛОЖЬ, если в результате вычисления хотя бы одного из аргументов получается значение ЛОЖЬ.

Обычно функция И используется для расширения возможностей других функций, выполняющих логическую проверку. Например, функция ЕСЛИ выполняет логическую проверку и возвращает одно значение, если при проверке получается значение ИСТИНА, и другое значение, если при проверке получается значение ЛОЖЬ. Использование функции И в качестве аргумента лог_выражение функции ЕСЛИ позволяет проверять несколько различных условий вместо одного

синтаксис:

И(логическое_значение1, [логическое_значение2], ...)

Функция ИЛИ

Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА или ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

синтаксис:

ИЛИ(логическое_значение1, [логическое_значение2],...)

Функция НЕ

Меняет логическое значение своего аргумента на противоположное. Функция НЕ используется в тех случаях, когда необходимо убедиться, что значение не равно некой конкретной величине.

синтаксис:

НЕ(логическое_значение)

Логическое_значениеОбязательный. Значение или выражение, принимающее значение ИСТИНА или ЛОЖЬ.

ПримерВ таблице олимпиады школьников в поле «Призы» поместить тексты:

· «Планшетный компьютер»- для участников, набравших 60 баллов

· «Смартфон» - для участников, набравших больше 55 баллов, но меньше 60 баллов.

· «Мобильный телефон» - для участников, набравших больше 50 баллов, но меньше 55 баллов.

· «Нет» для участников, набравших менее 50 баллов.

На этом примере рассмотрим использование вложенных функций. Для ввода функцииЕСЛИ сделаем активной ячейку K5, в которую будем вводить формулу и нажмем кнопку вызова функции в строке формул. В появившемся окне мастера функций (рисунок 12) выберем функциюЕСЛИ и нажмем .

Рисунок 11. 

Появится окно шага 2 мастера, в котором необходимо ввести аргументы функции ЕСЛИ (рисунок 12).

Рисунок 11. 

В окне Лог_выражение необходимо записать логическое выражение, соответствующее пункту задания «Планшетный компьютер»- для участников, набравших 60 баллов Количество баллов записано в ячейке H5, поэтому необходимо щелкнуть мышкой по этой ячейке и когда этот адрес появится в окне Лог_выражение необходимо дописать выражение H5=60. В окне Значение_если_истина необходимо записать текст «Планшетный компьютер», который будет в поле «Призы» в этом случае. В окне Значение_если_ложь необходимо поместить вложенную функцию ЕСЛИ, в которой необходимо реализовать условие для получения смартфона. Для его получения количество баллов должно быть в диапазоне 55≤ Количество баллов <60. Для реализации этого условия необходимо установить курсор в окно Значение_если_ложь и вызвать меню вложенных функций кнопкой  в левой части строки формул (рисунок 12).

Рисунок 12

Снова появится окно функции ЕСЛИ. В окне Лог_выражение необходимо ввести логическое выражение ЕСЛИ(H5>=55) реализующее выражение 55≤ Количество баллов <60, в окне Значение_если_истина набрать «Смартфон»  (рисунок 13).

Рисунок 13

Вокне Значение_если_ложь вновь вызвать вложенную функцию ЕСЛИ и в окне Лог_выражение ввести логическое выражение ЕСЛИ(H5>=50), в окнеЗначение_если_истина набрать «Мобильный телефон», а в окне Значение_если_ложь набрать «Нет» и нажать кнопку  (Рисунок 14). В строке формул и в ячейке будет набрана формула =ЕСЛИ(H5=60;"Планшетный компьютер";ЕСЛИ(H5>55;"Смартфон";"Нет")), реализующая задание.

Рисунок 14