2. Формуємо загальний список і підсумкову діаграму

продовжуємо розглядати Кейс 1. Особисті фінанси

Отже, ми підготували довідник категорій, тепер нам необхідно підготувати вихідні вибірки.

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

Відео: 2 Побудова організаційної діаграми за допомогою області тексту

Всі перетворення ми буде робити не зачіпаючи вихідної таблиці, дані будемо вносити в сусідніх стовпцях. Отже, нагадаю, що підсумковий файл у нас був отриманий наступний - файл.

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

Відео: UML діаграма класів

Далі нам необхідно перетворити текстовий формат осередків в числовий. Для цього скористаємося прийомом перемноження на одиницю. У стовпці «L» виведемо перетворену суму. Ускладнимо формулу, дописавши формулу перевірки стовпчика «C», якщо це видаткова операція, то сума виводитися, якщо будь-яка інша операція, то в комірці буде порожньо.

Додамо додатковий стовпець, де пропишемо формулу повернення номера рядка = РЯДОК (), так само зробивши перевірку тільки на витратні операції, нам це буде необхідно, для формування єдиного списку.

Аналогічні дії проведемо на аркуші «yandeх».

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

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

Поруч в стовпці «F» виведемо всі рядки, які у нас вийшли. За допомогою функції ВПР підтягнемо назва короткої операції, назва категорії з довідника і суму операції.

У підсумку ми отримали підсумкову об`єднану таблицю з двох вивантажень, наведених в єдиний формат. За допомогою зведеної таблиці будуємо підсумкову таблицю. Вставляємо кругову діаграму, в фільтрі прибираємо порожні рядки, а так же я прибрав категорію «Переклади / ІБ»

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

 підсумковий файл