今回の記事では
ある文字列(キーワード)を条件に一覧表から合計を計算する方法ってあるの？
どんな関数を使ったら狙い通りのキーワードで合計できるか分からない
複数の条件をキーワードにして一覧表から合計する方法も知りたい
簡単で柔軟に対応してくれる関数や方法って無いの？
こういった疑問に答えます
✔キーワードに一致する合計を計算する方法のテーマ
①キーワードに一致する条件で合計を計算するならSUMIF関数+ワイルドカードが正解です
②複数の条件に一致する合計を計算するならSUMIFS関数+ワイルドカードが最適解です
この記事を書いているのはOffice歴約20年
現在、エステサロン経営をしながらITを使った現場のマネジメントをしています
「キーワードに一致する合計を計算する方法」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
キーワードに一致する条件で合計を計算するならSUMIF関数+ワイルドカードが正解です
キーワードに一致する条件で合計を計算するには、SUMIF関数にワイルドカードを組み合わせれば実現できます
理由はSUMIF関数だけでキーワードを含む計算をすることが出来ないからです
では実際にやり方を見ていきましょう
今回はスーパーの鮮魚売り場で売っている魚介類の一覧表の中から、条件に一致する項目の合計金額を計算する方法を見ていきましょう
尚、今回はRakuten SEIYU ネットスーパー様よりデータの一部を流用させていただきました
今回の目的は「食品一覧表」から「まぐろ」という文字を含む品名の合計額を自動で計算することです
キーワードの「まぐろ」を含む「品名」は背景が薄い青色の２つなのでちゃんと合計金額が表示されていますね
今回の結論から言うと、次のように設定すればOKです
式はかなりシンプルですね
「=SUMIF(A5:A16,“*”&D5&”*”,B5:B16)」この式を分かりやすくするために日本語にしてみましょう
「=SUMIF(A5～A16セルの範囲を調べてね,調べる条件はD5セルのキーワードを含むもので,B5～B16セルの範囲を合計してね)」
という設定です
今回のポイントは「“*”&D5&”*”」の部分です
これはワイルドカードを使って「D5」の内容を含めるという意味になります
この設定が無いと上手くいきません
ワイルドカードとは「〇〇県」などのように「〇〇」を「*」などの記号で表す方法のことです
今回の場合は「D5のセルの内容を含む」という意味があり「～まぐろ～」というキーワードが「文字列(びんちょうまぐろ（刺身用・解凍）)」にあるか調べています
図解するとこんな感じです
調べたい文字列の中に探しているキーワードの「まぐろ」が含まれているか調べています
ワイルドカードを使ったセルの数え方なども「COUNTIF関数ならキーワードの数を簡単集計」などの記事で紹介してるので合わせて読んでもらえると更に理解が深まります
でも、ワイルドカードを使わなくても合計額を表示できそうですよね
では、試しにワイルドカードを使わない場合も見てみましょう
さっきの設定とほぼ一緒ですね
違いは検索するキーワードの部分が「D8」になっていることです
この場合D8セルの内容である「まぐろ」という文字を検索することになります
そのため答えは「0」になります
ワイルドカードを使った「まぐろという文字を含む」場合と「まぐろ」を調べた時の違いは「あいまい検索」か「完全一致検索」にあります
「あいまい検索」というのは今回のように「〇〇を含める」や「〇〇で始まる」「〇〇で終わる」といった柔軟な検索方法です
「完全一致検索」とは今回の場合「びんちょうまぐろ（刺身用・解凍）」と「まぐろ」は完全に一緒か？
という調べ方なので答えはNO つまり、合計金額も「0」になるということです
繰り返しになりますが、キーワードに一致する条件で合計を計算するには、SUMIF関数にワイルドカードを組み合わせれば実現できます
よくある質問：ワイルドカードは「”*”&指定セル&”*”」と必ず設定しないといけないの？
ワイルドカードの設定方法は目的によって変わりますが、より柔軟な方法なら今回のやり方がベストです
理由はセル内の文字をキーワードに設定するには今回の方法がベストだからです
一番単純なワイルドカードの設定方法は「*まぐろ*」のように「ワイルドカード+キーワード」と直接入力します
この方法が一番シンプルですが、この設定だと調べたいキーワードが変わるたびに直接変更しないといけなくなります
キーワードが変更されるたびに入力し直していると「設定の間違いや手間が増えてしまう」だけですよね
そこで、セルを参照したワイルドカードの設定をしようとすると「*D11*」と設定したくなるわけです
では、実際にこのやり方で設定してみましょう
実際にやってみると数式に問題があるというダイアログが表示されて失敗してしまいます
この対策をしたのがコレです
結果は全く同じ設定になってしまいますが、ポイントが2箇所あります
それは「”*”」と「&」です
アスタリスクとセルの内容を直接くっつけようとするとエラーになってしまうので、アスタリスクを「””(ダブルクォーテーション)」で囲って文字にします
さらに「&(文字を結合するための演算子)」を使って「アスタリスクとセルの参照」をくっつけるとSUMIF関数でも問題無く設定出来るという訳です
この方法はパターンなのであまり深く考えすぎず「ワイルドカードとセルの内容をくっつけたい」時はこの2つのポイントを上手に活用しましょう
繰り返しになりますが、ワイルドカードの設定方法は目的によって変わりますが、より柔軟な方法なら今回のやり方がベストです
複数の条件に一致する合計を計算するならSUMIFS関数+ワイルドカードが最適解です
キーワードが複数ある場合はSUMIFS関数を活用すると上手くいきます
理由はSUMIF関数だと複数の条件設定が出来ないからです
では実際に「複数のキーワードの条件に一致する合計額を計算する方法」について見ていきましょう
やり方は、こんな感じです
SUMIF関数と似ているので基本的な設定方法も似ていますね
キーワード1の「鮭」とキーワード2の「切身」を含んだ商品は薄い青色の部分ですが、ちゃんと計算出来ています
では、設定内容を見ていきましょう
「=SUMIFS(B5:B20,A5:A20,“*”&D5&”*”,A5:A20,“*”&E5&”*”)」この式を分かりやすいように日本語にしてみましょう
「=SUMIFS(合計範囲はB5～B20のセル範囲だよ,A5～A20のセル範囲を検索してね,D5の内容をキーワードにして,A5～A20のセルの範囲を検索してね,E5の内容をキーワードにして)」
といった感じですね
まつりSUMIFS関数の設定方法は
- 合計したい範囲を設定
- キーワード1を検索したい範囲を設定
- キーワード1を設定
- キーワード2を検索したい範囲を設定
- キーワード2を設定
- 両方のキーワードに一致した合計を計算する
この方法であれば、複数のキーワードに一致した合計金額を計算することが簡単に出来ます
今回のポイントもワイルドカードを上手く活用することですが、SUMIF関数と設定方法は一緒なのでチャレンジしてみましょう
ただ、SUMIFS関数の方が設定内容が多いですが、慣れてしまえば難しくは無いので複数のキーワードがで合計額を出す必要がある場合には活用してみましょう
繰り返しになりますが、キーワードが複数ある場合はSUMIFS関数を活用すると上手くいきます
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
![VLOOKUP関数で０やエラー表示を避ける方法［IF・IFERROR関数との組み合わせ]](https://i0.wp.com/tsurutoro.com/wp-content/uploads/2022/01/4327107_m.jpg?fit=1200%2C900&ssl=1&resize=40%2C40){kind=small}
![VLOOKUP関数で下から検索する方法［XLOOKUP関数で簡単検索も可能]](https://i0.wp.com/tsurutoro.com/wp-content/uploads/2022/01/957697_m.jpg?fit=1200%2C797&ssl=1&resize=40%2C40){kind=small}
コメントを残す