オフィスソフト使いこなし(第23回)

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

このエントリーをはてなブックマークに追加
Evernoteに保存
印刷

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

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

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

1) まずは資料を準備する

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

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

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

2) 宿泊した月を表示する数式を入力する

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

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

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

3) 数式で省入力

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

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

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

このエントリーをはてなブックマークに追加
Evernoteに保存
印刷

連載記事一覧

メルマガ登録


スペシャルインタビュー


成功企業の戦略


離島の廃校で学ぶ酒造りとまちづくり

ページトップへ