☰

Excel - 如何查找哪个工作表中存在的数字

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
Excel - 如何查找哪个工作表中存在的数字

我的 excel 文件中有 10 个工作表。Sheet2 至 Sheet10 是原始数据，每个工作表 A1 都有该工作表名称，如下所示

工作表2

        colA    colB    colC    colD
row1    Sheet2              
row2            489     154     785
row3            588     256     546
row4            758     654     945

工作表3

        colA    colB    colC    colD
row1    Sheet3              
row2            758     95      245
row3            236     265     956
row4            879     486     785

在 Sheet1 中，我找到了整个 Sheet 中每个单元格的最大值，如下所示。它只给我值，我还想知道这个值来自哪个 Sheet。所以我这样做了=INDEX(Sheet2:Sheet10!A1,MATCH(A1,Sheet2:Sheet10!B2,0))，但返回了#VALUE!。

        colA                    colB                    colC
row1    =MAX(Sheet2:Sheet10!B2) =MAX(Sheet2:Sheet10!C2) ...
row2    =MAX(Sheet2:Sheet10!B3) =MAX(Sheet2:Sheet10!C3)
row3    ...

我期望的是这样的

        colA    colB    colC    colD
row1    758                     Sheet3 (mean A1 max value 758 is from Sheet3)
row2    588     ...             Sheet2 (mean A2 max value 588 is from Sheet2)
row3    879

我该如何改变我的配方？

答案1

看看下面的解决方案是否适用于辅助列。为了简单起见，我只使用了三个工作表，即 Sheet2、Sheet3 和 Sheet4

在此处输入图片描述

以上是 Sheet2、Sheet3 和 Sheet4 中的示例数据

在主工作表 Sheet1 的一个角落处创建一个辅助列，并在其中放入所有工作表的名称（排除该列表中第一个工作表名称）。

请参阅下面的屏幕截图。

在此处输入图片描述

在这个例子中，我创建了辅助列 K。K1:K3 保存了包含原始数据的工作表名称。

B2 中的公式，您已经在使用。

=MAX(Sheet2:Sheet4!B2)

将其拖动到行和列之间以获取相应的最大值。

现在在 E2 中输入以下公式，然后在公式栏中按CTRL+ SHIFT+ENTER创建数组公式。花括号将自动添加到公式中，使其成为数组公式。

=INDEX($K$1:$K$3,MATCH(1,COUNTIF(INDIRECT($K$1:$K$3&"!"&CELL("address",B2)),B2),0))

分别沿行和列单元格向下拖动以获取 MAX 值所在的工作表名称。

请注意，这不考虑重复的 MAX 值。如果存在，则将返回第一个 MATCH 工作表名称。

工作表的位置也很重要。辅助列列表应与原始数据表的顺序（位置）相同，如果您稍后切换实际原始数据表的位置，解决方案将无法正确报告工作表名称。

希望这可以帮助。

相关内容