У звіті зведеної таблиці є кілька макетів, що забезпечують заздалегідь визначену структуру звіту: вони не підлягають настройці. Якщо при розробці макета звіту зведеної таблиці необхідна велика гнучкість, можна перетворити осередки в формули листа, а потім змінити макет цих осередків, користуючись перевагами всіх можливостей, які доступні для листа. Можна або перетворити комірки в формули, які використовують функції куба, або використовувати функцію GETPIVOTDATA. Перетворення осередків в формули значно спрощує процес створення, оновлення та підтримки таких звітів зведених таблиць.
Примітка: При перетворенні звіту зведеної таблиці Online Analytical Processing (OLAP) можна продовжувати оновлення даних для отримання нових значень, проте неможливо буде відновити фактичні члени, які відображаються в звіті.
В цій статті
Найбільш поширені сценарії перетворення звітів зведених таблиць в формули листа
Нижче наведені типові приклади того, що можна робити після перетворення осередків зведеної таблиці в формули листа для настройки макета перетворених осередків.
Зміна розташування і видалення осередків
Припустимо, є періодичний звіт, який потрібно створювати для персоналу щомісяця. Для нього необхідно тільки підмножина звітних даних і спеціальний макет. Можна просто перемістити і розташувати потрібним чином осередки в макеті, видалити осередку, включення яких в щомісячний звіт для персоналу не потрібно, а потім відформатувати осередки і лист за потрібне чином.
Вставка рядків або стовпців
Використання декількох джерел даних
Припустимо, необхідно порівняти результати в реальному і тестової базах даних, щоб переконатися в тому, що тестова база даних видає очікувані результати. Можна легко скопіювати формули осередків, а потім змінити аргумент для підключення, щоб вказати на тестову базу даних для порівняння цих двох результатів.
Використання посилань на осередки для зміни введених користувачем даних
Припустимо, необхідно змінити весь звіт в залежності від введених користувачем даних. Можна змінити аргументи в формулах куба на посилання на клітинки аркуша, а потім ввести в комірки інші значення і отримати відповідні результати.
Створення неоднорідного макета рядків або стовпців (асиметричне створення звіту)
Створення власних формул куба і багатовимірних виразів
Перетворення осередки в формули, що використовують функцію куба
Примітка: За допомогою цієї процедури можна перетворити звіт зведеної таблиці OLAP (Online Analytical Processing).
Щоб зменшити перерозподіл осередків після перетворення, підготуйте звіт зведеної таблиці наступним чином:
змініть макет на найбільш близький до потрібного;
отримаєте необхідні результати за допомогою фільтрації, сортування, переконструювання та інших дій зі звітом;
Клацніть звіт зведеної таблиці.
На вкладці Параметри в групі Сервіс натисніть кнопку Засоби OLAP. а потім кнопку Перетворити в формули.
Якщо фільтри звіту відсутні, процес перетворення завершується. Якщо є один або кілька фільтрів звіту, відкриється діалогове вікно Перетворення в формули.
Виберіть спосіб перетворення звіту зведеної таблиці.
Перетворення звіту зведеної таблиці цілком
Встановіть прапорець Перетворити фільтри звіту.
Всі осередки будуть перетворені в формули листа, а весь звіт зведеної таблиці буде видалений.
Перетворення тільки заголовків рядків, стовпців і області значень звіту при збереженні його фільтрів
Переконайтеся, що прапорець Перетворити фільтри звіту знятий. (Він знятий за замовчуванням.)
Всі заголовки рядків, стовпців і осередки області значень будуть перетворені в формули листа, початковий звіт зведеної таблиці буде збережений, але в ньому залишаться тільки фільтри - з їх допомогою в подальшому можна буде виробляти фільтрацію.
Натисніть кнопку Convert.
Операція перетворення спочатку оновлює звіт зведеної таблиці, щоб забезпечити використання нових даних.
Під час виконання цієї операції в рядку стану відображається повідомлення. Якщо операція триває занадто довго і її необхідно відкласти, натисніть клавішу ESC, щоб скасувати операцію.
Не можна перетворити комірки з фільтрами, застосовуваними до прихованих рівнями.
Не можна перетворити комірки, в яких поля використовують додаткові обчислення, створені за допомогою вкладки Додаткові обчислення в діалоговому вікні Параметри поля. (На вкладці Параметри в групі Активне поле натисніть кнопку Активне поле. А потім кнопку Параметри поля.)
Для перетворених осередків збережеться форматування, однак стилі зведеної таблиці будуть видалені, так як ці стилі можуть застосовуватися тільки до зведених таблицях.
Перетворення осередки в формули, що використовують функцію ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ
Переконайтеся, що доступна команда Створити GetPivotData в групі Зведена таблиця на вкладці Параметри.
У звіті зведеної таблиці переконайтеся, що осередок, яка буде використовуватися в кожній формулі, відображається.
У осередок листа, що знаходиться поза звіту зведеної таблиці, введіть потрібну формулу до того місця, де необхідно включити дані зі звіту.
Клацніть клітинку в звіті зведеної таблиці, яку необхідно використовувати у формулі в цьому звіті. У формулу, яка отримує дані зі звіту зведеної таблиці, буде додана функція листа ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ. Ця функція буде отримувати вірні дані, якщо макет звіту зміниться або дані будуть оновлені.
Завершіть введення формули і натисніть клавішу ENTER.