SUMIFとSUMIFSを使用して、特定の文字を含むデータのみ、合計値を求める- DL可

先日、SUM関数シリーズを紹介しました。

• 【SUM関数】合計値を求める
• 【SUMIF関数】特定の文字を含むデータを合計してみる
• 【SUMIFS関数】1つ以上の条件を同時に満たすデータの合計値を求める
• 【SUM/SUMIF/SUMIFS】違いと使い分け
  

SUMIF関数とSUMIFS関数を使えば、条件に応じた合計値を算出できました。

今回、その条件部分が曖昧になったとき、合計値を算出したい場合の話です。

例えば、

下記表から、
「亀田製菓　亀田の柿の種　６袋詰　１９０ｇ」
「亀田製菓　まがりせんべい　１６枚」

・・・といった

亀田製菓の商品の売上金額計

を算出したい時などです。

つまり、

亀田製菓という特定の文字列が含まれている商品名のみを対象して、
売上金額を合計したいケースです。

順位	商品名	金額
1	亀田製菓　亀田の柿の種　６袋詰　１９０ｇ	259309
2	明治　チョコレート効果カカオ７２％　大袋　２２５ｇ	160916
3	カルビー　じゃがりこサラダ　５７ｇ	138919
4	亀田製菓　まがりせんべい　１６枚	119709
5	不二家　カントリーマアムバニラ＆ココア　２０枚	114366
6	不二家カントリーＭチョコまみれミドルパック１２７ｇ	113647
7	ブルボン　アルフォートファミリーサイズ　１９９ｇ	108476
8	亀田製菓　ハッピーターン　１０８ｇ	108270
9	カルビー　ポテトチップスうすしお味　６０ｇ	101015
10	カルビー　ビッグバッグうすしお味	93628
11	ネスレ日本　キットカットミニ　１４枚	93143
12	亀田製菓　つまみ種　１２０ｇ	80966
13	ロッテ　ガーナミルク　５０ｇ	75019
14	森永製菓　ムーンライト　１４枚	74732
15	名糖　アルファベットチョコレート　１９１ｇ	74177
16	ロッテ　ラミー　３本	72782
17	岩塚製菓　岩塚の黒豆せんべい　１０枚	70615
18	亀田製菓　ぽたぽた焼　２０枚	66344
19	フルタ製菓　生クリームチョコ　１８４ｇ	59684
20	不二家　カントリーマアムバニラ＆ココア　１９枚	59157

以下の手順で説明してますので適宜、読み飛ばしください。

「例題」→「解答」→「構文解説」です。

問題

問題は、記事冒頭でお伝えした内容と同じで、

下記データから、亀田製菓の商品の売上金額計を算出せよ

です。

つまり、下記商品です。

「亀田製菓　亀田の柿の種　６袋詰　１９０ｇ」
「亀田製菓　まがりせんべい　１６枚」
「亀田製菓　ハッピーターン　１０８ｇ」
「亀田製菓　つまみ種　１２０ｇ」
「亀田製菓　ぽたぽた焼　２０枚」

解答付きサンプルデータ

解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。

解答

次の3つ方法があります。

• SUMIF関数+ワイルドカード
• SUMIFS関数+ワイルドカード
  

なお、次のようにSUM関数とIF関数を組み合わせれば可能ではないか？

=SUM(IF(B2:B21=”*亀田製菓*”,C2:C21,””))

と考える人もいるかもしれませんが、

IF関数はワイルドカードが使えません。

つまり、曖昧検索ができないからです。

関数式

次の関数式が解答です。

SUMIFの関数式

SUMIF関数の場合は、次の通りです。

=SUMIF($B$2:$B$21,”*亀田製菓*”,$C$2:$C$21)

SUMIFSの関数式

SUMIF関数の場合は、次の通りです。

=SUMIFS($C$2:$C$21,$B$2:$B$21,”*亀田製菓*”)

解説

SUMIF関数の解説

=SUMIF($B$2:$B$21,”*亀田製菓*”,$C$2:$C$21)

特定の条件に一致するセルの合計を計算します。

具体的には、

B2からB21までのセルの中で “亀田製菓” を含むセルに対応する、C2からC21までのセルの値を合計します。

この場合、

“亀田製菓” の商品に対する売上合計が計算されます。

ダイアログボックスで見ると、下図の通りです。

*亀田製菓*

謎の記述がは、*だと思います。

ワイルドカードのアスタリスク (*) と呼びます。
次の見出しで説明します。

SUMIFS関数の解説

=SUMIFS($C$2:$C$21,$B$2:$B$21,”*亀田製菓*”)

1つ以上の特定の条件に一致するセルの合計を計算します。

具体的には、

B2からB21までのセルの中で “亀田製菓” を含むセルに対応する、C2からC21までのセルの値を合計します。

この場合、

“亀田製菓” の商品に対する売上合計が計算されます。

ダイアログボックスで見ると、下図の通りです。

SUMIFS関数の図とSUMIF関数の図を見比べると、
ハイライトの色が違うことに気づくかもしれません。

これはExcelの設定によるものなので、
混乱しないようにご注意ください。

“*亀田製菓*”

謎の記述がは、*だと思います。

ワイルドカードのアスタリスク (*) と呼びます。
次の見出しで説明します。

*亀田製菓*

と記述すれば、
「亀田製菓　亀田の柿の種　６袋詰　１９０ｇ」
「亀田製菓　ハッピーターン　１０８ｇ」
「亀田製菓　つまみ種　１２０ｇ」

などを検索することが可能になるわけです。

上記を検索値として、

亀田製菓を含むデータを検索して、

合計するというロジックです。

小話

フォローアップ

外観上は数値のケース

関数では、”引数に文字列を設定してならない”と記載されています。

ただし、外観上は数値の場合には正常に動作します。

例えば、以下の2つのケースではどちらも「999」という値が数値に見えます。

しかし、2行目の「999」は書式が文字列です。

本来ならばルール違反であり、関数はエラーを返すべきですが、
実際には正常に動作します。

おそらく、裏で数値変換が行われているものと考えられます。

そのため、書式が文字列でも外観上数値であれば、関数は正常に動作するようです。

ダブルクォーテーション

文字列は、半角のダブルクォーテーション(“)で囲みます。

例えば、”スポーツ”と”スポーツ以外”とします。

最後に

SUMIF関数＋ワイルドカード、SUMIFS関数＋ワイルドカードの組み合わせることで、

SUMIF関数とSUMIFS関数の検索条件を曖昧することで、

特定の文字列に合致したデータのみを合計できることが分かっていただければ大変うれしく思います。

---

参考記事：

Excel関数を操作するための基本概念です。基本概念を理解すると、各関数の理解がより深まります。

---