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

昨日の『タテヨコ変換の基本形①「行列入れ替え+値貼り付け」』では、実務上極めて頻繁に出てくる「タテヨコ変換」の基本形のうち「行列を入れ替えてえの値貼り付け」を紹介した。ところが、これだといちいち手作業が介在してしまう。本稿では「タテヨコ変換」をさらに簡単にやる「極意」を紹介したい。

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

以前の『日付から曜日を抽出する:「地味に役立つ」テクニック』や『日付をさまざまな値に変換する「TEXT関数」の威力』では、エクセルにおける日付の特徴とその加工方法に関するさまざまな条件、テクニックなどを紹介した。

(なお、もっと基本的な、「日付を入力したら数字に化けてしまう」という問題については、『入力した日付が「数字の値」になってしまう場合の対処』あたりをご参照いただきたい)。

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

その際、「セルの書式設定」または「TEXT関数」を使えば、日付のシリアル値を「曜日」などに変換することができるという点は、以前紹介したとおりだ。詳しい書式は次の図表1を思い出してほしい。

図表1 セルの書式(日付、シリアル値44432の場合)
フォーマット 表示 備考
y 21 西暦下2桁
yy 21 西暦下2桁
yyy 2021 西暦4桁
yyyy 2021 西暦4桁
g R 元号のアルファベット
gg 元号の漢字1字
ggg 令和 元号の漢字2字表示
e 3 元号の数値
ee 03 元号の数値2桁表示
m 8 月の数値
mm 08 月の数値2桁表示
mmm Aug 月の英語名称3桁表示
mmmm August 月の英語名称
mmmmm A 月の英語名称1桁表示
d 24 日の数値
dd 24 日の数値2桁表示
ddd Tue 曜日の英語名称3桁
dddd Tuesday 曜日の英語名称
aaa 曜日の漢字1字
aaaa 火曜日 曜日の漢字表示

日付を曜日に変換しようと思うなら、 “ddd” “aaa” などを指定して頂ければよい(具体的な方法は上記過去記事をご参照頂きたい)。

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

たとえば、日付セルをいったん「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で割った余り」を使って、簡単に土日を把握することができるという点を覚えておいても損はないだろう。