2022/04/24

[Power Query] OneDrive パーソナル (onederive.live.com) の Excel ブックに接続する方法

Power BI は OneDrive for Business (中身は SharePoint で、URL に my.sharepoint.com が含まれるもの) のフォルダへの接続を前提にしているようで、サービス名が同じでややこしいのですが、個人が使う OneDrive (URL に onedrive.live.com が含まれるもの)への接続は「簡単には」許していないように思えます。それはもう7年も前のことで、以下のような投稿をしています。このときは OneDrive パーソナル(と便宜上呼びます)への接続方法を見つけることができませんでした。(2015年当時)

Power Query から https 経由で SharePoint の Excel ブックを開く

Googleスプレッドシート(ベータ)に接続する

最近 Power BI ユーザーさんのブログで Google スプレッドシートがベータとはいえ、Power BI から取り込み可能になった、と知りました。[データを取得] から「Google」キーワードで検索すると、ベータですが Google スプレッドシートが表示されるようになりました。

Googleスレッドシートを表示している URL を使い、Google アカウントでサインインして Power BI に取り込めました。また、Power BI Service にアップロードして、資格情報(Google アカウントによるサインインと、プライバシーレベルを Organizational)の再設定を行うことで、[スケジュールされている更新] が完了することを確認しました。

コネクタのリファレンス Googleシート(ベータ)
https://docs.microsoft.com/ja-jp/power-query/connectors/googlesheets

OneDrive パーソナルにある Excel ブックに接続したい

Google スプレッドシート同様に [データを取得] で OneDrive といれても、それらしい、わかりやすいサービスは列挙されません。[OneDrive 内の Excelブック] があればベストなのですが、残念ながら、そういう用途は SharePoint という前提なのでしょう。

データ分析的なフィロソフィーとしては、手入力の Excel をデータソースとしたくないのはわかります。それをするなら Power Apps でデータを収集すべきで、生スプレッドシートをいじらせるのは「綺麗なデータを用意する」という観点からもあまりお勧めできないからでしょう。そう、目指すは以下のような世界。

Power Apps、Power Automate、および Power BI を SharePoint Online と統合する

しかし、そのデメリットを理解した上でも、Google スプレッドシートが OK ならば、簡単に利用できる OneDrive パーソナルに保存した Excel ブックの Power BI への取り込みは世界中の Excel ユーザーと Power BI ユーザーが感じているはずで、2022年にもなれば何らかの解決方法があるのではないか?と思い、少しだけしつこく探して、、、見つけました! 2017年の投稿で、たしかにこの Embed の機能が2016年前後に追加されたのを覚えています。(訂正: Excel Web App で 2011年には存在していました)

PowerBI support for OneDrive Personal as data source
https://community.powerbi.com/t5/Power-Query/PowerBI-support-for-OneDrive-Personal-as-data-source/td-p/129125

実際に試して、上述の Google スプレッドシートの手順同様に Power BI Servcie にアップロードして、[スケジュールされている更新] もうまくいきました。その手順をあらためて紹介します。

1) OneDrive の Excel ブックで右クリックメニューで [埋め込み] を選択します

2) この操作がはじめての場合は URL生成のメッセージが右ペインに表示されるので [生成] をクリックします。[生成] ボタンの下に書いてあるように、これによりサインインせずにこのファイルを埋め込んだブログやWebページで表示できるようになります。セキュリティに注意してください。

3) プレビューと iframe コードが表示されるので、この iframe のコード内の、resid= から &em=数 まで (width の前) をコピーします。

4) Power BI に Web ソースとして入力する URL を作ります。以下の文字列に、上記 3) でコピーした文字列を追加し、&app=Excel で閉じます。

https://onedrive.live.com/download? <文字列コピー> &app=Excel

上記の例だと以下のようになります。

https://onedrive.live.com/download?resid=6E1B8001F99D3597%2179199&authkey=AACEgb0W3bmJHXs&em=2&app=Excel

5) Power Query で Web からデータをインポートを選び、URLをコピーします。[基本] で構いません。[OK] をクリックします。


6) ワークシートにテーブル オブジェクトが含まれていればテーブル名が表示され、SharePoint Online (OneDrive for Business) と同じようなナビゲーションが表示されます。


7) この pbix を Power BI  Servcie にアップロードし、データセットの [データ ソースの資格情報] のプラバシーレベルで [Public] を指定。(これは Embed コードは「一般公開」の設定のため - このプライバシーレベルは仕事で使う場合は常に注意ですね)


8) 資格情報を設定することで [スケジュールされている更新] の設定が可能になる。


以上となります。

この設定は Excel の取得と変換の [Web から] でも利用できます。


足かけ7年のモヤモヤが解消されました。

ただし、繰り返しますが、セキュリティとしては匿名アクセス可能な一般公開の URL を生成しています。一般公開されているデータ、特に公になっても問題の無いデータに限られ、不安であれば、SharePoint Online/OneDrive for Business で組織アカウント サインインの元でのデータ活用に切り替えたほうが良いと思います。ベータの Google スプレッドシートが認証を通しているのも納得できます。Goolge スプレッドシートも有力な選択肢ですね。

既知かもしれませんが、参考になれば幸いです。

2022/04/19

[Power BI] カスタム関数をつかったQueryを含むデータセットが自動更新できない

(追記: Excel のブックをそのまま Power BI Service にあげて使用するのではなく、Power Query + Excel Pivot Table レポートを Power BI Desktop でレポートを書き換えています。Power Query クエリの多くをそのまま移行できるのは、モダンExcel + Power BI の組み合わせの使い勝手の良いところです)

データを元にしたレポートやダッシュボードを複数のユーザーと定期的に共有することは、Excel で工夫・時間をかけて実現できますが、Power BI Service を含む Power BI は、その目的のためにつくられたこともあり、Excel での苦労や時間を軽減してくれます。

特に、モダンExcel な使い方をしているユーザーにとって魅力に見えるのは、レポートのデータ更新の自動化 につきるかと思います。

以前紹介した投稿 [Excel] Excel で JSON データを読み込む で利用してる connpass を使ったレポートなども、個人でデータを確認する分には Excel は柔軟で使いやすいのですが、他の人と、定期的に共有するとなった場合の共有方法については非常に悩みました。Excel ユーザーとしては OneDrive の活用が理想だったと思いますが、OneDrive と PowerQuery (取得と変換)の組み合わせはあまり良くありません。OneDrive といえども他社のファイルサーバー/サービス同様にxlsxのファイル保存先としてしか使えず、メールやメッセンジャーなどで保存先のパスを共有しながら、画像を貼り付けるような使い方になってしまいます。

もし、クラウドに保存している xlsx の「データ更新」ボタンが定期的、自動的に押され、そこにあるデータを表示できたら、、、

これは Pivot Table レポートや Power Query を活用し、複数の人とそれらを共有しなければならないデータエンジニアがいつも思うことです。これを可能にするのが、Power BI Service のダッシュボードやレポートで、データセットの設定にある「スケジュールされている更新」です。


この設定を行い、レポートおよびダッシュボードを共有することで、自動更新された最新データの表示が可能になります。以下は17日、18日の設定時に発生したエラーを含む対応がうまくいき、データセットが自動更新された履歴です。成功すると [状態] が完了になります。


解決しなければならなかった課題は複数あったのですが、今回、関連する日本語の情報を見つけることがなかなかできなかったので、そのうちのひとつを今回ご紹介したいと思います。残りもおって紹介したいと思います。

エラーメッセージ

データセットの[更新スケジュールの設定] で表示されたエラーの内容は「現在、以下のデータソースが更新をサポートしていないため、このデータセットの更新をスケジュールできません、、、」「・Query1 のデータソース」でした。


Query1 は具体的なクエリの名前を指しているわけではありません。
さらに具体的な Query に含まれるエラーの原因を探します。上の図の青い文字のリンク「データセットの表示」をクリックすると、エラーが発生しているデータセットの更新の状況を確認することができ、[最新の情報に更新済み] に更新しようとした日時が表示され、その横に注意マークがあるのでそれをクリックしてみました。するとどうやらデータセットに含まれる「動的データソース」があるために更新されない、と明示的に書かれていました。


このデータセットには、動的データソースが含まれています。動的データソースは Power BI サービスでは更新されないため、このデータセットは更新されません。詳細情報: https://aka.ms/dyanmic-data-sources。のメッセージ画面

このダイアログで紹介している https://aka.ms/dynamic-data-sources のページを開くと、実は非常に詳しく Power BI におけるデータ更新について書かれているのですが、これを全部読み込んで理解するのはなかなかつらいものがあります。以下、このページの日本語の URL です。

Power BI でのデータの更新
https://docs.microsoft.com/ja-jp/power-bi/connect-data/refresh-data

今回のケースにおける問題となった動的データソースは、connpass から REST API を使い JSON データを取りこむ際につかった start パラメーターが原因でした。どうしてこれが原因であるかがわかったのは後述する YouTube 動画のおかげです。

    ソース = Json.Document(Web.Contents("https://connpass.com/api/v1/event/?keyword=JAWS&count=100&start=" & p1)),
しかし、この start パラメーターによるページングの機能を使って、対象とするデータを Power BI に取りこむ(import) ことをしなければ集計ができません。この時点で日本語の情報が少なかったので、Power BI の UI を英語に切り替えて、英語でエラーメッセージやダイアログのメッセージを拾って検索することにしました。

ヘルプは上級者も含むユーザーを対象としているため、解決につながる直接的なイメージをその段階では思い浮かべることができなかったかもしれませんが、今思うとこの段階で Web.Contents のヘルプを読み込むべきでした。以下のヘルプの 例1 が解決方法だったのです。

Web.Contents
https://docs.microsoft.com/ja-jp/powerquery-m/web-contents 

解決方法は動的データソース表記を変更する

英語のキーワードのよる検索の試行錯誤の結果、以下の YouTube の解説ビデオを見つけることができました。

Making sure your Power BI web data source refreshes online (RelativePath, Query, BrowserContents)

上記の YouTube をみて、それから Web.Contents のヘルプをみると ? をつかったパラメーターの表記は NG で、ダイナミックURLを受け付けない静的ベース URL に対して、RelativePath や Query プロパティをつかった方法を使えばいいことがわかります。

上述のソースは以下のような表記方法に変わりました。
    ソース = Json.Document(Web.Contents("https://connpass.com/api/v1/event/?keyword=JAWS&count=100",
        [Query=[start=p1]])),

複数のパラメータの表記方法などは Web.Contens のヘルプに書かれています。
これで、パラメーターの変数を変えたいために動的URLを使うカスタム関数を作成し、その関数がクエリに含まれるためにスケジュールした更新が失敗する、といったことを回避することができます。

以上、参考になれば幸いです。

2022/02/05

[Excel BI/Power Query] そのフィルター大丈夫ですか?そのピボットテーブル レポート本当に正しいですか?

かなり前に書きかけていた記事を追記・完成させて公開します。

Excel は大量のデータから欲しいデータを絞り込み、集計をするのは得意とするところですし、それを実現するために、SUMIFS 関数などの「数式・関数」を使ったり、オートフィルターやテーブル機能などの「機能」を使ったり、VBA を使ったプログラミングで実現するなど、さまざまな方法を使うことができます。それらの中でも「ピボットテーブル」は本当によく使います。

今回データを「絞り込む」ということに関して、以前、私自身がはまって、その理由が小一時間わからず、あらためて Excel の機能の特性を勉強させられたので、共有したいと思います。特に、ピボットテーブルと取得と変換 (Power Query) のクエリエディタを組み合わせて使っている方は、データの絞り込みの仕組みの違いを意識していないと、以下のような思わぬところで落とし穴にはまるかもしれません。

データソースからデータを絞り込む方法 - ピボットテーブル

実際の元データは4年分の勉強会開催のデータでしたが、ここでは簡単なサンプルを使って説明します。ピボットテーブルの元データとなるデータソースは外部の Web サーバー上から取り出すテーブルですが、Excel 上にサンプルのテーブルを作り、それを元データとするピボットテーブルを作成します。
テーブルからピボットテーブルを作成する
このケースであれば、全部で9件分のデータをピボットテーブルでは「タイプ」のAからFにまとめて集計しています。元のデータソースは「テーブル」で作成しているので、11行目に新しいレコードが追加されても、ピポットテーブルは「更新」するだけで、元データソースの範囲を再指定することなく、追加された新しいレコードを含めた集計結果をピボットテーブルで返します。(これも知らない人が多いので注意です!)
元のデータソースにレコードを追加して、ピボットテーブルでレポートを更新する
いつもやっている、見慣れた操作とその結果です。
このピボットテーブルレポ―トから、タイプの A と B の集計の必要がない場合は、ピボットテーブルの「フィルター機能」を使うと思います。ピボットテーブルで絞込みをするための方法は2つの方法があって、1つは行ラベルにある矢印ボタンからフィルターを設定する方法、もう1つはピボットテーブルのフィールドリストにある「フィルター」を使って絞り込む方法です。ここでは行ラベルのボタンを使ってフィルターをかけてみます。
行ラベルのボタンでフィルターを設定する
何も問題ありませんよね。
「このレポートを作成・提出して終わり」といった Excel の使い方であれば、ピボットテーブルを使って作成するレポートはここで終わりだったと思います。

自分のケースの場合は、上記のサンプルのように Excel のテーブルが元データソースではなく、WebサービスからAPIで取得するものでした。その為、データを取得し、絞り込むところは取得と変換(旧Power Query)のクエリエディタで行っていました。結果、元のデータソースに日々データが追加されるような場合でも、不要なデータ(上記の例ではタイプAとB)を「Power Query エディタ-」で除外し、取得と変換の「更新」ボタンで最新データを取りこんで、新規に追加されたデータをピボットテーブル レポートに表示していました。

さて、サンプルに戻り、上記のケースと同様の状況を作ってみます。データソースのテーブルに新しいデータを追加して、ピボットテーブルを更新してみます。ピボットテーブルではAとBを除くフィルターが設定されている状況です。
データを更新、追加してピボットテーブルを更新する
タイプGの30、Bの30、Hの100を追加します。ピボットテーブルを更新することで、Bはフィルターでチェックを外しているので、集計対象になりません。Gは40から70に更新され、総計も110から140になりました。
ところが、Hのレコードはピボットテーブル レポートに反映されません。

この絞込みのフィルターの条件は「AとBをはずす」ではなく、絞り込むときに存在していたA,B,C,D,E,F,Gからチェックが付いている「C,D,E,F,Gだけを選ぶ」なのです。よって、新規にデータソースに追加されたHはピボットテーブル レポートに反映されないのです。

私がはまったのはまさにこのケースで、「ちょっと楽してピボットテーブル レポートの列フィルタで絞り込み」した結果、除外ではなく、そこに表示されているものだけを選択する、になってしまったため、その後でデータ/ レポートの更新を行っても、追加された新しいタイプのレコードはピボットテーブルに反映されない状態になったのでした。

クエリエディターの絞込みは「除外」(注:基本は・・・です)

ややこしいのは、この Excel の絞り込みの操作と、クエリ エディターでの操作がほぼほぼ見た目が同じということです。クエリエディタで行のフィルターをかける操作は、ピボットテーブルと同じような操作で、列名にあるボダンを使って指定できます。
クエリエディタ―の列名ボタンからフィルターを設定する
繰り返しになりますが、ピボットテーブルのフィルター設定と同じ操作でも、上記の場合、その意味は違っていて、■(すべて選択)が押されている状態から A, B を外した場合は、まさに「AとBを外す」という条件になります。詳細エディターで確認すると、以下のようなM言語による式が記録されています。

フィルターされた行 = Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B"))

上記の式では、Table.SelectRows に それぞれの行の[タイプ]がAではなく、かつ、Bでもない、という条件を引数として与えています。<>は「等しくない」を表します。

よって、全件が入っている元データから「取得と変換」のクエリ エディターを使って行の絞り込みを行い、その絞り込んだ結果を使ってピボットテーブルを作る方法は、レポートに新しいタイプの新規の行がきちんと追加されているので、問題ありませんでした。この使い方の多くは、レポートに不適切なレコードが入ってきたら、クエリエディターのフィルターオプションでそのタイプを除外するように設定して、必要なデータのみレポートで利用していました。

トラブルのケースでは、ついうっかりピボットテーブル側で行の絞り込み条件を設定しそのままにしてしまい、たまたま元のテーブルに新しいタイプのレコードが追加されたことを「知っていた」ので、ピボットテーブル レポートが正しくないことに気が付きましたが、これ、実際のデータは数万件で、毎月追加される件数もかなりの数ですから、気が付かないことのほうが多いかもしれませんね。

しかし、このトラブル、これで終わりではなかったのです。

クエリ エディターの絞り込みは必ず「除外」ではなく選択する数による

ところが、別件でまたピボットテーブル レポートに新しいデータが追加されないケースに遭遇しました。今度はピボットテーブルの列フィルタは使っていません。そこでクエリ エディターを見ると、なんと除外ではなく、Excel のピボットテーブルのフィルタ同様に「選択」になっていました。


= Table.SelectRows(変更された型, each ([タイプ] = "C" or [タイプ] = "D" or [タイプ] = "E" or [タイプ] = "F" or [タイプ] = "G"))

上記の式では、タイプがCまたはDまたはEまたはFまたはGと等しいもの、という指定になっています。これでは11種類めの新規のタイプが追加されてもクエリ エディターによる絞り込みテーブルには追加されず、ピボットテーブル レポートにも追加されません。

サンプルの10種類のタイプをもつレコードを用意して検証してみると以下のような結果になりました。

除外が1つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A"))

除外が2つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B"))

除外が3つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C"))

除外が4つの場合
= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C" and [タイプ] <> "D"))

除外が5つの場合(ここで条件式が変わる)
= Table.SelectRows(変更された型, each ([タイプ] = "F" or [タイプ] = "G" or [タイプ] = "H" or [タイプ] = "I" or [タイプ] = "J"))

以降除外が9までは「=」の指定

除外が10個の場合=すべて除外 (また <> が使われる)
= Table.SelectRows(変更された型, each ([タイプ] <> "A" and [タイプ] <> "B" and [タイプ] <> "C" and [タイプ] <> "D" and [タイプ] <> "E" and [タイプ] <> "F" and [タイプ] <> "G" and [タイプ] <> "H" and [タイプ] <> "I" and [タイプ] <> "J"))

なるほど、過半数以上になると条件式が「反転」するようですが、みなさん、これ、意識してますか? 

これ以来、自分は行のフィルター設定をいぢる場合は、ドロップダウンで表示されるGUIを使って指定しても、かならず fx の数式の条件が = なのか、<> なのかを確認するようになりました。

行フィルターを使用している、みなさんのクエリ エディターやピボットテーブル レポート、本当に新しいレコード含まれていますか? 

ちょっと怖いですね。

以上、参考になれば幸いです。

2021/07/18

[Excel] よく使うエクセルのショートカットキー

Excelあるあるで、ショートカットキーの一覧がほしい、というリクエストは多いのですが、正直一覧を手に入れて使えるようになりました!という人はあまり見たことありません(苦笑)

Ctrl+C, Ctrl+V のコピー & ペーストのショートカットキーはExcel以外の場面でも使う鉄板としても、自分が今でも Excel を使っている時に多用するショートカットキーはいくつかあります。ただ、基本的にショートカットキーはその人のExcelの使い方に依存するので「自分の仕事で繰り返し使う機能はどれか?」を把握することが大事です。そこから仕事を楽にするためにショートカットキーを確認して、なんども使う練習をして覚える人が多いと思います。

自分がよく使うショートカットキー(順不同)

Ctrl+↓、Ctrl+↑

マウスをガシガシ、マウスホイールをグリグリし続けなくても、データの一番上、一番下に移動することができます。正確にはデータの「かたまり」の一番上、一番下になるので、途中に空白のセルがあればそこでとまります。Shiftと組み合わせての範囲指定もよく使います。

Ctrl+T

モダン Excelのベースである「テーブル」作成のためのショートカットキー。テーブルについては過去の投稿でいくつも取り上げていますのでそちらも参照ください。

テーブルのすすめシリーズ

http://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
http://road2cloudoffice.blogspot.jp/2014/11/office-365-sharepoint.html

Ctrl+チルダ(~) (Ctrl+Shift+[へ])

このショートカットキーは、使っている人を見たり、話題にしたりする人をあまり見たことがないのですが、自分はほんとうに良く使います。それはワークシートの再利用をしているせいなのかもしれません。セルに数字をいれたら、前の設定があって「%」表示になっちゃった、という時に標準書式形式に戻すために使います。数字入れたら、「1900/1/2」などの日付になった場合も標準書式に戻すのに有効です。 

Ctrl+1

セルの書式設定のダイアログを表示するためのショートカットキーです。セルの書式設定はダイレクトに指定する方法がいくつかあって、たとえば数字の3桁カンマ表示なら Ctrl+Shift+1 ですが(そしてたまに間違える)、書式設定はさまざまな設定ができるので、Ctrl+1 でダイアログを表示してそこから選ぶ、という使い方をしています。

Ctrl+; と Ctrl+: の組み合わせ

現時点の日時を入力するために使います。Ctrl+; で日付を入力し、同じセル内で半角スペースを入力してすぐに Ctrl+: を使って時刻を入力します。自動的に yyyy/m/d h:mm の書式になります。

Ctrl+D

下方向にコピーです。使わない日はない、というほど使います。Ctrl+C, Ctrl+V より使うかもしれません。テーブル形式のデータを扱っていると、Ctrl+R(右方向にコピー)を使うことはないはずです。

これもよく「上のセルの値をコピーする」と紹介されていることがありますが、それよりも「同じ値を複数行に下方向にコピーする」という使い方です。

Ctrl+Shift+L

昔からあるフィルターのオン・オフのショートカットキーですが、モダン Excel で表形式のデータでもよく使うようになりました。モダン Excelではデータは「テーブル」になっているので、基本的にはフィルター機能はオンになっている状態です。Ctrl+Shift+Lを使う場面は、テーブルのフィルターを使って複数の列で絞り込みを指定しているものすべてを解除したい時に、Ctrl+Shift+Lをつかって全フィルターを解除して、再度ショートカットキーでフィルターをオンにする、という使い方が多く、テーブルの絞り込み条件解除に使います。フィルター機能のオン、オフだけのために使うことはないです。

Ctrl+H(またはCtrl+F)

モダン Excel ではテーブルのフィルターを使った検索が主流になるとはいえ、やはり「検索と置換」ダイアログはよく使います。

Ctrl+Alt+F5

ExcelでPower Queryを使っている人であればよく使うであろうショートカットキーだと思います。[データ]タブの[クエリと接続]の[すべて更新]に相当します。

Ctrl+S

たぶん、一番最初に全員が覚えるべきショートカットのひとつ。上書き保存です。

以上、自分がよく使うショートカットキーでした。

2021/07/10

[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関数

ただ、データクレンジングで 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、括弧( やはてな? やエクスクライメーション(びっくりまーく)!などの一部の記号の置き換えを書けばいいのです。これに対応する関数が出てこない原因はマイクロソフトへの日本のマーケットの影響が小さくなったとかネットで言われますが、それよりも、そもそもこのような全角、半角混在の入力ができることが問題で、直すのであれば、そちらを直すべきなんだろうなぁ、と思います。

2021/06/25

[Excel BI] ピボットテーブルで全体に対する%を計算する

ピボットテーブルを使いこなせたら、ものすごい時間短縮になり、仕事の結果も出る!と考えている人は多いと思います。ピボットテーブルが使えない原因のほとんどは「元データ」にあって、それで行き詰まる人が多い、というのはよく聞く話なんですが、逆に、もし元データに問題がなかったらすべてうまくいくのか?というと、実はそうでもありません。

やり方さえ知っていれば超時間短縮になるのに、といつも思うのがピボットテーブルの「全体に対する(占める)割合(%)の計算方法」です。ピボットテーブル作成までできているので、本当に「あとちょっと」なんです。

以下のようにピボットテーブルの外で計算しているワークシートを見ました。数式の =E2/$E$6 が総計に対する割合を計算しています。$は相対・絶対参照云々、、、ということを言いたいわけではないんです。


Excelの素晴らしいところは創意工夫と時間をかければ何とかなるところです。しかし、そのため知っていれば楽になる本来の使い方を知らないまま、無駄に時間をかけてしまうこともしばしばあります。(余談ですが、同じ結果が出る違うやり方があったり、追加された新機能で今まで苦労してたことが楽になった、など、経験者でもそれ知らなかった、なんてことはザラなので、知らないことを卑下することはありません!)

上記のやり方でレポート終了、今後そのワークシートを再利用しないのであれば、これでも全然問題ないのですが、例えば、製品Eが追加された、Bが無くなった、などのデータの追加・更新がはいった時は行数が変わるので計算式を調整する必要があります。1つや2つなら目で確認できますが、1000個ある内の20個くらいが入れ替わる、という状況では目で追うこともできません。それを関数やマクロでチェックする、、、なんていう斜め上の方向に行く前にピボットテーブルの機能を見直してください。めんどくさいなぁ、と思うことはだいたい実現されています。

ピボットテーブルで列に対する割合を計算する

上の図のように総計 30 を 100% として、各項目 A, B, C, D の占める割合 % を出したいパターンは使うケースがかなり多いと思います。とても簡単なので是非覚えてください。

ポイントはセルの書式設定と一緒で「データの見せ方を変えてあげる」なんです。

通常のデータと、割合のデータの2つが必要なので、以下のように個数の列を2つ作ります。


個数のフィールドを同じように値のボックスに何個でもドロップすることで同じ列を作成できます。次にこのように作った「合計/個数2」の列を%の見せ方に変えます。その列にあるセルの上で右クリックをして、右クリックメニュー(コンテキストメニュー)を表示します。


上のメニューの下から4番目にある「計算の種類」を選択します。


ここで「うぁ、、何選べかいいかわからない」となりそうですが、列に対する比率を計算するので、「列集計に対する比率」か「親列集計に対する比率」のいずれかです。とりあえず、「行集計に対する比率」を選んでください。


あとは、セルの書式設定で小数点以下の桁数を調整してもよし、値フィールドの設定から表示形式を調整し小数点以下の桁数を1などにすれば以下のようになります。


いろいろな計算オプションがあるのは、いろいろなケースに対応するためです。たとえば、以下のようなピボットテーブルの場合はどうでしょう。


単純に総計の 30 に対して占める割合であれば、先ほどの「列集計に対する比率」でOKです。でも、だいたいビジネスの世界では以下のように「キッチン用品」とか「庭用品」で小計をしたりします。同じように事業部毎だったり、リージョン・地域ごとだったりします。
(せっかくなので、その小計の出し方を含めてアニメーションGIFにします)



別の計算の種類を選ぶことで、カテゴリ(キッチン用品、庭用品)を100%にして各項目の割合を出すこともできます。いろいろ試してみてください。

忙しいビジネスパーソンこそピボットテーブルを習得すべき

ビジネスパーソンのための、、、というお題で極論すれば、Excelは関数、ましてやマクロやVBAを覚える前に、ピボットテーブルだけを覚えれば、大体の計算はできます。なぜならビジネスの基本のデータは「表」形式であり、表をベースにした計算が求められるからです。

Excelの機能としての「テーブル」、それらを成形・集計するピボットテーブル、さらにクラウドやサーバーからデータを取りこむExcelの「取得と変換」、グラフはExcelの機能としてはかなり複雑で難しいのですが、それでも最新機能で必要なところだけ押さえることでかなりのことができると思います。

あまた機能を内蔵しているExcelはその学習コストがあまりに高い、大きいです。よりに自分に必要な機能を厳選して、それらの機能習得に時間やお金をかけることが望まれます。

何らかの参考になれば幸いです。

過去の投稿ですが、知識としてはまだ使えますのでお時間があればどうぞ参照ください。

[Excel BI]ピボットテーブルで予算と実績を管理する
https://road2cloudoffice.blogspot.com/2019/09/excel-bi.html

[Excel] テーブルのすすめ ピボットテーブルとリレーションシップ

[Excel] テーブルのすすめ 集計行
https://road2cloudoffice.blogspot.com/2014/10/blog-post_31.html

2020/05/01

[Excel データの取得と変換] Webページのテキストデータを取得する

[注意] Webサイトによってはスクレイピングを明確に禁止しているサイトもあるので、利用の際は確認してください。

Power Query / Power BI の[データの取得と変換]によりさまざまなデータソースから Excel や Power BI Desktop にデータを取り込むことができることは、このブログでもご紹介してきました。

特に「テーブル」を使った Web ページからのデータの取り込みは非常に簡単になったのも [データの取得と変換] を使う理由です。この素晴らしい機能が、いまだに Excel ユーザーの中で知名度が低いのは、本当に残念です。

[マイクロソフト] チュートリアル:Power BI Desktop を使用して Web ページのデータを分析する
https://docs.microsoft.com/ja-jp/power-bi/desktop-tutorial-importing-and-analyzing-data-from-a-web-page

今回は、HTMLのテーブルにもなっていないWebページのデータを取得する方法をご紹介します。俗に「ウェブ スクレイピング」と呼ばれるもので、そもそもデータの再利用などを考慮していないページから、最新のデータを取得する、昔はやった方法です。

Doorkeeperconnpassで勉強会開催情報を公開しているAWSのユーザーグループ「JAWS-UG」の両サービスの登録会員数の取得を例としてその方法をご紹介します。ちなみに両サイトはREST APIを公開しているので、勉強会開催情報の取得では本来スクレイピングする必要はないのですが、公開APIを調べる限りだと「登録メンバー数」を取得するAPIがありませんでした。しばらく「登録メンバー数」の取得は月1回なので目視&手作業で行っていましたが、外出自粛で時間ができたので「データ取得の自動化」をしようと思い立ちました。APIを使ったデータの取り込みは以下でご紹介しました。

[Excel] Excel で JSON データを読み込む
https://road2cloudoffice.blogspot.com/2017/06/excel-excel-json.html

Webスクレイピングのポイントは「テキスト ファイル」で扱う

操作の大まかな流れは以下になります。

  1. Webページのhtmlファイルを「テキスト ファイル」としてPower Queryに取り込む
  2. 該当する行を固有のキーワードでフィルターをかけて特定する
  3. 特定した行から、該当するデータを抽出する
  4. 抽出したテキスト データの属性を調整する(テキストから整数など)
それでは順を追ってみていきましょう。

1)テキストファイル形式で取り込む

Webページの構成上「テーブル」形式のものは取り込みやすいのですが、以下のような数値を取り込むのは、HTMLの構造上、テーブル内のデータとして認識されていません。


このようなWebページからデータを取り込むには、まずリボンの[データ]タブの[データの取得と変換]の[データの取得]にある、[その他のデータ ソースから]の[Webから]を使います。

(直接 [Webから] でも OK。ただしExcel2016以前の[Webクエリ]は別モノなので注意)

サンプルとして利用する、JAWS-UG全国のURL(https://jaws-ug.doorkeeper.jp/)を入れます。今回の場合は [基本] を選択したままで [OK] を押します。


今回 [Document] のテーブルビューは使えません。WebページをHTMLのテーブルビューではなく「テキスト」として扱います。そのため、URLのフォルダーアイコン上で右クリックメニューの [データの変換] を選びます。


Power Query エディター ウィンドウが開くので、[クエリの設定] の [適用したステップ] にある [ソース] の横にある歯車マーク(①)をクリックして、[形式を指定してファイルを開く] のドロップダウンリスト(②)を開き、[テキスト ファイル] (③)を選択します。


テキスト ファイル形式を指定することで、Power Query内にWebページのhtmlファイルは「テキスト」として読み込まれます。ここから該当する箇所を探します。

2)フィルターをかけて行を特定する

上からテキストを見て探してもよいですが、最終的には「フィルター」の機能を使って、該当するデータを含む行を絞り込みます。今回の場合は、メンバー数のデータがある部分を取り出したく、該当する記述は以下になります。

<a class="label label-full community-members-count" href="/members">9464人</a>
class名の"label label-full community-members-count"と href="/members" はユニークでフィルターのキーワードで使えそうです。以下のようにタグをコピーして行フィルターをかけます。

3)行からデータを抽出する

MID関数のような機能を使ってテキストの行からメンバー数に該当する数値を抜き出します。抜き出した結果は列を追加して新しく加えるので、[列の追加] タブにある [抽出] を使います。

4)データの属性を調整する

抽出されたデータは「テキスト」なので、これを整数に変換し、列名も変更します。
以下のように簡単に変更することが可能です。


上記では Doorkeeper のWebページからメンバー数を抽出しましたが、同じ手順で connpass からもメンバー数を抽出することが可能です。いくつかの支部のメンバー数を抽出した結果は以下のようになります。


一度シートを作成してしまえば、あとは [データ] タブの [クエリと接続] の [すべての更新] ボタンで、Webページを参照し、最新のデータに更新します。以下のアニメーションGIFでは、合計が 22,887人だったものが、東京支部が1名増えて、22,888人に更新されています。


なお、今回はスクレイピングの手法で、これからも再利用可能なWebページでしたが、Webページの作り方によっては再利用ができないものもあるので、すべてのケースに適用できるわけではないことをご留意ください。(毎回手作業が発生するものは自動化する意味がないからです。ここでいう「自動化」は [クエリと接続] と [すべて更新] ボタンを押すことで最新のデータになるものを指します)

次のステップ

この手の自動化ツールは汎用性を目指さなくてもいいので、該当する支部をすべて抜き出しシートにコピペしながら手作業で一度作成すれば、あとは [すべて更新] で瞬時に最新データに更新されるので、自動化・省力化の目的は達成です。

しかし、ちょっと冗長な感じがするので、時間があれば以下のようなことを検討できるかもしれません。

  • URLを変数としてもらい、メンバー数を返すユーザー関数にする
  • ユーザー関数を使って、計算対象支部の増減に柔軟に対応するシートにする
たぶん、できると思います。対象支部は50以上あるので、汎用化するか、手作業でやるかは微妙な数ですが、できれば汎用化して、またアウトプットしたいと思います。

もうひとつは「最終開催日」の取得です。これはREST APIの勉強会情報の開催日からも取れそうですし、ページを指定して上記の方法のようなスクレイピングでもとれそうです。

以下、ポエム

しばらくこのデータの取得と更新は月に1回、対象の支部が50程度なので、1時間くらいかけて行っていました。各支部の状況なども確認できるので、決して無駄な時間ではない、と考えていましたが、このデータのチェックを月1回から週1回に行う可能性が出てきました。

そうなると、週1回の1時間かかるデータ更新は外部の協力者に作業依頼する方法もありました。しかし、私たちはIT業界にいます。データを扱うITベンダーのマーケティング部門の人間であれば、ITを使って自動化・省力化を考えたほうが前向きだと思っています。ノンテク部門はお金で解決しがちですが、IT業界にいるのですから、やはりITで解決することにまずはチャレンジしたいです。

立場上、できれば、AWSのGlueやQuickSightを使ったほうがいいと思います。個人でデータを扱うのに適した Excel ではなく、クラウド上でデータ処理を行い、他の人たちと処理したデータを共有すべきタスクができたらチャレンジしたいと考えます。

以上、最後はポエムでまとめてしまいましたが、REST APIなどが提供されていない場合、昔ながらのスクレイピングによるデータ取得も、現在、Excel や Power BI Desktop の Power Query でコーディングレスで可能なことがおわかりいただけたかと思います。
何らかの参考になれば幸いです。
Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。