Как вычислить куб числа в excel - Word и Excel - помощь в работе с программами

Download PC Repair Tool to quickly find & fix Windows errors automatically

Finding cubes and cube roots have plenty of real-life applications. They are essential as a part of many mathematical functions. Even more, they are used for estimating the volume of vessels. If you wish to find the cube and cube root of numbers in a cell or a range of cells in Excel, please read through this article.

There is no specific known function for finding the cube or cube root in Excel, so you could use the exponential function instead. That is seemingly the easiest option.

The syntax of the formula for finding the cube of a number in Excel is as follows:

=<first cell with number>^3

Where, <first cell with number> is the first cell in the range of cells from which you start counting the cube for the range of cells.

Eg. Let us consider an example where you have a range of numbers across column A from cell A3 to cell A11. You need the cube of these numbers in the respective columns across column B, from cells B3 to B11. To do so, enter the following formula in cell B3:

=A3^3

When you hit Enter, Excel will return the value of the cube of the number in cell A3 to the cell B3. Then, you can use the Fill function to pull the formula down to cell B11. To do so, click on any cell outside cell B3 (which contains the formula) and then back on it.

This will highlight the Fill function which is represented by a small dot at the right-bottom corner of the selected cell. Now, hover your mouse over that dot, click it, and without releasing the click, pull the formula down to cell B11.

How to find the Cube Root in Excel

The syntax of the formula for finding the cube of a number in Excel is as follows:

=<first cell with number>^(1/3)

Where, <first cell with number> is the first cell in the range of cells from which you start counting the cube for the range of cells.

Eg. Let us consider the previous example and add that you need the list of cube roots in column C, from C3 to C11. So, the formula you would have to enter the following formula in cell C3:

=A3^(1/3)

Hit Enter to get the cube root for the number in cell A3 in cell C3 and then use the Fill function to pull the formula down till cell C11.

Read: How to find the Square and Square Root of a number in Excel.

Hope it helps!

Ezoic

Karan is a B.Tech, with several years of experience as an IT Analyst. He is a passionate Windows user who loves troubleshooting problems and writing about Microsoft technologies.

Источник

Часто пользователям необходимо возвести число в степень. Как правильно сделать это с помощью «Экселя»?

В этой статье мы попробуем разобраться с популярными вопросами пользователей и дать инструкцию по правильному использованию системы. MS Office Excel позволяет выполнять ряд математических функций: от самых простых до сложнейших. Это универсальное программное обеспечение рассчитано на все случаи жизни.

Как возвести в степень в Excel?

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

Число «1» в любой степени будет оставаться «1».
Число «0» в любой степени будет оставаться «0».
Любое число, возведенное в нулевую степень, равняется единице.
Любое значение «А» в степени «1» будет равняться «А».

Примеры в Excel:

Вариант №1. Используем символ «^»

Стандартный и самый простой вариант – использовать значок «^», который получается при нажатии Shift+6 при английской раскладке клавиатуры.

ВАЖНО!

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

Мы возвели 8 в «квадрат» (т.е. ко второй степени) и получили в ячейке «А2» результат вычисления.

Вариант №2. С использованием функции

В Microsoft Office Excel есть удобная функция «СТЕПЕНЬ», которую вы можете активизировать для осуществления простых и сложных математических расчетов.

Функция выглядит следующим образом:

=СТЕПЕНЬ(число;степень)

ВНИМАНИЕ!

Цифры для этой формулы указываются без пробелов и других знаков.
Первая цифра – значение «число». Это основание (т.е. цифра, которую мы возводим). Microsoft Office Excel допускает введение любого вещественного числа.
Вторая цифра – значение «степень». Это показатель, в который мы возводим первую цифру.
Значения обоих параметров могут быть меньше нуля (т.е. со знаком «-»).

Формула возведения в степень в Excel

Примеры использования функции СТЕПЕНЬ().

С использованием мастера функций:

Запускаем мастера функций с помощью комбинации горячих клавиш SHIFT+F3 или жмем на кнопку в начале строки формул «fx» (вставить функцию). Из выпадающего списка «Категория» выбираем «Математические», а в нижнем поле указываем на нужную нам функцию и жмем ОК.

В появившимся диалоговом окне заполняем поля аргументами. К примеру, нам нужно возвести число «2» в степень «3». Тогда в первое поле вводим «2», а во второе — «3».

Нажимаем кнопку «ОК» и получаем в ячейке, в которую вводили формулу, необходимое нам значение. Для данной ситуации это «2» в «кубе», т.е. 2*2*2 = 8. Программа подсчитала все верно и выдала вам результат.

Если лишние клики вы считаете сомнительным удовольствием, предлагаем еще один простой вариант.

Ввод функции вручную:

В строке формул ставим знак «=» и начинаем вводить название функции. Обычно достаточно написать «сте» — и система сама догадается предложить вам полезную опцию.

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

После этого нажимаете на «Enter» — и в ячейке появляется высчитанное значение 8.

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

Корень в степени в Excel

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

Перейдите по закладке «Формулы». В разделе инструментов «Библиотека функций» щелкаем по инструменту «Математические». А из выпадающего списка указываем на опцию «КОРЕНЬ».

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

ВНИМАНИЕ! Если нам нужно узнать корень в степени в Excel то мы не используем функцию =КОРЕНЬ(). Вспомним теорию из математики:

«Корнем n-ой степени от числа а называется число b, n-ая степень которого равна а», то есть:
ⁿ√a = b; bⁿ = a.

«А корень n-ой степени из числа а будет равен возведению к степени этого же числа а на 1/n», то есть:
ⁿ√a = a^1/n.

Из этого следует чтобы вычислить математическую формулу корня в n-ой степени например:

⁵√32 = 2

В Excel следует записывать через такую формулу: =32^(1/5), то есть: =a^(1/n)- где a-число; n-степень:

Или через такую функцию: =СТЕПЕНЬ(32;1/5)

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

Как в Excel написать число в степени?

Часто вам важно, чтобы число в степени корректно отображалось при распечатывании и красиво выглядело в таблице. Как в Excel написать число в степени? Здесь необходимо использовать вкладку «Формат ячеек». В нашем примере мы записали цифру «3» в ячейку «А1», которую нужно представить в -2 степени.

Последовательность действий следующая:

Правой кнопкой мыши щелкаем по ячейке с числом и выбираем из выскакивающего меню вкладку «Формат ячеек». Если не получилось – находим вкладку «Формат ячеек» в верхней панели или жмем комбинацию клавиш CTRL+1.

В появившемся меню выбираем вкладку «Число» и задаем формат для ячейки «Текстовый». Жмем ОК.

В ячейке A1 вводим рядом с числом «3» число «-2» и выделяем его.

Снова вызываем формат ячеек (например, комбинацией горячих клавиш CTRL+1) и теперь для нас только доступна вкладка «Шрифт», в которой отмечаем галочкой опцию «надстрочный». И жмем ОК.

В результате должно отображаться следующее значение:

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

Источник

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

Метод 1: использование функции КОРЕНЬ
Метод 2: нахождение корня путем возведения в степень
Заключение

Метод 1: использование функции КОРЕНЬ

Множество операций в программе реализуется с помощью специальных функций, и извлечение корня – не исключение. В данном случае нам нужен оператор КОРЕНЬ, формула которого выглядит так:

=КОРЕНЬ(число)

Для выполнения расчета достаточно написать данную формулу в любой свободной ячейке (или в строке формул, предварительно выбрав нужную ячейку). Слово “число”, соответственно, меняем на числовое значение, корень которого нужно найти.

Когда все готово, щелкаем клавишу Enter и получаем требуемый результат.

Вместо числа можно, также, указать адрес ячейки, содержащей число.

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

Вставка формулы через Мастер функций

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

Выбрав ячейку, в которой мы хотим выполнить расчеты, щелкаем по кнопке “Вставить функцию” (fx).
В окне мастера функций выбираем категорию “Математические”, отмечаем оператор “КОРЕНЬ” и щелкаем OK.
Перед нами появится окно с аргументом функции для заполнения. Как и при ручном написании формулы можно указать конкретное число или ссылку на ячейку, содержащую числовое значение. При этом, координаты можно указать, напечатав их с помощью клавиатуры или просто кликнуть по нужному элементу в самой таблице.
Щелкнув кнопку OK мы получим результат в ячейке с функцией.

Вставка функции через вкладку “Формулы

Встаем в ячейку, в которой хотим произвести вычисления. Щелкаем по кнопке “Математические” в разделе инструментов “Библиотека функций”.
Пролистав предложенный перечень находим и кликаем по пункту “КОРЕНЬ”.
На экране отобразится уже знакомое окно с аргументом, который нужно заполнить, после чего нажать кнопку OK.

Метод 2: нахождение корня путем возведения в степень

Описанный выше метод позволяет с легкостью извлекать квадратный корень из числа, однако, для кубического уже не подходит. Но и эта задача в Excel реализуема. Для этого числовое значение нужно возвести в дробную степень, где в числителе будет стоять “1”, а в знаменателе – цифра, означающая степень корня (n).

В общем виде, формула выглядит так:

=(Число)^(1/n)

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

Для начала давайте рассмотрим формулу для извлечения квадратного корня. Она выглядит следующим образом: =(Число)^(1/2).

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

=(Число)^(1/3)

Допустим, нам нужно извлечь кубический корень из числа 27. В этом случае нужно записать в ячейке такую формулу: =27^(1/3).

Нажав Enter, получаем результат вычислений.

Аналогично работе с функцией КОРЕНЬ, вместо конкретного числа можно указать ссылку на ячейку.

Заключение

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

Источник

Содержание

Вычислите значения квадратов и кубов первых 10 чисел excel
Как рассчитывается квадрат числа
Формула для расчета квадрата числа
Функция СТЕПЕНЬ для возведения числа в квадрат
Заключение
Процедура возведения в квадрат
Способ 1: возведение с помощью формулы
Способ 2: использование функции СТЕПЕНЬ
Как рассчитывается квадрат числа
Формула для расчета квадрата числа
Функция СТЕПЕНЬ для возведения числа в квадрат
Заключение
Описание
Синтаксис
Замечания
Пример
Нахождение суммы квадратов для нескольких ячеек
Нахождение суммы квадратов всего для нескольких ячеек

Вычислите значения квадратов и кубов первых 10 чисел excel

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

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

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

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

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

Формула для расчета квадрата числа

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

Сама формула выглядит следующим образом: =n^2.

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

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

Для начала определяемся с ячейкой книги, в которой будет отображаться результат вычислений, и отмечаем ее левой кнопкой мыши. Затем пишем в ней формулу, не забывая в самом начале поставить знак “равно” (“=”) . Например, формула “=7^2″ означает, что мы хотим возвести в квадрат число 7. Формулу, кстати, можно прописать и в строке формул, предварительно выделив нужную ячейку.
После того, как формула набрана, щелкаем клавишу Enter на клавиатуре, чтобы получить требуемый результат.

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

Выбираем ячейку, где будет отображаться результат, и пишем в ней формулу. Как обычно, в начале ставим “=”. Затем щелкаем по ячейке, содержащей число, квадрат которого требуется получить (в нашем случае – это ячейка B3). Далее добавляем символ степени и цифру 2, означающую возведение во вторую степень. В итоге формула выглядит так: “=B3^2“.
После этого нажимаем Enter для вывода результата в выбранной ячейке с формулой.

Примечание: данная формула применима не только для возведения числа в квадрат, но и в другие степени. В этом случае вместо цифры 2 мы пишем другую желаемую цифру. Например, формула “=4^3” возведет число 4 в третью степень или, другими словами, в куб.

Функция СТЕПЕНЬ для возведения числа в квадрат

В данном случае для нахождения квадрата числа нам поможет специальная функция под названием СТЕПЕНЬ. Эта функция относится к категории математических операторов и выполняет задачу по возведению указанного числа в заданную степень.

Формула данного оператора выглядит так: =СТЕПЕНЬ(число;степень).

Как мы видим, в данной формуле присутствует два аргумента: число и степень.

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

Давайте разберем применение функции СТЕПЕНЬ на примерах:

Способ 1. Указываем в качестве значения аргумента «Число» конкретную цифру

Способ 2. Указываем в качестве значения аргумента «Число» адрес ячейки с числом

Теперь у нас уже есть конкретное числовое значение в отдельно ячейке (в нашем случае – B3). Так же, как и в первом способе, выделяем ячейку, куда будет выводиться результат, нажимаем на кнопку “Вставить функцию” и выбираем оператор “СТЕПЕНЬ” в категории “Математические”.
В отличие от первого способа, теперь вместо указания конкретного числа в поле “Число” указываем адрес ячейки, содержащей нужное число. Для этого кликаем сначала по полю аргумента, затем – по нужной ячейке. Значение поля “Степень” так же равно 2.
Далее нажимаем кнопку OK и получаем результат, как и в первом способе, в ячейке с формулой.

Примечание: Также, как и в случае использования формулы для расчета квадрата числа, функцию СТЕПЕНЬ можно применять для возведения числа в любую степень, указав в значении аргумента “Степень” нужную цифру. Например, чтобы возвести число в куб, пишем цифру 3.

Далее жмем Enter и значение куба указанного числа появится ячейке с фукнцией.

Заключение

Возведение числа в квадрат – пожалуй, самое популярное математическое действие среди всех вычислений, связанных с расчетами различных степеней числовых значений. В Microsoft Excel данное действие можно выполнять двумя способами: с помощью специальной формулы или используя оператор под названием СТЕПЕНЬ.

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

Процедура возведения в квадрат

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

Способ 1: возведение с помощью формулы

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

Общий вид формулы для возведения в квадрат следующий:

В ней вместо «n» нужно подставить конкретное число, которое следует возвести в квадрат.

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

«=»

«^»

«2»

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

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

Способ 2: использование функции СТЕПЕНЬ

Также для возведения числа в квадрат можно использовать встроенную функцию Excel СТЕПЕНЬ. Данный оператор входит в категорию математических функций и его задачей является возведение определенного числового значения в указанную степень. Синтаксис у функции следующий:

Аргумент «Число» может представлять собой конкретное число или ссылку на элемент листа, где оно расположено.

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

Теперь посмотрим на конкретном примере, как производится возведение в квадрат с помощью оператора СТЕПЕНЬ.

«Вставить функцию»

Происходит запуск окошка Мастера функций. Производим переход в нем в категорию «Математические». В раскрывшемся перечне выбираем значение «СТЕПЕНЬ». Затем следует щелкнуть по кнопке «OK».

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

В поле «Число» указываем числовое значение, которое следует возвести в квадрат.

В поле «Степень» указываем цифру «2», так как нам нужно произвести возведение именно в квадрат.

После этого производим щелчок по кнопке «OK» в нижней области окна.

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

«Число»

В поле «Степень», как и в прошлый раз, ставим цифру «2», после чего щелкаем по кнопке «OK».

Как видим, в Экселе существует два способа возведения числа в квадрат: с помощью символа «^» и с применением встроенной функции. Оба этих варианта также можно применять для возведения числа в любую другую степень, но для вычисления квадрата в обоих случаях нужно указать степень «2». Каждый из указанных способов может производить вычисления, как непосредственно из указанного числового значения, так применив в данных целях ссылку на ячейку, в которой оно располагается. По большому счету, данные варианты практически равнозначны по функциональности, поэтому трудно сказать, какой из них лучше. Тут скорее дело привычки и приоритетов каждого отдельного пользователя, но значительно чаще все-таки используется формула с символом «^».

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

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

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

Формула для расчета квадрата числа

Сама формула выглядит следующим образом: =n^2.

Для начала определяемся с ячейкой книги, в которой будет отображаться результат вычислений, и отмечаем ее левой кнопкой мыши. Затем пишем в ней формулу, не забывая в самом начале поставить знак “равно” (“=”) . Например, формула “=7^2″ означает, что мы хотим возвести в квадрат число 7. Формулу, кстати, можно прописать и в строке формул, предварительно выделив нужную ячейку.
После того, как формула набрана, щелкаем клавишу Enter на клавиатуре, чтобы получить требуемый результат.

Выбираем ячейку, где будет отображаться результат, и пишем в ней формулу. Как обычно, в начале ставим “=”. Затем щелкаем по ячейке, содержащей число, квадрат которого требуется получить (в нашем случае – это ячейка B3). Далее добавляем символ степени и цифру 2, означающую возведение во вторую степень. В итоге формула выглядит так: “=B3^2“.
После этого нажимаем Enter для вывода результата в выбранной ячейке с формулой.

Функция СТЕПЕНЬ для возведения числа в квадрат

Формула данного оператора выглядит так: =СТЕПЕНЬ(число;степень).

Как мы видим, в данной формуле присутствует два аргумента: число и степень.

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

Давайте разберем применение функции СТЕПЕНЬ на примерах:

Способ 1. Указываем в качестве значения аргумента «Число» конкретную цифру

Выбираем ячейку, в которой будем производить расчеты. Затем кликаем по кнопке “Вставить функцию” (с левой стороны от строки формул).
Откроется окно Мастера функций. Кликаем по текущей категории и выбираем в открывшемся перечне строку “Математические”.
Теперь нам нужно в предложенном списке функций найти и кликнуть по оператору “СТЕПЕНЬ”. Далее подтверждаем действие нажатием OK.
Перед нами откроется окно с настройками двух аргументов функции, которое содержит, соответственно, два поля для ввода информации, после заполнения которых жмем кнопку OK.
в поле “Число” пишем числовое значение, которое требуется возвести в степень
в поле “Степень” указываем нужную нам степень, в нашем случае – 2.
В результате проделанных действий мы получим квадрат заданного числа в выбранной ячейке.

Способ 2. Указываем в качестве значения аргумента «Число» адрес ячейки с числом

Теперь у нас уже есть конкретное числовое значение в отдельно ячейке (в нашем случае – B3). Так же, как и в первом способе, выделяем ячейку, куда будет выводиться результат, нажимаем на кнопку “Вставить функцию” и выбираем оператор “СТЕПЕНЬ” в категории “Математические”.
В отличие от первого способа, теперь вместо указания конкретного числа в поле “Число” указываем адрес ячейки, содержащей нужное число. Для этого кликаем сначала по полю аргумента, затем – по нужной ячейке. Значение поля “Степень” так же равно 2.
Далее нажимаем кнопку OK и получаем результат, как и в первом способе, в ячейке с формулой.

Далее жмем Enter и значение куба указанного числа появится ячейке с фукнцией.

Заключение

В этой статье описаны синтаксис формулы и использование функции СУММКВ в Microsoft Excel.

Описание

Возвращает сумму квадратов аргументов.

Синтаксис

Аргументы функции СУММКВ описаны ниже.

Число1, число2. Аргумент «число1» является обязательным, последующие числа необязательные. От 1 до 255 аргументов, для которых вычисляется сумма квадратов. Вместо аргументов, разделенных точкой с запятой, можно использовать один массив или ссылку на массив.

Замечания

Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.

Учитываются числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.

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

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

Пример

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

Как в программе Эксель (Excel) сделать таблицу квадратов натуральных чисел?

Вот как она выглядит:

Какая формула должна использоваться?

Для выполнения задачи можно использовать функцию СЦЕПИТЬ. Вот пошаговое выполнение задания:

1) Создаем первую строку и первую колонку таблицы, заполняя её числами от 0 до 9. Получили каркас таблицы:

2) Заполняем ячейку «B2» используя формулу:

3) Закрепляем переменную «В1» c помощью символа «$» и растягиваем колонку «B» от ячейки «B2»:

4) Далее открепляем значение колонки «B» и закрепляем значение ряда «A» для каждого ряда таблицы, после чего растягиваем сами ряды:

5) Наслаждаемся результатом:

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

Формула будет такой

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

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

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

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

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

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

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

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

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

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

Чтобы уметь использовать макросы в excel нужно уметь программировать. Если вы программировать не умеете, то и макрос скорей всего написать не сможете.

Если вы берете макрос с интернета, то скорей всего там будет подробная инструкция что и куда надо вставить. Лично я не пользуюсь ими. Мне, как обычному пользователю, хватает стандартных команд в виде IF, SUMM и т.д.

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

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

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

Нахождение суммы квадратов для нескольких ячеек

Начните новый столбец в любом месте электронной таблицы Excel и пометьте его. Здесь мы выведем решение наших квадратов. Квадраты не обязательно должны быть рядом друг с другом, как и секция вывода; это может быть где угодно на странице.

Введите следующую формулу в первую ячейку нового столбца:

Отсюда вы можете вручную добавить буквенно-цифровую комбинацию столбца и строки или просто щелкнуть мышью. Мы будем использовать мышь, которая автоматически заполняет этот раздел формулы ячейкой A2.

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

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

Чтобы применить формулу к дополнительным ячейкам, найдите маленький залитый квадрат в ячейке, содержащей решение нашей первой проблемы. В этом примере это C2.

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

Нахождение суммы квадратов всего для нескольких ячеек

В нашем столбце «Сумма квадратов», который мы создали в предыдущем примере, в данном случае C2, начните вводить следующую формулу:

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

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

7. Вычислить значение логического выражения, если X = Ложь, У = Истина, Z = Ложь: а) X и не (Z или У) или не Z; б) не X или X и (У или Z); в) (X или У … и не Z) и Z. 8. Вычислить значение логического выражения, если X = Истина, У = Ложь, Z = Ложь: а) не X или не У или не Z; б) (не X или не У) и (X или У); в) X» и У или X и Z или не Z. 9. Вычислить значение логического выражения, если А = Ложь, В = Ложь, С = Истина: а) (не А или не В) и не С; б) (не А или не В) и (А или В); в) А и В или А и С или не С ∝ВЫБРАТЬ ПРАВИЛЬНЫЙ ОТВЕТ∩

ВЫБРАТЬ ПРАВИЛЬНЫЙ ОТВЕТ 4. Вычислить значение логического выражения, если А = Истина, В = Ложь, С = Ложь: а) А или Б и не С; г) А и не В или С; б) не … А и не В; д) А и (не В или С); в) не (А и С) или В; е) А и (не (В или С)). 5. Вычислить значение логического выражения, если X = Ложь, У = Ложь, Z = Истина: а) X или У и не Z; г) X и не У или Z; б) не X и не У; д) X и (не У или Z); в) не (X и Z) или У; е) X и (не (У или Z)). 6. Вычислить значение логического выражения, если А — Истина, В = Ложь, С = Ложь: а) А или не (А и В) или С; б) не А или А и (В или С); в) (А или В и не С) и С.

1. Вычислить значение логического выражения, если X = Ложь, У = Истина, Z = Ложь: а) X или Z; б) X и У; в) X и Z. 2. Вычислить значение логического вы … ражения, если А = Истина, В = Ложь, С = Ложь: а) не А и В; б) А или не В; в) А и В или С. 3. Вычислить значение логического выражения, если X = Истина, У = Истина, Z = Ложь: а) не X и У; б) X или не У; в) X или У и Z ВЫБРАТЬ ОТВЕТ ПОЖАЛУЙСТА

Исполнители робот умеет перемещаться по лабиринтуНа Черчи Наму на плоскости, разбитые на клетки. Между соседними клетками может стоять стена, через ко … торый робот пройти не может. На бесконечном поле местности на длины отрезка стены неизвестно. Стена состоит из одного вертикального трёх равных горизонтальных отрезков (отрезки стены расположены буквой Е) Все отрезки Неизвестный длины. Робот находится в клетке, расположена непосредственно с лево от верхнего конца вертикального отрезка. Рисунке указан один из возможных способов расположения стены и робота .Напишите для робота алгорит, закрашиваю щи все клетки, расположенный на нижний в горизонтальном отрезком стены. Робот должен закрасить только клетки удовлетворяющий данном условия например для приведённого справа рисунка Робот должен закрасить следующие клетки

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

Какое значение получит переменная y после выполнения алгоритма? x:=5y:=2∗xy:=y+4y:=y∗xy:=y+5y:=y∗xy:=y+6

Сколько раз выполняется тело цикла в приведенных алгоритмах?

Дано масив: ‘процесор’, ‘команда’, ‘флешка’, ‘брелок’, ‘клавіатура’. Виконайте сортування його елементів в алфавітному порядку за допомогою методу виб … ору.(Python)

Источник

Егорова Елена 5.0

Отзыв о товаре ША PRO Анализ техники чтения по классам
и четвертям

Хочу выразить большую благодарность от лица педагогов начальных классов гимназии
«Пущино» программистам, создавшим эту замечательную программу! То, что раньше мы
делали «врукопашную», теперь можно оформить в таблицу и получить анализ по каждому
ученику и отчёт по классу. Великолепно, восторг! Преимущества мы оценили сразу. С
начала нового учебного года будем активно пользоваться. Поэтому никаких пожеланий у
нас пока нет, одни благодарности. Очень простая и понятная инструкция, что
немаловажно! Благодарю Вас и Ваших коллег за этот важный труд. Очень приятно, когда
коллеги понимают, как можно «упростить» работу учителя.

Наговицина Ольга Витальевна 5.0

учитель химии и биологии, СОШ с. Чапаевка, Новоорский район, Оренбургская область
Отзыв о товаре ША Шаблон Excel Анализатор результатов ОГЭ
по ХИМИИ

Спасибо, аналитическая справка замечательная получается, ОГЭ химия и биология.
Очень облегчило аналитическую работу, выявляются узкие места в подготовке к
экзамену. Нагрузка у меня, как и у всех учителей большая. Ваш шаблон экономит
время, своим коллегам я Ваш шаблон показала, они так же его приобрели. Спасибо.

Чазова Александра 5.0

Отзыв о товаре ША Шаблон Excel Анализатор результатов ОГЭ по
МАТЕМАТИКЕ

Очень хороший шаблон, удобен в использовании, анализ пробного тестирования
занял считанные минуты. Возникли проблемы с распечаткой отчёта, но надо ещё раз
разобраться. Большое спасибо за качественный анализатор.

Лосеева Татьяна Борисовна 5.0

учитель начальных классов, МБОУ СОШ №1, г. Красновишерск, Пермский край
Отзыв о товаре Изготовление сертификата или свидетельства конкурса

Большое спасибо за оперативное изготовление сертификатов! Все очень красиво.
Мой ученик доволен, свой сертификат он вложил в портфолио.
Обязательно продолжим с Вами сотрудничество!

Язенина Ольга Анатольевна 4.0

учитель начальных классов, ОГБОУ «Центр образования для детей с особыми образовательными потребностями г. Смоленска»
Отзыв о товаре Вебинар Как создать интересный урок:
инструменты и приемы

Я посмотрела вебинар! Осталась очень довольна полученной
информацией. Всё очень чётко, без «воды». Всё, что сказано, показано, очень
пригодится в практике любого педагога. И я тоже обязательно воспользуюсь
полезными материалами вебинара. Спасибо большое лектору за то, что она
поделилась своим опытом!

Арапханова Ашат 5.0

ША Табель посещаемости + Сводная для ДОУ ОКУД

Хотела бы поблагодарить Вас за такую помощь. Разобралась сразу же, всё очень
аккуратно и оперативно. Нет ни одного недостатка. Я не пожалела, что доверилась и
приобрела у вас этот табель. Благодаря Вам сэкономила время, сейчас же
составляю табель для работников. Удачи и успехов Вам в дальнейшем!

Дамбаа Айсуу 5.0

Отзыв о товаре ША Шаблон Excel Анализатор результатов ЕГЭ по
РУССКОМУ ЯЗЫКУ

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

Источник

Содержание

Возведение чисел
- Способ 1: возведение с помощью символа
- Способ 2: применение функции
- Способ 3: возведение в степень через корень
- Способ 4: запись числа со степенью в ячейке
Вопросы и ответы

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

Урок: Как поставить знак степени в Microsoft Word

Возведение чисел

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

Способ 1: возведение с помощью символа

Самый популярный и известный способ возведения в степень числа в Экселе – это использование стандартного символа «^» для этих целей. Шаблон формулы для возведения выглядит следующим образом:

=x^n

В этой формуле x – это возводимое число, n – степень возведения.

Например, чтобы возвести число 5 в четвертую степень мы в любой ячейке листа или в строке формул производим следующую запись:
=5^4

Для того, чтобы произвести расчет и вывести его результаты на экран компьютера, кликаем по кнопке Enter на клавиатуре. Как видим, в нашем конкретном случае результат будет равен 625.

Если возведение является составной частью более сложного расчета, то порядок действий производится по общим законам математики. То есть, например, в примере 5+4^3 сразу Excel выполняет возведение в степень числа 4, а потом уже сложение.

Кроме того, с помощью оператора «^» можно возводить не только обычные числа, но и данные, содержащиеся в определенном диапазоне листа.

Возведем в шестую степень содержимое ячейки A2.

В любое свободное место на листе записываем выражение:
= A2^6

Жмем на кнопку Enter. Как видим, расчет был выполнен корректно. Так как в ячейке A2 находилось число 7, то результат вычисления составил 117649.

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

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

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

Урок: Работа с формулами в Excel

Урок: Как сделать автозаполнение в Excel

Способ 2: применение функции

В Экселе имеется также специальная функция для проведения данного расчета. Она так и называется – СТЕПЕНЬ. Её синтаксис выглядит следующим образом:

=СТЕПЕНЬ(число;степень)

Рассмотрим её применение на конкретном примере.

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

Открывается Мастер функций. В списке элементов ищем запись «СТЕПЕНЬ». После того как находим, выделяем её и жмем на кнопку «OK».

Открывается окно аргументов. У данного оператора два аргумента – число и степень. Причем в качестве первого аргумента может выступать, как числовое значение, так и ячейка. То есть, действия производятся по аналогии с первым способом. Если в качестве первого аргумента выступает адрес ячейки, то достаточно поставить курсор мыши в поле «Число», а потом кликнуть по нужной области листа. После этого, числовое значение, хранящееся в ней, отобразится в поле. Теоретически в поле «Степень» в качестве аргумента тоже можно использовать адрес ячейки, но на практике это редко применимо. После того, как все данные введены, для того, чтобы произвести вычисление, жмем на кнопку «OK».

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

Кроме того, окно аргументов можно вызвать, перейдя во вкладку «Формулы». На ленте следует нажать кнопку «Математические», расположенную в блоке инструментов «Библиотека функций». В открывшемся списке доступных элементов нужно выбрать «СТЕПЕНЬ». После этого запустится окно аргументов этой функции.

Пользователи, которые имеют определенный опыт, могут не вызывать Мастер функций, а просто вводить формулу в ячейку после знака «=», согласно её синтаксису.

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

Урок: Мастер функций в Excel

Способ 3: возведение в степень через корень

Конечно, данный способ не совсем обычный, но к нему тоже можно прибегнуть, если нужно возвести число в степень 0,5. Разберем этот случай на конкретном примере.

Нам нужно возвести 9 в степень 0,5 или по-другому — ½.

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

В открывшемся окне Мастера функций ищем элемент КОРЕНЬ. Выделяем его и жмем на кнопку «OK».

Открывается окно аргументов. Единственным аргументом функции КОРЕНЬ является число. Сама функция выполняет извлечение квадратного корня из введенного числа. Но, так как квадратный корень тождественен возведению в степень ½, то нам данный вариант как раз подходит. В поле «Число» вводим цифру 9 и жмем на кнопку «OK».

После этого, в ячейке рассчитывается результат. В данном случае он равен 3. Именно это число и является результатом возведения 9 в степень 0,5.

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

Урок: Как посчитать корень в Экселе

Способ 4: запись числа со степенью в ячейке

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

Форматируем ячейку, в которую будет производиться запись, в текстовый формат. Выделяем её. Находясь во вкладке em«Главная» на ленте в блоке инструментов «Число», кликаем по выпадающему списку выбора формата. Жмем по пункту «Текстовый».

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

Ставим курсор в ячейку и выделяем только вторую цифру.

Нажатием сочетания клавиш Ctrl+1 вызываем окно форматирования. Устанавливаем галочку около параметра «Надстрочный». Жмем на кнопку «OK».

После этих манипуляций на экране отразится заданное число со степенью.

Внимание! Несмотря на то, что визуально в ячейке будет отображаться число в степени, Excel воспринимает его как обычный текст, а не числовое выражение. Поэтому для расчетов такой вариант применять нельзя. Для этих целей используется стандартная запись степени в этой программе – «^».

Урок: Как изменить формат ячейки в Excel

Как видим, в программе Excel существует сразу несколько способов возведения числа в степень. Для того, чтобы выбрать конкретный вариант, прежде всего, нужно определиться, для чего вам нужно выражение. Если вам нужно произвести возведение для записи выражения в формуле или просто для того, чтобы вычислить значение, то удобнее всего производить запись через символ «^». В отдельных случаях можно применить функцию СТЕПЕНЬ. Если вам нужно возвести число в степень 0,5, то существует возможность воспользоваться функцией КОРЕНЬ. Если же пользователь хочет визуально отобразить степенное выражение без вычислительных действий, то тут на помощь придет форматирование.

Источник

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

Обо всем этом и пойдет речь в следующих разделах статьи.

Способ 1: Использование специального символа

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Способ 2: Добавление функции степени

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

Активируйте ячейку для расположения функции, кликнув по ней левой кнопкой мыши. Затем нажмите по значку fx для открытия соответствующего окна.
В нем выберите категорию, отображающую полный перечень функций. Отыщите «‎СТЕПЕНЬ» и дважды кликните по этой строке.
В отдельном поле задайте число, а ниже укажите степень, в которую необходимо возвести число. В качестве числа можете использовать ячейку, имеющую определенное значение.
Примените изменения и вернитесь к таблице, чтобы ознакомиться с результатом. На следующем скриншоте вы видите, какую запись имеет эта функция, поэтому можете использовать ее для ручного ввода, если так будет проще.

Способ 3: Обозначение возведения в степень

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

Активируйте курсор на строке для ввода числа и на главной вкладке разверните список «‎Число», из которого выберите пункт «‎Текстовый».
Напишите два числа рядом: первое будет выступать основой, а второе – степенью.
Выделите то, которое является степенью, и щелкните по нему правой кнопкой мыши. Из контекстного меню выберите пункт «‎Формат ячеек».
Отметьте галочкой «‎Надстрочный» и примените изменения.
Вернитесь в таблицу и убедитесь в том, что результат отображается корректно, то есть так, как это показано на изображении ниже.

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

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

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

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

Источник

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):

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

«[Measures].[Total Sales]»;

«[Products].[Category].[All].[Bikes]»

)

Рис. 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

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

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

«[Measures].[Total Sales]»;

«[Products].[Category].[All].[Bikes]»

)

В итоге формула возвращает продажи по категории Велосипеды (рис. 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 примет вид:

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

«[Calendar].[CalendarYear].[All].[«&$B3&«]»;

«[Measures].[«&C$2&«]»;

Срез_Category

)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(

«[Measures].[Total Sales]»;

$B$2;

«[Products].[Category]»;«[Products].[Category].&[Bikes]»;

«[Calendar].[CalendarYear]»;«[Calendar].[CalendarYear].&[2002]»

)

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

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

«[Measures].[Total Sales]»;

«[Products].[Category].&[Bikes]»;

«[Calendar].[CalendarYear].&[2002]»

)

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

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

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

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

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

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

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

«[Measures].[Total Sales]»;

«[Products].[Color].[All].[Red]|[Silver]»

)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

«[Measures].[Total Sales]»;

КУБЭЛЕМЕНТ(«ThisWorkbookDataModel»;

{«[Products].[Color].[All].[Red]»;

«[Products].[Category].[All].[Bikes]»}

)

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

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

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

«[Measures].[Total Sales]»;

A5;

«[Calendar].[CalendarYear].[All].[2003]»

)

…она вернет ошибку #ЗНАЧ! Это связано с тем, что измерение [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а. Запятая, разделяющая элементы кортежа; точка с запятой не работает

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

=КУБМНОЖ(«ThisWorkbookDataModel»;

«{[Products].[Color].[All].[Red],

[Products].[Color].[All].[Silver]}»;

«Набор из двух элементов»

)

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

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

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

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

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

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

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

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

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

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

=КУБМНОЖ(«ThisWorkbookDataModel»;

«[Products].[Color].children»;

«Результат функции MDX»

)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

{=КУБМНОЖ(«ThisWorkbookDataModel»;

«[Calendar].[Date].[All].[«&ТЕКСТ(A1:A6;«М/Д/ГГГГ»)&«]»;

«множ»

)}

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

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

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

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

«[Measures].[Total Sales]»;

B1;

«[Products].[Color].[All].[Red]»;

«[Products].[Category].[All].[Bikes]»

)

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

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

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

=КУБМНОЖ(«ThisWorkbookDataModel»;

«{[Calendar].[Date].[All].[«&ТЕКСТ(A1;«М/Д/ГГГГ»)&«]:

[Calendar].[Date].[All].[«&ТЕКСТ(A6;«М/Д/ГГГГ»)&«]}»;

«множ»

)

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

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

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

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

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

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

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

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

=КУБПОРЭЛЕМЕНТ(«ThisWorkbookDataModel»;

«[Products].[Color].[All].children»;

СТРОКА()

)

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

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

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

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

=КУБПОРЭЛЕМЕНТ(«ThisWorkbookDataModel»;

КУБМНОЖ(«ThisWorkbookDataModel»;

«[Products].[ModelName].children»;

;

«[Measures].[Total Sales]»);

СТРОКА(А1)

)

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

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

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

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

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

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

=КУБМНОЖ(«ThisWorkbookDataModel»;

«[Products].[ModelName].children»;

;

«[Measures].[Total Sales]»

)

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

КУБМНОЖ(«ThisWorkbookDataModel»;

«[Products].[ModelName].children»;

;

КУБЭЛЕМЕНТ(«ThisWorkbookDataModel»;

«([Measures].[Total Sales],[Products].[Category].[All].[Accessories])»)

)

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

=КУБПОРЭЛЕМЕНТ(«ThisWorkbookDataModel»;

КУБМНОЖ(«ThisWorkbookDataModel»;

«[Products].[ModelName].children»;

;

КУБЭЛЕМЕНТ(«ThisWorkbookDataModel»;

«([Measures].[Total Sales],[Products].

[Category].[All].[Accessories])»));

СТРОКА(A1)

)

Эту формулу можно протянуть вдоль столбца до ячейки В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 прописываем формулу…

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

«[Measures].[Total Sales]»;

«[Products].[ModelName].[All].[«&$K4&«]»;

Срез_Country;

Срез_CalendarYear;

«[Calendar].[MonthName].[All].[«&L$3&«]»

)

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

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

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

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

=ЕСЛИ(КУБЧИСЛОЭЛМНОЖ(Срез_CalendarYear)*

КУБЧИСЛОЭЛМНОЖ(Срез_Country)*

КУБЧИСЛОЭЛМНОЖ(Срез_MonthName)>1;

«Отчет отражает корректные данные только

если выбран один элемент в каждом срезе»;

«»

)

Если хотя бы в одном срезе выбрано более одного элемента, произведение трех функций КУБЧИСЛОЭЛМНОЖ() будет более единицы, и в ячейке отобразится введенный текст. Если во всех срезах выбран один элемент, ячейка В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 – обязательные аргументы; многомерные выражения, возвращающее набор элементов (кортежей). Мера – необязательный аргумент; если он указан, то возвращаются только такие элементы (кортежи), для которых мера определена. Например, следующее выражение вернет клиентов, проживающих в Калифорнии и совершивших сделки в Интернете:

EXISTS(

[Customer].members,

[Customer].[State—Province].&[CA],

[Internet Sales]

)

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

Рис. 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 г.

Источник

How to find the Cube Root in Excel

Как возвести в степень в Excel?

Вариант №1. Используем символ «^»

Вариант №2. С использованием функции

Формула возведения в степень в Excel

Корень в степени в Excel

Как в Excel написать число в степени?

Метод 1: использование функции КОРЕНЬ

Метод 2: нахождение корня путем возведения в степень

Заключение

Вычислите значения квадратов и кубов первых 10 чисел excel

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

Формула для расчета квадрата числа

Функция СТЕПЕНЬ для возведения числа в квадрат

Заключение

Процедура возведения в квадрат

Способ 1: возведение с помощью формулы

Способ 2: использование функции СТЕПЕНЬ

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

Формула для расчета квадрата числа

Функция СТЕПЕНЬ для возведения числа в квадрат

Заключение

Описание

Синтаксис

Замечания

Пример

Нахождение суммы квадратов для нескольких ячеек

Нахождение суммы квадратов всего для нескольких ячеек

Возведение чисел

Способ 1: возведение с помощью символа

Способ 2: применение функции

Способ 3: возведение в степень через корень

Способ 4: запись числа со степенью в ячейке

Способ 1: Использование специального символа

Способ 2: Добавление функции степени

Способ 3: Обозначение возведения в степень

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источники

А вот еще интересные статьи: