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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.2 Завдання для самостійної роботи

Створіть форму для введення й коректування даних у списку й вивчите всі можливості роботи з формою. (Данные/Форма).

1. Організуйте перевірку значень, що вводяться, для декількох полів.

2. Підведіть підсумки по одному з полів таблиці.

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

3.3 Індивідуальні завдання

Варіант 1

1. Підрахувати загальну та максимальну кількість деталей, виготовлених робітниками кожної бригади за допомогою підсумків.

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

Варіант 2

1. Знайти мінімальну та загальну вартість електроенергії, спожи­тої мешканцями кожного будинку за допомогою підсумків.

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

Варіант 3

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

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

Варіант 4

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

2. Створити зведену таблицю, в якій для кожного автопарку ви­вести середню відстань поїздок в кожному класі таксі.

Варіант 5

1. Знайти середню кількість днів відпочинку та вартість проданих путівок для кожної з країн за допомогою підсумків.

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

Варіант 6

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

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

Варіант 7

1. Для кожної країни – виробника підрахувати загальну вартість ліків та максимальну вартість партії за допомогою підсумків.

2. Створити зведену таблицю, в якій для кожної країни – виробника і для кожного з найменувань ліків вивести їх вартість.

Варіант 8

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

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

Варіант 9

1. Визначити за допомогою підсумків загальну вартість замов­леного товару без знижок і середню кількість одиниць товару в одній заявці для кожного магазину.

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

Варіант 10

1. Підрахувати нараховану зарплату з надбавками та загальну кількість робітників різних розрядів за допомогою підсумків.

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

Варіант 11

1. Для кожної АТС підрахувати загальну вартість розмов та кіль­кість абонентів за допомогою підсумків.

2. Створити зведену таблицю, в якій для кожної АТС вивести середню тривалість для кожного типу розмов.

Варіант 12

1. Знайти загальну кількість проданих автомобілів та їх вартість зі знижками для кожної марки машин за допомогою підсумків.

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

Варіант 13

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

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

Варіант 14

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

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

Варіант 15

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

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

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

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

2. Перелічіть основні можливості й вимоги до опції Данные/Итоги.

3. Призначення зведеної таблиці.

4. Як створити зведену таблицю? Що являє собою Майстер зведених таблиць?

5. Що таке структура зведеної таблиці?

6. Поясните призначення всіх областей макета Майстра зведених таблиць (РЯДОК, СТОВПЕЦЬ, ДАНІ, СТОРІНКА).