商品管理シート|ExcelのPowerQueryを使ったテンプレートの作り方

ExcelのPowerQueryを使った商品管理シートのテンプレートです、無料でダウンロードできます。

最近PowerQueryの解説本を見かけるようになりました。
そこで簡単なPowerQueryの使用例を作成してみました。
ここの商品管理のサンプルはVLOOKUP関数を使っても作成可能ですが、PowerQueryの方が簡単で応用範囲も広そうです。
ダウンロードファイルで実際に試していただければ、簡単にできることが分かります。
ここでは商品の基本情報を入力するマスターシート(商品マスター)と、販売数量を入力するシート(販売実績シート)をマージ(結合)し作成します。
商品マスターの商品は複数登録はしないで1つだけ入力します。
販売実績シートは同じ商品を複数登録できます。
作成手順は下記の通りです。

  1. 商品マスターシートの作成
  2. 販売実績シートの作成
  3. クエリデータを取得する
  4. 取得したシートをマージ(結合)する
  5. マージしたシートを展開する
    ここからPower Queryエディターの操作にになります。
  6. 展開した列の名前を変更する
  7. [閉じて読込む]を実行する

掲載済みの「年齢表」も参照してください。


PowerQueryを使った商品管理シートの作成方法

PowerQueryを使った商品管理シートの作成

商品マスターシートを作成します。

商品マスターシート

販売実績シートを作成します。

販売実績シート

クエリデータを取得する

リボンの[データ]~[データの取得]~[ファイルから]~[ブックから]を選択します。

[ブックから]を選択する

データの取り込みが開くので、ファイルを選択し[インポート]ボタンをクリックします。
ここでは同じファイルにしていますが、別のファイルでも可能です。

[インポート]ボタンをクリックする

ナビゲータ ダイアログが開きます。

  1. ファイルに含まれるシート一覧が表示されるので、ここでは「販売実績」を選択します。
  2. [読み込み]ボタンをクリックします。
ナビゲータ ダイアログ

販売実績シートを読み込んだシートです。

販売実績シートを読み込んだシート

上と同じやり方で「商品マスター」を読み込みます。

「商品マスター」を読み込む

商品マスターシートを読み込んだシートです。

商品マスターシートを読み込んだシート

読込んだ販売実績シートに移動します。
リボンに[クエリ]タブが表示されているか確認してください。
表示されていなければ、データが表示されているセルをクリックしてください。

リボンの[クエリ]~[結合]をクリックします。

読込んだ販売実績シートに移動する

マージ ダイアログが開きます。

  1. 下側のコンボボックスで「商品マスター」を選択します。
  2. 販売実績の「商品コード」の列をクリックします。
    これで緑色の背景に変わります。
  3. 商品マスターの「商品コード」の列をクリックします。
  4. 結合の種類が「左結合」になっていることを確認します。
    左とは上のことで、上とはここでは販売実績のことを指します。
    つまり販売実績の商品コードに結合することになります。
マージ ダイアログ

Power Queryエディターの操作

Power Queryエディターが開き結合結果が表示されます。
ただ商品マスターのデータが表示されていないので展開します。
商品マスター右のボタンをクリックします。

Power Queryエディター

展開する列の選択ウィンドウが表示されます。
ここでは「商品コード」を解除します。
「商品コード」は結合した両方のシートに含まれるので、片方は非表示にします。

展開する列の選択ウィンドウ

商品マスターが展開できました。

商品マスターが展開できた

項目名にシート名が含まれ長いので変更します。
項目名の上で右クリックし、表示されるメニューの[名前の変更」で行ってください、

[名前の変更」を行う

リボンの[ホーム」~[閉じて読み込む」をクリックします。
これでPower Queryエディターは閉じ、結果がブックに表示できます。

これでPowerQuery超入門は完了です。
基本的なやり方は掲載した通りです、ダウンロードし実際にお試しください。

Excel PowerQueryを使った商品管理シートのテンプレートを無料ダウンロード:A4サイズ

商品管理シートのテンプレート

商品コード・日付・数量が販売実績シートに入力したデータです。
商品名・単価が商品マスターシートに入力したデータです。
上はその2つをマージ(結合)した結果のシートです。
背景色と項目名の▼ボタンは自動的に付加されます。


無料ダウンロード

ダウンロードし使用状況に合わせて、Excelで自由にカスタマイズしご利用ください。

Follow me!