アンケート一覧ページでアンケートを探す

回答受付が終了しました

1051886407

2023/4/26 20:30

33回答

Excelの関数で、指定した時間帯を抽出し、合計時間を求める場合はどういった式になるでしょうか？ Aセルに開始時間、Bセルに終了時間をまとめたデータに対して、

Excelの関数で、指定した時間帯を抽出し、合計時間を求める場合はどういった式になるでしょうか？ Aセルに開始時間、Bセルに終了時間をまとめたデータに対して、 例えばC1に開始9:00、D1に終了10:00を入力したとき、AB間の該当時間を合計してE1に表示したい、といった使い方になります。 すみませんがご助力お願いしますm(_ _)m

Excel・110閲覧

新機能 AI回答テストを実施中！ テスト対象カテゴリ：歴史・悩み相談 ※回答がつかない場合は、画面のリロードをお試しください

回答（3件）

was********

2023/4/26 21:26

配列数式を使います。 D1 =SUM(IF(IF(B1:B5<D1,B1:B5,D1)-IF(A1:A5>C1,A1:A5,C1)<0,0,IF(B1:B5<D1,B1:B5,D1)-IF(A1:A5>C1,A1:A5,C1))) 必要なバージョンでは「配列数式として確定」します。 元になる数式は、時間を時刻区分で集計する定番の数式です。 =MAX(0,MIN(B1*1,D1)-MAX(A1,C1)) 作業列にコピーして行ごとに返し、それをSUM関数で合計しても良いです。 上記のように1つの数式で返すためには、「配列」を使います。 その際「MAX関数・MIN関数」は配列を返さないので、IF関数に置き換えます。 =IF(IF(B1<D1,B1,D1)-IF(A1>C1,A1,C1)<0,0,IF(B1<D1,B1,D1)-IF(A1>C1,A1,C1)) A1セル、B1セル参照を「配列」に書き換えて先の数式になります。

NEW! この回答はいかがでしたか？ リアクションしてみよう

Grumpy_the_Dwarf

カテゴリマスター

2023/4/26 20:47

開始時刻から終了時刻のうち、時刻1から時刻2の間に該当する時間は、 =max(0, min(終了時刻*1,時刻2)-max(開始時刻,時刻1)) 「終了時刻と時刻2の早い方から開始時刻と時刻1の遅い方を引き算。ただしマイナスにはしない」 というのが定番の数式になります。

ID非表示

2023/4/26 20:41

Excelで指定した時間帯を抽出し、合計時間を求める式は、以下のようになります。 =SUMIFS(B:B,A:A,">="&C1,A:A,"<="&D1)-C1+D1 この式の解説は以下の通りです。 SUMIFS関数を使用して、開始時間が指定された時間帯の範囲内にあり、かつ終了時間が指定された時間帯の範囲内にあるセルの合計値を求めます。 -C1+D1を追加して、指定された時間帯の範囲内にない開始時間と終了時間を足し引きします。 この式をE1セルに入力し、指定した時間帯の合計時間が表示されます。 なお、この式ではA列に開始時間、B列に終了時間があることが前提となっています。実際のセルの範囲や条件に応じて、A列やB列の範囲を変更し、条件を修正する必要があるかもしれません。