【Excel】キーワードを含むかどうかでカテゴリ分けを行う

データのカテゴリ分けをしたい

「商品名の一覧」のようなデータを与えられたとき、それぞれの商品名がキーワードを含むかどうかでカテゴリ分けをする方法をご紹介します。

下図のように「商品名-カテゴリ」の対応表を作ることが目標になります💁‍♂️

f:id:ekanoh:20180911201506p:plain

キーワードに応じてカテゴリ分けする方法

f:id:ekanoh:20180911202809p:plain

設定としては上記のように商品名だけが与えられている状況です。

これに対して商品名に応じて「弁当」、「おにぎり」、または「パン」いずれかのカテゴリを振っていきましょう。

なお、この例の量であればコピー&ペーストしても事足りるのですが、実務で扱うデータでは商品名が100行~1000行以上は並んでいることがざらでしょう🤔

1. カテゴリを列ラベルとして並べて書く

f:id:ekanoh:20180911203319p:plain

まず、分類したい先のカテゴリを横に並べて書き込んでいきましょう

今回は「弁当」、「おにぎり」、または「パン」の3カテゴリだったので、その3つを書き込みます✏️

2. カテゴリを表す文字列が含まれているか判定

f:id:ekanoh:20180911203616p:plain

次にカテゴリを表す文字列が含まれているか否かを判定してフラグを立てていきます。

たとえば、「幕の内弁当」なら「弁当」というキーワードが含まれているので、「弁当」の列が1になります。

一方、「あんパン」には「弁当」というキーワードが含まれていないので「弁当」の列が0で、代わりに「パン」の列が1になっています。

これを実現するにはB2のセルに=COUNTIF($A2,"*"&B$1&"*")と書き込んでB2:D9の範囲でコピー&ペーストをすればOKです。

f:id:ekanoh:20180912094305p:plain
COUNTIF関数とワイルドカードの組み合わせ

上記の関数は、

という3つの機能を使っているためやや煩雑ですが、よくわからなくてもコピペすればとりあえず動くはずです💁‍♂️

わからない方はまず動かしてみてからなぜ動くかじっくり考えてみましょう🧐

3. フラグが立っている場所のカテゴリを記入する

f:id:ekanoh:20180912095138p:plain

最後に、「弁当」の列が1になっているところは「弁当」、「パン」の列が1になっているところは「パン」、という具合にフラグに応じてカテゴリの名前を振っていきます

ただし、「鮭おにぎり弁当」だけ「おにぎり」と「弁当」の両方のフラグが立ってしまっているので、ここは手作業でどちらかひとつのフラグだけ立てます。

さしあたり、「鮭おにぎり弁当」は弁当フラグだけにしておきましょう。

なお、このような例外パターンはデータセットが大きいと頻繁に発生しますが、SUM関数などを利用して2つ以上フラグが立ってしまっているデータだけフィルタをかけるなどすれば手作業でも修正がサクっと行えます。

f:id:ekanoh:20180912095408p:plain

無事に全商品に対してフラグがひとつだけ立っている状態(one-hotなどと呼びことがあります)になったら、E2に=INDEX($B$1:$D$1,MATCH(1,B2:D2,0))と書き込んで、E2:E9の範囲でコピー&ペーストすれば無事にカテゴリ分けが完了します🙆‍♂️

f:id:ekanoh:20180912095938p:plain
MATCH関数とINDEX関数の組み合わせ

こちらの場合も、

  • セルの固定
  • MATCH関数
  • INDEX関数

の3つの機能を使っているためやや煩雑になってしまっているのですが、よくわからなくてもコピペすればとりあえず動くので、まずは動かしてみましょう🤔

素早くデータ処理できるようになろう

コンサルタント・マーケター・データサイエンティストなどの職種を志している人は、仮説検証のサイクルを高速に進めることができるようになる必要があります。

まずはこのようなExcelの操作に慣れ親しむことで、ミスなく素早くデータ処理ができるようになるとよいですね。

更なるステップアップのためにはRやPythonなど分析に特化したプログラミング言語を使えるようにしておくとよいでしょう。たとえば、統計・機械学習に特化したオンライン学習サイトであるAidemyで無料カウンセリングを受けて、現状の能力と目標とするべきスキルセットを明確にしておくのがおすすめです。