Modern Excel
Power BI系のツールを調べていると Modern Excel (モダンエクセル）という言葉を見ることがあります。
これは Power Query や Power Pivot といった、Power なんとか系ツールを使ってお仕事を効率的にするための新しい Excel の使い方をさす「造語」です。
WHAT IS MODERN EXCEL? - ExcelTV(英語)
https://excel.tv/what-is-modern-excel/
Excel は製品の歴史が古いにもかかわらず、今でも多くのユーザーに使われ、それも業務の第一線で利用されている珍しいソフトウェアです。そのため、現場にこれまで蓄積されたテクニックや技術が数多くあり、鉄板ネタ、その世界では常識、といった手法や使い方が存在します。その反面、レガシーな方法でも多くのことができてしまうため、新しいツールや機能を積極的に取り込む「心の」ハードルが高くなっている、という状況も散見されます。
現在、モダン Excel の世界で Power Query や Power Pivot の緩やかな Excel 本体との統合が進んでいます。最新アップデートの Excel ではデータ タブの [データの取得と変換] は Power Query です。Power Pivot アドインを有効にしていなくてもデータ モデルやリレーションシップ、メジャーを Excel で使うことが可能です。
[Excel] データの取得と変換が標準になりました
https://road2cloudoffice.blogspot.jp/2017/06/excel.html
リレーションシップとデータ モデル
https://road2cloudoffice.blogspot.jp/2015/02/blog-post.html
[Excel BI] ピボット テーブルとメジャー
https://road2cloudoffice.blogspot.jp/2017/05/excel-bi.html
さらに、これから Excel との連携が進んでいくのは Power BI だけにとどまらず、 PowerApps と Microsoft Flow といったツールであるのは、ほぼ間違いないでしょう。OneDrive などのクラウド ストレージに保存されている Excel ブックをデータ ソースとして、PowerApps や Microsoft Flow で利用するパターンです。何やら難しい仕組みのようにも見えますが、これらのツールはタブレットやスマホなどの利用を前提とし、かつアプリ開発や設定もノンプログラミングでできることを目的にしているようです。
Microsoft Flow と Excel
将来重要性が増すだろうツールの1つである Microsoft Flow (マイクロソフト フロー) という単語を見ると、なにかワークフローを扱って、難しそう、というイメージがあるかもしれません。
Microsoft Flow
https://flow.microsoft.com/ja-jp/
自動化するワークフロー、ビジネス プロセスの自動化、などを見ると難しそうですが、Excel ユーザーの観点から以下のようなことが Flow でできます。
- Excel を開かずにワークシートのテーブルにデータを自動追加できる
Excel ブックのデータを読み込む、取り込む、といったことは Power Query で可能でした。Power BI系ツールでは難しく、VBA を使わなければならないのはブックへのデータの「保存」や「書き込み」の処理系でした。このエリアが Flow や PowerApps によって可能になりつつあります。
ただし、対象となる Excel ブックは OneDrive や SharePoint Online のフォルダーの中に作成することが必須です。クラウドが利用可能であれば、欲しいデータを欲しい形で「Excel のワークシート(のテーブル)」で扱うことができるので、いろいろとアイディアが膨らみますよね。Excel ブックとして扱うことはもちろん、レポートや分析は Power BI の得意の分野ですから、Excel ブックをデータ ソースとして利用することができます。
どのくらい Flow による Excel ブックへのデータの追加・保存が簡単なのか、順を追って見ていきましょう。
Flow で Excel を使うポイントは「テーブル」
Flow の設定は
Web 上で行います。そのため、
Microsoft Flow の Web サイトに「サインイン」する必要があります。Office 365 の組織アカウントか、Microsoft アカウントの利用が可能です。Power BI と違って、hotmail などのフリーのメールアドレスの Microsoft アカウントの利用が可能ですが、作成したフローを他の人と共有できません。他のユーザーとなんらかのコラボレーションをする場合は、Office 365 の組織アカウントをベースにする方向性なのでしょう。一人で試すだけであれば、いくつかの制限がありますが Microsoft アカウントでも利用可能です。
 |
| 他のユーザーとフローを共有しようとすると表示されるアラート |
Flow のサイトでは数多くのテンプレート（フローのサンプル）が用意されています。もちろんゼロから作ることも可能ですが、テンプレートを利用することで開発・設定の時間を大幅に削減することが可能です。
Excel ブックを保存先として使う場合のポイントは2つあります。
- 事前に使用するブックを用意し、ワークシートに「テーブル」を作っておく
- Excel Online でも、Flow のテスト時にはブックを必ず閉じる（ロックを避ける)
テーブルの列名はどんな名前でもいいです。フローの設定で、用意した列と保存するデータの突合せを行うことができるからです。とはいえ、どんなデータがフローのテンプレートで用意されているかを確認し、必要な列を準備することを考えれば、事前にテンプレートでデータの名前を確認して、それとわかる列名でテーブルを作成することになります。
なお、Excel の「テーブル」をあまり使ったことがない人も結構いるようです。モダン Excel では基本、テーブルの使用が前提となっていると考えて差し支えないでしょう。行・列からなるセル範囲とは全く別の新機能を提供してくれます。テーブルについては
当ブログでも過去に紹介しているのでぜひ参照してください。
自動的に Excel にデータを書き込む
Office 365 のユーザーは Office 365 の組織アカウントを使って Flow を使うのが良いでしょう。他のユーザーとのフローの共有はもちろんですが、サンプルとして使う「一定間隔で何かする」という繰り返しの設定の上限がマイクロソフト アカウントを使った場合(無料プラン)と変わります。さらに Flow の Premium ラインセンスを購入することで利用できる機能が変わります。上限を超えた設定をするとアラートメッセージなどでも表示されますが、プランの種類と制限の確認は以下を参照すると良いでしょう。
マイクロソフト フロー プラン
https://flow.microsoft.com/ja-jp/pricing/
シンプルな Flow と Excel の連携のサンプルを紹介します。とてもシンプルですが、これを Excel のみで実現するには、、、と考えながら見ると面白いでしょう。
アカウントはあえて Microsoft アカウントの無料プランと OneDrive を使ってみますが、Office 365 組織アカウントを持っているならば組織アカウントで試すのがいいと思います。サンプルの処理は
「15分おきに、特定の文字列と現在の時間を Excel のテーブルに追記する（＝最後の行に追加していく）」です。よって、前提として
・マイクロソフト アカウント取得済み
・マイクロソフト アカウントで OneDrive が使える
・できれば Excel が PC にインストールされている（Excel Online のみで操作可能ですが、あったほうが吉）
です。
大まかな流れは、
・Microsoft Flow で新しくフローを登録する
・OneDrive で Excel ブックを新規作成し、データ保存のためのテーブルを準備する
・フローの Excel 操作のアクションで準備したテーブルを指定する
・フローを動かす
・Excel のテーブルにデータが追加されているのを確認する
です。
1) Microsoft Flow にサインイン
Office 365 の組織アカウントを使うのであれば、ポータルのランチャーから [Flow] を選びます。直接 Flow のページへは、https://flow.microsoft.com/ja-jp/ でいきます。
Microsoft Flow サービスのページからサインインします。サインイン後にトップバー(タブ)の [マイ フロー] をクリックして、マイ フローに移動します。
2) [+ 一から作成] をクリックして、フローを新規作成、名前を付ける
フロー名 無題 の無題の部分にフロー名を入力します。[コネクタ] から [スケジュール] をクリックしてフローに追加します。このスケジュールは、設定する間隔で「動き出す」部品です。フローの開発はこのように「部品」を選択して、組み合わせていきます。
3) スケジュール 繰り返しの設定
繰り返しのスケジュールトリガーはプランの最小値の 15 分にしておきます。
Microsoft アカウントを使った無料プランでは、この間隔の最小設定は 15 分です。Office 365/Dynamics365 のプラン は 5 分（ただし、5分未満もお試し版で試すことができます）、Flow プラン1 は 3分、プラン２は 1分（最小設定）です。
次に、Excel へデータを追加するアクションを追加します。上図のように [+ 新しいステップ] から [アクションの追加] をクリックします。
4) Excel 操作アクションの追加と設定
結論から言えば、ここではアクションのみ追加します。OneDrive の Excel ブックとテーブルの事前準備ができていないためです。
[コネクタ] の下にある [アクション] から [Excel 行の挿入] を選択します。
このアクションを選択したのち、OneDrive の Excel ブックを指定することになるので、ここで、OneDrive に Excel ブックを作成します。
今回の Excel への行の追加のサンプルの「フォーマット」は自由ですが、アクションによっては追加できるデータの種類と数が決まっています。事前にテーブルと列を用意するにも、どんなデータを取得し追加するかを調べる必要があるので、実際のところはテーブルを用意する前にここでチェックすることになります。
5) OneDrive 上に Excel ブックとテーブルを作成する
OneDrive上に Excel ブックを作成します。作成するフォルダーは自由です。デスクトップ アプリケーションの Excel でブックとテーブルを作成し、OneDrive にアップロードしたものも当然使えます。私のサンプルでは 1_WORKS という名前のフォルダーを作り、そこに ブック1.xlsx を作成しました。
OneDrive の Excel Online だけで操作する場合の注意点は、テーブル ツールが使えないことです。ちょっと「？」な仕様なのですが、Excel Online にてテーブルの作成は可能で、作成されるテーブルは日本語環境であれば「テーブル1」になります。しかし、テーブルの名前の確認や、名前の変更は Excel Online からできません。
以下のアニメーションGIFは、Excel Online だけでテーブルを作る手順です。 列名は 1, 2,3,4 で連番にしました。テーブル名の指定はできないので、この場合は「テーブル1」になります。[挿入] タブの [テーブル] で作成ができます。
なお、テーブルを作成し終えたら、Excel Online のブラウザは閉じてください。
コンシューマー OneDrive の Excel Online は、ブックを開くと「編集モード」で開いているため、ブックにロックかかり、Flow によるデータ追加が失敗するからです。（これは、業務運用上大きな考慮点になりえます）
6) アクションに Excel ブックを関連付ける
Excel ブック操作のアクションを選択すると、マイクロソフト アカウントを使用しているので OneDrive へサインインになりますが、接続先のクラウド サービスを変更することは可能です。たとえば、OneDrive for Business や SharePoint Online ドキュメント フォルダーにある Excel ブックも、適切なアクセス権が設定されているアカウントを使うことで利用可能です。
Excel 行の追加のアクションを追加して、OneDrive 上のブックを指定します。ブックを指定することで、ブックに含まれるテーブルがリスト表示されるので、それを選ぶと、テーブルに含まれる列すべてが表示され、列に追加するデータを指定することが可能になります。
7) 追加データを列に設定する（行の追加日時の設定方法）
業務利用の場合は、イベント間隔の部品 [スケジュー 繰り返し] と Excel ブックへのデータ追加の部品 [行の挿入] の間に「なんらかの処理をする部品」を配置して、その結果を Excel に取り込みますが、今回は直接 [行の挿入] で入力データを設定します。
文字列はそのまま同じ文字列挿入の繰り返しになりますが、ここに変数や関数があれば、イベント毎に処理をして Excel に書き込むことができます。
Excel ユーザーとして、だいたい一番最初にやりたくなるのは、行が追加された日時（タイムスタンプ）を入れることです。ワークシート関数に NOW() を入れたけど、ワークシート更新するたびに数値が変わる、どうすればいい？のアレです。
列1に文字列の「Excel-Flowテスト」
列2に Flow で日時を取得する
ワークフロー定義言語の関数 utcnow()
列3に 世界標準時刻 utc に 9 時間追加した日本標準時刻
列4に yyyy/MM/dd HH:mm:ss 表示の日付・時刻
上記データを追加するための列設定は以下です。
列1
Excel-Flowテスト
列2
"@{utcnow()}"
列3
"@{addhours(utcnow(),9)}"
列4
"@{formatDateTime(addhours(utcnow(),9),'yyyy/MM/dd HH:mm:ss')}"
特に列4の英文字の大文字・小文字に注意してください。大文字の MM は月を、小文字の mm は分を、HH は24時間表示など、それぞれ意味があります。
utcnow() や addhours()、formatDateTime() は「ワークフロー定義言語」の関数です。M言語や DAX、R言語も Power BI でやらなきゃなぁ、と思っていたところにワークフロー定義言語 (Workflow Definition Language)がやってきました！これも避けては通れない言語でしょうね。ただ、大きなトレンドは「如何にコードを書かせないか」なので、将来的に学習コストは最小限になることを期待しましょう。
また、関数の前後についているダブルコーテーションは必要です。
ワークフロー定義言語については、まずは以下を参照されるといいでしょう。
Azure Logic Apps のワークフロー定義言語
https://docs.microsoft.com/ja-jp/azure/logic-apps/logic-apps-workflow-definition-language
また、Microsoft Flow は Azure Logic Apps をベースに作成されています。上記のようにワークフロー定義言語などの資料は Azure Logic Apps のものが参照できたりします。
Microsoft Flow と Azure Logic Apps の関係は以下のページが参考になります。
Flow, Logic Apps, Functions, WebJobs の比較
https://docs.microsoft.com/ja-jp/azure/azure-functions/functions-compare-logic-apps-ms-flow-webjobs
8) フローを保存し、動かす
フローの作成をクリックすると、保存され、実行されます。
エラーがなければ、完了をクリックのメッセージが表示されます。[完了]5D; の 5D; はバグですね。
完了を押さずとも、初回のフローが実行されて、その結果が再描画され表示されます。
完了をクリックすると、編集モードが終了します。
Excel ブックを確認すると、以下のようにデータが追記されていることが確認できます。
ダブルコーテーションで囲まれている文字を挿入しているので、Excel としては "2017/06/22 16:20:05" は日付データのシリアル値ではなく、文字列です。列2から列4のワークフロー定義言語の書き方の検討余地はまだあると思いますが、たとえば
"@{utcnow()}
" の両端のダブルコーテーションを
最初からとると駄目です。少し動きが怪しいのですが、ダブルコーテーションをつけることで確実に関数として動きますが、最初から外して関数を利用しようとすると「文字列」として処理されます。ただし、一度ダブルコーテーション付きで登録し動いているフローを、あらためてフローの編集で両サイドのダブルコーテーションをはずすと、ダブルコーテーションがない日付文字列が入ります。どうやらデータ挿入のためのワークフロー定義言語関数の処理と JSON の構成方法に課題がありそうです。まずはダブルコーテーション必須で覚えておいたほうがよさそうです。
ちなみに、これでタイプミスなどでエラーになった、もしくは上述のワークフロー定義言語の書き方を変える、などフローを更新した場合、間隔の15分待つことはありません。[再送信] や [今すぐ実行] をすることで、最小設定時間以内でもフローを動かすことができます。
Excel のテーブルと Flow
Flow で Excel を使う場合は、事前にExcelのテーブルを用意しますが、Flowにおけるテーブル利用時の特徴・注意点は「
セルの数式は使用できない」です。数式によって値を算出するセルをテーブルの列として使うことができません。Flow によって新しく追加した行には、テーブルで事前に定義された数式はコピーされません。A列に1、B列に2だけをいれて、C列で =A+B で 3 にするようなパターンです。コピーされないというより、数式がクリアされる感じです。
文字列や数値はそれほど扱いに困りません。何も設定していないセルの書式「標準」で事足ります。一方で、シリアル値は、上記の 8) で紹介したように utcnow() で世界標準時をとり、addhours() で9時間足せば日本の時間になりますが、 2017-06-22T09:50:51.0064743Z のような記述になり、このままセルに入力しても日付データとして Excel は認識せず、文字列として扱います。また繰り返しになりますが、ダブルコーテーション問題もあります。
なんだ、結局使えないじゃないか、と考える前に、この「Excel を起動することなく、自動的に、ブックにデータを追加する」という仕組みは、これまでプログラム開発が必要になるものでした。それ、Excel の仕事じゃないですよね？と言われる範疇のものです。これをノン・コーディングで可能にするのが Flow です。
後処理を Excel 中心にすると、どうしてもビックテーブル（ビックデータじゃありませんよ）で考えがちです。ビックテーブルとは、すべてのデータを含んだ1枚の大きな表です。その Excel のビックテーブルと、数式が使えない、ブックを開いているとロックする、といった Flow の制限を組み合わせると活用が難しく感じられますが、Power BI を使ってデータ ソースとして Excel ブックを指定すると Excel でブックを開くことなくデータを取得することができ、Power Query のデータクレンジングとデータの型変換を持つ ETL 機能を使えば、ダブルコーテーション付きのテキスト文字列を日付のシリアル値に変えることはクリック操作のみで可能です。異なるブックのテーブル間のリレーションシップによるマージも可能です。
制限や考慮点がある、情報がまだまだ少ない、といった状況ですが、モダン Excel の重要な要素の１つであることは間違いないので、引き続きウォッチしていく価値はあると思います。
まとめ
Excel と Flow の関係を紹介してきましたが、Excel を扱うアクションは行の挿入だけではなく、行の取得もあります。取得するを開始する行番号をスキップ数で指定し、そこから指示した行数分のデータを全列分取得します。Flow の内部では、取得したデータは JSON形式で保持され、次のアクションで利用されます。
どんなことが Flow と Excel の組み合わせでできるかはテンプレートを参照するのが一番です。Excel を含むテンプレートの一覧は、Flow のトップページの検索ボックス [最初に使用するテンプレートまたはサービスを探す] で Excel を入力して検索すると表示されます。
以上、長くなりましたが、参考になれば幸いです。
