[Excel BI] 集計をするのに扱いづらいデータの直し方（データの取得と変換）

 久しぶりの前回の投稿で「ピボットテーブルが使えない原因のほとんどは元データにあって・・・・」と書きましたが、この使えない原因というのは「データが汚い」と呼ばれる状態であって、Excel に限った話ではありません。

先日 #ohayo_aws で ETL (Extract, Transform, Load) のサービス関連 (AWS Glue Databrew など）で「朝から汚いデータの話をしてはいけない」」みたいな話題になりましたが、データを集計したり、分析する人たちにとっては、汚い元データとの格闘の逸話は数多くあるようです。

Excel の世界でも「データクレンジング」と呼ばれ、昔からこの手の処理はありました。データをきれいにするためにいくつかの関数や機能を駆使してきましたが、今は「データの取得と変換」による機能（または、Power Query エディターの M言語）を使うのが モダンExcel になると思います。さらりとどんなやり方があるか、旧来のExcelの機能を含めておさらいしてみましょう。

Excelの機能 「検索と置換」は Ctrl+H

Ctrl+Hは「検索と置換」ダイアログの「置換」タブを表示させるショートカットキーで、これをつかって、汚いデータの文字または文字列の置き換えをします。ワークシート上に汚いデータがあることが分かっていて、ピンポイントで変換したいときはこれが直感的でかつ一番簡単な方法です。

明示的に全角の文字を半角に置き換え指定したり、一文字だけでなく、文字列を指定して他の文字や文字列にも置き換えることができます。株式会社を㈱にするなども可能です。（それがいいかどうかは別として）

機能として意外に見落としがちなのは、この検索と置換を使って汚いデータの文字・文字列の「削除」もできることです。たとえばハイフン「-」を消したい場合は、検索する文字に「-」を入れて、置換後の文字列に「なにも入れない」状態で置換を行うと文字列から「-」を削除してくれます。

この機能は半角ブランクや全角ブランクも「文字」として扱ってくれます。よって、全角ブランクを半角ブランクにしたい場合は、検索する文字列に全角ブランクを、置換後の文字列に半角ブランクをいれて置き換えることができます。ただし、いずれも何も表示されていないのでちょっと不安になりますよね。

この「検索と置換」機能と同じ動きをする Excel 関数は REPLACE のような気がしますが、SUBSTITUE 関数のほうが期待される同じ動きだと思います。

SUBSTITUE関数

https://support.microsoft.com/ja-jp/office/substitute-%E9%96%A2%E6%95%B0-6434944e-a904-4336-a9b0-1e58df3bc332

ただ、データクレンジングで Excel の関数の出番はあまりないと考えた方がいいです。セルに埋め込む関数を使ってデータをきれいにすると、ネストされた複雑な数式となり、さらに、そのセルにどんな数式/関数が入っているかをチェックする手間を考えると、表形式で多くのデータを持つテーブルを関数と数式で修正する方法はあまり効率的ではありません。

データの取得と変換によるデータクレンジング

Excel のデータの取得と変換により、Web サーバーやクラウドからデータを取得する場合でも集計のためにデータを加工することがあります。あまり想定したくないですが、手によるフリーテキスト入力で発生しがちなケースも含めて、その対応方法を紹介します。

URLの処理　スラッシュ(/)無しあり、半角ブランク

サンプルテーブル1

Webサイトやページの特定をするのに URL の情報を扱うことがありますが、その時にURL文字列の最後にスラッシュ (/) があるケースとないケースがあります。扱うデータとしてあった方が良いか、ない方がよいかを判断して加工する、さらにAPIによる取得ではなく、画面のスクレイピングをして取得する場合などは前後に半角スペースが入ってしまう場合もあり、半角スペースの削除をしたい、そんな場合です。
[2022年追記：このケースの場合のPowerQueryでの最適な方法は Excel のフラッシュフィルに似た機能の「列の追加」の「例からの列」ですが、テキスト処理の考え方として参考にしてください。]

上図サンプルテーブルのような表またはテーブルのURLの部分を加工したい場合、モダン Excel であれば「データの取得と変換」機能を使って加工します。加工したいテーブルにアクティブカーソルがある状態で、リボンの「データ」タブにある「データの取得と変換」から「テーブルまたは範囲から」を選びます。

モダン Excel ではデータソースとしての表は「テーブル」にしてください。そうしなければ、マイクロソフトが今さらに今後提供してくるさまざまな機能が使えない、と断言できます。テーブルについては過去にもこのブログで多く言及していますので、そちらを参考にしてください。

テーブルのすすめ - 集計行　ここから他のテーブル説明投稿のリンク参照できます

Power Query エディターが立ち上がります。ここで加工をします。

なんとなく「変換」タブに該当する機能がありそうで選んでみますが、リボンには多くの機能があって、なかなかそれらしい機能をすぐに見つけられないかもしれません。

Power Query エディター [変換]タブ表示

今回操作（加工）したいのは「URL」の列なので、「URL」列を選択した状態（＝緑色のなっている状態）で、リボンの「テキストの列」にある「書式」にある「トリミング」を選びます。

Power Query エディタ「変換」「テキストの列」「書式」「トリミング」

トリミングの上にカーソルを置くと、ツールチップ（説明）が表示されますが、そこには「選択されている列の各セルから先頭と末尾の空白文字を削除します。」とあります。Excel 関数の TRIM と全く同じではありませんが似たようなスペースの処理をしてくれます。

なお、うれしいことに半角ブランクだけでなく、全角のブランクも削除してくれますし、ブランクが2つ、3つと複数あってもすべて削除してくれます。

ちなみに「クリーン」も不要な文字を消してくれますが、ツールチップには「選択されている列に含まれる印刷不可能な文字を削除します。」とあります。具体的にはTabや改行コードです。SharePoint リストからデータを取得したときに Tab が入っていたことがありました。 Excel のテーブルや表であればAlt+Enterによる「改行コード」が含まれている可能性があります。見た目変わらないのに、テキストとして同じ判定をしてくれない、、という場合はこの「クリーン」を試してみてください。

サフィックス機能だけではスラッシュ対応ができない場合

同じく「書式」には「サフィックスの追加」があるので、これを使って URL の最後に "/" をつけられそうです。サーバーやクラウドサービスのデータを取得するようなケースではデータに揺らぎがない場合が多く、例として勉強会情報の connpass から取得する URL は / が最後につくけど、Doorkeeper から取得する URL には / がつかない、といった具合です。この場合は connpass からの URL データは最後の / を取り除くか、または、Doorkeeper からの URL データには「サフィックスの追加」機能を使って / を追加するかのどちらかで、スラッシュのある、なしを統一させることができます。

困るのが「ゆらぎ」がある場合ですよね。あるデータはスラッシュがついて、あるデータはつかない、という場合です。

 やりかたはいくつかありますが、あまりコードを書かずに、Power Queryの機能、ダイアログによる設定だけでやる方法を紹介します。

[列の追加] タブの [カスタム列の追加] と [重複する列]

Power Query エディターはリボンから機能を選択していき、一連の処理を行うことができます。この「一連の処理」は Power Query エディター画面右にある [クエリの設定] ウィンドウの [適用したステップ] に記録されていきます。これは裏側ではM言語というプログラミング言語ですべて記述されています。[ホーム] タブの [クエリ] - [詳細エディター] で確認することができます。

Power Query 詳細エディター

繰り返しになりますが、この詳細エディターは Power Query のM言語で記述されているので、そのお作法に則れば直接、自由に処理を書くことができます。ただ、自分はこの Power Query の詳細エディターを使った記述はあまり使わず、おすすめしないことにしています。というのも、エディターの機能としてはあまりに貧弱すぎるのと、たぶん設計思想としては直接M言語によってフローや機能を書かずに、ダイアログで操作を記録・記述することを優先しているように感じるからです。

よって、最低限のコード記述で、詳細エディターを使わず、リボンからの機能選択の方法で「最後にスラッシュがなければスラッシュを追加する」という処理を追加します。

1) 加工用の列のコピーを作る　（重複する列）

2) コピーの列を使って「最後の1文字」を抜き出す

3) カスタム列の追加で、新たに列を追加する

4) 追加列の処理を IF 文を使って最後の文字判定がスラッシュでなければ URL にスラッシュを追加すると設定する

以上が流れになります。

1) 加工用の列のコピーを作る　（重複する列）

ブランクの削除のトリムや、制御コード削除のクリーンは、オリジナルの列を指定して機能を適用しましたが、最後の1文字を抜き出す、などでオリジナルの列はそのままにしたい場合は「列の追加」タブの全般の「重複する列」を使います。最終的にこの列は削除してしまうので列名にこだわりはいらないですが、以下ではわかりやすくするために列の名前を入れています。

重複する列を使って列のコピーを行う

2) コピーの列を使って「最後の1文字」を抜き出す

判定列を選択している状態で、「変換」タブの「テキストから」の「抽出」にある「最後の文字」を適用し、文字数カウントで1を設定して、最後の1文字を抜き出します。

抽出の最後の文字を使って1文字抜き出す

3) カスタム列の追加で、新たに列を追加する

カスタム列の追加を工夫することで、詳細エディターをできるだけ使わずに処理をすることが可能になると思います。Excel でいうところのセルの数式を作成して、追加する列のすべての行（セル）に適用するイメージです。この数式で他の列や、あらかじめ作成しておいたカスタム関数などを再利用することができます。この操作は 以下の 4) にまとめます。

4) 追加列の処理を IF 文を使って最後の文字判定がスラッシュでなければ URL にスラッシュを追加すると設定する

今回は、判定列により抜き出したテキストがスラッシュでなければスラッシュを追加し、スラッシュであればURLそのまま、という処理にします。

カスタム列を追加して if then else 式を設定

なお、カスタム列の式を入れるボックスも関数の入力補助機能などがあるのでいろいろ試してみるのが良いでしょう。if then else でのエラーでよく出て最初なにやらさっぱりわからないのが、「トークン Eof が必要です。」です。これは If などで大文字（Upper Case）を使っている時にでます。記述はすべて小文字（Lower Case）にすれば大丈夫です。

最後に「判定列」を削除し、オリジナルを削除し、カスタム列の列名を「URL」にして保存します。

後処理

Power Query エディター、データの取得と変換機能を使わなくても、従来通りのTRIM関数やIF関数とMID関数を使って数式をセルに埋め込んだり、VBAを使うと同じ処理はできます。そちらのほうが早い！という方は無理に覚えなくでもいいでしょう、、、と言いたいところですが、ぜひがんばって Power Query を習得してください。よく知っている人ほど、この Power Query を中心とした モダン Excel のすごさを体感できると信じています。

自分の学びとしては、カスタム列、カスタム関数、今回は紹介していませんが条件列を工夫することで、詳細エディターで M言語を直接書きたくなる衝動を抑えることだと思います。マクロ記録のように操作を記録させるほうが多くの人に受け入れらやすいのは確かだと思います。（ただし、VSCodeが詳細エディターにとって変わる日がきたらその限りではないでしょう！）

最後に、これらの知識は　2016年くらいからあまり変わっていません。業務でも Power Query を使っていますので、機能追加などがあればチェックしていますが、チェックしきれていない可能性もあります。もし、新機能が出て、もっと簡単に同様のことができるようであれば、是非共有してください！

この記事がなんらかの参考になれば幸いです。

[追記] 2021.07.11

モダンじゃないExcelでできて、モダンなExcel (Power Query) で簡単にできなくて工夫せざるを得なかったことがあります。ワークシート関数の ASC と JIS に相当する機能やM言語関数が無いため、半角文字を全角に(JIS関数）、全角文字を半角に(ASC関数)するためにカスタム関数を作りました。VBAのようにPower Queryからワークシート関数の呼び出しができればいいのですが、それは私の知っている範囲だとできません。
とはいえ、0から9までの数字、アからンのカタカナ、aからz、AからZ、括弧( やはてな? やエクスクライメーション（びっくりまーく）!などの一部の記号の置き換えを書けばいいのです。これに対応する関数が出てこない原因はマイクロソフトへの日本のマーケットの影響が小さくなったとかネットで言われますが、それよりも、そもそもこのような全角、半角混在の入力ができることが問題で、直すのであれば、そちらを直すべきなんだろうなぁ、と思います。