いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)一般化した金種計算の例

 Excelで、貨幣の種類がどのようなものであっても1つの同じ式で金種計算を行う計算の例です。
 式が複雑になり、決して実用的とは言えませんが参考まで。

  • REDUCE/LAMBDA関数を活用して、どのような金種にも対応できる金種計算を行うことができます。

手順

 B3:B11の範囲には金種計算の対象となる金額(合計金額)を、C2:L2の範囲には2,000円を含む日本のすべての種類の金種(貨幣)を入力しています(左の金種ほど高額になるようにソートされている必要があります)。
 これに対しC3セルに式を入力して、各金種の必要枚数を出力させています。
 2,000円を考慮した金種計算は少々厄介なものになりますが、正しく計算できているのがわかります。

 C3セル

=DROP(DROP(
REDUCE(HSTACK("",B3),C2:L2,
LAMBDA(a,b,HSTACK(DROP(a,,-1),INT(TAKE(a,,-1)/b),MOD(TAKE(a,,-1),b)))
),,1),,-1)

 基本的には金額を10000で割った商(小数切捨。以下同様)を求め、その余りを5000で割った商を求め……ということを繰り返しています。
 具体的な式の内容ですが、REDUCE関数とLAMBDA関数を使い、「HSTACK("",B3)」をa(の初期配列)、「C2:L2」をbと名付けています。
 bのそれぞれの値について、「aの最後のセルを削除し、『aの最後のセルにあった値をbで割った商』のセルを追加し、さらに『aの最後のセルにあった値をbで割った余り』のセルを追加する」という操作を繰り返しています。
 具体的に37592という金額に対して配列aは次のように変化していきます。

 {"",37592}
 {"",3,7592}
 {"",3,1,2592}
 {"",3,1,1,592}
 {"",3,1,1,0,592}
 ・
 ・
 {"",3,1,1,0,1,0,1,4,0,2,0}

 最初の空白セル(初回のDROPでエラーにならないためのセル)と最後の0(「1」の貨幣に対する余り)は結果から除く必要がありますので、二重のDROP関数で削除して完了となります。
 REDUCE関数内だけみれば案外複雑でもないのですが、それでも到底スマートとはいえないのが痛いところです。


 なお、対象となる金額である「B3」を「B3:B11」に変更するだけで、すべての金額に対する金種計算ができます。

 C3セル

=DROP(DROP(
REDUCE(HSTACK("",B3:B11),C2:L2,
LAMBDA(a,b,HSTACK(DROP(a,,-1),INT(TAKE(a,,-1)/b),MOD(TAKE(a,,-1),b)))
),,1),,-1)

 「C2:L2」に対する絶対参照を行う必要はなく、まさにスピルのメリットといえます。


 さらに金種の内容を変更した結果です。
 このように、どれほど珍妙な通貨体系であっても正しく計算されます(もちろん、金種の数が増減した場合は参照範囲を変更する必要があります)。

 最後に備考ですが、解が存在しないケース(「1」の貨幣が存在しない場合に起こります)では、金種計算の結果と金額が一致しない結果になります。もし現実に「1」の貨幣が存在しない場合、それでも金種欄には「1」の貨幣を設け、その必要枚数が0でなければ解が存在しないとみなすのがよいでしょう。