Створення зведених таблиць

Зведена таблиця - це інтерактивна таблиця робочого листа, що підсумовує великі обсяги даних, використовуючи обраний вами формат і метод обчислень. Зведена таблиця дозволяє обчислювати підсумкові величини для даних, у яких збігаються значення у двох зазначених стовпцях. Зведені таблиці є динамічними об'єктами, що дозволяють виводити інформацію з різним ступенем деталізації.

Для створення зведеної таблиці активізуємо будь-яку клітину усередині списку й виконаємо команду Данные/Сводная таблица. Починає працювати програма, що називається Майстер зведених таблиць. Ця програма допоможе вам задати всі необхідні параметри для створення зведеної таблиці.

Створимо зведену таблицю для нашого прикладу за допомогою майстра.

У першому діалоговому вікні Майстра зведених таблиць установимо перемикач у положення, що показує, звідки беруться дані для зведеної таблиці (зі списку). У поле Діапазон другого діалогового вікна Майстри зведених таблиць укажіть, на основі якого діапазону будується зведена таблиця. У третім діалоговому вікні Майстра зведених таблиць потрібно вказати місце розташування зведеної таблиці, установити необхідні параметри (кнопка Параметры…), а також можна викликати діалогове вікно установки макета зведеної таблиці. У четвертому діалоговому вікні Майстра зведених таблиць необхідно вказати структуру зведеної таблиці (див. рис. 3.7).

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

¨ Строка -для використання поля, розташованого в цій області, як заголовки рядків.

¨ СТОЛБЕЦ – для використання поля, розташованого в цій області, як заголовки стовпців.

¨ ДАННЫЕ – для підсумовування значень поля, розташованого в цій області, в осередках зведеної таблиці.

¨ СТРАНИЦА – для забезпечення можливості виведення даних зведеної таблиці, що мають відношення тільки до поля, що розташоване в цій області.

У нашому прикладі кнопку № бригады треба перетягнути в область СТОЛБЕЦ, кнопку Сорт в область СТРОКА, а в область ДАННЫЕ треба перетягнути кнопку Количество.

Подвійний щиглик на кнопці, що переміщена до області ДАННЫЕ, відкриває діалогове вікно Вычисление поля сводной таблицы, що дозволяє вибрати правило, за яким підводять підсумки у зведеній таблиці.

Вкажіть структуру зведеної таблиці як на рисунку 3.7 та перевірте, що одержали зведену таблицю як на рисунку 3.6.

Можна змінити зведену таблицю, що наведена на рисунку 3.6, у такий спосіб: поміняти місцями рядки й стовпці. Зробіть це самостійно.

Рисунок 3.6 – Зведена таблиця, побудована по установках рис. 3.7

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

Рисунок 3.7 - Четверте діалогове вікно Майстра зведених таблиць

Для внесення змін у зведену таблицю, або для побудови нової зведеної таблиці на основі існуючої зручно скористатися макетом зведеної таблиці. Для виклику макета виділіть будь-яку клітину усередині зведеної таблиці й викличте контекстне меню (правою кнопкою миші). Виберіть пункт Мастер. З'явиться третє діалогове вікно майстра зведених таблиць. Клацніть на кнопці Макет…

Одне з полів можна розташовувати в області Страница. Нижче наведений макет зведеної таблиці з використанням поля Страница.

Рисунок 3.8 – Макет зведеної таблиці з полем Сторінка

Зведена таблиця, побудована на основі цього макета, наведена на рис. 3.9. Зверніть увагу на те, що в клітини B1 з'явилося слово (ВСЕ). Це означає, що підсумки обчислені на підставі даних про всі бригади.

Щоб переглянути підсумки по кожній окремій бригаді, потрібно розгорнути список, що розкривається, що перебуває в клітини B1, і вибрати бригаду, що цікавить вас.

Рисунок 3.9 – Зведена таблиця з використанням поля Сторінка

Редагування даних у зведеній таблиці. Excel не дозволяє редагувати дані безпосередньо у зведених таблицях. Для цього треба змінити дані у вхідному списку й перерахувати зведену таблицю (знак! на панелі зведених таблиць).

У зведену таблицю можна вставляти поля, що обчислюють. Наприклад, створимо нове поле з назвою Налог, який можна обчислити за формулою Начислено (грн.)*0,07 (податок 7%). Для цього на панелі інструментів Сводные таблицы обираємокоманду Сводная таблицаÞ Ф ормулыÞВычисляемое поле (рис. 3.10).

Рисунок 3.10 - Панель інструментів „Зведені таблиці”

Рисунок 3.11 - Створення нового поля Налог

З'явиться діалогове вікно Вставка вычисляемого поля. Заповніть його як показано на рис. 3.11.

Щоб на екрані з'явилася панель інструментів С водные таблицы, виберіть пункт меню Вид/Панели инструментов і відзначте рядок С водные таблицы. Зведена таблиця з полем, що обчислює, наведена на рисунку 3.12.