Excel2010 休日出勤を関数式で表示するには

質問

休日に出勤した日数を下記の図のごとく計算を考えています。

(1)には別セルの年月を参照して日にちを表示しています。
(2)には別シートにある休日表を参照して、休日を表示しています。
(3)は休日に出勤した担当者名です。
(4)は休日に出勤した日数をカウントして表示させるエリアです。今、数値が入っていますが、これを関数式で表示させたいと思って、トライしていますがうまくいきません。

どなたか教えてください。

回答No.7

tom04 · ベストアンサー

何度もお邪魔します。

エラーの件でかなり悩まれていらっしゃるようなので・・・

エラーの原因はWEEKDAY関数のシリアル値の取得部分でエラーになります。
すなわちWEEKDAY関数の範囲内すべてが数値（シリアル値）でないとエラーとなります。
その解消のために前回のような方法を提案しました。

仮に見た目は「空白」でも数式によって「空白」表示されている場合はExcel的には「空白」とは判断してくれません。
Deleteキーで完全に数式まで消した場合は「空白」＝0と判断してくれますので、
エラー表示は解消されます。
しかし、月が替わるたびに数式を入れたり・消したりは大変だと思い、
とりあえずWEEKDAY関数の「土日曜」とは全く関係のない平日（月曜日）の「2」を「空白」の代わりに
表示させた数式です。

尚、前回のセルの表示形式も手を加えたくない場合は
条件付き書式で　セル値が「2」に等しい　→　書式　→　フォントの色を「白」としてもかまいません。

余談ですが、小の月の月末セルは
「0」→Excel的には1900/1/0　→　土曜
「1」→　1900/1/1（日曜）
の数値であっても29行目以降が空白だと思いますので、
何らかの数値さえ入っていれば休日・祝日出勤とはカウントされません。

※　土日と祝日が重複する月もあるはずですので、前回の数式のように
土日の出勤数＋平日（月～金）で祝日の出勤数　を出す計算式としています。
単に土日曜＋祝日　としてしまうと重複してカウントされる月が出てきます。

以上、長々と失礼しました。m(_ _)m

お礼

ありがとうございました。今後もよろしくお願いします。

回答No.8

ko_futaba

NO．４,５,６です

NO．７さんのおっしゃる通り
　土日＋祭日では重なった場合二重にカウントされます。

　説明が不足していましたが、
　祭日一覧から「土日」の「祭日」を除いた「祭日一覧表」
　を作って使用しておりました。
　それを使えばご希望の計算は出来ます。

　祭日一覧表はハッピーマンデー（振替休日）の関係で
　毎年作りますので、その際「土日」と重複した「祭日」を除いて
　作られると１年間は使えます。
　２５年度で４日程重なっています。

　日付について
　日付は３１日の欄まで式を入れておきます。
　大の月小の月については条件付き書式で
　翌月分は非表示にします。

　条件付き書式は
　＝MONTH(日付）<>別セルの月　設定で
　フォントの色を白にします。

　従って１日～３１日蘭まで日付データは必ず
　設定されていますので、空白欄は有りません。

　11月（小の月）では日付欄「$D$26：$AH$26」の
　[AH26]欄は空白かも知れませんね。
　空白欄が有ればエラーの原因かもしれません。

　従って大の月、小の月で数式を変えることも有りません。
　毎月１つの数式で計算されます。

　後は「祭日一覧表」（土日重複を除く）を年に１回更新
　するだけです。

お礼

ありがとうございました。今後もよろしくお願いします。

補足

回答ありがとうございます。返事が遅れまして申し訳ありません。
図はAGで終わっていますが、表自体は大の月もあるので、AHまであります。11月は小の月なので、AGで切り取り貼り付けたので、空白列はないとお考えになられたと思います。大変申し訳ありません。その空白行が災いしているのか、AHまで式に含むとエラーになりますが、AGまでだとＯＫです。
また、土日と祝日の重なり考え付かなかったですが、ご指摘ありがとうございます。

回答No.6

ko_futaba

NO.4,5です。

１．日付欄は”別セルの年月を参照して日にちを表示”
　とありますので各セルには「年月日」のデータが有り「日」のみを表示
　しているものとしています。

２．別シートで”休日表を参照して”と有りますので
　祭日の「年月日」の一覧表が名前を定義してあるものと
　しています。

３．ご質問の空白欄（ヌル）は計算には影響されません。

手元の Excel シートでは正常に計算されますので
再度上記をご確認ください。

お礼

ざいました。今後もよろしくお願いします。

回答No.5

ko_futaba

NO.4です

式の記述が違っています。
＞=SUMPRODUCT(((WEEKDAY(D29:AH29,2)>=6)+COUNTIF(祝日,D29:AH29))*($D29:$AH29<>""))
としていますが、

WEEKDAY　関数の引数は　「$D$26:$AH&26」の日付です。
　２６行目で固定します。

COUNTIF　関数も同じ　「$D$26:$AH&26」の日付です。　
　２６行目で固定します。

調べる該当欄は　「$D29:$AH29」となります。　
　２９行目で下にコピー出来る様に複合参照です。
　２６行　と　２９行が違っています。

お礼

ざいました。今後もよろしくお願いします。

補足

セル番地が違っておりました。しかし、次のように入力しても#VALUE!エラーとなります。

=SUMPRODUCT(((WEEKDAY($D$26:$AH$26,2)>=6)+COUNTIF(祝日,$D$26:$AH$26))*($D29:$AH29<>""))

($D29:$AH29<>"")ヌルではないとありますが、図のように飛び飛びにヌルの所があるのですが、関係ありますか。よろしくお願いします。

回答No.4

ko_futaba

初めて回答します。
同じような計算（個人々の土日祭の勤務日数計算→給与計算）をしていました。

名前「日程」（$D$26:$AH$26）
名前「祝日」（祝日表）を定義しておきます。
式が読み易くなると思います。

=SUMPRODUCT(((WEEKDAY(日程,2)>=6)+COUNTIF(祝日,日程))*($D29$AH29<>""))

WEEKDAY(日程,2)>=6 で土日
COUNTIF(祝日,日程)　　で祝日の

$D29$AH29<>""　　で該当欄（$D29$AH29）の空白でないセル数を数えています。

以下、下にコピーします。

OKWaveで随分教えて頂きましたので少しでもお役に立てればと思います。
この式もOKWaveをヒントに考えたものです。
シルバー世代（６８歳）ですが頑張っています。

お礼

ありがとうございました。今後もよろしくお願いします。

補足

回答ありがとうございます。私も教えてもらうばかりではなく、人に教える様になりたいものです。

=SUMPRODUCT(((WEEKDAY(D29:AH29,2)>=6)+COUNTIF(祝日,D29:AH29))*($D29:$AH29<>""))

でトライしましたが、#VALUE!エラーが出ます。教えてください。

回答No.3

tom04

続けておじゃまします。

月ごとに数式を変えたくないというコトなので・・・

苦肉の策です。
SUMPRODUCT関数で数式が入った空白セルを範囲に含んだ場合はエラーになるようなので、
小の月の月末を空白にするのではなく、数値にしてセルの表示形式の操作でフォント色を「白」にする方法です。

※　シリアル値で0は土曜日・1は日曜日となりますので、「2」を表示させ、フォント色を「白」にする設定とします。
そこで表の式に手を加えさせていただきます。

D26セルの表示形式をユーザー定義から
[白][=2]G/標準;d
としておき
=IF(MONTH(DATE($D$1+1988,$F$1,COLUMN(A1)))=$F$1,DATE($D$1+1988,$F$1,COLUMN(A1)),2)
という数式を入力
D27セル（セルの表示形式はユーザー定義から　aaa　としておきます）は
=IF(D26=2,"",D26)
という数式を入れD26・D27セルを範囲指定　→　D27セルのフィルハンドルで月末のAH列までオートフィルでコピー！

D28（28行目）の数式はそのままでOKです。

これで前回の数式がそのまま利用できると思います。

C29セルに
=SUMPRODUCT((WEEKDAY($D$26:$AH$26,2)>=6)*(D29:AH29<>""))+SUMPRODUCT((WEEKDAY($D$26:$AH$26,2)<6)*($D$28:$AH$28<>"")*(D29:AH29<>""))
という数式を入れオートフィルで下へコピーしてみてください。

おそらく大の月・小の月も対応できるはずです。
※　結局いま入っているIF関数の「空白」の場合は「2」（2以外のシリアル値が土日以外でもよい）
を表示させるだけです。

お役に立ちますかね？m(_ _)m

お礼

ざいました。今後もよろしくお願いします。

補足

回答ありがとうございます。返事が遅れまして申し訳ありません。
まだ、トライしていませんが、エラーが出ないなら利用させていただきます。これからトライします。

回答No.2

tom04

No.1です！

前回の数式で間違いがありました。
列の範囲指定に空白列が入っているとエラーになってしまいますので、
お示しの画像の場合（小の月）は
数式内のAH部分をAGに変更してください。

大の月だけAHに変更する必要があります。
（2月はその年に適宜合わせてみてください）

検証せずに投稿してごめんなさいね。m(_ _)m

お礼

ありがとうございました。今後もよろしくお願いします。

補足

回答ありがとうございます。
カレンダーについては別セルを参照して大の月、小の月を自動判別して表示しております。
プログラムの知識のないオペレーターが月毎に式を「いじる」のは好ましくないのでエラーのない式ができればと思います。

回答No.1

tom04

こんばんは！
画像では30日のAG列までしかありませんが、実際は大の月の場合31日のAH列までになると思いますので、
AH列までの範囲としてみました。

小の月の場合月末列は空白にとなる前提です。

C29セルに
=SUMPRODUCT((WEEKDAY($D$26:$AH$26,2)>=6)*(D29:AH29<>""))+SUMPRODUCT((WEEKDAY($D$26:$AH$26,2)<6)*($D$28:$AH$28<>"")*(D29:AH29<>""))

という数式を入れオートフィルで下へコピーではどうでしょうか？m(_ _)m

お礼

ありがとうございました。今後もよろしくお願いします。

補足

早速の回答ありがとうございます。
１１月のカレンダーなので３１日が抜けておりました。大の月は３１日まで表示します。
さて、回答を当てはめてみましたが#VALUE!エラーが表示されうまくいきません。よろしくお願いします。