Google Workspace でつくる業務日誌002
行事予定表とその影武者を作っていく
～情報ダッシュボード・サイネージを目指して01～
見た目はありふれた行事予定表ですけど、自分なりのカラクリを仕掛けたいと思っています。
一般会社・法人でも使えるものを目指しますが、サンプルデータは古巣で使っていたようなものでないとサンプルを作れないので、学校用です。ご了承ください。他業種のデータって興味ないですか？
まずはおもての行事予定表から
なんの仕掛けもない行事予定表。一つの行事に1行を使います。1日にいくつもの行事を入れていくスタイルもありますが、行事予定って動くじゃないですか。これなら、予定が変わっても、日付や時刻を変えるだけで対応できます。
じつは仕掛け、有りました。
…日付が同じだと、「4/1(金)」が縦にズラッと並んで、次の日と見分けにくいので、前の行と日付が同じ時は色を薄くしています。手でやるなんて論外です。こうします…
➔メニュー「表示形式」から「条件付き書式」を使います。
範囲は「A5:A」と指定。
ルールは「カスタム数式」を選び、
数式には「=A5=A4」とします。
地は無指定、字は薄い色を選びます。
でもこれだけじゃ、わざわざネットに公開する意味ないです(汗)。
つくりたいのは、行事予定と出張予定と出来事の記録、業務連絡その他の情報を統合したダッシュボードです。このダッシュボードを大型ディスプレイで表示して近頃流行りのサイネージにしたいのです。
同じ日のスケジュールを1つのセルにまとめたい！
ダッシュボードのデータ表（この後作ります）は、1日毎に1行なので、1日に行事が2つ以上あるときは、一つにまとめなければなりません。これも、手でやるなんて以ての外なので、GASを使えないので、関数を使います。
どうやろうか…最初、いろいろ、ずっと考えました。で考えついたのが、下のやり方です。トリッキーですが、ちゃんと動きます。
日まとめシート（裏シート）を作る
「行事入力」シートのコピーを作ります。
コピーした表は、キツイ仕事を請け負う影武者・ゴーストライターのようなシートです（実際、完成後は非表示推奨です）。
元のシートが「行事入力」なら「行事入力のコピー」と名前が付きます。長ったらしいので「＃行事入力」と変えます。
「＃行事入力」シートのA4セルに数式を入れます。
=query('行事入力'!A4:D, "select * where B is not null order by A,C",1)
いきなりの query関数
出ました！query関数です。これは、
「行事入力シートのA～D列から空じゃないデータをもっておいで。それでもって、日付、時刻順にならべなさい」
という意味の命令です。
と、ここでエラーが出ます。
なぜエラーかというと、query関数は複数セル（つまり範囲）に値を出力する関数なので、指定された「A4:D」の範囲になにか値が存在していると、ぶつかってしまい、エラーになります。
なので、「A4:D」つまり、A4セルから右4列、下ぜんぶのセルでDeleteを押してセルを空白にしてください。
Shift+Ctrl+↓ などを使って効率よくやりましょう。
…空白のシートに式を打てばよいのですが、学習のための「やらせ」です。ご了承ください。
さてこれで、「＃行事入力」に行事のデータがコピーされます。コピーですが「影」です。映し出されているだけ。実体のあるデータではありません。表示するだけだからいいんです。
これをもとに日まとめデータを作っていきます。
ちなみに、query 関数に並べ替えまでやらせてます（「order by …」がそれです）ので、元のデータである「行事入力」シートが日付順になっている必要がありません。思いついたら、追加していくだけでOKです。これすごくないですか？
また、やることは決まっているんだけど、日程が決まっていないというものも、とりあえず入力しておけば、忘れることがないですよね。書いてないと忘れちゃう。
カラム「日付２」
E列に「日付２」を設定します。
何をしたいのかというと、A列には日付が縦にずらっと並んでいますが、同じ日付は無視して、次の行から日付が変わるという際（きわ）の日付だけを表示したい。
そして、その行から vlookup関数で、その日の行事をまとめて引用しようと思うからです。E4セルに、
=arrayformula({"日付2"; if(A5:A="","", if(A5:A=offset(A5:A,1,0),"",A5:A) )})
と書きます。
arrayformula 関数
アレイフォーミュラ関数といいます。1つのセルに設定すると、指定により右方向、下方向に同じ計算式を適用してくれる、有り難い、有り難い関数です。
そしてまた、「｛｝」「；」にも意味があって、「｛｝」は「配列」を表します。配列はデータの集合です。
ためしに、どこかのセルに
「={0,1,2,3,4;5,6,7,8,9;10,11,12,13,14;15,16,17,18,19}」
…と、全て半角文字で打ってみてください。
「，」（カンマ）は横に並べ、「；」（セミコロン）は縦に並べという意味になります。
そういうわけで、「{"日付2";」で「日付2」という列のタイトルを指定し、「；」以下で範囲「A5:A」（A5より下のA列全部）に当てはめる計算式を指定しているのです。
offset関数
いきなりヘビーな関数を連発してしまいますが、次はオフセット関数です。
if関数はきっとおわかりでしょうから、解説はいいですよね。
if関数のなかは、「ひとつ下のセルの日付がもし同じだったら」という意味で、「ひとつ下のセル」を
offset(A5:A,1,0)
で表現しています。A列の任意のセルの「一つ下」というのを第2引数「１」で表しています。第3引数の「０」は「右側に０ずれて」（つまり同じ列）という意味です。
「A5:A,１,１」なら「A列のあるセルから下に１、右に１ずれたセルを調べておいで」（つまり右斜下となり）という命令です。
以上をまとめますと、E列のif()式は
A列の日付を下に見ていって、一つ下の日付が同じだったら空白を。次の行で日付が変わるようだったら、日付をE列に書き込んでね。
という意味です。
F列は、B列（予定行事）、C列（時刻）、D列（場所）を「＆」（文字列連結）で一つのセルにまとめています。
H列から先に説明します。
上の図のF列にまとめられている4月1日の行事はF列の何行目から何行目までか、ここでは5行目から12行目までですね。それを「F5:F12」という文字列で表しています。これをG列に入れる関数（textjoin）で使います。
4月1日が最初に出てくる行番号をmatch関数で見つけています。4月1日の最後の行は行番号を返してくれるrow関数で取得しています。それらの数字に列名の「F」をくっつけて「F5:F12」を生成しています。そしてこれをG列のtextjoin関数で使います。
G列ですが、ここが今回のキモでありまして、上の図では、ここで4月1日のすべての行事を1つのセルにまとめています。
textjoin関数は、指定した範囲のセルの内容を指定した文字、ここでは「char(10)」（改行文字）で連結します。
そして、その「範囲」はH列に書いてありますから、indirect関数で引用しています。
------------------------------
《注意》textjoin関数はarrayformula関数と仲が悪いので組み合わせて使えません。なので、G列だけはG5から下にずっと計算式を埋め込む必要があります。かっこよくありません。ネットで調べましたが、これは、しょうがないんだそうです。残念。こういうのを「おとなの事情」というのでしょうか。大人の事情には大人の対応を。
------------------------------
これを、皆さんよくご存知のvlookup関数をつかって必要なところに呼び出せばいいのです。
たったこれだけのシートなのに、解説すると字数がどんどん増えていきますね。
次は、この「行事予定」を呼び出す一つ上役の表を作っていきます。
つづく
今回の関数・テクニック
「条件付き書式」（「カスタム数式」）
影武者シートを作る
query関数
arrayformula関数
「｛…,…;…｝」（波括弧＝配列）
offset関数
if関数
match関数
textjoin関数
indirect関数
vlookup関数
この記事が気に入ったらサポートをしてみませんか？