js总计_Excel总计前3名加上其他的总计

js总计

In a pivot table, you can use the built-in Top 10 command, to show the Top 10 or Bottom 3, or almost any other top/bottom summary. In this post, we'll see how to show top 3, plus "other", to show the remaining amounts.

在数据透视表中，您可以使用内置的Top 10命令来显示Top 10或Bottom 3，或几乎任何其他顶部/底部摘要。 在这篇文章中，我们将看到如何显示前3名以及“其他”来显示剩余金额。

In this screen shot, the Top 10 is selected as a Value Filter.

在此屏幕快照中，前10名被选择为值过滤器。

After you select this command, the pivot table shows the selected results, and hides everything else.

选择此命令后，数据透视表将显示所选结果，并隐藏其他所有内容。

前3加其他 (Top 3 Plus Other)

Recently, I had to make charts that showed the top 3 amounts in a category, and the remaining amounts lumped together as "Other". The pivot table summarized the data very efficiently, but wouldn't create an "Other" grouping.So, to solve the problem, I used formulas adjacent to the pivot table.

最近，我不得不制作图表以显示类别中的前3个金额，而其余金额则汇总为“其他”。 数据透视表非常有效地汇总了数据，但不会创建“其他”分组。因此，为解决此问题，我在数据透视表旁使用了公式。

In this example, we want to see the three dates with the highest amounts, and a total for the remaining dates.

在此示例中，我们希望看到金额最高的三个日期，其余日期总计。

The first formula, in column D, pulls the top three dates. An IF formula is used, in case there are fewer than 3 dates in the results. The formula is entered in cell D5, and copied down to cell D7:

D列中的第一个公式拉出前三个日期。 如果结果中少于3个日期，则使用IF公式。 将公式输入到单元格D5中，然后向下复制到单元格D7中：

=IF(OR(A5="",A5="Grand Total"),"",A5)

= IF(OR(A5 =“”，A5 =“ Grand Total”)，“”，A5)

The next formula is entered in cell E5 and copied down to E7:

下一个公式在单元格E5中输入，并向下复制到E7：

=IF(D5="","",B5)

= IF(D5 =“”，“”，B5)

计算其他金额 (Calculate the Other Amount)

In cell D8, a different formula shows the result of "Other", if there are 3 or more top amounts.

在单元格D8中，如果有3个或更多的最高金额，则不同的公式将显示“其他”的结果。

=IF(OR(A8="",A8="Grand Total"),"","Other")

= IF(OR(A8 =“”，A8 =“ Grand Total”)，“”，“ Other”)

In cell E8, the final formula calculates the amount for Other, by subtracting the top 3 amounts from the Grand Total.

在单元格E8中，最终公式通过从总计中减去前3个金额来计算“其他”的金额。

=IF(D8="","", GETPIVOTDATA("Total",$A$4)-SUM(E5:E7))

= IF(D8 =“”，“”，GETPIVOTDATA(“ Total”，$ A $ 4)-SUM(E5：E7))

When you change the pivot table Report Filter selections, the Top 3 and Other amounts change automatically.

当您更改数据透视表的报表过滤器选择时，前3个和其他金额会自动更改。

创建前三名图表 (Create a Top 3 Chart)

To show the results on an Excel dashboard, you can create a chart, and move it to a Summary sheet.

要在Excel仪表板上显示结果，您可以创建一个图表，然后将其移至“摘要”表。

If you're using Excel 2010, you can add Slicers, so users can change the filter selections, without messing up your pivot table layout.

如果您使用的是Excel 2010，则可以添加Slicers ，以便用户可以更改过滤器选择，而不会弄乱数据透视表的布局。

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

To see the pivot table and formulas, you can download the Pivot Table Top 3 Other sample file. The file is in Excel 2010 format, and is zipped.

要查看数据透视表和公式，可以下载“ 数据透视表前3个其他”样本文件。 该文件为Excel 2010格式，并且已压缩。

There are no macros in the file, and the Slicers are only visible in Excel 2010. ________________

文件中没有宏，并且切片器仅在Excel 2010中可见。

翻译自: https://contexturesblog.com/archives/2011/03/23/excel-totals-for-top-3-plus-other/

js总计