【チェックボックス 番外編】GASなしで出来る小ネタ 【反復計算】
チェックボックスネタを全3回書いてきましたが、今回は「番外編」ということで GASなしでチェックボックスで出来る小ネタを取り上げてみましょう。
前回の記事
Googleスプレッドシート の チェックボックス小ネタ
他のサイトでも紹介しているような基本的な利用方法は、ここで触れる必要もないでしょう。
たとえば 条件付き書式と組み合わせて、チェックしたら色を付ける、To Doリストみたいな利用で チェックしたら 項目に取り消し線を入れる、またはFILTER関数や フィルタ機能と組み合わせて、チェックしたものだけを抽出表示させる。
この辺りはもちろん便利ですが、チェックボックスの活用としてはメジャーなネタだし 基本動作なんで難しくも目新しくもありません。他のサイトを参考にしてください。
じゃあ、mirのnoteでは どんな小ネタを取り上げるのか？
既に紹介している スペースキーによる 一括 ON/OFF以外だと、やはり 特殊な動作をGASなしでやるには、アレが必要になってきます。
アレってのは、GASなしでタイムスタンプの記事でも登場した 禁術「反復計算 （循環参照）」です。
事前に反復計算を設定する
というわけで、事前にスプレッドシートの設定をしておきましょう。
メニューバーから
ファイル > 設定 > 計算タブ と進み
反復計算を オン にして、設定を保存 とするだけです。
mirも毎回 禁術とかいって煽ってるのもよくないんですが、反復計算（循環参照）は 表計算において タブー ってわけじゃないです。普通に機能の一つです。
ワクチンと違って副反応もないですし ドラッグみたいな中毒性もないので、身構えずに軽い気持ちで使っても問題ないですよー。
これはリンパの流れをよくするマッサージなんで、皆さん普通にやってることですよー。（余計怪しいｗ）
とりあえずは、お試しで使ってみても大丈夫ってことです。
これで反復計算を使う準備はOK。
反復計算（循環参照）とチェックボックスで 出来る 小ネタ4選
他にも出来ることはありますが、今回は以下の4つを紹介します。
チェックした 日時を隣のセルに書き込む
チェックした順に 番号を振る
チェックした順に 並べる
チェックで 乱数を固定する
こんなことGAS使わなくて出来るの？？
って思うかもしれませんが、これが実際に出来ちゃうのが 反復計算の面白いところ。
1. チェックした 日時を隣のセルに書き込む
一つ目はチェックボックス連動のタイムうタンプ。
これは以前 Googleスプレッドシート 自動でタイムスタンプを入力する3つの方法 -3 【GASなし 関数で出来る！】 で紹介したテクニックとほぼ同じです。
文字が入力されたらの部分を チェックされたらに変えればよいだけ。
例えば A1:A20がチェックボックスで、チェックを入れた日時を 隣のB1:B20 にタイムスタンプとして残したい場合は、
=ARRAYFORMULA(IFS(NOT(A1:A20),,B1:B20>0,B1:B20,A1:A20,NOW()))
B1に上の式をいれれば OKです。
これだけで
こんな感じで チェックを入れた日時が B列に記録されます。
スプレッドシートを更新して（一度閉じて）も記録された日時が固定されているのがわかりますね。
IFSの式の中で重要なのは順番で、優先度が高い条件を先に（左に）記述する必要があります。
今回の場合は
優先度1 A列 チェックボックスにチェックがついてなければ 空白
NOT(A1:A20), ← カンマの後ろに何も入れない = 空白を返す
,
優先度2 B列に既に時刻が入っていれば、そのまま
B1:B20>0,B1:B20
,
優先度3 A列 チェックボックスにチェックが付いていれば 現在時刻を入力
A1:A20,NOW()
としています。
A1:A20,NOW() の部分を先にすると、他の行のチェックがついたり他のセルに何か入力されただけで 再計算が動き時刻が更新されちゃいます。
もう1つ注意するのが、タイムスタンプの回でも書きましたが 既に時刻が書き込まれているかどうかの判断は、 Googleスプレドシートの場合は
B1:B20<>"" ではなく B1:B20>0
とする点でしょうか。
タイムスタンプの記事でも触れましたが、ここはExcelと違うんですよね。
当然ですが、B1:B20は 保護をかけてユーザーに直編集させないようにすることも可能です。
こうしておけば、過去日時のタイムスタンプへ改ざんすることは不可能ですね。
2. チェックした順に 番号を振る
これは 上のタイムスタンプ打刻の応用。A列にチェックボックスがあり、チェックをした順に B列に連番を振るというもの。
比較的簡単です。
=ARRAYFORMULA(IFS(NOT(A1:A20),,B1:B20>0,B1:B20,A1:A20,MAX(B1:B20)+1))
最後の条件部分 A1:A20,MAX(B1:B20)+1 以外は完全に チェック時打刻の式と一緒ですね。
A1:A20,MAX(B1:B20)+1 これによって、チェックが入った隣に その時点でのB列の最大値+1 、たとえば B列に入ってる番号が 1,2,3 だったら 最大値の 3 に +1した 4を 入れる処理をさせています。
最初の１回目は 全て空欄なので 最大値は 0、 +1 で 1 スタートとなります。
チェック毎に連番が振られ、チェックをした以外の行には影響がないことがわかりますね。
先ほどのタイムスタンプ同様、他のセルに編集があっても再計算されることはなく、F5更新（スプレッドシートのリロード）でも連番は保持されます。
ただ、チェックを外すと その隣のセルの番号は消えるのですが、MAX関数で連番を振っているので、消した番号はそのままで 常に最大値 +1 の番号が振られれます。
上記のgif では、A5のチェックを外すと 隣の 3が消えますが、再度チェックを入れたら、その段階での最大値 ７ の +1、8が入ります。
残念ながら関数で制御しているので、3を消しても 自動で 4が振られているセルが 3に、5のセルが4にといった 前に詰める動きはできません。
それでもチェックを付けた順が 前後することはないので、これを応用することで、チェックを付けた順番に並べるといったことも出来ます。
3. チェックした順に 並べる
「チェックした順に 番号を振る」の応用なんですが、単に この番号で関数でソート（並び替え）しようとすると、微妙にうまくいきません。
例えば B列（B2:B16）がチェックボックスで、C列（C2:C16)に名前があったとして、チェックした順に A列に番号を振りたい場合、 A2セルに以下の式をいれます。
=ARRAYFORMULA(IFS(NOT(B2:B16),,A2:A16>0,A2:A16,B2:B16,MAX(A2:A16)+1))
ここまでは、ほぼ先ほどと一緒ですね。
この番号を使って E2以降に 番号と名前を 昇順に出力すれば、「チェック順に並べる」という処理になります。
絞り込んで並び替えですから、FILTERとSORT を使うか、Query関数で orde by を使う方法が思いつきますね。
3-1. Query関数で order byで並べ替え 【やや失敗】
■E2に入れる式
=QUERY({A2:C16},"select Col1,Col3 where Col1>0 order by Col1 asc",0)
Query式の範囲を { } で括って配列化 しているのは mirの記述の好みの問題です。個人的に後ろの列指定は Col1,Col2 といった表記にした方が汎用性があるので、あえてほぼ毎回 配列化させています。
そのまま A2:C16として後ろの記述を select A,C・・・という形でも問題ありません。
order by Col1 asc でCol1（A列）の昇順で並び替えをしていますが、ascの場合は省略できるので order by Col1 としてもOKです。
ただ、この式だと以下のような動きになります。
1つ目のチェックでは反応せず、その後も一つ遅れて出力されている感じですね。これは Query側の計算タイミングと 反復計算のタイミングのズレから発生していると思われます。
それでは Queryの条件の方、where Col1>0 ではなく、where Col2 =TRUE とチェックを判定基準としてみたらどうでしょうか？
※チェックボックス判定は 文字列ではないので シングルクォート付けず =TRUE でOK
これはダメですね。チェックは確かに拾えてますが、今チェックした箇所の番号が拾えてない為、最新チェックの番号が空欄になり 一番上に出力されちゃってます。やはり計算タイミングのズレがあるんでしょうね。
もちろん、適当なセルで Delete押せば再計算され正しくなるんですが、間違いのもとなんで もう一つの FILTER、SORTでやってみましょう。
3-2. FILTER + SORT で並べた場合 【惜しい】
■E2に入れる式
=SORT(FILTER({A2:A16,C2:C16},B2:B16),1,true)
A列とC列をまとめたものを B列のチェックを条件に FILTERで絞り込んで、その結果を SORT で 1列目（A列の番号） をキーに昇順に並び替え、という処理です。
FILTERしてから SORTと 関数を2段構えにすることで、反復計算の処理とタイミングが合うんじゃないなかって期待があります。試してみましょう。
お、惜しい・・・。並びは合ってるし、チェックをOFFにしたときも反応してるんですが、番号だけが拾いきれてないケースがありますね。でも、チェックしたものは一番下にきているので、要件は満たしてると言えるかな。
これでもいいんですが、せっかくなんで完全な チェック順並び替えを実現したいと思い式を色々こねくり回したんですが、イマイチ反復計算のロジックが分からず お手上げ状態に・・・。
3-3. 反復計算に Arrayformulaを使わない 【成功】
で、結局たどり着いたのが反復計算の式で Arrayformulaを使わないという方法。
幸せの青い鳥は実は一番近くにいましたって感じｗ
■A2に以下を入れて下にオートフィル
=IFS(NOT(B2),,A2>0,A2,B2,MAX($A$2:$A$16)+1)
■E2に入れる式は 3-2と一緒
=SORT(FILTER({A2:A16,C2:C16},B2:B16),1,true)
これだけで解決しましたｗ
一見同じように見えて 反復計算においては Arrayformulaを使うと処理が微妙に違うんでしょうね。
Query の方の式を使っても同様に問題なく動きます。
原因と理由がイマイチわからないのが引っ掛かりますが、反復計算で期待する結果にならない場合は、Arrayformulaを使わずに試してみるのが良さそうです。
オマケ：LAMBDA + BYROWで試す 【完全にダメ】
一応、Arrayformulaがダメならもう一つのスピらせる手法、LAMBDA + BYROW だったらどうか？ 一応試してみました。
=BYROW(A2:A16,LAMBDA(r,IFS(NOT(OFFSET(r,0,1)),,r>0,r,OFFSET(r,0,1),MAX(r)+1,true,)))
これはダメですね。理由はわかりませんが、ナンバーが1しかつきません。
Arrayformula と LAMBDAヘルパー関数の スピり方は 感覚的に違うってイメージでしたが、実際だいぶ違うみたいですね。
そのうちじっくり検証したいと思います。
4. チェックで 乱数を固定する
乱数系の関数は ランダムな結果が欲しい時に重宝するんですが、NOW関数と一緒で、シート内で入力や変更があるたびに再計算されちゃうんですよね。
乱数の再計算を制御できたらいいのに～、って思ったことある人も多いんじゃないでしょうか？
もちろんGASでやってもいいんですが、チェックボックスと 反復計算を使って簡単に実現できます。
■D1を乱数固定チェックボックスとした場合、A2に入れる式
=IF(D1,A2,RANDBETWEEN(1,20))
※A2 には 1～20の数字をランダムに表示する
チェックが外れている時は、シート内で編集があるたびに乱数が再計算されていますが、チェックＯＮとすれば乱数の計算が固定されているのがわかりますね。
RANDBETWEENは、数値範囲を指定して その範囲内の整数をランダムで返すことが出来る関数です。
これで 乱数を固定した状態で 他のセルを編集できますね。さらに 応用すれば A2セルの 乱数を他のセルに出た順にログとして残すことも出来ます。
ん・・・ということはもしや？？
応用すれば パーティーゲームのアレが作れちゃう？
今回 チェックボックスと 反復計算で実現できる 小ネタを4つ紹介しました。他にも使い方が色々あった気がするんで、思い出したらまた別で記事を書きます。
ところで 最後の 乱数固定でなんとなくアレが作れそうな気がしませんか？
パーティーゲームでお馴染みのアレです。そう、ビンゴゲームです！
専用アプリを使うか javascript で自作するか、スプレッドシートで実現するにしても GASが必須と思われがちですが、
チェックボックス + 反復計算 ＋ シート関数 + 条件付き書式
これだけで ビンゴゲームを スプレッドシートで作れます！
というわけで次回は GASを使わないチェックボックスネタ の応用編ってことで、
【GAS不要！？】チェックボックスでビンゴゲームをつくる
を書こうかな・・・
と思いましたが、色々な都合とチェックボックスが続いて飽きたんで、ちょっと1週 違うネタを挟もうかなと。
1行カレンダーの記事 番外編で扱った 祝日を取得する関数が結構人気だったんで、Googleスプレッドシートで 和暦変換 ってネタを書こうと思います。
Excelと違って和暦なんて知らんよってスタンスのGoogleスプレッドシートで、どう和暦を扱えばよいか？ APIや関数を使った方法を検証してみましょう。
■次の記事
緊急特番的に 最新アップデートの紹介記事に差し替えとなりました。
でも、このアップデートはすごい！！！（個人的に）
和暦変換ネタは 1週遅れての掲載となります。