こんにちは、Excelパーソナルトレーナー 土谷です。 本日はデータクレンジング方法の7回目。以前ご紹介した下記14手順の内、手順5をご説明いたします。
1 クレンジングの対象、範囲を決定
2. 法人名の半角と全角を統一(JIS関数)
3. 冒頭、末尾のスペース、改行を削除(TRIM関数、CLEAN関数)
4. 法人格の表記揺れを解消(文字の置換)
5. 都道府県、市区町村の表記揺れを解消(フィルタリング)
6. (存在するのであれば)法人番号や企業コードで並び替えを実行
7. (存在するのであれば)法人番号や企業コードの一致するものを精査、名寄せ
8. 法人名、都道府県、市区町村で並び替えを実行
9. 法人名、都道府県、市区町村、その他の付帯情報で総合して精査、名寄せ
10. 都道府県、市区郡町村で並び替え
11. 都道府県、市区郡町村、丁目、番地、URL等その他の付帯情報から法人格未記載の情報を精査、名寄せ
12. 上記手順で不明なデータを記載者に直接確認
13. クレンジング不可能なデータの取り扱いを判断
14. 主キーを付与
5. 都道府県、市区町村の表記揺れを解消(フィルタリング)
都道府県、市区町村の表記揺れでよく見られるのは「政令指定都市の区の記載場所」と「[町域]と[行政町](以下[町])の区別」の2パターンであると考えています[注1]。
今回の「都道府県」列、「市区町村」列、「その他」列の3列で分割するパターンの場合、下記のように分割します。
・「都道府県」列：都道府県名のみ。市区町村以下の情報は記載しない。
・「市区町村」列：政令指定都市の場合は[市]と[区]の二つとも記載[注2]。
また、[町域]は記載しない一方、[町]は記載。
・「その他」列 ： [町域]以下を記載。
上記のルールに従って表記揺れを解消していきます。
※[町域]と[町]の判別が大変な場合：関係部署で意思統一が出来れば、[町]と[町域]のいずれも「その他」列に含めることで手順5の工数を半分以下に出来ます。
(私の経験上ではまとめたことでの大きな影響はありませんでした。)
以下の手順では見やすくするため、電話番号等のH列～K列を非表示にしてご説明します。
また、今回に限り[医療法人BCD会]の住所を”埼玉県入間郡三芳町”に変更します。
(a)項目名記載セルの全範囲を選択(今回は「B1」セル～「G1」セル)
(b) [「データ」タブ → 「フィルタ」ボタン]の順番に押下してフィルタを適用
([「ALT」キー → 「A」キー → 「T」ボタン]の順に押下しても適用可能)
(c) 「住所(その他)」列の項目名セル(今回は「G1」セル)の右下の”▼”ボタンを押下
(d) 表示されたウィンドウ内の「検索」ボックスに”区”と記載
(e) 「OK」ボタンを押下しフィルタリング
(f) 表示されたデータに政令指定都市が含まれていた場合、[区]の名前を「住所(その他)」列から「住所(市区町村)」列に移動
(データが無い場合は次へ)
(g) [「データ」タブ → 「クリア」ボタン]の順番に押下してフィルタをクリア
([「ALT」キー → 「A」キー → 「C」ボタン]の順に押下してもクリア可能)
(h) 「住所(市区町村)」列の項目名セル(今回は「F1」セル)の”▼”を押下(手順(c)参照)
(i) 表示されたウィンドウ内の「検索」ボックスに”群”と記載(手順(d)参照)
(j) 「OK」ボタンを押下しフィルタリング(手順(e)参照)
(k) データが存在していた場合、「住所(その他)」列の項目名セルの”▼”を押下(手順(c)参照)
(データが存在しない場合、手順(o)へスキップ)
(l) 表示されたウィンドウ内の「検索」ボックスに”町”と記載(手順(d)参照)
(m) 手順(e)と同様、「OK」ボタンを押下しフィルタリング(手順(e)参照)
(n) [町]の名前を「住所(その他)」列から「住所(市区町村)」列に転載
(データが無い場合は次へ)
(o) [「データ」タブ → 「クリア」ボタン]の順番に押下してフィルタをクリアし、修正完了!!
([「ALT」キー → 「A」キー → 「C」ボタン]の順に押下してもクリア可能)
上部でも述べましたが、[町]と[町域]を区別しなければ、手順(a)～(g)の7ステップとなります。関係部署全体で区別の必要が無いと認識出来れば、省略をご検討ください。
これで「都道府県」、「市区町村」の表記ゆれを解消できました。
次回は「6. (存在するのであれば)法人番号や企業コードで並び替えを実行」、
「7. (存在するのであれば)法人番号や企業コードの一致するものを精査、名寄せ」
をご説明いたします。
---------------------------------------------
[注1] ：[町]には[町域]と[行政町]が存在します。
[町域]とは市、区の郵便番号の振り分けに用いられている区分です。
基本的に市、区に振り分けられている町名は[町域]と考えて良いと思います。
(例：”神奈川県横浜市中区曙町”の”曙町”部分)。
一方、[市]に属さず[町]として独立している場合、[行政町]と呼びます。
基本的に[郡]に帰属します。
(例：”埼玉県入間郡三芳町”の”入間郡三芳町”部分)。
[注2] ： 政令指定都市は[市]の次に[区]があることにご注意ください。
「市区町村」列に[市]と[区]のいずれも記載します。
簡単Excel集計にご興味のある方は、是非ともお問合せ下さい！
Excel集計のお悩み相談はこちらから
(1) 急に上司にデータを出せと言われて残業
(2) 単純業務に時間が多く取られる
(3) ミスが多くて怒られる
(4) もっと集計データを活用したい
特にこのようなお悩みをお持ちのあなたにお勧めです！
無料相談はこちらから