Excel2010 土日祝に出勤した日数を計算

質問

教えてください。

今、月毎の勤務表があります。A列には別セルの年月を参照して、日(曜日)が１日～３１日(日数は月毎に変わります。)B列以降には勤務した場合に〇を入力します。ただし勤務の場所が４か所あるので、勤務場所ごとに一人で４列使用します。それが８人用用意されています。表にすると次のようです。
同じ勤務地にはダブって勤務することはないので、1(金)の例ではA氏がB地に勤務すると、B氏他はそれ以外の勤務地になります。教えてほしいのは、このような勤務状態の土日祝の勤務日数を、勤務者ごとに知りたいのです。尚、祝日は別シートに一覧として記載しております。

下表は位置合わせのスペースは無視されて詰まってしまうので―で位置合わせしているもので質問に対する意味はありません。

－－－－－｜－－－－－A氏－－－－－－｜－－－－－B氏－－－－－｜ ～
－－－－－－－a地－－b地－－c地－－d地－－a地－－b地－－c地－－d地
列→－－ A－－B－－－C－－－D－－－E－－－F－－－G－－－H－－－I～
－－－1(金)－－－－－〇－－－－－－－－－－〇
－－－2(土)－－〇－－－－－－－－－－－－－－－－ 〇
－－－3(日)－－－－－－－－－〇－－－－－－－－－－－－－－－－－－〇
－祝日4(月)－－－－－－－－－－－－－〇－－－－－－－－－－〇
－－－5(火)－－－－－〇－－－－－－－－－－－－－－－－－－〇
－－－以下省略

以上よろしくお願いします。

回答No.5

kagakusuki · ベストアンサー

今仮に、

>A列には別セルの年月を参照して、

という箇所に記述されている「別のセル」がA1セルであり、A1セルの書式設定の表示形式が、

yyyy"年"m"月"

となっていて、そこに例えば

2012/10/1

と入力しますと、

2012年10月

と表示されるものとします。
　又、A3以下には

1(月)

とか、

祝日8(月)

といった形式で、日付と曜日、及び「祝日か否かの区別」を表示するものとします。
　又、祝日のリストがAL列に入力されているものとします。
　又、B1～AG1に、「A氏」～「H氏」といった各勤務者の氏名が、3列おきに並んでいるものとします。
　又、AI2以下に「A氏」～「H氏」といった各勤務者の氏名が並んでいて、その右隣のAJ2以下に各勤務者の休日出勤の日数が表示されるものとします。るものとします。

　まず、A1セルの書式設定の表示形式を[ユーザー定義]の

yyyy"年"m"月"

として下さい。
　次に、A3セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(MONTH(TEXT($A$1,"yyyy/m/d")+ROWS($A$3:$A3)-1)=MONTH(TEXT($A$1,"yyyy/m/d")+0))),IF(COUNTIF($AL:$AL,(TEXT($A$1,"yyyy/m")&"/"&ROWS($A$3:$A3))+0),"祝日","")&TEXT((TEXT($A$1,"yyyy/m")&"/"&ROWS($A$3:$A3))+0,"d(aaa)"),"")

　次に、A3セルをコピーして、A4～A33の範囲に貼り付けて下さい。
　次に、AI2セルに次の関数を入力して下さい。

=INDEX($1:$1,COLUMN($A$1)+1+(ROWS($AI$2:$AI2)-1)*4)&""

　次に、AJ2セルに次の関数を入力して下さい。

=IF($AI2="","",SUMPRODUCT(COUNTIFS(OFFSET($A:$A,,MATCH($AI2,$1:$1,0)-COLUMN($A$1)+INT((ROW(INDIRECT("A1:A"&4*3))-1)/3)),"〇",$A:$A,LOOKUP(MOD(ROW(INDIRECT("A1:A"&4*3))-1,3)+1,{1,2,3;"*(日)","*(土)","祝日*"}),$A:$A,"<>"&LOOKUP(MOD(ROW(INDIRECT("A1:A"&4*3))-1,3)+1,{1,2,3;"祝日*","祝日*",""}))*1))

　次に、AI2～AJ2の範囲をコピーして、同じ列の３行目以下に貼り付けて下さい。

　これで、各勤務者の休日出勤日数が自動的に表示されます。
　因みに、AJ2セルに入力する関数の中の「4*3」という箇所は「a、b、c、dの４地点」×「土、日、祝日という3種類の休日」を表しています。

お礼

回答ありがとうございます。図まで作成して戴きましてお手数おかけしました。まさにこの通りで私の表を見られた気分です。
早速確認しましたがA氏の４列の休日(土日祝)マーク(〇)しかカウントしません。B氏～H氏までの休日は〇を入力してもカウントしません。
実際には列番号などは違うのですがKagakusukiさんの図の通り再現して確認しました。何か違う所がるのでしょうか。何回も済みませんがよろしくお願いします。

補足

以下にB氏～H氏はできない旨記載しましたその後できました。ありがとうございました。

回答No.4

K Kazz（@JazzCorp）

＜＜訂正＞＞データの先頭を３行目に訂正
話を簡単にするためにデータは１人分として、作業列をＧとＨにしてある。実際はシート８人のようなので作業列の位置は調整必要。
その日が休日扱いになることをマークするためと、勤務があったことをマークするために２つの作業列を使う。（勤務のマークは人数分の列が必要）
休日扱いとなるものを調べる。休日は、土日とそれ以外での祝日(振替え休日)、土日はすべて休日扱い。
祝日は、祝日を列挙した以下のHolidayシートをVLOOKUP関数で照合する。
Holidayシートはもっと大きくても構わない。例えば複数年に対応するものでも良いが、検索範囲は調整必要。
Ｇ列：休日マーク（"H"）
=IF(WEEKDAY($A3,2)>5,"H",IF(ISNA(VLOOKUP($A3,Holiday!$A$1:$A$19,1,FALSE)),"","H"))
その日に勤務したかは、勤務地のマーク（例えばＢ～Ｅ列）を文字列合成）したものが、空白か○かで判断できる。（間違って複数のマークがあった場合、エラーとすることも可能）
Ｈ列：各人の勤務マーク（"○"）
=LEFT(B3&C3&D3&E3,1)
結局、休日扱いの日に、勤務のマークがある数を数えることになる。
=SUMPRODUCT(($G3:$G33="H")*($H3:$H33="○"))
＜＜８人分は面倒だが、まとめて下方向にコピー可能＞＞

Holidayシート：
Ａ列に祝日（振替え休日になる場合はそちら）を列挙、（土）と重なるものがあっても問題ない。
1月2日(月)
1月9日(月)
2月11日(土)
3月20日(火)
4月30日(月)
5月3日(木)
5月4日(金)
5月5日(土)
7月16日(月)
9月17日(月)
9月22日(土)
10月8日(月)
11月3日(土)
11月23日(金)
12月24日(月)

休日 出勤
5 ←10月の休日扱い勤務日数（各人）
10月1日(月) ○ ○
10月2日(火) ○ ○
10月3日(水) ○ ○
10月4日(木) ○ ○
10月5日(金) ○ ○
10月6日(土) ○ ○ H ○
10月7日(日) H
10月8日(月) ○ ○ H ○
10月9日(火) ○ ○
＜＜以下、省略＞＞

回答No.3

KURUMITO

複雑な式を使うこともなく分かり易い、作業列を設けて対応する方法です。
シート2のA列には祝日が入力されているとします。
シート1では次の作業を行います。
A1セルには例えば2012/10/1と入力してセルの表示形式ではユーザー定義で　yyyy年m月　として2012年10月のように表示させるとします。
A3セルには次の式を入力して下方にドラッグコピーします。

=IF(MONTH(A$1+ROW(A1)-1)<>MONTH(A$1),"",A$1+ROW(A1)-1)

セルの表示形式はユーザー定義で　d日(aaa) と入力して1日（月)　のように表示させます。
氏名の8人をB1セル、F1セル、J1セル・・・・とAD1セルまで入力します。
勤務場所はそれぞれの氏名ごとにa,b,c,dと2行目に入力します。3行目から下方には勤務した場合には○を入力するとします。
そこで作業列ですがAH3セルには次の式を入力して下方にAH33セルまでドラッグコピーします。

=IF(A3="","",IF(OR(WEEKDAY(A3,2)>=6,COUNTIF(Sheet2!A:A,A3)>0),IF(COUNTIF(B3:E3,"○")>0,"A","")&IF(COUNTIF(F3:I3,"○")>0,"B","")&IF(COUNTIF(J3:M3,"○")>0,"C","")&IF(COUNTIF(N3:Q3,"○")>0,"D","")&IF(COUNTIF(R3:U3,"○")>0,"E","")&IF(COUNTIF(V3:Y3,"○")>0,"F","")&IF(COUNTIF(Z3:AC3,"○")>0,"G","")&IF(COUNTIF(AD3:AG3,"○")>0,"H",""),""))

お求めの表をA３９セルからB47のセル範囲に作るとしてA３９セルには氏名の項目名を、B３９セルには休日勤務日数と入力します。
A40セルから下方には氏名を入力します。
B40セルには次の式を入力して下方にドラッグコピーします。

=IF(A40="","",COUNTIF(AH:AH,"*"&A40&"*"))

回答No.2

K Kazz（@JazzCorp）

その日が休日扱いになることをマークするためと、勤務があったことをマークするために作業列を使う。
休日は、日付毎に土日とそれ以外での祝日(振替え休日)か調べる。
土日はすべて休日扱いにマーク（"H"）
=WEEKDAY($A2,2)>5
祝日はVLOOKUP関数でできる。（Holidayシートに祝日を列挙する）
その他はヒットした場合だけ休日扱いにマーク（"H"）
=VLOOKUP($A2,Holiday!$A$1:$B$19,2,FALSE)
その日に勤務したかは、勤務地のマーク（例えばＢ～Ｅ列）を文字列合成（=LEFT(B2&C2&D2&E2,1)）したものが、空白か○かで判断できる。
結局、休日扱いの日に、勤務のマークがある数を数えることになる。
Ｇ列：休日扱いマーク（"H"）
Ｈ列：勤務マーク（"○"）
=SUMPRODUCT(($G2:$G32="H")*($H2:$H32="○"))

回答No.1

keithin

添付図：
簡単のためＡ列に年/月/日で日付を記入（必要に応じて表示形式で日のみ表示にしておいてよい）
Ｂ列に曜日列を準備
Ｂ３：
=IF(A3="","",IF(COUNTIF(祝日一覧,A3),"祝",TEXT(A3,"aaa")))
以下コピー

Ａ氏の土日祝出勤数：
=SUMPRODUCT((($B$3:$B$33="土")+($B$3:$B$33="日")+($B$3:$B$33="祝"))*(C3:F33<>""))

＃参考
曜日列をどうしても設けたくない場合：
=SUM(IF($A$3:$A$33<>"",((WEEKDAY($A$3:$A$33,3)>4)+COUNTIF(祝日一覧,$A$3:$A$33))*(C3:F33<>"")))
と記入し、コントロールキーとシフトキーを押しながらEnterで入力する
ただし３１日が無い月の３１日のセルには""（空白）を計算して置く事。