マスタデータの履歴管理を行う方法

パワークエリの「クエリのマージ機能」はとても便利で、エクセル関数のVLOOKUP関数より使いやすいです

「クエリのマージ機能」を有効活用すれば、参照表(マスタデータ)の活用もかなり手軽に行えます

 今回の記事では、「クエリのマージ機能」に加えて「グループ化機能」も活用して、マスタデータを更に有効に活用できるようにする方法を解説します!

マスタデータの履歴管理

マスタデータには顧客マスタや商品マスタなど様々なものがあります

種類は様々でも、絶対的なルールが一つあります

***【40代、50代歓迎】エクセルの学び直しを応援します!***

エクセルの進化を味方にして業務を効率化しましょう!

時間は30分/1,000円から!あなたの都合に合わせて相談になります!!平日の昼間も歓迎です! **********************************************************

これは、マスタデータには重複があってはならないということです

もし、マスタデータのIDが「1,2,3・・・」と採番されていたとしたら、IDの1が2つあってはならないということです

ところが、

マスタデータの変更履歴を残す必要がある場合があります

例えば、以下の画像のケースです

顧客マスタ

上の画像は顧客マスタです

ID:1番の中尾さんのマスタデータが重複しています

何故かというと、IDが1番の中尾さんが名古屋市から東京に引っ越ししているからです

ただし、データ管理上は前の住所も残しておく必要があります

この場合は、日付が最新(最大)のもののみマスタデータとして表示できるように工夫する必要があります

では次から、日付が最新(最大)のもののみをマスタデータとして表示する為のポイントを2つ紹介します

ポイント

グループ化

パワークエリにはグループ化という機能があり、重複を排除してグループ化しつつ合計処理などの操作を行えます

 今回はグループ化機能の操作を「最大」で指定して、IDの重複がある場合には「最大の日付」のものを抽出できるようにします

複数キーによるマージ

エクセル関数のVLOOKUP関数では、検索値は一つのみ指定できます

パワークエリのマージ機能では、実は、複数列を照合列として指定できます

手順

マスタデータからクエリ作成

解説は、前述のマスタデータをテーブル化してエディタを開くところからははじめさせて頂きます

エディタを開いたら、下の画像の日付の列が時刻表示になってしまっています

こちらは、日付形式に直しておいてください

日付形式に直したら、クエリ名を「顧客マスタ_元」としてください

グループ化による最大日付の抽出

まずは「顧客マスタ_元」クエリを「複製」してクエリ名を「顧客マスタ_グループ化」に変更してください

この後、ポイントで前述したように下の画像のような設定でグループ化を行ってください

グループ化(最大)

グループ化を行うと、前述のID:1番の方の日付が最新の日付になっているはずです

クエリのマージ処理

 では、次にクエリのマージを指定しますが、下の画像の「新規としてクエリをマージ」を指定して、新規にクエリを作成できるようにします

マージ対象のクエリは下の画像のように「顧客マスタ_元」を指定します

ポイントの章で前述したように、複数の列を照合列として指定します

 今回のケースの場合は、IDだけでなく「日付」も指定することで、IDに重複がある場合には、日付も一致したマスタデータのみをマージできるようにします

下の画像のように照合列として複数列を指定する場合には、Ctrlキーを押しながら指定します

画像に alt 属性が指定されていません。ファイル名: image-106.png

マージする条件を指定した後は、マージされたクエリの中かから「ID列と日付」以外を展開します

マージされた列を展開した後は、列の順番等を整えてください

<まとめ>

 今回は、パワークエリのグループ化機能とマージ機能をうまく組み合わせてマスタデータの履歴管理を行う方法を解説しました

今回の最大のポイントはグループ化機能です

グループ化を行う際に、操作を「最大」にして日付が最大のもののみを抽出できるようにします

今回解説した内容で一点、注意点があります

マージする際に、グループ化した内容は1番目にくるようにしてください

画像に alt 属性が指定されていません。ファイル名: image-114.png

元の顧客マスタは、上の画像にて黄色の印をつけた箇所のように、2番目に来るようにしてください

この順番が狂うと、IDが重複したままになってしまいますので注意が必要です


にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です