☰

在Excel中，我需要從sheet1上的數據找到sheet3上的數據

tag-icon • tag-icon microsoft-excel worksheet-function
在Excel中，我需要從sheet1上的數據找到sheet3上的數據

我有一個包含幾張工作表的 Excel 工作簿。該列'SheetA'!W:W列出了可在 row 的列標題中找到的文字'Sheet3'!1:1。例如，SheetA'!W42包含“B16”。單元格Sheet3'!CB1的標題文字為“B16：樣本 40”，因此它將是目標列。

例：樣本'SheetA'!W42

在此輸入影像描述

其中'SheetA'!CD42有一個公式可以計算需要定位的樣本數'Sheet3'!A:A。在下面的範例中，CD42顯示計算值“30”，可以在 中找到該值'Sheet3'!A32。

範例：Sheet3 中的範例資料顯示標題以及列標題 (B16) 與包含感興趣的範例編號的行的交集：

在此輸入影像描述

目標是檢索 Sheet3 上搜尋到的列和行相交的儲存格的內容，並將其顯示在 SheetA 的儲存格中。在提供的範例中，在'Sheet3'!CB:CB相交的第 32 行和工作表 A 上，將顯示值「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是您要使用的儲存格範圍的參考。請記住，第一行必須包含查找值。該範圍還必須包含所有可能的值行索引號。
    • Sheet3!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 列更靠右，和/或資料可能會新增至更多列，則您需要調整表格數組因此。

相關內容