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

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

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

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

例えば、

下記表から、
「亀田製菓 亀田の柿の種 6袋詰 190g」
「亀田製菓 まがりせんべい 16枚」

・・・といった

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

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

つまり、

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

順位商品名金額
1亀田製菓 亀田の柿の種 6袋詰 190g259309
2明治 チョコレート効果カカオ72% 大袋 225g160916
3カルビー じゃがりこサラダ 57g138919
4亀田製菓 まがりせんべい 16枚119709
5不二家 カントリーマアムバニラ&ココア 20枚114366
6不二家カントリーMチョコまみれミドルパック127g113647
7ブルボン アルフォートファミリーサイズ 199g108476
8亀田製菓 ハッピーターン 108g108270
9カルビー ポテトチップスうすしお味 60g101015
10カルビー ビッグバッグうすしお味93628
11ネスレ日本 キットカットミニ 14枚93143
12亀田製菓 つまみ種 120g80966
13ロッテ ガーナミルク 50g75019
14森永製菓 ムーンライト 14枚74732
15名糖 アルファベットチョコレート 191g74177
16ロッテ ラミー 3本72782
17岩塚製菓 岩塚の黒豆せんべい 10枚70615
18亀田製菓 ぽたぽた焼 20枚66344
19フルタ製菓 生クリームチョコ 184g59684
20不二家 カントリーマアムバニラ&ココア 19枚59157
表1 お菓子売上ランキング 2022年度の集計ランキング
お菓子ブログ|ライフクエストブログ“【最新版】お菓子の売上ランキングをご紹介”. お菓子ブログ|ライフクエストブログ. 2022-05-31. https://lifequestblog.com/snack-ranking/, (2023-09-28より抜粋)

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

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

問題

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

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

です。

つまり、下記商品です。

「亀田製菓 亀田の柿の種 6袋詰 190g」
「亀田製菓 まがりせんべい 16枚」
「亀田製菓 ハッピーターン 108g」
「亀田製菓 つまみ種 120g」
「亀田製菓 ぽたぽた焼 20枚」

サンプルデータ

オリジナルデータ

お菓子売上ランキング 2022年度の集計ランキング
お菓子ブログ|ライフクエストブログ“【最新版】お菓子の売上ランキングをご紹介”. お菓子ブログ|ライフクエストブログ. 2022-05-31. https://lifequestblog.com/snack-ranking/, (2023-09-28より抜粋)

解答付きサンプルデータ

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

解答

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

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

“*亀田製菓*”

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

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

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

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

文字説明使用例
*任意の数の文字と一致します。 アスタリスク (*) は文字列のどの場所でも使用できます。wh* では、white や why は検索されますが、awhile や watch は検索されません。
アスタリスク (*) に関して
ワイルドカード文字の例 – Microsoft サポート”. https://support.microsoft.com/ja-jp/office/%E3%83%AF%E3%82%A4%E3%83%AB%E3%83%89%E3%82%AB%E3%83%BC%E3%83%89%E6%96%87%E5%AD%97%E3%81%AE%E4%BE%8B-939e153f-bd30-47e4-a763-61897c87b3f4, (不明)より一部改変

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

*亀田製菓*

と記述すれば、
「亀田製菓 亀田の柿の種 6袋詰 190g」
「亀田製菓 ハッピーターン 108g」
「亀田製菓 つまみ種 120g」

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

上記を検索値として、

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

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

小話

フォローアップ

外観上は数値のケース

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

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

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

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

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

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

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

ダブルクォーテーション

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

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

最後に

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

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

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

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

原理原則に関する記事

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

構文に関する記事

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

数式の原則|Excel関数にはそれぞれ構文がある

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

数式の原則|Excel関数の引数とは

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

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

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

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

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

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

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

参照形式に関する記事

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

数式の原則|絶対参照/相対参照/複合参照とは

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

数式の原則|絶対参照/相対参照/複合参照の使い方

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