エクセルを使ってリピートユーザーの売上を判別する

　売り上げデータを集計するとき、リピーターがどの程度あるのかわかれば、今後の経営に非常に役立ちます。

ここでは、飲食ホテル業の予約集計を例に、数式やチェックボックスを使って、リピーターの把握がわかりやすくできる方法を考えていきましょう。

数式がたくさん出てきますので、少し難解に感じるかもしれませんが、一度作ってしまえば、その後の入力の手間は半減します。

１） まずは資料を準備する

　まずは、宿泊者の情報を入力する「住所録」と、宿泊料金などを記載した「予約コース」という2つのシートを作成します。

　新しいシートは、現在のシートの右に「＋」ボタンをクリックすると作成されます。住所録には、予約者の名前や住所、電話番号を入力し、「予約コース」にはコースや料金の情報を入力し、それぞれ名前をつけておきます。

　さらにもう1つ、予約関する詳しい情報を記載する「予約状況」というシートを作成しましょう。項目については後で付け足せるので、今は必要な部分を並べておけばＯＫです。

２） 宿泊した月を表示する数式を入力する

　入力ができたら、実際にデータを入力します。まずは、後で月別の状況を集計するために、宿泊した日から月だけを抜き出す式を、予約状況の「予約日」の欄に入力します。

式：=IF(B2=””,””,MONTH(B2))

　関数「month」は、予約日が3月なら「3」、4月なら「4」を予約月に自動的に入力させます。「MONTH(B2)」だけ入力するのでもよさそうですが、予約日が未入力のセルでは値が取れないので、エラー表示になります。それを回避するため、あえて「IF」から始まる前半部分が必要になります。

３） 数式で省入力

　予約状況のセルに、予約者の指名と住所を入力していきます。しかし、できるだけ文字や数値を入力する部分を減らした方が間違いの数は減りますし、入力もラクです。

　そこで、過去に予約をした人の名前を入力すると、自動的に住所録から住所と電話番号を呼び出す数式を入力しましょう。数式は以下のとおりになります。

住所に入力する式：=IF(D2=””,””, VLOOKUP(D2,住所録!B:L,10,FALSE))

電話番号に入力する式：=IF(D2=””,””,IF(VLOOKUP(D2,住所録!B:L,6,FALSE))

　ここで使った「vlookup」という関数は、指定した範囲（ここでは住所録）から、氏名に入力された値と一致するデータを選び、住所列（10番目の列）から該当する住所を選んで表示させる関数です。

　もう一度、住所を呼び出す数式をもう一度振り返りましょう。

式：=IF(D2=””,””, VLOOKUP(D2,住所録!B:L,10,FALSE))

　最初のif関数は、データが入力されていないときにエラー表示されるのを回避するための配慮です。

　以降は、氏名（d2）に入力されているデータを、住所録シートにあるデータ（住所録!B:L）から検索し、これに完全一致（FALSE）するデータがあれば、住所列（10）から抽出する、という命令となります。

　“10”というのは、住所録シートの中の「氏名」列（B列）から10番目の列という意味です。電話番号を表示するのであれば、B列から6番目なので、”6”となります。

　ところが、ここで1つ問題があります。住所録にないデータが入力されると、エラーが表示されます。気になるなら、少し複雑ですが、エラーが出ないようにできます。それには、数式に「もし住所に該当する氏名がなければ空白を表示する」という命令を付け加えます。

　まずは、住所録の氏名に合致する名前があるかどうかを判別する列を追加します。そして電話番号の右側の列番号のところで右クリックし、「挿入」を選びます。すると、電話番号の右に新しい行が追加されます。

　挿入した列に、次の数式を入力します。

式: =IF(D2=””,””,COUNTIF(住所録!B:B,予約状況!D2))

　上記の関数は、住所録に該当の名前があれば“１”，なければ“0”を表示する、という命令です。

　さきほど「IF～VLOOKUP～」を入力した住所の列に戻り、エラーが表示されないように、式を修正しましょう。

式:=IF(D5=””,””,IF(G5=1,VLOOKUP(D5,住所録!B:L,10,FALSE),””))

　If関数の間にもう一つif関数を挟みます。これで、氏名が空白なら“”、空白でなく、チェック欄が”１”ならvlookup関数を実行し、それ以外なら“”となります。

　この数式は、もちろん住所以外の欄でも対応可能です。たとえば電話番号の場合は、住所に当たる”10”から、電話番号のセルに当たる”6”に変えます。

　ここで、今まで作った式を、それぞれの下のセルにコピーし、データを入力していきます。数個入力してみて、正しい値が出ない場合は、範囲の選択や式の書き間違いをチェックし、修正していきます。

４） リスト表示で省入力

　数式以外でも、わかっている項目はリスト選択をする方法があります。ここでは、予約状況のシートの「予約コース」欄に、リスト入力を使ってみましょう。

　メニューリボンの「データ」タブをクリックし、データの入力規則を選択します。

　ポップアップが開くので、入力値の種類にリストを選び、リストに使用する範囲（予約コースシートの予約コース行に入力されたセル）を「元の値」に入力します。

　範囲はセルをコピーしたときにずれないように、「$B$1:$B$8」のように“＄”をはさんでおきます。“＄”は、セルや範囲を指定したときに、コピーや移動で範囲がずれるのを防ぐための記号です。

　これで「予約コース」をイチイチ入力することなく、プルダウンで選択することができます。このリスト入力をコピーするには、普通のコピペではなく、「書式のコピー」を選択します。

５） 計算式を入力する

　金額などは計算式で一度にやってしまいましょう。

「予約状況」のシートに入力した大人、子どもの人数と、「予約コース」シートに入力された料金から、金額を簡単に求める式を作ります。ここで「lookup」関数を使います。「vlookup」関数とよく似ていますが、設定が簡単なのでこちらを使います。

　まずは、大人の予約金額を算出する計算式を作りましょう。そのための第一歩として、「予約コース番号」のセルに、以下の式を入力します。

式: =IF(K2=””,””,LOOKUP(K2,予約コース!$B$2:$B$8,予約コース!$A$2:$A$8))

　これは「予約コース」シートに登録した連番を呼び出すための関数です。すでにK列で予約コースは選択されているため、K列を参照するように指示しています。下の行にもコピーしていきますが、参照元は変わらないため、“＄”を忘れないようにしましょう。

　続いて、予約時の大人の金額を算出しましょう。 “予約コースの大人の金額”×”大人の人数“です。If関数とlookup関数を組み合わせ、以下の数式をM列に入力します。

式:=IF(L2=””,””,(LOOKUP(L2,予約コース!$A$2:$A$8,予約コース!$C$2:$C$8))*予約状況!I2)

　L列で表示した予約コースの番号を元に大人料金を参照し、その料金と人数をかけることで、大人料金の合計金額を算出しています。

これは子供料金に関しても同様です。N列にこども金額、合計金額に以下の数式を入力し、下のセルにコピーして、表を完成させます。

６） チェックボックスを使う

　ここからもう少しステップアップしましょう。当初の目的である、リピーターがどのくらいあるか、チェックボックスを使って、わかりやすくする方法です。

「リピータチェック」列（G列）の上にチェックボックスを作ります。「開発」タブのメニューを使いますので、メニューに表示されていない場合には、「ファイル→オプション→リボンのユーザー設定」の順にクリックし、右側の一覧の「開発」のところのチェックボックスをオンにします。

　続いて、「リピータチェック」のセルを選択して、コードの表示を選びます。すると、VBAのコードが表示されます。

　詳しい説明は省きますが、とりあえず下記コードを入力してみてください。これはチェックボックスを一気に作成するマクロのプログラムです。作成数とスタートするセルの位置は適宜設定します。

　コピーが終わったら、コードは開いたままで、元の表を表示させます。タスクバーのExcelアイコンを長押しすると選択できます。

　開発タブの「マクロ」をクリックし、実行ボタンをクリックしてマクロを実行させます。すると、リピーターのチェックボックスにマークが表示され、リピーターなのかそうでないのかが、一目瞭然でわかるというわけです。

7）まとめ

今回は難しい数式やプログラムも使いました。しかし、これらの関数を使えば、間違いも少なく、正しい分析が可能になります。チェックボックスを使うことで、リピーターの割合が一目瞭然となり、今後の事業展開にも役立てられます。ぜひ挑戦してみてください。