BDAstyle

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

円グラフを使った「タイムスケジュール」の描画 with Excel 2013 or later

アウトライン

1日のスケジュールを(24時間,あるいは任意の時間の)時計を模した円グラフに重ねて示す,日常でよく目にする表現の方法をエクセルで実現するためのハウツーです。

例示のための状況設定として,ある技術テーマに関する外部講師による勉強会のスケジュールを描きたいとします。この勉強会では,

  1. 9:00~10:00 ガイダンス(1h)
  2. 10:00~12:00 講義(2h)

を皮切りに,お昼の休憩を挟んで,

  1. 13:00~15:00 グループワーク(2h)
  2. 15:00~17:00 プレゼンテーション(2h)

が予定されています。またこれらの過程をおえたのち,懇親会に移る計画があります。

ここでは,これらの予定をサークルの上で図示するための,2つの方法を扱いたいと思います。

ひとつ目は,シンプルなtype 1のそれと,

24時間 円グラフ エクセル type1

ふたつ目は,レゴブロックを並べたような,ポップな印象のtype 2です。

24時間 円グラフ エクセル type2

なお,この作例では1時間ごとの切りのいい区分でシートを設計しています。30分単位,ないしは15分単位等より細かな区切りを必要とするときは,各typeの後段の「拡張」部分でそのtipsについて触れています。

以下,エクセルでタイムパイチャートを作るための具体的な手続きです。

工程

type 1

type1-Step 1

シートに必要なデータを作成します。

各列の役割については,以下の通りです。

  • 「時」/0:0023:00まで等間隔で作成。「消費時間」や「ラベル」を作るための目安として使用。
  • 「1hスライス」/円グラフに“時計の文字盤”を収めるため,「時」と同数の“スライス”をつくるにあたってのダミー。
  • 「消費時間」/0:00を基点に,何もしないことを含む時間の束を指示するための列。この合計は,必ず「1hスライス」の要素の数(この作例では24)と一致する必要あり。
  • 「ラベル」/「消費時間」で費やす時間の内容。表示の必要がない場合は入力不要。

24時間 スケジュール エクセル 円グラフ type1

type1-Step 2

「1hスライス」列を選択して“ドーナツ”グラフを作成し……

24時間 スケジュール エクセル 円グラフ type1

この系列にデータラベルを追加します。

24時間 スケジュール エクセル 円グラフ type1

ラベルの内容を「分類名」とし……

24時間 スケジュール エクセル 円グラフ type1

グラフを下図のような状態としておきます。

24時間 スケジュール エクセル 円グラフ type1

type1-Step 3

「消費時間」列をクリップボードにコピーしておき……

24時間 スケジュール エクセル 円グラフ type1

グラフに直接ペーストします。

―――たった今追加した系列に対して,先と同様データラベルを加えます。

24時間 スケジュール エクセル 円グラフ type1

ラベルの内容を「セルの値」とし,「ラベル」より下の内容を指定します。

24時間 スケジュール エクセル 円グラフ type1

この段階で,グラフは下図の通りとなります。

24時間 スケジュール エクセル 円グラフ type1

type1-Step 4

任意の系列を選択した状態から「グラフの種類の変更」ダイアログを呼び出して,「1hスライス」系列をに,「消費時間」系列を2軸にした上でドーナツに変更します。

24時間 スケジュール エクセル 円グラフ type1

360/24=15°の内角で区切られた部屋に格納した数字を,(この時点において見た目の上ではズレているので)しかるべき位置へと動かします。

具体的には,「1hスライス」系列の“グラフの基線位置”を部屋の内角の半分だけ,すなわち7.5°と指定して調整します。

24時間 スケジュール エクセル 円グラフ type1

先の作業を終えたところで,グラフは下図のようになります。

24時間 スケジュール エクセル 円グラフ type1

type1-Step 5

「1hスライス」系列の塗り色・線の色を取り除き,

24時間 スケジュール エクセル 円グラフ type1

「消費時間」系列のドーナツ中央の穴を任意の大きさに調整します。

24時間 スケジュール エクセル 円グラフ type1

その他適宜書式を整えて,type 1グラフの完成です。

24時間 スケジュール エクセル 円グラフ type1

type1-Step 6拡張1)データラベルの位置

参考までに,「1hスライス」系列のデータラベルを“外部”に出すと,下のような外形に変えることができます。

24時間 スケジュール エクセル 円グラフ type1

type1-Step 7拡張2)分区切りのスケジュールでも描画を可能にしたい場合

また,1時間ごとの区切り以外でスケジュールを描画したいときは,「消費時間」列の値を

時+(分/60)

で入力します。この場合,この列の合計の辻褄さえ合えば(=「1hスライス」列の要素の総数と合計が一致する)描画に関して支障はないので,「時」列との整合はアバウトにとっておけばOKです。

24時間 スケジュール エクセル 円グラフ type1

type 2

type2-Step 1

シートに必要なデータを作成します。

AC列については,type 1のそれと同じですので説明を省きます。残る列は,

  • 「カウンタ」/数字を円グラフの最上部から時計回りで配置するために調整したダミー。0を開始値に1ずつ減少していく連続データ。
  • 「1hスライスX」/時計の文字盤様に表示する,「カウンタ」をもとに計算した個別の数字の配置場所についてのX座標。
  • 「1hスライスY」/同じくY座標。
  • 「ラベル2」/文字盤の数字そのもの。つまり,0から1ずつ増えていく連続データ。

24時間 スケジュール エクセル 円グラフ type2

「1hスライスX」「1hスライスY」両列について,下式を入力し表の最下行までコピーします。

E2 =COS((2*PI()*D2)/COUNT(D:D)+1/2*PI())
F2 =SIN((2*PI()*D2)/COUNT(D:D)+1/2*PI())

24時間 スケジュール エクセル 円グラフ type2

「ラベル2」に0, 1, 2, …と連続データを作ります。

24時間 スケジュール エクセル 円グラフ type2

type2-Step 2

「消費時間」列を選択し,“ドーナツ”グラフを作成します。

24時間 スケジュール エクセル 円グラフ type2

つづけてデータラベルを加え,

24時間 スケジュール エクセル 円グラフ type2

データラベルの内容として「ラベル1」より下の範囲を指定します。

24時間 スケジュール エクセル 円グラフ type2

これを終えたとき,グラフは下図の通りです。

24時間 スケジュール エクセル 円グラフ type2

type2-Step 3

「1hスライスY」列をクリップボードにコピーして……

24時間 スケジュール エクセル 円グラフ type2

グラフに直接ペーストします。

―――たった今追加した系列を選択した状態から「グラフの種類の変更」ダイアログを呼び出して,「1hスライスY」系列を散布図に変更します。

24時間 スケジュール エクセル 円グラフ type2

上の作業を受けて,あらたにこの系列(「1hスライスY」)のX範囲を指定することが必要となります。具体的には,「系列の編集」ダイアログを呼び出して,“系列Xの値”に下図の範囲(「1hスライスX」列)を指定します。

24時間 スケジュール エクセル 円グラフ type2

この段階において,グラフは下図のようになっているかと思います。

24時間 スケジュール エクセル 円グラフ type2

type2-Step 4

散布図の縦・横軸とも最小値・最大値をそれぞれ順に-2,2に変更します。

24時間 スケジュール エクセル 円グラフ type2

「消費時間」系列のドーナツの穴の大きさを50%に変更します。

このとき,散布図のマーカーは軸の絶対値にいう1の位置にハマるよう計算されているので,ドーナツの穴を50%としたことを受けるかたちで,マーカーが内側の円周とちょうど重なる位置にやって来ます。

24時間 スケジュール エクセル 円グラフ type2

グラフのサイズを任意の大きさ(用途においてユーザーが想定するサイズ)に調整します。

24時間 スケジュール エクセル 円グラフ type2

type2-Step 5

マーカーサイズを任意の大きさに増やします。

24時間 スケジュール エクセル 円グラフ type2

「1hスライスY」系列にデータラベルを追加して,これの内容として「ラベル2」より下の範囲を指定します。

―――なお,ラベル位置は原則“中央”としておきます(手作業によるラベル位置の微妙な調整は疲弊するので)。

24時間 スケジュール エクセル 円グラフ type2

―――この作例と同様のデザインを採る場合,背景色と同じ色でマーカーを彩色します。

24時間 スケジュール エクセル 円グラフ type2

その他適宜書式を整えて,type 2グラフの完成です。

24時間 スケジュール エクセル 円グラフ type2

type2-Step 6拡張1)データラベルの位置

参考までに,「1hスライスX」「1hスライスY」に半径r(この例では2)を乗じてやれば,マーカーを外周上に配置することも可能です。

24時間 スケジュール エクセル 円グラフ type2

type2-Step 7拡張2)分区切りのスケジュールでも描画を可能にしたい場合

type 1と同様,「消費時間」列の指定を時+(分/60)でおこないます。

24時間 スケジュール エクセル 円グラフ type2

type 2の場合,この分単位の描画についてはtype 1より優れる部分を持っています。この場合,たとえば以下のようにとりわけ分単位までを明記しておきたいポイントを別途指定(この例では9:30, 13:30, 18:30)し,

24時間 スケジュール エクセル 円グラフ type2

「minX」「minY」列に下式を入力して,

J2 =2*COS((2*PI()*-I2)/COUNT(D:D)+1/2*PI())
K2 =2*SIN((2*PI()*-I2)/COUNT(D:D)+1/2*PI())

これら「minX」「minY」列を(散布図の)あらたな系列としてグラフに追加していきます。

24時間 スケジュール エクセル 円グラフ type2

これにより,円の外周上に目的のポイントを任意に配置させることができます。そして先の作例と同様の書式設定を加えていけば,ある意味タイムパイチャートの最大の弱点たる“分単位の測りづらさ”の部分を緩和することが可能です。

24時間 スケジュール エクセル 円グラフ type2

その他の参照