5.2.1. Основні поняття.
Під консолідацією розуміється узагальнення однорідних даних з різних джерел. Уміння консолідувати інформацію з різних джерел дуже високо цінується у всіх сферах ділового життя. Наприклад, при консолідації на основі значень декількох клітинок листу Excel формується значення e результуючої клітинці шляхом підсумовування, обчислення середнього значення, максимуму і т.д. Типовий приклад використання консолідації – узагальнення даних по виторзі, прибутку і т.д.
Наприклад, на трьох аркушах у Вас розташовані однакові за структурою таблиці за січень, лютий і березень. А Вам потрібно одержати сумарні дані за перший квартал. Це і виходить, що потрібно консолідувати три таблиці в одну.
Інструмент Консолідація дозволяє об'єднати дані таблиць, що знаходяться в різних місцях однієї книги, різних книг (тобто різних файлів ! ). Усі таблиці повинні мати абсолютно ідентичну структуру. З'єднання даних не є механічним.
У Excel існує два основних види консолідації даних:
консолідація по фізичному розташуванню осередків і
консолідація по заголовках.
Ці два види консолідації розрізняються способом, котрим задається зв'язок вихідних осередків з підсумковими.
При консолідації по фізичному розташуванню вказуються номери осередків, у яких містяться вихідні дані.
При консолідації по заголовках використовуються заголовки рядків і стовпців. При цьому консолідуються дані, розташовані в рядках і стовпцях з однаковими назвами.
Консолідацію по розташуванню варто використовувати у випадку, якщо дані усіх вихідних областей знаходяться в одному місці і розміщені в однаковому порядку; наприклад, цей спосіб можна використовувати для консолідації даних декількох аркушів, створених на основі одного шаблона.
Консолідацію по категорії варто використовувати у випадку, якщо потрібно узагальнити набір аркушів, що мають однакові заголовки, але з різною організацією даних. Цей спосіб дозволяє консолідувати дані з однаковими заголовками з всіх аркушів.
КОНТРОЛЬНІ ПИТАННЯ.
Що таке консолідація даних?
Які існують види консолідації і чим вони відрізняються?
Як перейменувати лист книги?
Приведіть приклад задачі, розв'язуваної за допомогою консолідації.
У якому випадку варто використовувати консолідацію даних по фізичному розташуванню?
5.2.2. ПРАКТИЧНЕ ЗАВДАННЯ № 2.
КОНСОЛІДАЦІЯ ДАНИХ ПО ФІЗИЧНОМУ РОЗТАШУВАННЮ.
Відкрийте нову книгу. Перейменуйте аркуш Лист 1 на Січень і наберіть невелику таблицю починаючи з клітинки а1
2. Аналогічно створіть таблицю на наступному аркуші Лист1, якому дайте ім'я "Лютий":
3. Аналогічно створіть аркуш "Березень" :
4. Створіть ще аркуш "1 квартал":
5. Тепер виділите інтервал b1:b3 на аркуші "1 квартал" і виконайте команди Данные > Консолидация. Ви побачите діалогове вікно "Консолидация".
Поставте прапорець ліворуч від напису "Создавать связи с исходными данными". Це значить, якщо у Вас будуть мінятися первинні таблиці, автоматично буде перераховуватися і підсумовуюча таблиця.
Примітка: Зв'язки не можна використовувати, якщо вихідна область і область призначення знаходяться на одному листі. Після установки зв'язків не можна додавати нові вихідні області і змінювати вихідні області, що уже входять у консолідацію.
Клацніть по віконцю "Ссылка". Тепер потрібно вказати, що саме Ви збираєтеся консолідувати.
Клацніть по закладці аркушу "Січень" і виділите інтервал b1:b3. Клацніть по кнопці "Добавить".
Перейдіть на аркуш "Лютий" і повторите цю процедуру. Зрозуміло, що і з аркушом "Березень" потрібно проробити те ж. А от тепер можна і клацнути по кнопці ОК. Перевірте: на аркуші "1 квартал" повинні бути підсумовуючі дані.
Збережіть отриману книгу з ім'ям Консолідація-1.
Кнопки зі знаком + дозволяють подивитися на дані, по яких виконувалось підсумовування і змінювати їх.
Питання. Що буде, якщо змінити дані, наприклад, за березень? Чи зміняться підсумовуючі дані?
Нагадаємо, Ви включили режим "Создать связи с исходными данными". До речі, як Ви думаєте, чи завжди потрібно його включати?
Примітка: Консолідацію можна робити не тільки за допомогою підсумовування. Можна використовувати й інші функції, наприклад, максимум чи середнє (для цього при консолідації потрібно клацнути по віконцю "Функция" і вибрати потрібну функцію).
Що буде, якщо консолідувати дані, що знаходяться не в однакових інтервалах (у нас усе було в інтервалах b1:b3, а могло б бути і так: у січні c4:c6, у березні - a8:a10, а в лютому - b1:b3)?
Слід зазначити, що консолідація може бути застосована і до тільки однієї таблиці, при цьому попереднє сортування не потрібно.