GASで複数条件対応オリジナルVLOOKUP関数の作成
【入場無料】TD SYNNEX主催のIT展示会「Inspire 2024」10/24(木)東京国際フォーラムにて開催!
スプレッドシートのVLOOKUP関数で複数条件を用いたいケースをGoogleAppsScriptのカスタム関数で解決する方法を紹介します。
概要
VLOOKUP関数は特定の条件に一致する行の指定列を取得できる、スプレッドシート上の関数のひとつです。
しかしVLOOKUP関数では1つの条件に合致する行しか検索できず、複数の条件に合致する行を絞り込みたい場合には手間と工夫が必要になります。
今回は前回の記事とは違う方法で解決する方法を紹介したいと思います。
前回はスプレッドシートで条件のセルを編集した際に自動実行するトリガーを用いて、複数条件に一致するデータの特定の列の内容を出力する方法をご紹介しました。
しかしこの方法では元のVLOOKUP関数のように1つのセルで完結できる必要がある場合は少し不便になります。
今回は「カスタム関数」を用いて複数条件に対応できるオリジナルVLOOKUP関数を作成してみたいと思います。
スプレッドシートには適当なデータを入れておきます。
念のため、前回とは違うスプレッドシートを用いてください。
使用するGASはスプレッドシート等に紐づいたタイプにしたいと思います。
GASの準備については以前の記事の「スプレッドシートに紐づいたGASの作成方法」の項目を参考にしてください。
カスタム関数とは
カスタム関数とは、GASで作成した関数をスプレッドシート上でスプレッドシート関数のように使用することができる機能です。
カスタム関数には返り値
の設定が必要になります。
返り値とは:
関数を実行した際に返ってくる値のことを言います。
例えば、代表的なスプレッドシート関数のSUM関数
の場合は入力・指定された値の合計
が返り値
となります。
簡単なカスタム関数の例を見てみましょう。
function TEST_FUNCTION() {
// 返り値を設定
return "これはテスト関数です"
}
これをスプレッドシート上で使用してみましょう。
任意のセルに=TEST_FUNCTION()
と入力します。
エンターして数秒待つと返り値のこれはテスト関数です
が表示されました。
カスタム関数の基本的な動きと使い方は以上になります。
GASの作成
先に通常のVLOOKUP関数の形を確認してみましょう。
=VLOOKUP(検索キー, 範囲, 指数, false)
では、今回作成するカスタムVLOOKUP関数はこれを改良して以下のようにしてみます。
※並べ替えの部分に関しては今回は考慮しません。
=C_VLOOKUP(検索キー1, 検索キー2, 範囲, 指数)
検索キー1
:もとのVLOOKUP関数の検索キーと同じです。範囲の最初の列でこの内容と一致するものを探します。検索キー2
:2つめの検索キーです。範囲の2番目の列でこの内容と一致するものを探します。範囲
:もとのVLOOKUP関数の範囲と同じです。セル名で範囲を指定します。指数
:もとのVLOOKUP関数の指数と同じです。最初の列から数えて何列目のデータを取り出したいか指定します。
1. 引数の設定
まずは引数
の設定をします。
引数とは: 関数を実行する際に与えるデータのことを言います。
例えば、代表的なスプレッドシート関数のSUM関数
の場合は入力・指定する範囲
が引数
となります。
今回の場合、引数は検索キー1, 検索キー2, 範囲, 指数
の4つになります。
まずは引数がどのように取得されるかを確認してみましょう。
下のコードの時点では検索は行わず引数を取得する動作だけですので、返り値は仮のものとして、検索キー1で取得した値をそのまま表示するようにしておきます。
// ※実行・テストはスプレッドシート上で行ってください
// =C_VLOOKUP(検索キー1, 検索キー2, 範囲, 指数)
function C_VLOOKUP(keyA, keyB, range, target){
// 入力された引数を実行ログに表示
console.log('keyA = '+ keyA);
console.log('keyB = '+ keyB);
console.log(range);
console.log('target = '+ target);
// 仮の返り値(検索処理は行わず、keyAをそのまま表示します)
return keyA
}
実行結果:
検索処理をせず、返り値には取得したkeyAを指定しているので、スプレッドシート上には検索キー1
の内容が表示されました。
実行ログはGASの左メニュー「実行数」から、該当の実行をクリックして開くと確認できます。
このように範囲
には指定した範囲のデータが2次元配列で入っています。
2. 検索処理の追加
次に、引数で与えられたデータを用いて取得したいデータを取り出してみたいと思います。
2次元配列で取得した範囲のデータにfind処理を用いて検索します。
find処理はGASではなくJavaScriptの機能のひとつです。
以前の記事内で解説しているので参考にしてみてください。
// ※実行・テストはスプレッドシート上で行ってください
// =C_VLOOKUP(検索キー1, 検索キー2, 範囲, 指数)
function C_VLOOKUP(keyA, keyB, range, target){
// 検索キーに一致する行のデータ用の変数を作成
let resultRow = [];
// 検索結果用の変数を作成
let result = '';
// find処理を用いて2次元配列から検索キー2つに一致する行を取り出す
// data[0]は各行の1つ目のデータ=姓、data[1]は各行の2つ目のデータ=名
resultRow = range.find(data => data[0] == keyA && data[1] == keyB);
// 指数を用いて検索結果を取り出す
// target = 4 の場合、配列の4番目つまり [3] となる
// ※配列の取り出し方 resultRow[0]⇒resultRowの1番目のデータ、resultRow[1]⇒resultRowの2番目のデータ…
result = resultRow[target - 1];
// 実行ログで検索結果を確認
console.log(resultRow);
console.log('result = ' + result);
// 返り値(検索結果を表示)
return result
}
実行結果:
スプレッドシート上には2つの条件に一致する行の4番目 =「都道府県」が表示されました。
GASの左メニュー「実行数」から、確認できる実行ログには検索で取り出した行のデータと検索結果が表示されます。
基本的な作りについてはこれで完成です。
3. 引数の確認
現在の状態だと意図しない形のデータを受け取った時にエラーが発生してしまいます。
例1:検索キーに範囲を指定
例2:指数に数字以外
例2:指数が範囲外の数字
例1であれば「ERROR」と出るのでまだ間違いに気付けますが、例2や3のように何も起きないのは間違いを見落としそうで困ります。
引数のデータをチェックして不適な場合はエラー表示するようにしたいと思います。
エラーの表示はthrow new Error(エラーメッセージ)
で行います。
function C_VLOOKUP(keyA, keyB, range, target){
// 追加部ここから
// Array.isArray(Object)を用いてkeyAとkeyBが配列(=範囲)ではないか確認
if(Array.isArray(keyA)) throw new Error('検索キー1で範囲が選択されています');
if(Array.isArray(keyB)) throw new Error('検索キー2で範囲が選択されています');
// rangeの要素数(=行数)が2未満の場合は、検索の意味がないのでエラー
// rangeの1つ目の要素の要素数(=1行目の列数)が3未満の場合は、検索条件と検索結果が重複するのでエラー
if(range.length < 2) throw new Error('範囲では複数行の範囲を指定してください');
if(range[0].length < 3) throw new Error('範囲では3列以上の範囲を指定してください');
// 指数が3未満の場合は、検索条件を検索結果にしてしまうのでエラー
// 指数が範囲の列数より大きい場合は、検索結果の取得ができないのでエラー
if(target < 3) throw new Error('指数は3以上の数字で指定してください');
if(target > range[0].length) throw new Error('指数が範囲外です');
// 追加部ここまで
let resultRow = [];
let result = '';
resultRow = range.find(data => data[0] == keyA && data[1] == keyB);
result = resultRow[target - 1];
console.log(resultRow);
console.log('result = ' + result);
return result
}
実行結果:
スプレッドシート上で、検索キー1を範囲にしてみるとこのようになります。
4. 関数の説明を表示
元からあるスプレッドシート関数は関数の説明が表示されて便利ですよね。
引数をいくつ設定しなければいけないのか、どういうものにすべきかもわかるので1つ1つ暗記していなくても大丈夫です。
カスタム関数でもこれらを表示するように設定が可能です。
下記のようにコメントを用いて関数の直前に記載します。
各引数の{}
の中の文字は例として説明に表示される入力例です。
/**
* 複数条件で行検索ができるカスタムVLOOKUP関数
*
* @param {山田} keyA 【検索キー1】検索条件の1つ目、範囲の先頭列からこの値を検索する。値またはセル1つで指定する。
* @param {花子} keyB 【検索キー2】検索条件の2つ目、範囲の2列目からこの値を検索する。値またはセル1つで指定する。
* @param {A1:E9} range 【範囲】検索対象の範囲です。範囲の先頭列で [検索キー1][検索キー2] に一致するキーを検索します。
* @param {3} target 【指数】値を返す列の指数です。[範囲] の先頭列を 1 とします。
* @customfunction
*/
function C_VLOOKUP(keyA, keyB, range, target){
// 以下前項のまま
if(Array.isArray(keyA)) throw new Error('検索キー1で範囲が選択されています');
if(Array.isArray(keyB)) throw new Error('検索キー2で範囲が選択されています');
if(range.length < 2) throw new Error('範囲では複数行の範囲を指定してください');
if(range[0].length < 3) throw new Error('範囲では3列以上の範囲を指定してください');
if(target < 3) throw new Error('指数は3以上の数字で指定してください');
if(target > range[0].length) throw new Error('指数が範囲外です');
let resultRow = [];
let result = '';
resultRow = range.find(data => data[0] == keyA && data[1] == keyB);
result = resultRow[target - 1];
console.log(resultRow);
console.log('result = ' + result);
return result
}
実行結果:
スプレッドシート上でカスタム関数の説明が表示されました。
まとめ
今回はカスタム関数を作成してスプレッドシートのセル上で元のVLOOKUP関数のようにカスタム関数を呼び出す方法をご紹介しました。
今の関数では検索キーが先頭列とその次の列で固定になってしまっているので、任意の列を検索キーに設定できるように引数を増やして改良してみるのも良いと思います。
是非便利な形に改造して活用してみてください。