☰

將兩列與部分值進行比較並獲取有關匹配和列號的信息

tag-icon • tag-icon microsoft-excel worksheet-function
將兩列與部分值進行比較並獲取有關匹配和列號的信息

我使用了 VLOOKUP、MATCH、INDEX 甚至 Fuzzy 插件。我確信其中之一或組合可能會起作用，我只是不知道如何讓它起作用並一直得到“#N/A”...

問題：我有兩列，項目總數不同，充滿參考文獻。一個與​​另一個具有部分相同的數字，但有一些變化，如下所示：

Column A typical number (ex): 025983553-1 
Column B typical number (ex): 225983553

但我有數千個數字，我想匹配從A 到B 的整個範圍的每個單元格，如果有匹配，即使在單元格B6544 中，我也想知道並獲得一些內容（在C 列中），例如“True B6544”。

我正在處理的一個（小）例子（不是確切的參考資料，這些是內部的敏感數據）：

       B                J       H
  1 025983553-1     225983553   True, B1
  2 025973223-1     222222345   False
  3 025965463-2     233444667   False
  4 025911122-4     211198989   False
  5 025998764-1     212989238   False
  6 025925925-3     224397501   False
  7 025900000-2     225973223   True, B2
  8 025999999-5     223334445   False
  9 025965453-6     211100110   False
 10 025943536-2     225911122   True, B4
 ...

如您所見，J1 與 B1 部分匹配，因此 H1 返回“True, B1”。

如上所述，我嘗試了 MATCH、VLOOKUP、INDEX 和 Fuzzy 附加元件。我知道我必須這樣做：

 =VLOOKUP(B1, $J$1:$J$10, valueThat IwantIThink, FALSE)

但似乎沒有任何作用...任何幫助將不勝感激！

答案1

嘗試這個小的使用者定義函數：

Public Function PartialMatch(r1 As Range, r2 As Range) As Variant
    Dim boo As Boolean, v As Variant, r As Range
    boo = False
    v = Mid(r2.Text, 2)
    For Each r In r1
        If InStr(1, r.Text, v) > 0 Then
            PartialMatch = "True, " & r.Address(0, 0)
            Exit Function
        End If
    Next r
    PartialMatch = boo
End Function

在此輸入影像描述

如圖所示，在K1進入：

=partialmatch($B$1:$B$10,J1)

並向下複製。

這個例程從子字串中去掉前導數字，並嘗試在列中找到它。

答案2

由於Excel不支援正規表示式，所以我認為沒有一個公式不需要輔助列。在我的例子中，

  • A 包含有「-」的數字
  • B 包含相符的數字
  • C 包含 B 的值，但不包含第一位數字
  • 如果 A 的值與 B 列中的任何值都不匹配，則 D 為“FALSE”，或為“TRUE”+ 對符合儲存格的參考。

C1中的公式為：

=RIGHT(B1,LEN(B1)-1)

這會刪除 B1 的第一位數字。

D1中的公式為：

=IFERROR("TRUE, "&ADDRESS(MATCH(MID(A1,2,SEARCH("-",A1)-2),C$1:C$10,0),COLUMN(B1)),FALSE())

請注意，雖然這應該會產生所需的輸出，但我建議將 TRUE/FALSE 和單元格引用分成兩列。因此，我只會解釋這ADDRESS部分，如果找到匹配，它將為您提供單元格引用，否則將提供錯誤。

VLOOKUP這裡沒有用，因為它將傳回與匹配值位於同一行的值。MATCH另一方面，傳回符合值的行。

  • MID(A1,2,SEARCH("-",A1)-2)傳回 A1 的子字串，從第二個字元開始，直到並排除第一次出現的「-」。這就是我們要尋找的值（lookup_value）。
  • MATCH(lookup_value, C$1:C$10, 0)將會傳回 第一次出現的行lookup_value。由於我們無法對範圍應用函數，且 Excel0不支援正規表示式，因此我們需要輔助列 C。請注意，傳回的行值是相對於指定範圍的，因此如果您的範圍不是從第 1 行開始，您將必須考慮到這一點（例如，透過新增ROW([first cell])-1的結果MATCH）。
  • ADDRESS(matched_row, COLUMN(B1))產生單元格引用。如果您願意，您可以使用行的絕對編號COLUMN(B1)，但這不利於人類可讀。

您必須自己決定哪些引用應該是絕對的還是相對的。

答案3

我試著部分回答你的問題，因為我們還沒有完整的情況。

我添加了兩個輔助列來處理數據並比較它們 - 這將使您更好地理解。

該C-helper列剝離了0開頭和結尾的- number。
該I- helper列刪除 的前導數字（或第一位數字）J col。然後我對數據
進行了檢查，並根據 vlookup() 結果找出是否找到數據。 vlookupI-helperC - helper

我的公式是

  • C - helper:==MID(B4,2,LEN(B4)-3)
  • I - helper:=MID(D4,2,LEN(D4)-1)
  • vlookup col: =IF(ISNA(VLOOKUP(E4,$C$4:$C$13,1,FALSE)),"not_found","found")

問題的下一部分是找到該條目的位置。如果找到該條目，則傳回其位址，否則傳回字串Not_Available is returned。範例資料現在看起來像這樣

ABC - 助手 JI - 助手 vlookup col HI X-助手
1 025983553-1 25983553 225983553 25983553 發現正確，B1 $C$4
2 025973223-1 25973223 222222345 22222345 not_found FALSE Not_Available
3 025965463-2 25965463 233444667 33444667 not_found FALSE Not_Available
4 025911122-4 25911122 211198989 11198989 not_found FALSE Not_Available
5 025998764-1 25998764 212989238 12989238 not_found FALSE Not_Available
6 025925925-3 25925925 224397501 24397501 not_found FALSE Not_Available
7 025900000-2 25900000 225973223 25973223 發現正確，B2 $C$5
8 025999999-5 25999999 223334445 23334445 not_found FALSE Not_Available
9 025965453-6 25965453 211100110 11100110 not_found FALSE Not_Available
10 025943536-2 25943536 225911122 25911122 發現正確，B4 $C$7

編輯

我的公式是

  • X - helper:=IF(F4="found",CELL("address",INDEX($B$4:$C$13,MATCH(E4,$C$4:$C$13,0),2)),"Not_Available")

相關內容