转置和分组数据 [英] Transpose and group data
问题描述
我需要在行中转置两列,并按第一列进行分组;这里是一个例子。
从这里:
AB
IP1 21
IP1 22
IP1 23
IP2 80
IP2 443
IP3 21
IP3 22
IP3 23
IP3 80
IP3 443
为此:
ABCDEF
IP1 21 22 23
IP2 80 443
IP3 21 22 23 80 443
我该怎么做?可以避免使用宏和VBA吗?
您最好使用 VBA
,但如果您真的需要公式解决方案:
D2 = IFERROR(INDEX($ A $ 1: $ A $ 19,MATCH(0,COUNTIF($ D $ 1:D1,$ A $ 1:$ A $ 19),0)),0)
并将其拖动以复制。 / p>
然后,我们需要查找第1,第2,第3等匹配:
E2 = IFERROR(INDEX($ B $ 1: $ B $ 19,SMALL(IF($ D2 = $ A $ 1:$ A $ 19,ROW($ A $ 1:$ A $ 19)-MIN(ROW($ A $ 1:$ A $ 19))+ 1,), E $ 1)),0)
再次拖动它,向右拖动以填充 E2上的所有单元格:N12
注意:
Rememeber可以使用 CTRL + SHIFT + ENTER $ c $输入两个公式因为它们都是数组公式。根据您的数据范围更新范围。
I need to transpose two column in rows, and group by first column; here is an example. From this:
A B
IP1 21
IP1 22
IP1 23
IP2 80
IP2 443
IP3 21
IP3 22
IP3 23
IP3 80
IP3 443
To this:
A B C D E F
IP1 21 22 23
IP2 80 443
IP3 21 22 23 80 443
How can I do this? Can I avoid the use of macro and VBA?
You better use VBA
, but if you really need formula solution:
First, you need to create Unique list:
D2=IFERROR(INDEX($A$1:$A$19, MATCH(0, COUNTIF($D$1:D1, $A$1:$A$19), 0)),0)
And drag it down to copy.
Then, we need to lookup for 1st, 2nd, 3rd, etc match:
E2=IFERROR(INDEX($B$1:$B$19, SMALL(IF($D2=$A$1:$A$19, ROW($A$1:$A$19)-MIN(ROW($A$1:$A$19))+1, ""), E$1)),0)
Again drag it down, drag it to the right to fill in all cells on E2:N12
Note:
Rememeber to enter both formulas with CTRL+SHIFT+ENTER
as they are both array formulas. Update ranges according to your data ranges.
这篇关于转置和分组数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!