☰

在 Excel 中我需要根据 Sheet1 上的数据查找 Sheet3 上的数据

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中我需要根据 Sheet1 上的数据查找 Sheet3 上的数据

我有一个包含多张工作表的 Excel 工作簿。列'SheetA'!W:W列出了可以在行的列标题中找到的文本'Sheet3'!1:1。例如，SheetA'!W42包含“B16”。单元格Sheet3'!CB1的标题文本为“B16：Sample 40”，因此它将是目标列。

示例：样本'SheetA'!W42

在此处输入图片描述

在 中'SheetA'!CD42有一个公式，用于计算需要位于 中的样本编号'Sheet3'!A:A。在下面的示例中，CD42显示计算值“30”，可在 中找到'Sheet3'!A32。

示例：Sheet3 中的样本数据显示了标题以及列标题（B16）和包含感兴趣的样本编号的行的交叉点：

在此处输入图片描述

目标是检索 Sheet3 上所搜索的列和行相交的单元格的内容，并将其显示在 SheetA 上的单元格中。在提供的示例中，在'Sheet3'!CB:CB相交的第 32 行和 SheetA 上将显示值“0.1950581843”。

有谁知道如何实现这一点？

答案1

根据以上数据，我将做出一些假设。如果所有这些都是真的，那么就HLOOKUP可以轻松解决您的问题。否则，可能需要更复杂的函数链。

假设：

  1. 所有相关标题文本'Sheet3'!1:1均为绝对独一无二在该行内。
  2. 所有相关标题文本的'Sheet3'!1:1格式均为“[X]: [Y]”，其中：
    • [X] 是一个可以在 中找到的值'SheetA'!W:W。
    • 'SheetA'!X:X[Y] 是与 [X] 位于同一行的值。
  3. 所有可能的值都'SheetA'!CD:CD可以在 中进行搜索'Sheet3'!A:A，以便它们与要检索的数据位于同一行。
    • 我之所以把这个放进去，是因为我注意到在 Sheet3 中这些值似乎有一些重复，并且想确保不存在任何可能在您搜索应该'SheetA'!CD:CD与另一列匹配的值的情况下（因此如果匹配可能会产生不准确的结果'Sheet3'!A:A）。
    • 如果上述重复仅仅是为了便于阅读，我是否可以建议使用“冻结窗格”功能？
  4. 中的所有相关值'Sheet3!A:A'都是完全连续的，从中的“1”开始'Sheet3'!A3，在该列中永不重复，并且总是按升序排列。

基于上述假设，我使用样本数据构建了自己的表格，该表格大致代表了您的场景。

这是我的“Sheet3”的部分截图。

在此处输入图片描述

这是我的“SheetA”的局部照片。

在此处输入图片描述

类似物列表：

  • 工作表3
    • 我的 A:A = 您的 A:A、BY:BY:、CA:CA:、...
    • 我的 B：B，C：C，D：D，... = 你的 B：B，BZ：BZ，CB：CB，...
  • 表A
    • 我的 A:A = 你的 W:W
    • 我的 B:B = 你的 X:X
    • 我的 C:C = 你的 CD:CD
    • 我的 D:D = 您想要将找到的数据放入的任何列。

正如您在第二张屏幕截图中看到的，D2 的公式是：

=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)

公式的逐步演练：

查找表可让您水平查看单元格区域的值，然后根据相对行位置返回同一列中单元格的值。它需要四个参数，其中三个是必需的：查找值，表格数组，行索引号，[范围查找]。此公式将执行在 Sheet3 中查找所需数据并将其拉入 SheetA 中的单元格的实际工作。

  • 查找值HLOOKUP是您要首先查找的值。此值必须出现在第一排表格数组，因为这是将被搜索的唯一行。HLOOKUP此外，它只会返回第一个匹配项，因此这些值也应该是唯一的。在这里，我们用它CONCATENATE来构建搜索字符串。
    • 连接可以将各种字符串和值组合成一个字符串。它接受一系列参数，这些参数按照它们在结果字符串中的放置顺序进行排序。
      • A2是我们的第一个参数CONCATENATE。字符串的第一部分将是 A2 中的“样本名称”。
      • “：”是 的第二个参数CONCATENATE。这会将冒号和空格放入字符串中，以匹配 中的值格式'Sheet3'!1:1。
      • B2是 的最后一个参数CONCATENATE。它从 B2 中提取“样本 ID”，以完成 中标题使用的语法'Sheet3'!1:1。
  • 表格数组是对要处理的单元格范围的引用HLOOKUP。请记住，第一行必须是包含查找值。此范围还必须包含所有可能的值行索引号。
    • 工作表3！A：Y是对 Sheet3 中 A 到 Y 列（我的 Sheet3 中唯一填充的列）所有单元格的引用。这可确保稍后在新行中添加的任何数据也将在搜索范围内。如果要将数据添加到新列而不是新行中，我将希望使用引用Sheet3!1:32（目前，32 是 Sheet3 中填充的最后一行）。如果数据可能通过新列添加和Sheet3!1:1048576新行，我将使用或引用整个工作表Sheet3!A:XFD。 （注意：“整个工作表”引用适用于 Excel 2013。早期版本可能具有较小的行/列限制 -根据需要 调整1048576或。）XFD
  • 行索引号是一个正整数，表示相对于最上面一行的行位置表格数组。这表示HLOOKUP您希望从匹配列返回哪个单元格。请注意，因为HLOOKUP只搜索查找值在最上面一行表格数组， 和行索引号不能为负数，不能使用HLOOKUP（至少不能单独使用）来返回高于单元格的信息查找值。
    • C2+2- 由于 中的所有值'Sheet3'!A:A都是完全连续的，没有跳过的整数，并且始终按升序排列，因此我们可以使用这些值（也表示在 中'SheetA'!C:C）作为我们要查找的数据的行号的指示符。 是+2为了说明 Sheet3 的第 3 行上的编号从“1”开始。
  • [范围查找]是 的可选参数HLOOKUP。选项为 TRUE 或 FALSE，表示您是否允许近似匹配有效，或者是否只允许精确匹配。如果省略此参数，Excel 默认为 TRUE（近似匹配），这通常会导致不良行为 - 尤其是如果您的工作表未按特定方式排序。因此，我们指定错误的确保HLOOKUP只会选取完全匹配的结果。

根据您工作表的布局调整上述内容，我相信这是您需要的单元格公式'SheetA'!CE42（假设这是您想要将数据放入的位置）：

=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)

请注意，如果 Sheet3 中的数据比 CB 列更靠右，和/或数据可能会添加到其他列，则需要进行调整表格数组因此。

相关内容