☰

文字列内の複数のコード化された値を検索し、見つかったコードごとに検索値を返す

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
文字列内の複数のコード化された値を検索し、見つかったコードごとに検索値を返す

私には 600 個のアイテム コードがあり、それぞれが全体のアイテム コード内の属性でコード化されています。たとえば、プレーン アイテム 600 の他に、600BK (黒のボディ)、600BKR (黒のボディ、赤色のディテール)、600BKR-YEL (黒のボディ、赤色のディテール、黄色のベース) などがあります。

それで、アイテムコードのリストができました:

600
600BK
600BKR
600BKR-YEL

次に、別のワークシートに、各コードとその意味の参照リストを作成します。

BK    Black Body
R     Red Detail
YEL   Yellow Base

ルックアップ シートにあるアイテム タイトルのすべてのコードを検索し、見つかったコードごとに対応するすべての値を、できれば 1 つのセルに返す関数を作成したいと思います。

600
600BK         Black Body
600BKR        Black Body Red Detail
600BKR-YEL    Black Body Red Detail Yellow Base

これは可能ですか？

答え1

私なら、次のようなことをします。アイテム コードのリストが Sheet1 の列 A、行 2 ～ 20 にあり、ルックアップ リストが Sheet2 の列 A と B、行 2 ～ 10 にあると仮定します。

  =IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,8,3),Sheet2!$A$2:$B$4,2,0),"")

セル B2 に入力し、Sheet1 のすべての行にコピーします。

確かにルックアップは 1 つの数式にまとめられますが、コードが多数ある場合、この方法は非常に扱いにくくなります。Sheet1 に各コード位置のヘルパー行 (例: mid(A2,4,2)) を挿入し、それらを 1 つの文字列に連結することをお勧めします。

答え2

簡単に答えると「はい」ですが、その方法の 1 つがかなり長くなります。

質問では除外されていない可能性が多数あるため (プレーン アイテムが 3 桁以上の数字または英数字でコード化されている、プレーン アイテムごとに 3 つ以上のコードがある、プレーン アイテムによってコードの重要性が異なるなど)、まずはコードの解析から始めるのが最も安全だと思います。これにより、GR がグレーのボディに赤いディテール (多くの場合、互換性のある配色です!) またはグリーンのボディであるといった複雑さを回避できます。

これらが解析されていると仮定すると (行 2 から始まる 3 つの列 B:D)、その後は、ルックアップ テーブル (別のシートにあるもの) の名前を使用すると簡単ですcodes。

=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)

アイテム コード シートで。値の間にスペースを入れるために、コード配列の右側の列のすべてのエントリがスペースで終わるものと想定しました (=A2&" " などをコピーして配置するのは簡単です)。

したがって、難しいのはアイテム コードからコードを解析することかもしれません。そのためには、以下のように列を追加することをお勧めします (コードが 3 つ以上ある場合はさらに追加します)。

SU531526 最初の例

式は以下のようになります。

SU531526 2番目の例

列 B は、コードの検索を開始する場所を定義します (プレーン コードが 3 つの数字以外の場合)。列 C:D は、次のコードの検索を開始する場所/次のコードの長さです。エレガントではありませんが、比較的汎用性が高いことに同意します。右側に進む前に、左側の解析が正しいことを確認してください。

解析が成功したら、コピー/貼り付け/値（数式を削除するため）を行ってから置き換えることをお勧めします。空白ピリオドで区切ってください (検索式がエラーになるのを防ぎ、複雑にならないようにするため)*。また、アイテム コードのリストが列 A にあると仮定すると、上記のように検索式を適用する前に列 B:F を削除し (または参照を調整し)、必要に応じてさらに検索を追加します。

*注意: 参照テーブルの各列のセルにピリオドが追加されていることを確認してください。

c5、c6、c7 の列を追加して、(a) 最大長 7 と (b) 「最悪の場合」のシナリオ (つまり、すべて 1 文字) に対応できるようにします。

答え3

これは、検索機能 (大文字と小文字を区別しない find のバージョン) を使用するバージョンです。設定は次のとおりです。

シート 1 では、検索するコードはシート 1 の列 A から始まります。最終結果は列 B になります。列 C、D などでは、行 1 に一意のコードが水平に配置されています (つまり、C1 には「BK」、「D2 には「R」など)。これを実現する実用的な方法は、ルックアップ テーブルにコード リストをコピーし、セル C1、D1 などに水平に転置して貼り付けることです。

まずセル B2 に次の数式を入力します。

  =IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")

この数式を、列 C から行 2 で作成したコード列の数までコピーします。

最後に、セルC2で、行2のすべての結果を連結します。つまり、数式

 =D2&" "&E2&" "&F2

行 1 にコードがあるすべての列に対して、などを実行します。この手順は面倒ですが、次の VBA 関数を使用すると短縮でき、範囲内のすべてのセルを連結できます。

  Function Concat(useThis As Range, Optional delim As String) As String
     ' this function will concatenate a range of cells and return the result as a single string
     ' useful when you have a large range of cells that you need to concatenate
     ' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

  Dim retVal As String, dlm As String, cell As Range
  retVal = ""
  If delim = Null Then
     dlm = ""
  Else
     dlm = delim
  End If
  For Each cell In useThis
      If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
          retVal = retVal & CStr(cell.Value) & dlm
      End If
  Next
  If dlm <> "" Then
     retVal = Left(retVal, Len(retVal) - Len(dlm))
  End If
  Concat = retVal
  End Function

この関数を Developer VBA のモジュールに挿入してコピーします。使い方は簡単です。たとえば、concat(C1:D1," ") などです。

このアプローチは、すべての 2 文字コード、および 2 文字以上のコードに含まれないすべての 1 文字コード、つまり「R」や「BR」などのコード ペアがない場合に機能することに注意してください。

関連情報