Преобразование ячеек сводной таблицы в формулы листа

В pivotTable есть несколько макетов, которые предопределяют структуру отчета, но их невозможно настроить. Если вам нужна более гибкость при разработке макета отчета таблицы, вы можете преобразовать ячейки в формулы на нем, а затем изменить макет этих ячеек, воспользовавшись всеми возможностями, доступными на этом сайте. Вы можете преобразовать ячейки в формулы, в которые используются функции кубов, или воспользоваться функцией ПОЛУЧИТЬ.В.ВТБ. Преобразование ячеек в формулы значительно упрощает создание, обновление и обслуживание настраиваемых стеблей.

При преобразовании ячеек в формулы эти формулы имеют доступ к тем же данным, что и в pivotTable, и могут обновляться для получения обновленных результатов. Тем не менее, за исключением фильтров отчета, у вас больше нет доступа к интерактивным функциям отчета, таким как фильтрация, сортировка, расширение и укушение уровней.

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

Узнайте о распространенных сценариях преобразования сводных таблиц в формулы на

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

Переустановка и удаление ячеек    

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

Вставка строк и столбцов    

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

Использование нескольких источников данных    

Предположим, что вы хотите сравнить результаты между производственной и тестовой базой данных, чтобы убедиться, что тестовая база данных приводит к ожидаемым результатам. Вы можете легко копировать формулы в ячейки, а затем изменить аргумент подключения, чтобы он указать на тестовую базу данных для сравнения этих двух результатов.

Использование ссылок на ячейки для разного ввода пользователем    

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

Создание макета (асимметричной отчетности) для строк или столбцов, не влияемого на другие формы.    

Предположим, что вам нужно создать отчет, содержащий столбец "Фактические продажи" за 2008 год (2009 г.) под названием "Проектируемые продажи", но другие столбцы не нужны. В отличие от отчета, который требует симметричной отчетности, вы можете создать отчет, содержащий только эти столбцы.

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

Предположим, что вы хотите создать отчет, в который по данным за июль были продажи для конкретного товара трем конкретным продавцам. Если вы хорошо знаете о выражениях с многомерными выражениями и запросах OLAP, вы можете ввести формулы куба самостоятельно. Хотя эти формулы могут быть достаточно сложными, их создание можно упростить и повысить их точность с помощью автозаполнения формул. Дополнительные сведения см. в автозавершении формул.

Примечание: Этой процедурой можно преобразовывать только с помощью интерактивной аналитической вычислительной системы (OLAP).

  1. Чтобы сохранить ее для дальнейшего использования, рекомендуем скопировать книгу перед преобразованием, нажав кнопку "Файл" > "Сохранить как". Дополнительные сведения см. в теме "Сохранение файла".

  2. Подготовьте ее так, чтобы можно было свести к минимуму ранг ячеек после преобразования:

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

    • Вы можете работать с отчетом, например фильтровать, сортировать и переделывать его, чтобы получить нужные результаты.

  3. Щелкните сводную таблицу.

  4. На вкладке "Параметры" в группе "Инструменты" выберите "Инструменты OLAP"и нажмите кнопку "Преобразовать в формулы".

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

  5. Решите, как вы хотите преобразовать ее:

    Преобразование всей pivotTable    

    • Выберите поле "Преобразовать фильтры отчета".

      При этом все ячейки будут преобразованы в формулы на данном этапе, а вся таблица будет удалена.

      Преобразование только подписей строк в отчете, подписей столбцов и области значений с сохранением фильтров отчета    

    • Убедитесь в том, что не настроим поле "Преобразовать фильтры отчета". (Это значение по умолчанию.)

      При этом все подписи строк, столбцов и ячеек с областью значений преобразуются в формулы на месте и при этом сохраняется исходная таблица, но только фильтры отчета, что позволяет продолжить фильтрацию с помощью фильтров отчета.

      Примечание:  Если у вас версия 2000–2003 или более ранная, вы можете преобразовать только всю ее.

  6. Нажмите кнопку Преобразовать.

    В ходе преобразования сначала обновляется и используется обновляемая информация.

    Во время преобразования в сообщении в панели состояния отображается сообщение. Если операция занимает много времени и вы предпочитаете преобразовать ее в другое время, нажмите клавишу ESC, чтобы отменить операцию.

    Примечания: 

    • Ячейки с фильтрами, примененными к уровням, которые скрыты, преобразовать нельзя.

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

    • Для преобразованных ячеек форматирование ячеек сохраняется, но стили в pivotTable удаляются, так как эти стили могут применяться только к сововодным формам.

Функцию ПОЛУЧИТЬ.ДАННЫЕ.ДАННЫЕ.ТАБЛИЦЫ можно использовать в формуле для преобразования ячеек таблицы в формулы на worksheet, если вы хотите работать с источниками данных, которые не являются источниками данных OLAP, если вы предпочитаете не выполнять обновление до нового формата таблицы версии 2007 или избежать сложностей с использованием функций куба.

  1. Убедитесь в том, что команда "Создать ПОЛУЧИТЬ.PIVOTDATA" в группе "PivotTable" на вкладке "Параметры" включена.

    Примечание: Создание наборов команд GETPIVOTDATA или очистка параметра "Использовать функции GETPIVOTTABLE для ссылок на данные таблиц" в категории "Формулы" раздела "Работа с формулами" в диалоговом окне "Параметры Excel".

  2. В pivotTable убедитесь, что ячейка, которую вы хотите использовать в каждой формуле, видна.

  3. Введите формулу в ячейку, не включаемую в отчет, в ячейку на месте таблицы.

  4. Щелкните ячейку в pivotTable, которую вы хотите использовать в формуле. В формулу добавляется функция ПОЛУЧИТЬ.ДАННЫЕ.ДАННЫЕ.ТАБЛИЦЫ, которая извлекает данные из таблицы. Эта функция по-прежнему будет получать правильные данные, если макет отчета изменится или данные обновите.

  5. Завершите ввод формулы и нажмите ввод.

Примечание: If you remove any of the cells referenced in the GETPIVOTDATA formula from the report, the formula returns #REF!.

См. также

Проблема: не удается преобразовать ячейки сводной таблицы в формулы листа

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×