В прошлый раз мы научились выбирать доход/расход за период.

В этой статье мы усовершенствуем нашу таблицу и научимся добавлять расходы по категориям.

Ссылка на файл: https://docs.google.com/spreadsheets/d/1eKBVd_8BTSdI3t7yfDmO_ZY0whqcm7evQ_K02Bv6BX0/edit?usp=sharing
(нажмите «Файл»/«Создать копию»)

Для чего это нужно?

Это нам позволит не только считать общие доходы/расходы за период, но и выбирать доходы/расходы по каждой категории.

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

1. Добавляем пустой столбец для категории

Нам нужно добавить пустой столбец между столбцами B и С

Нажимаем на столбец B, правой кнопкой мыши и выбираем «Вставить справа»

Назовем наш столбец «Категория»

Результат:

Аналогично сделаем для столбца с доходами

Результат:

2. Добавляем Справочник

Создаем новую вкладку

Нажимаем на «+»

Переименовываем в «Справочник»

В справочнике создаем 2 столбца: «Расходы» и «Доходы»

Пишем под каждым свои категории — категории расходов и категории доходов

Вы всегда сможете дополнить и изменить эти категории

3. Добавляем возможность выбора категории

В таблице «Расходы» выделяем ячейки под столбцом «Категория»

Рекомендую выделить больше строк — для будущих записей

Можно выделить 100-200 строк 

Нажимаем «Данные» / «Настроить проверку данных»

Нажимаем на знак таблицы

Теперь нам нужно выбрать категории расходов

Для этого переходим во вкладку «Справочник» и выделяем категории расходов с запасом.
Рекомендую выделить сразу 20-30 ячеек.

Если вы в дальнейшем будете добавлять категории, они у вас автоматически будут появляться в раскрывающемся списке.
Нажимаем ОК

Нажимаем «Сохранить»

Результат:

Теперь вы можете выбирать из раскрывающегося списка категории расходов

Аналогично делаем с доходами

Результат:

Заполним графу категории во вкладке «Расходы»

4. Вывод списка категорий

В ячейке F4 Напишем формулу =UNIQUE(C2:C) для того, чтобы вывести уникальные значения категорий.

Уникальные значения — список всех значений в столбце С без повторов.

Результат:

Если вы добавите новую категорию, она появится в списке автоматически

В ячейке G4 напишем формулу =СУММЕСЛИМН($B:$B;$C:$C;F4;$A:$A;»<=»&$G$2;$A:$A;»>=»&$F$2)

Разберем формулу:

Эту формулу мы начали разбирать в первой части статья (ссылка)

Сумма диапазона B:B (столбец с цифрами расходов)

Условия суммирования:

Если столбец C:С = F4
Если значение в столбце категорий С  равно значению категории, по которой мы проводим суммирование. В нашем случае «Маркетинг».
Т.е формула суммирует все значения в столбце B, рядом с которыми есть значение «Маркетинг»

Дополнительные условия:

Столбец А (столбец дат) меньше или равен дате начала периода (ячейка G2)
Столбец А больше или равен дате конца периода (ячейка F2)

Т.к мы будем протягивать формулу вниз, то все ячейки, кроме F4 у нас со знаком $.
Т.е при протягивании в формуле будет только меняться параметр F4 — параметр названия категории.

Результат:

Протянем ячейку G2 вниз на 3 строки

Результат:

Готово!

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

5. Небольшое дополнение

Если мы протянем ячейки дальше вниз (с запасом на появление новых категорий), то у нас будут нули в ячейках (т.к пока нет новых категорий в списке)

У нас есть 2 варианта действий:

  1. Пусть будут нули до появления новых категорий. Это не красиво.
  2. Протягивать ячейку с суммой при появления новых категорий. Это не очень удобно и требует дополнительных действий

Есть решение

Допишем нашу формулу

=ЕСЛИ(ЕПУСТО(F4);;СУММЕСЛИМН($B:$B;$C:$C;F4;$A:$A;»<=»&$G$2;$A:$A;»>=»&$F$2))

Что дает дополнение:

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

Дополнение в формуле подчеркнуты красным

Аналогично делаем для вкладки «Доходы»

Результат: