Excelで「イタリア,フランス,日本,アメリカ」のように1つのセルにカンマ区切りで入力されたデータを、それぞれ別シートの対応表から数値に変換したいケースがあります。このような処理は通常のVLOOKUPだけでは対応できないため、分割と配列処理を組み合わせる必要があります。本記事ではその考え方と実用的な関数の組み方を解説します。
やりたい処理の全体像
今回の目的は、1つのセル内にある複数の文字列をそれぞれ別々に変換することです。
例えば「イタリア,フランス,日本,アメリカ」を国コードや番号に変換するような処理です。
このような場合は「分割 → 検索 → 結合」という流れで処理します。
VLOOKUP単体ではできない理由
VLOOKUP関数は基本的に1つの値に対して1つの結果を返す関数です。
そのためカンマ区切りの複数値をそのまま処理することはできません。
例えば「イタリア,フランス」をそのまま入力しても1つの文字列として扱われてしまいます。
TEXTSPLIT関数で分割する方法（Excel 365以降）
Excel 365ではTEXTSPLIT関数を使うことで簡単に分割できます。
例：=TEXTSPLIT(A1,”,”) とすることでカンマ区切りを配列に変換できます。
例えば「イタリア,フランス,日本」がそれぞれ別セルとして扱われます。
XLOOKUPまたはVLOOKUPで対応表を参照する
分割した値に対して、別シートの対応表を参照します。
XLOOKUPの方が柔軟ですが、VLOOKUPでも対応可能です。
例えば =XLOOKUP(TEXTSPLIT(A1,”,”),Sheet2!A:A,Sheet2!B:B) のように記述します。
最終結果をカンマ区切りで結合する
検索結果は配列として返るため、TEXTJOIN関数で再度結合します。
これにより「05,73,47,12」のような形式に整形できます。
例えば =TEXTJOIN(“,”,TRUE,結果範囲) のようにまとめます。
旧バージョンExcelの場合の代替方法
TEXTSPLITが使えない場合は、MIDやFILTERXMLなどの関数を組み合わせる必要があります。
またはPower Queryを使うことで視覚的に分割・変換する方法もあります。
例えばPower Queryでは「区切り記号で分割」機能を使うのが一般的です。
まとめ
カンマ区切りの複数データをVLOOKUPで一括変換するには、分割と検索と結合の組み合わせが必要です。
Excel 365ならTEXTSPLITとXLOOKUPを使うことでシンプルに実現できます。
旧バージョンではPower Queryや複数関数の組み合わせが現実的な解決方法になります。
コメント