【Excel】45分で日付・時刻を完全マスターしよう
新人育成用に”Excelでの日付の扱い方”について調べていたところ、断片的な関数紹介などが多く、まとめて学べるものが少なかったのでまとめました。断片的というと批判的ですが、「レシピ的」「辞書的」という意味です。
日付や時刻についての説明は前提知識が必要なことが多く、初心者～中級者の方にとっては辞書的な記事よりも”そもそもの仕組みから、実務でどう使うか”までを一連の流れとして学習できる記事があったほうがいいのではないか？ と思い、本記事を書きました。
もともと新人用に書いたものなので、
・初出の単語はなるべくふりがなや説明を加える
・格好をつけた小難しいことは一切やらず、実務で必須の知識に絞って解説
するようにしています。
逆に必要度の高いものであれば、多少難しくても脱線して解説をしています。
アンチパターンやちょっとした小技、ショートカットや日付系以外の業務への応用方法などです。
「書式やシリアル値についての理解はあるが業務でやりたいことが言語化できていない」という方は、後半の関数紹介だけ見て行ってください。
具体的な業務に関数を当てはめる形式ではなく「ある関数がどんな(抽象的な)業務に当てはまるか」という観点で解説をしているので、一般的な業務はもちろん特殊な業務でも使える知識があるはずです。
また日付時刻以外の関数も「日付時刻を扱うときに便利な関数」は思い付く限りすべて記載しています。
本記事は初級～中級の方を対象としています。
対象読者
・断片的ではなく、日付や時刻の取り扱い全般を(最速で)マスターしたい
・日付や時刻に関する知識がとっ散らかっているので整理したい
→最初からお読みください
・業務で日付や時刻を扱うが、苦労することが多い
→関数だけ読んでも構いませんが、最初から読むことを推奨します。レシピ的な使い方は想定しておらず、「教科書的」「小説的」な構造になっているためです。
対象レベル
・セル番地が読めて、数式の書き方や簡単な関数（SUMやIFなど）は知っている
・書式（セルの表示形式）についての理解はいまいち
・日付や時刻に関する知識はほぼ無い～なんとなく使っている程度
1.日付の扱いを理屈で覚えよう
Excel作業でなにか改善や自動化をしたいと思ったとき、「関数一撃で解決する」ということは実はそんなに多くなく、
「作業を細かい要素に分解→一つずつ解決することで、結果として作業全体が自動化する」ということの方が圧倒的に多いです。
基礎部分はあまり面白くないと感じられるかもしれませんが、この1章は非常に重要な部分です。Excelが日付をどのように扱っているのかを理解することで、以降の章がすんなりと頭に入ってくるはずです。
1-1.日付形式とは｜なぜ日付の扱いがこんなにも面倒なのか
Excelでは様々な形式の値（データ）をセルに格納することができ、日付形式はそのうちの一つです。
日付形式の話をする前に、そもそもどんなデータ形式があるのかを確認しておきましょう。他にもありますが、代表的なものは以下の４つです。
・文字列
・数値
・数式
・日付や時刻
以降、日付や時刻のことをまとめて「日付」と記載することがあります。
おさらいしておくと、
・文字列は計算に使うことができない値のことです。
「村上」や「500円」、「0120-783-640」や「1+2+3+4」などはすべて文字列です。
計算には使えないので、たとえばA1セルに「500円」と入っている場合に「=A1*2」とすると、「1000円」とはならずにエラーになります。
・数値は計算することのできる値です。
「123」や「500」、「2.7182」、「-8192」などが数値です。ちなみに数値は先頭を0にすることはできません。
「000123」と入力すると、先頭の0がすべて消えて「123」という数値になります。
・数式は=（半角のイコール）から始まる計算式のことです。
「=A1&"様"」、「=500」、「=1+2+3+4」、「=SUM(A1:A100)」などが数式です。数式はその計算結果をセルに表示します。
また”文字列を演算している”、”検索値が見つからない”など、適切に処理することができない場合はエラー値を出力することもあります。
・そして日付です。これはExcelが日付と認識できる値を日付として表示しているもののことです。ほかと違ってわかりづらいですね。
「2024/3/17」や「2月22日」などは日付に見えますが、必ずしもそうではないことに注意してください。
1-2.日付と認識できる値を日付として表示しているもの とは
日付形式はExcelの中でも特に扱いが難しいものとなっています。
日付形式が扱いづらい理由ですが、
①シリアル値 （日付として認識できる値）
②セルの表示形式 （日付として表示）
の２つを理解する必要があるためです。
そして、ある１つの物事を学ぶために２つの要素を同時に覚える必要がある場合、たいていの場合学習意欲が大変に下がります。その結果学習が後回しとなり、
・覚えないまま日付を扱うことを諦める
・「年セル・月セル・日セル」に分けて無理やり運用している
・時間だけは毎回手計算する
といったことになりがちなのです。
ここでは根本原因であるシリアル値と表示形式について、「どちらもわからない」という前提で順を追って説明していきます。
インジェラの作り方
まずはテフをオフチョベットします
そしてオフチョベットしたテフをマブガッドしてリットを作る
そして発酵させたリットにアブシィトを加えて混ぜます
2.シリアル値と表示形式
2-1.シリアル値｜Excelが日付をどう扱っているか
まずはExcelで日付や時刻をどのように扱っているのかを見ていきましょう。
上でも出てきましたが、Excelでは日付をシリアル値という方法で扱っています。
シリアル値がどんなものかというと、「1900/1/1を 1 として、以降1増えるごとに1日進んだとして扱う値」のことです。
1900/1/1が 1 なので、たとえばシリアル値が10なら日付としては1900/1/10ですし、日付が1900/1/25ならシリアル値は25です。
そして混乱しがちなポイントですが、シリアル値は数値です。
「数値とは別のシリアル値という形式がある」わけではありません。
単なる数値を、日付を扱う場合にはシリアル値と呼ぶということです。
シリアル値の説明は以上です。
「えっ、これだけ！？」と思った方も多いとは思いますが、本当にこれだけです。
これだけと言うと語弊があるので少し説明を加えると、
シリアル値1増加につき1日進むので、
・シリアル値0.5増加なら半日＝12時間進む
・シリアル値1/24（二十四分の一）増加なら、1時間進む
ことになります。
また負の値や大きすぎる値はシリアル値としては扱うことができません。
これらについてはあとで詳しく説明します。
こうすることで日付と時刻をまとめて扱える。
2-2.セルの表示形式｜中身を変えずに見た目だけ変える
Excelには中身はそのまま、見た目だけ変える機能があり、その機能のことを「表示形式」（の設定|を設定する 等）と言います。
よく使うところで言うと数値にカンマを付ける、￥記号を付ける、パーセンテージ表示にする、小数点を２桁まで表示する、といったものがそうです。
見た目を変えられることで何がうれしいかというと、
・入力を簡略化できる（いちいちカンマや￥を入力しなくて良い）
・表示上「円」や「個」を付けつつ、数値として扱える（計算ができる）
などがあります。
では実際に表示形式を変更してみましょう。
表示形式はセル単位で設定ができます。表示形式を変更したいセル上でCtrl＋1（テンキーではなく、Qキーの上の1キー）を押すと書式設定の画面が開くので、実際にやってみましょう。
セルに適当な数値（5000など）を入力して、そのセル上でCtrl＋1を押してください。
（セル上で右クリック→セルの書式設定 でも構いません）
ちなみにタスク（開いているアプリ）の切り替えはAlt ＋ Tabでできます。Excelとブラウザを行ったり来たりする場合、このショートカットを使いましょう。
Altを押したままTabを押すと、開いている画面の一覧が表示されます。そのままTabキーを連打するか矢印キーで開きたい画面が選択できます。
またTabキーを押す際、Shiftキーも一緒に押すと逆回転になります。
セルの書式設定ダイアログが開いたら、以下の手順に従って表示形式を「0”円”」としてみてください。
以下の図の上側のようになればOKです。
セルでの表示は「5000円」となっていますが、数式バーで見ると中身は数値の5000のままであることがわかります。
単に「5000円」と入力した場合、数式バーで見ると実際に「5000円」という文字列が入っています。
セルの中身は5000という”数値”のまま、表示形式によって見た目だけ「円付き」にしている。
セル自体に「5000円」と入力してしまうと”文字列”となり、計算ができない。
本記事は日付に関しての解説なのであまり深追いはしませんが、少しだけ書式に関して説明をしておきましょう。
まず、書式設定ダイアログの 「数値」 「通貨」 「会計」 「パーセンテージ」あたりはよく使います。これに関しては特に説明なしで触れるかと思いますので、どのような設定があるかだけでも見ておくと良いでしょう。
「文字列」を選択すると、そのセルは何を入力しても文字列とみなされるようになります。
これはたとえば0から始まる数値や=から始まる文字列などを入力したいときに使います。
「ユーザー定義」は、書式記号と呼ばれるものを使うことで好きにカスタマイズした書式を使える機能です。
今回使用した「0”円”」というものは、「0」の部分が【数値形式 - カンマ区切り無し、0の場合0と表示】であることを表す書式記号です。
(””でくくられた部分は書式記号ではなく、固定の文字を表します)
書式記号には以下の図のようなものがあります。記号の羅列に最初は面食らうかもしれませんが、実際はそこまで難しくありません。
今回挙げた「0”円”」や「0”個”」などはデフォルトでは使えない（ユーザー定義で設定する必要がある）表示形式なので、覚えておきましょう。
そして、シリアル値を日付として表示する表示形式があります。
次の節が今回の本題です。
ちなみに、Excelはセルに入力した値によって自動的に表示形式を変更してくれる（しやがる）ことがあります。
たとえばセルに「1,234」と入力すると自動で【通貨 -カンマ区切り｜ 通貨記号無し】になりますし、「2024/1/23」と入力すると自動で【日付 - 年4桁/月1or2桁/日1or2桁】という表示形式に変更されます。
そしてこの表示形式はセルの中身を消しても残ります。
「数値を入れると1903年とかのわけのわからない日付になる」の原因はこれです。
3.表示形式に関しての大事な補足
本題に入る前に、２つだけ大事な補足をしておきます。
3-1.補足① 日付と見なされる入力
自動的に表示形式を変更してくれる機能についての補足です。
Excelは「日付と見なすことのできる入力」をすると、自動的に以下の２つの処理を行います。
①入力した文字を、日付とみなした場合のシリアル値に変換
②表示形式を、入力に合った形の日付形式に変換
「2024/1/23」なんかは当然日付として入力しているはずなので問題ありませんが、
・「出席者数11人/15人中」の意味での「11/15」
・型番や章番号のつもりでの「1-1」
・住所番地の意味での「24-1-2」
なども、勝手に日付にされてしまいます。
具体的な対応（対策）方法はやりたいことによって違ってきますが、「どういった入力が日付と見なされるのか」をざっと把握しておくことで、予防とトラブル解決につながります。
ここでは日付とみなされる入力の代表的なケースを記載します。
パターン１.〇年〇月〇日という入力
・令和5年12月4日
・2024年1月23日
・3月19日
これらの入力は日付と見なされます。表示形式も入力した内容に合ったものになります。（たとえば令和5年12月4日と入力しているのに2023/12/4となることはなく、令和〇年〇月〇日という形式は保持されます）
これらはもともと日付として入力していると思いますので、日付（シリアル値）に変換されて困ることは無いでしょう。
困るケースは次のパターン２です。
パターン２．スラッシュまたはハイフンで数字をつないだ入力
・2024/1/23
・2024-1-23
・2024-1/23 2024/1-23
・2024/01 2024-01
・r5-1-23
・1-23 1/23
これらの入力はすべて日付と見なされます。数字部分は全角半角を問いません。しかも、1-23や1/23は「1月23日」形式に、ひどい場合は2024-01→「Jan - 24」形式（しかもこの24は2024年と言う意味！）に変換されます。
型番や分数の意味でそれらを使いたい場合は表示形式を文字列にしてから入力するか、「・」などの記号を頭につけると良いでしょう。
ちなみに入力の先頭に「'」（シングルクォーテーション）を付けることでも文字列にすることができます。
ただ、個人的におすすめなのは「そもそも日付以外でそういった形式を使わない」ことです。特に共有することのあるファイルではExcelの知識が少ない人も触りますので、ミスを未然に防ぐことが大切です。
シングルクオーテーションは Shift + ７ で入力できる。
ちなみに、
・2024/2/30のような「日付と見なせない」値
・平成10 2月2日 や 平成25年1月23など、空白があったり”日”が欠けている。いわゆる「不備がある」値
の場合、文字列となります。
また補足の補足ですが、勝手に変換してくれる（しやがる）ものは他にも以下のものがあり、中でも「分数」「カッコつき数値」は困ることが多いので、覚えておきましょう。
・時刻
・パーセンテージ
・カンマ付きの数値
・円記号付きの数値
・分数
→ 1 2/3 など。この場合は帯分数とみなされ、内部的に1.666666・・・という値に変換される。
・カッコつき数値
→ (5000)なら-5000等、マイナスの値に変換される
3-2.補足② CSV取り扱い時の注意点【要注意】
そして補足①の現象は「ExcelでCSVを開いたとき」にも起こります。
ExcelとCSVは別のもの
CSVファイルとExcelはと同じものだと思っている方もいるかと思いますが、実は別のものです。
CSVというのはファイルの形式のことで、ファイル形式というのは「このファイルは画像だよ」「このファイルは圧縮ファイルだよ」「このファイルはテキストだよ」といったファイルの種別を表すものです。
そしてCSVは「Comma Separated Values」の略で、「値をカンマで区切ったテキストだよ」という形式です。
値をセルで区切っているExcelと親和性が高いので、自動的にExcelで開くような設定なっていることが多いのです。
これは大変危険な要素なので、必ず覚えておいてください。CSVファイルをExcelで開いて、さきほど挙げた
・「出席者数11人/15人中」の意味での「11/15」
・型番や章番号のつもりでの「1-1」
・住所番地の意味での「24-1-2」
これらの値が勝手に日付に変換されて、そのことに気づかず保存してしまったとします。
そうすると、もしバックアップをとっていないファイルであれば、復元は不可能です。
日付化や「電話番号の先頭の0が抜ける」ぐらいならなんとか修正ができるかもしれませんが、「予想もできない値が分数や海外日付に変換されて完全に復元不可能」といったことが起こりますので、CSVをExcelで開く際は必ず何らかの対策を取るようにしましょう。
4.日付や時刻に関する表示形式
4-1.日付に関する表示形式
いよいよ本題です。
冒頭で、「日付とは、Excelが日付と認識できる値を日付として表示しているもの」と説明しました。最初は意味が分からないと思われたでしょうが、ここまでの説明で、
・日付として認識できる値 ＝ シリアル値（ただの数値）
・日付として表示 ＝ 表示形式（を設定）
ということは理解いただけたかと思います。
さて、中身を変えずに表示だけ変えるのが表示形式でした。
ここで学ぶことは「シリアル値にどんな表示形式を適用すると日付に見えるようになるのか？」です。
ここで2つ、ショートカットキーを覚えましょう。
Ctrl + ； で、本日の日付が2025/1/23形式でセルに入力されます。
Ctrl + ： で現在の時刻が12:34形式で出てくるので、それとあわせて覚えると覚えやすいかもしれません。
あとからイメージしやすいように、具体例から見ていきましょう。
適当なセルに45500と入力してください。
その後 Ctrl + 1 で書式設定ダイアログを開き、表示形式を見てみると「標準」になっていると思います。ここで
①分類から「日付」を選択する
②画面を閉じずに「ユーザー定義」を選択する
と、いまどんな表示形式が適用されているのか（「書式記号を使って書くとどうなるのか」）が表示されています。
これまで「2024/1/23形式」と言っていた表示形式は、書式記号で書くと「yyyy/m/d」というものだということがわかりました。
気づいた方もいるかもしれませんが、yearのy、monthのm、dayのdです。
そして日付は他の表記方法を使いたいこともあります。例えば「24年1月23日形式」や「令6 1.23（火）形式」などです。
これらの表記をしたい場合、別の書式記号を使う必要があります。これは覚えるしかないものなので、以下に一覧とまとめを掲載します。一覧で見ると数が多く感じますが、まとめで見ると実は簡単です。
日付の表示形式一覧
ちなみにGoogleSheetsの場合、曜日はaaaではなくddd
日付の表示形式まとめ
これらの書式記号を組み合わせて表示をカスタマイズすることになります。
前の章では「0”円"」という形式を使いましたが、それと同じ要領で
「yy”年"m"月"d"日"」とすれば「24年1月23日形式」に、
「ge m.d （aaa）」とすれば「令6 1.23（火）形式」になります。
「日」や「円」、「kbps」など、固定の文字を入れる際は””（ダブルクォーテーション）でくくるのがルールです。（括弧やスラッシュなどの記号は””を付けなくても構いません）
実際は””を付けなくてもOKボタンを押せば自動で補完してくれますが、もともと書式記号として使われている場合はエラーが出ます。
（たとえば勤務時間などで 0.0 H という書式を使いたい場合、Hは書式記号として使われているのでエラーになります）
また自動補完の場合は1文字ずつ””がついて見た目が汚くなります。
4-1-1.曜日について｜曜日の上手な取り扱い方法
曜日の書式記号が aaa であるということは上記の通りです。
そのため「日付部分に曜日も表示したい」といった用途なら、「yyyy/m/d(aaa)」とすれば事足ります。
「日付部分と曜日部分でセルを分けたい」といった場合、以下のようにします。シフト表など、横幅を取りたくない場合によく使う方法です。
注意点として、月や火などはあくまで表示形式で、中身はシリアル値であることを忘れないようにしてください。
つまり、このままではSUMIFSやCOUNTIFSなどで「金曜日の売上」といった集計をすることはできません。
対策方法としては、
・曜日を1～7（または0～6）として扱う
・曜日を文字列として扱う
の２つがあります。
これらについては後半の関数の章で説明します。
4-2.時刻の場合のシリアル値
ここまでは日付の扱いについて学んできましたが、時刻についても学んでいきましょう。とはいっても、特に新しいことはありません。
日付と同様に、時刻もシリアル値（要はただの数値）で管理されています。
日付の場合は「1増えると1日進む」というルールでしたが、時刻の場合も全く同じルールで、「1増えると24時間進む」と読み替えるだけでOKです。0.25なら6時間、0.5なら12時間といった具合です。
適当なセルで Ctrl + ： を押して現在時刻を表示してみましょう。
その後に Ctrl + 1 で書式設定ダイアログを開き、先ほどと逆の要領で表示形式を「標準」に戻してみてください。
時刻によって表示は変わりますが、その時刻は「1日(24時間)の何倍か？」という数値が表示されます。たとえば12時ちょうどなら二分の一日なので0.5、18時ちょうどなら四分の三日なので0.75ですね。
ちなみに
1時間は1/24日（二十四分の一日）で、これは小数にすると0.04166666666・・・という値になります。
そのため、実際には時刻→シリアル値に変換した場合、○時ちょうどという時刻だったとしても0.1や0.3のようなきれいな数値になることはあまりありません。
さて、このように日付と時刻を同じルールで扱っているので、「2024/3/17 12:00:00」みたいな値を扱うこともできます。
たとえば2024/3/17 12:00をシリアル値に変換してみましょう。
45368が日付の分、0.5が時刻のぶんです。
4-2-1.時刻に関する表示形式
こちらも特に新しいことはありません。
Excelが時刻と認識できる値を時刻として表示するための書式記号を覚えましょう。日付の場合と同様に、英単語の頭文字となっているので覚えやすいですね。
日付との大きな違いとして、24時間（や60分、60秒）を超えた場合にどう表示するかという選択があります。
たとえばシリアル値が1.5の場合について考えてみましょう。業務内容によって「12:00」と表示してほしい場合と「36:00」と表示してほしい場合があると思います。
どちらにしたいかによって、hと[h]を使い分けてください。
上図にも記載しましたが、「時間セルを合計して、合計時間をh:mm形式で表示したい」という使い方が大半かと思います。
以下の図は、勤務時間の合計を求めようとしたら意味不明な値になったというよくある例です。
こういった場合は[h]:mmとすることで、24時間を超えても時間で表記してくれるようになります。
注意点が１つあって、Excelでは日付も時刻もシリアル値を日付や時刻として見せているだけです。
そのため、たとえば”36:00"という値は、数式バーで見てみると「1900/1/1 12:00」と表示されています。どちらもシリアル値は1.5ですが、表示形式のみ違うのです。
そこでよくあるうっかりとして、
「値としては日付＋時間が入っているが、表示としては時刻のみ表示されている」ことに気づかず計算に使用してしまって結果がおかしくなる
というものがあります。
こういった現象に遭遇したときに対処できるよう、知識として知っておきましょう。
4-3.日付と時刻に関するエラーやトラブル
最後によくあるエラーやトラブルを紹介します。
こちらも知識として覚えておいて、何かエラーが発生したときに対処できるようにしておきましょう。
①負の値と大きすぎる値
冒頭で少し触れましたが、シリアル値は
・負の値(0未満)
・大きすぎる値(2,958,466以上）
を扱うことはできません。
これらの値に対して日付書式を適用すると、#############というちょっと変わったエラー表示が出ます。
よく発生するシチュエーションとしては、時刻同士の引き算をしたときに0時をまたいでいたような場合です。
24時のつもりで0時と入力してしまって、0時-19時のような計算をするとマイナス5時間となってしまうのです。
ちなみにこのエラーは「幅が足りなくて数値が表示しきれていないとき」に出るものと同じですが、"日付が負の値または大きすぎる値エラー"の場合はどこまで幅を広げても#####のままになります。
なんで2,958,466？
2,958,465を日付にすると9999/12/31となります。日付の限界が9999年末と言うのはちょっと面白いですね
②実は日付じゃない
パッと見日付っぽく見えるけど、実は文字列だったということがあります。
文字列に対して演算を行ってしまうと#VALUE!エラーになりますね。
Excelでは数値は右寄せ、文字列は左寄せがデフォルトになっているので、慣れてくるとこんな風に見分けることもできます。
ここまでで「シリアル値」と「表示形式」についての説明は終了です。
長々とお疲れ様でした。これ以降が楽しい部分です。
5.日付や時刻の計算
5-1.計算結果として「日付」がほしい場合
シリアル値というのはただの数値のことでした。
つまり、数値に対してできる演算は日付に対しても全てできるということになります。
そして日付や時刻の計算は、突き詰めていけばたいていの場合四則演算で事足ります。
「やりたいことはただの足し算なんだけど、”時”の値だけほしい」とか「月末日が毎回変わるから手作業になってしまう」といった部分で困ることの方が多いでしょう。
それらの解決方法は次の「関数」の章で解説するので、組み合わせて使ってみてください。
日付と数値ではなく日付同士の引き算で「残り〇日」を出すこともできる
ここまでの知識があれば、
・足し算なのに結果の方が小さい
・書いてある通りにやったのに#VALUE!になる
・数値を入力したのに1900年とか出てくる
といったことは起きないでしょうし、起きたとしても自力で対処できるようになっているでしょう。
補足 日付を直接（セル参照ではなく）計算する
通常の場合、日付はセルに入った値を参照して計算することが多いでしょう。
セルに直接日付を入れて計算したい場合、””（ダブルクォーテーション）で日付を囲うことで日付シリアルとして扱うことができます。
5-2.計算結果として「数値」がほしい場合
「日付 - 日付で日数を求める」など、日付の場合は特に問題は起きないかと思います。（書式の変更忘れには気を付けましょう）
時刻の場合には注意点があって、時刻シリアルの特徴を理解しておく必要があります。
時刻シリアルは24時間で1進む
1時間なら1/24（二十四分の一｜0.41666…）進む
何度も見てきたように、シリアル値は「1日を1」として扱っているのでした。つまり、「1時間を1」として扱いたい場合、シリアル値に24を掛ける必要があるということになります。
これは「時間×単価＝料金」というよくある計算に必須となる知識ですので、必ず覚えておいてください。
「計算結果が時刻（日付）になってしまう」現象は、もう説明する必要はありませんね。
時刻に関しては24を掛けて数値化（表示形式を数値系のものに）することで、計算上扱いやすい値になります。
言うまでもないことですが、書式を「0」など小数点のないものにしてしまうと小数点以下が四捨五入されて、たとえば1.5（時間）が「2」と表示されてしまうので、このことには必ず留意してください。
「Excelは勝手に四捨五入する」と言われる原因の一つです。
6.日付や時刻に関する関数
ここでは日付や時刻を扱う関数だけではなく、日付や時刻に関する関数も含めて紹介していきます。
解説の形式ですが、具体的な業務内容に対して関数を当てはめる形式ではなく、その関数を使うときにありがちな業務やシチュエーション（の断片）を実例として挙げる形式をとっています。
前者の方法ではカバー範囲が狭いためです。
そこで、この章を読むときは「自分の環境ではどういう使い方があるかな」という目線で読まれることを推奨します。
またこの章の関数は一度に全部を覚える必要はありませんので、まず「どんな関数があるのか」をざっと眺めてみてください。
「何に使うか」と「よくあるシチュエーション」が書いてあるので、そこだけ目を通して、興味をひかれたものだけ見てみるぐらいで問題ありません。
そして自分の業務では使わなそうな関数は記憶の片隅にでも置いておき、自分の業務で使えそうな関数があれば、その部分だけ時間をかけて読んでみるぐらいで良いでしょう。
いざ必要になった時に「こんな関数あったな」ということを思い出せることが大事で、そのときに調べれば問題ありません。そのうち体が覚えます。
関数説明はなるべく直感的に理解できるように、例えば本来なら「シリアル値」と書くべきところを「日付セル」と書いたりしています。
・YEAR関数 MONTH関数 DAY関数｜日付シリアルから「年」「月」「日」を数値として取り出す
この関数については特に説明の必要はないでしょう。
書式の章で覚えたyyyyやm、dを数値として取り出せると考えればOKです。
・DATE関数｜（バラバラの）「年」「月」「日」からシリアル値を生成
最初はなかなか使いどころが思い浮かばない関数ですが、慣れてくるととても重宝します。
よくあるシチュエーションは図にも記載していますが、使いどころをイメージしてもらうために具体例を見てみましょう。
①別のセルに入った年月日を、ちゃんとした日付にしたい
②請求書が届いた日を入力したら、自動的に「翌月25日」の日付が入るようにしたい
関数の中に関数が入っていて分かりづらいですね。順を追って説明します。
DATE関数を使うためには「年」「月」「日」をそれぞれ数値で入れないといけないので、①まずYEAR関数とMONTHで年と月を取り出します。
そして②MONTH(D4)+1とすることで翌月の月に変換して、③「日」部分を固定で25としています。
MONTH(D4)+1 部分がどのように扱われるについて補足しておきましょう。
先に「MONTH(D4)」が計算されて「3」となり、その後に3+1が行われ最終的に「4」になります。その結果、DATE(2024,4,25)となります。
12月の場合どうなるの？ と思われるかもしれませんが、ちゃんと年に繰り上がってくれます。
繰り上りの例
=DATE(2024,13,1) →2025/1/1
=DATE(2024,2,31) →2024/3/2 ※うるう日も考慮される
注意
MONTH(D4+1)だと全く違った意味になってしまいます。
この場合は「D4セルのシリアル値に1を足した値の月」、つまり「D4セルの1日後の月」なので、3のままになってしまいます。
・EOMONTH関数｜〇カ月後の月末日
使うシチュエーションはDATE関数に近いですが、たとえば25日固定ではなく月末固定のような場合に使います。
月末日は月によって変わってしまうためです。
他にも「EOMONTHした値に1を足すことで、翌月1日の日付を取得」といった使い方もします。
・TODAY関数｜今日の日付
今日の日付を出してくれます。
ただ、ファイルを開く、セルを編集する、などの何 かしらのアクションを行わないと値の更新がされません。昨日開いたファイルをそのまま放っておくと、日が変わっても昨日の日付のままです。
また注意点として、「日付入力の自動化」といった用途には使えません。
日が変わればTODAY関数の中身も変わってしまうためです。
・DATEDIF関数｜開始日と終了日の差【日単位・月単位・年単位】
※この関数は【隠し関数】です。そのため、=DATEDIF( まで入力しても、入力時ヒントなどは出てきません。
隠し関数
なぜか３つだけある、関数ライブラリには表示されず、関数ウィザードのダイアログにも現れず、セルに手入力をしてもヒントも予測も出てこない関数。
ある日とある日の差を、日数ではなく月単位・年単位で求めることができます。（生年月日や入会日から今日までの年数を出そうとして、引き算して365で割ったあとアレっとなった人は多いのではないでしょうか）
使い方は簡単で、
=DATEDIF( 開始日シリアル値 , 終了日シリアル値 , 単位 ) とするだけです。終了日にはTODAY関数を埋め込むことが多いでしょう。
「単位」には以下の6種類があります。
Y、M、Dは見ての通りです。
YMやMDをどういったときに使うかというと、「〇年〇カ月」といった表記をしたい場合です。
たとえば社歴を「〇年〇カ月」で表したい場合、年の部分は"Y"で、カ月の部分は（”M"ではなくて）”YM”でそれぞれ取得することになります。
ちなみに日数のみが欲しい場合、単に引き算したほうが早いです。
・SUMIFS関数、COUNTIFS関数｜日付を条件に集計してみよう
SUMIFS、COUNTIFSはどちらも良く使う関数ですが、日付が条件になると混乱してしまいがちです。
ここではSUMIFSやCOUNTIFSは知っている前提として、日付が条件の場合の使い方を見てみましょう。
例. 元となる表があって、月や四半期単位で集計したい。
集計表ができればグラフ作成も簡単。
まずはありがちな混乱パターンを見てみましょう。以下のようなものがあります。
文字列の場合、 =COUNTIFSの中にDATE(IF(H5>=4,2024,2025),LEFT(IF(LEN(H5)=2,1,2)),1) と EOMONTH(DATE(IF(H5>=4,2024,2025),LEFT(IF(LEN(H5)=2,1,2)),1),0)を入れてガチャガチャする、みたいな馬鹿馬鹿しいことになる。
これらの混乱は、最初の図のよう「集計開始日」と「集計終了日」をにセルに入れてしまうことで、ずいぶんと簡単になります。
しょせんはただの〇〇IFS関数です。特に難しいことはなく、「条件範囲と条件の組み合わせ」がすっきりすればよいのです。
「ある日からある日まで」という条件を〇〇IFS関数で使うときの「条件範囲と条件の組み合わせ」は以下のように書くことができます。
・日付列 , ">="&開始日 (条件範囲1と条件1)
・日付列 , "<="&終了日 (条件範囲2と条件2)
=COUNTIFS( $C:$C , ">="&$H5 , $C:$C , "<="&$I5 )
なかなか覚えられないという人もいるかと思いますが、これらは機械的に覚えてしまってもよいでしょう。
また、見た目を整えたい場合は以下のような手段があります。
行や列の非表示については賛否ありますが、手段として知っておいて損はありません。
どうしても非表示にしたいならI列ではなくZ列やAA列など、遠く（あまり触らない位置）に置いておく方が良い。
いわゆる「変な数字になった」というときに原因の特定が少しだけ難しいが、このネスト(→関数のなかに関数を入れること)は便利。
・LEFT関数、MID関数、RIGHT関数｜入力を楽にする、データを綺麗にする
あまり目立たない関数ですが、データ整形や入力支援としては非常に強力な関数です。
日付や時刻の場合も活躍する機会の多い関数ですので、実例を見てみましょう。
ちなみに日付に対して単に=LEFT(日付セル,1)のようにすると、日付をシリアル値に直したあとに左から1文字取ってきます。（たいていの場合、「4」が返ってきますね）
「2024/1/23」から年だけ取り出したい場合はLEFTではなくYEARを使用しましょう。
①時刻の「：」を打つのが面倒
勤怠ツールのある会社の方からすると「なんじゃそりゃ」という感じかもしれませんが、中小～個人事務所などでは非常によく見かける形式です。
場合によってはこういったシートを数十枚から百枚近く手入力するという業務があるのです。（ここではExcel以外の話はしません）
そうなってくると「：」入力の手間だって馬鹿にはなりません。テンキーから遠いので、地味にイライラするのです。
そういうとき、「入力部分は文字列にしておいて4桁入力」→「LEFTやRIGHTで左右2桁ずつ取り出してうまい具合に数値にしたあと24で割る」という方法があります。
4桁文字列から時刻への変換手順は、
①「時」をLEFTで取り出す
②「分」はRIGHTで取り出したものを60で割る ※1時間＝60分なので
③時と分を足す
④24で割る
です。
例として、入力が「1815」の場合を頭のなかで追ってみましょう。
①「1815」から18を取り出す ←18（時）が取れた
②「1815」から15を取り出して60で割る ←0.25（時）が取れた
③時と分を足す ←18.25（時）になった。18:15は18.25時なので合っている
④24で割る ←シリアル値に変換
（最後の④はシリアル値に変換するためですので、最終的に数値に戻して計算する予定なら18.25のまま扱っても良いでしょう）
時刻でこのような入力方法を使用することには賛否あると思いますが、他の業務でも応用が利きます。
たとえば紙のアンケート集計で「1~5で評価する項目が10個ほどある」といったフォーマットの場合、「10個のセルにそれぞれの数値を入力する」のではなく、「10桁の数字を1つのセルに入力して、それぞれの項目をMIDで取り出す」とすると、手を動かす回数が格段に少なくなりますね。
②YYYYMMDD形式から年月日を取り出す
これも同様にLEFTやMID、RIGHTで容易に取り出すことができます。
関数の使い方の説明は不要でしょうが、注意点があります。年月日の入ったセルを見てください。左寄せ、つまり文字列になっています。
実は、LEFTなどの関数で取り出した値は文字列になるのです。
このままでも問題が起きないことはありますが、よくあるトラブルとして以下の２つを覚えておいてください。
文字列トラブル1.計算結果がおかしくなる
年月日を足し算することは無いでしょうが、例としてSUM関数で文字列の2024、12、21をSUM関数で足してみた結果です。
SUMなどの関数は文字列を渡すとその部分を無視して扱うものがあるため、このような結果になるのです。
ちなみに四則演算の+を使えば「数値としてみなせる文字列は数値として扱う」というようになっています。
ただし、SUM関数は「文字列を無視する」ので 範囲内に”田中”や”合計”などのマジの文字列が入っていても数値部分だけ計算してくれますが、+を使った場合は#VALUE!エラーになります。
文字列トラブル2.ありがた迷惑機能の発動
Excelには入力内容に応じて自動的に表示形式を変更してくれる機能があるというのは前述の通りです。
この機能ですが、文字や値の入力時だけでなく、「表示形式の設定されたセルを使った数式」を入力した場合にも発動します。
ついでに小数点以下の桁数が増える
さて、この機能が「表示形式＝文字列」が設定されたセルに対して発動するとどうなるでしょうか。
お察しの通り、数式ではなく「=から始まる文字列」になります。
たいていの場合、1回目に入力した時点では数式として認識してくれるのですが、そのセルを編集してエンターキーを押すと文字列に変化する、という感じになります。
ちなみにもしこうなってしまった場合、表示形式を標準に戻してから再度セルを編集(内容の変更はしなくてもよい)→エンターキーを押すことで直ります。
当然このままでは使い物にならないので、次に説明するVALUE関数を使って対策しましょう。
・VALUE関数｜文字列を数値にする
「数値と見なすことができる文字列」を数値にしてくれる関数です。
LEFT関数などで取ってきた値は文字列になってしまうので、それを数値に戻したい場合などに使用します。
ほかにもCSVなどから貼り付けた「カンマ付き数字っぽい文字列」や「円記号付き数値っぽい文字列」なんかを数値にしたいときにも使用することがあります。
簡単な関数ではありますが、変換の一例を載せておきます。
・TEXT関数｜①「書式を設定して」②「文字列にする」
セルへの設定ではなく、関数を使って書式を表現します。
使いどころとしては「曜日を文字列で表示したい場合」が大半かと思います。
TEXT関数で出力される値は文字列になるので、「曜日条件で集計」といった場合に便利です。
この図では曜日が「シリアル値を表示形式で曜日にしたもの」だったので、曜日での集計ができなかった。
・WEEKDAY関数｜曜日を使って何かやりたいときの選択肢パート２
シリアル値を「1～7」（または「0～6」）に変換してくれる関数です。
第二引数は関数入力時のヒントを見ると [種類] としか書いてなくて分かりづらいですが、入力を進めるとちゃんとしたヒントが表示されます。
こちらの方法で曜日条件の集計をする場合、出力側でも「曜日部分を数値で入れて→表示形式をaaaにする」必要があります。
曜日として使いたい場合、種類は1にすると勝手が良いです。
1900/1/1（シリアル値1）が日曜日のため、他の方法だと曜日書式にしたときにずれが出てしまうためです。
・RANDBETWEEN関数｜ある範囲内のランダムな整数を取得
日付の足し算ができることはもうご存じの通りです。
RANDBETWEENはランダムな整数を返すので、起点となるセルだけ日付を入れておき、以降は「そのセル+RANDBETWEEN(10,20)」などとすればうまい具合に散らばっている（けど昇順ではある）データが作れます。
ちなみに上の表は日付以外の部分でもRANDBETWEEN関数を使用しています。確度や金額部分のように、
・小数点以下がある（パーセンテージなど）
・1000円単位で揃えたい
ような場合、いったん整数で範囲をしたあとに10で割ったり1000を掛けることで目的が達成できます。
取引先部分は未説明の関数を２つ使用していますので、興味があれば調べてみてください。
・MOD関数｜割り算したときの余りを求める
割り算の答えの整数部分のことを商といいますが、余りのことを剰余（じょうよ）といいます。
数学好きな方やプログラミングをかじったことのある方はご存じかもしれませんが、剰余演算というのはとても便利です。
剰余演算（じょうよえんざん）
２つの数値を演算するものは
加算・・・和を求める
減算・・・差を求める
乗算・・・積を求める
除算・・・商を求める
が代表的ですが、ほかに
剰余演算・・・割った余りを求める
という演算があります。
割った余りは 「ゼロ ～割る数-1」の周期でループするため、何かと都合が良いのです。
「3を何かで割る」という演算を考えてみましょう。
何かを0から始めて1ずつ増やしていくと、余りは「0、1、2、0、1、2、・・・」と、0～2でループしますね。
その場合、たとえば7を3で割った剰余を求める式は 「7 % 3 = 1」 となる。
日付関連でよく使う場面としては、「日付+時刻」（2024/4/1 12:25 など）といった形でセルに入っている値から、時刻だけ取り出したいといったものが挙げられます。
シリアル値は「日付は1日で1増えて、時刻は小数で表す」という決まりになっていましたね。なので、上記のケースでは「1で割る」ことで端数部分のみ取り出すことができます。
ここでは詳解はしませんが、MOD関数は条件付き書式でもよく使います。
=MOD(ROW(),2)
を条件にすることで、偶数行のみ色を付けるといったことができるようになります。
引数を省略すると「その式が入ったセルの行番号」となる。
・WORKDAY関数｜休日を考慮して〇日後、〇日前の日付を求める
内容、使い方に関しては図の通りです。
「日付と数値」から「日付」を求めます。
祝日がある場合に使用する「祝日リスト」は、以下のようにセル範囲を指定します。
祝日は法定休日でなくても構いません。お盆や年末年始など、自社にあわせたものを設定してください。
注意点としては、この「祝日」として指定したセル範囲に文字列が入っているとエラーになります。
慣れている人ほどやってしまいがちですが、「列全体」で指定するとこのエラーが出てしまいやすいので気を付けてください。
・NETWORKDAYS関数｜休日を考慮して、ある日とある日の日数差を求める
WORKDAY関数は「日付と数値」から「日付」を求める関数でしたが、こちらは「日付と日付」から「日数（数値）」を求めます。
変わった使い方として、＝NETWORKDAYS(同じ日,同じ日,祝日リスト)とすることで平日判定にも使えます。（平日なら1、祝日なら0になる）
・HOUR関数 MINUTE関数 SECOND関数｜時刻シリアルから「時」「分」「秒」を数値として取り出す
日付関数の「YEAR、MONTH、DAY」の時刻バージョンです。
時給や残業代の計算などがあるため時刻は数値として扱いたい場面が多く、日付関数よりも使用頻度は高いでしょう。
ただ、計算するときは「24を掛ける」方が圧倒的に使います。
・TIME関数｜「時」「分」「秒」からシリアル値を生成
DATE関数の時刻バージョンです。
DATE関数は使う場面が多いのですが、こちらは残念ながらよくあるシチュエーションが思い浮かびませんでした。
・NOW関数｜今の時刻
今の時刻を出してくれます。
TODAY関数と同様、何らかのアクションを行わないと値の更新がされません。昨日開いたファイルをそのまま放っておくと、日が変わっても昨日の時刻のままです。
注意点もTODAY関数と同様で、「時刻入力の自動化」といった用途には使えません。
・CEILING.MATH関数 FLOOR.MATH関数｜10の倍数以外での切り上げ切り捨て
切り上げや切り捨てには通常ROUND系関数を使うことが多いですが、時間の切り上げ切り捨てはこの関数を使います。
３つの引数があって、第一引数の”元となる値”は説明の必要はないでしょう。
第二引数の”切り上げ（捨て）単位”は図のようにセルの値を拾うこともできますし、数式に直接入力しても構いません。
おすすめの方法としては、「単位の設定」といったようなセルを別途作っておき、そのセルを参照することです。
こうすることで、もし変更があったとしても1か所の修正で済むようになりますし、どこを修正すれば良いのかもわかりやすくなります。
将来的に変更の可能性がある部分はなるべく変数にすると楽ができる。
第三引数の[マイナスのときにどうするか]ですが、これは元の値がマイナスの場合に「0側に寄せるか」「絶対値の大きい方に寄せるか」を指定するためのものです。他の関数と同様に数値で指定します。
たとえば+3200を1000単位で切り上げると4000になりますが、-3200という値を1000単位で切り上げる場合、-3000と-4000のどちらにしますか？
これはどちらが正解ということもなく、どちらの解釈や運用方針も考えられるため、選択式となっているのです。
・MAX関数 NIN関数｜深夜や早朝時間の計算※
※深夜や早朝の時間計算に特化した説明です。一般的には
=MAX(最大値を求めたいセル範囲)
=MIN(最小値を求めたいセル範囲)
として、範囲内で最大や最小の値を探すために使用することの多い関数です。
まず「2値の低い(高い)方取り」について説明しましょう。
「2値」と「低い方取り」はどちらも読んで字のごとくで、2値というのは「2つの値」のことで、低い方取りは「いくつかある値の中で、低い方を選択する」という意味です。
よく見かける例としては、駐車場の最大料金なんかがそうなっていますね。
たとえば「1時間300円（最大1800円）」という料金システムなら、「300*利用時間 と 1800 の低い方取り」と言えます。
高い方取りの例は「最低保証ありのインセンティブ式報酬」や「最低利用金額と実際の利用金額」などがあります。
これをExcelで書くとこのようになります。
=MIN(300*利用時間セル , 1800) ※順番は1800が先でもOK
深夜時間を計算するには以下のようにします。早朝時間を求めるような場合も同様です。
・「利用時間に応じた料金を計算」という業務がある場合で最低料金やパック料金がある
・「お客さんが日付をまたいで利用することがあり、曜日や時間によって料金が変わる」
ような場合、これらの関数を多用することになります。
深夜時間・・・「22時と実際の退勤時間の高い方取り」
補足
「22以降以前」をIFで判定して分岐させても全く問題ありません。
ただしその場合、時刻シリアルを”22:00”のような形で直接入力するとIF(A1>="22:00",真の場合,偽の場合)が常にFALSE判定になるという謎の挙動をすることがあるので注意が必要です。
※IF(A1>=("20:00"+"2:00"),真の場合,偽の場合) や、 IF(A1>=VALUE("22:00"),真の場合,偽の場合) だと正常挙動をします。
補足の補足
数値計算ではなく比較演算で”00:00”形式を用いた場合、単に「文字列」と判定されるようです。
「数値を文字列と大小比較した場合、常にFALSEになる」というExcelの仕様があり、そのためFALSEとなってしまうのです。
("20:00"+"2:00")やVALUE関数を挟む例では一度数値計算を挟んでいるので正常挙動するということですね。
ここまででメイン部分は終了です。
以下はちょっとした解説になります。
7.条件付き書式
7-1.条件付き書式について
これはどちらかというと日付ではなく「相対参照」に関する話なので、実際の手順や理屈についての解説はしません。
知っておいてほしいことを２つだけお伝えします。
１．「セルの強調表示ルール」や「上位/下位ルール」では「そのセルの値」しか参照できない
曜日や日付に対応する行や列全体の書式を変えたい場合、「数式を使用して、書式設定するセルを決定」という機能を使う必要があります。
7-2.やりたいことの上手な検索方法
Excelで何かやりたいことがある場合、通常検索ではなく画像検索をすることで目当てのものが探しやすくなることがあります。
検索時点では疑問を言語化できていないことも多く、通常の検索だと『記事タイトルは求めている内容っぽかったけど、読み進めてみたら微妙に違った』ということが起こりやすいためです。
たとえば「Excel 曜日 色」で画像検索してみると、以下のように表示されます。
赤で囲った記事は全ての行に着色がされており、目的の記事である可能性が高そうです。反対に、緑で囲った記事は今回求めているものとは明らかに違いますね。
8.日付を使うときに便利な操作
個人的に「日付系の何かをしているときによく使うなあ」というショートカットやフィルなどを紹介します。
・Ctrl ＋ ； ｜本日の日付を入力
・Ctrl ＋ D ｜上のセルを下にコピー
上のセルと同じ値を入力します。同じ日付をたくさん入れる場合によく使用します。
このショートカットには2種類の挙動があり、
１．単一セルまたは単一行のみ選択している場合
→１つ上のセルの値を、コピーする形で選択セルに入力します。
２．複数行を選択している場合
選択している範囲の最上段の値を、コピーする形で選択セルに入力します。
逆に「書式設定がされていないセルを始点に適当に範囲選択→Ctrl ＋ D」でまとめてセルの掃除ができたりもする。
・Ctrl ＋ R ｜左のセルを右にコピー
挙動についてはCtrl + Dと同様です。
DownのD、RightのRと覚えましょう
・Alt → H → O → I ｜列幅の自動調整
選択しているセルの幅を自動で調整します。「セル幅部分でダブルクリック」と同じ効果です。
複数セル選択している場合、最も幅の大きいセルに対して調整がなされます。
Ctrl系のような「＋」表記は同時押し、Alt系のような「→」表記は左のキーを押してから次のキーです。
またAlt系のショートカットはAltを押すと画面上に対応キーが表示されるので、頻繁に行う操作であれば自分で調べて覚えるようにするとよいでしょう。
・Shift + スペースキー ｜半角スペースを入力
日本語入力状態でも半角スペースを打つことができます。
いちいち入力方式を切り替えなくて済むので大変便利です。
・Ctrl + スペースキー ｜行全体を選択
「列見出しをクリックしたとき」と同じ動作です。
まとめて日付書式にするときなどに使用します。
・Ctrlを押しながらフィル ｜デフォルトとは逆の動作
通常、フィルは以下のルールで動作します。
・単一日付の場合→１ずつ増える
・単一数値の場合→同じ数値
（・複数セル選択時はうまいこと等差数列っぽくフィル）
Ctrlを押しながらフィルをすると、これとは逆の動作をします。
・Ctrl ＋ Enter ｜複数セルへ一括入力
そもそも同じ内容を大量に入力したい場合、範囲選択→なにか入力した後Ctrl + Enterで一括入力ができます。
・Ctrl ＋ ３ ｜yyyy/m/d形式にする
セル上でCtrl ＋ ３とすることで、yyyy/m/d形式にすることができます。
他にも書式を変更するショートカットがあり、よく使うものとしては
Ctrl + 1 ・・・「#,##0」
Ctrt + 5 ・・・「0%」
Ctrl + ^ ・・・「標準」
があります。
・アプリケーションキー → R ｜書式のみ貼り付け
・アプリケーションキー → V ｜数値のみ貼り付け
コピー後、「書式だけ」「数値だけ」貼り付けたい場合に使用します。
「出先のノートPCでアプリケーションキーが無い」といった場合、Shift ＋ F10 で代用可能です。
以上、お疲れ様でした。
ここまで読まれた方なら、「やりたいことを細分化→簡単な関数や数式で実現」ができるようになっているはずです。
Excel作業は本業ではありません。無駄に時間を食うExcel作業はなるべく減らし、本業の時間に充てることで楽して成果を上げましょう。
この記事が気に入ったらサポートをしてみませんか？