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

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

Створення зведеної таблиці в Excel

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

Варіант 1: Звичайна зведена таблиця

Ми будемо розглядати процес створення на прикладі Microsoft Excel 2010, однак алгоритм застосуємо і для інших сучасних версій цього додатка.

1. За основу візьмемо таблицю виплат заробітної плати працівникам підприємства. У ній вказані імена працівників, підлогу, категорія, дата і сума виплати. Тобто кожному епізоду виплати окремому працівнику відповідає окрема строчка. Нам належить згрупувати хаотично розташовані дані в цій таблиці в одну зведену таблицю, при цьому відомості будуть братися тільки за третій квартал 2016 року. Подивимося, як це зробити на конкретному прикладі.
2. Перш за все перетворимо вихідну таблицю в динамічну. Це потрібно для того, щоб при додаванні рядків і інших даних вони автоматично підтягувалися в зведену таблицю. Наводимо курсор на будь-яку клітинку, потім в розташованому на стрічці блоці «Стилі» натискаємо на кнопку «Форматувати як таблицю» і вибираємо будь-який вподобаний стиль таблиці.



3. Відкривається діалогове вікно, яке нам пропонує вказати координати розташування таблиці. Втім, за замовчуванням координати, які пропонує програма, і так охоплюють всю таблицю. Так що нам залишається тільки погодитися і натиснути на «OK». Але користувачі повинні знати, що при бажанні вони тут можуть змінити ці параметри.



4. Таблиця перетворюється в динамічну і автоматично розтягується. Вона також отримує ім'я, яке при бажанні користувач може змінити на будь-який зручний йому. Переглянути або змінити ім'я можна на вкладці «Конструктор».



5. Щоб безпосередньо почати створення, вибираємо вкладку «Вставка». Тут тиснемо на першу кнопку в стрічці, яка так і називається «Зведена таблиця». Відкриється меню, де слід вибрати, що ми збираємося створювати: таблицю або діаграму. В кінці натискаємо «Зведена таблиця».



6. У новому вікні нам знову треба вибрати діапазон або назва таблиці. Як бачимо, програма вже сама підтягла ім'я нашої таблиці, так що тут нічого більше робити не потрібно. У нижній частині діалогового вікна можна вибрати місце, де буде створюватися зведена таблиця: на новому аркуші (за замовчуванням) або на цьому ж. Звичайно, в більшості випадків набагато зручніше тримати її на окремому аркуші.



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



9. У конкретному випадку ми перемістили поля «Пол» і «Дата» в область «Фільтр звіту», «Категорія персоналу» - в «Назви стовпців», «Ім'я» - в «Назва рядків», «Сума заробітної плати» - в «Значення ». Слід зазначити, що всі арифметичні розрахунки даних, підтягнутих з іншої таблиці, можливі тільки в останній області. Під час того, як ми робили такі маніпуляції з перенесенням полів в області, відповідно змінювалася і сама таблиця в лівій частині вікна.



10. Вийшла ось така зведена таблиця. Над нею відображаються фільтри по підлозі і датою.

Варіант 2: Майстер зведених таблиць

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

1. Переходимо до пункту меню «Файл» і натискаємо на «Параметри».



2. Заходимо в розділ «Панель швидкого доступу» і вибираємо команди з команд на стрічці. У списку елементів шукаємо «Майстер зведених таблиць і діаграм». Виділяємо його, тиснемо на кнопку «Додати», а потім «OK».



3. В результаті наших дій на «Панелі швидкого доступу» з'явився новий значок. Кількома по ньому.



4. Після цього відкривається «Майстер зведених таблиць». Є чотири варіанти джерела даних, звідки буде формуватися зведена таблиця, з яких вказуємо відповідний. Внизу слід вибрати, що ми збираємося створювати: зведену таблицю або діаграму. Здійснюємо вибір і йдемо «Далі».



5. З'являється вікно з діапазоном таблиці з даними, що при бажанні можна змінити. Нам цього робити не треба, тому просто переходимо «Далі».



6. Потім «Майстер зведених таблиць» пропонує вибрати місце, де буде розміщуватися новий об'єкт: на цьому ж аркуші або на новому. Робимо вибір і підтверджуємо його кнопкою «Готово».



7. Відкриється новий лист в точності з такою ж формою, яка була при звичайному способі створення зведеної таблиці.
8. Всі подальші дії виконуються за тим же алгоритмом, який був описаний вище (див. Варіант 1).

Налаштування зведеної таблиці

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

1. Для приведення таблиці до потрібного вигляду натискаємо на кнопку близько фільтра «Дата». У ньому встановлюємо галочку навпроти напису «Виділити кілька елементів». Далі знімаємо галочки з усіх дат, які не вписуються в період третього кварталу. У нашому випадку це всього лише одна дата. Підтверджуємо дію.



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



3. Зведена таблиця набула такого вигляду.



4. Для демонстрації того, що управляти інформацією в таблиці можна як завгодно, знову відкриваємо форму списку полів. Переходимо на вкладку «Параметри», і клацніть на «Список полів». Переміщаємо поле «Дата» з області «Фільтр звіту» в «Назва рядків», а між полями «Категорія персоналу» і «Пол» проводимо обмін областями. Всі операції виконуємо за допомогою простого перетягування елементів.



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



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



7. Ви також можете відобразити числові значення таблиці у вигляді гістограми. Для цього виділяємо клітинку з числовим значенням, заходимо на вкладку «Головна», тиснемо «Умовне форматування», вибираємо пункт «Гістограми» і вказуємо сподобався вигляд.



8. Гістограма з'являється тільки в одній комірці. Щоб застосувати правило гістограми для всіх елементів таблиці, натискаємо на кнопку, яка з'явилася поруч з гістограмою, і у вікні, переводимо перемикач в позицію «До всіх осередків».



9. У підсумку наша зведена таблиця стала виглядати більш презентабельно.

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