エクセルで勤務表を作っています

質問

エクセルで、○年○月と入力すればその月の日数、曜日が出る勤務表を作成しました。
（例えば、9月と打てば30日まで表示され、10月と打てば31日まで表示される）
DATE関数を使っています。

残業時間を合計する欄があるのですが、
例えば9月は30日までですが、30日の次の空白欄に8月31日のデータが残ってしまっているようで、
合計に反映されてしまいます。

31日のデータを非表示にするだけでなく、合計の対象外にする方法はあるでしょうか。

分かりにくくてすみません。

回答No.7

chonami · ベストアンサー

> 空白にする条件設定もできるのでしょうか。

No.2です。

>上のセルと月が違ったら空白にする、というような内容の式を作るといいかもしれませんね。

この部分は日付の数式自体の事をさしているので条件付き書式の条件の事ではありません。
ややこしかったですかね。
日付自体を空欄にしておけば、残業時間などを算出するセルに、日付が空欄ならば空欄、そうでなければ計算する、というような式をいれておけば別の月の時間が計算されることはないと思います。

お礼

なるほど！
日付の数式自体を「もし前月と違ったら空白」と設定しておけばいいんですね。
それから残業時間のセルにもIF関数を使って。。。

これなら自分に理解できる数式でなんとかなりそうです。
ありがとうございました！

回答No.6

koko88okok

No.3です。

> 日付欄に、「入力月と違う月の場合は文字色を白に」という設定をしています。
> 文字の色を白くしているだけなのでデータが残ってしまっているようです。

> 空白にする条件設定もできるのでしょうか。

日付は兎も角、不要なデータを削除する「条件設定」はないと思います。
従って、シートをコピーして増やす場合は、不要なデータを人が判断して削除するか、データが入力されていないシートをコピーするなど、ファイルの運用を改める必要があるように思います。

「シートをコピーするには」
http://www.wanichan.com/pc/excel/2010/1/page08.html

お礼

ご回答ありがとうございます。
空白にする条件設定はないのですね。

履歴を残す必要がないファイルなので、皆さんコピーされず上書きで入力されているようです。
一応、使用方法には「まず全て削除してから」と書いているのですが、
勤務表の作成の使用方法なんてあまり皆さん目を通しませんよね(^-^;

合計欄の数式を変えることで対応できるか、試してみたいと思います。

ありがとうございました！

回答No.5

kagakusuki

>勤務表の1日目の欄に

>2日以降は「=前日+1」しています。

とありますが、「勤務表の1日目の欄」や「勤務表の1日目の欄」は何というセル番号のセルなのでしょうか？
　その点が不明なため、情報不足で実際に動作する関数を組む事は出来ませんが、以下の様な形式の関数として下さい。

【方法その1】
　まず、29日以降（29日目も含む）の日付欄に入力する関数を

=IF(MONTH(前日+1)=$C$1,前日+1,"")

として下さい。
　その上で、合計欄に入力する関数を月の様なものとして下さい。

=SUMIF(「勤務表の日付欄の1日目のセル」:「勤務表の日付欄の31日目のセル」,">0",「勤務表の1日目の残業時間欄のセル」:「勤務表の31日目の残業時間欄のセル」)

【方法その2】
　或いは、日付欄の関数を修正しなくとも、次の様な関数とする事で、指定した月の残業時間のみを合計する事が出来ます。

=SUMIF(「勤務表の日付欄の1日目のセル」:「勤務表の日付欄の31日目のセル」,"<"&,DATE($A$1,$C$1+1,1)
,「勤務表の1日目の残業時間欄のセル」:「勤務表の31日目の残業時間欄のセル」)-SUMIF(「勤務表の日付欄の1日目のセル」:「勤務表の日付欄の31日目のセル」,"<"&,「勤務表の日付欄の1日目のセル」
,「勤務表の1日目の残業時間欄のセル」:「勤務表の31日目の残業時間欄のセル」)

【方法その3】
　もし、「勤務表の日付欄に指定した月よりも前の日付が存在していない事」が保障されている場合には、

=SUMIF(「勤務表の日付欄の1日目のセル」:「勤務表の日付欄の31日目のセル」,"<"&,DATE($A$1,$C$1+1,1)
,「勤務表の1日目の残業時間欄のセル」:「勤務表の31日目の残業時間欄のセル」)

だけでも、【方法その2】と同様の事が出来ます。

お礼

ご回答ありがとうございます。
質問文が不明確で申し訳ありません。
ずっと前に作成したファイルなので、自分でもどのように作ったのか忘れてしまっていまして。。。

合計の数式を変えることでも対応できるんですね、試してみます！

ありがとうございました。

回答No.4

KURUMITO

次のような式にすればよいでしょう。
A1セルに2012などの年、C1セルに9などの月が有るとします。
A3セルから下方にその月の日付が入力されており、E3セルから下方に残業時間が入力されているとします。
その場合の残業の合計時間が次の式で求められますね。

=SUMPRODUCT((YEAR(A3:A33)=A1)*(MONTH(A3:A33)=C1),E3:E33)

なお、勤務時間の合計を時間で表示させる場合には　「セルの書式設定」の「表示形式」で種類の窓に　[h]:m　と入力してOKします。
ご質問で正確な表示にならないのは式ではなくて「表示形式」の問題かもしれませんね。例えば残業の合計時間が28時間のような場合には上のような表示形式になっていない場合には1日と4時間ですから4:00のように表示されてしまいます。
セルの表示形式を確かめることから初めてみてください。

お礼

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

セルに条件付き書式で「月が違う場合は文字色を白に」と設定していたため、
データが残っていることに気付けなかったようです。

合計時間の数式を変えることでも対応できるんですね、試してみます。
ありがとうございました！

回答No.3

koko88okok

> 9月は30日までですが、30日の次の空白欄に8月31日のデータが残ってしまっているようで、
合計に反映されてしまいます。

何故、８月のデータが残っているのか理解できませんが、９月の合計に8/31は無関係ですので、
非表示にするのではなく、削除すべきと思います、

お礼

大変失礼いたしました。

日付欄に、「入力月と違う月の場合は文字色を白に」という設定をしています。

文字の色を白くしているだけなのでデータが残ってしまっているようです。

空白にする条件設定もできるのでしょうか。

回答No.2

chonami

>と入力し、2日以降は「=前日+1」しています。

これだと、31日を小の月にどうやって非表示にしているかわからないですよね？
それを聞いているんだと思いますよ。
31日のセルにはどういった式を入れているんですか？
それとも、条件付き書式で白文字とか？

上のセルと月が違ったら空白にする、というような内容の式を作るといいかもしれませんね。

お礼

大変失礼いたしました。

日付欄は、条件付き書式で「入力月と違う場合は文字色を白に」という設定がされています。

文字色を白、ではなく空白の設定もできるのでしょうか。

回答No.1

aokii

30日の次の空白欄の数式によって処理は異なります。

お礼

失礼致しました。

A1に「年」を入力し、C1に「月」を入力した上で、

勤務表の1日目の欄に

=DATE(A1,C1,1)

と入力し、2日以降は「=前日+1」しています。