Excelの日付表示。XLOOKUPでエラーになる理由・月末や月初を表示する関数。

Excel

Excelの日付。表示の変更をしたり、XLOOKUPやコピーする場合の関数など工夫もできます。

その実態は数字。表示はあっていても数字が違えばエラーになることもあります。

ExcelのXLOOKUPでできること

ExcelのXLOOKUPでは次のようなことができます。

セルD3と同じデータをカギにして、リストから同じ月の数字(ここでは売上)を、
GOforIT

表示することができます。
GOforIT 1

XLOOKUPについては、過去にも記事にしています。現在、よく使っている関数の1つです。

ExcelのXLOOKUPでできること。MFやfreeeの推移表に同じデータ(検索値)が2つある場合の解決策 | GO for IT 〜 税理士 植村 豪 Official Blog

XLOOKUPやVLOOKUPは検索値がリストにあるデータと一致している必要があります。

検索値がないと、エラーになることがあります。

年月で検索してエラーになる理由

Excelの日付表示。

表示の変更はセルの書式設定から変更することができます。

たとえば、2021年1月と表示したい場合には、ユーザ定義で「yyyy”年”m”月”」と設定します。

GOforIT 7

ただ、同じ2021年3月で「11,318,000」が表示されるはずが、エラーになることがあります。

GOforIT 3

同じ2021年3月なのになぜエラーになるか?ちゃんと理由があります。

「2021年3月」と表示されていたとしても、それは見た目が同じというだけで、実際のデータが違っているからです。

セルD3にカーソルを合わせてみると、「2021/3/1」になっています。

GOforIT 4

いっぽうで、リストの方を見ると、「2021/3/31」。

GOforIT 6

Excelの日付。どちらも表示では「2021年3月」。ただ、その実態は数字です。

日付は1900年1月1日を起点にした日数になっています。

セルの書式設定で、日付を数字に変更するとそれがわかります。

GOforIT 8

  • 「2021/3/1」→44256
  • 「2021/3/31」→44286

それぞれの数字の差は30で、ちょうど日数の差です。

関数を使って月初を表示するには?

1つ日付を入力し、翌月以降をコピーしたい場合という場合もあるでしょう。

1つずつ入力するのではなく、関数を使っていきましょう。

翌月末はEOMONTH

翌月末なら、EOMONTH。(End of Month)

A5セルに「=EOMONTH(A4,1)」とすれば、A4セルの翌月末が表示されます。

GOforIT 5

関数ですが、A5セルにある日付は2021/3/31です。

なので、D3セルを「2021/3/31」にしておけば、一致するので3月の売上が表示されます。

DATE・YEAR(年)・MONTH(月)・DAY(日)

月初を表示したい場合には、DATE関数を使うのがおすすめです。というか、月初に限らず使えますが。

A4セルに「=DATE(year(A4),month(A4)+1,day(A4))」と入力することで、A4セルの翌月1日の日付が表示されます。

これはA4セルが「2021/2/1」のデータを持っているからです。(もしくは「=DATE(year(A4),month(A4)+1,1)」でも結果は同じ)
GOforIT

  • 翌々月の1日なら「=DATE(year(A4),month(A4)+2,day(A4))」
  • 前月の1日なら「=DATE(year(A4),month(A4)-1,day(A4))」

と月の部分で調整しますし、

  • 来年の同日なら「=DATE(year(A4)+1,month(A4),day(A4))」

と年の部分で調整。

関数を使えば、1つ入力して、あとはコピーをすれば大丈夫です。

Excelの日付表示は自由に設定ができますし、関数を使って入力を効率化することもできます。

いろいろ試しつつ、使っていきましょう。


【編集後記】
昨日はオフ。雨だったこともあり、自宅でゆっくりと。夕方に子どもたちとOM4(ジンバル)やα7Cを使って動画を撮影したりして遊びました。市役所、窓口のシャッター閉まっているのになぜか空いていました。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
市役所で子どもたちと動画撮影