Как применить формулу массива к каждому значению, возвращаемому другой формулой массива?

Я использую Microsoft 365 и хочу найти способ в Excel последовательно применять формулы массива – одну на выходе другой – для получения 2D таблицы.

Конкретно, я хочу применить regex с группами захвата к предоставленному пользователем блоку текста. В идеале REGEXEXTRACT() должен возвращать 2D массив, где каждая строка соответствует совпадению, а каждый столбец соответствует группе захвата. Однако в настоящее время REGEXEXTRACT() возвращает (используя аргумент return_mode) либо 1D массив, содержащий группы захвата первого совпадения, либо 1D массив, содержащий каждое полное совпадение (не учитывая группы захвата).

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

К сожалению, хотя 1) первый REGEXEXTRACT() работает нормально, 2) второй REGEXEXTRACT() работает нормально, когда применяется вручную к каждой строке, выводимой из первого, я не смог найти способ успешно автоматически воспроизводить формулу для любой строки. Если я использую оператор разлива #, хотя формула затем распространяется вертикально, она не выводит ничего горизонтально, что значит, возвращается только первая группа захвата.

Вот упрощенный пример, показывающий работу двух формул REGEXEXTRACT() для первой строки:

• B1: Regex (в данном случае возвращает две группы захвата числовых данных)
• B2: строка для поиска
• B5: =TRANSPOSE(REGEXEXTRACT(B2,B1,1,1))
• C5: =REGEXEXTRACT(B5,$B$1,2,1)

Вот пример, показывающий, что разлив не работает одновременно по горизонтали и вертикали:

• B1, B2, B5: как выше
• C5: =REGEXEXTRACT(B5#,$B$1,2,1)

Я уверен, что это можно сделать в VBA, но я предпочел бы не прибегать к этому. Я чувствую, что упускаю что-то простое – есть ли это?

Существует известное (и обоснованное) ограничение обработки массивов в Excel, часто описываемое как “ограничение массива массивов”.

Существует множество способов обойти это ограничение. Возможно, один из самых распространенных – так называемый паттерн reduce/vstack.

В общем смысле, мы используем REDUCE для итерации по входному массиву, и внутри его LAMBDA используем VSTACK для накопления результатов обработки каждой входной строки.

Предположим, у меня есть ваши данные и еще одна строка (чтобы продемонстрировать, что это работает для нескольких строк) в B3:B4, тогда эта формула вернет результат:

=DROP(
    REDUCE(
        0,
        B3:B4,
        LAMBDA(a, b,
            LET(
                ar, TEXTSPLIT(b, CHAR(10)),
                rg, DROP(
                    REDUCE(
                        0,
                        ar,
                        LAMBDA(a, b,
                            VSTACK(a, REGEXEXTRACT(b, "(\d+).(\d+)", 2, 1))
                        )
                    ),
                    1
                ),
                VSTACK(a, HSTACK(EXPAND(b, ROWS(rg), 1, b), rg))
            )
        )
    ),
    1
)

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

После импорта функций STACKER в Advanced Formula Environment в Excel Labs:

Вам нужно только определить функцию для решения одной строки вашего ввода:

// Определите функцию, которая обрабатывает одну строку
SPLITLINES = LAMBDA(row,
    LET(
        ts, TEXTSPLIT(row,CHAR(10)),
        HSTACK(EXPAND(row, COLUMNS(ts), 1, row), TOCOL(ts))
    )
);

// Определите функцию, которая обрабатывает одно измерение
SPLITDIMENSIONS = LAMBDA(row,
    HSTACK(row,REGEXEXTRACT(INDEX(row,1,2),"(\d+).(\d+)",2,1))
);

И затем формула для решения вашей проблемы становится проще:

=VSTACKER(SPLITDIMENSIONS)(VSTACKER(SPLITLINES)(B3:B4))

Если вы хотите узнать больше о STACKER, вы можете просмотреть это видео и это последующее видео.

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

РЕДАКТИРОВАНИЕ:

Для обработки одной строки вы можете использовать эту формулу:

=DROP(REDUCE(0,TEXTSPLIT(B3,,CHAR(10)),LAMBDA(a,b,VSTACK(a,REGEXEXTRACT(b,"(\d+).(\d+)",2,1)))),1)

РЕДАКТИРОВАНИЕ 2:

Я просто хочу отметить, что для этой конкретной задачи, когда нужно разделить только 1 ячейку, самый простой ответ – это TEXTSPLIT. Я разместил отдельный ответ для этого.

С Excel 365 вы также можете использовать функции Python.

В вашем случае это кажется гораздо более простым решением:

[cell.split("x") for cell in xl("B2").splitlines()]

Вы отметили в комментарии к моему другому более общему ответу, что вам нужно применить это только к одной ячейке. В этом случае, вероятно, самый простой способ – это:

=TEXTSPLIT(B3,"x",CHAR(10))

Где “x” – это разделитель столбцов, а CHAR(10), который представляет разрыв строки, – это разделитель строк.