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

2024年6月18日

English versoin.

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

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

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

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

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

原因と対処方法

数値が表示される場合

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

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

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

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

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

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

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

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

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

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

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

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

N/Aエラーの場合

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

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

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

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

1900/1/0になる場合

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

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

これは検索結果の空白が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関数も使用して結果を文字列にしながら日付の表記にした状態のキャプチャ

Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド

本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。

【本書の特徴】

  • 情報量:Word-A4サイズ換算で1,400ページ相当
  • 基本操作から高度なテクニックまで、段階的に学習可能
  • 140種類の関数を詳細に解説
  • 109種類の実用的なテクニックと具体例を紹介
  • 最新機能(XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等)を徹底解説

【対象読者】

  • Excel初心者からプロフェッショナルまで
  • 体系的にExcelスキルを向上させたい方
  • 業務効率化を目指すビジネスパーソン
  • データ分析や可視化のスキルを磨きたい方

【本書の強み】

  • 実務に即した例題と解説
  • 視覚的な図表やスクリーンショットで理解を促進
  • 最新のExcelバージョンに対応した内容
  • 著者の長年の経験に基づく、実践的なTipsを多数収録

【更新とサポート】

  • 常に最新の情報を反映するため、定期的に内容を更新
  • 最新版の入手方法:Amazonカスタマーサポートへお問い合わせください

【入手方法】

  • 買い切り:950円
  • Kindle Unlimited:読み放題プランで利用可能

Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。

関連記事