使用公式过滤数组(无VBA) [英] Filter an array using a formula (without VBA)
本文介绍了使用公式过滤数组(无VBA)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以使用单个公式(没有自动过滤器,VBA或其他列)过滤数组?
Is it possible to filter an array using a single formula (without autofilter, VBA, or additional columns)?
例如,我有以下电子表格:
For example, I have the following spreadsheet:
A | B | C
--------------------
1| ID | Class | Value
2| 1 | A | V1
3| 1 | B | V2
4| 2 | A | V3
5| 3 | B | V4
6| 3 | B | V5
我想在VLOOKUP中使用此数组的子集.即,我只想与类为"B"的那些行匹配.所以我希望我可以使用类似以下的内容
I want to use a subset of this array in VLOOKUP. Namely, I only want to match against those rows where class is "B". So I was hoping I could use something like the following
=VLOOKUP(A3, FILTER_FUNC(A:C, B="B"), 3, false)
其中FILTER_FUNC是某种类型的函数或表达式,该函数或表达式返回仅包含满足条件的行的数组.
where FILTER_FUNC is some type of function or expression that returns an array that only contains those rows that meet the condition.
推荐答案
=VLOOKUP(A2,IF(B1:B3="B",A1:C3,""),1,FALSE)
Ctrl+Shift+Enter
进入.
这篇关于使用公式过滤数组(无VBA)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文