在Excel数据透视表中显示排名

最新推荐文章于 2024-09-27 00:45:01 发布

culiao2169

最新推荐文章于 2024-09-27 00:45:01 发布

阅读量1.3k

点赞数

文章标签： java python 大数据数据分析数据库

原文链接：https://contexturesblog.com/archives/2015/07/02/show-rank-in-excel-pivot-table/

版权

A pivot table is a great way to quickly summarize number, to see how your business is doing. But sometimes those numbers are too large to analyze in your head, so let the pivot table give you a bit of help, with the pivot table Rank calculation.

数据透视表是一种快速汇总数字，查看业务状况的好方法。但有时这些数字太大了，无法在您的脑海中进行分析，因此让数据透视表通过数据透视表Rank计算为您提供一些帮助。

Add Rank to Excel Pivot Table http://blog.contextures.com/

比较排序字段 (Compare Sorted Fields)

Last week, I was looking at my website statistics in a pivot table, and had one field with the number of views per page. Then, I added some revenue data, to see which pages were the most profitable. I could sort by either field, but it was difficult to try and match them up – were the pages with the most views also the most profitable pages? Once the numbers are more than 2 digits, it's hard to sort them in your head. Well it is for me anyway!

上周，我在数据透视表中查看了我的网站统计信息，并且在一个字段中列出了每页的浏览量。然后，我添加了一些收入数据，以查看哪些页面最赚钱。我可以按任何一个字段进行排序，但是很难对其进行匹配-包含最多视图的页面还是利润最高的页面？一旦数字超过2位，就很难对它们进行排序。好吧反正对我来说！

To show you what I mean, I created an example with fake data, for food product sales. If we sort the pivot table to see the products with the highest sales, does that match up with the number of cases sold?

为了说明我的意思，我创建了一个包含虚假数据的食品销售示例。如果我们对数据透视表进行排序以查看销售额最高的产品，这是否与售出的案例数量相符？

In the screen shot below, the Cases seem to be in a similar order to the Sales $ numbers, but it's hard to tell for sure, at a quick glance.

在下面的屏幕快照中，“案例”的顺序似乎与“ Sales $”的顺序相似，但是很难一眼就能确定。

使用排名计算 (Use the Rank Calculation)

To make it easier to spot an differences in the order, I added another copy of the Cases field, between the two existing value fields. Then, I changed that field's Show Values As setting, to use the Rank Largest to Smallest calculation.

为了更容易发现顺序差异，我在两个现有值字段之间添加了Cases字段的另一个副本。然后，我更改了该字段的“将值显示为”设置，以使用“从最大到最小的排名”计算。

In the revised pivot table, it is much easier to spot any differences in the order of Cases versus Sales $. It's very easy for us to sort the numbers 1-10 in our head!

在修订后的数据透视表中，发现案例与销售$顺序之间的任何差异要容易得多。对我们来说，很容易将1-10之间的数字排序！