如何用纯公式在Google Sheets中提取关键词间多行数据到相邻列

可以通过标记起始/结束行号+批量提取数据块+横向合并的思路实现，以下是适配需求的完整Excel公式：

=LET(
    range, B:B,
    start_rows, FILTER(ROW(range), range="Specific competencies."),
    end_rows, FILTER(ROW(range), REGEXMATCH(range, "^Basic knowledge of.* courses?\.$")),
    block_count, MIN(ROWS(start_rows), ROWS(end_rows)),
    extract_block, LAMBDA(i, 
        LET(
            start, INDEX(start_rows, i)+1,
            end, INDEX(end_rows, i)-1,
            IF(start>end, "", FILTER(range, ISBETWEEN(ROW(range), start, end)))
        )
    ),
    blocks, REDUCE("", SEQUENCE(block_count), LAMBDA(acc, i, HSTACK(acc, extract_block(i)))),
    DROP(blocks, , 1)
)

公式核心逻辑拆解 #

• start_rows & end_rows：分别提取所有起始标记行号（Specific competencies.所在行）和结束标记行号（匹配正则规则的行）
• block_count：取起始/结束标记的最小数量，避免因标记数量不匹配导致报错
• extract_block 自定义函数：根据索引i提取第i个数据块——起始行是标记行的下一行，结束行是结束标记的上一行，通过FILTER+ISBETWEEN过滤出中间数据
• REDUCE+HSTACK：遍历所有数据块，将每个块横向合并为多列
• DROP：移除REDUCE初始生成的空列

可选优化 #

如果需要过滤提取结果中的空白行，可修改extract_block内的FILTER语句：

FILTER(range, ISBETWEEN(ROW(range), start, end)*NOT(ISBLANK(range)))

内容的提问来源于stack exchange，提问作者zagalico