スプレッド シート プルダウン 連動。 Excel 複数のプルダウンを連動させる方法

スプレッドシートのプルダウンリスト連動をGASで実現する

スプレッド シート プルダウン 連動

複数のプルダウンを連動させる手順 連動させるシートの例 具体的にはこのような例で利用します。 地方と都道府県のセルがあり、 地方のセルを選択すると都道府県の選択肢が変化します。 プルダウンで地方を選択すると 都道府県のプルダウンに選択した地方に対応する選択肢が表示される 地方が変わると都道府県も変化 簡単な方法 セル範囲名とINDIRECT関数 地方のプルダウン設定手順 まずこのような地方と都道府県の一覧表を用意します。 地方と都道府県の一覧表 次に地方の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択します。 メニューの「データ」、「データの入力規則」の位置 次に「入力値の種類」より「リスト」を選択し、「元の値」に地方データのセル範囲を設定します。 「データの入力規則ウインドウ」の「入力値の種類」と「元の値」の位置 これによりプルダウンで地方を選択できる状態になります。 プルダウンで地方を選択 都道府県のプルダウン設定手順 都道府県を地方ごとにを設定します。 それには都道府県を地方ごとに範囲選択します。 なお空白セルは選択範囲から除く必要があります。 その状態で数式バーの左にあるセル名欄に地方の名前を入力します。 この名前は一覧表の地方名と一致する必要があります。 具体的なセルの配置 これを地方の数だけ繰り返し、このような状態にします。 一覧表のセル範囲とセル範囲名の対応 なおセル範囲名の設定を間違えた場合はする必要があります。 最後に都道府県の入力セルにプルダウンを設定します。 都道府県の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択します。 「入力規則」の画面を起動し、入力値の種類を「リスト」に変更します。 そして元の値にを利用し下記の数式を設定します。 (プルダウンメニューが連動) プルダウンで地方を選択 数式の説明(読み飛ばし可) は文字列のやアドレスをセル参照に変換する関数です。 「元の値」に指定したを使用することは可能ですが セルを指定するとセル範囲でなくただの文字列として扱われます。 それでは動作しないのででセル範囲に変換する必要があります。 高度な方法 OFFSET関数とMATCH関数 セル範囲を設定する方法との相違点(メリット・デメリット) 先述のセル範囲の方法では地方ごとにセル範囲名を設定する手間があります。 (連動元の選択肢の数だけ設定の手間) また選択肢が増えたり変わる場合もセル範囲からやり直す必要があります。 とを使用した方法ではそれらの問題が解決します。 しかしそのトレードオフで数式が複雑になり理解が難しくなります。 方法 メリット デメリット セル範囲名 ・設定が簡単で分かりやすい。 ・選択肢が多いとセル範囲名設定の手間が膨大になる。 ・選択肢の変更があるとセル範囲名の設定変更が必要。 と ・設定の手間が少なく、選択肢の変更でも作業が発生しない ・数式が複雑 よってこれから紹介する方法は連動元プルダウンの選択肢が多い場合か 頻繁に変更が発生する場合にのみ検討する方法になります。 設定手順(連動先の選択肢が横方向の場合) 地方(連動元)の設定はセル範囲のものと変わりません。 都道府県(連動先)の入力規則の設定で下記のような数式を設定します。 ただしこの方法は都道府県(連動先)が横方向に並んでいる場合のみです。 縦方向の場合、数式を変える必要があります。 設定手順(連動先の選択肢が縦方向の場合) このような数式になります。 北海道は1件のみのため多くの空白が発生 これは最初の方法のは選択肢に空白を含まない範囲を選ぶことで 空白を発生させないことが可能です。 しかしとの例では連動先の最大選択肢数として 固定値(例は9)を入れた影響で全ての選択で空白を含めた9個の選択肢が 表示される状態になってしまいます。 これに対応するには入力規則に設定する数式をを入れて改造します。 ただし数式が更に長く複雑になるため、このままで問題ない場合は そのままにしておくのもよいでしょう。 まず選択肢が横方向の場合の数式です。 太字が数式の改修箇所です。 これは一度目のとで連動元の選択肢に該当する個数を で算出し、(ここが太字の改修箇所) それを二度目のとの取得件数に使用しています。 これにより余計な空白を除外することが可能です。 連動先の選択肢から不要な空白を除外した状態 ただし選択肢が空白セルなしに詰められている必要があります。 選択肢の途中に空白が混ざっていると、空白削除不可 このような状態になってしまうためこれが発生しないように 横方向なら左に、縦方向なら上に空白無しに詰めるようにしましょう。 関連記事 応用例 BMI判定をVLOOKUP関数で行う方法.

次の

スプレッドシートのプルダウンリスト連動をGASで実現する

スプレッド シート プルダウン 連動

背景 Googleスプレッドシートで「プルダウンリスト連動」をする方法を調べると、いくつもHITします。 私がみた記事はスプレッドシートの関数 VLOOKUPとか で実現している記事だったのですが、GASの知識が無くても扱える点では有利なのですが、 入力欄が増えると、それに合わせて式のコピーや入力規則のコピーが必要になる みたいなのです。 入力行を増やすたびにコピーミスや誤って式を消してしまったりするのが嫌だなーって思ったのです。 これは何の記事? 「プルダウン連動」をGASで実現したらどうなるかな、をやってみました。 もっとうまいやり方があったらコメント欄によろしくおねがいします! 完成図 「カテゴリ1」を選ぶと、それに連動する「カテゴリ2」の選択肢だけが表示されるようにしたい。 スプレッドシートへの仕込み 「カテゴリ1」については通常の「データの入力規則」で、「設定シート」の「A2:A」を指定して設定しておく。 「使う側のシート」のB2に設定して、B3以降はこれをコピーしちゃう。 range. range. source. source. getDataRange. newDataValidation. requireValueInList catgory2List , true ; rule. setAllowInvalid false. build ; range. range. source. getSheetName! range. getColumn! value return false ; ってやりたくなるでしょ!? でもこれだと、「ペースト」されたときに undefined になるんです。 「使う側のシート」のカテゴリ1の列に「木管楽器」という文字列をコピペしても、 e. value は undefined なのです... と言っておきながら、下記のデメリットのように「コピペ」に対応できていないのですけどね。 メリット• おそらく「入力規則」を使える人は多いので、カテゴリ1の入力規則だけ設定できればあとは行データが下に増えても「行のコピー」で増やせる。 「設定シート」も難しくないので誰でも増やせる。 行データを下に増やしても、途中に行を挿入してもOK。 GASが分かる人にとっては、スプレッドシート関数を駆使してやるよりわかりやすい。 ここは私の主観ですけど デメリット• GASを書けないとメンテナンスできない。 動作が遅い。 onEidtで動作させているため、動作完了までに時間がかかる• 「使う側のシート」の「カテゴリ1」の列に「コピペ」したときに、カテゴリ2が出てこない。 同じように、「B列の複数行をコピペ」してもカテゴリ2が出てこない。 ちと今は力尽きたのであとで調べる。 どなたか知ってたら教えて!• うん、きっとできる。 カテゴリ2に連動するカテゴリ3も、、、できそうですね!• まぁスプレッドシートの機能で「警告」が出るから「なにかおかしいぞ」は気付けるはず。

次の

【小技集】意外と知られていないGoogleスプレッドシートを便利にする技まとめ

スプレッド シート プルダウン 連動

IMPORTRANGE "スプレッドシートキー", "範囲の文字列" この 『範囲の文字列』のところに、 "持ってきたいシートの名前! 持ってきたいセル"を入力します。 例えば、 データベースという名前のシートの A1セルを参照したい場合は、 "データベース! A1"と入力すれば大丈夫です。 表(範囲セル)を参照する 表を参照したい場合は、 『範囲の文字列』のところに、 "持ってきたいシートの名前! 持ってきたい範囲セル"を入力します。 例えば、 データベースという名前のシートの A1からG9を参照したい場合は、 "データベース! A1:G9"と入力すれば大丈夫です。 集計したデータや表引きを参照する方法 ただ、値を引っ張ってをくるだけじゃ面白くないですよね! IMPORTRANGE関数は、他の関数と組み合わせることでその真価を発揮します。 例1:合計金額を参照する(SUM関数) 合計金額だけを参照したいときは、SUM関数とIMPORTRANGE関数を組み合わせます。 例えば、 別のシートのA列の合計金額を参照したい場合は、次のようにSUM関数と組み合わせます。 例えば、次のような表があったとします。 ここで『1002』をキーとして、商品名である『ぶどう』を参照したい場合は、次のように記述します。 ほら、こんな感じで参照することができました。 IMPORTRANGE関数とVLOOKUP関数を組み合わせれば、このように他のスプレッドシートであっても表引きを実現できるんです。 スプレッドシートすごすぎません?Excelには出来ない芸当ですよね。 例3:スプレッドシートを簡易的なデータベースとして使う 集計したデータや表引きしたデータだけをIMPORTRANGEで持ってくるということは、スプレッドシートをデータベースのように扱うことができます。 スプレッドシートは200万個のセルにしか値を入力できませんが、IMPORTRANGEを使えば実質無限のセルを使うことができるんですよ。 詳しくは割愛しますが、気になる方はこの記事をどうぞ!.

次の