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

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

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

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

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

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

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

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

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

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

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

Змінення введених користувачем відомостей за допомогою посилань на клітинки 

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

створення нерівномірного макета рядків або стовпців (так званий асиметричний звіт). 

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

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

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

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

1. Щоб зберегти зведену таблицю для подальшого використання, ми радимо скопіювати книгу, перш ніж перетворювати зведену таблицю. Для цього виберіть команду "Зберегти>як". Докладні відомості див. в статті "Збереження файлу".

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

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

3. Клацніть зведену таблицю.

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

5. Вирішіть, як потрібно перетворити зведену таблицю:
   Перетворення всієї зведеної таблиці 

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

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

     Примітка.

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

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

   Примітка.

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

Перетворення клітинок за допомогою функції GETPIVOTDATA

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

1. Переконайтеся, що команду "Згенерувати GETPIVOTDATA" в групі "Зведена таблиця " на вкладці "Параметри " ввімкнуто.

   Примітка.

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

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

3. У клітинці аркуша за межами зведеної таблиці введіть формулу, яка має містити дані зі звіту.

4. Клацніть клітинку у зведеній таблиці, яку потрібно використати у формулі зведеної таблиці. До формули додається функція аркуша GETPIVOTDATA, яка отримує дані зі зведеної таблиці. Ця функція продовжуватиме отримувати правильні дані, якщо зміниться макет звіту або ви оновите дані.

5. Завершіть введення формули та натисніть клавішу Enter.

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