Excel 関数のコーディング事例をご紹介しています。
VBAは使用していないのですが、関数をネスト(入れ子)にする事は多用していますので判読性がし難い事と存じますが、なるべく分かり易くお伝えできればと考えています。
今回のテーマは「マイ・ペイすリボ払い試算表」になります。
今までとは少し趣を変えて「関数だけでアプリケーションを考えたらどのようなものになるか?」についてご紹介したく存じます。
Excel 2016バージョン2106(ビルド 14131.20320)を使用しています。
なぜマイ・ペイすリボ払い試算表なのか?
つまらない理由で誠に恐縮ですが、会社で使用している業務用クレジットカードの年会費の優遇条件が2021年2月に改定された事がきっかけです。
この改定された事とリボ払い試算表との結び付きなのですが、これまでも年会費の優遇を受けるためには支払方法で「マイ・ペイすリボ」を選択する必要はありますが、改定前は「カードを年1回以上利用」するだけで良かったのが、改定後は「年1回以上リボ払い手数料の支払い」がある事に条件に変わりました。
もう少し具体的にご説明しますと、今までは「マイ・ペイすリボ」で1回のリボ支払金額の設定を高くしておけば支払時は1回払いとなり(つまりリボ払いにならず)、リボ払い手数料は発生せず、しかも年会費は払わなくても良かったのです。
それが改定後は少なくても1回はリボ払いをしなくてはならない事になり、そうなると「リボ払い手数料がどのくらいになるのか?」を試算したくなったためです。
ただし、わざわざExcelでアブリケーションを作らなくてもクレジットカードのWebサイトにログインをすればシミュレーションするページが用意されている事と存じます。
そうなると「そっちを使った方が正確ではないか?」と思われるかもしれませんが、シミュレーションのページを見てみると、試算できるのはリアルな支払い残高に対してだけであり、気軽に金額を変えて試算できなかったりします。
そこで今回は正確性を目指すのではなく、気軽に動かせるExcel関数アプリケーションの作成例してご紹介して参ります。
実装する要件をまとめる
試算表と言ってもいろいろな形が考えられますがまずは外観といいますか見え方を決める必要があります。
ただし外観と一言に言っても、例えば文字の色・大きさとか罫線の色・付け方や配置の仕方などの話もありますが、ここでは機能として必要な外観上の要件を列挙します。
機能面での外観の要件
要件を列挙して行きますが、文中の青色下線に「[数字]」している箇所は後段の「要件をExcelの数式にする際のポイント」の章で仕様をご説明しています。
- 同一締年月の利用日と利用額を5行入力できる。
- 利用日が同一締め年月で無い時は見た目で判断できるようにする。
- 関数のエラー表示の他に条件付き書式も使用する。
- 利用日が同一締め年月で無い時は見た目で判断できるようにする。
- 「締め日と支払日」はテーブル形式で設定することができる。
- このテーブルをもとにExcel入力規則でプルダウンメニューを設定する[1]。
- 実質年率は入力で変更できる。
- リボ払いの回数は最大100回まで対応できるようにする。
- リボ払い支払額は回ごとに設定でき、かつ「まとめ入力」できるようにする。
- リボ支払い日が土日祝日の場合は翌営業日が表示されるようにする[2]。
- リボ払い手数料の計算の中で営業日を使用する事はありません。「実際にお金が支払口座から引落される日はいつになるか?」を表す参考情報になります。
- 回ごとの支払い残高・引落額・リボ払い手数料とリボ払い手数料の合計は表示する。
- 「入力するセル」と「関数を設定したセル」にはそれぞれ別の背景色を付ける。
上記以外にも、「日付入力」や「パーセント入力」さらには「金額上限」で入力補助機能が必要になる場合もある事と存じますが、今回は対象外にしていますのでお含み置きいただければ幸いです。
マイ・ペイすリボ払い手数料の計算方法
マイ・ペイすリボのお支払い方法やお支払い例としての計算式は下記三井住友カードのサイトに記載されています。
今回は試算ではありますが、できるかぎり実際の支払い明細に添う形での計算を目指したいと思います。
そうなると次の点で注意が必要になります。
- 閏年対応するためには、計算対象期間が月をまたぐ時は月を分けて計算する必要があります。
- 月をまたいだ時に閏年に入る時は、またぐ前は365日[3]、またいだ後は366日で日割り計算
- 月をまたいだ時に閏年から出る時は、またぐ前は366日[3]、またいだ後は365日で日割り計算
- リボ支払手数料の計算は「①支払日の翌日から締め日までの日数」と「②締め日の翌日から支払日までの日数」に分けて計算する必要があります。
- ①は次の支払日[4]にリボ払い手数料として引落される。
- ②はリボ払い手数料としては金額は計算されるが、次の締め日[4]で確定する。
確定した後、次の支払い日にリボ払い手数料として引落される。
※つまり①が次月の支払日に引落されるのに対して、②は次々月の支払日に引落される。
「15日締め翌月10日支払 設定したリボ支払金額15,000円 実質年率15%」での【例】
閏年の場合をご説明するめに過去日付の利用日にしていますが11月締め月で25,000円の利用額合計があるケースをもとにマイ・ペイすリボ払い手数料の計算方法のポイントをご説明いたします。なお以降の月でのクレジット利用は無いものとします。
| イベント | 日付 | 金額 | 備考 |
| 利用日 | 2019/10/16 | 10,000 | クレジット利用額 |
| 2019/11/15 | 15,000 | 〃 | |
| 11月締め日 | 2019/11/15 | 25,000 ① | 利用額合計 |
利用後初めての支払になる12月支払日ではまだリボ払い手数料の発生はありません。
| イベント | 日付 | 支払金額 | リボ払い手数料 | 利用残高 | 備考 |
| 12月支払日 | 2019/12/10 | 15,000 ② | 0 | 10,000 | (=①‐②) |
初回支払の翌日から利用残高10,000円に対して、まずは12/11日(12月支払日翌日)～15日(12月締め日)の5日間のリボ払い手数料が発生します。
なぜ5日間だけの期間にしているか?というと12月締め日で一旦締めて1月支払日に支払う事になるからです。
計算式は下記になります。
10,000円×15%×5日間÷365日=20.54794521 実際の手数料は小数点以下切捨てになります。
このリボ払い手数料は1月支払日にリボ支払金額とは別に支払うのがマイ・ペイすリボ払いの規定です。
そしてリボ払い手数料は15日を超えた後も続けて発生します。
残りの期間である12/16日～31日(末日)[5]の16日間と1/1日～1/10日の10日間の計26日間の手数料の計算式は下記になります。
10,000円×15%×16日間÷365日(通常年)=65.75342466
10,000円×15%×10日間÷366日(閏年)=40.98360656
両者を足した結果の106.7370312を小数点以下切捨てにした106円を2月支払日に支払うことになります。
| イベント | 日付 | 支払金額 | リボ払い手数料 | 利用残高 | 備考 |
| 1月支払日 | 2020/1/10 | 10,000 | 20 | 0 | リボ支払金額15,000円に対して利用残高は 10,000円のため1月の支払で完済 |
| 2月支払日 | 2020/2/10 | 0 | 106 | 0 | 最後にリボ払い手数料だけ支払 |
なお数式が複雑になる場合はあえて数式を分けて、つまりは複数のセルにそれぞれ数式を入力して計算することにしますが、リボ払い手数料を計算するために必要なポイントは以上ですべてになります。
要件をExcelの数式にする際のポイント
使用するすべての数式を対象にすると分量が多くなってしまうため、ポイントに絞ってご説明いたします。
前章で青色のラインマーカーを引いた箇所がポイントであると考え、そこに[1]から[5]までのルビを振ってありますので、その番号に沿ってご説明いたします。
[1]締め日からテーブルにセットした支払日を取り出す
入力規則を使ってプルダウンメニューに設定してあるセルで締め日を選択した時に、テーブルにセットしてある締め日から支払日を取り出すにはVLOOKUP関数を使用します。
| ◢ | A | B |
| 1 | 締日 | 支払日 |
| 2 | 15 | =IF(A2=””,””,VLOOKUP(A2,payment,2)) |
| ◢ | A | B |
| 1 | 締め日 | 支払日 |
| 2 | 15 | 10 |
| 3 | 末 | 25 |
右側の表シートでA2:B3の範囲を「payment」という「名前」が付けてあり「ブック」で参照できるものとします。
この場合は左側B2セルの数式で支払日を取り出すことができます。
[2]翌営業日を求める
営業日を求めるためには銀行の休日テーブルを用意する必要があります。
この件につきましては弊社『EXCEL WORKDAY関数とWEBSERVICE関数等で「今日が営業日である事」を判定する』で詳しくご紹介していますので、お手すきの時にご参照いただければ幸いです。
下記A2セルの数式は、A1セル(=今日)が営業日であれば今日の日付を、非営業日であれば翌営業日を返します。
| ◢ | A |
| 1 | =TODAY() |
| 2 | =IF(WORKDAY(WORKDAY(A1,-1,holiday),1,holiday)=A1,A1,WORKDAY(A1,1,holiday)) |
「holiday」は祝日を1列にならべて範囲指定した「名前」になります。
[3]閏年を判定する
閏年の判定ロジックにつきましてはMicrosoft Docsの「年がうるう年かどうかを判断する方法」というページで計算方法が公開されています。
ロジックはそのままにして下記に流用いたします。A1セル(=年)が閏年ならばA2セルの数式は真になります。
| ◢ | A |
| 1 | =YEAR(TODAY()) |
| 2 | =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),True, False) |
[4]次の支払日、次の締め日を求める
Excelには月を加算・減算するEDATE関数がありますが、支払日や締め日のように日にちが定められている場合はEDATE関数よりもDATE関数で月を加算・減算しながら日にちも一緒に求めた方が効率的です。
15日締めの10日支払とすると、下記のA2セルは次月の支払日、A3セルは次月の締め日になります。
| ◢ | A |
| 1 | =TODAY() |
| 2 | =DATE(YEAR(A1),MONTH(A1)+1,10) |
| 3 | =DATE(YEAR(A1),MONTH(A1)+1,15) |
[5]月末日を求める
いろいろ方法はありますが、EOMONTH関数を使うことにします。
A1セルに年、B1セルに月がセットされている時、月末日はC1セルの数式で求めます。
| ◢ | A | B | C |
| 1 | =YEAR(TODAY()) | =MONTH(TODAY()) | =EOMONTH(DATE(A1,B1,1)) |
実装したExcel関数アプリケーション
今回作成したのは「リボ払い試算表」「支払日テーブル」「休日テーブル」の3つのシートを含む、リボ払い試算表.xlsxファイルになります。
- リボ払い試算表
- 前章で使用した「15日締め翌月10日支払 設定したリボ支払金額15,000円 実質年率15%」での【例】が入力してありますが、適宜更新入力できます。
- リボ払い手数料の計算は1回から100回までできるように数式はセットしてあります。
- 支払日テーブル
- 15日締めと末日締めのデータがセットしてあります。
- 休日テーブル
- 2019年から2045年までの銀行の休日がセットしてありますが、国民の祝日が法律で変更された時は個別に修正していただく必要がありますのでお含み置きください。
- 休日テーブルにつきましては「Excelで使う休日テーブルの書式」で機能のご紹介をしていますのでご参照いただければ幸いです。
各シートの画面ショットは下記になります。
リボ払い試算表 
支払日テーブル 
休日テーブル
各シート共通で背景色の意味合いは下記になります。
| 背景色 | 用法 |
|---|---|
| ■ | 入力・更新可能なエリア(ロック解除) |
| ■ | アプリケーションで使用する数式がセットされている(ロック) |
| ■ | 画面表示するための数式がセットされている(ロック) |
| □■ | 項目名・コメントなどを表示するエリア(ロック) |
実際に動かすことができるExcelブックをダウンロードできるようにしています。
| 解凍時ファイル名 | 解凍時サイズ | ZIPファイル無料ダウンロード |
|---|---|---|
| リボ払い試算表.xlsx | 133KB |
リボ払い手数料の計算枠は2回から100回までコピー貼付け
リボ払い試算表では1回目から100回目までの計算枠は見た目は同じに設定しています。
ただし1回目はリボ払い手数料の発生が無いために、2回目以降と数式が異なる箇所があります。
それはH13セル(引落総額を求める式)、G14セル(リボ払い手数料のコメント)、H15セル(リボ払い手数料を求める式)の3ヶ所になります。
2回目以降はすべて同じ数式になるため、「B17:I22のセル範囲をコピーして貼り付け」する事を繰り返して100回分の計算枠を設定することができます。
なお1回目と2回目の違いを吸収して一つの式にする事は可能ですが、数式が複雑になり、しかもその複雑な数式が100回以上コピーさける事になるので2つに分けた形で実装しています。
利用日の誤入力に対する処置
例えば締め年月が違う利用日を一緒に入力してしまった時、何も考慮がされていないと間違ったクレジット払い合計に対してリボ払い手数料を計算することになり、折角のアプリケーションが台無しになってしまいます。
そこで間違えがある事を入力している人に気付いてもらう必要がありますが、マクロ(VBA)を使用していない場合ポップアップウインドウを表示して強制的に気付きを与えるような動き方は実装できません。
そうなると取れる方法としては下記のいづれか、もしくは両方になる認識です。
- 「条件付き書式」の数式で誤入力と判定し、該当セルの書式を気付き易いものに変更する。
- 最終目的の数式が「#VALUE!」エラーになるようにする。
- 例えば今回であればリボ払い手数料の計算式が該当します。
これらの実装の仕方を見て行きます。
「条件付き書式」の数式
今回は「リボ払い試算表」シートのE5セルからE8セルの範囲に下記の「条件付き書式」を設定しています。
=IF(AND($B$4=$B5,$C$4=$C5),FALSE,TRUE)
【数式の説明】
No2以降のセルに入力された利用日の「締め年」と「締め月」が、No1のセルに入力された利用日の「締め年」と「締め月」等しくない時にTrueを返します。
この「条件付き書式」のためにNo2以降の利用日の入力途中では入力文字が赤太字に書式設定されますが、正しい利用日が入力されるとNo1の利用日の書式と同じになります。
No1の利用日が正しい事を前提にしたロジックになりますので、コメントでNo1から明細入力する注意書きを掲載しています。
なおNo1の利用日を入力していないと数式は「#VALUE!」エラーとなるので、気付いていただけるものと思います。
数式を「#VALUE!」エラーにする
締め年月が違う利用日を一緒に入力してしまった時、「入力された利用日の締め年・締め月が同一か?」を判定するために、B9セルとC9セルに次のような数式を入力してあります。
=IF(COUNT(B4:B8)=0,"",IF(COUNTIF(B4:B8,B4)=COUNT(B4:B8),B4,"誤")) =IF(COUNT(C4:C8)=0,"",IF(COUNTIF(C4:C8,C4)=COUNT(C4:C8),C4,"誤"))
両者の違いは対象になるセルの違いだけで数式の形としては一緒で、利用日が同一でない場合は日付ではなく「誤」というテキスト値を返します。
解り難い数式で恐縮ですが、最初のCOUNT関数で利用日に入力があるか?を判定し、入力がある時は、No1に入力された利用日の締め年・締め月が入力されている利用日の数だけある時は、すべて同一の入力がされていると判断します。
これを使って数式を「#VALUE!」エラーにする方法はいろいろありますが、リボ払い試算表では締め日が重要な要素になっており、その中でも最初の利用額合計が決まる締め日が正しくないと、2回目以降はコピーしている関係ですべての締め日・支払日が正しくなりません。
この事を踏まえるとE9セル(最初の締め日)に次のような数式を入力することで、締め年・締め月が「誤」になっていた時には「#VALUE!」エラーになるようにする事ができます。
=IF(OR(B9="",C9=""),"",IF($C$2="末",EOMONTH(DATE(B9,C9,1),0),DATE(B9,C9,$C$2)))
締め日は15日と末日の2パターンあるために、数式は複雑になっていますが、EOMONTH関数にしろ、DATE関数にしろ、引数にテキスト値がセットされている場合は「#VALUE!」エラーを返します。
なお最初のIF文は次の章でご説明いたします。
利用日の入力が無い時の「#VALUE!」エラーは出さないようにする
入力された利用日を起点にして以降の締め日・支払日を算出しているので、利用日が入力されていないと「#VALUE!」エラーが表示されてしまいます。
これを回避するためにはIF文で「利用日の入力が無い」場合は空文字にする」というロジックを入れています。
このロジックを入れると数式自体は長くなりますが、前章のエラーを際立たせるためには致し方ないという判断です。
そのために下記のIF文が諸所に設定されています。
=IF(OR(B9="",C9=""),"",利用日が入力されていた時の処理)
リボ払い支払額をまとめて入力する
リボ払い支払額は1回目から101回目まで6行置きに配置されているために、まとめて入力するには不便です。
そこでI9セル「表示選択」を先頭にして101回目までの範囲でフィルターを設定しています。
下記のような操作で「※リボ払い支払額」にフィルターを設定することで、まとめて入力する事ができます。
フィルターした状態で1つのリボ払い支払額をコピーして貼り付ける事も可能です。
最後に
今回「関数だけでアプリケーションを考えたらどのようなものになるか?」という趣旨でリボ払い試算表についてご説明して参りました。
Excelの関数アプリケーションは何も情報が無い状態で渡されると、全体像を把握するには地道にセットされている数式の意味を理解していく作業が必要になります。
関数アプリケーションを作った際には概要、特に「何をトリガーにしてどんな計算をしているか」だけでも書き残しておいていただければ、のちのちメンテナンスをする人には一助になることと存じます。
話が変わりますが試算した結果としてリボ支払手数料がどのくらいなるのか?につきましては『「15日締め翌月10日支払 設定したリボ支払金額15,000円 実質年率15%」での【例】』で試算した結果を見ると、引落残高10,000円を1回リボ払いした時のリボ払い手数料の合計は126円ぐらいになることが分かります。
ただし実際に上記のような設定でリボ払い手数料を調整して年会費を回避するためにはいくつか注意しなければならないポイントがある認識です。
※下記1に関する詳細は「マイ・ペイすリボ払い手数料の計算方法」の章でご紹介した「マイ・ペイすリボお支払方法」のページをご参照いただければ幸いです。
- 所定の申し込み期限までに希望のリボ支払い金額を申し込みます。
- 利用代金明細書が届いたあとからでも支払い金額を決められますが、支払い口座に指定の金融機関によっては、金額を変更できない場合があります。
- 支払い口座に指定の金融機関によって申し込み期限が異なります。
- また、引き落し日ごとに申し込み期限が異なります。
- 一度クレジット会社のサイトでシミュレーションを実施してみてください。
- 支払金額を減額した時は、必要に応じてリボ支払金額を増額しておかないと別な利用額がリボ払いの対象になってしまう恐れがあります。
- 特に毎月クレジット払いを利用される時は注意が必要です。
- 年会費が「いつ請求されるか?」のタイミングを確認しておく必要があります。
以上最後までご一読いただき誠にありがとうございました。
