はじめに
スプレッドシートには関数が多々ありますが、その中でも「COUNTIFS」と「SUMIFS」の2つを覚えれば基本的なデータ分析を実施することは可能です。
そこでこの記事では「SUMIFS」の使い方についてご紹介します。
「COUNTIFS」についてはこちらの記事で解説してますので、同時に学んでください。
「SUMIFS」で何ができるのか？
まず「SUMIFS」で何ができるのかをご紹介します。
「SUMIFS」とは「条件に合った数を足し合わせる」ことができます。
ここから紹介するものは「SUMIFS」を使用した一例になりますので、イメージを沸かせるために活用していただければと思います。
月ごとの数を足し合わせる
まずよく使われるのが「月ごとの数を足し合わせること」です。
- ３月の合計売上
- 今期の総支出額
- 毎月の人件費
など、項目ごとの数字が月別に分かれている際に、それぞれを足し合わせて月間の合計数字を出すときに「SUMIFS」を使用します。
各商材の合計売上金額を計算する
次に、「各商材ごとの売上金額を把握するとき」に使用します。
例題でよく出されるのが「りんご」と「みかん」の合計売上はいくらかです。
実際のビジネスで「りんご」などをそのまま使用することは少ないのですが、色々置き換えて使用することで自分自身のビジネスに活用することができます。
単価が〇〇~〇〇円の商品がいくら売り上げたか
最後に単価の範囲を決めて、その範囲における売上金額がいくらなのかを計算する際に使用します。
単価が高くなるほど売れづらく、単価を安くすれば売りやすい、その分岐ポイントを調べたり、どの範囲が顧客に売れやすいのかなど経営判断をする際などにも利用できます。
「SUMIF」と「SUMIFS」の違い
「SUMIFS」の詳しい説明の前に、よく間違えられる「SUMIF」との違いをご紹介します。
この2つの関数の違いは「条件が1つだけか複数かどうか」です。
足したい条件の数が1つの場合→「SUMIF」
複数条件の数を足す場合→「SUMIFS」
また、関数の書き方も異なり、
「SUMIFS」：SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, …], [条件2, …])
「SUMIF」：SUMIF(範囲, 条件, [合計範囲])
と「SUMIFS」は足し合わせる範囲が先、「SUMIF」は後になります。
ただ、「SUMIFS」を使用すれば「SUMIF」の代用も可能なため、「SUMIFS」のみを使えるようになれば問題ありません。
ここでは「SUMIF」について解説はしませんがどうしても「SUMIF」を使いたい方はGoogleで再度検索をしていただければと思います。
「SUMIFS」の形を説明
実際に「SUMIFS」を使用する前に関数の構成自体を紹介していきます。
さきほど、少し関数の中身を紹介したのですが、ここでさらに詳しく紹介していきます。
まずスプレッドの関数説明では、
SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, …], [条件2, …])
例
COUNTIFS(A1:A10, B1:B10, ">20", C1:C10, "<30")
概要
複数の基準に応じた範囲の合計を返します。
合計範囲
合計する範囲です。
条件範囲1
[条件1] に対して検証する範囲です。
条件1
[条件範囲1] に適用するパターンまたはテストです。
条件範囲2… - [任意] 反復可能
検証対象となる追加の範囲です。
条件2… - [任意] 反復可能
検証対象となる追加の条件です。
と書かれています。
合計範囲：足し合わせたい数字が記載されている範囲
例：売上、単価など
条件範囲：数えたい情報の範囲
例：登録日、性別、商品名など
条件：数える条件
例：20代、1月に登録、りんごなど
合計範囲は「足し合わせたい数字を記載している場所」という意味で「A:A」など範囲で記載します。
また、合計範囲は「数値」を選択しないとエラーになるため注意が必要です。
条件範囲は「数えたい情報が集まっている場所」という意味で、「A:A」など範囲で記載します。
条件は「数えたい条件の詳細」という意味で、「”>20”」などで記載します。
ただ、正直この情報だけだと理解しづらい部分が多いため、実際に手を動かして学んでいただければと思います。
「SUMIFS」は最初に「合計範囲」を記載しその後は「範囲」,「条件」,「範囲」,「条件」,「範囲」,「条件」,…と条件が増えるごとにこの順番で合計範囲の後ろに記載していくと問題ありません。
実際に「SUMIFS」を使用してみましょう
ここまで「SUMIFS」の概要を説明しましたので、ここから実際に「SUMIFS」を手を動かして学んでいきましょう。
①月別の売上を計算しよう
まずはよくデータ分析で求められる「月別の売上」を「SUMIFS」を用いて計算してみましょう。
| A | B | |
|---|---|---|
| 1 | 月 | 数字 |
| 2 | 21年1月 | =sumifs('使用データ'!E:E, '使用データ'!F:F,">=2021/01/01",'使用データ'!F:F,"<2021/02/01") |
| 3 | 21年2月 | =sumifs('使用データ'!E:E, '使用データ'!F:F,">=2021/02/01",'使用データ'!F:F,"<2021/03/01") |
| 4 | 21年3月 | =sumifs('使用データ'!E:E, '使用データ'!F:F,">=2021/03/01",'使用データ'!F:F,"<2021/04/01") |
関数自体はこちらを利用すれば計算することができます。
②部署ごとの売上を計算する
次に「部署ごとの売上」を計算します。
今回は「営業部署」「エンジニア部署」「コンサルティング部署」の売上を計算してみます。
| A | B | |
|---|---|---|
| 1 | 部門 | 数字 |
| 2 | 営業部門 | =sumifs('使用データ'!E:E, '使用データ'!C:C,"営業部門") |
| 3 | エンジニア部門 | =sumifs('使用データ'!E:E, '使用データ'!C:C,"エンジニア部門") |
| 4 | コンサルティング部門 | =sumifs('使用データ'!E:E, '使用データ'!C:C,"コンサルティング部門") |
ここでは条件が商品を選択する必要があるため、「商品が記載されている部分」を選択し、計算したい商品名をそれぞれ記載します。
こちらは条件が少ないため、そこまで難しくない例題になります。
③事業部ごとの月別売上を計算する
最後に①②でやったことの応用として「月別の部門ごとの売上」を計算してみます。
| A | B | C | |
|---|---|---|---|
| 1 | 月 | 部門 | 数字 |
| 2 | 21年1月 | 営業部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/01/01", '使用データ'!F:F, "<2021/02/01", '使用データ'!C:C, "営業部門") |
| 3 | エンジニア部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/01/01", '使用データ'!F:F, "<2021/02/01", '使用データ'!C:C, "エンジニア部門") | |
| 4 | コンサルティング部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/01/01", '使用データ'!F:F, "<2021/02/01", '使用データ'!C:C, "コンサルティング部門") | |
| 5 | 21年2月 | 営業部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/02/01", '使用データ'!F:F, "<2021/03/01", '使用データ'!C:C, "営業部門") |
| 6 | エンジニア部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/02/01", '使用データ'!F:F, "<2021/03/01", '使用データ'!C:C, "エンジニア部門") | |
| 7 | コンサルティング部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/02/01", '使用データ'!F:F, "<2021/03/01", '使用データ'!C:C, "コンサルティング部門") | |
| 8 | 21年3月 | 営業部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/03/01", '使用データ'!F:F, "<2021/04/01", '使用データ'!C:C, "営業部門") |
| 9 | エンジニア部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/03/01", '使用データ'!F:F, "<2021/04/01", '使用データ'!C:C, "エンジニア部門") | |
| 10 | コンサルティング部門 | =sumifs('使用データ'!E:E, '使用データ'!F:F, ">=2021/03/01", '使用データ'!F:F, "<2021/04/01", '使用データ'!C:C, "コンサルティング部門") |
ここでは「名前の一致」「期間の一致」の両方を使用して計算しました。
今後「SUMIFS」を活用していく中でこの2つは必ず使用していくため、ぜひこの機会に覚えていただければと思います。
まとめ
「SUMIFS」について関数の使用方法についてご紹介しました。
「SUMIFS」と「COUNTIFS」は関数を身につけ、スプレッドを使用したデータ分析ライフをスタートさせていただければと思います。
