メインコンテンツまでスキップ

【全データ】一次事務編アプライ回答タブ

このページでは、36thで総務が使用していた個人情報シートのうちのタブの一つ、【全データ】一次事務編アプライ回答タブ(内部用サイトで閲覧可能)について解説します。

構造(概観)​

A-F列とAM列より右のゾーンで異常検知・間のゾーンがフォームの回答の生データという構造を取っています。 生データを左と右から関数等が入った行で挟み込み、左側の部分で管理できるようにしています。

作成​

  • フォームの回答をリンクさせる

  • 左端に6行追加、右端は最低BA列まで拡張しておく。

  • FMTタブからA1:F1とAL1:BA1の範囲をそれぞれコピペしておく

    • 見やすさを考えると、フォームの回答ではない部分の色は変えてもよい

  • 1行目を固定

  • E2、F2、AN2～BA2までの範囲の関数をコピペする

  • 条件付き書式を設定する

    • AM列：空白セルに対して灰色背景

      • フォームの回答部分との境界確認用。なくても可。

    • F列：1以上の値に対して赤色背景

      • AN列より右の行の値を合算し、1以上の場合にハイライトを入れる
      • ハイライトが入っていた場合、何らかの回答が要修正であることがわかる

    • AN～BA列：0より大きい値に対して赤色背景

      • それぞれの行で個別の設問に合わせた異常検知の関数が入っている
      • 下のセクションで解説しています

    • AH、AK列：空白ではないセルに対して赤色背景

      • フォームの回答の生データ部分でもハイライトが入るように設定
      • 健康上の通達事項、事務局への質問が対象

    • C列：「無効アプライ」を含むテキストに対して黒色背景、白文字

      • 対応ログに無効アプライを含む場合、黒色でハイライト

    • C列：「キャンセル済み」を含むテキストに対して黒色背景、白文字

      • 対応ログにキャンセル済みを含む場合、黒色でハイライト

    • C列：「要対応」を含むテキストに対して黄色背景

      • 対応ログに要対応を含む場合、黄色でハイライト
      • 黄色ハイライトが入っている人が、回答の修正の必要があるなどする人

    • C列：「問題なし」を含むテキストに対して緑色背景

      • 何も対応する必要がない人も緑でハイライト。なくても可。

    • AG列：「なし」を含まないテキストに対して赤色背景

      • アレルギーがある人に対してフォームの回答部分でハイライト

    • B列：「FALSE」を含むテキストに対して黒色背景、白文字

      • TRUEは大会の有効アプライとしてカウント、FALSEは無効orキャンセルによって有効ではなくなったアプライとしてカウント
      • FALSEのものはB列、C列にまたがって黒色ハイライトが入るようになっている

  • フォームの回答部分に保護設定を行う

    • 36thでは総務統括のアカウントのみが警告表示あり+編集可能にしていた

個別の列の解説​

基本的に上記で解説した作成手順に従えば何ら問題なく動作可能なシートは作成できますが、念のためそれぞれの列の意味を解説しておきます。

  • A列：付番。Discord上でログを取る際、付番何番の～～を～～しました、のように記述していた。ちなみに、左側にさらに行が必要になった場合の予備としても捉えていた(左に新たに行を追加すると、他のシートからimportrangeする際の範囲が狂ってしまうためできるだけ既存の行の位置関係を固定しておきたかった)

  • B列：アプライのステータス管理。C列と並行して、総務の担当者が手動で埋める。B列は純粋に有効(TRUE)か、無効(FALSE)かを判断するのみである。この行を条件にして有効アプライ数を出したりしている。

  • C列：対応ログ。メモ入れとして使用。こちらも手動更新である。キャンセルになったら必ず「キャンセル済み」というキーワードをこのメモ入れに入れている。無効な回答の場合は「無効アプライ」というキーワードである。

  • D列：支払いステータス。こちらは会計に入力してもらっていた。会計が通帳を記帳し、振り込みを確認したら該当のアプライ行のD列のセルにチェックを入れるという形。

  • E列：会議の紐づけ列。こちらは会議編が提出され、フロントによる会議割りが確定したのちに、会議割りの会場のスプレッドシートからデータを引っ張ってきている

  • F列：対応の必要性を数値で表示する列。AN列～BA列までを合計して表示している。0より大きい場合何らかの対応が必要であることを示している。

  • G列～AK列：フォームの回答。生データなので慎重に扱う必要がある。

  • AN列：メアドが一致しているか確かめる関数が入っている。=arrayformula(IF(H2:H=I2:I,"0","1"))

  • AO列：性別対応で「回答しない」を選択した場合に1を示す関数が入っている=arrayformula(IF(Q2:Q="回答しない (事務局から個別に連絡差し上げます)",1,0))

  • AP・AQ列：電話番号の整形用。全角で入力したり、ハイフンが入っていたりする場合があるのでそれを整形している。AP列は本人の電話番号、AQ列は保護者の電話番号を整形している。

    • AP列：=arrayformula(SUBSTITUTE(ASC(R2:R),"-",""))
    • AQ列：=arrayformula(SUBSTITUTE(ASC(S2:S),"-",""))

  • AR列：保護者と本人の電話番号が一致しているか確かめる。一致していると困る(保護者の電話番号がわからない)ので、一致したら1を表示している。=ArrayFormula(IF(AP2:AP=AQ2:AQ,"1",0))

  • AS列：証明書のアップロードがあった場合に1を表示している。=ARRAYFORMULA(IF(Y2:Y="",0,1))

  • AT列：宿泊希望がある場合に1を表示している。頻出するので異常検知のF列の合計には含めなくてもよいかも。=arrayformula(IF(AA2:AA="泊まる",1,0))

  • AU列：宿泊要望がある場合に1を表示している。=arrayformula(IF(AB2:AB="",0,1))

  • AV列：体調不良時、親などに迎えに来てもらう予定なのに保護者の連絡先が未回答の人に1を表示している。=arrayformula(IF(AI2:AI="送迎可能",IF(AJ2:AJ="",1,0),0))

  • AW列：健康面の伝達事項がある場合に1を表示している。=arrayformula(IF(AH2:AH="",0,1))

  • AX列：質問がある場合に1を表示している。=arrayformula(IF(AK2:AK="",0,1))

  • AY列：参加費・規約への同意にチェックが入っていない場合に1を表示している。本来は構造上防げるものではあるが、フォームの設定ミスにより参加費の項目に同意を押さなくてもフォームを出せるようになっていた時期があり、その時に作成した関数。=arrayformula(IF(AC2:AC<>"",IF(AD2:AD<>"",0,1),1))

  • AZ列：アレルギーがある場合に1を表示。=arrayformula(IF(AG2:AG="なし",0,1))

  • BA列：事務編アプライを二回出す人がいたので、整形済みの本人の電話番号で重複を確認している=arrayformula(COUNTIF(AP2:AP,AP2:AP)-1)

    • メールアドレスだと複数アカウント持っている人が抜け落ちそうな気がしたので、同一のものを書き込む可能性が高い電話番号で見分けようとしていた。