英文:
Excel Filter Function Include Different Array for Filtering
问题
I wanted to make a filter function, but the array for criteria is referring to a different cell
这里是一个示例
我的数组是 C3:C17,我想根据 A3:A5 列表上的名字来过滤这个数组
这里我尝试使用这个公式
=FILTER(HSTACK($C$3:$D$17),C3:C17=A3:A17)
我希望返回 Agent 1、5 和 10 以及它们相应的 Leaders 列在 F3 单元格上,但是公式只返回了一个结果,即 Agent 1 和 Leader 1
期望/所需结果
英文:
I wanted to make a filter function, but the array for criteria is referring to a different cell
Here is an example
My array is C3:C17, I wanted to filter the names on this array based on the list from A3:A5
Here I tried using this formula
=FILTER(HSTACK($C$3:$D$17),C3:C17=A3:A17)
I am expecting to return Agent 1, 5 and 10 with their corresponding Leaders on cell F3 but instead the formula only return 1 result which is Agent 1 and Leader 1
Expecting/Desired result
答案1
得分: 3
使用 ISNUMBER
和 XMATCH
:
=FILTER(C3:D17,ISNUMBER(XMATCH(C3:C17,A3:A5)))
答案2
得分: 3
另一个选择: BigBen 先生很快,否则我可能会选择使用 ISNUMBER()
和 XMATCH()
• 在单元格 F3
中使用的公式
=FILTER(C3:D17,MMULT(N(C3:C17=TOROW(A3:A5)),SEQUENCE(ROWS(A3:A5))))
为了避免在名单中进行额外更改,这将使用 TOROW()
和 DROP()
函数: -> 第一个用于排除空白,而后者用于排除前面的 2
行。
• 在单元格 F3
中使用的公式
=LET(α,TOROW(DROP(A:A,2),1),
FILTER(C3:D17,MMULT(N(α=C3:C17),SEQUENCE(COLUMNS(α)))))
英文:
Another alternative: BigBen Sir was pretty quick otherwise I would have opted for ISNUMBER()
with XMATCH()
• Formula used in cell F3
=FILTER(C3:D17,MMULT(N(C3:C17=TOROW(A3:A5)),SEQUENCE(ROWS(A3:A5))))
To avoid additional changes in the roster, this will work using TOROW()
& DROP()
function: -> The first one is used to exclude the blanks while the latter is used to exclude the top 2
rows.
• Formula used in cell F3
=LET(α,TOROW(DROP(A:A,2),1),
FILTER(C3:D17,MMULT(N(α=C3:C17),SEQUENCE(COLUMNS(α)))))
答案3
得分: 2
使用 INDEX/XMATCH
进行筛选
- 请注意,此简化仅在您希望返回与“查找”列 (
A
) 中的值数量相同的结果时有效,即如果列C
中没有重复值。否则,您需要使用FILTER
并切换XMATCH
参数,类似于 BigBen 在他的答案中所做的操作。 - 请注意,您不需要变量
l
、s
、dr
和dc
,您可以将所有内容写成一行。它们仅用于更好地理解正在发生的情况,即使使其更可读。
=LET(lrg,A3:A17,srg,C3:D17,sc,1,
l,TOCOL(lrg,3),s,INDEX(srg,,sc),
dr,XMATCH(l,s),dc,SEQUENCE(,COLUMNS(srg)),
IFERROR(INDEX(srg,dr,dc),""))
英文:
Filter Using INDEX/XMATCH
- Note that this simplification only works if you want to return as many results as there are values in the 'lookup' column (
A
) i.e. if there are no duplicates in columnC
. Otherwise, you need to useFILTER
and switch theXMATCH
parameters i.e. do something like BigBen did in his answer. - Note that you don't need the variables
l
,s
,dr
, anddc
i.e. you can write all of it in one long line. They are used just to better understand what's going on i.e. to make it more readable.
=LET(lrg,A3:A17,srg,C3:D17,sc,1,
l,TOCOL(lrg,3),s,INDEX(srg,,sc),
dr,XMATCH(l,s),dc,SEQUENCE(,COLUMNS(srg)),
IFERROR(INDEX(srg,dr,dc),""))
答案4
得分: 2
另一种替代方法:
在F3
中的公式:
=FILTER(C3:D17,COUNTIF(A3:A5,C3:C17))
或者简单点(但速度较慢):
=FILTER(C:D,COUNTIF(A3:A5,C:C))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论