Как автоматически получить список приложений для каждой персоны, комбинируя две матрицы в Excel
Задача простая по сути, но на практике требует правильной агрегации данных: у вас есть две таблицы — связь «Роль → Приложения» и связь «Персона → Роли» (обозначение X в пересечении значит наличие). Нужно для каждой персоны автоматически вывести перечень приложений, доступных через все её роли.
Общий алгоритм решения (логика, которую нужно реализовать в формулах или Power Query):
- 1) Для каждой персоны определить, какие роли у неё отмечены («X») в таблице Персона→Роли.
- 2) По списку ролей получить из таблицы Роль→Приложения все приложения, которые связаны с этими ролями.
- 3) Собрать итоговый список приложений (в столбец под персоной), опционально убрать повторы и/или превратить строку в столбец.
Вариант 1 — LET + динамические массивы (сложная, но мощная формула)
Эта формула использует LET для удобного именования промежуточных массивов и PIVOTBY для финального преобразования. Подходит, если нужна одна формула, которая выдаёт развернутую таблицу «Персона → приложения в столбцах».
=LET(
_a, I4:K7,
_b, TOROW(IF(_a="X", H4:H7, 0/0), 2),
_c, TOROW(IF(_a="X", I3:K3, 0/0), 2),
_d, C4:F7,
_e, TOCOL(IF(_d="X", B4:B7, 0/0), 2),
_f, TOCOL(IF(_d="X", C3:F3, 0/0), 2),
_g, TOCOL(IF(_e=_b, _c, 0/0), 2),
_h, TOCOL(IF(_e=_b, _f, 0/0), 2),
_i, SEQUENCE(ROWS(_g)),
_j, MAP(_g, _i, LAMBDA(x, y, SUM((_i<=y)*(_g=x)))),
_k, PIVOTBY(_j, _g, _h, SINGLE, , 0, , 0),
_l, IF(TAKE(_k, , 1)="", "Apps", ""),
HSTACK(_l, DROP(_k, , 1)))
Коротко о том, что делает формула (по шагам и по переменным):
- _a — диапазон таблицы «Персона→Роли» (матрица отметок X).
- _b — список персон (строка) соответствующих колонок из таблицы ролей (с использованием TOROW и IF для фильтрации по X).
- _c — заголовки ролей из первой таблицы (или заголовки приложений), соответствующие отметкам X.
- _d — диапазон таблицы «Роль→Приложения».
- _e, _f — аналогичные преобразования для второй матрицы: разворачивание ролей и приложений в один столбец.
- _g, _h — сопоставление ролей персоны к приложениям ролей.
- Дальше идёт подсчёт порядковых номеров и PIVOTBY, который превращает пары (persona, app) в сводную таблицу, где приложения размещены в колонках для каждой персоны.
Плюсы: можно получить аккуратно организованную таблицу с приложениями в отдельных колонках; формула даёт управляемый результат в рамках одного выражения.
Минусы: формула большая и сложная для понимания/поддержки; использует функции, доступные только в последних версиях Excel (Microsoft 365).
Вариант 2 — Power Query (рекомендуется для удобства и производительности)
Если вы не ограничены только формулами, Power Query — отличный выбор: легко читаемо, масштабируемо и лучше при больших объёмах данных. Ниже — пример M-кода, который превращает две таблицы в нужный итог.
let
SourceOne = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
UnpivotOne = Table.UnpivotOtherColumns(SourceOne, {"Column1"}, "Attribute", "Value"),
SourceTwo = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
UnpivotTwo = Table.UnpivotOtherColumns(SourceTwo, {"Column1"}, "Attribute", "Value"),
Merge = Table.NestedJoin(UnpivotTwo, {"Column1", "Value"}, UnpivotOne, {"Column1", "Value"}, "UnpivotOne", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "UnpivotOne", {"Attribute"}, {"Attribute.1"}),
RemovedCols = Table.RemoveColumns(Expand,{"Column1", "Value"}),
GroupBy = Table.Group(RemovedCols, {"Attribute"}, {{"All", each _, type table [Attribute=text, Attribute.1=nullable text]}}),
IndexCol = Table.AddColumn(GroupBy, "Custom", each Table.AddIndexColumn([All], "Apps", 1, 1)),
RemovedOthers = Table.SelectColumns(IndexCol,{"Custom"}),
ExpandNew = Table.ExpandTableColumn(RemovedOthers, "Custom", {"Apps", "Attribute", "Attribute.1"}, {"Apps", "Attribute", "Attribute.1"}),
PivotBy = Table.Pivot(ExpandNew, List.Distinct(ExpandNew[Attribute]), "Attribute", "Attribute.1")
in
PivotBy
Объяснение шагов:
- 1) Загружаем обе таблицы в Power Query.
- 2) «Unpivot» (раскладываем) таблицы — это превращает матрицы с заголовками в нормализованные пары (Role, Value) и (App, Value).
- 3) Соединяем (Merge) таблицы по паре (role, маркер X) чтобы для каждой персоны получить связанные приложения.
- 4) Группируем и Pivot’им результат обратно в удобный формат.
Плюсы: читабельный процесс, легко поддерживать и адаптировать; работает отлично с большими данными; результаты можно легко обновлять.
Минусы: требует базовых навыков Power Query; если вы намерены держать всё в формулах — не подойдёт.
Вариант 3 — компактная формула с TOCOL/FILTER/XMATCH
Если вам нужно для каждой персоны получить вертикальный список приложений (один столбец под персоной), подойдёт такая формула (ставится в I11 и копируется вправо на каждую персону):
=TOCOL(IFS(FILTER($C4:$F7,ISNUMBER(XMATCH($B4:$B7,
FILTER($H4:$H7,I4:I7="X"))))="X",$C3:$F3),2,1)
Как это работает (пошагово):
- 1) Вложенный FILTER(FILTER($H4:$H7,I4:I7=»X»)) получает список ролей для текущей персоны (те строки, где в столбце персоны стоит X).
- 2) XMATCH сравнивает роли из таблицы ролей со списком ролей персоны — получается фильтр по строкам в таблице Роль→Приложения.
- 3) Внешний FILTER берёт только те строки таблицы Роль→Приложения, которые относятся к выбранным ролям.
- 4) IFS возвращает названия приложений для ячеек с X, иначе ошибку; TOCOL собирает всё в один столбец и удаляет ошибки.
Замечание: при наличии одинаковых приложений в нескольких ролях формула вернёт повторы. Если повторы надо убрать, оберните результат в UNIQUE.
Вариант 4 — простой и читаемый подход с BYCOL + FILTER + TOCOL
Короткая, очень понятная формула, если у вас Excel с BYCOL, TOCOL и FILTER:
=TOCOL(FILTER($B$1:$E$1,BYCOL(FILTER($B$2:$E$5,H2:H5="X")="X",OR)))
Что делает формула:
- FILTER($B$2:$E$5,H2:H5=»X») — выбирает строки Role→App для тех ролей, где в таблице Persona→Role стоит X.
- Сравнение = «X» даёт маску TRUE/FALSE по ячейкам.
- BYCOL(…, OR) сводит столбцы этой маски в один ряд TRUE/FALSE: есть ли в столбце хотя бы одно TRUE.
- FILTER($B$1:$E$1, …) — фильтрует заголовки приложений по полученной маске, а TOCOL разворачивает их в столбец.
Плюсы: коротко, быстро и очень читаемо. Хорошо подходит, если вам нужен просто список приложений по одной персоне.
Какой вариант выбрать?
- Для простоты и читаемости (и если у вас Excel 365 с TOCOL/BYCOL): используйте вариант с BYCOL (Вариант 4). Это компактно и легко поддерживать.
- Если нужно убрать дубликаты, оберните результат в UNIQUE вокруг формулы или примените DISTINCT в Power Query.
- Если данных много или вы хотите стабильный, легко обновляемый процесс, выбирайте Power Query (Вариант 2). Он масштабируем, прозрачен и подходит для регулярных обновлений/автоматизации.
- Если нужен единый сложный результат в одной формуле (например, свод с приложениями по колонкам), LET-формула (Вариант 1) даёт гибкость и мощность, но она сложнее в понимании и отладке.
Рекомендации и практические замечания
- Проверяйте, чтобы форматы и диапазоны в формулах соответствовали вашим таблицам (заголовки, имена диапазонов или таблиц Excel).
- Если в исходных таблицах могут появляться пустые строки или лишние пробелы, предварительно почистите данные (TRIM, удаление пустых строк) либо сделайте это в Power Query.
- При больших диапазонах формулы с динамическими массивами могут быть медленнее, чем Power Query. Для больших наборов данных Power Query обычно предпочтительнее.
- Если нужна конкретная адаптация формул под ваши реальные адреса ячеек/имена таблиц — приложите пример данных (скриншот или экспорт в CSV) и я помогу подогнать формулы.
Нужен ли вам конкретный вариант для вашей рабочей книги — укажите, какие функции доступны в вашем Excel (Power Query, Excel 365 с TOCOL/BYCOL и т. п.) и пришлите пример диапазонов/имён таблиц — и я адаптирую подходящую формулу или шаги Power Query под ваш файл.
