现金分解电子表格：可能的有限精度？ [英] Cash breakdown spreadsheet: possible limited precision?
问题描述
我试图将一系列价值分解为现金收集货币的组成部分。
BCDEFGHIJKLMN
1工资£20.00£10.00£5.00£2.00£1.00£0.50£0.20£0.10£0.05£0.02£0.01检查
2£355.20 17 1 1 0 0 0 0 1 1 2 0£355.19
3£212.46 10 1 0 1 0 0 2 0 1 0 0£212.45
4£299.96 14 1 1 2 0 1 2 0 1 0 0£299.95
这就是我试图粗略地说：
D2 = ArrayFormula（ROUNDDOWN（（$ B2-SUMPRODUCT（$ C $ 1：C $ 1，$ C2：C2））/ D $ 1,0））pre
下一列：
D3 = ArrayFormula（ROUNDDO WN（（$ B2-SUMPRODUCT（$ C $ 1：D $ 1，$ C2：D2））/ E $ 1,0））
- 找到已分配给以前面值的总和（例如5 x£20 =£100）
- 从工资总额中减去这个数字，看看剩下多少钱分配给下一个较小的面值（例如£5） li>
- 查看下一个较小的面额将进入剩余工资的次数（例如£15.20 /£10 = 1.52）
- 到下一个整数，因为你不能有1.52英镑10的票据，但你可以有1×10英镑的票据。
我想我由于四舍五入而遇到了一些问题，但我不确定该怎么做。
例如，第2行的值为355.20英镑，而是比使用1 x 0.20英镑，使用更小的硬币（0.10英镑等），最后的0.01英镑错过了，总计不足1p。同样，第2行和第2行3也短于1p，但它们并不使用小于所需的小分类（例如2 x 10p而不是1 x 20p）。
如果表格格式化出错了，我做了一个可修改的电子表格和 hastebin copy 。
解决方案可能更简单：
= INT（MIN（$ B2，$ B2-SUMPRODUCT（$ B $ 1：B $ 1 * $ B2 ：B2））/ C $ 1）
在D2中复制并向下和向下复制。 然而，它复制你的方法太好了 - 即包括舍入误差。我担心这些可能不会受到直接解决方案的影响，因为即使是小数点后15位的浮点错误（并且这样可能不可避免地以这种方式使用SUMPRODUCT）可能会导致应该是
1到小于1。应用INT或ROUNDOWN（便于正确计算其他值），即使将.9999999999999视为0。
黑客可能会通过伪装M1值来进行抢占，比如
.00999，这是为了显示目的仍然可以显示为£0.01。它不会损害字面上计数便士的任何控制，因为ColumnM中的所有其他值都将是0或整数1。
它可能会影响交叉检查数据（ColumnN中的数据），具体取决于您如何计算这些数据（未显示），但计算方法如下：
= round（sumproduct（C $ 1：M $ 1，C2：M2），2）
并复制M1中削减的微量金额，如有需要，可以通过四舍五入加回去。
I'm trying to break a series of values down into their constituent denominations of currency for a cash collection.
B C D E F G H I J K L M N 1 wage £20.00 £10.00 £5.00 £2.00 £1.00 £0.50 £0.20 £0.10 £0.05 £0.02 £0.01 check 2 £355.20 17 1 1 0 0 0 0 1 1 2 0 £355.19 3 £212.46 10 1 0 1 0 0 2 0 1 0 0 £212.45 4 £299.96 14 1 1 2 0 1 2 0 1 0 0 £299.95This is how I'm trying to do it roughly speaking:
D2=ArrayFormula(ROUNDDOWN(($B2-SUMPRODUCT($C$1:C$1, $C2:C2))/D$1, 0))next column:
D3=ArrayFormula(ROUNDDOWN(($B2-SUMPRODUCT($C$1:D$1, $C2:D2))/E$1, 0))
- find the total sum of what has been allocated to the previous denominations (e.g. 5 x £20 = £100) so far
- subtract that from the total wage to see how much left there is to be allocated to the next, smaller denomination (e.g. £5)
- see how many times the next smaller denomination will go into this remainder of the wages (e.g. £15.20 / £10 = 1.52)
- round down this figure to the next whole number, because you can't have a 1.52 £10 notes but you can have 1 x £10 notes.
I think I've getting some problems due to rounding, but am not sure what to do about it.
For example, with the value £355.20 in row 2, rather than use a 1 x £0.20, smaller coins (£0.10 etc) are used and the final £0.01 missed, making the total short of 1p.
Similarly rows 2 and 3 are also short by 1p, but they do not use smaller demonominations than necessary (e.g. 2 x 10p instead of 1 x 20p).
In case the formatting of the tables goes awry, I've made an editable spreadsheet and a hastebin copy.
解决方案This replicates what your formulae achieve but may be simpler:
=INT(MIN($B2,$B2-SUMPRODUCT($B$1:B$1*$B2:B2))/C$1)in D2 and copied across and down.
However it replicates your approach all too well - ie including the rounding errors. I fear these may not be susceptible to a direct solution because even a floating point error in say the 15th decimal place (and such are probably inevitable with SUMPRODUCT used in this way) may edge what should be
1to a very tiny bit less than1. Applying INT or ROUNDOWN (convenient for computing the other values correctly) to that then treats even .9999999999999 as0.A hack might be to preempt that by fudging the M1 value, to say
.00999, which for display purposes could still be shown as£0.01. It won't impair any control that literally "counts pennies" as all the other values in ColumnM would either be0or integer1.It may impact the crosscheck figures (those in ColumnN), depending upon how you have calculated those (not shown), but calculated as:
=round(sumproduct(C$1:M$1,C2:M2),2)and copied down the slight amount shaved off in M1 would, where required, be added back by rounding.
这篇关于现金分解电子表格：可能的有限精度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
