文字列に空白が入っていて処理に困ったことってありませんか？
そういったデータに限って、大量にデータ数があったりしますので、手動で処理するのは困難です
Power Queryであれば直観的なクリック操作により、一括で処理できるので、とても効率的です
今回、解説に使用するデータは次の画像のデータです
上の画像の商品名の列にある文字は最大で3文字しかないように見えます
実は空白文字が入ってるので、LEN関数で長さを測ると10文字あります
データを確認したので、これから本格的な解説を始めます！
➀データ上にカーソルを置いて、データタブから”テーブルまたは範囲から”をクリックします
➁テーブル作成のポップアップが出たらOKをします
こんにちは、Excellent仕事術のガッツ鶴岡です
前回ウォーミングアップとしてシート上にあるテーブルからデータを取得し、別シートに読込処理を行いました
今回から本格的にデータ取得について解説したいと思います。
ちなみに筆者は昔、EC通販会社の経営企画の仕事をしていました
経営企画とは名ばかりで、とにかくデータのツギハギ作業を一日中していました
ある時はこのファイルの・このシートの・この部分・
そして、ある時はあのファイルの・あのシートのあの部分・
といった感じで、いろんなところから必要な数字をコピーしてきて自社の数字を説明する資料を作るわけです
このツギハギ作業はとてもガッツを要する作業で、ミスも起こしやすく本当に嫌で仕方なかったのをよく覚えています
今回の解説記事にて、「操作するファイル」とは「別のファイル」からデータを取得する技術を習得しましょう！
そうすれば、面倒なデータのツギハギ作業から解放されます
ところで、ファイルの中にあるデータで、名前をつけて管理できるものは何種類ありますでしょうか？
シート名はすぐ思いつくと思います
実は、シートの他にもテーブル名、名前（英語でDefinedName）があります
今回は、操作しているファイルとは別のファイル内にある「テーブル」データをはじめとした、名前がつくデータを取捨選択しながら取得する方法を解説します。
では解説に入りたいと思いますが、その前にサンプルファイルを開いてみてください！
このサンプルファイルの中に、名前がつくデータが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のある機能を使うと、フォルダにファイルを入れておきさえすれば、全て一括で取得してくれるのです！
記事を最後まで読む時間が無い、という方はぜひこちら↓の動画をご覧ください
ちなみに、動画内で使用しているサンプルファイルは下からダウンロードできます
本題に入る前に、今回の解説に使用するデータを紹介します
今回の解説に使用するフォルダ（Data)の中には、３つファイルが入っています
営業の部署 にて、営業担当者毎に販売内容をフォルダ内に入れていく想定です
ファイルに書き込まれている内容（シート：Data)は次のような項目です
ちなみにDataシート以外にもメモというシートがあります
では、本格的な内容の解説に入りたいと思います
解説は 「１．取得元のフォルダを指定する」と「２．ファイルの中で取得するシートを指定する」の２つに分けて行います
今回もデータタブの操作から処理を開始します
➀フォルダから取得を指定します（下の図参照）
➁実際に使用するフォルダを指定します
今回の解説で使用するのは「Data」フォルダーです
③指定するフォルダ名を確認 ⇒「OK」を押すとフォルダの指定は終了です
フォルダの指定が終了した後は、１～3回目の解説では出てこなかった画面が登場します
➀フォルダに入っているファイル名が表示されます
下の図の中に「結合」のボタンが見えますでしょうか？
一番下に４つのボタンが見えますが、一番左のボタンです
このボタンについては次の➁の解説で操作します
➁ボタン右横の▼マークを押し、「データの結合と変換」を押します
この時、フォルダ内のファイルが１つでも開いていると、データの結合と変換がうまくいかないので注意が必要です
「データの結合と変換」をクリックした後は、また更に新たな画面が開きます
③取得するシートを指定します
新たに開いた画面（下の図参照）の左側に、ファイルに含まれるシート名（Data、メモ）が表示されるので、Dataシートを選択します
Dataシートを選択したら、OKボタンを押します
そうすると、Power Queryエディターが開きます
④Power Queryエディター画面で読込処理を行う
Power Queryエディター画面には複数ファイルの情報が一括表示されます
まず、設定されるクエリの名前を「一括データ」に変更しましょう！
次にPower Queryエディター画面左上で「閉じて読み込む」ボタンをクリックします
これでフォルダ内のデータが一括で読み込まれます
ファイル別に分かれていた注文NOも１～6まで一括で表示されています
さて、ここからがこのフォルダ一括読込機能の最大の醍醐味です
フォルダ内にもう一つ、ファイルを追加します
通常は、これまで行ってきた１、２の作業を行う必要がありそうですよね
ところが、ワンクリックで追加ファイルを一括で読み込むことができます
エクセルシートの右に”一括処理”という前回作成したクエリがあります
こちらのクエリの上にカーソルを置き、右クリックします
そうすると最新の情報に更新できるようになります
更新処理を行うと、フォルダ内に新規追加したファイル内の注文NO：7と8が下の図のようにシート上に反映されています
この機能はこれまでのエクセル処理の常識を覆す内容です
今までは私もそうでしたが、フォルダ内にある複数ファイルのデータを一括で取得しようとしたらVBAのコードを書いていました
しかも、そのVBAコードはVBA初心者が書ける内容ではないです
2-②「データの結合と変換のクリック時」でも説明しましたが、フォルダ内のファイルが一つでも開いているとうまく行かない点だけは注意が必要です
後はPower Queryの画面操作に慣れてくれば、とても簡単な処理だと思います
ぜひ、職場で有効活用してみてください！
次回は読込先の変更について解説します！
こんにちは、Excellent仕事術のガッツ鶴岡です
前回はフォルダ内にある複数ファイルのデータを一括で取得してシートに読込ました
ところで、このデータを読み込む作業をする時に、読込場所を自由に指定できたらどうでしょう？
とても便利ですよね
エクセル作業をする上で、大量のデータをミスなく移動させるのはガッツが必要です
元々、エクセルは表計算ソフトです
大量のデータを扱うのには、本来は不向きなのです
ところが、Power Queryの登場により大量のデータも無理なく扱えるようになりました
今回はPower Queryの機能を使用してデータの読み込み先を、自由自在に変更する方法を紹介します。
加えて、Power Query独自の読込手法も紹介します
ところで、
エクセルのセルの中には何が入っているでしょうか？
エクセルのセルに値が入っていると、セルの中にデータが蓄積されていると思いがちです
実はセルに表示されている値は、他の場所に蓄積されているデータが表示されているだけです
しかも、表示するのはとてもPCに負担をかけるので、エクセルの行が増えると、ファイルが重くなります
今回、紹介する読込先の変更手法では、このファイルが重くなる問題を回避する方法も、あわせて紹介しちゃいます
今回の読込先の変更は、クエリ上から右クリックするところから行います
右クリックを実行すると、次の画像の「読み込み先…」から読込先の変更が行えます
もし、このクエリが画面上に見えていない場合には、画面の上のデータタブから 「クエリと接続」をクリックしてください
今回、解説に使用するデータは、下の画像のように”12行”を別ファイルから読みこんであるデータです
解説に使用するデータを確認したところで、早速、解説を始めましょう！
前述のように、クエリ上で右クリックをすると、”読み込み先”を選択できます
①の読込先選択を行うと「データのインポート」という画面が開きます
データのインポート画面（下の図参照）にて「接続の作成のみ」を選択してOKを押します
次に、下の図のようにメッセージボックスが開きますので、OKを押して処理を進めてください
するとシート上からデータの表示が消えます
クエリの表示も下記の画像のように、接続専用と表示されます
再び、データのインポート画面を立ち上げます
①で開いたデータのインポート画面上でテーブルを選択します
すると、データの読込先を「既存のシート」上か「新規ワークシート上」かを選択できるようになります
「既存のワークシート」を選択した場合は、データを読み込むセル位置を指定できます
セル位置を指定してOKボタンを押すと、データのインポート画面で指定したセル位置にデータが再び表示されます
では、新規のワークシートを選択するとどうなるでしょうか？
新規のワークシートが作成され、新規のワークシート上にデータが読みこまれます
読込先の変更により、「ピボットテーブル」への変更も行えます
前述の「データのインポート」画面でピボットテーブルへの変更を行いますが、「ピボットテーブル レポート」という名称になっています
今回は、クエリの「接続の作成のみ」を通じて、データの読込先の変更を行いました
このエクセルシート上に「データを表示しない」選択が行える点は、Power Queryの特徴の一つと言って過言でないでしょう！
データのシート移動についても、ⅰ）読込先を「接続の作成のみ」へ変更、ⅱ）移動先のシートを指定、の２つの処理を通じて行うことができます！
エクセルはデータが増えるとファイルが重くなりがちです
エクセルで大量なデータを使用する上で、この「接続の作成のみ」を選択できるのは、Power Queryを使用する大きなメリットです
次回から中級編を始めますが、この「接続の作成のみ」は中級編ではどんどん活用していきます
中級編では、作成したクエリーを複数組合わせて、有効活用する方法も紹介します
この複数のクエリー複数組合わせた時に「接続の作成のみ」が威力を発揮します
ぜひ、今回の内容は復習しておきましょう！
また、
中級編では「グループ化による集計」についても解説をしていますので、こちらもぜひチャレンジしてみてください！
記事を最後まで読んで下さり、誠にありがとうございました！
今回は今までと違う側面からPower Queryを使用するメリットを解説していきたいと思っています
言うまでもなくエクセルは便利なツールです
但し、問題点が一つあります
”他人の作成したエクセルファイルはとても分かりにくいのです”
コメントが無い記録マクロ・・・ネストの中にネストを繰り返したIF関数・・・
などなど例を上げたらきりがありません
一方、Power Queryでは、Power Queryエディターを開けると、作成されたクエリ（出力内容）の中味と、出力されるまでの履歴が、とても簡単に分かるようになっています
今回は以下のテーマを題材にしてPower Queryのクエリの中味と履歴を把握する方法を解説していきます
＜今回のテーマ＞
・元データ：2016年と2017年の自動車販売データ（テーブル名：販売データ）
・元データをPower Queryにて年度別に合計金額を出します
・Power Queryの出力データ：年度別・合計金額
１．適用したステップの確認
テーマで紹介した販売データから年度別の販売金額を出力するまでの履歴をPower Queryエディターの右側、＜適用したステップの確認＞で確認することができます
２．ステップの中味の確認
各ステップ毎の詳細な中身は２つの方で確認できます
➀数式バーを確認する
今回の例で言えばソースをクリックすると、ソース元のテーブルが何かを確認することができます
②ステップ自体の中味の確認
各ステップの中味を確認するには下の画像の黄色の部分をクリックします
すると上の画像の例で言えば、グループ化した時の画面が出てきます
＜まとめ＞
Power Queryを使ったことの無い人には少し分かりにくい内容だとは思いますが、通常のエクセルの機能とは違い、中身と履歴が簡単に分かるというのは理解して頂けたのではないかと思います。更に分かり易くする為には各ステップの名前（通常は自働設定される）を分かり易く変更したりする方法も可能ですので、ぜひトライしてみてください
前回、Power Queryとは
”本来、表計算ソフトであるエクセルにAccessのデータベース管理機能を加えたもの”
という説明をしました
今回は、Power Queryの数ある機能の中で、Accessと同じように使える機能を一つだけ紹介します
Accessで使える機能の一つがテーブル作成で使用するオートナンバー機能です
この機能に代わる機能がPower Queryにもあります
Power Queryエディターを起動したら、列の追加タブをクリックしてください
するとインデックスというリボンが見えるはずです
０からでも１からでもいいので、どれかをクリックしてみてください
するとインデックスという列が出来ています
これがAccessのオートナンバーの替わりに活用できます
最後に
短い内容でしたが、いかがでしたでしょうか？
個人的にはエクセルでここまでできるようになったことに感慨深いです
ちなみにインデックス列は最終列に追加されるので、最初に持ってくるようにしましょう！（列名を変えるのを忘れずに！）
前回はPower Queryを使って区切り文字によりデータを分割する方法を紹介しました。今回はPower Query上で合計などを計算する方法を紹介します
＜使用するデータ＞
・年度別売上データ
＜前提操作＞
➀データタブから”テーブルまたは範囲から”クエリを作成するを選択します
②”テーブルの先頭行を見出しにする”にチェックを入れたままOKを押す
③Power Queryを立ち上げおく
１．合計値をグループ別（年度別）に算出する
➀売上金額の列にカーソルを置いたままグループ化をクリックする
②以下の画面のように各項目を設定する
③年度別に合計金額が算出されているのを確認する
④エクセルシートにクエリを読み込む
２．四則演算をする
➀カスタム列を追加する
売上金額の列にカーソルを置いたまま列の追加タブから”カスタム列”をクリックする
②計算したい列を追加する
事前に列名を”千円単位”に変えておく
③割り算（/1000）をする
④正しく値が算出されていることを確認する
＜まとめ＞
１．２．の事例ともに直感的に操作できている印象を持たれたのでしょうか？特に１についてはピボットテーブルの内容をより簡単に使えている印象です。エクセルが苦手な方でも抵抗なく活用できるのでないでしょうか？ぜひ有効活用してみてください
前回は
”本来は表計算ソフトであるエクセルにAccessのデータベース管理機能を加えたもの” として、代表的な機能を組み合わせて活用例（結合やピボット解除、マージ等）を紹介しました
今回はもう少し細かい特徴も紹介したいと思います
今回、紹介するPower Queryの特徴は関数を不要にする機能です
Power Queryはスマホのように直感的に操作できます。直感的に操作して関数を不要にする事例を１つ紹介します
＜区切り文字でデータを分割する＞
もし、次の様なデータを”－”で区切る場合には通常なら”区切り位置”の機能を使うと思います
ところが、データを都度、追加していく場合には複数の処理が必要な”区切り位置”機能を使うのが面倒なので、関数を使うことになります
上の画像だと３つの種類の関数を使っています
とても、作成が面倒ですし、管理も大変です
ところが、Power Queryだと実に簡単に区切り処理ができます
下のGIFはPower Queryでの処理です
テーブル更新するだけで、区切りが行われています
実は事前にテーブルへ区切り処理を埋め込んであります
➀テーブル上にカーソルを置いてPower Qeryエディターを起動する
②Power Queryエディタ上で変換タブから”列の分割”を指定して読込処理を行う
実はこれだけです
とても直感的だと思います
＜最後に＞
今回は関数を置き換える例としては、特殊な事例だったと思います（FIND関数を知っている人もそもそも少ないと思います）
もっと標準的な機能としてはPower Queryエディタ上にこんな機能があります
指定した列を四則演算するものです
これを使えば関数を使用する必要性はあまりありません
ちなみに行の合計はテーブル自体で行うことが可能です（過去記事）
ぜひ活用してみてください
Power Queryって言葉は知っているけど、具体的には何も分からないという方も多いと思います
私も色々と研究してみました
私の個人的な感想は
”本来は表計算ソフトであるエクセルにAccessのデータベース管理機能を加えたもの”
という感じでした
早速、”論より証拠！”、ということで実際にPower Queryで処理するところを、概要だけになってしまいますがお見せしたいと思います
＜実施すること＞
・１つのフォルダに、販売個数データに関する３つファイルが入っている
・３つのファイルにデータを１つにまとめた上で、価格表を参照して販売金額を算出して表にまとめる
・しかも、クリック処理だけで行う
＜手順＞
・ファイル結合
ファイルが入っているフォルダを指定してファイルを結合する
するとPowerQueryエディタ上でこんな感じになっています
・横持ちのデータを縦持ちに変える
PowerQueryエディタ上にある”列のピボット解除”をクリックすることにより、データの構造自体を一発で変えます
・PowerQueryエディタ上で縦持ちになったデータを価格表とマージします
・PowerQueryエディタ上で掛け算（販売個数X価格）を行いシート上に読み込む
最後までクリックだけで処理を完結することができました
＜まとめ＞
いかがでしたでしょうか？本来は関数などを使いながら、手間暇がかかった処理がクリックだけで簡単に出来そうなのが伝わりましたでしょうか？
今回は概要だけお見せしましたが、もっと詳細な解説ができるように準備を進めていきます！ （Power Queryって２？？はこちらから）
Power Queryを使えばAccessとExcelのいいところを組み合わせて利用することができます
昔、電話は電話帳をめくりながら、ダイヤルを回しましたが、今はスマホで検索して電話をしています
PowerQueryを使えば、電話帳のような大量のデータをAccessにて一括管理して、必要な時にエクセルで抽出して加工することができます
VBAのような専門知識は必要とせず、流れを覚えてさえしまえばとても簡単です
以下、概要だけ羅列します
１．PowerQueryを起動
２．抽出条件を指定します
３．抽出条件は後から削除することもできます
４．エクセルのセルで条件を指定できるようにします＿1/2
5．エクセルのセルで条件を指定できるようにします＿2/2
上記だけでは分かりずらいと思いますが、直感的にできるようになっていると思うのでぜひ試してみてください
