関数の事に関する質問 - 出勤時間と退社時間に応じた深夜判定の方法を教えてください
- 質問内容: 下のような書式があるのですが、A列の出勤時間が5:00前の時(4:50や2:00等まちまち)退社時刻が22:00を過ぎた時に深夜判定 C列に1と入力するのですが、 C列のセルに IF関数や COUNTIF等でチャレンジしてみましたが、A列B列にデーターが無い時の処理が、上手くいきません 良い方法を教えてください。
- 出勤時間が5:00前かつ退社時刻が22:00を過ぎた場合に、C列に1と入力する方法が知りたいです。A列とB列にデータが無い場合の処理も教えてください。
- A列の出勤時間とB列の退社時刻を元に、深夜判定(C列に1を入力)を行いたいです。A列が出勤時間、B列が退社時刻で、A列の時間が5:00前でかつB列の時間が22:00を超えた場合に、C列に1を入力する方法を教えてください。また、A列とB列にデータが無い場合の処理もお願いします。
- ベストアンサー
関数の事で、教えてください。
関数の事で、教えてください。 下のような書式があるのですが、 A列の出勤時間が5:00前の時(4:50や2:00等まちまち) 退社時刻が22:00を過ぎた時に 深夜判定 C列に1と入力するのですが、 C列のセルに IF関数やCOUNTIF等でチャレンジしてみましたが、 A列B列にデーターが無い時の処理が、上手くいきません 良い方法を 教えてください。 出勤 退社 認定 A B C 1 5:00 7:00 空白 2 4:30 12:00 1 3 13:00 22:10 1
- YOMOGI(@07535373)
- お礼率61% (60/98)
- Windows Vista
- 回答数9
- ありがとう数10
- みんなの回答 (9)
- 専門家の回答
質問者が選んだベストアンサー
>もう1度 おねがいしたいのですが、 IFの条件式が時間単位の整数のため計算誤差が生じたようです。 見方を変えて2通りの関数を提案します。 fx=IF(AND(A2>TIMEVALUE("4:59"),B2<TIMEVALUE("22:01")),,IF(A2=0,,IF(B2=0,"",1))) fx=IF(OR(AND(0<A2,A2<TIMEVALUE("5:00")),B2>TIMEVALUE("22:00")),1,"")
その他の回答 (8)
- Baku7770
- ベストアンサー率37% (110/292)
No.1です。補足に対しコメントします。 countblankは文字通り空白をカウントする関数です。 countblank(範囲)が使い方です。ご質問の場合だと、 countblank(c1:c1000)-countblank(a1:a1000)でA列が空白でなくc列が空白の数が出ます。 A列B列片方が空白の場合をどうするかという処理も必要なんじゃないですか?
- bunjii
- ベストアンサー率43% (3589/8248)
条件によって誤りが出ましたので再度の訂正です。 Fx=IF(AND(HOUR(A2)>5,HOUR(B2)<22),,IF(A2=0,,IF(B2=0,,1))) IF文の正に未定義の場合は0が与えられます。 0でなく空白にしたいときは正のとき""として下さい。 Fx=IF(AND(HOUR(A2)>5,HOUR(B2)<22),"",IF(A2=0,,IF(B2=0,"",1)))
お礼
何度も、お付き合いいただき 恐縮ですが、 退勤22:00から C列に 1 で無く、 22:00過ぎてから出したい時は、 どうすればよいでしょうか、 22:10 から 1としたいのですが、 試しても できませんでした・・・・ 0・91736111等になおしたり・・・ やはり 私では できませんでした。 もう1度 おねがいしたいのですが、
- bunjii
- ベストアンサー率43% (3589/8248)
ANo.5の訂正があります。 fx=IF(AND(HOUR(A2)>5,HOUR(B2)<22),"",IF(A2+B2=0,"",1)) ↓ fx=IF(AND(HOUR(A2)>5,HOUR(B2)<22),"",IF(A2*B2=0,"",1)) お勧めは次の通りです。 fx=IF(AND(HOUR(A2)>5,HOUR(B2)<22),0,IF(A2*B2=0,0,1))
- bunjii
- ベストアンサー率43% (3589/8248)
C列2行目の計算式は次のようになります。 fx=IF(AND(HOUR(A2)>5,HOUR(B2)<22),"",IF(A2+B2=0,"",1)) 但し、C列の計算結果を他の列で計算対象にするエラーが発生することがありますので注意して下さい。 ""の代わりに0を適用すればエラーが発生しませんので、こちらをお勧めします。
- tabotabo55
- ベストアンサー率32% (172/532)
5:00 22:00 始業 終業 4:30 13:00 1 0 1 7:00 23:00 0 1 1 23:10 0:10 1 1 1 8:10 14:10 0 0 上記のようになるようにしました。(E列の結果が上から、1,1,1,非表示 。またC列は1,0,1,0 D列、0,1,1,0 です) まずセルA1、B1に深夜の基準の時刻、その他仮のデータを入れ セルC3に =IF(A3="","",IF(OR(A3<$A$1,A3>$B$1),1,0)) セルD3に =IF(B3="","",IF(OR(B3<$A$1,B3>$B$1),1,0)) セルE3に =IF(C3+D3>0,1,"") 以下は上記の範囲のセルを下にコピー 式が複雑になりそうでしたので2つに分けました。後は、不要な列C,Dを非表示にすればOKかと。
お礼
今回の件で色々と考えていただきありがとうございました。 皆さまの力を借りて無事解決しましたので、 お礼を兼ねてほうこくします。 ありがとうございました。
補足
回答ありがとうございます。 このような 考え方もあったのですね。 新しいシートでためしたら、うまくゆきました。 目から鱗です。 しかし残念な事に、データーを入れたいシートに、 他のシートがでーたーを読み込んでますので、 行や列の追加ができないので、 なんとか、A・BをCのセル1つで処理したいのですが、 可能でしょうか、 よろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8248)
>退社時刻が22:00を過ぎた時に深夜判定 C列に1と入力するのですが、 深夜勤務の判定は22:00から翌朝5:00までと違いますか? 出勤が5:00より前の場合と退社が22:00を過ぎた場合の単独ではなく、出勤が9:00で退社が翌日の0:00を過ぎた場合もあり、16:00に出勤して翌日5:00を過ぎて退社した場合もあります。 それらのことを全て条件に入れる必要がありますか? 以上の補足を頂いてから考えたいと思います。
補足
そうそうの返答ありがとうございます。 言われるとうり、夜勤の者が、 14:00に出勤して、退社が5:00等の可能性も 在りますが、 近年年に 1度か2度程度なので、 単純に 出勤(A1)が、5:00前 つまり4:50から、が、朝勤の深夜判定 1 として、 午後勤の者が、 退社(B1)が、22:00を過ぎてからが、 (22:10) 深夜 1と 考えていますが、 こちらも 可能性としては、 22:00すぎ 出勤の場合なども考えられますが、 ほとんど 件数がありませんので、考えてませんでした。 IFやCOUNTIF関数に条件で、>を付けてためしたのですが、 データーが、無いセルの場合でも、1の判定が出てしまします。 たぶん 出勤時間 5:00>とした時、空白を0と考え 1と判定するのかとおもいますが、 何せ素人考えなので、 そこも分からないのです。 お手数ですが、よろしくお願いします。
- akiomyau
- ベストアンサー率43% (555/1280)
C列に以下のような式でどうでしょうか。 =IF(IF(A1="","",(A1>=TIMEVALUE("5:00"),"",1))
お礼
今回の件で色々と考えていただきありがとうございました。 皆さまの力を借りて無事解決しましたので、 お礼を兼ねてほうこくします。 ありがとうございました。
補足
ご検討ありがとうございます。 今書式に入れたのですが、エラー表示されます。 少し手を加えたのですが、 初心者な物で、やはりだめでした。 セルA1とB1の時間を読ませたいのですが、この式で、OKでしょうか!
- Baku7770
- ベストアンサー率37% (110/292)
countblank関数は試してみましたか?
補足
回答ありがとうございます。 COUNTBLANKですが、 どのように使えばよいのでしょうか、 あまり詳しくないのですが、 空白をカウントする関数ですおうか・・・ 使い方が分かりません、 お手数おかけします。
関連するQ&A
- 複数の条件を数える関数
エクセルでたとえばA1からA10・B1からB10のセルにa1、A2、B1、B2、C1、C2等入力された文字を数える関数を教えてください(空白はカウントしない)。COUNTIFで挑戦しましたができませんでした。
- ベストアンサー
- オフィス系ソフト
- エクセル関数の質問。
エクセル関数の質問です。 画像添付します。 画像のように、A・B列に「0」「1」「2」「空欄」がランダムに並んでいます。 C列に判定を表示します。 判定基準は、A・B列が同じ=○ 〃 異なる=× 〃 空欄有り=△です。 この場合、IF関数とCOUNTIF関数で式を作ったのですがエラーが出てしまいます。 お分かりの方がいましたら教えて下さい。 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 関数について教えてください。
エクセルで [A] [B] 1/ 1 3 2/ 1 3 3/ 0 4 4/ 1 5 5/ 1 4 6/ 1 0 7/ 0 1 8/ 3 2 9/ 1 3 10/ 0 2 11/ 1 0 12/ 0 2 13/ 2 3 14/ 0 1 このとき、B列が0の時のA列の1を数えたいのですが 適切な関数を教えてください。 今は、C列を=IF(AND(A:A=1,B:B=0),"●","")にし =COUNTIF(C:C,"●")で●の数を数えるという方法なのですが 面倒なのでもっといい方法はないものかと思いまして。 どうぞよろしくお願いします。
- 締切済み
- オフィス系ソフト
- 関数がうまくいきません?
簡単な、関数で恥ずかしいのですが フィールド行が5行目にあり6行目からデータ(データは毎日変更があります)が入力されたシートがあります。 I列からL列に条件が一致するものに○があり、一致しないものは空白となっています。 A B C D・・ I J K L 6 ○ 7 ○ ○ ○ 8 ○ ○ ○ ○ 9 ○ ○ 10 ○ ○ ○ 11 12 ○ ○ 13 ○ ○ 14 ○ ○ ○ ○ 15 ○ ○ ○ 16 ○ 17 ○ ○ 18 ○ ○ ○ ○ 19 ○ ○ ○ 20 ○ ○ ・ ・ ・ このI列の中で空白になっているセルの合計値をB2へ J列の中で空白になっているセルの合計値をD2へ、K列の中で空白になっているセルの合計値をB4へ、L列の中で空白になっているセルの合計値をD4へ入れるために =COUNTIF(I6:I65536,"<> ○") =COUNTIF(J6:J65536,"<> ○") =COUNTIF(K6:K65536,"<> ○") =COUNTIF(L6:L65536,"<> ○") と関数を設定しましたが、うまくいきません。 また =COUNTBLANK(I6:I65536) でも試してみましたが、データの入力の無い空白セルもカウントして駄目みたいです。 どなたかご教授を!
- ベストアンサー
- オフィス系ソフト
- 出勤簿のエクセル関数の作り方について
現在ある出勤簿のデータから、出勤時間の計算を自動で行いたいのですが 関数がうまく設定できなかったため、ご教示いただければ幸いです。 データは下記のようになっています。 ------------------------------- A列 B列 C列 D列 04/02(月) 09:00~19:00 09:00 9:00 04/03(火) 09:00~18:00 09:00 8:00 04/04(水) 09:00~21:30 09:00 1:30 04/05(木) 09:00~21:30 09:00 1:30 04/06(金) 09:00~20:00 09:00 0:00 …以下同様に続く ------------------------------- こちらのページ(http://www.enjoy-web-jp.com/jp/excel/sample/excel13.html)を参考に B列の情報を出勤時間と退社時間に分けようとして、 C列=LEFT(C3,5) D列==RIGHT(C3,5) としたのですが、C列はうまく表示できたのですが、 D列が上記のようになってしまい、うまく表示が分けられませんでした。 セルの書式設定は、「標準」「時刻」いずれでも上記のようになります。 原因が分かる方がいらっしゃいましたら、ご教示いただければ幸いです。 何卒よろしくお願い申し上げます。
- ベストアンサー
- その他MS Office製品
- Excelの関数を複数まとめて
Excelの関数のまとめ方について質問させてください。 A列 B列 C列 1 10 2 11 3 12 1 10 1 11 3 空白値(値なし) A列が1で且つB列が10の場合はC列にaと表示 A列が1で且つB列が11の場合にはC列にbと表示 A列が3で且つB列が空白(スペース)の場合はC列にcと表示 条件に合致しない場合は、B列の値をそのまま表示 というような複数条件判定の関数を一列内に纏めて計算させる場合には どのようにすればよいのでしょうか? ご教授の程宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセル関数でこんな判定?
A1:B20にリストがあります。 A列は文字列です。 B列は数値です。 質問1 A列に入力があり、同行のB列が空白または0というものが1つでもあるかどうかを判定する関数を教えてください。 質問2 B列に入力があり、同行のA列が空白というものが1つでもあるかどうかを判定する関数を教えてください。 多分SUMPRODUCTを使うのだとは想像するのですが、手がでません・・・・。(泣)
- ベストアンサー
- オフィス系ソフト
- 時間計算 エラー表示を無くしたい
出勤簿 A B C D 1 出勤時刻 退社時刻 勤務時間 2 月 18:00 0:00 6:00 3 火 20:00 2:00 6:00 4 水 #VALUE! 5 木 18:00 0:00 6:00 6 金 18:00 0:00 6:00 7 土 18:00 2:00 8:00 8 日 18:00 2:00 8:00 9 合計 #VALUE! 出勤時刻と退社時刻は別シートのタイムカードを参照してあります。 タイムカードの空白を参照した場合に出勤簿の時刻欄も空白にしたいため =IF(タイムカード!B4=0,"",タイムカード!B4) と入れてあります。 D列には =IF(C4-B4<0,1+C4-B4,C4-B4) という関数を入れてあります。 空白欄を参照した場合の D4 の#VALUE!表示をさせず、合計値を(この場合は40:00)表示させる方法はないでしょうか? よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 関数によって空白したのを数値「0」に置き換える
A.............B...............C 1 ZZ............6..............× 2 AA........... 1 3 BB 4 CC............3 B列にはそれぞれ関数が入っています。 セルB1には、B2、B3、B4の数値の合計です。 C1のセルには、C1数値とB2、B3、B4の数値の合計が一致しているか否かの判定する関数が 入っており、一致していなければ、"×"が表示させるようにしています。 しかし、ここで問題が起きました。 「VALUE」というエラーが表示されました。 上記の表では、セルB3は、空白になっております。 本来は、セルが空白になっている場合は、「0」が格納されていると思うのですが、 ここには、関数によって、文字列?の「空白」に置き換えられています。 例えば = IF((Z5) ,5, "") ようにです。 多分、関数によって文字列として認識される空白("")を置いたためと思います。 関数によって置き換えた空白("")を数値「0」として認識できる関数を教えてください。 いろいろな方法があると思いますが、「関数」のみで教えてください。 C1には、以下の関数が入っています。 =IF((B1) = (B2+B3+B4) ,"○","×")
- ベストアンサー
- オフィス系ソフト
お礼
ありがとうございます。 2通りとも 上手くいきました。 何回も考えていただき感謝します。 私の力では、まだまだ 条件や数式が、重なると、どのような組み立てしたらよいか 分からないので、 今回の2つの関数も含めて、違いなどを勉強したいと思います。 お力添えありがとうございました。