Shulou(Shulou.com)06/02 Report--

Ranking analysis is a common analysis method, which mainly allows users to understand the order of data records under the current business dimension through ranking, and is mostly used for performance evaluation and comparative analysis. Although the ranking requirements look very simple, but the actual operation will find that there are often not easy to achieve, this is because the ranking of the pattern is really many. Today, I will take you to play the rankings, open plug-in minutes to get the demand off work to wave!

Generally speaking, common ranking methods have the following categories: single-level ranking, de-ranking, horizontal ranking, multi-level ranking, etc. The following is a detailed explanation of how various rankings are easily handled by plug-in software--dry reports.

I. Single-level ranking

Single-level ranking is also called inter-group ranking, that is, ranking the numerical value of a field, for example, ranking the total order price of each city. The effect diagram is as follows:

The specific implementation operations are as follows:

Among them,

A2:=ds1.group(owner city, owner city:1) group data by city

B2:=ds1.sum(unit price * quantity) Calculate the sales volume of each city

C2:=count(B2[`0]{B2>$B2})+1 Perform city ranking analysis according to the total order price, that is, count the number of cities whose total order price is greater than the total order price of the current cell. The number of the first place is 0, so 1 needs to be added.

This is a single-level ranking analysis, isn't it so easy?

As expected, some students raised their hands and said that the fourth place repeated (Guangzhou and Shijiazhuang), so if you need to achieve re-ranking, what should you do?

Second, to re-ranking

As the name implies, to re-ranking is not repeated in the ranking, if the same ranking occurs, the ranking behind plus 1.

Very simple, we only need to add a column of assistance column processing can be, as shown in the following figure:

Added assistance column:

C2 Expression: =row() Gets the row number of the current cell

E2 Expression: =D2+count(B2[`0]{D2==$D2&&C2$C2})+1 Calculate the total price ranking of orders in the same region

E2:=count(C2[`0]{C2>$C2})+1 Calculate total order price ranking in all regions

Count(C2[A2]{C2>$C2})+1 counts the number of cities in which the total order price in a certain area after cell A2 is expanded is greater than the total order price in the current cell, and then adds one to achieve the effect of multi-level ranking of the total order price.

recalling

Well, at this point, the common ranking requirements effect has been introduced, let's review the problems encountered and the skills to solve them:

Cell positioning:

Tip: We used expressions like "count(C2[`0]{C2>$C2})" in many places earlier. In the dry report, this expression has a formal name: hierarchical coordinate notation: cellx[`0, Lk:lk]. By using hierarchical coordinate notation, we can locate a particular cell, where `0 represents the root coordinate.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.