2. Формируем общий список и итоговую диаграмму
Продолжаем рассматривать Кейс 1. Личные финансы
Итак, мы подготовили справочник категорий, теперь нам необходимо подготовить исходные выборки.
- Необходимо автоматически преобразовывать столбец с наименованиями банковских операций, чтобы была возможность подтягивать соответствующую категорию из справочника.
- В выгрузке сумма операций в нашем случае выгружается в Excel в виде текстового формата, поэтому нам необходимо текст преобразовать в число, чтобы над ними можно было совершать математические действия.
- Необходимо объединить полученные данные их двух выборок в одну итоговую (при этом взять преобразованное наименование операции, сумму и только расходные операции)
Все преобразования мы будет делать не затрагивая исходной таблицы, данные будем вносить в соседних столбцах. Итак, напомню, что итоговый файл у нас был получен следующий – файл.
Рассмотрим на примере выгрузки Банка Тинькофф (лист tcs). В столбце «K» прописываем формулу для преобразования текста, а именно обрезаем полную фразу до третьего пробела, если пробелов меньше, то берем фразу полностью (то же самое мы делали в предыдущем уроке).
Далее нам необходимо преобразовать текстовый формат ячеек в числовой. Для этого воспользуемся приемом перемножения на единицу. В столбце «L» выведем преобразованную сумму. Усложним формулу, дописав формулу проверки столбца «C», если это расходная операция, то сумма выводиться, если любая другая операция, то в ячейке будет пусто.
Добавим дополнительный столбец, где пропишем формулу возврата номера строки =СТРОКА(), так же сделав проверку только на расходные операции, нам это будет необходимо, для формирования единого списка.
Аналогичные действия произведем на листе «yandeх».
Прием с формированием списка заключается в следующем, мы используем номер строки, как уникальные числа, при этом в другой выборке Яндекс.Денег мы тоже будет использовать номер строки, но чтобы числа не повторялись, ко всем текущим строкам прибавим, например число 1000, то есть начнем отсчет с 1000 (предполагая, что у нас вряд ли будет более 1000 транзакций за месяц).
Далее на отдельном листе мы выводим полученные данные из обоих выборок, только номер строки выводим в первом столбце, дальше название короткой операции и потом сумма. Вторую выборку выведем в таком же порядке, но начиная с 250 строки просто ссылаясь на ячейки и протянув формулу вниз.
Рядом в столбце «F» выведем все строки, которые у нас получились. С помощью функции ВПР подтянем название короткой операции, название категории из справочника и сумму операции.
В итоге мы получили итоговую объединенную таблицу из двух выгрузок, приведенных в единый формат. С помощью сводной таблицы строим итоговую таблицу. Вставляем круговую диаграмму, в фильтре убираем пустые строки, а так же я убрал категорию «Переводы/иб»
Диаграмма готова, теперь, если нам необходимо проанализировать новый месяц. Мы должны выгрузить из интернет банка отчеты, вставить их просто на листы, если появились, новые операции, то внести их в справочник, обновить сводную таблицу и анализ готов.