Зміст
![Создание сводных таблиц в Excel](https://cdn.daad.org.ua/images/7973/53e72fc3e764f5a5722104d98181af92.png)
Зведені таблиці Excel надають можливість користувачам в одному місці групувати значні обсяги інформації, що міститься в громіздких таблицях, а також складати комплексні звіти. Їх значення оновлюються автоматично при зміні значення будь-якої пов'язаної з ними таблиці. Давайте з'ясуємо, як створити такий об'єкт у Microsoft Excel.
Створення зведеної таблиці в Excel
Оскільки в залежності від результату, який хоче отримати користувач, зведена таблиця може бути як простий, так і складно складеної, ми розглянемо два способи її створення: вручну і за допомогою вбудованого в програму інструменту. Додатково розповімо, як налаштовуються такі об'єкти.
Варіант 1: звичайна зведена таблиця
Ми будемо розглядати процес створення на прикладі Microsoft Excel 2010, проте алгоритм застосуємо і для інших сучасних версій цього додатка.
- За основу візьмемо таблицю виплат заробітної плати працівникам підприємства. У ній вказані імена працівників, стать, Категорія, дата і сума виплати. Тобто кожному епізоду виплати окремому працівнику відповідає окремий рядок. Нам належить згрупувати хаотично розташовані дані в цій таблиці в одну зведену таблицю, при цьому відомості будуть братися тільки за третій квартал 2016 року. Подивимося, як це зробити на конкретному прикладі.
- Перш за все перетворимо вихідну таблицю в динамічну. Це потрібно для того, щоб при додаванні рядків та інших даних вони автоматично підтягувалися в зведену таблицю. Наводимо курсор на будь-яку клітинку, потім в розташованому на стрічці блоці «стилі» натискаємо на кнопку " форматувати як таблицю» і вибираємо будь-який вподобаний стиль таблиці.
- Відкривається діалогове вікно, яке нам пропонує вказати координати розташування таблиці. Втім, за замовчуванням координати, які пропонує програма, і так охоплюють всю таблицю. Так що нам залишається тільки погодитися і натиснути на «OK» . Але користувачі повинні знати, що при бажанні вони тут можуть змінити ці параметри.
- Таблиця перетворюється в динамічну і автоматично розтягується. Вона також отримує ім'я, яке при бажанні користувач може змінити на будь-який зручний йому. Переглянути або змінити ім'я можна на вкладці»Конструктор" .
- Щоб безпосередньо почати створення, вибираємо вкладку " Вставка» . Тут тиснемо на першу кнопку в стрічці, яка так і називається " Зведена таблиця» . Відкриється меню, де слід вибрати, що ми збираємося створювати: таблицю або діаграму. В кінці натискаємо " Зведена таблиця» .
- У новому вікні нам знову треба вибрати діапазон або назва таблиці. Як бачимо, програма вже сама підтягнула ім'я нашої таблиці, так що тут нічого більше робити не потрібно. У нижній частині діалогового вікна можна вибрати місце, де буде створюватися зведена таблиця: на новому аркуші (за замовчуванням) або на цьому ж. Звичайно, в більшості випадків набагато зручніше тримати її на окремому аркуші.
- Після цього на новому аркуші відкриється форма створення зведеної таблиці.
- У правій частині вікна розташований список полів, а нижче чотири області: назви рядків, назви стовпців, значення, фільтр звіту. Просто перетягуємо мишкою необхідні нам поля таблиці у відповідні потребам області. Тут не існує будь-якого чіткого встановленого правила, які поля слід переміщати, адже все залежить від таблиці-першоджерела і від конкретних завдань, які можуть змінюватися.
- У конкретному випадку ми перемістили поля " Пол» і »Дата" в область " фільтр звіту» , " Категорія персоналу» & 8212; в " назви стовпців» , «Ім'я» &8212; в " Назва рядків» , " сума заробітної плати» &8212; в " Значення» . Слід зазначити, що всі арифметичні розрахунки даних, підтягнутих з іншої таблиці, можливі тільки в останній області. Під час того, як ми проробляли такі маніпуляції з перенесенням полів в області, відповідно змінювалася і сама таблиця в лівій частині вікна.
- Вийшла ось така зведена таблиця. Над нею відображаються фільтри по підлозі і датою.
![Форматирование как таблица в Microsoft Excel](https://cdn.daad.org.ua/images/7973/586b5788c123c5e5437c34f9bcc92711.png)
![Указание расположения таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/51db4b5d39f1a268c3e19b522880ea6b.png)
![Имя таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/78194c4a69afa2cd8ab0d6b5c44f8ac8.png)
![Переход к созданию сводной таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/dd0f0602a8af4261861c58c29b78fe03.png)
![Диалоговое окно в Microsoft Excel](https://cdn.daad.org.ua/images/7973/dea5d17a196926263d249fbece75a020.png)
![Форма для создания сводной таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/9c90d6e887d1b5480c01b6ea9b3cb4d9.png)
![Поля и области сводной таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/d968ff4e4d0d6d779556af84da390a5f.png)
![Перенос полей в области в Microsoft Excel](https://cdn.daad.org.ua/images/7973/df8ecc68c557886ade55bce595fc1a84.png)
![Сводная таблица в программе Microsoft Excel](https://cdn.daad.org.ua/images/7973/7c66dde37df0a7df9319828ba0a4819a.png)
Варіант 2: майстер зведених таблиць
Створити зведену таблицю можна, застосувавши інструмент & 171; майстер зведених таблиць & 187; , але для цього відразу потрібно вивести його на &171;панель швидкого доступу&187; .
- Переходимо в пункт меню " Файл» і натискаємо на »Параметри" .
- Заходимо в розділ " панель швидкого доступу» і вибираємо команди з команд на стрічці. У списку елементів шукаємо " майстер зведених таблиць і діаграм» . Виділяємо його, тиснемо на кнопку »додати" , а потім «OK» .
- В результаті наших дій на &171;панелі швидкого доступу&187; з'явився новий значок. Кількома по ньому.
- Після цього відкривається & 171; майстер зведених таблиць & 187; . Є чотири варіанти джерела даних, звідки буде формуватися зведена таблиця, з яких вказуємо відповідний. Внизу слід вибрати, що ми збираємося створювати: зведену таблицю або діаграму. Здійснюємо вибір і йдемо »далі" .
- З'являється вікно з діапазоном таблиці з даними, який при бажанні можна змінити. Нам цього робити не треба, тому просто переходимо»далі" .
- Потім & 171; майстер зведених таблиць & 187; пропонує вибрати місце, де буде розміщуватися новий об'єкт: на цьому ж аркуші або на новому. Робимо вибір і підтверджуємо його кнопкою »Готово" .
- Відкриється новий лист в точності з такою ж формою, яка була при звичайному способі створення зведеної таблиці.
- Всі подальші дії виконуються за тим же алгоритмом, який був описаний вище (див. Варіант 1 ).
![Добавление мастера сводных таблиц в Microsoft Excel](https://cdn.daad.org.ua/images/7973/fdf3e0eba8809d35f97cd95924b50c2c.png)
![Переход в панель быстрого доступа в Microsoft Excel](https://cdn.daad.org.ua/images/7973/733dd881a4995713531a6522f65a101d.png)
![Выбор источника сводной таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/0266d5d1e8e44fdd01c35b2d1b22ca65.png)
![Выбор диапазона данных в Microsoft Excel](https://cdn.daad.org.ua/images/7973/bdbb1e37ce5411d508edb115f17f31bc.png)
![Выбор места размещения сводной таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/d887aea603930790773a329b5637824f.png)
![Форма для создания сводной таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/9c90d6e887d1b5480c01b6ea9b3cb4d9.png)
Налаштування зведеної таблиці
Як ми пам'ятаємо з умов поставленого завдання, в таблиці повинні залишитися дані тільки за третій квартал. Поки ж відображаються відомості за весь період. Покажемо приклад, як можна зробити її настройку.
- Для приведення таблиці до потрібного виду натискаємо на кнопку близько фільтра»Дата" . У ньому встановлюємо галочку навпроти напису " виділити кілька елементів» . Далі знімаємо галочки з усіх дат, які не вписуються в період третього кварталу. У нашому випадку це лише одна дата. Підтверджуємо дію.
- Таким же чином ми можемо скористатися фільтром за статтю і вибрати для звіту, наприклад, тільки одних чоловіків.
- Зведена таблиця набула такого вигляду.
- Для демонстрації того, що управляти інформацією в таблиці можна як завгодно, знову відкриваємо форму списку полів. Переходимо на вкладку»Параметри" , і клацаємо на " список полів» . Переміщаємо поле »Дата" з області " фільтр звіту» в " Назва рядків» , а між полями " Категорія персоналу» і " Пол» виробляємо обмін областями. Всі операції виконуємо за допомогою простого перетягування елементів.
- Тепер таблиця виглядає зовсім інакше. Стовпці діляться по полам, в рядках з'явилася розбивка по місяцях, а фільтрацію тепер можна здійснювати по категорії персоналу.
- Якщо ж в списку полів назву рядків перемістити і поставити вище дату, ніж ім'я, тоді саме дати виплат будуть поділятися на імена співробітників.
- Можна також відобразити числові значення таблиці у вигляді гістограми. Для цього виділяємо осередок з числовим значенням, заходимо на вкладку»Головна" , тиснемо " Умовне форматування» , вибираємо пункт «гістограми» і вказуємо сподобався вид.
- Гістограма з'являється лише в одній клітинці. Щоб застосувати правило гістограми для всіх осередків таблиці, натискаємо на кнопку, яка з'явилася поруч з гістограмою, і у вікні переводимо перемикач в позицію " до всіх клітин» .
- В результаті наша зведена таблиця стала виглядати більш презентабельно.
![Изменения диапазона периода в Microsoft Excel](https://cdn.daad.org.ua/images/7973/e1fe4d3454786642ff98ce28b9329de3.png)
![Фильтр по полу в Microsoft Excel](https://cdn.daad.org.ua/images/7973/bcfef3a9f944bd6e056ab6d415a91065.png)
![Изменение сводной таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/2380f2206c5ab2eb74162733f1528ee6.png)
![Обмен областями в Microsoft Excel](https://cdn.daad.org.ua/images/7973/f9466d1633acec02d45cb7efede10abf.png)
![Изменение вида сводной таблицы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/9e7ca2848e4df10fe64af6e4269b5b8e.png)
![Перемещение даты и имени в Microsoft Excel](https://cdn.daad.org.ua/images/7973/8a0803b9e03c7a8d3fca7c8a7d07f99e.png)
![Выбор гистограммы в Microsoft Excel](https://cdn.daad.org.ua/images/7973/12f897de2ea51441b0a750a360de1287.png)
![Применение гистограммы ко всем ячейкам в Microsoft Excel](https://cdn.daad.org.ua/images/7973/eb76a91efa57e34f370d760dc3c5d8e8.png)
![Сводная таблица в Microsoft Excel готова](https://cdn.daad.org.ua/images/7973/c165d7a90618c60c6779a696cd209b47.png)
Другий спосіб створення надає більше додаткових можливостей, але в більшості випадків функціональності першого варіанту цілком достатньо для виконання поставлених завдань. Зведені таблиці можуть формувати дані в звіти по практично будь-яким критеріям, які вкаже користувач в Налаштуваннях.