葛飾でひっそり暮らすIT屋の G Suite メモ

Google Apps で快適業務ライフなノウハウを伝えます

【Google スプレッドシート】SORT と VLOOKUP で最終購入日を取得する【販売管理】

ネットショップの顧客管理をスプレッドシートで、のシリーズ第二弾です。

そして、今回で終わります。

前回の記事はこちら。

www.katsushikatech.net

前回は、販売データの中の顧客をユニーク化して顧客データとして使うというところまでやりました。

今回やるのは販売購入総額と最終購入日の算出です。

購入日が近い人と遠い人では対応も変わってくるでしょうから、最終購入日はひと目で確認できるといいですね。

まずは、購入総額を計算する

さて、まずは購入総額の計算です。

これは、SUMIF 関数を使うと簡単にできます。

support.google.com

SUMIF は指定した条件にあうデータがある行の任意の列の値を合計するものです。


SUMIF を使った数式


販売データ

画像の通り、SUMIF の第一引数で販売データのメールアドレスが記載されている列を指定します。

第二引数で数式を入力しているセルと同じ行のメールアドレスが記載されているセルを指定。

第三引数は、販売データの金額が記入されている列を指定しています。

これで、同じメールアドレスの購入金額を合計して表示してくれるというわけです。

本エントリーの本題 SORT と VLOOKUP

さて、ここからがタイトルにある内容です。

SORT 関数と VLOOKUP 関数を使って、最終購入日を取得していきます。

この方法の考え方としては、SORT 関数で、購入日付を基に降順で並べ替えて、VLOOKUP でメールアドレスをキーに購入日を取得するという方法になります。

VLOOKUP は第一引数で指定した条件が最初にヒットした行の値を取りにいきます。

なので、購入日付を降順にしておけば、購入者の購入日付が日付が一番大きいデータを拾うことができるということ。

まずは SORT を見てみましょう。

SORT の第一引数にはメールアドレスから購入日付までの列範囲を指定します。これによって、メールアドレスをもとに VLOOKUP でデータを探すことができるのです。

第二引数は、どの列の情報を基に並べ替えるか、です。

今回は購入日付を並べ替えたいので、指定した範囲のなかで購入日付が何列目なのかを指定します。

購入日付が指定した範囲の中で 6列目なので6を入れています。あくまでも第一引数で指定した範囲の中で何列目かであって、シート全体の中での列数では無いことに注意して下さい。

そして第三引数。"昇順" が TRUE か FALSE かということですが、今回は降順で並べたいので FALSE を指定します。つまり "昇順ではない" ということですね。

これでまずは並べ替えができました。

並べ替えたデータの列範囲はセルの色を変えておくと間違って編集したりすることが減ると思います。

そして次に VLOOKUP です。

顧客台帳のシートで最終更新日を入れる列に数式を入力していきます。

VLOOKUP の第一引数はメールアドレス、第二引数は 販売データシートの SORT による結果の列範囲を指定。

第三引数は選択範囲の中での購入日付の位置。今回の場合は 6 ですね。

これを入力すると、最終購入日を取得できます。

こんな感じで、いくつかの関数を組み合わせることで結構いろんな機能を実装することができるんです。

いっぺんに全部を覚えるのはしんどいですが、ひとつづつ使いながら覚えていきましょう。

ところで、最近ディープラーニングに興味を持って本を読んだりプログラムを試しているのですが、学習させるのに結構手間が掛かるんですね。

これがもっと簡単になったらどんな世界が待っているのか。

この書籍を読むとなんとなく想像が付きそうです。