☰

部分的な値を持つ2つの列を比較し、一致と列番号の情報を取得する

tag-icon • tag-icon microsoft-excel worksheet-function
部分的な値を持つ2つの列を比較し、一致と列番号の情報を取得する

私は VLOOKUP、MATCH、INDEX、さらには Fuzzy アドオンも使用しました。これらのうちの 1 つまたは組み合わせが機能すると思いますが、それを機能させるためのノウハウがないため、常に「#N/A」が表示されます...

問題: 参照がいっぱいの、項目の合計数が異なり、列が 2 つあります。1 つの列の数値は部分的に同じですが、一部変更されています。次のようになります。

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 とセル参照を 2 つの列に分割することをお勧めします。したがって、ADDRESS一致が見つかった場合はセル参照が、そうでない場合はエラーが返される部分についてのみ説明します。

VLOOKUPは、一致した値と同じ行の値を返すため、ここでは役に立ちません。MATCH一方、は一致した値の行を返します。

  • MID(A1,2,SEARCH("-",A1)-2)A1 の 2 番目の文字から始まり、最初の "-" の出現までの部分文字列を返します。これが探している値です ( lookup_value)。
  • MATCH(lookup_value, C$1:C$10, 0)は、 が最初に出現する行を返しますlookup_value。範囲に関数を適用することはできず、Excel は正規表現をサポートしていないため、補助列 C が必要です。最後のパラメーター ( 0) が必要なのは、値が並べ替えられていないためです。返される行の値は指定された範囲に対する相対値であるため、範囲が行 1 から始まらない場合は、そのことを考慮する必要があります (ROW([first cell])-1の結果に追加するなどMATCH)。
  • ADDRESS(matched_row, COLUMN(B1))セル参照を生成します。 の代わりに行の絶対番号を使用することもできますCOLUMN(B1)が、人間が読みやすい形式ではありません。

どの参照を絶対参照にするか、または相対参照にするかは、自分で決める必要があります。

答え3

まだ全体像が把握できていないため、質問に部分的に答えようとしています。

データを処理して比較するためのヘルパー列を 2 つ追加しました。これにより、理解が深まります。

列は、先頭と末尾のC-helperを削除します。 列は、 の先頭の数字 (または最初の数字) を削除します。次に、に対してデータの を 実行し、vlookup() の結果に基づいてデータが見つかったかどうかを確認します。 0- number
I- helperJ col
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 が見つかりました True、B1 $C$4
2 025973223-1 25973223 222222345 22222345 not_found FALSE 利用不可
3 025965463-2 25965463 233444667 33444667 not_found FALSE 利用不可
4 025911122-4 25911122 211198989 11198989 not_found FALSE 利用不可
5 025998764-1 25998764 212989238 12989238 not_found FALSE 利用不可
6 025925925-3 25925925 224397501 24397501 not_found FALSE 利用不可
7 025900000-2 25900000 225973223 25973223 が見つかりました 真、B2 $C$5
8 025999999-5 25999999 223334445 23334445 not_found FALSE 利用不可
9 025965453-6 25965453 211100110 11100110 not_found FALSE 利用不可
10 025943536-2 25943536 225911122 25911122 が見つかりました True、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")

関連情報