Excel VLOOKUP関数で日付が正常に表示されない場合の対処方法
VLOOKUP関数の結果として日付を表示する場合、
下のキャプチャのように日付ではなく数字が表示される場合があります。
この記事ではその対処方法を紹介します。
XLOOKUP関数でも同様の方法で対処可能です。
原因と対処方法
まずこの事象はバグなど不具合ではなく、シリアル値がそのまま表示されているだけであり
（ シリアル値…1900年1月1日から始まる1日毎の連番 ）
日付のデータを正常に取得していることに替わりがなく見た目がシリアル値なだけの状態です。
VLOOKUP関数の結果を文字列に変化や文字列結合をしていない場合、
表示形式を変更する必要があります。
まず右クリックで「セルの書式設定」を選択します。
表示形式の分類が日付以外になっているので、ここを「日付」に変更します。
すると日付として表示されるようになります。
もちろんユーザ定義書式で任意の形式を設定しても構いません。
N/Aエラーの場合
検査値が見つからない場合、#N/Aエラーとなります。
これに対処する場合、IFERROR関数を使用します。
=IFERROR(VLOOKUP(…),"")
しかしこの数式では検索値が存在して表示項目が空白の場合、不正な表示になります。
これは検索結果の空白が0として扱われ
シリアル値の0である1900年1月0日という
あり得ない日付として表示されるためです。
この場合は更にIF関数も使用する必要があります。
=IF(IFERROR(VLOOKUP(…),"")="","",VLOOKUP(…))
同じVLOOKUP関数を二つ書いてしまい冗長でない数式となってしまいますので
VLOOKUP関数部分を別セルに切り出すと冗長性と長さが解消されおススメです。
| セル | 数式 |
|---|---|
| 中間セル | =IFERROR(VLOOKUP(…),0) |
| 結果セル | =IF(中間セル=0,"",中間セル) |
中間セルの列は非表示にしても問題ありません。
数式の効率化(LET関数が使える場合)
2020年10月頃にMicrosoft(Office)365で追加された新機能のLET関数を利用すれば
VLOOKUP関数の中間セルも数式の重複もなしに数式を組むことが可能です。
（いつ更新が反映されるかは時間差があります）
LET関数は一定の数式に任意の名前を付けて再利用可能にする関数です。
（マクロやプログラミングの変数のようなもの）
数式は以下のようになります。
=LET(任意の名前,VLOOKUP関数,IF(IFERROR(任意の名前,"")="","",任意の名前))
今回の例では以下のようになります。VLOOKUP関数をvlという名前に置き換えます。
=LET(vl,VLOOKUP(B3,E3:F6,2,FALSE),IF(IFERROR(vl,"")="","",vl))
LET関数を使わない場合は以下のような数式です。
=IF(IFERROR(VLOOKUP(B3,E3:F6,2,FALSE),"")="","",VLOOKUP(B3,E3:F6,2,FALSE))
これにより以下のようなメリットがあります。
- 数式の重複（冗長）がなくなり、数式の変更が簡単になる
- 意味のある名前を付けられるため数式の意図を把握しやすくなる（ただしLET関数を知っている必要あり）
- 数式の実行回数が減るのでExcelの計算が早くなる(今回の例ではVLOOKUP関数が2回→1回)
VLOOKUP関数やXLOOKUP関数は計算が重い部類の関数なので
数式を使うセルが多い場合は特に効果が大きいでしょう。
空白文字を結合している場合
下の記事でも紹介している方法ですが、検索結果が0の場合の対処法で
空白の文字列を結合するものがあります。
=VLOOKUP(…)&""
これを行っている場合、セルが日付ではなく文字列として扱われてしまうため
先述の表示形式変更を行っても表示が改善されません。
そのため空白の結合を削除して表示形式変更を実施します。
もし文字列として扱いたい場合はTEXT関数を利用します。
| セル | 数式 |
|---|---|
| 中間セル | =IFERROR(VLOOKUP(…)&"","") |
| 結果セル | =TEXT(IF(中間セル="","",中間セル),"yyyy/mm/dd") |
検索結果が見つからない場合、空の場合にも対処してある数式です。
これにより文字列の状態のまま見た目を正常な日付にすることが可能です。
Excelを効率的に習得したい方へ
当サイトの情報を電子書籍用に読み易く整理したコンテンツを
買い切り950円またはKindle Unlimited (読み放題) で提供中です。
Word-A4サイズ：1,400ページの情報量で
（実際のページ数はデバイスで変わります）
基本的な使い方、関数の解説(140種類)、
頻出テクニックと実用例(109種類)、
XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。
体系的に学びたい方は是非ご検討ください。
アップデートなどの更新事項があれば随時反映しています。
なお購入後に最新版をダウンロードするには
Amazonへの問い合わせが必要です。