メインコンテンツへスキップ
見出し画像

Googleスプレッドシート カスタム値チェックボックスのススメ(超応用例いろいろ)

    スプレッドシートの便利機能 チェックボックス。

    画像

    今回は、これをカスタム値で利用するネタを紹介します。

    1. チェックでテーブルを拡張

    2. 上から順番にチェックが必須のチェックボックス

    3. 欽ちゃんの仮装大賞風 得点バー

    4. 選択数の上限を指定したチェックボックス

    5. ラジオボタン風(イマイチ)

    6. 一括チェックボタン

    こんなことがGAS無しで出来ます。ただし期待しすぎないでください・・・。(詳しくは読めばわかります)

    チェックボックスネタも結構溜まってたのでマガジン化しました。

    前回のnoteは 新機能「手動計算モード」について検証しました。


    チェックボックスは カスタム値を設定できる

    画像

    普通に挿入したセル内チェックボックスは、

    チェックあり ・・・ TRUE
    チェックなし ・・・ FALSE

    このようにチェックのあり・なしで、ブール値(真偽値)  TRUEまたはFALSEを返します。

    実は、このチェックあり・なしの値を、自分で好きな値にカスタムすることができるんです。


    チェックボックスのカスタム値設定方法

    チェックボックスにカスタム値を設定する手順を紹介します。

    PCブラウザからのみ設定可能で、スマホアプリ版では設定は出来ません。


    1.普通にチェックボックスを挿入
    まずはチェックボックスを入れたいセル範囲を選択して、

    メニューから挿入 > チェックボックス

    で、普通にチェックボックスを入れます。

    画像
    ※直接データの入力規制としてもOK


    2.サイドバーでデータの入力規制を開く

    画像

    メニューから

    データ > データの入力規制

    を選択し、サイドバーでデータの入力規制を開きます。

    3.該当のチェックボックスの設定を開き、カスタムのセル値を使用するをチェック

    画像
    ※マウスオーバーするとセル範囲の色が変わる

    データの入力規制内から、対象範囲のチェックボックスをクリックして設定を開き、カスタムのセル値を使用するに✅チェックを入れます。


    4.それぞれの値を設定し「完了」を押す

    画像

    チェックマーク付き、チェックマークなし、それぞれの値を入力して「完了」を押せば設定終了です。


    画像

    カスタム値を設定したチェックボックスは、数式バー上では設定したカスタム値になっているのがわかります。

    画像

    また、数式で参照した場合もカスタム値が出力されます

    画像



    カスタム値チェックボックスでゲームも出来る

    チェックボックスのカスタム値は、✅するまでは何が入っているかわからないという特徴があります。

    この仕様と「範囲をランダム化」という機能を使って、簡単に黒ひげ危機一髪!風ゲームが出来ます。

    画像

    チェックボックスの「チェックあり」のカスタム値を 👼にしたものの中に、一つだけカスタム値を 💀 としたチェックボックスを入れて、ゲーム開始前に範囲をランダム化。

    あとは参加者が順番に好きな場所にチェックをしていき、💀が出たら負け(罰ゲーム)といった単純なゲームです。

    他にもマインスイーパー風や神経衰弱など、工夫次第で色々出来そうです。

    ま、この辺りは今やAIでさくっとゲームが生成出来ちゃうんで、需要はあまり無いかもしれません。

    ただ、チェックボックスの新たな使い方としては色々可能性が広がって面白いですよね。



    設定できるカスタム値の制限

    このように、絵文字も チェックボックスのカスタム値として指定できるんですが、カスタム値として設定できないものもあります。

    1つは = からはじまる数式です。数式はチェックボックスのカスタム値に指定が出来ません。

    画像

    もう1つがチェックありの値を「空」とすることです。チェックありを「空」のままだと 保存が出来ません。

    画像

    一方、チェックマークなしの値であれば 空 を設定することが出来ます。

    画像

    で、ここからが本題で、このチェックなしを空に設定したカスタム値チェックボックスが色々活用できるんです。



    便利なチェックボックスカスタム値設定(TRUE・空)

    冒頭で書いた色々な応用技の為の設定が、

    画像

    チェックマーク付き: TRUE
    チェックマークなし: (空 なにも入れない)

    この、チェックマークなし(通常はFALSE)を空とするカスタム値の設定です。

    このカスタム値には、

    1. 普通のチェックボックスと同じように使える

    2. チェックなしが空なので、スピル式で展開した値を入れられる

    3. スピル式で TRUEや空文字を入れると チェックボックスはロックされる

    このような利点があります。


    利点1. 普通のチェックボックスと同じように使える

    この設定のメリット1つ目は、、通常のチェックボックスと変わらずに使えるという点です。

    画像

    基本的にチェックボックスを活用する際は、「TRUEだったら」を条件とすることがほとんどです。

    つまり、チェックなしだけカスタム値で、チェックありが TRUEのままであれば、ほぼ使い勝手が変わりません。

    また、チェックなし の値が「空」であることも、

    画像

    IF関数で判定した場合に「空」はFALSEと扱われる為、まったく問題ありません。



    利点2. スピル式で展開した値を入れられる

    2つ目のメリットが、チェックなしが空であれば、左や上に入れたスピル数式で展開させた値を入れることができるという点です。

    例えば、式を入れたセルと右隣りのセル2セルに結果が展開する

    =SEQUENCE(1,2)

    という式を入れた場合、

    画像

    隣のセルに何も入っていなければ、スピルした 2が表示されますが、通常のチェックボックスを設定している場合は、

    チェックあり TRUE
    チェックなし FALSE

    と、どちらもセルに値が入っている状態なので、👆画像のように 配列が展開されず #REF!エラーとなります。

    しかし、カスタム値で チェックなしを空に設定したチェックボックスとすれば、

    画像

    このようにチェックなしのチェックボックスが入ったセルは空なので、スピル式で展開された値を入れることが出来るんです。

    わかりにくいですが、👆の画像は B4セル、B6セルは チェックボックス(チェックなし)が入っています。そこに2が上書きされている状態です。

    一方、チェックありの方はTRUEが入っているので展開できず #REF!エラーのままです。

    ただし、チェックなしのセルはTRUEか空のみを受け付けるデータの入力規制が設定されている為、「無効」というエラーメッセージが出ます。



    利点3. スピル式でTRUEや空文字を書き込むとチェックボックスがロックされる

    では、入力規制に違反しない TRUEや空(空文字でOK)をスピル式で入れたらどうなるか?

    画像

    ={"",""}

    👆A2セルに、こんなスピル式を入れた場合、隣のB2セルのチェックボックス(チェックなし)には空文字が書き込まれますが、チェックなしの状態のまま変わらず。


    ={"",TRUE}

    👆A3セルに、こんなスピル式を入れた場合、隣のB3セルのチェックボックス(チェックなし)にはTRUEが書き込まれますが、TRUE = チェックあり なので、チェックボックスがチェックありに変化します。

    つまり、数式で

    🔵チェックなし ▶ チェックあり

    が制御できるということです。

    一方、手動でチェックを入れたセルに関しては

    ❌チェックあり ▶ チェックなし

    これは数式では不可能です。

    さらに面白いのが、

    画像

    スピル式で TREUや空文字を書き込んだチェックボックスは、チェック状態がロックされ、クリックやスペースキーでチェックあり・なしが変更できなくなるという不思議な仕様。

    通常はスピル式で展開された値よりも、手入力の方が強い(手入力は普通に出来る)のですが、チェックボックスのクリックやスペースキーによるチェックのあり・なし切り替えは、この限りではないようです。

    以上、チェックなしを空に設定したカスタム値チェックボックスの利点3つを紹介しました。


    カスタム値チェックボックス 超応用例

    チェックマーク付き: TRUE
    チェックマークなし: (空)

    このカスタム値設定をしたチェックボックスの超応用例を見ていきましょう!


    応用例1 チェックボックスで簡単テーブル拡張

    Googleスプレッドシートのテーブル機能は、テーブル範囲の最終行の一つ下の行に値を入力すると、自動でテーブル範囲が拡張する仕様になっています。

    画像
    前回の手動計算モードで使ったGIFの使いまわし

    でも、不慣れなユーザーからすると、いきなりテーブル範囲の外に入力するって抵抗感があるんですよね。

    先に入力する範囲をテーブルにしておきたい。でも範囲変更やドラッグで広げるのは不安。

    そんな時に使えるのが、カスタム値チェックボックスによるテーブル拡張です。

    画像

    テーブルの一番左に1列追加し、テーブルの列の型で設定するのではなく、テーブルの範囲を超えた行まで多めにチェックボックスを挿入し、カスタム値を設定します。(ついでに 条件付き書式で 空の時の文字色をかなり薄いグレーとしています)

    このようにすると、チェックなしのチェックボックスは空である為、チェックした瞬間に値を入力したと同じ効果が得られます。

    つまり、

    画像

    チェックするとテーブルが拡張できるってことです。これなら不慣れな人も安心ですね。

    チェックボックスは、範囲選択 & スペースキーで一括チェックが出来るんですが、もちろんこれも有効です。

    画像

    これは通常のチェックボックスだと機能しません。

    チェックなしが FALSEというブール値が入ってる状態なので、チェックをつけても値を入力したとは見なされない為です。

    画像

    「チェックなし」を空とすることで使える小ネタです。

    なお、一度拡張したテーブルは チェックを外しても範囲が戻ることはありません。Ctrl + Z (元に戻す)を使いましょう。

    画像

    スピル式 + カスタム値チェックボックス 超応用例

    チェックなしの値を空とする最大のメリットが、スピル式で上から数式の値を入れることが出来る点です。



    応用例2 上から順番にチェックが必須のチェックボックスを作る

    画像

    カスタム値チェックボックスを B2:B11に入れ、右のC2:C11に必ず上から順番に確認して欲しい項目(テキスト)を入れます。

    左隣りのA列はA2セルは空のままとして、一つ下の A3セルに

    =IF(B2,,{"",""})

    と式をいれます。

    これは、一つ上の行のチェックボックスがチェックあり(TRUE)なら空を チェック無し(空 つまり FALSE扱い)なら {"",""} と横に展開する空文字配列を返し、隣のチェックボックスをチェック無しでロックする式です。

    これをチェックボックス分、A11まで下にフィルコピーします。

    画像

    ここは MAP関数等で 一つの式にまとめることは出来ません。必ず1セルごとに式が入っている必要があります。

    これで上から順番にチェックが必須のチェックボックスが完成です!

    画像

    ルールを無視して途中のチェックボックスをクリックしても、スペースキーを押してもチェックを入れることは出来ません。

    「上から順番にチェックをつける」というルールを守る必要があります。

    さらに、チェックボックス範囲を選択(Ctrl + A でもOK)してスペースキーで一括チェックを付けようとすると、スペースキーを押すたびに上から順にチェックが付いていくんです。

    全てチェックありの状態でスペースキーを押すと、全てチェックなしとなり最初に戻ります。

    画像

    なかなか面白いですよね。


    応用例3 欽ちゃんの仮装大賞風 得点バー

    画像
    どうしてそーなるの!?

    上のネタをアレンジすると、スペースを押した分だけ点数が積みあがる、欽ちゃんの仮装大賞風得点バーが作成できます。

    これも 「チェック無し」を空としたカスタム値チェックボックスを利用します。

    一番上のチェックボックス の左側の C2セルの数式を

    画像

    =IF(D3,,{"",""})

    と、今度は 一つ下のチェックボックスを参照する式として、一番下のチェックボックスの一つ上20行目までフィルコピーすればOK。

    スペースを押すたびに下からじわじわ伸びていく動きが、まさに欽ちゃんの仮装大賞ですねw



    反復計算 + カスタム値チェックボックス 超応用例

    ここからは数式が循環参照となる為、反復計算をONとして利用する応用例です。

    メニューの ファイルから 設定を開き

    画像


    ダイアログのタブを「全般」から「計算」に切り替えて、

    画像

    反復計算をONとし、最大反復回数を 1として「設定を保存」を押す。

    まずは、この設定にしておきましょう。

    反復計算を利用すると、GASなしでも色々面白いことが出来ます。


    この反復計算とカスタム値チェックボックスを組み合わせた応用例を3つ紹介します。


    応用例4 選択数の上限を指定したチェックボックス

    画像

    まず1つ目です。

    指定した数以上のチェックが出来ない、選択数上限付きチェックボックスが GAS無しで出来ます。

    チェック無しを空としたカスタム値チェックボックスを利用します。

    隣のA列の数式は、一番上のA3に

    画像

    =IF(B3,,IF(SUMPRODUCT($B$3:$B$11)=$B$1,{"",""},))

    こんな式を入れます。

    これはまず B3セルのチェックボックスが チェックあり(TRUE)なら空として、違う場合は さらにIFで

    SUMPRODUCT($B$3:$B$11)=$B$1

    B3~B11のチェックボックスのチェックされている数が B1セルの上限数と一致するか?を判定。

    一致したら {"",""} を返して隣のチェックボックスをチェックなしでロック。

    不一致であれば、空を返す(ロックしない)としています。

    SUMPRODUCT関数を使っているのは、ブール値のTRUEを 1として合計できる為です。SUM関数だと TRUEを1と見なしてくれません。

    また COUNTAを使ってしまうと、スピル式で書き込んでいる空文字も値が入ったセルとして数えてしまうので、ここは TRUEの数を合計する式が適切です。

    SUMPRODUCT($B$3:$B$11) 

    もしくは

    COUNTIF($B$3:$B$11,TRUE)

    とするのがよいでしょう。

    スピル式 {"",""} で書き込むセル B3を数式内で参照するので、循環参照となります。

    この循環参照エラーを回避する為に、反復計算をONとしているわけです。



    応用例5 イマイチなラジオボタン風

    上のネタの選択数の上限を1とするか、もしくは数式を

    =IF(B3,,IF(SUMPRODUCT($B$3:$B$11),{"",""},))

    として下にフィルコピーすれば、

    画像


    選択肢の中から1つだけ選択できるラジオボタン的な チェックボックスとなります。

    ただし本物のラジオボタンと違って

    画像

    別の箇所をチェックしても、前のチェックが自動で解除されることはないので、

    前のチェックを再度クリックしてチェック無しにする
     ▼
    新しいチェックボックスをチェック

    と、一度手動でチェック解除してから新たな選択肢をクリックという手順になります。

    ラジオボタン風と呼ぶには、かなーりイマイチですね。

    実務で使うことはなさそうですが、一応こんなことも出来るよ!ってことで。



    応用技6 一括チェックボタンの実装

    画像

    これまでは空文字をスピル式でチェックボックスのセルに書き込んでロックする技を利用してきましたが、スピル式で TRUEを上から被せる(チェックアリに出来る)ことも出来ます。

    これを利用した 一括チェックボタンをGASなし 数式で実装してみましょう!

    👆上はチェックボックスの左側 A3セルに

    =IF(B3,,IF($B$1,{"",$B$1},))

    画像

    こんな式を入れて、下にフィルコピーしています。

    B1の一括選択のチェックボックスを✅とすることで、全てのチェックボックスが✅ありになりましたね。

    ただ数式で上から被せている都合上、当然

    画像

    一括チェックを解除しても、手動でチェックを付けた箇所は残りますし、

    一括でチェックをつけてから、不要な個所だけ手動でチェックを外すといった利用も出来ません。(ロックされている為)

    画像

    以上、カスタム値チェックボックスの超応用例 6選でした。

    実務で使えるか?は微妙ですが、GASなしで色々出来る可能性は感じていただけたんじゃないでしょうか。



    カスタム値チェックボックス 利用の注意点

    カスタム値チェックボックスを使ったネタは、機能的には結構穴があるので、それを理解した上で使用することをお勧めします。



    DeleteやBack spaceでチェックボックスが消せてしまう

    画像

    セル内チェックボックス自体は保護が出来ません。(セルの保護をするとチェック操作も出来なくなる)

    上で「ロック」という言い方をしていますが、スペースキーやクリックでチェックのあり・なしを操作できないってだけで、簡単に Deleteキーや Back spaceキーで チェックボックスそのものを削除出来てしまいます。

    これを防止するには テーブル化して、テーブルの列の型としてチェックボックスを指定するしかありません。

    ただし、テーブルの列の型で設定したチェックボックスは、カスタム値が使えません。悩ましいですね・・・。



    ダブルクリックやF2キーによる編集モードでの入力は防止できない

    画像

    同じく「ロック」状態であっても、 セルの中身の編集は防止できません。

    その為、

    • チェックボックス周りの空白をダブルクリック

    • F2キーで編集モードにする

    • セル選択後、直接数式バーに入力

    これらで値を入れることが出来てしまいます。

    ここでキーボード入力で TRUEと入力すれば

    画像

    制限を突破してチェックを入れることも出来ちゃいます。

    残念ながらこれを防止することは出来ません。



    スペースによる一括チェックは防げない

    選択数の上限を設定したチェックボックスやラジオボタン風チェックボックスは、チェックの数が上限に達したら残りのチェックなしのチェックボックスをスピル数式でロックしています。

    その為、範囲選択 > スペースキー による一括チェックが防止できません。

    画像

    同時タイミングで一括チェックを入れてしまうと、スピル式が機能する前に✅ありになってしまう為です。

    これも仕様だと思って諦めましょう。



    空文字ではな「空白」を書き込んだ時の特殊な挙動

    Googleスプレッドシートは、数式で空白を扱えるので空文字ではなく空白をスピル式でチェックボックスのセルに上からかぶせることも出来ます。

    ただ、カスタム値のチェックボックスに「空白」をスピル式で入れた場合は、ちょっと特殊な挙動になります。

    空白のスピル式は ={,} このような配列の書き方が出来ない為、関数を組み合わせる必要があります。

    一番簡単なのが

    画像

    =WRAPROWS(,2,)

    WRAPROWS関数を使った、こんな式です。

    これで A2セル、B2セルと横に2セル数式で空白が展開された状態となります。

    数式で 一次元の空配列を生成する


    画像

    この時、チェックボックスはクリックしても反応がなく空文字の時と同じようにロックされているように見えますが・・・

    なぜかスペースキーではチェックのON/OFFが出来ちゃいます。

    理由はよくわかりませんが、空文字と空白では挙動が微妙に違うってことです。

    とりあえずカスタム値チェックボックスの活用では、スペースキーによるチェックボックス操作も防止できる、空文字を使っておいた方が良いでしょう。



    カスタム値 チェックボックス活用のまとめ

    今回はカスタム値を設定したチェックボックス、特に チェックなしを空とした時の スピル数式や反復計算と組み合わせた応用テクニックを紹介しました。

    実務で使うにはややイマイチなものが多いですが、GASなしでも色々出来ることがわかりましたね!

    次回はこの流れで、チェックボックスを使ったGAS無しスピンボタンの最新版を書きたいと思います。

    画像

    👆こんなやつ(ただ、これも実務利用は微妙なネタですw)

     
     

    mir

     
     
    元Excel職人・VBA使いから、Googleスプレッドシート職人・GAS使いにジョブチェンジ。謎解き感覚で お題(課題)を解決していくような記事を書こうかなと。その他、AIやらGeminiやらGoogleWorkspaceネタ全般

    あなたへのおすすめ