Google Apps は Microsoft Office のような機能を持つ Office Suite 群です。
機能的には十分業務に使えるレベルを持ち、無償で使えるという太っ腹なツールです。また、マクロを JavaScript で記述することができる「Google Apps Script」を備えているので、データの加工などを自動化することもできます。
このエントリーでは Google Apps Script を使って 特定の値を持つ行の任意の列の値を集めるスクリプトをシート関数として使う方法を紹介します。VLOOKUP の機能拡張版と思っていただくとわかりやすいかと思います。
VLOOKUP では指定した値に合致した最初の行だけを処理します。これは重複のない値の列に対しては有効なのですが、例えばチーム分けをしたいというような場合には使えません。
動作イメージとしてはこうです。
次のようなデータから
| チーム | 名前 |
| 赤 | 平田 |
| 青 | 岡田 |
| 黄 | 山田 |
| 青 | 田村 |
| 赤 | 橋本 |
| 青 | 鬼瓦 |
以下の様なデータを作成します。
| 赤 | 青 | 黄 |
| 平田 | 岡田 | 山田 |
| 橋本 | 田村 | |
| 鬼瓦 |
まずはコードを。
function GroupFilter(key, range, position, target) {
// key, range, position が空の場合は null を返す
if (! (key || range || position) ) return null;
// target のデフォルト値を設定する
if (typeof target === 'undefined') {
target = 0;
}else{
// シート上の指定値を配列で使えるようにする
target--;
}
position--;
var ss = SpreadsheetApp.getActiveSheet();
// シートの最終行を取得する
lastRow = ss.getLastRow();
var result = [];
for (var i = 0; i < lastRow; i++) {
// 指定した値に指定列の値が合致する場合にその行の指定した列の値を配列に挿入
// push するオブジェクトを [] で囲むことで行にする
if (range[i][target] === key) result.push([range[i][position]]);
}
return result;
}
関数の書式は下記のとおりです。
=GroupFilter(検索条件, 検索の範囲, 指数, 検索列)
コードの最後、処理の結果を代入する配列 result に対して push する際、オブジェクトを [] でくくっています。こうしないと処理の結果が行方向に展開されてしまいます。それは、Spreadsheet の行列が下記のような配列だからです。
[ [列1, 列2, ...] // 行1 [列1, 列2, ...] // 行2 [列1, 列2, ...] // 行3 ]
この関数と UNIQUE や SUMIF のような関数を組み合わせるとチームごとの得点の集計などが自動化できます。
ループを回すときの最大値を range の length ではなくシートの最終行としています。range の length を使うと範囲として行全体を選択した場合に処理が数百行にわたって処理されてしまうからです。
JavaScript についてはまだまだ初心者なので、甘い部分もあると思います。もっとこうしたほうがいいよ、という意見があればコメントでいただけると助かります。