如何将单元格中的字符串转换为行和列范围的数组?

如何将单元格中的字符串转换为行和列范围的数组?

我打算从列表中转换这些名称,在图像中所示的范围矩阵中,在调试公式时,我希望它准确地显示行和列的范围数组,有什么办法可以将这些名称转换为像这样的范围??在此处输入图片描述

答案1

如果您想要文本字符串,请使用:

="={"""&TEXTJOIN(""",""",,SUBSTITUTE($A$2:$A$5," ","""/""")) & """}"

如果您想要一个在其他地方使用的实际数组,那么使用:

IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TRANSPOSE($A$2:$A$5)," ","</b><b>")&"</b></a>","//b["&ROW($A$1:$A$4)&"]"),"")

这将创建一个 4x4 数组,可用于其他公式。根据版本的不同,在另一个公式中使用此数组时,需要在退出编辑模式时使用 Ctrl-Shift-Enter 而不是 Enter 来强制使用数组公式。

我不知道有什么方法可以做您想做的事情F9并在公式栏中显示数组。

答案2

好吧,除了我不会重复的拼写错误“JONH”之外,文字字​​符串可以通过对 Scott Craner 的公式进行稍微调整来获得(我用的是SEQUENCE()技巧ROWS(),必须使用新函数来习惯它们,是吗?),然后用作一些字符串工作的基础:

="={"""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(B234:B237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(C234:C237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(D234:D237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(E234:E237),SEQUENCE(4)))&"""}"

请记住,虽然它给出了字面上的期望输出,但输出是不是从 Excel 的角度来看,这是一个数组常量,并且不会像结果那样被它精确地使用F9

它只是 Excel 关心的文本字符串。因此,在公式中使用它可能会导致失败,或者纯属偶然地成功。对于某些用途,它也许也能正常工作。但它与 Excel 内部使用的数组常量突出显示某些内容并点击F9创建完全不同或等同。

话虽如此,它还是很丑陋和臃肿的。我想我不会在评论中来回跟踪这个线索,所以我不明白为什么 Scott Craner 也TRANSPOSE()强迫我这样做,因为似乎所需的输出是从左到右(上面的评论是“A234 =“Jonh”,...)所以把它拿出来,然后调整他的 A2:A5 为 A1:A4(或者为我的实验删除一行),我得到:

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(($A$234:$A$237)," ","</b><b>")&"</b></a>","//b["&SEQUENCE(1,4)&"]"),"")

他的公式并重新加工我的公式,然后混合他的公式以使用单一公式,我得到:

="={"""&TEXTJOIN("""\""",,B234:E234)&""","""&TEXTJOIN("""\""",,B235:E235)&""","""&TEXTJOIN("""\""",,B236:E236)&""","""&TEXTJOIN("""\""",,B237:E237)&"""}"

顺便说一句,由于它是有图案的,在构造上是重复的,因此可以使用旧的CONCATENATE()union operator几天的技巧来构建它,行和列在最终连接之前进行很好的调整,将其粘贴为值,然后在它前面放置一个“=”以使其成为公式。现在更容易了。对于不是四行且少于五个元素的数据。

F9因此,这就给出了表达的所需结果字符串,但同样,它不是 Excel 所看到的结果,与如上所述使用后看到的数组常量相同。

然后,修改后的 Scott Craner 公式将给出注释中描述的单个单元格结果。该公式本身可以在公式中使用,让 Excel 创建该内部数组常量。但我认为,不要使用字符串公式(从计算上讲,这是一条死路),而是使用 Scott Craner 的(调整后的)公式,无论你需要什么公式,它都是so Excel will evaluate it to the desired数组常量,然后,不是填充这 16 个单元格,而是将其传递到该公式内的计算链中。

刚刚意识到这是南方公园里的“斯科特·泰诺曼”(或接近),卡特曼……对他进行了报复……我不再为一遍又一遍地说“斯科特·克兰纳”而感到困扰了!

相关内容