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の設定によるものなので、
混乱しないようにご注意ください。

“*亀田製菓*”

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

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

Excelのワイルドカードのアスタリスク(*)の解説

ワイルドカードのアスタリスク (*) を使っています。

「任意の数の文字と一致します」とMicrosoft社は説明しています。

文字	説明	使用例
*	任意の数の文字と一致します。 アスタリスク (*) は文字列のどの場所でも使用できます。	wh* では、white や why は検索されますが、awhile や watch は検索されません。

アスタリスク (*) はワイルドカードとして機能し、
任意の文字列を表します。

*亀田製菓*

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

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

上記を検索値として、

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

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

小話

フォローアップ

外観上は数値のケース

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

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

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

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

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

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

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

ダブルクォーテーション

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

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

最後に

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

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

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

—————————————
参考記事
—————————————

原理原則に関する記事

Excel関数自体の原理原則に関する記事です。こちらを参照すると、個別の関数の理解がさらに深まるでしょう。

構文に関する記事

Excel関数の引数と戻り値は何だっけ？という時に、役立つ記事です。

Excel関数にはそれぞれ構文がある

Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…

データ分析ドットコム

Excel関数の引数とは

Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。

データ分析ドットコム

ダイアログボックスの使い方に関する記事

Excel関数名は何だっけ？VLOOKUP関数の使い方は何だっけ？という時に、ダイアログボックスを使うと便利です。
下記はそのダイアログボックスに関する記事です。

Excelの検索ダイアログから、関数を探す

Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。

データ分析ドットコム

Excelのダイアログに沿って、関数を使う

Excelのダイアログに使えば、関数の使い方を調べることができます。言わば、関数のガイドラインのようなものです。

データ分析ドットコム

参照形式に関する記事

関数式をコピーする時はどうしたらいい？
$マークは、どう付けたらいいだろう？
といった悩みをお持ちの方は以下をご覧ください。

絶対参照/相対参照/複合参照とは

絶対参照/相対参照/複合参照とは何かをどこよりも分かりやすく解説します。

データ分析ドットコム

絶対参照/相対参照/複合参照の使い方

「絶対参照」「相対参照」「複合参照」は、どうやって使うのかを紹介します。 ポイントは、「$マークは、直後にあるモノを固定する」です。

データ分析ドットコム