前回スプレッドシートのプルダウン連動を同じシート内で行う方法をご紹介しました。
今回はその応用編として、別シートのデータベースを参照して
スプレッドシートのプルダウンを連動する方法についてご紹介いたします(^^)
スプレッドシートのプルダウンを連動！別シートのデータを参照する方法
前回の記事では、同じスプレッドシート内でプルダウンの連動を行いました。
参考｜スプレッドシートのプルダウンを連動させよう！同じシートの中で設定する基本編
なるべく再現性を上げたかったので、GAS（Google App Script）を使わずに
- IFERROR関数
- VLOOKUP関数
とつの関数を使った連動を行ってみましたが上手く出来ましたでしょうか？
今回は
- OFFSET関数
- MATCH関数
- COLUMN関数
この3つの関数を駆使して、別シートのデータベースを参照にしたプルダウンの連動を行っていきます。
普段業務で利用する時は、
- 入力項目フォーム（プルダウン）を作成
- １に別シートのテーブルを参照させる
といった使い方をされている場合が多いと思いますので、しっかりご紹介させていただきます！
別シートからプルダウンの連動をやってみよう！
「別シートを参照してプルダウンの連動を行う」といっても考え方は同じです。
- 不変の参照元テーブルを作る
- 可変の参照元テーブルを作る
- データの入力規則を２と繋げる
この連携を関数で行えばGASは使わずに、別シートのテーブルを参照したプルダウン連動を行うことが可能です。
今回も別シートを参照したプルダウンの連動sampleをご用意いたしましたので、分からない場合は参照しながら進めていただければなと(^^)
STEP1. プルダウン表示用のタイトルを作成
スプレッドシートを開いて「シート1」にプルダウン表示用のタイトルを作成します。
STEP2. 別シートに不変データベースを作成する
2-1. 別シートを作成してシート毎に名前を変えておく
プルダウンの参照元となる不変データベースを作成するために「シート2」を追加します。
画面左下にある「＋」をクリック。
シート２が追加されました。
シートの名前を分かりやすいように変更しておきましょう。
こんな感じに変更しました↓
| 変更前の名前 | 変更後の名前 | 定義 |
|---|---|---|
| シート1 | フォーム | プルダウン |
| シート2 | 参照元 | 参照元データベース |
2-2. 不変データベースを作成
シート " 参照元 " に不変データベース（不変サブタイトル）を作成します。
このデータベースの場所は任意でOKです。
分かりやすいようにセルA1：F11に作成しました。
不変データベースの内訳
セル範囲A1〜A11はシート " フォーム " のプルダウンA列の参照元。
セル範囲B1〜F11はシート "参照元 " の可変データベース（可変サブタイトル）の参照元。
今回の不変データベースのsampleです↓といっても前回と殆ど同じ...
| タイトル | 不変サブタイトル(可変用参照テーブル) | ||||
|---|---|---|---|---|---|
| あいうえお | あ | い | う | え | お |
| かきくけこ | か | き | く | け | こ |
| さしすせそ | さ | し | す | せ | そ |
| たちつてと | た | ち | つ | て | と |
| なにぬねの | な | に | ぬ | ね | の |
| はひふへほ | は | ひ | ふ | へ | ほ |
| まみむめも | ま | み | む | め | も |
| やゆよ | や | ゆ | よ | ||
| らりるれろ | ら | り | る | れ | ろ |
| わをん | わ | を | ん | ||
では
- OFFSET関数
- MATCH関数
- COLUMN関数
を駆使して可変データベースを作成していきます。
STEP3. プルダウン連動元の可変データベースをシート参照元に作成
シート " フォーム " の、プルダウンA列の値でサブタイトルB列が連動する仕組みを構築します。
不変データベースの下に、可変データベースのタイトルを作成します。（場所は任意でOKです）
不変データベースのタイトルsampleです↓今回はA13〜F13に作成しました。
| タイトル | 可変サブタイトル（offsetとmatch関数で取得） |
|---|
3-1. フォームのプルダウンA列と連携させる数式を設置
シート " フォーム " のA列と可変データベースを数式で連携させます。セルA14に下記数式を設置します。
セルA14（シート "参照元")に設置する数式
='フォーム'!A2
セルA14に入力したらA23までオートフィルコピー
3-2. フォームのプルダウンB列と連携させる関数を設置
シート " フォーム ”のプルダウンB列と可変データベースを関数で連携させます。OFFSETとMATCH関数、COLUMN関数を使った数式です。
まずOFFSET関数でプルダウン値の参照範囲を指定します。
OFFSET関数
開始セル参照から指定した行数と列数だけシフトした位置にあるセル範囲の参照を返します。
使用例：OFFSET(A2,3,4,2,2)
使用例2：OFFSET(A2,1,1)
構文：OFFSET(セル参照, オフセット行, オフセット列, [高さ], [幅])
次にMATCH関数を使って行を取得します。
OFFSET関数と組み合わせることで、指定した範囲の中から該当する値を検索し、該当した行を返すことが可能です。
MATCH関数
指定した値と一致する範囲内のアイテムの相対的な位置を返します。
使用例：MATCH("日曜日",A2:A9,0)
使用例2：MATCH(DATE(2012,1,1),A2:F2)
構文：MATCH(検索キー, 範囲, 検索の種類)
最後にCOLUMN関数で列を指定します。
選択したセルがA1なら1、B1なら2、C1なら3を返してくれます。
COLUMN関数
指定したセルの列番号を返します（A は 1 となります）。
使用例
COLUMN(A9)構文
COLUMN([セル参照])セル参照 - [省略可 - デフォルトは数式を含むセル] - 列番号を返すセルを指定します。A 列に対応する値は 1 です。
3-3. 数式を設置して可変データベースを作成
この3つの関数を組み合わせた数式をセルB14に設置します。
=iferror(OFFSET('参照元'!$A$1,match($A14,'参照元'!$A$2:$A$11,0),column(A14)),"")
上の数式をセルB14に設置します。
セルB14に入力したら
- F14まで横にオートフィルコピー
- F23まで下にオートフィルコピー
すれば可変データベースは完成です。
OFFSET＋MATCH＋COLUMNの数式は作る手間はありますが、COLUMN関数のセル位置が自動変更なので設置がラクですね。
今回の数式は電脳電網総研さんの記事を参考にさせていただきました(_ _)ｱﾘｶﾞﾄｳｺﾞｻﾞｲﾏｽ
ここまででシート"参照元”の各データベースの準備は完了しました。
" フォーム " シートに戻ってプルダウンを設置していきましょう。
前回と同様に、プルダウンの連動を行うには「データの入力規則」を設置する際に、手順を踏む必要があります。