Как обновить куб в Excel

Обновлено: 20.11.2024

Представьте, что стоит жаркий летний день, и вы наслаждаетесь холодным напитком в своем любимом месте. Что сделает этот напиток особенно освежающим? Кубики льда! Функции КУБ в Excel подобны кубикам льда напитка PowerPivot. Они не обязательны, но просто добавляют элемент в напиток, который делает его намного лучше.

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

Поскольку вы, вероятно, потратите много времени на работу с функциями КУБ, в этой статье основное внимание будет уделено методам эффективного создания формул КУБЗНАЧЕНИЕ. Формулы КУБЗНАЧЕНИЕ могут быть длинными и трудными для чтения и записи. Поэтому лучше найти способы сэкономить как можно больше времени при работе с ними.

Кубики льда и дробленый лед

Существует два основных способа написания функции КУБЗНАЧЕНИЕ. Я называю их методами «Кубик льда» и «Дробленый лед», и, как и их замороженный аналог, тот, который вы используете, основан на личных предпочтениях и, возможно, размере вашей чашки (электронная таблица). 😉

Метод Ice Cube (ссылки на ячейки)

Метод кубика льда основан на ссылке на другие ячейки для выражений элементов в функции КУБЗНАЧЕНИЕ. Эти другие ячейки содержат функции КУБЭЛЕМЕНТ, которые помогают определить, какой фрагмент данных будет возвращен в вашей формуле КУБЗНАЧЕНИЕ.

При преобразовании сводной таблицы в формулы с помощью инструментов OLAP вы получаете формулы, автоматически созданные Excel в методе Ice Cube. Созданное КУБЗНАЧЕНИЕ содержит только ссылки на другие ячейки, а эти ячейки содержат ссылки на элементы модели данных.

Это скорее непрямой подход. Я называю это методом кубика льда, потому что формулы КУБЗНАЧЕНИЕ, как правило, более гладкие и однородные по размеру, но их трудно использовать, не разбивая на части.

Плюсы

Преимущество заключается в том, что формула КУБЗНАЧЕНИЕ короткая. Его также легко создать, если у вас уже есть все настройки формул КУБЭЛЕМЕНТ на листе.

Минусы

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

Например, следующая формула ссылается на выражение члена в ячейке $E13.
=КУБЗНАЧ("Данные PowerPivot",$E$6,$E13,G$7)
В ячейке E13 отображается слово "Красный". Чтобы определить, к какой таблице и полю относится «красный», мне нужно выбрать ячейку E13 и прочитать формулу КУБЭЛЕМЕНТ:

Теперь я вижу, что "Красный" входит в поле "Цвет" в таблице "Товары".

Затем вам придется повторить этот процесс для каждого аргумента в формуле КУБЗНАЧЕНИЕ, чтобы получить полное представление о том, что вычисляется.

Это может занять немного времени и приводит к методу дробленого льда.

Метод дробленого льда: выражения полного члена

Метод дробленого льда относится к формулам КУБЗНАЧЕНИЕ, которые содержат выражения полного члена в формуле. Вместо ссылки на другие ячейки, содержащие формулы КУБЭЛЕМЕНТ, вы можете добавить полную строку в качестве аргумента выражения члена в функции КУБЗНАЧЕНИЕ.

Полное выражение члена будет выглядеть следующим образом: «[Имя таблицы].[Имя поля].[Имя члена]»

И эта же формула будет выглядеть следующим образом:

=КУБЗНАЧ("Данные PowerPivot","[Показатели].[Транзакции]","[Товары].[Подкатегория].&[Горные велосипеды]","[Календарь].[Финансовый квартал].&[2] ","[Продукты].[Цвет].&[Серебро]")

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

Рекомендуется изменить имя элемента на ссылку на ячейку. Тогда формула будет выглядеть следующим образом.

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

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

Плюсы

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

Минусы

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

Куб или дробленый?

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

Какой бы метод вы ни выбрали, он будет зависеть от макета и гибкости вашего отчета. Я обнаружил, что сочетаю оба метода на одном листе. Вероятно, это НЕ лучшая практика, но иногда проще написать формулу КУБЗНАЧЕНИЕ с выражениями из полных членов, чем создавать ячейки КУБЭЛЕМЕНТ в рабочей области, а затем ссылаться на них.

Эта тема, безусловно, открыта для обсуждения, и, надеюсь, мы все сможем извлечь уроки из вашего мнения. Оставить комментарий! 🙂

Преобразовать GETPIVOTDATA в КУБЗНАЧЕНИЕ

Когда вы вводите «=» в ячейку, а затем выбираете ячейку в сводной таблице, в формулу автоматически вводится функция GETPIVOTDATA. Если источником сводной таблицы является PowerPivot, формула GETPIVOTDATA будет содержать выражения членов модели данных. Это быстрый способ получить все выражения членов, которые создали срез данных для ячейки, на которую вы нажали, и вы можете использовать эти выражения в функции КУБЗНАЧЕНИЕ. GETPIVOTDATA содержит дополнительные аргументы, которые необходимо удалить перед использованием в содержимом функции КУБЗНАЧЕНИЕ.

Невозможно (пока) создать функцию КУБЗНАЧЕНИЕ, просто щелкнув ячейку в сводной таблице. Было бы неплохо иметь эту функцию в будущем.

Вот краткое руководство по преобразованию формулы GETPIVOTDATA:

<р>1. Введите = в ячейке, затем щелкните ячейку в сводной таблице. Будет создана формула GETPIVOTDATA, нажмите Enter.

Формула GETPIVOTDATA содержит БОЛЬШИНСТВО выражений-членов, которые вам понадобятся для формулы КУБЗНАЧЕНИЕ, и на самом деле это просто вопрос копирования/вставки текста в формулу КУБЗНАЧЕНИЕ. Я говорю БОЛЬШИНСТВО выражений, потому что формула GETPIVOTDATA НЕ содержит выражений членов в области фильтров сводки. Вам придется добавить их вручную.

<р>2. Скопируйте весь текст внутри круглых скобок ( ) GETPIVOTDATA («копировать это содержимое»).

<р>3. В другой ячейке введите =КУБЗНАЧ("Данные PowerPivot",

Это начало функции КУБЗНАЧЕНИЕ.

<р>4. Теперь вставьте текст, который вы скопировали из функции GETPIVOTDATA, в конец КУБЗНАЧЕНИЕ.

<р>5. Текстовая строка GETPIVOTDATA содержит ДОПОЛНИТЕЛЬНЫЕ аргументы, которые необходимо удалить. GETPIVOTDATA содержит аргумент Field и Item для каждого выражения. Вам НЕ нужен аргумент Поле для КУБЗНАЧЕНИЕ, поэтому вы можете удалить каждое вхождение. Это означает, что вы можете удалить любой другой аргумент в текстовой строке, оставив только аргументы Item. Вы также оставите аргумент измерений, который находится в начале строки.

Есть трюк, который немного упрощает процесс удаления. Выберите ячейку, содержащую формулу КУБЗНАЧЕНИЕ, и нажмите клавишу F2 на клавиатуре, чтобы отредактировать формулу. Обычно вы видите длинную строку текста со всеми аргументами выражения-члена.

Формула будет автоматически переноситься, когда окажется ближе к правому краю окна. Нажмите кнопку «Восстановить вниз» или «Восстановить окно» в окне Excel, затем измените размер рабочего листа, чтобы формула располагалась ближе к правой стороне листа. Вы заметите, что по мере уменьшения окна текст будет продолжать обтекаться. Обычно вы можете выстроить его так, чтобы каждая строка содержала один аргумент.

<р>6. Теперь вам просто нужно удалить каждую вторую строку (аргумент) в формуле.Функция ПОЛУЧИТЬСВОДДАННЫЕ содержит дополнительные аргументы, которые не требуются в функции КУБЗНАЧЕНИЕ.

<р>7. Теперь ваша функция КУБЗНАЧЕНИЕ должна содержать полные выражения-члены в качестве аргументов. Нажмите Enter, чтобы создать формулу. Результаты должны соответствовать исходной формуле GETPIVOTDATA, которую вы использовали в качестве источника.

Окно аргументов функции

При аудите функции КУБЗНАЧЕНИЕ с использованием метода дробленого льда вы можете столкнуться с проблемой невозможности увидеть имя элемента указанной ячейки. Например, ячейка E13 в формуле скрыта, поэтому я точно не знаю, на какое значение ссылается эта формула.

Окно «Аргументы функций» можно использовать для просмотра значения в E13. Поместите курсор мыши в любое место функции КУБЗНАЧЕНИЕ и нажмите кнопку «Вставить функцию» слева от строки формул. Откроется окно «Аргументы функции», и полностью вычисленные выражения будут отображаться в правой части полей ввода. Если ваши имена таблиц и полей длинные, может быть трудно увидеть имя члена. Но это простой способ увидеть все выражения-члены в списке, и он поможет вам при проверке ваших формул.

Конвертация между дроблением и кубами

Небольшой совет. Если вы хотите преобразовать формулу из сжатой (длинной) в кубическую (компактную), можно скопировать выражения элементов в формуле КУБЗНАЧЕНИЕ и вставить их в формулу КУБЭЛЕМЕНТ в другой ячейке. Затем вы замените исходное выражение элемента в формуле КУБЭЛЕМЕНТ ссылкой на ячейку, содержащую формулу КУБЭЛЕМЕНТ.

Закрытие

CUBE — невероятно полезные функции, которые позволяют создавать настраиваемые отчеты вне сводной таблицы. Вероятно, вы обнаружите, что часто используете их при создании информационных панелей и расширенных моделей. Эти советы должны помочь вам сэкономить время при работе с формулами КУБЗНАЧЕНИЕ. Поделитесь своим опытом и советами по работе с функциями CUBE.

Вы можете в любой момент нажать кнопку "Обновить", чтобы обновить данные для сводных таблиц в книге. Вы можете обновить данные для сводных таблиц, подключенных к внешним данным, например к базе данных (SQL Server, Oracle, Access или другой), кубу служб Analysis Services, веб-каналу данных и многим другим источникам. Вы также можете обновить данные из исходной таблицы в той же или другой книге. Кроме того, вы можете настроить книгу на автоматическое обновление данных сводной таблицы при ее открытии.

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

Обновить вручную

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

Нажмите «Анализ» > «Обновить» или нажмите клавиши ALT+F5.

Совет. Чтобы обновить сразу все сводные таблицы в книге, нажмите "Анализ" > "Обновить все".

Если обновление занимает больше времени, чем вы ожидаете, нажмите Анализ > Стрелка обновления > Состояние обновления, чтобы проверить состояние обновления.

Чтобы остановить обновление, нажмите "Отменить обновление".

Запретить корректировку ширины столбцов и форматирования ячеек

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

Нажмите Анализ > Параметры.

На вкладке "Макет и формат" установите флажки "Автоподбор ширины столбцов при обновлении" и "Сохранить форматирование ячеек при обновлении".

Автоматическое обновление данных при открытии книги

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

Нажмите Анализ > Параметры.

На вкладке "Данные" установите флажок "Обновлять данные при открытии файла".

Обновить вручную

Нажмите в любом месте сводной таблицы.

Откроются инструменты сводной таблицы, добавлены вкладка "Параметры" и "Дизайн".

На вкладке "Параметры" в группе "Данные" выполните одно из следующих действий:

Чтобы обновить информацию в соответствии с источником данных, нажмите кнопку "Обновить" или нажмите клавиши ALT+F5.

Вы также можете щелкнуть сводную таблицу правой кнопкой мыши и выбрать "Обновить".

Чтобы обновить все сводные таблицы в книге, нажмите стрелку кнопки "Обновить" и выберите "Обновить все".

Если обновление занимает больше времени, чем вы ожидаете, нажмите «Параметры» > «Обновить» > «Статус обновления», чтобы проверить статус обновления.

Чтобы остановить обновление, нажмите "Отменить обновление".

Запретить корректировку ширины столбцов и форматирования ячеек

Нажмите "Параметры" > "Параметры".

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

Нажмите в любом месте сводной таблицы.

На вкладке "Параметры" в группе "Сводная таблица" нажмите "Параметры".

В диалоговом окне "Параметры сводной таблицы" на вкладке "Данные" установите флажок "Обновлять данные при открытии файла".

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

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

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

В этой главе из книги Формулы и функции Microsoft Excel 2010 наизнанку рассматриваются функции куба в Microsoft Excel 2010.

Функции куба появились в Microsoft Excel 2007. Они используются при подключении к внешним источникам данных SQL и предоставляют инструменты анализа. Кубы данных — это многомерные наборы данных, которые можно хранить в электронной таблице, предоставляя средства для суммирования информации из источника необработанных данных. Куб отличается от запросов в Microsoft Access или Microsoft SQL Server, поскольку данные в кубе уже сгруппированы в иерархии, а рассчитанные показатели сохраняются в кубе. Это дает пользователю два преимущества: сводная информация легкодоступна, а большинство ресурсоемких вычислений выполняется на сервере. Пользователю не нужно тратить много времени на консолидацию данных в Excel. Однако вы не можете использовать вычисляемые поля или элементы для сводной таблицы.

Чтобы использовать функции куба, вы должны работать с данными, доступными в одной из этих двух форм:

Через подключение к источнику данных SQL Server Analysis Services

В автономном кубе в локальной файловой системе пользователя

Эти условия ограничивают полезность функций куба. Чтобы вы могли работать с некоторыми примерами, примеры файлов, прилагаемые к этой книге, включают автономный куб и файлы подключения к данным для примера, описанного в главе 2, «Использование функций и PowerPivot».

Вы можете создавать и изменять автономный куб (файл с расширением .cub) в Excel.

Во-первых, необходимо установить подключение к службам Analysis Services с помощью Microsoft Query (на вкладке "Данные", "Запрос внешних данных/из другого источника/из Microsoft Query") или с помощью помощника по подключению данных (на вкладке "Вставка", выберите Сводная таблица/Использовать внешний источник данных). Затем нажмите кнопку OLAP Tools, как показано на рис. 14-1, чтобы открыть диалоговое окно Offline OLAP Settings. (OLAP расшифровывается как онлайн-аналитическая обработка.) Нажмите кнопку "Создать автономный файл данных", чтобы создать куб, и следуйте пошаговым инструкциям.

Рис. 14-1. Создание автономного куба.

Однако вам придется изменить примеры файлов подключения к данным (они имеют расширение .odc для подключения к книге или .oqy для Microsoft Query), поскольку путь к базе данных должен быть полным. Используйте Блокнот Windows, чтобы изменить пути следующим образом:

Используйте автономный образец файла cubeTest.xlsx. Этот образец файла и дополнительные файлы находятся в папке Chapter14. Дополнительную информацию о файлах примеров см. в разделе «Использование файлов примеров» на стр. xxiii.

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

На вкладке "Вставка" выберите "Сводная таблица/Использовать внешний источник данных". (Можно выполнить поиск дополнительных элементов и использовать существующие файлы подключения к данным.)

Создайте макет и включите контент из источника данных.

Используйте функции куба.

Когда вы открываете книгу с подключениями к данным и используете настройки Excel по умолчанию, вы должны явно разрешить эти подключения (нажмите кнопку «Включить содержимое», как показано на рис. 14-2). Когда вы активируете документ в Excel 2010, документ становится доверенным, и вам не нужно подтверждать активацию снова, пока доверенный документ не будет сброшен в Центре управления безопасностью.

Рис. 14-2. Предупреждение системы безопасности, отображаемое при доступе Excel к внешним данным.

Если выбрать «Преобразовать в формулы» в меню «Инструменты OLAP» (см. рис. 14-1, показанный ранее в этой главе), Excel преобразует часть сводной таблицы или всю сводную таблицу в неформатированную таблицу с тем же содержимым, что и сводная таблица. Преимущество этого в том, что весь макет (столбцы и строки) фиксирован. Вы также можете включить фильтры.

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

Таблица 14-1 Обзор функций куба

Функция

Описание

Возвращает запрошенное свойство для ключевого показателя эффективности (KPI) куба

Возвращает элемент куба

Возвращает запрошенное свойство (атрибут) элемента куба

Возвращает n-й элемент набора

Определяет набор элементов для создания вложенного куба

Возвращает количество элементов в наборе

Возвращает агрегированное значение из куба данных

КУБЭЛЕКТРЭЛЕМЕНТ()

Синтаксис CUBEKPIMEMBER(соединение,имя_kpi,свойство_kpi,заголовок)

Определение Эта функция возвращает свойство ключевого показателя эффективности (KPI) и отображает имя KPI в ячейке.

Аргументы

connection (обязательно) Строка с именем подключения книги к кубу. После того, как вы введете первую кавычку, отобразятся существующие контекстно-зависимые подключения к данным (см. рис. 14-4, показанный далее в этой главе при описании функции КУБЭЛЕМЕНТ()).

kpi_name (обязательно) Указывает имя KPI в кубе.

kpi_property (обязательно) KPI состоит из нескольких компонентов, которые задаются целым числом (см. Таблицу 14-2).

В предыдущем посте я писал о преимуществах использования модели данных в Power Pivot. Это не только экономит ваше время, поскольку вам не нужно жонглировать формулами поиска. Это также обеспечивает значительную экономию размера файла. Все это звучит здорово. Тем не менее, для многих пользователей единственным способом работы с данными в модели данных является использование сводной таблицы. К сожалению, сводная таблица не всегда обеспечивает гибкость, необходимую разработчикам электронных таблиц. Это может помешать Excel Pro использовать модель данных. Что ж, функции CUBE решают эту проблему!

Оглавление

Что такое функции куба?

Данные, хранящиеся в модели данных Power Pivot, не отображаются непосредственно на листе. Это означает, что на листе нет доступных ячеек для традиционных формул Excel, на которые можно было бы ссылаться. Таким образом, традиционные формулы Excel не могут извлекать данные из модели данных. Тем не менее, функции куба в Excel — это формулы, которые позволяют пользователям извлекать данные из определенных источников. Функции куба могут взаимодействовать с моделью данных в Power Pivot.

Теперь вы можете сказать: "Эй, я потратил все свое время на изучение DAX для выполнения вычислений в модели данных. Каким образом функции куба извлекают данные из модели данных на листе Excel?“

Не беспокойтесь. Фактически, существует прекрасное сотрудничество между DAX (меры) и Cube functinos. Язык DAX является необходимым компонентом для выполнения вычислений в вашем наборе данных. Определение показателя находится в так называемом Показании и сохраняется как часть модели данных.

С другой стороны, функции куба ссылаются на элементы и получают значения из куба данных. Мера, определенная в DAX, является одним из возможных элементов, на которые могут ссылаться функции куба. Можно сказать, что формулы куба — это портал между моделью данных и рабочим листом Excel. Именно через этот портал пользователь Excel получает доступ к данным модели данных. Функции куба никогда не заменят язык формул DAX. Вместо этого они зависят от мер, определенных в DAX. Поскольку я не хочу утомлять вас излишней теорией, давайте, наконец, запачкаем руки.

Как использовать функции куба

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

Настройка данных

Представьте, что у вас есть следующий набор данных.

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

Таблица Product Sales содержит показатель с формулой DAX:

Общий объем продаж = СУММ('Продажи продукта'[Продажи])

Создание сводной таблицы с использованием модели данных

Зная настройку данных, теперь вы можете сделать сводку, показывающую общий объем продаж за день. Название недели.

Перейдите на вкладку "Вставка" > "Сводная таблица".
Использовать модель данных этой книги -> ОК
Добавьте название дня в строки и измерьте общий объем продаж как значение

Обратите внимание, что приведенная выше сводная таблица использует модель данных. Используемым таблицам (выделенным жирным шрифтом) предшествует значок таблицы с желтым значком базы данных в правом нижнем углу. Этот желтый символ указывает на то, что таблица является частью модели данных PowerPivot.

Преобразовать в формулы

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

Убедитесь, что курсор находится в сводной таблице.
Нажмите контекстную вкладку "Анализ". (Примечание: эта вкладка отображается, только если курсор находится в сводной таблице)
Нажмите "Инструменты OLAP" > выберите "Преобразовать в формулы".

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

Уделите немного времени изучению формул. Цветные ячейки справа содержат те же формулы, что и цветные ячейки слева. Вновь созданными функциями куба являются КУБЭЛЕМЕНТ() и КУБЗНАЧ().

Ячейка, отмеченная оранжевым цветом, представляет собой формулу КУБЭЛЕМЕНТ, содержащую ссылку на ячейку показателя DAX, который называется "Общий объем продаж".
Ячейки, отмеченные зеленым цветом, представляют собой формулы КУБЭЛЕМЕНТ, которые содержат ссылки на один элемент модели данных. В этом случае столбец «Название дня» из таблицы «Календарь» отфильтрован по пятнице.
Ячейка, отмеченная синим цветом, — это формула КУБЭЛЕМЕНТ, которая также ссылается на столбец "Название дня" в таблице "Календарь". При этом он ссылается на все значения, а не фильтрует один день.
Числа представляют собой формулы КУБЗНАЧЕНИЕ, которые в этом примере ссылаются на две формулы КУБЭЛЕМЕНТ. Обратите внимание, что формула КУБЗНАЧЕНИЕ одинакова для всех значений. Он всегда ссылается на показатель Total Sales и элемент куба слева от него, ссылаясь на день недели. Без корректировок формулу КУБЗНАЧЕНИЕ можно легко скопировать.

Функции КУБЭЛЕМЕНТ и КУБЗНАЧЕНИЕ

Функции КУБЭЛЕМЕНТ и КУБЗНАЧЕНИЕ играют центральную роль в извлечении данных из модели данных. Существуют и другие функции куба, но эти две наиболее важные для освоения. Итак, какова цель этих функций куба и как они работают?

КУБЭЛЕМЕНТ

Синтаксис формулы КУБЭЛЕМЕНТ состоит из 3 аргументов:
КУБЭЛЕМЕНТ( соединение, выражение_члена, заголовок)

Первый аргумент — это соединение. Это относится к имени вашей модели данных. Excel автоматически создает его, и обычно для меня это «ThisWorkbookDataModel».
Выражение Member_Expression стоит на втором месте. Этот аргумент либо разделяет куб данных на определенные элементы, либо указывает меру DAX.
Третий аргумент Caption является необязательным. Если вы хотите, чтобы ваш аргумент КУБЭЛЕМЕНТ отображался с удобным для пользователя именем, заполните его здесь. Этот аргумент очень гибкий и может содержать статический текст, ссылки на ячейки или формулы.

КУБЗНАЧЕНИЕ

Функция КУБЗНАЧ объединяет функции КУБЭЛЕМЕНТ, на которые она ссылается. Затем он возвращает агрегированное значение. Вы можете рассматривать его как инструкцию по извлечению данных из модели данных. Его значение зависит от двух элементов. Прежде всего, это зависит от функций КУБЭЛЕМЕНТ, которые разбивают модель данных на указанные элементы. Это похоже на фильтрацию вашего набора данных. И, во-вторых, это зависит от показателя DAX, который указывает, какой расчет он должен выполнять. На пересечении указанных КУБЭЛЕМЕНТОВ формула КУБЗНАЧЕНИЕ выполняет измерение DAX. Все члены куба функционируют как фильтр с условием И.

Синтаксис формулы КУБЗНАЧЕНИЕ:
КУБЗНАЧЕНИЕ(соединение, выражение_члена1, выражение_члена2, …)

<р>1. Соединение относится к имени модели данных.
2. Выражения-члены — это ссылки на функции КУБЭЛЕМЕНТ. Выражения-члены, которые идут после первого, являются необязательными. При желании вы можете добавить больше из них, если вам нужно.

Заключение

Сами по себе функции КУБЗНАЧЕНИЕ и КУБЭЛЕМЕНТ не очень полезны. Одна формула КУБЭЛЕМЕНТ может показать вам элемент куба. И одна формула КУБЗНАЧЕНИЕ не вернет никакого значения. Именно их синергия при совместной работе бесценна. Используя модель данных, немного зная DAX и используя формулы куба в качестве инструмента, вы можете создавать невероятно мощные отчеты Excel.

Категории Excel сообщить об этом объявлении

О Рике де Гроте

Рик — основатель и редактор BI Gorilla. Он считает, что обучение — одно из самых больших удовольствий в жизни, и хочет поделиться своими знаниями, чтобы помочь вам улучшить свои навыки.

Узнайте больше о нем здесь, свяжитесь с ним в Twitter, Facebook и LinkedIn и подпишитесь на его канал YouTube.

Читайте также: