У цій частині посібника описано, як створити зведену таблицю в Excel. Ця стаття була написана для Excel 2007 (а також пізніших версій). Інструкції для попередніх версій Excel можна знайти в окремій статті: Як створити зведену таблицю в Excel 2003?
Як приклад розглянемо наступну таблицю, яка містить дані про продажі компанії за перший квартал 2016 року:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Дата | Посилання на рахунок | сума | Торговий представник. | область |
| 2 | 01/01/2016 | 2016 - 0001 | 819 XNUMX$ | Барнс | На північ |
| 3 | 01/01/2016 | 2016 - 0002 | 456 XNUMX$ | коричневий | Південь |
| 4 | 01/01/2016 | 2016 - 0003 | 538 XNUMX$ | Джонс | Південь |
| 5 | 01/01/2016 | 2016 - 0004 | 1,009 XNUMX$ | Барнс | На північ |
| 6 | 01/02/2016 | 2016 - 0005 | 486 XNUMX$ | Джонс | Південь |
| 7 | 01/02/2016 | 2016 - 0006 | 948 XNUMX$ | коваль | На північ |
| 8 | 01/02/2016 | 2016 - 0007 | 740 XNUMX$ | Барнс | На північ |
| 9 | 01/03/2016 | 2016 - 0008 | 543 XNUMX$ | коваль | На північ |
| 10 | 01/03/2016 | 2016 - 0009 | 820 XNUMX$ | коричневий | Південь |
| 11 | ... | ... | ... | ... | ... |
Для початку давайте створимо дуже просту зведену таблицю, яка буде показувати загальні продажі кожного з продавців відповідно до таблиці вище. Для цього виконайте такі дії:
- Виберіть будь-яку клітинку з діапазону даних або весь діапазон для використання у зведеній таблиці.УВАГА: Якщо вибрати одну клітинку з діапазону даних, Excel автоматично виявить і вибере весь діапазон даних для зведеної таблиці. Щоб Excel правильно вибрав діапазон, мають бути виконані такі умови:
- Кожен стовпець у діапазоні даних повинен мати власне унікальне ім’я;
- Дані не повинні містити порожніх рядків.
- Натискання кнопки зведена таблиця (Зведена таблиця) у розділі таблиці Вкладка (Таблиці). Insert (Вставити) Стрічки меню Excel.
- На екрані з'явиться діалогове вікно. Створіть зведену таблицю (Створіть зведену таблицю), як показано на малюнку нижче.
Переконайтеся, що вибраний діапазон відповідає діапазону клітинок, який слід використовувати для створення зведеної таблиці. Тут також можна вказати, куди слід вставити створену зведену таблицю. Ви можете вибрати наявний аркуш, щоб вставити на нього зведену таблицю, або опцію – На новий аркуш (Новий аркуш). натисніть OK. - З’явиться порожня зведена таблиця, а також панель Поля зведеної таблиці (Список полів зведеної таблиці) з кількома полями даних. Зауважте, що це заголовки з оригінальної таблиці даних.
- У панелях Поля зведеної таблиці (Список полів зведеної таблиці):
- перетягнути Торговий представник. до області ряди (Мітки рядків);
- перетягнути сума в Значення (Цінності);
- Ми перевіряємо: in Значення (Значення) має бути значенням Поле Сума Сума (Сума суми), а не Сума за полем Сума (Розрахунок суми).
У цьому прикладі колонка сума містить числові значення, тому площа Σ Значення (Σ Значення) буде вибрано за замовчуванням Поле Сума Сума (Сума суми). Якщо в стовпчик сума міститиме нечислові або порожні значення, тоді можна вибрати зведену таблицю за умовчанням Сума за полем Сума (Розрахунок суми). Якщо це станеться, ви можете змінити кількість на суму наступним чином:
- У Σ Значення (Σ Значення) натисніть Сума за полем Сума (Кількість суми) і виберіть опцію Параметри поля значення (Параметри поля значень);
- На вкладці Додатково операція (Підсумувати значення за) виберіть операцію Сума (Сума);
- Натисніть тут OK.
Переконайтеся, що вибраний діапазон відповідає діапазону клітинок, який слід використовувати для створення зведеної таблиці. Тут також можна вказати, куди слід вставити створену зведену таблицю. Ви можете вибрати наявний аркуш, щоб вставити на нього зведену таблицю, або опцію – На новий аркуш (Новий аркуш). натисніть OK.
Зведену таблицю буде заповнено загальними показниками продажів для кожного продавця, як показано на зображенні вище.
Якщо ви хочете відобразити обсяги продажів у грошових одиницях, необхідно відформатувати комірки, які містять ці значення. Найпростіший спосіб зробити це — виділити комірки, формат яких потрібно налаштувати, і вибрати формат Грошові розділ (Валюта). Номер Вкладка (Номер). Головна (Домашня сторінка) Стрічки меню Excel (як показано нижче).
В результаті зведена таблиця матиме такий вигляд:
- зведена таблиця перед налаштуванням числового формату
- зведену таблицю після встановлення формату валюти
Зверніть увагу, що формат валюти за замовчуванням залежить від налаштувань системи.
Рекомендовані зведені таблиці в останніх версіях Excel
В останніх версіях Excel (Excel 2013 або новішої) на Insert Присутня кнопка (Вставити). Рекомендовані зведені таблиці (Рекомендовані зведені таблиці). На основі вибраних вихідних даних цей інструмент пропонує можливі формати зведеної таблиці. Приклади можна переглянути на сайті Microsoft Office.