おすすめコンテンツ
home

2015年9月4日

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

customer-data-mistake

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

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

customer-data-mistake1

MID関数を使ってみよう

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

customer-data-mistake2

指定した開始位置から文字を取り出せる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”)

customer-data-mistake3

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

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

customer-data-mistake4

VLOOKUP関数を使ってみよう

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

customer-data-mistake5

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

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

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

customer-data-mistake6

次に比較します。

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

  • LEFT(該当セル,3) →先頭3文字分を取り出す
  • LEFT(該当セル,4) →4文字県名対応用

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

customer-data-mistake7

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

customer-data-mistake8

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

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

おすすめコンテンツ

※1 Excelは、米国Microsoft Corporationの、米国およびその他の国における登録商標です。

※2 「エクセルサプリ」 は、独立のコンテンツであり、マイクロソフトコーポレーションと提携しているものではなく、また、マイクロソフトコーポレーションが許諾、後援、その他の承認をするものではありません。

ヒューマンリソシア
Copyright © 2015 Human Resocia Co., Ltd. All Rights reserved.
smp-menu
  • ヒューマンリソシア