Excel：基于公共单元格值对行中的值进行分组 [英] Excel: Group values from rows based on common cell value

问题描述

我有一个Excel文件，其中包含组列表和属于它们的用户。我需要生成属于每个组的用户列表。关键点：虽然这是用perl，python或任何其他编程语言完成的琐碎任务，但我想在Excel中完成全部操作，因此无需导出到csv，重新导入结果并重新-每次进行更改时都要进行格式化。

I have an Excel file that contains a list of groups and the users that belong to them. I need to generate a list of users that belong to each group. Key point: while this is a trivial task in perl, python, or any other programming language, I'd like to do the whole thing in Excel so I don't have to export to csv, re-import the results, and re-do the formatting every time there's a change.

文件格式（导出到csv时）类似于：

The file format (when exported to csv) is akin to:

Group, username, email
Perl Jam, evedder, [e-mail]
Perl Jam, mcameron, [e-mail]
Perl Jam, jament, [e-mail]
Perl Jam, sgossard, [e-mail]
Perl Jam, mmccready, [e-mail]
Soundgarden, mcameron, [e-mail]
Soundgarden, ccornell, [e-mail]
Soundgarden, kthayill, [e-mail]
Soundgarden, bshepherd, [e-mail]
Temple of the Dog, evedder, [e-mail]
Temple of the Dog, ccornell, [e-mail]
Temple of the Dog, jament, [e-mail]
Temple of the Dog, sgossard, [e-mail]
Temple of the Dog, mmccready, [e-mail]
Temple of the Dog, mcameron, [e-mail]

我想结束的是：

Group, usernames
Perl Jam, evedder, mcameron, jament, sgossard, mmccready
Soundgarden, mcameron, ccornell, kthayill, bshepherd
Temple of the Dog, evedder, mcameron, ccornell, jament, sgossard, mmccready

推荐答案

要获取唯一列表，请使用以下数组公式：

To get your unique list use this array formula:

=IFERROR(INDEX($A$2:$A$16,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$16),0)),"")

作为数组公式，退出编辑时需要使用Ctrl-Shift-Enter而不是Enter进行确认模式。如果操作正确，则Excel将在公式周围放置 {} 。

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

在第一个单元格中，按Ctrl-Shift -Enter，然后向下复制，直到获得空白。

Put in first cell, hit Ctrl-Shift-Enter, then copy down till you get blanks.

要获取用户名：

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$16)/($A$2:$A$16=$E2),COLUMN(A:A))),"")

其中$ E2是另一个公式所在的单元格。

Where $E2 is the cell in which the other formula is.

然后反复复制。

这篇关于Excel：基于公共单元格值对行中的值进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！