MOD関数を使って「曜日」を特定する超絶テクニック

本稿は、著者自身がかつて運営していたウェブサイト『エクセルの達人(エク達)』に掲載された記事を転載するものです。ただし、記事冒頭で、現時点で当ウェブサイトへの転載が終わっていない記事を引用している記述があったため、転載にあたっては記事を一部修正しています。

エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

日付をさまざまな書式に変換する

当ウェブサイトでは以前、『入力した日付が「数字の値」になってしまう場合の対処』で、「日付を入力したら数字に化けてしまう」という問題について取り上げた。本稿は、その「続編」である。

仕事をしていると、どうしても「日付」から「平日」「土曜日」「日曜日」だけを抜き出して処理を変えたい、というニーズが出て来ることもある。

その際、「セルの書式設定」または「TEXT関数」を使えば、日付のシリアル値を「曜日」などに変換することができる。詳しい書式は次の図表1を参照していただきたい。

図表1 セルの書式(日付、シリアル値44432の場合)
フォーマット表示備考
y21西暦下2桁
yy21西暦下2桁
yyy2021西暦4桁
yyyy2021西暦4桁
gR元号のアルファベット
gg元号の漢字1字
ggg令和元号の漢字2字表示
e3元号の数値
ee03元号の数値2桁表示
m8月の数値
mm08月の数値2桁表示
mmmAug月の英語名称3桁表示
mmmmAugust月の英語名称
mmmmmA月の英語名称1桁表示
d24日の数値
dd24日の数値2桁表示
dddTue曜日の英語名称3桁
ddddTuesday曜日の英語名称
aaa曜日の漢字1字
aaaa火曜日曜日の漢字表示

日付を曜日に変換しようと思うなら、 “ddd” “aaa” などを指定して頂ければよい(具体的な方法は過去記事『日付データからさまざまな情報を抽出するTEXT関数』あたりもご参照頂きたい)。

「土日だけを抽出」、はて…?

たとえば、日付セルをいったん「TEXT関数」で曜日形式に変換し(図表2図表3)、そのうえで「IF関数」を使い、「もしこのセルが “日” だった場合には…」という演算をする(図表4)、というやり方が考えられる(※IF関数の入力結果については省略)。

図表2 A列の日付をB列で曜日に変換

図表3 変換結果

図表4 IF関数の入力(※計算結果については省略)

ただ、このやり方だと、あくまでも「セルの表示上、日付のシリアル値を曜日に変える」(セルの書式設定の場合)、または「日付のシリアル値を曜日に変える」という方法にならざるを得ない。

実務上、結構不便だ。

MOD関数で余りを求める

そこで、本稿では「MOD関数」を使って、一発で土日などを峻別する方法を紹介したい。書式は次のとおりだ。

MOD(数値,除数)

これは、「ある数値を除数で割った場合の余り」を返す関数だ。

「1週間は何日あるか」。

答えは7日だ。

従って、このMOD関数の数値に日付(図表2の例でいえばA列)を指定し、除数に「7」を入力してやれば、曜日を数値で示すことができる(図表5図表6)。

図表5 MOD関数の入力

図表6 計算結果

シリアル値を7で割っている

このメカニズムは、こうだ。

以前も『日付から曜日を抽出する:「地味に役立つ」テクニック』などで述べたとおり、エクセルでは日付をシリアル値(通し番号)で管理している。1900年1月1日を「1」と起算して、それ以降、9999年12月31日までのすべての日付を番号で指定しているのだ。

ということは、日付にMOD関数を適用すると、(日付そのものではなく)その「シリアル値」を「除数」で割った余りを返す、という計算をしてくれるのだ。

結論からいえば、次のとおり。

  • 土曜日…0
  • 日曜日…1
  • 月曜日…2
  • 火曜日…3
  • 水曜日…4
  • 木曜日…5
  • 金曜日…6

土曜日は7で割り切れるので余りはゼロ、それ以外の曜日はそれぞれ余りが1つずつ増えて行く、というわけだ。

そして、「日付のシリアル値を7で割って余りがゼロになる日が土曜日、1になる日が日曜日」と覚えておけば、たとえばマーケットデータを引用する際に、こんな使い方ができる。

もしも日付を7で割った余りが0か1だった場合は、そのセルの値は無視する」。

マーケット関係者にとっては大変役に立つ関数なので、是非とも「7で割った余り」を使って、簡単に土日を把握することができるという点を覚えておいても損はないだろう。

エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

読者コメント一覧

  1. 匿名 より:

    なるほどweekday関数より短くて簡単ですね。かつ週以外にも応用が効く。mod関数、便利ですね。

  2. へちまはたわしのみに非ず より:

    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判定でも差支えありませんが、書き方によりエラーを見過ごす羽目になるので論理式が好みです。
    ピボットテーブル使え、という話でもありますが、数万件のデータ明細ならフィルタ掛けて個々にエラーを潰せるほうがマシということでもあります。

    局面に応じ適切な使い分けができると良いと思います。

  3. 世相マンボウ 。 より:

    新宿会計士さまの
    エクセルTIPSご紹介の記事については
    はじめてコメントするのですが
    じつはとても有意義なものと
    感じていました。
    私も当時は大都市の書店にまで
    技術評論社の本を買いに行ったくちですが
    それがこうしてネットで知ることができるのは
    もっと多くの方が活用されるべきなのにと思います。

    とかくITスキルを向上したいと思っても
    ユーキャンとかの役に立たない資格ビジネスや
    猿でもわかるシリーズ(?)とかいう書籍等の氾濫で
    おかしな方向に持ってかれてしまって
    しまってます。
    どうして猿でもわかる程度のことを
    私達人類が金払って時間を無駄にする
    必要があるのでしょうか(笑)

    今の表計算は多機能で
    ほしい機能が関数で用意されていて
    簡単に使えるようにはなった反面
    思わぬ落とし穴にはまるかもと
    感じています。
    その点、一見とっつきにくいけど
    しっかり仕組みを理解して
    関数を組み立てたほうが
    あとでの検証でむしろ好いとも
    今でも感じています。
    とかく
    VBAで組んでしまいがちなエクセルですが
    むしろ、きっちり理解した関数で固めたほうが
    事後の検証と改修の際にもよいのにと
    私は感じています。

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。