Зведена таблиця містить кілька макетів, які містять попередньо визначену структуру звіту, але ці макети настроїти не можна. Якщо вам потрібні додаткові гнучкі можливості для створення макета звіту зведеної таблиці, можна перетворити клітинки на формули аркуша, а потім змінити макет цих клітинок, скориставшись усіма функціями, доступними на аркуші. Клітинки можна перетворити на формули, які використовують функції куба, або скористатися функцією GETPIVOTDATA. Перетворення клітинок на формули значно спрощує процес створення, оновлення та підтримки цих настроюваних зведених таблиць.
Якщо перетворити клітинки на формули, ці формули матимуть доступ до тих самих даних, що й зведена таблиця, і їх можна оновити, щоб переглянути оновлені результати. Однак, за винятком фільтрів звіту, ви більше не маєте доступу до інтерактивних функцій зведеної таблиці, як-от фільтрування, сортування або розгортання та згортання рівнів.
Примітка.: Коли ви перетворюєте зведену таблицю онлайнової аналітичної обробки (OLAP), можна й надалі оновлювати дані, щоб отримувати оновлені значення мір, але не можна оновити фактичні елементи, які відображаються у звіті.
Відомості про типові сценарії перетворення зведених таблиць на формули аркуша
Нижче наведено типові приклади того, що можна зробити, перетворивши клітинки зведеної таблиці на формули аркуша, щоб налаштувати макет перетворених клітинок.
Перевпорядкування та видалення клітинок
Припустімо, у вас є періодичний звіт, який потрібно створювати щомісяця для персоналу. Вам потрібна лише підмножина відомостей звіту, і ви віддаєте перевагу розмітити дані настроєним способом. Ви можете просто перемістити та впорядкувати клітинки в потрібному макеті макета, видалити клітинки, які не потрібні для щомісячного звіту персоналу, а потім відформатувати клітинки та аркуш відповідно до своїх уподобань.
Вставлення рядків і стовпців
Припустімо, вам потрібно відобразити відомості про збут за попередні два роки, розбиті на регіон і групу продуктів, і що потрібно вставити розширений коментар до додаткових рядків. Просто вставте рядок і введіть текст. Крім того, потрібно додати стовпець, у якому відображаються дані про продажі за регіонами та групою продуктів, яких немає у вихідній зведеній таблиці. Просто вставте стовпець, додайте формулу, щоб отримати потрібні результати, а потім заповніть стовпець униз, щоб отримати результати для кожного рядка.
Використання кількох джерел даних
Припустімо, потрібно порівняти результати між виробничою та тестовою базою даних, щоб забезпечити отримання очікуваних результатів у тестовій базі даних. Ви можете легко скопіювати формули клітинок, а потім змінити аргумент підключення, щоб він указував на тестову базу даних, щоб порівняти ці два результати.
Використання посилань на клітинки для різних введених даних користувача
Припустімо, що потрібно змінити весь звіт на основі введених користувачем даних. Ви можете змінити аргументи на формули куба на посилання на клітинки на аркуші, а потім ввести різні значення в цих клітинках, щоб отримати різні результати.
Створення неуніформного макета рядків або стовпців (так званого асиметричного звітування)
Припустімо, потрібно створити звіт зі стовпцем "Фактичні продажі" (2008 р.), стовпцем 2009 року "Прогнозовані продажі", але інші стовпці не потрібні. На відміну від зведеної таблиці, можна створити звіт, який містить лише ці стовпці, що вимагає симетричного звітування.
Створення власних формул кубів і виразів багатовимірного виразу
Припустімо, потрібно створити звіт, у якому показано продажі певного товару трьома конкретними продавцями за липень місяць. Якщо ви маєте знання про вирази багатовимірного виразу та запити OLAP, ви можете ввести формули кубів самостійно. Хоча ці формули можуть стати досить складними, ви можете спростити створення та підвищити точність цих формул, використовуючи автозаповнення формул. Докладні відомості див. в статті Використання автозаповнення формул.
Примітка.: За допомогою цієї процедури можна перетворити лише зведену таблицю онлайнової аналітичної обробки (OLAP).
-
Щоб зберегти зведену таблицю для подальшого використання, радимо створити копію книги, перш ніж перетворювати зведену таблицю, вибравши файл > Зберегти як. Докладні відомості див. в статті Збереження файлу.
-
Підготуйте зведену таблицю, щоб мінімізувати впорядкування клітинок після перетворення, виконавши такі дії:
-
Перейдіть до макета, який найбільше нагадує потрібний макет.
-
Взаємодійте зі звітом, наприклад фільтруючи, сортуючи та переплануючи звіт, щоб отримати потрібні результати.
-
-
Клацніть зведену таблицю.
-
На вкладці Параметри в групі Знаряддя натисніть кнопку Знаряддя OLAP, а потім виберіть команду Перетворити на формули.
Якщо фільтрів звіту немає, операцію перетворення буде завершено. Якщо є один або кілька фільтрів звіту, відкриється діалогове вікно Перетворення на формули .
-
Вирішіть, як перетворити зведену таблицю:
Перетворення всієї зведеної таблиці
-
Установіть прапорець Перетворювати фільтри звітів .
Усі клітинки буде перетворено на формули аркуша та видалено всю зведену таблицю.
Перетворення лише підписів рядків, підписів стовпців і значень зведеної таблиці, але збереження фільтрів звіту
-
Переконайтеся, що прапорець Перетворити фільтри звіту знято. (Це значення за замовчуванням.)
Усі підписи рядків, підписи стовпців і області значень перетворюються на формули аркуша, а вихідну зведену таблицю буде перетворено, але лише за допомогою фільтрів звіту, щоб і надалі фільтрувати дані за допомогою фільтрів звіту.
Примітка.: Якщо формат зведеної таблиці – версія 2000–2003 або старішої, можна перетворити лише всю зведену таблицю.
-
-
Натисніть кнопку Перетворити .
Операція перетворення спочатку оновить зведену таблицю, щоб переконатися, що використовуються оновлені дані.
Під час операції перетворення в рядку стану відображається повідомлення. Якщо операція триває довго та потрібно перетворити її в інший час, натисніть клавішу Esc, щоб скасувати операцію.
Примітки.:
-
Не можна перетворити клітинки з фільтрами, застосованими до прихованих рівнів.
-
Не можна перетворити клітинки, у яких поля мають настроюване обчислення, створене за допомогою вкладки Відображати значення як діалогового вікна Параметри поля значень . (На вкладці Параметри в групі Активне поле натисніть кнопку Активне поле та виберіть пункт Параметри поля значень.)
-
Для перетворених клітинок форматування клітинок зберігається, але стилі зведеної таблиці видаляються, оскільки ці стилі можна застосовувати лише до зведених таблиць.
-
Функцію GETPIVOTDATA можна використовувати у формулі, щоб відразу перетворити клітинки зведеної таблиці на формули аркуша, якщо потрібно працювати з джерелами даних, які не є olAP, якщо потрібно не переходити до нового формату зведеної таблиці версії 2007 або уникнути складності використання функцій куба.
-
Переконайтеся, що команду Generate GETPIVOTDATA у групі Зведена таблиця на вкладці Параметри ввімкнуто.
Примітка.: Команда Generate GETPIVOTDATA (Створити GETPIVOTDATA ) установлює або видаляє параметр Використовувати функції GETPIVOTTABLE для посилань зведеної таблиці в категорії Формули розділу Робота з формулами в діалоговому вікні Параметри Excel .
-
Переконайтеся, що у зведеній таблиці відображається клітинка, яку потрібно використовувати в кожній формулі.
-
У клітинці аркуша за межами зведеної таблиці введіть формулу, яка має містити до того місця, де потрібно додати дані зі звіту.
-
Клацніть клітинку зведеної таблиці, яку потрібно використати у формулі зведеної таблиці. Функція аркуша GETPIVOTDATA додається до формули, яка отримує дані зі зведеної таблиці. Ця функція продовжує отримувати правильні дані, якщо змінюється макет звіту або ви оновлюєте дані.
-
Завершіть введення формули та натисніть клавішу Enter.
Примітка.: Якщо видалити зі звіту будь-яку клітинку, на яку посилається формула GETPIVOTDATA, формула поверне #REF!.
