EXCELの検索テクニック

[EXCEL]自由検索が社内のエクセル低スキル化解消!SUMIF

集計表を「作ってあげる人」と「作ってもらう人」に分裂してしまった会社で、「作ってあげる人」へ改革のヒントです。

なぜ集計作業が必要なのか?考えてみると、販売データなどを業務現場へ提供しているシステムが出す情報が「未集計の生データ」だからです。

かと言って、システム側のメニューにいくつかの定型メニューを用意したとしても、業務現場は様々な集計パターンを要求するため、結局は「定型メニュー」プラス「生データ」を提供することになります。そして「生データ」を新しいパターンで集計してみることで営業チャンスを発見しようとします。

EXCELの本来の役割りは、まさにこの需要を満たすために生まれ育った「分析ツール」であり、業務現場全員がある程度使いこなせれば、常に新しい発見ができるはずなのですが、日本のオフィスでは「作ってあげる人」と「作ってもらう人」に役割り分担してしまうようです。

作ってもらう人が要求するとおりの集計表を寸分違わず作るのではなく、作ってもらう人にもちょっとだけ参加してもらうことで、エクセル本来の機能を知ってもらい、かつ作る側の負担を大きく軽減するという「一石二鳥」の秘策「自由検索」について解説いたします。

社内のデータの単位である「品名」には複数の要素が詰め込まれていることが多いと思います。例えば「国産」という産地「冷凍」という状態「牛肉」という種類、といった要素が含まれている、ということです。

だから「産地別に昨年と比較してみたい」などの要求がとめどなく湧いてきて、最前線でありながらエクセル集計担当「作ってあげる人」が必要になってしまうのです。きりの無い要求を満たすために、毎度人間がエクセルを使って色々なパターンの集計表を苦労して作成するとなると、たいへんな手間になるはずです。

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

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

今回は「産地」「状態」「種類」と3つの項目を自由に組み合わせたキーワードでGoogle検索と同じように、品名の中から複合キーワードが含まれるものを探し出して集計する、という集計表を作ります。キーワードの前後にアスタリスク「*」を付けると、検索先文字列のどの位置にキーワードがあってもOKとなり、これを「ワイルドカード」と呼んでいます。

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

品名の中に「産地」「状態」「種類」と3つの要素がある場合、要素別集計値が簡単に取り出せるようにするには?考えてみましょう。

SUMIFとワイルドカード*

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

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

SUMIF(範囲,”*キーワード*”,合計範囲)
前後にアスタリスク「*」を付けると、キーワードが品名中どの位置にあっても「検索一致」となります。

よくある過剰サービスの例

集計準備サービス

①要素が3つある、ということはタテ✕ヨコでは足りないため、思い浮かべるのはピボットテーブルです。ピボットテーブルを作るための下準備として、品名の中に埋没している3つの要素を新しい列に書き出す、というたいへんな作業をはじめてしまいます。

ピボットテーブルその1

②3要素をタテヨコ集計しなければならないので、「産地」の下層へ「状態」を置いたところ、作ってもらう人から「これではチルド計がわからない」と無いものねだりされてしまいました。

ピボットテーブルその2

③列の「産地」「状態」を入れ替えた表も作らざるを得なくなりました。ピボットテーブルとは気に入ったレイアウトを「やりながら」模索できるシミュレーション機能なんですが、これでは単なる集計メニューとしてしか貢献していませんね。

SUMIF自由検索作戦で一石二鳥

気づきと省力化を生む検索

④受け取った人が検索、集計できるようにしましょう。セルB2へ入力したキーワードを含む品名の合計をセルC2へ表示させる式「=SUMIF(B5:B15,”*”&B2&”*”,C5:C15)」を入力します。

欲しい集計要素を入力させる方式

⑤「国産」や「牛肉」など単独キーワード、「国産チルド」や「冷凍牛肉」などの連続した複合キーワードも検索ヒットしたものの合計を表示します。

⑥非連続キーワード「国産」と「牛肉」の間に入っている文字を無視する場合、「国産 牛肉」(間にスペース)ではなく「国産*牛肉」(間に「*」アスタリスク)を入れます。

3要素以上にも有効

今回の例は、品名に3つの要素が含まれる場合を想定しましたが、同じ方法で4要素以上、さらにルール不在が原因で、個々が好きな品名を登録して、その結果エクセル集計者が苦労している場合などに効果を発揮するはずです。

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

EXCEL屋 無料活用術その1

働き方改革の名の元、業務時間の短縮を指示されるのに、現場からの無理難題は増える一方!もう投げ出したい(泣)、、、そうなる前に【EXCEL屋】で検索!
まずはコチラをブックマーク

EXCEL屋 無料活用術その2

業務現場で「本当に」役立つEXCEL活用術をメールでお届けします。もちろん無料!
登録はコチラ

EXCEL屋 無料活用術その3

検索したり、いろいろ頑張ったけど、どうしても解決できない問題を【EXCEL屋】が解決します。「成功事例」として記事化OKの場合は「解決料無料!」
コチラから課題をお知らせください