金種表を作成したい−MOD・INT・TRUNC関数

Excelを使って金種表を作成するというのは、Excel中級者向けWebや参考書の定番と言っていいネタだと思います。

例えば、アルバイト料を現金で支払うような場合に、一万円札が何枚必要で、五千円札が何枚で必要で・・・といった計算が必要です。こういった支払額から、紙幣・硬貨の種類（金種）ごとの必要枚数を求める計算を金種計算と呼び、紙幣・硬貨の種類ごとの必要枚数がまとめられた表を金種表と呼んでいます。

「金種表」「金種計算」をキーワードに含めて検索していただくと、様々なサイトがヒットするわけですが、この金種計算についてネットでその方法を見つけた方からよくいただく質問と言えば、
「なぜ二千円札があるときとないときとでは計算が違うのでしょうか？」
というご質問です。

多くのWebや参考書では、金種計算を余りを求めるMOD関数と、整数を求めるINT関数かTRUNC関数を使った方法をご紹介していて、二千円札があるかどうかで計算式が異なると記述されているのですが、なぜ変える必要があるのかというご質問をいただくことがあるのです。

これは別にExcelに起因するものではなく、算数・数学的な理由からです。

端的に言えば、二千円札がない場合上位の金種額が下位のすべての金種額の倍数であるという関係が成立しているのに対し、二千円札が含まれているときにその関係が成立しないからです。

二千円札を含まないお札・硬貨の種類は以下のとおりです。
一万円札（10,000）
五千円札（5,000）
千円札（1,000）
五百円硬貨（500）
百円硬貨（100）
五十円硬貨（50）
十円硬貨（10）
五円硬貨（5）
一円硬貨（1）

10,000は、下位の5,000・1,000・500・100・50・10・5・1すべての倍数です。
5,000は、下位の1,000・500・100・50・10・5・1すべての倍数です。
1,000は、下位の500・100・50・10・5・1すべての倍数です。
このような関係がすべての金種で成立しています。

ところが二千円札が入るとこの関係が成立しなくなります。
五千円札と二千円札とでこの関係が成立していないのです。
5,000は2,000の倍数ではありません。

このため二千円札があるときとないときとで、MOD関数を利用した金種計算を紙幣によって微妙に変更する必要があります。

とりあえず、二千円札がない場合の金種計算をしっかりご理解ください。

▼サンプルファイル（002218.xls　16KByte）ダウンロード

サンプルファイルでは、「○円礼」という表示にするためにA4:A6セルに「#,##0"円札"」というユーザー定義書式、「○円硬貨」という表示にするためにA7:A12セルに「#,##0"円硬貨"」というユーザー定義書式を設定し、C4:C12に金種額と必要枚数を計算し、C3セルでC4:C12セルの合計を求めることで検算をするようにしてあります。

B4セルの「=INT(B3/A4)」は、最高額紙幣一万円札の必要枚数を計算する数式で、これだけ他とは異なっています。単純に支払総額を10,000で割った整数部分を取得すれば一万円札の必要枚数が計算できます。

二千円札がない場合、五千円札以下は統一することができ、
支払総額を一つ大きな金種額で割った余りを求めたい金種額で割った整数値
が、必要枚数になります。

それが、「=INT(MOD($B$3,A4)/A5)」という計算式で、支払総額を一つ大きな金種で割った余りを求めるのが「MOD($B$3,A4)」という部分です。

MOD関数は第1引数を第2引数で割った余りを返してくれる関数です。計算式をあとでオートフィルすることを考慮して、支払総額のB3セルを絶対参照にしています。

余りを金種額で割って整数値を取得する「=INT(余り/A5)」という数式になると、先の一万円札部分を計算する「=INT(B3/A4)」と実は同じ計算式だということが見えてきます。

この
「=INT(支払総額を一つ大きな金種額で割った余り/求めたい金種額)」
という計算式について、
「=INT(支払総額からそれまでに金種計算されている分を引いて一つ大きな金額で割った余り/求めたい金種額)」
という計算式にしないといけないと感じる方がいらっしゃるようです。

ここで上位の金種額が下位のすべての金種額の倍数となっている関係が生きてきます。

10,000は、5,000の倍数ですから、
支払総額を求めたい金種額（5,000）で割ったときの余りと
支払総額から上位の金種（10,000）で支払われた額を引いた数値を求めたい金種額（5,000）で割ったときの余りは
等しくなります。

この関係が二千円札が存在しない場合、全金種において成立しています。

ですからB5セルに入力した「=INT(MOD($B$3,A4)/A5)」という計算式をオートフィルするだけで金種表が完成することになります。

• 小数点以下を切り捨てる