Зведені таблиці

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

Наприклад, якщо треба розрахувати максимальне значення в полі Виручка для кожної фірми або визначити відсоткове відношення суми поля Витрати кожної фірми до загальної суми в цьому полі, тоді для створення зведеної таблиці треба виконати такі дії:

1) Виділити клітинку таблиці і вибрати команди Данные – Сводная таблица.

2) Відкривається вікно Майстра зведених таблиць

Рис. 93

3) У першому вікні встановити перемикач У списку или базе даннях і натиснути кнопку Далее

4) У наступному вікні вказати або перевірити діапазон клітинок на основі яких будується зведена таблиця і натиснути кнопку Далее

5) У наступному вікні вибрати існуючий лист

Рис. 94

І вибрати клітинку, в якій починає створюватися зведена таблиця. У цьому ж вікні вибрати кнопку Макет…

Рис. 95

6) У даному вікні для формування стовпців, які матиме зведена таблиця, необхідно із набору стовпців перетягнути кнопку з назвою Фірма у частину вікна Столбец; у частину Данные перетягнути кнопки з назвами стовпців, значення яких розраховується (Виручка, Витрати)

7) За замовчуванням у підсумку розраховується функція Сума, для визначення іншої функції треба клацнути двічі мишкою на кнопці з назвою поля в частині Данные і після появи наступного вікна у переліку Операція вибрати потрібну функцію (Максимум)

Рис. 96

8) За допомогою кнопки Дополнительно > > активізується перелік додаткових функцій (Доля от суммы по строке), натиснути ОК, Далее, Готово.

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

Консолідація даних

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

Рис. 97. Дані про продаж: а - магазин Гейм"; б - магазин " Обрій"

 

При консо­лідації можна виконувати ті ж опе­рації над даними, що і при підбитті проміжних підсумків (Сумма, Произведение, Среднее тощо).

Наприклад є два списки на окремих робочих аркушах (мал.97, а, б), які мають імена - назви магазинів " Гейм" і " Обрій". Знайдемо загальні підсумки продажу в двох магазинах, тобто консо­лідовані дані.

Для консолідації даних треба вико­нати такі операції:

· Створити окремий аркуш для консолідованих даних і активізувати верхню ліву клітинку ділянки, призначеної для розміщення цих даних (наприклад, А1).

· Вибрати команду меню Данные - Консолидация і у діалоговому вікні Консолидация (Рис.98) у полі Функция задати операцію для підбиття підсумків. За замовчуванням встановлюється функція Сумма.

Рис. 98. Діалог Консолидация

У полі Ссылка введіть першу частину даних, що консолідуються (у даному прикладі - це діапазон для списку на Рис. 97, а).

Цю частину можна задати набором із клавіатури, але простіше – прийомом виділення. Для цього клацніть по кнопці праворуч від поля Ссылка, діалог Консолидация згорнеться до розміру однорядкового поля введення, звільняючи робочу частину аркуша. Обведіть мишею клітинки, що консолідуються, включаючи назви стовпців, і клацніть по кнопці справа від поля введення. У результаті в полі Ссылка діалогового вікна Консолидация з'явиться запис-посилання (у ньому зазначене ім'я аркуша, а потім через знак "! " абсолютні адреси клітинок діапазону). Натисніть кнопку Добавить.

· Аналогічно задайте другу частину даних, що розміщається на аркуші " Обрій" (Рис. 97, б). Оскільки діапазони клітинок на двох аркушах розташовані однаково, виділяти діапазон на другому аркуші не потрібно. Досить перейти на аркуш " Обрій",
а потім при поверненні в діалогове вікно Консолидация клацнути по кнопці Добавить.

· Для визначення імен даних, що консолідуються, необхідно встановити опцію Значення левого столбца або Подписи верхней строки (Рис. 98).

· Якщо встановити опцію Создавать связи с исходными даныями то дані в консолідованій таблиці оновлюватимуться при зміні вихідних даних.

· На завершення натиснути кнопку ОК.

На визначеному робочому аркуші буде відображена таблиця з кон­солідованими даними (Рис.99).

Рис. 99. Таблиця консолідованих даних

Ця таблиця буде структурована і в ній з'явиться додатковий стовпчик В з назвою робочої книги. Якщо клацнути по будь-якій із кнопок розгортання структури (кнопки «+»), з'являться додаткові рядки, що відображають початкові дані для кожного запису (Рис. 99).