知らないと損！Excel2019「Power Query」データの整形編

この連載では「Excel2013で止まっている人必見！業務効率がUPするExcel2019活用術」と題し、Excel2019（Office365）の新機能や活用方法を、13回にわたり紹介しています。

連載第10回からご紹介しているのは、Excel2019で進化した便利機能「PowerQuery」の使い方。前回はPower Queryの概要と、データの取込み方法についてご案内しました。今回は、PowerQueryエディターを使ったデータ整形の方法（テーブルの編集やデータの変換）をご紹介します。

Power Queryでこのエディター機能を使いこなせれば、これまで苦労していたデータ整形が、嘘のように素早く処理できてしまうのです。ぜひ覚えて業務効率に役立ててください。

データの取込み・集計に最適！PowerQueryの使い方
（1）概要とデータの取込み（連載第10回）
（2）データの整形（連載第11回） ←今回はココ
（3）複数データの結合（連載第12回）
（4）フォルダ内複数データの結合から集計まで（連載第13回）

月次売上から自店データを確認する｜Power Queryを使わない場合

Power Queryエディターの活用法を解説する前に、Excel2019のPower Query機能を使わない場合の、日々の作業の一例を見てみましょう。

下記の左の表は、複数店舗の売上データです。
例えば東店の社員が、自店のデータだけを金額が高い順に見ようと思ったら、Excel2013以前では右の表のように、「並べ替え」と「フィルター機能」を使うことになります。

ただ、毎日この作業をするのは、手間がかかり大変です。それだけでなく、並べ替えやフィルターを行う項目が多ければ多いほど、間違いが起こる元にもなるでしょう。

しかし、Power Queryエディターを活用すれば、この日々の作業が圧倒的に簡単になるのです。

月次売上から自店データを確認する｜Power Queryを使う場合

では、今度はPowerQueryを使って、東店の月次売上データを降順で表示するまでを確認してみましょう。

前回で解説したとおり、Power Queryエディターにデータを取り込んだあと、ワークシート上にテーブルとして読み込んでみたとします。流れは以下の通りです。

ですがこのままでは、取り込んだテーブルに何も手を入れていないため、元の売上データに変更があれば、都度並べ替えやフィルター処理を行うことになります。これでは、Excel2013以前の処理と何ら違いありません。

そこで、その都度処理をすることを回避するために、Power Queryエディターを使って、東店のフィルター処理と金額の降順で並べ替えを行う「クエリ」を作成します。
クエリ作成後、ワークシートに読み込むと、右下のような、目的通りのテーブルが完成します。

Power Queryエディターの機能を使って、フィルターや並べ替えなどの編集作業を設定登録しておけば、下図のように、日々の入力データに追加や変更があったとしても、「更新」するだけで最新の状態になります。

PowerQueryエディターにより、日々の定型作業の手間を大幅に省くことができるわけです。どうでしょうか。とても画期的だと思いませんか？

売上データから、東店の売上を降順で表示する「クエリ」を作成する

ここらは、PowerQueryエディターで実際に、東店の売上データを降順で表示する「クエリ」を作成していきましょう。

以下は、新規Excelワークシートに、売上データを取り込んでPower Queryエディターを開いた状態です（手順は、第10回「Power Queryでブック（Excelファイル）を取り込もう」の「1つのシートを取り込む場合」①～④までを参照）。

① フィルターを使って「東店」のデータを抽出するために、店舗名の横の下向き三角をクリックして、「東店」のみにチェックを入れましょう。

② 次に金額を降順に並べ替えます。金額の横の下向き三角をクリックし、「降順で並べ替え」をクリックしましょう。

③ このクエリ（抽出整形の手順）に名前を付けます。画面右にある「クエリの設定」で名前を「東店降順」と入力してください。
今後クエリが増えていくことを考えた場合、このようにわかりやすい名前を付けておけば、管理がしやすくなります。

④「閉じて読み込む」をクリックすると、ワークシートにデータが挿入されます。

⑤ 今後データの追加や変更があった場合は、「データ」タブから「すべて更新」をクリックすると、いつでも最新のデータになります。

これは、使わないと損なExcel2019の機能ですよね！ぜひ一度試してみてください。

残る3店舗についても同様のクエリを作成する

先程は、東店の売上データ（降順）をいつでもチェックできるクエリを作成しました。
次は、東店以外の店舗についても、同様に表示するクエリを作成してみましょう。

しかし、これらを1から作るのは結構大変。
楽に作るためには、Power Queryエディターで、「東店降順」のクエリを3個コピーして、設定を変えるだけでOKです。

この方法は、間違いや手間を省くために有効ですので、ぜひ覚えてください。
では、やっていきましょう。

① ワークシートからPower Queryエディターを開くには、画面右側の「クエリと接続」を使います（もしも表示されていなければ「データ」タブで「クエリと接続」を選択すると表示されるようになります）。
「クエリと接続」にある「東店降順」にマウスを当てると、このクエリの情報が開きます。「編集」を選択するとPower Queryエディターが開きます。

② 次に左側のクエリ画面で、「東店降順」の上で右クリックし、「コピー」をしましょう。

③ その場で右クリックし「貼り付け」をすることで、「東店降順(2)」が作られます。

④ 画面の右側の「クエリの設定」で、名前を「東店降順(2)」から「西店昇順」に変更します。

⑤ 店舗名の右側下向き三角ボタンをクリックし、フィルターの選択する中で「西店」を選びたいところですが、出てきません。

⑥ 画面右の「クエリの設定」にある「適用したステップ」を見てください。これは、このクエリ（抽出整形の手順）を設定した手順の履歴です。

適用したステップの下から2番目「フィルターされた行」では、「東店」を抽出する設定が記録されています。一番下は、降順に並べ替える設定です。

それぞれの先頭にあるバツ印をクリックしてステップを削除すれば、フィルターの設定が消えて、他の店舗が表示されるようになります。あとは「東店降順」クエリを作成した時と同様、西店を選んで金額降順で並べ替え、「閉じて読み込む」を実行します。

⑦ ワークシートに「西店昇順」が表示されればOKです。

この要領で、目的に応じたクエリをいくらでも作成することができます。
ワークシートにクエリをあらかじめ作成しておけば、途中でデータが追加されても、「更新」するだけで最新のデータに自動で置き換わるようになるのです。

定期的に売上レポートなどを作成する人は、このようにPower Queryの使い方を覚えておけば、データ抽出・整形の手間が省けます。

文字列を変換するクエリを作成してみよう

通販サイトなどでは、氏名や住所の入力を行うフォームを見かけますね。そこでは、氏名は「姓」や「名」、住所は「都道府県」や「市町村」、「その他」のように、分けて入力を求められることがあります。入力ミスや漏れを極力減らすように考えられているからです。

でもこの方法、入力する側としてはわかりやすいかもしれませんが、データを活用する側にとっては、データが分割されていて使いづらいですよね。

そんなとき、PowerQueryエディター機能を使えば、文字を分割したり結合したりが簡単にできるので便利です。
これは、Excel2013などでは関数を使って整形していたこと。Excel2019では、関数を使わずにより簡単に整形できます。

今回は氏名とメールアドレスのデータを使って、氏名を「姓」と「名」に分割、「ユーザー名」と「ドメイン名」を1つのメールアドレスにする練習をしてみましょう。

結果として、下図のように「姓」と「名」が分けられ、メールアドレスが1つになればOKです。

ワークシート上にある下図のテーブルを使って、データ取り込みから行います。
では、手順を追ってみていきましょう。

文字列を分割する

① セルA1からC7までのどこかをクリックし、「データ」タブで「テーブルまたは範囲から」をクリックします。

② PowerQueryエディターが起動しました。
このデータの場合、氏名の間に全角スペースが入っています。この全角スペースによって前は姓、後ろは名に分けられるのです。

③「氏名」の列の1行目を選択し、「変換」タブの「列の分割」をクリックします。

④「区切り記号による列の分割」にある「区切り記号を…」の下向き三角の中から「カスタム」を選択します。
すると、下に文字を入力する箇所が表示されますので、全角のスペースキーを入力して「OK」をクリックしてください。

⑤ 氏名が分割できたら、列名を変更します。「氏名.1」の部分をダブルクリックして「姓」と入力し、同じように「氏名.2」の部分をダブルクリックして「名」と入力すれば、氏名は完成です。

文字列を結合する

分割されている「姓」と「名」をつなげるには、前述③の工程で、「姓」を選択した状態でCtrlキーを押しながら「名」を選択し「列のマージ」をクリックしましょう。

「列のマージ」にある「区切り記号」を「カスタム」にし、下に全角スペースを入力すれば、結合が完成です。

この要領で、メールアドレスをつなげるときには、「@」でつなげることになります。

変換操作を取り消す

Power Queryエディター画面右側「クエリの設定」の「適用したステップ」は、PowerQueryエディターで行った操作履歴が表示されています。ここのバツ印をクリックすると変換の操作が取り消されるので、元の状態に戻せます。

ここまで、文字列を分割または結合するクエリをご紹介しましたが、これ以外にも、文字列を大文字から小文字、小文字から大文字に変換する機能などもあります。
どんな機能があるのか、「変換」の中にある機能はひととおり見ておくとよいかもしれません。

日付・時刻を変換するクエリを作成してみよう

「2020/1/11:01」のような日付データから「年」のデータを取り出す場合、Excel2013以前も、YEAR関数を使えば処理できました。
しかしPowerQueryを使えば、もっと簡単に処理できます。

年以外にも「月」や「日」、「時」、「分」なども取り出せます。では、さっそくやっていきましょう。

年・月・日・時・分に列を分割する

① 以下のデータを使い、日時列のデータを年、月、日、時、分に分割していきます。
セル「A列」のどこか1ヵ所をアクティブセルにしておいて、「データ」から「テーブルまたは範囲から」をクリックします。

② PowerQueryエディターが開きます。「列の追加」タブの「日付」をクリックし、「年」ならば「年」、「月」ならば「月」というようにどんどん選択しましょう。

③ 時間についても同様に「列の追加」タブ「時刻」をクリックし、「時」、「分」と選択します。
【注】「列の追加」タブの左側にある「変換」タブにも同じ「日付」や「時刻」がありますが、こちらは追加ではなくデータの変換、つまり置き換わることになります。

④ 全て取り出せました。関数を使って取り出すよりも簡単ですね。

年・月・日に分かれた列を結合する

先ほど、姓と名に分かれた氏名を結合したように、年・月・日に分かれた日付も結合することが可能です。

① 先ほどと同様の手順で、Power Queryエディターを開きます。列名の「年」を選択しCtrlキーを押しながら「月」、「日」と選択しましょう。
そして「列の追加」タブにある「列のマージ」をクリックします。

②「列のマージ」画面で、区切り記号は「カスタム」を選択して「/」を指定し、新しい列名には「結合日付」と入力しましょう。

③ 結合された日付の列が作成されました。しかし、少し違和感がありますね。左寄せになっていて、列タイトルの書式アイコンがABC（文字列）になっています。つまり書式が、日付ではなく、文字列として扱われているのです。

④ 「結合日付」列の書式アイコンをクリックし「日付」を選択すると、書式が日付形式に変わります。

日付に関する変換機能としては他に、下図のように、結合日付から当月の最終日や週の開始日を求める機能もあります。
例えば月末を支払期日とするときや、週のスケジュールを立てるときなどに役立つのですが、Excel2013以前では、こうした日付を自動計算するには難易度が高めの関数を使う必要がありました。Power Queryで簡単に求められるのは大変便利ですね。

確認のためパソコンのカレンダーと並べてみました。
2021年2月8日の、月の最終日は28日、週の開始日は7日となっているのがわかりますね。

連載「Excel2013で止まっている人必見！業務効率がUPするExcel2019活用術」、今回は、Power Queryエディターの使い方を中心に紹介しました。

これまでExcel2019を使っているのに、定期的に作成するレポートにおいて、並べ替えやフィルター機能をその都度使っていた方は、今回紹介した一連の流れに沿ってPower Queryを活用してもらえれば、大幅な業務改善になるかと思います。

また、これまでデータの変換・整形に手こずっていた方にとっても、PowerQueryエディターの変換機能は、一瞬のうちにその手間を解消してくれる救世主のような存在だとおわかりいただけたと思います。ぜひ、有効に利用してください。

次回は、Power Queryを使った、表の結合を中心に紹介していきます。
Power Queryには、まだまだ多くの役立つ機能があり、手間が掛かる作業にこそうってつけです。ぜひ次回もお見逃しなく！

これだけマスター！Excel講座