В прошлый раз мы научились выбирать доход/расход за период.
В этой статье мы усовершенствуем нашу таблицу и научимся добавлять расходы по категориям.
Ссылка на файл: 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 варианта действий:
- Пусть будут нули до появления новых категорий. Это не красиво.
- Протягивать ячейку с суммой при появления новых категорий. Это не очень удобно и требует дополнительных действий
Есть решение
Допишем нашу формулу
=ЕСЛИ(ЕПУСТО(F4);;СУММЕСЛИМН($B:$B;$C:$C;F4;$A:$A;"<="&$G$2;$A:$A;">="&$F$2))
Что дает дополнение:
Если значение в ячейках столбца F не пустое, то формула работает. Если значение в столбце F нет значений, формула не выдает ничего.
В таком случае мы можем протягивать ячейку G4 на любую длину вниз и у нас до появления новых категорий будет пустота, а как появляется новая категория формула будет производить расчет.
Аналогично делаем для вкладки «Доходы»
Результат: