☰

是否可以引用 Excel 中的兩個儲存格來尋找第三個儲存格？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
是否可以引用 Excel 中的兩個儲存格來尋找第三個儲存格？

我有一個電子表格，其中包含大量數據。我需要將其中一些資料返回到某些單元格。我需要返回的資料始終位於其中包含“附加元件”的單元格附近。問題是，有多個“附加組件”單元。例如，我有兩個部分，“第 1 部分”和“第 2 部分”，這兩個部分中的每一個都有一個彼此相對較近的“附加元件”部分。它們所在的儲存格也不會保持不變，否則我只會引用這些儲存格。這是我目前使用的公式，用於傳回一個零件的「附加組件」附近的資料：

=IFNA(INDEX(L15:R46,MATCH("Attached Components",M15:M46,0)+2,3),"0")

總而言之，我需要一個公式，該公式從引用“附加元件”的單元格返回數據，然後引用“部分#_”。

以下是「附加元件」位置如何更改以及它相對於「第 1 部分」的位置的範例。

在此輸入影像描述

這是一個非常具體的問題，我知道我的解釋並不是最清楚的。我感謝您的幫助，並隨時詢問更多具體細節！

答案1

我試圖讓它在以下假設下工作：

  • “附加元件”始終位於同一列中。
  • 您實際上是在嘗試尋找每個“材料#”的描述。

我將使用這張表來處理：

例子

這可能不完全是您所需要的，但我可以嘗試根據您的評論來改進我的答案。

透過重複使用您的公式來確定列中「附加元件」的位置，然後新增 2，它會給出材料描述開始的相對行：

=MATCH("Attached Components",B1:B32,0)+2

結果在範例中為“7”。

在您需要確定描述所在的最後一行之後。為了在正確的範圍內搜索，公式需要根據「附加元件」所在的行進行更改。 MATCH、ADDRESS、CONCATENATE 的組合將重新建立範圍。

MATCH 為您提供相對行，ADDRESS 將行號和列號轉換為帶有單元格名稱的字串（ ADDRESS(1,1)="$A$1" ），CONCATENATE 會將字串放在一起以建立範圍。

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

這將會傳回一個類似「$C$7:$C$25」的字串。因此，它覆蓋了「描述」列，並從您的值所在的行開始，向下 18 行。要覆蓋更多或更少的行，只需將公式中的“+20”更改為適當的值即可。

尋找最後一行只需使用 IF 和 MIN 尋找第一個空白儲存格即可。

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

這個公式是一個陣列公式。這就是為什麼它周圍有括號（不要鍵入括號，它們會在您輸入公式然後按 Ctrl+Shift+Enter 時出現）

間接轉換我們建構到單元格引用中的字串。 ROW 給出結果的行號。 MIN 將取回範圍內的最小值。末尾的“-1”表示最後一個描述的行號，而不是第一個空白行。

在範例中，此公式傳回“9”。

現在我們有了第一個描述和最後一個描述的行號，7 到 9。但這次您有一個特定的儲存格引用可供使用。

例如材質#找：

VLOOKUP範例

在最後一個例子中，單元格包含

E2：

=MATCH("Attached Components",B1:B32,0)+2

F2（使用 Ctrl+Shift+Enter 輸入）：

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

F7：

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

這樣，當您在儲存格 E7 中鍵入材料編號時，它會在儲存格 F7 中顯示說明。

編輯:

根據評論，解決方案可以這樣制定：

使用一個更複雜的例子：

複雜的例子

行匹配只是2個MATCH函數的級聯。使用第一個 MATCH 函數尋找零件編號，然後使用第二個 MATCH 函數尋找感興趣的部分：

2 匹配

F3：您要尋找的部分的字串

F4：在第一列中找出「零件號碼」的公式。

=MATCH($F$3,A1:A32,0)

F6：您要尋找的部分的名稱

F7：在先前確定的部分中找出部分的公式。匹配是在從「Part #」行（儲存在儲存格 F4 中）開始的範圍內完成的。此範圍是使用與 INDIRECT、CONCATENATE、ADDRESS 相同的公式建構的。然後 MATCH 傳回的相對行會透過 F4-1 偏移以獲得絕對行號。

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

現在，為了識別描述的第一行和最後一行，我們可以重複使用與先前相同的公式：

第一行和最後一行用於描述

F9：「附加元件」行的行號加2，得到第一個描述行。

=F7+2

F10：尋找描述範圍中的第一個空白行（從 F9 儲存的行開始）。這是一個陣列公式，需要使用以下命令輸入CTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

然後，為了顯示描述，我們可以使用 INDIRECT 和索引列：

顯示矩陣

F15：

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

G15：

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

這些公式將顯示材料編號以及由 E 列中的索引標識的行的描述。 IF 語句是為了確保我們不會顯示最後一行下面的行。在範例中，它僅顯示 5 行，但您可以透過向下拖曳第一行並新增索引來複製此公式以取得更多行。

相關內容