Googleスプレッドシートを利用して現場運営表を作ると様々なメリットがあることをご紹介しました。
現場運営表では”顧客ID”を元に様々な情報を顧客名簿から読み込みます。
実際にやってみると気づくのですが…
そもそも名簿から特定のゲストの名前を見つけるのが面倒！！！
忙しいシーズンになればなるほど、そんな”面倒”にぶち当たります。
ということで、今回はそちらを解決してみたいと思います。
前提条件
名簿の項目として
ひらがなで苗字、名前が別々に記載されている
とします。
これを実現するために300件ほど手入力しましたが、運営表作りの手間に比べればそれくらい…（笑）
完成イメージ
- ひらがなで苗字を打ち込む
- 名前の入力欄に該当する苗字を持つ人の名前が表示される
- 名前を選択すると、苗字・名前から顧客IDが抽出される
STEP1 苗字＋名前→顧客ID
ある条件に合致する行を探し、特定のデータを返す。
これはVLOOKUP関数で実現出来ます。
しかし、複数の条件に合致する行を探し、特定のデータを返すとなると、VLOOKUP関数は利用できません。
複数の条件に合致する行数をカウントしたり、合計したり、条件分岐をしたり…
こういった動きは
COUNTIFS関数やSUMIF関数、IFS関数で実現出来ます。
なのに、VLOOKUPS関数、みたいなのは存在しないんですね…
ということで、今回はQUERY関数を使ってみたいと思います。
細かい使い方は以前ご紹介したものをご覧頂くとして、例えば
- A列に”顧客ID”
- B列に”みょうじ”
- C列に”なまえ”
だとすると、こんな感じになります。
リストに明訓五人衆を入れてみたり…（笑）
構文は以下の通りです。
=QUERY(＜検索対象範囲＞,”select ＜抽出データの列名＞ where ＜検索範囲のみょうじの列名＞ = ‘”&＜みょうじのセル番号＞&”‘ AND ＜検索範囲のなまえの列名＞ = ‘”&＜なまえのセル番号＞&”‘ limit 1″)
limit 1は、誤作動を防ぐため、結果のうちの1つだけを表示することを明示しています。
ゲストの苗字と名前が完璧に頭に入っていれば、これでも十分に活用できますね！
STEP2 苗字から名前の候補を表示する
これは関数では実現できません…
複雑なことを行うとなると、GoogleAppsScript（以下GAS）のお世話になる必要があります。
GASは以前もご紹介しましたが、Googleが提供するスプレッドシートなどのアプリケーションの、複数の作業を自動化したり、いくつかのGoogleのアプリケーションを連携したり、と様々なことを可能にするプログラミング言語のことだと思って下さい。
ちなみにこれを編集するためには、スプレッドシートで『ツール』→『スクリプトエディタ』とクリックして行くと編集画面が出現します。
実現したいことを紐解く
今回の想定する挙動を棚卸ししてみます。
- 検索対象範囲を指示する
- “みょうじ”を読み込む
- 検索対象の”みょうじ”と”みょうじ”を1行ずつ比較し、一致したら”なまえ”を格納する
- 格納した”なまえ”を項目とするプルダウン形式の入力規則を生成する
以上です。
えと…
あとは…
えいっ！
//関数開始
function onEdit(e){
//今のシートを『sheet』に指定
var sheet = SpreadsheetApp.getActiveSheet();
//検索対象のシートを『source』に指定
var source = SpreadsheetApp.getActive().getSheetByName('名簿');
//今選択しているセルを『myCell』に指定
var myCell = sheet.getActiveCell();
//もしmyCellがC列なら（3番目の列なら）
if(myCell.getColumn()==3){
//myCellの行番号を『rownum』に格納
var rownum = myCell.getRow();
//myCellの値を『lastname』に格納
var lastname = myCell.getValue();
//検索対象のシートから”みょうじ”を『lastNameKana』に格納
var lastNameKana = source.getRange("B:B").getValues();
//検索対象のシートから”名前”を『firstNameKana』に格納
var firstNameKana = source.getRange("C:C").getValues();
//一致したものを格納する箱『matchList』を用意
var matchList = new Array();
//lastNameKanaの要素の数だけ繰り返し。iはカウント用
for (var i = 0; i<lastNameKana.length; i++){
//もしlastNameKanaのi番目の値がlastnameと一致したら
if(lastNameKana[i] == lastname){
//matchLishに対応する”なまえ”を格納
matchList.push(firstNameKana[i]);
//一致判定終わり。一致しなければ何もしない。
}
//繰り返し終わり。
}
//プルダウン形式の選択肢をmatchListにした入力規則を構築
var rule = SpreadsheetApp.newDataValidation().requireValueInList(matchList,true).build();
//入力規則を現在の行の4番目の列（D列）に適用
sheet.getRange(rownum, 4).setDataValidation(rule);
//現在のセルがC列の場合、の条件分岐終わり。一致しなければ何もしない。
}
//関数終わり
}
すこぶる見づらいですが、こんな感じです。
ただしこの機能にはひとつだけ弱点が。
同姓同名のゲストが名簿にいると、上手く行きません。
それすらも解決するのであれば、生年月日などで名寄せをするしか無いかと思います。
例えば今回のコードに、同姓同名だった場合のみ生年月日を入力するダイアログボックスを出して…
と、やり方は思いつきますが、そんなに頻出する課題では無いかと思うので、今回はこの辺で…（笑）