Формулы подсчета и суммирования в Excel

В программе MS Excel существует около 500 различных формул, но все их выучить и использовать на практике невозможно, да и не нужно. Достаточно знать о функционале 30–40 формул, которые покрывают 90 % задач бизнес-аналитики

ВЫПУСК №4 (АПРЕЛЬ, 2019)

 

Формулы подсчета и суммирования в ExcelВсе формулы разделены на категории по логике их использования. Сегодня речь пойдет о формулах подсчета и суммирования, которые позволяют формировать некоторые показатели в отчетах быстро и без особой подготовки данных источника.

 

Все формулы данной группы можно условно определить в три группы:

1. Безусловные.

2. На одно условие.

3. На много условий.

 

Рассмотрим особенности использования формул по каждой из групп на примерах.

 

Безусловные формулы подсчета и суммирования

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

 

На рис. 1 приведена таблица с продажами товаров, на основании которой рассчитаны следующие показатели:

1. Сумма продаж за год по всем товарам

 =СУММ(число1;[число2];…) — суммирует значения.

В ячейке К2 вводим формулу =СУММ(D2:D18).

2. Среднее количество по продажам в штуках

=СРЗНАЧ(число1;[число2];…) — возвращает среднее значение (среднее арифметическое) аргументов.

В ячейке К3 =СРЗНАЧ(C2:C18).

3. Количество всех товаров в таблице

=СЧЕТЗ (значение1;[значение2];...) — подсчитывает количество заполненных ячеек в диапазоне.

В ячейке К5 высчитаем =СЧЕТЗ(B2:B18).

4. Количество только тех товаров, по которым были осуществлены продажи (

=СЧЕТ (значение1;[значение2];…) — подсчитывает количество ячеек, содержащих числа, и количество чисел в списке аргументов.

В ячейке К6 вводим=СЧЕТ(D2:D18).

5. Сумма минимального чека

=МИН(число1;[число2];...) — находит наименьшее значение в списке аргументов.

В ячейке К7 вводим формулу =МИН(D2:D18).

6. Максимальная сумма транзакции

=МАКС(число1;[число2];...) — выводит максимальное значение среди аргументов.

В ячейке К8 определим =МАКС(D2:D18).

7. Нужно подсчитать сколько товаров не продавались, то есть сумма продаж содержит пустые ячейки.

=СЧИТАТЬПУСТОТЫ (диапазон) — позволяет подсчитать количество пустых ячеек в диапазоне ячеек.

В ячейке К9 вводим формулу =СЧИТАТЬПУСТОТЫ(D2:D18).

Рис. 1. Подсчет показателей по всей таблице

Рис. 1. Подсчет показателей по всей таблице

 

Формулы подсчета и суммирования на одно условие

Функции данной группы позволяют подсчитать не всю информацию с источника, а только ту, которая будет удовлетворять критерий отбора. Критерием может выступать ссылка на ячейку с конкретным значением, текст введенный с клавиатуры или же операции сравнения сцепленные опять же либо с ссылкой на значение в ячейке, либо с критерием, введенным как текст с клавиатуры. Подробнее на примере: на рис. 2 представлена таблица с продажами товаров в штуках и деньгах по различным категориям за кварталы 2018 года.

 

Приобрести полную версию статьи