こちらの記事は次のようなことを知りたいという方に向けて書いています。
- 複雑な計算が必要なExcelを作るのは大変だけど、どうやって作っていけばいいの？
- Excelで表を作るときに考えておいたほうがいいことって何？
- Excelで集計表を効率的に作る方法を教えて！
楽天ポイント集計用Excelとは？
楽天ポイント集計用Excelとは、楽天ポイント集計をするために私が作成したExcel集計表のことです。それぞれの項目にデータを入れていくと、獲得できる楽天ポイントがわかるという、個人的には利用頻度のかなり高いツールです。
楽天ポイントの概要ならびに集計用Excelのダウンロード、使用方法の説明についてはこちらの記事に記載しておりますので、よろしければご参照ください。
Excelで集計表を作成するために考えるべきこと
楽天ポイント集計用Excelで具体的なExcel作成手順を説明していく前に、Excelで集計表を作成するにあたって考えておくべきことを説明します。
この点を理解しておかなければ、作成した後の修正で大幅な手戻りがあったり、場合によっては初めから作成したほうが速い、といった事態になり兼ねません。
もちろん、そうした試行錯誤を繰り返して自分のExcel作成スキルを向上していくという方法もありますが、前もって準備しておけば時間も短縮できますし、参考にしてもらえるとありがたいです。
要件の洗い出しをして共通で使える部分を把握する
まずは、要件の洗い出しをして共通で使える部分を把握しておくことが重要です。これは、システムの設計でも同様に重要な点なのですが、この共通部分を探すことを先におこなっていないと、後で確認したときに冗長、つまり同じような要件が別々にExcelに反映されてしまっていて、それぞれにデータを入力しなければならないケースが起こりえます。
こうした冗長なデータ構成は、後のメンテナンスも面倒になりますので、できる限りまとめておいたほうがいいです。そのため、前もって共通する内容をまとめておくことをオススメします。
後から要件を追加する可能性を考えておく
Excelで表を作成していくときに、後から後からどんどん追加が必要になって、行や列を増やしていった結果、数式や集計のミスが多発したことはありませんか？
もちろん初めから集計表に必要な内容がすべて把握できているわけではなく、一度作ってみたあとで、追加で入れる必要のある項目が出てくるのが通常でしょうから、ある程度後から追加するのは想定しておくべきではあります。
ですが、Excelを作るときに最初から必要な条件を洗い出しておいて、そこから作成し始めることを考えておくと、後から修正が必要になったときに作業が少なくて済むというメリットがあります。
Excelではすべて自動化を目指さない
昨今デジタルトランスフォーメーション、いわゆるDXという言葉がはやっており、IoTやAI化などの技術が進んでいます。中でもRPAを活用した自動化やAIによるビッグデータ解析など、夢のような技術が実現していっています。
ですが、高度な自動化は内容の複雑さとのトレードオフとなります。ある程度の期間使用していくようなExcel集計表であれば、変更が必要になることも多いでしょうし、あまりに作り込んでしまうと、メンテナンスが煩雑になってしまいますので、割り切って一定の自動化でとどめておくことが賢明です。
楽天ポイント集計用Excelの作成手順
それでは、ここから楽天ポイント集計用Excelを題材にして、Excel作成手順を追って説明していきます。
システムを設計していくような場合、下記のような手順で実施していくことが一般的です。
ですが、Excelで集計表を作成していく場合、下記の手順くらいでいいかと思います。一般的なシステム設計を端折るようなイメージですね。
手順について簡単にまとめると下記のようになります。
- 要件定義・・・Excelで実現したい内容を羅列して、共通する部分をまとめる
- 実装・・・共通する部分を中心にExcelの数式を組んで表を作成していく
- テスト・・・仮のデータを入れてみながら結果を確認する
この手順に従って、Excelを作っていきましょう。
要件定義①要件を洗い出す
まずは楽天ポイントを集計するために必要となる要件を洗い出します。要件というのは、ここでは
楽天ポイントをExcelで集計するために必要な条件
と理解してもらえればいいと思います。
楽天ポイントというのは貯まる条件がさまざまあって複雑であり、すべての条件を洗い出すことは不可能と言ってもいいくらい難しいのですが、大きく分ければ下記のような条件に分けることができます。
- 買い物金額に対して値引きクーポンなどの値引きがあったり、送料がかかることがある
- 買い物金額の税込、税抜き、税抜きでは100円ごとと200円ごとにポイントが加算されることがある
- お買い物マラソンでは、1店舗1,000円以上の購入ごとに店舗数に応じてポイントが付与される倍率が変わる（期限。上限あり）
- SPUでは、満たした条件のポイントが加算される（上限あり）
- 毎月決まった日や特定の店舗のみに適用されるポイント加算がある（上限あり）
- 特定の店舗、特定の商品についてのみ適用されるポイント加算がある（上限あり）
- 倍率は各キャンペーン・条件で異なる
- 楽天会員種別、楽天モバイル契約で条件が変わる
他にもいろいろあるのですが、システム設計をおこなうような緻密までは必要でないため、とりあえずはこれくらいでいいと思います。あまりに要件をたくさん出しすぎても、逆に作業開始が遅くなってしまいますので、ある程度で止めておくことも重要です。
要件定義②要件を共通するものでまとめる
要件を洗い出したら次に要件を共通するものでまとめていきます。
- ポイントの上限がある
- 特定の日付、特定の期間が対象になる
- それぞれ倍率が変わる必要がある
- 楽天会員種別、楽天モバイルで条件が変わる
こちらもざっくりとまとめています。この４点くらいは共通でまとめたほうがよさそうです。「上限」や「日付」や「倍率」や「楽天会員種別」など、キーワードがいくつか出てきていますね。
実装①Excel集計表の構成を決める
楽天ポイント集計用Excelについて、要件定義でまとめた要件をExcelに実装していきます。まずは、把握した共通する要件を中心に、Excelの構成を決めていきます。
このときに、どこからどこまでの条件が、共通する項目に影響するのかを意識しておきましょう。
つまり、
- ポイントの上限がある→各条件を列（横）に表示して、それぞれ上限を設定できるようにする
- 特定の日付、特定の期間が対象になる→日付を行（縦）に表示できるようにして、列（横）に期間を入れられるようにすることで対象日付内か判定できるようにする
- それぞれ倍率が変わる必要がある→倍率を列（横）に表示する
- 楽天会員種別、楽天モバイルで条件が変わる→多くの条件に影響するため共通項目としてトップに配置
というように共通項目を整理して、Excel集計表のどの部分に要素を配置していくかを決めていきます。楽天ポイント集計用Excelの場合は、下記のような形ですね。
実装②Excel数式の入力
Excel集計表の大体の配置を決めることができました。これでExcelの構成ができましたので、数式を入れていきましょう。ここからがまさにExcelの肝である数式を組んでいく作業となります。
数式を組んでいく中で、必ず意識しなければいけないこととして、過度な自動化を目指さない、ということです。
自動化を目指す場合、IF文による条件分岐やAND/OR条件の組み込みなどが多数必要となってしまい、Excelの数式が非常に複雑になってしまいます。Excelの数式では、数式の中にすべての分岐や条件を反映させなければなりません。これは後で数式を修正するときに面倒になりますので、やり過ぎは禁物です。
分岐や条件を数式に複雑に組み込んでしまうくらいならば、その条件や分岐の情報をマスタとして別のシートに作成して、数式をシンプルに抑えることも重要です。
なお、楽天ポイント集計用Excelで実際にどのような数式を使用しているかは、説明が長くなってしまうこともあり、別の記事で説明します。
テストデータを入れて修正していく
Excel集計表の作成においては、このテストがとても重要です。実装の部分がプログラム設計のように細かく作り上げているわけではないため、Excel数式の作成がうまくできていなかったり、ミスを犯している可能性があります。
テストにおいては閾値（しきいち）といって、この条件を超えたら結果が変わる、といった点を中心に確認していくことが重要です。楽天ポイント集計用Excelの例で言えば、
- お買い物マラソンで１０店舗以上購入した場合に倍率が９倍で止まるか
- 上限ポイントがあるもので上限でポイント加算が止まるか
- 特定の日付でポイントの計算がされているか
- 楽天会員種別、楽天モバイルの条件がきちんと反映されているか
などといった点を中心に確認していきます。
ですが最もいい方法は、実際に楽天で購入する際にデータを入れていくことです。結局のところ、Excelの集計表の場合は実際に使ってみる、というのが一番です。必要に応じて修正を加えていくことが重要です。
ただ、楽天ポイントの場合、
- ポイントを獲得できるのが翌日～数カ月後と幅広い
- 条件が多く、すべて明細では別々に記載されており膨大なデータになる
- お買い物マラソンなどのポイント還元はまとめて明細に表示される
といった点から、本当に集計が正しいか検証することが難しいのが問題です。
どうしても楽天ポイントの明細まで確認したい！という場合にはこちらの記事でPythonで楽天ポイント明細をスクレイピングする方法をご紹介していますので、試してみてください。
楽天ポイントの場合、制度変更がしょっちゅうおこなわれますので、Excelの変更もその都度していかなければならないのですが、こればかりは楽天が決めることですので仕方がありませんね。
まとめ
以上、Excel集計において考えておくべきことをまとめてみましたがいかがでしたでしょうか。ここでまとめておきます。
- Excelで集計表を作成するために考えるべきことは、「要件の洗い出しをして共通で使える部分を把握する」「後から要件を追加する可能性を考えておく」「Excelではすべて自動化を目指さない」
- Excel集計用の作成手順は、「要件定義で要件を洗い出して要件を共通するものでまとめる」→「実装でExcel集計表の構成を決めて数式を入力していく」→「テストデータを入れて修正していく」
- 楽天ポイントはしょっちゅう変更があるのでExcelで集計するのは大変
楽天ポイントをすべて把握するのは難しいのですが、マスターすればポイントの恩恵で日常生活の助けになることもあると思いますので、Excelを活用してみるのもいいかもしれませんね。
以上、お読みいただきましてありがとうございました。
