エクセルチュートリアル②PowerQuery・PowerPivotを利用した予実管理-テスト初回実行管理
エクセルチュートリアル第2弾です。今回はシステム開発でよくある単体テストの予実管理を行うため、必要な情報を整理、収集してエクセルでダッシュボードを作成するというシナリオを想定しています。今回は大分専門色が強くて、業務シナリオの中身を理解するのがもしかすると難しいかもしれませんが、システム開発のPMOをやってる方は知っていて損がない内容となっています。業務シナリオが難しいな。。。と思った場合は、とりあえず読み進めて実際に手を動かしてエクセルでレポートを作ってみることをお勧めします。（予算と実績の予実対比などもいつか取り上げる予定です）
今回取り上げる機能は下記です。
・PowerQueryでマージすることを前提とした実績入力用のエクセルテンプレートの作り方
・クラウド上（今回はOneDrive上）のファイルの読み込み方法
・複数エクセルファイルの読み込み・マージ方法
・予実対比レポートの作成方法
例のごとく、すべてマクロを利用せずにエクセルの標準機能のみで実現可能です。
※今回も架空のデータ（筆者作成）を利用しての解説になります。説明を簡単にするため、一部（業務シナリオとデータに）不自然なところがありますがご容赦ください。
00.こんな人向け
・エクセルでちょっと高度な予実管理レポートを作成したい人
・エクセルで開発管理をさせられる人（なぜか多い...）
・PowerQueryやPowerPivot等モダンエクセルの機能を学び、業務で活用するための入口がほしい人
・普段のExcelを利用した業務をノンプログラミングで効率化したい人
・何らかの事情でともかくMicrosoft製品を利用して仕事をする必要がある人
・毎回同じようなデータ加工・集計作業を行う必要がある人
・Office365等最新のExcel(Ecel2010以降)を利用できる人
・Excelの基本的な動作は把握している人（どこにどのタブがあるか分かり、関数を使った経験のある人）
キーワード：Excel,Power Query(パワークエリ),PowerPivot(パワーピポット),予実管理,BI,データ分析,DAX
01.業務シナリオの確認
あなたはソフトウェア開発のPMOです。ソフトウェアの開発が一通り終わり単体テスト（作成したプログラム単体で予想通りに動くかを確認するテスト）を行う必要があります。
テストを行う際には予定をたてて、実績と比較して遅延が発生しているようであれば、なんらかの対応を行う必要があります。つまりざっくりと考えて下記を行う必要がありそうです。
イ）予定に対して、どの程度テストを終えているのかを把握
ロ）予定通り推移していない場合は何が原因なのかを把握
ハ）原因が判明した場合は何らかの対応を考える
イとロ、ハの一部分をダッシュボードを作成して、予実対比をしながら管理を行うことにしました。”予実対比”と一口に行っても色んな方法があるのですが、今回は計画されたタスクの消化率を追っかける方法で行うことにします。具体的に言うと、Aさんが〇月△日までに終えないといけないタスクを１０個持っていた時に、現時点でいくつのタスクが完了したか、期限までに終わりそうか。を追っかけていくことを想定しています。
具体的に言うと下記みたいな予実に関するグラフを表示するダッシュボードを作成して、予実の差を色んな軸で分析して打ち手を考えます。
さて、実際にダッシュボードを作成していきたいのですが、ダッシュボードに必要な要素は何でしょうか。上記のイ、ロ、ハで大事なことは結局、遅れている部分を特定し、その原因分析を行い、対策を行いスケジュール通りになるように頑張るというとこです。つまり、ダッシュボードに求められることは
・適切な粒度でどこが遅れているのかがわかる（＝どこに問題があるのかがわかる）
・遅れている理由を推定するきっかけが分かる。（個人のスキルが原因なのか、作業量が多すぎるのか、タスクが難しすぎるのか等）
になりそうです。
上記をブレイクダウンして下記をダッシュボードに実装しようと思います。
・予定に対するタスクの消化率を問題があるかどうかの指標として使う。つまり、予定に対するタスクの消化率を追いかけられる。
・個人あるいはチームの単位でドリルダウンして予実を把握できる。（消化率が悪い人あるいはチームにヒアリングをかけられるように）
・特定のタスクや機能単位でドリルダウンして予実を把握できる。
ダッシュボードの要件が決まりました。次はダッシュボードを作成するための必要な情報を整理してい行きましょう。
Note：予実対比
上述の通り予実対比の方法は色々あります。今回は日別にスケジュールされているタスクの消化率を追いかけていますがこれは進捗率にフォーカスした管理方法です。
実際の業務ではそのプロジェクトのファイナンス（財政状況）も重要な指標です。例えば特定の人が残業をたくさんして進捗を保っているけど、実はプロジェクトとしては赤字みたいなパターンは今回のタスクの消化率を追いかけているだけでは気づけません。
今回は説明を簡単にするためにタスクの消化率を追いかけていますが、実際のプロジェクトでどんな指標を確認すればよいかはPMPの本などを確認すれば詳しく書かれていますので気になる方は確認してみてください。そちらで得た知識とエクセルやPowerBIを組み合わせると管理業務をかなり効率的に行えると思います。
02.ダッシュボードを作るのに必要な情報の整理
さてではダッシュボードに必要な情報を整理しましょう。このときに一回どんなレポートを作りたいのか手書きでもよいのでイメージを整理しておくといいです。今回はこんなイメージのダッシュボードを作ろうと思います。
上記のレポートを作成するのに必要な情報を整理しました。すでに一覧として整備されている情報はそれを流用するとして、今手元にない情報は必要に応じて自分で作り出すか、どこかから取得してくる必要があります。
今回は手元にない、テストケースごとの予実を入力してもらうエクセルフォームと、カレンダーテーブルを自分で作成することにします。
表1.ダッシュボードで利用するデータと諸元
03.必要な情報の収集
それでは必要な情報を用意しましょう。表１に記載したメンバリストとテスト対象機能一覧は、すでに整備された一覧がOndeDrive上にあるのでこれを利用します。（通常テストよりも前の工程で作成されています）
自分で用意する必要があるテストケースごとの予実を管理するエクセルフォームと、カレンダーテーブルを作成します。
作成したテストケースは下記のようなフォルダをOneDrive上に作成して担当チーム毎に担当するファイルを格納しておきます。
※今回ダッシュボード作成に利用するメンバリストと対象機能一覧は「10.各種一覧」というフォルダに格納しています。
図２.フォルダ構成イメージ
今回はテスター（テスト実行者）に下記のようにテストを実行してもらう想定です。
①テスターにテストケース（何をテストするかの一覧）を作成してもらう
②テストケースの実行管理用のフォームに予定を入力
③テスト実行・実行結果を記入
その後管理者（あなた）が、下記のような作業を行ってテストを実行していく想定です。
④実行結果を日次で集計
⑤どこに問題があるかを分析
⑥何らかの打ち手を検討する。
01.業務シナリオで出てきた要件をもとにあなたは今回下記のようなテンプレートを作成し、テスターにテストケースとそれらの実行予定を書き込んでもらうことにしました。（今回は初回実行についてのダッシュボードを作成しますが、実際の業務では2回目以降についてもダッシュボードを作成してちゃんとテストが完了するまで管理を行います。）
どういう項目を設定すべきかはプロジェクトの性質によって変わるのであまりここでは言及しません。ですが、エクセルフォーム作成時にやった方がいいことがあります。それは、入力用のフォームは範囲ではなくテーブルで作成すべきということです。詳しくは別ノートで解説予定ですが、テーブルで作成することによってこんなメリットを享受できます。
①テスターが勝手に列を追加しても、項目名（ヘッダー名）を変えていなければ、正しくマージできる
②パワークエリで取り込み時にテーブル名を指定するだけで取り込み作業が楽
特に①は管理作業を行っているひとからすると、ありがたさがよくわかるのではないでしょうか。（エクセルマクロでは実現できないのでは？）
テーブルにする方法は簡単で、作成した表を選択してAlt+H+T（テーブル化のショートカット）をして好きなデザインのテーブルを選べばOKです。この時デザインタブからテーブル名を設定することを忘れないように。ここで分かりやすいテーブル名を設定しないと後でマージするときに少し面倒です。
Note:テーブルと範囲
エクセルのデータの持ち方にはこれまでに皆さんが慣れ親しんだ「範囲」と「テーブル」の二つがあります。
この2つにどんな差があるのかは別途記事にする予定ですが、ざっくり書くとテーブルにすると、そのテーブルをエクセルが一つのデータの塊と認識してくれます。その結果このテーブルと教えてあげることでマージが容易になったり、構造化参照という列名を利用したセルの指定ができエクセルとしての保守性が上がります。
また今回はテストケースのファイル名については下記のような命名規則を設けています。
テストケースファイル命名規則：単体テストケース_機能名_vx.xx
このようにファイルの命名規則を定めることで後程行うエクセルファイルのマージが簡単にできます。
04.カレンダーテーブル作成
次にカレンダーテーブルを作成しましょう。カレンダーテーブルとは、簡単に言えば日付に関する情報を分析したい粒度で整理した表です。
例えば週単位で集計したいときはどの日が何週目なのかを定義してあげる必要があります。ほかにも曜日単位で分析をしたいなら曜日をセットします。ここら辺は分析していくうちに追加できるので、とりあえず下記表のように作成しました。この表はエクセル関数でバチバチっと作成しています。こちらもテーブルとして設定してあり、名前もカレンダーテーブルにしてあります。
05.ダッシュボード作成
では、ダッシュボードを作成していきましょう。今回利用するファイルは下記からDLできますので、OneDrive上にファイルを置いて「03.必要な情報の収集」で提示したフォルダ構成と同様のフォルダ構成にして実践してみてください。ちなみに中身をランダム関数を利用して作成している関係上、テストケースのファイルを開いて保存すると今回の結果と違う結果のダッシュボードが作成されます。
ダッシュボード作成のために必要な作業は下記です。
a)テストケースのエクセルファイルをマージする。
b)マージしたテストケースを予定と実績で分割する。
c)カレンダーテーブルや、メンバリストなどの他の情報と予実テーブルにリレーションを作成して分析の軸を作成する
d)DAX式を設定
e)グラフやらなんやらを作成してダッシュボードを構築
05-a)テストケースのエクセルファイルをマージする。
まずエクセルファイルをマージします。
データタブ⇒データの取得⇒ファイルから⇒フォルダから をクリックします。
OneDrive上のテストケースを保存しているフォルダ（20.テストケース）を選択します。
現れたウインドウでデータの変換をクリックしてクエリエディタを起動します。
読み込むファイルをテストケースの予実を記載しているファイルに絞り込むため、「name」列の▼ボタンをクリック⇒テキストフィルター⇒「指定の値を含む」をクリックします。出てきた行のフィルターウインドウに「単体テスト」と入力してOKをクリックします。
※実際の命名規則を見ると、「単体テスト」をキーワードにするよりも、「単体テストケース」をキーワードにしたほうが良いかもですね。
Note:ファイル命名規則
今回は複数ファイルをマージするために、マージするファイルの命名規則を利用しました。他の用途にもファイル名を利用することがあるので、すべてのファイル名が命名規則に従っていることは重要です。
ところがファイル命名規則を明示しておいても命名規則に従わないで自由にファイル名を設定してしまう方がほぼ必ず現れます。この場合の対応方法は２つあります。
①あらかじめテンプレートのファイル名を命名規則通りに変更したものを作成して、所定のフォルダに格納して置く。(=ガワだけ作成してOneDrive上に置いておく)作業者はファイルの中身だけを更新させる
②ファイル作成時に定期的にファイル名をPowerQueryで読み込んで変なファイルがないかチェックする。
個人的な経験としては大規模プロジェクトなら①、目が行き届く程度の大きさなら②がリーズナブルな選択な印象ですがプロジェクトの文化によるところが大きいですね。
このままマージをしたいところですが、テンプレートのファイルが含まれているので、また「Name」列の▼をクリックしてテンプレートファイルを外します。テストケースのエクセル以外に利用しないエクセルファイルがあればここでフィルタリングして対象から外します。フィルタを行うときはファイル名以外にもファイル拡張子や、フォルダ名なども利用するとよいでしょう。今回の場合はテストケースのエクセルはチームごとに作成されているフォルダに格納されているので、フォルダ名を”「チーム」を含む”という条件でフィルタしてもよいでしょう。
使用するのは「Content」列と「Name」列のみなので、他の列を削除します。
マージしたいファイルを絞り込めたので、マージしたいテーブル(テストケーステーブル)を導出します。CSVを読み込むときと同じ気持ちでContent列をポチっとしたくなりますが、エクセルファイルのマージ・読み込みを行うときは、Excel.Workbook関数を利用しましょう。
やり方はカスタム列の追加をクリック⇒カスタム列の式に「Excel.Workbook([Content])」を入力⇒OKをクリックです
するといろんな列名ががあらわれます。これらの列名のざっくりとした理解は、下記の表のような感じです。
今回テストケースは、テーブルオブジェクトにしてあるので
「カスタム.Kind」列：「Table」
「カスタム.Name」列：「テストケース」
でフィルタをします。（フィルタの方法は先ほど説明したのでキャプションを省略しています。）
必要なのは、元からある「Name」列と、先ほど追加された「Data」列（スクショではカスタム.Data列）なので、他の列を削除します。その後Data列のヘッダーの右端のボタンをクリックして、出てきたウインドウの「列名をプレフィックスとして使用します」のチェックを外してOKをクリックします。
（※今回はテストケースの全項目を取得するので、列名のチェックはすべてつけています。不要な列がある場合は、チェックを外します）
すべてのテーブルが展開され結合されました。
05-b)マージしたテストケースを予定と実績で分割する。
予実対比のためマージしたテーブルを予定と実績で分割します。実は今回のように予実を対比するグラフを作成したい場合は、予実でテーブルを分割しないとうまく予実対比のグラフは作成できないのです。
分割しないでもグラフを作成することはできるのですが、下記のイメージのように予定線はずっと未来まで引いて、実績線は実績が記入されている日付までしか引かないというグラフを作成するのは非常に難しいでしょう。（できる方法があるのなら教えてほしいです。。。）
先ほど作成したエクセルをマージしたテーブルを複製してそれぞれ予定の列と実績の列だけを残します。元のテーブルは何かあった時に実際の記入状況を確認するために残しておきましょう。
ナビゲーションウインドウをオープンします。（いきなりクエリのプレビューが変わってますが気にしないでください）
先ほど作成したクエリを右クリックして参照をクリックします。これを二回繰り返して二つクエリを作成します。複製でも同じデータを出力するクエリが発生しますが、複製とした場合は全く同じ作業（今回の場合はテストケースのエクセルのマージ作業）をするクエリを作成してしまうので、パフォーマンス的によろしくないです。参照は参照元のクエリの結果をコピーして出力するだけなのでこういうときに便利です。
作成したクエリの名前をそれぞれ「予定」と「実績」に変更します。
今回は初回実行のみを追いかけますが、実際は2回目以降の欄についてもダッシュボードを作成するので、ここをピポット解除して予定テーブル、実績テーブルでそれぞれで日付列を一つにします。
データ構造が変更され、これまであった予定日列はなくなり、「属性」列と「値」列が追加されました。属性列には元の列名、値列には元の列に入っていた値が入力されています。値列は日付情報が入っている列なので、データ型を日付に変更しておきます。同様の作業を実績テーブルにも行い、作成したクエリを読み込みます。
Note:列のピボット解除
列のピボット解除は非常に便利で、データ構造（データの持ち方）を変更し、ある種の正規化を行うことにより後続の処理がしやすいようにデータを変形します。
今回は2列をピポット解除しただけなので、いまいちすごさが分かりにくいですが、複数列に対して適応すると何列もある列が「属性」列と「値」列の2列に集約されます。列のピポット解除を行った前後でテーブルが持っている情報量は変わらないので、他の列でもっていたキー項目を利用することが可能です。
作成した予定テーブルと実績テーブルを見る要件はないのでデータモデルに追加した上で、スプレッドシートには出力しないようにします。
作成したクエリを右クリックして、読み込み先をクリックします。
接続の作成のみクリック⇒このデータをデータモデルに追加にチェック、OKをクリックします。これを予定テーブルと実績テーブル両方に行います。
05-c)カレンダーテーブルや、メンバリストなどの他の情報と予実テーブルにリレーションを作成して分析の軸を作成する
元のテーブルの準備ができたので、PowerPivotを利用して作成したテーブル間でリレーションを作成します。分析の軸（＝マスタ）となるテーブルを読み込んでPowerPivotに読み込みます。データモデルへの追加方法は2通りあります。
i)同じファイル内にあるテーブルを読み込む場合⇒該当のテーブルを選択してデータモデルに追加。
ii)別ファイルから読み込むとき⇒PowerQueryで読み込んでデータモデルに追加
カレンダーテーブルをi)の方法で読み込み、メンバリストをii)の方法で読み込んでみましょう。作成したカレンダーテーブルを管理簿と同じファイルの別シートに貼り付けます。今回はカレンダーと名付けたシートに作成したカレンダーテーブルを張り付けました。張り付けたカレンダーをクリックして、後PowerPivotタブをクリック⇒データモデルに追加をクリックします。
PowerPivotのウインドウが立ち上がり、カレンダーテーブルがデータモデルに追加されました。
次にii)の方法でメンバリストを読み込みます。データタブ⇒データの取得⇒ブックからを順にクリックします。
OneDrive上に格納されているメンバリストを選択し05-a)と同じ手順で該当のテーブルを読み込みます。その後05－b)で行ったのと同様の手順で、データモデルに追加します。
マスタとなるカレンダーテーブルとメンバリストを読み込めました。
リレーションを作成していきます。どこのシートのどこのセルでもよいのでAlt+N+Vでピポットテーブルの作成ウインドウを出します。このブックのデータモデルを使用するにチェックを入れて、OKをクリックします。
ピポットテーブルの作成画面がでたらピポットテーブル分析タブ⇒リレーション⇒リレーションシップ管理のウインドウの新規作成をクリック
予定テーブル、実績テーブルの「値」列とカレンダーテーブルの「日」列でそれぞれリレーションシップを作成します。これは両方ともに「日付」を表す列ですね。
同様に予定テーブルと、実績テーブル両方の「担当者」列とメンバリストの「名前」列でリレーションシップを作成します。こちらもやはり同じく「人の名前」を表していますね。
リレーションシップを作成できました。
05-d)DAX式を設定してグラフを作成
次に予実対比を行うグラフを作成するためのDAX式を設定します。DAXとはPowerPivotやPowerBIで利用する集計用の式です。ピポットテーブルでこういう計算できないかな？という計算をしてくれます。
Note:DAX・メジャー
さらっと書きましたが、このDAXが初心者の人は一番理解が難しいところかなと感じます。個人的にもDAXは結局なんなんだといわれるうまく説明できないのですが、ピポットテーブルでの集計時にユーザーの要件に合わせたフィルタ・集計方法を実装するための仕組みとざっくり理解しています。
言われてみれば当たり前なのですがピポットテーブルは集計時に、行と列の項目に合致するレコード毎に集計（個数の合計だったり、集計元テーブルのセルに入力されている値だったりしますが）を行っています。もともとピポットテーブルに実装されているフィルタを利用すれば集計元テーブルをフィルタ条件に従ってフィルタした上で列と行の項目に合致するレコードの集計をしてくれます。
ですが現実的にはテーブル全体にフィルタをかけた量ではなく、特定の列を特定の条件でフィルタした数量と、違う列を違う条件でフィルタした数量を集計して比較したい。ある列の総和でこの列の各値を除算したいという場面が多々あります。そんなニーズに答えるための関数がDAXで、DAXによって行われる集計結果を出力する列をメジャーというと理解しています。
どんなDAX関数があるかはMSのリファレンスから確認できます。
では実際に集計用のDAX式を設定していきますが、その前にどういう集計をしたいのかイメージを作成して置きます。（こういうドラフトを手書きでもよいので作成するとのちの作業効率が全然違います。）
今回作成したいピポットテーブルのイメージとそこから、ほしい情報を整理して必要なDAX式が何かを考えます。再掲しますが今回は下記のようなグラフを作成したいのでした。
エクセルで上記のようなグラフを作成するためにはどんなデータの持ち方をすればよいかを考えると、下記のようになりました。
すごく雑ですが、こんな感じでほしい図表を得るためにどんなピポットテーブルを作成すればよいのか考えます。念のため言っておくとこの時書いている数字はイメージ合わせのため適当に入れている数字です。準備しないといけない情報はどうやら
1）予定の累計数を数えカレンダーテーブル記載の最終日までの累積数を表示する
f）実績が記入されている最終日（これはg）の実現のために必要そうです）g）実績の累計を数えるが、実績が記入されている最終日までしか数字を表しない
の3つです。それぞれについてDAX式を設定したメジャーを作成していきます。
5-d-1）予定の累計数を数えカレンダーテーブル記載の最終日までの累積数を表示する
予定テーブルに初回実行予定の数を数えるメジャーを作成していきます。先ほどのピポットテーブルのフィールドウインドウで、予定と書いてあるテーブルを右クリック⇒メジャーの追加をクリックして、メジャーというウインドウを表示させます。
メジャーウインドウの名前欄に「初回実行予定」、数式欄に「=CALCULATE(COUNTA([値]),'予定'[属性]="初回実行結果 実行予定日")」と入力します。'予定'[値]は予定テーブルの値列という意味です。シングルクオーテーションで囲まれているテーブル名を省略すると、メジャーを作成したテーブルに存在する列名を指定することになります。
CALCULATEはCALCULATE(集計方法,フィルタ条件)でフィルタ条件に該当するものを集計方法で集計するという意味です。今回は、予定テーブルの属性列が「初回実行結果 実行予定日」のセルの個数をかぞえる(COUNTA([値])という意味です。
5-d-2）実績が記入されている最終日
先ほどのピポットテーブルのフィールドウインドウで、実績と書いてあるテーブルを右クリック⇒メジャーの追加をクリックして、メジャーウインドウを表示させます。
メジャーウインドウの名前欄に「最終実績日」、数式欄に「=MAX('実績'[値])」と入力します。この「'実績'[値]」とは実績テーブルの値列という意味なので、この最終実績日は実績テーブルの値列のうち値が一番大きいものを取得するメジャーになります。
5-d-3）実績の累計を数えるが、実績が記入されている最終日までしか数字を表しない
同じくDAX式でメジャーを設定していくのですが、ここは少し難しいです。まず最終実績日とカレンダーテーブルの日付を比較して、カレンダーテーブルの日付が最終実績日よりも前なら、属性列が「初回実行結果 実績日」でかつ、実績が記入されている日付の個数をカウントした結果を出力し、カレンダーテーブルの日付が最終実績日よりも後なら空白を出力させます。ですのでIF分で分岐を発生させつつ、特定のフィルタでカウントします。
先ほどと同様にピポットテーブルのフィールドウインドウで、実績と書いてあるテーブルを右クリック⇒メジャーの追加をクリックすると、メジャーというウインドウを表示させます。メジャーウインドウの名前欄に「初回実行実績」、数式欄に「=IF(MIN( 'カレンダーテーブル'[日] ) <= [最終実績日],CALCULATE(COUNTA([値]),'実績'[属性]="初回実行結果 実行日"),"")」と入力します。実際は数式の見やすさを優先して
=IF(
MIN( 'カレンダーテーブル'[日] ) <= [最終実績日],
CALCULATE(
COUNTA([値]),
'実績'[属性]="初回実行結果 実行日"),
"")
と記入します。
これで実績テーブルに２つのメジャーが設定されました。同じくピポットテーブルのフィールド欄の実績テーブルのところを開くと先ほど設定した「最終実績日」と「初回実行実績」の2つのメジャーが追加されていることがわかります。試しに行カラムにカレンダーテーブルから「日」列をいれ、値カラムに先ほど作成した2つのメジャーを入れてみると、思った通りに集計されています。
応用しようと思ったときには、上記で記載したDAX式の仕組みがよく分からなくても、記入されている列名を変えるだけで使用できると思いますが、一応軽く説明を入れておきます。
このままだと何パーセント消化したのかわからないので、消化率（全体のうち何パーセントを消化したのか）を計算するDAXも作成します。
実績テーブルに作成しましょう（どのテーブルで作成しても大丈夫です）。メージャーの名前を消化率、数式には「=DIVIDE('実績'[初回実行実績],'予定'[初回実行予定])」と入力してメジャーを作成します。
05-e)準備したデータを合わせて、ダッシュボードを構築する
これで準備が整ったので、ようやく肝心のダッシュボードを構築します。
まずは予実対比用のグラフを作成しましょう。ピポットテーブルの行カラムにカレンダーの「日」を、値カラムに予定テーブルから先ほど作成した「初回実行予定」、実績テーブルから「初回実行実績」をそれぞれ挿入します。
このままでは、A列にある日付に対応する実績をカウントするだけなので、値の数え方を累計に変えます。先ほど値カラムに入れた列の▼をクリック⇒値フィールドの設定をクリックします。
現れたウインドウの計算の種類タブをクリック⇒累計をクリックします。これを予定と実績両方に行います。
B列もC列も累計が表示されるようになりました。実績の方は最後に実績の記入されている5/2までしかデータが表示されていませんね。
このピポットテーブルをベースに予定と実績を比較するグラフを作成します。ピポットテーブルを選択した上で、挿入タブ⇒2-D折れ線⇒折れ線を選択します。
ちょっと見にくいので、グラフの大きさを変更して、日付の表示間隔の変更、軸名の追加などを行いました。
これだけでは、現在の消化率がわからないので消化率を表示するレポートを作成します。消化率を表示させたいセル（今回はE3セル）を選択しAlt+n+v(ピポットテーブルのショートカット)を入力します。このブックのデータモデルを使用するにチェックを付けて、ピポットテーブルウインドウを表示させます。
行カラムにメンバリストテーブルから「チーム」列を、値カラムに予定テーブルから「初回実行予定」、実績テーブルから「初回実行実績」、「消化率」をそれぞれ挿入します。消化率が少数点表示なので、パーセント表示に変更します。
値カラムに入れた▼をクリック⇒表示形式⇒パーセンテージを選択⇒OKをクリック
できた表を見やすいように
・列名を変更
・フォント変更
・文字の配置を変更
・罫線を追加しました。
を行いました。
06.ダッシュボードで遅れの原因を見つける
ダッシュボードを作成したのでこれを色んな粒度でドリルダウンをしながら、遅延の原因を見つけましょう。先ほど作成した全体の消化率をみるとAチームが先行しているとは言えどこのチームもやばそうな状況です。
もう少し細かくそれぞれのチームごとの消化状況を見ていきましょう。先ほど作成した予実対比のレポートにチームの軸を入れてみます。列カラムにメンバリストから、「チーム」列を挿入します。（邪魔にならないように、消化率のレポートを移動させました）
このままでは見にくいので、チームごとに色をそろえて、実績を実戦、予定を破線で表現して区別をつきやすくしました。
このグラフを見るにどうやら下記が言えそうだと考えました。
・Aチーム（青線）は基本的にオンスケで消化。最近何らかの理由で遅れてきたようだが、そもそも実績線が本日時点まで伸びていないので実績の記入漏れがありそう。これまでの消化状況を見るに、実績をいれてもらったら、オンスケに戻りそう。
・Bチーム（オレンジ線）は初日から大分遅延しており、遅延幅拡大中なので何らかの手段を講ずる必要がありそう。
・Cチーム（緑線）は初日の遅れを引きずっているがそれ以降はいいペースで消化、最近何らかの理由で遅れて遅延してそう。そもそも実績線が本日時点まで伸びていないので実績の記入漏れがありそう。これまでの消化状況を見るに、実績をいれてもらったら、いいラインまで行きそう
どうやら実績を入れ忘れているのであろうAチームとCチームには実績を入れてもらい、とにもかくにも消化率のやばそうなBチームのケアを考えます。対抗策を考えるためもう少し細かくBチームを見てみましょう。
Bチーム用のダッシュボードを作成して、個別に様子を見てみます。
別のシートにピポットテーブルを作成して、いろいろ分析してみます。
この時ある程度の仮説をもって軸を設定するとよいです。よくある考え方ですが、量と質両方に問題がないかをかを考えます。量とは簡単に言えば、タスク量に対して適切なチームの人数(=工数)を与えられているかです。つまり一人当たりが担当する負荷（今回はテストケース数を一つの指標に見てみます）を比べるのです。こちらもDAXを利用して進めましょう。
メンバリストにDAXを作成します。名前を「人数」、数式に「=COUNTA([名前])]」を入力します。
またもう1つメジャーを作成します、名前を「一人当たり負荷」、数式に「=DIVIDE('予定'[初回実行予定],[人数])」を入力します。
行カラムにメンバリストテーブルから「チーム」を入れ、値カラムに先ほど作成した2つのメジャーを挿入します。
遅れの多いBチームが多くのタスクを抱えているというわけではないようです。どうやら量には問題がなさそうなので、次は質を確認していきましょう。この場合はタスクの質と作業者の質両方がある考え両方を確認していきます。まずはタスクの質を確認してみましょう。もう一つ新しいピポットテーブルを作成して、行カラムにメンバリストテーブルから「チーム」、列カラムに予定テーブルから「工数」、値カラムに「一人当たり負荷」を挿入します。（前のチュートリアルで利用したデータバーを利用して比較しやすくしています。）
どうやらBチームだけが、難しいタスクを多く抱えているわけでは無さそうです。ということは作業者側の質に問題があるのでは？と思い、列カラムに「チーム」と「稼働率」を、行カラムにメンバリストテーブルから「経験年数」、値カラムに「チーム」を挿入します。
Bチームは他のチームに比べて経験年数が1年の方が多いですね。経験年数が10年目のベテランが2人いるけども、一人は稼働率50%なのであまりコミットできなさそうです。
この結果を見るにどうやらBチームはあまり経験のない人を大量に投入するも教育を行う人数が少なく、教育や研修がうまくいかないため作業に習熟していないのかもしれません。結果、作業効率が上がらず、ずるずると遅延をしてしまっているという可能性があります。
もし上記の仮説が間違っていないなら、チームの体制に問題がありそうということです。Bチームにふられているタスクと作業者のレベルにアンマッチがあるということなので、この場合は作業者のレベルを上げるか、タスクを振りなおすかが、打ち手となりそうです。パッと思いつくままに書くと、
・チーム編成を組み替えて1年目の比率が各チームで同じくらいになるように配置しなおす。そして教育・研修がうまくいくようにしてチームメンバー全員の能力が上がるようにする。（全体を平準化して底上げを行う）
・チーム編成は変えず、BチームのタスクをAチーム、Cチームに振り直す（タスクの振り直し）
・Bチームに3年以上経験者を２，３人入れて1年目比率を下げる（Bチームに増強）
等が考えられます。実際の場面ではここまで原因分析ができたら、
・もう少し原因の深堀を行う（本当にBチームの1年目の人たちが作業に習熟していないのが原因なのか等）
・ディスカッションを行って対策をいくつかピックアップして何らかの手を打つ
の両方を行うことが多いような気がします。エクセルのダッシュボードではあくまでこうなんじゃないかな？という仮説しか生まれませんので、実際にBチームの人にインタビューをして、なにかやり方が悪いんじゃないのかとか、本当に作業効率が悪いのかといった裏を取りに行きますし、考えた打ち手についても、プロジェクトの状況があるのでこのうち手は実行できるけど、これはできないといった判断を行いながら取捨選択をしていく必要があります。ここら辺になるとエクセルのダッシュボードでは見えないことを追いかけていくことになります。
長くなりましたが、一連の業務のながれを実施してみました。今回はテストが始まってある程度の時間がたっている前提のデータを利用しましたが、実際はテストを開始するその日（あるいは前）からこのようなダッシュボードを作成して予実を管理、なにがボトルネックで何を改善すればよいのか考えて、手を打つ⇒その介入結果をみてまた打ち手を考える。ということをテストが完了するまで毎日続けます。
今回はチュートリアルなので、結論ありきでデータを作りましたが、実際の業務では原因がよく分からず、あれやこれやで分析するというのも大事になってきます。そのため今回のようにこういう視点で見ればよいのでは？と思ったことを素早くダッシュボードで可視化できる能力というの結構重要です。というのも、個人の勘の領域にある考えをデータによって支持することができるようになるからです。
07.終わりに
これで今回のチュートリアルは終わりです。なるべく短くしたいと思っていたのですが、すごい分量になってしまいました。。。。ご意見や感想、特にこういうテーマでチュートリアル作ってほしい等あれば大歓迎ですので、Twitter等でご連絡いただければ幸いです。
最後まで読んで頂いき、ありがとうございました。みなさんのお役にたったなら幸いです。
この記事が気に入ったらサポートをしてみませんか？