はじめに
ゴール
「オフィス出勤表」を参照した「座席表」を作成することを目指します。
< 座席表動作イメージ>
-
座席表_サンプル
※スマホだとうまく動作しないことがあるようです。
サンプルが欲しい方は、ご自身のgoogleアカウントで一旦上記リンクを閲覧のうえ、ご自身のGoogleドライブ上で複製いただければと思います。
作成背景
昨今の情勢の影響で、勤め先ではオフィスの出社人数を制限しています。しかし日によっては席が近い人の出勤が重なってしまうことがあります。空いている席に移動しても皆の出社時間がバラバラなこともあり、移動した先でまた密になってしまい、また移動を、、という面倒な状況が発生していたため、一目で今日出社する人の座席が判断できる画面が(さくっと)欲しく、Googleスプレッドシートで作成することにしました。
業務の隙間時間でサクッと作ることを目的としたため、実装の甘いところや、有識者からするともっとうまくできるとお叱りをうけるところもあると思います。ご了承ください。皆様の安全とご健康をお祈りしています。
Googleスプレッドシートの良いところ
- チームで共有できるところ。
- 簡易的に、ぽいシートが作れること
(いつ使わなくなるかわからないものの為に、わざわざWebアプリまで作りたくない)
Googleスプレッドシートの良くないところ
- かゆいところに手が届かない。
所々で記載しますが、若干泥臭いところがあります。あくまで、一時的に使う目的のものとして捉えてください。
つくりかた
準備
- 今回参照する出勤表はこちらです。なお、出勤表には特に触りません。
- 座席表を用意します。実際に手を動かしながら試したいという方もこちらのシートをご利用ください。(ご自身のgoogleアカウントで一旦閲覧のうえ、ご自身のGoogleドライブ上で複製してください)
1. 出勤表のデータを座席表のシートに取り込みます。(IMPORTRANGE関数)
今回は簡易的に作りこみたいので、出勤表のデータを一度まるっと座席表シートに取り込みます。使うのは IMPORTRANGE という超便利な関数です。
使用方法は下記です。
IMPORTRANGE("<取込対象のURL>", "<シート名>!<セル範囲>")
座席表シートの、下の方(A26)に下記のように記載します。
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1O6Q26sLSl-ZYgxStQS9Ll1VVbWcmmRGf9msovjHTqQc/edit?usp=sharing", "6月!A3:AE34")
初回に「これらのシートをリンクする必要があります」という表示が出るので「アクセスを許可」を押下します。
無事データが取り込めました。なお、このIMPORTRANGEで取り込んだデータは、元のシート側の変更もきちんと反映されます。(優秀！)
2. 確認したい日付のリストを作成します。
F22 セルに、選択する日付のリストを作成します。
取り込んだ B26:AE26 の範囲が今回選択したい日付のリストなので、これをそのまま使用します。
- リストを作りたいセル (F22)を選択
- [データ]→ [データの入力規則]
- リストを範囲で指定
- 範囲を B26:AE26 に指定
これで日付をリストで選択できるようになりました！
なお、このリスト自体は書式設定ができないようですので、６月１日（月）のような表示はあきらめましょう。
また、本日の日付も書いておいたほうが親切ですので、F21 セルに、今日の日付を表示させます。TODAY 関数を使用します。F21 セルに以下を記載ください。
=today()
3. 条件付き書式を設定する
条件付き書式をつかうことで、特定の条件に合致した際のセルの動作を設定できます。今回は、「選択した日付に出社する場合に座席(セル)の背景を変える」 という条件です。
条件付き書式は下記の手順で設定できます。
- 対象のセルを選択
- メニューの[表示形式]
- [条件付き書式]
なお、今回のように細かい条件を設定したい場合は「カスタム数式」を使います。
それでは細かい条件を洗い出しましょう。「田中さん」のセル(C4)を例にします。今回欲しい動作としては下記です。
- データ側の田中さん(A27)の行で、
- 選択された日付(F22) の日付の列が、
- 「社」である場合、
- 田中さんのセル(C4)の背景色を変更する。
上記の条件に当てはまるように実装していきます。
この後は複雑なので、一旦結論から書きます。
=INDEX($A$1:$AJ$60,MATCH(INDIRECT(ADDRESS(ROW(),COLUMN())) ,$A$1:$A$56,0),MATCH(F22,$A$26:$AJ$26,0))="社"
関数の説明は後ほど記載します。
これを先ほどの条件付き書式の「カスタム書式」の欄に記載することで、目的の動作になります。
注意：条件付き書式のコピー&ペーストについて
googleスプレッドシートには 「特殊貼り付け」→「条件付き書式のみ貼り付け」 という素敵な響きの貼り付け方法がありますが、こちらを使用すると想定外の動作をします。そのため今回は使用しないでください。（関数式との相性が悪いのかもしれません。詳しい方がいたら教えてください）
ではどうするのか。そうです、 座席の1セルごとに条件付き書式を都度設定 してください。 設定する関数式は同じものを使用します。
サンプルでは、32席あるので32回繰り返します。1セル3秒くらいで設定できるので、約1分半の間、心を無にして手を動かしましょう。
4.シートの保護・データ部分の非表示
- までの手順で実装完了です。チームメンバーに公開(※)した後に色々とデータや式をイジられると面倒なので、日付選択(F22)以外を保護設定にしましょう。サンプルでは 1~21行目、22行目〜 を保護設定にしているので変更できないようになっています。
※ [閲覧のみ]の公開だと、他者から日付リスト(F22)の変更もできなくなってしまう為、今回は保護設定を利用しています。
また、22行目以降のデータ部分は見栄えが悪いので、非表示に設定すると良いかと思います。
以上で実装完了です。お疲れ様でした。
以降は式の説明です。
関数式の説明
今回使用した式は下記です。
=INDEX($A$1:$AJ$60,MATCH(INDIRECT(ADDRESS(ROW(),COLUMN())) ,$A$1:$A$56,0),MATCH(F22,$A$26:$AJ$26,0))="社"
大枠としては以下の形です。
<この座席の名前の人の、指定した日のセルの値> = "社"
「この座席の名前の人の、指定した日のセルの値」の取り方を以下で解説します。
INDEX 関数
INDEX 関数は、指定した範囲の中の、縦から何番目、横から何番目の値を返す関数です。
INDEX(参照範囲,N番目の行、N番目の列) ->そのセルの値を返す。
類似の関数で、OFFSET 関数もありましたが、カスタム書式と相性が悪いようでうまく動かなかったので、今回はINDEXを使用しました。今回はものすごく雑で、シートの全て(A1:AJ60)を参照範囲にしています。
=INDEX(参照範囲,N番目の行、N番目の列)="社"
↓
=INDEX($A$1:$AJ$60,N番目の行、N番目の列)="社"
A1:AJ60 を ＄A＄1:＄AJ＄60 としていますが、今回はどちらでも構いません。気になる方は、絶対参照・相対参照で調べてみてください。今回はどちらでも構いません。
シート全体を参照範囲としたので、行と列が実際のセルとイコールです。例えば目的のセルが「6/4 の田中さん」のセル(E27 = 27行目、5番目の列)の場合は、「=INDEX(A1:AJ60,27,5)」で値がとれます。
MATCH 関数
=INDEX(参照範囲,N番目の行、★N番目の列★)="社"
次に、N番目の列 を取る関数を考えます。ここは、リストから選択した日付のある列が取れればOKです。
使用するのは MATCH 関数です。
MATCH(<検索キー>,<範囲>,0)
詳しいことは省きますが、今回最後の引数は 0 で良いです。
検索キー は、日付リストの値(セル/サンプルではF22)、
範囲 は、日付データのある26行目だけでいいので、A26:AJ26 で設定しました。
MATCH は数値で値を返すので、例えば 6月5日(F26セル) であれば「6」が得られます。
=INDEX(参照範囲,N番目の行、N番目の列)="社"
↓
=INDEX(参照範囲,N番目の行、MATCH($F$22,$A$26:$AJ$26,0))="社"
例のごとく、＄はつけてもつけなくても良いです。
自分自身のセル名を返す
=INDEX(参照範囲,★N番目の行★、N番目の列)="社"
最後に N番目の行 を得る式を考えます。
ここでは、条件付き書式を設定したセルの値と同じデータの行番号を得たいです。
ここでも、先ほど使用した MATCH 関数を使用します。
A27列 以下に名前のリストがあるので、ここを検索範囲にすればOKです。
なお、MATCHで返される値と実際の行番号を一致させたいため、かなり雑ですが、A列全てを対象にします。
N番目の行
↓
MATCH(<検索キー>,<範囲>,0)
↓
MATCH(<検索キー>,$A$1:$A$56,0)
さて、残った＜検索キー＞を考えます。田中さんなら直接「C4」のように指定しても良いのですが、こちらも関数でとってきてしまいたいです。自身のセルの値を返す方法は以下です。
INDIRECT(ADDRESS(ROW(),COLUMN())
これはこういうものだと思えば良いと思いますが、気になる方はドキュメントから意味を理解してみてください。やっていることは単純です。
これで最後の項目も完成です。
=INDEX(参照範囲,★N番目の行★,N番目の列)="社"
↓
=INDEX(参照範囲,MATCH(<検索キー>,$A$1:$A$56,0),N番目の列)="社"
↓
=INDEX(参照範囲,MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()),$A$1:$A$56,0),N番目の列)="社"
関数式まとめ
<この座席の名前の人の、指定した日のセルの値> = "社"
↓
=INDEX(参照範囲,N番目の行、N番目の列)="社"
↓
=INDEX($A$1:$AJ$60,N番目の行、MATCH($F$22,$A$26:$AJ$26,0))="社"
↓
=INDEX($A$1:$AJ$60,MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()),$A$1:$A$56,0),MATCH($F$22,$A$26:$AJ$26,0))="社"
以上です。