excel - 如何在 excel 中使用 2 个条件进行 Vlookup
问题描述
我正在评估我的投资组合,并以如下形式记录下来:
我希望从其中任何一个中获得这些股票的利润百分比;
卖出股票:(卖出价/买入价)-1
未售出的股票:(当前价格/买入价)-1
对于 1),我正在考虑通过使用带有 Col B 的“Stock A”和 Col C 的“SELL”的 VLOOKUP 条件来获得股票 A 的最后价格(Col E)(然后显然再次使用带有“SELL”的 VLOOKUP条件更改为“买入”)。
有没有办法在两个条件下查找值?任何其他建议(例如使用一点 VBA)将不胜感激。
解决方案
A实际上是(或,从 Office365 开始)VLOOKUP
的一种特殊形式。换句话说,与 相同,并且与INDEX MATCH
XLOOKUP
VLOOKUP("Value", A:C, 3, FALSE)
INDEX(C:C, MATCH("Value", A:A, 0))
VLOOKUP("Value", A:C, 3, TRUE)
INDEX(C:C, MATCH("Value", A:A, 1))
然后,要创建一个VLOOKUP
检查多个列的等效项,我们需要创建一个MATCH
函数来执行相同的操作。幸运的是,这可能是使用数组计算。
本质上,我们MATCH
用一个公式替换(要查找的值列表)的第二个参数,该公式根据我们的条件创建一个数组,然后我们在列表中搜索条件为真的地方。例如,如果我们的条件是Column B = "Stock A"
和Column C = "BUY"
,那么我们的数组计算是(B:B="Stock A")*(C:C="Buy")
,我们MATCH
看起来像这样:
MATCH(1, (B:B="Stock A")*(C:C="BUY"), 0)
(在布尔或按位级别,与: , ,*
相同)AND
TRUE*TRUE=TRUE
FALSE*FALSE=FALSE
TRUE*FALSE=FALSE
通常最好限制行数,而不是使用整列(速度很慢)。您可以手动执行此操作:
=INDEX($E$1:$E$5, MATCH(1, ($B$1:$B$5="Stock A")*($C$1:$C$5="BUY"), 0))
或者您可以使用INDEX
and动态执行此操作COUNTA
:
=INDEX($E$1:INDEX($E:$E, COUNTA($A:$A)), MATCH(1, ($B$1:INDEX($B:$B, COUNTA($A:$A)), ="Stock A")*($C$1:INDEX($C:$C, COUNTA($A:$A)), ="BUY"), 0))
最后说明:该MATCH
函数将从列表顶部开始,一直到第一个匹配项。如果这些是按日期顺序排列的,并且您想查找最后一个匹配项,那么 Office356 用户可以使用该XMATCH
功能,该功能有一个额外的参数用于决定搜索是先到后 ( XMATCH(.., .., .., 1)
)、后到先 ( XMATCH(.., .., .., -1)
)、升序二分查找 ( XMATCH(.., .., .., 2)
) 或降序二分查找 ( XMATCH(.., .., .., -2)
)
否则,您可能必须使用SUMPRODUCT
获取MAX
符合您条件的日期,并将其包含在您的MATCH
条件中 ( SUMPRODUCT(MAX(A:A*(B:B="Stock A")*(C:C="BUY"))
)
推荐阅读
- python - 卡在 Python 中的嵌套 for 循环上
- awk - 使用 AWK 获取整个记录信息
- python - PyFlink 有 Kinesis 连接器吗?
- python - ValueError: s 必须是标量,或者与 seaborn 可视化中的 x 和 y 大小相同
- javascript - Gulp-filter 的工作很奇怪而且错误
- sql-server - 带有 Oracle Connection Attunity 的 SSIS 包:无法打开 ORACLE 注册表项
- sql - RDBMS(SQL)存储带有变量标签/额外列属性的时间序列?
- c++ - C++中如何为数组分配内存
- ssl - 阿帕奇 2 ssl 配置
- python - 在 Python 3 中使用 Hooks 从内存中动态导入模块