Excel 2013 |列出数组中非空白单元格的内容 [英] Excel 2013 | List content of non-blank cells in array

问题描述

在Excel 2013中，我需要具有从H1到H10的单元格中列出的数组A9:E18的单元格的内容(因为我已经知道该数组中始终总是有10个非空白的单元格，其他人为空).我找不到合适的公式来完成它.有人可以帮我吗?

解决方案

我建议您将 FILTERXML()与 TEXTJOIN()函数一起使用.不幸的是 Excel2013 没有 TEXTJOIN()函数.您可以在本文中使用自定义的 TEXTJOIN()函数

In Excel 2013 I need to have the content of the cells on the array A9:E18 listed in the cells from H1 to H10 (because I already know that there will always be exactly 10 non-blank cells in that array, the rest of them being empty). I cannot find a proper formula to have it done. Could anybody help me, please?

解决方案

I would suggest you to use FILTERXML() with TEXTJOIN() function. Unfortunately Excel2013 doesn't have TEXTJOIN() function. You can use a custom TEXTJOIN() function from this article TextJoin UDF For Excel 2013 of @Scott Craner answer. Then use FILTERXML() like below.

=IFERROR(INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,$A$1:$E$18)&"</s></t>","//s"),ROW($A1)),"")

With Excel365 you can simply use-

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,$A$1:$E$18)&"</s></t>","//s")

这篇关于Excel 2013 |列出数组中非空白单元格的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！