Excel VLOOKUP関数で日付が正常に表示されない場合の対処方法

2020年11月24日

VLOOKUP関数の結果として日付を表示する場合、

下のキャプチャのように日付ではなく数字が表示される場合があります。

VLOOKUPの結果で日付を表示した場合、シリアル値がそのまま表示される状態のキャプチャ

この記事ではその対処方法を紹介します。

XLOOKUP関数でも同様の方法で対処可能です。

原因と対処方法

まずこの事象はバグなど不具合ではなく、シリアル値がそのまま表示されているだけであり

( シリアル値…1900年1月1日から始まる1日毎の連番 )

日付のデータを正常に取得していることに替わりがなく見た目がシリアル値なだけの状態です。

VLOOKUP関数の結果を文字列に変化や文字列結合をしていない場合、

表示形式を変更する必要があります。

まず右クリックで「セルの書式設定」を選択します。

セルを右クリックし、セルの書式設定を選択するキャプチャ

表示形式の分類が日付以外になっているので、ここを「日付」に変更します。

セルの書式設定で日付に設定変更するキャプチャ

すると日付として表示されるようになります。

もちろんユーザ定義書式で任意の形式を設定しても構いません。

セルの書式設定が完了し、日付表示が正常になった状態のキャプチャ

N/Aエラーの場合

検査値が見つからない場合、#N/Aエラーとなります。

検索値が存在せずVLOOKUP関数の結果が#N/Aエラーとなる状態のキャプチャ

これに対処する場合、IFERROR関数を使用します。

=IFERROR(VLOOKUP(…),"")
IFERROR関数によりエラーに対処した状態のキャプチャ

しかしこの数式では検索値が存在して表示項目が空白の場合、不正な表示になります。

検索値が存在して表示項目が空白の場合に不正な表示となる状態のキャプチャ

これは検索結果の空白が0として扱われ

シリアル値の0である1900年1月0日という

あり得ない日付として表示されるためです。

この場合は更にIF関数も使用する必要があります。

=IF(IFERROR(VLOOKUP(…),"")="","",VLOOKUP(…))
IF関数を使用して検索結果が空の場合に対処

同じVLOOKUP関数を二つ書いてしまい冗長でない数式となってしまいますので

VLOOKUP関数部分を別セルに切り出すと冗長性と長さが解消されおススメです。

セル数式
中間セル=IFERROR(VLOOKUP(…),0)
結果セル=IF(中間セル=0,"",中間セル)

中間セルの列は非表示にしても問題ありません。

IFERRORとVLOOKUPを別セルに切り出して数式の質を高めた例
数式の効率化(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関数は計算が重い部類の関数なので

数式を使うセルが多い場合は特に効果が大きいでしょう。

日付でなく数字(シリアル値)が表示される場合の対応方法を紹介。XLOOKUP関数ででも利用可能。LET関数での改善霊位

空白文字を結合している場合

下の記事でも紹介している方法ですが、検索結果が0の場合の対処法で

空白の文字列を結合するものがあります。

=VLOOKUP(…)&""

これを行っている場合、セルが日付ではなく文字列として扱われてしまうため

先述の表示形式変更を行っても表示が改善されません。

そのため空白の結合を削除して表示形式変更を実施します。

もし文字列として扱いたい場合はTEXT関数を利用します。

セル数式
中間セル=IFERROR(VLOOKUP(…)&"","")
結果セル=TEXT(IF(中間セル="","",中間セル),"yyyy/mm/dd")

検索結果が見つからない場合、空の場合にも対処してある数式です。

これにより文字列の状態のまま見た目を正常な日付にすることが可能です。

TEXT関数も使用して結果を文字列にしながら日付の表記にした状態のキャプチャ

関連記事