タグ別アーカイブ: MODERNエクセル

Power Queryって何??~日付を月や四半期単位に変換~

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

 Power Queryを知らない時には、様々な日付関数を使用して日付の変換を行っていました

例:「2021/01/27」⇒Year関数⇒「2021」⇒Month関数⇒「1」

 Power Queryエディタでは、直感的なクリック操作でこれらの処理は一括で行えるので覚えておくととても便利です!

更に、Power Queryエディタでは「四半期単位」に変換できる点にも注目です!

使う画面は変換タブの「日付と時刻の列」になります      

目次

1.年への変換

2.月への変換

3.四半期

1.年への変換

日付の「年」への変換(例:2019/09/01⇒「2019」)は次の画像の「年」をクリックすれば行えます

次にGIF画像のように列ごと一括で変換されます

2.月への変換

1.の年の変換と同じく次の画像の画面で行えます(例:2019/09/01⇒「9」)

尚、この「月」のメニューでは「月の開始日」や「月の最終日」にも変換できます(例:2019/09/09⇒2019/09/30)

以降は有料ページ/課金サービスへ

Power Queryって何??~読込んだデータに行番号追加~

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

Power Queryには、意外な便利機能が盛りだくさんあります

 今回、紹介する内容はあまり時短にはつながりませんが、Power Queryの奥深さを知って頂く意味でも、ぜひ紹介したいと思います

 Power Queryというと、どうしてもPower Queryエディタ(以降エディタ)での操作に注目が集まりますます

 今回の内容は、エディタからエクセルシートに読込んだ後の内容になりますが、かなり意外な内容です

下の画像のような、クエリから読込んだデータテーブルがあったとします

こちらに行番号を、エディタを使わずに挿入します

必要な処理はクリックだけです

 データテーブルにカーソルを置いて、データタブのプロパティをクリックします

次に開いた画面で、下の画像の「行番号を含む」をクリックします

次にデータテーブル上で右クリックします

すると、「更新」がクリックできるようになります

 「更新」をクリックすると、上のGIF画像のように0から始まる「行番号」が挿入されます

エディタで行番号を挿入するメニューについては、以前も解説しました

エディタでは「1」から始まる番号も指定できました

今回紹介した内容はあくまで「0」から始まる番号なので、その点は残念です

今回は以上です

Power Queryの便利さや奥深さを、今回の記事で感じて頂けたら幸いです

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

文字列を記号毎に分割し、行方向に展開する方法

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

 以前、Power Query中級編にて記号の出現ごとに列分割する機能について解説しました

 この時には、記号毎に列分割した後に、ピボット解除により縦横の並びを縦縦方向へに並び替えました

 実は、Power Queryには記号毎に行方向に分割する機能もありますので、今回は前回の補足として記事を書きます

まずは、前回の具体的な内容について、振り返りたいと思います

前回は、次の画像のような表を解説に使用しました

 この上の表を「1.担当企業の列をコンマで列分割」⇒「2.ピボット解除」⇒「3.NOをつける」の3つの変換処理により、下の画像のようにしました

 では、 「1.担当企業の列をコンマで列分割」 を行分割で行う方法をPower Queryエディタ画面を開いたところから解説します

まずは、下の画像の「区切り記号による分割」をクリックします

次の画面で以下の2つを指定します

ここまでは、前回と一緒です

実は、「区切り記号の出現ごと」の下に詳細設定オプションというのがあるので、こちらをクリックします

すると、分割の方向が「行」でも選べるようになっています

こちらを指定して、画面右下のOKボタンをクリックします

すると、上のGIF画像のように行で分割されます

この機能は、データ整理(データクリーニング)でかなり活用できる内容です

覚えておいて絶対に損はないはずです

では今回は以上です

最後まで記事を読んで下さりありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

Power Queryって何~既存クエリのステップを一括で削除する方法

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

今回は、既に作成したクエリをステップ毎に分割する方法を解説します

 例えば、以下のように5つのステップ(最初のソース以外)があるクエリがあったとします

最後のステップで右クリックします

こちらで、「前のステップの抽出」をクリックします

すると次の画面になるので、新たなクエリ名を入力してOKボタンを押します

すると元のクエリは次の様になります

右クリックしたステップが残っています

一方、新しいクエリは次のようになります

右クリックしたステップより前のステップが残っています

では、新たに作成したクエリにて、途中のステップで「前のステップの抽出」を同じように行ったらどうなるでしょう?

更に新しく作成したクエリでは、上の画像の黄色の箇所の1つ前「変更された型」のみが抽出されています

今回は以上です

いつもご愛読頂きまことにありがとうございます

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

「例からの列」にて変換パターン自作~上級11回目~

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

今回は、新たなタイプの変換処理について解説します

これまでの変換処理は、予めメニュ―に設定された内容での変換処理でした

 今回紹介する変換処理は予め、ユーザー自身で変換パターン(及び作成パターン)を作成します

パターン作成➡パターン反映の流れ

 上のGIF画像では、左の2列から「例1」の列で3つの変換パターンが例示により作成されています

・「氏名」⇒氏名から「姓」を抜き取り

・「個数」⇒整数を抜き取り

・「姓」と「整数」から【「姓」さんは「整数」個販売】のテキスト作成

この機能を利用すれば、様々な処理に応用できそうですね!

この機能について、2回に分けて解説を行って行きます

まずは、今回の変換処理のポイントを2点だけ解説します

A.変換パターン作成及びパターン反映画面

「列の追加タブ」⇒「例からの列」メニューから処理を行いますが、通常の処理と画面構成が違います

新たな列が、テーブルから離れた場所に追加されてからの変換処理になります

B.例としてのパターン作成

 変換パターンの例示は、1度で済む場合と2度以上の例示が必要になるケースとに分かれます

 1度目に例示する場合も、2度目に例示する場合も、例示する場所はどこでも大丈夫です

 それでは、次の画像のデータをテーブル化し、Power Queryエディターで開いたところから解説を始めます

1.「例からの列」

 前述のように「列の追加タブ」の「例からの列」を指定するのですが、2種類の変換タイプを選択できます

 今回は、複数の列を扱うので「すべての列から」を選択して、すべての列を変換対象にします

すると前述のように、新たな列が画面右に設定されます

2.記号で文字を変更するパターン

ここから、具体的な処理の解説になります

下の図の「氏名」列から姓だけを切り取った列を作成します

右側に出来た「列1」の1番上に「吉永」と入力します

この時に、「吉永」と最初から入力する必要はありません

下の画像では分かりにくいですが、カーソルを当てると入力候補が出てきます

該当の箇所にカーソルを置き直し、エンターキーを押すと下の図のように反映されます

この時、既に列の上の名前が変わり、左の「氏名」の列の内容がコピーされています

では、上の画像の状態から名前を消去して変換パターンの例示を行うと、次の画像のように列全体に姓の文字だけが残ります

ちなみに、例示に基づいて変換が行われると「変換式」が左上に作成されます

では、逆に姓ではなく名前だけ残したら、この「変換式」はどうなるでしょう?

つまり、空白の後を切り取っている内容になっています

以降の解説でも、この変換式は活用します

では、変換が終わったので、元のテーブルに変換した内容を追加します

ショートカットキー:Ctrl+Enterで追加処理は行えます

<まとめ>

今回は、まずは記号で文字を変換するパターンを解説しました

 「列の追加タブ」から「例からの列」をクリックした後の画面が通常のPower Queryエディターの画面と違いました

 テーブルと離れた箇所に列が追加され、追加された列の上で変換処理(作成処理)を行いました

 変換処理(作成処理)が終了したら、ショートカットキー:Ctrl+Enterにて元のテーブルに新たに作成した列を追加しました

 では、次回は冒頭に解説したように、2度の変換パターンを例示するパターンも合わせて解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

「例からの列」による桁数が規則列への対応~上級12回目~

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

前回は「例からの列」のメニューを使用して、変換パターンを1つ自作しました

 こちらは1つ例を示して、氏名から「姓」だけ、もしくは「名前」だけを分解する変換パターンでした

下の画像が、変換前の氏名でした

そして「例からの列」機能を使い、上の画像を下の様に分解しました

 では、今回は変換内容の2つの例示を通じ、作成したい変換パターンをPower Queryに汲み取らせた上で、変換パターンを確定する方法を紹介します

この「2つの例示を行うパターン」が使いこなせるようになると、列のデータが不規則でも、ノーコードにてデータ成型が一括で行えるようになります

解説に使うデータは前回と同じです

1.桁数が不規則な列を揃える

今回変換するのは、下の画像の「部門」の列です

こちらは部門コードが4~5桁になっており、桁数が不揃いになっています

この部門コードを3桁で統一します

まずは、例示の1回目を行います

1行目の「ABC1」を「ABC」にて例示すると下の図のようになります

やりたいことは列全体を3桁に揃えることでしたが、3桁になったのは例示した行も含めて2行だけでした

・ABC1 ➡ABC

・ABC10 ➡ABC

他の行は次のような状態です(一部省略)

・BBC2 ➡BBC2

・ABB33➡ABB33

全く変化が起こっていませんので、画面左上の変換式をチェックしてみます

要は「1」の前の文字を切り取る変換式が出来ていました

では、次に下のGIF画像のように「1」を含まない行にて、2つ目の例示を行いましょう

2つ目の例示を追加したら、全て3桁に揃いました

では、変換式を確認してみましょう!

「Text.Start([部門],3)」となっています

こちらは、「部門」の列の各行の文字から3文字を左から切り取ったという意味になります

つまり、2つの例示を通じて「3桁」にしたいという意図をPower Queryが汲み取った形になっています

2.計算

あまり使う機会がないかもしれませんが、「列」と「列」の計算もこの「例からの列」で行えます

「単価」と「個数」を乗じた計算結果を2つ例示してみます

今回は、1行目と2行の計算結果、1800x 7 = 「12600」と3800 x 3 = 「11400」を入力してみます

すると、2つの計算結果を汲み取ってくれて、列の全行に計算結果を入力してくれます

<まとめ>

 今回は複数の例示から、変換パターンを汲み取らせて変換するパターンを行いました

 前回の冒頭にも紹介しましたが、複数の列を組み合わせて「文章」も作成することもできます

 但し、大量のデータがある場合には、変換ミスが起こっていても気づかないケースもありうります

なるべく複雑な変換は行わないようにしましょう!

そして、画面左にある「変換式」はなるべくチェックするようにしましょう!

最後に、参考までに今回の解説で使用したデータと完成版を添付します!

次回は、エディタを開かないまま「データソース」を変更する処理を解説します

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

シート上からデータソースを変更する方法~上級編14回目

 Power Queryのクエリと連携するファイルを、まるでスマホを操作するかのように簡単に変更できたらいいですよね!

 Power Queryはとても便利なのですが、エディタを開かないと変更ができないのが少々面倒です

前回は「パラメーターの管理」の画面から変更をかける方法を解説しました

 今回は、エディタを完全に開かずに、クエリのデータソース(データ取得元)を下のGIF画像のようにセルの値をもとにして変更する方法を解説します

まず今回のポイントを2点だけ解説します

目次

今回のポイント

1.セルに名前を付ける

2.データソースの確認(ファイルの取得)

3.名前の組み入れ

4.コードの修正

<まとめ>

今回のポイント

A.名前の活用

今回使うデータは、フォルダ名とファイル名がリスト化されています

リスト化されている2つのセルは、左のセルで名前をつけます

B.M言語の使用

 要はコードを書くということなのですが、後で添付するテキストをコピペして使い回した方が効率的です 

A.にて名前を付けたセルの値は可変です

こちらの名前を、「ファイルデータを取得するクエリ内」のコードの中に組み入れます

使用する画面は、過去の記事でオリジナル関数を作成した画面と一緒です

名前を通じて、セルの値を間接的にコードの中に組み込む形にするということになります(例:名前:フォルダ名➡コード:FilePath➡FilePathをデータソースの中のコードへ組み込み)

 では、下の画像のフォルダ内のファイルから「データを取得するクエリ/クエリ名:Dataが既に存在する」という前提にて本格的な解説をはじめます

 上の画像では、フォルダ名が「Folder1」となっていますが「Folder2」も別途存在するものとして解説を行います

1.セルに名前を付ける

ポイントA.でも前述したように、可変のセルに名前を付けます

まず、名前を付ける範囲をカーソルで指定します

名前を付けるのはセルの値に対してですが、名前自体もセルから抽出するので「2行2列」をカーソルで指定します

次に下の図の、黄色の各箇所をクリックします

後は、OKボタンを押せば2つの箇所に名前が設定されています

2.データソースの確認(ファイルの取得)

では、既に作成してあるファイルデータを取得するクエリの中味を確認しましょう

前述の通り、「Folder1」からファイルを取得しているのが分かります

前回は、こちらの画面に別途作成したパラーメーターを組み入れました

今回は別の画面で「組み入れ」作業を行います

まず下の「詳細エディター」をクリックします

すると、コードを修正する画面が開きます

上の画面のコードを、前述の画面と比べてみましょう

画像が小さくて分かりずらいかもしれませんが、コードの内容は一緒です

今回は、1.で作成した2つの名前をまずは組み入れます

3.名前の組み入れ

ここからは、ぜひ以下のテキストファイルからコードをコピペして活用してください

アイコン

上級編14回_コード 0.60 KB 99 downloads

...

1.で作成した2つの名前を、2.で確認したコードの中に組み入れるコードは以下の通りとなります(名前はフォルダ名、ファイル名でそれぞれ作成しました)

********

・FilePath = Excel.CurrentWorkbook(){[Name=”フォルダ名”]}[Content]{0}[Column1],
・FileName = Excel.CurrentWorkbook(){[Name=”ファイル名”]}[Content]{0}[Column1],

********

注意点としては3つあります

・{0}は1行目という意味になります(Power Queryは0からカウントします)

コンマをコードの終わりに必ず入れてください

・FilePathとFileNameはご自身で使いやすい名前に変えて頂いて構いません

4.コードの修正

3.でFilePathとFileNameに「名前」を通じてシートの値が設定されました

では、このFilePathとFileNameを2.で確認したデータソースのコードに組み入れます

********

ソース = Excel.Workbook(File.Contents(FilePath&FileName), null, true),

********

組み込みが終了すると、「適用ステップ」に組み込んだ内容が下のGIF画像のように表示されます

これで、エクセルシートに読込処理を行えば処理は終了です

ちなみに、エクセルシート上で値を変えても、更新処理を行わないと表示内容は変わりません

ただし、更新処理を行うと列幅がテーブルに合わせて変わってしまいます

そうすると、全体が不格好になってしまいますので次の箇所を変更します

データタブ⇒プロパティとクリックすると「列の幅を調整する」のチェックを外せるようになります

これで、更新前に設定した列幅が維持されます

<まとめ>

 今回は、セルの値をパラメーターとして設定し、セルの値を変更すればデータ取得先のフォルダとファイル名を変更できる仕組みについて解説しました

 仕組みとしては大きく分けて2つありました

①セルの値を名前で登録する

②詳細エディタで①で設定した名前をコードの中に組み入れる

 この2つの処理を通じて、セルの値を間接的にデータソースを取得するコードに反映できるようにしました

 コードについては、まずはコードの意味自体を理解するより「どこにコピーを活用するか?」「どこは手動で直さないといかないのか?」を理解するのが重要です

 今回、解説したような[]{}が度々出現するコードの規則性を理解するのはかなり困難です

一方で、次回の解説でも当てはまりますが「コピペ」で十分活用できます!

今回は以上です

最後まで長文にお付き合い頂き、誠にありがとうございました

次回もエディタを開かずに変更を行う方法について解説をします!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

クエリのコピー・バックアップ・削除他~上級編16回

今回は、各種クエリに関する処理について解説します

解説する処理内容は全部で5つになります

1.クエリを別ファイルにコピー

2.クエリのバックアップ、復元

3.クエリの数が多い場合のグループ化

4.クエリの一括削除

5.依存関係

最後依存関係は前述の1.~4.全てに関わります

この回で解説する依存関係とは、データソースやクエリのマージ(結合)のことになります

 順序は逆になりますが、今回の事例として使用するファイルを解説する意味もありますので、5.依存関係から解説を行います

5.依存関係

今回使用するファイルは、過去の記事でも使用したファイルです

元データ

 この時には、商品台帳と売上台帳をマージして「売上金額クエリ」を作成し、売上金額を計算しました

では早速、「売上金額クエリ」の依存関係を見てみます

解説対象のクエリ

 まず「売上金額クエリ」の上で右クリックして、Power Queryエディタ(以降、エディタ)を開きます

クエリの編集

エディタが開いたら、表示タブをクリックします

クエリの依存関係

すると、上の画像右の「クエリの依存関係」をクリックできるようになります

下の画像が、「クエリの依存関係」をクリックした時に開く画面です

依存関係

上の図を見ると「売上金額クエリ」は①商品台帳クエリと②売上台帳クエリとマージしており、この2つのクエリに依存しているのが分かります

①と②についても、依存の状況は分かります

①商品台帳クエリ

このクエリは「e:\パワークエリ講座・・・」のフォルダにあるファイルからデータを取得しているのが分かります

依存関係

この 「e:\パワークエリ講座・・・」の上にカーソルを置くと更に詳細が分かります

②売上台帳クエリ

 売上台帳クエリは①の商品台帳クエリと違い、売上金額クエリが存在するファイル内からデータを取得していることが分かります

 上の関係図にはないですが、売上台帳クエリは現在ブック内の「売上台帳テーブル」からデータを取得しています

 ちなみに上の図に、「読み込まれていません」とありますが、こちらはクエリの読込先が「接続のみ」になっていることを表します

 今回、事例とするクエリのことも解説したところで、前述の1.~4.についても解説を行って行きます

1.クエリを別ファイルにコピー

まず、商品台帳クエリを別ファイルにコピーします

商品台帳クエリ

商品台帳クエリ上で右クリックし、「コピー」をクリックします

クエリのコピー

そして、別ファイルで貼り付けます

クエリの貼り付け

では、他2つのクエリに依存している「売上金額クエリ」を別ファイルにコピーしてみます

すると下の図の通り、依存している2つのクエリも同時にコピーされます

売上台帳クエリは「現在のブック」内のテーブルを取得元にしています

別ファイルには、もちろん「売上台帳クエリ」の取得元になるテーブルはありませんので、売上台帳クエリはエラーになります

そして、「売上台帳クエリ」に依存している「売上金額クエリ」もエラーになります

2.クエリのバックアップ、復元

では冒頭で紹介した、「事例として使用するファイル内」にある「商品台帳クエリ」をバックアップします

次の図の「接続ファイルのエクスポート」をクリックします

するとクエリのバックアップ先を指定する画面が出ますので、そちらに保存処理を行います

バックアップの復元については、以下の画面で行います

こちらは、4.のクエリの一括削除を行った後に、追加で解説を行います

3.クエリの数が多い場合のグループ化

では、冒頭の事例として解説したファイルに戻ります

Ctrlキーを押しながら「商品台帳クエリ」「売上台帳クエリ」を選択します

そして、右クリックすると「グループ作成」を指定できます

作成するグループには「名前」をつけます

名前をOKボタンを押すと、「作成したグループ名」のグループと、それ以外のグループに分かれます

作成したグループを解除する時には、下の図の「グループ解除」をクリックします

クエリをグループ間で移動させたい時には、移動させるクエリを右クリックし、次の画像の黄色の箇所をクリックします

4.クエリの一括削除

引き続き3.で使用したファイルにて、クエリの削除を行います

「商品台帳クエリ」上にカーソルを置いて右クリックし、更に削除をクリックします

すると、「商品台帳クエリ」は「売上金額クエリ」に依存されているため、下の画像のようなメッセージが出て削除ができません

一方で、Ctrlキーを使用して、全てのクエリを指定して削除をすると「依存関係」自体も削除されるため、一括で削除されます

なおクエリが削除されて後も、クエリからシートに読込まれていたテーブルデータは削除されません

ちなみにCtrlキーを使用する方法の他にも、一括でクエリを削除する方法があります

まず「ファイルタブ」をクリックした後、情報タブをクリックします

更に「問題のチェック」の右下にある▼マークをクリックした後に、「ドキュメント検査」をクリックします

次に「ドキュメントの検査」が開くので、画面下の「検査」ボタンをクリックします

クエリがファイル内にあると、下の図のように「カスタムXMLデータが見つかりました。」とメッセージが出ます

「すべて削除」をクリックすると、すべてのクエリがファイルから削除されます

ファイル内のクエリが全て削除されたところで、2.でバックアップを取ったクエリを復元してみます

クエリの復元は、前述のようにデータタブにある「既存の接続」から行います

「既存の接続」をクリックした後に開いた画面の左下に「参照」があります

上の図の「参照」をクリックすると、バックアップしたクエリを指定できるようになります

クエリを復元する時には、下の図のように「データのインポート」画面が開きますので、こちらで読込先を指定します

<まとめ>

今回は、クエリの「依存関係」を含め、全部で5つの処理を解説しました

1.クエリを別ファイルにコピー

2.クエリのバックアップ、復元

3.クエリの数が多い場合のグループ化

4.クエリの一括削除

5.依存関係

 1.~4.の処理を行うに当たっては、必ず5.の「依存関係」は事前に確認しておきましょう

 尚、1.のクエリのコピーについては、過去の記事で紹介した「複製」とはきちんと区別しておきましょう

クエリの複製の場合には、他ファイルにはコピーはできません

そして、複製の場合には1.のコピーとは違い「依存関係」があるクエリはコピーされません

 ちなみに、1.のクエリのコピーした内容をテキストボックスに貼り付けると、詳細エディタの内容がコピーされます

今回は以上です

次回は、重要なデータ成型の仕方の一つ「文字列の抽出」について解説します

長文に最後までお付き合い頂き誠にありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

複数データの組み合わせ/クエリのマージ~中級編1回目~

今回から、中級編をはじめます

初級編では、データを取得してエクセルシートに読み込むだけでした

中級編では、取得したデータを編集・加工していきます

このデータ編集・加工手法を身に付ければ、様々な箇所から集めた大量のデータを関数やVBAを使わないでも、サクッと一括処理ができるようになります!

次回から2回は、関数やVBAを使用した場合には作成に手間がかかりそうな表を サクッと2つの表を組み合わせるだけで作成する方法を紹介します

今回は、次の回から主に使用する技術の概要だけ紹介しておきます

1.クエリのマージ

イメージとしては下の図のようなことを行います

関数で言えば、VLOOKUP関数も同じような事ができますが、このPower Queryのマージには、VLOOKUP関数で処理する時と比べて次のようなメリットがあります

・他の表から抽出する項目が多く、データ自体が大量な場合

 ⇒ワンクリックで処理できる

・VLOOKUP関数の場合は参照する値の位置が左にないと使用できなかった

 ⇒位置は関係なく処理できる

ちなみに、Power Queryにて実際にマージを行う画面は、以下の画面になります

2.計算処理

Power Query上では、2つの表を組み合わせた上での計算処理が、とても簡単に行えます

計算処理がもし±X÷を行う四則演算であれば、対象となる列を指定するだけで計算処理が行えます

SUMIFやSUMIFSなどの、関数であれば条件設定が伴う、少しややこしい計算も、下の図のような画面にて、直感的に計算処理が行えるようになっています

では次回から2回に分けて、1.クエリのマージ、2.計算処理を中心に解説を行って行きます!

https://analytic-vba.com/power-query/intermediate/merge-query/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

クエリのマージ/項目追加~中級編2回目~

中級編2回目は前回紹介したマージの技術を、実際にサンプルを使用して解説を行います

下の図にある商品台帳売上台帳が解説に使用するデータです

こちらの表2つを組み合わせる(マージする)ことにより、2つの表に分かれていた項目を組み合わせて活用できるようにします

下の図のように、売上台帳に商品台帳の「商品名」と「販売単価」を追加します

今回のポイント

A.キー列を設定します

2つの表を組み合わせるためのキー列を指定します

B.組みあせる項目、組み合わせ方を選択します

下記の画面で組み合わせる項目と組み合わせ方を指定します

上の図に”集計”とありますが、集計については次回に解説します

では早速、下記にサンプルファイルをダウンロードして演習をすすめていきましょう!

1.クエリ作成

商品台帳と売上台帳ともに、次の①~③の処理を行い、クエリを作成しておきます

①Power Query Editorを開く

データタブから”テーブルまたは範囲から”を指定して、Power Query Editorを開きます

範囲を指定する際、自動的に題名の部分(下の図であれば商品台帳)がテーブルの見出しとして範囲に入ってしまうケースがあります

こちらについては、題名部分を除いて範囲を指定し直してください

②Power Query Editor上の処理

Power Query Editorの画面右にて、クエリに名前をつけましょう!

マージ処理をする時にクエリー名が分かり易くつけてあると、処理がとても進めやすいです

③読込処理

ホームタブの右上の「閉じて読み込む」ボタンの右に▼マークがあるので、こちらをクリックしてください

すると、次の画面のように「閉じて次に読込む」ボタンが出てきますので、こちらをクリックします

そうすればデータのインポート画面が開くので、読込先を指定できるようになります

下の図のようにデータのインポート画面が開いたら、読込先は”接続の作成のみ”を指定してください

ここまで行えば、クエリが画面右に出来ています!

上の①~③の処理を通じて2つのクエリを作成したら、2.のマージ作業に移ります

2.マージ作業

2つのクエリをマージする場合、事前に2つの方法が選択できます

ⅰ)既存のクエリにマージする方法

ⅱ)新規にマージ用のクエリを作成する方法

今回はⅱ)で行います

➀売上台帳クエリを参照

新規にクエリを作成するといっても、全く一からクエリを作成するわけではありません

売上台帳クエリを基にして新規のクエリを作成します

まずは、売上台帳クエリ上にカーソルを置いて右クリックし、更に”参照”をクリックします

「参照」をクリックしたら、Power Query Editorが開きます!

➁クエリのマージをクリック

新しいクエリのクエリ名は、次の画面のように変更しておきましょう!

③クエリのマージ

次に画面上にある、「クエリのマージ」ボタンをクリックします

④マージするテーブルを指定

マージをクリックした後に、新規に次のGIFにあるように「マージ」方法を指定する画面が立ち上がります

この画面では マージするテーブルとキー列を次のように指定します

テーブル⇒商品台帳、キー列⇒商品コード

⑤マージする項目、方法を指定

④が終了すると下記の画像のように、商品台帳の内容が一番右の列に追加されます

次に、商品台帳の右横にあるマーク(2つの矢印の組み合わせたマーク)をクリックし、実際に組み合わせる項目と組み合わせ方法を指定します

上のGIFのように、マークをクリックした段階では全てにチェックが入っています

ここでは商品名と販売単価はチェックを入れたままにしましょう!

「元の列名をプレフィックスとして使用します」についてはチェックを外しましょう

チェックを入れたままだと下の図のように、余計な名前(商品台帳)が商品名と販売単価の左横に表示されるようになってしまいます

⑥読込処理

今回は”閉じて読み込む”を指定し、接続の作成のみでなく、実際にシートに読み込みます

これでマージ処理は完成です!

<まとめ>

はじめてのマージ処理はいかがだっだでしょうか?

2つの表を組み合わせるにあたり、VLOOKUP関数を2つの列 (商品名、販売単価 で作成する)よりずっと簡単だったのではないでしょうか?

もし、”しまった、他の項目も追加が必要だった”ということになっても、関数を追加したりしなくても、簡単に追加が行えます

具体的な方法については次回、詳細に解説します

次回は単に項目の組み合わせだけでなく、計算処理も行います!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村
</a