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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  6. Натисніть кнопку Перетворити.

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

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

    Примітки.: 

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

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

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

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

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

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

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

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

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

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

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

Див. також

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

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

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

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

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

×