Excelで起こりがちな「どこのセルがどう更新された問題」を解決する方法

「Excelの数字が変わっていますけど、どこをどう更新しました？」仕事中によく聞かれるやり取りだ。1つのファイルを複数人が扱う場合、誰がどこをいじったのかが分からなくなることはよくあることだ。今回は、この問題を解消する方法を解説する。

[三沢友治，富士ソフト]

　1つのExcelファイルを複数人で更新した場合、「誰がいつどこのデータをどのように更新したのか」が不明瞭になりがちです。今回は、営業部の実績管理表を例に、更新前と更新後の差分の抽出方法と、Excelと「Microsoft Power BI」を連携させて差分抽出を自動化する方法を説明します。

Excelで管理している営業実績の更新箇所のみを抽出したい

サンプル課題

Excelで管理している「営業1部2課」の第4四半期の実績について、更新前と更新後の実績数値の差分を知りたい。

　図1と図2は営業部ごとの実績値をまとめたものです。図1は過去のデータを示す「テーブル1（2）」で、図2は更新された最新データを示す「テーブル1」です。

図1　更新前のデータテーブル「テーブル1（2）」（出典：筆者によるキャプチャー画像）

図2　更新後の最新データテーブル「テーブル1」（No 2 営業1部 2課の4Qのデータが更新されています）（出典：筆者によるキャプチャー画像）

　図1のテーブルに対して、図2のように赤枠部分が更新されました。この赤枠（変更箇所）を抽出して、図3のようにまとめる方法を見ていきましょう。

図3　図1と図2のデータを読み込み、2つのデータの差分を新たなテーブルに表示する（出典：筆者によるキャプチャー画像）

1つのExcelファイルにデータ更新前と更新後のテーブルを表示させるには

　まず新しいExcelファイルを作成して、「データ＞データの取得」で過去のデータ「テーブル1（2）」と最新データ「テーブル1」の2つのテーブルを読み込みます（図4）。

図4　「データ」タブ - 「データの取得」でExcelブックを読み込みます（出典：筆者によるキャプチャー画像）

　読み込むExcelブックを選択すると「ナビゲーター」ダイアログが表示されます。テーブルを選択して「読み込み先…」で、過去データテーブルと最新データテーブルそれぞれを同じシートに読み込みます（図5）。

図5　最新データのテーブル「テーブル1」を読み込みます（出典：筆者によるキャプチャー画像）

データの差分を計算するテーブルを作成するには

　これで、1つのExcelファイルに図1と図2のテーブルが読み込めました（図6）。次に、データの差分を計算するテーブルを作成します。ここで説明する方法は読み込むテーブルのデータを転記する方法であり、1対1で部署が用意できる場合に有効です。部署の統廃合が発生する場合は、この方法は使えないため注意してください。

図6　同一シートに読み込んだ過去データの「テーブル1（2）」（上段）と最新データの「テーブル1」（下段）（出典：筆者によるキャプチャー画像）

テーブルの形式が変更された場合でも、差分を抽出できるようにするには

　更新前のデータと更新後の最新データについて、同一の部署情報を基に1つにまとめていきましょう。次の手順でテーブルの結合と行ごとの計算を行います。

　テーブルの結合には「データ＞データの取得＞クエリの結合」と進み、「マージ」をクリックしてクエリの結合機能を利用します。

図7　テーブルの結合を行うにはクエリの結合機能を利用します（出典：筆者によるキャプチャー画像）

　今回は、部署数が変わった場合を想定して結合の種類を「完全外部（両方の行すべて）」にします。統合する2つのテーブルを選択して、それぞれのテーブルで結合のキー（一意の部署情報）とする「No」「部署」「課」を選択します。「Ctrl」キーを押したままクリックすると複数選択が可能です。

図8　テーブルとキーを全て選択し、結合の種類を「完全外部（両方の行すべて）」にします（出典：筆者によるキャプチャー画像）

　クエリを結合すると「Power Queryエディター」が起動します。最新データのテーブルが「1Q」「2Q」「3Q」「4Q」の列で展開され、過去データ「テーブル1（2）」の列ができました。「テーブル1（2）」の列の展開ボタン（図9の囲み部分）をクリックします。

図9　「テーブル1（2）」の列に展開ボタンが表示されます（出典：筆者によるキャプチャー画像）

　次に、展開する列を選択します。展開する列とは差分を確認する項目であり、結合する際にキーとして使用した項目は不要です。ここでは「1Q」「2Q」「3Q」「4Q」を選択します（図10）。

図10　差分を確認したい列を選びます（出典：筆者によるキャプチャー画像）

　過去データのテーブルが「1Q.1」「2Q.1」「3Q.1」「4Q.1」の列として展開されました。次に、差分を計算する列を追加します。「列の追加」タブで「カスタム列」を選択します。

図11　差分を計算するため、カスタム列を追加します（出典：筆者によるキャプチャー画像）

　カスタム列では、展開された列のデータを使って計算します。ここでは、カスタム列に差分の計算式を入力します。まずは「1Q差分」の列を作成して、最新データ「1Q」と過去データ「1Q.1」の差分を計算します。カスタム列の式に「=[1Q]-[1Q.1]」と入力します（図12）。「使用できる列」から挿入すると、簡単に式が作成できます。

図12　「使用できる列」から「カスタム列の式」に使用する列を選択できます（出典：筆者によるキャプチャー画像）

　同様に2Q、3Q、4Qの差分の列を追加します。カスタム列が作成できたら、差分を計算するための元データは不要なので、列を削除して「ホーム＞閉じて読み込む」を選択します（図13）。

図13　差分の計算に使用する列は不要、列を削除しても元データは削除されません（出典：筆者によるキャプチャー画像）

　Power Queryを閉じると、差分データのみを抽出するテーブルが作成されました（図14）。この差分データは、過去データの「テーブル1（2）」と最新データの「テーブル1」のデータを元に計算しています。別ファイルにある元データに変化があった場合は、更新ボタンをクリックすることで差分データを最新の状態に更新できます。

図14　最新データと過去データの差分データを表示できました（出典：筆者によるキャプチャー画像）

Power BIとPower Automate で差分データの更新を自動化する

　ここで説明した方法で差分抽出のテーブルを作成した場合、元データが更新された時は手動で更新する必要があります。「Microsoft 365 E5」に付属する「Microsoft Power BI Pro」を利用すれば差分情報をWeb化できます。また、「Microsoft Power Automate」と連動させることで過去データの更新に加えて、最新データもメンテナンスできるので、差分チェックも自動化できます。

　今回はPower BI ProでのWeb化は行わず、無料版のPower BI Desktopを使ってデータを閲覧する方法を見ていきましょう。

　まず、Excelデータの格納場所を用意するために「Microsoft SharePoint Online」のドキュメントライブラリを作成します。「Microsoft Teams」のチームに過去データと最新データのファイルをアップロードすることで、ドキュメントライブラリを簡単に作成できます（図15）。ここではTeamsを使った、基になるExcelファイルの展開方法を説明します。

図15　Teamsのチームで過去データ「5章_過去データ.xlsx」と最新データ「5章_最新データ.xlsx」のExcelファイルをアップロードします（出典：筆者によるキャプチャー画像）

Power Automateでデータを定期的に更新する設定

　Power Automateを使ってファイルの自動更新を設定します。図16のようにTeamsのチームからSharePointに移動します。

図16　「…」もしくはメニューに表示された「SharePointで開く」を選択します（出典：筆者によるキャプチャー画像）

　SharePoint Onlineのドキュメントライブラリが表示されたら、「統合＞Power Automate＞フローの作成」を選択します（図17）。

図17　SharePoint OnlineのドキュメントライブラリでPower Automateを選択します（出典：筆者によるキャプチャー画像）

　右側の情報バーに「フローの作成」が表示されたら「フローの表示」をクリックします（図18）。

図18　さまざまなテンプレートが表示されます（今回は利用しません）（出典：筆者によるキャプチャー画像）

　Power AutomateのサイトがWebブラウザから起動します。Power Automateの「マイ フロー」で、「新しいフロー」の「スケジュール済みクラウド フロー」を選択します（図19）。

図19　定期的に実行させるフローを定義するにはスケジュール済みクラウド フローを利用します（出典：筆者によるキャプチャー画像）

　「スケジュール済みクラウド フロー」を使うと、一定間隔でフローを実行できます（図20）。Power Automateはライセンスによって単位時間に起動可能な回数が異なるため、ライセンスを確認して起動回数がオーバーしないように実行間隔を設定するよう注意が必要です。万一、起動回数がオーバーとなった場合は起動可能となるまで動作が停止します。

図20　今回は「繰り返し間隔」を4時間毎に設定しています（出典：筆者によるキャプチャー画像）

　フローを実行する「開始日」「繰り返し間隔」など詳細を設定して「作成」ボタンをクリックすると新しいフローが作成され、繰り返し操作として「Recurrence」が設定されます。次にPower Automateを「GUIで操作」を選択してフローを組み立てます。操作の選択画面でSharePointを選択すると、SharePointのアクションを設定できます（図21）。

図21　定期的にファイルを更新する操作を設定するためSharePointを選択します（出典：筆者によるキャプチャー画像）

　「ファイル コンテンツの取得」で最新データのファイルコンテンツを取得し、「ファイルの作成」で過去データのファイルを作成します（図22）。SharePointでは「別名でファイルを保存する」項目は選択できません。元のファイルコンテンツを取得した後にその内容をパラメータにしてファイルを作成することで別名保存できます。「ファイルの作成」では同名の既存ファイルがある場合は上書きされるため注意が必要です。

図22　「ファイル コンテンツの取得」と「ファイルの作成」の操作を設定します（出典：筆者によるキャプチャー画像）

　これで定期的に最新データのファイルが、過去データのファイルとして自動的にコピーされるようになりました。

Power BIで差分を確認する

　次にPower BIを使って更新前と更新後のデータの差分をダッシュボードに表示させる方法について説明します。

　Power BI Proは「Microsoft 365 E5」のライセンスで利用できます。1日に8回までの更新を自動化でき、Webから差分データを確認する設定が可能です。Power BI Proのライセンスがない場合は、無料で利用できる「Microsoft Power BI Desktop」を利用することで同様の操作が可能です。Power BI Desktopは「Microsoft Store」からダウンロードできます（図23）。Power BIという別アプリもあるので注意しましょう。ここではPower BI Desktopを利用した方法を説明します。

図23　Microsoft StoreからPower BI Desktopを入手できます（出典：筆者によるキャプチャー画像）

　Microsoft Store からPower BI Desktopをダウンロードし、インストール後に「開く」をクリックして起動させます（図24）。

図24　インストールすると「入手」ボタンが「開く」ボタンに変わりPower BI Desktopが起動できるようになります（出典：筆者によるキャプチャー画像）

　Power BI Desktop起動後に「作業の開始」ダイアログが表示されたら、「データを取得」をクリックします（図25）。

図25　作業の開始ダイアログで「データの取得」をクリックします（出典：筆者によるキャプチャー画像）

　「SharePointフォルダ」を選択して「接続」ボタンをクリックし、SharePointに接続します。

図26　「SharePointフォルダ」を選択、SharePointに接続します（出典：筆者によるキャプチャー画像）

　「SharePointフォルダ」の「サイトURL」に、SharePointサイトのURLを入力して、「OK」ボタンをクリックします。サイトのURLは以下の形式で入力します。

https://□□.sharepoint.com/sites/〇〇

図27　SharePointサイトのURLを入力します。末尾の”/”は不要です（出典：筆者によるキャプチャー画像）

　SharePointサイト内のファイルが表示されたら、「データの変換」ボタンをクリックします（図28）。

図28　表示されるのは一部のファイルのみなので、使用するファイルが表示されていなくても操作を進めます（出典：筆者によるキャプチャー画像）

　するとPower Queryが表示されます。「クエリ1」でアップロードした2つのファイルが表示されます。過去データのファイル「5章_過去データ.xlsx」の「Content」列にある「Binary」をクリックします（図29）。

図29　Power Queryで過去データのファイルの「Content」列の「Binary」をクリックします（出典：筆者によるキャプチャー画像）

　ファイル内のコンテンツ「テーブル1」が表示されるので、「Data」列にある「Table」をクリックします（図30）。

図30　「テーブル1」の「Data」列にある「Table」をクリックします（出典：筆者によるキャプチャー画像）

　最新データも同様の設定をします。同じSharePointサイトにアップロードしたので、「ホーム＞最近のソース」から選択できます。

図31　最新データは同じSharePointサイトにあるので「ホーム」タブ - 「最近のソース」から選択できます（出典：筆者によるキャプチャー画像）

　ここからの作業は先述のテーブルの統合と同様です。クエリをマージして差分抽出に必要な列を抽出します（図32〜37）。

図32　「クエリのマージ」 - 「クエリ1」を選択します（出典：筆者によるキャプチャー画像）

図33　結合するクエリとキーを選択します（図 8と同様）（出典：筆者によるキャプチャー画像）

図34　「クエリ2」の列に展開ボタンが表示されます （図 9と同様）（出典：筆者によるキャプチャー画像）

図35　差分を確認したい列を選び、不要な列を除外します （図 10と同様）（出典：筆者によるキャプチャー画像）

図36　差分を計算するためカスタム列を追加し、差分の計算に使用する列は不要なので削除します （図11、図12、図13と同様）（出典：筆者によるキャプチャー画像）

図37　「ファイル＞閉じて適用」をクリックして保存します（出典：筆者によるキャプチャー画像）

　これでクエリの準備ができました。次にPower BI Desktopのページを作成します。ページは複数のデータを作成できるダッシュボードになっています。前述の方法と同じフローで作成するにはテーブルで構築するとよいでしょう。テーブルアイコンをクリックします（図38の囲み）。

図38　Power BI Desktopでテーブルアイコンをクリックします（出典：筆者によるキャプチャー画像）

　デスクトップにテーブルが追加されたら、画面右側に表示される「フィールド」から

表示したい列を選びます。選択した列は、選択順にデスクトップのテーブルに表示されます。

　データを更新したい場合は、「更新」ボタンをクリックすることでExcelのデータが更新されます（図39）。

図39　フィールドから項目を選択した順にテーブル内に列が作成されます（出典：筆者によるキャプチャー画像）

　Power BIは読み取り専用のツールでありExcelのようにデータを操作できないため、ファイルを展開して安全に情報共有することが可能です。また、Excel以外のデータソースとも連携が可能です。フィルター類と合わせて利用すれば情報の一元管理が容易となります。Power BIは操作が簡単で高機能なツールなので覚えておくといいでしょう。

　昨今の潮流として、外部接点の強化を目的にSoE（System of Engagement）への投資が集中する一方で、SoR（System of Record）や SoI（System of Insight）などの社内向けシステムは現場主導で進められるケースが増えました。その背景にはPower Platformなどのローコード／ノーコードツールによる市民開発が定着したことがあります。こうしたツールの台頭によってデータ分析や集計作業は容易になりましたが、重要なのは目的やシーンによって使うツールを使い分けながら、最適な手法で業務を進めることです。ツールの使い方を決めるのは利用者となる皆さんです。今回の連載が、読者皆さまにとって参考になれば幸いです。

著者紹介：三沢友治

2007年頃に「Microsoft SharePoint Server」の導入に携わり、それ以後数多くのMicrosoft製品の導入案件を担当。最近は「Microsoft 365」の導入や活用支援、さらにはMicrosoft 365の機能を活用したカスタマイズ開発の指揮も担う。ミドルウェアのバージョンアップにおけるカスタム機能の互換性維持に苦慮した経験から、継続開発が続けられるシステム環境としてMicrosoft製品群に期待を寄せる。2017年には「Microsoft MVP Award」を受賞。