excel - 查找最后一个匹配条件的数组公式
问题描述
我正在尝试用数组公式做一些有点可怕的事情。我快到了,但无法完成最后一块。
假设我有这样的数据行......
Property 1 Property 2 Property 3
Orange Square Hot
Blue Circle Cold
Purple Star Slimy
我有一个标准表,由要检查的列和要匹配的值组成:
Property Index Value
2 Circle
1 Purple
3 Slimy
对于每一行数据,我试图确定该行数据满足的最后一个标准。
IE。对于上面的三行数据,我希望得到以下输出:
0 <- a hot orange square isn't slimy or purple or a circle
1 <- a cold blue circle matches (only) the circle criterion
3 <- a slimy purple star is both purple and slimy,
and we count the last match
到目前为止,我已经在一个单元格中尝试了这个数组公式:
{=MAX(
IF(
INDEX(Data[@[Property 1]:[Property 3]], Criteria[Property Index])
= Criteria[Value],
ROW(Criteria[Value]),
0
)
)}
问题在于,当我逐步评估公式时,Criteria[Property Index]
只评估第一个条目的值 2,而不是评估整个列{2, 1, 3}
,所以我只是重复测试了第一个标准。
我希望使用这个索引范围来查找每行的置换列表{Square, Orange, Hot}
,{Circle, Blue, Cold}
和{Star, Purple, Slimy}
,以与值范围进行比较。
当公式跨越多个单元格时,我已经能够让 INDEX 使用一系列索引值并返回每个索引结果的数组,但我试图避免将我的工作表绑定到 Criteria 表的固定长度. 我希望能够向其中添加更多行并让公式仍然有效。
在这种情况下,有什么方法可以哄 Excel 将 index 参数视为一个范围,以获得我想要的数组输出?或者是否有另一种方法可以执行此计算,该计算仍会随着用户添加/删除标准行而扩展?
解决方案
这是可行的,但是使用命名范围来封装数据表的严格属性列是一个好主意
=MAX(IF(
INDEX(
INDEX(PropertyData, 1 + ROW()-ROW($I$15), 0),
1,
N(IF({1}, Criteria[Property Index]))
) = Criteria[Value],
ROW(Criteria[Value])-ROW(Criteria[#Headers]),
0
))
PropertyData 是 Property 1 列中通过Named Range到末尾的第一个单元格。
PropertyData 定义为:
=INDEX(Data, 1, COLUMN(Data[Property 1])-COLUMN(Data) + 1):INDEX(Data, ROWS(Data), COLUMNS(Data))
一个硬编码值是第一个公式所在的 $I$15 单元格。我将表格和公式不同步以显示它处理得很好。
推荐阅读
- swift - 代码连接到情节提要时应用程序崩溃
- c# - .Equal() 测试的流利验证无法按预期工作
- apache-spark - Spark 如何执行 I/O?
- dart - Flutter - 打开应用程序时提示输入 TouchID/FaceID
- c# - Asp.Net MVC - SecurityException:找不到源,但无法搜索部分或全部事件日志。无法访问的日志:安全性
- c# - 如何根据给定的值自动缩放 Unity 对象。?
- windows - Windows 用户空间 TCP/IP 堆栈 - 如何抑制指定以太网接口的内核堆栈?
- django - 在 Django 项目中设置 Reactjs
- java - 如何使 TestRestTemplate 忽略重定向
- javascript - 如何使用时刻 js 获得 24 小时时间