时间戳

首页 > 解决方案 > Excel - 使用 INDEX / MATCH 根据多个匹配项从表中检索数据

excel - Excel - 使用 INDEX / MATCH 根据多个匹配项从表中检索数据

问题描述

我有一个列表名称（在侧面）和日期（在顶部），并且每个名称还有一个额外的区域/区域列，例如：

--Name--------Region-------13/12/19--------14/12/19--------15/12/19--------16/12/19-----------17/12/19
John Smith    North           IN             IN              OFF              IN                OFF
Jane Doe      North           OFF            IN              IN               IN                OFF
Bob Newhart   South           IN             IN              OFF              OFF               OFF

我还有一份每个人完成的工作清单，例如：

--Name--------Region-----Job#---CompletedDate-----JobType
John Smith                22        14/12/19         xx
John Smith                23        14/12/19         yy
John Smith                24        16/12/19         zz
Bob Newhart               25        14/12/19         aa

我知道如何从名称中查找区域=INDEX(table[Region],MATCH(A2,table[Name],0),0) ，我什至研究了如何根据 2-way INDEX/MATCH（例如=INDEX(Rota!B:B,MATCH(A2,Rota!A:A,0),MATCH(D2,Rota!1:1,0))

我的问题是当一个人改变区域时，例如约翰史密斯从北移动到西：

--Name--------Region-------13/12/19--------14/12/19--------15/12/19--------16/12/19-----------17/12/19
John Smith    North           IN             IN              OFF                                
John Smith    West                                                            IN                OFF
Jane Doe      North           OFF            IN              IN               IN                OFF
Bob Newhart   South           IN             IN              OFF              OFF               OFF

我需要查看他们在工作完成当天所在的地区，这样我就可以分别总结每个地区完成的工作和每人完成的工作数量。

我猜它类似于上面的第二个索引/匹配，但具有某种形式的“如果单元格为空白，则向下移动到同一列中其名称的下一个匹配项”。这甚至有意义吗？

任何帮助，将不胜感激。

谢谢，艾伦

标签： excelexcel-formula

解决方案

创建一个结合人员+区域的辅助列怎么样？例如，您将有一个（隐藏）列，其中包含诸如John Smith~north（您需要一个能够正确处理 Jane South 的分隔符）之类的值。

此外，我认为您创建的第一个索引函数可以用 VLOOKUP 代替：

=VLOOKUP(A2,rota!$A:$E, 2, FALSE) 
// look for value A2 
   in the first column of A:E on the Rota sheet, then 
   copy over the value from the 2nd
   Don't approximate matches

如果工作表之间的日期完全一致（即，如果每列的日期相同），则可以使用简单的 COLUMN() 引用重新创建第二个。

编辑

仔细看了一下——你有点把 Excel 推到了设计的极限。您可以尝试解决这个问题，例如通过将第二个 INDEX 函数包装在一个 IF 中，该函数检查索引的结果是否不为空，如果是，则将匹配项向下移动一个。但这不会解决 John 在三个区域之间移动的示例。

从概念上讲，您正在处理三个表： 1. 具有输入或输出值的名称/日期 2. 具有区域值的名称/日期 3. 具有名称值的工作/日期

因为您的 ROTA 表正在合并 1 和 2，所以您会为一个人获得多行，这会给您在第三张表中带来麻烦。也许在您的设计中，您可以将它们拆分，这样可以更轻松地使用您使用的功能来获取它们。这开始看起来很像一个数据库......

但也许您在 ROTA 表的字段中添加区域？

--Name----------13/12/19--------14/12/19--------15/12/19--------16/12/19-----------17/12/19
John Smith      IN~N            IN~N            OFF~N           IN~S               OFF~S

这意味着对于该区域，如果您在所有情况下都使用一个附加字符，则可以使用您已经使用过的功能：

=LEFT(INDEX(table[Region],MATCH(A2,table[Name],0),0), LEN(INDEX(table[Region], MATCH(A2,table[Name],0),0)) -2)
=RIGHT(INDEX(table[Region], MATCH(A2,table[Name],0),0), 1)

或者，如果您想使用分隔符（示例中为 ~）

=LEFT(INDEX(table[Region],MATCH(A2,table[Name],0),0), FIND("~", INDEX(table[Region],MATCH(A2,table[Name],0),0) -1)
=RIGHT(INDEX(table[Region],MATCH(A2,table[Name],0),0), FIND("~", INDEX(table[Region],MATCH(A2,table[Name],0),0) -1)

推荐阅读