Excel - 过滤列A中的唯一值,并将相关的值从列B移动到其他列 [英] Excel - Filter unique values from Column A and move related values from Column B to other columns
问题描述
我有这样的excel
I have a excel like this
Col1 - Col2
A - 1
B - 2
A - 3
B - 4
A - 5
我想从上面的一个生成一个excel,如下所示
I want to produce an excel from above one as shown below
Col1 - Col2 - Col3 - Col4
A - 1 - 3 - 5
B - 2 - 4
有没有办法在excel做这个?
Is there a way in excel to do this?
请帮助
推荐答案
这是一种方法与公式。首先需要一点格式化,所以也许将你的数据复制到新的工作表,然后进行调整。
Here's a way to do it with formulas. It takes a small bit of formatting first though, so perhaps copy your data to a new sheet, then tweak.
首先,从列A中获取唯一的值。在你的例如,这些将是 A
和 B
。 (对于长列表,将列A复制到一些随机列中,选择该列,转到数据 - >删除重复,这将为您提供唯一的列表)。
First, get the unique values from Column A. In your example, these would be A
and B
. (For long lists, copy column A to some random column. Select that column, go to Data--> Remove Duplicates. This will give you your unique list).
列出之后,将其放在D列(例如)中。然后,在列E中输入此公式作为数组(用 CTRL + SHIFT + ENTER 输入):
After you have the list, place it in Column D (for example). Then, enter this formula as an array in column E (enter with CTRL+SHIFT+ENTER):
=IFERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D1,ROW($A$1:$A$6)-ROW($A$1)+1),ROWS(INDIRECT("$A$1:A"&COLUMN()-4)))),"")
一对情侣注释:根据需要调整行1到6的范围。另外,如果选择列E以外的列来放入,则调整末尾的 Column() - 4
部分,以使列号减去另一个数字在我的例子中, 4
)等于1.(在我的公式中, Column()
返回5(对于E )和5-4 = 1。
A couple notes: Adjust the range from rows 1 to 6 as necessary. Also, if you choose a column other than column E to put this in, adjust the Column()-4
part at the end so that the column number minus the other number (in my example, 4
) equals 1. (In my formula the Column()
returns 5 (for "E") and 5-4=1.
然后,您可以拖曳,然后拖曳,它应该可以工作。
You can then drag over, and down, and it should work.
这篇关于Excel - 过滤列A中的唯一值,并将相关的值从列B移动到其他列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!