住所録の入力の表記がこの様に、バラバラの状態のデータを整える作業はよくあると思います。得に、データを入力している方が日によって別々だったり、部署別で入力方法が違ったりすると、集められたデータを確認したら、このままでは使用できない・・・・ということがあります。この状態をクリーニングしないといけません。
今回は、パワークエリを使った、クレンジングと、関数を使用して、データを統一する方法をご紹介します。
住所録の入力がバラバラ 統一する方法
Powerqueryでクレンジングする為に、データをテーブル化
Powerqueryで情報を整理する為にデータを先にテーブル化します。
表の中の何処かをクリックして、キーボードの【Ctrl】+【T】を押すと、データ全体が波線で囲まれます。
又、【テーブルの作成】ダイアルボックスが表示されます。ここでは、【先頭行をテーブルの見出しとして使用する】にチェックを入れて、【OK】ボタンを押します。
すると、この様に、表がテーブル化します。表のテーブル化は別途解説してますので、コチラをご参考ください
テーブル名を作成
【テーブルデザイン】タブから、【テーブル名】を住所録と入力して、エンターキーを押します。すると、このテーブルが、住所録に変更されます。
Powerqueryに接続
【データ】タブから【テーブルまたは範囲から】をクリックします。
すると、この様な、Powerqueryエディタが立ち上がります。別のウォンドウでひらきますので、元のデータはシート上に残っています。こちらのPowerqueryで情報を整理します。
クレンジング
頭の氏名に全角のスペースがあったり、半角スペースが入ったりしており、バラバラに表示されてしまっています。
全角・半角スペースは不要です。そこで、パワークエリエディタで情報の整理をします。
パワークエリエディタ上で、氏名の欄をクリックして、【変換】タブをクリックします。【書式▼】をクリックして、【トリミング】を選択します。
すると、先頭の文字が左に寄ります。
こちらの氏名のカタカナは、全角と半角で入力されてあり、統一されてないので、後から関数で全角に統一します。
性別の欄は、男性だったり、男だったりと統一されていません。男性・女性の性を外します。コチラは、パワークエリエディタで操作できるのですが、Excelの標準機能で置換を使い、後から整理していきます。
〒は、無しで統一します。ここでは標準のExcel画面でで操作します。又、郵便番号もハイフンも同じです。住所の番地も大文字と小文字が混じっています。こちらもExcelの標準機能で実装します。
そして、Powerqueryを閉じて読み込みます。
すると、この様に、Excel画面に読み込まれます。
関数で処理
関数で全角と半角
こちらの氏名のカタカナは、全角と半角で入力されてあり、統一されてないので、関数で全角に統一します。
表がテーブルとなっているので、F列に関数を入力すると、スピルというエラーが起こりますので、G列にJIS関数を使用して、半角を全て、全角にして、B列に値を貼り付けます。そして、G列は削除します。
後は、置換して、性と‐を消します。
また、表示形式を郵便番号にします。
詳しくは動画で確認してください。
住所の番地を大文字から小文字にする場合はコチラの記事をご参照ください
【Excel】エクセルで番地の全角・半角を統一する方法
まとめ
住所録の入力の表記がバラバラの状態のデータを整える作業はよくあると思います。
今回は、パワークエリを使った、クレンジングと、関数を使用して、データを統一する方法をご紹介しました。
コメント