Как составить таблицу кубов в эксель

Microsoft постоянно добавляет в Excel новые возможности в части анализа и визуализации данных. Работу с информацией в Excel можно представить в виде относительно независимых трех слоев:

• «правильно» организованные исходные данные
• математика (логика) обработки данных
• представление данных

Рис. 1. Анализ данных в Excel: а) исходные данные, б) мера в Power Pivot, в) дашборд; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

Функции кубов и сводные таблицы

Наиболее простым и в тоже время очень мощным средством представления данных являются сводные таблицы. Они могут быть построены на основе данных, содержащихся: а) на листе Excel, б) кубе OLAP или в) модели данных Power Pivot. В последних двух случаях, помимо сводной таблицы, можно использовать аналитические функции (функции кубов) для формирования отчета на листе Excel. Сводные таблицы проще. Функции кубов сложнее, но предоставляют больше гибкости, особенно в оформлении отчетов, поэтому они широко применяются в дашбордах.

Дальнейшее изложение относится к формулам кубов и сводным таблицам на основе модели Power Pivot и в нескольких случаях на основе кубов OLAP.

Простой способ получить функции кубов

Когда (если) вы начинали изучать код VBA, то узнали, что проще всего получить код, используя запись макроса. Далее код можно редактировать, добавить циклы, проверки и др. Аналогично проще всего получить набор функций кубов, преобразовав сводную таблицу (рис. 2). Встаньте на любую ячейку сводной таблицы, перейдите на вкладку Анализ, кликните на кнопке Средства OLAP, и нажмите Преобразовать в формулы.

Рис. 2. Преобразование сводной таблицы в набор функций куба

Числа сохранятся, причем это будут не значения, а формулы, которые извлекают данные из модели данных Power Pivot (рис. 3). Получившуюся таблицу вы может отформатировать. В том числе, можно удалять и вставлять строки и столбцы внутрь таблицы. Срез остался, и он влияет на данные в таблице. При обновлении исходных данных числа в таблице также обновятся.

Рис. 3. Таблица на основе формул кубов

Функция КУБЗНАЧЕНИЕ()

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

Написание формулы «с нуля»

Вам не обязательно преобразовывать готовую сводную таблицу. Вы можете написать любую формулу куба «с нуля». Например, в ячейку С10 введена следующая формула (рис. 4):

Рис. 4. Функция КУБЗНАЧЕНИЕ() в ячейке С10 возвращает продажи велосипедов за все годы, как и в сводной таблице

Маленькая хитрость. Чтобы удобнее было читать формулы кубов, желательно, чтобы в каждой строке помещался только один аргумент. Можно уменьшить окно Excel. Для этого кликните на значке Свернуть в окно, находящемся в правом верхнем углу экрана. А затем отрегулируйте размер окна по горизонтали. Альтернативный вариант – принудительно переносить текст формулы на новую строку. Для этого в строке формул поставьте курсор в том месте, где хотите сделать перенос и нажмите Alt+Enter.

Рис. 5. Свернуть окно

Синтаксис функции КУБЗНАЧЕНИЕ()

Справка Excel абсолютно точна и абсолютно бесполезна для начинающих:

КУБЗНАЧЕНИЕ(подключение; [выражение_элемента1]; [выражение_элемента2]; …)

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

Выражение_элемента – необязательный аргумент; текстовая строка, представляющая многомерное выражение, которое возвращает элемент или кортеж в кубе. Кроме того, «выражение_элемента» может быть множеством, определенным с помощью функции КУБМНОЖ. Используйте «выражение_элемента» в качестве среза, чтобы определить часть куба, для которой необходимо возвратить агрегированное значение. Если в аргументе «выражение_элемента» не указана мера, будет использоваться мера, заданная по умолчанию для этого куба.

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

Некоторые сведения о кубах OLAP и моделях данных Power Pivot

Кубы данных OLAP (Online Analytical Processing — оперативный анализ данных) были разработаны специально для аналитической обработки и быстрого извлечения из них данных. Представьте трехмерное пространство, где по осям отложены периоды времени, города и товары (рис. 5а). В узлах такой координатной сетки расположены значения различных мер: объем продаж, прибыль, затраты, количество проданных единиц и др. Теперь вообразите, что измерений десятки, или даже сотни… и мер тоже очень много. Это и будет многомерный куб OLAP. Создание, настройка и поддержание в актуальном состоянии кубов OLAP – дело ИТ-специалистов.

Рис. 5а. Трехмерный куб OLAP

Аналитические формулы Excel (формулы кубов) извлекают названия осей (например, Время), названия элементов на этих осях (август, сентябрь), значения мер на пересечении координат. Именно такая структура и позволяет сводным таблицам на основе кубов и формулам кубов быть столь гибкими, и подстраиваться под нужды пользователей. Сводные таблицы на основе листов Excel не используют меры, поэтому они не столь гибки в целях анализа данных.

Power Pivot – относительно новая фишка Microsoft. Это встроенная в Excel и отчасти независимая среда с привычным интерфейсом. Power Pivot значительно превосходит по своим возможностям стандартные сводные таблицы. Вместе с тем, разработка кубов в Power Pivot относительно проста, а самое главное – не требует участия ИТ-специалиста. Microsoft реализует свой лозунг: «Бизнес-аналитику – в массы!». Хотя модели Power Pivot не являются кубами на 100%, о них также можно говорить, как о кубах (подробнее см. вводный курс Марк Мур. Power Pivot и более объемное издание Роб Колли. Формулы DAX для Power Pivot).

Основные компоненты куба – это измерения, иерархии, уровни, элементы (или члены; по-английски members) и меры (measures). Измерение – основная характеристика анализируемых данных. Например, категория товаров, период времени, география продаж. Измерение – это то, что мы можем поместить на одну из осей сводной таблицы. Каждое измерение помимо уникальных значений включает элемент [ALL], выполняющий агрегацию всех элементов этого измерения.

Измерения построены на основе иерархии. Например, категория товаров может разбиваться на подкатегории, далее – на модели, и наконец – на названия товаров (рис. 5б)  Иерархия позволяет создавать сводные данные и анализировать их на различных уровнях структуры. В нашем примере иерархия Категория включает 4 Уровня.

Рис. 5б. Иерархия категорий товаров

Элементы (отдельные члены) присутствуют на всех уровнях. Например, на уровне Category есть четыре элемента: Accessories, Bikes, Clothing, Components. Другие уровни имеют свои элементы.

­Меры – это вычисляемые значения, например, объем продаж. Меры в кубах хранятся в собственном измерении, называемом [Measures] (см. ниже рис. 9). Меры не имеют иерархий. Каждая мера рассчитывает и хранит значение для всех измерений и всех элементов, и нарезается в зависимости от того, какие элементы измерений мы поместим на оси. Еще говорят, какие зададим координаты, или какой зададим контекст фильтра. Например, на рис. 5а в каждом маленьком кубике рассчитывается одна и та же мера – Прибыль. А возвращаемое мерой значение зависит от координат. Справа на рисунке 5а показано, что Прибыль (в трех координатах) по Москве в октябре на яблоках = 63 000 р. Меру можно трактовать, и как одно из измерений. Например, на рис. 5а вместо оси Товары, разместить ось Меры с элементами Объем продаж, Прибыль, Проданные единицы. Тогда каждая ячейка и будет каким-то значением, например, Москва, сентябрь, объем продаж.

Кортеж – несколько элементов разных измерений, задающие координаты по осям куба, в которых мы рассчитываем меру. Например, на рис. 5а Кортеж = Москва, октябрь, яблоки. Также допустимый кортеж – Пермь, яблоки. Еще один – яблоки, август. Не вошедшие в кортеж измерения присутствуют в нем неявно, и представлены членом по умолчанию [All]. Таким образом, ячейка многомерного пространства всегда определяется полным набором координат, даже если некоторые из них в кортеже опущены. Нельзя включить два элемента одного измерения в кортеж, не позволит синтаксис. Например, недопустимый кортеж Москва и Пермь, яблоки. Чтобы реализовать такое многомерное выражение потребуется набор двух кортежей: Москва и яблоки + Пермь и яблоки.

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

Автозавершение в помощь

Вернемся к синтаксису функции КУБЗНАЧЕНИЕ. Воспользуемся автозавершением. Начните ввод формулы в ячейке:

=КУБЗНАЧЕНИЕ("

Excel предложит все доступные в книге Excel подключения:

Рис. 6. Подключение к модели данных Power Pivot всегда называется ThisWorkbookDataModel

Рис. 7. Подключения к кубам

Продолжим ввод формулы (в нашем случае для модели данных):

=КУБЗНАЧЕНИЕ("ThisWorkbookDataModel";"

Автозавершение предложит все доступные таблицы и меры модели данных:

Рис. 8. Доступные элементы первого уровня – имена таблиц и набор мер (выделен)

Выберите значок Measures. Поставьте точку:

=КУБЗНАЧЕНИЕ("ThisWorkbookDataModel";"[Measures].

Автозавершение предложит все доступные меры:

Рис. 9. Доступные элементы второго уровня в наборе мер

Выберите меру [Total Sales]. Добавьте кавычки, закрывающую скобку, нажмите Enter.

=КУБЗНАЧЕНИЕ("ThisWorkbookDataModel";"[Measures].[Total Sales]")

Рис. 10. Формула КУБЗНАЧЕНИЕ в ячейке Excel

Аналогичным образом можете добавить третий аргумент в формулу:

В итоге формула возвращает продажи по категории Велосипеды (рис. 11). Автозавершение фактически ведет нас по иерархии модели данных:

• название самой модели
• название таблицы (или набор мер – Measures)
• название иерархии/столбца (или имя меры)
• общий итог по столбцу – [All]
• название элемента столбца

Чтобы правильно сослаться на элемент измерения, необходимо описать полный путь к нему по иерархии, начиная с самого верхнего уровня, например: [Products].[Category].[All].[Bikes]. Однако если имя члена уникально в пределах какой-то иерархии, то эту иерархию можно опустить. Если имя уникально в кубе, то можно опустить все промежуточные уровни (рис. 11). В тоже время лучшая практика заключается в том, чтобы оставить на месте все уровни. Это делает формулу более информативной.

Рис. 11. Общие продажи велосипедов; необязательные уровни

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

Рис. 12. Автозавершение предлагает все имеющиеся в модели срезы

В примерах выше выпадающий список появлялся после ввода двух символов:

"       открывающие кавычки – в начале каждого аргумента; предлагаются доступные подключения, измерения/таблицы, набор мер;

.        точка – после закрывающей прямоугольной скобки; предлагает элементы следующего уровня иерархии.

На самом деле, автозавершение срабатывает и после нескольких других символов. Мы рассмотрим их позже.

Режим автозавершения работает не только при наборе формул. В него можно перейти и для  редактирования готовой формулы. Для этого встаньте на ячейку с формулой. Нажмите F2. Вы перейдете в режим редактирования формул (1 на рис. 12а). В левом нижнем углу окна Excel появится надпись Правка (2). Переместите курсор в интересующее вас место формулы (3). Или вместо шагов 1–3 сразу установите курсор в строке формул (4). Нажмите комбинацию клавиш Alt + стрелка вниз. Выпадающий динамический список отразит доступные опции. Обратите внимание, что в другой позиции курсора список иной (5).

Рис. 12а. Работа автозавершения при редактировании формул

Составные строки в качестве аргументов

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

Рис. 13. Аргумент, набранный из нескольких текстовых строк, сцепленных вместе

Кавычки (1 и 2) выделяют первый фрагмент текстовой строки. Знаки конкатенации (3 и 5) – операторы Excel, каждый из них соединяет предыдущий и последующий текстовые фрагменты. Ссылка на ячейку $Е$11 возвращает текст Bikes. Последний фрагмент текстовой строки ] взят в кавычки (6 и 7), поскольку это текст. Результат сцепки фрагментов – "[Products].[Category].[Bikes]".

Изучая формулы в Интернете я заметил, что многие авторы отделяют имена столбцов от конкретного значения знаком &. Например:

"[Products].[Category].[All].&[Bikes]"

Здесь этот знак необязателен. Я предполагаю, что наличие & является признаком хорошего стиля (или традиции), упрощающего чтение формулы. Причем & здесь не оператор конкатенации, а просто текстовый символ (поскольку находится между открывающими и закрывающими кавычками). Этот знак обрабатывается уже внутри модели Power Pivot, и не мешает распознать, к какому элементу обращается формула. Знак конкатенации в других частях аргумента возвращает ошибку:

"[Products].&[Category].[All].[Bikes]"

"[Products].[Category].&[All].[Bikes]"

Знак & также возвращает ошибку (в любом месте текстовой строки) при обращении к кубу OLAP. Т.е., Power Pivot «проглатывает» & в «правильном» месте, а куб OLAP – нет.

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

Еще одна версия, & – элемент языка MDX (подробнее см. ниже), в котором к члену иерархии можно обратиться несколькими способами. Например:

[Calendar].[CY 2004].[H1 CY 2004].[Q1 CY 2004]

[Calendar].[Calendar Quarter].&[2004].&[1]

В первом варианте обращение к члену иерархии происходит через указание полного пути и полных имен членов на этом пути. Во втором варианте к члену иерархии обращаются по ключу в форме &[ЧастьИмени]. При использовании пути по ключу всегда используется символ & перед ключевыми частями имени члена.

Обязательные и необязательные аргументы

В справке MS по синтаксису функции КУБЗНАЧЕНИЕ указано, что обязательный аргумент один – Подключение. Формально это правильно, но… Если никаких аргументов более нет, а для куба не указана мера по умолчанию, то функция КУБЗНАЧЕНИЕ вернет пустоту (рис. 14). В модели данных Power Pivot меру по умолчанию, похоже, задать нельзя (для куба OLAP такая возможность есть). Так что, в общем случае нужно как минимум два аргумента – Подключение и Мера, чтобы было, что подсчитать и возвратить. Все остальные аргументы задают координаты куба (кортеж), для которых будет рассчитана мера.

Рис. 14. Одного аргумента в функции КУБЗНАЧЕНИЕ, как правило, мало

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

КУБЗНАЧЕНИЕ(подключение; мера[; элемент1] [; элемент2] …)

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

Мера – обязательный аргумент – текстовая строка, имя меры.

Элемент1, Элемент2, … – необязательные аргументы; каждый из них – имя среза или текстовая строка, описывающая элемент измерения или кортеж в кубе. Мера будет рассчитана на совокупности всех элементов и кортежей, перечисленных в аргументах.

Два метода записи формул

Формулы на основе КУБЗНАЧЕНИЕ могут быть длинными и трудными для понимания и записи. Используют два основных метода:

• ссылки на ячейки
• полный путь к элементу куба

Преобразование сводной таблицы в формулы использует первый метод (см. строку формул на рис. 3). Например,

=КУБЗНАЧЕНИЕ("ThisWorkbookDataModel";$B3;C$2;Срез_Category)

Обратите внимание на смешанный тип ссылок: $B3 и C$2. Такой подход позволяет протягивать формулу по строкам и столбцам таблицы отчета. В ячейках же B3 и C2 содержатся формулы КУБЭЛЕМЕНТ(), ссылающиеся на элементы модели данных, соответствующие заголовкам строк и столбцов таблицы:

Рис. 15. Формулы КУБЭЛЕМЕНТ() в заголовках строк и столбцов таблицы

Обратите внимание, что в таблице заголовки в ячейках В3 и С2 не могут быть представлены текстовыми строками, например, «2001» и «Total Sales». Если так, то КУБЭЛЕМЕНТ не поймет, что это элементы модели данных. Чтобы КУБЭЛЕМЕНТ справился c таким написанием, используйте второй метод записи формул, указывая полный путь к элементу куба/модели данных. При этом, часть пути может быть описана в виде ссылок на ячейки (в стиле, как на рис. 13). Формула в ячейке С3 примет вид:

Рис. 16. Формула КУБЭЛЕМЕНТ(), когда в заголовках строк и столбцов текст

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

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

Преобразование ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в КУБЗНАЧЕНИЕ

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

Синтаксис функций ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и КУБЗНАЧЕНИЕ немного отличается, поэтому надо удалить кое-что лишнее (удаляемое выделено).

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элемент1]; [поле2; элемент2]; …)

КУБЗНАЧЕНИЕ(подключение;[выражение_элемента1];[выражение_элемента2];…)

Вот пошаговое руководство по преобразованию:

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

Рис. 17. Наберите в ячейке Е10 " = ", кликните на ячейку С5

Шаг 2. Скопируйте весь текст между открывающей и закрывающей скобками в буфер.

Шаг 3. В другой ячейке введите =КУБЗНАЧЕНИЕ(«… Автозавершение предложит модель данных. Выберите.

Шаг 4. Вставьте текст из буфера.

Шаг 5. Отредактируйте текст. Функция…

…превращается в…

Шаг 6. Нажмите Enter.

Окно аргументов функции КУБЗНАЧЕНИЕ

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

Рис. 18. Окно Аргументы функции

По одному элементу за раз

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

…или что-то подобное работать не будет (здесь оператор | соответствует логическому ИЛИ). Но можно просто сложить две функции:

Рис. 19. Продажи красных И серебристых велосипедов

На самом деле всё не так плохо, и мы вернемся к этому вопросу ниже.

Функция КУБЭЛЕМЕНТ()

Возвращает элемент (координату по одному измерению) или кортеж (набор координат по разным измерениям) из куба. Синтаксис:

КУБЭЛЕМЕНТ(подключение; выражение_элемента [; подпись])

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

Выражение_элемента – обязательный аргумент; текстовая строка, описывающая элемент в кубе или кортеж.

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

С первыми двумя вы уже знакомы, а смысл третьего аргумента поясним на примере:

Рис. 20. Аргумент Подпись

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

КУБЭЛЕМЕНТ позволяет в аргументе Выражение_элемента указать кортеж. Последний берется в фигурные скобки:

Рис. 21. Аргумент Выражение_элемента в виде кортежа

Я не нашел объяснение такому синтаксису, и он отличается от стандартного для кортежей, который будет описан ниже.

Если аргумент Подпись отсутствует, в ячейке отражается последний элемент кортежа. На рис. 21 это было бы Bikes.

Если вам кажется, что составление таких формул отнимает много времени, попробуйте метод ссылок на ячейки. Функция КУБЭЛЕМЕНТ() допускает ссылку на диапазон ячеек:

Рис. 22. Аргумент Выражение_элемента в виде ссылки на диапазон ячеек

В качестве аргументов функций КУБ() можно использовать другие функции, возвращающие «правильный» тип данных (часто это текстовые строки). Например, формула…

…вернет продажи красных велосипедов.

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

…она вернет ошибку #ЗНАЧ! Это связано с тем, что измерение [Calendar].[CalendarYear] в последнем аргументе уже присутствует неявно в А5.

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

Семейство функций КУБ()

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

Рис. 23. Список аналитических функций Excel (функций кубов)

Функция КУБМНОЖ()

Возвращает набор элементов или набор кортежей для их последующего использования в других функциях КУБ(). На вход КУБМНОЖ подаются аргументы в виде ссылок на ячейки Excel, или текстовых строк. На выходе – массив. Синтаксис:

КУБМНОЖ(подключение;выражение_множества;[подпись];[порядок_сортировки];[сорт_по])

Выражение_множества – обязательный аргумент; текстовая строка, задающая условия, какие наборы элементов (кортежей) извлечь из куба. Если Выражение_множества содержит более 255 символов, что является предельной длиной для аргументов функции, КУБМНОЖ возвращает ошибку #ЗНАЧ!. Для использования текстовых строк длиной свыше 255 символов введите строку в ячейку, а затем используйте ссылку на ячейку в качестве аргумента.

Подпись – необязательный аргумент; текстовая строка, отображаемая в ячейке вместо подписи из куба. Поскольку функция возвращает массив, в ячейке ничего не отражается. Присвойте аргументу Подпись значение, чтобы не «потерять» ячейку с функцией КУБМНОЖ.

Порядок_сортировки – необязательный аргумент; тип сортировки; цифры от нуля до шести (в английской версии Excel могут использоваться также смысловые константы); значение по умолчанию 0; при сортировке кортежей выполняется сортировка по последнему элементу кортежа. Значения 1 и 2 требуют наличия аргумента Сорт_по. Если его нет, то функция вернет ошибку. Остальные значения не требуют аргумента Сорт_по, а если он присутствует, то игнорируется.

Рис. 24. Порядок сортировки элементов/кортежей, возвращаемых функцией КУБМНОЖ

Сорт_по – необязательный аргумент; текстовая строка – мера, по которой нужно выполнить сортировку.

Синтаксис функций КУБ() это синтаксис MDX

На мой взгляд, самое загадочное в всей этой истории – это синтаксис формул куба, который довольно сильно отличается от стиля, принятого в Excel. Это связано с тем, что формулы куба унаследовали язык запросов к многомерным данным MDX (MultiDimensional eXpressions), который давно используют разработчики OLAP-кубов.

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

[Products].[Category].[All].[Clothing]

У каждого измерения существует член по умолчанию, который используется в случае, если описание измерения в явном виде в запросе отсутствует. В роли элемента по умолчанию выступает элемент [All], который добавляется автоматически при создании измерения и содержит совокупные результаты по всем элементам измерения.

Я уже писал о двух символах, поддерживающих режим автозавершения: кавычки и точка (см. пояснения после рис. 12). Добавим еще три символа в эту коллекцию. Вспомните, что кортеж – совокупность элементов разных измерений, определяющая координаты точки в многомерном пространстве, для которой вычисляется мера. Например,

Элемент1а = [Products].[Color].[All].[Red]

Элемент1б = [Products].[Category].[All].[Bikes]

Кортеж1 = ([Products].[Color].[All].[Red],[Products].[Category].[All].[Bikes])

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

=КУБМНОЖ("ThisWorkbookDataModel";"([Products].[Color].[All].[Red]

…если вы продолжите точкой с запятой, автозавершение будет безмолвствовать (рис. 24а). Если же вы поставите запятую, автозавершение предложит варианты:

Рис. 24а. Запятая, разделяющая элементы кортежа; точка с запятой не работает

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

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

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

Рис. 24б. Аргумент Выражения_множества в виде набора кортежей

Формула также может  быть набрана из частей. Например так:

Рис. 24в. Аргумент Выражения_множества набран из фрагментов текста и ссылок на ячейки

Может быть запись второго аргумента – "{"&E15&","&E16&"}" – будет понятнее, если вместо конкатенации использовать функцию СЦЕПИТЬ:

Рис. 24г. Аргумент Выражения_множества на основе функции СЦЕПИТЬ

Ранее я описал особый синтаксис, который поддерживается функцией КУБЭЛЕМЕНТ (см. рис. 21). КУМНОЖ такой синтаксис не поддерживает…

Рис. 24д. Альтернативный (слева) и стандартный (справа) синтаксис кортежа в аргументе функции КУБЭЛЕМЕНТ

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

Здесь множество [Products].[Color].children возвращает названия всех цветов.

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

Вот полный список символов вызывающих автозавершение:

"    открывающие кавычки – в начале каждого аргумента; показывают доступные подключения, измерения/таблицы, набор мер;

.     точка – после закрывающей прямоугольной скобки; показывает следующие элементы иерархии;

(     открывающая круглая скобка – после: а) открывающих кавычек, б) открывающей фигурной скобки, в) запятой – в текстовой строке с многомерными выражениями; говорит о начале кортежа;

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

{     открывающая фигурная скобка – после открывающих кавычек в текстовой строке с многомерными выражениями; обозначает начало набора элементов или кортежей;

:     двоеточие – после закрывающей прямоугольной скобки в текстовой строке с многомерными выражениями; отделяет начальное значение от конечного, как в обычной ссылке Excel А2:А9.

КУБМНОЖ возвращает массив элементов на основе данных на листе Excel

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

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

=КУБМНОЖ("ThisWorkbookDataModel";"[Calendar].[Date].[All].

…автозавершение предложит варианты:

Рис. 25. Автозавершение покажет формат дат в кубе

Формат даты – "М/Д/ГГГГ". Теперь разместим на листе Excel столбец с интересующими нас датами (в любом удобно для нас формате, см. 1 на рис. 26). Поскольку КУБМНОЖ в качестве аргумента Выражение_множества требует текстовую строку, мы формируем таковую на основе конкатенации текстовых фрагментов и функции ТЕКСТ (2):

Вводим формулу в ячейку, как формулу массива. В ячейке хранится массив дат, а отображается текст, введенный нами в аргумент Подпись – множ (2). Любопытно, что диапазон А1:А6 должен быть или в одну строку или в один столбец. Прямоугольный диапазон возвращает ошибку.

Рис. 26. КУБМНОЖ позволяет сформировать массив элементов, передаваемых на ось для вычисления меры в функции КУБЗНАЧЕНИЕ

Формула с КУБЗНАЧЕНИЕ (3)…

…возвращает меру [Total Sales] для красных велосипедов, проданных в период, сформированный в ячейке В1.

И наконец, с помощью сводной таблицы (4) проверяем полученное значение.

Более того, функция КУБМНОЖ() дает возможность ввести первую и последнюю ячейки диапазона, разделив их двоеточием:

Формула вернет тот же массив дат с 1 по 6 июля 2001 г.

Функция КУБПОРЭЛЕМЕНТ()

Возвращает n-й элемент множества. Используется для возвращения одного или нескольких элементов в множестве, например, лучшего продавца или 10 лучших студентов. Синтаксис:

КУБПОРЭЛЕМЕНТ(подключение;выражение_множества;ранг;[подпись])

Выражение_множества – обязательный аргумент; текстовая строка, представляющая выражение множества, например, "[Products].[Category].[All].children". Здесь используется выражение MDX children, означающее все уникальные имена таблицы [Products], столбца [Category]. Выражение_множества также может быть функцией КУБМНОЖ или ссылкой на ячейку, содержащую функцию КУБМНОЖ. Например, КУБМНОЖ может возвращать массив категорий продуктов, отсортированных, по убыванию по объему продаж.

Ранг – обязательный аргумент; целое число. Если Ранг имеет значение 1, возвращается наибольшее значение, если Ранг имеет значение 2, возвращается второе по величине значение, и т.д. Чтобы возвратить 5 наибольших значений, вызовите функцию КУБПОРЭЛЕМЕНТ пять раз, указывая каждый раз новое значение Ранг: от 1 до 5. Если аргумент Выражение_множества  представлен строкой типа "[Products].[Category].[All].children", то массив упорядочен в алфавитном порядке.

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

Функция используется, например, для извлечения элементов какого-то измерения:

Рис. 27. Доступные цвета товаров

Ранг задан функцией СТРОКА(). Цвета выводятся в алфавитном порядке. Оказалось, что цветов 10, так что, начиная с 11-й строки формула возвращает ошибку #Н/Д.

Совместно использование КУБПОРЭЛЕМЕНТ и КУБМНОЖ

Роль функции КУБМНОЖ наилучшим образом раскрывается в связке с КУБПОРЭЛЕМЕНТ. Первая формирует массив на основе данных листа Excel или напрямую из куба, а вторая извлекает элементы массива в ранжированном порядке.

Функция КУБМНОЖ говорит кубу: «Верни все уникальные имена моделей из столбца [ModelName] таблицы [Products], и расположи их в массиве в порядке убывания по продажам [Total Sales]».

Рис. 28. Ранжированные продажи различных моделей

Проверяем вычисления с помощью обычной сводной таблицы:

Рис. 29. Проверочная сводная таблица

Ранжирование на основе кортежа

Задача усложняется, если нужно вывести ранжированный список, отфильтрованный не только по объему продаж, но и относящийся, например, к определенной категории продуктов или периоду времени. Повторим фрагмент приведенной выше формулы:

Идея в том, чтобы массив "[Products].[ModelName].children", получаемый из куба, оставить без изменений, а дополнительные условия фильтрации отразить в последнем пятом аргументе. Вместо ссылки на меру "[Measures].[Total Sales]", можно сослаться на кортеж, возвращаемый функцией КУБЭЛЕМЕНТ:

Здесь функция КУБЭЛЕМЕНТ говорит функции КУБМНОЖ: «Ранжируй массив по продажам аксессуаров». Итоговая формула в ячейке В4:

Эту формулу можно протянуть вдоль столбца до ячейки В13:

Рис. 30. ТОП-10 моделей аксессуаров по объему продаж

Ранжирование с использованием срезов

Формулам ранжирования, описанным в предыдущих разделах можно добавить гибкости, если использовать срезы. Excel допускает использование срезов и без сводных таблиц. Для создание таких срезов можно: 1) создать сводную таблицу; создать к ней срезы, а затем удалить сводную таблицу; 2) создать срез, пройдя по меню Вставка –> Фильтр –> Срез. Каждому срезу соответствует именованный диапазон, начинающийся со слова Срез_ (рис. 31). Хотя срез отражается в Диспетчере имен, соответствующего ему диапазона ячеек в книге Excel нет. К срезу можно обратиться по имени только внутри функций КУБ(). Обращение к срезу возвращает массив элементов (подробнее см. Блеск и нищета сводных таблиц, часть 13).

Рис. 31. Срезы в диспетчере имен

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

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

Создадим отчет, отбирающий ТОП-10 продаваемых моделей в выбранной стране, за один месяц. Добавим так же сравнение с продажами этих же моделей за предыдущий месяц:

Рис. 32. Ранжирование по продажам на основе срезов

Шаг 1. Поместим значения срезов в ячейки G18:G20. Для этого воспользуемся формулами типа

=КУБПОРЭЛЕМЕНТ("ThisWorkbookDataModel";Срез_Country;1)

Аргумент Срез_Country возвращает массив элементов среза, а функция КУБПОРЭЛЕМЕНТ возвращает первый в списке. Поскольку на срезе выбран один элемент, он и возвращается.

Шаг 2. В ячейках К4:К13 извлечем список моделей ранжированный по объему продаж в США за март 2004 года. Этот трюк вы видели ранее. Новый здесь фрагмент, отвечающий за фильтры:

КУБЭЛЕМЕНТ("ThisWorkbookDataModel";($G$17:$G$20)))

Он собирает набор из ячеек G17:G20, добавляя к значениям трех срезов меру [Total Sales]. Набор взят в круглые скобки. Если заменить ссылки на ячейки значениями, хранящимися в этих ячейках, функция КУБЭЛЕМЕНТ не позволит ввести формулу в ячейку К4 появится сообщение, что это не формула. Диапазон G17:G20 может иметь любую прямоугольную форму.

Шаг 3. В ячейке L3 располагаем название месяца из среза.

Шаг 4. В ячейке М3 располагаем название предыдущего месяца. И здесь еще один трюк с привлечением функции MDX lag(1), которая возвращает предыдущий к March элемент из столбца [MonthName]:

=КУБЭЛЕМЕНТ("ThisWorkbookDataModel";"[Calendar].[MonthName].[All].["&L3&"].lag(1)")

Шаг 5. В ячейке L4 прописываем формулу…

… и протягиваем ее на диапазон L4:M13.

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

Рис. 33. Формулы КУБ() дают сбой при выборе более одного элемента в срезе

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

Если хотя бы в одном срезе выбрано более одного элемента, произведение трех функций КУБЧИСЛОЭЛМНОЖ() будет более единицы, и в ячейке отобразится введенный текст. Если во всех срезах выбран один элемент, ячейка В22 останется пустой.

Функция КУБЧИСЛОЭЛМНОЖ()

Пожалуй, это самая простая и очевидная функция кубов. Возвращает число элементов в множестве. Синтаксис:

КУБЧИСЛОЭЛМНОЖ(множество)

Не требует указывать Подключение. Это означает, что аргумент Множество не может быть текстовой строкой (хотя справка MS утверждает именно это, пусть и с уточнениями). Аргумент Множество может быть именем среза, функцией КУБМНОЖ или ссылкой на ячейку, содержащую функцию КУБМНОЖ. На рис. 34 левая формула возвращает 9 – число элементов, выделенных на срезе; правая формула возвращает 10 – общее число элементов в множестве [Products].[Color].

Рис. 34. Функция КУБЧИСЛОЭЛМНОЖ()

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

Рис. 35. Кортежи, мера по которым равна нулю, не подсчитываются

Некоторые выражения MDX, используемые в формулах кубов

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

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

.members – похоже на .children, но возвращает также элемент [All] на первом месте и все элементы более глубоких уровней, если таковые имеются. Например, если создать в модели данных иерархию с именем Territory с двумя подуровнями Continent и Country, то [Territory].children вернет 3 элемента, а [Territory].Members – 10:

Рис. 36. Выражения .members и .children; слева фрагмент модели данных Power Pivot

Ссылку на соседние члены измерения без указания имени члена обеспечивают выражения .PrevMember и .NextMember. Чтобы обратиться к элементу, отстоящему на два назад, можно повторить выражение два раза .PrevMember.PrevMember, но удобнее применить более общее выражение Lag(2). Отрицательное число в скобках меняет направление отсчета членов измерения.

Чтобы определить первый или последний элемент того же уровня можно использовать .FirstSibling и .LastSibling. Для дочерних элементов подойдет .FirstChild и .LastChild. Чтобы получить родителя воспользуйтесь .Parent. Это выражение можно применить, если нужно найти долю продаж элемента в классе, например:

Рис. 37. Выражение .Parent позволяет находить вклад элемента в общие продажи, прибыль, …

Для того чтобы определить «дедушку» (родителя родителя), можно использовать выражение: .Parent.Parent. Для этого в кубе должна быть определена соответствующая иерархия.

Функция КУБЭЛЕМЕНТКИП()

Возвращает свойство ключевого показателя эффективности, КПЭ, и отображает его имя в ячейке. (В аббревиатуре русского названия функции используется другое наименование – ключевой индикатор производительности, КИП. В английском варианте CubeKPImember). Синтаксис:

КУБЭЛЕМЕНТКИП(подключение;имя_КПЭ;свойство_КПЭ;[подпись])

Имя_КПЭ – обязательный аргумент; текстовая строка, представляющая имя ключевого показателя эффективности в кубе (как создать КПЭ см. раздел KPI заметки Марк Мур. Power Pivot).

Свойство_КПЭ – обязательный аргумент; указывает, какое именно свойство KPI следует вернуть функции КУБЭЛЕМЕНТКИП (рис. 38). В модели данных из примера доступны только первые три свойства.

Рис. 38. Возможные свойства ключевого показателя эффективности

Подпись – необязательный аргумент; альтернативная текстовая строка. Имя по умолчанию формируется так: Имя_КПЭ + Свойство_КПЭ (имя свойства Значение КИП опускается):

Рис. 39. Имена, возвращаемые для меры [Profit Pct]

Чтобы использовать КПЭ в вычислениях, нужно разместить функцию КУБЭЛЕМЕНТКИП в аргументе «выражение_элемента» функции КУБЗНАЧЕНИЕ (рис. 40). Данные отчета на основе функций КУБ() проверены с помощью сводной таблицы. В правой части рис. 40 выделены поля KPI. И сводная таблица (ячейки F12:F15), и отчет (F4:F7) возвращают числа от 0 до 1. При этом сводная таблица выводит значки благодаря внутренним механизмам, а для замены чисел на «светофор» в отчете применяется условное форматирование.

Рис. 40. Отчет о продажах с использованием функции КУБЭЛЕМЕНТКИП

Функция КУБСВОЙСТВОЭЛЕМЕНТА()

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

КУБСВОЙСТВОЭЛЕМЕНТА(подключение; выражение_элемента; свойство)

Новым здесь является только третий элемент – имя свойства измерения. Если в процессе набора формулы автозавершение безмолвствует, значит свойство для данного измерения не определено. К сожалению, это единственная функция кубов, которая работает только с кубами OLAP (но не с моделями данных Power Pivot). Чтобы в кубе OLAP проверить, обладает ли измерение свойством, поместите измерение в сводную таблицу в область строк (или столбцов). Встаньте на одну из ячеек в этой области, и пройдите по меню Работа со сводными таблицами –> Анализ –> Средства OLAP –> Поля свойств (рис. 41).

Рис. 41. Проверка наличия свойств у измерения

Если у измерения есть свойства появится окно Выбор полей свойств для размерности (рис. 42). Если свойств у измерения нет появится сообщение об их отсутствии.

Рис. 42. Окно Выбор полей свойств для размерности

Если перенести свойства из левого окна в правое, они будут отражаться в сводной таблице. Но нас сейчас интересует лишь подтверждение того, что у измерения [Клиент] есть свойства. Теперь можно написать формулу:

Рис. 43. Автозавершение «увидело» свойства измерения

Функция КУБСВОЙСТВОЭЛЕМЕНТА может быть полезной для отображения на осях отчета неких измерений, связанных с базовым. Например, номера квартала по дате, e-mail по ID клиента, университета по имени игрока и т.п. С числовыми свойствами (в нашем примере это ИНН) можно выполнять все математические операции. Подробнее о свойствах измерений куба OLAP см. Павел Сухарев Блеск и нищета сводных таблиц, часть 5.

Как обойти ограничение Power Pivot и получить свойство элемента измерения

Хотя КУБСВОЙСТВОЭЛЕМЕНТА не поддерживает модели Power Pivot, можно эмулировать работу функции в этой среде. Попробуем на основе уникального ID клиента получить иные сведения о нем, хранящиеся в модели Power Pivot в таблице [Customers]. Для этого воспользуемся MDX функцией EXISTS. Она возвращает набор кортежей первого аргумента, которые встречаются во втором аргументе. Синтаксис:

Exists(Выражение1, Выражение2 [, Мера])

Выражение1 и Выражение2 – обязательные аргументы; многомерные выражения, возвращающее набор элементов (кортежей). Мера – необязательный аргумент; если он указан, то возвращаются только такие элементы (кортежи), для которых мера определена. Например, следующее выражение вернет клиентов, проживающих в Калифорнии и совершивших сделки в Интернете:

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

Рис. 44. Формула, эмулирующая работу КУБСВОЙСТВОЭЛЕМЕНТА в среде Power Pivot

Источники

Jon Acampora Tips & Tricks for Writing CUBEVALUE Formulas

Excel-файл с примерами я построил на основе модели из книги Роб Колли. Формулы DAX для Power Pivot (глава 15).

Обсуждение, можно ли задать в Power Pivot меру по умолчанию.

Павел Сухарев. Блеск и нищета сводных таблиц. Цикл статей в журнале Компьютер Пресс.

Статьи по формулам кубов на сайте powerpivotpro.com

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

Полина Трофимова, Алексей Шуленин. Введение в MDX. Цикл статей в журнале Компьютер Пресс.

Cube Functions in Microsoft Excel 2010

A CUBEMEMBERPROPERTY Equivalent With PowerPivot

Актуальность ссылок проверена 29 июня 2019 г.

Можно особо не усложнять задачу и просто написать «кв.м.».

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

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

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

сначала ставим букву «М» в ячейку. Затем нажимаем на вкладку «Вставка» и ищем там графу «Символы». В открывшемся окне выбираем символ квадрата. Таким образом получится квадратный метр.

Пишем в ячейке М2. Выделяем мышкой цифру 2. Вызываем правой клавишей мышки контекстное меню — «Формат ячеек» — в области «Видоизменение» помечаем галочкой пункт «надстрочный», нажимаем ОК. Двойка превращается в верхний индекс .

Если вам нужно при работе с Microsoft Excel написать квадратный метр (или же нужно что — то возвести в квадрат), то сделать это можно одним из следующих способов:

• самый простой вариант — это написать «М» и вставить символ «квадрата» (делается это через вкладку «Вставка» > «Символы», где из списка нужно выбрать вторую степень или же квадрат);
• другой способ — написать сначала «м2», а потом перейти к следующим действиям: после выделения двойки (цифры 2) нажимаем на правую кнопку мышки и появляется меню, далее переходим в окно формата ячеек и в разделе «Видоизменение» просто выбираем второй пункт (надстрочный) и жмем на ок.

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

Выберите нужную ячейку, затем нажмите F2.

В ячейке пишем следующее: «м2», затем выделяем двойку и щелкаем правой кнопкой мыши. Появится список команд, из которых мы выберем «Формат ячеек».

Далее всплывет вот такое окошко:

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

Сделать желаемое можно двумя способами:

1. При помощи вставки специальных символов.
2. При помощи оформления двоечки как «надстрочный» («Формат ячеек».

Но я бы посоветовал, если часто нужно с квадратными метрами работать, настроить автозамену (указать, что нужно «м2» заменять на «м²» — можете прямо отсюда и скопировать, кстати, уже готовое), тогда всё будет вообще легко и просто.

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

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

Чтобы значок метр квадратный изобразить в ячейке Microsoft Office Excel, можно зайти в Вставка, далее в Символы и там выбрать нужный символ. Посмотрите на картинке ниже, станет сразу понятно:

Чтобы написать квадратный метр, использую наиболее простой для меня способ: пишу букву «м», затем во вкладке «вставка» выбираю «символ», нажимаю на него и нахожу значок «2» (маленькая двоечка в верхнем правом углу).

Да, недавно настроила себе автозамену, как посоветовали здесь же, и теперь вообще нет никаких проблем.

Время, в Excel является числом, точнее, десятичной дробью меньше единицы. (Соответственно дата — число, больше единицы. А дата с временем — это сумма этих чисел.).

Что бы данное число смотрелось в клетке как Дата, или Время, или Дата с Временем — для этого необходимо задать определенный формат с свойствах ячейки (Втор. кл. мыши по ячейке — пункт «Формат Ячейки»). Следственно, над любой датой и над любым временем можно производить операции сложения и вычитания. Так же надо помнить, что в Excel есть функция (ВРЕМЯ()), которая преобразовывает три значения (часы,минуты,секунды) в специальную десятичную дробь, которая, по сути, является временем «чч:мм:сс», если изменить формат ячейки на «Время-13:30:55».

1. Пусть в ячейке A1 у нас есть время «23:23:59«, тогда
2. Пусть, клетках B1,C1,D1 у нас будет количество часов,минут,секунд, (целые числа) которое мы хотим добавить к нашему времени.
3. Для получения результата, запишем в клетке E1 формулу: =A1+ВРЕМЯ(B1;C1;D1)

График можно перенести как картинку обычным копированием.

Для того, что бы перенести график из EXCEL в Power Point. В Excel выделить график, выполнить «копировать», открываем Power Point, открываем нужный слайд, выполняем операцию «вставить».

Сохранить можно множеством способов:

1 — Горячие клавиши. Shift+F12 — сохранить. F12 — сохранить как.

Contrl + S — Сохранить.

2 — Нажав на клавишу альт и управляя стрелочками, выбрать нужное меню затем нажать копку Enter.

Можно указать текстовый формат ячейки,а значение минут получить по формуле (если информация в ячейке D4):

=ЗНАЧЕН(ЛЕВСИМВ(D4;НАЙТИ(«:»;D4)-1)

Для получения количества секунд подойдет формула:

=ЗНАЧЕН(ПРАВСИМВ(D4;ДЛСТР(D4)-НАЙТИ(«:»;D4))

не забывайте переводить секунды в минуты делением на 60

В общем-то это просто делается. Когда копируете в буфер обмена содержимое ячейки (не важно с помощью меню, ленты или просто CTRL+C), то потом, когда в другую ячейку надо будет вставить только данные надо кликнуть по стрелочке расположенной рядом с кнопочкой в меню «Вставить». Там выпадет менюшка с запросом чтот именно вы хотите вставить. Если нет прямого указания (например, есть только иконки разные), то копайте глубже через пункт «Специальная вставка». Дальше думаю сами разберетесь.

Лично мне там нравится опция «Вставить ширину колонок». Часто, когда копируешь блок в новое место, в этом месте ширина колонок остается оригинальной, что сильно мешает восприятию информации. Так вот когда скопируешь туда ширины исходных колонок, все становится на свои места.

Как в Эксель поставить метры кубические?

Как в excel написать м2. нажимаешь F2 в ячейке, пишешь м2, затем выделаешь двойку, щелкаешь по ней правой кнопкой мыши и выбираешь Формат ячеек. В открывшемся окошке идешь на закладку Шрифт и ставишь опцию «Надстрочный».

Как поставить в кубе в Эксель?

1. Щелкните внутри ячейки на листе.
2. Введите в ячейку =N^2, где N — это число, которое нужно возвести в квадрат. Например, чтобы вставить в ячейку A1 квадрат числа 5, введите в нее =5^2.
3. Нажмите клавишу ВВОД, чтобы получить результат.

Как рассчитать площадь в Excel?

Вы можете легко рассчитать его площадь по этой формуле: =(C3+C4)/2*(B4-B3). 2. Затем вы можете перетащить маркер автозаполнения ячейки формулы вниз, чтобы вычислить площади других трапеций.

Как написать число в квадрате на телефоне?

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

Как написать в Excel квадрат и куб числа?

1. Выделите ячейку, где необходимо произвести вычисления.
2. Поставьте знак равенства («=»), чтобы программа поняла, что вписывается формула.
3. Введите число, которое необходимо возвести в степень. .
4. Установите символ степени «^». .
5. Введите степень, в которую хотите возвести число, то есть 4.

Как писать м2?

Квадратный метр (русское обозначение: м², международное: m²) — единица измерения площади в Международной системе единиц (СИ), а также в системах МТС и МКГСС. 1 м² равен площади квадрата со стороной в 1 метр.

Как поставить знак гривны в Excel?

1. Выделить все ячейки в колонке, в которой будешь вписывать цифры. .
2. Правой кнопкой на выделенном, выбрать Format cell. .
3. Выбрать вкладку Number (она может быть выбрана по-умолчанию)
4. В списке Category выбрать Custom. .
5. Type — в поле Type вписываем следующее: # ##0 «грн.»

Как сделать надстрочный знак в Excel?

1. Дважды кликаете в ячейку;
2. Выделяете текст, который надо сделать надстрочным;
3. Кликаете на нем правой кнопкой мыши и выбираете «Формат ячеек»;
4. Ставите галочку «Надстрочный» и жмете «ОК»

Как в Excel поставить верхний и нижний индекс?

На вкладке Главная в группе Шрифт нажмите кнопку Параметры шрифта диалогового окна. Московская обл. Нажмите сочетание клавиш CTRL+1. В разделе эффекты , установите флажок Надстрочный или подстрочный и нажмите кнопку OK.

Как поставить цифру сверху в Экселе?

Нажмите на клавиатуре горячие клавиши Ctrl+1. Появится окно «Формат ячеек». В нем вам необходимо в области «Видоизменение» установить отметку напротив пункта «Надстрочный». Нажмите ОК.

Как рассчитать объем в Excel?

1. В ячейку B2 запишем формулу вычисления объема сферы в одну строку: =(4/3)*ПИ()*A2^3 (A2 – это ссылка на ячейку).
2. В ячейке A2 будем вводить разные радиусы и после каждого ввода в ячейке B2 будем получать результат вычисления объема сфер соответствующих своим радиусам.

Как вычислить формулу в Эксель?

1. Выделите ячейку, которую нужно вычислить. .
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Вычислить формулу .
3. Нажмите кнопку Вычислить , чтобы проверить значение подчеркнутой ссылки. .
4. Продолжайте этот процесс, пока не будут вычислены все части формулы.

Как найти площадь?

Когда известно значение длины и ширины фигуры

Для вычисления необходимо умножить их друг на друга. S = a * b, где S — площадь; a, b — длина и ширина.

6 простых способов сделать автоматическую нумерацию в Excel — инструкция и видео

Основатель Checkroi.ru, делаю онлайн-образование понятнее.

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

Как в Word поставить квадратный метр или метр кубический на клавиатуре: как сделать значок, как напечатать его и правильно указать значок | Все о программе Word

Совет: Если после обозначения квадратных или кубических метров нет текста, кликните левой кнопкой мышки возле этого обозначения (сразу после него), чтобы отменить выделение, и еще раз нажмите кнопку “Надстрочный знак”, поставьте точку, запятую или пробел, чтобы далее продолжить набирать обычный текст.

Как рассчитывается квадрат числа

Как мы помним из школьной программы, квадрат числа – это число, помноженное на само себя. В Excel для возведения числа в квадрат, разумеется, используется этот же принцип. И для решения этой задачи можно пойти двумя путями: воспользоваться формулой, включающей специальный символ степени “^”, либо применить функцию СТЕПЕНЬ.

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

Как в эксель написать метр кубический

• “Число” – аргумент, который может быть представлен двумя способами. Можно прописать конкретное число, которое требуется возвести в степень, либо указать адрес ячейки с требуемым числом.
• “Степень” – аргумент, указывающий степень, в которую будет возводиться наше число. Так как мы рассматриваем возведение числа в квадрат, то указываем значение аргумента, равное цифре 2.

Другой вариант для вставки кубического метра ( m 3 ) в Excel выполняется копипастом. Да, просто скопируйте и вставьте его в Excel или куда угодно. Выберите любой из этих символов :

Функция КУБЗНАЧЕНИЕ()

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

Вам не обязательно преобразовывать готовую сводную таблицу. Вы можете написать любую формулу куба «с нуля». Например, в ячейку С10 введена следующая формула (рис. 4):

Рис. 4. Функция КУБЗНАЧЕНИЕ() в ячейке С10 возвращает продажи велосипедов за все годы, как и в сводной таблице

Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Как в word поставить квадратный метр — IT Журнал

• Написать число и поставить после него знак ^ (сочетание клавиш SHIFT + 6 на английской раскладке клавиатуры) после чего написать нужную вам степень. Выглядеть это будет так 54^3. Знак ^ — общепринятое обозначение степени;
• Если редактор позволяет ставить степень ( например Word), то воспользоваться этим, и после этого скопировать туда, куда вам нужно;

У многих пользователей старая версия Word, даже 2003 или 2007. Но нет причин огорчаться, поскольку вы все равно справитесь с задачей. Нужно выделить текст, которой должен стать степенью, и нажать на него правой кнопкой. В появившемся меню нажать на вкладку «Шрифт».

Перевод единиц измерения в Excel с помощью функции ПРЕОБР

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

Скачать заметку в формате Word или pdf, также доступны примеры в формате Excel2013

Например, формула =ПРЕОБР(A2; » mi » ; » m » ) переводит мили (стандартные / уставные) в метры.

Функция ПРЕОБР переводит единицы измерения в следующих категориях: вес и масса, расстояние, время, давление, сила, энергия, мощность, магнетизм, температура, объем (жидкостей), биты/байты, скорость, префиксы – степени десятки и двойки (рис. 1).

Рис. 1. Некоторые примеры использования функции ПРЕОБР

• Регистр значений аргументов играет роль (в ячейке С3 введено » Nmi » ; если ввести » nmi » , функция вернет ошибку.
• Исходные и конечные единицы измерения могут содержать множители/префиксы (в ячейке С4 использован аргумент » cm » , составленный из двух частей: » c » – префикс санти и » m » – метр)
• При вводе формулы появляется всплывающая подсказка, облегчающая выбор аргументов (рис. 2).
• Для отображения единиц измерения в столбцах А и В я использовал возможности форматирования чисел (рис. 3); например, в ячейке А5 содержится значение 1, а формат ячейки – 0 » фут « (значение в кавычках отображается в ячейке, но не влияет на значение).

Рис. 3. Отображение единиц измерения с помощью форматирования

Любопытно, что в подсказке MS содержится ошибка (рис. 4). Вместо «морская миля» следует читать «морской узел»…

Рис. 4. Опечатка; вместо «морская миля» следует читать «морской узел»

В Excel имеется также ряд функций, преобразующих какую-то одну единицу измерения в другую:

Возведение числа в квадрат в Эксель

Как ни странно, огромное количество пользователей ПК не знает как ввести правильный знак квадратного метра на клавиатуре и пишут м2, когда нужно написать m 2 . В статье мы разберем, как поставить квадратный метр в Word.