カテゴリー別アーカイブ: Power Query

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

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

読者の皆さんは、他の方が作成した表を見て「使いにくい」と思ったことはありませんか?

例えば、下の図のような表です

1セルの中に複数のデータがカンマ” , ”を区切りにして混在しています

しかも、担当者が縦への繰返しで並んでいるのに、担当企業の欄は横に並んでいます

この表を他の目的に活用しようと思ったら、下の図のような「縦並びが複数列で繰り返された表」に変換するしかありません

この変換処理は手で行ったとしたら莫大な時間がかかります

ところが、Power Queryならば一括で変換できてしまうのです!

今回は上の表の変換を事例として、Power Queryを活用したデータクリーニング技術について、2回に分けて解説します

<今回のポイント>

A.セル分割

このセル分割によるデータ変換が今回のメインの解説になります

区切り文字を指定し、分割の仕方を指定するのがポイントです

B.ピボット解除

ピボット解除については前回も解説しました

この機能はとても便利です

今回も横並びを縦並びにするのに活用します

では、演習ファイルをダウンロードして頂いたら解説をはじめます!

今回は、セルの分割までです

1.セル分割

①Power Query Editorを開く

問題となる表の上にカーソルを置き、”テーブルまたは範囲から”をクリックし、Power Query Editorを開く

➁列の分割をクリック

Power Query Editorが開いたら、担当企業を選択した上で、変換タブから”列の分割”の▼マークをクリックします

③区切り記号と分割方法を指定

列の分割の▼マークをクリックすると、”区切り記号による分割”が選択できるようになりますので、またこの文字をクリックします

すると次のような画面が開きます

さて、今回のキーポイントに入る前に、元の表を再度確認しましょう!

カンマ” , ”が不規則に出現し、複数のデータが1セル内に混在しています

このセルを、区切り文字のカンマが出現するごとにデータ分割すればいいのです

ですから、区切り記号は”カンマ”を指定し、分割方法はデフォルトの「区切り記号の出現ごと」のままにし、右画面下のOKボタンを押します

すると、1セルの内容がカンマ” , ”ごとに複数のセルに分割されます!

<まとめ>

今回は、カンマ” , ”で区切られた使いにくい表をPower Query Editor上で分割まで行いました

今までのエクセル上でも同じようなことはできましたが、Power Queryではもっとシンプルに行えるようになっています

次回は使える表への変換をガッツで完成しましょう!

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

にほんブログ村

エディター内のやり直し操作~中級編9回目~

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

Power Queryでは、従来のエクセル操作では全く使うことがなかった画面が出てきます

一番、代表的なのがデータを取得した後に出てくるPower Query Editor(以降、エディター)です

エディターでは、様々なデータの変換操作が行えます

前回はエディター内にて、1つのセルのデータを複数のセルに分割しました

ただ、このエディターでは従来のやり直し操作、ショートカットキーで言えばCtrl+Zの操作はどう行うのでしょうか?

やり直しが出来ないと、操作ミス一つで全てをやり直さなくなってしまいます

これでは、Power Queryを新たに覚えて業務を効率化をする意味がなくなってしまいます!

今回は「やり直し操作」について解説していきます

1.ステップ管理

エディター内では、全ての操作はステップという形で記録されていきます

次の画像は初級講座2回目で使用したデータを取得し、エディターを開いた状態の画像です

ここから2つの操作を行います

エディター画面の右には適用したステップを管理する画面があり、操作前は以下の状態です

ここから2つの操作を行います

①受注日の型式を日付形式に変える

➁日付を9月3日から9日の範囲でフィルターをかける

①②の操作を行うと、下のGIFのように適用したステップ画面にステップが記憶されます

2.やり直し操作

1.でステップ管理について解説したので、このステップをやり直す方法について解説します

このやり直しには2つの方法があります

1つはステップを消去する方法です

もう一つはステップ自体を修正する方法です

①ステップを消去する

適用したステップを消去すれば、下のGIFにあるように元の状態に戻ります

「フィルターされた行」のステップを消去した瞬間に日付の行が増えているのがよくわかると思います

この方法は後で紹介する➁の方法より簡単ですが、元の状態に戻るだけです

上のGIFの例で言えば、フィルターに設定する条件を変えようと思ったら、またフィルター設定だけは一からやり直さなくてはなりません

➁ステップの修正

ステップ自体を修正する方法もあります

ステップの右横の*マークを押せばステップ自体を修正できます

以下のGIFでは、フィルター処理をしたステップにて、右横の*マークを押した後、フィルターの条件設定画面からやり直しが行えるようになりました

これまで①②とエディター画面にて「やり直す方法」を解説しました

1点、①の「元に戻す」方法については注意点があります

最後尾のステップのステップではなく、途中のステップを削除や修正を行う場合です

これまで解説してきた事例では、

 日時型式⇒日付形式への変換

を行った後、

 日付形式でフィルター設定

を行いましたので、もし、日時型式のままだとフィルターの設定型式が不整合になってしまいます

仮に「変更された型」のステップを消去しようとすると、以下の様なメッセージが出ます

上のメッセージで削除を選択した場合には、「フィルター」のステップも消去することになります

<まとめ>

今回は、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へ

にほんブログ村

複数条件でグループ化~中級編12回目~

前回はPower Queryのグループ化機能により、グループ毎に合計金額を集計しました

今回は前回の内容を活かしながら、グループ化の切り口を複数指定できるようにしましょう!

実質的には、ピボットテーブルでグループを複数階層化するのと同じことが行えるようになります

グループ化詳細
今回のアウトプット

では、ポイントの解説から始めましょう!

今回のポイント

A.グループ化指定

今回はグループ化の出力指定を、基本ではなく「詳細設定」にて指定します

パワークエリ-グループ化詳細
詳細設定

指定方法を詳細設定に指定することで、グループ化切り口を追加できるようになります

後の指定方法については、前回の基本設定と同じです

B.ステップ修正

中級編8回目で解説をしましたが、Power Queryでは「やり直し操作」はステップ管理画面にて行います

ステップ管理画面

なお今回は、前回の集計結果がエクセルシートに読込まれた状態からグループ化処理を行います

ですので、クエリー編集⇒エディター画面を開く⇒ステップ管理⇔ステップ修正の流れになります

さて、

ポイントを2点確認したところで、詳細な解説を始めましょう!

下のファイルは前回の集計結果をシートに読込んだ状態になっていますので、ぜひこちらをご活用ください

目次

クエリー編集

ステップ変更

詳細設定およびグループ化の追加

グループ化項目の設定

読込処理

<まとめ>

クエリー編集

クエリーを右クリックし、編集をクリックします

クエリー編集

もし、クエリ―画面が表示されていない場合には、以下の画面を操作してください

クエリ表示

ステップ変更

開いたエディター画面にて、前回グループ化を行ったステップを変更します

パワークエリ・ステップ修正
ステップ修正

するとグループ化画面を修正できるようになります

続きを読む 複数条件でグループ化~中級編12回目~

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を使用して、シート上のデータを「テーブルに変換した上で取得」し、別シートへそのまま読み込んでみましょう!

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

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エディターを立ち上げる

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

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

<まとめ>

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

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

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

続きを読む 文字列から空白を取り除く_Power Query

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

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

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

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

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

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

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

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

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

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

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

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

今回のポイント

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

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

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

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

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

このサンプルファイルの中に、名前がつくデータが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へ

にほんブログ村