タグ別アーカイブ: 取得

不規則に散らばっているデータを一括取得~M関数~

 今回は上の画像のようにデータが複数シートに散らばっており、しかも行見出し位置/ヘッダー行不規則なデータを一括で取得します

今回の最大のポイントは、List関数で行見出しの位置を取得する点です

そこさえクリアすれば、意外と簡単にできます

では、今回使用するデータと行いたい事の確認から解説を始めます

今回使用するデータと行いたい事

今回使用するのは次のファイルです

このファイルの中には3つのシートがあります

但し、前述のように3つのシートそれぞれの行見出し位置が違います

 2020年1月のデータは1行目から始まっていますが、2月のデータは2行目から始まります

そして、3月のデータは3行目からです

 この3つのシートからM関数を組み合わせてデータを一括で取得するのが、今回行いたい事です

今回のポイント

 今回の最大のポイントは、前述のようにList関数で行見出し位置を取得することです

 行見出し位置を取得するには、行見出しにある「客先コード」をキーにして、M関数「List.PositionOf」を使います

List.PositionOf関数の他にも2つM関数を組み合わせます

List.PositionOf

List.PositionOf関数はリストの中から、指定の値の位置を取得します

書き方は、次の通りとなります

List.PositionOf(リスト名,取得したい値)

ここで、注意点が1点あります

M言語は0ベースである点です

例えば、List.PositionOf関数を使用すると、次の画像の「客先コード」の位置はではなく「0」とでます

これは、M言語が0で始まる仕組みになっているからです

Table.RemoveFirstN

Table.RemoveFirstNは、テーブルから指定した行数を削除するM関数です

書き方は次のように書きます

Table.RemoveFirstN(テーブル名, 削除する行数)

Table.PromoteHeaders

Table.PromoteHeadersは、1行目を見出しに昇格させるM関数です

Power Queryエディタ内にも同じメニューがあります

今回の内容は、後述しますがM関数で行った方がベターです

このM関数の書き方は、次の通りとなります

Table.PromoteHeaders(テーブル名)

今回のポイントである、3つのM関数の内容を確認したところで、本格的な解説をはじめます

ファイルの読込

まず最初に、前述のサンプルファイルの読込処理を行います

サンプルファイルとは別のファイルで、下の画像の処理を行います

 上の画面の「ブックから」で前述のサンプルファイルを指定した後、Power Queryエディタ(以降、エディタ)が開くと次の画像のような状態になっています

上の画像の左から2つ目の列にある「Data」が今回のポイントの一つです

 各行にある「Table」の文字の横をクリックすると、次のGIF画像のように中味が見れます

次からはこの「Data」列を活用して、カスタム列をM関数により作成します

カスタム列の作成

List.PositionOf関数で行位置を抽出

では、カスタム列・作成画面を開きましょう

 ここで確認ですが、行位置を取得する対象は下の画像の「客先コード」の文字です

 上の画像を見ると、「客先コード」にはⅰ)Data/Table➡ⅱ)Column1➡ⅲ)客先コードの順で指定するとたどり着けるようになっています

 カスタム列・作成画面でも、M関数/List.PositionOfでの引数設定時に上の流れを使用して指定します

上の画像のようにList.PositionOf関数の引数を次の様に指定します

一引数:[Data][Column1]

第二引数:”客先コード”

そして、カスタム列・作成画面の下にあるOKボタンを押すと次の画像のように客先コードの位置が出力されます

あくまでM言語は0から始まるので、その点についてはご注意ください

 1行目の内容では、客先コードは1行目にありますがM言語のベースに従って0と出力されています

Table.RemoveFirstNで不要な行の削除

 List.PositionOf関数により行見出しの位置は抽出できましたので、「行見出しの位置-1」分の行数をM関数/Table.RemoveFirstNを使用して削除します

Table.RemoveFirstN関数の書き方は、前述の通り次の通りです

Table.RemoveFirstN(テーブル名, 削除する行数)

今回は上の第一引数のテーブル名には、[Data]を入力します

そして、第二引数には上のList.PositionOf関数をー1をせずにそのまま設定します

 上の画像の通りに数式を指定してOKボタンを押すと、「本来、行見出しにするべき行/客先コードのある行」が次の画像のように1行目に来ています

但し、このまま作成されたテーブルを展開すると不都合な点が1点あります

 本来、行見出しにするべき行が行見出し/ヘッダーになっていませんので、このまま展開作業をすると不要な行見出しがデータとして入り込んでしまいます

ですので、もう一つのM関数で不要な行を削除できるようにします

Table.PromoteHeadersで見出し行を作成

では、最後の仕上げとして各テーブルの1行目を見出し行/ヘッダーにします

 こちらは、M関数/Table.PromoteHeadersの()の中にこれまでの内容を入れるだけです

Table.PromoteHeaders関数の内容を反映した結果が次の画像です

 上の画像の内容で、各テーブルを展開処理すればいいのですが、その前に既存の列は削除しておきましょう

「他の列の削除」が終了したら、後は各テーブルを展開するだけ終了です

<まとめ>

 今回は、List.PositionOf関数をはじめとする3つのM関数を組み合わせて、不規則なデータを一括で処理できるようにしました

最大のポイントは、不規則な行見出しの位置をM関数で取得する点です

 それさえできれば、後は機械的にM関数を組み合わせるだけで一括処理ができるようになりました

今回の題材は、M関数の魅力、そしてM言語の魅力を知るのにいい題材だったと思います

ぜひ手を動かして体験してみてください

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

参考までに、今回の内容の完成版のファイルを添付します

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


にほんブログ村

Power Queryって何7???~Accessとエクセルの連携~

以前、Power Queryとは何か?について、スマホを例にして解説をしました

ガラケーではネットにある電話番号のデータを、キーボードに打ち込んで電話をしていました

一方、スマホではネットで検索した電話番号をそのままダイヤルすることができます

Power Queryも同じです!

エクセルファイル内の各シート、もしくは、他のファイル、フォルダ内のエクセルデータをそのまま活用できます!

今回はエクセルデータでなく、Access内のデータを活用してみましょう!

Accessというデータベースソフトと表計算ソフトのエクセルを直接、連携できれば今よりもデータを有効活用できることは間違いなしです!

1.取得先のAccessファイルを指定

今回は以下のAccessファイルからデータを取り込みます

①データ取得種類の指定

データタブから、データの取得⇒Microsoft Accessデータベースから

の順に指定

➁取込むファイルの指定

2.Access内の取得データを指定

取得するファイルを指定したらナビゲーター画面が立ち上がります

ナビゲーター画面

①取込みデータの指定

今回は複数のテーブル、もしくは選択クエリを取り込む方法で行います

上の画像のように、ナビゲーター画面の左上にある”複数のアイテムの選択”にチェックを入れ、取得するデータの横にチェックを入れます

➁読込先の指定

ナビゲーター画面の右下で”読込み先”をクリックします

読込み先のクリック後にデータのインポート画面が立ち上がります

今回は読込先にテーブルを指定します

すると、新たなシートにAccess内・2つのデータが読み込まれます

ちなみに、右画面にクエリが2つできましたが、これはスマホ内のブックマークと同じようなものです

更新すればAccess内の最新のデータが表示されます

<まとめ>

エクセルはとても便利なのですが、表計算ソフトなので大量のデータを各自で共有しながら作業するのにはあまり向いていません

一方、Accessは大量のデータ管理には向いていますが、不慣れな人が多いうえ、あまり細かい作業は行えません

このPower Queryをうまく使えば、エクセルとAccessのそれぞれの長所をうまくいいとこどりすることができます

ちなみに、Accessのエクスポート機能を使えばエクセルファイルをAccessから出力できますが、Power Queryであれば、前述の通りAccess内の最新データがエクセル内からいつでも取得することができます!

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

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

にほんブログ村

セル分割他・変換処理2~中級編8回目

こんにちは、Excellent仕事術ガッツ鶴岡です

前回はカンマ” , ”で区切られた「使いにくい表」をPower Query Editor上で「使いやすい形」になるように行方向に分割しました

このままだと、行別にみると2列の行があったり、5列以上の行あったり不規則で使いにくい表のままです

今回はPower Query Editor上で行方向に広がった列の並びを、縦の並びに変換して1列にし「使いやすい表」に変換します!

そして、前回使用したセル分割のテクニックを再び使い、連番までつけてしまいます!

前回の冒頭で1.セル分割2.ピボット解除を解説すると案内しました

前回1.セル分割を行ったので2.以降から解説を開始します

2.ピボット解除

コンマ” , ”で区切られたデータが混在したセルを分割した後は、データは横並びになっています

この並びを縦並びにして1列にします

この並び替えにはピボット解除の技術を使います

①担当者の列を選択

担当者の列をカーソルで選択し、右クリックします

➁その他の列のピボット解除

担当者以外の列が並び替えの対象です

その他の列のピボット解除をクリックします!

すると、並び順が縦の並びの繰返しに変わります

3.読込

いよいよ2.で変換した内容をシートに読込みます

その前に若干、修正を加えましょう!

①再分割

ピボット解除をした時、新たに属性の列ができています

この列を更に2列に分割し、

⇒属性1の列は削除

⇒属性2の列は”NO”として残しましょう

今回の分割処理はピリオド”.”を区切り記号に指定して行います

2列に分割された属性1の列は削除し、

属性2の列は列の名前を変えましょう!

➁クエリ名前の変更

クエリの名前は担当企業としましょう!

③既存シートへの読込

①②が終わったところで読込を行います

今回は読込先を指定するので「閉じて読み込む」の右下▼から「閉じて次に読み込む」をクリックします

更にデータのインポート画面で

既存のワークシートを選択し、読み込むセル位置をE2セルに指定します

これで使いにくい表を使いやすくする表にする処理は完成です!

<まとめ>

今回は”,”や”.”で区切られたセルを分割することで表を使いやすくする技術を学びました

従来のエクセル手法であれば、この分割処理を行うのにワークシート上で大量の手作業が発生していました

Power Queryであれば一括で行えるのでとても便利です!

参考までに今回の解説に使用したサンプルデータを参考までに添付します


では次回はもっとPower Queryを実践的に使いこなせるようになるため、「やり直し」の方法についてガッツで解説します

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

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

にほんブログ村

Power Queryでエクセル操作はどう変わる?~初級講座1回目

こんにちは、Excellent仕事術ガッツ鶴岡です

 読者の皆さんはエクセルのPower Queryと聞いて、具体的に使用するイメージが湧きますか?

 エクセルが誕生して20年以上も経ちますが、海外ではPower Queryはエクセルが生んだ機能の中で最高機能とも言われています

 ところが、日本ではPower Queryはどういう風に使うものなのか?何ができるのかについては、まだあまり知られていません

まずは、このPower Queryはどんな方におススメの機能なのか?について解説したいと思います

Power Queryは、こんな方に特におススメです

・日常的にエクセルで大量のファイル、シートをガッツで扱っている

・Access、関数、VBAを覚えてエクセル作業を効率化したいが、時間がな

 Power Queryはスマホのような直感的なクリック操作で、データをブロック遊びのように結合したり、分解したりできます

 ぜひ、上記のような悩みを持つ方にはこの記事を最後まで読んで頂きたいと存じます!

もし、時間が無い方はぜひ、下の動画をのぞいてみてください!

https://youtu.be/XoG54HePhuU

 この回では、Power Queryの使用イメージついて、読者の皆さんが日常的に使用しているものを例にしながら丁寧に解説していきたいと思います

さて、読者の皆さんは、ガラケーを使ったことはあるでしょうか?

筆者はガラケーが出て来た時点で、既に衝撃を受けていました

以前、使っていたのは固定電話だけでした

 ガラケーが登場した時には、ついに「電話を持ち歩き出来る」新しい時代が来たな、と思いました

そして、ガラケーからスマホに変えたときに、更に大きな衝撃を受けました。

 Power Queryを初めて使った時のイメージは、スマホに変えた時のイメージに近いです

 さて、ここでPower Queryをイメージしやすくする為に「電話帳でレストランを探して、ガラケーで電話予約」するシーンを思い浮かべてみましょう!

そして「スマホでレストランを検索して、スマホで電話予約」するシーンも思い浮かべてみて両者を比較しましょう!

この2つにはどんな違いがありますでしょうか?

この2つの違いを意識しながら、Power Queryの使用イメージの解説を読み進めていってください!

データ取得

さきほどのレストランを予約するシーンでは、ガラケーで電話をする場合は、電話帳を見ながら電話をしていました

つまり、ガラケーの中にはレストランの電話番号というデータはありません。

一方、スマホの場合もスマホの中に電話番号というデータが無いのはガラケーと一緒です

但し、ネットから電話番号を取得しています。

この「データ取得」が従来のエクセル操作方法とPower Queryの違いの一つです

Power Queryでは、下の画像の「データの取得」を通じて、様々な箇所からデータを取得します

データの取得画面

取得できるのはⅰ)シートデータ、ⅱ)ファイルデータ、ⅲ)フォルダ内のファイルデータなどのエクセルデータに加え、なんとⅳ)PDFファイル内やⅴ)Webページ内のデータも取得できるのです!

ここで次の疑問を持つ人もいるとおもいます

これらの取得したデータはファイル内に保持することになるのか?

この点ですが、Power Queryでデータを取得すると「クエリ」というものが画面右にできます

クエリー

このクエリーは、スマホ内のものに例えるなら「ブックマーク」だと思ってください

ブックマーク

このクエリーをクリックすれば、一度取得したデータに再アクセスできます

変換・読込

ガラケーではレストランに電話をするときに電話番号を0、3、4・・・とひとつずつ打ち込んでいました。

一方、スマホでは、Webサイト内の電話番号のマークをクリックすれば、スマホに電話番号がセットされます。

つまり、Webサイトを通じて取得した電話番号を、スマホでそのまま電話できるように変換しています

そして、変換したデータをスマホ内の電話画面に読込先を変更しているのです

Power Queryでは、取得したデータはPower Queryエディター(以降、エディタ)で様々な変換処理を行うことができます

Power Queryエディター

以下は、Power Queryエディター内・変換処理の1例です

①複数のクエリーの組み合わせ

こちらは、VLOOKUP関数の処理に相当します

複数のテーブルデータを、クリック操作のみでブロックのように結合します

 下のGIF画像では、「売上結果」というクエリと「商品台帳」というクエリを結合しています

➁計算処理(四則演算処理

下のGIF画像では、エディタ内で「販売単価」と「販売個数の合計」の列を選択乗算をクリック のみで販売金額を一括で算出しています

 そして、変換したデータは必要に応じて、様々な箇所、様々な方法で読み込むことができます

データのインポート

①テーブル

テーブルデータとして既存シートの中や、新規シートに表示することができます

➁ピボットテーブル

変換データをそのまま、ピボットテーブルにすることもできます

③接続の作成のみ

取得したデータをシート上に「読込まない」「表示しない」という方法を選択することができます

ここがPower Queryの使い方の大きなポイントの一つです

ところで、エクセルを使用していて、ファイルが重くなって困った事はありませんか?

エクセルシートに表示されているデータは、あくまで表示されているだけであって、表示されている箇所にデータ自体が存在するわけではありません

データはエクセルシートとは別の箇所に保管されています

そのため、シート上に他の箇所にあるデータを表示する際、PCに負担をかけてしまい、動作が重たくなるのです

表示行数が制限内でも、表示行数が多くなればファイルの動きも重くなります

そんな悩みを解決するのが「接続の作成のみ」です

表示しないクエリーを作成しておいて、前述の変換処理、①複数のクエリーの組み合わせ により、必要なデータだけ抜き出して表示する、もしくは利用するということができます

表示しないクエリーを利用することで、エクセルの処理を格段にスピードアップさせることができます!

<まとめ>

 今回は、Power Queryでは操作の仕方がどう変わるかについて、スマホを例えに使用して操作のイメージを解説しました

スマホはネットの情報をそのまま利用できます

 Power Queryでも、スマホと同様に「シート上のデータ」や「他のファイルのデータ」を取得し、そのまま結合・変換して直接利用することができます

 そして、読込先を「接続の作成のみ」に指定することで、ファイルを重たくしないように工夫することもできます。

 さらに、今回ご紹介できなかったPower Queryの魅力の一つは、スマホのように「直感的なクリック操作」に使用できることです。

 次回からはイメージだけでなく、実際に使用してみることにより、Power Queryの「直観的な操作」に触れてみましょう!

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

にほんブログ村

 

シートからデータ取得~ 初級講座2回目~

こんにちは、Excellent仕事術ガッツ鶴岡です

前回はPower Queryとは何か?について、スマホを例えにして解説しました

「スマホで好みのレストランを検索(データ取得)」⇒

⇒「レストランのページ内にある電話番号を活用(変換)」⇒

⇒「そのまま予約電話をかける(読込)」

 今回は操作のウォーミングアップとして、実際にエクセルシート内にあるデータを、Power Queryで取得しましょう!

 そして、取得したデータの変換処理⇒読込までの一連の流れを行ってみましょう!

今回のポイント

ところで、読者の皆さんは、テーブル機能を普段、活用されてますでしょうか?  

Power Queryでは、特にテーブル機能の理解が必須です

テーブルは「見出し」と「データ」の2つの箇所から構成されます

テーブル

 Power Queryではこのテーブルをブロック遊びのように結合したり、分解したりします

 エクセルの最新バージョンの機能は、このテーブルと連携する前提で作成されています

テーブル機能を覚えておいて損はありません

シート上にあるデータを、一括で処理できるようになります

まだ知らないという方は、ぜひこの機会に習得してみませんか?⇒関連記事

 テーブルの解説記事を読む時間が無い方は「テーブルとは、データを扱いやすくする機能」という風に割り切って覚えましょう!

 とにかくシート上で、データを見たら「見出し」があるのを確認してショートカットキー「Ctrl+T」を押しましょう!

 下の画像のように右クリックをしたら表示される、下の画像の箇所をクリックするのでもOKです!

 上の処理により、データの右下には下の画像のようなマークがついているはずです

これだけでテーブルが完成です

後はテーブル上で様々な事が行えます

 行列を自動的に拡大したり、1つのセルに関数を入力するだけで「1列」に関数を反映したりします

今回はPower Queryを使用して、シート上のデータを「テーブルに変換した上で取得」し、別シートへそのまま読み込んでみましょう!

尚、サンプルデータは以下をご活用ください!

アイコン

Power Query Data01 15.73 KB 79 downloads

...

1.シート上のデータを取得する

➀まず、サンプルデータにある表のどこかにカーソルを置きましょう!

②それから、データタブを選択後、下の画像上で黄色のマークをつけた箇所をクリックしましょう!

③すると、下の画像のようなダイアログボックスが開きます

「先頭行をテーブルの見出しとして使用する」にチェックを入れたまま、OKボタンをクリックします!

④しばらくすると、Power Queryエディターが開きます!

Power Queryエディター

Power Queryエディターが開いた時には、シート上の表はテーブル化されています

.クエリに名前をつける

Power Query Editorの右に「クエリ」の設定という欄があります

ここで、名前だけ変更しましょう!

今回は受注データとします

3.Power Queryエディターの内容を読み込む

では、Power Queryエディターの左上にある「閉じて読み込む」の右下の▼マークをクリックしましょう!

すると、以下のように「閉じて読み込む」というテキストが見えますので、こちらをクリックしましょう!

すると、データが蓄積されてあったシート(Dataシート)とは別のシートにデータが読み込まれます

読み込み

ここでシートの右側を確認してみてください!

受注データというクエリが見えるはずです!

受注データ・クエリーにカーソルを置いて右クリックするとダイアログボックスが表示されますので、一番下に見えるプロパティをクリックし、てみてください!

次に表示されたダイアログボックスの内容を見てみると、このクエリが「どこかに接続されている」ということがなんとなく分かると思います

前回、クエリーについては「スマホ内に設定するブックマーク」みたいなものと解説しました

実際、このクエリーを通じてデータにアクセスすることができています

試しに、取得元のデータに1行追加してみましょう!

その後クエリーを右クリックし、表示された「最新の情報に更新」をクリックしてみてください

するとクエリーの行数が増えているはずです

これはクエリーがデータに接続されているため、行数の増加が反映されたのです

さて、クエリーが「どういうものか?」について概要を理解できたところで、まとめに移りましょう!

<まとめ>

 今回は、シート上にあるデータをテーブルに変換した上で「データ取得~別シートにデータ読込」までを行いました

 読込まで行って作成されたクエリーは、後日解説するマージ技術を組み合わせると、複数シートに分かれたデータを1シートでまとめて処理できるようになります

 もし、テーブル機能をこれまで使ったことがなかったという方がいらっしゃいましたら、これを機会に有効活用していきましょう!

 データをエクセルで扱おうと思ったら、このテーブル機能はとにかく便利です。そして、Power Queryを操作する上でとても重要な機能です!

 ところで、Power Queryを初めて操作された感想はいかがでしょうか?スマホと同じような直感的な操作で一連の操作ができたはずです

直感的な操作のみでデータを扱えるのが、このPower Queryの良さです!

どんどん手を動かして慣れていきましょう!

次回は、他のファイルからデータを取得してみます!

それでは3回目でまたお会いしましょう!

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

にほんブログ村

文字列から空白を取り除く_Power Query

文字列に空白が入っていて処理に困ったことってありませんか?

 そういったデータに限って、大量にデータ数があったりしますので、手動で処理するのは困難です

 Power Queryであれば直観的クリック操作により、一括で処理できるので、とても効率的です

今回、解説に使用するデータは次の画像のデータです

サンプルデータ

 上の画像の商品名の列にある文字は最大で3文字しかないように見えます

 実は空白文字が入ってるので、LEN関数で長さを測ると10文字あります

データを確認したので、これから本格的な解説を始めます!

1.PQエディターを立ち上げる

➀データ上にカーソルを置いて、データタブから”テーブルまたは範囲から”をクリックします

➁テーブル作成のポップアップが出たらOKをします

2.トリミングを行う(空白を排除)

Power Query Editorが立ち上がったら、事前にトリミングを行う列は選択しておきます

トリミングを行うのは次の画像の画面になります

では、トリミングを実行してみます

一見、全く変わりはありません

エクセルシートに読込んで、文字の長さをLEN関数で測ってみましょう

3.エディタの内容をシートに出力する

ホームタブにもどり、”閉じて読み込む”をクリックします

この時に、「長さ」の列は事前に削除しておきます

 シートに出力した文字列の長さを調べてみましょう!空白が取り除かれているはずです

トリミングを行った右側の「商品名」は空白が混ざっていないので、文字列が見た目通りの長さになっています

<まとめ>

 今回のように空白を取り除くなどの変換処理は、関数で処理するなどの方法より、Power Queryで行うほうが圧倒的に処理時間は早いです 

 Power Queryについては講座も行っていますので、ぜひご参照ください

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

にほんブログ村

ファイルからデータ取得 初級講座3回目

こんにちは、Excellent仕事術ガッツ鶴岡です

前回ウォーミングアップとしてシート上にあるテーブルからデータを取得し、別シートに読込処理を行いました

今回から本格的にデータ取得について解説したいと思います。

ちなみに筆者は昔、EC通販会社の経営企画の仕事をしていました

経営企画とは名ばかりで、とにかくデータのツギハギ作業を一日中していました

ある時はこのファイルの・このシートの・この部分・

そして、ある時はあのファイルの・あのシートのあの部分・

といった感じで、いろんなところから必要な数字をコピーしてきて自社の数字を説明する資料を作るわけです

このツギハギ作業はとてもガッツを要する作業で、ミスも起こしやすく本当に嫌で仕方なかったのをよく覚えています

 今回の解説記事にて、「操作するファイル」とは「別のファイル」からデータを取得する技術を習得しましょう!

そうすれば、面倒なデータのツギハギ作業から解放されます

今回のポイント

ところで、ファイルの中にあるデータで、名前をつけて管理できるものは何種類ありますでしょうか?

シート名はすぐ思いつくと思います

実は、シートの他にもテーブル名、名前(英語でDefinedName)があります

今回は、操作しているファイルとは別のファイル内にある「テーブル」データをはじめとした、名前がつくデータを取捨選択しながら取得する方法を解説します。

では解説に入りたいと思いますが、その前にサンプルファイルを開いてみてください!

アイコン

サンプル4回目 15.54 KB 71 downloads

...

このサンプルファイルの中に、名前がつくデータが3種類、全部で4つあるのを確認してください。

①シート ⇒”Data”と”Presentation”の2つのシートがあります

②テーブル ⇒”テーブル1”があります

上の画像の「Dataシート」にテーブル名:テーブル1があります

見出しが緑色の表の上のどこかにカーソルを置いてテーブルデザインタブをクリックすると以下の画面が確認できます

③名前(DefinedName) ⇒”部門”

数式タブの名前管理をクリックして見てください

部門という名前のデータがDataシートにあるのが分かります

以下がDataシートにある、実際の部門という名前のデータです

上記の4つのデータを確認したら、サンプルファイルをどこか適当なフォルダにファイル名をつけて保存しましょう!

そして別途、Power Queryの操作用に新たなファイルを開いておきましょう!

それでは、実際の作業(以下1~4)の解説をはじめます

1.データを取得するファイルを指定

エクセル画面の上にあるデータタブをクリックした後

⇒データ取得 ⇒ファイルから ⇒ブックから

と順にクリックしていきましょう

するとファイル名を指定する画面が下のGIFのように開きます

この画面にて、先ほど保存したサンプルのファイル名を指定しましょう

ファイル名を指定したら、ナビゲーターという画面が開いています

下のGIFは「ブックからを指定」⇒「ファイル名指定」⇒「ナビゲータ―画面」までの一連の流れを撮影したものです

2.データの取得の仕方を指定

ナビゲーター画面の左には、下の画像のようにサンプルファイル内にある4つのデータが表示されているはずです

ナビゲーター画面

試しに、テーブル1のところをクリックしてみてください。

すると、先ほどサンプルファイル内で確認した、テーブル1の内容が出てくるはずです

つまり、このナビゲーターに表示されている内容でデータを取得できるという意味です。 

ちなみに、ナビゲーターの左上に「複数のアイテムの選択」というチェックボックスがあります

こちらにチェックを入れてみてください

すると4つのデータの左にもチェックボックスが表示されますので、取得するデータを自分の都合に合わせて複数指定することができるようになります。

今回は、取得するデータを取捨選択しないので「複数のアイテムの選択」のチェックを 外してください

そして、上の画面にある「表示オプション」の下に表示されているサンプルファイル名の部分(WorkbookData.xlsx[4])をクリックしてください

その後、下の画像にあるナビゲーター画面・右下の「データの変換」をクリックして下さい

すると、下のGIFのようにPower Queryエディターが開くはずです

3.実際に取得するデータを選択する

「データの変換」を押した後、次のようにPower Queryエディター画面が出てきましたでしょうか?

Power Queryエディター

上の画面では4つの行が表示されています

見出しの右から2つめの「Kind」の列に、データ種類が表示されています

Power Queryエディター内のどの行が、サンプルファイル内のどのデータかについてはすぐ分かると思います。

今回は4つのデータの内、上から3番目の行の「テーブル1」のデータを取得したいとおもいます

下のGIFのように「3番目の行」の「左から2番目」にある”Table“のテキストをクリックしてください。

サンプルファイル内の「テーブル1」のデータが展開されたと思います

次は、下のGIFのように画面左上にある「閉じて読み込む」をクリックしましょう

すると操作しているファイルにPower Queryエディター上で展開されたデータが読み込まれます!

読込を行ったデータは、操作したシートとは別のシートに読込まれまているのを確認してください

筆者が操作したのはSheet1だったので、下の画像のようにSheet2に読込まれました

<まとめ>

 今回は「操作しているファイル」とは「別のファイル」からナビゲーション画面を通じ、別ファイルのテーブルデータを取得しました

 Power Queryエディターで取得するデータを指定する際に気づかれた方も多いと思いますが、データを指定する際は「名前の管理」がきちんとされていると作業効率が上がります

 Powr Queryを有効活用するため、シート内のデータは普段から意識して名前管理を行っておきましょう!

 名前の管理をきちんとしておくことにより、後日解説するPower Queryの「変換機能」も有効活用していくことできます

では初級講座4回目を楽しみにしていてください!

フォルダ内にある「複数ファイルのデータ」を一括で取得するスゴ技を紹介します!

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

にほんブログ村