Excel 2007: консолідація даних

Excel 2007: консолідація даних

Працюючи з базами даних Excel, ми досі рухалися від детальної інформації до її узагальнення. Режим автофільтру, підбиття підсумків — усі вони як вихідні дані використовують докладну базу, а як результати повертають підсумкові значення або локальну вибірку записів за заданими критеріями. Тобто так чи інакше ці інструменти вирішують схожі завдання: із розгорнутого, докладного списку формують компактний підсумковий звіт. Однак у практиці бухгалтерського обліку буває й інша ситуація: коли потрібно звести декілька розрізнених таблиць в один узагальнений звіт, об’єднавши в ньому дані в однойменних рядках та колонках. Для великих таблиць зробити таку операцію вручну дуже важко. Працювати з декількома таблицями завжди важко і це призводить до безлічі помилок. На щастя, в Excel 2007 є просте вирішення цієї проблеми — інструмент консолідації даних. Розібратися з ним і є метою цієї статті. Юрій ЦИГАНОК, головний редактор Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

Консолідація даних — це об’єднання декількох звітів з різних листів (або робочих книг) в одну підсумкову таблицю. При виконанні консолідації даних Excel виконує арифметичні операції. Список доступних операцій практично такий самий, як при визначенні проміжних підсумків. Це сума, мінімальне, максимальне або середнє значення, добуток, кількість чисел тощо.

Способи консолідації

Метод виконання консолідації залежить від способу організації вихідних даних. При цьому можна виділити три варіанти:

1) консолідація за допомогою формул. Тут мається на увазі ручний спосіб консолідації таблиць. У цьому випадку розрахунок підсумкових значень виконується за формулами з тривимірними посиланнями, тобто посиланнями на діапазони та комірки з різних листів. Такий спосіб застосовують, коли вихідні таблиці не мають загальної структури, заголовки в них різні, дані розташовані безсистемно. Застосовують цей метод не тому, що він зручний, — просто нічого іншого не залишається;

2) консолідація за розташуванням комірок. У цьому випадку передбачається, що вихідні дані на всіх листах мають однакову структуру. Наприклад, якщо на робочому листі «

3) консолідація за заголовками рядків або стовпців.

Щоб розібратися з інструментами консолідації, нам знадобиться приклад. Ми вибрали як такий приклад завдання формування оборотного балансу виробничого об’єднання. Інформація ця отримана зі звітності конкретного підприємства. Щоправда, частину даних відкориговано (спрощено для більшої наочності) та зі зрозумілих причин змінено назву підприємства.

Отже, у зоні нашої уваги виробниче об’єднання «Завод імені Большакова». Підприємство випускає міні-техніку сільськогосподарського призначення (мотоплуги, мінітрактори тощо). До складу об’єднання входять три виробничі підрозділи, що не є юридичними особами: ливарний завод, моторобудівний (дизельний) завод, машинобудівний (механоскладальний) завод. Це означає, що кожний підрозділ самостійно веде бухгалтерський облік і щомісячно здає в заводоуправління свій оборотний баланс. У заводоуправлінні за допомогою консолідації складається оборотний баланс об’єднання в цілому.

Усі операції між окремими підрозділами а також заводоуправлінням та підрозділами проводяться через рахунок внутрішньогосподарських розрахунків «

— «

— «

— «

— «

Наприклад, заготовки для литва, придбані заводоуправлінням та оприбутковані на субрахунок «

Операції з контрагентами проводить тільки заводоуправління. Тут під контролем заводоуправління зосереджені і банківські рахунки об’єднання. Нарахування заробітної плати здійснюється централізовано розрахунковим відділом.

У результаті щомісячної консолідації формується оборотний баланс за об’єднанням у цілому як сума сальдо на субрахунках усіх виробничих підрозділів та заводоуправління. Рахунок «

Отже, принцип організації бухгалтерського обліку на виробничому об’єднанні «Завод ім. Большакова» ми виклали. Час перейти до практики та продемонструвати сам процес виконання консолідації. Для цього на окремих листах робочої книги розмістимо оборотні баланси всіх виробничих підрозділів та заводоуправління, щоб провести їх консолідацію засобами Excel 2007. Робочі листи назвемо за скороченими назвами підрозділів: «

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

Почнемо із заводоуправління. Його оборотний баланс, в якому є всі рахунки нашого умовного робочого плану рахунків, наведено на рис. 1. З метою економії місця для решти підрозділів ми не наводитимемо всі рахунки, а обмежимося тільки тими, які реально використовуються (табл. 1, 2, 3). Баланс кожного підрозділу розмістимо на окремому листі, починаючи з комірки «

Таблиця 1

Оборотний баланс за підрозділом «Ливарний завод»

Таблиця 2

Оборотний баланс за підрозділом «Моторобудівний завод»

Таблиця 3

Оборотний баланс за підрозділом «Машинобудівний завод»

Консолідація за допомогою формул

Насамперед слід зрозуміти, що ми хочемо отримати насправді. І найкращий спосіб зробити це — спершу вирішити завдання вручну, що ми зараз і зробимо. А інструментом для отримання консолідованого звіту будуть… тривимірні формули. Послідовність наших дій буде такою. Спочатку створюємо новий робочий лист. Для цього робимо так:

1) клацаємо правою кнопкою миші по ярличку будь-якого листа;

2) із контекстного меню вибираємо «

3) у вікні «

4) натискуємо «

Формувати підсумковий звіт на новому листі — не обов’язкова умова, але так зручніше працювати. Та й результат буде наочнішим. Тепер

1) клацаємо правою кнопкою миші по ярличку створеного листа;

2) із контекстного меню вибираємо «

3) уводимо нове ім’я, наприклад, «

4)  натискуємо «

Заголовки в підсумковому звіті точно повторюватимуть вихідні таблиці. Тому просто скопіюємо їх через буфер обміну:

1)  переходимо на лист «

2) виділяємо блок «

3) натискуємо «

4) переходимо на лист «

5) стаємо на комірку «

6) натискуємо «

Переходимо власне до розрахунків підсумкових значень. Робимо так:

1)  ставимо покажчик на «

2) вводимо формулу «

3) завершивши формування формули, натискуємо «

4) копіюємо формулу з «

5) у комірку «

6) у комірку «

7)  виділяємо блок «

Форматуємо підсумковий звіт. Тут є одна хитрість. За ідеєю лист підсумкових значень «

1)  клацаємо лівою кнопкою миші по ярличку листа, хай це буде «

2)  утримуючи натиснутою клавішу «

3) ставимо покажчик миші на межу колонки в області її заголовка;

4) коли покажчик набуде форми двоспрямованої стрілки, натискуємо ліву кнопку миші;

5)  утримуючи її, злегка переміщаємо покажчик убік і тут же повертаємо на місце. Тим самим ми проімітували зміну ширини колонки на листі «

6)  виконуємо цю операцію з усіма колонками таблиці на листі «

7) виділяємо весь лист («

8) установлюємо тип та розмір шрифту, і також формат відображення чисел. Як це робити, думаю, повторювати не потрібно;

9) клацаємо по кнопці «

10) клацаємо по групі параметрів «

11) знаходимо розділ «

12) дивимося на значення у списку листів, там має бути зазначено «

13) зніміть прапорець «

14)  натисніть «

15)  послідовно клацаємо мишею по ярличку листа «

Консолідований звіт готовий. Його форму наведено на рис. 2 (див. с. 32).

Об’єднання значень через інструмент «Консолідація»

Завдання ми вирішили. Причому витратили загалом небагато часу. Але це можливо тільки при дотриманні двох умов:

1) вихідна таблиця невелика;

2) усі дані для консолідації мають абсолютно однакову структуру.

Уявіть собі, що таблиці «

КОНСОЛІДАЦІЯ ДАНИХ за ЇХ РОЗТАШУВАННям НА ЛИСТІ

Інструмент консолідації даних Excel 2007 розташований у групі іконок «

1)  створюємо новий лист, назвемо його «

2)  ставимо покажчик миші на комірку «

3) вибираємо розділ «

4) у групі іконок «

У верхній частині вікна знаходиться список «Функция:». У ньому перелічено набір можливих арифметичних операцій при консолідації даних: сума, кількість, середнє значення, добуток тощо. За умовчанням Excel пропонує використовувати функцію «Сумма». Залишаємо цей пункт без змін.

У центрі вікна бачимо інформаційне поле, воно називається «Ссылка:». Сюди потрібно послідовно вводити діапазони з даними для консолідації. Адреси діапазонів уручну вибирати не потрібно. Ви можете просто виділити область на робочому листі, а Excel підставить адреси виділеного блока в область «Ссылка:».

Кнопка «Обзор…» відкриває вікно Провідника, де можна завантажити зовнішній файл з даними для консолідації. Така можливість дозволяє об’єднувати інформацію з декількох робочих книг в один підсумковий звіт.

Нижче параметра «Ссылка:» знаходиться область «Список диапазонов:». Тут перелічено всі фрагменти робочих листів та книг MS Excel, які братимуть участь у консолідації.

У правій частині вікна розташовано три кнопки. Із кнопкою «Обзор…» ми вже знайомі. Кнопка «Добавить» — додає поточний діапазон до списку консолідації. По суті, вона переносить вміст параметра «Ссылка:» до області «Список диапазонов:». Кнопка «Удалить» прибирає виділений елемент із списку діапазонів.

У нижній частині вікна «Консолидация» є ще три прапорці: «подписи верхней строки», «подписи левого столбца» та «Создавать связи с исходными данными». Роботу з ними розглянемо трохи пізніше на конкретних прикладах.

З вікном ми розібралися, випробуємо інструмент консолідації на практиці. Нагадаємо, що зараз у нас відкритий лист «Свод_» та активне вікно «Консолидация». Настав час заповнити його параметри. Робимо так:

1) клацаємо по параметру «Ссылка:»;

2) переходимо на лист «ЗУ»;

3) утримуючи кнопку миші, обводимо блок комірок «C3:H26». Це всі дані для консолідації за винятком заголовків рядків та колонок таблиці;

4) натискуємо кнопку «Добавить» (рис. 4 на с. 33). Адреса виділеного блока з’явиться в області «Список диапазонов:»;

5) переходимо на лист «ЛЗ». У ньому Excel автоматично виділить діапазон «ЛЗ!C3:H26»;

6) натискуємо кнопку «Добавить». У списку діапазонів консолідації з’явиться «C3:H26»;

7) повторюємо ці дії для листів «МЗ» та «МСЗ», допоки не вкажемо всі діапазони для консолідації. У нашому прикладі це «$ЗУ!$С$3:$H$26», «$ЛЗ!$С$3:$H$26», «$МЗ!$С$3:$H$26», «$МСЗ!$С$3:$H$26»;

8) завершивши формування діапазонів, натискуємо «ОК». Результат наведено на рис. 5.

На екрані ми бачимо таблицю підсумкових значень. Кожний елемент цієї таблиці отриманий як сума відповідних комірок листів — учасників консолідації. Заголовків у таблиці немає, оскільки ми виконали консолідацію, спираючись на розташування комірок.