実務では、店舗コードや商品コードを使って、XLOOKUPで検索することがよくあります。ところが、データの出どころによって型が違います。ほかの人が基幹システムから出すデータは、店舗コードや商品コードが文字列です。私の部署のデータベースから出すデータは、同じコードが数値です。見た目は同じ「1001」でも、片方が文字列、片方が数値だと、XLOOKUPでは見つかりません。
日付も、「20261028」のような8桁の値で出てくることがあります。このままでは、日付として計算や絞り込みができません。
この記事では、ローデータには手を加えずに、式で型をそろえる方法を紹介します。
なお、この記事の表や手順は説明のために作ったもので、実際に使っているExcelとは作りが違います。コードや数字も架空です。
ローデータは触らず、横の列で型を変える
資料を作るときは、ローデータをなるべく触らないようにしています。手で直すと、間違いのもとになるからです。
そこで、次のような形にしています。
- C列から右に、ローデータをそのまま貼り付ける
- A列・B列には式を入れておき、日付やコードの型を変える
式の列は固定しておくので、新しいデータを貼り付けるだけで、毎回同じように変換されます。
式の列だと分かるように、A列とB列の見出しに色を付けておくのも有効です。どこから貼り付ければよいかも、ひと目で分かります。
表をテーブルにしておくと、データを貼り付けて行が増えたときも、A列とB列の式が自動で入ります。式を下までコピーし忘れる心配がありません。テーブルの便利な点は、こちらの記事で紹介しています。
| A：日付（式） | B：商品コード（式） | C：日付 | D：商品コード | E：数量 |
|---|---|---|---|---|
| 2026/10/1 | 1001 | 20261001 | 1001 | 3 |
| 2026/10/1 | 1002 | 20261001 | 1002 | 5 |
| 2026/10/2 | 1001 | 20261002 | 1001 | 2 |
C列からE列が貼り付けたローデータ、A列とB列が式で変換した値です。
20261028を日付に変える
A2セルに次の式を入れます。
=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))LEFTで先頭の4文字（年）、MIDで5文字目から2文字（月）、RIGHTで最後の2文字（日）を取り出して、DATE関数で日付にしています。C2が数値の20261028でも、文字列の「20261028」でも、同じ式で日付になります。
結果が46323のような数字で表示される場合は、セルの表示形式を「日付」にします。
見た目だけ日付にしたい場合
表示を変えるだけでよければ、TEXT関数でもできます。
=TEXT(C2,"0000!/00!/00")「!」は、次の1文字をそのまま表示するという意味です。「2026/10/28」と表示されますが、中身は文字列なので、日付として計算したり、日付で絞り込んだりはできません。表示用として使います。
XLOOKUPで数値と文字列をそろえる
コードの型を変えるときは、次の2つを使っています。
- 文字列を数値にする：
=D2*1 - 数値を文字列にする：
=D2&""
文字列の「1001」に1を掛けると、数値の1001になります。反対に、数値の1001に空の文字列をつなげると、文字列の「1001」になります。
たとえば、基幹システムから出したデータ（商品コードが文字列）で、私の部署のデータベースから出した商品マスタ（商品コードが数値）を検索する場合は、検索値に1を掛けて数値にそろえます。
=XLOOKUP(D2*1,商品マスタ[商品コード],商品マスタ[商品名])反対に、商品マスタの商品コードが文字列なら、D2&"" にします。検索する側（商品マスタ）の型に、検索値を合わせるのがポイントです。
B列で =D2*1 のように変換しておき、XLOOKUPではB列を使う形でも同じです。
型の見分け方
初期設定では、数値はセルの右寄せ、文字列は左寄せで表示されます。また、文字列の数値には、セルの左上に緑の三角が付くことがあります。見た目が同じなのにXLOOKUPで見つからないときは、両方の型を確認してみてください。
パワークエリのマージでも、型の違いで失敗したことがあります。そちらはこの記事で紹介しています。
区切り位置との違い
［データ］→［区切り位置］でも、20261028を日付に変えたり、文字列の数値を数値に変えたりできます。
ただ、区切り位置はローデータそのものを変える操作なので、データを貼り付けるたびにやり直す必要があります。式の列にしておけば、貼り付けるだけで済みます。毎回データを貼り替える資料では、式で変える方が手間も間違いも少なくなります。
まとめ
- ローデータは触らず、横の列の式で型を変えると、貼り付けるだけで毎回同じ結果になる
- 20261028のような値は、DATE・LEFT・MID・RIGHTで日付にできる
- 見た目だけならTEXT関数でもよいが、中身は文字列になる
- XLOOKUPで型が合わないときは、検索値に
*1で数値、&""で文字列にそろえる - 区切り位置は、貼り付けるたびにやり直しが必要
