Распределяем список по наборам

Постановка задачи

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

Исходные данные и результат

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

  • сотрудников по командам
  • водителей по маршрутам
  • клиентов по менеджерам
  • товары по корзинам и т.д.

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

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

Способ 1. Вручную через сводную таблицу

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

1. Ставим активную ячейку в любое место исходной таблицы и строим по ней сводную через Вставка - Сводная таблица (Insert - Pivot Table).

2. В панели полей сводной закидываем поле Корзина в область строк, поле Товары - в область столбцов и в область значений. На выходе должны получить сводную, где на пересечении корзины и товара будет число, если товар входит в заданную корзину:

Строим сводную

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

4. В скопированном диапазоне выделим снова только центральный блок с числами без подписей и затем заставим Excel выделить только ячейки с числами, нажав клавишу F5 - кнопка Выделить - Константы (F5 - Go to Special - Constants):

Выделяем ячейки с числами

5. Аккуратно, чтобы не сбить получившееся выделение, вводим в первую выделенную ячейку (она будет белой) ссылку на соответствующий ей товар (в нашем случае это будет ссылка на Брокколи), жмём на клавишу F4, чтобы оставить знак доллара только перед цифрой (т.е. закрепить строку) и сочетание клавиш Ctrl+Enter (чтобы ввести аналогичные формулы сразу во все выделенные ячейки):

Вставляем товары по ссылкам

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

7. Используя уже знакомый по п.4 трюк с клавишей F5 - Выделить на этот раз выделяем пустые ячейки и удаляем их со сдвигом влево:

Удаляем пустые ячейки со сдвигом влево

Задача решена.

Способ 2. Обновляемый запрос через Power Query

Этот способ чуть сложнее, но позволит не повторять весь процесс в будущем при изменении исходных данных - достаточно будет просто обновить наш запрос. Будем использовать для решения надстройку Power Query, которую для Excel 2010-2013 можно бесплатно скачать с сайта Microsoft. Начиная с версии Excel 2016, Power Query уже встроена в Microsoft Excel по умолчанию.

Чтобы решить нашу задачу, делаем следующее:

1. Превращаем исходную таблицу в динамическую "умную", чтобы не думать впоследствии о изменении её размеров. Можно использовать команду Главная - Форматировать как таблицу (Home - Format as Table) или сочетание клавиш Ctrl+T.

2. Загружаем полученную "умную" таблицу в Power Query через команду Данные - Из таблицы / диапазона (Data - From Table/Range).

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

4. Группируем таблицу по корзинам, выбрав на вкладке Преобразование команду Группировать по (Transform - Group by) и задав в качестве операции Все строки (All rows):

Группируем строки по корзинам

Наша исходная таблица "схлопнется" до корзин, содержимое которых будет лежать во вложенных таблицах в последнем столбце:

Готовые группы во вложенных таблицах

5. Выбираем команду Добавление столбца - Настраиваемый столбец (Add Column - Custom column), чтобы добавить к каждой вложенной таблице столбец индекса с порядковым номером строки с помощью М-функции Table.AddIndexColumn:

Добавляем индексный столбец

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

Развернутая таблица с добавленными индексами в корзинах

7. Добавляем в последнем столбце к нумерации слово "Товар " с помощью команды Преобразование - Формат - Добавить префикс (Transform - Format - Add Prefix).

8. Последний штрих: выполняем свёртку по последнему столбцу с нумерацией с помощью команды Преобразование - Столбец сведения (Transform - Pivot Column):

Выполняем свёртку по столбцу Товар 1,2,3...

Задача решена! Осталось выгрузить полученные результаты обратно в Excel через Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load to...)

Готовый результат запроса

При изменении данных в исходной таблице необходимо будет обновить наш запрос, щёлкнув по результирующей таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или кнопку Обновить всё на вкладке Данные (Data - Refresh All).

Способ 3. Функции динамических массивов

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

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

Поехали:

1. Превращаем исходную таблицу в динамическую "умную", чтобы не думать впоследствии о изменении её размеров. Можно использовать команду Главная - Форматировать как таблицу (Home - Format as Table) или сочетание клавиш Ctrl+T.

2. Формируем отсортированный список уникальных названий корзин из столбца [Корзина] нашей умной таблицы Таблица4 - с помощью функций СОРТ (SORT) и УНИК (UNIQUE), соответственно:

Список корзин с помощью динамических массивов

3. Выводим все товары из каждой корзины с помощью функции ФИЛЬТР (FILTER):

Выводим товары из каждой корзины

По умолчанию, функция ФИЛЬТР выводит результаты в столбец. Чтобы развернуть их по горизонтали - в строку - используем дополнительно функцию транспонирования ТРАНСП (TRANSPOSE).

4. Добавляем шапку для красоты:

Добавляем шапку

Здесь логика такая:

  • Функция СЧЁТЕСЛИ (COUNTIF) вычисляет массив с размерами каждой корзины - это, в нашем случае, будет {4,2,7,1,2}
  • Затем функция МАКС (MAX) определяет в нём самое большое число (7)
  • Новая функция динамических массивов ПОСЛЕД (SEQUENCE) генерирует числовую последовательность от 1 до 7, к которой затем приклеивается слово "Товар " в начало.

Ссылки по теме



19.12.2020 23:54:11
Еще один способ:
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Сгруппированные строки" = Table.Group(Источник, {"Корзина"}, {{"Товар", each _[Товар], type list}}),
    #"Извлеченные значения" = Table.TransformColumns(#"Сгруппированные строки", {"Товар", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Извлеченные значения", "Товар", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Товар.1", "Товар.2", "Товар.3", "Товар.4", "Товар.5", "Товар.6"})
in
    #"Разделить столбец по разделителю"
Во втором шаге вручную дописано {{"Товар", each _[Товар], type list}
#"Сгруппированные строки" = Table.Group(Источник, {"Корзина"}, {{"Товар", each _[Товар], type list}}),
Чтобы результатом группировки был список значений из колонки [Товар], а не таблица с двумя колонками.
Далее используя интерфейс редактора pq выбрать "Извлечь значения..." указать какой-нибудь разделитель.
Далее Преобразование - Разделить столбец - По разделителю столбцы будут пронумерованы pq через точку.
Вроде результат похож, и почти все делается с помощью кнопок только чуть-чуть в коде поправить.
22.12.2020 10:14:50
Ваш код не является универсальным, в шаге где вы делите по разделителю в коде объявляется жесткая привзяка по количеству результирующих столбцов - не более 6ти. Если в исходнике будут строки с большим количеством элементов, то в результат они не попадут.
22.12.2020 10:12:47
Вариант для PQ по-короче:
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    GroupedRows = Table.Group(Source, {"Корзина"}, {{"таб", each Table.FromRows({{[Корзина]{0}}&[Товар]}), type table}}),
    таб = Table.Combine( GroupedRows[таб] )
in
    таб
Добавил запрос в пример Николая, Там же на листе с Power Query вариант решения задачи при помощи сводной из модели данных Power Pivot, которая может обновляться. ;)
05.01.2021 19:31:01
А не проще "сортирнуть" список вручную по "корзине", а потом вручную создать список товаров в корзине.
Теперь представьте, что в корзине из списка окажется даже 100 товаров. И вид предлагаемой Вами таблицы.
11.01.2021 11:19:00
Сергей, в данном случае не стоит вопрос "зачем это делать", а стоит вопрос "как".
Если можно обойтись без такой ситуации в принципе - отлично.
А делать вручную такую раскладку по строчкам-наборам - это жесть, согласитесь :)
21.01.2021 09:44:16
Есть ещё способ. Подойдёт тем, у кого относительно старый Excel, а использование макросов запрещено политикой работодателя.
1. Добавляем в исходную таблицу столбцы "НомерК", "НомерТ" и "Индекс".
2. Вводим в них формулы:
 "НомерК"
=ЕСЛИ(СЧЁТЕСЛИ(СМЕЩ(Таблица1[[#Заголовки];[Корзина]];1;0;СТРОКА()-СТРОКА(Таблица1[#Заголовки]);1);[@Корзина])=1;ЕСЛИОШИБКА(МАКС(СМЕЩ(Таблица1[[#Заголовки];[НомерК]];1;0;СТРОКА()-СТРОКА(Таблица1[#Заголовки])-1;1))+1;1);ИНДЕКС([НомерК];ПОИСКПОЗ([@Корзина];[Корзина];0)))
 
  "НомерТ"

=СЧЁТЕСЛИ(СМЕЩ(Таблица1[[#Заголовки];[Корзина]];1;0;СТРОКА()-СТРОКА(Таблица1[#Заголовки]);1);[@Корзина])
 
 "Индекс"

=[@НомерК]&"@"&[@НомерТ]
 
3. Создаем smart-таблицу для результата (количество столбцов для товаров позже можно будет увеличить). Вносим формулы:
 "Корзина"

=ЕСЛИОШИБКА(ИНДЕКС(Таблица1[Корзина];ПОИСКПОЗ(СТРОКА()-СТРОКА(Таблица2[#Заголовки]);Таблица1[НомерК];0));"")
 
 Остальные столбцы
=ЕСЛИОШИБКА(ИНДЕКС(Таблица1[Товар];ПОИСКПОЗ(СТРОКА()-СТРОКА(Таблица2[#Заголовки])&"@"&СТОЛБЕЦ()-СТОЛБЕЦ([Корзина]);Таблица1[Индекс];0));"")
 
Наверх