2. Формируем общий список и итоговую диаграмму

Продолжаем рассматривать Кейс 1.  Личные финансы

Итак, мы подготовили справочник категорий, теперь нам необходимо подготовить исходные выборки.

  1. Необходимо автоматически преобразовывать столбец с наименованиями банковских операций, чтобы была возможность подтягивать соответствующую категорию из справочника.
  2. В выгрузке сумма операций в нашем случае выгружается в Excel в виде текстового формата, поэтому нам необходимо текст преобразовать в число, чтобы над ними можно было совершать математические действия.
  3. Необходимо объединить полученные данные их двух выборок в одну итоговую (при этом взять преобразованное наименование операции, сумму и только расходные операции)

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

Рассмотрим на примере выгрузки Банка Тинькофф (лист tcs). В столбце «K» прописываем формулу для преобразования текста, а именно обрезаем полную фразу до третьего пробела, если пробелов меньше, то берем фразу полностью (то же самое мы делали в предыдущем уроке).

Далее нам необходимо преобразовать текстовый формат ячеек в числовой. Для этого воспользуемся приемом перемножения на единицу. В столбце «L» выведем преобразованную сумму. Усложним формулу, дописав формулу проверки столбца «C», если это расходная операция, то сумма выводиться, если любая другая операция, то в ячейке будет пусто.

Добавим дополнительный столбец, где пропишем формулу возврата номера строки =СТРОКА(), так же сделав проверку только на расходные операции, нам это будет необходимо, для формирования единого списка.

Аналогичные действия произведем на листе «yandeх».

Прием с формированием списка заключается в следующем, мы используем номер строки, как уникальные числа, при этом в другой выборке Яндекс.Денег мы тоже будет использовать номер строки, но чтобы числа не повторялись, ко всем текущим строкам прибавим, например число 1000, то есть начнем отсчет с 1000 (предполагая, что у нас вряд ли будет более 1000 транзакций за месяц).

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

Рядом в столбце «F» выведем все строки, которые у нас получились. С помощью функции ВПР подтянем название короткой операции, название категории из справочника и сумму операции.

В итоге мы получили итоговую объединенную таблицу из двух выгрузок, приведенных в единый формат. С помощью сводной таблицы строим итоговую таблицу. Вставляем круговую диаграмму, в фильтре убираем пустые строки, а так же я убрал категорию «Переводы/иб»

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

 Итоговый файл

 

 

Поделиться:
Нет комментариев

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.


×
Рекомендуем посмотреть