プログラミング

コピペで簡単!スプレッドシートの連動プルダウンを作成する方法を解説します

スプレッドシートで連動プルダウンをつくる方法を解説します。

こんな感じのやつです。

単一行ではなく、複数行です。

下画像は2~10行目のみに設定してますが、100行目まででも列全体でも大丈夫です。

 

この記事の方法には、メリットとデメリットがあります。

■メリット
→選択肢を自由にメンテナンスできる
→その際の設定変更は不要
→複数セルの同時入力にも対応

■デメリット
→遅い

 

プログラム(Google Apps Script)を使いますが、コピペするだけなので、まったく分からない方でもOKです。

下記の5ステップで終わります。

ある程度スプレッドシートに慣れている方なら、10分後には連動プルダウンが動いているはずです。

  1. 入力シートと設定シートを作成
  2. コードをコピペ
  3. 定数を変更
  4. データの入力規則を設定
  5. 動作確認

コピペで簡単!スプレッドシートの連動プルダウンを作成する方法を解説します

ちなみに、自分はプログラマーです。

最低限の品質はあると思っています。

 

①入力シートと設定シートを作成

まずは、入力シートと設定シートをつくります。

下記の意味合いです。

  • 入力シート
    →連動プルダウンをつくるシート
  • 設定シート
    →プルダウンの選択肢を設定するシート

 

入力シートは、下画像と同じような構成でつくってください。

プルダウンの行列は、どこでも大丈夫です。

ヘッダーの項目名は、お好きなように…。

 

シートをつくったら、上画像の番号をメモしてください。

  • ①シートの名前
    →なんでもOK
  • ③プルダウン(ひとつ目)の列番号
    →Aなら1、Bなら2…
  • ④プルダウン(ふたつ目)の列番号
    →Bなら2、Cなら3…
  • ⑤プルダウンをつくる最初の行
    →2行目なら2、3行目なら3…

※上画像でいうと、①=入力シート、③=1、④=2、⑤=2 となります。

 

設定シートは、下画像と同じような構成でつくってください。

ひとつ目のプルダウンは1行目に設定し、選択肢を横向きに加えていきます(⑦のように)

ふたつ目のプルダウンは、ひとつ目にぶらさがる選択肢を縦向きに加えていきます(⑥のように)

 

選択肢の設定をはじめる行列は、どこでも大丈夫です。

また、選択肢の数に上限はありません。

シートをつくったら、上画像の番号をメモしてください。

  • ②シートの名前
    →なんでもOK
  • ⑥設定の開始列番号
    →Aなら1、Bなら2…
  • ⑦設定の開始行番号
    →1行目なら1、2行目なら2…

※上画像でいうと、②=設定シート、⑥=1、⑦=1 となります。

 

②コードをコピペ

つぎに、プログラム(Google Apps Script)をコピーします。

なにも考えずに、下記のコードをまるごとコピーしてください。

const SHEET_TARGET = "入力シート";   //①連動プルダウンをつくるシート名
const SHEET_SETTINGS = "設定シート"; //②プルダウンの選択肢を設定するシート名

const COLUMN_TARGET_GROUP1 = 1;    //③入力シート 大分類プルダウンの列番号
const COLUMN_TARGET_GROUP2 = 2;    //④入力シート 小分類プルダウンの列番号
const ROW_TARGET_START = 2;        //⑤入力シート プルダウンをつくる最初の行番号

const COLUMN_SETTINGS_START = 1;   //⑥設定シート 最初の列番号
const ROW_SETTINGS_START = 1;      //⑦設定シート 最初の行番号

function onEdit(e) {
  
  //ほかの処理に干渉させないため、関数にする
  createDropdownList(e);
  
  //ほかの処理があれば続けて書く
  
}

function createDropdownList(e) {

  var sheetTarget = e.source.getActiveSheet();

  //対象外のシートは無視する
  if (sheetTarget.getName() !== SHEET_TARGET) {
    return;
  }
  
  //設定シートの大分類を読み込む
  var sheetSettings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_SETTINGS);
  var rangeGroup1 = sheetSettings.getRange(ROW_SETTINGS_START, COLUMN_SETTINGS_START, 1, sheetSettings.getLastColumn() - COLUMN_SETTINGS_START + 1).getValues()[0];

  //入力シートの編集された範囲を取得(最初の行列、最後の行列)
  var columnStart = e.range.columnStart;
  var columnEnd = e.range.columnEnd;
  var rowStart = e.range.rowStart;
  var rowEnd = e.range.rowEnd;

  //コピーで複数セルに一括入力された場合に対応するため、編集された全セルをループする
  for (var c = columnStart; c <= columnEnd; c++) {
    
    //大分類プルダウンの列が編集された場合のみ処理する(ほかの場合は小分類プルダウンを作り直す必要がない)
    if (c !== COLUMN_TARGET_GROUP1) {
      continue;
    }
    
    for (var r = rowStart; r <= rowEnd; r++) {
      
      //プルダウンをつくらない行(ヘッダー行など)は無視する
      if (r < ROW_TARGET_START) {
        continue;
      }
      
      //大分類と小分類のセルを取得
      var cellGroup1 = sheetTarget.getRange(r, COLUMN_TARGET_GROUP1);
      var cellGroup2 = sheetTarget.getRange(r, COLUMN_TARGET_GROUP2);
      
      //小分類のセルを初期化(値とプルダウン設定)
      cellGroup2.clearContent();
      cellGroup2.clearDataValidations();

      //設定シートにおける大分類の場所を探す
      var index = rangeGroup1.indexOf(cellGroup1.getValue());
      
      //入力された大分類が設定シートになければ、プルダウンをつくらない(不正な値)
      if(index !== -1) {
        
        //小分類の設定を読み込む
        var rangeValidation = sheetSettings.getRange(ROW_SETTINGS_START + 1, COLUMN_SETTINGS_START + index, sheetSettings.getLastRow());
        var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rangeValidation).build();
        
        //プルダウンをつくる
        cellGroup2.setDataValidation(validation);
        
      }
    }
  }
  
}

 

 

スプレッドシートに貼りつける方法は、下画像を参考にしてください。

保存を忘れずに。

 

③定数を変更

「①入力シートと設定シートを作成」でメモしていただいた①~⑦を、プログラムの最初の部分に書いてください。

「const xxxx = yyyy;」の「yyyy」の部分のみ変えます。

ダブルクォーテーション(”)やセミコロン(;)は、そのまま残してください。

保存を忘れずに。

 

④データの入力規則を設定

入力シートにもどって、ひとつ目のプルダウンを設定する範囲を選択し、データの入力規則を設定します。

下画像のとおりに進めてください。

以上で、連動プルダウンの完成です。

 

⑤動作確認

「プルダウンが設定されないな…」という場合は、「③定数の変更」で変更した部分を見直してください。

シート名が間違っているか、行列番号がずれていることが原因だと思います。

 

スポンサーリンク

連動プルダウンのサンプルスプレッドシートはこちら

連動プルダウンのサンプルをつくったので、デモ版として動かしたい方はお使いください。

下記のリンクからコピーできます。

→サンプルはこちら

 

①「ファイル」 → 「コピーを作成」の順にクリック

 

②「OK」をクリック

スポンサーリンク

こちらの記事もおすすめ!