楼主
rongjun |
Q： 如何根据总金额计算所需各种面值的充值卡数量?
例如：市面上有5种面值的充值卡，分别为300元、150元、100元、50元、30元，现已知总金额，如何求出各面值的充值卡数量，使得总金额的余额最小。
A： 方法一：
面值300的充值卡计算公式为：
- =INT((A2-MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}*50),10^4))/300)
面值150的充值卡计算公式为：
- =MOD(INT((A2-MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}*50),10^4))/150),2)
面值100的充值卡计算公式为：
- =INT(MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}),10^4)/2)
面值50的充值卡计算公式为：
- =MOD(MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}),10^4),2)
面值30的充值卡计算公式为：
- =INT(MOD((A2-MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}*50),10^4)),150)/30)
余额的计算公式为
- =MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30))
方法二（由chrisfang版主提供）：
面值300的充值卡计算公式为：
- =INT((A2-E2*50-F2*30-D2*100-C2*150)/300)
面值150的充值卡计算公式为：
- =MOD(INT((A2-E2*50-F2*30-D2*100)/150),2)
面值100的充值卡计算公式为：
- =RIGHT(MIN(MOD(TEXT(A2-E2*50-F2*30-100*{0;1;2},"0.00;149"),150)*10^4+{0;1;2}))
面值50的充值卡计算公式为：
- =RIGHT(MIN(MOD(TEXT(A2-50*{0;1},"0.00;29"),30)*10^4+{0;1}))
面值30的充值卡计算公式为：
- =RIGHT(MIN(MOD(TEXT(A2-30*{0;1;2;3;4},"0.00;49"),50)*10^4+{0;1;2;3;4}))
余额的计算公式为
- =A2-SUM((B2:F2*{300,150,100,50,30}))
方法三——VBA算法（由chrisfang版主提供）：
- Sub test()
- nrow = 2
- mode = InputBox("使用100元替代150元与50元的组合（1），否（2）", "模式选择", 1)
- Do While Range("A" & nrow) <> ""
- number = Range("A" & nrow).Value
- Range("i" & nrow) = "'" & fenpei(number, mode)
- nrow = nrow + 1
- Loop
- End Sub
- Function fenpei(number, mode) As String
- Dim temp(1 To 5)
- maxnum = 0
- mincount = Int(number / 30) + 1
- For i300 = 0 To Int(number / 300)
- For i150 = 0 To 1
- For i100 = 0 To 2
- For i50 = 0 To 1
- For i30 = 0 To 4
- s = i30 * 30 + i50 * 50 + i100 * 100 + i150 * 150 + i300 * 300
- scount = i30 + i50 + i100 + i150 + i300
- If s <= number And s >= maxnum Then
- If s = maxnum And scount <= mincount Then
- mincount = scount
- Else
- maxnum = s
- End If
- temp(1) = i300
- temp(2) = i150
- temp(3) = i100
- temp(4) = i50
- temp(5) = i30
- End If
- Next i30
- Next i50
- Next i100
- Next i150
- Next i300
- If mode = 2 And temp(2) & temp(3) & temp(4) = "020" Then
- fenpei = temp(1) & "101" & temp(5)
- Else
- If mode = 1 And temp(2) & temp(3) & temp(4) = "101" Then
- fenpei = temp(1) & "020" & temp(5)
- Else
- fenpei = temp(1) & temp(2) & temp(3) & temp(4) & temp(5)
- End If
- End If
- End Function
|
2楼
rongjun |
方法一的思路解析：
因为300、150、100是50或30的倍数，所以可以将总金额拆分成这样：- 总金额=30*A+50*B+余额
其中A、B为整数；
根据数学知识，整数B可以表示为B=3*C+Y，其中C为整数，Y的取值为{0;1;2}，所以：- 总金额=30*A+50*（3*C+Y）+余额
化简为：- 总金额=30*（A+5*C）+50*Y+余额
再化简：- 总金额=30*X+50*Y+余额
其中X为整数，Y为0或1或2
所以根据余额最小的原则，用如下公式求出最小余额：- MIN(MOD(总金额-{0;1;2}*50,30))
考虑到有可能出现负数而产生错误值，所以加个限制条件(总金额>={0;1;2}*50)
得出求最小余额的公式：- MIN(MOD(总金额-{0;1;2}*50*(总金额>={0;1;2}*50),30))
利用加权法求得Y值：- MOD(MIN(MOD(总金额-{0;1;2}*50*(总金额>={0;1;2}*50),30)*10^6+{0;1;2}),10^4)
利用加权法求得X值：
- =INT((总金额-MOD(MIN(MOD(总金额-{0;1;2}*50*(总金额>={0;1;2}*50),30)*10^6+{0;1;2}*50),10^4))/30)
然后将X值瓜分：每10张30元面值换成1张300元面值，余下每5张30元面值换成1张150元面值，余下为30元面值的数量。
将Y值瓜分：每2张50元面值换成1张100元面值，余下为50元面值的数量。
假设总金额位于A2单元格，即得出公式如下：
- 300元面值充值卡数量=INT((A2-MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}*50),10^4))/300)
- 150元面值充值卡数量=MOD(INT((A2-MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}*50),10^4))/150),2)
- 30元面值充值卡数量=INT(MOD((A2-MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}*50),10^4)),150)/30)
- 100元面值充值卡数量=INT(MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}),10^4)/2)
- 50元面值充值卡数量=MOD(MOD(MIN(MOD(A2-{0;1;2}*50*(A2>={0;1;2}*50),30)*10^6+{0;1;2}),10^4),2)
|
3楼
rongjun |
方法二的思路解析：
总金额可以用如下数学表达式表示（其中A、B为整数）：
- 总金额=30*A+50*B+余额
由此可以得出余数计算式：
- 余额=总金额-50*B-30*A=MOD(总金额-50*B,30)
其中的B，理论上可以为0～INT(总金额/50)，但实际上由于两张50元就可以换成一张100元的，所以B的区间可以设为{0;1}。
由此可以得到最小余额的表达式为：
- =MIN(MOD(总金额-50*{0;1},30))
考虑到“总金额-50*{0;1}”可能出现负数情况，利用TEXT函数将负数转化为最大余数，达到除错的目的，因此最小余额表达式可以转化为：
- =MIN(MOD(TEXT(总金额-50*{0;1},"0.00;29"),30))
利用加权法可以得出50元面值充值卡数量的计算式为：
- =RIGHT(MIN(MOD(TEXT(总金额-50*{0;1},"0.00;29"),30)*10^4+{0;1}))
同理，余额的表达式也可以表示为：
- 余额=总金额-50*B-30*A=mod(总金额-30*A,50)
其中的A，理论上可以为0～INT(总金额/30)，但实际上由于5张30元就可以换成一张150元的，所以A的区间可以设为{0;1;2;3;4}。同样使用TEXT函数除错，利用加权法可以得出30元面值充值卡数量的计算式为：
- =RIGHT(MIN(MOD(TEXT(总金额-30*{0;1;2;3;4},"0.00;49"),50)*10^4+{0;1;2;3;4}))
扣除30元和50元面值的金额后，采用同样的算法可以得出100元面值充值卡数量的计算式为：
- =RIGHT(MIN(MOD(TEXT(总金额-50元面值金额-30元面值金额-100*{0;1;2},"0.00;149"),150)*10^4+{0;1;2}))
由于2张150元面值可以换成300元，因此只需将总金额扣除30元、50元、100元面值金额后除以150取整，再对2求余，即可得出150元面值充值卡的数量：
- =MOD(INT((A2-E2*50-F2*30-D2*100)/150),2)
最后，将总金额扣除30元、50元、100元、150元面值的金额，再除以300，取整，即可得出300元面值充值卡的数量：
- =INT((A2-E2*50-F2*30-D2*100-C2*150)/300)
|
4楼
rongjun |
附件：
 金额分配（方法一）.rar
金额分配（方法二）.rar
金额分配（VBA法）.rar
|
5楼
落雪绽菊 |
用规划求解可以得到 |
6楼
icenotcool |
|
7楼
E林好汉 |
|
8楼
lrlxxqxa |
好帖
|