大量の顧客リストから、特定の条件に合致した情報だけ抽出したいとき、どのようにするのがいいでしょうか。
色々とやりかたはありますし、どれが正解というわけではなく自分にあった方法でいいと思いますが、今回はエクセルの関数やテキストエディタとの組み合わせについてまとめました。知っている人にはなんてことはない情報です。
ちなみに、本記事内ではエクセルではなくMacのNumbersを使っていますが、どちらでも用意されている関数を使います。
目次
エクセルの関数を使うことで欲しい情報が取れる
例として、簡単なデータを作成しました。
| みかん |
| りんご |
| ぶどう |
| バナナ |
| メロン |
このデータを対象に、以下のデータを探したいデータを探します。
| ぶどう |
| トマト |
| レタス |
| みかん |
| ピーマン |
エクセルだとこのようになります。
重複したデータを探すときにはCOUNTIF関数
「対象となるデータ」と「探したいデータ」を見比べると、「ぶどう」「みかん」が重複しているのがわかります。このくらいの量のデータであれば目視で可能ですが、数千、数万件のデータとなると目視ではなくコンピュータにやってもらうのが楽です。
そういうときはCOUNTIF関数（カウントイフ関数）を使います。この関数は条件に一致した場合に一致したセルの数を返してくれます。
先程の例に、COUNTIF関数をC列に追加しました。
例えばB2の「ぶどう」は対象データ内に1つ含まれているため、COUNTIF関数を書いたC2に「1」と表示されているのがわかります。B3, B4のトマト、レタスは対象データ内に含まれていないため、0が表示されています。もし、対象データ内にぶどうがもう一つあれば、2を返します。
= COUNTIF(参照するセル, 探す対象)今回は「参照するセル」はA2からA6までとなり、参照位置を固定したいため絶対参照するため「$」をカラムであるAの前と、列である2の前にそれぞれ付与しています。こうすることで、関数を記載するセルの位置に左右されず、参照先は固定化されます。
「探す対象」はB2は「ぶどう」、B3は「トマト」のようにそれぞれ変化します。よって、ここではセルを指定します。
※Numbersの場合はセルを指定しても例のように「探したいデータ みかん」のように表示されてしまい、ちょっとわかりづらいです。
COUNTIF関数を記述したセルを編集状態にするとこのように関数が編集できる状態となります。
さらに、対象となるリストをコピーする必要があるときは、降順に並び替えることで、対象のリストが集まってくるため、簡単にコピーすることができます。
抽出したいデータが別のセルの場合はVLOOKUP関数
探したいデータに対し、その行内の別のセルを抽出したい、という場合があると思います。先程の例に「価格」列を追加しました。
参照後に対象となるデータ（今回の場合は果物）ではなく価格を返したい、という場合はVLOOKUP関数（ブイルックアップ関数）を使います。D列に関数を追加しました。
参照範囲は「果物と価格」とし、探したいデータを参照範囲から探しだし、価格の列を返すように定義しました。
例えばD2だと、探したいデータは「ぶどう」となります。参照範囲であるA2〜B6を探すとぶどうが合致するため、その都内の価格である「80」を表示します。その下のトマトやレタスは参照範囲になく条件に合致しないため、エラーとなります。
=VLOOKUP(探したいデータ、返す値を含む列の範囲、返す列の数字、近似値または完全一致（1/TRUE、または 0/FALSE）)「探したいデータ」は今回だと「探したいデータ」列です。
「返す値を含む列の範囲」は検索対象です。注意点として、返す値まで含める必要があります。今回は果物と価格の2列を範囲としました。また、COUNTIF関数と同様、絶対参照としています。
「返す列の数字」は価格の列を返したいため、検索対象とした2列目である「2」を入力します。
「近似値または完全一致」は完全に一致した場合としたいため、完全一致または0を設定します。
必要に応じてデータの調整や結合にテキストエディタを利用
このようにエクセルだけでも便利に任意のデータを取得することができるのですが、元データによっては検索のために一定の編集や加工が必要となる場合があります。
例えば、顧客リスト内で同一の住所を検索したいのだけど、住所情報が「郵便番号」「都道府県」「市区町村」「それ以下」などとセルが別れている場合です。
エクセルではCONCATENATE関数や&（アンド・アンパサンド）を用いて文字列を結合することも可能ですが、一旦テキストエディタに持っていき、タブに対して一括置換することで結合や別の文字列を挿入することも可能です。
住所ではありませんが、先程の例だと果物と価格部分のセルをコピーしテキストエディタに貼り付けてみます。ちなみに、エディタはSublime Textを利用しました。
セルからペーストした情報にはセル間はタブによって分かれており、タブは正規表現で「\t」で検索できます。結合したいならタブを消せばよく（置換には何も入力しない）、別の文字列で置き換えたい場合は置換に任意の文字列を入力し、一括置換を実施します。
変換後、再度エクセルへデータを持っていき、作業を続けます。正規表現を扱うとデータの編集・加工がよりやりやすくなります。
テキストエディタ、Sublime Text、正規表現などについては以下もご参考ください。
Sublime TextのFind All機能が便利すぎて仕事が捗る
仕事効率化したいデスクワーカーのための正規表現
自分に合ったやり方でいい
このように、データ加工・編集をすることで、手作業や目視での確認だと面倒な作業でも一定の自動化ができ、作業効率・正確性が向上します。
ちなみに今回は取り上げませんでしたが、COUNTIF関数とVLOOKUP関数を組み合わせて、特定の条件で作成した顧客リストから、特定の条件に合致する顧客のみを取り出す、ということも可能です。また、データ加工にはテキストエディタで正規表現を使いました。
いずれにしても、今回ご紹介の方法はただの一つの方法でしかないため、ご自身にとってやりやすい方法で行うのが望ましいと思います。
コメントを残す