SUM系関数
SUM関数には他にも「SUM〇〇」といったようにSUM系の関数が複数あります。ここでは他のSUM系関数をご紹介していきます。
まずは基礎知識としてご紹介し、別の記事で詳細にご紹介していこうと思いますので、まずはSUM系関数の基礎知識を覚えていきましょう！
練習用エクセルファイルを用意したので、必要な方は下記よりダウンロードしてくださいね。
SUM関数
数値を合計する関数です。
連続したセルはもちろん、離れたセル同士や、連続したセルと離れたセルの合計なども可能です。
下記で、セルB1にSUM関数を入れていきたいと思います。まず = を入力し SUM と入力後に Tab キーを押します。この時、入力文字から候補が出るのでスペルを覚えていなくても候補の中からSUMを選んで Tab キーを押せば大丈夫です。
第1引数の数値1に合計を開始したいセルを入力したら ： を打ち、続けて第2引数の数値2に合計を終了するセルを入力したら Enter を押して完了です。今回は開始セルをD9、終了セルをD55とします。
- マウス操作で選択する場合
D9からD55までをマウスで範囲選択すれば自動的に ： でつないでくれます。 - キーボード操作で選択する場合
D9セルを選択した状態で、範囲選択のショートカット Shift を Ctrl を押しながら ↓ を押して最終行まで選択すると、自動的に選択した範囲を ： でつないでくれますので、最後に Enter を押せば合計が表示されます。
=SUM(集計範囲)
=SUM(D9:D55)
SUBTOTAL関数
集計の種類を指定して集計・合計・平均などしてくれる関数です。
フィルターなどで選択範囲が可動しても、連動して集計範囲を選択してくれる便利な関数です。今回はSUM系関数なので、SUBTOTALの中でも集計方法を SUMを使用した場合をご紹介します。
合計したい数値はD列になるので、今回はひとまずD7セルに数式を入れていきます。 = を入力し、SUBまで入力すると候補はかなり絞られるので、候補の中から「SUBTOTAL」を選択し Tab キーを押します。
続けて次の候補が出てきて「どの方法で集計しますか」と聞かれるので、ここでSUMを選びます。もしくはキーボードの 9 を押してからカンマ , を押しても大丈夫です。
そのあとは、普通のSUMと同じく合計する範囲を選択して Enter を押せば完了です。A1セルのSUMの結果と一致しましたね。
SUBTOTALの使用方法については、この後のSUMIF関数やSUMIFS関数の検算に使用したいので、一旦このまま次に進みます。各関数の検算の項目で詳細をご紹介しますね。
=SUBTOTAL(集計方法,集計範囲)
=SUBTOTAL(9,D9:D55)
SUMIF関数
条件を指定して数値を合計する関数です。
下記の表でセルB2に男性の利用金額、セルB3に女性利用金額と条件を指定して合計を出していきたいと思います。
まずセルB2に男性の利用金額と条件を指定して合計を出します。 = を入力した後にSUMIFと入れますが、SUまで入力すると候補はかなり絞られるので、候補の中から「SUMIF」を選択し、キーボードの Tab キーを押します。
次に「範囲」を選択しますが、この「範囲」とは次の条件「男」を検索する範囲のことになります。今回で言うと、セルC9～C55までが選択範囲になります。
このあとB3セルに数式をコピーするので、忘れずに絶対参照にしておきましょう！そしてカンマ , を打って条件が入っているA2を選択してカンマ , を打ちます。
範囲を選択の際、必ず F4 キーを押して絶対参照にするのを忘れないように要注意！
絶対参照が分からないという人は、絶対参照について紹介している記事と動画があるので、参考にしてください。絶対参照の記事はこちら
動画は下記をご視聴ください。
次に合計したい範囲を選択します。今回はD列の利用金額を集計したいので、セルD9～D55を選択し、ここでも忘れずに絶対参照にしておきましょう。
最後に Enter を押せば完了です。B2セルには9行目以降の表にある男性の利用金額の合計が出ました。
では、先ほどSUBTOTAL関数を入力した結果で検算をしてみましょう！
まず性別と書かれたC8セルのフィルターをクリックして「すべて選択」のチェックを外し、男のみチェックを入れOKをクリックすると、D7セルには男性のみの利用金額の合計が表示されましたね。
B2セルの合計はフィルターをかけても影響を受けず、表全体の合計が表示されたままですが、SUBTOTAL関数は選択範囲にフィルターがかけられると、そのフィルターに紐付いて合計も再集計されます。
上記と同じ要領でB3セルに女性の合計を出してみましょう。
B3セルを選択し「上のセルをコピペするショートカット」の Ctrl と D を押してB2セルの数式をコピペします。B2セルに数式を入れる時に範囲を絶対参照にしていないと、ここで範囲がずれてしまい正しい集計ができないので、忘れずに絶対参照をしましょう！
先ほどと同じくSUBTOTAL関数を入力した結果で検算をしてみましょう！
まず性別と書かれたC8セルのフィルターをクリックして「すべて選択」のチェックを外し、女のみチェックを入れOKをクリックすると、D7セルには女性のみの利用金額の合計が表示されましたね。
また、条件を入力するときに、今回は「男」「女」と入力したセルA2、A3セルを選択しましたが、条件をテキストで入力しキーボードの Shift と 2 を押してダブルクオーテーション 囲んで “男” や “女” と入力しても大丈夫です。
=SUMIF(条件検索範囲,条件,集計範囲)
=SUMIF($C$9:$C$55,A2,$D$9:$D$55)
=SUMIF($C$9:$C$55,"男",$D$9:$D$55)
SUMIFS関数
複数の条件を指定して数値を合計する関数です。
先ほどのSUMIF関数も条件を指定して数値を合計してくれますが、指定できる条件は1つのみです。このSUMIFS関数は複数の条件を指定できるので条件複数ある時はSUMIFS関数を使用しましょう。
下記の表を使用して5行目に各月の男性の利用料金を集計していきます。
まずB5セルに1月の男性の利用料金の合計を出すよう関数を入力していきましょう。
= を入れた後に SUMIFSと入れますが、SUまで入力すると候補はかなり絞られるので、候補の中から「SUMIFS」を選択し Tab キーを押します。
次に「合計する範囲」を選択します。
今回は利用金額を合計するので、D9～D55が範囲となります。そして後でC列以降と6行目にコピペをするので、忘れずに絶対参照しておきましょう。絶対参照って何？？って思われた方は、SUMIF関数のところで動画を掲載したので、ぜひ確認してくださいね！範囲を選択したらカンマ , を打ちます。
次に条件を指定する範囲を選択します。
今回は「男性」の「各月」なので、C9～C55を選択し絶対参照にしてからカンマ , を打ちます。そして指定する条件を入れます。
先ほど指定した範囲は「性別」なのでA5セルを選択したら、あとでA6にコピぺをするので「列は絶対参照、行は相対参照の複合参照」にし、カンマ , を打ちます。
次に2つ目の条件範囲を指定します。2つ目の条件「各月」の情報が入っているA列が対象なので、範囲はA9～A55になります。ここも絶対参照にしてからカンマ , を打ちます。
そして2つ目の条件であるB4セルの1月を選択します。この時、列は動いてほしいけれど行は固定にしたいので「列は相対参照、行は絶対参照の複合参照」にします。今回は条件は2つだけなので最後に Enter を押して完了です。
これで1月の男性の利用金額合計が表示されましたね。B5セルの右下角にある緑の■（フィルハンドル）にマウスを合わせ、マウスが＋に変わったら12月まで引っ張り、オートフィルをしてコピペをします。
オートフィルで12月までコピーできましたね。
今オートフィルで12月までコピーした直後で範囲選択されている状態なので、M5セルの右下角にある緑の■（フィルハンドル）にマウスを合わせ、マウスが＋に変わったら1行下まで引っ張り、オートフィルで6行目にコピペをします。
6行目にも数式が反映されましたね。
では最後に検算をしてみましょう。
複数あるので1つずつ検算していると効率が悪いので、まとめて検算します。まず男性の検算をします。セルB5～M5を範囲選択します。右下の合計とB2セルの男性合計が同じ1,026,000円で一致したので間違いないですね。
ここの数字が違う場合、数式が間違っている可能性があるので、絶対参照になっているか、選択範囲や条件に間違いがないか確認しましょう。
女性の行も同じ方法で、セルB6～M6を範囲選択し、右下の合計とセルB3の女性合計が同じ1,266,000円で一致したので間違いないですね。
ここの数字が違う場合、数式が間違っている可能性があるので、絶対参照になっているか、選択範囲や条件に間違いがないか確認しましょう。
=SUMIFS(集計範囲,条件検索範囲①,条件①,条件検索範囲②,条件②)
=SUMIFS($D$9:$D$55,$A$9:$A$55,B$4,$C$9:$C$55,$A5)
まとめ
SUM系関数の基本的な使用方法をいくつかご紹介しました。
お好みの部分もあると思いますが、私は個人的には条件指定はセルを指定するよりも、文字をダブルクオーテーションで囲む “●●” のスタイルの方が好きです。後々メンテナンスで修正やエラーを探すときにセルよりも探しやすいのが理由ですが、好みの問題かと思うので、どちらでも良いと思います。
ただし、セル指定の時は、相対参照・絶対参照・複合参照を決めて入力し忘れると正しく集計できなくなるので、忘れないように気を付けましょう。
動画でもご紹介していますので、ぜひ動画も確認しながらマスターして下さい。
励みになります！高評価とチャンネル登録よろしくおねがいいたします。
