ワイルドカードSUMIF集計と条件付き書式

Excelで「特定のキーワードを含むデータだけを合計したい」「該当するセルを自動で色付けして視覚的に分かりやすくしたい」と思ったことはありませんか？

今回は、検索用のセルにキーワードを入力するだけで、該当するお酒の「合計本数」を自動集計し、対象のセルを「自動で黄色くハイライト」する動的なシートの作り方を分かりやすく解説します！

今回作成するExcelシートの完成イメージ

例えば、以下のようなお酒の在庫リストがあるとします。

検索セルを「焼酎」や「銀醸」に変えれば、合計本数と色付け位置もリアルタイムに切り替わります。

まずは、キーワードを入力した際に自動で「〇〇を含む」というわかりやすい表示に切り替わるように、セルの書式設定を行います。

これで、セルに「純米」と入力してエンターキーを押すだけで、見た目は「純米を含む」に自動で変わります（セルの中身自体は「純米」という文字だけの状態が維持されます）。

次に、入力したキーワードを「含む」データの数値を合計するSUMIF関数を設定します。ここで重要なのが、部分一致を表現するワイルドカード（アスタリスク *）の使い方です。

=SUMIF(お酒の名前の範囲, "*" & 検索セル & "*", 在庫数の範囲)

合計値を表示させたいセルを選択し、=SUMIF( と入力します。
範囲：お酒の名前が並んでいるセル範囲（例：B3:B10）をドラッグして選択し、カンマ（,）を打ちます。
検索条件：ここがポイントです。検索セルの前後にアスタリスクを結合するため、以下のように入力してカンマ（,）を打ちます。
"*" & H3 & "*"
※「シフト＋2」でダブルクォーテーション（"）、「シフト＋6」でアンド（&）を入力します。
合計範囲：合計したい在庫数が並んでいるセル範囲（例：C3:C10）を選択します。
最後に ) で括弧を閉じてエンターキーを押します。

これで「純米」を含むお酒だけの合計本数が正しく計算されるようになります。

最後に、検索したキーワードが含まれるセルを自動で黄色くハイライトする設定を行います。
検索セルが「空欄」のときは、シートが黄色だらけにならないように「何も色付けしない」というスマートな設定（AND関数とFIND関数の組み合わせ）を組み込みます。

色付けを適用したいお酒の名前のセル範囲（例：B3:B10）をすべて選択します。
ホームタブの「条件付き書式」を左クリックし、「新しいルール」を選択します。
ルールの種類から「数式を使用して、書式設定するセルを決定」を選択します。
数式の入力欄に、以下の数式を入力します。
=AND($H$3<>"", FIND($H$3, B3)>0)
- $H$3<>"" ： 検索セルが空ではない、という条件です。
- FIND($H$3, B3)>0 ： 検索セルの文字が、対象のセル（例：B3）の中に含まれているか判定します。含まれていれば1以上の文字位置（数字）が返されるため、>0 と指定することで「キーワードを含む」という条件になります。
- ※ B3（お酒の名前の先頭セル）を指定する際、キーボードの「F4」キーを複数回押して、列や行のドルマーク（$）を外して相対参照にする必要があります。
数式を入力したら、「書式」ボタンを左クリックします。
「塗りつぶし」タブから「黄色」を選択し、「OK」をクリックします。
ルール設定画面に戻ったら、再度「OK」を押して完了です。