タグ別アーカイブ: 変換

「例からの列」にて変換パターン自作~上級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へ

にほんブログ村

Power Queryって何8???~PDFファイルをエクセルに変換~

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

この記事は、Power Queryというエクセルのスゴイ新機能をまだ知らない人向けに書いている記事です

「エクセルで大量のデータを扱っているけど、何とか作業を効率化したい」

「エクセルをぜひ得意分野にしたい」

という方はぜひページの最後までお付き合いください

ところで、読者の皆さんは、スマホを初めて操作した時にどんな印象をもちましたでしょうか?

「ネットでレストランを見つけてそのまま予約電話ができる!!」

「直観的で操作が分かり易い」

筆者はPower Queryを登場した時には同じぐらい、いや、それ以上の衝撃を受けました

今回は筆者が衝撃を受けたPower Queryの機能の一つ、PDFファイル内のデータをエクセルファイルにダウンロードする方法を、簡単に紹介していきたいと思います

ちなみに今回、紹介する内容は、今までPower Queryを使ったことが無い人でも取り組める内容になっています

本題に入る前に、サンプルで使用するPDFの中味について紹介させてください

サンプルのPDFファイルは2ページに分かれており、以下のように2ページ内に3つの表があります

・1ページ目⇒2つの表データ

・2ページ目⇒1つの表データ

今回の解説では、PDFファイルのデータをまるごとエクセルシートにダウンロード(読込)するのではなく、ファイル内の3つの表をそれぞれ3つのエクセルシートにダウンロードします!

それでは、本格的な操作について解説を始めます

解説に使用するPDFファイルはぜひ、事前にダウンロードしておいてください

アイコン

サンプルPDFファイル 49.80 KB 371 downloads

...

1.PDFファイルを指定

データタブの「データ取得」から「ファイルから」⇒「PDFから」を指定します

次にファイル自体を指定します

取得するファイルを指定すると、2.で解説する「ナビゲーター画面」が開きます

(注)もし、「PDFから」のボタンが無い場合

        ➡4.「PDFから」のボタンが無い場合

2.読込む対象データを指定

PDFのデータをPower Queryで取得する場合には、2つの方法を選択できます

・PDFのページ全部を取得する

・ページ内のテーブルデータ(表データ)を取得する

上の2つの方法の選択は、取得するファイルを指定した後に開く、この「ナビゲーター画面」で行います

ナビゲーター画面では、ページ単位やテーブルデータ単位にて、取得するものを取捨選択することもできます

ナビゲーター画面では、左側にリストで表示される各Table(テーブルデータ)、もしくは各ページの内容を、下のGIFのように確認することができます

実際に取得する内容を決めたら、ナビゲーター画面の左上「複数のアイテムの選択」にチェックを入れた上で、取得対象にもチェックを入れます

今回の解説では、下の画像のように3つのTable(表データ)にチェックを入れます

3.ダウンロード

取得対象データをナビゲーター画面で3つ指定しましたので、今度はダウンロード先を指定します

ナビゲーター画面下の「読み込みボタン」の横にある▼ボタンをクリックし、クリック後に表示された「読み込み先」のテキストをクリックします

「読み込み先」のテキストをクリックした後は実際の読込先、つまりダウンロード先を指定する画面が開きます

この解説では、読込先を指定する画面にて「テーブル」「新規ワークシート」の2つを指定します

この読込先の指定については、詳細な内容を解説した記事がありますので、そちらを参照してください

読込先として既存シートに読込を行ったり、ピボットテーブルで出力することも可能です

ここまで行えば、PDF内にあった3つの表データが下のGIFのように、3つのエクセルシートに分かれてダウンロードされています

ちなみに画面右に「クエリ」というものが3つできていますが、これはスマホ内のブックマークのようなものだと思ってください

4.「PDFから」のボタンが無い場合

バージョンによっては「PDFから」のボタンが出てきません

その際には、以下の手順でPower Queryエディタにデータを読み込んでください

①データタブ➡データの取得➡ファイルから➡ブックから

「PDFから」ではなく「ブックから」をクリック

②すべてのファイルを指定

通常ですと、PDFファイルを指定できませんので次の画像のように「すべてのファイル」を指定してください

すると、次の画像のようにPDFファイルが指定できます

③エディタでPDFファイルをクリック

エディタが開いたら、PDFファイルの表示をクリックしてください

④開く内容を指定

PDFファイルの内容が展開されるので、実際に開く内容を指定します

上の画像で「Table」の横あたりにカーソルを置くと、中身を見ることができます

開く内容を確認したら該当する行の「Table」のテキストをクリックしてください

後は、3.ダウンロードから処理を進めていってください

参考までにYOUTUBE動画も添付させて頂きます

https://youtu.be/pr14ReXLFC8

<まとめ>

今回は、PDFファイル内の表をPower Queryを使ってエクセルシートにダウンロードしました

ダウンロードするまでには3つの処理を行いました

1.PDFファイルの指定

2.ナビゲーター画面で取得テーブルデータ、もしくはページの指定

3.エクセルシートへの読込処理

この3つの処理だけで「PDFファイル内のデータ」をエクセルシートにダウンロードすることができました!

今回はPDFファイル内のデータをダウンロードする技術について解説しましたが、実はWebページ内のデータもダウンロードできます

Webページ内のデータのダウンロードについて興味がある方はこちらの記事をぜひご参照ください!

PDFファイルをPower Queryを使用してエクセルシートに取得する際には、注意点が一点あります

PDFファイルの状態によっては、表の体裁を整えるために見出し行を調整するなどのデータ整理処理が必要なケースがあります

この点は、実際に業務でPDFファイルを読み込む際には調整が必要になります

➡調整方法はこちらの記事で解説しています

https://analytic-vba.com/power-query/advanced/delete-row/

 
 今回はご紹介できませんでしたが、 Power Queryのスゴイ点の1つは、データ整理などが一括で行える点です

もちろん、Power Queryのデータ整理技術は、前述のPDFファイルからデータ取得した際にも活用できます!

もし、ここまで記事を読んでPower Queryについてご興味が湧いたという方はぜひ、シリーズで解説記事を書いていますので、そちらをぜひご参照ください

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 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へ

にほんブログ村

変換した列の追加~中級編10回目~

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

前回、案内したようにエディター画面での変換処理について解説します
エクセルはあくまで表計算ソフトであり、Accessのような大量のデータを扱うのに適したデータベースソフトではありません
Power Queryのエディター画面にある機能を使いこなせば、データベースソフトと同じ様に一括で変換処理が行えます
今回は、エディター画面の「列の追加」タブにある機能の一部を解説します

エディター画面
列の追加画面

解説に使うデータは、初級講座1回目で使用したデータを使います

解説用データ
      販売データ

次から解説する、1~3のステップを通じて、既存の表の横に「連番」と「部門コード」「販売金額」の3列から構成される表を追加します

列追加
連番及び条件付き列追加

.連番追加(インデックス)

取得したデータに、エディター画面で連番を付けます

①エディター画面を開く

まず、既存の表からデータを取得してエディター画面を開きます

取得データ
取得データ

⓶インデックス列

エディター画面が開いたら「列の追加」タブをクリックし、「インデックス列」の横にある▼をクリックします

インデックス列の付け方
インデックス列

ここで、連番の付け方を選ぶことができます

 ・0から始め、増分を1にする

 ・1から始め、増分を1にする

 ・開始番号と増分をカスタマイズする

ここでは1からを選択します

インデックス列
連番

2.条件列

この条件列は従来のエクセルではIF関数に相当するものです

IF関数とは違い、エディタ―画面では設定条件に従い、一括で変換します

それに、IF関数ほど設定の仕方は複雑ではありません

まず、「条件列」をクリックして、設定画面を開きましょう

条件列の追加

新たに開いた「条件列の追加」画面では、以下の6つを設定します

 ①追加される列名

 ②~④設定条件

 ⑤設定条件に合致する場合の出力結果

 ⑥設定条件に合致しない場合の出力結果

今回は、部門名が東京の場合は「T」大阪の場合、つまり東京以外の場合は「O」を出力するので、以下の画面のように設定します

条件設定
条件設定画面

3.読込処理

①列の削除

今回出力する表は以下の様になるので、無駄な列は削除します

出力する表

削除は「削除する列」にカーソルを置いて右クリック⇒削除、の順で行います

列が隣合っている列を一気に削除する場合は、Shiftキーを押しながら削除対象列を指定してください

複数列指定
列削除

②列移動

新たに追加した2列を、まとめて移動します

列移動
列移動

③読込先の指定

今回は、既存シートにエディターで操作したデータを読み込みます

読込

ホームタブで「閉じて次に読み込み」をクリックした後、データインポート画面にて読込位置を指定してください

「表示」はテーブル、「データを返す先」は既存のワークシートを指定します

データのインポート
データのインポート

<まとめ>

今回は、エディター画面で2種類の列を追加する方法を解説しました

1つは連番の追加、2つ目は従来のエクセルで言えばIF関数に相当する条件列の追加です

他にもエディター画面での列削除や列移動についても解説を行いました

今回の解説を通じて、エディター画面がスマホと同じ様に、直感的に操作できる利点を感じて頂けたら嬉しいです!

次回はPower Queryを使用して集計上手になるため、グループ化について解説します

それではガッツで頑張りましょう!

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

にほんブログ村

グループ化1~中級編11回目~

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

前回は、Power Queryのエディター画面(Power Query Editor)にて条件列とインデックス列を作成しました

今回は前回の「列の追加タブ」ではなく、「HOME」タブにてデータのグループ化に取り組みます

ここで言うグループ化とは、単にデータをグループに分けるだけでなく、データをある切り口でグループ化した上で、グループ毎に合計金額などを集計することです

このグループ化の処理が行えると、関数の入力もピボットテーブルの利用もないので、数字の集計処理が格段に早くなります

グループ化はスマホのように直感的に行えるようになっていますので、ぜひサンプルファイルを使用して実際に試してみてください

Power-Query-Editor
グループ化

今回、サンプルに使用するデータに5列あり、列の構成はⅰ)年度別、ⅱ)製品別、且つ、ⅲ)Region、ⅳ)地域、a:売上金額となっています

使用データ

このデータをグループ化して、ⅱ)製品別のa:売上金額を集計します

使用データ

1.エディターを開く

①「テーブルまたは範囲から」をクリック

データ上にカーソルを置き、、「テーブルまたは範囲から」をクリックします

②エディターが開く

今回は、既にテーブル化してあるので、①の操作でそのままエディターが開きます

2.グループ化

グループ化の処理は、前述したようにエディター内のHOMEタブで行います

①グループ化をクリック

グループ化

②グループ化設定 ⇒ OKボタン

今回は、前述のようにグループ化するのは「製品別列」、合計するのは「売上金額列」になります

グループ化画面にて最初に設定するのは、グループ化する列です

グループ化する対象列は▼マークをクリックすると選択できるようになります

後は、画面の表示に従って設定しています

グループ化する列を設定したら、画面下の3項目を設定します

グループ化・設定

「新しい列名」については、今回は「製品別・売上金額」としました

「操作」では、合計や平均などの集計方法を選択できます

今回は「合計」を集計方法として選択します

最後は集計対象の列、「売上金額」を指定します

指定が終わり、OKボタンを押せば、以下のようにグループ化が行われています

グループ化・結果

<まとめ>

今回は、エディター画面でグループ毎に合計金額を集計する方法について解説しました

記事の冒頭でグループ化は「スマホのように直感的に行える」と述べました

グループ化設定する項目の位置については、集計する対象の列と混同しやすいので、この点だけは注意が必要です

まず最初に設定するのが、グループ化する列です

そして、一番最後に設定するのが「集計する対象の列」となります

今回の解説は以上です

次回は、グループ化・機能を更に有効活用するためにピボットテーブルと連携します

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

にほんブログ村

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

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

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

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

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

サンプルデータ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<まとめ>

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

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

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

にほんブログ村

フォルダから複数ファイルデータを一括取得~初級講座4回目

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

前回はエクセルファイルからデータを取得しました

今回はフォルダにある複数ファイルから一括でデータを取得します。

フォルダ内データ一括取得

この機能はPower Queryの機能の中でも一押しの機能のひとつです

何故なら、複数ファイルに分散しているデータを一括で処理しようとしたら、通常はガッツのある人の手が必要となるからです。

その一括処理の内容はとても面倒くさく、ミスも起こり易いです

ところが、今回紹介するPower Queryのある機能を使うと、フォルダにファイルを入れておきさえすれば、全て一括で取得してくれるのです!

記事を最後まで読む時間が無い、という方はぜひこちら↓の動画をご覧ください

ちなみに、動画内で使用しているサンプルファイルは下からダウンロードできます

https://www.youtube.com/watch?v=LgjzjTSylcs

本題に入る前に、今回の解説に使用するデータを紹介します

今回の解説に使用するフォルダ(Data)の中には、3つファイルが入っています

アイコン

フォルダ用データ 22.26 KB 7 downloads

...

営業の部署 にて、営業担当者毎に販売内容をフォルダ内に入れていく想定です

ファイルに書き込まれている内容(シート:Data)は次のような項目です

ちなみにDataシート以外にもメモというシートがあります

では、本格的な内容の解説に入りたいと思います

解説は 「1.取得元のフォルダを指定する」と「2.ファイルの中で取得するシートを指定する」の2つに分けて行います

1.取得元のフォルダを指定する

今回もデータタブの操作から処理を開始します

➀フォルダから取得を指定します(下の図参照)

➁実際に使用するフォルダを指定します

今回の解説で使用するのは「Data」フォルダーです

③指定するフォルダ名を確認 ⇒「OK」を押すとフォルダの指定は終了です

フォルダの指定が終了した後は、1~3回目の解説では出てこなかった画面が登場します

2.ファイルの中で取得するシートを指定する

➀フォルダに入っているファイル名が表示されます

下の図の中に「結合」のボタンが見えますでしょうか?

一番下に4つのボタンが見えますが、一番左のボタンです

このボタンについては次の➁の解説で操作します

➁ボタン右横の▼マークを押し、「データの結合と変換」を押します

この時、フォルダ内のファイルが1つでも開いていると、データの結合と変換がうまくいかないので注意が必要です

「データの結合と変換」をクリックした後は、また更に新たな画面が開きます

③取得するシートを指定します

新たに開いた画面(下の図参照)の左側に、ファイルに含まれるシート名(Data、メモ)が表示されるので、Dataシートを選択します

Dataシートを選択したら、OKボタンを押します

そうすると、Power Queryエディターが開きます

④Power Queryエディター画面で読込処理を行う

Power Queryエディター画面には複数ファイルの情報が一括表示されます

まず、設定されるクエリの名前を「一括データ」に変更しましょう!

次にPower Queryエディター画面左上で「閉じて読み込む」ボタンをクリックします

これでフォルダ内のデータが一括で読み込まれます

ファイル別に分かれていた注文NOも1~6まで一括で表示されています

さて、ここからがこのフォルダ一括読込機能の最大の醍醐味です

フォルダ内にもう一つ、ファイルを追加します

アイコン

追加用1 9.66 KB downloads

...

通常は、これまで行ってきた1、2の作業を行う必要がありそうですよね

ところが、ワンクリックで追加ファイルを一括で読み込むことができます

エクセルシートの右に”一括処理”という前回作成したクエリがあります

こちらのクエリの上にカーソルを置き、右クリックします

そうすると最新の情報に更新できるようになります

更新処理を行うと、フォルダ内に新規追加したファイル内の注文NO:78が下の図のようにシート上に反映されています

<まとめ>

この機能はこれまでのエクセル処理の常識を覆す内容です

今までは私もそうでしたが、フォルダ内にある複数ファイルのデータを一括で取得しようとしたらVBAのコードを書いていました

しかも、そのVBAコードはVBA初心者が書ける内容ではないです

2-②「データの結合と変換のクリック時」でも説明しましたが、フォルダ内のファイルが一つでも開いているとうまく行かない点だけは注意が必要です

後はPower Queryの画面操作に慣れてくれば、とても簡単な処理だと思います

ぜひ、職場で有効活用してみてください!

次回は読込先の変更について解説します!

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

にほんブログ村