spreadsheetの使い方

スプレッドシートのプルダウンを連動させよう!違うシートのテーブルを参照する応用編

更新日:

前回スプレッドシートのプルダウン連動を同じシート内で行う方法 をご紹介しました。

 

今回はその応用編

別シートのデータベースを参照して
スプレッドシートのプルダウンを連動する

方法についてご紹介いたします(^^)

スプレッドシートのプルダウンを連動!別シートのデータを参照する方法

前回の記事では、同じスプレッドシート内でプルダウンの連動を行いました。

注目!
スプレッドシートのプルダウンを連動させよう!同じシートの中で設定する基本編

スプレッドシートでプルダウンの連動が出来たら・・ とっても便利ですよね!   そこで今回は スプレッドシートのプルダウンを 連動させる方法【基本編】 をご紹介していきます(^^) スプレッド ...

 

なるべく再現性を上げたかったので、GAS(Google App Script)を使わずに

  • IFERROR関数
  • VLOOKUP関数

と2つの関数を使った連動を行ってみましたが上手く出来ましたでしょうか?

 

今回は

  • OFFSET関数
  • MATCH関数
  • COLUMN関数

この3つの関数を駆使して、別シートのデータベースを参照にしたプルダウンの連動を行っていきます。

 

アヒルン
うわ、なんだか難しそうな関数ばっかじゃん・・
oyakun
大丈夫です。各関数の簡単な解説も入れてやっていきます

 

普段業務で使うには

  1. 入力項目フォーム(プルダウン)を作成 
  2. 1に別シートのテーブルを参照させる

方が多いと思いますので、がっつりご紹介させていただきます(`・ω・´)ゞ

 

別シートからプルダウンの連動をやってみよう!

「別シートを参照してプルダウンの連動を行う」といっても考え方は同じです。

  1. 不変の参照元テーブルを作る
  2. 可変の参照元テーブルを作る
  3. データの入力規則を2と繋げる

この連携を関数で行えばGASは使わずに、別シートのテーブルを参照したプルダウン連動を行うことが可能です。

 

今回も別シートを参照したプルダウンの連動sample をご用意いたしましたので、分からない場合は参照しながら進めていただければなと(^^)

1・プルダウン表示用のタイトルを作成

スプレッドシートを開いて「シート1」にプルダウン表示用のタイトルを作成します。

 

2・別シートに不変データベースを作成

2-1・別シートを作成してシート毎に名前を変えておく

プルダウンの参照元となる不変データベースを作成するために「シート2」を追加します。

 

画面左下にある「+」をクリック。

 

シート2が追加されました。

 

シートの名前を分かりやすいように変更しておきましょう。

こんな感じに変更しました↓

変更前の名前 変更後の名前 定義
シート1 フォーム プルダウン
シート2 参照元 参照元データベース

 

2-2・不変データベースを作成

シート "参照元”に不変データベース(不変サブタイトル)を作成します。

 

このデータベースの場所は任意でOKです。分かりやすいようにセルA1:F11に作成しました。

 

不変データベースの内訳

セル範囲A1〜A11はシート "フォーム” のプルダウンA列の参照元。

セル範囲B1〜F11はシート "参照元” の可変データベース(可変サブタイトル)の参照元。

 

今回の不変データベースのsampleです↓といっても前回と殆ど同じ・・

タイトル 不変サブタイトル(可変用参照テーブル)
あいうえお
かきくけこ
さしすせそ
たちつてと
なにぬねの
はひふへほ
まみむめも
やゆよ    
らりるれろ
わをん    

 

では

  • OFFSET関数 
  • MATCH関数
  • COLUMN関数

を駆使して可変データベースを作成しましょう。

 

3・プルダウン連動元の可変データベースをシート参照元に作成

シート"フォーム”のプルダウン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(セル参照, オフセット行, オフセット列, [高さ], [幅])

引用元:https://support.google.com/OFFSET

 

次にMATCH関数を使って行を取得します。OFFSET関数と組み合わせることで指定した範囲の中から該当する値を検索し、該当した行を返すことが出来ます 

MATCH関数

指定した値と一致する範囲内のアイテムの相対的な位置を返します。

使用例:MATCH("日曜日",A2:A9,0)

使用例2:MATCH(DATE(2012,1,1),A2:F2)

構文:MATCH(検索キー, 範囲, 検索の種類)

引用元:https://support.google.com/MATCH

 

最後にCOLUMN関数で列を指定します。選択したセルがA1なら1,B1なら2,C1なら3を返してくれます。 

COLUMN関数

指定したセルの列番号を返します(A は 1 となります)。

使用例
COLUMN(A9)

構文
COLUMN([セル参照])

セル参照 - [省略可 - デフォルトは数式を含むセル] - 列番号を返すセルを指定します。A 列に対応する値は 1 です。

引用元:https://support.google.com/COLUMN

 

3-3・数式を設置して可変データベースを作成

この3つの関数を組み合わせた数式をセルB14に設置します 

 

上の数式をセルB14に設置します。

 

セルB14に入力したらF14まで横にオートフィルコピー、F23まで下にオートフィルコピーすれば可変データベースは完成です。

 

OFFSET+MATCH+COLUMNの数式は作る手間はありますが、COLUMN関数のセル位置が自動変更なので設置がラクですね。

 

今回の数式は電脳電網総研さんの記事 を参考にさせていただきました(_ _)アリガトウゴザイマス

 

ここまででシート"参照元”の各データベースの準備は完了です。シート ”フォーム ”に戻ってプルダウンを設置していきましょう。

 

前回と同様にプルダウンの連動を行うには「データの入力規則」を設置する際に、手順を踏む必要があります。

次のページへ >

広告と関連記事


  • この記事を書いた人
  • 最新記事
oyakun

oyakun

30代半ば♂妻・子3人の5人で日々激戦中。Google app script&VBA初心者が自分の備忘録を兼ねて「スプレッドシート、Excel、WordPress、plugin」の設定等、主にブログやアフィリエイト周辺のtipsを背伸びしながらご紹介しています。

Copyright© OYAKUDACHI.XYZ , 2018 All Rights Reserved.