データ整理に潜む魔物と戦う話。
突然ですが、皆さん「データの整理」ってどうしてますか？
WEB上のツールを活用してグラフや資料を作成する場合、またデータの入れ替えや整理を行う場合にデータをエクスポートして取り出すことがあります。
また、データを分析する時にそのデータを加工することもあるでしょう。
例えば、スプレッドシートやCSVに出力した「住所」や「会社名」などの一覧を、何らかの理由で整理することとなりました。
「重複を削除する」「名字と名前を別の列に入れる」など、データの整理をしたいときが訪れたとします。
そんな時、より時間と手間をかけずに作業を完了する方法はないものでしょうか？
本日は、そんな時のお話です。
Contents
難易度は高くないけれど、手間がかかるフェーズ。
さて、今回のケースは「CSVに出力した住所、会社名、担当者などの一覧を整理したい」というものです。
基となるデータは、会社で利用している顧客管理ツールから取り出したということにしておきます。
ということは、同一のツールを利用して社内である程度のルールに基づいて使用しているはずです。
しかし。
それでもブレる書き方の嵐。
例えば同じ社名を入力する場合でもいくつかのパターンが想像できます。
スラッシュ（株）
上記はどちらも「スラッシュ株式会社」とわかりますが、データとしては別物となってしまう入力の方法です。
ルールを決めていたとしても、入力する際にエラーになるわけではないのでついつい忘れてしまうことも、便利な方で使ってしまうこともあるでしょう。
弊社でもツールに入力する時に「正式名称で入れろ」「こうしろ！」とルールを定め口酸っぱくリマインドすることがあります。
・・・・さて、それではデータを整理しましょう。
データの件数は約3万件。
エクセル上で置換、ソート、VLOOKUPを活用して作業を進める上ではそう多い件数ではないようにも思えます。
と、ここでもう１つ。
今回のポイントはマスタとしているCSVと新しいシステムで使用するCSVのカラム構成が大幅に異なるということを補足したいと思います。
つまり、単純に重複したデータを削除することや半角を全角に修正するだけではなく、ある条件によってセルを分離したり内容を切り分けたりする必要があるのです。
ケース１）苗字と名前が同じセル内にあるものを分離しろ
方法としては簡単ですね。
空いた列か新しく列を追加して、Flashフィルを使う場合と関数を使う場合、または区切り位置指定を使う場合。
いずれかの方法を選んでサクッと分離することが可能です。
関数を利用する手間を考えると、区切り位置指定がお手軽ではないでしょうか。
また、EXCEL2013以降を利用しているのであればフラッシュフィルがとても便利です。
フラッシュフィル
入力されたデータの規則性をエクセルが認識し、必要とするデータを連続して入力していく機能です。
必要なセルに情報を入力する際に自動認識されますが、上手くいかない場合は画像のようにコピー機能の中から「フラッシュフィル」を選択すると良いでしょう。
区切り位置の指定
タブ、カンマ、セミコロン、スペースなどで区切られた文字を分割することができます。
「赤坂 太郎」を「赤坂」と「太郎」に分割するということですね。
「データ」タブの中にある「区切り位置」から操作ができます。
さて、こうした方法を駆使して作業を進めようと思うのですが、複数人によって作成されたデータを扱う場合にはいくつか問題点も生じます。
でてきた壁
１：苗字と名前がスペースで分かれていたりいなかったり
どっちかに統一しろよ！といいたくなる件。
「赤坂太郎」なのか「赤坂 太郎」なのかということですね。
一見するとどちらも同じ赤坂太郎さんのことを指しますが、データとして作業する場合にはスペースがあるかないかで変わります。
２：謎の表記がある
・・・・つまり？「佐藤」を残せばOK？
テキスト入力をOKとしているフォームに多く見られる現象です。
入力できてしまうが故に、使いやすいように独自の発展を遂げていく・・・
３：ふりがなまで同じセルに振った
ふりがなは！別の入力スペースが！あるのに！
なぜ同じところに入力してしまったのでしょうか。
これでは置換する一手間が増えますね。
とまあ、データのマスタとなるCSVを見ているだけでも頭が痛くなりそうなことが判明しました。
「そんなことあるの？」と思う方もいらっしゃるでしょう。あるあるです。
この話を始めると社内における「共通言語作りの大切さ」を語ることになりますので、そちらはまたの機会に。
ケース２）住所を都道府県とそれ以降に分離させろ
一つのセルに入っている都道府県住所を、任意の場所で分離させたい。
そんな時があります。
エクセルの機能を利用すると、比較的簡単に実現できることなので作業を時短していきましょう！
エクセルのワイルドカードを使う
エクセルにはワイルドカードと呼ばれるある特定の文字を含むものに対しての作業を指示する「*（アスタリスク）」という記号が使えます。
例えば、指定の文字を含むものだけを検索したい場合はどうすれば良いでしょうか。
フィルタや通常の検索をすることで取り出すことも可能ですが、取り出したデータを素早く編集したい、より早く対象の文字を探したい時に便利な機能があります。
それがワイルドカードという機能なのです。
「*」の使い方は３種類。
- 前後につけて「*川*」とし、「川を含む文字列」を探す
- 前方につけて「*川」とし、「川で終わる文字列」を探す
- 後方につけて「川*」とし、「川で始まる文字列」を探す
これを使えば・・・・
例えば住所の整理をするとき、都道府県を別セルに移し、それ以降の住所はいらないので消したいということも可能ですね。
ワイルドカードを用いた検索と置換
「県*」
置換後の文字列
「 」※空白
ぽん！
「置換」は一発で多くのセルの内容を書き換えることができます。
この機能とワイルドカードを組み合わせて、指定文字である「県」の後ろをすべて消してしまおうという魂胆です。
間違えてしまった場合は、「もとに戻す」ボタンで作業を無かったことにしてしまいましょう。
ワイルドカードを用いた抽出の関数
ちなみに、この「指定文字以降、以前」は関数を使って抽出をすることもできます。
これは「県」を含みその左側（LEFT）にある文字を取り出す、という指示の関数です。
RIGHTにするとRIGHT＝右側なので県の右側にある文字列が取り出されます。
こうした関数でも良いと思います。
この場合は「○○県」と「県」の文字も残りますから。
ケース３）特定の条件にあてはまるデータだけ置換しろ
ソートしたセルだけを選択したい。
ある程度規則性のある入力方法で記載されているシートであれば、ソートで並び替えを行ったり絞り込んだりすることもあるでしょう。
絞り込んだデータだけを選んで操作したいのに、離れた位置にあるとなかなか上手くいかない。
セルを絞り込んだところにコピーしたいのに、隠したセルにまでコピーされてしまう・・・・
そんなときの解決方法です。
操作したいセルをソート、必要なセルのみ表示させる
[alt]+[;]
データ＞ソート解除
これで可視化されたセルだけを選択することができました！
この状態から、「ホーム」→「フィル」→「連続データの作成」を選択すると、ソートを掛けた必要なセルだけに連番を振ることも可能です。便利。
検索と置換を呼び出すショートカットキー
意外と知られていないショートカットキー。
こちらはエクセルを使うときだけでなく、メモ帳のテキストでもWEB上のソースでもなんだったらブラウザ上でも発動できます。
「どこにあるかわからないけれど調べたい」単語や文字を検索するときに便利ですよ。
作業を上書き保存するショートカットキー
最後のセルに移動するショートカットキー
そんなに行や列は入れていないのになぜか余分な余白があってデータ量が増えている時
ありますよね、そこまでデータが入っているわけでもないのに空白でスクロールバーが小さくなっているとき。
こんなとき、ファイルを開こうとすると重たかったりするものです。
矢印キー（カーソルキー）を押すと画面がスクロールしてしまう罠を解除する方法
「スクロールロック」がオンになってしまっているのではないでしょうか。
そんなときは、以下のキーを押して解除すると良いでしょう。
さて、色々と便利なショートカットキーや小技はまだまだありますが、今回はよく使いそうなところをピックアップしました。
こうしてデータ整理を時短しながら、必要な形式に整えていくことになります。
定期的なバックアップを忘れずに作業を進めましょう！