【EXCEL VBA】COUNTIF関数で勤務時間を自動計算する方法
- Excel VBAを使って、勤務時間の自動計算を行いたい場合には、COUNTIF関数を利用することができます。
- 具体的には、出社時刻と退社時刻がブランク（休日等）の場合に、勤務時間が0時間ではなく24:00:00で換算されることがあるため、1ヶ月分の勤務時間の合計に影響が出てしまいます。
- そこで、各々氏名コード毎に月末日の箇所に24:00:00で換算された時間を自動的にカウントするために、COUNTIF関数を使用します。具体的な関数の記述方法は、[=COUNTIF(F2:F32,"24:00:00")]です。
- ベストアンサー
【EXCEL VBA】COUNTIF関数について
A列・B列：氏名コード、C列：日付、D列：出社時刻、E列：退社時刻、F列：勤務時間といった具合に1ヶ月分のデータが入力されています。 各々氏名コード毎にG列の月末日の箇所に1ヶ月分の勤務時間の合計を入れているのですが、出社時刻と退社時刻がブランク(休日等)の場合、F列の勤務時間が0時間でなく24:00:00で換算されてしまう為、実質1ヶ月分の勤務時間の合計がその分加算されてしまいます。(画像参照) そこで、各々氏名コード毎にH列の月末日の箇所に24:00:00で換算されてオーバーしてしまった時間を自動的にカウント出来るようにしたいのです。 手動で勤務時間の1ヶ月の合計を算出する場合は、 [=IF(A32<>A33,SUMIF(A:A,A32,F:F),"")]で、 また手動で24:00:00で換算されてオーバーしてしまった時間をカウントする場合は、 [=COUNTIF(F2:F32,"24:00:00")] と関数対応できるところまではこじつけたたのですが、マクロで自動的に反映させる方法が分かりません。 何かいい手順がございましたら、ご指導の程宜しくお願い致します。 尚、作業環境はWindows7、office2010です。
- La_Ola_Azul
- お礼率66% (10/15)
- その他MS Office製品
- 回答数4
- ありがとう数5
- みんなの回答 （4）
- 専門家の回答
質問者が選んだベストアンサー
>F列には[=IF(E2-D2>0,E2-D2,E2+1-D2)]が入っています。 ⇒この数式を以下のようにすれば調整が不要ではないでしょうか。 [=IF(E2-D2>=0,E2-D2,E2+1-D2)] >マクロで自動的に反映させる方法が分かりません ⇒何をマクロで自動化したいのか判りませんので具体的に補足して下さい。
その他の回答 (3)
- KURUMITO
- ベストアンサー率42% (1835/4283)
手動で24:00:00で換算されてオーバーしてしまった時間をカウントするとのことですが、勤務時間の計算においてそのようなことになることは通常の方法ではありえませんね。避けることが必要でしょう。 どうやら24時間の分を差し引くことになっている原因はF列に入力している式に問題がありますね。 実際の計算ではシリアル値から時刻を出すため、F列には[=IF(E2-D2>0,E2-D2,E2+1-D2)]が入っています。 出社時刻と退社時刻がブランクの場合、F列の勤務時間が0:00:00=24:00:00と認識されてるかと思います。 F列の式が上のようではE列やD列のデータが空の場合には1の値で、それがシリアル値となって時間では24:00と同じことになります。上のような式ではE2-D2の値がE2に比べてD2の値が大きい場合にはエラー表示になってしまうのでそのようにしているのでしょうか？ シリアル値から時刻を出すためにそうしているとのことですが、例えば時刻で12:00と入力すればエクセルではそれが自動的にシリアル値では0.5となって時間の計算に使われているのです。逆にセルに0.5と入力してからセルの表示形式を時刻にすることで12:00のように表示させることができます。すなわちセルに入力した時刻はシリアル値としてエクセルでは理解され、セルに入力した数値もまたセルの表示形式を操作することで時刻の表示にもできるのです。 シリアル値から時刻を出すために1などの数値を加算するなどのことは全く必要ありません。シリアル値と時刻とは一体ものと考えればよいのです。 通常ではE列の時刻(シリアル値）がD列の時刻(シリアル値)よりも大きくなっていますのでE2-D2の値は負になることはありませんが負になる場合には時間の計算ではエラー表示になります。例えば退勤時間が夜中の午前1時の場合にE2セルに1：00のように入力した場合にはエラー表示になってしまいますのでその場合にはE2セルに25：00と入力するか、あるいはE2+1からD2セルの値を引くことになります。この式で1は24:00と同じ意味を持っています。 E列で午前1時を25：00のように入力する場合にF2セルに入力する式は次のようにすればよいでしょう。 =IF(COUNT(D2:E2)<>2,"",E2-D2) 午前1時を1:00のように入力する場合には =IF(COUNT(D2:E2)<>2,"",IF(E2>=D2,E2-D2,E2+1-D2)) F2セルに入力した式を下方にオートフィルドラッグすればよいでしょう。 G2セルには次の式を入力して下方にオートフィルドラッグすればよいでしょう。 =IF(A2<>A3,SUMIF(A:A,A2,F:F),"")
お礼
主題の件、仰るとおりF列の数式に問題がありました。 下記コメントを頂いた方の数式で無事解決しました。 コメント有難うございました。
- keithin
- ベストアンサー率66% (5278/7940)
とりあえずご質問の直接の回答としては,Ｈ列の数式を =IF(A32<>A33,COUNTIFS(A:A,A32,F:F,1),"") とでもしておけばＯＫです（勿論書式は[h]:mm:ssのように）が， そもそもで言えば ＞F列の勤務時間が0時間でなく24:00:00で換算されてしまう こちらを解消するのが先でしょう。 一体全体いまＦ列にどんな式を入れていて(若しくはＤ列Ｅ列にいったい何を入れていて)そんなけったいな事になってしまうのかというのはありますが，ごくシンプルに =IF(COUNT(D32:E32)=2,E32-D32,0) といった具合にしておくか，もしかしてもしかすると =IF(COUNT(D32:E32)=2,MOD(E32-D32,1),0) としておくとよいかも？しれません。
お礼
主題の件、ご提示頂いた数式で表示出来ました。 コメント有難うございました。
補足
実際の計算ではシリアル値から時刻を出すため、F列には[=IF(E2-D2>0,E2-D2,E2+1-D2)]が入っています。 出社時刻と退社時刻がブランクの場合、F列の勤務時間が0:00:00=24:00:00と認識されてるかと思います。 まずは教えて頂いた数式を入れてみますね。
- bin-chan
- ベストアンサー率33% (1403/4213)
列Fの式でその行の列D,列Eを評価して勤務時間を算出させるほうが先なのでは？ 補足してください。 １）２５時など、２４時を過ぎる退社時刻がありますか？ ２）そもそも列Fに何か式を設定してるのでは？何と設定してますか？
お礼
主題の件、無事解決しました。 コメント有難うございました。
補足
・24時を過ぎる退社時刻があります。 ・F列には[=IF(E2-D2>0,E2-D2,E2+1-D2)]が入っています。
関連するQ&A
- 【EXCEL VBA】データの並べ替えの方法
元データが左から右へ氏名コード、日付(2011/5/1)、出社時刻、退社時刻、日付(2011/5/2)、出社時刻、退社時刻、・・・・・日付(2011/5/31)、出社時刻、退社時刻、と順に1ヶ月分入力されています。（画像上） このデータを一番左端に氏名コード、上から日付(2011/5/1)、出社時刻、退社時刻、行を変えて日付(2011/5/2)、出社時刻、退社時刻、・・・・・日付(2011/5/31)、出社時刻、退社時刻。（画像下）と言う形に行・列の並べ替えをしたいと考えています。 EXCELの機能である、「コピー→編集→形式を選択して貼り付け→行列を入れ替える」では対応できず、VBAマクロ又は関数で試みようとしましたが、こちらも対応方法が見つかりません。 何かいい方法がございましたら、ご指導のほど宜しくお願い致します。 尚、作業環境はWindows7、MS office2010です。
- ベストアンサー
- その他MS Office製品
- COUNTIF 関数について
エクセル関数（COUNTIF）を教えてください 例題 勤務 氏名 1 日勤 A 2 日勤 B 3 公休 C 4 特休 D 5 日勤 E 6 年休 F 7 日勤 G 8 公休 H 9 日勤 I 10 特休 J Ａ列に勤務、Ｂ列に氏名が入力されています 質問の関数でＣＯＵＮＴＩＦ（Ａ５：Ａ１０、"日勤”）ですが ”Ｅ”さんの位置より後に”日勤”が何人いるかをカウントしたい 計算式のＡ5の値を自動的に判別した計算式にしたい（Ｅさんの位置が変動するためにＡ５が固定できないためです） 以上よろしくお願いいたします
- ベストアンサー
- Windows XP
- 不完全な関数やVBAをまとめてVBA化したい
初めての質問と長文で、失礼いたしマス。 前任者の置きみやげ？の日報（一部不完全な関数とVBAを使用したもの）を職場で日々使ってい ます。 最近サイズが大きくなってきたり、使い勝手がいまいちということもあり、すっきりサックリなものにと 改良をしようとしましたが、Excel関数をかじった程度の自分の知識では無理でした。 この不完全な関数やVBAをまとめてVBA化する、詳しい手順や記述などを教えていただけないで しょうか？ 以下、現状と希望や条件などです。 ～下に画像添付いたします～ ・図／表１の氏名欄（T4、AC4、AL4、AU4、BD4）は、毎日変動します。 それぞれ入力規則のリストから選べるようになっています。 ・処理の対象はフォルダ内全ての日報ファイルです。 ちなみに、ドキュメント内に各年度フォルダ／各月フォルダ／日報ファイル１（Sheet1～ Sheet10）、日報ファイル２（Sheet11～Sheet20）、日報ファイル３（Sheet21～Sheet31） となっています。 ・原則として、自動入力させるセルは、氏名欄１（T列）、氏名欄２（AN列）、氏名欄３（BJ列）と 時刻欄２（W列）、時刻欄３（AQ列）のみです。 なお、時刻欄１（A列）と内容欄１（D列）、内容欄２（Z列）、内容欄３（AT列）は手動での入力と なります。 ・自動入力させるセルには、VBAの処理を無視した入力も可能にしたいです。 ※内容欄２（Z列）のみや内容欄３（AT列）のみを入力することがあり その際に時刻欄２（W列）、時刻欄３（AQ列）や氏名欄２（AN列）、氏名欄３（BJ列）への 入力が必要となる為です。 ★氏名欄１（T列）、氏名欄２（AN列）、氏名欄３（BJ列） について 希望として、入力した時刻に該当する"氏名（苗字のみ）"を、図／表１の 時間帯(1) 0:00～8:29 (2) 8:30～11:59 (3) 12:00～17:14 (4) 17:15～20:29 (5) 20:30～23:59 を参照して自動入力させたいです。 現状は ・同じ行の内容欄に記述がある場合にのみ"氏名（苗字のみ）"を自動入力。 ・一番上の行や上のセルが空欄の場合には"氏名（苗字のみ）"を入力。 ・"氏名（苗字のみ）"の場合は、"〃"を入力。 ・"〃"が入力されている場合は、"〃"を入力。 となるような下記関数を使用中です。 また、時間帯によって下記関数内の"氏名（苗字のみ）"を置換を使用し変更している状態です。 ◎例えば 8:30～11:59 と 17:15～20:29 の入力時には、下記関数内の氏名の箇所を"長谷川" に置き換えています。 氏名欄１(T12) =IF(AND(D12<>"",OR(T11="〃",T11="長谷川")),"〃",IF(AND(D12<>"",T11=""),"長谷川","")) 氏名欄２(AN12) =IF(AND(Z12<>"",OR(AN11="〃",AN11="長谷川")),"〃",IF(AND(Z12<>"",AN11=""),"長谷川","")) 氏名欄３(BJ12) =IF(AND(AT12<>"",OR(BJ11="〃",BJ11="長谷川")),"〃",IF(AND(AT12<>"",BJ11=""), "長谷川","")) また上記関数の場合、時間帯が切り替わる時には正しい"氏名（苗字のみ）"が入力されない ので、その場合の各氏名欄へは手入力が必要となっています。 ★時刻欄２（W列）、時刻欄３（AQ列）について 基本的に内容欄１（D列）の入力を基準として、関連する項目があれば、内容欄２（Z列）もしくは 内容欄３（AT列）に追加入力があります。 その場合、入力時刻は内容欄１（D列）の時刻と同じにします。 となるように、時刻欄２（W列）と時刻欄３（AQ列）で、下記関数を使用中です。 ◎例えば：時刻欄１（A12）と内容欄１（D12）に入力や記述があり、内容欄２（Z12）または 内容欄３（AT12）にも記述がある場合には、時刻欄２（W12）や時刻欄３（AQ12)に時刻が 自動入力されるとして 時刻欄２（W12）： =IF(Z12="","",A12) 時刻欄３（AQ12）： =IF(AT12="","",A12) なお、内容欄１（D列）、内容欄２（Z列）、内容欄３（AT列）への記述は、１～数行になることが あります。 この場合、該当する時刻欄には最初の行のみ時刻を入力し、以下の行の時刻欄は空白と なります。 ★内容欄１（D列）、内容欄２（Z列）、内容欄３（AT列）について 内容欄１（D列）への記述で（ ）付の文字列、 ○号室 or △号室 or □号室 or 空き のみの場合には、自動で中央揃えになるように下記のVBAを使用中しています。 これが唯一、重宝している置きみやげでした。 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Sh.Range("D:D"), Target) Is Nothing Then Exit Sub Dim r As Range For Each r In Intersect(Sh.Range("D:D"), Target) If InStr("/（○号室）/（△号室）/（□号室）/（空き）/", "/" & r.Value & "/") > 0 Then r.HorizontalAlignment = xlCenter End If Next End Sub - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 上記、内容欄１（D列）で処理されるVBAを除いて、関数や条件付書式などを考えて改良を 試みてみましたが、難しかったです。 上の内容欄１（D列）のVBAを含めたものなら幸いです。 よろしくお願いいたします。
- 締切済み
- その他([技術者向] コンピューター)
- Excelですが関数式を教えて下さい。
Sheet1に下記の表があり、Sheet2では氏名順不同でSheet1のB列,C列と小計。D列,E列の小計,合計の表にしたいのです。理屈は簡単そうですが素人でわかりません。何か良い方法があれば教えて下さい。 Sheet1 A B C D E F 1 (1) (2) (3) (4) 合計 2 小泉 25 25 23 24 97 3 福田 23 24 20 22 89 4 阿部 24 22 19 25 90 Sheet2 (1) (2) 合計 1 阿部 46 44 90 2 麻生 ・ ・ ・ 3 ・
- ベストアンサー
- オフィス系ソフト
- Excel関数
五年計画で事業を進めています。 一ヶ月毎の進捗率を出したいです。 Sheet1に A列(項目) あ い う え お B列(数量) 10 20 30 40 50 C列(完了日) 2014/07/15 2014/07/22 2014/08/05 2014/08/06 2014/07/31 Sheet2に A列1行に 7月進捗の(10+20+50)／全体数＊100 答え A列2行に 8月進捗の(30+40)/全体数＊100 答え A列3行に 9月未定？ 答え A列4行に 10月未定？ 答え と進めたいがパーセントへの計算式は別として、Sheet2のA列1行に条件としてSheet1のC列7月分に完了した分だけを拾い合計しなさい！とし、同じくSheet2のA列2行にSheet1の8月分だけを拾い合計としたい。 現在SUMIFで考えていますが、範囲はSheet1のA列で合計範囲はSheet1のB列で検索条件はSheet1のC列ですが文字(みかん)や記号(★)なら出来ますが日付 2014/08/06とか言った様な場合がどの様にしたらわかりません。 この用紙だと列を横書きになってますがあくまで標準のExcelのシートで考えて下さい。 宜しくお願いします
- 締切済み
- Excel（エクセル）
- excel2000でVBAのコード相談
添付図にあるように、 Ａ列 日付 Ｂ列 時刻 Ｃ列 値 という表があります。 そして、画像のように 値が3以上の時の、日付と時刻を、Ｆ列～Ｊ列の表に書き出したいです。 書き出す表は、上から順に 1回目が9行目、2回目が10行目、3回目が11行目 ・・・ といった具合です。 どういったコードがいいか、なかなか思い浮かばず、いいものを教えていただければ幸いです。 よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- Excel2010で
Excel2010でB3出社時刻8:00 C3退社時刻 18:30 D3勤務時間:10:30に成りますD3 10:30を１０．５に表示する方法、セルとセル×て合計セルだけ残して掛け算セルを削除する方法有りますか。
- ベストアンサー
- オフィス系ソフト
- エクセルで残業時間を時間帯によって＋１時間＋30分にしたい
関数がわからなくて困っています。 基本は8:30～17:30 残業（早出含む）とされる時間帯 5:00～7:30／17:30～19:30 この時間帯は1.0時間刻み （退社が18:00だと残業は0） 7:30～8:30／19:30～24:00 この時間帯は0.5時間刻み （退社が20:00だと残業は2.5） 休憩は1:00（12:00～13:00） 以上の条件でＣ列―出社時刻、Ｄ列―退社時刻、 Ｅ列―勤務時間、Ｆ列―残業時間 で表を作った場合、Ｆ列の残業時間を出す式がわかりません。 （Ｅ列の勤務時間もですね。自分で考えたのはアヤシイ） E/F列は、例えば1時間30分なら1.5と出てくるようにします。 7:30出社で20:00退社の場合、勤務時間は8.0、残業時間は3.5となる ようにしたいのです。 時間の計算が苦手で・・・。どなたか教えてください。
- ベストアンサー
- オフィス系ソフト
- IF関数やCOUNTIF関数など詳しい方
エクセルについての質問です ある機械の稼動グラフを作りたいと思っています そのためのデータをまとめたいのですが シート１のＡ列に１分おきの時刻表があり シート２のＡ列に発生時刻（時間帯はバラバラです）、Ｂ列にＯＮとＯＦＦがはいっています こんな感じです シート1 シート２ A B A B 1 9:00 0 1 8:20 ON 2 9:01 2 8:43 OFF 3 9:02 3 9:03 ON 4 9:03 （略） （略） このときシート１のＢ２から下に、ＯＮになれば５、ＯＦＦになれば０ 何もないときは上のセルと同じ値（ＯＦＦのときは０が続いていく感じです）を入れたいのですが なかなかうまくいきません（日付などはあわしています） 誰か詳しい方ご教授お願いします
- ベストアンサー
- オフィス系ソフト
- エクセルの関数COUNTIF
F16に合計時間を計算させています。 sheet1~sheet8までの0より大きな時間の（時間の合計でなく）個数の合計を知りたいのですが ＝COUNTIF(Sheet1:Sheet8!F16,"0>") ｖalueのエラーが出ます。 またセルに参照式が入っていています。＝Ａ2等、参照元には、時間が入力されていますが、単純に、時間が入力された個数だけ知りたいのですが どのようなCOUNT関数を使えばよいですか。
- ベストアンサー
- Excel（エクセル）
お礼
主題の件、ご提示頂いた数式で問題が一発で解決しました。！ お陰でH列を使用する無駄が省けました。 本当に有難うございました。