エクセルでポイントカードを作成する｜台帳と印刷を分けて考える

エクセルでポイントカードを作成したいと考えるとき、実は2つの別の作業が頭の中で混ざっています。ひとつはお客さまに渡す紙のカードそのものを作ること。もうひとつは、誰が何ポイント持っているかを記録して数えることです。前者は1時間ほどで終わりますが、後者は運用の設計が要ります。この記事では、この2つを分けて手順を示し、エクセルのままで続けられる規模と、そうでなくなる境目をはっきりさせます。

エクセルで作れるものと、作れないもの

先に守備範囲を決めます。ここを曖昧にしたまま始めると、途中で「思っていたことができない」と気づいて作り直しになります。

エクセルで作れるもの。 紙のカードの版下、つまり印刷するための面のデザイン。お客さまごとのポイント残高を並べた台帳。来店の履歴と、そこから計算した合計。特典を出す条件に達した人の抽出。月ごとの発行枚数や利用状況の集計。これらはすべてエクセルの範囲です。

エクセルで作れないもの。 お客さまが自分のスマホでポイント残高を確認する画面。レジで自動的にポイントを加算する仕組み。ポイントが貯まったことをお客さまに自動で知らせる連絡。複数の店舗が同時に同じ台帳を書き換える運用。これらは別の仕組みが必要になります。

この線引きが分かると、判断が単純になります。お客さまが自分で残高を見る必要がなく、レジでスタッフが手で入力してよく、店が1つなら、エクセルで十分に回ります。逆に、どれか1つでも必要なら、エクセルで作り込む時間を、別の道具を選ぶ時間に回したほうが早く着きます。

もう一つ注意したいのは、エクセルは「その瞬間に開いている人だけが正しい値を持つ」道具だということです。スタッフが2人で同時に開いて、それぞれがポイントを加算すると、後に保存した人の内容だけが残ります。この性質は、レジが1台なら問題になりませんが、2台になると必ず事故になります。

印刷する面をエクセルで作る手順

まず、お客さまに渡すカードそのものを作ります。専用のデザインソフトは要りません。

サイズを決める。 財布に入る前提なら、名刺サイズの91×55mmが基本です。二つ折りにしてスタンプ欄を広く取るなら、開いた状態で182×55mmになります。印刷会社の規格もこのサイズを標準にしているので、あとから外注するときに作り直しが要りません。

ページ設定をミリ単位にする。 エクセルの既定の単位はセルの幅と高さで、ミリではありません。表示をページレイアウトに切り替えて、行の高さと列の幅をミリで合わせていく方法が確実です。目安として、標準のフォントで行の高さを1mmにすると約2.83ポイントになります。細かい計算が面倒なら、図形を挿入して幅と高さをミリで指定し、その図形の中に文字を入れる方法が早いです。

入れる要素を決める。 表面に店名、連絡先、営業時間。裏面にスタンプやポイントを押す枠、有効期限の注記、特典の条件。裏面の枠は、実際に使うスタンプの直径より2mmほど大きく取ります。枠がぴったりだと、少しずれたときにはみ出します。

枠の数を決める。 スタンプ式なら10個か12個が扱いやすい数です。10個は達成が見えやすく、12個は月に1回来る人が1年で1周する形になります。枠が20個を超えると、達成する前に紛失されます。

印刷して手で確かめる。 家庭用のプリンターで1枚刷って、実際にスタンプを押してみます。インクがにじむ紙、押しても薄くしか付かない紙があります。紙の相性は刷ってみないと分かりません。

外注するかを決める。 手元で刷る場合は1枚あたりの用紙代とインク代だけで済みますが、枚数が増えると手間が費用を超えます。印刷会社に出す場合の相場は、名刺サイズの片面カラーで100部1,577円から、二つ折りで100部3,929円からという価格が公開されています。手元で刷るか外注するかの境目は、おおむね100枚あたりです。

刷る紙を選ぶ

版下ができたら、どの紙に刷るかを決めます。スタンプを押す前提のカードでは、ここを外すと全部刷り直しになります。

印刷会社が公開している筆記性の目安では、上質紙は鉛筆もボールペンもスタンプも押せる紙として案内されています。マット紙は鉛筆とボールペンは書けるものの、スタンプは適さないとされています。光沢紙は鉛筆が書けず、スタンプも適しません。表面の加工が強い紙は、インクが乾かずににじみます。

つまり、スタンプを押すカードなら上質紙を選びます。写真やイラストをきれいに見せたいなら光沢のある紙になりますが、その場合はスタンプ欄を作らず、ポイント式にして台帳側で数える形にします。見た目とスタンプは両立しないので、どちらを取るかを先に決めます。

厚さも決めます。薄いと財布の中で折れ、厚いと財布に入りません。名刺と同じくらいの厚さが目安で、印刷会社の用紙の一覧では上質180kgやマットコート180kgから220kgの範囲が標準的な選択肢として並んでいます。手元のプリンターで刷る場合は、対応する厚さの上限を先に確かめておきます。

ポイントを記録する台帳をエクセルで作る

次に、誰が何ポイント持っているかを記録する側を作ります。ここが本体です。

シートを3枚に分ける。 1枚目を顧客の一覧、2枚目を来店の記録、3枚目を集計にします。1枚のシートに全部を詰め込むと、来店が増えたときに横に伸びて扱えなくなります。

顧客の一覧に置く列。 顧客番号、氏名、ふりがな、電話番号、登録日、備考。顧客番号は必ず作ります。同姓同名が出たときに、氏名だけでは区別できません。番号はカードに印刷するか、手書きで書き入れます。

来店の記録に置く列。 日付、顧客番号、金額、付与ポイント、使用ポイント、担当者、メモ。1回の来店で1行を書きます。この形にしておくと、あとから集計の切り口を変えられます。逆に、顧客ごとに横方向へ来店を書き足していく形にすると、集計ができなくなります。

集計は関数で出す。 顧客ごとの残高は、来店の記録から計算します。手で残高の数字を書き換える形にすると、書き換え漏れと二重計上が必ず起きます。計算で出す形にしておけば、元の記録が正しければ残高も正しくなります。

入力を選択式にする。 担当者や来店区分は、入力規則のリストから選ぶ形にします。手入力にすると表記が揺れて、集計が合わなくなります。

この形で作ると、来店が月200件あっても、エクセルは重くなりません。重くなるのは、1つのセルに複雑な関数を何千行も入れたときです。

関数の組み方は3つ覚えれば足りる

台帳の計算に使う関数は、実務では3つで足ります。

SUMIF。 特定の顧客番号の行だけを足します。来店の記録から、その人の付与ポイントの合計と使用ポイントの合計をそれぞれ出し、引き算すれば残高です。条件を1つだけ指定する形なので、顧客番号を渡すだけで動きます。

COUNTIF。 特定の顧客番号が何回出てくるかを数えます。来店回数がこれで出ます。スタンプ式のカードなら、この回数がそのままスタンプの数になります。

VLOOKUP。 顧客番号から氏名や電話番号を引いてきます。来店の記録に氏名を手で書かなくて済むので、表記の揺れが消えます。

この3つを組み合わせると、顧客の一覧に「来店回数」「付与合計」「使用合計」「残高」の4列が自動で出ます。ここまで作れば、台帳としては完成です。

条件が2つ以上になる集計、たとえば「今年の来店だけ数える」ときは、SUMIFS と COUNTIFS という複数条件の形に置き換えます。名前が違うだけで、考え方は同じです。日付の範囲を条件に入れる場合は、比較する日付をセルに入れて、そのセルを参照する形にすると、期間を変えるときに関数を書き直さずに済みます。

関数を入れる場所は、必ず集計のシートか、顧客の一覧の右側にまとめます。来店の記録のシートに関数を混ぜると、行を追加したときに計算式が崩れます。

還元率と特典の条件を決める

仕組みができても、還元率の設計が雑だと続きません。決める項目は3つです。

何に対して付けるか。 金額に対して付ける形と、来店回数に対して付ける形があります。単価が人によって大きく違う業種は金額、単価がほぼ一定の業種は回数のほうが分かりやすくなります。

どのくらい付けるか。 実際に運用されている例を見ると、付与の単位と還元率を階段状に設計しているものがあります。

エクセルメンバーズカードは、使うほどにポイント付与額アップ！ エクセルでご利用の場合最大5%ポイント還元。（エクセル以外でのご利用の際は月間ご利用合計1,000 円毎に5ポイントとなります。） 出典: x-sell.jp

この例では、利用額に応じて還元率が上がる階段が作られています。小さな店で同じ形を真似する必要はありませんが、「使うほど得になる」という設計が続く理由は押さえておく価値があります。小さな店で扱いやすいのは、還元率を1%から3%の範囲に置き、それより上は回数の特典で作る形です。金額の還元だけで差を付けようとすると、原価を削る話になります。

いつまで有効か。 有効期限を決めます。期限がないと、何年も前のカードが突然出てきて、そのときの条件で特典を求められます。1年か2年が扱いやすい範囲です。期限はカードの裏面に必ず印刷します。

還元率を決めたら、原価に対する比率を計算します。粗利が50%の商品で3%を還元すると、粗利の6%を使うことになります。この数字を出さずに還元率を決めると、あとから下げることになり、下げるときにお客さまの不満が出ます。

エクセル運用が崩れる4つの場所

エクセルで作った台帳が回らなくなるのは、だいたい同じ場所です。

同時に開けない。 スタッフが2人いて、それぞれがポイントを入力しようとすると、片方の入力が消えます。共有フォルダに置いた形では、この事故を防げません。この段階でスプレッドシートに移す店が多くあります。

レジの横で開いていられない。 会計のたびにパソコンでファイルを開いて、顧客番号を探して、行を追加する。この操作が1回30秒かかると、1日30人で15分です。忙しい日には入力が後回しになり、後回しになった入力はまとめて入れることになり、まとめて入れた入力は間違います。

お客さまが残高を見られない。 残高が分かるのは店の中だけなので、お客さまは自分が何ポイント持っているかを覚えていません。覚えていないポイントは、来店の動機になりません。ここが紙とエクセルの組み合わせのいちばん大きな弱点です。

貯まったことを知らせられない。 あと1回で特典に届く人に連絡できれば、その人は来ます。エクセルには連絡の手段がないので、抽出はできても送信は手作業になります。抽出した人に1通ずつメッセージを送る運用は、30人を超えたところで続かなくなります。

この4つのうち、1つでも実際に困っているなら、エクセルの作り込みを進めるより、仕組みを変えるほうが早く楽になります。困っていないなら、エクセルのままで構いません。

スプレッドシートに移すと解決することと、しないこと

同時に開けない問題は、Googleスプレッドシートに移すと解決します。複数の人が同時に編集でき、変更の履歴も残ります。関数の考え方はほぼ同じで、SUMIF、COUNTIF、VLOOKUP はそのまま使えます。

解決しないこともあります。お客さまが自分で残高を見られないこと、貯まったことを自動で知らせられないこと、レジでの入力が手作業であること。この3つは、表計算の道具である以上、どちらでも同じです。

保存場所を決めるときは、3つの原則で考えます。原本を1つに決めること。開ける人を限ること。書き出したファイルを個人の端末に残さないこと。お客さまの氏名と連絡先が入った表は、扱いの丁寧さが問われる情報です。取り扱いの判断に迷う場合は、個人情報保護委員会（https://www.ppc.go.jp/）の案内を確かめてください。

バックアップは、月に1回、日付を付けた複製を作る形で足ります。毎日残す必要はありませんが、まったく残していないと、誤って上書きしたときに戻せません。

スタンプ式とポイント式、どちらで作るか

同じカードでも、スタンプ式とポイント式では設計が違います。

スタンプ式。 来店1回で1個押します。押す作業が目に見えるので、お客さまにとって進捗が分かりやすい形です。計算が要らないので、レジでの手間はほぼゼロです。台帳側の管理も、来店の記録を1行足すだけで済みます。単価が一定の業種、回数で通ってもらいたい業種に向きます。

ポイント式。 金額に応じて付与します。単価の高いお客さまに報いられる形です。ただし、レジで計算して書き込む手間があり、書き間違いが起きます。手書きの数字を積み上げる形は、10回を超えたところで合わなくなります。ポイント式にするなら、台帳側での計算を前提にしたほうが安全です。

両方を混ぜる形。 来店回数でスタンプを押しつつ、金額の累計で別の特典を出す形もあります。分かりやすさは落ちますが、常連と単価の高いお客さまの両方に効きます。運用の手間が増えるので、台帳が自動で計算される状態になってから検討する形です。

紙のカードには、紛失と持参忘れという弱点があります。紛失したときの再発行のルールを決めておきます。「再発行は1回まで、残高は台帳から引き継ぐ」と決めておけば、その場で判断できます。台帳をきちんと作っておく価値は、この場面で出ます。

無料のテンプレートを使うか、自分で作るか

エクセルには、カードや名刺のテンプレートが用意されています。検索して出てくる無料のテンプレートも多くあります。使うかどうかの判断は、直したい箇所の数で決まります。

テンプレートが向くのは、店名と連絡先を差し替えるだけで済む場合です。枠の数、サイズ、色をそのまま使えるなら、作業は10分で終わります。

テンプレートが向かないのは、枠の数を変えたい、サイズを名刺サイズに合わせたい、印刷会社の規格に寄せたいという場合です。他人が作った図形の重なりを直す作業は、白紙から作るより時間がかかることがよくあります。テンプレートの構造を3分見て、どこをどう直せばよいかが分からなければ、白紙から作ったほうが早いと判断してよい場面です。

白紙から作るときは、図形を1つ挿入して幅と高さをミリで指定し、その中に文字と枠を配置していく方法が確実です。セルの幅と高さでレイアウトを作ると、印刷したときにずれます。図形の上に図形を重ねる形にすれば、位置を数値で指定できるので、再現性があります。

どちらで作る場合も、完成したファイルは「版下」として別名で保存し、日々の台帳と同じファイルに入れないようにします。同じファイルに入れると、台帳を編集しているうちに版下の配置が崩れます。

会員番号の付け方と、重複を防ぐ方法

台帳を作るときに最初に決めるのが会員番号です。ここを適当に決めると、あとから直せません。

連番にする。 1から順番に振るのがいちばん扱いやすい形です。1001から始めると、桁数がそろって見やすくなります。年を頭に付けて2026001のようにする形もありますが、年が変わったときに桁数が変わるので、連番のほうが単純です。

欠番を埋めない。 退会した人の番号を、別の人に付け直してはいけません。過去の来店の記録がその番号に紐付いているので、付け直すと履歴が混ざります。欠番はそのまま空けておきます。

重複を機械で止める。 顧客の一覧の会員番号の列に、入力規則で重複を禁止する設定を入れます。COUNTIFで自分より上の行に同じ番号がないかを数える形の設定にすると、同じ番号を入れた時点で警告が出ます。人が目で確かめる運用にすると、忙しい日に必ず重複します。

カードに書く。 会員番号は、渡すカードに手書きでも印刷でも必ず入れます。番号がないと、来店したときに氏名で探すことになり、同姓同名や旧姓で詰まります。

電話番号を鍵にしない。 電話番号を会員の識別に使うと、番号が変わったときに履歴がつながらなくなります。検索の手がかりとしては便利ですが、識別の鍵は番号にします。

レジでの入力を短くする工夫

エクセルの台帳がうまく回らない最大の理由は、レジでの入力に時間がかかることです。短くする手当てを挙げます。

入力用の行を最上部に置く。 来店の記録のシートで、いちばん下に行を足していく形にすると、毎回スクロールが要ります。新しい行を上に挿入する形にすると、開いた瞬間に入力できます。

日付を自動で入れる。 日付の欄に毎回打ち込む形にすると、それだけで数秒かかります。ショートカットキーで当日の日付が入る操作を覚えるか、日付の列を空欄にしておいて、その日の分をまとめて一括で埋める形にします。

会員番号だけで氏名が出る形にする。 VLOOKUPで氏名が自動で出るようにしておけば、レジで打つのは会員番号と金額の2つで済みます。打つ項目が2つなら、10秒で終わります。

その日の来店を紙に書いて、夜にまとめて入れる。 入力の速さを追いかけるより、レジでは紙に会員番号と金額だけを書いて、閉店後に5分でまとめて入れる形のほうが現実的な店もあります。ただし紙をなくすと復元できないので、紙は必ず同じ場所に置く運用にします。

月末に合わせる作業を作らない。 レジの売上と台帳のポイントを月末に突き合わせる運用にすると、その作業が重くて続きません。日々の入力が正しいことを前提にして、月末は集計を見るだけにします。

有効期限と特典の書き方で気をつけること

ポイントや特典は、お客さまとの約束です。書き方が曖昧だと、あとで説明する場面が来ます。

カードの裏面に必ず書く3つ。 有効期限、特典の条件、再発行の扱い。この3つが書いてあれば、店頭でのやり取りはほとんど済みます。

条件は数字で書く。 「たくさんご利用いただいたお客さまに」ではなく、「スタンプ10個で1回無料」と書きます。数字で書くと、お客さまが自分で進捗を数えられます。

変更する場合の書き方。 「内容は変更する場合があります」と一行入れておきます。入れていない状態で還元率を下げると、説明が難しくなります。ただし、この一行があるから何でも変えてよいという話ではありません。すでに貯まっているポイントの扱いは、変更の前に決めます。

景品や値引きの表示。 特典の見せ方によっては、表示のルールに関わる場合があります。「通常5,000円が無料」のような書き方をするときは、通常の価格が実際に販売されている価格であることが前提になります。判断に迷う内容は、消費者庁（https://www.caa.go.jp/）の案内を確かめるか、専門家に相談してください。記事の範囲で言えるのは、書いた条件はそのまま守れる形にしておくべきだという実務の話までです。

会計の扱い。 値引きとして処理するか、無償の提供として処理するかで、記録の付け方が変わります。税務の扱いについては、国税庁（https://www.nta.go.jp/）の案内や顧問の税理士に確かめてください。

紙のカードを渡すときの声かけを決める

作ったカードが使われるかは、渡すときの一言で決まります。渡し方を決めていない店では、忙しい日にカードが引き出しの中に残ります。

渡す言葉を1文に決める。 「次回から使えるカードです。10回で1回無料になります」。これだけで足ります。長い説明は聞かれません。スタッフ全員が同じ1文を言える状態にします。

渡すタイミングを決める。 会計の直前か直後か。直前に渡すと、その日の来店を1個目として押せます。1個目が押された状態で渡すと、始まっている感覚が出るので、持ち帰られる率が上がります。

財布に入れてもらう。 渡すときに「財布に入れておいてください」と添えます。袋に入れて渡すと、袋ごと捨てられます。

忘れたときの扱いを決める。 持参を忘れた人に、台帳で確かめて押す運用にするか、次回にまとめて押すか。決めておけば、その場で判断が揺れません。台帳があるなら、忘れても対応できるのが強みです。

渡した記録を残す。 誰に渡したかを台帳に印を付けておきます。渡していない常連がいることに気づけます。

月に1回、数字を見て続けるかを決める

作ったあと、効いているかを確かめます。見る項目は4つです。

発行した枚数。 何人に渡したかを数えます。渡していないカードは効きません。渡す声かけができているかが、ここで分かります。

2回目の来店率。 カードを渡した人のうち、2回目に来た人の割合を出します。カードを渡していない時期と比べられるなら、比べます。この数字が動いていないなら、カードは装飾になっています。

特典まで到達した人数。 何人が達成したかを数えます。1人も達成していないなら、枠の数が多すぎるか、有効期限が短すぎます。

入力の遅れ。 来店の記録が、当日のうちに入っているかを見ます。遅れが常態化しているなら、入力そのものが現場に合っていません。

この4つを3か月続けて記録すると、続けるか、形を変えるかが決まります。カードを作ることは目的ではなく、2回目の来店を増やすことが目的です。数字が動いていない形を、手間をかけて続ける理由はありません。

予約から来店の履歴が残る形なら、台帳は作らなくてよい

最後に、別の考え方を挙げます。ポイントカードの台帳が必要になるのは、来店の履歴が記録されていないからです。予約を受ける時点で履歴が残る仕組みなら、台帳を別に作る作業そのものが要りません。

日時を決めて来店する業種、たとえばサロン、整体、レッスン、クリニックでは、予約の記録がそのまま来店の記録になります。誰が何回来たか、前回はいつだったか、何を選んだかが予約の側に残るので、エクセルに転記する作業が消えます。会員登録とマイページがある形なら、お客さまの側も自分の来店の履歴を見られます。何が記録されるかはできることにまとまっています。

来店後の御礼のメールを自動で送れる形なら、「あと1回で特典です」という連絡も、抽出して手で送る作業なしに出せます。ここが、エクセルの台帳といちばん差が出る部分です。決済が必須でない業態に合わせて、予約の受付から次の来店までを一続きにしている形を選べば、現金や振込で完結している店でも、来店の履歴とその後の連絡がつながります。

費用は、無料のまま使える範囲があるかどうかで判断が変わります。予約件数の上限を置いた無料プランを用意している製品が複数あり、月50件を境目にしている形が主流です。同じ使い方でそろえた比較を他社との比較に、プランごとの上限と金額を料金に載せてあります。業種ごとの受付の組み方は業種ごとの使い方に分かれていて、実際の画面をログインなしで触れるデモを見るから確かめられます。細かい疑問はよくある質問に並んでいます。

紙のカードをやめる必要はありません。お客さまに渡す紙は残したまま、数える側だけを予約の記録に寄せる形が、いちばん移りやすい順序です。エクセルで作った版下はそのまま使えます。

Q1. エクセルでポイントカードの版下を作るとき、サイズはどうしますか？

財布に入る前提なら名刺サイズの91×55mmが基本です。二つ折りでスタンプ欄を広く取るなら、開いた状態で182×55mmになります。印刷会社の規格もこのサイズを標準にしているため、あとから外注する際に作り直しが不要になります。スタンプ欄は使うスタンプの直径より2mmほど大きく取ります。

Q2. ポイントの残高は手で書き換えてよいですか？

避けたほうがよい形です。来店の記録を1行ずつ足していき、残高は関数で計算する形にします。SUMIFで付与と使用の合計をそれぞれ出して引き算すれば残高になります。手で残高を書き換えると、書き換え漏れと二重計上が必ず起きて、あとから正しい数字に戻せません。

Q3. 何枚からなら印刷会社に出したほうが安くなりますか？

おおむね100枚が境目です。名刺サイズの片面カラーで100部1,577円から、二つ折りで100部3,929円からという価格が公開されています。手元のプリンターで刷る場合は用紙代とインク代だけで済みますが、枚数が増えると裁断や折りの手間が費用を超えます。

Q4. エクセルからほかの仕組みに移るべきなのはどんなときですか？

スタッフが同時に入力したい、レジでの入力が後回しになる、お客さまが自分の残高を見たい、貯まったことを自動で知らせたい。この4つのどれかで実際に困っているときです。困っていなければエクセルのままで回ります。日時を決めて来店する業種なら、予約の記録をそのまま来店の履歴として使う形も選べます。