Excel：从列A获取所有值，其中列B等于所选值

I have an excel with all teams and their respective team members.

我对所有团队及其各自的团队成员都表现出色。

In the same excel file I have a sheet where you can select a team, and based on the value I should fill out all of that team's members in a cell on that sheet.

在同一个excel文件中,我有一个工作表,您可以在其中选择一个团队,并根据该值,我应该在该工作表的单元格中填写该团队的所有成员。

Currently I have a solution where the user will need to filter the list of members to only include the members of his/her team with the following function:

目前我有一个解决方案,用户需要过滤成员列表,只包括他/她的团队成员,具有以下功能:

=TEXTJOIN(",",TRUE,'Team members'!A2:A13)

I now need to edit this formula to make sure I only get members of a selected team (instead of a hard coded one).

我现在需要编辑这个公式,以确保我只获得所选团队的成员(而不是硬编码团队成员)。

Team Members Sheet:

团队成员表:

In this second sheet (CoverSheet) the user needs to select one of the teams from the screenshot above in cell A2. Based on the value in A2 the result in B2 should change to list all the team members.

在第二张表(CoverSheet)中,用户需要从单元格A2中的屏幕截图中选择一个团队。根据A2中的值,B2中的结果应更改为列出所有团队成员。

EDIT

I have just tried out the solution provided below but when I try it on the actual Excel that's being used I get a #VALUE! error because the total length of the result would go over a certain number of characters. I assume this based on the remarks in this article: https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

我刚刚尝试了下面提供的解决方案但是当我在正在使用的实际Excel上尝试时,我得到一个#VALUE!错误,因为结果的总长度将超过一定数量的字符。我假设这是基于本文中的评论:https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

Does anyone have an idea of how to work around this?

有没有人知道如何解决这个问题?

2 个解决方案

#1

Since you have the formula =TEXTJOIN(",",TRUE,'Team members'!A2:A13) in your question I assume you are using Excel 2016 in which case we can modify this formula to work for what you need I think. I do not have 2016 or office 365 so I can not test.

由于您在问题中有公式= TEXTJOIN(“,”,TRUE,'团队成员'!A2:A13),我假设您使用的是Excel 2016,在这种情况下我们可以修改此公式以满足您的需要。我没有2016或Office 365所以我无法测试。

=TEXTJOIN(",",TRUE,If('Team Members'!B2:B13='CoverSheet'!B1,'Team members'!A2:A13,"")

You will need to hit Ctrl + Shift + Enter to make this work. Thanks @Michael for pointing that out.

您需要按Ctrl + Shift + Enter才能使其正常工作。谢谢@Michael指出这一点。

#2

You could use the materix formulas applied at the function TEXTJOIN. Something like:

您可以使用在TEXTJOIN函数中应用的materix公式。就像是:

=TEXTJOIN(",",TRUE,IF(B:B=F3,A:A))

Instead of press ENTER, you have to put the cursor on "B:B" and then hit CTRL + SHIFT + ENTER. This will add curly brackets to your formula and transform it in a matrix formula.

而不是按ENTER键,你必须将光标放在“B:B”上,然后按CTRL + SHIFT + ENTER。这将为您的公式添加大括号并将其转换为矩阵公式。

With the spreadsheet

使用电子表格

+---+---------+--------+
|   |    A    |   B    |
+---+---------+--------+
| 1 | User    | Team   |
| 2 | User 01 | Team 1 |
| 3 | User 02 | Team 1 |
| 4 | User 03 | Team 2 |
| 5 | User 04 | Team 3 |
+---+---------+--------+

Where F3 contain the Team name (i.e. "Team 1"). I don't have access to Excel 2016 at the moment so I can't be more practical.

其中F3包含团队名称(即“团队1”)。我目前无法访问Excel 2016,所以我不能更实用。

Please consider this answer that you coluld apply to your formula.

请考虑您对该公式适用的答案。

#1

=TEXTJOIN(",",TRUE,If('Team Members'!B2:B13='CoverSheet'!B1,'Team members'!A2:A13,"")