Power QueryでExcelのデータを変換する

仕入れ先との取引について、 取引から、確定日までの日数を 調べてほしいとの依頼がありました。 取引に長くかかっている 仕入れ先がないかを確認したいのです。 ここでは、取引の多くが２日以内に 終了しているかどうかを見てみます。 必要なデータは揃っていますが、 分析に必要な計算は、まだ行っていません。 そこで、まずデータを変換してから、 必要な計算を実施します。 クエリと接続を選び、 ここで、「編集」を選択します。 いくつかのデータ変換は、 いつもなら、Excel の関数を作成して 処理しているかと思います。 しかし、今回は Power Query を使っているため、 関数を使用する必要はありません。 まず、仕入先の名前を設定します。 今回は、仕入先名を すべて大文字で表示します。 「仕入れ先名」から「変換」に行き、 「大文字」を選びます。 「取引日」だけでなく 「取引年」も必要です。 「取引日」を右クリックし、 「重複する列」を選んで、 「追加された新しい列」に 年だけを表示します。 右クリックして、 「変換」「年」また「年」を選択します。 名前を「取引年」にしましょう。 そして、「取引日」の左側に移動します。 ここに、２つの金額、 「税抜金額」と「税額」がありますが、 必要なのは、合計金額なので、 その数式を作成します。 列の追加で、「カスタム列」を選択し、 列の名前を「合計金額」にします。 右側の「使用できる列」をスクロールして、 「税抜金額」をダブルクリック、 プラスの記号を追加、 「税額」をダブルクリックします。 構文エラーはないので、 「OK」をクリックします。 合計金額の書式を 「通貨」に設定します。 必要なのは、合計金額だけなので、 税抜金額を右クリックして、 削除します。 税額も削除します。 次に、取引日から確定日までに かかった日数を確認しましょう。 列を追加します。 カスタム列から、 新しい列の名前を日数にします。 取引日を選んで、 マイナス記号を追加し、 「確定日」を選択して、 「OK」をクリックします。 すると、日数が表示されますが、 取引日が確定日より前の日付なので、 マイナスになります。 また、数字ではなく、 時刻形式のように見えます。 そのため、このように、 整数値に変更します。 ここで必要なのは、絶対値です。 そこで、また列を右クリックして 「変換」＞「絶対値」を選びます。 これで、必要な情報は、ほぼ揃いました。 あと必要なのは、 取引日から確定日までが ３日以上か未満かを示す フィールドだけです。 そこで、条件列を使用します。 条件列で日数を確認し、 結果を出力します。 まずは、「条件列」を選んで、 「条件列の追加」を開き、 「オーバーアンダー」と名付けます。 「日数」を選択したら、 「次の値以上」を選んで、 ３と 「３日以上」を入力します。 ２日以内については、 ここに「２日以内」と入力します。 これは、論理関数で 日数に基づいて、条件が真なら真の値を、 偽なら偽の値を返します。 「OK」をクリックすると、 Excel の IF 関数と 同じような処理をします。 これで、分析を始める準備が整いました。 「ホーム」から閉じて読み込むを選びます。 追加した列がすべて表示され、 仕入先名が自動的に 大文字で表示されています。 これで、仕入先との取引が ３日以上かかるかどうかを確認できます。 これで、取引品質についての簡単な質問に 答えることができるようになりました。 まず、ピボットテーブルを挿入します。 「挿入」から「ピボットテーブル」を 選びます。 「Suppliers」を範囲にして、 「新規ワークシート」を選び、 「OK」をクリックします。 「オーバーアンダー」を 「行」にドラッグします。 次に、「仕入先取引 ID」を、 「値」にドラッグします。 数字なので、自動的に集計されます。 これを、「個数」に変更します。 「OK」をクリックします。 この数字を見るだけで、 ほとんどの取引が、 ３日以上かかっていることがわかります。 もうひとつ、簡単な分析をしてみましょう。 右クリックし、「計算の種類」、 「総計に対する比率」を選びます。 すると、確定まで３日以上かかっている 取引が、69% あることがわかります。 ２日以内に終わるのは、 約 31% だけです。 もっと分析する必要がありそうです。 このように、さまざまなちょっとした テクニックを駆使して、 分析のためにデータを変換してみましょう。