Excelの覚える関数を減らすシリーズ①
SUMIFS関数を覚えればSUMIF関数は覚えなくて大丈夫!
↑動画版
目次
SUMIFS関数とは
指定された条件(複数でもOK)に一致するセルの値の合計を求める関数式です。
商品購入履歴を商品ごと男性・女性それぞれにいくら売れたかを集計することができます。
SUMIF関数とSUMIFS関数の違い
SUMIF関数
→指定された1つの条件に一致するセルの値の合計を求める
SUMIFS関数
→指定された条件(複数でもOK)に一致するセルの値の合計を求める
SUMIFS関数の式
式:SUMIFS(合計対象範囲,条件範囲1,条件1, [条件範囲2],[条件2])
それぞれの説明
合計対象範囲:合計の対象としたいセル範囲を指定
条件範囲1:条件1に一致する値のあるセル範囲を指定
条件1:条件範囲1内で一致するセルを指定
条件範囲2:条件2に一致する値のあるセル範囲を指定
条件2:条件範囲2内で一致するセルを指定
単一条件でのSUMIFS関数
商品名のみを条件に集計する。
SUMIFS関数の手順
①SUMIFS関数(単一条件)
入力するセルを選択します。
「=sum」と入力すると入力補助が表示されるので、「SUMIFS」を選択します。
②SUMIFS関数(単一条件)
SUMIFSを選択したら「(」を入力。
③SUMIFS関数(単一条件)
合計対象範囲を選択します。
F2:F19を選択します。
④SUMIFS関数(単一条件)
ファンクションキー「F4」を押して、絶対参照にします。
F2:F19 → $F$2:$F$19
これにより式をコピーして他のセルに貼り付けても、 F2:F19の範囲が必ず参照されます。
⑤SUMIFS関数(単一条件)
条件範囲1を設定します。
条件1に一致する(次に設定)対象となるセル範囲を指定します。
B2:B19を選択します。
⑥SUMIFS関数(単一条件)
ファンクションキー「F4」を押して、絶対参照にします。
B2:B19 → $B$2:$B$19
⑦SUMIFS関数(単一条件)
条件1を設定します。
条件範囲1内で一致するセルを指定します。
H2を選択します。
⑧SUMIFS関数(単一条件)
ファンクションキー「F4」を3回押して、相対参照にします。
H2 → $H2
これにより式をコピーして他の列に貼り付けても、H列が必ず参照されます。
⑨SUMIFS関数(単一条件)
式が完成しました。
=SUMIFS($F$2:$F$19,$B$2:$B$19,$H2)
値は5,800
商品名「みかん」の取引金額の集計額です。
⑩SUMIFS関数(単一条件)
下の列にも式をコピー貼り付けました。
=SUMIFS($F$2:$F$19,$B$2:$B$19,$H●)
●の部分のみが変わります。
りんごの行 $H3
ももの行 $H4
梨の行 $H5
合計金額がデータと一致しているのがわかります。
複数条件でのSUMIFS関数
商品名と性別との複数条件に一致する数値の合計を求めます。
SUMIFS関数の手順
①SUMIFS関数(複数条件)
さきほどのSUMIFS関数をコピーして使いましょう。
セルI5をコピーしてセルJ5に貼り付けます。
②SUMIFS関数(複数条件)
セルI5の式
=SUMIFS($F$2:$F$19,$B$2:$B$19,$H5)
③SUMIFS関数(複数条件)
すでに条件1までは入力されていますので、条件範囲2と条件2を設定します。
$H5の後に「,(カンマ)」を入れ、
④SUMIFS関数(複数条件)
「,(カンマ)」の後に条件範囲2を設定します。
条件範囲2は性別となりますので、C2:C19を範囲しています。
⑤SUMIFS関数(複数条件)
ファンクションキー「F4」を押して、絶対参照にします。
C2:C19 → $C$2:$C$19
⑥SUMIFS関数(複数条件)
ファンクションキー「F4」を押して、絶対参照にします。
C2:C19 → $C$2:$C$19
⑦SUMIFS関数(複数条件)
ファンクションキー「F4」を2回押して、相対参照にします。
J1 → J$1
これにより式をコピーして他の行に貼り付けても、1行目が必ず参照されます。
⑧SUMIFS関数(複数条件)
式が完成しました。
=SUMIFS($F$2:$F$19,$B$2:$B$19,$H5,$C$2:$C$19,J$1)
値は2,100 商品名「梨」、性別「男」の取引金額の集計額です。
式をコピーしてセルK5に貼り付けます。
=SUMIFS($F$2:$F$19,$B$2:$B$19,$H5, $C$2:$C$19,K$1)
最後の「J$1」が「K$1」とだけ変わりました。
値は7,800 商品名「みかん」、性別「女」の取引金額の集計額です。
⑨SUMIFS関数(複数条件)
他のセルに式をコピーして貼り付けます。
=SUMIFS($F$2:$F$19,$B$2:$B$19,$H●,$C$2:$C$19,●$1)
●の部分のみが変わります。
みかんの行 男の列:$H2、J$1 女の列:$H2、K$1
りんごの行 男の列:$H3、J$1 女の列:$H3、K$1
ももの行 男の列:$H4、J$1 女の列:$H4、K$1
梨の行 男の列:$H5、J$1 女の列:$H5、K$1
⑩SUMIFS関数(複数条件)
I列の合計がJ列「男」K列「女」に分かれているのがわかります。
みかん 合計5,800→男5,400、女400
りんご 合計5,800→男2,600、女1,000
もも 合計5,800→男3,450、女900
梨 合計9,900→男2,100、女7,800
まとめ
とりあえずSUMIFS関数を覚えましょう。
動画版はこちらです。