Перетворення осередків зведеної таблиці на формули листа

Зведена таблиця має кілька макетів, які надають зумовлену структуру звіту, але ці макети не можна налаштувати. Якщо вам потрібно більше гнучкості при проектуванні макету звіту зведеної таблиці, можна перетворити комірки на формули аркуша, а потім змінити макет цих осередків, використовуючи всі можливості, доступні на аркуші. Можна перетворити комірки на формули, що використовують функції Куба, або використовувати функцію GETPIVOTDATA. Перетворення осередків у формули значно спрощує процес створення, оновлення та обслуговування цих зведених таблиць, що настроюються.

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

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

Відомості про поширені сценарії перетворення зведених таблиць у формули листа

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

Перевпорядкування та видалення осередків

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

Вставка рядків та стовпців

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

Використання кількох джерел даних

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

Використання посилань на комірки для зміни вхідних даних користувача

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

Create неуніверсальний макет рядка або стовпця (також званий асиметричними звітами)

Припустимо, що вам потрібно створити звіт, що містить стовпець 2008 з ім'ям "Фактичні продажі", стовпець 2009 з ім'ям "Прогнозовані продажі", але інші стовпці не потрібні.Ви можете створити звіт, який містить лише ці стовпці, на відміну від зведеної таблиці, для якої потрібна симетрична звітність.

Create власні формули куба та висловлювання багатовимірних виразів

Припустимо, що ви хочете створити звіт про продаж певного продукту трьома конкретними продавцями за липень. формул за допомогою автозаповнення формул. Додаткові відомості див. у статті Використання автозаповнення формул.

Примітка: Перетворити зведену таблицю OLAP можна лише за допомогою цієї процедури.

Щоб зберегти зведену таблицю для використання в майбутньому, рекомендується створити копію книги перед перетворенням зведеної таблиці, клацнувши Файл >Зберегти якДодаткові відомості див. у розділі Збереження файлу.
Підготуйте зведену таблицю, щоб можна було звести до мінімуму перевпорядкування осередків після перетворення, виконавши такі дії.
- Змініть макет, який найбільше нагадує потрібний.
- Взаємодія зі звітом, наприклад, фільтруванням, сортуванням та зміною звіту, щоб отримати потрібні результати.
Натисніть на зведену таблицю.
На вкладці Параметри у групі Сервіс виберіть інструменти OLAP, а потім - Перетворити на формулиЯкщо фільтрів звітів немає, операція перетворення завершується. Перетворити на формули .
Вирішіть, як перетворити зведену таблицю: Перетворення всієї зведеної таблиці
- Встановіть прапорець Перетворити фільтри звітів перевірка.При цьому всі осередки перетворюються на формули листа і видаляються всі зведені таблиці. Перетворіть лише мітки рядків зведеної таблиці, мітки стовпців та області значень, але збережіть фільтри звітів
- Переконайтеся, що прапорець Перетворити фільтри звітів перевірку встановлено. (Це значення за замовчуванням.) При цьому всі комірки мітки рядків, мітки стовпця та області значень перетворюються на формули аркуша і зберігають вихідну зведену таблицю, але тільки з фільтрами звіту, щоб можна було продовжувати фільтрацію за допомогою фільтрів звіту.

Примітка: Якщо формат зведеної таблиці має версію 2000–2003 або раніше, можна перетворити тільки всю зведену таблицю.

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

Функцію GETPIVOTDATA у формулі можна використовувати для перетворення осередків зведеної таблиці у формули листа, якщо ви хочете працювати з джерелами даних, що не є джерелами даних OLAP, якщо ви не оновлюєтеся до нового формату зведеної таблиці версії 2007 відразу або якщо ви хочете уникнути складності використання функцій Куб.

Створити GETPIVOTDATA

Зведена таблиця

Параметри

Примітка: Команда Generate GETPIVOTDATA задає або очищає параметр Використовувати функції GETPIVOTTABLE для посилань на зведену таблицю у категорії Формули розділу Робота з формулами у діалоговому вікні Параметри Excel .

Примітка: Якщо видалити зі звіту будь-яку осередку, на яку посилається формула GETPIVOTDATA, формула поверне #REF!.

Як зробити зведену таблицю в Excel – покрокова інструкція

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

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

Що таке зведена таблиця?

Це інструмент для вивчення та узагальнення великих обсягів даних, аналізу пов'язаних підсумків та подання звітів. Вони допоможуть вам:

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

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

І щодня сюди додаються нові відомості. Одним із можливих способів підсумовування цього довгого списку чисел за однією або декількома умовами є використання формул, як було продемонстровано в посібниках з функцій СУМІСЛІ та СУМІСЛИМН.

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

Ось подивіться самі.

Цей скріншот демонструє лише кілька з можливих варіантів аналізу продажів. І далі ми розглянемо приклади побудови зведених таблиць в Excel 2016, 2013, 2010 та 2007.

Як створити зведену таблицю в Excel

Багато хто думає, що створення звітів за допомогою зведених таблиць для «чайників» є складним та трудомістким процесом. Але ж це не так! Microsoft багато років удосконалювала цю технологію, і в сучасних версіях Excel вони дуже зручні та неймовірно швидкі.

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

1. Організуйте свої вихідні дані

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

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

Корисні поради:

Додайте оригінальні, значні заголовки в стовпці, вони потім перетворяться на імена полів.
Переконайтеся, що вихідна таблиця не містить порожніх рядків або стовпців та проміжних підсумків.
Щоб спростити роботу, можна присвоїти вихідній таблиці унікальне ім'я, ввівши його в поле «Ім'я» у верхньому правому кутку.

2. Створюємо та розміщуємо макет

Виберіть будь-яку комірку у вихідних даних, а потім перейдіть на вкладку Вставка > Зведена таблиця.

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

Вибір нового робочого листа помістить зведену таблицю на новий аркуш, починаючи з комірки A1.
Вибір існуючого листа розмістить у вказаному вами місці на існуючому аркуші. У полі «Діапазон» виберіть першу комірку (тобто верхню ліву), в яку ви хочете помістити свою зведену таблицю.

Натискання ОК створює порожній макет без цифр у цільовому місці, який виглядатиме приблизно так:

Корисні поради:

У більшості випадків має сенс розміщувати на окремому робочому листі. Це особливо рекомендується для початківців.
Якщо ви берете інформацію з іншої таблиці або робочої книги, увімкніть їхні імена за допомогою наступного синтаксису: [workbook_name]sheet_name!Range. Наприклад, [Книга1.xlsx] Аркуш1!$A$1:$E$50.Звичайно, ви можете не писати це все руками, а просто вибрати діапазон осередків в іншій книзі за допомогою миші.
Можливо, було б корисно збудувати таблицю і діаграму одночасно. Для цього в Excel 2016 та 2013 перейдіть на вкладку «Вставка», клацніть стрілку під кнопкою «Зведена діаграма», а потім натисніть «Діаграма та таблиця». У версіях 2010 та 2007 клацніть стрілку під зведеною таблицею, а потім - Зведена діаграма.

Організація макету.

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

Розділ «Поле» містить назви показників, які можна додати. Вони відповідають іменам стовпців вихідних даних.
Розділ «Макет» містить область «Фільтри», «Зтовбці», «Рядки» і «Значення». Тут ви можете розташувати в потрібному порядку поля.

Зміни, які ви вносите в цих розділах, негайно застосовуються у таблиці.

3. Як додати поле до зведеної таблиці

Щоб мати можливість додати поле в потрібну область, встановіть прапорець поруч із його ім'ям.

За промовчанням Microsoft Excel додає поля до розділу «Макетнаступним чином:

Нечислові додаються до області Рядки;
Числові додаються до області значень;
Дата і час додаються до Стовбці.

4. Як видалити поле зі зведеної таблиці?

Щоб видалити будь-яке поле, ви можете виконати таке:

Зніміть прапорець навпроти нього, який ви встановили раніше.
Клацніть правою кнопкою миші поле та оберіть «Видалити……».

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

5. Як упорядкувати поля у зведеній таблиці?

Ви можете змінити розташування показників трьома способами:

Перетягніть поле між 4 областями розділу за допомогою миші. В якості альтернативи клацніть та утримуйте його ім'я в розділ «Поле», а потім перетягніть у потрібну область у розділі «Макет». Це призведе до видалення з поточної області та її розміщення у новому місці.

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

Натисніть на поле у розділі «Макет», щоб вибрати його. Це відразу відобразить доступні параметри:

Всі внесені зміни застосовуються негайно.

Ну а якщо схаменулися, що зробили щось не так, не забувайте, що є «чарівна» комбінація клавіш CTRL + Z, яка скасовує зроблені вами зміни (якщо ви не зберегли їх, натиснувши відповідну клавішу).

6. Виберіть функцію для значень (необов'язково)

За промовчанням Microsoft Excel використовує функцію «Сума» для числових показників, які ви розміщуєте в області «Значення». Коли ви розміщуєте нечислові (текст, дата або логічне значення) або порожні значення в цю область, до них застосовується функція «Кількість».

Але, звісно, ви можете вибрати інший метод розрахунку. Клацніть правою кнопкою миші поле значення, яке потрібно змінити, виберіть Параметри поля значень і потім – необхідну функцію.

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

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

В Excel 2010 і нижче опція «Підсумовувати значення по» також доступна на стрічці - на вкладці «Параметриу групі «Розрахунки».

7. Використовуємо різні обчислення у полях значення (необов'язково)

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

Це називається «Додаткові обчислення». Доступ до них можна отримати, відкривши вкладку «Параметри . », як це описано трохи вище.

Підказка. Функція «Додаткові обчислення» може виявитися особливо корисною, коли ви додаєте те саме поле більше одного разу і показуєте, як у нашому прикладі, загальний обсяг продажів і обсяг продажів у відсотках від загальної кількості одночасно. Погодьтеся, звичайними формулами робити таку таблицю доведеться довго. А тут – пара хвилин роботи!

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

Робота зі списком показників зведеної таблиці

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

Щоб змінити спосіб відображення вашої робочої області, натисніть кнопку «Інструменти» і виберіть потрібний макет.

Ви також можете змінити розмір панелі по горизонталі, перетягуючи роздільник, який відокремлює панель від аркуша.

Закриття та відкриття панелі редагування зведеної таблиці

Закрити список полів у зведеній таблиці так само просто, як натиснути кнопку «Закрити» (X) у верхньому правому куті панелі. А ось як змусити його з'явитися знову - вже не так очевидно :)

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

Також можна натиснути кнопку «Список полів» на стрічці, яка знаходиться на вкладці меню «Аналіз».

Давайте покращимо зведену таблицю.

Тепер, коли ви знайомі з основами, ви можете перейти до вкладок «Аналіз» і «Конструктор» інструментів в Excel 2016 та 2013 ( вкладки « Параметри» і « Конструктор» у 2010 та 2007). Вони з'являються, як тільки ви клацаєте у будь-якому місці таблиці.

Ви також можете отримати доступ до параметрів та функцій, доступних для певного елемента, клацнувши його правою кнопкою миші (про це ми вже говорили під час створення).

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

Щоб покращити дизайн, перейдіть на вкладку «Конструктор», де ви знайдете безліч зумовлених стилів. Щоб отримати власний стиль, натисніть кнопку «Створити стиль…» внизу галереї «Стилі зведеної таблиці».

Щоб налаштувати макет певного поля, натисніть на ньому, потім натисніть кнопку «Параметри» на вкладці «Аналіз» в Excel 2016 та 2013 (вкладка « Параметри»у 2010 та 2007). Також ви можете клацнути правою кнопкою миші поле та вибрати «Параметри. » у контекстному меню.

На знімку екрана нижче показано новий дизайн та макет.

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

Думаю, стало навіть краще. 😊

Як позбутися заголовків «Мітки рядків» та «Мітки стовпців».

При створенні зведеної таблиці Excel застосовує Стиснену форму за замовчуванням. Цей макет відображає «Мітки рядків» та «Мітки стовпців» як заголовки. Погодьтеся, це не дуже інформативно, особливо для новачків.

Простий спосіб позбавитися цих безглуздих заголовків - перейти зі стисненого макета на структурний або табличний. Для цього відкрийте вкладку «Конструктор», клацніть список «Макет звіту» та виберіть « Показати у формі структури» або « Показати у табличній формі» .

І ось, що ми отримаємо в результаті.

Показані реальні імена, як ви бачите на малюнку справа, що має набагато більше сенсу.

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

Як оновити зведену таблицю

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

Як оновити зведену таблицю вручну

Клацніть на неї в будь-якому місці.
На вкладці «Аналіз» натисніть кнопку «Оновити» або натисніть клавіші ALT + F5 .

Крім того, ви можете за клацанням правої кнопки миші вибрати пункт Оновити з контекстного меню.

Щоб оновити усі зведені таблиці у файлі, натисніть стрілку кнопки «Оновити», а потім - «Оновити все».

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

Після запуску оновлення ви можете переглянути статус або скасувати його, якщо ви передумали. Просто натисніть на стрілку кнопки «Оновити», а потім - «Стан оновлення» або «Скасувати оновлення».

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

Відкрийте вкладку параметрів, як це ми щойно робили.
У діалоговому вікні «Параметри . » перейдіть на вкладку «Дані» та встановіть прапорець «Оновити під час відкриття файлу».

Як перемістити таблицю на нове місце?

Можливо, ви захочете перемістити свій витвір у нову робочу книгу? Перейдіть на вкладку «Аналіз», натисніть кнопку «Дії», а потім - «Перемістити. ». Виберіть новий пункт призначення та натисніть ОК.

Як видалити зведену таблицю?

Якщо вам більше не потрібен певний звіт, ви можете видалити його кількома способами.

Якщо таблиця знаходиться на окремому аркушіпросто видаліть цей лист.
Якщо вона розташована разом з деякими іншими даними на аркуші, виділіть її за допомогою миші і натисніть клавішу Delete.
Клацніть будь-де в зведеній таблиці, яку хочете видалити, перейдіть на вкладку «Аналіз» (див. скріншот вище) => група «Дії», натисніть невелику стрілку під кнопкою «Виділити», виберіть «Вся зведена таблиця», а потім натисніть Видалити.

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

Сподіваємося, що цей самовчитель стане для вас гарною відправною точкою. Далі на нас чекають ще кілька рекомендацій, як працювати зі зведеними таблицями. І дякую за читання!

Як зберегти зведену таблицю у звичайну