Как сделать поиск одного массива по другому в Excel: практическое руководство

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

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

Задача: поиск массива по другому массиву

Представим, что у вас есть следующие исходные данные:

• Таблица с типами автомобилей и их цветами.
• Таблица с фабриками и цветами, которые они поставляют.

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

Решение с помощью Power Query

Power Query — удобный инструмент для работы с большими таблицами и сложными преобразованиями. Он позволяет объединять таблицы, фильтровать и группировать данные без сложных формул.

Для решения этой задачи можно:

1. Загрузить обе таблицы (Car и Factory) в Power Query.
2. Использовать функцию Table.NestedJoin для объединения данных по столбцам цвета.
3. Развернуть объединённую таблицу Table.ExpandTableColumn и удалить лишние столбцы.
4. Удалить дубликаты и сгруппировать данные по типу автомобиля.
5. Добавить индекс, расширить и повернуть таблицу с помощью Table.Pivot.

Ниже приведён пример кода Power Query для решения задачи:

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:

• Подходит для сложных и больших наборов данных.
• Легко повторять и обновлять при изменении исходных данных.
• Нет необходимости в сложных формулах.

Использование формул Excel без LAMBDA

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

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

excel
=UNIQUE(TOROW(IF(TOROW(FILTER(Car_Typetbl[Car Colour], G1=Car_Typetbl[Car Type], ""))
=Factorytbl[Colour Suppliers], Factorytbl[Factory], NA()), 2), 1)

Эта формула делает следующее:

• Фильтрует цвета по типу автомобиля.
• Сравнивает с таблицей цветов фабрик.
• Возвращает уникальный список фабрик, соответствующих цветам.

Для удобочитаемости формулу лучше записать с помощью функции LET:

excel
=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))

Динамические формулы с использованием PIVOTBY

Excel 365 предлагает мощную функцию PIVOTBY для создания сводных таблиц на базе динамических массивов.

Ниже пример сложной формулы, которая позволяет:

• Соответствовать цвета и типы автомобилей.
• Получать уникальные фабрики для каждого типа.
• Повернуть данные для удобства отображения.

excel
=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, желающих получить максимально компактный и динамический результат.

Пошаговое построение решения формулами

Для лучшего понимания, разберём пошагово простой вариант решения:

Шаг 1: Фильтрация цветов по типу автомобиля

Используем функцию FILTER, чтобы получить список цветов для конкретного типа автомобиля:

excel
=FILTER(C7:C14,B7:B14=B3)

Шаг 2: Преобразование результата в строку и сравнение

Конвертируем массив цветов в строку и сравниваем с цветами в таблице фабрик:

excel
=TOROW(FILTER(C7:C14,B7:B14=B3))=E7:E15

Получаем булев массив, показывающий соответствие цветов.

Шаг 3: Анализ по строкам с помощью BYROW и OR

Используем BYROW и OR для объединения результата по строкам:

excel
=BYROW(TOROW(FILTER(C7:C14,B7:B14=B3))=E7:E15,OR)

Шаг 4: Фильтрация фабрик по булевому массиву

Получаем фабрики, соответствующие цветам из шага 3:

excel
=FILTER(F7:F15,BYROW(TOROW(FILTER(C7:C14,B7:B14=B3))=E7:E15,OR))

Шаг 5: Уникальные фабрики и преобразование в строку

Заключительный шаг — получить уникальный список фабрик и перевести его в строку или массив:

excel
=TOROW(UNIQUE(FILTER(F7:F15,BYROW(TOROW(FILTER(C7:C14,B7:B14=B3))=E7:E15,OR))))

Альтернативный комплексный подход с REDUCE и VSTACK

Для более сложных случаев, можно использовать связку формул с REDUCE и VSTACK для объединения фабрик по типам:

excel
=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 идеально подходит для масштабных задач и удобен в работе с большими данными.
• Динамические формулы Excel с FILTER, TOROW, LET и PIVOTBY дают быстрый и гибкий результат без использования внешних инструментов.
• Пошаговое построение формул помогает лучше понять логику и настраивать решение под конкретные нужды.
• Современные функции Excel позволяют обходиться без VBA и долгоискомых сложных формул для таких задач.

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

Используйте эти методы, чтобы эффективно создавать взаимосвязи между вашими таблицами и быстро получать нужные результаты!

Источник