将多个列中的值合并为一个 [英] Merge values in multiple columns into one

问题描述

我有以下数据结构：






如您在列J 中看到的，我试图将数据合并到列 A & C & E & <强G 即可。

I have the following data structure:



As you see in column J, I am trying to merge data into one column from columns A & C & E & G.

我正在使用以下公式：

I am using this formula:

=IF(ROW()<=COUNTA($A:$A);INDEX($A:$C;ROW();COLUMN(A1));INDEX($A:$C;ROW()-COUNTA($A:$A)+1;COLUMN(C1)))

我得到列 K 。目前这个公式只合并了两列。 如何修改它合并所有四列？

and I get the values in column K as you see. Currently this formula is merging only two columns. How to modify it to merge all four columns?

如何只从第5行？

列高度会不断变化：有时在列A中有10个值，有时有2个值。

And how to only get those values starting from row 5?
The column height will vary constantly: sometimes there are 10 values in column A and sometimes there are 2 values.

任何excel公式或任何VBA代码都可以接受。

推荐答案

有一个相当标准的方法来检索唯一列而不是多列的值。要实现从多个列的检索，您需要堆叠多个公式，并将处理传递给相继的列，一个较早的公式出错。

There is a fairly standard method for retrieving unique values from a column but not multiple columns. To achieve the retrieval from multiple columns you need to stack multiple formulas together with the processing being passed to successive columns one the earlier formula errors out.

数组公式 ¹在J5中，

=IFERROR(INDEX($A$5:$A$99, MATCH(0, IF(LEN($A$5:$A$99), COUNTIF(J$4:J4, $A$5:$A$99), 1), 0)),
 IFERROR(INDEX($C$5:$C$99, MATCH(0, IF(LEN($C$5:$C$99), COUNTIF(J$4:J4, $C$5:$C$99), 1), 0)),
 IFERROR(INDEX($E$5:$E$99, MATCH(0, IF(LEN($E$5:$E$99), COUNTIF(J$4:J4, $E$5:$E$99), 1), 0)),
 IFERROR(INDEX($G$5:$G$99, MATCH(0, IF(LEN($G$5:$G$99), COUNTIF(J$4:J4, $G$5:$G$99), 1), 0)),
 ""))))

我只包括列A，C，E和G作为您的样本数据仅显示B，D，F和H列中的重复项。

I have only included columns A, C, E and G as your sample data shows only duplicates in columns B, D, F, and H.

¹ _{Array FORMUL根据需要用 Ctrl + Shift + 输入。如果输入正确，Excel将括号中的公式（例如 {和} ）。你不要在自己的大括号中输入。一旦正确输入第一个单元格，就可以像其他任何公式一样填充或复制。尝试并减少您的全列引用范围更加密切地表示实际数据的范围。阵列公式对数计算循环，所以将参考范围缩小到最小值是一个很好的做法。请参阅有关更多信息的数组公式的指南和示例。}

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

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