私はいつもExcelを簡単な計算や簡単な表の作成に使ってきました。しかし、よく使う数式や基本的なデータ操作テクニックを除けば、Excelの関数を新たに学ぶ必要性を感じたことはありませんでした。プロジェクトが複雑になってくるまでは。
クイックリンク
ついに私が注目することになった問題
様々な市場要因と輸入関税の影響で、私の住む地域ではコンピューター部品の購入価格が米国よりも高くなることがよくあります。同じ部品を米国で購入するとどれくらい高いのか、そして地元の小売店ではなくAmazonやNeweggから直接注文する方がお得なのかを知りたいと思いました。そこで、地元の小売店が通常輸入している主要なコンピューター部品（CPU、GPU、RAM）の価格データを数ヶ月にわたって収集しました。簡単な追跡プロジェクトだと思いませんか？しかし、そうではありませんでした。
あっという間にデータがめちゃくちゃになってしまいました。各小売業者がそれぞれ異なるフォーマットで情報をエクスポートしていたため、ファイルを統合するのはほぼ不可能でした。AmazonはMM/DD/YYYY、NeweggはYYYYMMDD、Shopee（私の近所の店）はDD-MM-YYYYという形式で日付を提供していました。
不一致はそれだけにとどまりませんでした。列名も大きく異なっていました。Neweggでは価格が「retail_price」と表示されているのに対し、Amazonでは「unit_price_usd」、Shopeeでは「price_php」と表示されていました。価格のフォーマットも同様に問題があり、ファイルによっては通貨記号を含む「₱18,600」と表示されているのに対し、「320」のような通常の数字が表示されていました。ブランド名にも一貫性がなく、同じメーカーなのに異なるファイルでは「gigabyte」「GIGABYTE INC.」「Gigabyte Tech」と表示されていました。
このデータを手作業でクリーニングしてマージするだけでも、すでに何時間もかかっていました。ファイル間のコピー＆ペースト、矛盾する値の検索と置換、空の行を一つずつ削除するといった作業も必要でした。価格比較のためにPHPをUSDに変換するには、為替レートを確認するために常に別の画面を見る必要がありました。全体的に、この作業は退屈で、間違いが起きやすく、ほとんど諦めかけていました。
そこで私は、Excel愛好家がいつも話題にする機能の一つであるPower Queryを使うことを思いつきました。 Excelが提供する他の多くの強力な機能しかし、Power Queryが私の特定の問題に最適なツールだと聞いていました。そこで、YouTubeのチュートリアルをいくつか見た後、Power Queryエディターを使ってインターネットから収集した雑然としたデータを整理すれば、どれだけ時間を節約できるかすぐに実感しました。Power Queryを使えば、様々なソースからデータを簡単にインポートし、標準化された形式に変換して効率的に分析できるため、コンピューター部品の価格分析プロジェクトにかかる貴重な時間と労力を節約できます。
Power Query を使用して非構造化データをクリーンアップするにはどうすればよいですか?
しばらくして、Power Query エディターでシンプルなステップバイステップのプロセスに落ち着きました。ここでは、乱雑なCSVエクスポートを整理し、一貫性のある整理されたスプレッドシートに変換した方法を具体的にご紹介します。
まず、空のブックを開いて、 Rescale データ リボンで、 テキスト/CSVから次にCSVファイルを選択してクリックしました データを変換する Power Query エディターを使用して開きます。
まず日付列を修正しました。12時間の時差があるXNUMXつのソースからデータを収集していたため、日付を統一する必要がありました。これは非常に簡単でした。列を定義しました。 日付右クリックしてコンテキストメニューを開き、 タイプを変更 > ロケールを使用ポップアップメニューでタイプを 日付 そして決まったのは English (United States) 一貫した書式設定を確保するために、Power Query は、MM/DD/YYYY、YYYY/MM/DD などのさまざまな形式や、DD-MM-YY などの記号を使用する変数を自動的に認識し、すべてを 1 つの日付形式に統合します。
日付形式を修正したので、列をクリーンアップするだけです。 كناك Excelスプレッドシートをクリーンアップするさまざまな方法しかし、すべてのエラーはスクレーパーによって生成された不正なエントリであったため、フィルターを使用することを選択しました。 エラーを削除 これらのエントリを削除します。 この手順により、null 値と、適切に記録されなかった残りの問題のあるデータが削除され、すべてのファイルにわたってクリーンで一貫した日付が残りました。
次に、ブランド名の乱雑さを機能で解決しました。 値の置換前回と同様に、対象の列を選択し、右クリックしてコンテキストメニューを開き、 値の置換ポップアップ ウィンドウで、フィールドに矛盾する値を入力します。 見つけるべき価値 そして私のフィールドでの標準値 置換フィールド.
これをさらに2回繰り返し、最終的にすべてのファイルで「gigabyte」と「GIGABTYE Inc.」のエントリを「GIGABYTE」という単一の統一された名前に変更しました。AMDでも同じことをしたので、GPUのブランド列全体が標準的なブランド名になりました。
Power Query: 作業時間の短縮に役立ったこと
Power Query を避けていた理由の一つは、複雑な機能で習得に時間がかかるだろうと思ったからです。しかし、実際には予想以上に簡単でした。検索と置換のコマンドを延々と実行する代わりに、Power Query を使えばデータ収集ツールからデータを素早く自動的にクリーンアップできます。
Power Queryで一番驚いたのは、実行したすべてのコマンドが記録され、何度でも繰り返し実行できることです。これは、乱雑なCSVファイルを整理されたスプレッドシートに変換できる自動クリーンアップスクリプトのようなもので、複雑なデータを扱う場合に最適です。 ウェブスクレイピングを使用してカスタムデータセットを作成するこれらのツールは、多くの場合、不正確なデータを生成するためです。
定期的なデータクリーンアップ、一貫性のない形式、複数のデータソースに悩まされている方にとって、Power Queryはこれらの負担をシンプルで自動化されたプロセスに変えてくれます。毎週何時間もかけて手動で修正する代わりに、「更新」ボタンを押すだけで分析を開始できます。これは、ずっと前に導入しておけばよかったExcelの機能です。自動化され、繰り返し実行可能なクリーンアップスクリプトの威力を一度体験したら、もう後戻りはできません。Power Queryは、データ処理にかかる時間と労力を節約する強力なツールであり、効果的なデータクレンジングと変換のための高度なソリューションを提供します。
