先日、SUM関数シリーズを紹介しました。
- SUM関数：使い方と解説
- 【SUMIF関数】検索条件に一致したデータの合計値を求める
- 【SUMIFS関数】1つ以上の条件を同時に満たすデータの合計を出す
- SUM/SUMIF/SUMIFS関数：違いと使い分け
SUMIF関数とSUMIFS関数を使えば、条件に応じた合計値を算出できました。
今回、その条件部分が曖昧になったときです。
例えば、
下記表から。「亀田製菓 亀田の柿の種 ６袋詰 １９０ｇ」「亀田製菓 まがりせんべい １６枚」
・・・といった
亀田製菓の商品の売上金額計
を算出したい時などです。
亀田製菓という特定の文字列が含まれている商品名のみを対象して、売上金額を合計したいケースです。
Contents
お菓子売上ランキング 2022年度の集計ランキング
～集計期間 2022年1月1日~2022年11月30日～
| 順位 | 商品名 | 金額 |
|---|---|---|
| 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 |
お菓子ブログ｜ライフクエストブログ“【最新版】お菓子の売上ランキングをご紹介”. お菓子ブログ｜ライフクエストブログ. 2022-05-31. https://lifequestblog.com/snack-ranking/, (2023-09-28より抜粋)
記事の読み方
説明の順は以下です。
まずは、「問題」を与えて、
次に、「解答」を提示します。
最後に、「解説」です。
よくある、結論→理由の構成順に沿いました。
先に「解答」を知りたい人を考慮したためです。
「解説」の説明は不要で、とりあえずビジネス現場で使えればいいと思う方もいるためです。
また、ある程度の経験者であれば、
「問題」と「解答」だけで、関数を使いこなせてしまうためです。
そのために、即利用できるように、ダウンロードファイルを用意していますので、
活用してみてください。
（経験者であれば、ダウンロードファイルだけで、実務に応用できてしまうのではないでしょうか）
問題
問題は、記事冒頭でお伝えした内容と同じで、
下記データから、亀田製菓の商品の売上金額計を算出せよ
です。
つまり、下記商品です。
「亀田製菓 亀田の柿の種 ６袋詰 １９０ｇ」
「亀田製菓 まがりせんべい １６枚」
「亀田製菓 ハッピーターン １０８ｇ」
「亀田製菓 つまみ種 １２０ｇ」
「亀田製菓 ぽたぽた焼 ２０枚」
データ
記事冒頭のデータです。
解答付きデータのみを欲しい方は、下記からダウンロードしてください。
解答
SUMIF関数+ワイルドカード、SUMIFS関数+ワイルドカードを使います。
本問題では、SUMIF関数＋ワイルドカードが一番のおススメです。
理由は、『SUM/SUMIF/SUMIFS関数：違いと使い分け』に記載していますが、
条件の数が1つだからです。
条件の数が0ならば、SUM関数、
条件の数が1つ以上ならば、SUMIFS関数
を使用してください。
もし、「亀田製菓」の条件が追加された場合は、SUMIFS関数を使用してください。
裏を返せば、SUMIFS関数は１つ以上の条件を曖昧に検索することができます。
なお、次のようにSUM関数とIF関数を組み合わせれば可能ではないか？と考える人もいるかもしれませんが、
IF関数はワイルドカードが使えないため、曖昧検索ができないからです。
=SUM(IF(B2:B21="*亀田製菓*",C2:C21,""))
関数の結果は、0になり、期待する結果は求まりません。
関数式
SUMIF関数の場合
SUMIF関数の場合は、次の通りです。
=SUMIF($B$2:$B$21,"*亀田製菓*",$C$2:$C$21)
SUMIFS関数の場合
SUMIF関数の場合は、次の通りです。
=SUMIFS($C$2:$C$21,$B$2:$B$21,"*亀田製菓*")
解説
SUMIF関数とSUMIFS関数の構文に関しては、
を参照してください。
単に検索範囲を列「商品名」、合計範囲を列「金額」に設定し、
条件を以下としているだけです。
*亀田製菓*
謎の記述がは、*だと思います。
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, (不明)より一部改変
*亀田製菓*と記述すれば、
「亀田製菓 亀田の柿の種 ６袋詰 １９０ｇ」「亀田製菓 ハッピーターン １０８ｇ」「亀田製菓 つまみ種 １２０ｇ」などを検索することが可能になるわけです。
上記を検索値として、亀田製菓を含むデータを検索して、合計するというロジックです。
小話
ダブルクォーテーション
文字列は、半角のダブルクォーテーション(“)で囲みます。
外観上は数値のケース
SUM関数では、引数に文字列を設定してならないとルールがあります。
ただし、外観上は数値の場合には正常に動作します。
例えば、以下の2つのケースではどちらも「999」という値が数値に見えます。
しかし、2行目の「999」は書式が文字列です。
本来ならばルール違反であり、SUM関数はエラーを返すべきですが、
実際には正常に動作します。
おそらく、裏で数値変換が行われているものと考えられます。
そのため、書式が文字列でも外観上数値であれば、SUM関数は正常に動作するようです。
最後に
SUMIF関数＋ワイルドカード、SUMIFS関数＋ワイルドカードの組み合わせることで、
SUMIF関数とSUMIFS関数の検索条件を曖昧することで、
特定の文字列に合致したデータのみを合計できることが分かっていただければ大変うれしく思います。
