22.03.2010
Excel 2007: консолідація даних
| Працюючи з базами даних Excel, ми досі рухалися від детальної інформації до її узагальнення. Режим автофільтру, підбиття підсумків — усі вони як вихідні дані використовують докладну базу, а як результати повертають підсумкові значення або локальну вибірку записів за заданими критеріями. Тобто так чи інакше ці інструменти вирішують схожі завдання: із розгорнутого, докладного списку формують компактний підсумковий звіт. Однак у практиці бухгалтерського обліку буває й інша ситуація: коли потрібно звести декілька розрізнених таблиць в один узагальнений звіт, об’єднавши в ньому дані в однойменних рядках та колонках. Для великих таблиць зробити таку операцію вручну дуже важко. Працювати з декількома таблицями завжди важко і це призводить до безлічі помилок. На щастя, в Excel 2007 є просте вирішення цієї проблеми — інструмент консолідації даних. Розібратися з ним і є метою цієї статті. Юрій ЦИГАНОК, головний редактор |
Консолідація даних — це об’єднання декількох звітів з різних листів (або робочих книг) в одну підсумкову таблицю. При виконанні консолідації даних Excel виконує арифметичні операції. Список доступних операцій практично такий самий, як при визначенні проміжних підсумків. Це сума, мінімальне, максимальне або середнє значення, добуток, кількість чисел тощо.
Способи консолідації
Метод виконання консолідації залежить від способу організації вихідних даних. При цьому можна виділити три варіанти:
1) консолідація за допомогою формул. Тут мається на увазі ручний спосіб консолідації таблиць. У цьому випадку розрахунок підсумкових значень виконується за формулами з тривимірними посиланнями, тобто посиланнями на діапазони та комірки з різних листів. Такий спосіб застосовують, коли вихідні таблиці не мають загальної структури, заголовки в них різні, дані розташовані безсистемно. Застосовують цей метод не тому, що він зручний, — просто нічого іншого не залишається;
2) консолідація за розташуванням комірок. У цьому випадку передбачається, що вихідні дані на всіх листах мають однакову структуру. Наприклад, якщо на робочому листі «
Кв1» у комірці «A10» записано дебетовий оборот за рахунком 281 за І квартал, то і в таблиці для ІІ кварталу «Кв2» цей оборот теж знаходиться в комірці «A10». Зібрати такі дані у загальний звіт можна автоматично, через інструмент консолідації: викликаємо функцію об’єднання, вказуємо діапазони даних, вибираємо операцію, решту Excel зробить сам. Цей спосіб об’єднання чимось нагадує операцію складання матриць, причому матрицями є листи робочої книги. У бухгалтерській практиці такий метод консолідації застосовують рідко. Більш поширеним є третій варіант;3) консолідація за заголовками рядків або стовпців.
Тут дані для об’єднання ідентифікуються не адресою комірки, а заголовками рядків та (або) колонок. Це означає, що вихідна інформація на робочих листах може бути розташована довільно. Наприклад, дебетовий оборот за рахунком 281 за І квартал — у комірці «A10» листа «Кв1», а той самий оборот за ІІ квартал — у комірці «A15» листа «Кв2». Свободи при об’єднанні в цьому випадку значно більше. Та й сам метод більш наочний. Розглянемо роботу з усіма згаданими варіантами консолідації на практиці.
Вихідні дані для прикладу
Щоб розібратися з інструментами консолідації, нам знадобиться приклад. Ми вибрали як такий приклад завдання формування оборотного балансу виробничого об’єднання. Інформація ця отримана зі звітності конкретного підприємства. Щоправда, частину даних відкориговано (спрощено для більшої наочності) та зі зрозумілих причин змінено назву підприємства.
Отже, у зоні нашої уваги виробниче об’єднання «Завод імені Большакова». Підприємство випускає міні-техніку сільськогосподарського призначення (мотоплуги, мінітрактори тощо). До складу об’єднання входять три виробничі підрозділи, що не є юридичними особами: ливарний завод, моторобудівний (дизельний) завод, машинобудівний (механоскладальний) завод. Це означає, що кожний підрозділ самостійно веде бухгалтерський облік і щомісячно здає в заводоуправління свій оборотний баланс. У заводоуправлінні за допомогою консолідації складається оборотний баланс об’єднання в цілому.
Усі операції між окремими підрозділами а також заводоуправлінням та підрозділами проводяться через рахунок внутрішньогосподарських розрахунків «
683», на якому для кожного підрозділу, а також для заводоуправління відкрито окремі субрахунки, а саме:— «
6830» — заводоуправління (ЗУ);— «
6831» — ливарний завод (ЛЗ), випускає деталі для двигунів та литво під замовлення;— «
6832» — моторобудівний завод (МЗ), випускає двигуни для власної продукції та на продаж;— «
6833» — машинобудівний завод (МСЗ), випускає готову продукцію виробничого об’єднання.Наприклад, заготовки для литва, придбані заводоуправлінням та оприбутковані на субрахунок «
201», воно передає ливарному заводу з розподільного складу проводкою «Дт 6831 — Кт 201». Ливарний завод, у свою чергу, відображає отримання заготовок на свій склад проводкою «Дт 201 — Кт 6830».Операції з контрагентами проводить тільки заводоуправління. Тут під контролем заводоуправління зосереджені і банківські рахунки об’єднання. Нарахування заробітної плати здійснюється централізовано розрахунковим відділом.
У результаті щомісячної консолідації формується оборотний баланс за об’єднанням у цілому як сума сальдо на субрахунках усіх виробничих підрозділів та заводоуправління. Рахунок «
683» у консолідованому оборотному балансі приймає нульове сальдо внаслідок того, що в цілому обороти за дебетом та кредитом симетричні, хоча й проходять вони за різними його субрахунками.Отже, принцип організації бухгалтерського обліку на виробничому об’єднанні «Завод ім. Большакова» ми виклали. Час перейти до практики та продемонструвати сам процес виконання консолідації. Для цього на окремих листах робочої книги розмістимо оборотні баланси всіх виробничих підрозділів та заводоуправління, щоб провести їх консолідацію засобами Excel 2007. Робочі листи назвемо за скороченими назвами підрозділів: «
ЗУ», «ЛЗ», «МЗ», «МСЗ».Для наочності (пожертвувавши реалістичністю) залишимо в оборотному балансі тільки деякі, найбільш використовувані рахунки. Крім того, обмежимо рівень деталізації облікових даних рахунками першого порядку («
20», «31» тощо), за винятком рахунка «68», в якому виділимо субрахунок внутрішньогосподарських розрахунків «683». Таке обмеження непринципове: зрозумівши принцип проведення консолідації в Excel, ви легко поширите його на свої реальні дані.Почнемо із заводоуправління. Його оборотний баланс, в якому є всі рахунки нашого умовного робочого плану рахунків, наведено на рис. 1. З метою економії місця для решти підрозділів ми не наводитимемо всі рахунки, а обмежимося тільки тими, які реально використовуються (табл. 1, 2, 3). Баланс кожного підрозділу розмістимо на окремому листі, починаючи з комірки «
A1». Наше завдання — побудувати зведений оборотний баланс усього виробничого об’єднання.
Таблиця 1
Оборотний баланс за підрозділом «Ливарний завод»
Счет | Наименование счета | Сальдо на 01.04.2009 | Обороты, апрель 2009 | Сальдо на 30.04.2009 | |||
Дт | Кт | Дт | Кт | Дт | Кт | ||
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
10 | Основные средства | 80000000 |
|
|
| 80000000 |
|
11 | Прочие необратимые материальные активы | 7650000 |
|
|
| 7650000 |
|
13 | Износ необратимых активов |
| 23600000 |
| 701300 |
| 24301300 |
15 | Капитальные инвестиции |
|
|
|
|
|
|
20 | Производственные запасы | 978600 |
| 48000 | 495200 | 531400 |
|
22 | Малоценные и быстроизнашиваемые предметы | 244400 |
| 13600 | 65800 | 192200 |
|
23 | Производство | 834800 |
| 652400 | 798500 | 688700 |
|
24 | Брак в производстве | 35900 |
| 3600 | 9500 | 30000 |
|
25 | Полуфабрикаты | 154000 |
| 64500 | 25000 | 193500 |
|
26 | Готовая продукция | 654800 |
| 798500 | 697500 | 755800 |
|
30 | Касса |
|
|
|
|
|
|
31 | Счета в банках |
|
|
|
|
|
|
36 | Расчеты с покупателями и заказчиками |
|
|
|
|
|
|
40 | Уставный капитал |
|
|
|
|
|
|
44 | Нераспределенная прибыль (непокрытые убытки) |
|
|
|
|
|
|
63 | Расчеты с поставщиками и подрядчиками |
|
|
|
|
|
|
64 | Расчеты по налогам и платежам |
|
|
|
|
|
|
65 | Расчеты по страхованию |
|
|
|
|
|
|
66 | Расчеты по оплате труда |
|
|
|
|
|
|
683 | Расчеты по другим операциям (внутрихозяйственные расчеты) |
| 66952500 | 1212200 |
|
| 65740300 |
70 | Доходы от реализации |
|
|
|
|
|
|
90 | Себестоимость реализации |
|
|
|
|
|
|
98 | Налоги на прибыль |
|
|
|
|
|
|
| ИТОГО | 90552500 | 90552500 | 2792800 | 2792800 | 90041600 | 90041600 |
Таблиця 2
Оборотний баланс за підрозділом «Моторобудівний завод»
Счет | Наименование счета | Сальдо на 01.04.2009 | Обороты, апрель 2009 | Сальдо на 30.04.2009 | |||
Дт | Кт | Дт | Кт | Дт | Кт | ||
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
10 | Основные средства | 85000000 |
|
|
| 85000000 |
|
11 | Прочие необратимые материальные активы | 8032400 |
|
|
| 8032400 |
|
13 | Износ необратимых активов |
| 25060000 |
| 801300 |
| 25861300 |
15 | Капитальные инвестиции |
|
|
|
|
|
|
20 | Производственные запасы | 874060 |
| 58000 | 595200 | 336860 |
|
22 | Малоценные и быстроизнашиваемые предметы | 244400 |
| 23600 | 75800 | 192200 |
|
23 | Производство | 978950 |
| 752400 | 898500 | 832850 |
|
24 | Брак в производстве | 32900 |
| 4600 | 10500 | 27000 |
|
25 | Полуфабрикаты | 268000 |
| 64500 | 35000 | 297500 |
|
26 | Готовая продукция | 987600 |
| 898500 | 797500 | 1088600 |
|
30 | Касса |
|
|
|
|
|
|
31 | Счета в банках |
|
|
|
|
|
|
36 | Расчеты с покупателями и заказчиками |
|
|
|
|
|
|
40 | Уставный капитал |
|
|
|
|
|
|
44 | Нераспределенная прибыль (непокрытые убытки) |
|
|
|
|
|
|
63 | Расчеты с поставщиками и подрядчиками |
|
|
|
|
|
|
64 | Расчеты по налогам и платежам |
|
|
|
|
|
|
65 | Расчеты по страхованию |
|
|
|
|
|
|
66 | Расчеты по оплате труда |
|
|
|
|
|
|
683 | Расчеты по прочим операциям (внутрихозяйственные расчеты) |
| 71358310 | 1412200 |
|
| 69946110 |
70 | Доходы от реализации |
|
|
|
|
|
|
90 | Себестоимость реализации |
|
|
|
|
|
|
98 | Налоги на прибыль |
|
|
|
|
|
|
| ИТОГО | 96418310 | 96418310 | 3213800 | 3213800 | 95807410 | 95807410 |
Таблиця 3
Оборотний баланс за підрозділом «Машинобудівний завод»
Счет | Наименование счета | Сальдо на 01.04.2009 | Обороти, апрель 2009 | Сальдо на 30.04.2009 | |||
Дт | Кт | Дт | Кт | Дт | Кт | ||
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
10 | Основные средства | 122600000 |
|
|
| 122600000 |
|
11 | Прочие необоротные материальные активы | 9832400 |
|
|
| 9832400 |
|
13 | Износ необоротных активов |
| 28060000 |
| 801300 |
| 28861300 |
15 | Капитальные инвестиции |
|
|
|
|
|
|
20 | Производственные запасы | 874060 |
| 58000 | 595200 | 336860 |
|
22 | Малоценные и быстроизнашиваемые предметы | 244400 |
| 23600 | 75800 | 192200 |
|
23 | Производство | 978950 |
| 752400 | 898500 | 832850 |
|
24 | Брак в производстве | 32900 |
| 4600 | 10500 | 27000 |
|
25 | Полуфабрикаты | 268000 |
| 64500 | 35000 | 297500 |
|
26 | Готовая продукция | 996780 |
| 898500 | 797500 | 1097780 |
|
30 | Касса |
|
|
|
|
|
|
31 | Счета в банках |
|
|
|
|
|
|
36 | Расчеты с покупателями и заказчиками |
|
|
|
|
|
|
40 | Уставный капитал |
|
|
|
|
|
|
44 | Нераспределенная прибыль (непокрытые убытки) |
|
|
|
|
|
|
63 | Расчеты с поставщиками и подрядчиками |
|
|
|
|
|
|
64 | Расчеты по налогам и платежам |
|
|
|
|
|
|
65 | Расчеты по страхованию |
|
|
|
|
|
|
66 | Расчеты по оплате труда |
|
|
|
|
|
|
683 | Расчеты по прочим операциям (внутрихозяйственные расчеты) |
| 107767490 | 1714560 | 302360 |
| 106355290 |
70 | Доходы от реализации |
|
|
|
|
|
|
90 | Себестоимость реализации |
|
|
|
|
|
|
98 | Налоги на прибыль |
|
|
|
|
|
|
| ИТОГО | 135827490 | 135827490 | 3516160 | 3516160 | 135216590 | 135216590 |
Консолідація за допомогою формул
Насамперед слід зрозуміти, що ми хочемо отримати насправді. І найкращий спосіб зробити це — спершу вирішити завдання вручну, що ми зараз і зробимо. А інструментом для отримання консолідованого звіту будуть… тривимірні формули. Послідовність наших дій буде такою. Спочатку створюємо новий робочий лист. Для цього робимо так:
1) клацаємо правою кнопкою миші по ярличку будь-якого листа;
2) із контекстного меню вибираємо «
Вставить…»;3) у вікні «
Вставка» вибираємо «Лист»;4) натискуємо «
ОК».Формувати підсумковий звіт на новому листі — не обов’язкова умова, але так зручніше працювати. Та й результат буде наочнішим. Тепер
змінимо ім’я листа:1) клацаємо правою кнопкою миші по ярличку створеного листа;
2) із контекстного меню вибираємо «
Переименовать»;3) уводимо нове ім’я, наприклад, «
Свод»;4) натискуємо «
Enter».Заголовки в підсумковому звіті точно повторюватимуть вихідні таблиці. Тому просто скопіюємо їх через буфер обміну:
1) переходимо на лист «
ЗУ»;2) виділяємо блок «
A1:H2»;3) натискуємо «
Ctrl+C»;4) переходимо на лист «
Свод»;5) стаємо на комірку «
А1»;6) натискуємо «
Ctrl+V».Переходимо власне до розрахунків підсумкових значень. Робимо так:
1) ставимо покажчик на «
C3» листа «Свод»;2) вводимо формулу «
=ЗУ!C3+ЛЗ! C3+МЗ!C3+МСЗ!C3». Нагадаю, що друкувати її не потрібно. Набираємо символ «=», клацаємо на листі «ЗУ» у комірці «C3», друкуємо «+», клацаємо на листі «ЛЗ» у комірці «C3» тощо;3) завершивши формування формули, натискуємо «
Enter»;4) копіюємо формулу з «
C3» на всю ширину таблиці (до комірки «H3»);5) у комірку «
A3» листа «Свод» уводимо посилання «=МСЗ!A3»;6) у комірку «
B4» вводимо формулу «=МСЗ!B3»;7) виділяємо блок «
A3:H3», копіюємо його в буфер обміну та вставляємо на всі комірки консолідованого звіту. У прикладі це блок «C3:H26».Форматуємо підсумковий звіт. Тут є одна хитрість. За ідеєю лист підсумкових значень «
Свод» має бути відформатовано так само, як вихідні таблиці. Здавалося б, можна скористатися інструментом «Формат по образцу» і швидко вирішити це завдання. Але при цьому виникає одна проблема. У вихідній таблиці частину комірок відформатовано з параметром об’єднання. При спробі копіювати формат Excel видасть попередження про помилку та відмінить цю операцію. Тому ми вчинимо інакше і пригадаємо, що в Excel 2007 можна форматувати та заповнювати даними декілька листів одночасно. Робимо так:1) клацаємо лівою кнопкою миші по ярличку листа, хай це буде «
МСЗ»;2) утримуючи натиснутою клавішу «
Ctrl», клацаємо по ярличку листа «Свод». Так ми виділили обидва листи одночасно. Тепер усі операції з листом «МСЗ» синхронно виконуватимуться і на листі «Свод». У нашому прикладі в такий спосіб зручно привести у відповідність ширину колонок на різних листах;3) ставимо покажчик миші на межу колонки в області її заголовка;
4) коли покажчик набуде форми двоспрямованої стрілки, натискуємо ліву кнопку миші;
5) утримуючи її, злегка переміщаємо покажчик убік і тут же повертаємо на місце. Тим самим ми проімітували зміну ширини колонки на листі «
МСЗ». На листі «Свод» відповідна колонка набуде такої самої ширини;6) виконуємо цю операцію з усіма колонками таблиці на листі «
МСЗ»;7) виділяємо весь лист («
Ctrl+A»);8) установлюємо тип та розмір шрифту, і також формат відображення чисел. Як це робити, думаю, повторювати не потрібно;
9) клацаємо по кнопці «
Офис», потім по кнопці «Параметры Excel»;10) клацаємо по групі параметрів «
Дополнительно»;11) знаходимо розділ «
Показывать параметры для следующего листа»;12) дивимося на значення у списку листів, там має бути зазначено «
Свод». Якщо це не так, розкрийте список та виберіть із нього лист, де розміщуватимуться підсумки;13) зніміть прапорець «
Показывать нули в ячейках, которые содержат нулевые значения»;14) натисніть «
ОК». Тепер нульові значення не буде видно на екрані;15) послідовно клацаємо мишею по ярличку листа «
ЗУ», потім — по ярличку «Свод», щоб скасувати групування.Консолідований звіт готовий. Його форму наведено на рис. 2 (див. с. 32).
Об’єднання значень через інструмент «Консолідація»
Завдання ми вирішили. Причому витратили загалом небагато часу. Але це можливо тільки при дотриманні двох умов:
1) вихідна таблиця невелика;
2) усі дані для консолідації мають абсолютно однакову структуру.
Уявіть собі, що таблиці «
ЗУ», «ЛЗ», «МЗ», «МСЗ» були б різні. Наприклад, якщо в них відрізняється кількість рядків або порядок розміщення рахунків. Тоді кожну формулу в підсумковому звіті довелося б формувати окремо! Ні про яке копіювання не могло бути й мови. Звісно, така робота потребувала б набагато більше часу та сил. Тому розробники Excel 2007 забезпечили своє дітище потужним та зручним інструментом для консолідації даних. І ми зараз розберемося, як із ним працювати.
КОНСОЛІДАЦІЯ ДАНИХ за ЇХ РОЗТАШУВАННям НА ЛИСТІ
Інструмент консолідації даних Excel 2007 розташований у групі іконок «
Работа с данными». Ця група належить до розділу головного меню «Данные». Розглянемо, як можна застосувати цей інструмент для вирішення завдання з нашого прикладу. Робимо так:1) створюємо новий лист, назвемо його «
Свод_»;2) ставимо покажчик миші на комірку «
A1»;3) вибираємо розділ «
Данные» головного меню Excel 2007;4) у групі іконок «
Работа с данными» клацаємо по елементу «Консолидация» (рис. 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.
На екрані ми бачимо таблицю підсумкових значень. Кожний елемент цієї таблиці отриманий як сума відповідних комірок листів — учасників консолідації. Заголовків у таблиці немає, оскільки ми виконали консолідацію, спираючись на розташування комірок.
Важливо!
Excel обробив комірки робочої книги, не зважаючи на їх смислове навантаження. Наприклад, якби як діапазони консолідації ми вказали блоки «$A$1:$H$26», він підсумував би навіть номери рахунків… Відверто кажучи, хотілося б знайти більш акуратний спосіб формування підсумкового звіту.Отже, що ми отримали в результаті? Таблицю підсумкових значень, яку ще доведеться привести до належного вигляду, — додати заголовки рядків, колонок, скопіювати номери та назви рахунків. Часу це забере небагато, гірше інше. При будь-яких доопрацюваннях таблиці є ймовірність зробити помилку. А наше завдання — мінімізувати таку ймовірність. Є і ще один недолік: консолідований звіт ніяк не пов’язаний з вихідними даними. І якщо ми відкоригуємо дані за підприємствами, підсумковий звіт залишиться без змін.
У наступному номері ми розповімо, як обійти зазначені недоліки за допомогою інших, удосконалених методів консолідації. А поки що чекаємо ваших запитань, листів, пропозицій та зауважень на
bk@id.factor.ua, nictomlar@rambler.ru або форумі редакції www.bk.factor.ua/forum . Успішної роботи!