根据下拉框中的选择更改公式 [英] Change formula based on selection from dropdown box

问题描述

我正在尝试创建一种在Excel工作表上进行更改的快速方法，并且我想知道是否有人可以启发我以一种有效的方式来解决以下问题.

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.

假设我有一个由三个数字组成的行，可以说A1:C1(但是，数据不在真实数据集中的相邻列中):

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):

5, 10, 15

我想在该行(A4)的末尾有一个下拉框，其中显示了可以在这些数字上使用的功能列表(例如:SUM，AVERAGE，MAX，MIN)，并以此来决定A5中的公式.

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.

一种简单的方法是创建一个IF语句和一个下拉框，例如:

A simple way to do this is to create an IF statement and a dropdown box of the possibilities: e.g.

= 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?

也许类似

= 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.

推荐答案

有趣的问题.:)

查看此示例

假设您的工作表如下

代码:

将此内容粘贴到模块中

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

接下来，在单元格中输入公式 = GuessFunction(A1:H1，I1)并完成操作.

Next enter the formula =GuessFunction(A1:H1,I1) in the cell and your are done.

输出:

替代

另一种做事的方法是使用 EVALUATE

Another way to do what you is to use 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:

您不必像上面的 Select Case 中那样为每个公式指定单独的代码.

You don't have to specify separate code for each formula like we did it in Select Case above.

缺点:

您只能使其适用于 SUM/MAX/MIN 之类的公式，即类型为 FORMULA(ADDRESS)的公式.您不能使用它来说 VLOOKUP

You can only make it work for formulas like SUM/MAX/MIN i.e of type FORMULA(ADDRESS). You cannot use it for say VLOOKUP

这篇关于根据下拉框中的选择更改公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！