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

Googleスプレッドシート で使えるスピンボタン検証6 【GASなし 超改良版】

    Excelにはあるけど Googleスプレッドシートには無い スピン機能(スピンボタン)。

    こちらを GAS や 関数で 実装する方法を検証し、どのアプローチがベストなのか? 過去5回にわたってお送りしてきました。

    👆第5回の 関数でスピン【改良版】 を公開した 2023年2月から3年以上が経過し、その後の検証で新たな発見などもあったので、今回は久々にスピンボタンシリーズの最新版

    GASなし 関数で実現できる 超改良版スピンボタン

    を紹介したいと思います。

    前回のnoteは 「カスタム値チェックボックスのススメ」というテーマで書きましました。



    超改良版スピンボタンのゴール(前バージョンとの違い)

    まずは最終的に

    どんなスピンボタンが作れるのか?

    今回のゴールを見てみましょう。前回のスピンボタンと比べ、どこが改良されたのか?にも注目してください。

    チェックボックスをボタン(スイッチ)に見立てて、スピン機能を実現しています。

    第5回で紹介したGASなしのスピンボタンも、動きはスムーズですし値も保持されるし悪くないんですが、

    画像
    前回のスピンボタン
    • スピンボタンの増減を切り替える為にプルダウンが必要

    • ロックをしないと他のセルを編集した際に数が変わってしまう

    • 複数のスピンボタンを設置するのが難しい

    こんな課題がありました。

    これらを改良したのが、👇今回紹介する超改良版スピンボタンです。

    画像
    今回のスピンボタン

    こちらは作業領域(画像のE2:F)こそ必要としますが

    • チェックボックス2つのクリックだけで スピンボタンの増減が可能

    • ロックをせずに他のセルを編集しても数が変わらない

    • 複数スピンボタン設置が簡単

    このように 前回のチェックボックスの課題が解消されたものとなっています。

    かなり一般的にイメージするスピンボタンに近づいたんじゃないでしょうか?

    もちろん、これをアレンジしてデータベースと組み合わせて活用することも可能。(※注意点があります)

    画像

    GASを使わないからこそ、ここまでサクサク動きます。

    早速作り方に入っていきましょう!



    1. 超改良版スピンボタン 基本バージョンの作り方

    超改良版スピンボタンの作り方を解説していきます。

    なお、作成済みのスピボタンはスマホアプリ版のスプレッドシートからも利用できますが、作成はパソコンのブラウザからのみとなります。



    1-1. 反復計算を設定

    GASなしスピンボタンは、自分のセルを参照し加算(減算)する 循環参照を利用した技なので、反復計算をONとする必要があります。

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

    画像
    前回の画像の使いまわしです


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

    画像

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

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

    ※反復計算をONにしても循環参照ではない通常の数式への影響はありません。ただし循環参照する数式の数が多い場合は、計算負荷が高くなりシートの動きが悪くなる可能性があります。



    1-2. 数値(結果)セル、プラス、マイナスのチェックボックスセルを作成

    画像

    数値(結果)を表示させるセル、プラスのチェックボックス、マイナスのチェックボックスを用意します。

    チェックボックスはカスタム値を指定する必要は無く、通常の TRUE・FALSEのチェックボックスでOK。

    とりあえず結果を表示するセルは数式じゃなくていいので、手入力で 0を入れておきましょう。

    文字(チェックボックス)の色、フォントサイズ、横位置など、レイアウトをいい感じに調整してください。

    おススメは👆上のような横並びですが、

    画像

    👆こんな感じにスピンボタンを縦配置にすても良いです。

    ただし、これだと配置のバランス的に結果セルはセル結合が必要となりますし、後々複数設置したい時に不便を感じます。これは後ほど解説します。



    1-3. 作業セルに数式を入れる

    作業用セル(2つ)を決めて数式を入れます。

    この時チェックボックスが横2つなら横2セルにスピルする 👇こんな式を

    画像

    =LET(自セル,D2,{B2:C2})

    チェックボックスが縦2つなら縦2セルに展開する 👇こんな式を入れます。

    画像

    =LET(自セル,D2,{C2:C3})

    LET関数で、数式を入れたセルを「自セル」とおいて、

    画像
    画像

    そして、中カッコで チェックボックスのセル範囲を配列として返す式とします。


    でも、=LET(自セル,D2,{B2:C2})  この式って違和感ありますよね?

    わざわざ 式を入れた D2セルを LET関数で「自セル」と変数化しているのに、計算にはまったく使ってません。

    これ意味あるの?

    と思うかもしれませんが、自分自身のセルを参照し循環参照とすることで計算処理のタイミングを意図的に遅らせています。

    加えて作業セルは、作成するセル位置が非常に重要となります。

    これらの理由は後ほど解説するので、まずは 上の画像の通りの配置で作成してみてください。



    1-4. リセット用チェックボックスを作成する

    画像

    続いて スピンボタンを0に戻す為の リセット用チェックボックスを用意します。

    これは無くてもいいって人もいるかもしれませんが、今 0を入れてるセル(画像だと A2セル)には数式を入れるので、簡単に初期値 0に戻すことが出来ません。

    0に戻すスイッチがあると後々便利です。



    1-5. 数値(結果)セルに数式を入れる

    数値(結果)セルの手入力していた0を消して、メインの数式を入れましょう。

    画像

    =IF($E$1,0,A2+IF(B2<>D2,1,IF(C2<>E2,-1,0)))

    この数式の意味は、

    画像

    リセットチェックボックスが ✅ありなら 0を返す、✅なし(FALSE)なら

    A2(数式を入れた自分自身のセル)に IF関数の結果を加算します。

    画像

    B2(プラスのチェックボックス)と D2(B2を参照した結果)が一致しなければ 1を

    画像

    C2(マイナスのチェックボックス)と E2(C2を参照した結果)が一致しなければ -1を、一致するなら(プラスもマイナスも作業セルの参照と差異が無ければ) 0を A2に加算するという数式になっています。

    チェックボックスとそれを数式で参照してるセルなんだから、「普通に一致するでしょ」って思うかもしれません。

    しかし、先ほどの作業セルにポイントを押させた式をいれたことで、微妙な反映タイミングのズレを発生させ、チェックした瞬間に「一致しない」と判定をさせることで、プラスマイナスの増減を制御しています。

    また、コピペで複数配置した時にリセットボタンを共用(全スピンボタンの結果を一括リセット)する為に、リセットボタンのセルだけ $D$1 と 絶対参照にしているのもポイントです。

    ちなみに、結果セルの数式は IFS関数を使って

    画像

    =IF($E$1,0,A2+IFS(B2<>D2,1,C2<>E2,-1,true,0))

    👆こんな式にしてもOKですし、

    SUMPRODUCT関数を使ってセル範囲をまとめて処理する

    画像

    =IF($E$1,0,A2+SUMPRODUCT(B2:C2<>D2:E2,{1,-1}))

    👆こんな式にしてもOKです。

    ただし、最終的に ARRAYFORMULA関数を組み合わせて一つの式で複数スピンボタンを制御することを考えると、最初のIF関数のネスト式かIFS関数の式が良いかなと思います。


    スピンボタン用のチェックボックスを縦並びに配置した場合も同様で、

    画像

    =IF($D$1,0,A2+IF(C2<>D2,1,IF(C3<>D3,-1,0)))

    👆こんな式で対応可能です。



    1-6. 超改良版スピンボタン 基本バージョン 動作確認

    それでは、ここまでの手順で作成したスピンボタン風チェックボックスの動作確認をしてみましょう。

    画像
    画像

    どちらもしっかりスピンしてますね!

    チェックボックスだけ(+作業セル、数式、反復計算)で実現する GAS不要のスピンボタン、まずは基本バージョンが完成です。

    以降は横並びタイプのスピンボタン配置で解説していきます。



    1-7. 超改良版スピンボタンは コピペで簡単に複数設置可能

    今回のスピンボタンの利点は、コピペで簡単に複数設置出来ることです。

    画像

    このように簡単にコピーやフィルコピーで複数設置出来ます。

    前回のスピンボタン Ver5 と違って、数値をロックしなくても、他のチェックボックスにチェックが入ったままの状態でも、スピンボタン(チェックボックス)を操作した際に、他の行の結果(数値)は変化しません。

    これはかなり理想的ですね!



    2. 超改良版スピンボタンのロジックと反復計算の仕組みを(ふわっと)理解する

    超改良版スピンボタンをもう少しアレンジしたいところですが、先に謎多き反復計算の仕組みについて触れておきましょう。

    なお、反復計算は非常に不安定というか謎の多い仕様で、mirも完全に理解出来ているわけではありません。その点ご了承ください。

    上で書きましたが、今回のポイントは2つ

    1. 作業セルの数式は自セルを参照する循環参照とする

    2. 作業セルは必ず結果セルの下もしくは同じ行の右のセルに作成する

    これを説明します。


    2-1. 作業セルの数式は自セルを参照する循環参照とするのがおススメ

    理解しやすいように簡略化して、チェクボックスと 作業セルの一致だけを比較する式で動作を見てみましょう。

    画像

    いかがでしょうか?

    2行目の 単純に C2セルのチェックボックスを D2セルに入れた =C2 という式で参照した場合は、 B2セルの数式 =C2=D2 は常に一致(TRUE)となります。

    これは当然ですよね。

    しかし、3行目では D3セルの数式を =LET(自セル,D3,C3) として、あえて結果には関係ない数式を入れたセル自身を参照して、意図的に循環参照を発生させることで、B3セルの数式 =C3=D3 は、クリックした瞬間に一致しない(FALSE)という結果を返します。

    別のセルを編集(Deleteボタン)すると、もう1度計算が走り  =C3=D3 は一致する(TRUE)に変わります。

    この一致のズレをスピンボタン内のIF関数で条件判定して、対象のスピンボタンの結果(数値)を +1、-1、+0(そのまま) に制御しています。

    不思議ですよね。反復計算ではこんなことが起こるんです。



    2-2. 作業セルは必ず結果セルの下もしくは同じ行の右のセルに作成する

    もう1つのポイントは、作業セルの位置です。

    Googleスプレッドシートでは、反復計算モードとした際に

    画像

    このように、左から右、上から下へと順番に計算が実行されるようです。

    画像

    実際に

    1⃣ チェックボックス(スピンボタン)
    2⃣ 結果セル(数値が変わる)
    3⃣ 作業セル

    とした場合、

    1⃣のチェックボックスをクリックしてチェックをつけると、先に2⃣の結果セルの数式が計算されます。

    この時点では 3⃣ はまだ処理されておらず、H5は FALSE のままなので、

    E5<>H5 はTRUE判定となり D5+1 で 結果セル E6の数値が1増えます。

    その後、3⃣のセルが計算されて TRUEになる、こんな動きをしています。(肉眼ではわかりませんがw)

    ここで  3⃣の作業セルの位置を 2⃣の結果セルの上の行に移動にすると、

    画像

    計算の順番が変わってしまい先に3⃣が計算される、常に D5=G2 は成り立つので スピンが機能しません。

    同じ5行目でも、3⃣が結果セル2⃣の右側だと

    画像

    計算の順番は 3⃣ 作業セル → 2⃣ 結果セル なので、スピンボタンは機能せずです。

    しかし、一つ下の行であれば 2⃣結果セルの左側であっても

    画像

    計算の順番は  2⃣ 結果セル → 3⃣ 作業セル となるので、スピンボタンが機能します。

    なお、作業セルを別シートに作成することも可能と思われますが

    画像
    一応作業セルを別シートで非表示にしても機能したが・・・

    スピンボタンを複数配置した際に挙動が安定しなかった為、mirとしては 同じシート内に作成することをお勧めします。

    また、この反復計算モードにおけるセルの計算順番は Googleスプレッドシートの独自仕様である為、Excelでは同じことが出来ません。(そもそもExcelなら普通にスピンボタン使えばよいんですが)

    反復計算のロジックを完全に理解しようとすると沼りそうなので、

    「同じシートの右下に作業セル(後から計算したい処理)を入れる。」

    これだけ押さえておけばOKと思っておきましょう!



    3. 超改良版スピンボタン 機能追加

    ここからは、基本バージョンのスピンボタンを使いやすい形にアレンジしていきたいと思います。



    3-1. LET関数で式をわかりやすくする

    画像

    まずは修正しやすく、わかりやすくする為に、LET関数を使って式を整理しましょう。


    👆上の状態のD2セルの式を

    画像
    =LET(自セル,D2,チェックボックス,B2:C2,{チェックボックス})

    このように変更します。

    そして、A2セル(結果セル)の式を

    画像
    =LET(
      リセット,$E$1,結果,A2,
      プラス,B2<>D2,マイナス,C2<>E2,
      IF(リセット,0,結果+IF(プラス,1,IF(マイナス,-1,0)))
    )

    こうしておきましょう。

    セル、数式に名前をつけてわかりやすくなりました。



    3-2. ステップ(変化の値)を変更できるようにする

    続いて実用的なアレンジです。

    現在はチェックボックスの操作で、1ずつ増減する仕組みとなっていますが、一気に増やしたい(減らしたい)時に1ずつだと非常に面倒です。

    一気に10、50、100と変化の幅(ステップと言います)を切り替えられるように、プルダウンで「ステップ切り替え機能」を追加しましょう。

    画像

    チェックボックスの右に1列追加して、

    画像

    プルダウンで 1、5、10、50、100あたりをリストとして設定しておきましょう。

    プルダウンはチップ型でも通常の矢印型でもどちらでもOK。

    👇プルダウンもチェックボックス以上にディープなネタをまとめています。


    先ほどLET関数を組み入れた結果セルの数式にもステップを追記します。

    画像
    =LET(
      リセット,$F$1,結果,A2,ステップ,D2,
      プラス,B2<>E2,マイナス,C2<>F2,
      IF(リセット,0,結果+ステップ*IF(プラス,1,IF(マイナス,-1,0)))
    )

    結果+ステップ*IF(プラス,1,IF(マイナス,-1,0))

    これによって、ステップ分だけ増減するスピンボタンになりました。

    本当はプルダウンが未選択(空)の時には1とする式にしたいところですが、式が煩雑になるので「プルダウンは必ず選択しておく」という条件で利用するものとします。

    最初に1回 1を選んでおけばよいですし、あとは1を選んだプルダウンをコピペすればいいので、そこまで不便は感じないかと思います。

    画像

    一気に10刻みや100刻みが出来るようになったので、大きな数からスタートしたい場合にも対応できますね。



    3-3. 1つの式で複数スピンボタンを制御するスピル式にする

    これで1つのスピンボタンのセットが作成出来ました。

    これを普通にフィルコピーして複数スピンボタンにしてもいいんですが、

    画像

    縦並びのスピンボタンを 1つの数式(スピル式)で制御するようにしてみましょう。

    ここでスピル式に変更するメリットは後ほど解説します。

    画像

    まず結果範囲、作業範囲の先頭となる A2、E2セルの数式だけ残して、下のセルの数式を消しておきます。

    作業セル E2の数式を

    画像
    =LET(自セル,E2,チェックボックス,B2:C7,{チェックボックス})

    これは大丈夫ですね。

    そして 結果セル A2の数式を

    画像
    =ARRAYFORMULA(LET(
      リセット,$F$1,結果,A2:A7,ステップ,D2:D7,
      プラス,B2:B7<>E2:E7,マイナス,C2:C7<>F2:F7,
      IF(リセット,結果*0,結果+ステップ*IF(プラス,1,IF(マイナス,-1,0)))
    ))

    このようにします。

    結果セルをスピル式とする際の ポイントは2つ。

    1つは ARRAYFORMULA関数を一番外側に付けることで、 内部で配列処理し結果を配列として出力できるようにすること。

    もう1つがリセットボタンを押した時の 処理を

    IF(リセット,結果*0,

    このように 0でなく 結果*0とする点です。

    ここが 0だと、リセットボタンを押した時だけは配列が返らず

    画像

    先頭の数式を入れた結果セルだけ 0表示となり、下のセルが空白となってしまいます。

    結果*0 とすることで、結果範囲のセルに 0をスピルさせます。

    画像

    スピル数式変更後、動作に問題ないか確認しておきましょう。

    このスピル数式化は必ず両方をセットで行ってください。

    たとえば 作業セル側だけ スピル式にして、結果セルはそのまま1セル1数式としていた場合

    画像

    このように 2つ目以降のスピンボタンが機能しなくなります。

    これは重要なポイントとして上げた、セル位置による計算の順番が影響している為です。

    画像

    作業セル側の数式が E2に集約されると、A2セルの数式よりは 後に処理されますが、その下の行の結果セル A3やA4よりも E2の数式が先に 処理される為、チェックボックスと作業セルのズレが利用できなくなり、スピンボタンとして機能しません。



    4. ブラウザを閉じても結果を保持する為のセーブ機能を追加

    これで完成!のように見えますが、実はもう一つ大事な機能を追加する必要があります。

    それが、ブラウザを閉じても結果の値を保持する為のセーブ機能です。



    4-1. スピンボタン連打による短時間の計算実行はサーバーがついてこれない

    👇スピンボタンを利用後、F5でブラウザを更新すると

    画像

    更新前と数字が大幅に変わってしまいました。

    画像

    この原因は、チェックボックス連打による短時間の計算処理の繰り返しにサーバー側の処理がついてこれない為です。

    Googleスプレッドシートは、セルの数値を変更した際に、クライアント側(Chrome内)とサーバー側、それぞれで計算が実行されます。

    通常は同じ結果となるのですが、このような特殊な処理をした際は クライアント側(見えている結果)とサーバー側(真の結果)がズレることがあります。

    このズレに関しては、最近だと手動計算モードの回でも触れています。

    クライアント側とブラウザ側で結果がズレた場合、優先されるのはサーバー側の結果となります。

    その為、一度ブラウザを閉じたり F5で更新すると クライアント側のスピンボタンの結果は消えて、チェックによる計算の一部しか拾えていないサーバー側の結果に置き換わってしまうのです。



    4-2. プルダウンでクライアント側の計算結果を値化して保持する

    これを解決する方法が、前回のスピンボタンで利用した プルダウンによって 計算結果を値化するセーブテクニックです。

    解決策:プルダウンを 使って 値貼付け風を実現する

    まずは、シンプルなセーブ機能を実装してみましょう。作業列のさらに右側にセーブ用のプルダウン領域をつくります。

    画像

    結果セルと同じ行数分 セルを選択して 挿入 >プルダウンと進み、

    条件を「プルダウン(範囲内)」に切り替えます。

    範囲の箇所に =A2 と結果領域の先頭セルを指定します。

    ここは頭に =をつけるのがポイントです。

    =を付けずに単に A2として保存すると 勝手に$がついて絶対参照となります。

    ここは相対参照とする必要があるんです。

    画像

    また、このプルダウンはチップ型ではなく矢印の方が使い勝手が良いかと思います。これで「完了」ボタンをクリック。

    画像

    相対参照としたので、各行のプルダウンで選択できるのは、その行の結果数値だけです。

    最後にA2の結果セルの数式を

    画像
    =ARRAYFORMULA(LET(
      リセット,$F$1,結果,A2:A7,ステップ,D2:D7,セーブ,H2:H7,
      プラス,B2:B7<>E2:E7,マイナス,C2:C7<>F2:F7,
      IF(リセット,結果*0,IF(セーブ<>"",セーブ,結果+ステップ*IF(プラス,1,IF(マイナス,-1,0))))
    ))

    LET関数に変数 セーブ として H2:H7を追記し、

    IF関数のネストをもう1つ増やします

    IF(リセット,結果*0,IF(セーブ<>"",セーブ,

    👆優先度としては、

    ①リセットが✅されていたら 0(の配列)を返す
    ②セーブが空白でない(プルダウン選択されてる)なら、
     セーブのセルを返す

    このようになります。

    これで セーブ機能(プルダウン)を使って、閉じる前に結果を値化することが出来るようになりました。


    画像

    👇このように ブラウザ更新後は、プルダウンで選択(セーブ)しなかった下の3つの 結果は サーバー側の値に差し替えられ変わってしまいますが、

    画像

    上の3つのスピンボタンは、セーブをしたことで結果を保持出来ています。

    閉じる前に一手間増えますが、現状これより楽な方法は無く、このプルダウンによる値化が、数式スピンボタンの結果を保持する最適解となります。



    4-3. プルダウンを1つに集約し 一括セーブボタンとする

    とは言え、複数のプルダウンそれぞれにプルダウンを用意して、毎回1つずつ選択するのは面倒です。

    これをプルダウンを一つに集約して、一括セーブボタンにしましょう!

    ※一括セーブが使えるのは、結果セルの数式を 1つのスピル式に集約していることが前提となります。

    画像

    まずは、全ての結果セルの数値をまとめる為の作業セル(とりあえず I2セル)を用意し、そこに数式を入れます。

    =JOIN(",","🔒",A2:A7)

    JOIN関数でカンマ区切りで連結します。

    セーブして数値をロックしていことを明示する為に、頭に絵文字 🔒 をつけています。(💾 でもOK)

    もう1つ下の I3セルには '(シングルクォート)だけを入れておきます。

    画像

    これは無くてもいいんですが、あると結構便利なんです。(プルダウン化すると理由がわかります)

    この I1:I2 をリスト範囲とするプルダウンを作成します。

    とりあえず D1セルにでも設定しておきましょう。(セーブ用のプルダウンの場所はどこでもOKです)

    画像

    ステップのプルダウンと区別する為に、チップ型でなく矢印型としておくのがおススメです。

    最後に 結果セルの数式を変更します。

    画像
    =ARRAYFORMULA(LET(
      リセット,$F$1,結果,A2:A7,ステップ,D2:D7,セーブ,D1,
      プラス,B2:B7<>E2:E7,マイナス,C2:C7<>F2:F7,
      IF(リセット,結果*0,IF(セーブ<>"",TOCOL(SPLIT(セーブ,"🔒,")),結果+ステップ*IF(プラス,1,IF(マイナス,-1,0))))
    ))

    セーブという変数の中身を D1セル とし、

    IF(セーブ<>"",TOCOL(SPLIT(セーブ,"🔒,"))

    セーブが空でない(プルダウン選択状態)なら、🔒と,(カンマ)で セーブの中身を SPLIT関数で分割し、TOCOL関数で縦1列に変換しています。

    これで 一度 JOIN関数で1つにまとめた結果セルの値を、配列として 結果セルに返すことができます。(循環しているので頭がこんがらがりそうですが)

    不要になった 個別セーブ用の プルダウン列は削除してOKです。

    画像

    先ほど入れたシングルクォートだけのセルをプルダウンのリスト範囲に含めたことで、

    画像

    このようにプルダウンの選択肢に空白(セーブ解除)を表示させることが出来ます。

    シングルクォートのみ入れたセルは 空文字扱いとなり、セーブ<>"" がFALSE判定となるので、マウスクリックだけで セーブ解除が出来るわけです。(これが無いと解除の為には キーボード操作が必要)

    これで超改良版 スピンボタン 完成です!



    5. 超改良版スピンボタンまとめ

    改めて超改良版スピンボタンの 完成形の数式をおさらいし、動作を確認しましょう。


    5-1. 超改良版スピンボタン 数式確認

    画像

    このような縦並びのスピンボタンを作りたい場合は、まずは 反復計算(計算回数1)をONにして、3ヵ所にスピル数式を入れます

    A2セル(結果セル)

    =ARRAYFORMULA(LET(
      リセット,$F$1,結果,A2:A7,ステップ,D2:D7,セーブ,D1,
      プラス,B2:B7<>E2:E7,マイナス,C2:C7<>F2:F7,
      IF(リセット,結果*0,IF(セーブ<>"",TOCOL(SPLIT(セーブ,"🔒,")),結果+ステップ*IF(プラス,1,IF(マイナス,-1,0))))
    ))


    E2セル(作業セル)

    =LET(自セル,E2,チェックボックス,B2:C7,{チェックボックス})


    H2セル(セーブ用プルダウンリストセル)

    =JOIN(",","🔒",A2:A7)


    そして、チェックボックスとプルダウンを設定。

    画像

    B2:C7 ・・・ スピン用チェックボックス

    F2 ・・・ リセット用チェックボックス

    D1 ・・・ セーブ用プルダウン (範囲 H2:H3) ※ H3には シングルクォート

    D2:D7 ・・・ ステップ用プルダウン (リスト 1,5,10,50,100)

    位置は ドラッグで変えることが可能です。


    5-2. 完成形 超改良版 複数スピンボタンの動作確認

    動作を確認しましょう。

    画像

    ブラウザを閉じる前(F5更新前)に プルダウンでセーブしたことで、更新前のスピンボタンの結果が保持されています。

    更新後は プルダウンで空(シングルクォート)を選択し セーブを解除すれば、保存した続きから再度スピンボタンで操作出来ています。

    超改良版スピンボタン、動作もOKですね!



    5-3. プルダウンセーブボタンの最大の利点

    今回紹介したスピンボタンは、今現在の Googleスプレッドシートの循環参照の特殊な仕様を使った、いわば抜け道的なテクニックとなります。

    その為、今後Google側でスプレッドシートの計算処理の仕様変更があった場合、いきなり勝手に結果数値が変わってしまう、消えてしまうといったリスクがあります。

    ただ、仮に結果が吹っ飛んだとしても プルダウンでセーブしていれば

    画像

    セーブ用のプルダウンのセルを右クリックして「編集履歴を表示」することで、

    画像

    指定した日時の時点での結果数値がどうだったか?をログとして確認することが出来ます。

    計算結果は 履歴を遡っても正しい保証はありませんが、プルダウンで値化したものであれば 確実に履歴から確認・復元が出来るということです。

    これなら実務利用でも安心ですね。(それでもクリティカルな業務にはおススメはしません)



    5-4. 超改良版 スピンボタンのアレンジ

    画像

    計算の順番(左から右、上から下)を意識した配置であれば、作業セルの列を非表示にしたり、条件付き書式を組み合わせたりと アレンジは自由です。

    ちなみに、結果セルをまとめるJOIN関数を入れるセルの位置も重要です。

    👆上は 結果セルのスピル式 A2セルと同じ行の 右側 H2セルなので問題ありませんが、

    画像

    👆このように JOINで結果を結合する式を H1と、結果セルの数式より上のセルに入れてしまうと、結果セルの計算の前に H1セルの数式が処理されてしまう為、常に一つ遅れた結果となります。

    画像

    うまく動かない時は、まずは数式を入れているセルの場所が正しいか?を確認しましょう。



    5-5. データベースと組み合わせた活用

    スピンボタンはデータベース型の表と組み合わせることで、さらなる活用が出来ます。

    👇 たとえば、別シートに このようなテーブルがあったとします。

    画像


    これに CHOOSEROWS関数とスピンボタンの結果数値を組み合わせれば、DBのデータをクリックで順番に表示させることが出来ます。

    画像

    ただし、👆 このように スピンボタンの結果セルが、結果セルの数値を参照して DBからデータを取り出す CHOOSEROWS関数が入ったセルより 右側、もしくは下のセルにあると

    画像

    計算順序の影響で結果が1つ遅れて反映となってしまいます。

    その為、利用する スピンボタンの結果セル(列)は、DBの表示領域よりも左側にもってくる必要があります。

    画像

    スピンボタンの結果を A列としてB2セルの数式が

    =IFERROR(CHOOSEROWS(ポケモン,A2))

    となれば、ズレが発生することなくスピンボタンを利用したDBビューが可能となります。

    この利点は、テーブルを利用することでデータ範囲に最終行に自動でフィットすること、そして CHOOSEROWS関数は 第2引数がマイナスの値の場合は下から参照できるという点です。

    その為、初手でマイナスのスピンをすると、結果は -1ですが、

    画像

    データは一番下の No.151 ミュウを表示させることが出来て、そのまま逆順でめくることが出来ます。

    こんなの、これからは Sheets Canvas でAI生成できるからいらんだろ、と思うかもしれませんが、実は Sheets Canvasは セル内の画像や URLから画像を出力することが出来ません。(2026年10月時点)

    もちろんGASでWebアプリ化する方法もありますが、スプレッドシートだけで手軽に実現できるのは魅力ですよね。



    5-6. GASなし。だからスマホアプリでも使える

    さらに、このスピンボタンはGAS不要なので、スマホアプリ版のスプレッドシートからも利用が可能なんです。

    画像

    スマホのタップだと、画面キーボードが表示されやすいのが難点ですが、一応パソコン上のブラウザ操作と同じように スピボタン、リセットボタン、セーブプルダウンが利用できます。

    スマホからも使えるのは良いですね!



    チェックボックスの可能性は∞

    今回はGoogleスプレッドシートで、チェックボックスを利用してスピンボタン風機能を実装する方法を紹介しました。

    Googleスプレッドシートだと当たり前に使ってる、セル内チェックボックスですが、実は Excelだと365版とWeb版でしか使えないんです。

    買い切り版だと最新の Excel2024でも、セル内チェックボックスは利用出来ません。

    このセル内に配置できて数式で参照できるGoogleスプレッドシートの チェックボックスに、循環参照や数式、フィルタビュー、テーブル、GASなど色々な機能を組み合わせたユニークなネタをマガジンにまとめています。

    実用的か?と聞かれると微妙なのも多いんですが、チェックボックスの可能性を感じていただけると思うので、興味があれば 過去noteも読んでみてください。

    今回は長くなったので、次回は軽めのQuickTipsを予定。(のつもりでしたが、今回の関連ネタをもう1回やろうかなと。)

     
     

    mir

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

    あなたへのおすすめ