10 способів розширити гру зі зведеною таблицею Excel

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

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

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

Дізнайтеся, як створити зведену таблицю за допомогою інструмента швидкого аналізу Excel .

Увімкніть та вимкніть панель завдань Поля зведеної таблиці Excel

За замовчуванням, коли ви клацнете усередині зведеної таблиці, Excel відображає панель завдань Поля зведеної таблиці, а потім знову приховує панель завдань Поля зведеної таблиці, коли ви клацнете за межами звіту зведеної таблиці.

На перший погляд у цьому немає нічого поганого. Однак, якщо ви хочете працювати з командами на контекстній вкладці «Інструменти зведеної таблиці» стрічки, у звіті зведеної таблиці потрібно вибрати принаймні одну клітинку. Але вибір будь-якої клітинки зведеної таблиці означає, що у вас також є панель завдань Поля зведеної таблиці, яка займає цінну нерухомість екрана.

На щастя, Excel також дає змогу вручну вимкнути та ввімкнути панель завдань Поля зведеної таблиці, що дає більше місця для відображення звіту зведеної таблиці. Потім ви можете знову ввімкнути панель завдань Поля зведеної таблиці, коли потрібно додати, перемістити або видалити поля.

Щоб увімкнути та вимкнути панель завдань «Поля зведеної таблиці», виконайте такі дії (усі два!):

Клацніть всередині зведеної таблиці.

Виберіть Аналіз→ Показати→ Список полів.

Швидкий спосіб приховати область завдань «Поля зведеної таблиці» — це натиснути кнопку «Закрити» у верхньому правому куті панелі.

Змінити макет області завдань Поля зведеної таблиці Excel

За замовчуванням область завдань «Поля зведеної таблиці» розділена на два розділи: розділ «Поля» містить перелік доступних полів джерела даних і з’являється у верхній частині панелі, а розділ «Області» містить області зведеної таблиці — фільтри, стовпці, рядки та значення — і з’являється в нижній частині панелі. Ви можете налаштувати цей макет відповідно до вашого способу роботи. Ось такі можливості:

• Розділ полів і розділ «Області» зі складом: це макет за замовчуванням.
• Розділ полів і розділ «Області» поруч: Розміщує розділ «Поля» ліворуч, а розділ «Області» — праворуч. Використовуйте цей макет, якщо вихідні дані містять велику кількість полів.
• Лише розділ «Поля»: приховує розділ «Області». Використовуйте цей макет, коли ви додаєте поля до зведеної таблиці, клацнувши правою кнопкою миші назву поля, а потім клацнувши область, куди потрібно додати поле (замість перетягування полів до розділу Області). Приховавши розділ Areas, ви отримаєте більше місця для відображення полів.
• Лише розділ «Області» (2 на 2): приховує розділ «Поля» та впорядковує області у два рядки та два стовпці. Використовуйте цей макет, якщо ви закінчили додавати поля до зведеної таблиці і хочете зосередитися на переміщенні полів між областями та фільтрації полів.
• Лише розділ «Області» (1 на 4): приховує розділ «Поля» та відображає області в одному стовпці. Використовуйте цей макет, якщо вам більше не потрібен розділ «Поля». Крім того, цей макет надає кожній області ширше відображення, що корисно, якщо деякі з ваших полів мають смішно довгі назви.

Нижче наведено кроки, які потрібно виконати, щоб змінити макет панелі завдань Поля зведеної таблиці:

Клацніть будь-яку клітинку всередині зведеної таблиці.

Натисніть Інструменти.

Кнопка «Інструменти» — це та, що має значок шестерні.

Натисніть Інструменти, щоб побачити параметри макета панелі завдань Поля зведеної таблиці.

Excel відображає інструменти панелі завдань «Поля зведеної таблиці».

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

Зверніть увагу, що ви також можете сортувати список полів, хоча у вас відображаються інструменти області завдань «Поля зведеної таблиці» . За замовчуванням — «Сортувати в порядку джерела даних», що означає, що Excel відображає поля в тому ж порядку, що й у джерелі даних. Якщо ви бажаєте сортувати поля в алфавітному порядку, натисніть Сортувати від А до Я.

Відображення подробиць за даними зведеної таблиці в Excel

Основною перевагою використання зведених таблиць є те, що вони дають вам простий метод узагальнення великої кількості даних у стислий звіт для аналізу даних. Коротше кажучи, зведені таблиці показують вам ліс замість дерев. Однак іноді вам потрібно побачити деякі з цих дерев. Наприклад, якщо ви вивчаєте результати маркетингової кампанії, ваша зведена таблиця може показати загальну кількість навушників, проданих у результаті акції 1 Free With 10. Однак що, якщо ви хочете побачити деталі, що лежать в основі цього числа? Якщо ваші вихідні дані містять сотні чи тисячі записів, вам потрібно якимось чином відфільтрувати дані, щоб побачити лише потрібні записи.

Ви продали 792 навушники, але які деталі ховаються за цією цифрою?

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

Щоб детально розглянути деталі, що лежать в основі точки даних зведеної таблиці, скористайтеся одним із наступних методів:

• Клацніть правою кнопкою миші значення даних, для яких потрібно переглянути основні відомості, а потім натисніть Показати деталі.
• Двічі клацніть значення даних.

Excel відображає основні дані на новому аркуші. Наприклад, на цьому зображенні показано деталі за 792 навушниками, які продаються разом із акцією 1 Free з 10, показаною на зображенні вище.

Деталі продажу навушників.

Коли ви намагаєтеся деталізувати основну інформацію про значення даних, Excel може відобразити повідомлення про помилку Не вдається змінити цю частину звіту зведеної таблиці. Ця помилка означає, що функцію, яка зазвичай дає змогу деталізувати, вимкнено. Щоб знову ввімкнути цю функцію, клацніть будь-яку клітинку всередині зведеної таблиці, а потім натисніть Аналіз → Зведена таблиця → Параметри, щоб відкрити діалогове вікно Параметри зведеної таблиці. Перейдіть на вкладку «Дані», установіть прапорець «Увімкнути показ деталей», а потім натисніть «ОК».

Протилежна ситуація виникає, коли ви розповсюджуєте книгу, що містить зведену таблицю, і не бажаєте, щоб інші користувачі деталізувати та захаращувати книгу детальними аркушами. У цьому випадку натисніть «Аналіз» → «Зведена таблиця» → «Параметри», перейдіть на вкладку «Дані», зніміть прапорець «Увімкнути показ подробиць», а потім натисніть «ОК».

Іноді ви можете побачити всі вихідні дані зведеної таблиці. Якщо вихідними даними є діапазон або таблиця в іншому аркуші, ви можете побачити основні дані, відобразивши цей аркуш. Однак, якщо вихідні дані не так легко доступні, Excel дає вам швидкий спосіб переглянути всі основні дані. Клацніть правою кнопкою миші загальну клітинку зведеної таблиці (тобто клітинку в нижньому правому куті зведеної таблиці), а потім натисніть «Показати деталі». (Ви також можете двічі клацнути цю клітинку.) Excel відображає всі основні дані зведеної таблиці на новому аркуші.

Застосуйте стиль зведеної таблиці в Excel

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

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

Стиль являє собою набір варіантів форматування - шрифти, рамки і кольору фону - що Excel визначає для різних областей зведеної таблиці. Наприклад, стиль може використовувати напівжирний білий текст на чорному тлі для міток і загальних підсумків і білий текст на темно-синьому фоні для елементів і даних. Визначення всіх цих форматів вручну може зайняти від півгодини до години. Але за допомогою функції стилю ви вибираєте той, який хочете використовувати для зведеної таблиці в цілому, і Excel автоматично застосовує окремі параметри форматування.

Excel визначає понад 80 стилів, розділених на три категорії: Світлий, Середній і Темний. Категорія Light включає зведений стиль Light 16, форматування за замовчуванням, яке застосовується до створених вами звітів зведеної таблиці; і Немає, що видаляє все форматування зі зведеної таблиці. Ви також можете створювати власні формати стилю.

Нижче наведено кроки, які потрібно виконати, щоб застосувати стиль до зведеної таблиці Excel:

Клацніть будь-яку клітинку в зведеній таблиці, яку потрібно відформатувати.

Перейдіть на вкладку Дизайн.

У групі «Стилі зведеної таблиці» натисніть кнопку «Більше».
З’явиться галерея стилів.

Натисніть стиль, який потрібно застосувати.
Excel застосовує стиль.

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

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

Excel пропонує вам величезну гнучкість, коли ви створюєте власні стилі зведеної таблиці. Ви можете відформатувати 25 окремих елементів зведеної таблиці. Ці елементи включають всю таблицю, мітки та значення полів сторінки, перший стовпець, рядок заголовка, рядок загального підсумку та стовпець загального підсумку. Ви також можете визначити смуги, які є окремими форматами, що застосовуються до чергуються рядків або стовпців. Наприклад, смуга першого ряду застосовує форматування до рядків 1, 3, 5 тощо; Смуга другого ряду застосовує форматування до рядків 2, 4, 6 тощо. Смуги можуть зробити довгий або широкий звіт легшим для читання.

Маючи контроль над такою кількістю елементів, ви можете створити власний стиль відповідно до ваших потреб. Наприклад, вам може знадобитися ваша зведена таблиця, щоб відповідати вашим корпоративним кольорам. Аналогічно, якщо зведена таблиця відображатиметься як частина більшого звіту, вам може знадобитися форматування зведеної таблиці, щоб відповідати темі, використаній у більшому звіті.

Єдиним недоліком створення спеціального стилю зведеної таблиці є те, що це потрібно робити з нуля, оскільки Excel не дозволяє налаштовувати існуючий стиль. Ой, Excel! Отже, якщо вам потрібно визначити форматування для всіх 25 елементів зведеної таблиці, створення спеціального стилю може зайняти багато часу.

Однак якщо ви все ще готові до цього, ось кроки, які потрібно виконати, щоб створити свій власний стиль зведеної таблиці Excel:

Перейдіть на вкладку Дизайн.

У групі «Стилі зведеної таблиці» натисніть «Більше».
З’явиться галерея стилів.

Натисніть Новий стиль зведеної таблиці.
З’явиться діалогове вікно нового стилю зведеної таблиці.

Нове діалогове вікно «Стиль зведеної таблиці».

Введіть назву свого користувацького стилю.

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

Натисніть Формат.

З’явиться діалогове вікно Формат комірок.

Використовуйте параметри на вкладці «Шрифт», щоб відформатувати текст елемента.
Ви можете вибрати шрифт, стиль шрифту (наприклад, напівжирний або курсив) і розмір шрифту. Ви також можете вибрати підкреслення, колір та ефект закреслення.

Використовуйте параметри на вкладці Межа, щоб відформатувати межу елемента.
Ви можете вибрати стиль рамки, колір і розташування (наприклад, лівий край, верхній край або обидва).

Використовуйте параметри на вкладці «Заливка», щоб відформатувати колір фону елемента.
Ви можете вибрати однотонний колір або візерунок. Ви також можете натиснути кнопки «Ефекти заливки», щоб вказати градієнт, який змінюється від одного кольору до іншого.

Натисніть OK.

Excel повертає вас до діалогового вікна «Новий стиль зведеної таблиці».

Повторіть кроки з 5 по 10, щоб відформатувати інші елементи таблиці.

До речі, діалогове вікно «Новий стиль зведеної таблиці» містить розділ «Попередній перегляд», який показує, як виглядатиме стиль, коли його застосувати до зведеної таблиці. Якщо ви особливо пишаєтеся своїм новим стилем, ви можете використовувати його для всіх своїх зведених таблиць. Чому ні? Щоб повідомити Excel використовувати ваш новий стиль як стандартний для будь-якої майбутньої зведеної таблиці, яку ви підробите, установіть прапорець Встановити як стиль зведеної таблиці за замовчуванням для цього документа.

Коли ви нарешті все закінчите, натисніть ОК.
Excel зберігає власний стиль зведеної таблиці.

Як не дивно, але після закриття діалогового вікна «Новий стиль зведеної таблиці» Excel не застосовує новий стиль до поточної зведеної таблиці. Тупий! Щоб самостійно застосувати стиль, виділіть будь-яку клітинку в зведеній таблиці, клацніть «Дизайн», натисніть кнопку «Більше» в групі «Стилі зведеної таблиці», щоб відкрити колекцію стилів, а потім клацніть свій стиль у розділі «Власний», який тепер з’являється у верхній частині галереї.

Якщо вам потрібно внести зміни до свого користувацького стилю, відкрийте галерею стилів, клацніть правою кнопкою миші користувацький стиль, а потім натисніть «Змінити». Використовуйте діалогове вікно «Змінити стиль зведеної таблиці», щоб внести зміни, а потім натисніть «ОК».

Якщо ви виявите, що вам потрібно створити інший користувацький стиль, подібний до існуючого спеціального стилю, не турбуйтеся про створення нового стилю з нуля. Замість цього відкрийте галерею стилів, клацніть правою кнопкою миші наявний користувацький стиль, а потім клацніть Копувати. У діалоговому вікні «Змінити стиль зведеної таблиці» відкоригуйте назву стилю та форматування, а потім натисніть «ОК».

Якщо вам більше не потрібен власний стиль, ви повинні видалити його, щоб зменшити безлад у галереї стилів. Перейдіть на вкладку «Дизайн», відкрийте галерею стилів зведеної таблиці, клацніть правою кнопкою миші користувальницький стиль, який вам більше не потрібен, а потім натисніть «Видалити». Коли Excel попросить вас підтвердити, натисніть OK.

Зберегти форматування зведеної таблиці Excel

Excel має неприємну звичку іноді не зберігати власне форматування, коли ви оновлюєте або перебудовуєте зведену таблицю. Наприклад, якщо ви застосували жирний шрифт до деяких міток, ці мітки можуть повернутися до звичайного тексту після оновлення. Excel має функцію під назвою «Зберегти форматування», яка дозволяє зберегти таке форматування під час оновлення; Ви можете зберегти власне форматування, активувавши його.

Функція збереження форматування завжди активована у зведених таблицях за замовчуванням. Однак інший користувач міг вимкнути цю функцію. Наприклад, ви можете працювати зі зведеною таблицею, створеною іншою особою, і він чи вона вимкнули функцію збереження форматування.

Однак зауважте, що коли ви оновлюєте або перебудовуєте зведену таблицю, Excel повторно застосовує поточне стильове форматування звіту. Якщо ви не вказали стиль, Excel повторно застосовує стиль зведеної таблиці за замовчуванням (названий підсвіткою стилю зведеної 16); якщо ви вказали стиль, Excel повторно застосовує цей стиль.

Нижче наведено кроки, які потрібно виконати, щоб налаштувати зведену таблицю Excel для збереження форматування:

Клацніть будь-яку клітинку в зведеній таблиці, з якою потрібно працювати.

Виберіть Аналіз → Зведена таблиця → Параметри.

З’явиться діалогове вікно «Параметри зведеної таблиці» з відображеною вкладкою «Макет і формат».

Зніміть прапорець «Автоматичне підбір ширини стовпців під час оновлення».
Скасування цього параметра не дозволяє Excel автоматично форматувати такі речі, як ширина стовпців, коли ви повертаєте поля.

Установіть прапорець «Зберегти форматування клітинок під час оновлення».

Click OK.
Excel preserves your custom formatting each time you refresh the PivotTable.

Rename Excel PivotTables

When you create the first PivotTable in a workbook, Excel gives it the uninspiring name PivotTable1. Subsequent PivotTables are named sequentially (and just as uninspiringly): PivotTable2, PivotTable3, and so on. However, Excel also repeats these names when you build new PivotTables based on different data sources. If your workbook contains a number of PivotTables, you can make them easier to distinguish by giving each one a unique and descriptive name. Here’s how:

Click any cell within the PivotTable that you want to work with.

Click Analyze → PivotTable.

Use the PivotTable Name text box to type the new name for the PivotTable.

The maximum length for a PivotTable name is 255 characters.

Click outside the text box.
Excel renames the PivotTable.

Turn off Excel PivotTables grand totals

A default PivotTable that has at least one row field contains an extra row at the bottom of the table. This row is labeled Grand Total and includes the total of the values associated with the row field items. However, the value in the Grand Total row may not actually be a sum. For example, if the summary calculation is Average, the Grand Total row includes the average of the values associated with the row field items.

Similarly, a PivotTable that has at least one column field contains an extra column at the far right of the table. This column is also labeled “Grand Total” and includes the total of the values associated with the column field items. If the PivotTable contains both a row and a column field, the Grand Total row also has the sums for each column item, and the Grand Total column also has the sums for each row item.

Besides taking up space in the PivotTable, these grand totals are often not necessary for data analysis. For example, suppose you want to examine quarterly sales for your salespeople to see which amounts are over a certain value for bonus purposes. Because your only concern is the individual summary values for each employee, the grand totals are useless. In such a case, you can tell Excel not to display the grand totals by following these steps:

Click any cell within the PivotTable that you want to work with.

Click Design → Grand Totals.
Excel displays a menu of options for displaying the grand totals.

Click the option you prefer.
The menu contains four items:

• Off for Rows and Columns: Turns off the grand totals for both the rows and the columns.
• On for Rows and Columns: Turns on the grand totals for both the rows and the columns.
• On for Rows Only: Turns off the grand totals for just the columns.
• On for Columns Only: Turns off the grand totals for just the rows.

  Excel puts the selected grand total option into effect.

The field headers that appear in the report are another often-bothersome PivotTable feature. These headers include Sort & Filter buttons, but if you don’t use those buttons, the field headers just clutter the PivotTable. To turn off the field headers, click inside the PivotTable and then choose Analyze → Show → Field Headers.

Reduce the size of Excel PivotTable workbooks

PivotTables often result in large workbooks because Excel must keep track of a great deal of extra information to keep the PivotTable performance acceptable. For example, to ensure that the recalculation involved in pivoting happens quickly and efficiently, Excel maintains a copy of the source data in a special memory area called the pivot cache.

If you build a PivotTable from data that resides in a different workbook or in an external data source, Excel stores the source data in the pivot cache. This greatly reduces the time Excel takes to refresh and recalculate the PivotTable. The downside is that it can increase both the size of the workbook and the amount of time Excel takes to save the workbook. If your workbook has become too large or it takes too long to save, follow these steps to tell Excel not to save the source data in the pivot cache:

Click any cell in the PivotTable.

Click Analyze → PivotTable → Options.
The PivotTable Options dialog box appears.

Click the Data tab.

Deselect the Save Source Data with File check box.

Click OK.
Excel no longer saves the external source data in the pivot cache.

Use a PivotTable value in Excel Formulas

You might need to use a PivotTable value in a worksheet formula. You normally reference a cell in a formula by using the cell’s address. However, this won’t work with PivotTables because the addresses of the report values change as you pivot, filter, group, and refresh the PivotTable.

To ensure accurate PivotTable references, use Excel’s GETPIVOTDATA function. This function uses the data field, PivotTable location, and one or more (row or column) field/item pairs that specify the exact value you want to use. This way, no matter what the PivotTable layout is, as long as the value remains visible in the report, your formula reference remains accurate.

Here’s the syntax of the GETPIVOTDATA function:

GETPIVOTDATA(data_field, pivot_table, [, field1, item1][, …]

The two required fields are data_field, which is the name of the field you’re using in the PivotTable’s Values area, and pivot_table, which specifies the cell address of the upper-left corner of the PivotTable. The rest of the arguments come in pairs: a field name and an item in that field.

For example, here’s a GETPIVOTDATA formula that returns the PivotTable value where the Product field item is Earbuds and the Promotion field item is 1 Free with 10:

=GETPIVOTDATA("Quantity", $A$3, "Product", "Earbuds", "Promotion", "1 Free with 10")

The GETPIVOTDATA function doing its thing.

GETPIVOTDATA трохи складно, але не хвилюйтеся. Вам майже ніколи не доведеться вибирати цю функцію та всі її аргументи вручну. Натомість Excel зручно обробляє все за вас, коли ви клацнете значення зведеної таблиці, яке хочете використовувати у своїй формулі. Фу!