エクセル 出勤しなければならない日数

質問

いつもお世話になっています。

現在、勤怠表の月別集計表を作っています。
出勤しなければならない日の計算がうまくいきません。

C2・・・2012/4/1
D2・・・2012/5/1
E2・・・2012/6/1
・
・
・
C2から右へ月の一日の日を記載し、書式設定で月のみ表示させています。

会社の休みは日曜・祝日と指定された日です。
別シートに祝日の一覧と指定された休みの一覧を作り、

祝日→「祝日」
指定休み→「休日」　と名前（名前の定義）を付けました。

C4には4月の、D4には5月の・・・・
出勤しなければならない日数を表示させたいです。

自分なりに色々試してみたのですが、
上手くいきません。

宜しくお願い致します。

OS：win7
office：2010

回答No.4

MackyNo1 · ベストアンサー

C4セルに以下の式を入力して右方向にオートフィルコピーしてください。

=SUMPRODUCT((MONTH(C2+ROW($A$1:$A$31)-1)=MONTH(C2))*(WEEKDAY(C2+ROW($A$1:$A$31)-1)<>1)*(COUNTIF(祝日,C2+ROW($A$1:$A$31)-1)=0)*(COUNTIF(休日,C2+ROW($A$1:$A$31)-1)=0))

お礼

ご回答ありがとうございました。
たしか、別件でもBAさせて頂きましたよね？
毎回、すごいなぁとただただ尊敬の念を抱いています。

今回も思い通りの事ができました！

詳しい式の内容については、
使っている関数自体がそれほど難しくなさそうなので、
どのように使っているかなど、
自分なりに解読していきたいと思います。

大変助かりました。
ありがとうございました。

また何かありましたら宜しくお願いします！

回答No.3

tom04

こんばんは！

NETWORKDAYS関数とEOMONTH関数の併用ではどうでしょうか？

※　祝日と休日は別々に名前定義されているというコトですが、
休日も祝日の表内に追加しておき、それをまとめて「祝日」と名前定義します。

ただ、NETWORKDAYS関数は土日・祝日が除外されてしまいますので、
一案として↓の画像のように別Sheetに曜日を表示させておきます。
画像ではSheet2に作成してみました。
Sheet2の1行目はSheet1の3行目をそのままコピー＆ペーストしておきます。
（4以降の数値はその月の1日のシリアル値としています。）

Sheet2のA2セルに
=IF(MONTH(A$1+ROW(A1)-1)=MONTH(A$1),TEXT(A$1+ROW(A1)-1,"aaa"),"")
という数式を入れ32行目（31日分）までオートフィルで下へコピー！そのまま列方向にオートフィルでコピー！
これで各月の曜日がすべて表示されます。

最後にSheet1のC4セルに
=NETWORKDAYS(C$2,EOMONTH(C$2,0),祝日)+COUNTIF(Sheet2!A:A,"土")
という数式を入れ列方向にオートフィルでコピーすると
何とかご希望に近い形にならないでしょうか？m(_ _)m

お礼

ご丁寧に、画像までつけて頂き、
とてもわかりやすいご説明、ありがとうございました。

色々とやり方があるのだなぁと勉強になりました。

実際に使用するのが私ではなく、
あまりパソコン自体を得意としない者が主となり使うファイルなのと、

勤怠簿だけでもとても重たいファイルとなってきている為、

出来るだけシートは少なく、関数も必要最低限でと考えています。

その為、今回は別の方のやり方を使用させて頂きますが、
大変参考になりました。

お手間かけて頂きありがとうございました。
また何かありましたら、宜しくお願い致します。

回答No.2

KURUMITO

NETWORKDAY関数は土曜日も休日として扱っています。
例えばお求めの表がシート1に有るとして、シート2には祝日や指定休みなどが入力されていることでしょう。そこでそのシートをシート1のデータを取得するために必要な表を作成することにします。
例えばシート2のＡ1セルからＡ29セルまでに祝日が、Ｂ1セルからＢ29セルまでに指定休みの日にちを入力することとして、30行以降の行には作業用の表を作成することとします。
Ａ30セルには作業する年を例えば2012と入力します。
Ａ31セルには次の式を入力してＡＥ31セルまでドラッグコピーしたのちに下方にもドラッグコピーします。例えば54行まで。
これで出勤するするべき日付には1が入力されますので、複雑な関数を使ってシート1のデータを求めるよりも、実際のデータを直視できる点で優れていると思います。

=IF(MOD(ROW(A1),2)=1,IF(DATE($A$30,ROUNDUP(ROW(A1)/2,0)+3,COLUMN(A1))>DATE($A$30,ROUNDUP(ROW(A1)/2,0)+4,0),"",DATE($A$30,ROUNDUP(ROW(A1)/2,0)+3,COLUMN(A1))),IF(MOD(ROW(A1),2)=0,IF(A30="","",IF(OR(WEEKDAY(A30)=1,COUNTIF($A$1:$A$29,A30)>0,COUNTIF($B$1:$B$30,A30)),"",1))))

そこでシート1のＣ4セルには次の式を入力して右横方向にドラッグコピーします。行の表示形式は標準にします。

=IF(C$2="","",SUM(INDEX(Sheet2!$A$31:$A$78,MATCH(C$2,Sheet2!$A$31:$A$78,0)+1):INDEX(Sheet2!$AE$31:$AE$78,MATCH(C$2,Sheet2!$A$31:$A$78,0)+1)))

お礼

ご回答ありがとうございます。

なるほどな、と感心致しました。

すでにファイルの容量が心配になり、
個人別の勤怠簿と月間の集計を別ファイルとすることを検討している位で、

なるべく作業列での関数は控えたいと考えています。

その為、今回は別の方の方法でやらせていただきましたが、
大変参考になりました。

また何かありましたら宜しくお願い致します。
貴重なお時間を割いていただいてありがとうございました。

回答No.1

aokii

祝日と休日をまとめて一つにして、「休み」という名前（名前の定義）を付けた場合、

C4セルは、
=NETWORKDAYS(C2,EOMONTH(C2,0),休み)

C4セルを右へドラッグコピーでいかがでしょう。

補足

早速のご回答ありがとうございます。

日曜と指定休日の定義は、
出来れば別々にしておきたいと考えています。
理由としては、別のところでもこの定義を使っている箇所があり、

日曜は法定休日、指定休日は通常休日として、
別の休日の扱いになっている為、分けています。

どうしても方法がないようでしたら、
一緒にしたものを別口で定義付けしようと思っていますが、
結構複雑なファイルになってきている為、
できるだけシンプルに作成していきたいと思っています。

教えて頂いた数式をもとに、
自分でも試行錯誤してみようと思いますので、

もしお時間がございましたら、
引き続き宜しくお願い致します。