有两个工作薄:甲.xls 和乙.xls ,其中甲.xls中的某个工作表中有A列有若干数据,这些数据分布在乙.xls中的多个工作表中,假设分布在乙.xls中的工作表“工作表1”,“工作表2”,“工作表3”,“工作表4”,“工作表5”中,但具体分布在哪个工作表中是没有规律可寻的,并且甲.xls中的A列每一个数据,只在乙.xls中的这五个工作表中仅有某一个位置出现,也就是说好比甲中的a1单元格数据,在乙工作薄中搜索,只会有一个位置显示有。
以上是给定的条件,现在的目的是,要在甲.xls中的B列,标出A列数据在乙.xls中的工作表名称。
解决的方法是:在甲.xls中的B1单元格输入以下公式:
- =LOOKUP(1,0/COUNTIF(INDIRECT({"'[乙.xls]工作表1","'[乙.xls]工作表2]","'[乙.xls]工作表3","'[乙.xls]工作表4","'[乙.xls]工作表5"}&"’!a:iv"),A1),{"工作表1","工作表2","工作表3","工作表4","工作表5"})
复制代码
然后,你就会看到B1单元格就显示出来的是A1里面的内容在乙.xls工作薄所在的工作表的名称了。
- INDIRECT({"'[乙.xls]工作表1","'[乙.xls]工作表2]","'[乙.xls]工作表3","'[乙.xls]工作表4","'[乙.xls]工作表5"}&"’!a:iv")
复制代码 这个公式,是构建了一个数组,而这个数据就是countif要查找搜索的范围,一共是五个工作表,也就是五个搜索范围.。
- COUNTIF(INDIRECT({"'[乙.xls]工作表1","'[乙.xls]工作表2]","'[乙.xls]工作表3","'[乙.xls]工作表4","'[乙.xls]工作表5"}&"’!a:iv"),A1)
复制代码
countif在这五个搜索范围之中分别查找A1在工作表1中有几个,工作表2中有几个,……工作表五中有几个,当然,得到的结果肯定要么是1,要么是0,并且,仅且只有一个1,因为上面的条件说了,A列中的数据,每个只在乙中出现一次。我们假设A1在乙.xls中的工作表5中,那么countif得到的结果就是{0,0,0,0,1}。
而接着用零除于countif的结果,我个人的理解就是,同样得到一个向量,{错误,错误,错误,错误,0}
而lookup(1,{错误,错误,错误,错误,0},{"工作表1","工作表2","工作表3","工作表4","工作表5"})的意思就是,在向量{错误,错误,错误,错误,0}中查找等于1或是小于1的最大值0在向量{错误,错误,错误,错误,0}中的位置顺序,然后返回后面一个向量{"工作表1","工作表2","工作表3","工作表4","工作表5"}中相同位置的数据,也就是第五个数据“工作表5”。
|