Microsoft Excel - 将多个交错行内的数据移动到单行

Question 1

嗯……小图片等没关系。它们足够清晰，可以作为一般解决方案。每列中只有一个项目，并且希望它们都位于一行中。

后期补充：一旦您插入一行，如下所示，您实际上可以简单地将以下公式放在插入行的 B 列单元格中：

=INDEX(SORT(B2:B10,,-1),1)

然后将其复制到剪贴板并粘贴到该行的所有单元格中。

你甚至可以这样做：

=IF(ISNUMBER(INDEX(SORT(B2:B10,,-1),1)),VALUE(INDEX(SORT(B2:B10,,-1),1)),INDEX(SORT(B2:B10,,-1),1))

使得下面提到的“数字作为文本，但大部分可用”的问题。

（抱歉，看了下面这些之后才想到这个。）

Isolated如果数据全是数字（很可能如此），那么这种方法可以很好地解决问题。但如果不是，只要九个行集中每列确实有一个项目，下面的方法就可以提供通用解决方案。

=SUBSTITUTE(IFERROR(TRANSPOSE(FILTERXML("<group><element>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYTOTEXT(TRANSPOSE(B2:P10),1),",",""),"{",""),"}",""),";","</element><element>")&"</element></group>","/*/*")),""),CHAR(34),"")

注意：其结果都是文本，但可直接用于计算。如果某个值为 9.2，虽然它显示为 '9.2（因此...像字母一样左对齐，不会“接受”您对其执行的任何数字格式），但如果另一个单元格将 5 添加到它所在的单元格，则您将得到 14.2。您可以在上述内容周围添加更多看似密集的步骤来解决这个问题，但我在将所有内容移动到一行时就停止了。

首先，由于九行中的第一行（如果九行只是一个例子，则可能是多行）可能有（或者肯定有）自己的值需要保留，因此您需要插入一个空白行，大概就在这些行的上方或下方。填充后，您可以复制它并粘贴特殊值，最后删除原始的九行。

通常在这种情况下，人们会用TEXTJOIN()手头的材料制作单个字符串，因为人们希望去掉空字符串，它会直接帮你做到这一点，不需要像这里一样进行额外的步骤。但是如果你在这里丢失了空字符串，你也会失去将结果放入它们开始所在的列中的能力。

如此ARRAYTOTEXT()使用。它可以生成带有逗号空格分隔符的字符串，但这样会丢失所有列信息。使用其“严格”形式会在列末尾放置分号。这是必要的。

它还添加了一个{，一个}删除SUBSTITUTE()它们（这里使用了该函数的两个功能），并且它在任何文本元素周围加上双引号（是的，它将 9.2 与“9.2”区分开来，直到最后，然后无论如何都将其显示为文本……想想看……）。这些是在最后删除的，但如果您希望在一组函数中完成所有这些替换，则可能会在“内部”删除更多。

首先你会得到如下字符串：

"""a"";""g"";""e"";9.2;""r"";""k"";;;;;""w"";""e""""r"";""b"";""h"";""k"""

（它稍后会删除所有多余的"字符，只留下每个文本元素周围的单对字符来清除字符。我没有尝试过，只是对自己说“让我们删除"最后的字符，这样我们就不会把任何东西弄乱了。”）

注意到了其中的;'s 为我们维护了列吗？包括中间的集合，最终结果会给出中间的四个空单元格吗？（尝试匹配您的模式，如果不是不可读的数据。）使用严格形式允许这样做。

TRANSPOSE()使用了两次。埋在里面的那个是因为ARRAYTOTEXT()跨列读取，然后向下读取到范围内的下一行，而我们需要向下读取列，然后向上读取到下一列的第一行。靠近公式开头的那个是因为FILTERXML()。它希望在单列中显示结果，而您需要将其显示在单行中。幸运的是，这两次您需要的转置都是简单的转置TRANSPOSE()。但如果您需要完全相反的操作，从右下角开始向上读取，然后读取到下一列等等，您可以使用这种INDEX()方式重新排列内容。它可以进行所有四种符合几何意义的重新排列。

最后，由于这些空列会导致ERRORS!这些单元格中出现，因此我们使用IFERROR()来代替放置在这些单元格中。顺便说一句，虽然这解决了这个问题，但如果这在使用数据的任何地方都会产生影响，那么""它确实会产生影响。not-BLANK

然后最后一步是"从文本结果周围删除 s。

所有这些都是使结果可用的表面文章。公式中的实际引擎，使一切发挥作用的引擎，用于FILTERXML()获取经过处理的输出ARRAYTOTEXT()并将其从单个字符串更改回单独的数据片段，包括空列的贡献，以便它可以填充一行而不是单个单元格。

基本上，我们在ARRAYTOTEXT()结果之前和之后使用一个字符串，并SUBSITUTE()在这些ARRAYTOTEXT()结果上使用一个字符串来制作一个FILTERXML()可以理解的 HTML 字符串。

（还记得;我们上面非常重视的那些字符吗？实际上，可以使用稍微复杂一点的 HTML 格式来完成这项工作，我们可以取消该SUBSTITUTE()层。稍微复杂一点，我们可以取消{和}字符。但这对于第一次看到这种技术的人来说并不方便，所以我在这里没有这样做。）

所以总结一下，我们对范围内的所有数据进行了仔细的字符串处理，然后对其进行修改以删除不需要的内容，添加并进一步修改以生成 HTML 字符串，然后将该字符串转换为一组 Excel 单元格值，并做了一些最后的修改。

公式中只有一个可更改的内容，那就是要处理的范围。在上面，它是 B2:P10。如果您需要更改它，因为比如说，一组数据有 4 行或 14 行，只需更改它即可。您甚至可以添加LET()整个内容，并为范围命名。这样做的目的是，范围现在正好位于公式的开头，第一行，因此很容易看到和更改。这比埋在公式的行中容易得多，而且很难在密集的混乱中看到。在这种情况下，没有太多帮助，只有一些帮助，但如果您有几件事情可能需要编辑，那么这样做会非常有用。

在开始对要整理的行进行分组之前，您需要这样做Sort。还要记住，您插入一行，将公式粘贴到数据列上方的第一个单元格中（该行的 B 列单元格），然后复制到剪贴板并粘贴特殊（值）以获得一个干净的（没有公式的）行和结果，最后，通过删除行来删除所有原始数据。

如果您想要一个简单的解决方案，让数字条目显示为文本，尽管在 Excel 中基本上可以用作数字……大多数情况下……通过将它们改回数字，在 Excel 发现数字有用的地方很有用，您可以完成您的工作，然后使用Paste|Special|Multiply乘以 1 的技术（在某个单元格中输入“1”，将单元格格式化为您想要显示数字的方式，然后将该单元格复制到剪贴板），所有这些都将改回完全正确的数字，而文本不会受到影响（没有错误！）。

Answer