Консолідація по розташуванню

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

Приклад 1.1. Виконати консолідацію звітів про розподіл готельних номерів у розрізі відповідальних осіб за тиждень. Розташувати консолідовані звіти по розташуванню і категоріям на окремих аркушах.

Виконання:

1. Додати в книгу зі звітами 2 нових листи і привласнити їх ярличкам імена Консолід_по_розташ. і Консолід_по категор.

2. Скопіювати в лист Консолід_по_розташ форму звітупо реалізації товару, очистити кінцеву область (область призначення), тобто блок осередків, у котрий будуть поміщені консолідовані дані – В7:К15. Виділення не знімати!

3. Виберіть команду Консолідація в меню Дані (рисунок 1).

Рисунок 1 - Діалогове вікно консолідації.

 

4. Для підрахунку значень з кожного вихідного листа випливає в поле зі списком Функція, у вікні діалогу Консолідація вибрати функцію Сума. Залишіть прапорці в секції Використовувати мітки невстановленими. Оскільки ми не збираємося створювати зв'язку з вихідними аркушами, прапорець Створювати зв'язку з вихідними даними також залишіть невстановленим.

5. Уведіть посилання для кожного вихідного діапазону в поле Посилання або виділите ці діапазони за допомогою миші: клацнути по полю Посилання ® клацнути по ярличку Лист1 ® виділити осередку В7:К15 (зменшити діалогове вікно можна клацнути по кнопці наприкінці поля Посилання) ® клацнути по кнопці Додати (розгорнути діалогове вікно можна щигликом по тій же кнопці наприкінці поля Посилання) – зазначений діапазон осередків повинний з'явитися в поле Список діапазонів ® клацнути по ярличку Лист2 ® виділити осередку В7:К15 (при створенні форм документів у групі діапазон осередків буде встановлений автоматично) ® клацнути по кнопці Додати ® повторити зазначені дії для всіх підсумкових звітів ® ОК.

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

{Ім'я файлу}Ім'я листа! Посилання

Якщо вихідному діапазонові було призначене ім'я, можна використовувати це ім'я замість посилання.

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

Примітка. Консолідація виконується по блоці суміжних осередків, тому графа «Ціна», як довідковий матеріал, винесена в кінець звіту, щоб не розривати консолідований діапазон.

 

Консолідація по категорії

Розглянутий приклад 1.1. припускав повну ідентичність звітів у частині розглянутих категорій, у даному випадку, розподіл готельних номерів, що в практичній діяльності зовсім не обов'язково. Список готельних номерів у кожної підзвітної особи може змінюватися. У цьому випадку необхідно використовувати консолідацію по категоріях.

Приклад 1.2. Додати в звіт про розподіл номерів на Лист4 будь який готельний номер, на Лист5 – також відмінний від інших номерів. На листі Консол_по_категор створити консолідований звіт по всім відповідальним за тиждень.

Виконання.

1. На лист Консол_по_категор скопіювати заголовну частину таблиці звіту (А1:L6) з будь-якого листа.

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

3. М. Дані ® к. Консолідація ® заповнити вікно діалогу Консолідація ® вибрати Сума в поле зі списком, Функція ® установити прапорець У лівому стовпці в секції Використовувати мітки.

4. Увести посилання для кожного вихідного діапазону в поле Посилання або виділити ці діапазони за допомогою миші: клацнути по полю Посилання ® клацнути по ярличку Лист1 ® виділити осередку А7:К15 (у діапазон повинні бути включені осередки з категоріями) ® клацнути по кнопці Додати ® клацнути по ярличку Лист2 ® виділити осередку А7:К15 ® клацнути по кнопці Додати ® повторити зазначені дії для всіх підсумкових звітів, виділяючи дані про реалізовані товари ® ОК.

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

6. Консолідований звіт можна відформатувати, додати підсумкові рядки з формулами, довідкову інформацію (наприклад, ціну).

Примітка. Категорії можуть розташовуватися або в лівому стовпці, або у верхньому рядку виділюваного діапазону, на що повинно вказувати прапорець у секції Використовувати мітки. Тільки в одному стовпці або в одному рядку.

 

Завдання для лабораторної роботи № 4 на тему

“Microsoft Excel- консолідація даних”

Варіант 1