BDAstyle

ビジネスデータ分析ツールの作成 with Excel

予定の追加・削除や変更ができるタイムテーブルを,Excelグラフで描画する -会議室の予約管理を例として

アウトライン

1 日のタイムテーブルをExcelのグラフ機能で描画します。

例示のための状況設定として,今回,ある会社の2つの会議室が予約制になったとして,その管理者が予約の現況を広く社内に提示する必要を負ったとします。

具体的に,管理者は次のような構成の図でそれを実現したいと考えました。

タイムテーブル 完成図

もっとも,ここには予定の追加・削除や変更が容易に可能で,管理の煩雑化を防ぐしくみも必要でしょう。

以下,そうした点を顧みながら,Excel でタイムテーブルを組み立てていきます。

工程

シート部分

Step 1ブックの構造

1 枚のシートにあれこれ詰め込みすぎるのもスマートさに欠けるので,ここではシートをいくつかに分けていきたいと思います。

具体的には,3 枚のシートを利用します。各シートの名称と役割については,以下の通りです。


ブックの構造 シート名:INPUT, COORDINATES, OUTPUT 役割:入力, 座標計算, 出力(グラフ)

Step 2INPUTシート:入力部

「INPUT」シートです。

ここでは画面キャプチャの都合上,1 日の予定の上限を10個の予定(以下「ユニット」)と定めます。実際には運用上想定されるユニット数の上限をカバーできる範囲で,余裕を持たせたシートの設計をしておくことが肝要です。

ということでこのシートには,2 部屋とも上限いっぱいの10個のダミーのユニットを,次のような構成で空行を開けずに※並べた状態から作図を進めたいと思います。

※意図せぬ作図ミスを防ぐため,運用も同様のルールでおこないます。

DL

元データ ROOM1, ROOM2 各10件の予定

なお上図にいう「番号」は一意の数字で作成し,これ以後のプロセスでは,この数字をそのままプロット上のX座標として利用します。

Step 3COORDINATESシート:処理部

「COODINATES」シートです。

下図のような見出しを用意しておきます(これは2部屋分の場合の見出しです)。直下の表をコピペすると簡単です。

All Day Units Units
X Y Num M1x M1y num L1x L1y Caption Num M2x M2y num L2x L2y Caption
X-Axis Labels
X Y Caption

scrollable

COORDINATES・元表

部屋の名称,および部屋ごとのユニットの数を「INPUT」シートより引用,ないしは数えます。

―――具体的な計算式は次のとおりです。

E1 =INPUT!C2
G1 =COUNTA( INPUT!A:A )-1
M1 =INPUT!G2
O1 =COUNTA( INPUT!E:E )-1

scrollable

Step 4COORDINATESシート・「All_Day」「X-Axis_Labels」表

タイムテーブルでは“未占有” “埋まってない”時間帯がハッキリと区別可能なことが望まれるので,ベース部分に会議室の開室時間帯(この例では8:00―20:00とします)をあらわす線を描いておこうと思います。まずはこの座標をつくります。

この系列の呼称を以下「All_Day」とし,下図の彩色セルに次の計算式を入れておきます。なお2部屋分のベースを描くにあたって,下図の空白行を隔てた2つのブロック(セルA4:B8)を使用しています。

A4, A5 =INPUT!$B$2
A7, A8 =INPUT!$F$2
B4, B7 利用可能区間の先端(開室時間)のシリアル値 ex.0.333333
B5, B8 利用可能区間の終端(閉室時間)のシリアル値 ex.0.833333

scrollable

つづいて部屋名の表示位置に関する座標を作成します。

A14 =A4
A15 =A7
B14 =MIN( B4, B7 )
B15 =MIN( B4, B7 )

scrollable

部屋名を転記します。

C14 =E1
C15 =M1

scrollable

Step 5COORDINATESシート・「In_Use」表

1 部屋目の諸要素についての座標をつくります。

まずは同値を3回繰り返すかたちで連番を作成します。終端はStep 2で決めたユニットの数です。

E4 =QUOTIENT( ROW()-4, 3 )+1
M4 =E4

scrollable

“占有された” “埋まっている” 時間帯をあらわす棒,以下「In_Use」と呼ぶ系列のX座標を求めます。

F4 =IF( E4>G$1, NA(), INPUT!B$2 )
N4 =IF( M4>O$1, NA(), INPUT!F$2 )

またこれを直下に1つコピーします。

scrollable

「In_Use」系列のY座標を求めます。

G4 =IF( E4>G$1, NA(), OFFSET( INPUT!A$5, E4, 1 ) )
G5 =IF( E5>G$1, NA(), OFFSET( INPUT!A$5, E5, 2 ) )
O4 =IF( M4>O$1, NA(), OFFSET( INPUT!E$5, M4, 1 ) )
O5 =IF( M5>O$1, NA(), OFFSET( INPUT!E$5, M5, 2 ) )

scrollable

下図強調範囲を表の最下行までコピーします。

scrollable

Step 6COORDINATESシート・「In_Use_Labels」表

これ以後の座標は会議の名称や利用者等の文字列を表示するための,データラベルを目的としたものです。以下この系列を「In_Use_Labels」と呼びます。

Step 2 で決めた終端まで,通常の連続番号をつくります。

scrollable

X・Y 座標を用意し,データラベルの内容を「INPUT」シートより引用します。

I4 =IF( H4>G$1, NA(), INPUT!B$2 )
J4 =IF( H4>G$1, NA(), (OFFSET( INPUT!A$5, H4, 2 )-OFFSET( INPUT!A$5, H4, 1 ))/2 +OFFSET( INPUT!A$5, H4, 1 ) )
K4 =IF( H4>G$1, NA(), OFFSET( INPUT!A$5, H4, 0 ) )
Q4 =IF( P4>O$1, NA(), INPUT!F$2 )
R4 =IF( P4>O$1, NA(), (OFFSET( INPUT!E$5, P4, 2 )-OFFSET( INPUT!E$5, P4, 1 ))/2 +OFFSET( INPUT!E$5, P4, 1 ) )
S4 =IF( P4>O$1, NA(), OFFSET( INPUT!E$5, P4, 0 ) )

scrollable

グラフ

Step 7COORDINATESシート・「All_Day」系列でグラフをつくる

下図強調範囲を選択し,グラフ散布図(直線)を挿入します。

scrollable

A3:B8

「All_Day」系列を描画したこの時点で,グラフは下のとおりの状態です。

Step 8COORDINATESシート・「X-Axis_Labels」系列でグラフをつくる

先のグラフに下図強調範囲の赤をX,青をYとして「X-Axis_Labels」系列を描画(=追加)したうえで,この系列にデータラベルを加えます。このとき,データラベルの内容として緑範囲を指定します。

scrollable

A14:A15, B14:B15, C14:C15

「X-Axis_Labels」系列を描画したこの時点で,グラフは下のとおりの状態です。

Step 9COORDINATESシート・「In_Use」系列をグラフに追加

先のグラフにあたらしい系列「In_Use」を追加します。

このとき,系列のXには下図の赤い強調部分を同時に,同じくYには下図の青い強調部分を同時に指定します。左右の表で選択範囲が非対称となる点に注意です。

scrollable

F4:F33,N4:N32, G4:G33,O4:O32

「In_Use」系列を追加したこの時点で,グラフは下のとおりの状態です(下図では青い線の上にグレーの線があたらしく引かれています)。

Step 10COORDINATESシート・「In_Use_Labels」系列をグラフに追加

先のグラフにあたらしい系列「In_Use_Labels」を追加します。

このとき,系列のXには下図の赤い強調部分を同時に,同じくYには下図の青い強調部分を同時に指定します。

さらに重ねて,この系列にデータラベルを追加します。データラベルの内容には,下図の緑の強調部分を同時に指定します。

すべて左右の表で選択範囲が非対称となる点に注意です。

scrollable

I4:I14,Q4:Q13, J4:J14,R4:R13, K4:K14,S4:S13

「In_Use_Labels」系列を追加したこの時点で,グラフは下のとおりの状態です。

Step 11書式の設定:線色・線幅・線の先端

系列「All_Day」および「In_Use」について,線色線幅線の先端の設定をおこないます。

このとき,線の先端については,フラット以外の選択肢は厳禁です。

線色:任意 線幅:任意(この例では100pt)

この時点で,グラフは下のとおりの状態です。

Step 12書式の設定:縦軸目盛

グラフ縦軸の書式設定にて,開室時間帯の先端(=最小値)と終端(=最大値)をシリアル値で指定します。

あわせて任意の時間ごとの仕切り線(=;下図では1時間)もシリアル値で指定します。

上の設定を終えたら,軸を反転してグラフの天地を入れ替えます。

主:0.041666667(1h)

表示形式を時刻表記に変換します。

この時点で,グラフは下のとおりの状態です。

Step 13書式の設定:ラベル位置と線の色

系列「X-Axis_Labels」ラベルの表示位置をに指定しなおして,これを項目軸ライクに利用します。

系列「X-Axis_Labels」「In_Use_Labels」のなしに設定します(不可視化)。

Step 14書式の設定:横軸の設定

横軸の表示範囲がアンバランスなので,適当な範囲を指定してこれを修正し(下図では 0.5-2.5),

不要となった軸のラベルを不可視化,つまりラベルの位置なしにします。

完成

Step 15OUTPUTシート:出力部

作成したグラフを,あたらしいグラフシート「OUTPUT」に移動させます。

細部に任意の設定を加え,Excel グラフによるタイムテーブルの完成です。

運用

Step 16グラフの再描画

「INPUT」シートの内容を書き換えると「COORDINATES」シートが再計算され,原則として「OUTPUT」シートのグラフも追従し変化します。つまり予定の変更などに伴っていちいちグラフ側に調整を加える必要を回避できます。

参考までに「INPUT」シートの「ROOM1」の予定を午後,同じく「ROOM2」の予定を午前に関してすべて削除してみると,グラフは下のようなかたちに自動的に変化することが確認できます。

拡張

Step 17個別に時刻を表示する/仕切り線を追加する

系列「In_Use」にデータラベルを追加すると,ユニットごとに開始・終了時刻を表示させることも可能です。下図はラベルを中央位置に配した例です。

またこの「In_Use」系列に任意のマーカーを適用することで,仕切り線としての役割を与えることができます。ユニット間に“間がない”シチュエーションがデフォな場合などには便利です。たとえば下図では,組み込みのマーカーでなく,線幅とほぼ同幅の図形「直線」を適用しています(cf. 層別散布図)。

頁頭の作例のように,これらの設定は両立させることができます。

Step 18BugFix

この作例はExcelのバージョン 1902(ビルド 11328.20222)を利用したものですが,Step 16 での説明に「原則として」と加えたとおり,筆者周辺の環境ではデータを削除・追加した場合などにグラフ側の修正が必要な場面があります。

たとえばStep 16の状態でExcelを終了してから再度起動し,あらためて当初のダミーデータで予定をフルに埋めようとしても,下図のように追加された部分にはデータラベルが表示されていません。個人的には,Excel を再度起動した段階で発生するトラブルのように見ています。

この現象はそもそも仕様なのか,はてはバグなのか不明ですが,対処としては「In_Use_Labels」のデータラベルの書式設定から,セルの値チェックボックスをいったんOFFにしてから再度ONにし,さらにラベルテキストのリセットを掛けることで回復させることができます。

もっともマクロが利用できる環境なら,たとえば次のようなコードを組んで,「OUTPUT」グラフシートをアクティブにしたタイミングで上記の一連の動作が自動的に掛かるしくみを加えたほうが解決はシンプルです。

Private Sub Chart_Activate()
' DataLabels BugFix
Dim timeTable As Chart
Set timeTable = Application.Charts("OUTPUT")
With timeTable.SeriesCollection("In_Use_Labels").DataLabels
    .ShowRange = False
    .ShowRange = True
    .AutoText = True
End With
End Sub

その他の参照