Сводная таблица имеет несколько макетов, которые предоставляют предопределенную структуру отчета, но эти макеты нельзя настроить. Если вам требуется больше гибкости при проектировании макета отчета сводной таблицы, можно преобразовать ячейки в формулы листа, а затем изменить макет этих ячеек, используя все возможности, доступные на листе. Можно преобразовать ячейки в формулы, использующие функции Куба, или использовать функцию GETPIVOTDATA. Преобразование ячеек в формулы значительно упрощает процесс создания, обновления и обслуживания этих настраиваемых сводных таблиц.
При преобразовании ячеек в формулы эти формулы получают доступ к тем же данным, что и сводная таблица, и их можно обновить, чтобы просмотреть актуальные результаты. Однако, возможно, за исключением фильтров отчетов, у вас больше нет доступа к интерактивным функциям сводной таблицы, таким как фильтрация, сортировка или расширение и сворачивание уровней.
Примечание: При преобразовании сводной таблицы OLAP можно продолжать обновлять данные, чтобы получать актуальные значения мер, но не сможете обновить фактические элементы, отображаемые в отчете.
Сведения о распространенных сценариях преобразования сводных таблиц в формулы листа
Ниже приведены типичные примеры того, что можно сделать после преобразования ячеек сводной таблицы в формулы листа для настройки макета преобразованных ячеек.
Переупорядочение и удаление ячеек
Предположим, что у вас есть периодический отчет, который необходимо создавать каждый месяц для сотрудников. Вам требуется только подмножество сведений отчета, и вы предпочитаете выкладывать данные в соответствии с настройками. Вы можете просто переместить и расположить ячейки в макете, удалить ячейки, которые не нужны для ежемесячного отчета о персонале, а затем отформатировать ячейки и лист в соответствии с вашими предпочтениями.
Вставка строк и столбцов
Предположим, что вы хотите отобразить сведения о продажах за предыдущие два года с разбивкой по регионам и группам продуктов, а также вставить расширенные комментарии в дополнительные строки. Просто вставьте строку и введите текст. Кроме того, необходимо добавить столбец, показывающий продажи по регионам и группам продуктов, которые отсутствуют в исходной сводной таблице. Просто вставьте столбец, добавьте формулу для получения нужных результатов, а затем заполните столбец вниз, чтобы получить результаты для каждой строки.
Использование нескольких источников данных
Предположим, что вы хотите сравнить результаты между рабочей и тестовой базами данных, чтобы убедиться, что тестовая база данных дает ожидаемые результаты. Вы можете легко скопировать формулы ячеек, а затем изменить аргумент подключения, чтобы он указывал на тестовую базу данных для сравнения этих двух результатов.
Использование ссылок на ячейки для изменения входных данных пользователя
Предположим, что вы хотите изменить весь отчет в зависимости от введенных пользователем данных. Можно изменить аргументы формул куба на ссылки на ячейки на листе, а затем ввести в эти ячейки разные значения, чтобы получить разные результаты.
Создание неуниверсичного макета строки или столбца (также называемого асимметричными отчетами)
Предположим, что вам нужно создать отчет, содержащий столбец 2008 с именем "Фактические продажи", столбец 2009 с именем "Прогнозируемые продажи", но другие столбцы не требуются. Вы можете создать отчет, содержащий только эти столбцы, в отличие от сводной таблицы, для которой требуется симметричная отчетность.
Создание собственных формул куба и выражений многомерных выражений
Предположим, что вы хотите создать отчет о продажах определенного продукта тремя конкретными продавцами за июль. Если вы знакомы с многомерными выражениями и запросами OLAP, вы можете самостоятельно ввести формулы куба. Хотя эти формулы могут стать довольно сложными, вы можете упростить создание и повысить точность этих формул с помощью автозаполнения формул. Дополнительные сведения см. в статье Использование автозаполнения формул.
Примечание: Преобразовать сводную таблицу OLAP можно только с помощью этой процедуры.
-
Чтобы сохранить сводную таблицу для использования в будущем, рекомендуется создать копию книги перед преобразованием сводной таблицы, щелкнув Файл > Сохранить как. Дополнительные сведения см. в разделе Сохранение файла.
-
Подготовьте сводную таблицу, чтобы можно было свести к минимуму переупорядочение ячеек после преобразования, выполнив следующие действия.
-
Измените макет, который больше всего похож на нужный.
-
Взаимодействуйте с отчетом, например фильтрацией, сортировкой и изменением отчета, чтобы получить нужные результаты.
-
-
Щелкните сводную таблицу.
-
На вкладке Параметры в группе Сервис выберите инструменты OLAP, а затем — Преобразовать в формулы.
Если фильтров отчетов нет, операция преобразования завершается. Если есть один или несколько фильтров отчетов, откроется диалоговое окно Преобразовать в формулы .
-
Решите, как преобразовать сводную таблицу:
Преобразование всей сводной таблицы
-
Установите флажок Преобразовать фильтры отчетов проверка.
При этом все ячейки преобразуются в формулы листа и удаляются все сводные таблицы.
Преобразуйте только метки строк сводной таблицы, метки столбцов и области значений, но сохраните фильтры отчетов
-
Убедитесь, что флажок Преобразовать фильтры отчетов проверка установлен. (Это значение по умолчанию.)
При этом все ячейки метки строк, метки столбца и области значений преобразуются в формулы листа и сохраняют исходную сводную таблицу, но только с фильтрами отчета, чтобы можно было продолжать фильтрацию с помощью фильтров отчета.
Примечание: Если формат сводной таблицы имеет версию 2000–2003 или более раннюю, можно преобразовать только всю сводную таблицу.
-
-
Нажмите кнопку Преобразовать.
Операция преобразования сначала обновляет сводную таблицу, чтобы убедиться, что используются актуальные данные.
Сообщение отображается в строке состояния во время операции преобразования. Если операция занимает много времени и вы предпочитаете выполнить преобразование в другое время, нажмите клавишу ESC, чтобы отменить операцию.
Примечания:
-
Невозможно преобразовать ячейки с фильтрами, примененными к скрытым уровням.
-
Невозможно преобразовать ячейки, в которых поля имеют пользовательское вычисление, созданное на вкладке Показать значения как диалогового окна Параметры поля значений . (На вкладке Параметры в группе Активное поле щелкните Активное поле, а затем — Параметры поля значений.)
-
Для преобразованных ячеек форматирование ячеек сохраняется, но стили сводной таблицы удаляются, так как эти стили могут применяться только к сводных таблицам.
-
Функцию GETPIVOTDATA в формуле можно использовать для преобразования ячеек сводной таблицы в формулы листа, если вы хотите работать с источниками данных, не являющимися источниками данных OLAP, если вы предпочитаете не обновляться до нового формата сводной таблицы версии 2007 сразу или если вы хотите избежать сложности использования функций Куб.
-
Убедитесь, что команда Создать GETPIVOTDATA в группе Сводная таблица на вкладке Параметры включена.
Примечание: Команда Generate GETPIVOTDATA задает или очищает параметр Использовать функции GETPIVOTTABLE для ссылок на сводную таблицу в категории Формулы раздела Работа с формулами в диалоговом окне Параметры Excel .
-
Убедитесь, что в сводной таблице отображается ячейка, которую вы хотите использовать в каждой формуле.
-
В ячейке листа за пределами сводной таблицы введите нужную формулу до точки, в которую необходимо включить данные из отчета.
-
Щелкните ячейку в сводной таблице, которую вы хотите использовать в формуле в сводной таблице. В формулу добавляется функция листа GETPIVOTDATA, которая извлекает данные из сводной таблицы. Эта функция продолжает получать правильные данные при изменении макета отчета или при обновлении данных.
-
Завершите ввод формулы и нажмите клавишу ВВОД.
Примечание: Если удалить из отчета любую из ячеек, на которые ссылается формула GETPIVOTDATA, формула вернет #REF!.
