gpt4 book ai didi

excel - 用excel处理变量

转载 作者:行者123 更新时间:2023-12-04 21:00:51 24 4
gpt4 key购买 nike

语言:Excel

您好,感谢您阅读我的帖子。

我正在尝试创建一个公式来...根据变量乘以单元格(或在本例中为字母)。变量是它是否可以被(1000 + 250x)整除,并根据答案,乘以相应的字母百分比。

视觉表现:

   A     B     C   
1% 2% 3%
250 500 1000


1 1,000
2 1,250
3 1,500
4 1,750
5 2,000

例如,因为 #1 可以被 1000 整除,所以我将它乘以 3%
第二个例子,因为 #2 可以被 250 和 1000 整除,我将 250 乘以 1% 和 1000 乘以 3%,然后将它们相加。

我目前的尝试:
=IF(MOD(A2,F14)<=1,A2*F15,"")

A2 = 起始金额
F14 = A2 除以什么
F15 = 百分比

这种工作,但它不允许我找到最好的解决方案。

非常感谢您在我的困境中提供的帮助。

最佳答案

我想不出 Excel 公式有什么好的解决方案,因为你想要的结果太复杂了:就像你标记了你的问题一样,无论如何你都需要一个循环,恐怕哪些公式不能为你做。
但是,正如您添加的 VBA作为您的标签之一,我假设 VBA解决方案对你有用,所以这是我写的脚本:

Option Explicit 'variables MUST BE declared, otherwise error. very handy rule
Option Base 0 'won't be needed this time, but in general, this rule is also a great ally
'(it says: arrays' 1st item will always be the "0th" one)

Dim divLARGE, divMED, divSMALL 'you can use variable types in Excel
Dim percLARGE, percMED, percSMALL 'but sadly, not in VBScript which I have ATM

'test input values and their results, won't be needed in your Excel
Dim testA, testB, testC, testD, testE, testF 'so add types if you like
Dim resA, resB, resC, resD, resE, resF '(should make execution a little faster)

'Init our variables declared above. in VBScript you can't do this at declaration,
'i.e. can't say "Dim whatever As Boolean = true" which would be the right way to do this
Call Initialize()

'Call the "main routine" to execute code
Call Main()

'you can add access modifiers here. "private" would be the best
'i.e. "private Sub Main()"
Sub Main()
resA = CalcMaster(testA, divLARGE)
resB = CalcMaster(testB, divLARGE)
resC = CalcMaster(testC, divLARGE)
resD = CalcMaster(testD, divLARGE)
resE = CalcMaster(testE, divLARGE)
resF = CalcMaster(testF, divLARGE)
MsgBox (CStr(testA) + " --> " + CStr(resA) + vbCrLf + _
CStr(testB) + " --> " + CStr(resB) + vbCrLf + _
CStr(testC) + " --> " + CStr(resC) + vbCrLf + _
CStr(testD) + " --> " + CStr(resD) + vbCrLf + _
CStr(testE) + " --> " + CStr(resE) + vbCrLf + _
CStr(testF) + " --> " + CStr(resF) + vbCrLf)
End Sub

Sub Initialize()
divLARGE = 1000 'the large number for which we look after remnants
divMED = 500 'medium/middle sized number to divide by
divSMALL = 250 'the small value
percLARGE = 3 'percentage we want if no remnants on LARGE number
percMED = 2 'same but for medium/mid size numbers
percSMALL = 1 'and the percentage we want for the small remnants

testA=1000 'result should be exactly 30.0
testB=1250 'res == 32.5
testC=1500 'res == 40.0
testD=1750 'res == 42.5
testE=2000 'res == 60.0
testF=-198 'res == #ERROR/INVALID VALUE
End Sub

Function CalcMaster(inVar, byDiv) 'A silly function name popped in my mind, sorry :)
Dim remnant, percDiv
'sometimes happens, looks cheaper calc.wise to handle like this; if initial input
'can be 0 and that's a problem/error case, handle this scenario some other way
If (inVar = 0) Then Exit Function
remnant = inVar Mod byDiv
'if you'll implement more options, do a Select...Case instead (faster)
If (byDiv = divLARGE) Then
percDiv = percLARGE
ElseIf (byDiv = divMED) Then
percDiv = percMED
Else
percDiv = percSMALL
End If


If (remnant = 0) Then
CalcMaster = inVar * (percDiv / 100)
Exit Function
End If
'had remnant; for more than 3 options I would use an array of options
'and call back self with the next array ID
If (byDiv = divLARGE) Then
CalcMaster = CalcMaster(inVar - remnant, divLARGE) + CalcMaster(remnant, divMED)
ElseIf (byDiv = divMED) Then
CalcMaster = CalcMaster(inVar - remnant, divMED) + CalcMaster(remnant, divSMALL)
Else 'or return 0, or raise error and handle somewhere else, etc
'MsgBox ("wrong input number: " + CStr(inVar))
CalcMaster = -1
End If
End Function
这并不完美,我认为那里可能有更好的解决方案,但我认为这对事业来说已经足够了。我希望你同意:)干杯

关于excel - 用excel处理变量,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36527924/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com