Програма Microsoft Excel: зведені таблиці
Зведені таблиці Excel надають можливість користувачам в одному місці групувати значні обсяги інформації, що міститься в громіздких таблицях, а також складати комплексні звіти. При цьому, значення зведених таблиць оновлюються автоматично при зміні значення будь-якої пов'язаної з ними таблиці. Давайте з'ясуємо, як створити зведену таблицю в програмі Microsoft Excel.
зміст
Створення зведеної таблиці звичайним способом
Хоча, ми будемо розглядати процес створення зведеної таблиці на прикладі програми Microsoft Excel 2010, але даний алгоритм застосуємо і для інших сучасних версій цього додатка.
За основу візьмемо таблицю виплат заробітної плати працівникам підприємства. У ній вказані імена працівників, підлогу, категорія, дата виплати, і сума виплати. Тобто, кожному епізоду виплати окремому працівнику відповідає окрема строчка таблиці. Нам належить згрупувати хаотично розташовані дані в цій таблиці в одну зведену таблицю. При цьому, дані будуть братися тільки за третій квартал 2016 року. Подивимося, як це зробити на конкретному прикладі.
Перш за все, перетворимо вихідну таблицю в динамічну. Це потрібно для того, щоб в разі додавання рядків і інших даних, вони автоматично підтягувалися в зведену таблицю. Для цього, стаємо курсором на будь-який елемент таблиці. Потім, в розташованому на стрічці блоці «Стилі» натискаємо на кнопку «Форматувати як таблицю». Вибираємо будь-який вподобаний стиль таблиці.
Далі, відкривається діалогове вікно, яке нам пропонує вказати координати розташування таблиці. Втім, за замовчуванням, координати, які пропонує програма і так охоплює всю таблицю. Так що нам залишається тільки погодитися, і натиснути на кнопку «OK». Але, користувачі повинні знати, що при бажанні, вони тут можуть змінити параметри охоплення області таблиці.
Після цього, таблиця перетворюється в динамічну, і авторастягівающуюся. Вона також отримує ім'я, яке при бажанні користувач може змінити на будь-який зручний йому. Переглянути або змінити ім'я таблиці можна у вкладці «Конструктор».
Для того, щоб безпосередньо розпочати створення зведеної таблиці, переходимо у вкладку «Вставка». Перейшовши, тиснемо на найпершу кнопку в стрічці, яка так і називається «Зведена таблиця». Після цього, відкривається меню, в якому слід вибрати, що ми збираємося створювати, таблицю або діаграму. Тиснемо на кнопку «Зведена таблиця».
Відкривається вікно, в якому нам знову потрібно вибрати діапазон, або назву таблиці. Як бачимо, програма вже сама підтягла ім'я нашої таблиці, так що тут нічого більше робити не потрібно. У нижній частині діалогового вікна можна вибрати місце, де буде створювати зведена таблиця: на новому аркуші (за замовчуванням), або ж на цьому ж. Звичайно, в більшості випадків, набагато зручніше використовувати зведену таблицю на окремому аркуші. Але, це вже індивідуальна справа кожного користувача, яке залежить від його переваг, і поставлених завдань. Ми ж просто тиснемо на кнопку «OK».
Після цього, на новому аркуші відкривається форма створення зведеної таблиці.
Як бачимо, в правій частині вікна розташований список полів таблиці, а нижче чотири області:
- Назви рядків;
- Назви стовпців;
- значення;
- Фільтр звіту.
Просто, перетягуємо мишкою потрібні нам поля таблиці до відповідних нашим потребам області. Тут не існує будь-якого чіткого встановленого правила, які поля слід переміщати, адже все залежить від таблиці-першоджерела, і від конкретних завдань, які можуть змінюватися.
Отже, в даному конкретному випадку, ми перемістили поля «Пол» і «Дата» в область «Фільтр звіту», поле «Категорія персоналу» в область «Назви стовпців», поле «Ім'я» в область «Назва рядків», поле «Сума заробітної плати »в область« Значення ». Потрібно відзначити, що всі арифметичні розрахунки даних підтягнутих з іншої таблиці можливі тільки в останній області. Як бачимо, під час того, як ми робили дані маніпуляції з перенесенням полів в області, відповідно змінювалася і сама таблиця в лівій частині вікна.
Вийшла ось така зведена таблиця. Над таблицею відображаються фільтри по підлозі і датою.
Налаштування зведеної таблиці
Але, як ми пам'ятаємо, в таблиці повинні залишитися дані тільки за третій квартал. Поки ж відображаються дані за весь період. Для того, що б привести таблицю до потрібного нам увазі, натискаємо на кнопку близько фільтра «Дата». У віконці, що з'явилося встановлюємо галочку навпроти напису «Виділити кілька елементів». Далі, знімаємо галочки з усіх дат, які не вписуються в період третього кварталу. У нашому випадку, це всього лише одна дата. Тиснемо на кнопку «OK».
Таким же чином, ми можемо скористатися фільтром по підлозі, і вибрати для звіту, наприклад, тільки одних чоловіків.
Після цього, зведена таблиця набула такого вигляду.
Щоб продемонструвати, що управляти даними в таблиці можна як завгодно, знову відкриваємо форму списку полів. Для цього переходимо у вкладку «Параметри», і тиснемо на кнопку «Список полів». Потім, переміщаємо поле «Дата» з області «Фільтр звіту» в область «Назва рядків», а між полями «Категорія персоналу» і «Пол», проводимо обмін областями. Всі операції виконуємо за допомогою простого перетягування елементів.
Тепер, таблиця має зовсім інший вигляд. Стовпці діляться по полам, в рядках з'явилася розбивка по місяцях, а фільтрувати таблицю тепер можна по категорії персоналу.
Якщо ж у списку полів назву рядків перемістити, і поставити вище дату, ніж ім'я, тоді саме дати виплат будуть поділятися на імена співробітників.
Також, можна відобразити числові значення таблиці у вигляді гістограми. Для цього, виділяємо клітинку з числовим значенням в таблиці, переходимо у вкладку «Головна», тиснемо на кнопку «Умовне форматування», переходимо до пункту «Гістограми», і вибираємо сподобався вигляд гістограми.
Як бачимо, гістограма з'являється тільки в одній комірці. Для того, щоб застосувати правило гістограми для всіх елементів таблиці, натискаємо на кнопку, яка з'явилася поруч з гістограмою, і у вікні, переводимо перемикач в позицію «До всіх осередків».
Тепер, наша зведена таблиця набула презентабельний вигляд.
Створення зведеної таблиці за допомогою Майстра зведених таблиць
Створити зведену таблицю можна, застосувавши Майстер зведених таблиць. Але, для цього відразу потрібно вивести цей інструмент на Панель швидкого доступа.Переходім в пункт меню «Файл», і тиснемо на кнопку «Параметри».
У вікні параметрів, переходимо в розділ «Панель швидкого доступу». Вибираємо команди з команд на стрічці. У списку елементів шукаємо «Майстер зведених таблиць і діаграм». Виділяємо його, тиснемо на кнопку «Додати», а потім на кнопку «OK» в правому нижньому кутку вікна.
Як бачимо, після наших дій, на Панелі швидкого доступу з'явився новий значок. Кількома по ньому.
Після цього, відкривається майстер зведених таблиць. Як бачимо, ми маємо чотири варіанти джерела даних, звідки буде формуватися зведена таблиця:
- в списку або в базі даних Microsoft Excel;
- в зовнішньому джерелі даних (інший файл);
- в декількох діапазонах консолідації;
- в інший зведеної таблиці або в зведеної діаграмі.
Внизу слід вибрати, що ми збираємося створювати, зведену таблицю або діаграму. Робимо вибір і тиснемо на кнопку «Далі».
Після цього, з'являється вікно з діапазоном таблиці з даними, що при бажанні можна змінити, але нам цього робити не потрібно. Просто тиснемо на кнопку «Далі».
Потім, Майстер зведених таблиць пропонує вибрати місце, де буде розміщуватися нова таблиця на цьому ж аркуші або на новому. Робимо вибір, і тиснемо на кнопку «Готово».
Після цього, відкривається новий лист в точності з такою ж формою, яка відкривалася при звичайному способі створення зведеної таблиці. Тому, окремо зупинятися на ній немає сенсу.
Всі подальші дії виконуються за тим же алгоритмом, який був описаний вище.
Як бачимо, створити зведену таблицю в програмі Microsoft Excel можна двома способами: звичайним способом через кнопку на стрічці, і за допомогою Майстра зведених таблиць. Другий спосіб надає більше додаткових можливостей, але в більшості випадків, функціоналу першого варіанту цілком достатньо для виконання поставлених завдань. Зведені таблиці можуть формувати дані в звіти по практично будь-яких критеріїв, які вкаже користувач в налаштуваннях.