Создание полного перечня пересечения объектов в Excel с помощью операции CrossJoin

Рассмотрим приемы, с помощью которых можно создать полный перечень пересечения объектов в Excel (т. е. выполнить операциюи CrossJoin).

Предложим три варианта:

1) с использованием формул СТРОКА(), ОКРУГЛВВЕРХ(), ОСТАТ(), ИНДЕКС();

2) с помощью инструментов интерфейса Power Query;

3) с помощью DAX-запросов к таблицам в модели данных Power Pivot.

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

Рассмотрим простой пример.

Имеем выгрузку продаж в кг за день по категории «Молочная продукция» в пяти магазинах (рис. 1). При этом у нас есть пять магазинов — таблица «Магазин» и пять единиц товаров — таблица «Товар».

Количество строк в отчете «Выгрузка» — 23, а полное пересечение магазинов и товаров — 25 строк (5 магазинов × 5 товаров). Это значит, что два товара в нашем случае имеют нулевые продажи за день, поэтому не попали в выгрузку.

Для магазина «Купеческий» нет категории «Молоко», для магазина «Фестивальный» — позиции «Йогурт».

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

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

Построим сводную таблицу, поместив в область строк поле «Магазин», в поле значений — Сумму по полю Продажи, кг, Среднее по полю продажи, кг, Количество по полю Продажи, кг (рис. 2).

Первый столбец значений — «Сумма» — показывает сумму проданных килограммов, а столбец «Количество» — количество категорий. Средним в сводной таблице является отношение суммы к количеству.

Мы знаем, что в нашей таблице перечислены не все категории, которые продавались в магазине, а значит, среднее значение по магазинам «Купеческий» и «Фестивальный» завышены.

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

Столбец «Отношение» на рис. 2 — это разница между столбцом «Правильное среднее» и «Среднее по полю продажи».

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

Полный перечень пересечений также называется операцией CROSSJOIN.

Способ 1

CrossJoin двух объектов с помощью формул

Мы знаем, что нам нужно создать полный список пересечений магазинов и товаров и у нас есть пять магазинов и пять товаров. Полный перечень — это всегда произведение уникальных значений каждого объекта. В нашем случае это 25 (рис. 3).

Если бы надо было добавить еще пять дней к перечню, то перечень состоял бы из 25 × 5 = 125 строк и т. д.

Порядок расчета:

1. Начиная с ячейки A2 пронумеруем строки от 1 до 25. Автор использует формулу:

=СТРОКА()–1

Функция СТРОКА() возвращает порядковый номер текущей строки.

2. В столбце B указан порядковый номер магазина. Нам необходимо сделать так, чтобы для порядковых номеров столбца А 1–5 был указан номер 1, для порядковых номеров 2–10 — номер 2 и т. д. Для этого запишем в ячейку B2 формулу:

=ОКРУГЛВВЕРХ(A2/5;0)

Функция ОКРУГЛВВЕРХ() округляет число до ближайшего сверху с заданной точностью. Мы делим число в столбце А на 5 и округляем до целого.

3. В столбце C указан порядковый номер товара. Нам необходимо сделать так, чтобы в данном столбце цифры от 1 до 5 шли в хронологическом порядке, возвращаясь после пяти к единице и т. д. Для этого запишем в ячейку C2 формулу:

=ЕСЛИ(ОСТАТ(A2;5)=0;5;ОСТАТ(A2;5))

Функция ОСТАТ() возвращает остаток от деления (в нашем случае — деления на 5). Если остаток равен нулю, то возвращает 5.

4. Теперь мы имеем порядковые номера магазинов — столбец B и порядковые номера товаров — столбец C. Чтобы добавить магазины в столбец D, воспользуемся функцией ИНДЕКС().

Для подстановки магазинов в столбце D функция будет выглядеть следующим образом:

=ИНДЕКС(Магазины;B2)

Для подстановки товаров в столбце E:

=ИНДЕКС(Товары;B2)

Столбцы D и E — это тот результат, к которому мы хотели прийти. Чтобы подставить продажи из первоначальной таблицы, можно воспользоваться функцией ВПР() по связке столбцов либо СУММЕСЛИМН(), где условиями будут «Магазины» и «Товары».