SUMIF / SUMIFS 関数
SUM関数といえば、数値を合計する関数です。また、IF関数は「もし○○だったら～」のように条件分岐をする関数です。両者が合体したSUMIF関数は、ある条件に一致する値だけを合計する関数です。SUMIF関数は、条件をひとつしか指定できませんが、SUMIFS関数は複数の条件を指定できます。
SUMIF関数
SUMIF関数の書式は、次のとおりです。
SUMIF(検索範囲, 条件, 合計範囲)
まずは、最も簡単なケースをご覧ください。
セル範囲A2:A10のセルが、条件に指定したセルD1に入力されている"田中"と等しいとき、合計範囲B2:B10のうち同じ位置にある数値だけを合計します。"同じ位置にある"のように曖昧な言い方をしないで、シンプルに"同じ行の"セルを合計しますと書きたいところなのですが、実は次のような使い方もできるので、必ずしも"行"とは限りません。
あまり知られていませんが、条件にはワイルドカードを指定できます。
「=SUMIF(A2:A10,"田中",B2:B10)」のように指定した"田中"は、「"=田中"」("田中"と等しい)という意味です。「"田中"」のように、条件(ここでは田中)の前を省略して何も付けていないときは、"="とみなされます。省略しないで、明示的に指定すれば「田中ではない」のような条件も指定可能です。
条件がセルに入力されている場合は、次のように"<>"という記号と、セルを結合させます。
条件に空欄("")を指定することで、ブランクセルを対象にできます。
上図で、セルD1は条件に空欄("")だけを指定しています。しかし、上で解説したように、"="などの記号(比較演算子)を何も指定しないと"="が指定されたものとみなされるので、だったら"="だけを指定したセルD5も同じ意味になります。これは、いわば「何もない と等しい」という意味です。したがって、セルD9のように指定すれば「何もない と等しくない」つまり、何らかの値が入力されているセルだけを特定できます。
条件で判定できるのは、文字列だけではありません。次のように、数値の大小比較も指定可能です。
条件がセルに入力されているときは、先と同じように文字列結合します。
なお、上図のように「検索範囲」と「合計範囲」が同じときは、3番目の引数「合計範囲」を省略できます。引数「合計範囲」を省略すると、引数「検索範囲」に指定されたセル範囲を合計します。
Excelは日付(と時刻)を、シリアル値として扱っています。ユーザーがセルに日付を入力すると、実際にはセルにシリアル値(数値)が入力されます。したがって、日付を条件にするときも「より大きい(以降)」や「より小さい(以前)」を指定できます。
日付を条件にするとき、日付の指定に関しては、かなり寛容です。
A列にはシリアル値が入力されています。セルD2では日付を文字列形式で指定しました。また、セルD6ではDATE関数を使っていますので、日付をシリアル値で指定しています。どちらも問題ありません。セルD10とセルD14では、シリアル値の実体である数値を指定してみました。文字列形式で指定しても、数値として指定しても、いずれにしても正しく計算されています。
今度は、A列に文字列形式で日付を入力してみました。どの指定方法でも、まったく問題ありません。正しい計算結果が表示されています。余談ですが、このSUMIF関数とCOUNTIF関数は、こうした日付の扱いに関して、Excelトップクラスの柔軟性を持っています。ほかの関数や機能などでは、「文字列で指定してはダメ」とか「シリアル値で指定しないと正常に動作しない」など、それぞれ、いろいろな制約や決まりがあります。対して、SUMIF関数とCOUNTIF関数での日付の扱いは、本当に、何というかMicrosoftの本気を感じる素晴らしさです。
セルに日付(シリアル値)が入力されていて、条件にシリアル値を指定できるのですから、下図のように、セルの表示形式と、条件に指定する日付の表記が異なっていてもかまいません。下図セルD2をご覧ください。
ただし、注意していただきたいのは、上図のセルD18です。A列に表示されている文字列が「2022年1月16日」で、条件に指定したのが文字列形式の「"2022年1月16日"」です。これは、セルに「表示されている文字列」を指定したのではありません。たとえば、セルに「2022/1/16」という文字列を入力すると、Excelはこれが日付であると認識します。そこで、セルにはシリアル値を入力して、日付の表示形式を設定します。Excelが"これは日付である"と認識するのは、いくつかの決まりがあります。「2022/1/16」や「2022年1月16日」などは日付と認識されますが、曜日まで含めて「2022年1月16日(日)」のように入力すると、Excelはこれを日付と認識しません。実際に、セルに入力してみれば分かります。上図セルD18が正しく計算されているのは、SUMIF関数内で指定した"2022年1月16日"を、Excelが日付と認識して、この文字列を一度シリアル値に変換しているからです。下図セルD22のように指定すると、たとえA列に表示されている文字列と同じであっても、この"2022年1月16日(日)"という文字列をExcelが日付と認識できないので失敗します。
では最後に、下図のような日付のうち「2月の数値」だけを合計するケースを考えてみましょう。
次のように考えると失敗します。
2月の日付ということは、表示されている文字列のうち「2022/2/」までは固定で、最後はどんな数値でもいいのだから、ここはワイルドカードを使って…みたいな発想です。これ、2つの間違いを犯しています。まず、ワイルドカードはセルに文字列が入力されていてるときにしか使えません。今回のA列には日付が入力されています。文字列ではありません。だからワイルドカードは使えません。2つめの間違いは、先にも解説したように、SUMIF関数の条件は「表示されている文字列」で考えるのではなく、「入力されているシリアル値」を指定しなければならないからです。こんなときは、次に解説するSUMIFS関数を使います。
SUMIFS関数
SUMIF関数は、条件をひとつしか指定できませんが、SUMIFS関数関数は複数の条件を指定できます。書式は次のとおりです。
SUMIFS(合計範囲, 検索範囲1, 条件1, 検索範囲2, 条件2 ...)
引数「検索範囲」と引数「条件」はセットで指定します。最大で127セット指定できるらしいです。
では、このSUMIFS関数を使って、先の「2月の日付だけ合計する」を、やってみましょう。
オートフィルタには「2月で絞り込む」という機能があります。
こうした機能が、あらかじめ用意されているのは希です。一般的にExcelで「2月だけで」のように期間を指定するときは、次のように考えるのがセオリーです。
まず、最も重要であり、かつ最も忘れがちなポイントです。日付というのは"時系列"などと呼ばれるように、連続している概念です。別に、"2月16日"というモノが存在するわけではありません。もうひとつ、日付に関する質問を受けていて感じるのが「年月日すべてを意識していない」という点です。たとえばですけど「ここに2月16日って指定したけど動かないんです」みたいな話で、私が「それってさ、年を指定していなくね？」って言われて初めて「あっ！」みたいな。特に"今年の日付"だけとか、○年の日付だけを扱っているとき、ついつい年のことを忘れがちです。どの年にも属さない2月16日なんて存在しません。当たり前ですよね。
そもそも、"2月の日付"って、どういうことでしょう。2月の日付というのは「2月1日以降」であり、なおかつ「2月28日以前」である日付です。したがって、2月の日付を特定するのなら「"2022/2/"で始まる」とかじゃなくて、この2つの条件を指定します。
これを、SUMIFS関数で指定します。
まぁ、これだけなら簡単なんですけど。もう少し、実務的な話をしましょうか。実務では、毎回「2月の日付」みたいに特定できるケースばかりじゃありません。2月の"2"だけ与えられて、これで何とかしろ、みたいなことも多いです。
この場合、もちろんセルD1の"2"を"3"に変えたら、3月の日付を合計したいです。こうなると難易度も上がります。ここで考えるべきは2つです。
- 月初の日付をどうやって特定するか
- 月末の日付をどうやって特定するか
なお、ここでは話を簡単にするため、年は"2022"だとします。月初は楽勝ですね。月初というのは、もちろん1日です。したがって、日は"1"です。年月日3つの数値が決まれば、そこから日付を作れます。使うのはDATE関数です。
問題は、月末の日付です。言うまでもありませんが、月末の日付は毎月異なります。こんなとき使うのがEOMONTH関数です。EOMONTH関数の詳しい解説は、下記ページをご覧ください。
さて、EOMONTH関数で最初の引数には「開始日」を指定します。まぁ、要するに日付です。実を言うと、ここにはどんな日付を指定してもいいのですが、ここはシンプルに考えましょう。今回知りたいのは「2月の月末日」です。もっと正確に言うと「セルD1で指定された月の月末日」です。だったら、EOMONTH関数の引数「開始日」にも2月の日付を指定する、と考えるのが普通でしょう。先に月初の日付をDATE関数で調べましたので、ここでもそれを指定すればいいです。
こうして求めた月初の日付と月末の日付に、不等号などの記号を文字列結合します。
=SUMIFS(B2:B10,A2:A10,">="&DATE(2022,D1,1),A2:A10,"<="&EOMONTH(DATE(2022,D1,1),0))
実務では、単純に数値を合計するだけでなく、ある条件に一致する数値だけを扱うことが多いです。そんなときに大活躍するのがSUMIF関数です。意外なことに、SUMIF関数をご存じの方でも、SUMIFS関数は知らないと言われることもあります。ここは、セットで覚えましょう。条件がひとつだったらSUMIF関数、条件が複数だったら、複数形の"S"がついているSUMIFS関数です。