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

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

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

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

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

В этой статье

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

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

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

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

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

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

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

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

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

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

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

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

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

Создание неоднородного макета строк или столбцов (асимметричное создание отчета)   

Предположим, необходимо создать отчет, содержащий 2008-й столбец с заголовком «Текущие продажи» и 2009-й столбец с заголовком «Планируемые продажи», и при этом других столбцов не требуется. Можно создать отчет, содержащий только эти столбцы, в отличие от отчета сводной таблицы, который требует симметричного создания.

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

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

К началу страницы

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

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

  1. Чтобы сохранить отчет сводной таблицы для использования в будущем, рекомендуется создать копию книги, перед преобразованием сводной таблицы с помощью команды « Сохранить как » на Кнопку Microsoft Office Изображение кнопки Office . Подробнее читайте сохранения файла.

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

    • измените макет на наиболее близкий к нужному;

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

  3. Щелкните отчет сводной таблицы.

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

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

  5. Выберите способ преобразования отчета сводной таблицы.

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

    • Установите флажок Преобразовать фильтры отчета.

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

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

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

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

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

  6. Нажмите кнопку Convert.

    Операция преобразования сначала обновляет отчет сводной таблицы, чтобы обеспечить использование новых данных.

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

    Примечания : 

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

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

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

К началу страницы

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

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

  1. Убедитесь, что доступна команда Создать GetPivotData в группе Сводная таблица на вкладке Параметры.

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

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

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

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

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

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

К началу страницы

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

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

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

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

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

×