Консолідація робочих листків

Під терміном консолідаціямається на увазі ряд операцій з кількома робочими листками або робочими книгами. В деяких випадках консолідація може включати створення зв’язаних формул.

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

Є кілька прийомів консолідації інформації з кількох робочих книг:

* Використання формул, що містять зовнішні посилання.

* Копіювання даних і залучення команди “Спеціальна вставка”.

* Залучення команди “Дані ð Консолідація”(Data ð Consolidate).

* Використання зведеної таблиці.

Використання формул, що містять зовнішні посилання. Цей прийом передбачає створення формул, які містять посилання на інші робочі листки або робочі книги. Перевагами такого прийому є:

® Динамічне поновлення. У випадку зміни значень у вихідній робочій книзі формули перераховуються автоматично.

® Під час створення консолідуючих формул вихідні робочі книги необов’язково повинні бути відкритими.

Приклади:

= СУММ ( Лист2 : Лист10 ! А1 )

=[ Регіон1 . xls ] Лист1 ! А1 + [ Регіон2 . xls ] Лист1 ! А1

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

Скопіювати в буфер обміну дані з першого діапазону вихідного робочого листка.

Активізувати залежну робочу книгу і комірку, в яку треба помістити консолідовані дані.

Виконати команду “Правка ð Спеціальна вставка”(Edit ð Paste Special). В діалоговому вікні “Спеціальна вставка”вибрати потрібну арифметичну операцію і клацнути на кнопці “ОК”.

Залучення команди“ДаніðКонсолідація”. Це універсальний прийом консолідації. Команда“ДаніðКонсолідація”підтримує такі методи консолідації:

* По позиції.Метод застосовується, якщо розміщення інформації у вихідних робочих листках ідентичне.

* По категорії.Метод дозволяє підбирати дані з вихідних робочих листків по заголовкам рядків та стовпчиків.

В результаті виконання команди“Дані ð Консолідація”з’являється діалогове вікно “Консолідація”(Consolidate), яке має такі елементи:

® Поле “Функція”(Function). В цьому полі визначається функція, яка буде виконуватись над консолідованими даними, наприклад: “Сумма”(Sum); “К-сть значень”(Count); “Середнє”(Avg); “Максимум”(Max); “Мінімум”(Min); “Добуток”(Product); “К-сть чисел”(CountNums); “Незсунуте відхилення”(StdDev) (стандартне відхилення); “Зсунуте відхилення”(StdDevp) (стандартне відхилення для генеральної сукупності); “Незсунута дисперсія”(Var); “Зсунута дисперсія”(Varp)(дисперсія генеральної сукупності).

® Поле “Посилання”(Reference). В це поле вводиться адреса діапазону вихідного файлу для консолідації. Посилання на діапазон можна ввести вручну або скористатись будь-яким стандартним способом, якщо відповідна вихідна книга відкрита. Після введення адреси діапазону треба клацнути на кнопці “Додати”(Add), щоб додати адресу в список консолідованих діапазонів. Якщо консолідація виконується за позицією, то не треба включати заголовок діапазону, а якщо за категорією —включати обов’язково.

® Вікно “Список діапазонів”(All references) містить перелік всіх посилань, які були додані кнопкою “Додати”(Add).

® Розділ “Використовувати як імена”(Use Labels In). Опції цього розділу дозволяють при консолідації використовувати як імена заголовки в верхньому рядку, лівому стовпчику або в обох позиціях. Опції використовуються при консолідації по категорії.

® Опція “Створити зв’язки з вихідними даними”(Create Links to Source Data ). При виборі цієї опції в залежному робочому листку створиться структура, яка містить зовнішні посилання на комірку призначення. Крім того, структура містить також формули підрахунку підсумків. Якщо не активізувати цю опцію, то при консолідації формули створюватись не будуть.

® Кнопка “Огляд”активізує діалогове вікно, в якому можна вибрати робочу книгу. В результаті ім’я файлу буде вставлене в поле “Посилання”і залишиться додати лише адресу діапазону.

® Кнопка “Додати”(Add) додає посилання з поля “Посилання”(Reference) у вікно “Список діапазонів”(All references).

® Кнопка “Вилучити”(Delete) вилучає вибране посилання із списку діапазонів.

Приклад:

Треба консолідувати дані, що знаходяться в трьох листках різних робочих книг і містять деякі таблиці даних. Таблиці оздоблені рядком заголовків стовпчиків (назви місяців) та стовпчиком заголовків рядків (назви видів виконаних робіт). Самі таблиці містять обсяги виконаних робіт по видах і по місяцях для трьох регіонів. Для консолідації цих даних треба виконати такі дії:

Створити нову робочу книгу. Відкриті вихідні робочі книги чи ні — не має значення.

Залучити команду“ДаніðКонсолідація” —з’являється вікно “Консолідація”.

Вибрати тип консолідації. В даному випадку — функція “Сума”.

Ввести посилання на діапазон першого робочого листка. Якщо вихідна робоча книга відкрита, можна вказати діапазон за допомогою мишки, якщо закрита — знайти потрібний файл, клацнувши на кнопці “Огляд”. Посилання повинно включати адресу діапазону. Доцільно вказувати діапазон більший, ніж той, який потрібно консолідувати, щоб мати гарантію правильної роботи у випадку розширення діапазону у вихідному файлі. Після формулювання в полі “Посилання”потрібного посилання треба клацнути на кнопці“Додати”, щоб добавити його до списку діапазонів.

Аналогічно ввести посилання на діапазони другого і третього робочих листків, що консолідуються.

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

Встановити прапорець опції “Створити зв’язки з вихідними даними”. В результаті Excel створить структуру з зовнішніми посиланнями.

Щоб почати консолідацію, треба клацнути на кнопці “ОК”.

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

Робота із списками