長年、私は簡単な計算や基本的な表の作成にExcelを頼りにしてきました。よく使う数式を使ったり、簡単なデータ操作をしたりすることはあっても、プロジェクトが複雑化するまでは、Excelのより複雑な機能を深く掘り下げる必要性を感じたことはありませんでした。
私の探求のきっかけとなった挑戦
私の住む地域では、PCパーツの価格が高騰しており、市場の変動や輸入税の影響で米国よりも高くなることも少なくありませんでした。そこで、価格差を調査することにしました。地元の小売店ではなく、AmazonやNeweggなどのプラットフォームから直接購入する方が経済的かどうかを調べようと考えたのです。そのために、CPU、GPU、RAMといった主要なPCパーツの価格データを数か月分収集しました。当初は単純な追跡プロジェクトだと思っていたのですが、あっという間に困難な作業へと変わりました。
私が集めたデータは混沌としていました。各小売業者のファイルの形式が異なっていたため、統合は困難を極めました。例えば、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 エディターで簡単な手順を開発し、乱雑な CSV ファイルをクリーンアップして、一貫性のある整理されたスプレッドシートに変換しました。
まず、Power Query エディターにデータをインポートしました。新しい Excel ブックを開き、リボンから「データ」を選択し、 「テキスト/CSV から」を選択しました。CSV ファイルを選択した後、「データの変換」をクリックしてPower Query エディターに入りました。
最初のステップは、日付列を標準化することでした。2つのソースのタイムラインが12時間離れているため、日付の書式設定を統一することが不可欠でした。このプロセスは驚くほど簡単でした。日付列をハイライトし、右クリックして「種類の変更」>「ロケールの使用」を選択しました。表示されたポップアップで、種類を「日付」に設定し、書式設定の一貫性を保つために「英語（米国）」を選択しました。Power Queryは、MM/DD/YYYY、YYYY/MM/DD、DD-MM-YYYYといったさまざまな形式を巧みに識別し、簡単に単一の形式に標準化しました。
日付形式を修正したら、列のクリーンアップに進みました。ほとんどのエラーはデータスクレイピングツールに起因していたため、「エラーの削除」フィルターを使用して誤ったエントリを削除しました。この手順により、null値と問題のある残りのデータが効果的に削除され、すべてのファイルで日付の表示が統一されました。
次に、 「値の置換」機能を使って、ブランド名の混乱を解消しました。該当する列を選択し、右クリックして「値の置換」にアクセスし、ブランド名のあらゆるバリエーションを入力して標準化しました。例えば、「gigabyte」と「GIGABYTE INC.」を「GIGABYTE」という統一されたエントリに置き換えました。AMDにも同じアプローチを適用することで、すべてのデータソースでブランド列を効果的に整理できました。
-
スクリーンショット：ジェイリック・マニング – クレジット表記不要 -
スクリーンショット：ジェイリック・マニング – クレジット表記不要
最後に、価格列を標準化しました。多くの項目が通貨に関する情報を欠いた単なる数字だったため、変換作業が複雑になり、課題となりました。幸い、店舗列に即座に情報が提供されたため、Shopee PHの価格をUSDに簡単に変換できました。
この問題を解決するために、まず「値の置換」機能を使って「Price」列からすべての通貨記号（例：$、₱、USD、PHP）を削除しました。「Change Type」>「Decimal」を選択してこれらの値を小数点形式に変換した後、「 Column」タブの「 Custom Column」を使って「Price USD」という新しい列を作成しました。そして、以下の数式を実装しました。
if [Store] = "Shopee PH"then Number. Round([Price] / 55, 2) else Number. Round([Price], 2)
「OK」をクリックすると、Shopee PH のすべての価格が明確に比較できるように USD に変換されます。
この数式はPower Query M 数式言語で作成されています。このスクリプト言語は、比較的理解しやすいシンプルな構文を備えています。効果的に活用するために言語全体を習得する必要はありません。私は数式を作成するために必要な場合にのみ使用しています。
データが修正されたら、Power Query エディターを閉じて [保存] をクリックし、ワークブックに加えられたすべての変更を保存します。
Power Queryによる時間節約
当初、Power Queryの導入をためらっていました。時間の無駄になるような複雑な機能が追加されるのではないかと心配だったからです。しかし、予想に反して、Power Queryははるかにシンプルでした。検索と置換を延々と繰り返す代わりに、Power Queryを使えばスクレイピングツールで生成されたデータを効率的にクレンジングできるようになりました。
さらに驚いたのは、私が実施した調整がすべて文書化されており、クリーンアッププロセスを簡単に再現できたことです。これは本質的にデータクリーンアップの自動スクリプトとして機能し、乱雑なCSVファイルを整理されたスプレッドシートに変換します。Webスクレイピングで生成されたカスタムデータセットなど、データ品質が不安定な状況に最適です。
面倒なデータクリーンアップ作業、一貫性のない形式、複数のデータソースを頻繁に管理している方にとって、Power Query はこれらのプロセスを、管理しやすい自動化されたワークフローへと効率化します。毎週何時間もかけて手動で問題を修正する代わりに、「更新」をクリックするだけで、すぐに分析作業に取り掛かることができます。これはExcelの強力な機能であり、もっと早く導入しておけばよかったと思っています。自動化され、簡単に繰り返し実行できるクリーンアッププロセスの効率性を一度体験したら、もう後戻りできません。
コメントを残す