Суммесли(диапазон1, критерий, диапазон2).

Эта функция выполняется в два этапа:

• проверяется критерий в диапзоне1 и отбираются нужные значения;
• вычисляется сумма чисел в диапазоне2, соответствующие отобранным значениям.

Примеры записи функции:

a) =СУММЕСЛИ(A1:A4;“<120”;B1:B4) –подсчет суммы чисел в интервале B1:B4, причем выбираются только те строки, которые соответствуют значениям меньших 120 в интервале A1:A4.

в A1 - 100 в B1 - 200

в A2 - 125 в B2 - 460

в A3 - 90 в B3 - 300

в A4 - 150 в B4 - 690

Результат: 200+300=500

б) =СУММЕСЛИ(D1:D7;“инженер”;E1:E7) – подсчет суммы значений в интервале E1:E7 соответствующих строке “ инженер ” в интервале D1:D7.

Примечания:

1. В качестве критерия могут быть:

• константы: число, если диапазн1 числовой; текст, если диапазн1 текстовый;
• выражение в кавычках с применением знаков сравнений: <, >, =, <= (меньше или равно), >= (больше или равно, <> (не равно). Справа от этих знаков используется только число(“>25”; “<>8” и т.п.).

2. Проверка критерия и суммирование можно производить в одном и том же диапазоне. В этом случае диапазон2 можно опустить. Например, =СУММЕСЛИ(C5:C20;”>100”) – подсчет суммы чисел больших 100 из диапазона C5:C20.

 

Пример 2. Дан список наименований товара с указанием объема, цены за единицу и поставщика. Определить:

1. Стоимость всего объема каждого наименования.

2. Суммарную стоимость всех наименований.

3. Суммарный объем, доставленный указанным поставщиком.

4. Суммарную стоимость, значения которых превышают заданную величину.

 

Структура таблицы:

Выполняемые действия:

1. Оформить шапку таблицы.

2. Ввести исходные данные в столбцы A, B, C, D, E.

3. Вычислить стоимость в ячейке F3: =C3*D3 и распространить вниз по столбцу.

4. Вычислить суммарную стоимость в ячейке F8: =СУММ(F3:F7) или применить кнопку для вычисления суммы - S.

5. Вычислить суммарный объем поставщика "Заря" в ячейке F9:

=СУММЕСЛИ(E3:E7;”Заря”;C3:C7).

6. Вычислить суммарную стоимость, превышающую 10000 в ячейке F10:

=СУММЕСЛИ(F3:F7;”>10000”).

Статистические функции

Аргументы статистических функций задаются всегда в виде списка из чисел, адресов ячеек, диапазонов ячеек, перечисленных через точку с запятой. Вставляется статистическая функция так же, как и функции суммирования.

Рассмотрим некоторые функции из статистической категории.

 

1. Определение среднего значения в списке: = СРЗНАЧ(список аргументов) -=СРЗНАЧ(А1:А10)

2. Подсчет количества значений, удовлетворяющих заданному условию:

=СЧЕТЕСЛИ(диапазон, критерий).

Эта функция используется так же, как и функция СУММЕСЛИ, но подсчитывает количество значений при условии, а не сумму. Критерий создается аналогично.

Примеры записи функции:

a) =СЧЕТЕСЛИ(F2:F10; “Первый”) – подсчитывает, сколько раз повторяется слово «Первый» в указанном диапазоне F2:F10.

b) =СЧЕТЕСЛИ(B2:B12;”<>10) – количество значений не равных 10 в диапазоне B2:B12.

3. Функции определения оптимальных значений в списке.

a). Поиск максимального значения в списке: =МАКС(список) -=МАКС(А1:А10)

b). Поиск минимального значения в списке: =МИН(список) -=МИН(А1:А10)

Пример 3. Дан перечень наименований фирмы «ГАРАНТ» с указанием сорта и объема.

Определить:

1. Суммарный, средний, максимальный, минимальный значения по объему.

2. Объем товаров высшего и первого сорта.

3. Количество товаров первого и второго сорта.

Структура таблицы:

 

Выполняемые действия:

1. Оформить шапку таблицы.

2. Ввести исходные данные в столбцы A,B,C,D.

3. Вычисление суммарного объема – курсор в ячейке D11 и щелкнуть по кнопке S.

4. Вычисление среднего объема – в ячейку D12 вставить функцию СРЗНАЧ(D3:D10) из категории Статистические.

5. Аналогично вычисляются функции для определения максимума и минимума. Аргументы у них одинаковые.

6. Суммарные объемы::: =СУММЕСЛИ(С3:С10; “высший”; D3:D10); =СУММЕСЛИ(С3:С10; “первый”; D3:D10)

7. Количество товаров:: =СЧЕТЕСЛИ(С3:С10; “первый”)

=СЧЕТЕСЛИ(С3:С10; “второй”)

8. Выполнить форматирование таблицы.

 

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

Логическая константа и логическое выражение

В состав логических функций входят логические константы и логические выражения. Логическая константа – это одно из двух значений: истина или ложь. Логическое выражение принимает значение логической константы.

Частным случаем логического выражения является выражение отношения - два арифметических выражения, связанных одним из знаков сравнения: <, >, =, <= (меньше или равно), >= (больше или равно), <> (не равно).

Например, значение выражения =2>5 будет ложь, а выражения =1<8 - истина. При этом числа могут находиться в ячейках (=A1>A2).

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

1. И(логическое выражение 1; логическое выражение 2; …).

Результат функции - истина, если значения всех составных логических выражений истинны; в противном случае – ложь.

2. ИЛИ(логическое выражение 1; логическое выражение 2; …).

Результат функции – ложь только в том случае, если значения составляющих выражений ложны; во всех остальных случаях – истина.

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

Эта функция позволяет осуществить выбор варианта вычислений. Если значение логического выражения истинно, то выбирается вариант - значение _ истина; иначе – значение _ ложь. В качестве логического выражения могут применяться в сложных случаях функции «И» и «ИЛИ». Также при формировании выражений значение _ истина и значение _ ложь применяется вновь функция ЕСЛИ. В этом случае в ячейку вставляется вложенная функция ЕСЛИ.