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

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

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

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

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

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

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

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

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

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

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

今回のポイント

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

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

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

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

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

サンプル4回目

1 ファイル 15.54 KB

ダウンロード

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

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

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

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

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

③名前(DefinedName)　⇒”部門”

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

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

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

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

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

それでは、実際の作業（以下１～４）の解説をはじめます

１．データを取得するファイルを指定

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

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

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

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

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

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

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

２．データの取得の仕方を指定

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

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

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

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

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

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

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

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

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

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

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

３．実際に取得するデータを選択する

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

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

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

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

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

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

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

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

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

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

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

＜まとめ＞

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

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

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

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

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

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

にほんブログ村

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

前回はフォルダ内にある複数ファイルのデータを一括で取得してシートに読込ました

ところで、このデータを読み込む作業をする時に、読込場所を自由に指定できたらどうでしょう？

とても便利ですよね

エクセル作業をする上で、大量のデータをミスなく移動させるのはガッツが必要です

元々、エクセルは表計算ソフトです

大量のデータを扱うのには、本来は不向きなのです

ところが、Power Queryの登場により大量のデータも無理なく扱えるようになりました

今回はPower Queryの機能を使用してデータの読み込み先を、自由自在に変更する方法を紹介します。

加えて、Power Query独自の読込手法も紹介します

ところで、

エクセルのセルの中には何が入っているでしょうか？

エクセルのセルに値が入っていると、セルの中にデータが蓄積されていると思いがちです

実はセルに表示されている値は、他の場所に蓄積されているデータが表示されているだけです

しかも、表示するのはとてもPCに負担をかけるので、エクセルの行が増えると、ファイルが重くなります

今回、紹介する読込先の変更手法では、このファイルが重くなる問題を回避する方法も、あわせて紹介しちゃいます

＜今回のポイント＞

！クエリの操作

今回の読込先の変更は、クエリ上から右クリックするところから行います

右クリックを実行すると、次の画像の「読み込み先…」から読込先の変更が行えます

もし、このクエリが画面上に見えていない場合には、画面の上のデータタブから 「クエリと接続」をクリックしてください

今回解説に使用するデータ

今回、解説に使用するデータは、下の画像のように”12行”を別ファイルから読みこんであるデータです

解説に使用するデータを確認したところで、早速、解説を始めましょう！

既に表示してあるデータを非表示にする ⇒ 接続の作成のみ

➀クエリ上で読み込み先を選択

前述のように、クエリ上で右クリックをすると、”読み込み先”を選択できます

➁「接続の作成のみ」の選択

①の読込先選択を行うと「データのインポート」という画面が開きます

データのインポート画面（下の図参照）にて「接続の作成のみ」を選択してOKを押します

次に、下の図のようにメッセージボックスが開きますので、OKを押して処理を進めてください

するとシート上からデータの表示が消えます

クエリの表示も下記の画像のように、接続専用と表示されます

非表示になったデータの読込先の指定

➀クエリ上で”読み込み先”をクリック

再び、データのインポート画面を立ち上げます

➁テーブルの選択

①で開いたデータのインポート画面上でテーブルを選択します

すると、データの読込先を「既存のシート」上か「新規ワークシート上」かを選択できるようになります

③既存のワークシートを選択

「既存のワークシート」を選択した場合は、データを読み込むセル位置を指定できます

セル位置を指定してOKボタンを押すと、データのインポート画面で指定したセル位置にデータが再び表示されます

④新規のワークシートを選択

では、新規のワークシートを選択するとどうなるでしょうか？

新規のワークシートが作成され、新規のワークシート上にデータが読みこまれます

ピボットテーブルへの変更

読込先の変更により、「ピボットテーブル」への変更も行えます

前述の「データのインポート」画面でピボットテーブルへの変更を行いますが、「ピボットテーブル レポート」という名称になっています

＜まとめ＞

　今回は、クエリの「接続の作成のみ」を通じて、データの読込先の変更を行いました

　このエクセルシート上に「データを表示しない」選択が行える点は、Power Queryの特徴の一つと言って過言でないでしょう！

　データのシート移動についても、ⅰ）読込先を「接続の作成のみ」へ変更、ⅱ）移動先のシートを指定、の２つの処理を通じて行うことができます！

エクセルはデータが増えるとファイルが重くなりがちです

　エクセルで大量なデータを使用する上で、この「接続の作成のみ」を選択できるのは、Power Queryを使用する大きなメリットです

　次回から中級編を始めますが、この「接続の作成のみ」は中級編ではどんどん活用していきます

　中級編では、作成したクエリーを複数組合わせて、有効活用する方法も紹介します

　この複数のクエリー複数組合わせた時に「接続の作成のみ」が威力を発揮します

ぜひ、今回の内容は復習しておきましょう！

また、

中級編では「グループ化による集計」についても解説をしていますので、こちらもぜひチャレンジしてみてください！

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

にほんブログ村