- ベストアンサー
時間帯を切り取って合計時間を算出したい
下のカッコ内はエクセルのセル番地を示しています。 2行目に数値を入れて任意の時間帯に何時間あるか調べたいと考えています。下では前日22時から当日9時までの合計11時間あり、その内訳は時間帯Aに9時間、時間帯Bに2時間ある、という計算です。 こういう計算を関数を使って表すことはできるでしょうか? 開始(B1) 終了(C1) 合計(D1) 時間帯A(E1) 時間帯B(F1) 22:00(B2) 9:00(C2) 11:00(D2) 9:00(E2) 2:00(F2) ※時間帯A=前日22:00~当日7:00 時間帯B=当日7:00~12:00
- wait4u
- お礼率45% (619/1365)
- オフィス系ソフト
- 回答数4
- ありがとう数7
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
例えば開始が6:00、終了が23:00であった場合は E2は6:00~7:00の1時間と22:00~23:00の1時間を足して2時間 F2は7:00~22:00の15時間 でいいのですよね? D2は =IF(B2>C2,C2+1-B2,C2-B2) E2はちょっと複雑ですが =IF(AND(ISBLANK(B2)=FALSE,ISBLANK(C2)=FALSE),IF(C2<B2,IF(C2>"7:00"*1,"7:00"*1+1,C2+1)-IF(B2<"22:00"*1,"22:00"*1,B2)+IF(B2<"7:00"*1,"7:00"*1-B2,"0:00"*1),IF(B2<"7:00"*1,IF(C2<"7:00"*1,C2,"7:00"*1)-B2,"0:00"*1)+IF(C2>"22:00"*1,C2-"22:00"*1,"0:00"*1)),"0:00"*1) E2の式の意味は以下の通りです。 開始時刻>終了時刻となった場合(0:00をまたいだ場合) 終了時刻+1日と開始時刻の差を求め、その内の22:00~7:00の間の時間数を算出 上記以外の場合(開始時刻≦終了時刻、0:00をまたがない場合) 開始時刻が7:00以前、終了時刻も7:00以前の場合、開始時刻から終了時刻までの時間数 開始時刻が7:00以前、終了時刻が7:00以降の場合、開始時刻から7:00までの時間数 終了時刻が22:00以降の場合、22:00から終了時刻までの時間数 これを合算したもの F2は =D2-E2
その他の回答 (3)
- spring57
- ベストアンサー率0% (0/1)
D2のセルに=(24-B2)+C2の関数式を E2のセルに=C2を F2のセルに=(24-B2)の関数式を入力して見て下さい。
お礼
ありがとうございました。
- KURUMITO
- ベストアンサー率42% (1835/4283)
D2セルには次の式を入力します。 =IF(COUNT(B2:C2)<>2,"",IF(B2>"12:00"*1,C2+"24:00"-B2,C2-B2)) E2セルには次の式を入力します。 =IF(COUNT(B2:C2)<>2,"",IF(B2>"12:00"*1,MIN("7:00"+"24:00",C2+"24:00")-MAX("22:00",B1),MIN("7:00",C2)-B2)) F2セルには次の式を入力します。 =IF(COUNT(B2:C2)<>2,"",IF(B2>"12:00"*1,MIN("12:00"+"24:00",C2+"24:00")-MAX("7:00"+"24:00",B1),MIN("12:00",C2)-MAX("7:00",B1))) D2セルからF2セルまでの範囲を選択し下方にオートフィルドラッグします。 ところでD2セルの式ですが例えば開始が21:00で終了が9:00のときはD2セルは12:00となるのに対してE2セルは9:00、F2セルは2:00となります。これは時間帯Aの最大値が9:00であるためです。D2値がE2とF2の合計にするのでしたら、D2への入力の式は次のようになりますね。 =IF(COUNT(B2:C2)<>2,"",E2+F2)
お礼
ありがとうございました。 うまくいきました。関ここまでの関数はかなり勉強しないと難しいですね。
- DIooggooID
- ベストアンサー率27% (1730/6405)
B1セルには、前日の時刻が設定され、 C1セルには、当日の時刻が、 必ず設定されるのですか?
お礼
こんにちは、早速ありがとうございます。 B1、C1セルですが、前日、当日はまちまちです。左の値が右より大きい時は左は前日というようにできればと考えています。シリアル値ですので、こういったことは無理なのでしょうか・・・
関連するQ&A
- 時間を自給として計算してかつ個別に合計を出すには?
A B C D E F G 1担当 始まり 終わり 合計 2Aさん 8:00 10:35 2:35 3Bさん 9:50 15:00 5:10 4Cさん 23:00 2:00 3:00 5Aさん 2:00 5:00 3:00 6 7 Aさん合計 Bさん合計 Cさん合計 勤務時間を打ち込むと給料も計算できるように考えているのですが、 担当者ごとに合計を分けて、そして時間給を5分単位で時間を計算したいんです。 たとえば私の自給900円として。 2行目の様にA列で担当者がAさんの時に2時間35分勤務した場合の金額の合計をAさんの場合はAさんの合計(B7)に、Bさんの場合はBさんの合計としたいのですが、条件での関数はいけるんでしょうか?
- 締切済み
- オフィス系ソフト
- 1日=465分の勤務時間の合計計算
お世話になります。 1日7時間45分勤務において、従事時間の合計を日数・時間・分で表示させたいのですが、 以下のような数式を入れて、時間計算しようとしたのですが、どうしてもうまくいきません。 例)従事時間合計が25時間15分の場合、3日2時間0分と表示させたい。 =SUM()で、表示形式を[h]:mmにして、合計従事時間数「25:15(セルA2)」を算出。 次に、総取得分にする・・・B2=A2*24*60=1515(以下表示形式はすべて:標準) 総取得分を日(465分)に変換する・・・C2=ROUNDDOWN(B2/465,0) 日数を除き残った分を計算する・・・D2=B2-C2*465 時間に変換・・・E1=D1/60 残分を計算・・・F1=D2-ROUNDDOWN(E2,0)*60 という、式を組み立てました。 表示させるセル D5=C2 (表示形式 #"日") E5=ROUNDDOWN(E2,0) (表示形式 #"時間") F5=F2 (表示形式 #"分") とすると、3日1時間60分となってしまいます。 ・・・3日2時間の表示にしたい。 従事時間合計が55時間15分の場合も、7日60分という表示になってしまいます。 ・・・7日と1時間にしたい。 よい方法はありませんでしょうか? よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- 時間の合計計算ができません。
給料計算の表なんですが、 A1 B1 C1 D1 E1 F1 日付 始業時刻 終業時刻 勤務時間 休憩時間 実働時間 と項目をつくりました。 終業時刻ー始業時刻=勤務時間とし、休憩時間は1時間と決まっているので、日付を入れた時点で自動入力されるように=IF(A1<>"","1:00","")といれました。すべてうまくいったと思ったんですが、E1のタテの合計が空白になってしまいます。 どう解消したらいいでしょうか? いい方法をご教授ください。お願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル・時間の合計が合わない
実労働時間の1ヶ月の合計をだすのに、SUMを使ったのですが、 合計がすごく小さい数字になります。 表示は※※:※※で表示しています。 表の内容は下記の通りです。 4行目に題名 ・B出勤時刻・C休憩開始・D休憩終了・E退社時刻 F4・I4・L4・N4・Q4=0:00 G4・J4・M4・O4・R4=17:00 F =IF($E5-F$4>0,$E5-F$4,0)-IF($B5-F$4>0,$B5-F$4,0) G =IF($E5-G$4>0,$E5-G$4,0)-IF($B5-G$4>0,$B5-G$4,0) I =F5-SUM(J5:$K5) L =IF($D5-L$4>0,$D5-L$4,0)-IF($C5-L$4>0,$C5-L$4,0) M =IF($D5-M$4>0,$D5-M$4,0)-IF($C5-M$4>0,$C5-M$4,0) N =L5-SUM(O5:$P5) O =M5-SUM(P5:$P5) Q =I5-N5 (実労働時間17時までの1日の計) R =J5-O5 (実労働時間17時からの1日の計) Qに=SUM(Q5:Q14) Rに=SUM(R5:R14) を入力しましたが、まったく違う時間数になります。 どうすれば正しい計算式がでるのでしょうか? お教え願いたくお願い申し上げます。
- ベストアンサー
- オフィス系ソフト
- Excelの表計算の方法(各セルの自然対数の合計と分数の合計の求め方)
A B C D E 1 0 2 1 3 まず、上記の表で、D1はA1からC1までの合計です。 ここでE1で = -A1/F1*LN(A1/F1)-B1/F1*LN(B1/F1)-C1/F1*LN(C1/F1) という特殊な演算(詳しくはエントロピーの計算です)をしたいと思います。 以下詳細な説明です。 A1/F1 → 分子が各セル、分母が合計 LN(A1/F1) → 上記の自然対数を求めます -A1/F1*LN(A1/F1) → 上の二つを掛けて、負にします -A1/F1*LN(A1/F1)-B1/F1*LN(B1/F1)-C1/F1*LN(C1/F1) → 行全ての合計 計算式は上記であっているのですが、2つ問題があります。 ・実際は列が多く、各セルの計算式を上記のように書くことはできません。まとめて関数を記述することはできないでしょうか? ・自然対数の計算(=LN(値))は、値に0が入ると、エラー(#NUM!) が出てしまいます。上記の計算式では、#NUM!が起こります。上手く値の0は飛ばして行全体の合計を出すことはできないでしょうか? 以上、ご教授の方、よろしくお願い致します。
- 締切済み
- オフィス系ソフト
- エクセルで合計を簡単に出すための方法を教えてください
| A | B |C| D | E | --+---+---+-+--+----+ 1 | | 金 額 | 時間計| --|名前 +---+-+--+----+ 2 | |○:○○|~|0:00| ○:○○| --+---+--------+----+ 3 | | 金 額 | 時間計| --|名前 +---+-+--+----+ 4 | |○:○○|~|0:00| ○:○○| --+---+--------+----+ ・ | | ・ | (省 略) | ・ | | ・ | | --+----+-------+----+ 50 | 合計 | 金額合計 |時間合計| ----------------------------- わかりづらくてすいません。B2-D2=E2となるような式を入力してあり、E2の時間の利用金額がB1という表なんですが、50行目で 金額合計を出す時に=B1+B3+B5+B7+・・・ 時間合計を出す時に=E2+E4+E6+E8+・・・ と入力する以外方法はないんでしょうか?書いてはいませんが、Fの列は時間は出さずに金額だけを表示するのでSUM(F1:F49)で金額合計が出ます。いい方法があれば教えてください。
- ベストアンサー
- オフィス系ソフト
- EXCELで時間の計算
EXCELで時間の計算をしたいのです。(和) A1→15 B1→30 C1→22 D1→00 A2→16 B2→30 C2→21 D2→00 A3→ 5 B3→00 C3→12 D3→30 と入力してあります。 A1とB1でひとつの時刻です。 つまり1行目は15:30と22:00です。 まずは後ろの時刻から前の時刻を差し引いたもの つまり1行目でいうところの22:00から15:30を引いたものの結果6時間30分を E1→6 F1→30とするにはどうしたらよいですか? またその合計 1行目→6時間30分 2行目→4時間30分 3行目→7時間30分を足したもの18時間30分を E4→18 F4→30とするにはどうしたらよいですか? そもそも時と分を分けたところに失敗があるのですが それはもうどうしようもありません。 それでは不可能なんでしょうか? E1、F1に入れる式、 E4、F4に入れる式を教えてください。 よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- 本日の合計を表示したい
A1に本日 E1にはAの 本日の日付の実績時間があります。 G1には Cの実績時間の合計が入っていますが、前倒しした12/16の分は入っていません あくまでも本日分の A B C Dの合計を出すには E1 E2 G1 G2にはどんな計算式が必要ですが?
- ベストアンサー
- その他MS Office製品
- 1列おきに合計、a1 + c1 + e1 …
一列おきにある数値の合計を求めるにはどうしたらよいでしょうか。 A1 に B1+D1+F1 …… の合計を求めたいのです。 C2、E2など合計したくないセルにも数値が入っています。 実際にはデータが一行につきデータが数十から200ぐらいあります。 A B C D E F 1 2
- ベストアンサー
- オフィス系ソフト
- 合計値を出したいのですが、難しいです。
計算式がすごいことになっていたので、改めて質問させてください。 すみません。 いつもお世話になっております。 SUMIFとMATCHを使って、1月から6月までのデータの合計を出したいのですがなかなかうまくいきません。。。 下記に貼り付けてみましたが、ちょっとわかりにくいですよね。。。 何をやりたいかというと、M1がリファレンスで、M5が月。 M21という項目にあるデータの1月から6月までの数字の合計を出したいのです。 一番下を見るとわかるのですが、3001の合計をSUMIFで出すことができます。 ただその場合、どの行か指定しないとなりません。(この場合C2:C14) それをMATCHで探し出してくれないかなと、やってみたのですが、どうやらうまくいきません。 どなたかお分かりになる方教えてください!!! Aセル Bセル Cセル Dセル M1 M5 M21 M735 3001 1 300 80 3001 2 500 60 3001 3 200 20 3001 4 100 4000 3001 5 10 960 3001 6 3 5 3002 1 700 62 3002 2 90 4 3002 3 160 3002 4 10 55 3002 5 15 56 3002 6 60 12 Fセル Gセル M1 M21 3001 =SUMIF(A2:D14,F3,C2:C14) 3002 =SUMIF(A2:D14,F4,match(G2,A2:D2,0))
- ベストアンサー
- オフィス系ソフト
お礼
ありがとうございました。 わたしにはちょっと複雑ですが、教えていただいたとおりいれたらできました。