如何在Excel中合并多个列 [英] How to combine multiple columns in excel

问题描述

我使用的是在Excel中最多合并三列的公式:

I'm using this formula that combines up to 3 columns in excel:

=INDIRECT("A"&ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98)),0))&" "&INDIRECT("B"&MOD(ROUNDUP((ROW())/COUNTA($C$1:$C$98),0)-1,COUNTA($B$1:$B$98))+1)&" "&INDIRECT("C"&MOD(ROUNDUP((ROW()),0)-1,COUNTA($C$1:$C$98))+1)

它将基本上将以下三列的内容结合起来:

It will basically combine the content of three columns like this:

3列效果很好，但是，我尝试修改公式以支持第4列(可能还有更多列)，但是没有正确组合单词.我想知道是否有人可以向我展示如何修改它以使其与4列或更多列一起使用.

Works great with 3 columns, however, I tried modifying the formula to support a fourth column (and perhaps more), but it's not combining the words properly. I was wondering if someone could show me how to modify it to make it work with 4 and maybe more columns.

谢谢！

推荐答案

如果分解公式，您会注意到涉及到对INDIRECT的调用的模式，因此删除每个列的内部公式，您将得到以下内容: /p>

If you break down the formula, you'll notice a pattern involving calls to INDIRECT, so removing the inner formulas for each column you have something like this:

=INDIRECT("A"&XXX)
&" "&INDIRECT("B"&YYY)
&" "&INDIRECT("C"&ZZZ)

其中XXX，YYY和ZZZ是每列的相应公式.因此，您要做的就是附加一个附加的&" "&INDIRECT("D"&TTT)，其中TTT是新的第四列的公式.

Where XXX, YYY, and ZZZ are the respective formulas for each column. Therefore, all you need to do is append an additional &" "&INDIRECT("D"&TTT) where TTT is the formula for the new 4th column.

扩展我的答案，您正在寻找的功能如下:

Extending my answer, the function you are looking for looks like this:

=INDIRECT("A"&ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98),COUNTA($D$1:$D$98)),0))&" "&INDIRECT("B"&ROUNDUP((ROW())/PRODUCT(COUNTA($C$1:$C$98),COUNTA($D$1:$D$98)),0))&" "&INDIRECT("C"&MOD(ROUNDUP((ROW())/COUNTA($D$1:$D$98),0)-1,COUNTA($C$1:$C$98))+1)&" "&INDIRECT("D"&MOD(ROUNDUP((ROW()),0)-1,COUNTA($D$1:$D$98))+1)

简而言之，您会注意到最后3个INDIRECT调用与您现有的INDIRECT调用相同，除了所有引用都移动了一个字符(即A变为B，B变为C和C成为D).现在，查看新的第一个INDIRECT调用并将其与第二个调用进行比较:

Breaking it down, you'll notice that the last 3 INDIRECT calls are identical to your existing INDIRECT calls, except all the references are shifted by one character (ie, A became B, B became C and C became D). Now look at the new first INDIRECT call and compare it to the second one:

INDIRECT("A"&ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98),COUNTA($D$1:$D$98)),0))
INDIRECT("B"&ROUNDUP((ROW())/PRODUCT(COUNTA($C$1:$C$98),COUNTA($D$1:$D$98)),0))

您应该注意到它们的不同之处仅在于第一行在PRODUCT函数中包含一个附加的,COUNTA($D$1:$D$98)代码. 使用这些观察结果，您应该可以将公式扩展到其他列，但是正如@Tim Biegeleisen所说，如果您打算扩展函数，则应该寻找其他方法.

You should notice that they differ only in that the first line has an additional ,COUNTA($D$1:$D$98) code inside the PRODUCT function. Using these observations, you should be able to expand the formula to further columns, but as @Tim Biegeleisen said, you should look for a different approach if your intention is to expand the function.

这篇关于如何在Excel中合并多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！