[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『二つの日付から木曜日と日祝日を除いた出勤日数を求める』（こうた）
初めて質問させて頂きます。例えば2月21日〜締日3月20日までの勤務日数を 求めたいのですが定休日が木曜日と日曜祝日なのでこれを除外して日数を求めたいのですが 宜しくお願いします WindowsVistaでExcel2007です
どこかに祝日リストを作成するものとします。(下例では、D2:D25 に作成)
(1) 祝日リストの右に、木曜日を除いた祝日リストを計算させます。 E2セル =(WEEKDAY(D2)<>5)*D2 下にフィルコピー
(2) 出勤日数を計算します。 C1セル =IF(COUNT(A1:B1)<2,"",NETWORKDAYS(A1,B1,$E$2:$E$25)-INT((B1-A1+(MOD(WEEKDAY(A1)+1,7)+1))/7))
行 ___A___ ___B___ _C_ ____D_____ __ E ___ 1 2月21日 3月20日 15 祝日リスト (除木曜) 2 2009/1/12 39825 3 2009/2/11 39855
(半平太) 2009/01/29 14:54
定休日が木曜日と日曜祝日 土曜日は勤務日という事で ななしのかかし
ご指摘ありがとうございます。 （気づきませんでした。週3日も休めていいなぁと思っていました。）
(半平太) 2009/01/29 16:26
そうすると、木曜日の日数を引き、土曜日の数を足す、と云うことで、以下でどうでしょうか？
C1セル =IF(COUNT(A1:B1)<2,"",NETWORKDAYS(A1,B1,$E$2:$E$25)-INT((B1-A1+(MOD(WEEKDAY(A1)+1,7)+1))/7)+INT((B1-A1+WEEKDAY(A1))/7))
(半平太) 2009/01/29 17:17
↑ を踏まえまして、、、、
１．考え方 (1)勤務日数の算出は、祝日リストを作ってから、Networkdays関数で出すのが普通です。 しかし、この関数は土日祝を除くものなので、今回のケースに合わせて補正が必要になります。
(2)補正の「当初案」は以下でした。 (a)関数で除かれた土曜の日数を加える。 (b)関数で勤務日とされている木曜の日数を減ずる。 ただし、休日リストに木曜があると、それは関数の方で既に差し引いているので 引き過ぎになるため、休日リストから木曜日のデータは除いて置く必要がある。 それがE列の式です。Networkdays関数の休日リストはE列の方を参照することにします。 初めからD列のリストを作る時、木曜除きにすれば、この作業は不要です。でも、曜日を気にしながら 休日リストを作って行くのって、神経使いませんか？(わたし的には、気を使う必要がないやり方を好みます)
(3)再考案 今回、改めて補正の方法を考えてみますと、、 木曜日と土曜日の数は、ほとんど同じなので、実際の日数を数える必要がないことに気づきました。
つまり、以下のケースだけ1日の差が出るので、それを補正すればいいはずです。
(a)スタートが「日〜木」で、エンドが「木〜金」なら、木曜が1日多い(補正はマイナス１) (b)スタートが「金〜土」で、エンドが「土〜水」なら、土曜が1日多い(補正はプラス１)
※ 曜日の判定にはWeekday関数を使います。(返り値は、日曜〜土曜が、1〜7になります) この関数を使いやすくするために、上の補正を以下のように再構成します。
(a)スタートが「日〜木」で、『エンド+１日』が『金〜土』なら、木曜が1日多い(補正はマイナス１) (b)スタートが「金〜土」で、『エンド+１日』が『日〜木』なら、土曜が1日多い(補正はプラス１) ２．具体的な数式
(1) 木曜の祝日は除いた祝日リストの作成 E2セル =(WEEKDAY(D2)<>5)*D2 フィルダウン
※ セルの表示形式は、日付/標準 いずれでも構いません。 前者なら「2009/1/12」、後者なら「39825」と云った表示になります。 後者が「0」の時、前者が「1900/1/0」と表示されることに抵抗を感じるかも知れません。(実害はないのですが。)
(2) C1セル =IF(COUNT(A1:B1)<2,"",NETWORKDAYS(A1,B1,$E$2:$E$25)-(WEEKDAY(A1)<6)*(5<WEEKDAY(B1+1))+(5<WEEKDAY(A1))*(WEEKDAY(B1+1)<6))
~~~~~~~~~~~~~~↑~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~↑~~~~~~~~~~~~~~~~~
補正a 補正b
行 _____A_____ _____B_____ _C_ ________D_________ ____ E _____ 1 2月21日(土) 3月20日(金) 19 祝日リスト(手作成) (同左除木曜) (表示形式が標準の場合) 2 2009/1/12 2009/1/12 39825 3 2009/2/11 2009/2/11 39855 4 2009/3/20 2009/3/20 39892
(参考) 祝日リストが古くなると問題なので、その時は警告を出す様にした式
C1セル =IF(MAX($D$2:$D$25)<B1,"祝日リストをUPDATE要!",NETWORKDAYS(A1,B1,$E$2:$E$25) -(WEEKDAY(A1)<6)*(5<WEEKDAY(B1+1))+(5<WEEKDAY(A1))*(WEEKDAY(B1+1)<6))
(半平太) 2009/01/30 10:02
ロジックに穴がありました。土曜日が祝日の場合、その日は勤務日にカウントされてしまいまーす。 m(__)m
その日数を引く「補正c」を施すと、こんなんなっちゃいました！ ↓ C1セル =IF(COUNT(A1:B1)<2,"",NETWORKDAYS(A1,B1,$E$2:$E$25)-(WEEKDAY(A1)<6)*(5<WEEKDAY(B1+1))+(5<WEEKDAY(A1))*(WEEKDAY(B1+1)<6)) -SUMPRODUCT((WEEKDAY(A1-1+COLUMN(A1:INDEX(1:1,B1-A1+1)))=7)*COUNTIF($D$2:$D$25,A1-1+COLUMN(A1:INDEX(1:1,B1-A1+1)))) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~↑~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 補正c これはちょっと説明する気も起きません。発想の転換が必要です。 私の出る幕ではなかったです。退散します。orz
識者からのレスをお待ち下さい。
(半平太) 2009/01/30 13:06
どうも、半平太さんに申し訳ないので
長くなりますが作ってみました。
[A] [B] [C] [D] [E]
[1] 最初 終わり 日数 祝日リスト
[2] 2月21日 3月20日 19 1月1日
[3] 1月12日
[4] 2月11日
[5] 3月20日
↑その年の祝日を
入力しておいてください。
C2セルに
=SUMPRODUCT((WEEKDAY(A2-1+ROW(INDIRECT("A1:A"&B2-A2+1)))<>1)*(WEEKDAY(A2-1+ROW(INDIRECT("A1:A"&B2-A2+1)))<>5))-
SUMPRODUCT((E$2:E$20>=A2)*(E$2:E$20<=B2)*(WEEKDAY(E$2:E$20)<>1)*(WEEKDAY(E$2:E$20)<>5))
の式を入れます。（２行で一つの式です。）
この式は、 １行目で、A2〜B2の間の日木以外の日数を求め ２行目で、E2:E20に入力された範囲内の日付のうち、 日木以外の日数を引き算しています。
まずは上と同じ配置の表を作成し、C2セルに数式をコピペしてもらった後 2009/1/1〜2009/3/31までの色々な日付をA2:B2セルに入力し C2セルの日数がご希望と合うか確認してみてください。
(HANA)
=SUMPRODUCT(((A2-1+ROW(1:50))<=B2)*(MOD((A2-1+ROW(1:50)),7)<>5)*
(MOD((A2-1+ROW(1:50)),7)<>1)*NOT(COUNTIF(E2:E7,(A2-1+ROW(1:50)))))
んなとこかな
識者ではありませんが とこかな
極力短い数式でてことで
祝日リストの隣にこんな物を書いておいて =IF(OR(WEEKDAY(E2)=1,WEEKDAY(E2)=5),0,1)
SUMPRODUCで該当期間割り出し合計を引けば・・・。 BJ
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.