Создание именованного набора в сводной таблице OLAP

Применяется к
Excel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

  • Группируйте общие наборы элементов, которые можно использовать повторно, даже если эти наборы отсутствуют в данных.
  • Объединяйте элементы из разных иерархий способами, которые были недоступны в более ранних версиях Excel. Их часто называют асимметричными отчетами.
  • Создайте именованный набор с помощью пользовательских многомерных выражений — языка запросов для баз данных OLAP, предоставляющего синтаксис вычислений, аналогичный формулам на листе.

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

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

В этой статье

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

  1. Щелкните сводную таблицу OLAP, для которой требуется создать именованный набор.
    Откроется вкладка "Работа со сводными таблицами" с дополнительными параметрами и вкладками "Конструктор".

  2. На вкладке "Параметры " в группе "Вычисления " щелкните "Поля", "Элементы" & "Наборы", а затем выберите пункт "Создать набор на основе элементов строк " или " Создать набор на основе элементов столбцов".
    ''Работа со сводными таблицами'': группа ''Вычисления'' на вкладке ''Параметры''
    Откроется диалоговое окно "Новый набор ". При необходимости вы можете изменить размер этого диалогового окна, перетащив маркер изменения размера в правом нижнем углу диалогового окна.

  3. В поле Имя набора введите имя, которое будет использоваться для набора.

  4. Чтобы указать строки, которые нужно включить в именованный набор, выполните одно или несколько из указанных ниже действий.

    • Чтобы удалить строку из списка элементов, щелкните область слева от строки, которую нужно выделить, и нажмите кнопку Удалить строку.
    • Чтобы добавить новую строку в список элементов, щелкните область слева от строки, под которой требуется добавить новую строку, и нажмите кнопку "Добавить строку".
    • Чтобы создать копию элемента, щелкните область слева от строки, которую нужно скопировать, и нажмите кнопку "Копировать строку".
    • Чтобы переместить элемент в другое место, щелкните область слева от строки, которую требуется переместить, а затем с помощью клавиш со стрелками вверх и вниз переместите его в нужное место.

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

    • Чтобы отображать их в одном поле с другими элементами, снимите флажок Показывать элементы с разных уровней проверка отдельных полях.
    • Чтобы сохранить текущие поля в области строк или столбцов, снимите флажок Заменить текущие поля в области строк новым набором или Заменить поля, находящиеся в области столбцов, новым набором проверка. Набор не будет отображаться в сводной таблице после нажатия кнопки "ОК", но будет доступен в списке полей сводной таблицы.

  6. Нажмите кнопку "ОК", чтобы создать именованный набор.

    Примечание

    • Чтобы отменить все действия после закрытия диалогового окна, нажмите кнопку "Отменить " на панели быстрого доступа.
    • К именованным множествам нельзя применять фильтры любого типа.

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

Создание именованного набора с помощью многомерных выражений

Важно

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

  1. Щелкните сводную таблицу OLAP, для которой требуется создать настраиваемый именованный набор.

  2. На вкладке "Параметры " в группе "Вычисления " щелкните "Поля", "Элементы" & "Наборы", а затем — "Управление наборами".
    ''Работа со сводными таблицами'': группа ''Вычисления'' на вкладке ''Параметры''
    Откроется диалоговое окно Set Manager . При необходимости вы можете изменить размер этого диалогового окна, перетащив маркер изменения размера в правом нижнем углу диалогового окна.

  3. Нажмите кнопку "Создать", а затем выберите пункт "Создать набор с помощью многомерных выражений".

  4. В поле Имя набора введите имя, которое будет использоваться для набора.

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

    • В поле "Задать определение " введите или вставьте скопированное определение многомерных выражений.

    • На вкладке "Поля и элементы " выберите запись списка полей, которую нужно включить, и нажмите кнопку "Вставить".
      Можно также перетащить запись списка полей в поле Задать определение или дважды щелкнуть запись списка полей.
      Доступные записи списка полей

      Запись списка полей Примеры многомерных выражений, созданных с помощью куба Adventure Works
      измерение [Продукт]
      Иерархия атрибутов (включает элемент "Все") [Продукт]. [Категория]
      Уровень иерархии атрибутов (не включает элемент "Все") [Продукт]. [Категория]. [Категория]
      Элемент из иерархии атрибутов [Продукт]. [Категория].&[4]
      Иерархия пользователей [Продукт]. [Категории продуктов]
      Уровень иерархии пользователя [Продукт]. [Категории продуктов]. [Категория]
      Участник из иерархии пользователей [Продукт]. [Категории продуктов]. [Категория].&[4]
      Измерение [Меры]. [Объем продаж через Интернет]
      Вычисляемая мера [Меры]. [Отношение Интернета к родительскому продукту]
      Именованный набор [Основная группа продуктов]
      Значение KPI KPIValue("Маржа валовой прибыли продукта")
      Цель KPI KPIGoal(«Маржа валовой прибыли продукта»),
      Статус KPI KPIStatus("Маржа валовой прибыли продукта")
      Тренд KPI KPITrend("Маржа валовой прибыли продукта")
      Свойство элемента из иерархии пользователей [Продукт]. [Категории продуктов]. Properties("Class" )
      Свойство элемента из иерархии атрибутов [Продукт]. [Продукт]. Properties("Class")

    • На вкладке "Функции " выберите одну или несколько функций из списка многомерных выражений, которые требуется использовать, и нажмите кнопку "Вставить". Функции многомерных выражений поддерживаются службами Analysis Services; они не включают функции VBA.
      Аргументы функции помещаются внутрь символов шеврона (<<>>). Чтобы заменить аргументы-заполнители, щелкните их и введите допустимые имена.
      Примеры многомерных функций
      ADDCALCULATEDMEMBERS( «Set» )
      АГРЕГАТ( «Множество»[, «Числовое выражение»] )
      «Уровень». ALLMEMBERS
      «Иерархия». ALLMEMBERS
      ANCESTOR( «Член» «Уровень» )
      ПРЕДОК( «Участник», «Расстояние»)
      ANCESTORS( «Член», «Расстояние» )
      ANCESTORS( «Участник», «Уровень»)
      ВОСХОДЯЩИЕ ( «Член»)
      AVG( «Set»[, «Числовое выражение»] )
      AXIS( «Числовое выражение» )
      BOTTOMNCOUNT( «Set», «Count»[, «Числовое выражение»] )
      BOTTOMPERCENT( «Set», «Percentage», «Numeric Expression» )
      BOTTOMSUM( «Множество», «Значение», «Числовое выражение» ) ...

  6. Чтобы протестировать новое определение многомерных выражений, нажмите кнопку Проверить многомерные выражения.

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

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

  8. Если вы подключены к кубу SQL Server 2008 Analysis Services, динамический именованный набор создается по умолчанию. Этот именованный набор пересчитывается автоматически с каждым обновлением.

    • Чтобы именованный набор не пересчитывался при каждом обновлении, снимите флажок Пересчитывать набор при каждой проверке обновления.

  9. Нажмите кнопку "ОК", чтобы создать именованный набор.

    Примечание

    • Чтобы отменить все действия после закрытия диалогового окна, нажмите кнопку "Отменить " на панели быстрого доступа.
    • К именованным множествам нельзя применять фильтры любого типа.

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

Изменение или удаление именованного набора

  1. Щелкните сводную таблицу OLAP, содержащую именованный набор, который требуется изменить или удалить.

  2. На вкладке "Параметры " в группе "Вычисления " щелкните "Поля", "Элементы" & "Наборы", а затем — "Управление наборами".
    ''Работа со сводными таблицами'': группа ''Вычисления'' на вкладке ''Параметры''
    Откроется диалоговое окно Set Manager . При необходимости вы можете изменить размер этого диалогового окна, перетащив маркер изменения размера в правом нижнем углу диалогового окна.

  3. Выберите набор, который нужно изменить или удалить.

  4. Выполните одно из следующих действий:

    • Чтобы изменить выбранный именованный набор, нажмите кнопку "Изменить" и внесите необходимые изменения.
    • Чтобы удалить выбранный именованный набор, нажмите кнопку "Удалить", а затем нажмите кнопку " Да " для подтверждения.

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