[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤務計算で休日出勤の抽出の仕方』（まりお）
タイムカードをパソコンで管理しています。
Ａ Ｂ Ｃ Ｄ Ｅ Ｆ
日付 出勤 退勤 小計 残業 深夜
3月21日(Mon) 8：00 18：00 7.0 1.0
3月22日(Tue) 8:27 17:02 7.0
3月23日(Wed) 8:17 15:03 5.0
こんな感じの表で
休日にあたるセルは赤文字にしています。
その赤文字で入力されたＤの小計だけを計算したいのと
その日数をカウントしたいのですがどうすればいいのでしょうか？
ヘルプやサイトで調べたつもりなんですが、
見つけることが出来ませんでしたので、
よろしくお願いします。
WindowsXP Excel2000です。
日付は表示形式で日付のユーザー定義で曜日をつけるように設定しました。
出勤時間・退勤時間は普通に8：00と入力しています。
小計・残業・深夜には計算式が入っています。
赤色にするのはすべてのセルで表示形式のフォントで赤色を選択して赤にしています。
他に日曜日と指定して、赤にする方法とかあるのでしょうか？
ユーザー定義では、m"月"d"日"(ddd)と設定し、fxﾊﾞｰには2005/3/21と表示されセルには3月21日(Mon)と表示されているのですが・・・
他の方法なのでしょうか？
条件付書式で「数式が」「=TEXT(A1,"aaa")="日"」とすれば日曜日は赤く出来ます。 祝日の休日とかはありますか？ [[20031123213620]] 『祝祭日、振替休日の曜日を赤く表示するには』(taka) この辺を参考にして下さい。 （ケン）
日曜日だけが休日ということなら、 休日の合計(小計欄の縦計)は、=SUMPRODUCT((WEEKDAY(A2:A13)=1)*(D2:D13)) 休日の件数は、 =SUMPRODUCT((WEEKDAY(A2:A13)=1)*(D2:D13>1)) で、できそうですが、祝日とかほかの休日が混ざっている場合は、 作業列に休日のフラグをたてて、それを条件に計算することになります。 (sato)
数式が”にして＝WEEKDAY(A3)=1で書式設定
A32までフィルドラック、G2=IF(B1="","",IF(C1="","",IF(WEEKDAY(A1)=1,C1-B1,"")))を入れてフィルドラック、G34に合計を出すとして、
=IF(COUNTIF(G1:G30,"@")="","",SUM(G1:G30))
G35に日数を出すとして、=COUNTA(A1:A31)-COUNTIF(G1:G31,"")
こんなんでどうでしょ？
もしかしたら間違っているかも（笑）
（TK)
G列に休日の場合のみ 1 と入力することとして、
休日の合計(小計欄の縦計)は、=SUMPRODUCT((G2:G13=1)*(D2:D13))
または =SUMIF(G2:G14,1,D2:D14)
休日の件数は、 =SUMPRODUCT((G2:G13=1)*(D2:D13>1)) とすれば求められます。
ちなみに条件付書式は、「数式が」、$G2=1 として書式を設定すれば、 いいでしょう。 (sato)
次に（TK)さんへ、一番最初に日付を数値にすると関数で日曜日を取り出して計算
することは可能です。と書いていましたが、この関数はどのような方法なのでしょうか？
あと(sato)さんへ、休日が2種類あるので先ほど教えていただいた関数ではまだ足りないと思いましたので、まだやってみていません。ごめんなさい。
いろいろな方のいろいろな回答でちょっと初心者の私としてはとまどっています。
申し訳ありません。m(__)m
条件付書式で赤文字にしているものだけ合計する。関数は見つけたのですが、
うまく反映せずに0と出てしまいます。
どこがおかしいのか教えてください。
条件付書式で「数式が」「=TEXT($A1,"aaa")="日"」と入れています。
休日日数には=COUNTIF(B5:B35,"=日")
休日時間には=SUMIF(D5:D35,"=日",D5:D35)
と関数を入れています。
よろしくお願いします。
よくよくこの関数の乗っているところを詳しく見ると、
フォントで赤にしているものではなかったのですが、
私の場合は条件付書式でフォントで赤にしているものです。
この場合、集計はできないのでしょうか？
誰か助けてください・・・（まりお）
たくさんの回答をいただいて少し混乱してるのですね。 もう少しお待ち下さいね、どなたかがきっとまとめてくれますよ。 （jun53）
jun53さんにそういわれると恥ずかしくて書くのをためらうのですが、 少し、問題を整理してみましょう。
フォントが赤いものだけを集計することは、できません(少なくとも標準の関数では)。 もし、そのフォントが条件付書式で設定されたものであれば、 その条件を元に集計することは可能です。
しかし、今回の場合は、赤い文字は日曜だけではなくて、 祝日、不定期の土曜日などが含まれるようですので、 このまま集計するのは難しそうです。(絶対にできないとは言えませんが)
そこで、私の提案は、G列(またはどこでもいいんですけど)に、 休日にしたい日すべてに 1 (これも他のどんな数字でも文字でもいいんですけど)を入力して、 それを条件にして集計をしたり、条件付書式でフォントの色を変えたりしたらどうかということなんです。 （あくまでひとつの案なんですけど) お分かりいただけますでしょうか？ (sato)
もう少し、まりおさんの数式を見てみましょう。
>休日日数には=COUNTIF(B5:B35,"=日") これは、B5:B35の値が"日"となっているセルの数を数える、という意味になります。 実際にはB5:B35には出勤時間が入っていますので、"日"というセルはひとつもありませんね。 したがって、その答えは、ゼロになります。
>休日時間には=SUMIF(D5:D35,"=日",D5:D35) これは、D5:D35の値が"日"であったなら、D5:D35の値を合計しなさい、という意味にんなります。 先ほどの場合と同様、D5:D35には"日"というセルはありません。 それに仮に"日"というセルがあっても、それは数値ではないので計算できません。 したがって、この答えもゼロになってしまいます。
最初はややこしいかもしれませんが、少しずつ覚えていけば大丈夫ですから、 ゆっくり前進するようにがっばってください。 このサイトの先輩たちは皆さん親切ですから、 わからないことはいろいろ教えてもらえると思いますよ。 (sato)
返事が遅くなり申し訳ございません・・・
今週は土曜日が休みの日で、私は会社でこのエクセルの計算を
勉強していたもので・・・
ところで、satoさんが”フォントが条件付書式で設定されたものであれば、
その条件を元に集計することは可能です。”と書かれていましたが、
とりあえず、今は日曜日だけを条件付書式で赤に表示させているので、
その計算方法というのを教えていただけませんか？
関数で赤色だけを抜くことって出来るのでしょうか？
関数は複雑でつまづいてしまうと、そこから頭が混乱して
何もわからなくなってしまいます。なさけない・・・・
とりあえず、satoさんの説明で自分が入れている関数の何が
間違っていたのかは把握できました。ありがとうございます。
今後もよろしくお願いいたします。
（まりお）
A B C D E F 1 出勤 退勤 小計 残業 深夜 2 4月1日(Fri) 8:00 17:00 7 3 4月2日(Sat) 8:10 18:00 7.5 1.0 4 4月3日(Sun) 8:15 17:30 7 ←日曜日 5 4月4日(Mon) 9:00 17:50 6.5 0.5 6 4月5日(Tue) 8:10 16:30 6 7 4月6日(Wed) 8:25 19:00 8.5 2.0 8 4月7日(Thu) 8:15 17:50 7 0.5 9 4月8日(Fri) 8:05 16:30 6 10 4月9日(Sat) 8:10 17:30 7 11 4月10日(Sun) 9:15 18:30 7 1.5 ←日曜日 12 4月11日(Mon) 8:15 16:30 6 13 4月12日(Tue) 8:05 17:45 7 0.5 14 15 16 日曜日の 時間 14 1.5 17 回数 2 1
仮に、上記のような表を作りました。 小計や残業の計算基準がわからないので適当に入れてあります。
この場合、
D16=SUMPRODUCT((WEEKDAY(A2:A13)=1)*(D2:D13))
D17=SUMPRODUCT((WEEKDAY(A2:A13)=1)*(D2:D13>0))
~~訂正しました
E16=SUMPRODUCT((WEEKDAY(A2:A13)=1)*(E2:E13))
E17==SUMPRODUCT((WEEKDAY(A2:A13)=1)*(E2:E13>0))
~~訂正しました
で求められます。
また、条件付書式設定は、A2からF13を選択した状態で、 「数式が」で、=WEEKDAY($A2)=1 と入力して適当な書式を設定すれば、 日曜日の行に赤色などが表示されます。 (sato)
satoさん、ありがとうございますm(__)m。
日曜日の休日出勤日数と時間が表示されるようになりました。
この関数は、頭がごちゃごちゃなって、整理をする前にも
書いていてくれましたよね・・・先ほど一から読み返していて
あることに気がつきました。何度も同じ関数を書いていただいて
ありがとうございます。私の理解力のなさに申し訳なさでいっぱいになります・・・・
あとは不定休なのですが、表の別枠に今月の休日3/21,4/2,4/9,4/16と記載して、
この日付をヒットさせ、集計する方法はあるのでしょうか？
A B C D E F
1 出勤 退勤 小計 残業 深夜 2 4月1日(Fri) 8:00 17:00 7 3 4月2日(Sat) 8:10 18:00 7.5 1.0 4 4月3日(Sun) 8:15 17:30 7 ←日曜日 5 4月4日(Mon) 9:00 17:50 6.5 0.5 6 4月5日(Tue) 8:10 16:30 6 7 4月6日(Wed) 8:25 19:00 8.5 2.0 8 4月7日(Thu) 8:15 17:50 7 0.5 9 4月8日(Fri) 8:05 16:30 6 10 4月9日(Sat) 8:10 17:30 7 11 4月10日(Sun) 9:15 18:30 7 1.5 ←日曜日 12 4月11日(Mon) 8:15 16:30 6 13 4月12日(Tue) 8:05 17:45 7 0.5 14 15 16 日曜日の 時間 14 1.5 17 回数 2 1
今月の休日 3/21 4/2 4/9 4/16
さきほどの式に、日曜日以外の休日(不定期)を追加したいということですが、 この場合は、G列に作業列を設けるのが一番わかりやすそうです。
A B C D E F G 1 出勤 退勤 小計 残業 深夜 休日 2 4月1日(Fri) 8:00 17:00 7 3 4月2日(Sat) 8:10 18:00 7.5 1.0 1 4 4月3日(Sun) 8:15 17:30 7 1 5 4月4日(Mon) 9:00 17:50 6.5 0.5 6 4月5日(Tue) 8:10 16:30 6 7 4月6日(Wed) 8:25 19:00 8.5 2.0 8 4月7日(Thu) 8:15 17:50 7 0.5 9 4月8日(Fri) 8:05 16:30 6 10 4月9日(Sat) 8:10 17:30 7 1 11 4月10日(Sun) 9:15 18:30 7 1.5 1 12 4月11日(Mon) 8:15 16:30 6 13 4月12日(Tue) 8:05 17:45 7 0.5 14 15 16 日曜日の 時間 28.5 2.5 17 回数 4 2
G列に休日という行をもうけて、休日の場合はすべて1を入力します。(日曜日も)
この場合、 D16=SUMPRODUCT((G2:G13=1)*(D2:D13)) D17=SUMPRODUCT((G2:G13=1)*(D2:D13>0))
E16=SUMPRODUCT((G2:G13=1)*(E2:E13)) E17=SUMPRODUCT((G2:G13=1)*(E2:E13>0)) となります。先ほどの式との違いを確認してみてください。 (先ほどの式に一部誤りがありましたので訂正しておきました。大変失礼しました)
また、条件付書式設定は、 「数式が」で、=$G2=1 とすればいいと思います。 (sato)
satoさんへ
長い間、こんな私につきあっていただいて、ありがとうございました。
すべて解決することが出来ました。本当にありがとうございました。
感謝！感謝！です。
ここのサイトの方はみんな親切でていねいに教えていただけるので、すごくわかりやすかったです。また、わからないことが出てきたら、質問させていただきたいと思いますので、その時はよろしくお願いいたします。m(__)m
（まりお）
とほほ・・・すみません・・・
またまた問題がでてきてしまいました・・・。
前回教えていただいた、休日の関数で完璧と思っていたのですが、
リンクで別の表に移すときに、発覚しました。
別表の中で日曜日・日曜日以外の休日・平日の日数と時間を別々に表示するように指示されていました。
日曜日の日数には =SUMPRODUCT((G5:G35=2)*(D5:D35>0))
日曜日の時間には =SUMPRODUCT((G5:G35=2)*(D5:D35))
日曜日以外の日数には=SUMPRODUCT((G5:G35=1)*(D5:D35>0))
日曜日以外の時間には=SUMPRODUCT((G5:G35=1)*(D5:D35))
と教えていただいたように入力し、計算もばっちりされています。
が、平日の日数と時間を
平日の日数=SUMPRODUCT((G5:G35<>1),(G5:G35<>2))*(D5:D35>0)
平日の時間=SUMPRODUCT((G5:G35<>1),(G5:G35<>2)*(D5:D35))
と入れているのですが、＃VALUEや0になってきちんと計算してくれません。
G列が1のみや、2のみの場合は計算式はなりたっているのですが、
1と2両方を除きたいときはどのようにすればいいのでしょうか？
,は（ ）と（ ）ということではないんですか？
ifも使ってみたのですが、関数をあまり理解できていない私にとってはやはり難しいのです・・・・
また、助けてください・・・・
（まりお）
G5:G35に日曜は2、日曜以外の休日に1と入れて、平日には何も入っていないとすれば、 日曜・・・・G5:G35=2 日曜以外・・G5:G35=1 としたら、 平日は・・・G5:G35="" でできますね。 (sato)
satoさん、本当に何度も何度もありがとうございました。
一生懸命がんばって調べているつもりでも、見ているだけなのか、
頭で理解できないほど力がないのか、ほとほと困り果ててしまいます。
でも、こんどこそ完璧です。
ほんとうにありがとうございました。m(__)m
（まりお）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.