Office Hacks: Excel data reporting is faster – that is, pull the statistics

Usually we often use Excel statistics, if the statistics of more categories, in querying the statistical results will need to laboriously find and sort in the list. Take the following table as an example, if you want to statistical PVC-1 product sales and, because there are more than one department sales, the summation will need to sort the products first, and then summation. After re-sorting will destroy the original table arrangement, and each query a product also need to re-sort, summation, the operation is very inconvenient. Now we can use the “drop-down list of data validation + statistical summation” to solve this problem, so that you can quickly see the statistical results by selecting the category in the drop-down list (Figure 1).

202006jljb1

Figure 1 Dropdown + Statistics Example

As you can see from the sample diagram, this combination is mainly composed of “data validation” + “summation”, so to achieve this effect, we need to integrate the corresponding categories according to the original data in the “data validity Therefore, to achieve this effect, we need to integrate the corresponding categories in the “Data validity” drop-down list according to the original data, and count the sum of the corresponding values.

First set up the data validity drop-down list, because here there are multiple departments selling the same product (for example, Sales 1 and Sales 2 both have sales of PVC-1). In order to facilitate filtering, the first table into a dynamic table, select all the contents of the table, click “Insert → Form”, switch to “Form Tools → Design”, check the “title line “, “border line”, “filter button” (Figure 2).

202006jljb2

Figure 2 conversion form

Then copy the contents of cell D2: D25 to M2: M25 cells, enter the “series” in cell M1, select the contents of cell M2: M25, click the menu bar “Data → Remove Duplicates”, in the open window In the window that opens, check the “Select All” and “Data contains the title”, click “OK” (Figure 3).

202006jljb3

Figure 3 Remove duplicate products

After the above operation, the data of duplicate products will be deleted automatically, and only the unique product values will be retained, which can be used as serial data for data validity (Figure 4).

202006jljb4

Figure 4 Retain unique product values

positioned in cell J1, enter “Select Query Product”, cell K1, enter “Sales”, then positioned in cell J2, click the menu bar “Data → Data Validation → Settings “, select “Series” in the list of allowed, click on the data source after “Source”, then select “M2:M6” data (that is, the above de-duplicated (i.e., the above de-duplicated sequence data), click “OK” to complete the settings (Figure 5).

202006jljb5

Figure 5 data validation settings

Now from cell J2 to expand the drop-down list can be selected in turn the above product content. The next step is to set the summation value in cell K2. Summation with the SUMIF function to complete, positioned in cell K2 enter the formula “= SUMIF (Table 1 [product], J2, Table 1 [amount])”, when we select the product in the drop-down list in cell J2, in cell K2 will automatically display the corresponding amount, so that the query data is obviously much more convenient a lot easier to query the data (Figure 6).

202006jljb6

Figure 6 summation function settings

Tip.

the above formula, “Table 1 [product]” parameter is to sum the range of conditions is in the “Table 1 product field column”, where the “Table 1” is the above implementation “Insert → Table” operation in the default name of the dynamic table (switch to “Form Tools → Design → Table Name”, you can customize the settings). The parameter “J2” indicates the condition that the summation condition is the specified product shown in J2 in the product sequence (the summation condition will be changed dynamically with the selection of the drop-down list). The “Table 1 [Amount]” summation range is the “Product Amount column of Table 1”, i.e. the summation of the amount of the corresponding product in column H after selecting the product in J2.

Because we are using a dynamic table (summation conditions and range is set by the form of the field), after completing the above settings, if you need to add data later, for example, in cell A26:H26 added PVC-6 sales data, then the summation in cell K2 will also be synchronized to change.

SUMIF is the summation of a single condition, if the summation of multiple conditions, we can also use SUMIFS to complete. Suppose now you need to query both the department and the specified product sales data and, for example, query sales of a PVC-1 sales data. As above in cell I1, enter “departmental query”, in cell I2 and then set up a data validation sequence (the content of the sequence for sales of a sales department to sales department three). Positioned in cell K2, enter the function “= SUMIFS (Table 1 [amount], Table 1 [department], I2, Table 1 [product], J2)”, you can simultaneously query the department and product two conditions (Figure 7).

202006jljb7

Figure 7 Multi-conditional summation query

Tip.

The parameter “Table 1[Amount]” means that the “summation range” is the value under the field of Table 1[Amount], and the parameter “Table 1[Department]” means that the range of the condition is the field of [Department]. The parameter “I2” indicates that the summation condition is the specific department displayed in the cell, and the parameters “Table 1 [Product], J2” correspond to the range of [Product] field, respectively, and the summation condition is “J2 The summation condition is “J2” which shows the product name. If there are more than one condition, continue to add “condition range”, “condition” parameters can be added, for example, you can add “Table 1 [Order ID],N2” to increase the product corresponding to the order ID query.

If there are many departments and products, you can enter the “Data → Data Validation → Settings”, select “any value” in the list of allowed, so as long as the I2 and K2 cells to enter their own department and product values can be queried. If the statistics of many reports, we can create a new worksheet dedicated to query, the same as the original data in each worksheet insert a dynamic table, such as in Sheet2 insert “Table 2”, and so on. Then as long as the “query table” in cell C3 enter the formula “= SUMIFS (Table 2 [amount], Table 2 [department], A3, Table 2 [product], B3)” (need to query which table data, here the table name and field (modified to the corresponding name), you can very easily query all the data in a dedicated table of other worksheets (Figure 8).

202006jljb8

Figure 8 Dedicated Query Table

QQ图片20210420165237 

Leave a Comment