EXCEL：PowerQueryでデータの一部を取り出したり、時差の補正をおこなう

はじめに

今回はPowerQueryでの前処理を扱います。このブログでは以前扱いましたが、扱うデータによって微妙に処理が異なり悩んでしまうこともあると思います。いろんなパターンをみておくことで、「この場合はこうすればできそうだ」のようにアイデアが浮かぶようになります。今回は、フィールドにセットされたデータから規則に従ってデータを抽出する方法をみていきましょう。一度、設定してしまえば二回目以降はデータが更新されても自動で処理できるところまで設定しましょう。

サンプルデータ

今回は本部組織に在籍していて、各支店のからお客様にむけて送られるメールの配信結果を集計して分析する、という想定としましょう。分析するにあたり、まずはデータを見やすい形に加工する必要があります。この前処理の部分を扱います。データは次のようなデータです。

配信日時が海外サーバーの時間になっていて、さらに、各メール配信結果はぱっと見ではどの支店のものかわかりません。これを解決していきましょう。

やりたいこと

配信日時を日本時間にする
どの支店の結果かわかるようにする

配信日時を日本時間にする

Webサービスなどを使っていると、サーバーが海外にあって、現地時間で表示されていることがありますね。サマータイムを導入している国であると、一定の時差ではないため苦労します。サマータイムは1時間のズレなので気にならない場合もあるかもしれませんが、たとえばメールの配信時間などは、送る時間帯による開封率の分析などをする場合には、正確な時間が必要となります。

どの支店の結果かわかるようにする

サンプルデータを眺めていると、アセット名をよくみると支店名の記述がありますね。但し、カラムとして値を持っているわけではありません。なんとか、アセット名から「支店名」を抜き出す方法を考えましょう。こうすることで、支店ごとに集計して傾向をみる、などをおこなうことができます。

PowerQueryによる処理

データの読み込み

まずはデータを読み込むところから見ていきましょう。EXCELにデータを読み込むには「データ」メニューの「データの取得と変換」リボンの「テキストまたはcsvから」を選択します。

すると次のような画面が開きます。特に文字化けもしていませんし、特に設定をいじる必要はなさそうです。画面の下の方にある「データの変換」をクリックしてください。

すると、PowerQueryエディターが起動します。まずは、画面右の領域の「クエリの設定」のプロパティで名前を設定しましょう。ここでは、「配信結果レポート」とすることにします。

次に各フィールドの型をみていきましょう。「配信日時」は「日付/時刻」、「アセット名」は「テキスト」など、各フィールドのデータ型が正しいかを確認します。ここまでで、データを取り込む基本的な準備はできました。ただ、今回は取り込む前の前処理として、「配信日時を日本時間にする」と「どの支店の結果かわかるようにする」をしたいのでした。次から、これらの処理のやり方を見ていきましょう。

配信時間を日本時間にする

海外との時差は、サマータイムも考慮に入れる必要があります。今回のデータは、EDT東部夏時間としましょう。日本との時差は冬時間で14時間、夏時間で13時間となります。夏時間・冬時間の切り替えは、

サマータイムの開始：2020年3月8日 2:00 am → 3:00 am
サマータイムの終了：2020年11月1日 2:00 am → 1:00 am

となります。つまり、2020/3/8 2:00～2020/11/1 2:00 の間は13時間の時差、2020/11/1 2:00以降は14時間の時差となります。切り替えのタイミングとなる2:00 amの扱いは微妙ですが、基本的に日曜日の深夜の切り替えとなっているので、メール配信時間を知るにあたっては特に影響しません。

まずは「配信日時」ごとに「時差」を計算して、次に「配信日時」に対して「時差」を考慮することで日本時間を計算することにします。はじめに「配信日時」のフィールドを選択した状態にしてください。

次に「ファイル」メニューの「全般」リボンから「条件列」を選択してください。

すると、「条件列の追加」画面が表示されます。新しい列名を「時差」としましょう。列名の「条件」のプルダウンメニューを表示させて、「配信日時」を選択してください。この配信日時ごとに時差の値を埋めていきます。

先ほど整理したように、2020/3/8 2:00～2020/11/1 2:00 の間は13時間の時差、2020/11/1 2:00以降は14時間の時差でした。なので、次のように設定します。

これで時差の計算ができました。では、「配信日時（日本時間）」という新しいフィールドを作っていきましょう。「列の追加」メニューの「全般」リボンから「カスタム列」を選択してください。

ここで新しい列名に「配信日時（日本時間）」をセットします。次に、「カスタム列の式」の部分を記述していきます。

右側に「使用できる列」があるので、「配信日時」を選択してください。この「配信日時」に対して「時差」を加えればOKです。時差を加えるときには、#duration(日,時,分,秒)を使います。ここでは、日や分、秒の調整はしないので0として、時の部分は先ほど計算した「時差」とすればよいです。次のようにします。

これで日本時間が計算できました。表示上も日本の日中の時間になっていますね。「配信日時（日本時間）」のフィールドの型をみてみると、「日付/時刻」の型になっていませんね。これを修正しておきましょう。

「配信日時（日本時間）」フィールドの左の「ABC(123)」と書かれているところをクリックすると、プルダウンメニューが表示されます。

ここで「日付/時刻」を選択してください。これでにサマータームを考慮して日本時間への変換ができました。確認してみましょう。列はドラッグ＆ドロップで並び替えができるので、「配信日時」の横に「時差」「配信日時（日本時間）」を持ってくることにしましょう。

1行目は4/6のデータだから夏時間で時差は13時間。日本時間は13時間進むので4/6 23:00は日付が変わって4/7 12:00となって合ってますね。同じように2～8行目までは夏時間なので時差13時間となっています。一方、9行目は11/21のデータなので冬時間で時差は14時間。日本時間は14時間進むので11/21 23:00は日付が変わって11/22 13:00となります。こちらもあってますね。同様に10-11行目も14時間の時差となっています。

どの支店の結果かわかるようにする

この辺はデータによってまちまちな処理になるので、よく観察するところから始めるとよいと思います。よくみると、アセット名には命名ルールがあるようです。＜エリア〇＞のあとに「_支店名_」となっているようにみえます。これだと、「_」という「区切り記号の間のテキスト」で抽出できそうです。但し、「箱根支店」や「藤沢支店」は支店名の前に「_」が入っていません。これでは、「区切り記号の間のテキスト」では抽出できません。

そこで、支店名を、「_」で区切ることにしましょう。「＞」のあとに「_」を入れてしまうと、もともと「＞_」となっていたデータは「＞__」となってしまいます。そこで、一旦「＞_」があったら「＞」に変換します。そのあとに「＞」を「＞_」に変換することにします。

「＞_」を「＞」に変換します。こうすることで、「＞支店名_」となります。

さらに「＞」を「＞_」に変換しましょう。

すると、支店名が「_」で囲まれているはずです。

実は、この操作をしなくても「開始区切り記号」を「＞」として、「終了区切り記号」を「_」とすることで抽出することもできます。ここでは、わかりやすくするために開始と終了の区切り記号をそろえています。

では、ここからは「支店名」を抽出していきましょう。いま、「支店名」は「_」で囲まれているので、抽出するのは簡単です。「アセット名」を選んだ状態にしてください。

「列の追加」メニューの「テキスト」リボンの中から「抽出」のプルダウンメニューを表示させてください。

そこに「区切り記号の間のテキスト」というメニューがあるので、これを選びます。

表示された画面で「開始区切り記号」「終了区切り記号」に「_」をせっとしてOKをクリックしてください。

すると、以下のように「支店名」を抽出することができました。フィールド名がわかりにくいので、「支店名」に書き換えておきましょう。フィールド名をダブルクリックすることで編集できます。

よくデータを見てみると、「●●支店」のような不要なデータが紛れ込んでいます。アセットのテンプレートを使ったテストデータが紛れ込んでいるようです。PowerQueryではこのような不要なデータのフィルタリングも簡単にできます。フィールド名の横にある「▼」をクリックしてプルダウンメニューを表示させてください。

すると、フィルターを設定する画面が表示されるので、不要なデータ（ここでは「●●支店」）のチェックを外しましょう。