Введение
Excel часто требуется сопоставлять один массив с другим: например, по заданному типу автомобиля получить все возможные цвета, а затем — все заводы, которые могут поставлять краску для этих цветов. В этой статье переведены и структурированы несколько рабочих решений из англоязычного источника: через Power Query, простые формулы и продвинутые динамические массивы (UNIQUE, TOROW, LET, PIVOTBY, LAMBDA и др.). Для каждой техники указаны принципы работы, примеры формул и рекомендации по выбору.
Исходная задача
Имеется две таблицы: таблица с типами автомобилей и соответствующими цветами, и таблица с поставщиками красок (цвет → завод). По входному типу автомобиля нужно:
— найти все цвета, которые используются для этого типа;
— на основе этих цветов получить все уникальные заводы-поставщики;
— вернуть результат в виде динамического массива в строке или столбце.
Решение 1 — Power Query (рекомендуется для больших наборов и объединения данных)
Power Query удобно использовать, если данные часто «накладываются» (stacking) или требуется более надёжная подготовка/очистка. Основная идея: объединить таблицы по цвету, удалить дубликаты и сделать сводку по типу автомобиля, затем повернуть поставщиков в колонки.
Пример M-кода:
let
_Car = Excel.CurrentWorkbook(){[Name=»Car_Typetbl»]}[Content],
_Factory = Excel.CurrentWorkbook(){[Name=»Factorytbl»]}[Content],
_Merge = Table.NestedJoin(_Factory, {«Colour Suppliers»}, _Car, {«Car Colour»}, «_Car», JoinKind.LeftOuter),
_Epxand = Table.ExpandTableColumn(_Merge, «_Car», {«Car Type»}, {«Car Type»}),
_RemovedCols = Table.RemoveColumns(_Epxand,{«Colour Suppliers»}),
_RemovedDupes = Table.Distinct(_RemovedCols),
_GroupBy = Table.Group(_RemovedDupes, {«Car Type»}, {{«All», each _, type table [Factory=text, Car Type=text]}}),
_Index = Table.AddColumn(_GroupBy, «_Index», each Table.AddIndexColumn([All], «Index», 1, 1)),
_Removed = Table.SelectColumns(_Index,{«_Index»}),
#»Expanded _Index» = Table.ExpandTableColumn(_Removed, «_Index», {«Factory», «Car Type», «Index»}, {«Factory», «Car Type», «Index»}),
_PivotBy = Table.Pivot(Table.TransformColumnTypes(#»Expanded _Index», {{«Index», type text}}, «en-US»), List.Distinct(Table.TransformColumnTypes(#»Expanded _Index», {{«Index», type text}}, «en-US»)[Index]), «Index», «Factory»)
in
_PivotBy
Плюсы Power Query:
— легко масштабируется на большие таблицы;
— удобен для предварительной обработки и удаления дубликатов;
— изменение логики в редакторе проще, чем переписывать длинные формулы.
Минусы:
— требует перехода в редактор запросов, не всегда удобно для быстрых формул в ячейках;
— обновление данных требует Refresh.
Решение 2 — Простая формула FILTER + MATCH (подходит для одной записи)
Самый простой способ для одной ячейки — отфильтровать столбец заводов по совпадению поставляемых цветов с набором цветов для данного типа:
Формула (обобщённый вид):
=FILTER(Factory_Column, ISNUMBER(MATCH(Colour_Suppliers_Column, FILTER(Car_Colour_Column, Car_Type = Lookup_Car), 0)))
Пояснение:
— FILTER(Car_Colour_Column, Car_Type = Lookup_Car) — получает цвета для заданного типа;
— MATCH(…) возвращает соответствие цветов между двумя диапазонами;
— FILTER(Factory_Column, ISNUMBER(MATCH(…))) — возвращает заводы, у которых есть совпадение цвета.
Решение 3 — Динамические массивы: UNIQUE, TOROW, LET (без LAMBDA)
Если вы используете Microsoft 365 с динамическими массивами, можно получить компактную и читабельную формулу:
Формула (пример, используемая в ячейке H1):
=UNIQUE(TOROW(IF(TOROW(FILTER(Car_Typetbl[Car Colour], G1=Car_Typetbl[Car Type], «»)) = Factorytbl[Colour Suppliers], Factorytbl[Factory], NA()), 2), 1)
Вариант с LET для читаемости:
=LET(
_a, TOROW(IFS(G1=Car_Typetbl[Car Type], Car_Typetbl[Car Colour]), 2),
UNIQUE(TOROW(IFS(_a=Factorytbl[Colour Suppliers], Factorytbl[Factory]), 3), 1))
Пояснение:
— TOROW превращает массив цветов в единственную строку, позволяя сравнивать с каждым поставщиком;
— IFS/IF используются для построения логики совпадения;
— UNIQUE удаляет дубликаты поставщиков.
Решение 4 — Один сложный динамический массив с PIVOTBY и LAMBDA
Для вывода в виде сводной таблицы одного выражения можно использовать PIVOTBY, MAP, TEXTJOIN и другие новые функции Excel (требует последних сборок Microsoft 365):
Пример (упрощённо):
=LET(
_a, DROP(A:.B, 1),
_b, DROP(D:.E, 1),
_c, MAP(INDEX(_a, , 2), LAMBDA(_x, TEXTJOIN(«|», 1, FILTER(INDEX(_b, ,2), INDEX(_b, , 1)=_x)))),
_d, TEXTSPLIT(TEXTAFTER(«|»&_c, «|», SEQUENCE(, MAX(LEN(_c)-LEN(SUBSTITUTE(_c, «|», ))+1))), «|»),
_e, SORT(UNIQUE(HSTACK(TOCOL(IFS(1-ISNA(_d), INDEX(_a, , 1)), 3), TOCOL(_d, 3))), {1,2}),
_f, INDEX(_e, , 1),
_g, SEQUENCE(ROWS(_f)),
_h, «Make «&MAP(_f, _g, LAMBDA(_x,_y, SUM((_f=_x)*(_g<=_y)))),
PIVOTBY(_f, _h, DROP(_e, , 1), SINGLE, , 0, , 0))
Плюсы:
- полностью в одной формуле — результат сразу в нужной форме;
- мощный контроль над формированием столбцов и сводной структурой.
Минусы:
- сложность понимания и поддержки;
- требует очень свежих версий Excel (Microsoft 365 Insider или эквивалент).
Пошаговый разбор формулы с BYROW (разбор из источника)
Ниже — более наглядный метод по шагам, без LAMBDA/PIVOTBY, понятный и удобный для обучения:
1) Получаем цвета для выбранного типа:
=FILTER(C7:C14,B7:B14=B3)
2) Приводим к одной строке и сравниваем с колонкой поставщиков:
=TOROW(FILTER(C7:C14,B7:B14=B3))=E7:E15
3) По строкам применяем OR, чтобы узнать, какие строки поставщиков соответствуют любому из цветов:
=BYROW(TOROW(FILTER(C7:C14,B7:B14=B3))=E7:E15, OR)
4) Фильтруем заводы по полученному булеву массиву:
=FILTER(F7:F15,BYROW(TOROW(FILTER(C7:C14,B7:B14=B3))=E7:E15,OR))
5) Берём уникальные и превращаем в строку:
=TOROW(UNIQUE(FILTER($F$7:$F$15, BYROW(TOROW(FILTER($C$7:$C$14,$B$7:$B$14 = B3))=$E$7:$E$15, OR))))
Альтернативный подход с REDUCE и VSTACK (для одной ячейки в B15)
Если данные расположены иначе, можно собрать результат с REDUCE:
=TRANSPOSE(SORT(UNIQUE(DROP(REDUCE("", FILTER($B$3:$B$10, $A$3:$A$10=A15),
LAMBDA(a,r, VSTACK(a, FILTER($E$3:$E$11, $D$3:$D$11=r)))),1))))
Когда использовать каждое решение — рекомендации
- Power Query: если у вас большие таблицы, регулярные обновления данных, нужна надёжная очистка и последующая агрегация. Лучше для ETL-процессов.
- Простые формулы (FILTER+MATCH): когда нужен быстрый результат для одной ячейки и нет зависимости от очень новых функций Excel.
- Динамические массивы (UNIQUE, TOROW, LET): когда у вас Microsoft 365 и вы хотите компактные формулы, легко получаете массив в ячейку.
- PIVOTBY / LAMBDA и прочие: когда нужен очень гибкий вывод (несколько столбцов с автозаполнением), и вы готовы поддерживать сложные формулы. Подходит для продвинутых пользователей.
Совместимость и производительность
- FILTER, UNIQUE, TOROW, BYROW, LET, MAP, TEXTSPLIT, PIVOTBY доступны только в Microsoft 365 c современными сборками. На старых Excel (2016, 2019) этих функций нет.
- Power Query доступен в большинстве современных Excel-версий и часто является оптимальным выбором по скорости и устойчивости.
- Сложные формулы с множественными массивными операциями могут быть медленными на больших наборах данных; в таких случаях лучше Power Query или промежуточные таблицы.
Вывод
Для задачи «поиск массива в другом массиве» в Excel существует множество подходов. Для производственной надежности и масштабируемости — выбирайте Power Query. Для быстрого решения и простоты — используйте FILTER + MATCH. Если у вас Microsoft 365 и вы хотите динамические, компактные решения — применяйте UNIQUE/TOROW/LET или продвинутые шаблоны с PIVOTBY и LAMBDA. В статье приведены рабочие формулы и M-код, которые можно адаптировать под свои имена таблиц и расположение данных.
Если нужно — могу:
- адаптировать конкретную формулу под вашу структуру таблиц (укажите имена столбцов и пример диапазонов);
- подготовить пошаговый Power Query на основе ваших исходных данных.
Источник
