関数を用いて顧客データの間違いを抽出する方法

顧客データを入力しているときにありがちなのが、「顧客住所の都道府県名を入力し忘れる」ということ。こういうものはその顧客データを使用するときになって、ミスが発覚するものです。

今回はそんなデータを抽出する方法を、関数を用いて行ないます。

MID関数を使ってみよう

都道府県名はほとんどが3文字ですが、「神奈川県」「和歌山県」「鹿児島県」のみ4文字になります。そのため、単に住所の先頭3文字目を比較すればよいわけではありません。

指定した開始位置から文字を取り出せるMID関数を用いて抽出してみましょう。

セルの3文字目を取り出す式はMID（該当セル,3,1)になります。この3文字目が都道府県いずれかになっているかどうかを探すため、OR関数を使います。

OR(MID(該当セル,3,1)=”都”,MID(該当セル,3,1)=”道”,MID(該当セル,3,1)=”府”,MID(該当セル,3,1)=”県”))

神奈川県、和歌山県、鹿児島県の場合は4文字目に「県」が来ますので

OR(MID(該当セル,4,1)=(“県”))

これをIF関数で記入すると以下のとおりになります。

=IF(OR(MID(F2,3,1)=”都”,MID(F2,3,1)=”道”,MID(F2,3,1)=”府”,MID(F2,3,1)=”県”,MID(F2,4,1)=”県”),”OK”,”NG”)

これで都道府県が入っていないデータは「結果」項目にNGと表示されます。

どのデータがNGかわかりやすくするために、条件付き書式を設定してあげるといいでしょう。Excel2007以降をお使いの方は、下記のサイトを参考にして、特定の文字が入った列に色をつけてみてください。

しかし、上記の方法だと、文字数は一致していても変換ミスなどは発見できません。例えば「茨木県」や「名古屋県」などというものは抽出できないのです。

こういった誤字や変換ミスをなくすために、VLOOKUP関数を使って抽出してみましょう。

まず、47都道府県のリストを作成します。

予めチェック用のセルシートを作っておくと作業効率が上がる上に、他のチェック時にも流用できるのでお勧めです。今回は”CITY”と名前をつけてあります。

次に比較します。

今度は左から指定した文字数を取り出すLEFT関数を使います。

=IF(ISERROR(VLOOKUP(LEFT(F2,3),CITY,1,0)),IF(ISERROR(VLOOKUP(LEFT(F2,4),CITY,1,0)),”NG”,”OK”),”OK”)

※上記の計算式に入っているISERROR関数は、エラーが出たときTRUEを返す関数です。ここでは、TRUEと出た際に“NG”が返ってきます。NGが返ってきた行に問題が無いかを確認しましょう。

このように、関数を使うことで間違いデータの抽出が効率良くできます。

人間の作業ですから、ミスはつきものです。それをサラリとカバーして、業務をこなすことで、あなたも社内で一目置かれる人になれるでしょう。