Недавно я на практике столкнулся с задачей подсчитать сумму расходов по разным категориям и хочу сегодня поделиться с вами несколькими вариантами решения. Допустим, исходный список расходов выглядит так:
Общая сумма затрат понятна, но хотелось бы видеть более конкретный расклад по статьям расходов.
Вообще, в списке явно просматриваются две категории — магазины и кафе. Поэтому имело бы смысл добавить третий столбец «Категория» и указать ее в каждой строке (как сейчас сделано в файле, который вы найдете в конце статьи). Но на скорую руку можно обойтись и без этого.
Я не пользуюсь русским Office, т.к. не понимаю локализованные формулы Excel. Поэтому приверженцам тотальной локализации придется потерпеть скриншоты английского интерфейса. Ссылки по теме:
- Как добавить русский язык интерфейса и проверки правописания в английский Office
- Соответствие английских и русских формул Excel
Теперь к делу!
Способ 1 — Сводная таблица
Выделите ячейки с расходами (кроме итога) и выберите на ленте Вставка (Insert) — Сводная таблица (Pivot Table).
Увеличить рисунок
Увеличить рисунок
Чтобы увидеть сумму расходов только в кафе или только в магазинах, выберите фильтром названия, соответствующие желаемой категории.
В строке GrandTotal отобразится сумма строк, выбранных фильтром. Чтобы не возиться с фильтрами вручную каждый раз, имело смысл ввести категории еще на первом листе, как я сказал выше. Но можно достичь желаемого результата прямо в сводной таблице. Читатель Павел Симаков записал для вас отличное видео о группировке данных в сводной таблице!
Я знал о сводных таблицах, но фильтры навели меня на мысль о том, нельзя ли обойтись без сводной таблицы. Оказалось, что можно!
Способ 2 — Сумма отфильтрованных значений
На исходном листе:
- Выделите столбцы с данными и выберите на ленте Данные (Data) — Фильтр (Filter). Я рекомендую выделять именно столбцы, т.к. при выделении ячеек автоматический фильтр может не сработать (коллега по работе совсем недавно впал в ступор от такого).
- Замените формулу СУММА (SUM) на ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL), указав в качестве номера функции значение 9. Оно отвечает за сумму отображаемых строк.
- Выберите фильтром нужные названия или категорию, если имеется.
Вуаля! Общая сумма расходов в кафе как на ладони.
Вводя формулу SUBTOTAL, вы могли заметить, что у нее 11 функций, причем название каждой повторяется дважды (1 и 101, 2 и 102, и т.д.)
«Сотенные» функции учитывают скрытые строки — проверьте их работу самостоятельно.
