EXCELの検索テクニック

[EXCEL]任意のキーワードで検索合計する方法SUMIF

社内のデータの単位である「項目名」には「2017関西支社実績」のように「年度」や「支社名」といった複数の要素が詰め込まれていることが多いと思います。

「年度」でくくれば合計いくらになるのか?とか「支社」でくくれば合計は?といった、きりの無い要求を満たすために、毎度人間がエクセルを使って色々なパターンの集計表を苦労して作成するとなると、たいへんな手間になるはずです。

元をただせば、データの単位である「項目名」の中に複数の要素が詰め込まれていることが原因であったはずなのに、原因の改善はあとまわしにしてエクセルのできる人間にシワ寄せが来る、といった困り事はよく耳にします。困った会社ですね。

受け取った人が検索、集計できるように仕込んだエクセルを渡すことにしましょう。未整理のまま多数の要素を含んだ名称がまかり通っている場合は、毎回苦労して集計する前に、「配布先、利用者側に検索させる」という情報提供方法があっても良いと思いますよ。

Google検索と同じように検索語句をキーワードと呼び、メニュー名称の中に「関西」というキーワードが含まれるものを探し出す、という検索スタイルを「あいまい検索」と呼んでいます。「*関西*」のようにキーワードの前後にアスタリスク「*」を付けると、検索先文字列のどの位置に「関西」があってもOKとなり、これを「ワイルドカード」と呼んでいます。

キーワードで一覧表を検索集計する

SUMIFとワイルドカード*

SUMIF(範囲,検索条件,合計範囲)

指定された検索条件に一致するセルの値を合計します。

①検索結果を表示するセルに「=SUMIF(A4:A11,”*”&A2&”*”,B4:B11)」と入力します。A2の前後に「*」を付けることによって、検索先文字列のどの位置にキーワードがあってもOKとなります。

②キーワードを検索する範囲は A4:A11 です。

③合計範囲は B4:B11 です。

③利用者が検索キーワードを入力します。

検索ヒットしたデータを塗りつぶす

どのメニューが検索にヒットしたかがわかるようにしておきましょう。

①項目、金額全体の範囲を選択しておきます。

②「ホーム」タブの「条件付き書式」「新しいルール(N)」をクリックします。

③「数式を使用して、書式設定をするセルを決定」「OK」

④数式窓へ「=COUNTIF($A4,”*”&$A$2&”*”)」と入力します。「$A4」は 4 の前の $ マークをはずして相対参照にすることがポイントです。入力出来たら「書式(F)」をクリックします。

⑤塗りつぶす色を決めて「OK」します。

動作テスト

キーワードを入力すると「合計金額」を表示して、同時に検索ヒットしたメニューを塗りつぶします。