経験本数の把握を実現する方法を、以下２つの記事でお伝えしました。
ここでご覧いただいた完成イメージをよく見てみると
最終潜水
内容
という項目があることをお気づきになるかと思います。
ということで、今回は経験本数の把握を拡張し、前回来店と、その時の内容を一目で把握出来るようにしたいと思います！
前提条件
顧客名簿をGoogleスプレッドシートで作っている場合に、今回の方法が威力を発揮します。
イメージはこんな感じ。
さらに、ダイビング当日の体調チェックがGoogleFormにより以下の様に収集されているものとします。
※赤枠はダイビング終了後に手入力
完成イメージ
- ダイビング当日に体調チェックのフォームに記入してもらう
- その日の終わりに、潜った本数を記入する
- 自動的に顧客名簿に最終潜水とその時の内容が反映される
という流れを実現したいと思います。
使用する関数-QUERY
今回使用するのはQUERYという関数です。
スプレッドシートを提供するGoogleによれば
Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。
今までになくよく分からない説明ですね…
データを行ごとに見た時、ある列が特定の条件に合致する行の、別の列の値を取り出します。
この指示する命令文のことをクエリと言います。
それでもまだピンと来ないと思うので、実例を挙げて見て見ましょう。
黄色のセル（＝場所）に『=QUERY(A5:C7,”select C where B = “‘&A3&”‘”)』と打ち込むことで、結果を返します。
他の関数は、関数と結果が1対1対応であることが多いのですが、この関数は結果が複数該当する場合もあるため、黄色のセル1ヶ所に打ち込んだだけで自動的に条件に合致する結果全て（灰色のセル）が入力されます。
この関数を訳してみると
“A5″〜”C7″の範囲の値で、”B”列の値が”A3″の行の”C”列の値を返しなさい
という意味です。
実装
最終潜水
ここからは、体調チェックとその日のダイビング本数を記入した以下のシートのタイトル行以外を、以前ご紹介したIMPORANGE関数によって、顧客リストというファイルの”活動”というシートに展開されているものとします。
〜顧客リストの先頭行〜
考え方としては、活動データの中から特定の氏名の人のデータを抽出し、そのデータの最新の日付のものを取り出します。
これを実現するには、J列、”最終潜水”のところに、以下のように入力します。
=QUERY(‘活動’!A:P,”select A where C = ‘”&B2&C2&”‘ order by A desc limit 1″)
“活動!”というのは、『活動というシートの』という意味です。
“B2&C2″の”＆”は、『B2とC2の文字列をつなげて』という意味です。
範囲について、”列番号”:”列番号”という書き方は、その列全ての範囲、という意味です。
尚、先程ご説明していない英語も出てきているのでご説明したいと思います。
“limit 1″というのは、結果のうちの1つだけを表示しなさい、という命令です。
これによって、先程の例のように1回の入力で複数の結果が表示されてしまうことを防ぎます。
これだけだと昇順で先頭のひとつ、つまり最も古い日付が出てきてしまうため、”order by A desc”という命令を付け加えます。
これは、”A”列を降順で並べ替えてから表示しなさい、という命令です。
これで無事、最終潜水を表示することが出来ました！
内容
お次はその時の活動内容を表示したいと思います。
考え方としては、活動データの中から特定の氏名かつ最新の日付のものの活動内容を取り出します。
これを実現するにはK列、”内容”のところに、J列の最終潜水同様
=QUERY(‘活動’!A:P,”select P where C = ‘”&B3&C3&”‘ and A = ‘”&J3&”‘ limit 1″)
とすれば良さそうです。
ここで、”and”を使用することで”C列”が特定の名前の人でかつ”J列”の最終潜水の日付のデータ、という複数の条件を与えています。
さて、これで良さそうなのですが、これでは上手く行きません。
条件の二つ目、最終潜水の日付のデータは、当然”年月日”の形式になっています。
QUERY関数では、この”年月日”のデータを直接比較することが出来ないため、一度文字列に変換するという作業が必要になります。
“年月日”のデータを文字列に変換するためには”TEXT”という関数が使用でき
TEXT（”変換する年月日データのセル”,”変換形式”）
とすることで変換が可能です。
尚、変換形式は”YYYY-MM-DD”の様に表し、これは年を4桁で、月を2桁（一桁の場合は0x）で、日を2桁（1桁の場合は0X）で、それぞれを”−”（ハイフン）で繋ぐ、ということを表現しています。
これを踏まえてQUERY関数を書き直すと、以下のようになります。
=QUERY(‘活動’!A:P,”select P where C = ‘”&B3&C3&”‘ and A = date'”&TEXT(J3,”YYYY-MM-DD”)&”‘ limit 1″)
これで無事、内容を表示することが出来ました！
今までご紹介した関数に比べると、格段に複雑な関数ですが、その分実現できることの幅が非常に広いQUERY関数。
上手く利用すれば、様々なことに応用出来そうですね！
多くの方がお使いのMicrosoftExcelには無い機能なので、実現する際にはGoogleスプレッドシートをお使い下さいね！
さて、最終潜水時の内容が集計出来る、ということは、その内容のうちファンダイビング以外のもの、＝講習の中で最新のもの、も集計することが出来そうですよね！
そうすれば、その人のライセンスランクも自動で表示できる…
“ある人”の、”ファンダイビング以外”の、”最新のもの”を表示するには以下のように書きます。
=QUERY(‘活動’!A:P,”select P where C = ‘”&B2&C2&”‘ and P <> ‘”&”ファン”&”‘ order by A desc limit 1″)
※ファンダイビングを『ファン』と表現している場合
ただし、よくよく考えてみると、ダイビングショップの活動にはライセンスランクと活動内容の名前が一致しないものとして、ファンダイビング以外もありますよね？
体験ダイビング、各種スペシャルティー、リフレッシュダイビング…
特にスペシャルティーなどは、新たなコースを開催する場合もあると思うので、その都度関数を書き換えていたら大変です。
どちらかといえば、ライセンスランクと活動内容の名前が一致しないもの、よりもライセンスランクと活動内容の名前が一致するもの、の方が限定出来ると思うので、こう？
=QUERY(‘活動’!A:P,”select P where C = ‘”&B2&C2&”‘ and (P = ‘”&”SCD”&”‘ or P = ‘”&”ASD”&”‘ or …) order by A desc limit 1”)
※『…』はその他のライセンスランクも同様に、という意味です
あーでもこれだと、活動内容を『SCD初日』、『SCD2日目』…の様に書いている場合、ライセンスランクのところにも『SCD3日目』などと表示されてしまう…
とまぁ実現したいことに対して工夫を凝らすのも関数の非常に楽しいところなのですが…（自分だけ？）
ライセンスランクを自動で表示する方法についてはまたの機会にしたいと思います！