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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

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

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

У цій статті

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

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

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

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

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

Змінення порядку клітинок та видалення їх   

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

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

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

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

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

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

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

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

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

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

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

На початок сторінки

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

Примітка : За допомогою наведеної процедури можна тільки перетворювати звіт зведеної таблиці Online Analytical Processing (OLAP).

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

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

    • Змініть макет звіту на той, що найбільше схожий на потрібний.

    • За потреби відфільтруйте, відсортуйте та змініть оформлення звіту для отримання бажаних результатів.

  3. Виділіть звіт зведеної таблиці.

  4. На вкладці Параметри в групі Знаряддя виберіть пункт Знаряддя OLAP, а потім виберіть команду Перетворити на формули.

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

  5. Виберіть тип перетворення звіту зведеної таблиці:

    Перетворення всього звіту зведеної таблиці   

    • Установіть прапорець Перетворити фільтри звіту.

      Ця команда перетворює усі клітинки на формули аркуша та видаляє весь звіт зведеної таблиці.

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

    • Перевірте, чи знято прапорець Перетворити фільтри звіту. (Цей параметр задано за промовчанням.)

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

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

  6. Натисніть кнопку Convert.

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

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

    Примітки : 

    • Не можна перетворювати клітинки з фільтрами, застосованими до прихованих рівнів.

    • Не можна перетворювати клітинки, у яких поля містять обчислення, створені за допомогою вкладки Відображати значення як діалогового вікна Настройки поля значень. (На вкладці Параметри групи Активне поле виберіть пункт Активне поле та виберіть пункт Настройки поля значень.)

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

На початок сторінки

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

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

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

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

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

  3. За межами звіту зведеної таблиці введіть потрібну формулу в тій клітинці, де потрібно розташувати дані зі звіту.

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

  5. Введіть формулу до кінця та натисніть клавішу ВВІД.

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

На початок сторінки

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

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×