Chiquilin Site■07.1.26_Excel:金種の計算を行う

その1:曲者は二千円札
その2:二千円札を計算に入れない場合
その3:二千円札を計算に入れる場合
ChiquilinSite

その1:曲者は二千円札

金種計算表
今時 金種計算ってそれほど必要なのかどうか分かりませんが 珠に質問で見ますね。金種計算とは ある金額に対して紙幣と硬貨を何枚ずつ用意すればいいかを計算することです。でこの金種計算で一番厄介なのが 小渕さんの忘れ形見「二千円札」です。これを入れるかいれないかで 計算に影響が出てきます。
ページの一番上へ

その2:二千円札を計算に入れない場合 

というのも 金種計算の基本は「額の大きいお札順に必要枚数を決めていくこと」ですが その際「『ひとつ大きな額の紙幣/硬貨で割り切れなかった金額』が残りの金額」という暗黙のルールが 二千円札登場まではありました。よって計算は
■C3セル
 =INT(B3/C$2)
■D3セル
 =INT(MOD($B3,C$2)/D$2)
 K3セルまでオートフィルコピー
 任意行までオートフィルコピー
でよかった訳です。
ところが二千円札が入るとこの計算では正しい結果にならない場合が出てきます。例えば3行目にある「539,485」円の場合です。
二千円札が入るとうまくいかない事例
本来は「五千円札1枚」「二千円札2枚」で「千円札0枚」になるべきところですが「1」が返ってしまいます。この計算結果自体が間違っている訳ではありません。しかし それまで暗黙の了解だった「ひとつ大きな紙幣/硬貨はそれよりひとつ小さな紙幣/硬貨で必ず割り切れる」という大前提が崩れてしまった為に計算結果が合わなくなってしまったのです。二千円札では五千円札を割り切れません。その為「元の金額÷5000」で その余りを計算していたのでは答えが合わなくなる場合が出てきます。二千円札を入れて計算する場合は 計算方法そのものを見直す必要があります。
ページの一番上へ

その3:二千円札を計算に入れる場合

金種計算表(二千円あり)
■C3セル
 =INT(B3/C$2)
■D3セル
 =INT(($B3-SUMPRODUCT($B$2:C$2,$B3:C3))/D$2)
 L3セルまでオートフィルコピー
 任意行までオートフィルコピー
この計算式なら問題なく計算できます。さっきと何が違うかと云うと 今度は 元の金額から それまでに計算した紙幣/硬貨の金額を合計して引いていることです。これなら計算結果がずれることはありません。2行目と3行目から それまでの金額を計算するのに SUMPRODUCT関数を使っています。もちろんですが 二千円札がなくてもちゃんと計算します。
あと C列の数式だけ 他と違うのが嫌だという人用ですが こんな方法があります。
B2セルのセルの書式設定で[表示形式]を[ユーザー設定]で「"金額"」として[OK]
B2セルに「0」と入力

これをしておけば
■C3セル
 =INT(($B3-SUMPRODUCT($B$2:B$2,$B3:B3))/C$2)
 L3セルまでオートフィルコピー
 任意行までオートフィルコピー
と C列からL列まで全て同じ計算式でできます。
→参考ファイル(028.xls)
ページの一番上へ
ChiquilinSite トップページへ  Copyright(C)2005 Chiquilin_site. All Rights Reserved.