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

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

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

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

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

Изменение порядка и удаление ячеек 

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

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

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

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

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

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

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

Создание неоднородного макета строк или столбцов (также называется асимметричной отчетностью) 

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

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

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

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

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

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

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

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

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

5. Определите, как вы хотите преобразовать сводную таблицу:
   Преобразование всей сводной таблицы 

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

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

     Примечание

     Если вы используете формат сводной таблицы 2000–2003 или более ранние, вы можете преобразовать только всю сводную таблицу.

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

   Примечание

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

Преобразование ячеек с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

1. Убедитесь, что в группе "Сводная таблица" на вкладке "Параметры" включена команда "Создать ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ".

   Примечание

   Команда "Создать" ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ устанавливает или снимает флажок "Использовать функции ПОЛУЧИТЬ.СВОДНУЮ.ТАБЛИЦУ для ссылок на сводные таблицы" в категории "Формулы" раздела "Работа с формулами" диалогового окна "Параметры Excel".

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

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

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

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

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