Распределяем список по наборам
Постановка задачи
Имеем список объектов (например, товаров) с пометкой, к какому набору (корзине) каждый из них относится. Необходимо разложить объекты по своим наборам, сформировав таблицу как на рисунке справа:
Похожие задачи встречаются на практике весьма часто - в случаях, когда приходится распределять те или иные ресурсы:
- сотрудников по командам
- водителей по маршрутам
- клиентов по менеджерам
- товары по корзинам и т.д.
В прошлом я уже делал статью и видео о похожей задаче, где мы разбирали как переложить одномерный столбец с данными в двумерную таблицу, но там ситуация была проще, т.к. каждый набор (строка) имел одинаковый размер (число столбцов). Здесь же количество элементов в наборах заранее не известно и не равно друг другу, так что подход придётся изменить.
Чтобы у вас был выбор, давайте разберем несколько способов решения этой задачи.
Способ 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):
Задача решена! Осталось выгрузить полученные результаты обратно в 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, к которой затем приклеивается слово "Товар " в начало.
Ссылки по теме
- Динамические массивы - тихая революция в Excel
- Трансформация столбца в таблицу (формулами и через Power Query)
- Превращение строк в столбцы и обратно
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 #"Разделить столбец по разделителю"#"Сгруппированные строки" = Table.Group(Источник, {"Корзина"}, {{"Товар", each _[Товар], type list}}),Далее используя интерфейс редактора pq выбрать "Извлечь значения..." указать какой-нибудь разделитель.
Далее Преобразование - Разделить столбец - По разделителю столбцы будут пронумерованы pq через точку.
Вроде результат похож, и почти все делается с помощью кнопок только чуть-чуть в коде поправить.
let Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], GroupedRows = Table.Group(Source, {"Корзина"}, {{"таб", each Table.FromRows({{[Корзина]{0}}&[Товар]}), type table}}), таб = Table.Combine( GroupedRows[таб] ) in табТеперь представьте, что в корзине из списка окажется даже 100 товаров. И вид предлагаемой Вами таблицы.
Если можно обойтись без такой ситуации в принципе - отлично.
А делать вручную такую раскладку по строчкам-наборам - это жесть, согласитесь
1. Добавляем в исходную таблицу столбцы "НомерК", "НомерТ" и "Индекс".
2. Вводим в них формулы:
"НомерК"
"Корзина"
а как называется Задача- если у меня есть общая сумма оплат (из бух программы), а мне надо ее расставить напротив начислений.
Т.к менеджеры хотят видеть за какой последний месяц оплачено. А клиенты платят не пойми что и за какой месяц
Благодарю:)