この記事はエクセルで日時の情報とあわせて収集したデータを分析する際に
○○日から○○日の合計、または○○時から○○時の合計を知りたい場合に有効です。
日時の範囲を高頻度で変更したい場合、通常のSUM関数では毎回参照範囲を変えるといった作業が発生し、とても手間になります。
しかし別のセルに算出開始日時と算出終了日時を入力して
自動でその範囲間の合計を求めてくれたら便利ですよね。
今回はそんな悩みを解決できるような内容になっています。
それでは見ていきましょう。
■こんなことができるようになる!
・指定した2つの日付の範囲に当てはまる合計を算出することができる。
⬛︎指定範囲間のデータの合計を算出する
通常であれば合計を求める際には「 SUM関数」と呼ばれる関数を使用します。
SUM関数についてはこちらから☟
【SUM関数】エクセルで範囲を選択し、合計（総和）を関数を使用して求める方法 ► 独学エクセル塾 (dokugakuexcel.com)
しかし今回は指定範囲で絞り込むので SUMIFS関数を使用していきます。
SUMIFS関数についてはこちらから☟
【SUMIFS関数】エクセルで複数の条件に当てはまる合計を求める ► 独学エクセル塾 (dokugakuexcel.com)
では内容を見ていきましょう
・２つの日付の範囲内の合計を求める
見本として下図のような収益のデータを用意しました。
B列には日付のデータが入っておりC列には収益額が入力されています。
今回はE3とF3に入力した日付の間の合計を求めてみましょう。
SUMIFSの関数の仕組みはこのようになってます。
=SUMIFS(合計したい範囲,合計の条件範囲1,合計の条件1,合計の条件範囲2,合計の条件2,・・・)
合計したいの範囲は収益なので収益の範囲のC3からC12
合計の条件の範囲1は日付で絞り込みたいので日付の範囲のB3：B12
合計の条件1はE3以上の日付を合計したいので“>=”&E3
合計の条件の範囲2も日付で絞り込みたいので日付の範囲のB3：B12
合計の条件2はF3以下の日付を合計したいので“<=”& F3となります。
では当てはめていきましょう。
関数はこのようになります。
=SUMIFS(C3:C12,B3:B12,“>=”&E3,B3:B12,“<=”&F3)
それでは入力していきましょう。
結果はこのようになります。
5月27日から5月30日の合計を求める事ができました。
該当する収益は「735」「342」「245」「657」で合計は1979なので
狙い通りの動作ができていますね。
もちろん、日付を変更しても変更した日付の範囲で合計をしてくれます。
・２つの時刻の範囲内の合計を求める
今度は入力した2つの時刻の範囲の合計を求めてみましょう。
今回も先ほどの見本を参考にしていきましょう。
まず、前提として条件となる日時のデータに時刻の情報が含まれている必要があります。
数式は先ほどと同様になります。
=SUMIFS(C3:C12,B3:B12,“>=”&E3,B3:B12,“<=”&F3)
では絞り込みの条件を14時から19時にしてみたいと思います。
結果はこのようになります。
時刻でも同様に２つの時刻範囲の合計を求める事ができました。
該当する収益は「735」「342」「245」「657」「874」「584」で合計は3437なので
狙い通りの動作ができていますね。
⬛︎補足 ○○日・時間以上・以下のデータの合計を算出する
ここからは補足です。
先ほどは2つの日時の範囲間の合計を求めましたが、
今度は○○日・時間以上の合計・以下の合計を求めてみましょう。
・○○日・時間以上のデータの合計
まずは○○日・時間以上のデータの合計の算出方法から見てみましょう。
では再度、先ほどの見本データをご覧ください。
今回はE3に入力した日時以上の合計を求めたいとしましょう。
先ほどは SUMIFS関数を使用しましたが、今回は条件が1つなので SUMIF関数となります。
SUMIF関数の仕組みは下記の通りです。
=SUMIF(絞り込む対象の範囲,絞り込む条件,合計の範囲)
絞り込む対象の範囲は日時なのでB3：B12
絞り込む条件は F3に入力した日時以上なので“>=”&E3
合計の範囲は収益なのでC3：C12となります。
関数に当てはめるとこのようになります。
=SUMIF(B3:B12,“>=”&E3,C3:C12)
それでは入力してみましょう。
結果はこのようになります。
5月26日以上の合計は「5281」ということがわかりました。
・○○日・時間以下のデータの合計
今度は○○日・時間以下のデータです。
考え方は先ほどと同様で「“>=”」の部分が「“<=”」となります。
では見本を見ていきましょう。
今回はE3に入力した日時以下のデータの合計を見ていきます。
数式はこのようになります。
=SUMIF(B3:B12,”<=”&E3,C3:C12)
では入力してみましょう。
結果はこのようになります。
5月27日以下の収益の合計は「1997」ということがわかりました。
⬛︎補足 ２つの日付の範囲＋絞り込み条件
先ほどは2つの日付の範囲の合計を求めましたがさらに絞り込み条件を
増やしたい場合の方法を紹介します。
下図の見本をご覧ください。
先ほどのデータに加え、対象の項目をD列に追加しました。
仕様として2つの日付の範囲＋対象に○がついてあるものの合計をしたいと思います。
関数はSUMIFS関数を使用していきます。
SUMFS関数の条件は2つまでの縛りはなく、127個まで設定することができます。
では条件を追加した数式を見てみましょう。
=SUMIFS(C3:C12,B3:B12,”>=”&F3,B3:B12,”<=”&G3,D3:D12,”○”)
「D3:D12,”○”」を追加する事でD列に○がついてある条件が追加となりました。
それでは入力してみましょう。
結果はこのようになります。
5月26日から5月30日で対象に○がついてあるデータは「578」「342」「245」なので合計「1165」で
狙い通り計算できています。
今回は2つの日付データと対象の3つの条件でしたが
数式を追加する事で条件をさらに追加することができます。
⬛︎よくあるエラーと対処法
・条件に当てはまるが合計してくれない
こちらは対象日時または絞り込む際の条件の日時に問題があるかもしれません。
実際の画面をみてみましょう。
通常は「○月○日」といった表記ですが
ダブルクリックすると「年」までデータが入っています。
エクセルは日時と判断すると日付データとして年月日、
時刻を入力すると＋時刻までデータとして入力されます。
つまり「年」が異なっていると範囲から除外されるので合計してくれません。
一度日時データを確認してみてください。
・計算結果が0になる
日付範囲を設定する際に条件を「”>=”&対象セル」と設定しました。
この不等号の記号「<」と「>」ですが、全角と半角があります。
全角は「＞」「＜」で小文字は「>」「<」となってます。
見た目ではわかりにくいですが、全角で入力すると文字として認識され
不等号にならないので0となってしまいます。
入力の際は半角で入力してください。
⬛︎まとめ
いかがだったでしょうか。
日時とセットでデータを収集している方も多いとおもいます。
是非今回の内容を活用して
2つの日付間の合計を求めてみてください。
それでは次回の記事でお会いしましょう。
コメント