☰

交错（合并）两个溢出范围

tag-icon • tag-icon microsoft-excel worksheet-function
交错（合并）两个溢出范围

我正在尝试合并两个大小相同的溢出范围，以便溢出范围 A 的第一列位于溢出范围 B 的第一列旁边。

我接近找到答案了，但与此同时，我感觉我完全走错了方向。

举个例子：
在A1输入公式=SEQUENCE(3,2,1)。
在A5输入公式=CHAR(SEQUENCE(3,2,65))

这将给出以下两个范围。我想要的结果在第 9 行到第 11 行。
在此处输入图片描述

我一直想用 来做这件事FILTERXML。

这将连接两个范围并开始构建 xml 字符串（在单元格中输入F1）：

=LET(ColumnNum,COLUMN(A1#),
     FirstTable, A1#,
     SecondTable, A5#,
     PrefixA, "<a" & ColumnNum & ">",
     SuffixA, "</a" & ColumnNum & ">",
     PrefixB, "<b" & ColumnNum & ">",
     SuffixB, "</b" & ColumnNum & ">",
     xml, PrefixA & FirstTable & SuffixA &
          PrefixB & SecondTable & SuffixB,
     xml)

这完成了 xml 并返回最终溢出的范围。

=LET(StartCol,COLUMN(F1),
     xml,F1#,
     LastCol,StartCol+COLUMNS(xml)-1,
     finalxml,"<y>" & TEXTJOIN("",FALSE,IF(COLUMN(xml)=StartCol,"<x>","") & xml & IF(COLUMN(xml)=LastCol,"</x>","")) & "</y>",
     CHOOSE(SEQUENCE(,COLUMNS(xml)*2,1),
            FILTERXML(finalxml,"//x/a1"),
            FILTERXML(finalxml,"//x/b1"),
            FILTERXML(finalxml,"//x/a2"),
            FILTERXML(finalxml,"//x/b2")))

这对于两列的两个范围来说工作得很好，但是列数会发生变化，因此CHOOSE每次都需要手动更新语句。

有办法让它工作吗？很高兴能找到LAMBDA解决方案，但担心我不能使用新TEXTSPLIT类型的功能。

答案1

与您当前的公式相比，这是一个更容易扩展的公式：

=LET(t_count, 3, tables, (A1#,A5#,A9#), row_count, ROWS(INDEX(tables,0,0,1)), col_count, COLUMNS(INDEX(tables, 0, 0, 1)) * t_count, s, SEQUENCE(row_count, col_count, 0), INDEX(tables, INT(s / col_count) + 1, INT(MOD(s, col_count) / t_count) + 1, MOD(s, t_count) + 1))

或者将相同的公式分解以便更好地概述：

=LET(
    t_count, 3, 
    tables, ( A1#, A5#, A9#), 
    row_count, ROWS(INDEX(tables, 0, 0, 1)), 
    col_count, COLUMNS(INDEX(tables, 0, 0, 1)) * t_count,  
    s, SEQUENCE(row_count, col_count, 0), 
    INDEX(
        tables, 
        INT(s / col_count) + 1, 
        INT(MOD(s, col_count) / t_count) + 1, 
        MOD(s, t_count) + 1
    )
 )

要修改公式，您只需更新t_count并列出所有范围tables。

在此处输入图片描述

答案2

在A9中

=INDEX($A$1#,0,INT((COLUMN()-1)/2)+1)

并在 B9 中输入

=INDEX($A$5#,0,INT((COLUMN()-1)/2)+1)

现在将 A9:B9 复制到 C9:D9。

如果有超过两列需要交错，则只需从 E9 开始按照两列的倍数范围继续复制即可。

此外，如果您有三个或更多“源”范围，只需扩展第三或第四个范围的公式，然后将“/ 2”更改为“/ 3”或“/ 4”或您正在使用的范围数。

编辑：我一开始想不出从单个动态范围公式解析此问题的方法。遗憾的是，Excel 不允许使用静态数组语法连接数组，例如不允许使用 {A1#, A5#}。但是，最近添加的 MAKEARRAY 和 LAMBDA 提供了一个有趣的解决方案：

单一配方解决方案！

=LET(ranges, ($A$1#,$A$5#), rangecount, AREAS(ranges), rangetext, CELL("address",ranges), MAKEARRAY(ROWS(INDEX(ranges,0,0,1)),COLUMNS(INDEX(ranges,0,0,1))*rangecount, LAMBDA(r,cc, INDEX(($A$1#,$A$5#),r,INT((cc-1)/rangecount)+1,MOD(cc-1,rangecount)+1))))

遗憾的是你不能使用范围由于某种原因（我认为是一个错误），lambda 函数内部会存在此问题。因此最简单的方法是定义两次范围 - 一次在 LET 语句中，一次作为 INDEX 的一部分。

相关内容