本を購入して読んでみたのですが、よくわからなかったので教えて下さい。
定時は9:00-18:00です。
１．次の場合どのような関数をいれればいいでしょうか？
出勤時間(C6)を 退出時間(E6）へ入力
7:00に出社しても9:00からしかカウントしません。
9:05までなら遅刻とせず、9:00出社としてカウント
9:05を過ぎると30分単位で時間をカウント（9:06出勤なら9:30から）
２．パートさんが曜日によって勤務時間が変わります。その場合どのよう
に関数をいれればいいでしょうか？
月・水・金 9:00-18:00
火・木 8:00-17:00
曜日によって１．の質問の9:00からのカウントを、8:00からのカウント
というような選択をしたい場合
３．次の場合のマクロをどのように作成すればいいでしょう。
（今回はじめてマクロに挑戦します。そもそも根本的な考え方が
おかしいかもしれません。ご指導よろしくお願いします。）
１名（タイムカード１枚）で１シート 社員３０名の１カ月分を
１ファイルで管理しようと思います。
Ａ．マクロ用のシートにタイムカードの管理期間、たとえば11/1－30すると
以下が自動的に入力されるマクロ
・全社員の勤怠表の日付(A6):(A35)まで自動的に日付が入力(A36）
は空欄表示
・全社員の勤怠表の曜日(B6):(B35)まで自動的に日付が入力(B36）
は空欄表示
Ｂ．マクロ用のシートにあるクリアボタンを押すと以下がデータクリアされる
マクロ
・全社員の勤怠表の出勤時間(C6):(C36)と退社時間(E6):(E36)が
自動的にデータ削除される。
質問が分かりづらいとは思いますがよろしくお願いします。
投稿日時 - 2008-10-23 22:13:23
No2です
ご質問２の後半部分なのですが
> ただその前に、ANo.8の自動化とは関数ではなくVBAということなのでしょうか？
> （関数で自動的に表示するようにはならないということでしょうか？）
大丈夫です、関数でも自動化できますよ
就業時間についほとんどの人が月～金で9:00-18:00、一部の方が月水金9:00-18:00、火木8:00-17:00ということですね。
前者を勤務パターン１、後者を勤務パターン２とします。
そして、今集計している方が勤務パターン１の人なのか勤務パターン2の人なのかをどこかに情報として持たせる必要があります。
とりあえずJ5セルを使い、勤務パターン１に方は数値で １ 、勤務パターン２の方は数値で ２ と入力することにしましょう
わかりやすくするためにI5セルには見出しとして 勤務パターンと入力しておきましょう
（I5およびJ5セルが開いていない場合にはほかのセルを使用してください。以下の数式のJ5セルは使用したセルに置き換えてください）
M列に入れる数式はIF関数をいくつか組み合わせて作ってもできますが、式が長くなり見通しが悪くなりますのでもう少しスマートしたいのと、将来勤務パターンの変更や別の勤務パターンが発生したときでも対応できるように、勤務パターンの情報を持たせます。
勤務パターンが曜日によって決まるということなので「曜日」シートを利用いたします。
「曜日」シートのC列に勤務パターン１の始業時刻・D列に勤務パターン２の始業時刻を入力します。
C1～C7には 9：00 D列は3行目（火曜日）および5行目(木曜日）に8：00、それ以外は9：00と入力してください。
（休日出勤については後で考えるとして、ここでは土日の始業時間は仮に9:00としております。）
勤務パターン別の始業時刻のみ入力し、終業時刻は情報として持たせておりません。
終業時刻についてはすべての場合において9時間拘束として考えておりますので、終業時刻＝始業時刻＋9時間 で算出することにします。
もしそうでない場合（将来的に拘束時間も人によって変わる事がある可能性があるのなら、終業時刻もここに入れておいたほうがよいと思います）
将来勤務パターンが変更になった場合はここの始業時刻を変えるだけでＯＫです。また新しい勤務パターンが発生した場合はＥ列以後に加えていけばよいでしょう。
曜日以外で決定されるパターンが発生する場合は以下に記述するＭ列の式を変更する必要がありますが、それはそのときに考えることとします。（それが数名ならＭ列に直接始業時刻を入力することで逃げることはできます）
また、「曜日」シートのセル範囲A1:D7には範囲名をつけたほうが後々のメンテナンスが楽になると思いますが、今回は範囲名は使用せずにいきます。（興味があれば「範囲名」について調べて利用してください）
M8セルには下記の数式を入力してください。
=VLOOKUP(D8,曜日!$A$1:$D$7,$J$5+2,FALSE)
ついでにH8セルの終業時刻にも下記の数式を入力してください。
=M8+TIME(9,0,0)
これで始業時刻・終業時刻とも自動的に表示されると思います。
休日についての処理は何もしておりませんので、出勤時刻、退出時刻の入力がないとエラーになってしまうと思いますが、とりあえず今のところはこのままで行きますね。
ただ、エラー表示を防ぐにはどうしたらよいかは考えておいてください。
投稿日時 - 2008-10-29 00:30:41
お礼
BrueBreeze様、ご回答いただきありがとうございます。
M8セルになぜか数式を入れても出来ませんでした。
今回はIF関数にIF関数をネストしVLOOKUP関数を使って逃げて解決しました。
ということで現状式が長くなり見通しが悪くなってます。（苦笑）
＞=VLOOKUP(D8,曜日!$A$1:$D$7,$J$5+2,FALSE)
上記の$J$5+2の+2をすることで勤務パターンが選択できると思うのですが
うまく動かなかったので、このタイムカード集計のエクセルがとりあえず完成たら勉強して作り直したいと思います。
また範囲名のアドバイスありがとうございます。
全く知らない言葉でした。すぐに検索をかけ読んでみたところ、とても便利な機能なので少しずつ使っていこうと思います。
これで質問を閉めますが、BrueBreeze様には本当に親切にご指導いただきありがとうございました。
貴重なお時間を割いていただいたおかげで、こちらは従来の何倍も早く当初の目的が達成できそうです。感謝申し上げます。
投稿日時 - 2008-10-29 23:29:46
1人が「このQ&Aが役に立った」と投票しています
ベストアンサー以外の回答（9件中 1～5件目)
No2です
> 本来はエクセルでタイムカードの情報を網羅し、エクセルをもとに給与計算ソフトに落とし込んでいくのがベストです。
げっ、手抜きができるな と思っていたのですがぬか喜びでしたね
>
> 遅刻した場合のマイナスを残業時間のトータルから相殺するようにしたかったのです。
> でも、その前の段階でつまずいていてそこまでいきついていませんでした。
そうでしたか
> 今回の質問で随分頭の中の整理もできましたし、これからまた作り込んでいこうと思います。ただ作り込んでいく中で、また壁にぶちあたることもあると思います。
そうですね、整理できたことから順番にこつこつやっていけばよいと思います。
No3さんのおっしゃっている
> 人手でやる場合はどういう操作になるか考えて逐一記述する。
> VBAも人手の操作でも出来ることを、コードに置き変えたものが、ほとんどであるから。
というのは本当にそのとおりなんです。
VBAに限らずね
がんばってください。
投稿日時 - 2008-10-28 16:00:40
お礼
BrueBreeze様、回答ありがとうございます。
VBAに関しては地道にやって行こうと思います。
ただその前に、ANo.8の自動化とは関数ではなくVBAということなのでしょうか？
（関数で自動的に表示するようにはならないということでしょうか？）
VBAということであればいったん質問を締切り、勉強した上でそれでもわからない時にもう一度質問しようと思ったものですから。
何度も質問してすみませんがよろしくお願いします。
投稿日時 - 2008-10-28 21:15:02
No2です
ａ）式でよいということで、式が簡単になってよかった
さて、ご質問の２について
ａ）式では始業時刻を仮に9:00としていました。
ａ）式 =IF(F8<=TIME(9,0,0),TIME(9,0,0),CEILING(F8-TIME(0,5,0),TIME(0,30,0))) の TIME(9,0,0) が 始業時刻である9:00をあらわしています。
ここが日によって変わるということなので、とりあえず（回答No5で使わなかった）M列を使いましょうか
セルG8の式の TIME(9,0,0) の部分をすべて M8 に変更して以下の様にしてください
=IF(F8<=M8,M8,CEILING(F8-TIME(0,5,0),TIME(0,30,0)))
列見出しが紛らわしいので
F列の見出しを「出勤時刻」M列の見出しを「始業時刻」にしてください。
日によって変わる始業時刻はＭ列に手作業で入力してください。
このままでも十分使えると思いますが、入力する項目が増えますので手間になりますね
なんとかM列を自動的に始業時刻が入るようにしたいのですが、自動化するためには決まったルールが必要になります。
ひとつは
イ） 月・水・金 9:00-18:00 火・木 8:00-17:00
ということですが、同時に
ロ）特定の人のみが曜日によって時間が変わります。
とのことでした。
これは、
ａ）ほとんどの人が原則イ）パターンで特定の人のみが例外的にイ）以外のパターンになるということでしょうか？そのイ）以外のパターンの法則性は？
ｂ）ほとんどの人の原則定時が決まっており、特定の人のみが例外的にイ）パターンになるということでしょうか？その場合のほとんどの人の定時は？
ａ）ｂ）いずれの場合でも、例外となる割合はどれくらいなのでしょう。
例外の発生が少ないのであればその例外の場合だけＭ列に手入力で逃げることは可能です。
また、例外の発生があまりに多いのであれば、自動化するのは難しくなります。すべてＭ列を手入力したほうがいいかもしれません。
投稿日時 - 2008-10-26 23:12:45
補足
BrueBreeze様、ご回答いただきありがとうございます。
＞ イ）月・水・金 9:00-18:00 火・木 8:00-17:00
＞ ということですが、同時に
＞ ロ）特定の人のみが曜日によって時間が変わります。
＞ とのことでした。
またわかりにくい質問ですみませんでした。
特定の人のみがイ）の勤務状態に曜日によって変わるということです。
その他のひとは月～金まで9:00-18:00でかわりません。
これならば自動化できますでしょうか？
よろしくお願いします。
投稿日時 - 2008-10-27 21:10:51
No2です
うまくいきましたか、よかったです。
> これは私が本やネットを参考に作ったものです。
そうでしたか、SUM関数しか知らない方が、ここまで作り上げたのですから立派なものです。
私が考えてしまった点は、ご回答いただいた構成を見て「残業時間の計算しかしていない」と感じたからです。
前出の「出勤時刻を入力するセルが見つからない」というのもそうですが
通常勤怠管理を行うなら、遅刻・早退の時間管理や休憩時間の扱い、休日出勤の処理等があってもよさそうなのですが、それがなかったもので
ただ、この疑問も
> 給与計算ソフトに落とし込む前のタイムカードの集計
とのことで納得しました。
ということは、この勤怠管理表ですべてを処理しなくてもよいと考えてもよろしいでしょうか？
給与計算ソフトに必要なデータさえ得ることができればよいと割り切ってしまえばかなり大雑把に作ってもいけると思います。
多少うまくいかない部分があっても、給与計算ソフトに入力する段階で修正して入力するという考え方でいかがでしょうか？
給与計算ソフトに落とし込む際に必要なデータは何が必要でしょうか？
> 具体的にどの列のものとか、なぜこの関数を使用しないのかとかヒントだけでもいただけるとありがたいです。
できるだけ現状のものを利用して回答していこうと考えております。
今までのものもすでに使用してある関数を使用しております。わからないことがあったらそれこそ本やネットを使ってできるだけ調べてください。
（もちろんネットの中にここ質問することも含まれますよ）
私の回答の中で不明な点があれば、「どうしてここはこうなんだ」と具体的に訊いて下さればＯＫです
投稿日時 - 2008-10-26 22:35:33
お礼
BrueBreeze様、ご回答いただきありがとうございます。
＞ この勤怠管理表ですべてを処理しなくてもよいと考えてもよろしいでしょうか？
本来はエクセルでタイムカードの情報を網羅し、エクセルをもとに給与計算ソフトに落とし込んでいくのがベストです。
遅刻した場合のマイナスを残業時間のトータルから相殺するようにしたかったのです。
でも、その前の段階でつまずいていてそこまでいきついていませんでした。
そういった未完の部分があったため疑問をいだかせてしまったのですね。
今回の質問で随分頭の中の整理もできましたし、これからまた作り込んでいこうと思います。ただ作り込んでいく中で、また壁にぶちあたることもあると思います。
その時はまたこちらで質問をするかもしれませんが、質問がBrueBreeze様のお目に止まりましたらご指導いただけたらと思います。よろしくお願いします。
投稿日時 - 2008-10-27 21:10:24
No2です
すいません、No5の回答のａ）式とｂ）式がまったく一緒でしたね
ビール飲みながらあれこれやってるとこれなんで（汗
ａ）式は以下のように訂正してください
=IF(F8<=TIME(9,0,0),TIME(9,0,0),CEILING(F8-TIME(0,5,0),TIME(0,30,0)))
投稿日時 - 2008-10-25 22:18:55
お礼
BrueBreeze様、ご回答いただきありがとうございます。
早速やってみました。うまくいきました！
回答を見ても、長い式なのですぐには理解できず、何度か見返してようやく納得できました。
ありがとうございました。
投稿日時 - 2008-10-26 21:39:17
No2です
現状を詳しく書いていただきありがとうございました。
おかげでどのようになっているのかよくわかりました。
これは、あなたが作られたのでしょうか、それとも前任者からの引継ぎでしょうか。いずれにしても、（細かいところで「ウーン」と考えてしまうところはありますが）よく作成されていると思います。
ご質問の内容が多いので順番にいきますね
まずご質問の１についてですが
出勤時刻を入力する箇所がほかに見当たらなかったのですが
現状で「出勤時刻はＦ列に手入力している」と考えていますがいかがでしょうか
タイムカードの出勤時刻を見ながら、始業時刻以前の出勤であれば始業時刻を、遅刻した場合はその時刻を入力しているとして以下の回答をしております。
（原則としてタイムカードの出勤時刻をそのままＦ列に入力するとして）
> 遅刻の場合、35分に出勤でも30分として認めるということです。
> 始業時のみ5分の遅刻を認め、それ以外は毎時正時と30分が起点となります。
> （病院等に行くために大幅に遅刻。11:05に出勤の場合は11:00として認められます。）
この部分の解釈なのですが、
ａ)原則として、始業時および、いかなる理由の遅刻の場合でも、5分の遅延が認められ例外は発生しないというのならば
遅刻の場合
病院等に行くために大幅に遅刻。11:05に出勤の場合は11:00として認めらる
寝坊して大幅に遅刻した場合でも11:05に出勤の場合は11:00として計算をする。
つまり、どのような理由で遅刻したかを問わず5分の遅延を認め、11:05分に出勤した場合は11:00として計算し、常に1通りの計算方法しかない。
ｂ）原則として始業時のみ5分の遅刻を認め、いかなる理由の遅刻の場合でも5分の遅延は認められない。（これはご質問の文面からは考えられないと思いますが）
ｃ）原則として、始業時のみ5分の遅延が認められ、それ以外は毎時正時と30分が基点となる。ただし、特別の事情が認められる遅刻の場合には特例として5分の遅延が認められるということでよろしいでしょうか？
遅刻の場合
病院等に行くために大幅に遅刻。11:05に出勤の場合は11:00として認めらる
寝坊して大幅に遅刻。11:05に出勤の場合は11:30として計算をする。
つまり、同じ11:05分に出勤した場合にでも、11:00出勤とする場合と11:30出勤で計算する場合の2通りの計算方法が考えられる。
それぞれの場合にG8セルに入力する式ですが
（わかりやすくするために、定時が変わる処理および休日の処理はまだしておりません、また始業時刻を仮に9:00としております）
ａ）の場合は処理としては非常に楽となります。考え方としては単純に始業も遅刻も関係なく出勤時刻から5分引いた時間で考えればよいからです。ただし、始業時刻＋5分までの出勤については強制的に始業時刻にします。
=IF(F8-TIME(0,5,0)<=TIME(9,0,0),TIME(9,0,0),CEILING(F8,TIME(0,30,0)))
ｂ）の場合（仮に始業時刻を9:00とします）
定時出勤、つまり出勤時刻が始業時刻＋5分より早い場合と、遅刻、つまり出勤時刻が始業時刻＋５分より遅い場合）に分けて考える必要があります。
=IF(F8-TIME(0,5,0)<=TIME(9,0,0),TIME(9,0,0),CEILING(F8,TIME(0,30,0)))
ｃ）の場合はもう少し複雑になります（同様に、仮に始業時刻を9:00としています）
まず、遅刻の場合、それが5分の遅延が認められる遅刻なのか認められない遅刻なのかを判断する必要があります。
=IF(F8-TIME(0,5,0)<=TIME(9,0,0),TIME(9,0,0),IF(<認められる>,CEILING(F8-TIME(0,5,0),TIME(0,30,0)),CEILING(F8,TIME(0,30,0))))
（このままでは使えません）
問題は <認められる> の部分、特別な事情として5分の遅延を認められるかを判断する条件なのですが、これは、どこかにその出勤が特別の理由として認められるかどうかの情報を入力する必要があります。
本来は、出勤時刻を入力したセルの横にその情報を入力できるようにしたほうがよいのですが、現状をあまり崩したくありませんので、ここではとりあえず開いている列 M列を使います。M列に ok と入力したら特別な理由として5分の遅延を認めるということにするなら
論理式 M8="ok" が5分の遅延を認める条件となりますので、上の式を下記のように変更します。
=IF(F8-TIME(0,5,0)<=TIME(9,0,0),TIME(9,0,0),IF(M8="ok",CEILING(F8-TIME(0,5,0),TIME(0,30,0)),CEILING(F8,TIME(0,30,0))))
ただね、5分の遅延が認められるかどうかの情報を入力する必要がありかえって手間が増えます。また、式自体も複雑となって、見通しが悪くなります
全部システムで解決するのではなく、運用上で解決するということも考えておいたほうがよいかもしれません。
運用上で解決するのであれば
式としては原則どおりのｂ）の方式で入れておいて、病院へ行くために大幅に遅刻をして11:05分に出勤した場合は11:00と入力し、寝坊で大幅に遅刻した場合はそのまま11:05と入力します。
もちろん始業時の処理にも同様なことが言え、始業時の5分の遅延の処理についても運用上で解決するならばｂ）式は
=IF(F8<=TIME(9,0,0),TIME(9,0,0),CEILING(F8,TIME(0,30,0)))
で、かまいませんし、始業時以前の出勤についてもすべて運用上で解決するのであれば
始業+5分までの出勤についてすべて始業時刻で入力するという手もあります。
その場合は
=CEILING(F8,TIME(0,30,0))
これでＯＫ
ただ、これでは、始業時刻が一定の場合はまだ楽なのですが、日によって変わるということなので頭で判断するとなると大変ですし、間違いの元になるでしょうね、それに、そもそものご質問の意に添えませんね
システムですべてを解決しようとするのではなく、場合によっては運用上で解決するほうが楽な場合もあるということで例出しました。
システムで解決するべき点、運用上で解決すべき点というのはバランス感覚です
ご自身でどちらが楽かを考えてみてください。
お勧めは ｃ)式 か ｂ）式＋運用での解決 のいずれかだと思います。
投稿日時 - 2008-10-25 22:07:37
お礼
BrueBreeze様、ご回答いただきありがとうございます。
これは私が本やネットを参考に作ったものです。（職場の諸先輩方は全て電卓もしくは暗算です。）
ですから、「ウーン」と考えてしまうのもあるかと思います。その「ウーン」と考えてしまうものですが、
具体的にどの列のものとか、なぜこの関数を使用しないのかとかヒントだけでもいただけるとありがたいです。
さて、本題の質問の１についてですが、出勤時刻を入力する箇所は「出勤時刻はＦ列に手入力している」であっています。記載ミスです申し訳ありません。
また解釈も
＞ ａ)原則として、始業時および、いかなる理由の遅刻の場合でも、5分の遅延が認められ例外は発生しない
であっています。
ｂ）、ｃ）につきましては、今回の内容には該当しませんでしたが、他の客先で起こる可能性がありますのでとても勉強になります。ありがとうございます。
そして、
＞ システムですべてを解決しようとするのではなく、場合によっては運用上で解決するほうが楽な場合もある
この言葉は深いですね。
いろいろなパターンを想定した上で、どちらを選択するかの判断も必要になりますし、人から仕事を受ける際、今まで自分の主観により勝手に解釈、
もしくは人に伝える際に伝わっていると思っていても違うことがあるかもとしれないと気付かされました。
また、BrueBreeze様のようにいろいろなパターンを想定できないことが今の自分に足りない点だと気づきました。ありがとうございます。
投稿日時 - 2008-10-26 21:29:35
