EXCEL：データ取り込み時に前処理を済ませる

▶ EXCELで定型処理を自動化したいなら以下の記事を参考にしてください。

EXCELで処理を自動化する方法を基本から丁寧に解説します。毎月データを取得して、毎月同じ集計をするのであれば、自動化することで時間の節約するだけでなく、ミスを防ぐこともできます。設定は簡単なので、この記事を読めば誰でも『自動化』することができます。

はじめに
サンプルデータ
やりたいこと
前処理
1. 区切り記号の間のテキストを抽出する
2. データを縦持ちにする
まとめ

はじめに

今回も、前回に引き続いてEXCELを扱います。Pythonを使わなくてもEXCELでもこんなことができるよ、というのをお見せしたいと思います。Pythonで括弧の中身を抽出する処理をしたことがありましたが、これをEXCELで処理してみることにしましょう。

Pythonでの処理は↓こちらを参照ください。

Python初心者向け：文字列から括弧で囲まれた部分を抽出する

Python初心者でもわかるように、実際に正規表現を使って文字列の抽出をおこないます。これは実際の仕事の場で必要となった作業で、「」や『』で囲まれた部分をデータとして抽出するしょりとなります。このようなときに、正規表現が使えると簡単に処理をすることができます。

サンプルデータ

Pythonで正規表現を使って、括弧で囲まれた部分を抽出したときに使った、データをつかうことにしましょう。次のようなどうぶつたちに図書室に関するアンケートをしたときのデータです。

やりたいこと

アンケートの項目は自由記述ですが、ここに回答者が『』や「」で本の名前を記載しています。普通の括弧だったり、二重括弧だったり、それらが複数あったり、混在している場合もあります。このようなデータから括弧の中身を取り出して、アンケートに記載された本について整理したい。

前処理

では、早速やっていきましょう。まずはデータの読み込みですが、これは前回、ご紹介したように「データ」タブからおこなうのでしたね。「データの取得と変換」リボンから「テキストまたはCSVから」に進み、ファイルを指定します。すると、次のようにプレビューを確認できます。

文字化けしているようです。文字コードが「日本語(シフトJIS)」が指定されているので、ここを「Unicode(UTF-8)」に変えてみましょう。

すると、次のように文字化けせずにプレビューが表示できました。

この状態で、このダイアログの下部にある「データの変換」をクリックしましょう。すると、Power Queryが立ち上がります。

データが表示されていますが、1行目にヘッダ行が表示されていて、データとして扱われています。これを修正しておきましょう。「ホーム」メニューの「変換」リボンから「1行目をヘッダーとして使用」をクリックします。

すると、次のように1行目がヘッダとして認識されるようになります。

区切り記号の間のテキストを抽出する

データの準備ができました。では、前処理をしていきましょう。やりたいのは、「アンケート」の列にある括弧「」や二重括弧『』で囲まれた書籍名の抽出です。以前、Pythonでは正規表現を利用して抽出しましたが、PowerQeuryを使うともっと簡単にできます。

抽出したい列を選択した状態で、「列の追加」メニューの「テキストの列」リボンの「抽出」から「区切り記号の間のテキスト」を選択してください。

すると、次のようなダイアログが表示されます。ここで、区切り記号の設定をしていきます。今回、「」で囲まれたものと『』で囲まれたものがありました。順にやっていきましょう。

まずは次のように「開始区切り記号」と「終了区切り記号」にそれぞれ「と」を設定すればよいでしょう。次のように設定して「OK) をクリックします。すると、次のように、アンケート列の各行にある「と」で囲まれた書籍名が新しい列に抽出されます。

よくみるとアンケート列の4行目のデータには「と」で囲まれる書籍名が4つもあります。そのため、「区切り記号の間のテキスト」による抽出を4回繰り返しておくことにしましょう。

今度は「詳細設定オプション」のセクションを開いて、上記のように設定します。１つ目のデータは抽出済みなので、「スキップする開始区切り記号の数」のところで「1」を指定します。すると、「の2つ目を捉えてくれます。

同様の要領で「と」で囲まれたもの（最大4）と『と』で囲まれたもの（最大2）の処理をおこなうと、次のようになります。

書籍名がちゃんと取り出されていますね。ここでは、列名を「書籍1」から「書籍6」に変更しています。ただ、このままでは扱いずらいですね。もうちょっと処理を続けましょう。

データを縦持ちにする

次に横に並んだ書籍名のデータを縦に並べることを考えましょう。Power Queryならこの処理も簡単にできます。まずは、縦に並べたい列「書籍名1」から「書籍名6」をすべて選択します。

この状態で右クリックをして、「選択した列のみピボット解除」を選択します。

すると、次のように「書籍1」～「書籍6」が属性という列であらわされ、「値」という列に実際の書籍名が格納されます。

今回、この「属性」の情報は不要なので削除していまいましょう。これは簡単ですね。「属性」列で右クリックして「削除」をクリックします。

すると、次のようになります。

「値」が入っていない行は不要ですから、これを削除してしまいましょう。これは、「値」列の右端にある▼をクリックして表示されるメニューから「空の削除」を選択してください。

すると、次のようになります。

ここで、「値」の列名は「書籍名」にしておいた方がわかりやすそうですね。

これで前処理の設定は完成です。「ホーム」メニューの「閉じて読み込む」をクリックしてください。

EXCELにテーブルとして、前処理済みのデータが読み込まれました。

まとめ

いかがでしたか？場合によってはPythonよりもEXCELで処理したほうが簡単なケースもあるかもしれません。いろんなツールでいろんな処理を体験しておくとよいですね。PowerQueryによる処理は再現性があるので、データが差し変わっても同じ処理を適用することができます。月次の集計などもこのような形で提携処理を定義しておくと便利でしょう。

▶ さらにEXCELでの前処理の実施例をみるなら、以下の投稿をご覧ください。