r中aggregate_在Excel中使用AGGREGATE更改功能

r中aggregate

A couple of years ago, we looked at the Excel SUBTOTAL function, and saw how you could allow users to select the function they want. In the example shown below, the total formulas are controlled by the drop down list at the top of the sheet. Today, we'll use a similar technique to change functions with AGGREGATE - one of Excel's newer functions.

几年前，我们研究了Excel SUBTOTAL函数 ，并看到了如何允许用户选择他们想要的函数。 在下面显示的示例中，总公式由工作表顶部的下拉列表控制。 今天，我们将使用类似的技术通过AGGREGATE更改功能-AGGREGATE是Excel的较新功能之一。

选择小计功能 (Select a Function for SUBTOTAL)

This screen shot shows the drop down list of functions that people could choose from, for a SUBTOTAL formula.

此屏幕快照显示了人们可以选择的功能的下拉列表，以计算SUBTOTAL公式。

聚集函数 (The AGGREGATE Function)

In Excel 2010 the AGGREGATE function was introduced, and it’s like a supercharged version of SUBTOTAL.

在Excel 2010中，引入了AGGREGATE函数 ，它类似于SUBTOTAL的增强版本。

AGGREGATE has a longer list of functions, plus options on what data, if any, to ignore.

AGGREGATE具有更长的功能列表，以及有关忽略哪些数据(如果有)的选项。

• 1. • AGGREGATE(function_num, options, ref1, [ref2], …)

       AGGREGATE(function_num，options，ref1，[ref2]，…)

     For some functions, you can use the Reference form, with these arguments:

     对于某些功能，可以使用带有以下参数的“ 引用”表单：

  2. • AGGREGATE(function_num, options, array, [k])

       AGGREGATE(function_num，options，array，[k])

     For a few functions, such as LARGE, you can use the Array form, with these arguments:

     对于一些函数(例如LARGE)，可以使用带有以下参数的Array形式：

使聚合更容易 (Make AGGREGATE Easier)

To make AGGREGATE easier to use, I’ve created a sample file that lets you select:

为了使AGGREGATE易于使用，我创建了一个示例文件，可让您选择：

• a function
  一个功能
• an option for what to ignore
  忽略什么的选择
• a [k] setting, for the array functions.
  [k]设置，用于数组功能。

设置数据 (Set Up the Data)

In this example, there are sales orders, in a formatted Excel table named tblSales. There is an error in one of the rows, and you will be able to ignore that, if you choose to.

在此示例中，在名为tblSales的格式化Excel表中有销售订单。 其中一行出现错误，并且您可以选择忽略该错误。

列出AGGREGATE函数和选项 (List the AGGREGATE Functions and Options)

In the Excel Help for AGGREGATE, you'll find a list of the functions and their numbers. There is also a list of the options for what to ignore.

在AGGREGATE的Excel帮助中 ，您将找到函数及其编号的列表。 还列出了忽略什么的选项。

I’ve put those lists into the sample workbook, as formatted tables. Then, I named the columns in each table, so I could use them in the formulas. For example, the column with function names is FunctionsList.

我已将这些列表作为格式化表格放入示例工作簿中。 然后，我为每个表中的列命名，以便可以在公式中使用它们。 例如，具有函数名称的列为FunctionsList。

There are 19 functions, and the third column shows which ones require the [k] argument

有19个函数，第三列显示哪些函数需要[k]参数

There are 8 options for ignoring data.

有8个用于忽略数据的选项。

创建下拉列表 (Create Drop Down Lists)

On the sales order sheet, above the Excel table, create drop down lists of functions and options, by using a data validation list. Between them, there is a cell where the k value can be typed, when it is required.

在Excel表格上方的销售订单表上，通过使用数据验证列表创建功能和选项的下拉列表。 在它们之间，有一个需要输入k值的单元格。

These cells are named – SelFnName, SelRef2, and SelOpt.

这些单元格的名称为– SelFnName，SelRef2和SelOpt。

设置默认功能和选项编号 (Set Default Function and Option Numbers)

The AGGREGATE function needs a number, not a function or option name. To find the numbers, you can use the INDEX function with MATCH.

AGGREGATE函数需要一个数字，而不是函数或选项名称。 要查找数字，可以将INDEX函数与MATCH一起使用。

First, I selected default functions and options, just in case the drop down lists are cleared out.

首先，我选择了默认功能和选项，以防万一下拉列表被清除。

In cell B4 of the lists sheet, I typed a 9, and in cell C4, used INDEX and MATCH to show the function name. There is a similar setup for the Options, in cells F4:G4.

在列表表的单元格B4中，我键入了9，在单元格C4中，使用了INDEX和MATCH来显示函数名称。 在单元格F4：G4中有一个类似的“选项”设置。

Here is the formula in cell C4:

这是单元格C4中的公式：

• =INDEX(FunctionsList,MATCH(B4,FunctionIDs,0))

  = INDEX(FunctionsList，MATCH(B4，FunctionIDs，0))

Cell B4 is named FunctionDefault and F4 is named OptionDefault.

单元格B4命名为FunctionDefault，而F4命名为OptionDefault。

查找所选功能和选件号 (Find the Selected Function and Option Number)

Next, on the orders sheet, use the INDEX function, to find the IDs for function and option, and see if a Ref2 is required. These formulas go into cells A3:C3, and those columns will be hidden.

接下来，在订单表上，使用INDEX函数查找函数和选项的ID，并查看是否需要Ref2。 这些公式进入单元格A3：C3，并且这些列将被隐藏。

• In cell A3: =IFERROR(INDEX(FunctionsRef2List, MATCH(SelFnName, FunctionsList,0)),"")

  在单元格A3中： = IFERROR(INDEX(FunctionsRef2List，MATCH(SelFnName，FunctionsList，0))，“”)

• In cell B3: =IFERROR(INDEX(FunctionIDs, MATCH(SelFnName, FunctionsList,0)), FunctionDefault)

  在单元格B3中： = IFERROR(INDEX(FunctionIDs，MATCH(SelFnName，FunctionsList，0))，FunctionDefault)

• In cell C3: =IFERROR(INDEX(OptionIDs, MATCH(SelOpt, OptionsList,0)), OptionDefault)

  在单元格C3中： = IFERROR(INDEX(OptionIDs，MATCH(SelOpt，OptionsList，0))，OptionDefault)

These cells are named as Ref2ID, SelFnID and SelOptID.

这些单元格分别命名为Ref2ID，SelFnID和SelOptID。

创建小计公式 (Create the Subtotal Formulas)

Above the table, we’ll use formulas to calculate the totals for the Units, Cost and Amt columns.

在表格上方，我们将使用公式来计算“单位”，“费用”和“金额”列的总计。

Here is the formula in cell F5, which is copied across to H5:

这是单元格F5中的公式，该公式被复制到H5：

• =IF(Ref2ID=0, AGGREGATE(SelFnID, SelOptID, tblSales[Units]),

  = IF(Ref2ID = 0，AGGREGATE(SelFnID，SelOptID，tblSales [Units])，

  AGGREGATE(SelFnID, SelOptID, tblSales[Units], SelRef2))

  AGGREGATE(SelFnID，SelOptID，tblSales [Units]，SelRef2)

First, the formula checks for a Ref2ID.

首先，该公式检查Ref2ID。

• If there isn’t one (=0), the first version of the AGGREGATE formula is used - AGGREGATE(SelFnID,SelOptID,tblSales[Units])
  如果没有一个(= 0)，则使用AGGREGATE公式的第一个版本-AGGREGATE(SelFnID，SelOptID，tblSales [Units])
• If there is a RefID, the second version of the formula is used - AGGREGATE(SelFnID,SelOptID,tblSales[Units],SelRef2)
  如果存在RefID，则使用公式的第二个版本-AGGREGATE(SelFnID，SelOptID，tblSales [Units]，SelRef2)

To show what was selected, the formula in cell E5 combines the function name and k value, if any:

为了显示所选内容，单元格E5中的公式将函数名称和k值(如果有)结合在一起：

=SelFnName & IF(A3=0,""," - "  & F3)

= SelFnName＆IF(A3 = 0，“”，“-”＆F3)

In cell E6, there is a simple link to the selected option: =SelOpt

在单元格E6中，有一个指向所选选项的简单链接： = SelOpt

测试下拉菜单 (Test the Drop Downs)

To test the drop downs, filter the Item column in the sal es table, to hide one of the items. Then, select a function name and option, and enter a k value, if required. There is conditional formatting on the k value cells, so they don’t appear if not required.

要测试下拉菜单，请过滤sales表中的Item列，以隐藏其中一项。 然后，选择函数名称和选项，然后根据需要输入ak值。 在k个值单元格上有条件格式设置，因此如果不需要，它们不会出现。

The total cells will automatically change, to show the totals for the selected function and option.

总计单元格将自动更改，以显示所选功能和选项的总计。

NOTE: In the sample file, the same formulas are in the tables Total row. To see that, select a cell in the table and click the Design tab on the Ribbon. Add a check mark to Total Row. I turned this off in the sample file because the drop down arrows in that row make it very easy to overwrite the formulas.

注意：在示例文件中，表“总计”行中具有相同的公式。 为此，请在表中选择一个单元格，然后单击功能区上的“设计”选项卡。 在总行中添加一个复选标记。 我在示例文件中将其关闭，因为该行中的下拉箭头使覆盖公式变得非常容易。

下载样本文件 (Download the Sample File)

To see the function, and the lists, you can download sample file. On my Contextures website, go to the Sample Files page, and in the Functions section, look for FN0030 – Flexible AGGREGATE Function Selector.

要查看功能和列表，可以下载示例文件。 在我的Contextures网站上，转到“示例文件”页面，然后在“功能”部分中查找FN0030 –灵活的AGGREGATE功能选择器 。

The file is in xlsx format, and is zipped. There are no macros in the workbook.

该文件为xlsx格式，并且已压缩。 工作簿中没有宏。

翻译自: https://contexturesblog.com/archives/2013/10/08/change-functions-with-aggregate-in-excel/

r中aggregate