本稿は、著者自身がかつて運営していたウェブサイト『エクセルの達人（エク達）』に掲載された記事を転載するものです。ただし、記事冒頭で、現時点で当ウェブサイトへの転載が終わっていない記事を引用している記述があったため、転載にあたっては記事を一部修正しています。
| エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
日付をさまざまな書式に変換する
当ウェブサイトでは以前、『入力した日付が「数字の値」になってしまう場合の対処』で、「日付を入力したら数字に化けてしまう」という問題について取り上げた。本稿は、その「続編」である。
仕事をしていると、どうしても「日付」から「平日」「土曜日」「日曜日」だけを抜き出して処理を変えたい、というニーズが出て来ることもある。
その際、「セルの書式設定」または「TEXT関数」を使えば、日付のシリアル値を「曜日」などに変換することができる。詳しい書式は次の図表１を参照していただきたい。
図表１ セルの書式（日付、シリアル値44432の場合）
| フォーマット | 表示 | 備考 |
|---|---|---|
| y | 21 | 西暦下２桁 |
| yy | 21 | 西暦下２桁 |
| yyy | 2021 | 西暦４桁 |
| yyyy | 2021 | 西暦４桁 |
| g | R | 元号のアルファベット |
| gg | 令 | 元号の漢字１字 |
| ggg | 令和 | 元号の漢字２字表示 |
| e | 3 | 元号の数値 |
| ee | 03 | 元号の数値２桁表示 |
| m | 8 | 月の数値 |
| mm | 08 | 月の数値２桁表示 |
| mmm | Aug | 月の英語名称３桁表示 |
| mmmm | August | 月の英語名称 |
| mmmmm | A | 月の英語名称１桁表示 |
| d | 24 | 日の数値 |
| dd | 24 | 日の数値２桁表示 |
| ddd | Tue | 曜日の英語名称３桁 |
| dddd | Tuesday | 曜日の英語名称 |
| aaa | 火 | 曜日の漢字１字 |
| aaaa | 火曜日 | 曜日の漢字表示 |
日付を曜日に変換しようと思うなら、 “ddd” “aaa” などを指定して頂ければよい（具体的な方法は過去記事『日付データからさまざまな情報を抽出するTEXT関数』あたりもご参照頂きたい）。
「土日だけを抽出」、はて…？
たとえば、日付セルをいったん「TEXT関数」で曜日形式に変換し（図表２→図表３）、そのうえで「IF関数」を使い、「もしこのセルが “日” だった場合には…」という演算をする（図表４）、というやり方が考えられる（※IF関数の入力結果については省略）。
図表２ A列の日付をB列で曜日に変換
図表３ 変換結果
図表４ IF関数の入力（※計算結果については省略）
ただ、このやり方だと、あくまでも「セルの表示上、日付のシリアル値を曜日に変える」（セルの書式設定の場合）、または「日付のシリアル値を曜日に変える」という方法にならざるを得ない。
実務上、結構不便だ。
MOD関数で余りを求める
そこで、本稿では「MOD関数」を使って、一発で土日などを峻別する方法を紹介したい。書式は次のとおりだ。
MOD(数値,除数)
これは、「ある数値を除数で割った場合の余り」を返す関数だ。
「１週間は何日あるか」。
答えは７日だ。
従って、このMOD関数の数値に日付（図表２の例でいえばA列）を指定し、除数に「7」を入力してやれば、曜日を数値で示すことができる（図表５、図表６）。
図表５ MOD関数の入力
図表６ 計算結果
シリアル値を７で割っている
このメカニズムは、こうだ。
以前も『日付から曜日を抽出する：「地味に役立つ」テクニック』などで述べたとおり、エクセルでは日付をシリアル値（通し番号）で管理している。1900年１月１日を「１」と起算して、それ以降、9999年12月31日までのすべての日付を番号で指定しているのだ。
ということは、日付にMOD関数を適用すると、（日付そのものではなく）その「シリアル値」を「除数」で割った余りを返す、という計算をしてくれるのだ。
結論からいえば、次のとおり。
- 土曜日…０
- 日曜日…１
- 月曜日…２
- 火曜日…３
- 水曜日…４
- 木曜日…５
- 金曜日…６
土曜日は７で割り切れるので余りはゼロ、それ以外の曜日はそれぞれ余りが１つずつ増えて行く、というわけだ。
そして、「日付のシリアル値を７で割って余りがゼロになる日が土曜日、１になる日が日曜日」と覚えておけば、たとえばマーケットデータを引用する際に、こんな使い方ができる。
「もしも日付を７で割った余りが０か１だった場合は、そのセルの値は無視する」。
マーケット関係者にとっては大変役に立つ関数なので、是非とも「７で割った余り」を使って、簡単に土日を把握することができるという点を覚えておいても損はないだろう。
| エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
View Comments (3)
なるほどweekday関数より短くて簡単ですね。かつ週以外にも応用が効く。mod関数、便利ですね。
A列:日付、B列:金額として、C列で指定曜日の金額のみ表示する場合の式
月曜日～土曜日まで
=AND(MOD(A1,7)-1)*B1
月曜日～金曜日まで
=AND(INT(MOD(A1,7)/2))*B1
土曜日・日曜日のみ
=NOT(INT(MOD(A1,7)/2))*B1
少し記事の主題と外れますが…
SUMIF、SUMIFS等で指定曜日の金額を集計する場合、未整備なローデータ（外部データ取込時の日付の文字列型の混在、金額の「円」他全角スペース混入など）だと、各曜日毎計とデータ合計のチェックを怠ると集計漏れを生じます。
上の場合、AB列いずれのエラーでC列でSUMをとればエラーを吐きます。IF判定でも差支えありませんが、書き方によりエラーを見過ごす羽目になるので論理式が好みです。
ピボットテーブル使え、という話でもありますが、数万件のデータ明細ならフィルタ掛けて個々にエラーを潰せるほうがマシということでもあります。
局面に応じ適切な使い分けができると良いと思います。
新宿会計士さまの
エクセルTIPSご紹介の記事については
はじめてコメントするのですが
じつはとても有意義なものと
感じていました。
私も当時は大都市の書店にまで
技術評論社の本を買いに行ったくちですが
それがこうしてネットで知ることができるのは
もっと多くの方が活用されるべきなのにと思います。
とかくITスキルを向上したいと思っても
ユーキャンとかの役に立たない資格ビジネスや
猿でもわかるシリーズ（？）とかいう書籍等の氾濫で
おかしな方向に持ってかれてしまって
しまってます。
どうして猿でもわかる程度のことを
私達人類が金払って時間を無駄にする
必要があるのでしょうか（笑）
今の表計算は多機能で
ほしい機能が関数で用意されていて
簡単に使えるようにはなった反面
思わぬ落とし穴にはまるかもと
感じています。
その点、一見とっつきにくいけど
しっかり仕組みを理解して
関数を組み立てたほうが
あとでの検証でむしろ好いとも
今でも感じています。
とかく
VBAで組んでしまいがちなエクセルですが
むしろ、きっちり理解した関数で固めたほうが
事後の検証と改修の際にもよいのにと
私は感じています。