> Google > スプレッドシートの複数条件VLOOKUP関数のGAS化

スプレッドシートの複数条件VLOOKUP関数のGAS化

2021.12.22 2021.12.22

スプレッドシートのVLOOKUP関数で複数条件を用いたいケースをGoogleAppsScriptで解決する方法を紹介します。

概要

VLOOKUP関数は特定の条件に一致する行の指定列を取得できる、スプレッドシート上の関数のひとつです。
しかしVLOOKUP関数では１つの条件に合致する行しか検索できず、複数の条件に合致する行を絞り込みたい場合には手間と工夫が必要になります。

今回はこれをGASを用いて解決する方法を紹介したいと思います。

スプレッドシートには適当なデータを入れておき、シート名はそのまま「シート1」としています。

VLOOKUP関数とは

VLOOKUP関数とは、指定した範囲内の先頭列で内容に一致する値を持つ最初の行で、その値以降の特定セルの内容を参照できる関数です。

動作の図解：

主に簡易の検索としてよく用いられているスプレッドシート関数です。
しかし値を複数条件の検索ができない点や、取得できるのが検索列以降となる点、一致するデータが複数あった場合も１つ目しか取得できない点などそのまま使うには少々不便な部分があります。

特に複数条件の検索ができない点が不便さを感じる一番のポイントではないでしょうか。
例えばこの例で言うと、「佐藤」が複数人いるので「姓」と「名」で絞り込みたくてもVLOOKUP関数だけでは上手くいきません。

対応策として検索用にデータを結合した列を追加するというものが多く見受けられますが、実際には不要なデータ列を追加するのは正直避けたいところです。
そこで今回はGASを使って解決してみたいと思います。

GASの作成

まずはスプレッドシートに検索条件の入力場所と、結果の出力先を作ります。

使用するGASはスプレッドシート等に紐づいたタイプにしたいと思います。
GASの準備については以前の記事の「スプレッドシートに紐づいたGASの作成方法」の項目を参考にしてください。

1. データリストの取得

まずはデータ範囲を丸ごと取得し、map処理を使ってオブジェクト化します。
データの取得の詳細についてはこちらの記事を参考にしてください。
配列のmap処理についてはこちらの記事内で解説しているので参考にしてください。

function multipleSearch() {

  // スプレッドシート＆シートオブジェクトを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  // B2～F6セルを選択
  var range = sheet.getRange('B2:F6');
  // 選択範囲の値を取得
  var values = range.getValues();

  // map処理で2次元配列をオブジェクトの配列に編集
  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  // 実行ログで確認
  console.log(values);
}

実行結果：

これで参照するデータリストをオブジェクトの配列として取得できました。

2. 検索条件の取得

次に、スプレッドシートの検索条件欄に入力をして、GASで検索条件の値を取得します。

function multipleSearch() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  var range = sheet.getRange('B2:F6');
  var values = range.getValues();

  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  // 変更部ここから
  // 検索条件をそれぞれB9セルとC9セルから取得
  const optionA = sheet.getRange('B9').getValue()
  const optionB = sheet.getRange('C9').getValue()

  // 実行ログで確認
  console.log('検索条件１：' + optionA);
  console.log('検索条件２：' + optionB);
}

実行結果：

これで検索条件が取得できました。

3. データリストから検索条件に一致するデータを取り出し

次に、データリストから検索条件に一致するデータを取り出してみましょう。
検索条件に一致するデータが複数あった場合を考慮して、filter処理を用いたいと思います。

filter処理とは：配列に対して行える処理のひとつで、GASではなくJavascriptに含まれる機能です。
配列のデータ１つ１つに同じ処理を行い、結果がTRUEになる要素だけを残した配列を返します。
つまり、指定した条件に一致するデータのみをフィルタリングすることができます。

function multipleSearch() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  var range = sheet.getRange('B2:F6');
  var values = range.getValues();

  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  const optionA = sheet.getRange('B9').getValue();
  const optionB = sheet.getRange('C9').getValue();

  // 変更部ここから
  // ２つの検索条件に一致するデータをfilter処理で取り出し
  // 　条件１：value.familyName == optionA　（姓が一致するデータ）
  // 　条件２：value.firstName == optionB　（名が一致するデータ）
  // ２つの条件を満たすもの（=AND条件=&&）をフィルタリングする。
  var results = values.filter(value => value.familyName == optionA && value.firstName == optionB);

  // 実行ログで確認
  console.log('検索結果：');
  console.log(results);
}

実行結果：

２つの検索条件に一致するデータを取り出すことができました。

一致するデータが複数あった場合

検索条件に一致するデータが複数あった場合の例を見てみましょう。
検索条件を姓のみにすると「山田」のデータは３つあるので、結果が３つになります。

function multipleSearch() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  var range = sheet.getRange('B2:F6');
  var values = range.getValues();

  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  const optionA = sheet.getRange('B9').getValue();
  const optionB = sheet.getRange('C9').getValue();

  var results = values.filter(value => value.familyName == optionA && value.firstName == optionB);

  // 変更部ここから
  // １つの検索条件に一致するデータをfilter処理で取り出し
  var sampleResults = values.filter(value => value.familyName == optionA);

  // 実行ログで確認
  console.log('検索結果：');
  console.log(sampleResults);
}

実行結果：

３つの検索結果が取り出せました。

4. スプレッドシートへ結果の書き込み

最後に、取り出せた検索結果をスプレッドシートに書き込みます。
もし複数のデータが一致していた場合は１つめの結果のみを書き込み「他〇件」という注釈を追加するようにしておきます。

function multipleSearch() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  var range = sheet.getRange('B2:F6');
  var values = range.getValues();

  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  const optionA = sheet.getRange('B9').getValue();
  const optionB = sheet.getRange('C9').getValue();

  var results = values.filter(value => value.familyName == optionA && value.firstName == optionB);

  // ここから変更部
  // B12セルに検索条件に一致したデータの都道府県を出力
  sheet.getRange('B12').setValue(results[0].prefectures);

  // もし検索条件に一致したデータが複数あった場合（results.length=配列の要素数 が1より大きい場合）
  if(results.length > 1){

    // C12セルに「他〇件」と出力
    sheet.getRange('C12').setValue('他' + (results.length-1) + '件');
  }
  else{
    // 検索条件に一致したデータが1つ以下の場合
    // C12セルの値を空白にする
    sheet.getRange('C12').setValue('');
  }
}

実行結果：

検索条件に一致したデータの都道府県がB12セルに表示されました。２つの検索条件に一致する結果は１件なので、C12セルには何も表示されません。

一致するデータが複数あった場合の出力

検索条件に一致するデータが複数あった場合の例も見てみましょう。

function multipleSearch() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  var range = sheet.getRange('B2:F6');
  var values = range.getValues();

  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  const optionA = sheet.getRange('B9').getValue();
  const optionB = sheet.getRange('C9').getValue();

  var results = values.filter(value => value.familyName == optionA && value.firstName == optionB);

  // 変更部ここから
  var sampleResults = values.filter(value => value.familyName == optionA);

  // results ⇒ sampleResults
  sheet.getRange('B12').setValue(sampleResults[0].prefectures);

  // results ⇒ sampleResults
  if(sampleResults.length > 1){

      // results ⇒ sampleResults
      sheet.getRange('C12').setValue('他' + (sampleResults.length-1) + '件');
  }
  else{

    sheet.getRange('C12').setValue('');
  }
}

実行結果：

検索条件を姓のみにすると「山田」のデータは３つあるので、B12セルには１つめの結果「神奈川県」が表示され、C12セルには「他2件」と表示されます。

特定のセルを編集したときに検索実行

このGASを検索条件を変えたときに自動で実行するようにしてみたいと思います。

トリガーの追加

自動実行にはonEdit(e)というトリガーを使います。
このトリガーはGASが紐づいているスプレッドシートのセル内のデータが編集されたときに自動で実行されるトリガーです。

e.range.getSheet().getSheetName()は編集されたシートの名前を返します。
e.range.getRow()は編集されたセルが何行目かを返します。
e.range.getColumn()は編集されたセルが何列目かを返します。

これらを使って、シート1のB9またはC9が編集されたときだけ、先ほど作成したmultipleSearch()関数を呼び出すようにします。
multipleSearch()関数は２つの検索条件のものに戻しておいてください。

// 追加部ここから
// スプレッドシート上のセルが編集された時に自動実行
function onEdit(e){

  // 編集されたシート名を取得
  const sheetName = e.range.getSheet().getSheetName();

  // 編集されたセルの行と列を取得（B9セル⇒Row:9,Col:2）
  const editRow = e.range.getRow();
  const editCol = e.range.getColumn();

  // 編集されたシート名が「シート1」の場合
  if(sheetName == "シート1"){

    // 編集されたセルが9行目かつ、B列またはC列の場合
    if(editRow == 9 && (editCol == 2 || editCol == 3)){

      // 複数条件で検索して出力を実行
      multipleSearch();
    }
  }
}
// 追加部ここまで

function multipleSearch() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  var range = sheet.getRange('B2:F6');
  var values = range.getValues();

  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  const optionA = sheet.getRange('B9').getValue();
  const optionB = sheet.getRange('C9').getValue();

  var results = values.filter(value => value.familyName == optionA && value.firstName == optionB);

  sheet.getRange('B12').setValue(results[0].prefectures);

  if(results.length > 1){

    sheet.getRange('C12').setValue('他' + (results.length-1) + '件');
  }
  else{

    sheet.getRange('C12').setValue('');
  }
}

スプレッドシート上で検索条件の名を「花子」に変えて試してみます。

実行結果：

結果欄が「神奈川県」に変化しました。
しかし、今の状態だと検索条件を存在しない名前に変えてみた場合も結果欄の内容が書き換わらず違和感があります。

一致するものがなかった場合の処理

検索条件に一致するものがなかった場合の処理を追加しましょう。
その場合、結果欄に「一致なし」と表示するようにします。

function onEdit(e){

  const sheetName = e.range.getSheet().getSheetName();

  const editRow = e.range.getRow();
  const editCol = e.range.getColumn();

  if(sheetName == "シート1"){

    if(editRow == 9 && (editCol == 2 || editCol == 3)){

      multipleSearch();
    }
  }
}

function multipleSearch() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');

  var range = sheet.getRange('B2:F6');
  var values = range.getValues();

  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  const optionA = sheet.getRange('B9').getValue();
  const optionB = sheet.getRange('C9').getValue();

  var results = values.filter(value => value.familyName == optionA && value.firstName == optionB);

  // 変更部ここから
  // 検索条件と一致するデータがある場合（一致するデータ数が0より大きい場合）
  if(results.length > 0){

    sheet.getRange('B12').setValue(results[0].prefectures);

    if(results.length > 1){

      sheet.getRange('C12').setValue('他' + (results.length-1) + '件');
    }
    else{

      sheet.getRange('C12').setValue('');
    }
  }
  else{
    // 検索条件と一致するデータがない場合
    // B12に「一致なし」、C12を空白にする
    sheet.getRange('B12').setValue('一致なし');
    sheet.getRange('C12').setValue('');
  }
}

スプレッドシート上で検索条件をリストに存在しないもの変えて試してみます。

実行結果：

結果欄に「一致なし」と表示されました。

【応用】コードの整理

現在のコードだと検索条件欄や結果欄を別のセルにしたい場合などに書き換える部分が多くて不便です。
名前付き範囲を使ってメンテナンス性を上げてみます。

まずは参照するデータ一覧の範囲を「データ」という名前で名前付き範囲作成。
「検索条件1」「検索条件2」「検索結果」も同様に作成します。

コードに改造を加えます。

まずは冒頭で名前付き範囲の名前を指定します。
今後もし名前付き範囲の名前を変えた場合は、ここの内容を変えるだけでOKです。

そして名前を使って名前付き範囲から範囲オブジェクト（Range）を取得する関数を後部に追加しています。
詳細の説明はコード内にコメントとして記載しています。

// ここで名前付き範囲の名前を指定

// 参照するデータ一覧の名前付き範囲
const dataName = 'データ'

// 検索条件の名前付き範囲
const optionNameA = '検索条件1';
const optionNameB = '検索条件2';

// 検索結果の名前付き範囲
const resultName = '検索結果'


// スプレッドシート内のセルが編集されたときに自動実行する関数
function onEdit(e) {

  // 編集されたセルを取得
  const editRange = e.range.getA1Notation();

  // 検索条件のセルを取得
  const optionRangeA = getRangefromNamedRange(optionNameA).getA1Notation();
  const optionRangeB = getRangefromNamedRange(optionNameB).getA1Notation();

  // 編集されたセルが検索条件のセルの場合、検索を実行
  if (editRange == optionRangeA || editRange == optionRangeB) {
    multipleSearch();
  }
}

// 検索を実行し、結果を出力する関数
function multipleSearch() {

  // 参照するデータ一覧のRangeと、値を取得
  var range = getRangefromNamedRange(dataName);
  var values = range.getValues();

  // 取得したデータをオブジェクト化して整理
  values = values.map(value => {
    return {
      familyName: value[0],
      firstName: value[1],
      gender: value[2],
      prefectures: value[3],
      municipalities: value[4]
    }
  });

  // 検索条件の値を取得
  const optionA = getRangefromNamedRange(optionNameA).getValue();
  const optionB = getRangefromNamedRange(optionNameB).getValue();

  // 検索結果のRangeと、備考を出力するセル（検索結果の右隣）のRangeを取得
  const resultRange = getRangefromNamedRange(resultName);
  const resultMemoRange = resultRange.offset(0, 1);

  // ２つの検索条件に一致するデータを取得
  var results = values.filter(value => value.familyName == optionA && value.firstName == optionB);

  // 一致するデータがある場合
  if (results.length > 0) {
    // 検索結果のセルに一致した1つめのデータの「都道府県」を出力
    resultRange.setValue(results[0].prefectures);

    // 一致するデータが複数ある場合
    if (results.length > 1) {
      // 備考のセルに「他〇件」と出力
      resultMemoRange.setValue('他' + (results.length - 1) + '件');
    }
    else {
      // 一致するデータが1つの場合、備考のセルを空白にする
      resultMemoRange.setValue('');
    }
  }
  else {
    // 一致するデータがない場合
    // 検索結果のセルに「一致なし」、備考のセルを空白にする
    resultRange.setValue('一致なし');
    resultMemoRange.setValue('');
  }
}

// 名前付き範囲の中から引数で指定した「名前」のRangeを返す関数
function getRangefromNamedRange(name) {
  
  // 引数で指定した「名前」のNamedRangeを取得
  const namedRange = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges().find(nr => nr.getName() == name);

  // 名前付き範囲が見つかった場合
  if (namedRange) {
    // Rangeを返す
    return namedRange.getRange();
  }
  else {
    // 名前付き範囲が見つからなかった場合、メッセージを表示
    Browser.msgBox('「' + name + '」の名前付き範囲がありません')
    return ;
  }
}

名前付き範囲の便利なところは、既に設定している範囲を切り取り＋貼り付けで移動させた場合、自動的に範囲を変更してくれるところです。
検索条件や結果のセルを移動すると、名前付き範囲の範囲も変わるので手間がかかりません。

画像のように、もしデータが増えた場合は名前付き範囲の範囲を変更してあげればOKです。
GAS画面を開くことなく対処できるのでとても良いですね。

まとめ

今回はスプレッドシートのVLOOKUP関数でカバーできない複数条件での検索について、GASを使って解決する方法をご紹介しました。
一致した結果が複数の場合にもその旨がわかる点が便利なポイントではないでしょうか。
複数の結果を全て表示するように改造してみるのも面白いと思います。

また、今回は検索条件や検索結果の項目が固定の前提で作成しましたが、検索条件を他の項目に変えたり、検索結果として取り出す項目を可変にするなど、改造・工夫の余地がたくさんあるので、是非練習としてトライしてみてください。

Google Cloudの導入は当社にご相談ください

ITディストリビューターであるTD SYNNEXはGoogle Cloud™ Partner Award を受賞するなど、長年にわたりGoogle™のグローバル認定ディストリビューターとして、総合的な Googleソリューションを提供しています。お客様にとって最適なソリューションの提案や導入、活用をサポートします。

製品・サービスについてのお問合せ

情報収集中の方へ

導入事例やソリューションをまとめた資料をご提供しております。

資料ダウンロード

導入をご検討中の方へ

折り返し詳細のご案内を差し上げます。お問い合わせお待ちしております。

お問い合わせ

目次

スプレッドシートの複数条件VLOOKUP関数のGAS化

概要

VLOOKUP関数とは

GASの作成

1. データリストの取得

2. 検索条件の取得

3. データリストから検索条件に一致するデータを取り出し

一致するデータが複数あった場合

4. スプレッドシートへ結果の書き込み

一致するデータが複数あった場合の出力

特定のセルを編集したときに検索実行

トリガーの追加

一致するものがなかった場合の処理

【応用】コードの整理

まとめ

Google Cloudの導入は当社にご相談ください

製品・サービスについてのお問合せ

情報収集中の方へ

導入をご検討中の方へ