Google Workspace割引クーポン無料配布中! 詳しくはこちら

別シート連動の自動入力プルダウンリストの作り方|スプレッドシート

  • URLをコピーしました!

スプレッドシートで連動するプルダウンメニューの作成方法を詳しくご紹介します。

連動するプルダウンとは、一つのセルの選択に応じて、別のセルが変化することです。

Web上で紹介されている記事のほとんどが、データを登録するたびに入力先連動先の2つをコピペする必要がありました。(2023年10月時点)

この記事で紹介するのは、メンテナンスが楽になる連動するプルダウンリストの作成方法です。

別シートから参照することを想定して作りました。

参考になれば嬉しいです。早速参りましょう!!

タップできる目次
スポンサー

作成するシートの完成形

今回は例として、拠点ごとの設備のエラーを入力していくシートを作成します。

通常のプルダウンの場合、選択肢を増やしすぎてどれを選べばいいのか迷ってしまいます。

プルダウンを連動させることで、関連性のある選択肢だけを簡単に表示することが出来ます。

連動プルダウンは特にオプションが多く複雑なフォームやデータ入力作業において非常に便利なツールとなります。

適切に設定された連動プルダウンは、ユーザーが簡単かつ迅速に正しい選択を行うのを助け、全体の作業プロセスをスムーズにします。

今回はこのプルダウンの作成方法について解説します。

連動するプルダウンリストの作成方法

連動するスプレッドシートの作成方法について解説します。

スプレッドシートの準備

まずはスプレッドシートの準備です。

3つのシートを準備します。

準備するシート
  1. 入力内容
  2. マスターリスト
  3. 連動シート

入力内容

STEP
『入力内容』シートの作成

データを入力していくシートです。

STEP
『マスターリスト』シートの作成
STEP
『連動シート』の作成

参照元となるマスターリストを作成

マスターリストに連動するプルダウンで表示したいデータを登録します。

大項目のプルダウンリスト(拠点名)を作成する

一度『入力内容』シートに戻って拠点のプルダウンを作成します。

STEP
プルダウンを作るエリアを選択
STEP
プルダウンを作成

『挿入』をクリック

『プルダウン』をクリックします。

※右クリックでも表示されます。

STEP
プルダウン(範囲内)を選択

プルダウン(範囲内)を選択します。

STEP
データ範囲の選択

条件下の『田んぼマーク』をクリックすると、データ選択の範囲が出てきます。

マスターリストのA列『拠点』を選択してください。

選択したら『OK』ボタンをクリックします。

データ範囲の選択時に最後の数字を抜くのをおすすめします。今後データが増えた際に、自動でプルダウンに追加されます。

STEP
『完了』をクリック

プルダウンリストに拠点データが入っていることを確認し、『完了』ボタンを押します。

以上で大項目(拠点)のプルダウンリストの作成は完了です。

連動させる小項目(設備名)のシートを作成

次にこの記事の本題である小項目(設備名)の連動プルダウンを作成します。

「連動シート」に連動させるプルダウンの選択元となるリストを作成します。

STEP
連動シートに名称を入力

A1に拠点、B2に拠点を作成します。

STEP
拠点情報を「入力内容」シートから参照する

 A2に以下の数式を入力します。

={'入力内容'!B2:B}

これでどうなるか?というと下の動画を御覧ください↓

『入力内容』シートで入力したデータに連動して、『連動シート』の値が変わっていることがわかるでしょうか?

数式内に { } の括弧 を使うことで、データをまとめて参照出来ます。

STEP
B2セルにArrayformula関数とVlookup関数を入力

ここが非常に重要になります!! 

A2に以下の数式を入力します。

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,'マスターリスト'!A2:E,{2,3,4,5},0)))

すると小項目の連動するプルダウンリストの元となるデータリストが完成です!!

やったー!!

・・・ではないですよね(笑)

この式の詳しい解説は次の項目で説明します。

一旦小項目の連動するプルダウンリストの作成までいってしまいましょう!!

▼すぐに知りたい方は解説記事へジャンプ

以上で連動するプルダウンの大元となる連動シートは完成です。

マスターリストの設備名が増えた場合、先程紹介した式の{2,3,4,5}の部分に『,6』といった形式で数字を入れてください。いちいち増やすのが面倒であれば、この数字を多めに設定、プルダウンの選択範囲も余裕を持って選択するのがおすすめです。

小項目のプルダウンリスト(設備名)を作成する

小項目の連動するプルダウンリストの指定方法です。

いよいよ最後です!!

STEP
小項目を作るエリア(設備名)を選択
STEP
「挿入」→「プルダウン」をクリック

『挿入』をクリック

『プルダウン』をクリックします。

※セル選択後、右クリックでも表示されます。

STEP
プルダウン(範囲内)をクリック

『条件』の項目にて、『プルダウン(範囲内)』をクリックします。

STEP
データ範囲の選択
  • 条件下の『田んぼマーク』をクリックすると、データ選択の範囲が出てきます。
  • 連動シートの「B2からE2」を選択してください。
  • 選択したら『OK』ボタンをクリックします。
STEP
選択範囲に『=(イコール)』を追加

ここが最重要ポイントとなります!!

先程選択した範囲に『=(イコール)』を追加してください。

イコールを入れることでプルダウンが連動します。

STEP
完了をクリック

以上で連動するプルダウンリストが完成しました!!

シートへの入力方法

入力するデータ(行)が増えた場合の対処方法を説明します。

入力箇所だけコピー&ペーストする

入力する内容の行を増やす場合は、「入力内容シート」のみをコピー&ペーストしてください。

もしくは最終行より1つ上を行挿入してあげると、間に挿入した行は自動でプルダウンが作成されます。

自動で連動するプルダウンも拡張されます。

入力内容シートに行を挿入

プルダウンの表示スタイルの変更

プルダウンの表示スタイルの変更・データ無効の場合の設定は、『詳細オプション』で変更可能です。

連動シートの関数(Arrayformula+Vlookup)について解説

本記事の肝である以下の式ArrayformulaとVlookupについて解説します。

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,’マスターリスト’!A2:E,{2,3,4,5},0)))

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,'マスターリスト'!A2:E,{2,3,4,5},0)))

VLOOKUP関数の基本

VLOOKUP関数は、「垂直検索」とも呼ばれ、指定された値を基にして別のセル範囲から情報を取得するための関数です。

ARRAYFORMULA関数を使う前の状態だと、以下のような式になります。

=VLOOKUP(A2, ‘マスターリスト’!A2:E, {2,3,4,5}, FALSE)

=VLOOKUP(A2, 'マスターリスト'!A2:E, {2,3,4,5}, FALSE)
  • A2: 検索する値が入っているセルです。
  • 'マスターリスト'!A2:E: 検索を行う範囲を指定します。この例では、’マスターリスト’というシートのA列からE列までを指定しています。
  • {2,3,4,5}: 検索結果として表示したい列番号のセットです。検索値に対応する2, 3, 4, 5列目のデータを取得します。
  • FALSE: 完全一致を意味します。検索値が正確に一致するデータだけを取得します。

VLOOKUP関数を使用することで、特定の値に基づいて必要なデータを迅速に見つけ出し、それを表示することができます。

2. IFERROR関数でエラー処理

VLOOKUP関数だけでは、検索値が見つからない場合にエラーメッセージが表示されます。

これを防ぐために、IFERROR関数を使用します。

=IFERROR(VLOOKUP(A2, ‘マスターリスト’!A2:E, {2,3,4,5}, FALSE), “”)

=IFERROR(VLOOKUP(A2, 'マスターリスト'!A2:E, {2,3,4,5}, FALSE), "")
  • IFERROR:検索値が見つからないときにエラーメッセージではなく、違う値を表示させる関数
  • VLOOKUP(...): 上記で説明したVLOOKUP関数です。
  • "": エラーが発生した場合に表示する値。ここでは空文字列(何も表示しない)を指定しています。

IFERROR関数を使うことで、検索値が見つからないときにエラーメッセージを表示する代わりに、セルを空にすることができます。

3. ARRAYFORMULAを使用して範囲全体に適用

最後に、ARRAYFORMULA関数を使用して上記の式をセル範囲全体に適用します。

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, ‘マスターリスト’!A2:E, {2,3,4,5}, FALSE), “”))

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, 'マスターリスト'!A2:E, {2,3,4,5}, FALSE), ""))
  • ARRAYFORMULA:複数行にわたって結果を表示する関数
  • IFERROR(VLOOKUP(...), ""): 上記で説明したIFERROR関数とVLOOKUP関数の組み合わせです。

プルダウンメニューから選択された値がA列の複数のセルにわたって入力されている場合でも、それぞれのセルに対してVLOOKUPとIFERRORが適用され、結果が対応するセルに表示されます。

おまけ:拠点を参照する配列{}は必須ではない

連動シートに入力内容を配列{}で抜き出している箇所があります。

ここは必須ではありません。以下のような式でもOKです。

=ARRAYFORMULA(IFERROR(VLOOKUP('入力内容'!B2:B,'マスターリスト'!A2:E,{2,3,4,5},0)))

何故入れたかというと、見たときに分かりやすいようにするためだけです。

シートの参照先がごちゃごちゃになって分からない!という方は配列 {} を使って必要なデータを抜き出してあげましょう。

データを視覚的に整理することが出来ると思います。

まとめ

別シート連動のプルダウンリストを自動作成する方法について解説しました。

ARRAYFORMULA と V LOOKUP を組み合わせることで、入力部分だけコピーすれば自動で連動されるので保守もしやすく便利だと思います。

みなさんの参考になれば幸いです。

以上、ごすけでした。

参考にさせていただいたサイト(外部リンク)

ごすけ|生産技術者

1989年生まれの30代。妻と娘の3人家族。
大手メーカーからスタートアップ企業に転職。工程設計の仕事の傍ら、Google Workspaceを使って社内DXを推進中。
Google Workspaceの使い方・DX活用事例をブログで発信しています。

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
タップできる目次