スポンサーリンク
はじめに
今回は、ExcelでPowerQueryを操作するに当たって、エディターの使い方の基礎について説明していく。
Excelの操作方法については、デフォルトのリボン設定を元に説明するが、リボン設定は極力カスタマイズして効率的に操作するようにしてもらいたい。
また今回の操作は、
こちらのサンプルファイル
を元に説明していく。
上記のファイルを、各自PCの適当なローカルフォルダに保存した上で、進めてもらいたい。
ファイルを開く
ではまずExcelファイルを開こう。
Excelのリボン「データ」タブ
→データの取得
→ファイルから
→ブックから
とする。
Excelファイルを開く画面が表示されるので、該当Excelファイルを開く。
左側の画面で、シートを選べるので、今回は唯一の「データ」シートを選択する。
次に「データの変換」を押す。
(私の考えでは「データの変換」は、本来のオススメ順としては2番めなのだが、手っ取り早く実験したいときは「データの変換」を使うことにして今後も解説していく。)
見出しを正しく認識させる
これで、PowerQueryエディターが表示されたはずだ。
さて、開いたExcelファイルでは、1行目～2行目に余計なデータが入ってるせいで、最上部に「Column1」とか表示されてる。
これは、見出しが正しく認識されていない状態だ。
邪魔な2行を削除するために、以下の手順を実行しよう。
→「行の削除」
→「上位の行の削除」
→次の画面で、邪魔な行数である「2」を入力し「OK」
これで邪魔な2行は削除されたが、まだ見出しに「Column1」などと表示されたままなので、見出しを正しく認識させる手順は次の通りだ。
→「1行目をヘッダーとして使用」
または
リボン「変換」タブ
→「1行目をヘッダーとして使用」
でも良い。
これで、見出しが正しく設定された状態になる。
PowerQueryの「ステップ」
さて、初めてでは目に入りにくいが、PowerQueryのエディターでは、こうして操作していった経緯が右端の「適用したステップ」という画面に記録されていく。
先ほど
(1)「行の削除」→「上位の行の削除」の操作でやったのが「削除された最初の行」というステップ
(2)「1行目をヘッダーとして使用」の操作でやったのが「昇格されたヘッダー数」 というステップだ。
どういう操作をすればどういう名前のステップが追加されていくのか、経験していけば少しずつ分かるようになってくる。
それはそうと、一番最後に付いている「型の変換」というやつは、上記の操作では全然やってない操作だが、自動で追加されたものだ。
どうもPowerQueryでは、「昇格されたヘッダー数」とかのステップ(操作)に対して、この「型の変換」が勝手に自動で付いてくるらしい。
で、これは今後の操作に邪魔なので削除しておきたい。
ここで大事なことなのだが、PowerQueryのエディターでは通常のExcelの操作のように、一つ一つの操作を操作された順番にCtrl+Z(元に戻す)などの操作で取り消していくのではない。
この「ステップ」画面に表示された操作ステップのうち、任意のものを指定して削除することで、途中の操作ステップを自由に消すことができる。
また、
- 操作ステップの順番を入れ替える
- 途中にステップを追加で埋め込む
- ステップの集まりを切り離して、別の処理クエリに分ける
といったこともできる。
ただ、「A列の列名を変更する」→「A列を削除する」とかいうステップは、この順序を逆に入れ替えるなんてことをしたらどう考えても無理なことになるので、そういうのはエラーになる。
では、長々と書いたが、ラストに自動追加されたけど邪魔な「変更された型1」というステップを削除しよう。クリックすると左端に出てくる☓印を押すことで、削除できる。
列を削除
では試しに、「姓カナ」「名カナ」の2つの列を、不要なものとして削除してみよう。
これは簡単で、これら不要な列を選択してDeleteキーを押せば良い。
複数列の選択は、Shiftキーを押しながらクリックしていけばよいし、離れた列どうしならCtrlキーを押しながらクリックすれば選択できる。
型の変換
さて、先ほど自動追加された「変更された型1」ステップを削除したが、これは、型の変更というやつは自動ではなく自分で正しく入れたいからだ。
では一番左の「生年月日8ケタ」という列を見ると、左端に「ABC」とか書かれている。
この「ABC」表記は、「生年月日8ケタ」が文字列の型だということだけど、この辺もどの表示が何の型かというのは、すぐ分かるようになる。
それでは、リボン「変換」タブの「データ型」というところから、「日付」の型を選ぼう。
あるいは、右クリックメニューからもこの型変換メニューは出てくる。
左端の「ABC」マークを押してからでも良いのだが、それでは複数列の型をいっぺんに変換することはできない。
この型変換の操作で、「生年月日8ケタ」フィールドは、正しく日付の型に変換される。
なんか「ABC」マークがカレンダーっぽいマークに変わっていて、これが日付型ということだ。
型の自動変換との競合
先ほど削除した、自動追加された「変更された型1」ステップにおいては、この「生年月日8ケタ」フィールドは自動的に数値として変換されている。まあ8ケタ数値だから当然だけど。
で、その自動追加された「変更された型1」ステップを削除しないまま進んで、「生年月日8ケタ」フィールドを日付の型に変換しようとすると、既存の型変換がありますとかいう鬱陶しいメッセージが出てくる。
「生年月日8ケタ」フィールドは既に数値の型にしたのに、それを日付の型に再度変換しようというのかと、イチイチ確認してくるわけだ。
他のフィールドを型変換しようとした際にもこのメッセージがイチイチ出てきてウザいので、自動型変換のステップは早めに削除しておこうというわけだ。
途中のステップを削除する
先ほど、「姓カナ」「名カナ」の2つの列を削除する操作をしてみたが、それは右端のステップ一覧に「削除された列」として記録されている。
その下にある「変更された型1」ステップが、「生年月日8ケタ」フィールドを日付型に変換したステップだ。
では、「削除された列」ステップをクリックすると左端に表示される☓印を押して、削除してみよう。
一番最後でなく途中のステップを削除しようとしているので、警告メッセージが出る。
その削除したステップ以降に、矛盾が発生するようだとエラーになるからだ。
今回はこのまま進んで良いので「削除」を押そう。
すると、先ほど削除した「姓カナ」「名カナ」の2つの列が復活する。それらの列を削除するというステップを、無かったことにしたからだ。
そして「生年月日8ケタ」フィールドは、日付型のまま残っている。
日付変換した処理は、消さずに残したからだ。
PowerQueryではこのように、ステップというものが逐一記録されていっており、それを途中部分だけ変更するとかいったことができるのが、通常のExcelとの大きな違いだ。
ステップの名前変更
さて、操作ステップの数はまだ少ないが、多くの操作ステップが積み重なると、「変更された型1」とか表示されているステップ名は、いつ何の処理をしたものなのか分からなくなってくる。
このステップの名前は、ある程度処理を進めたら適宜変更するようにしよう。
ステップの名前をクリックし、F2キーを押すことで名称変更できる。
今回は「生年月日の列を日付型に変更」と、名称変更してみる。
Excelにデータを出力する
では、PowerQueryで編集したデータを、Excelシートに出力してみよう。
リボン「ホーム」タブ
→「閉じて読み込む」
とする。
こうすると、Excelシートにデータが出力される。
最初データを開く時に「データの変換」を選ばず「読み込み」メニューから展開される「読み込み先…」を選べば、直接Excelシートに出力はされないけどね。ここは以前の記事を参照。
上図で、右端「クエリと接続」画面に「データ」と書かれているが、これが今回作成した処理手順＝「クエリ」の名前だ。
この「データ」というやつをダブルクリックすれば、PowerQueryのエディター画面に戻り、再び編集できる。
さて、上図ではデータが出力されたシートは「Sheet2」となっているが、このシートを削除してみよう。
すると、右端の「クエリと接続」画面で「接続専用。」という表記がされている。
これは、Excelシートにデータ出力こそされていないが、
ファイルを開く→見出しを正しく認識させる→日付の型を変更する
といった先ほどやった加工手順じたいは、記録に残っているということだ。
データが多い場合、Excelシートに展開したままにしておいては無駄に処理容量を食うだけなので、可能ならこの「接続専用。」状態にしておくようにしよう。
そしてこの「接続専用。」状態は、すぐまたExcelシートに出力できるということでもある。
Excelシートに再度出力したいときは、この「データ」を右クリックし「読み込み先…」を押そう。
そして「テーブル」を選択して「OK」を押す。
こうすれば、再びExcelシートに「テーブル」の形で出力される。
スポンサーリンク
