|
excelperfect 在某个工作表单元格区域中查找值时，我们通常都会使用VLOOKUP函数。但是，如果在多个工作表中查找值并返回第一个相匹配的值时，可以使用VLOOKUP函数吗？本文将讲解这个技术。 最简单的解决方案是在每个相关的工作表中使用辅助列，即首先将相关的单元格值连接并放置在辅助列中。然而，有时候我们可能不能在工作表中使用辅助列，特别是要求在被查找的表左侧插入列时。因此，本文会提供一种不使用辅助列的解决方案。 下面是3个示例工作表： 图1：工作表Sheet1 图2：工作表Sheet2 图3：工作表Sheet3 示例要求从这3个工作表中从左至右查找，返回Colour列中为“Red”对应的Amount列中的值，如下图4所示。 图4：主工作表Master 数组公式如下： =VLOOKUP($A3,INDIRECT('''&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT('''&Sheets&''!B:B'),$A3)>0,0))&''!B1:D10'),3,0) 其中，Sheets是定义的名称： 名称：Sheets 引用位置：={'Sheet1','Sheet2','Sheet3'} 在公式中使用的VLOOKUP函数与平常并没有什么不同，我们首先需要确定在哪个工作表中进行查找，因此我们使用的函数应该能够操作三维单元格区域，而COUNTIF函数就可以。公式中的： COUNTIF(INDIRECT('''&Sheets&''!B:B'),$A3) 转换为： COUNTIF(INDIRECT('''&{'Sheet1','Sheet2','Sheet3'}&''!B:B'),$A3) 转换为： COUNTIF(INDIRECT({''Sheet1'!B:B',''Sheet2'!B:B',''Sheet3'!B:B'}),$A3) INDIRECT函数指令Excel将这个文本字符串数组中的元素转换为单元格引用，然后传递给COUNTIF函数，同时单元格A3中的值作为其条件参数，这样上述公式转换成： {0,1,3} 分别代表工作表Sheet1、Sheet2、Sheet3的列B中“Red”的数量。 因为我们想得到第一个匹配的结果，所以将该数组传递给MATCH函数： MATCH(TRUE,COUNTIF(INDIRECT('''&Sheets&''!B:B'),$A3)>0,0) 转换为： MATCH(TRUE,{0,1,3}>0,0) 转换为： MATCH(TRUE,{FALSE,TRUE,TRUE},0) 结果为： 2 因此，将在工作表列表中的第2个工作表即Sheet2中执行VLOOKUP操作。 现在，将上面的结果作为参数值传递给INDEX函数： INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT('''&Sheets&''!B:B'),$A3)>0,0)) 转换为： INDEX(Sheets,2) 转换为： INDEX({'Sheet1','Sheet2','Sheet3'},2) 结果为： Sheet2 这里，需要使用INDIRECT函数进一步构造来生成传递给VLOOKUP函数的单元格区域，因此： =VLOOKUP($A3,INDIRECT('''&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT('''&Sheets&''!B:B'),$A3)>0,0))&''!B1:D10'),3,0) 转换为： =VLOOKUP($A3,INDIRECT(''Sheet2'&''!B1:D10'),3,0) 转换为： =VLOOKUP($A3,INDIRECT(''Sheet2'!B1:D10'),3,0) 转换为： =VLOOKUP($A3,'Sheet2'!B1:D10,3,0) 得到结果： 55 注：本技巧整理自excelxor.com，有兴趣的朋友对照原文研读，收获更丰。
|
|
|
来自： hercules028 > 《excel》
