Excel 2007: консолідація даних (ч. 2)
 | У попередній статті ми використовували кілька методів консолідації даних, але всі вони мають загальний недолік — при формуванні підсумкового звіту ми спиралися на фізичні атрибути таблиці. Так, при використанні формул це були посилання на відповідні комірки. При роботі з інструментом консолідації ми об’єднували дані за їх розташуванням на робочому листі. Це не завжди зручно — людині властиво оперувати поняттями логічними. І було б набагато краще консолідувати дані, використовуючи не реальні адреси, а назви рядків та колонок вихідної таблиці. Як реалізувати цю можливість в Excel 2007, які особливості цього способу консолідації, ми розберемося в цій статті, а заразом ознайомимося з деякими корисними прийомами формування консолідованих звітів. Юрій ЦИГАНОК, головний редактор |
Вихідні таблиці, за якими будується консолідований звіт, завжди мають заголовки. Це можуть бути заголовки рядків, колонок чи того та іншого. Логічно було б організувати об’єднання даних, використовуючи ці заголовки. І така можливість в Excel 2007 є. Причому для її використання достатньо просто перенастроїти параметри інструменту «
Консолидация».
КОНСОЛІДАЦІЯ ДАНИХ ЗА ЗАГОЛОВКАМИ
Роботу з інструментом «
Консолидация» ми продовжимо на даних прикладу з нашої попередньої статті. Нагадаємо, що ці дані зображено у вигляді чотирьох листів з іменами «ЗУ», «ЛЗ», «МЗ», «МСЗ». Структура даних на всіх листах однакова, фрагмент однієї з таблиць показано на рис. 1. Сьогодні нашим завданням буде — об’єднати інформацію з цих листів до одного підсумкового звіту. При цьому ми орієнтуватимемося не на місце розташування комірок, а на заголовки рядків та колонок таблиці. Виконуємо такі дії: 
1) відкриваємо документ з вихідними даними. Від попереднього прикладу у нас залишився консолідований звіт на листі «Свод_». Видаляємо дані з листа «Свод_»;
2) клацаємо по комірці «A1»;
3) вибираємо розділ «Данные» головного меню Excel;
4) у групі іконок «Работа с данными» клацаємо по елементу «Консолидация» (рис. 2). З’явиться вікно, показане на рис. 3. У ньому буде зображено настройки інструменту «Консолидация», які було виконано в попередньому сеансі роботи.
Важливо!
Excel 2007 запам’ятовує настройки (параметри) консолідації індивідуально для кожного листа, де є підсумковий звіт;5) у віконці «
Список диапазонов:» виділяємо діапазон «$ЗУ!$С$1:$H$26»;6) натискуємо на кнопку «
Удалить»;7) повторюємо цю дію для решти елементів у віконці «
Список диапазонов:»;8) клацаємо по параметру «
Ссылка:»;9) переходимо на лист «
ЗУ»;10) утримуючи натисненою кнопку миші, обводимо блок «
А1:h26». До цього блока потраплять усі дані разом із заголовками рядків та колонок таблиці ;11) натискуємо на кнопку «
Добавить» (рис. 3). Адреса виділеного блока з’явиться в області «Список диапазонов:»; 
12) переходимо на лист «ЛЗ». У ньому Excel автоматично виділить діапазон «А1:h26»;
13) натискуємо на кнопку «Добавить»;
14) повторюємо ці дії для листів «МЗ» та «МСЗ» доти, доки не сформуємо всі діапазони для консолідації. У нашому прикладі це «$ЗУ!$AС$1:$H$26», «$ЛЗ!$A$1:$H$26», «$МЗ!$A$1:$H$26», «$МСЗ!$A$1:$H$26»;
15) включаємо прапорець «значения левого столбца»;
16) натискуємо на «ОК». Фрагмент звіту з результатом консолідації показаний на рис. 4 (див. с. 29).
Підсумкові дані в таблиці на рис. 4 залишилися ті самі, але її форма змінилася. Перше, що впадає в очі, — у таблиці з’явилися заголовки рядків. Такими заголовками стали номера рахунків. Праворуч від колонки з номерами рахунків з’явився порожній стовпець. У принципі, на його місці мали б знаходитися найменування рахунків, але їх немає. Причина цього явища зрозуміла, вона випливає з логіки роботи Excel. Для об’єднання даних ми вирішили використовувати заголовки рядків (це лівий стовпець діапазону). Ці заголовки Excel розглядав як ключові поля. Збіг значень ключових полів для нього — своєрідний сигнал для обробки, у нашому випадку — для накопичення суми. Решту комірок області консолідації Excel вважає даними. До цієї категорії потрапили й комірки з найменуваннями рахунків. Оскільки в цих комірках знаходився текст, результатом підсумовування стали порожні значення.
Заголовки колонок у результуючій таблиці відсутні, оскільки ми не включили прапорець «подписи верхней строки» (рис. 3). У принципі, це не важливо: додати два рядки на початку таблиці та скопіювати заголовки колонок через буфер обміну забере не більше однієї хвилини.
Перш ніж продовжити роботу, повернемося до таблиці на рис. 1. На ньому показано приклад вихідних даних, для яких ми виконуємо консолідацію. Таку структуру таблиці вибрано не випадково. Зверніть увагу, що шапка таблиці займає два рядки і деякі комірки в ній оформлено з параметром об’єднання. Подивимося, що вийде, якщо до такої таблиці застосувати інструмент консолідації з використанням заголовків та рядків і колонок. Виконуємо такі дії:
1) видаляємо дані з листа «Свод_», робимо активною комірку «A1»;
2) вибираємо розділ «
Данные», потім клацаємо по іконці «Консолидация» (рис. 2). З’явиться вікно, показане на рис. 3;3) у віконці «
Список диапазонов:» нічого не змінюємо. Там повинні залишитися такі діапазони для консолідації: $ЗУ!$AС$1:$H$26», «$ЛЗ!$A$1:$H$26», «$МЗ!$A$1:$H$26», «$МСЗ!$A$1:$H$26»;4) включаємо прапорці «значения левого столбца» та «подписи верхней строки»;
5) натискуємо на «
ОК». Фрагмент звіту з результатом консолідації показано на рис. 5. 
У підсумковому звіті залишилися три колонки з даними. Це відбулося тому, що Excel використовував як заголовки колонок перший рядок таблиці. А в цьому рядку (рис. 1) лише п’ять елементів: номер рахунка, найменування рахунка, початкове сальдо, сума оборотів та кінцеве сальдо. Саме виходячи з цього Excel побудував структуру результуючого звіту. Він об’єднав дані початкових залишків по дебету і кредиту в одне ціле і те саме зробив з оборотами та кінцевим сальдо.
Важливо! При роботі з консолідацією не використовуйте в шапці таблиці об’єднання комірок. Краще спочатку зробіть консолідацію даних і лише потім красиво оформляйте заголовки.
Як вийти із такої ситуації?
Дуже просто. Потрібно перебудувати заголовки у вихідних таблицях, видаливши параметр об’єднання комірок.Стосовно нашого прикладу заголовки таблиць для консолідації можна було б оформити, як показано на рис. 6.
У цьому випадку об’єднання з використанням заголовків рядків та колонок дасть результат, фрагмент якого показано на рис. 7 (див. с. 30). Усе, що ми змінили у вихідних даних, це відмовилися від об’єднання комірок і кожному ключовому полю в колонці присвоїли унікальне ім’я. За таких умов структура результуючої таблиці практично збігається з вихідними даними. Єдине, чого в ній не вистачає, — це найменування рахунків (рис. 7).
Важливо!
Змінюючи форму вихідних таблиць, пам’ятайте, що при видаленні чи додаванні рядків або колонок адреси діапазонів консолідації не змінюються. Якщо додали (чи видалили) рядки (колонки) до вихідної таблиці, настройки інструменту «Консолидация» доведеться повторити.Консолідація таблиць за назвами заголовків має дуже важливу перевагу, яка полягає в тому, що у вихідних таблицях послідовність рядків та колонок може відрізнятися. А на практиці зазвичай так і є! Наприклад, ви можете для тренування переставити місцями колонки або пересортувати рядки на листах «
ЗУ», «ЛЗ», «МЗ», «МСЗ» і потім повторити консолідацію. Excel 2007 усе робить правильно. Головне, щоб текст у назвах заголовків рядків та колонок точно збігався.
КОНСОЛІДАЦІЯ ТА АВТОПІДСТАНОВКА
Вирішити проблему значень, яких бракує в консолідованому звіті, можна по-різному. Один із варіантів — скористатися буфером обміну та заповнити, наприклад, колонку «
Наименование счета» (рис. 6) значеннями із сусіднього листа. Спосіб простий, але не позбавлений недоліків. А що вийде, коли послідовність найменувань на листах відрізняється? При копіюванні доведеться уважно стежити за відповідністю кожного найменування та номера рахунка. Завдання не складне, але втомливе. До того ж є більш цікаве рішення — скористатися функцією даних «ВПР()». Ця функція має чотири параметри: шукане значення «ИскЗнач», блок комірок або зона пошуку «Блок», індекс зсуву «Индекс», тип порівняння «ТипСравн». А синтаксис функції має такий вигляд: «ВПР(ИскЗнач;Блок; Индекс;ТипСравн)». Функція переглядає значення першої (лівої) колонки області «Блок», намагаючись знайти значення «ИскЗнач». Якщо таке значення знайдене, функція зафіксує номер рядка та поверне як результат значення комірки зі зсувом за колонкою на величину «Индекс». Подивимося, як застосувати цю функцію до нашої консолідованої таблиці.Отже, наше завдання
: ми хочемо в підсумковому звіті заповнити колонку з найменуваннями рахунків. Алгоритм наших дій буде таким. Починаємо з другого рядка таблиці (рис. 7). У комірці «A2» знаходиться номер рахунка «10». Запам’ятовуємо це значення та починаємо переглядати номери рахунків у таблиці, де їх зображено в повному обсязі. У нас це може бути, наприклад, лист «ЗУ». У процесі сканування колонки «А» на листі «ЗУ» ми намагаємося відшукати значення «10». Його буде знайдено у другому рядку таблиці (рис. 6). У колонці «B2» знаходиться найменування для рахунка «10» — «Основные средства». Це значення розташоване зі зсувом «1» від початку зони пошуку. Його ми й повернемо як результат. А формула для комірки «B2» на листі «Свод_» має такий вигляд: «=ВПР(A2;ЗУ!$A$1:$B$65000;2;0)». Уводимо цю формулу до комірки «B2», потім копіюємо її на всю висоту таблиці. Остаточну форму консолідованого звіту за даними листів «ЗУ», «ЛЗ», «МЗ», «МСЗ» показано на рис. 8. 
ОРГАНІЗАЦІЯ ЗВ’ЯЗКІВ З ВИХІДНИМИ ДАНИМИ
Недолік отриманих нами раніше підсумків полягає в тому, що вони ніяк не пов’язані з вихідними даними. Спробуйте змінити дані на листах «
ЗУ», «ЛЗ», «МЗ», «МСЗ». Консолідовані підсумки на листі «Свод_» залишаться без змін. Для того щоб упровадити зв’язок між вихідними даними та результатом консолідації, використовується прапорець «Создавать связи с исходными данными» (рис. 3). Установимо його і повторимо консолідацію. Робимо так:1) видаляємо дані з листа «
Свод_», робимо активною комірку «A1»;2) вибираємо розділ «
Данные», потім клацаємо по іконці «Консолидация» (рис. 2). З’явиться вікно, показане на рис. 3;3) у віконці «
Список диапазонов:» нічого не змінюємо. Там повинні залишитися такі значення для консолідації: «$ЗУ!$AС$1:$H$26», «$ЛЗ!$A$1:$H$26», «$МЗ!$A$1:$H$26», «$МСЗ!$A$1:$H$26»;4) включаємо прапорці «
значения левого столбца», «подписи верхней строки» і «Создавать связи с исходными данными»;5) натискуємо на «
ОК». Фрагмент звіту з результатом консолідації показано на рис. 9. 
Зовнішній вигляд таблиці дуже змінився. З’явилися ознаки групування даних у вигляді значків зі знаком «+». Клацаємо по одному з таких значків. Наприклад, ліворуч біля рахунка «10» (у рядку з номером 6 на робочому листі). Значок «+» змінить свою форму і набуде такого вигляду: «-». А на робочому листі з’являться чотири приховані рядки, в яких знаходяться дані з підсумками по відповідному рахунку (рис. 9). Ці дані оформлено у вигляді посилань на комірки діапазонів консолідації. Наприклад, для отримання підсумків по рахунку «10» («Основные средства») Excel підсумовує такі комірки: «=ЗУ!$C$2», «=ЛЗ!$C$2», «=МЗ!$C$2», «=МСЗ!$C$2». Посилання на ці комірки розташовано за адресами «D2», «D3», «D4», «D5» листа з консолідованим звітом. Сама ж формула визначення початкового дебетового сальдо по рахунку «10» для всього підприємства має такий вигляд: «=СУММ(D2:D5)» (комірка «D6»). Пробуємо відкоригувати вихідні дані в будь-якій вихідній таблиці. Підсумки в консолідованому звіті теж зміняться.
Консолідація за заголовками таблиць з організацією зв’язків з вихідними даними — найуніверсальніший спосіб об’єднання даних з декількох робочих листів до одного підсумкового звіту. Хоча і в цьому способі роботи є свої нюанси. З одним із них ми сьогодні розберемося.
КОНСОЛІДАЦІЯ ЗА РОБОЧИМ КЛЮЧЕМ
Працюючи з інструментом консолідації, ми неодноразово стикалися з такою ситуацією, коли частина колонок у підсумковому звіті залишалася порожньою. Прикладом цьому може бути стовпець «
Наименование счета» на рис. 7. Це виникало в тих випадках, коли всередині таблиці (в області даних) знаходилися колонки з текстовою інформацією. Оскільки виконати арифметичні операції з такими даними Excel не може, він залишає в таблиці порожні значення. З точки зору правильності підсумкових значень у цьому немає нічого страшного. Але для подальшої обробки даних ситуація з порожніми комірками може перетворитися на проблему. Уявіть собі, що підсумковий звіт ви захочете використовувати як базу даних Excel, наприклад, побажаєте застосувати до нього фільтр або сортування. За наявності порожніх комірок на місці ключових полів зробити це буде неможливо. У будь-якому разі доведеться заповнити всі порожні комірки реальними даними. Для великої таблиці така операція може забрати чимало часу.Вирішити проблему можна різними способами. Ми вже використали два з них: заповнювали недостатню інформацію вручну (через буфер обміну) та застосовували функцію «
ВПР()». Здебільшого цього вистачає. Але так буває не завжди. Уявіть собі, що у вихідних таблицях частина інформації є унікальною. Стосовно нашого прикладу це означає, що в кожній таблиці є кілька рахунків, що не повторюються в інших таблицях. У цьому випадку ми не зможемо скористатися функцією «ВПР()» для підстановки недостатніх значень до підсумкового звіту. Не зможемо, оскільки в нас немає єдиного «довідника» з номерами всіх рахунків, які знадобляться для такої підстановки. У цій ситуації можна порадити зручний прийом, що дозволяє вирішити проблему незалежно від вмісту вихідних таблиць. Сенс його такий.Перед виконанням консолідації ми додамо до вихідних таблиць допоміжну колонку. Цю колонку прийнято називати робочим ключем. Стовпець із робочим ключем ми поставимо першим (крайнім ліворуч) у кожній таблиці. У ньому за допомогою формули ми об’єднаємо дані з декількох колонок. Причому формулу складемо так, щоб між кожним елементом знаходився символ-роздільник. Потім виконаємо консолідацію даних. Оскільки стовпець із робочим ключем стоїть першим, Excel використовуватиме його для об’єднання таблиць. Після консолідації ми отримаємо підсумковий звіт. У ньому буде колонка з робочими ключами і, звичайно, підсумкові значення. А оскільки в робочому ключі ми об’єднали дані з декількох стовпців, усі вони будуть видні в підсумковому звіті. Вважаю, усе стане зрозуміло, коли ми застосуємо цей прийом для консолідації наших таблиць.
Отже, за основу візьмемо дані, показані на рис. 7. Виконуємо такі дії:
1) відкриваємо файл, клацаємо по листу «ЗУ»;
2) утримуючи клавішу «
Ctrl», клацаємо по ярличках решти листів з вихідними даними — ми збираємося виконувати групову операцію, тобто відкоригувати кілька таблиць одночасно;3) виділяємо колонку «А
»;4) клацаємо правою кнопкою миші. Із контекстного меню вибираємо «
Вставить». Ліворуч від таблиці ми додали новий стовпець;5) до комірки «
А1» записуємо заголовок, наприклад, «Кл»;6) до комірки «
A2» вводимо формулу «=B2&”-”&C2». Ця формула об’єднує дані з колонок «B» і «C», тобто вона об’єднує номер рахунка та його назву, а між номером та назвою ставить роздільник «-»;7) копіюємо формулу на всю висоту таблиці. У результаті всі вихідні дані набудуть форми, як показано на рис. 10. Поки що нам не потрібні колонки «B» і «C» — усе продубльовано в колонці «
А». Але видалити їх ми не можемо, оскільки неправильно спрацюють формули, тому перетворимо всі формули на значення; 
8) виділяємо колонку «А»;
9) натискуємо на «Ctrl+С» (копіюємо її до буфера обміну);
10) не знімаючи виділення, розкриваємо список іконки «Вставить» головного меню. Із запропонованих варіантів вибираємо «Специальная вставка»;
11) у вікні «Специальная вставка» переводимо перемикач «Вставить» у положення «Значения»;
12) натискуємо на «ОК». Формули в таблицях зникли. На листі залишилися тільки результати їх роботи;
13) тепер видаляємо колонки «B» і «C» — вони нам більше не потрібні;
14) виконуємо консолідацію, використовуючи для об’єднання заголовки рядків та колонок. Фрагмент підсумкового звіту показано на рис. 11 (див. с. 33).
На наш погляд, цілком симпатичний результат. У колонці «А» можна побачити й номери, і назви рахунків. Це — результат об’єднання в робочому ключі відомостей із двох сусідніх колонок. Єдиний мінус полягає у тому, що ці дані займають один стовпець. Непогано було б рознести їх на дві колонки. Тим більше, що в підсумковій таблиці праворуч від колонки «B» є вільне місце (на рис. 11 це колонки «С» і «D», їх приховано для економії місця).
ЯК ПОДІЛИТИ РОБОЧИЙ КЛЮЧ
Завдання поділу робочого ключа вирішимо за допомогою формул. Це — не єдиний спосіб розв’язання проблеми. Свого часу ми обов’язково познайомимося і з іншими варіантами. Але сьогодні використання формул нам здається дуже доречним: і проблему вирішимо, і пригадаємо роботу із вбудованими функціями. Тим більше, що функції роботи з текстом докладно ми ще не застосовували.
Отже, наша мета. У колонці «А
» консолідованого звіту є дані про номер рахунка та його назву. Номер від найменування рахунка відокремлено символом «-».1) виділяємо колонки «
B», «С», «D»;2) натискуємо на клавішу «
Del», щоб очистити їх уміст;3) стаємо на комірку «
B1», уводимо заголовок «Счет». До комірки «С1» вводимо «Наименование»;4) до комірки «
B6» записуємо формулу «=ПСТР(A6;1;ПОИСК(“-”;A6;1)-1)» — ми заповнюватимемо тільки рядки з підсумками. Розглянемо роботу формули докладніше. Функція «ПОИСК(“{_”;A6;1)» застосовується для пошуку символу чи підрядка в текстовому рядку. У неї три параметри: що шукати, де шукати та з якої позиції тексту починати пошук. У нашому випадку функція шукає символ «"-"» у комірці «A6» починаючи з позиції «1». Для рядка «10-Основные средства/ЗУ» результатом її роботи буде «3». Функція «=ПСТР(A6;1;ПОИСК("-";A6;1)-1)» тепер еквівалентна «=ПСТР(A6;1;3-1)». У цієї функції три параметри: рядок, початкова позиція та кінцева позиція. Функція вирізає з рядка частину від початкової позиції до кінцевої позиції. У нашому випадку «=ПСТР (A6;1;3-1)» із рядка «10-Основные средства/ЗУ» поверне результат «10»;5) переходимо до комірки «
С6». Уводимо формулу «=ПСТР(A6;ПОИСК("-";A6;1)+1;ДЛСТР(A6))». Тут функція «ПОИСК("-";A6;1)+1» знаходить позицію початку частини рядка, що залишилася (це значення дорівнює «3+1», тобто «4»). А кінцева позиція — це довжина рядка, яку знаходимо за допомогою функції «ДЛСТР(A6)». Таким чином, для комірки «A6» із текстом «10-Основные средства/ЗУ» функція «=ПСТР(A6;ПОИСК ("-";A6;1)+1;ДЛСТР(A6)» еквівалентна «=ПСТР(A6;4;25)», а її результатом буде рядок «Основные средства/ЗУ»;6) копіюємо формули на всю висоту таблиці;
7) виділяємо стовпці «
B» і «С». За допомогою спеціальної вставки перетворимо їх на значення;8) видаляємо зайві колонки (у нашому випадку — це «
A» і «D»). Завдання вирішене. Тепер і номер рахунка, і його найменування займають два окремі стовпці.Прийом поділу колонок — дуже корисний інструмент для обробки даних. Тому нам хотілося б узагальнити його на більш складний випадок, коли вихідний матеріал потрібно поділити на кілька колонок. Якщо уважно придивитися, то ви побачите, що формули нашого прикладу для цього не підходять. Звернемося до таблиці, показаної на рис. 12. Вихідний рядок у комірці «
А2» містить код рахунка, найменування рахунка, назву підрозділу та номер року звітного періоду. Ці дані потрібно розподілити за окремими колонками. Таке завдання зручно вирішувати частинами. 
Додаємо 7 колонок. Перша («Ключ») містить вихідний рядок. Це текст такого вмісту: «10-Осн. Средства-ЗУ-2010». У колонці «B1» записуємо заголовок «Счет».
У комірці «B2» записуємо формулу «=ПСТР(A2;1;ПОИСК("-";A2;1)-1)». Вона вирізає з вихідного тексту номер рахунка, тобто результат роботи формули дорівнює «10». До колонки «C» запишемо залишок від вихідного рядка. Для цього в комірці «C1» друкуємо заголовок «Ост1», а до комірки «C2» заносимо формулу «=ПСТР(A2;ПОИСК (“-”;A2;1)+1;ДЛСТР(A2))». Результат її роботи — текст «Осн.средства-ЗУ-2010».
Із комірки «C2» отримаємо назву рахунка. Для цього в комірці «D1» записуємо текст «Наимен.», а в комірці «D2» — формулу «=ПСТР(C2;1;ПОИСК (“-”;C2;1)-1)». Аналогічні дії виконуємо з рештою колонок. Ось вміст окремих комірок:
Формули для поділу тексту на кілька колонок
| Адреса | Вміст комірки | Результат |
| «E1» | текст «Ост 2» | Ост 2 |
| «E2» | формула «=ПСТР(C2;ПОИСК( "-";C2;1)+1;ДЛСТР(C2))» | ЗУ-2010 |
| «F1» | текст «Подр.» | Подр. |
| «F2» | формула «=ПСТР(E2;1;ПОИСК( "-";E2;1)-1)» | ЗУ |
| «G1» | текст «Ост 3» | Ост 3 |
| «G2» | формула «=ПСТР(E2;ПОИСК( "-";E2;1)+1;ДЛСТР(E2))» | 2010 |
| «H1» | текст «Год» | Год |
| «H2» | формула «=G2» | 2010 |
Коли формули готові, копіюємо їх на всю висоту таблиці. Потім через інструмент спеціальної вставки перетворюємо формули на звичайний текст і видаляємо зайві колонки.
Запропонований спосіб — лише один із можливих варіантів поділу даних на кілька стовпців. Для вирішення цього завдання в Excel 2007 є чудовий інструмент. Він називається Майстром текстів. В одній із наших публікацій ми обов’язково подивимося, як з ним працювати.
На цьому ми завершуємо тему консолідації таблиць в Excl 2007. Сподіваємося, що матеріал цієї статті вас зацікавив і знадобиться у практичній роботі. А наступного разу ми приступимо до роботи зі зведеними таблицями — основним бухгалтерським інструментом в Excel 2007.
Чекаємо ваших запитань, листів, пропозицій та зауважень за адресами bk@id.factor.ua, nictomlar@rambler.ru або на форумі редакції www.bk.factor.ua/forum. Успішної роботи!
