vba 根据下拉框中的选择更改公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21819663/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change formula based on selection from dropdown box
提问by SamPassmore
I am trying to create a quick way to make changes on an Excel sheet and I was wondering if someone could enlighten me on an efficient way to solve the following problem.
我正在尝试创建一种在 Excel 工作表上进行更改的快速方法，我想知道是否有人可以启发我解决以下问题的有效方法。
Say I have a line of three numbers, lets say A1:C1 (However, the data is not in neighbouring columns in the real data set):
假设我有一行三个数字，比如说 A1:C1（但是，数据不在真实数据集中的相邻列中）：
5, 10, 15
I want to have a dropdown box at the end of that line (A4) which shows a list of functions that could be used on these numbers (For example: SUM, AVERAGE, MAX, MIN) and for that to dictate the formula in A5.
我想在该行 (A4) 的末尾有一个下拉框，它显示可用于这些数字的函数列表（例如：SUM、AVERAGE、MAX、MIN），并为此指定 A5 中的公式.
A simple way to do this is to create an IF statement and a dropdown box of the possibilities: e.g.
一个简单的方法是创建一个 IF 语句和一个可能性的下拉框：例如
= IF($A4 = "average", AVERAGE(A1:A3), IF($A4= "min", MIN(A1:A3), etc..
However this is tedious if there is a large number of function choices. So I was wondering if there was a quick way to do this?
然而，如果有大量的功能选择，这将是乏味的。所以我想知道是否有一种快速的方法来做到这一点？
Perhaps something like
也许像
= function(A1:A3)
Where function will change dependent on the phrase in the dropdown box, and the dropdown box is contingent on a list of functions held somewhere else on the sheet.
函数的变化取决于下拉框中的短语，而下拉框取决于工作表上其他位置的函数列表。
回答by pnuts
Must you "reinvent the wheel"? Seems all to have been done for you, with Insert > Tables - Table:
你必须“重新发明轮子”吗？似乎已经为您完成了所有工作，插入 > 表格 - 表格：
回答by Siddharth Rout
Interesting Question. :)
有趣的问题。:)
See this example
看这个例子
Let's say your worksheet looks like this
假设您的工作表如下所示
Code:
代码：
Paste this in a module
将此粘贴到模块中
Function GuessFunction(Rng As Range, FuncType As String) As Variant
Dim wFn As WorksheetFunction
GuessFunction = "Error - Please Check Input"
Set wFn = Application.WorksheetFunction
Select Case UCase(FuncType)
Case "AVERAGE": GuessFunction = wFn.Average(Rng)
Case "SUM": GuessFunction = wFn.Sum(Rng)
Case "MAX": GuessFunction = wFn.Max(Rng)
Case "COUNT": GuessFunction = wFn.Count(Rng)
'
'~~> Add More
'
End Select
End Function
Next enter the formula =GuessFunction(A1:H1,I1)in the cell and your are done.
接下来=GuessFunction(A1:H1,I1)在单元格中输入公式，您就完成了。
Output:
输出：
ALTERNATIVE
选择
Another way to do what you is to use EVALUATE
另一种方法来做你要使用的 EVALUATE
Function GuessFunction(Rng As Range, FuncType As String) As Variant
GuessFunction = "Error - Please Check Input"
GuessFunction = Application.Evaluate("=" & FuncType & "(" & Rng.Address & ")")
End Function
PROS:
优点：
You don't have to specify separate code for each formula like we did it in Select Caseabove.
您不必像我们在Select Case上面所做的那样为每个公式指定单独的代码。
CONS:
缺点：
You can only make it work for formulas like SUM/MAX/MINi.e of type FORMULA(ADDRESS). You cannot use it for say VLOOKUP
您只能使其适用于SUM/MAX/MINie 类型的公式FORMULA(ADDRESS)。你不能用它说VLOOKUP
