葛飾でひっそり暮らすIT屋の G Suite メモ

Google Apps で快適業務ライフなノウハウを伝えます

【Google Spreadsheet】タイムスケジュール表の作成を簡単にする3つの関数

【追記】

Spreadsheet や Excel では時間を計算すると誤差が出るようです。

それにより MATCH や VLOOKUP で時間をキーにした検索がうまくいかないことがあります。

回避策は下記の画像のように、比較する時間をテキスト化してから使うといいでしょう。

【追記終わり】

こんにちは、最近イベントの仕切りを任されててんやわんやしています。

イベントを運営するときに必要になるものの代表として、タイムスケジュールの作成があります。

これ、手打ちでポチポチ作っていくと非常に面倒なんですよね。修正入ったりすると全部手で打ち直しとか地獄としか思えません。

そこで、Google Spreadsheet を使って簡単に作ってしまおうというのが発端でこのエントリーを書くことになりました。

今回使用する関数は

  • VLOOKUP
  • OFFSET
  • MATCH

の3つです。

オマケで IFERROR も使っていますが、表示の調整用なのでまあまあ。

大まかな作成の流れは動画でどうぞ。

構成

このタイムスケジュールを実現するためには4つのシートを作成します。

  • 設定シート
  • 各項目への時間の割当
  • スケジュールのリスト
  • タイムスケジュール

といったところです。

全部同じシートに収めてもできるのですが、ちょっとごちゃごちゃするのでシートを分けます。

では、各シートの内容を確認しましょう。

設定

「設定」シートでは下記のような表を作ります。

項目
時間単位 0:05
開始時刻 10:00

「時間単位」は最終的なタイムスケジュール表の時間の刻みを設定します。大抵のイベントでは5分で刻んでおけばちょうどいいんではないかと思います。

「開始時刻」はそのままの通り、イベントの開始時刻を設定します。

時間割当表

演目に対して割り当てる時間を表にしておきます。

演目 時間
前座 0:30
スイカ割りMAX 0:45
正拳突き一本勝負 0:45
カリフラワー投げ 0:30
脳髄ビリビリ対決 1:00

ここまではなんてことないですね。

タイムスケジュール

ここから関数が出てきます。

A 列は演目を順番に並べていくだけです。

B 列で色々と計算していきます。

演目 開始時刻
前座 10:00
スイカ割りMAX 10:30
正拳突き一本勝負 11:15
カリフラワー投げ 12:00
脳髄ビリビリ対決 12:30
カリフラワー投げ 13:30
正拳突き一本勝負 14:00
スイカ割りMAX 14:45
終演 15:30

まず、B2 セルでは設定シートの「開始時刻」を参照します。

='設定'!B3

2行目以降になると VLOOKUP 関数が登場します。

=B2+VLOOKUP(A2,'時間割り振り'!$A:$B,2,0)

上の数式は B3 セルのものです。

まず、前の演目の開始時刻を基準にするので B2 セルを参照します。

そこに、時間を足すのですが、前の演目に割り当てられた時間を足すことで B3 の開始時刻を取得できるので、VLOOKUP の第一引数は A2 セル、一つ前の演目名を指すことになります。

後は時間割当表から該当する値をとるよう書いているだけです。

時刻表

最後に、時刻を並べた表に演目を記入していきます。

時刻 演目
10:00 前座
10:05
10:10
10:15
10:20
10:25
10:30 スイカ割りMAX
10:35
10:40

この表では B2 セルに下記の計算式が入っています。

=IF(C2<>"", IFERROR(D1+OFFSET($B:$B,MATCH($C1,$A:$A,0)-1,0,1,1),""),"")

時間を基準に、開始時刻に該当する演目をタイムスケジュールシートからとってくるのですが、スケジュールシートでは左に演目名、右に時間になっているので VLOOKUP が使えません。

そこで OFFSET と MATCH を組み合わせて、検索キーで指定する列の左にある値を取るようにしています。

これでタイムスケジュールが完成です。

タイムスケジュールって、難しい作業ではないのですがとにかく手間がかかるんですね。

変更があったときなどはいちいち時刻表から演目探したり、入れ替えたりしなければいけません。

今回作った表ならばタイムスケジュールの時間や、割当時間表を変更すると自動的に時刻表の方も変わるのでだいぶ楽になると思います。