Chiquilin Site■07.11.01_Excel:期間内の指定曜日の数

その1:開始日が何曜日かで決まる
その2:開始日を前週の指定曜日の翌日に合わせる
その3:期間内の特定曜日の数を求める
ChiquilinSite

その1:開始日が何曜日かで決まる。

期間内の特定曜日の数
こういう表です。開始日から終了日までに特定の曜日がいくつあるのかを計算します。単純に考えれば「(終了日−開始日)÷7」で求まりそうなものですが 開始日が何曜日かによって若干日数がずれます。例えば2007年11月の場合です。1日が木曜日で30日が金曜日ですから 第5週まであるのは木曜日と金曜日だけということになります。最終日が何曜日かは開始日からの日数に従いますから 結果的には開始日の曜日が全てを決定するといっていいでしょう。
ページの一番上へ

その2:開始日を前週の指定曜日の翌日に合わせる

第n指定曜日を求める」でも書きましたが曜日を数値化するのには WEEKDAY関数を使います。第二引数は月曜を基準として「0〜6」が返る「3」にします。何故「3」にするのか とお思いでしょうか。それは「開始日を基準に計算しない」からです。上の項で「開始日が全て」と書いておきながらどういうことかと云われそうですが それには理由があります。今回の場合「開始日が何曜日か」が決まっていません。「計算したい曜日」と「開始日の曜日」との差を一定に調整してからでないと単純に「7で割る」という計算式にできないのです。
例えば「月曜日の数」を求めようと思ったら 開始日は火曜日の時が 一番計算が楽です。「開始日から5日(6日間)は月曜日がない」ことがはっきりするからです。つまり6日後(期間にして7日)にようやく月曜日がきます。ということは単純に期間を7で割って端数を切るだけで答えが出ます。
指定曜日の次の曜日が開始日だと計算しやすい
6日後(7日間)に初めて1週間と計算するように計算してやれば 何曜日でも同じように計算できるようになります。指定曜日が火曜日だとするなら「開始日の前の週の水曜日」を求めればいいのです。ただWEEKDAY関数の第二引数を「3」にした場合 月曜日が始点となりますので 指定曜日とのずれを別途調整してやる必要はあります。
指定曜日が日曜日の場合 → 調整値は「0」
指定曜日が月曜日の場合 → 調整値は「1」
指定曜日が火曜日の場合 → 調整値は「2」
指定曜日が水曜日の場合 → 調整値は「3」
指定曜日が木曜日の場合 → 調整値は「4」
指定曜日が金曜日の場合 → 調整値は「5」
指定曜日が土曜日の場合 → 調整値は「6」
要は 何日前が日曜日かを考えればいいです。
ページの一番上へ

その3:期間内の特定曜日の数を求める 

最後に順を追って計算していきましょう。
■開始日から終了日までの期間
 =終了日-開始日+1

■開始日の前の週の指定曜日翌日
 =開始日-WEEKDAY(開始日-調整値,3)
合わせて 期間内の指定曜日の数を計算します。
■期間内の特定曜日の数
 =INT((終了日-(開始日-WEEKDAY(開始日-調整値,3))+1)/7)
 
括弧が余計なので省くと
 =INT((終了日-開始日+WEEKDAY(開始日-調整値,3)+1)/7)
 
初日を含めない場合は
 =INT((終了日-開始日+WEEKDAY(開始日-調整値+1,3))/7)
因みにこの数式を応用すると「土日を除いた日数」も求められます。
土日を除いた日数
 =終了日-開始日-SUM(INT((終了日-開始日+WEEKDAY(開始日+{0,1},3)+1)/7))+1
曜日を選択式にして対応させる方法は「第n指定曜日を求める」の方に書きましたので こちらでは書きません。調整値ですが 曜日に対応させて計算させてもいいんですが 表の外に背景と同色で入力しておくか「月曜日」「火曜日」と書かれているところに直接数値を入れて表示形式をユーザー定義で「"月曜日"」「"火曜日"」などとしておくだけでもいいと思います。
→参考ファイル(035.xls)
ページの一番上へ
ChiquilinSite トップページへ Copyright(C)2005 Chiquilin_site. All Rights Reserved.