DSUM和Excel表：具有多个条件的总和

最新推荐文章于 2023-01-28 08:17:17 发布

culiao2169

最新推荐文章于 2023-01-28 08:17:17 发布

阅读量873

点赞数

文章标签：数据库 python java mysql 人工智能

原文链接：https://contexturesblog.com/archives/2012/11/15/dsum-and-excel-tables-sum-with-multiple-criteria/

版权

If you need to get a total in Excel, based on criteria, there are a few different ways that you could do it. Today, we'll take a look at how DSUM and Excel Tables sum with multiple criteria.

如果您需要根据条件在Excel中获得总计，可以采用几种不同的方法。今天，我们将看看DSUM和Excel Table如何将多个条件相加。

其他求和方式 (Other Ways to Sum)

First, here are other ways that you can sum with single or multiple criteria. For example:

首先，这里是您可以对单个或多个条件求和的其他方法。例如：

SUMIF – total based on a single criterion
SUMIF –基于单个标准的总计
SUMIFS – total based on multiple criteria
SUMIFS –基于多个条件的总计
SUMPRODUCT – total based on multiple criteria
SUMPRODUCT –基于多个条件的总计
SUBTOTAL – total based on filtered data
小计–基于过滤数据的总计
Pivot Table – summarize filtered data
数据透视表–汇总过滤数据

The SUMIF, SUMIFS and SUMPRODUCT functions can be used in multiple rows, and refer to cells in their current row or column for criteria. Charles Williams recommends using SUMIFS whenever possible, to improve performance.

SUMIF，SUMIFS和SUMPRODUCT函数可以在多行中使用，并引用其当前行或列中的单元格作为条件。 Charles Williams 建议尽可能使用SUMIFS ，以提高性能。

The SUMIF, SUMIFS and SUMPRODUCT functions can also be used at the top of a worksheet, or on a dashboard, to summarize data in a table, based on selected criteria.

SUMIF，SUMIFS和SUMPRODUCT函数还可以用于工作表的顶部或仪表板上，以根据选定的标准汇总表中的数据。

使用DSUM (Using DSUM)

Another method for summarizing results in a dashboard, based on criteria, is to use DSUM, which is one of Excel's database functions. Because DSUM uses a criteria range, it isn't suitable for use in multiple rows, but is a good choice for a single summary, and it can use complex criteria.

根据条件汇总仪表盘中结果的另一种方法是使用DSUM，这是Excel的数据库功能之一。因为DSUM使用条件范围，所以它不适合在多行中使用，但是对于单个摘要是一个不错的选择，并且它可以使用复杂的条件。

We'll see how to use the DSUM function, with Excel's named tables – a feature that was introduced in Excel 2007.

我们将了解如何与Excel的命名表一起使用DSUM函数-Excel 2007中引入的一项功能。

设置DSUM工作簿 (Set Up the Workbook for DSUM)

To use the DSUM function, you'll have to set up the following ranges, similar to what you would use for an Advanced Filter:

要使用DSUM功能，您必须设置以下范围，类似于用于高级过滤器的范围：

A database range
数据库范围
A criteria range
标准范围

In this example, the database contains order information, and is in a table named tblOrders.

在此示例中，数据库包含订单信息，并且位于名为tblOrders的表中。

On another sheet, I'll set up a criteria range, using headings that exactly match headings in the database. In the cells directly below the headings, I'll type a sales rep's name, and an item that was sold.

在另一张纸上，我将使用与数据库标题完全匹配的标题来设置条件范围。在标题正下方的单元格中，我将输入销售代表的姓名和已售出的商品。

The address of the criteria range is E1:F2

条件范围的地址为E1：F2

创建DSUM公式 (Create the DSUM Formula)

Next, I'll create a DSUM formula that calculates the total units sold, by summing the Units column in the database, where the Rep name is Jones, and the Item is Pen.

接下来，我将创建一个DSUM公式，通过将数据库中的“单位”列加起来来计算已售出的总单位，其中“代表”名称为“琼斯”，“项目”为“笔”。

The arguments for the DSUM function are database, field and criteria. My database is the table named tblOrders, I want to sum the Units field, and my criteria range is E1:F2

DSUM函数的参数是数据库，字段和条件。我的数据库是名为tblOrders的表，我想对“单位”字段求和，我的标准范围是E1：F2

=DSUM(tblOrders,"Units",E1:F2)

= DSUM(tblOrders，“ Units”，E1：F2)

Unfortunately, that formula returns a #VALUE! error, because DSUM doesn't recognize the named table. Apparently the database functions weren't updated when named tables were added in Excel 2007.

不幸的是，该公式返回了#VALUE！错误，因为DSUM无法识别命名表。显然，在Excel 2007中添加命名表时，数据库功能没有更新。

为数据库创建命名范围 (Create a Named Range for the Database)

The DSUM doesn't recognize the named table, so I'll create a named range – dbOrders – based on the table.

DSUM无法识别命名表，因此我将基于该表创建命名范围dbOrders。

On the Excel Ribbon, click the Formulas tab, and click Define Name.
在Excel功能区上，单击“公式”选项卡，然后单击“定义名称”。
In the New Name dialog box, type a name for the range – dbOrders
在“新名称”对话框中，输入范围的名称– dbOrders
Click in the Refers To box, and on the worksheet, click on the upper left corner of the orders table, to select the entire table. The table name will appear in the Refers To box.
单击“引用至”框，然后在工作表上，单击订单表的左上角，以选择整个表。表名将出现在“引用到”框中。

=tblOrders

= tblOrders

Click on the upper left cell again, to include the heading cells in the selection. The Refers to box will change to:
再次单击左上方的单元格，以将标题单元格包括在选择中。引用框将更改为：

=tblOrders[#All]

= tblOrders [#All]

Click OK, to complete the name.
单击确定，完成名称。

更改DSUM公式 (Change the DSUM Formula)

Finally, I'll change the DSUM formula, so it uses the named range, dbOrders, instead of the named table.

最后，我将更改DSUM公式，以便它使用命名范围dbOrders而不是命名表。

=DSUM(dbOrders,"Units",E1:F2)

= DSUM(dbOrders，“ Units”，E1：F2)

With that change, the DSUM function happily calculates the number of pens that Jones sold.

有了这一更改，DSUM函数即可轻松计算出Jones出售的笔数。

建立确切标准 (Creating Exact Criteria)

In the example shown above, the DSUM function treats the text criteria as "begins with", rather than "equal to".

在上面显示的示例中，DSUM函数将文本条件视为“从...开始”，而不是“等于”。

So, the total units sold would include any pen, pen sets, and pencils that were sold by Jones, because those items all begin with "Pen"

因此，售出的总单位将包括Jones售出的任何笔，钢笔和铅笔，因为这些项目都以“笔”开头

If you want to find only the items that are equal to "Pen", change the criterion to:

如果只想查找等于“笔”的项目，请将条件更改为：

="=Pen"

=“ =笔”

With this criterion in cell F2, the total units is 91, which only includes the Pen sales, not pencils, or pen sets.

使用单元格F2中的此条件，总单位为91，仅包括笔的销售，不包括铅笔或笔的总和。

For more information and examples for setting up the criteria range, please see criteria range setup section on the Advanced Filter page – it uses the same type of setup.

有关设置标准范围的更多信息和示例，请参阅“高级过滤器”页面上的“ 标准范围设置”部分-它使用相同的设置类型。

添加更多条件行 (Add More Criteria Rows)

You're not just limited to one row in the criteria area. For example, you could add more rep names and items, and expand the Criteria Range in the DSUM formula.

您不仅限于条件区域中的一行。例如，您可以添加更多代表名称和项目，并在DSUM公式中扩展“条件范围”。

In the example shown below, the criteria range is now E1:F4

在下面显示的示例中，条件范围现在为E1：F4

=DSUM(dbOrders,"Units",E1:F4)

= DSUM(dbOrders，“ Units”，E1：F4)

DSUM calculates the total units for orders where:

DSUM计算以下订单的总单位：

- OR
  要么
- OR
  要么
Gill is the rep, AND the item name begins with Pen
Gill是代表，并且商品名称以Pen开头

在条件单元格中使用公式 (Use Formulas in Criteria Cells)

If you want to use more than a few criteria rows, it can be confusing and cumbersome to create a large criteria range. Instead, you can use formulas in the criteria cells, similar to the criteria formulas that you can use for an Advanced Filter.

如果要使用多个标准行，则创建较大的标准范围可能会造成混乱和麻烦。相反，您可以在条件单元格中使用公式，类似于可用于“高级筛选器”的条件公式。

If you're using formulas in the criteria range, leave the heading cell blank, or use a heading that is NOT used in the database.

如果您正在使用标准范围内的公式，请将标题单元格留空，或使用数据库中未使用的标题。

In the example shown below, the criteria headings have been changed to RepCount and ItemCount. At the left are named tables – tblRepSel and tblItemSel – where I have entered the reps and items that I want to include in the DSUM total.

在下面显示的示例中，条件标题已更改为RepCount和ItemCount。左侧是命名表– tblRepSel和tblItemSel –我在其中输入了要包含在DSUM总数中的代表和项目。

输入条件公式 (Enter the Criteria Formulas)

In cells E2 and F2, we'll use COUNTIF formulas, to check if:

在单元格E2和F2中，我们将使用COUNTIF公式来检查是否：

the rep name from the database is in the tblRepSel table,
来自数据库的代表名称在tblRepSel表中，
the item name from the database is in the tblItemSel table.
数据库中的项目名称在tblItemSel表中。

In the criteria formula, we'll refer to a cell in the first row of data in the database, using a relative reference. We can use a named table reference for the range argument in the COUNTIF function, but must use a normal cell reference for the criteria argument.

在条件公式中，我们将使用相对引用引用数据库第一行数据中的单元格。我们可以在COUNTIF函数中将命名表引用用于range参数，但是必须将常规单元格引用用于criteria参数。

The formula in E2 is:

E2中的公式为：

=COUNTIF(tblRepSel[Rep],Orders!D2)

= COUNTIF(tblRepSel [Rep]，Orders！D2)

The formula in F2 is:

F2中的公式是：

=COUNTIF(tblItemSel[Item],Orders!E2)

= COUNTIF(tblItemSel [Item]，Orders！E2)