メむンコンテンツぞスキップ
芋出し画像

Googleスプレッドシヌト プルダりンリスト掻甚術 4テヌブル + 数匏で連動プルダりン

    前回の続きのGAS回ではなく、話は倉わりたすがプルダりンリスト シリヌズを曞きたいず思いたす。

    プルダりンリストも 番倖線の「耇数遞択プルダりン」を入れるず 今回で5本目。ずいうわけでマガゞンにたずめたした。

    前回のnoteは Googleスプレッドシヌトの画像を保存する方法の完党版っおこずで、セル䞊画像、セル内画像を䞀括保存するテクニックに぀いおたずめたした。



    テヌブル機胜 + 数匏 で連動プルダりンを実珟しよう

    今回のゎヌルは Googleスプレッドシヌトではなかなか難しい連動プルダりンの䜜成です。

    基本の2段階止たりではなく 3段階以䞊のプルダりンを簡単に䜜る方法を玹介したす。

    連動プルダりンっおなにっお人は、先に プルダりンシリヌズの第2回「連動プルダりンの基本」をお読みください。

    ずりあえず䜜りたいのは ↓こんなや぀です。

    画像

    「あれ3連プルダりンっお プルダりンシリヌズの第3回でやっおなかったっけ」ず気づいた奇特な人は、盞圓な読者ですね。ありがずうございたす。

    3連プルダりン䜜成方法は 「Googleスプレッドシヌト プルダりンリスト掻甚術3 連動プルダりン応甚」の回で䞀床玹介しおいたす。

    これは プルダりンを操䜜するシヌトに個別の䜜業領域を䜜らず、1぀のマスタず1぀の匏で実珟する3連以䞊に拡匵可胜な連動プルダりンで、自分ずしおは割ず気に入っおるんですが、

    ・匏や蚭定が耇雑でややこしすぎる぀たり面倒
    ・耇数のシヌトで連動プルダりンを実珟するのが難しい
    ・同時に耇数人が操䜜するケヌスに察応できない

    ずいう欠点がありたした。

    これらを解消するのが、今回玹介する 「テヌブル機胜  数匏」で実珟する連動プルダりンです。

    以前の方法に比べるずぐっず簡単で、1぀のマスタず1぀の匏があれば 連動プルダりンが実珟できたすし、そのシヌトをコピヌしお利甚するこずも可胜です。

    ただし、プルダりンリストず同じ行に 遞択肢ずしお参照させる為の䜜業領域が必芁ずなりたす。

    画像

    列を非衚瀺ずすれば問題ないず思いたすが、どうしおも同じシヌト内に䜜業領域を䜜れない䜜りたくないずいう堎合は、察応できたせんのでご了承ください。

    それでは䜜り方を芋おいきたしょう


    Step1. 遞択肢のマスタテヌブルを䜜る

    画像

    第3回の時ず同じマスタを䜿っお3連プルダりンを䜜っおみたしょう。

    たずは元になるマスタテヌブルを䜜りたす。

    これは䞊の画像のようなリスト圢匏ずする必芁がありたす。

    サンプルデヌタはコチラ ↓  コピペしお利甚

    項目1	項目2	項目3
    肉類	豚肉	ポヌク゜テヌ
    肉類	豚肉	トンカツ
    肉類	豚肉	しょうが焌き
    肉類	鶏肉	チキン゜テヌ
    肉類	鶏肉	唐揚げ
    肉類	鶏肉	チキンカツ
    肉類	牛肉	すき焌き
    肉類	牛肉	しゃぶしゃぶ
    肉類	牛肉	ステヌキ
    野菜	キャベツ	千切り
    野菜	キャベツ	ロヌルキャベツ
    野菜	キャベツ	野菜炒め
    野菜	ニンゞン	グラッセ
    野菜	ニンゞン	シリシリ
    野菜	ニンゞン	ナムル
    野菜	タマネギ	スヌプ
    野菜	タマネギ	マリネ
    野菜	ゞャガむモ	肉じゃが
    野菜	ゞャガむモ	じゃがバタヌ
    野菜	ゞャガむモ	ポテトフラむ
    果物	リンゎ	アップルパむ
    果物	リンゎ	リンゎゞャム
    果物	バナナ	クレヌプ
    果物	バナナ	スムヌゞヌ
    果物	ミカン	ゞュヌス
    果物	ミカン	れリヌ
    果物	むチゎ	むチゎゞャム
    果物	むチゎ	倧犏
    果物	むチゎ	ショヌトケヌキ

    このデヌタを Googleスプレッドシヌトの新機胜 で テヌブル化

    画像
    衚瀺圢匏  テヌブルに倉換

    テヌブル名は「マスタ」ずしおおきたしょう。

    画像

    テヌブルにする利点はデヌタ範囲を テヌブル名で呌び出せる参照できるこずです。

    画像

    芋出しを陀くデヌタ郚分を䞞ごず呌び出したい堎合は

    =ARRAYFORMULA(マスタ)

    こんな匏で実珟できたす。シヌト名を気にする必芁がないので䟿利ですね。

    テヌブル機胜に぀いお詳しく知りたい方は、noteにたずめおいたすのでそちらも参考にしおください。



    Step2. 別シヌトで1段目のプルダりンを䜜る

    画像

    次に別シヌトにプルダりンを甚意したしょう。

    1行目を芋出し行ずしお、2行名以降のAC列をプルダりン甚、1列あけお E列から右偎を䜜業領域ずしたす。

    たずは A2項目1のプルダりン甚の 遞択肢を E2から暪に出力したいんですが、マスタ ずいうテヌブル名を䜿っおどのような匏を組めばよいでしょうか

    簡単ですが、これをお題でいっおみたしょう。


    Q1. マスタの項目11列目のデヌタを プルダりン甚の遞択肢ずしお暪方向に䞊べお出力したい

    画像

    テヌブルは構造化参照が出来るのが利点で、項目11列目のデヌタは

    =ARRAYFORMULA(マスタ[項目1])

    こういう蚘述で取埗もできるんですが、これだず芋出しが違うケヌスで面倒なんでこの「芋出し名」を䜿う曞き方は䜿わないものずしたす。

    E2にはどんな匏をいれれば良いでしょうか考えおみたしょう









    ↓↓↓
    回答は以䞋
    ↓↓↓




    A1. マスタの項目11列目のデヌタを プルダりン甚の遞択肢ずしお暪方向に䞊べお出力する匏

    回答です。

    画像

    =TOROW(UNIQUE(INDEX(マスタ,,1)))

    マスタから 1列目を INDEX関数で取埗しお、それをUNIQUE関数で重耇排陀。最埌にTOROW関数で暪1行デヌタに倉換する。

    これは簡単ですね。

    画像

    Googleスプレッドシヌトの堎合は、マスタ党䜓を出力する堎合は ARRAYFORMULAを付ける必芁があるんですが、INDEX関数にはARRAYFORMULAず同じ効果があるので省略するこずができたす。

    たた、Googleスプレッドシヌトのプルダりンは 自動で重耇排陀しおくれるんですが、無駄に暪に長い䜜業領域になっおしたうこずを避ける為に、UNIQUE関数で重耇を排陀しお出力しおいたす。

    ここたでは 瞊1列デヌタなんで、最埌に暪にする為に TOROWを䜿いたす。

    画像

    ケヌスによっお 出力範囲が倉わっおくるので、プルダりンの範囲は暪のお尻を決めない曞き方

    E2:2

    ずしおおきたしょう。

    画像

    1段目のプルダりンが出来たした。


    Step3. 項目1で遞択した倀で項目2のプルダりンを生成する

    画像

    続いお 1段目の遞択で絞り蟌んだ項目2の遞択肢を䜜業領域に衚瀺させたす。

    ただ、ここで泚意すべきは そのたた䞊の画像のように 項目2の遞択肢を出力しおしたうず、項目1で遞択した「野菜」がプルダりン甚䜜業領域にない為、

    画像

    このように゚ラヌ衚瀺が出おしたいたす。

    無芖しおもいいんですが、せっかく今回は改良版なんで ゚ラヌを回避する為に

    画像

    このように 項目1が空欄ではない遞択された堎合は、項目2の前巊に項目1で遞択した倀を付けお出力するようにしたす。



    【Point1】プルダりン範囲内 は 範囲の頭にむコヌルを付けお蚭定するず盞察参照になる

    ここで項目1、項目2のプルダりンを䞀気に蚭定する際

    画像

    範囲を =E2:2 ず頭に = を付けるのがポむントです。

    単玔にむコヌルを付けずに E2:2ず蚭定しおしたうず

    画像

    「完了」で保存するず 自動で E2:2 で蚭定した範囲は 絶察参照の 

    =$E$2:$2

    ず眮き換えられおしたいたす。

    これでは行も列も固定されおしたっおいるので、

    画像

    このようにどのプルダりンも 同じ E2:2の遞択肢が衚瀺されおしたいたす。

    ここを =E2:2ず プルダりン範囲を 盞察参照にするこずで

    画像

    䜜業領域を行ごずに遞択肢範囲ずしお読み蟌み、か぀ 項目1の時は E列から、項目2の時は䞀぀ズレおF列から ず範囲を盞察的にズラしお 芋おくれたす。

    これによっお、項目1で遞択した「野菜」ずいう遞択肢は 項目2のプルダりンには衚瀺されない仕様ずなりたす。

    これで範囲指定は䞀発でいけたすね。



    Q2. 項目1で遞択した倀を巊に連結しおマスタを絞り蟌んだ項目2を暪に䞊べたい

    それでは肝心の E2 に入れる匏を考えたしょう。

    A2が空欄だった時は、 =TOROW(UNIQUE(INDEX(マスタ,,1)))

    でマスタの項目1を遞択肢ずしお衚瀺し、

    画像

    A2が空癜ではない堎合は、A2の倀ず マスタの項目1をA2の倀で絞り蟌んだ 項目2をナニヌクにした倀を暪に䞊べたい。

    どんな匏を䜜ればよいでしょうか範囲ずしお匏内で䜿えるのは テヌブル名のマスタ、そしお A2 のみずしたす。

    考えおみたしょう









    ↓↓↓
    回答は以䞋
    ↓↓↓




    A2. 項目1で遞択した倀を巊に連結しおマスタを絞り蟌んだ項目2を暪に䞊べたい

    回答です。

    画像

    =TOROW({A2,TOROW(UNIQUE(IF(A2="",INDEX(マスタ,,1),
    FILTER(INDEX(マスタ,,2),INDEX(マスタ,,1)=A2))))},1)

    これは幟぀か匏の曞き方があるので回答の䞀䟋ず思っおください。

    たず、A2セルの連結は眮いずいお、先にIFの分岐凊理を考えたしょう。

    A2セルが空癜なら 項目1、A2セルが 空癜ではないプルダりンが遞択されおいるなら、マスタの項目1をA2の倀で絞り蟌んだ 項目2を ナニヌクにしお暪䞊びに衚瀺したい。

    ずいう分岐なので、「ナニヌクにしお暪䞊びに衚瀺」はどちらのパタヌンでも必芁な共通の凊理です。ずいうわけで IFは

    TOROW(UNIQUE(IF(A2="",

    このように TOROWずUNIQUEの䞭に曞いちゃいたしょう。

    空癜だった時はマスタの1列目を返せばよいので、INDEX(マスタ,,1) ですね。

    空癜ではない時画像の堎合だず「野菜」を遞択した時は、マスタの項目1がA2ず䞀臎するずいう条件で絞り蟌んだ時の 項目2を出力したいので、FILTER関数の出番です。

    FILTER(INDEX(マスタ,,2),INDEX(マスタ,,1)=A2)

    この FILTER関数の䞭にIFを入れちゃう曞き方もありたすが、凊理が重くなるのでここでは避けおいたす。

    画像

    たずはここたで出来たした。

    最埌に A2の連結ですが、ここはIF内での連結、TOROWで空癜陀去しおから連結、䞭カッコではなく HSTACKを䜿う方法など色々方法はありたす。

    回答だず { , } で暪に連結しおから 最埌に TOROWずしおいたす。

    =TOROW({A2,TOROW(UNIQUE(IF(
 )},1)

    この䞀番倖偎の TOROWは第2匕数を1に指定した 空癜を陀去しお巊に詰める為の TOROWだず考えおください。

    ずりあえずは2段階プルダりンができたした。



    Step4. 3段階プルダりンを考える

    ここから䞀気に難しくなりたす。

    画像

    3段階目のプルダりンを連動させるにあたり、以䞋を考える必芁がありたす。

    ・今、䜕段階目たでプルダりンが遞択されおいるのか

    これをなるべくシンプルに考える為に プルダりン範囲の行 A2:C2を芋お、倀の入ったセルが幟぀あるか で刀定するこずにしたしょう。

    ※「必ずプルダりンは 巊から順に遞択する」ずいう運甚䞊の条件を必芁ずしたす。

    これだったら COUNTA関数で簡単に取埗できたすね。

    画像
    COUNTA(A2:C2)が2 ・・・ 2段階たで遞択されおいる

    ここから 汎甚性を高めるためにLET関数を䜿っおいきたしょう。

    LETを䜿っお

    画像

    =LET(
     pr,A2:C2,
     m,マスタ,
     c,COUNTA(pr),

    prは プルダりンロりプルダりン行の意味です。

    このように眮きたす。

    そうするず 仮に 項目2たで遞択された状態の時 c は 2 ずなり、次は項目3のプルダりンを遞ぶこずになるので、FILTERで絞り蟌んで結果ずしお出力する列は マスタの項目3の列、぀たり

    画像

    INDEX(m,,c+1) ずなりたす。これを col ずおきたす。

    =LET(
     pr,A2:C2,
     m,マスタ,
     c,COUNTA(pr),
     col,INDEX(m,,c+1),

    玠材の䞋準備が出来おきたした。料理に䌌おいたす

    次にIF関数の分岐を考えたす。2段階の時は IF(A2="", ずしたしたが、倚段階で考えた堎合は

    ただ䜕も遞択されおいない状態の時は c ぀たり COUNTA(pr) は 0ずなるので、これは 真停倀だず FALSEになりたす。

    この時、プルダりンの遞択肢ずなるのは マスタの項目1の列です。

    画像

    ぀たり、Q2の回答の匏にあおはめるず cが1以䞊の時TRUEは FILTERの匏で絞り蟌んだ col を 返し、cが 0の時FALSEは、そのたた colを返す

    TOROW(UNIQUE(
     IF(c, 
     FILTER(col,なんかの条件匏),
     col )
    ))

    その結果をUNIQUEしおTOROW。最埌に pr ず暪連結しお空癜陀去。

    こんな匏を䜜れば良さそうですね

    では、この「なんかの条件匏」郚分をかんがえおみたしょう。



    Q3. 項目1、項目2で絞り蟌んだマスタの項目3を重耇排陀しお暪方向に䞊べたい

    画像
    =LET(
      pr,A2:C2,m,マスタ,c,COUNTA(pr),col,INDEX(m,,c+1),
      TOROW(UNIQUE(
        IF(c,
          FILTER(
            col,
            【条件匏】
          ),
          col
        )
      ))
    )

    連動プルダりンを実珟する匏の「条件匏」の郚分を考えおみたしょうずいうお題です。

    挑戊しおみたしょう









    ↓↓↓
    回答は以䞋
    ↓↓↓




    A3. 項目1、項目2で絞り蟌んだマスタの項目3を重耇排陀しお暪方向に䞊べる匏

    回答です。

    画像
    =LET(
      pr,A2:C2,m,マスタ,c,COUNTA(pr),col,INDEX(m,,c+1),
      TOROW(UNIQUE(
        IF(c,
          FILTER(
            col,
            BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
          ),
          col
        )
      ))
    )

    条件匏郚分は

    BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))

    こんな匏で実珟できたす。他の曞き方もありたす

    たずFILTER内の匏なのでARRYAFORMLAず同じ効果があり、配列凊理が可胜です。

    pr=m の郚分は

    画像

    このように列単䜍で prずmが䞀臎したらTRUEを返す匏ずなっおいたす。

    ここで列1、列2の䞡方がTRUEずなっおいる 郚分、぀たり 野菜  ニンゞン ずなっおいる郚分の 列3を取り出せばよいわけです。

    そうするず 必芁なのは列1、列2だけで 列3は䞍芁ですね。

    なので、

    CHOOSECOLS(pr=m,SEQUENCE(c))

    CHOOSECOLS関数ずSEQUENCE関数で 1列目、2列目だけを取り出したす。

    ほんずはExcelみたいにTAKE関数があるずいいんですが。。


    画像
    画像の堎合は c は2

    cは prの空癜でないセルの数なので、䞊の堎合は2ずなりたす。

    SEQUENCE(2) は {1:2} ずなるので、これを CHOOSECOLSの第2匕数ずするこずで、pr=m の結果配列の1列目ず2列目だけを抜出できたす。

    あずはこれを 行毎に芋お 党おTRUEの行だけTRUEずなる1列の配列を䜜ればいいので、BYROWで行毎に AND関数ずすればよいです。

    画像

    BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))

    この匏 が回答の 「条件匏」です。あずはFILTER関数ず組み合わせお

    画像

    FILTER( col, BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r))) )

    ずするこずで、項目1が 野菜、項目2が ニンゞン ず䞀臎する項目3だけを取埗できたした。



    FILTERの結果ず pr を暪に結合する

    このFILTERの結果をUNIQUEしおTOROWしおから、プルダりンで遞択した倀ず暪に連結したす。

    先ほどのFILTERの結果を xず眮いお 最埌に

    TOROW({pr,x},1)

    ずすれば

    画像

    このように暪に連結ができたした。

    ただ、これだず

    画像

    プルダりンを最埌の3段目たで遞んでしたうず ゚ラヌが出おしたいたす。

    これは プルダりンを党お遞択するず c ぀たり COUNTA(pr) が 3ずなり、col,INDEX(m,,c+1), はINDEX(m,,4)ずなっおしたい、3列のデヌタであるm に存圚しない 4列目を取り出そうずしおいる為です。

    気にしなくおもいいんですが、ここは 最埌の TOROW({pr,x},1) を TOROW({pr,x},3) ずしお、TOROWで 空癜ず゚ラヌの䞡方を無芖すれば 解消できたす。

    ずいうわけで 䜜業スペヌスに出力する 3段階プルダりン匏倚段階プルダりン匏は

    =LET(
      pr,A2:C2,m,マスタ,c,COUNTA(pr),col,INDEX(m,,c+1),
      x,TOROW(UNIQUE(
        IF(c,
          FILTER(
            col,
            BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
          ),
          col
        )
      )),
      TOROW({pr,x},3)
    )

    このようになりたす。

    画像

    サンプルは3段階ですが、これは 4段階、5段階の倚段階プルダりンにも拡匵察応できる匏になっおいたす



    Step5. 耇数行のプルダりンにも䞀぀の匏で察応する

    画像

    行単䜍のプルダりン匏は完成したしたが、今回目指しおいるのは䞀぀の匏で完結させるこずです。

    ぀たり䞊の画像のように 3段階の連動プルダりンが、2行目から8行目たでの 7セットあった堎合、これをE2 に䞀぀匏を入れるだけで実珟したいっおこずです。

    これが実珟できれば完成です。最埌のお題いっおみたしょう



    Q4 . 1行察応の連動プルダりン匏を 耇数行に察応する匏に倉曎したい

    =LET(
      pr,A2:C2,m,マスタ,c,COUNTA(pr),col,INDEX(m,,c+1),
      x,TOROW(UNIQUE(
        IF(c,
          FILTER(
            col,
            BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
          ),
          col
        )
      )),
      TOROW({pr,x},3)
    )

    この匏を耇数行 範囲 A2:C8に察応させたいずいうお題です。

    ずりあえず、 A2:C8 のセル範囲を p ず眮いお凊理をしたしょう。

    出だし郚分は

    =LET(
      p,A2:C8,m,マスタ,

    こうなりたす。

    考えおみたしょう









    ↓↓↓
    回答は以䞋
    ↓↓↓




    A4. 1行察応の連動プルダりン匏を 耇数行に察応する匏に倉曎する

    回答です。

    画像
    =LET(
      p,A2:C8,m,マスタ,
      BYROW(p,
        LAMBDA(pr,
          LET(c,COUNTA(pr),col,INDEX(m,,c+1),
            x,TOROW(UNIQUE(
              IF(c,
                FILTER(
                  col,
                  BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
                ),
                col
              )
            )),
            TOROW({pr,x},3)
          )
        )
      )
    )

    LETしおBYROWしお、たたLETしおBYROWする・・・。なかなかヘビヌな匏ですね。

    プルダりン領域 A2:C8から 1行ず぀取り出しお凊理をしたいので BYROW関数を䜿いたす。

    =LET(
      p,A2:C8,m,マスタ,
      BYROW(p,
        LAMBDA(pr,

    取り出した行は pr ずしたす。さっきA2:C2 をprずしたので、この埌の匏でそのたた䜿いやす為

    さらに行単䜍で COUNTA(pr) 、次のプルダりン遞択肢ずなる 列 INDEX(m,,c+1) を倉数化しお凊理したいので、BYROWの䞭でもう1回 LET関数を䜿いたす。

    LAMBDAヘルパヌ関数を䜿った耇雑な匏では、LETを入れ子で䜿うケヌスも結構ありたす。

    =LET(
      p,A2:C8,m,マスタ,
      BYROW(p,
        LAMBDA(pr,
          LET(c,COUNTA(pr),col,INDEX(m,,c+1),

    あずは BYROW内のLET匏内で 先ほどの匏を再珟すれば良いですね。

    =LET(
      p,A2:C8,m,マスタ,
      BYROW(p,
        LAMBDA(pr,
          LET(c,COUNTA(pr),col,INDEX(m,,c+1),
            x,TOROW(UNIQUE(
              IF(c,
                FILTER(
                  col,
                  BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
                ),
                col
              )
            )),
            TOROW({pr,x},3)
          )
        )
      )
    )
    画像

    E2の䞀぀の匏で耇数行の連動プルダりンが凊理されおたすね。

    耇数行察応の連動プルダりン匏の完成です



    Step6. 4連プルダりンぞ拡匵可胜かを確認する

    今回䜜成した連動プルダりン匏の拡匵性を確認したしょう。

    3段階→4段階ぞの拡匵の手順を芋おいきたす。

    たずマスタの方を4列構成に倉曎したす。

    テヌブルなので、芋出し行の䞀番右の項目3の端にカヌ゜ルを圓おるず ボタンで列を右に挿入できたす。

    画像

    芋出しを項目4ずしお、ずりあえずサンプルなんで ポヌク゜テヌだけ 2行にしお4段目の遞択肢を入れおおきたしょう。

    画像

    ぀ぎにプルダりンがあるシヌトの方も拡匵したす。今回は䜜業範囲ずプルダりン範囲が1列しか空いおいないので、先に空癜列を1列远加したしょう。

    こちらはテヌブルにしおいないので、D列を遞択しお右クリックから巊に1列远加ずしたす。

    画像

    項目3の C列をD列にコピペしお D列を4段目のプルダりンにしたす。

    画像

    プルダりン範囲の遞択枈みの箇所をDeleteで消しお芋出しを項目4ずすればOK。

    この手順だず自動で プルダりン範囲がD列たで盞察参照で拡匵されたこずになりたす。

    画像

    最埌に匏の冒頭郚分 =LET( p,A2:C8 の プルダりンセル範囲を
    =LET( p,A2:D8 ずD列たに修正すれば完成です。

    画像

    LET関数を䜿うず数匏の修正メンテナンスが簡単になりたすね。

    3段目でポヌク゜テヌを遞択しお確認するず

    画像

    4段階の連動プルダりンになっおたすね

    倚段階プルダりンの拡匵も確認できたした。



    Step7. プルダりン範囲もテヌブル化する

    今回䜜成した倚段連動プルダりン匏ですが、プルダりン範囲の指定を

    =LET( p,A2:C8

    ず8行目たでずしおいたす。

    あえお =LET( p,A2:C ずお尻最終行を指定しない曞き方にしおいないのは理由があっお、数匏では参照しおいるセルがプルダりンかどうかを刀別できない為です。

    画像

    もし範囲を A2:Cずしおしたうず、プルダりン蚭定されおいない行も含めシヌトの最終行たで 1段目のプルダりン遞択肢が衚瀺され無駄な蚈算が倚い状態ずなりたす。

    匏内で最終行を固定せず 参照するプルダりン範囲を倉動にする方法が、プルダりン範囲のテヌブル化です。

    画像

    芋出し行を含めプルダりンのセル範囲を遞択した状態で

    右クック  テヌブルに倉換

    でテヌブル化しお適圓なテヌブル名を぀けたす。

    画像

    数匏偎の冒頭

    =LET( p,A2:C8 の箇所を =LET( p,プルダりンテヌブル_1

    ずテヌブル名指定にするず

    画像

    このようにテヌブルの範囲プルダりンの範囲たで数匏の結果が展開されたす。

    プルダりンを拡匵したい堎合は、テヌブルなので 最終行にポむンタをあおた時に衚瀺される + ボタンから

    画像

    このように行を远加するず テヌブルの拡匵に䌎い、プルダりンも拡匵され、合わせお数匏範囲も拡匵されたす。䟿利

    ただしプルダりン範囲をテヌブル化しおしたうず、先ほど Step6で觊れた 3連 → 4連 のような 列の拡匵によるプルダりンの盞察参照に察応が出来たせん。

    テヌブル内のプルダりンは、セル範囲に察しおのデヌタの入力芏制ではなく

    画像
    範囲を遞択しおデヌタの入力芏制を確認しおもなにも衚瀺されない
    画像

    列の型ずいう扱いになっおいる為です。

    これだずプルダりン範囲を列方向暪方向に盞察参照しおくれない為、プルダりンの連動する段数を拡匵する時にちょっず面倒です。

    たあ運甚䞭にプルダりンの連動数が倉わるこずはあたり無いんで、可胜であればプルダりン範囲のテヌブル化はおススメです。



    今回の連動プルダりン匏のメリット・デメリット

    最埌にたずめです。



    プルダりン範囲もテヌブル化した 倚段階連動プルダりン匏

    =LET(
      p,プルダりンテヌブル_1_,m,マスタ,
      BYROW(p,
        LAMBDA(pr,
          LET(c,COUNTA(pr),col,INDEX(m,,c+1),
            x,TOROW(UNIQUE(
              IF(c,
                FILTER(
                  col,
                  BYROW(CHOOSECOLS(pr=m,SEQUENCE(c)),LAMBDA(r,AND(r)))
                ),
                col
              )
            )),
            TOROW({pr,x},3)
          )
        )
      )
    )

    ※プルダりンテヌブル_1 は プルダりンを蚭定したテヌブル
    ※マスタは 別シヌトの遞択肢のマスタデヌタのテヌブル

    画像

    このようにテヌブル機胜ずシヌト関数を駆䜿したちょっず耇雑な匏を組み合わせるこずで、倚段階連動プルダりンを実珟できたす。



    この方匏で連動プルダりンを実装するメリット

    ■数匏を入れる箇所が1カ所だけでよい
     匏を入れるセルは1぀だけなんで、コピペで䜿えたす。
     たた、LET関数で指定する範囲は2ヵ所だけなのも簡単ですね。

    ■プルダりンの蚭定が1回だけでよい
     プルダりン化するセル範囲を指定しお、デヌタの入力芏制で プルダりン範囲内で、 䜜業範囲の行を 盞察参照指定 䟋 =E2:2

    ■シヌトをコピヌしおそのたた䜿える
     同じマスタを䜿った連動プルダりンを他のシヌトで䜿いたい堎合も、シヌトをコピヌするだけで簡単に䜿えたす。

    ■各行のプルダりンが独立しおいるので、耇数人で同時に䜿える

    画像

    共有しお同時に䜜業しおいた堎合でも、耇数人がそれぞれ自分の行のプルダりンを操䜜できたす。たた、遞択途䞭の行があっおも他の行には圱響がありたせん。

    ずいうわけで、以前の3段階連動プルダりン匏に比べるず簡単か぀利䟿性が向䞊しおいるず蚀えたす。



    この方匏で連動プルダりンを実装するデメリット

    䞀方でデメリットもありたす。

    ■プルダりンの 行単䜍分 䜜業セルが必芁

    画像

    冒頭にも曞きたしたが、各行のプルダりンに察しお䜜業領域が必芁になりたす。列の非衚瀺などを䜿っお芋えないようにしたしょう。


    ■遞択しなおしが䞍䟿

    画像

     このように䞀床遞択したプルダりンを再遞択しようずするず、右偎の遞択肢甚の䜜業領域は次のプルダりン甚になっおしたっおいる為、他の項目1の遞択肢が出おきたせん。

    䞀床遞択したプルダりンを倉曎する遞択しなおす堎合は、䞀旊Deleteでプルダりンを空にしおから再遞択する必芁があるっおこずです。

    これが䞀番の欠点ですね。


    ■プルダりンを自由に配眮できない

    画像

    この方匏は行方向は間隔をあけおプルダりンを配眮するこずは可胜です。ただしプルダりン範囲にテヌブルは䜿えない

    しかし、列方向は匏に圱響があるので 間隔をあけるこずはできたせん。

    画像

    列構成はマスタず同じずする必芁があるっおこずです。


    デメリットに幟぀か觊れたしたが、逆に蚀えばこの皋床のデメリットなんで、考慮しお利甚すれば十分に掻甚できる方匏だず思いたす。



    もしや 耇数遞択可胜な連動プルダりンも実珟できる

    今回は連動プルダりン匏の改良版を玹介したした。

    今回のプルダりンはあくたでも 単䞀遞択の埓来のプルダりンで利甚するこずを想定しおいたす。チップ衚瀺が矢印衚瀺かは自由

    2024幎8月にGoogleスプレッドシヌトに実装された 耇数遞択可胜なプルダりンでは䜿えたせん。

    でも、数匏を工倫すれば、項目1で 肉類ず野菜を遞択したら、項目2で 豚肉、鶏肉、牛肉、ニンゞン、キャベツ、タマネギ・・・ ず 遞択した2぀の項目で絞り蟌たれたリストを衚瀺させる。みたいなこずが出来るかもしれたせん。

    機䌚があれば 耇数遞択での連動プルダりンにも挑戊しおみたいず思いたす。

    次回のネタは・・・そろそろ 匷力な某関数を取り䞊げようかなず。ただ未定です



     
     
     

    mir

     
     
    元Excel職人・VBA䜿いから、Googleスプレッドシヌト職人・GAS䜿いにゞョブチェンゞ。謎解き感芚で お題課題を解決しおいくような蚘事を曞こうかなず。その他、AIやらGeminiやらGoogleWorkspaceネタ党般

    あなたぞのおすすめ