【コツ徹底解説!】エクセル データ整理・クレンジングの早ワザと失敗例②|整理方法

【コツ徹底解説!】エクセル データ整理・クレンジングの早ワザと失敗例②|整理方法 1024 671 Biz Tips Collection

前回の「【コツ徹底解説!】エクセル データ整理・クレンジングの早ワザと失敗例①|作業設計」では、膨大なデータをエクセルで整理する際に気をつけなければならないことについて触れた。データの内容を確認し、どのようなデータへ整理していくかの設計図を作成し、上司と握ることが重要と述べた。今回は実際のデータ整理においてどのような方法(エクセルの帰納や関数)を利用してアウトプットデータを作成していくか述べるものとする。

なお、本シリーズで説明する内容は筆者が考える最短でデータ整理をするために利用する方法論と全機能である。複雑な関数や機能を利用することで欲しいデータを作ることは可能かもしれないが頭を使ったり、試した結果できなかったりすることを考慮すると本稿で紹介した手法のみでデータクレンジングをやり遂げる気持ちで業務を遂行して欲しい。

 

エクセルでデータ整理を行う際に利用する機能と関数

前回整理するデータに葉以下の3種類のデータがあることを紹介した。まず、データの項目の内容を確認する際とそれらのデータを整理する際に利用するエクセルの機能と関数を対応させて紹介する。
データの項目の内容を確認
ー重複削除
-フィルタによる降順並び替え
-区切り位置の指定

重複データ
ーフィルタを利用したフラグ付け
ー重複削除

Nullデータ
ーフィルタを利用したNullデータの書き換え

異型データ
-一括置換
ー全角カナを半角カナに変換するASC関数
-文字データを数値データにするVALUE 関数
-【参考】表示形式の変更が反映されない場合に利用するVBA

 

重複削除

重複削除は「データの項目の内容を確認」と「重複データ」の整理の際に利用するエクセルの機能だ。機能の内容は文字通り、選択した範囲から同一のデータを削除する機能だ。これにより、データ項目に重複したデータがどれくらいあるかを把握し、実際に重複しているデータを消しこんでいくことになる。

それでは具体的な利用方法について確認していく。なお、以下同様の整理手法についても同様であるが、作業する際は必ず元データを直接編集しないようにして欲しい。元データを残しておかないと、作業に失敗した際など、元に戻れなくなる恐れがある。

 


重複削除機能は、以上の様にデータタブのデータツール内にある。
項目をクリックすると以下のようなポップアップが出現する。

 


この画面ではデータが重複している場合に一緒に消すデータの列を選択する画面だ。データ項目の内容の確認の場合は対象となる列のみを選択してやればいいので重複確認をしたい項目のみチェックしてその他全てのチェックをはずす。重複データ削除の際はレコードを同時に削除するので重複をチェックしたい対象となるカラムの列を選択しておこう。

こうして重複データを削除していく。

 

フィルタによる降順並び替え

フィルタによる降順並び替えは、「データの項目の内容を確認」で利用する機能だ。その他の作業でも実施作業が反映されているか確認する際に利用できる。内容は、フィルターをかけてあいうえお順や数字順に並べ替える機能だ。不規則なデータを一定の規則によって並び替えることでデータの規則性を把握しやすくなる。

使い方は以下の通りだ。
整理するデータ全体を選択し、以下のボタンを押す。フィルターボタンはデータタブの並び替えとフィルター内に存在する。なお、データの項目名(カラム)を設定せずに空白にしているとフィルターを正しく設定できないので、メモ的なカラムだったとしても何かしらの名前をカラムに命名しよう。

フィルタボタンを押すと、下の図のような▽マークがカラムにつくので並び替えをしたいカラムの▽マークをクリック。
すると、ポップアップが出てきて降順を選択しよう。

 

 

区切り位置の指定

区切り位置の指定は「データの項目の内容を確認」の際にひとつのデータになっているが分解すれば使えるデータを整理する際に利用する。カンマ区切りのデータやIDなどの采番規則内に年齢が含まれていることがわかった場合などに利用する。例えば、「男性,44歳,東京都」・「22114898020170912」(最後の8桁が日付になっている)などの場合だ。

利用法は対象となるセルを選択し、データタブのデータツールにある区切り位置をクリックする。

どの後、どのような区切り方をするかの以下のような画面が出てくるので、データに応じて設定をするだけだ。なお、分割されたデータは右側のセルにずれていくので、右側にデータの入ったセルがないような状態にして利用しよう。

なお、左右のデータのみの抽出であれば、RIGHT関数やLEFT関数、MID関数を利用したすることで大体可能だ。
利用方法は
=RIGHT(対象となるセル,抜き出す文字数)
だけで完了だ。

 

フィルタを利用したフラグ付け

フィルタを利用したフラグ付けは、「重複データ」で利用する機能だ。そもそもフラグ付けとは作業者が認識できる目印のようなもので、例えば「男」で「50歳以上」の人にフラグをつけて管理しやすくしようとする場合などに使える。名寄せの際などに同姓同名だが違う人を見分けるフラグとして利用するのも効果的だ。

使い方は前項のフィルタと同様のなのでフラグ付けをメインに解説する。フラグの付け方は簡単だ。
以下の図のように対象となるもの1,0などのフラグをつければよい。まずは手作業でやる場合。

フラグ用のカラムを作り、先ほどの手順でフィルターをかける。
その上で、例えば40代の人を抽出するのであれば、▽マークがカラムを選択し上記図真ん中の図ように40代にチェックを入れる。

その上で、フラグのセルに1を入れていく。この際、データ量が多い場合コピー&ペーストを使いたいが気をつけて欲しい。単純にコピー&ペーストをするの消えているはずのレコードにも1が入ってしまうので以下の手順をはさんでコピー&ペーストを実行しよう。

もうひとつの方法として関数を使う方法がある。利用する関数は最も有名なIF関数だ。
IF関数で必要な条件を立てて”1”、”0”などのフラグを立てるのも良いだろう。

 

フィルタを利用したNullデータの書き換え

フィルタを利用したNullデータの書き換えは、「Nullデータ」を整理する際に利用する機能だ。Nullデータがあると集計作業で問題が発生しやすいのでN/Aなどを入れるようにしよう。

利用方法は前項で説明した「フィルタを利用したフラグ付け」のフラグと同様のやり方でフラグの代わりにN/Aを記載すればよい。

 

一括置換

一括置換は「異型データ」の修正で利用する。機能は「マチガイ」→「マチガイ」などに置換することだ。Ctrl+Hで置換ポップアップの呼び出しが可能である。タブでは以下、ホームタブの編集の一番右側にある。

 

全角カナを半角カナに変換するASC関数

ASC関数は「異型データ」の修正で利用する。機能は全角文字を半角文字に変換する。逆に半角文字を全角文字に変換するのはJIS関数だ。状況によって使い分けて欲しい。いずれの関数ももともと全角または半角のものはそのまま表示されるので、本関数を記載した列追加すればそのまま利用可能となる。

利用方法は
=ASC(対象となるセル)
だけで完了だ。

 

文字データを数値データするVALUE 関数

VALUE 関数は「異型データ」の修正で利用する。機能は文字データを数値データに変換する。アクセスを知っている方であればより意識をしたことがあるであろうが、データには型というものが存在する。エクセル上で文字データとして認識されている”1”は、例えばSUM関数などで集計することが出来ない。データ型の詳細説明は省くが、この関数はデータ型を修正しカウント可能なものに変更する。

例えば、電話番号などは文字データとして認識されるケースが多く、データの抽入方法によってはIDなどの連番も文字データとしてカウントされてしまうことがあるので、それを数値として利用したい場合に本関数を利用する。
データ型は下記の表記を見れば確認可能だ。

利用方法は
=VALUE(対象となるセル)
だけで完了だ。

 

【参考】表示形式の変更が反映されない場合に利用するVBA

なお、上記「文字データを数値データするVALUE 関数」で解説したデータ形式は以下のように変更することも可能だ。

しかし、データが量が非常に多い場合、データ型の表示は変わっているのにセル内部に反映されていないケースがある。その際、いちいちセルをクリックしなければ反映されない状況になってしまうのでデータ量が多い場合は実行性にかける。そんな時は以下のVBAを試して欲しい。

方法は、開発タブにあるVisual Basicを押して以下の作業を図の通りに実施するのみだ。選択されている列を上から順にクリックするというVBAなので自動でクリック反映作業を実施してくれる。

以下、コードをコピペしてそのまま図の通りに貼り付けて実行しよう。

Sub reconfirm()
For Each e In Selection
e.Value = e.Value
Next
End Sub

 

知ってる方法でどう解決するかに注力しよう!

冒頭に解説したとおり、上記で紹介した方法がデータクレンジングを行うのに必要最低限かつ十分な手法だ。エクセルにはさまざまな機能があり大変便利であるが機能や関数の理解が不十分であると思わぬ作業手戻りにつながる。あたらしい方法を試すのものよいが知っている方法でどう解決するかに注力しよう。一方で、確かに新しい方法を使えるようになると作業設計の幅が広くなるのも事実だ。一回のデータクレンジングにつき新しい方法1個くらいを目安に習得していくのは悪くないかも知れない。

最後にまとめとして、必要最低限かつ十分な手法をおさらいする。
データの項目の内容を確認
ー重複削除
-フィルタによる降順並び替え
-区切り位置の指定
重複データ
ーフィルタを利用したフラグ付け
ー重複削除
Nullデータ
ーフィルタを利用したNullデータの書き換え
異型データ
-一括置換
ー全角カナを半角カナに変換するASC関数
-文字データを数値データにするVALUE 関数
-【参考】表示形式の変更が反映されない場合に利用するVBA