wps透视表列总计移到顶部_数据透视表总计中的错误

wps透视表列总计移到顶部

Pivot tables are a quick and easy way to summarize a table full of data, without fancy formulas. Occasionally though, things can go wrong. Today we'll figure out why you might see errors in pivot table totals or subtotals, when all the item amounts look fine.

数据透视表是一种快速而简单的方法来汇总一个充满数据的表，而无需花哨的公式。 有时候，事情可能会出错。 今天，我们将弄清楚为什么当所有项目金额都很好时，数据透视表总计或小计中可能会出现错误。

为什么会有错误？ (Why Are There Errors?)

Here's a simple pivot table with error values in the Grand Total row.

这是一个简单的数据透视表，其中“总计”行中包含错误值。

It's understandable that the "Average of Price" total shows a #DIV/0! error, because that error also appears for the East region, in that column.

可以理解，“平ASP格”总计显示为＃DIV / 0！ 错误，因为该错误也出现在该列的东部区域。

But why are there errors in the totals for the Count and Count Numbers columns? There aren't any errors in the amounts that are being totalled there.

但是，为什么“计数”和“计数编号”列的总计中存在错误？ 总计金额中没有任何错误。

检查源数据 (Check the Source Data)

Some pivot table mysteries can be solved if you take a look at the source data, so let's start there.

如果您查看源数据，则可以解决一些数据透视表的奥秘，因此让我们从这里开始。

Here's a screen shot of the small table that's the source for the pivot table. You can download this sample file from the Pivot Table Summary Functions page on my Contextures website.

这是小桌子的屏幕截图，小桌子是数据透视表的来源。 您可以从Contextures网站上的“数据透视表摘要功能”页面下载此示例文件。

数据中的错误值 (Error Values in the Data)

Obviously, there are some problems with that data.

显然，该数据存在一些问题。

• There is text in cell C4, so the formula in E4 has a #VALUE! error, instead of a numeric result
  C4单元格中有文本，因此E4中的公式具有#VALUE！ 错误，而不是数字结果
• There are three #DIV/0 errors, because Excel can't divide by zero
  存在三个＃DIV / 0错误，因为Excel不能被零除
• There is a blank cell (E7), where a formula has been deleted
  有一个空白单元格(E7)，其中的公式已被删除
• Two cells (C4 and E9) contain text, instead of the numbers that should be in those columns
  两个单元格(C4和E9)包含文本，而不是这些列中的数字

创建数据透视表 (Create a Pivot Table)

What happens if you create a simple pivot table based on that error-filled data?

如果基于该错误填充的数据创建简单的数据透视表，会发生什么情况？

Things looked fine when I created this pivot table, with Region in the Rows area, and Count of Total in the Values area.

当我创建此数据透视表时，事情看起来很好，在“行”区域中具有“区域”，在“值”区域中具有“总计计数”。

The error values have disappeared from the pivot table, and the counts for each Region are showing correctly.

错误值已从数据透视表中消失，并且每个区域的计数正确显示。

更改总和功能 (Change to Sum Function)

However, things take a turn for the worse if you change the Summary Function.

但是，如果更改Summary Function ，情况会变得更糟。

Instead of a Count of the Total amounts, try these steps to see a Sum.

请尝试执行以下步骤以查看总和，而不是总计。

• Right-click on one of the numbers in the Count of Total column
  右键单击“总数”列中的数字之一
• Click Summarize Values by, and click Sum
  单击“汇总值依据”，然后单击“求和”

As soon as the function changes to Sum, errors appear in the pivot table, for the East region, and in the Grand Total.

一旦函数更改为Sum，错误就会在数据透视表，东部区域和总计中出现。

就像工作表功能一样 (Just Like Worksheet Functions)

The Pivot Table Sum function is like the worksheet SUM function, and it returns an error value, if there's one in the range being summed.

数据透视表求和函数类似于工作表的求和函数，并且如果要求和的范围中有一个，它将返回一个错误值。

The Count function in a pivot table is like the worksheet COUNTA function. It counts text, numbers and errors, and does not count blank cells.

数据透视表中的Count函数类似于工作表COUNTA函数 。 它计算文本，数字和错误，不计算空白单元格。

Pivot tables also have a Count Numbers function, which is like the worksheet COUNT function. It counts numbers, and does not count blank cells, errors or text.

数据透视表还具有一个Count Numbers函数，类似于工作表COUNT函数 。 它计算数字，不计算空白单元格，错误或文本。

源数据总计 (Totals in the Source Data)

If you create Count, CountA and Sum totals in the source data, only the Sum totals show errors.

如果您在源数据中创建Count，CountA和Sum总计，则只有Sum总计会显示错误。

Similarly, in the pivot table, Count and Count Numbers show totals.

同样，在数据透视表中，“计数”和“计数编号”显示总计。

But Sum shows an error value.

但是Sum显示一个错误值。

错误规则的例外 (Exceptions to the Error Rule)

So, Count and Count Numbers shouldn't show error values in their Grand Totals.

因此，“计数”和“计数编号”不应在其总计中显示错误值。

And they don't, if they're all alone in the pivot table.

如果不是他们一个人在数据透视表中，他们就不会。

But, if you add another Value field, and it has errors, the Count and Count Numbers totals might suddenly show errors too.

但是，如果您添加另一个“值”字段，并且该字段有错误，则“计数”和“计数编号”总数可能也会突然显示错误。

You'll see errors in the subtotals and totals, if these 2 conditions are met:

如果满足以下两个条件，您将看到小计和总计中的错误：

• • Oher summary functions are included in the pivot table, and those fields contain errors in the data

    枢纽分析表中包含汇总功能， 这些字段包含数据中的错误

  • There are error values in the data used for the Count and Count Number

    用于计数和计数编号的数据中存在错误值

    columns

    列

数据字段中没有错误 (No Errors in Data Field)

Here's another look at the pivot table from the top of this post.

这是本文顶部的数据透视表的另一种外观。

The first 2 count columns have an error in the total, because:

前两个计数列的总数有误，原因是：

• the Average of Price contains an error
  平ASP格包含错误
• The Total data contains errors
  总计数据包含错误

However, the Count of Date column show a numeric total, because the source data doesn't have any errors in the Date field.

但是，“日期计数”列显示的是数字总计，因为源数据在“日期”字段中没有任何错误。

获取样本文件 (Get the Sample File)

You can download this sample file from the Pivot Table Summary Functions page on my Contextures website.

您可以从Contextures网站上的“数据透视表摘要功能”页面下载此示例文件。

The zipped file is in xlsx format, and does not contain any macros.

压缩文件为xlsx格式，不包含任何宏。

翻译自: https://contexturesblog.com/archives/2019/05/02/pivot-table-value-errors/

wps透视表列总计移到顶部