Кряк, продолжаем создавать таблицу для кредитной карусели в Excel, часть 4. В прошлый раз мы с вами сделали модификацию, добавив туда карту Райффайзен банка на 110 дней.
Сегодня мы с вами капельку отдохнем и займёмся созданием календаря платежей и сводной. Хотя, как сказать, отдохнём... в моём стиле отдохнём))
Кому этим заниматься не хочется (а зря, буду фишки всякие рассказывать), буду рада, если вы вдумчиво полистаете статью до конца, чтобы мне монетизация засчитала ваш просмотр 😇. Файлик есть в конце.
Итак, начнём.
Создаём календарь контроля платежей
Поскольку я сказала изначально, что пользоваться всякими плюшками по типу Power Query мы с вами не будем (чтобы работало с телефона), поэтому будем выкручиваться по старинке.
Мне в голову пришло сразу простое решение в виде классического календаря, где в первой колонке у нас ключевое измерение, а в колонках — значения по датам. Ну, так и сделаю, но для начала...
Добавим новые колонки в таблицу на листе "Карусель"
В процессе раздумий у меня возникла идея сделать возможность настроить дату платежей, потому что в идейном файлике автор (ссылка в первой части) установила даты оплаты минимальных платежей на следующий же день после выписки.
Т.е. в исходной таблице мне не хватает двух колонок: насколько дней раньше выписки надо оплатить и дата платежа.
Ещё я решила убрать заливку колонок с формулами и сделать окрашивание полей для ввода значений розовым цветом, чтобы было удобно смотреть.
Добавляю колонки [Оплата раньше - N]
Напоминаю, что платежей у нас 3, поэтому и колонок новых надо тоже 3. Там ничего не пишу, это поле у нас будет для ввода.
Добавляю колонки [Дата оплаты - N]
Здесь формула элементарная: из [Вернуть до - N] вычитаем значение [Оплата раньше - N], если значение [Оплата раньше - N] не равно ноль. Проверка ЕСЛИ() нужна, чтобы не было ошибки при пустом поле даты.
Чтобы не лажануть с вводом чисел в поле я добавила:
- проверку введенных данных (число должно быть больше 0);
- форматирование по условию: [Дата оплаты - N] должна быть больше или равна [Дата снятия денег], если условие не выполнено, то покрасить ячейку красным.
Это нужно для ситуации, когда вы решили деньги вернуть раньше. Можно, конечно, изменить поле [Сколько платежей], но оно формульное, я предпочитаю его не трогать. Но это мне так нравится, вы можете делать как хотите.
Ну и для красоты перетасовала поля между собой и добавила группировку столбцов, чтобы было поменьше видно.
Создаю разлиновку по датам в Excel
В первой колонке ограничиваюсь 20 ячейками для карт (потому что больше я пока ни у кого не видела), в первой ячейке дат ставлю формулу "=СЕГОДНЯ()", чтобы у меня автоматически был сдвиг по датам при смене дня. Сверху над датой делаю преобразование в месяц и год, чтобы было видно, что это вообще за даты такие.
Кто хочет, может просто даты показывать. Мне так нравится тоже, но для выпендрёжа сделаем чуть иначе.
Чтобы оставить только день внешне, меняю формат ячейки в параметрах на "Д" — это для отображения дня без нулей для чисел меньше 10.
Дальше всё просто:
- к дате справа прибавляю 1, чтобы показывало следующий день;
- для отображения месяца сверху делаю проверку дат (см. скриншот), если даты отличаются — месяц отобразится.
А дальше протягиваю вправо настолько, насколько мне надо. Я решила сделать на 61 день (потому что больше неудобно смотреть), можно протянуть и больше.
Всё, календарь мы разметили, теперь можно и формулами заняться.
Считаем сумму платежей по картам по датам
Здесь почти всё просто, есть интересный нюанс для умных таблиц. Не все его знают, поэтому покажу.
Для начала, чтобы нули нам глаза не мусолили, через параметры Excel можно отключить их отображение на конкретном листе.
Теперь я хочу посчитать сумму платежа на дату по двум условиям: по названию карты и по дате в колонке. Использую формулу СУММЕСЛИМН(), закрепляя условия за колонкой A для карт и строкой 2 для дат.
Типа готово, но если я протяну эту формулу вправо, получится фигня — сдвинутся диапазоны по умной таблице.
Нам такого не надо, надо закрепить. В умной таблице это делается вот так (на примере поля [Карта]):
Расчет_платежей[[Карта]:[Карта]]
Это по аналогии похоже на диапазон типа A:A, только записанный как [[A]:[A]]. Выглядит немножко тупо, но отчасти стандартная запись — просто запомните, кто видит это в первый раз.
Вооружаемся кучей скобочек и дописываем формуру:
=СУММЕСЛИМН(Расчет_платежей[[Платёж - 1]:[Платёж - 1]];
Расчет_платежей[[Карта]:[Карта]];$A3;
Расчет_платежей[[Дата оплаты - 1]:[Дата оплаты - 1]];B$2)
Отлично, но... есть нюанс. У нас сейчас сколько платежей итого? :)
Правильно, три штуки. Нам надо трижды сделать расчёт суммы, поэтому и СУММЕСЛИМН() нам надо 3 раза.
Так, не хныкать! Мы же умные и можем это сделать быстро!
Берем формулу, копируем без знака равно в свободную ячейку. Выделяем несколько (!) ячеек и делаем замену " - 1" на " - 2".
Для третьего платежа делаем аналогично и всё — формула готова.
Всё, календарь готов! И даже с телефона его удобно листать.
Что касается сводной — у меня для вас мем. Причем тут мем — сейчас поймёте.
Способов много, но способа сделать сводную формулами — один
Смотрите, нам нужна такая сводная, которая автоматически покажет нам дату по каждой карте и сумму ближайшего платежа. Желательно, либо по факту наличия даты, либо на год вперёд. Таблица должна быть вот такого формата.
Тогда мы сможем получить вот такую сводную таблицу, в которой при обновлении можно будет просто убирать фильтром нули и будет нам счастье.
По другому не получится сделать именно в таком виде, чтобы сводная автоматически пересчитывала нам и даты, и платежи вперёд, и с телефона обновлялась. Отсюда возникает 2 варианта развития событий, где выбирать вам, что вы будете делать.
Вариант первый: придется добавлять данные по карте в промежуточную таблицу самостоятельно
Проще говоря, самый очевидный метод будет такой: надо создать промежуточную сборную таблицу, которая будет считать сумму платежей на год вперёд (для тех, кто планирует сразу много траншей проводить и работать с большим количество карт).
Чтобы вам с этим не мучаться, я сделала табличную заготовку на листе "Для сводной", которая использует ровно ту же формулу, что и на календаре, что закрепление там другое. Там есть буффер на 20 карт и даты подбиты на 365 дней вперёд. Перерасчёт идёт от СЕГОДНЯ(), поэтому даты в сводной будут тоже двигаться.
Тогда вам надо будет только переименовать на этом листе конкретную Карту-N на название, которое вы укажете в таблице на листе "Карты" — и всё.
Кстати, из этой сводной тоже можно сделать календарь и заменить ячейки на листе "Календарь" полноценной сводной.
Ну или поля дополнительные добавить — месяц, например.
Вариант второй: придется не использовать телефон
Те, кто будет сидеть в файле только с компьютера, знают способы намного проще и быстрее.
1. Сделать Unpivot через Power Query и смотреть актуальную сводную. Такое не работает в приложении на телефоне, обновляться не будет.
2. Преобразовать данные через функции ПОСТОЛБЦ, ВСТОЛБИК, ГСТОЛБИК, TAKE, DROP, LET и т.д. - эти функции доступны только для версий web и 365. По опыту своей работы точно могу сказать, что у некоторых до сих пор даже 2013-2016 версии не стоят, так что не подходит. Если вам это подходит, инструкция есть тут на английском.
Но если у вас версия 365 — нафига вам формулы?
Ну что? Живые? :)
Кто дочитал, тот получает 2 награды ⭐
1. Тьмок
2. Ссылку на файл в версии публикации. Снова нарекаю вас excel-скаутами, которые могут найти ошибки и сообщить об этом в комментариях к этой публикации.
В следующий раз добавляем карту последнего типа 🦆
И спасибо, что дочитали до этого места! Если вам понравилось и хочется меня поддержать, поставьте лайк и напишите комментарий. Если вам хочется сделать больше, вы можете кинуть монетку в мой пруд по этой ссылке.
Подписывайтесь, чтобы ничего не пропустить! Ну а если всё равно непонятно, что тут происходит — заходите в навигацию.
