せるワザでは、過去にも”Excelへのデータ取り込み窓口”として、パワークエリのご紹介をしてきました。
※CF． 「パワークエリのマージ(結合)機能がとても強力です!」
「有料ソフトいらずで、PDFからExcelへ表を取り出せます!」
しかし、Excel標準機能として、さまざまな使い道があるツールを、データ取り込みにしか使わないのはもったいない!
すこしずつでいいですから、活用の幅を広げてみませんか?
今回は、Power Query(パワークエリ)を使って、マクロやSQLを使わないデータ・クレンジングに挑戦してみます。
CSVなどのテキスト資料は、まずパワークエリ・エディタへインポートしましょう
実務上、「Excelだけ」とか「Outlookだけ」とか、特定のソフトウエア”だけ”を使い続けることは、稀(まれ)ではないでしょうか?
(専門性が高い職種で、業務専用ソフト/ツールをフル稼働させる場合はのぞきます。)
たとえば、
(1)業界標準の基幹系システムで情報を蓄積
(2)CSVやXML形式でデータを取り出して、ExcelやAccessで分析したり、グラフ作成
(3)最後に、成果物をWordやPower Pointでリポート化して、部署内や上席へ報告
というような流れは、多くの職場で一般的にみられるワークフローですね。
しかし、(1)から(2)への橋わたしにExcelを使おうとすると、データが重すぎて動かなくなったり、文字ばけをおこしてしまったり、うまくいかないことがあります。そのような時に、他のデータベースやCSVとExcelとをつなぐのが、パワークエリ・エディタの重要な役割のひとつなのです。
具体的にみてみましょう。
今回は、別の基幹系システムから出力した以下の名簿を、Excelに読み込む前にパワークエリ・エディタでクレンジングしてみます。
CSVファイルを直接ダブルクリックして、Excelでひらいてみた結果がこちら。
ダブルクリックだけで、うまくひらけるケースもありますが、このように文字ばけしてしまうことも多いのではないでしょうか?
なぜなら、CSVファイルとパソコンのExcelと、使用する文字コードが異なる場合が多いからです。
そこで、手持ちのCSVは直接Excelで開くのではなく、パワークエリ・エディタを介して操作することを習慣化してみましょう。
［データ］→［データの取得と変換］→［テキストまたはCSVから］→該当のファイルを選択して［インポート］をクリックすると、パワークエリ・エディタのプレビュー画面があらわれます。
IDが整数型(本来は0001/0002のような表示形式のデータが、ゼロ無し数値へ自動変換)だったり、元データに重複するレコードがあったりしますので、これをExcelへ読み込む前に、パワークエリ・エディタ上で直してしまいましょう。
［データの変換］をクリックして、ID列を選択し、［データ型］のプルダウンメニューから［テキスト］を選択すると…
IDが正しい表記に直りました。
さらに、仮に「年齢」列が不要だとするなら、［列の削除］をクリック。
重複レコードを排除したければ、［行の削除］プルダウンメニューに［重複を削除］メニューがありますので、これをクリックすれば、一意のレコードだけが残ります。
データ・クレンジング作業が終わったら、最後に左上の［閉じて読み込む］をクリックすれば…
思いどおりにデータ整形をすませた資料を、Excelワークシートへ読み込むことができました。
元のファイル情報をくずさず、余計な自動変換をさけるには、パワークエリ・エディタでプレビューする必要あり!
じつは、IDを整数型に直してしまうExcelのおせっかい機能については、世の中に困っている人がたくさんいたらしく、2023年秋に改善されました。
(「Excelでだってアタマをゼロ（0）にしたい！ 郵便番号や電話番号もシートで正確な表示をする方法」をご参照下さい。)
Microsoft 365などの最新版Excelでは、CSVをExcelで直接読み込む前に「先頭のゼロを削除するか、しないか」を問うダイアログボックスが出て、そこで修正をすませてしまうことができるのです。
それでも、Excelで直接CSVファイルを開くことをさけるべき理由は2つあります。
1つ目は、元データに手を加えず保存したままで、思いどおりにデータ整形したExcelワークシートを入手できる点。
もう1つは、先頭ゼロ以外にも起こりうる、おせっかいな自動変換をさけるためです。
たとえば、今回のサンプル名簿では、住所情報が1列(1レコード)にまとまっていましたが、データベースの作り方によっては住所を、「都道府県」「市区町村」「それ以降の番地」「建物名および部屋番号」に切り分けることがあります。
もし「市区町村」と「番地」がカンマで区切られた名簿CSVを、Excelで直接開いたらどうなるでしょう?
本来「4-2-10」「1-4-4」という番地情報だったものが、ハイフンが飛んでシリアル値化され、日付データへと自動変換されてしまったりするのです。
ここまでわかりやすい誤変換は、後から修正もできるでしょうが、複雑なレコードが数千件、数万件ある場合、そのExcel自動変換を目視チェックで正確に見つけ出すのは、ほぼ不可能な作業でしょう。
そのようなリスクを負ってしまう前に、あらかじめパワークエリ・エディタで「データのあるべき姿」をプレビューしてからExcelへ読み込むひと手間が、結果としてデータ誤入出力をさけることにつながるのです。
まとめ;CSVなどのテキスト・データをExcelで使うなら、直接ひらかず、パワークエリ・エディタでまずプレビュー!
直接できる作業に、わざわざパワークエリ・エディタというツールを使う理由、おわかりいただけたでしょうか?
取り扱う資料の容量が大きく、内容が重要なものであるほど、正確性とともに、トレーサビリティの確保も必要になるでしょう。その点では、Excelの自動変換は、時に非常に危険なレスポンスを返すことがあるのです。
ですから、たとえExcelから直接開けるテキスト・ファイルであったとしても、一度パワークエリ・エディタのプレビュー画面で、正確性を客観的に見直してみることをおすすめします。
ひと手間、ふた手間かけて回り道の作業をしてみることが、実は業務時短につながるかもしれませんよ。
