【エクセル関数の作り方】SUMIFで条件にあう数値を合計する

    SUMIFという名前から、SUM関数の仲間であることがわかります。SUMと付く関数はSUM関数、SUMIF関数、SUMIFS関数、SUMPRODUCT関数などがあります。今回は、さまざまな場面で使えるSUMIF、SUMIFS、SUMPRODUCT関数に関してまとめました。



    SUM関数とは

    まず、SUM関数のおさらいから。SUM関数はエクセルのメニューバーにオートSUMというボタンがある位エクセルで一番使われている関数です。関数の式は「=SUM(引数,引数)」で、引数同士を加算する関数です。

    また、オートSUMで使われるように「=SUM(A1:A10)」というように、セルA1からA10の範囲内の数値を合計することも出来ます。ちょっと捻ったところで「=SUM(A1:A10,B1:B10)」とすれば、セルA1からA10の範囲内の数値合計とB1:B10の数値合計を合算することも出来ます。

    条件に合ったものだけを合計するSUMIF関数

    SUM関数は範囲内の数値をすべて合計しますが、SUMIF関数は条件に合ったものだけを合計します。例えば正の数値のみを合計するとか、50以上100以下の数値を合計するという時に活躍する関数です。関数の書き方は、「=SUMIF(検索対象範囲,検索条件,合計範囲)」となります。簡単な例を示します。

    期末テストの結果から田中さんの4科目の合計得点を計算するという内容です。合計得点を表示するセルC15にSUMIF関数の式「=SUMIF(A3:A14,”田中”,C3:C14)」が入っています 検索対象範囲がA3:A14の名前の並んでいる範囲で、検索条件が田中さん”田中”、合計範囲がC3:C14の各科目の得点が並んでいる範囲です。

    もう一つ、合計範囲を省略できる例です。下図は、B列の数値のうち正の数のみを合計するというSUMIF関数式がセルB5に入っています。

    検索対象範囲が合計範囲と同じ場合、「=SUMIF(B1:B4,”>0″)」の式のように3番目の引数は省略可能です。

    複数の条件を指定できるSUMIFS関数

    田中さんの旅費交通費の合計を出したいといった複数の条件を指定して合計したい場合は、SUMIFS関数を使います。(※SUMIFの最後にSが付いていますので間違わないように) SUMIFS関数はエクセル2007以降に組み込まれました。それ以前は複数条件を指定して合計するには、後で紹介するSUMPRODUCT関数を利用していました。

    関数の書き方は、SUMIFは検索対象範囲が一番最初に来ましたが、SUMIFSでは「=SUMIFS(合計範囲,検索対象範囲1,検索条件1,検索対象範囲2,検索条件2)」のように 合計範囲が一番目の引数となります。

    下図をご覧ください。

    1月の社員の経費の中から田中さんの旅費交通費の合計を出しなさいという例です。田中さんの旅費交通費の合計を計算する関数「=SUMIFS(D2:D12,B2:B12,”田中”,C2:C12,”旅費交通費”)」がセルD14に入っています。

    合計範囲がD2:D12で、検索対象範囲1がB2:B12、検索条件1が田中さん、検索対象範囲2がC2:C12、検索条件2が旅費交通費になります。

    SUMPRODUCT関数とは

    SUM関連の関数で最後に紹介するのがSUMPRODUCT関数です。この関数は万能でかなり役立つ関数なのでぜひとも習得してほしい関数の一つです。SUMPRODUCT関数は一つの関数でありながら、SUM関数、SUMIF関数、SUMIFS関数、さらにはVLOOKUP関数のような使い方も出来ます。具体的には、単純な合計、条件に合ったものを合計、複数条件に合ったものを合計、条件検索をこなしてくれます。

    エクセルのヘルプにはSUMPRODUCT関数は、「範囲または配列の対応する要素の積を合計した結果を返します。」とあります。難解な文章ですね。
    例をあげてみてみましょう。

    この表はある日のコンビニの売り上げ表です。商品名、単価、数量と並んでいます。SUMPRODUCT関数では商品ごとの単価×数量を計算したものの合計を出してくれます。 式は「=SUMPRODUCT(B2:B8,C2:C8)」で、単価の範囲と数量をそれぞれ引数に指定しただけの非常にシンプルな式です。

    続いて、条件を付けた場合です。下の表からポテトチップスの3日間の売り上げ合計を出してみます。

    この計算をするSUMPRODUCT関数式は、「=SUMPRODUCT((B2:B22=”ポテトチップス”)*C2:C22*D2:D22)」です。B2:B22の範囲でポテトチップスを探してきて、ポテトチップスに対応する単価と数量をそれぞれC列、D列から選択し積をして合計しなさいという式になります。

    複数条件に合ったものを合計する場合です。「=SUMPRODUCT((検索範囲1=検索条件1)*( 検索範囲2=検索条件2)*合計範囲)」と書きます。SUMIFS関数がエクセル2007で実装されるまでは、SUMPRODUCT関数を使っていました。SUMIFS関数で使った1月の社員の経費の中から田中さんの旅費交通費の合計を出しなさいという例をSUMPRODUCT関数で計算してみました。

    この計算をするSUMPRODUCT関数の式は、「=SUMPRODUCT((B2:B12=”田中”)*(C2:C12=”旅費交通費”)*D2:D12)」となります。

    まとめ

    今回は合計=SUMに関する関数を紹介しました。関数を勉強すると、関数というのはひとつの関数で利用するのではなく、いろいろな関数を組み合わせて利用するほうが便利だという事がわかってきます。実際に、関数で出した結果がエラーを出した場合の回避で、IFERROR関数を使ったり、VLOOKUP関数で作成した表から今回紹介したSUMIFやSUMIFS関数を使って条件に合うものを合計するなど、複数の関数を組み合わせて利用する事の方が多いことがわかります。

    特に今回紹介した関数は他の関数と組み合わせて使う頻度が多い関数の一つです。是非習得してエクセルマスターを目指しましょう。

    この記事が気に入ったら
    いいね ! しよう



     トイロハのおすすめ記事