Главная Обратная связь

Дисциплины:






Консолідація даних в середовищі Microsoft Excel



Консолідація – це об’єднання даних з декількох таблиць,що можуть бути розташовані як на одному, так і на різних листах книги. В Excel існує декілька інструментів, що можуть консолідувати дані. Розглянемо 3 типу консолідації:

- по розташуванню – використовується для об’єднання даних, якщо дані вихідних областей знаходяться в одному і тому ж місці і розташовані в одному і тому ж порядку;

- по категоріям – використовується для об’єднання даних, якщо дані вихідних областей не упорядковані, але мають одні й ті ж заголовки;

- за допомогою трьохмірних посилань – використовується, коли відсутні обмеження по розташуванню даних на вихідних областях.

Розглянемо більш детально кожен з типів консолідації.

Для створення консолідації по розташуванню необхідно:

1. Скопіювати будь-яку таблицю на лист, на якому необхідно виконати об’єднання даних.

2. Виділити область числових даних без діапазону клітинок, що вміщують формули і очистити цю область.

3. Не знімаючи виділення, виконати команду Данные – Консолидация.

4. У вікні, що з’явилося, обрати функцію, яку необхідно використовувати для обробки даних. Для підведення підсумків в таблиці консолідації можуть бути використані операції:

- сума значень;

- середнє значення;

- максимум;

- мінімум;

- кількість значень;

- кількість непустих рядків;

- середньоквадратичне відхилення вибірки з генеральної сукупності;

- дисперсія вибірки з генеральної сукупності.

5. В поле Ссылка ввести перший діапазон даних, що консолідуємо і натиснути кнопку Добавить. При цьому цей діапазон відобразиться у полі Список диапазонов.

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

7. Натиснути кнопку ОК.

В результаті отримаємо таблицю з консолідованими даними.

 

Для створення консолідації по розташуванню необхідно:

1. Скопіювати шапку будь-якої таблиці на лист, на якому необхідно виконати об’єднання даних.

2. Поставити курсор в першу порожню клітинку.

3. Виконати команду Данные – Консолидация.

4. У вікні, що з’явилося, обрати функцію, яку необхідно використовувати для обробки даних.

5. В поле Ссылка ввести перший діапазон даних, що консолідуємо і натиснути кнопку Добавить. При цьому цей діапазон відобразиться у полі Список диапазонов.

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

7. Встановити параметри Использовать в качестве имен:

«Подписи верхней строки»;

«Значение левого столбца».

8. Натиснути кнопку ОК.

В результаті отримаємо консолідовану таблицю, без прив’язки до адресів клітинок.

Для демонстрації третього типу консолідації наведемо приклад, у якому розраховується сума вміст клітинки В4 першого листа, F5 другого листа та В9 третього листа. Все це робиться за допомогою формули:



 

=СУММ(Лист1!В4;Лист2! F5;Лист3!В9)

8.3 Контрольні питання

1. Що таке зведена таблиця?

2. Яке правило слід виконувати при формуванні макету зведеної таблиці?

3. Дайте визначення поняттю «консолідація даних».

4. Які типи консолідованих звітів можна зробити у Microsoft Excel?

5. Які операції можуть бути використані для підведення підсумків в консолідованому звіті?

 

Практичні завдання

Завдання 1

1. Побудувати таблицю в середовищі табличного процесору Microsoft Excel з використанням аналогічних способів форматування і заповнити її даними (див. рис. 8.1).

2. У клітинках на місці символу * створити необхідні формули для розрахунку значень.

3. Створити зведену таблицю за країною та кількістю придбаних путівок.

4. Створити зведену таблицю за датою, назвою туру і загальною сумою замовлення.

5. Створити зведену таблицю за назвою туру, датою, кількістю придбаних путівок і середньою сумою замовлення.

 

 

Рисунок 8.1 – Вихідна таблиця для виконання завдання 1

 

 

Методичні вказівки з виконання завдань за темою:

1. Для розрахунку суми замовлення необхідно у клітинку F3 ввести формулу: =D3*E3 і скопіювати у діапазон F4: F16.

2. Для розрахунку підсумкових значень у клітинці Е17 слід використати інструмент . В результаті отримаємо формулу =СУММ(Е4:Е16). Аналогічно визначається підсумок за сумою замовлення.

3. Побудова зведеної таблиці відбувається за допомогою майстра, що визивається за допомогою команди Данные – Сводная таблица.

4. На першому кроці майстра необхідно вибрати джерело даних для зведених таблиць - в списке или базе данных Microsoft Excel і тип звіту, що створюється, - сводная таблица. Натиснути на кнопці Далее.

5. На другому кроці слід виділити діапазон клітинок, у якому містяться дані для зведеної таблиці, тобто, всю таблицю одним блоком без підсумкового рядка. Для нашого завдання це буде діапазон А2:F16. Натиснути на кнопці Далее.

6. На третьому кроці, безпосередньо створюється макет зведеної таблиці як показано на рис.8.2, шляхом перетягування заголовків таблиці в потрібні області. ОК.

 

 

Рисунок 8.2 – Вікно макета зведеної таблиці по країнах і кількості придбаних путівок

 

7. Після побудови макета вибираємо місце розташування таблиці і натискаємо на кнопці Готово. В результаті отримаємо таблицю, що зображена на рис.8.3.

 

Рисунок 8.3 – Зведена таблиця по країнах і кількості придбаних путівок

 

8. Для створення наступної зведеної таблиці необхідно зробити таку ж послідовність дій, різниця буде лише у вигляді побудові макета (див. рис.8.4).

 

 

Рисунок 8.4 – Вікно макета зведеної таблиці за датою, назвою туру і загальною сумою замовлення

 

9. В результаті отримаємо таблицю, що наведена на рис. 8.5.

 

 

Рисунок 8.5 – Зведена таблиця за датою, назвою туру і загальною сумою замовлення

 

10. Макет зведеної таблиці для третього завдання буде мати вигляд, як на рис.8.6.

 

 

Рисунок 8.6 – Вікно макета зведеної таблиці за датою, назвою туру, кількістю і середньою сумою замовлення

 

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

 

 

Рисунок 8.7 – Діалогове вікно Вычисление поля сводной таблицы.

 

11. В результаті отримаємо таблицю, що наведена на рис. 8.8.

Рисунок 8.8 – Зведена таблиця за датою, назвою туру, кількістю проданих путівок і середньою сумою замовлення

 

Завдання 2

1. Побудувати таблицю в середовищі табличного процесору Microsoft Excel одночасно на чотирьох листах з використанням аналогічних способів форматування і заповнити її даними (див. рис.8.9).

 

Рисунок 8.9 – Вихідна таблиця для виконання завдання 2

 

2. У клітинках на місці символу * створити необхідні формули для розрахунку значень.

3. Перейменувати п’ятий лист на Звіт 1 і створити на ньому консолідацію за розташуванням на основі даних з чотирьох таблиць.

4. Внести зміни в назви турів у таблицях з різних листів (додати, видалити, поміняти місцями).

5. Перейменувати шостий лист на Звіт 2 і створити на ньому консолідацію за категоріями на основі даних зі змінених чотирьох таблиць.

6. Підготувати документ до друку: центрувати таблицю, створити колонтитули, що містять дату створення документу та прізвище студента, встановити необхідні поля та орієнтацію листа.

7. Документ зберегти в папці з прізвищем студента і роздрукувати.

 

Методичні вказівки з виконання завдання:

1. Щоб побудувати таблицю на чотирьох листах одночасно необхідно попередньо згрупувати ці листи за допомогою клавіш Ctrl або Shift, в залежності від того, чи є листи суміжними або несуміжними.

2. Для розрахунку підсумкових значень у клітинці G5 слід використати інструмент . В результаті отримаємо формулу =СУММ(B5:F5) і скопіюємо її у діапазон G6:G14.. Аналогічно визначається підсумки за менеджерами.

3. Для заповнення таблиць даними необхідно розгрупувати листи, натиснувши на будь-якому листі, що не входить в групу, після цього перейти на кожен з листів внести дані продажів путівок за всі чотири квартали.

4. Перейменування листів можна виконати за допомогою команди контекстного меню Переименовать.

5. Для створення консолідації за розташуванням необхідно скопіювати таблицю з будь-якого листа на лист Звіт 1, виділити діапазон B5:F14 і очистити його за допомогою клавіши Delete.

6. Після цього слід виконати команду Данные – Консолидация.

7. У вікні, що з’явиться вбрати функцію Сумма,перевести курсор у поле Ссылка, перейти на Лист1, виділити діапазон B5:F14 і натиснути на кнопку Добавить,при цьому посилання перейде у список діапазонів. Аналогічно поступити з усіма таблицями з інших листів (див. рис.8.10).

 

 

Рисунок 8.10 – Діалогове вікно «Консолидация»

 

8. Після натиснення на кнопці ОК отримаємо консолідовану таблицю, що містить об’єднані данні з чотирьох таблиць.

9. Для створення консолідації за категоріями спочатку внести зміни до складу категорій або порядку їх написання, після цього необхідно скопіювати шапку таблиці з будь-якого листа на лист Звіт 2,поставити курсор у клітинку А5 і виконати команду Данные – Консолидация.

10. У вікні, що з’явиться вбрати функцію Сумма,перевести курсор у поле Ссылка, перейти на Лист1, виділити діапазон А5:F14 і натиснути на кнопку Добавить,при цьому посилання перейде у список діапазонів. Аналогічно поступити з усіма таблицями з інших листів.

11. Включити прапорець значение левого столбцаі натиснути кнопку ОК.

12. Після цього отримаємо консолідовану таблицю, що містить об’єднані данні з чотирьох таблиць з урахуванням змін, що були зроблені раніше.


ЗМІСТОВНИЙ МОДУЛЬ 3
Системи управління базами даних

 





sdamzavas.net - 2018 год. Все права принадлежат их авторам! В случае нарушение авторского права, обращайтесь по форме обратной связи...