Excelの押えておきたい関数

販売履歴を商品名・性別の複数条件で販売金額を集計するSUMIFS関数

更新日:

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関数を覚えましょう。
動画版はこちらです。

03投稿記事下_関連コンテンツ

-Excelの押えておきたい関数

Copyright© 経理と総務の効率化 , 2024 All Rights Reserved.