Сегодня мы поговорим об одной, на мой взгляд, очень удобной функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (Subtotal).
Часто в табличных данных мы имеем не только чистые данные, но и некие подбивки (промежуточные итоги и т.д.). Посмотрите на образец таблицы:
В таблице - продажи менеджеров, скомпонованные по кварталам. Причем в конце каждого квартала имеется строка с сумой продаж за квартал. На самом деле можно легко подсчитать сумму продаж за квартал, используя классическую функцию СУММ.
Но, при попытке подсчета итоговых продаж за год, мы, использовать функцию СУММ не сможем. Иначе в итоговую суммы за год дважды попадут все продажи - один раз, как продажи менеджера, второй раз, как итоговые продажи за каждый квартал. В общем получится не верная сумма - получится фигня.
Второй вариант - просто сложить четыре числа - итоговые продажи за каждый квартал. Схема в принципе рабочая. Можно и так, но это сложно использовать, если промежуточных сумм не четыре, как кварталов в нашем примере, а 44! Всегда есть риск промахнуться выбирая слагаемое.
Гораздо удобнее для этих целей использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (В английской версии SUBTOTAL)
Основное отличие этой функции от функции типа СУММ или аналогичной, это то, что она пропускает значения полученные с использованием этой-же функции.
Давайте посмотрим как выглядит синтаксис этой функции
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(код_функции; диапазон_1; [диапазон_2; …])
где
код_функции - это код, который указывает что именно нужно делать с числами в указанном диапазоне. Есть несколько вариантов:
1 для функции СРЗНАЧ
2 для функции СЧЁТ
3 для функции СЧЁТЗ
4 для функции МАКС
5 для функции МИН
6 для функции ПРОИЗВЕД
7 для функции СТАНДОТКЛОН
8 для функции СТАНДОТКЛОНП
9 для функции СУММ
10 для функции ДИСП
11 для функции ДИСПР
Другими словами, если нам нужно просуммировать числа в диапазоне, то следует использовать код "9"
Диапазон_1 - это диапазон данных, в котором мы должны произвести вычисления, используя функцию
Итак, вернемся к нашему примеру.
Подсчитаем итоги за 1-ый квартал, используя функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Для ячейки В7 получим формулу:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B3:B6)
9- код, который говорит о том, что мы будем именно суммировать данные
B3:B6 - это диапазон, из которого мы будем брать значения
Соответственно для 2-го, 3-го и 4-х кварталов получим соответствующие формулы:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B9:B12)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B15:B19)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B22:B27)
Теперь нам нужно получить общую сумму за год.
Опять воспользуемся функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, но укажем уже весь диапазон наших данных, начиная с ячейки В3 и заканчивая ячейкой В28.
Для ячейки В29 получим формулу:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B28)
Обратите внимание, что несмотря на то, что ячейки, содержащие сумму продаж по кварталам, попали в наш диапазон, их значения не будут учитываться в итоговой сумме.
Вот, собственно в этом и проявляется вся прелесть функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Надеюсь Вам понравилась моя статья. Пожалуйста, поставьте лайк - это очень важно, т.к. служит индикатором того, что такие статьи полезны для аудитории канала. Заранее благодарю. Спасибо!
Все остальные уроки по Гугл таблицам Вы можете посмотреть, посетив страницу с содержанием:
Без рекламы и только про Гугл таблицы можно почитать здесь: