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

Зміст

• Створення зведеної таблиці в Excel
  • Варіант 1: звичайна зведена таблиця
  • Варіант 2: майстер зведених таблиць
  • Налаштування зведеної таблиці
• Питання та відповіді

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

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

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

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

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

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

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

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

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

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

7. Після цього на новому аркуші відкриється форма створення зведеної таблиці.

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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