Post #167

📊 Друзья, на канале новое видео: делаем квартальные отчёты в личном бюджете Сегодня продолжаем улучшать файл личного бюджета и создаём автоматические квартальные отчёты. Что делаем в видео: ✔️ копируем месячные отчёты как основу ✔️ меняем шапку и название ✔️ подтягиваем факт и планы автоматически ✔️ строим квартальные графики 💡 Формула для факта (по аналогии с месячным отчётом) Используем знакомую функцию: =СУММЕСЛИМН(РасшифровкаРасходов[Сумма];РасшифровкаРасходов[Год];'Структура расходов (Квартал)'!A4;РасшифровкаРасходов[Квартал];'Структура расходов (Квартал)'!B4;РасшифровкаРасходов[Категория];'Структура расходов (Квартал)'!C4) Но теперь фильтруем уже по: – году – кварталу вместо месяца – категории То есть можно быстро получить: 👉 сколько потрачено за квартал 👉 сколько пришло доходов за квартал Чтобы просуммировать планы, нам нужно связать месяцы с кварталами. 💡Как это сделать? Создаём маленький справочник с двумя полями. Месяц и Номер квартала. И дальше разбираем что делает ФИЛЬТР: =ФИЛЬТР(СправочникКварталов[Месяц];СправочникКварталов[Номер квартала]='Структура расходов (Квартал)'!B4) Чтобы Excel сам понимал, какие месяцы входят в нужный квартал. Дальше разбираем что делает ИНДЕКС: =ИНДЕКС(ФИЛЬТР(СправочникКварталов[Месяц];СправочникКварталов[Номер квартала]='Структура расходов (Квартал)'!B4);1) И дальше складываем всё что получили в один пирог: =СУММЕСЛИМН('Структура расходов (Месяц)'!E:E;'Структура расходов (Месяц)'!A:A;'Структура расходов (Квартал)'!A4;'Структура расходов (Месяц)'!B:B;ИНДЕКС(ФИЛЬТР(СправочникКварталов[Месяц];СправочникКварталов[Номер квартала]='Структура расходов (Квартал)'!B4);1);'Структура расходов (Месяц)'!C:C;'Структура расходов (Квартал)'!C4)+СУММЕСЛИМН('Структура расходов (Месяц)'!E:E;'Структура расходов (Месяц)'!A:A;'Структура расходов (Квартал)'!A4;'Структура расходов (Месяц)'!B:B;ИНДЕКС(ФИЛЬТР(СправочникКварталов[Месяц];СправочникКварталов[Номер квартала]='Структура расходов (Квартал)'!B4);2);'Структура расходов (Месяц)'!C:C;'Структура расходов (Квартал)'!C4)+СУММЕСЛИМН('Структура расходов (Месяц)'!E:E;'Структура расходов (Месяц)'!A:A;'Структура расходов (Квартал)'!A4;'Структура расходов (Месяц)'!B:B;ИНДЕКС(ФИЛЬТР(СправочникКварталов[Месяц];СправочникКварталов[Номер квартала]='Структура расходов (Квартал)'!B4);3);'Структура расходов (Месяц)'!C:C;'Структура расходов (Квартал)'!C4) Далее настраиваем проверочную ячейку для "Категории расходов относительно доходов": =D21/СУММЕСЛИМН(РасшифровкаРасходов[Сумма];РасшифровкаРасходов[Год];'Структура расходов (Квартал)'!A4;РасшифровкаРасходов[Квартал];'Структура расходов (Квартал)'!B4) Далее поправляем значения для "Категории расходов относительно доходов": =D4/СУММЕСЛИМН(РасшифровкаДоходов[Сумма];РасшифровкаДоходов[Год];'Структура расходов (Квартал)'!A4;РасшифровкаДоходов[Квартал];'Структура расходов (Квартал)'!B4) Далее не забываем также поправить значения для "Структура важности": =СУММЕСЛИМН(РасшифровкаРасходов[Сумма];РасшифровкаРасходов[Год];'Структура расходов (Квартал)'!S4;РасшифровкаРасходов[Квартал];'Структура расходов (Квартал)'!T4;РасшифровкаРасходов[Важность];'Структура расходов (Квартал)'!V4) 💡 Далее настраиваем автоматическую шапку отчёта, чтобы следующий отчёт создавался копированием. Формула сама делает следующий год и квартал: =СЦЕПИТЬ(ЕСЛИ(ЗНАЧЕН(ЛЕВСИМВ(A1;1))=4;1;ЛЕВСИМВ(A1;1)+1);" Квартал ";ЕСЛИ(ЗНАЧЕН(ЛЕВСИМВ(A1))=4;ЛЕВСИМВ(ПРАВСИМВ(A1;7);4)+1;ЛЕВСИМВ(ПРАВСИМВ(A1;7);4));" г.") 🤔 Подводя итог данному блоку и отвечая на вопрос: Зачем вообще нужны квартальные отчёты? Месяц – хорошо для текущего контроля. Но квартал уже показывает тенденцию. 👀 За квартал видно на более крупном отрезке: ✔️ растут ли расходы ✔️ выдерживается ли норма сбережений ✔️ какие категории раздулись ✔️ укладываемся ли мы в установленные планы ✔️ как идёт год в целом 🚗 Как я это вижу: День – шаг Неделя – короткая дистанция Месяц – километр Квартал – следующий город Год – точка на горизонте 🧐 Мы начинаем смотреть на маршрут 🎥 В видео (VK/RuTube) показываю всё пошагово на примере файла, который выложу ниже.