タグ別アーカイブ: ファイルからデータ取得

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

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

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

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

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

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

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

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

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

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

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

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

 この回では、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へ


にほんブログ村

 

ファイルからデータ取得 初級講座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へ

にほんブログ村