Office hacks: using good wildcards Merge statistics do not worry

A company wants to average the salary statistics of each office, for example, Sales 1, Sales 2 and Sales 3 are combined by Sales, and Production 1, Production 2, Production 3 and Production 4 are combined by Production, such statistics can be achieved if you use classification summary, but there are certain complications. If you add wildcards to the statistics, you can easily achieve it without sorting and summarizing.

First, in the blank space of the worksheet where the data source is located, enter the name of the field for which statistics are needed, to be consistent with the field name of the data source, in this case, enter “department” in G1, and “salary” in H1; then, in G2, enter “Production*” and “Sales*” in G3; select G1:H3, select the “Data” tab, click “Combine Calculations Select G1:H3, select the “Data” tab, click “Combine Calculations”, select “Average” at the “Function” pop-up window, select the data source corresponding to the entered field name and the data below it at the “Reference Location”, and select the data source corresponding to the entered field name and the data below it at the “Function” pop-up window. In the “Reference Location”, select the data source corresponding to the field name entered and the data below it, and in the “Label Location”, check the “First Row” and “Leftmost”, and then calculate the average salary of the corresponding department (Figure 1).

1820A-TPTJ-1

Finally, change the input “Salary” field name to “Average Salary” and delete the “*” from the input department.

Tip.

The “*” in the input department name is a wildcard character, representing any number of characters; at the same time, the results calculated by this statistical method will not follow the changes of data in the data source.

Speaking of wildcards, in addition to the “*”, there is also a “?” (English half-centers). “?” stands for any 1 character, and it can also be applied to categorical statistics. For example, to count how many people have two characters in their name and how many people have three characters in their name, you can use “?” to achieve this. Add a secondary column to the right of the “Name” field in the data source, the field name is “Count”, and the values below it are filled with “1”; similarly, in the blank space (such as F1, G1) Enter “Name”, “Count” two field names, in the input “Name” field under the column (such as F2, F3) respectively enter “? ” , “?????” Select F1:G3, click “Combine Calculations”; select “Count” in the pop-up window “Function”, and select “Reference Location Select “A1:B17” at the data source, check “first line” and “leftmost” at the “label position”, and then calculate the number of people with How many people have two characters and how many people have three characters (Figure 2).

1820A-TPTJ-2

As already mentioned above, this merge calculation method is simple to operate, but one of its drawbacks is still obvious, that is, it does not follow the changes of data in the data source. To solve the above problem, you also have to use SUMIF, AVERAGEIF, COUNTIF and other functions, these functions support wildcards, are able to classify and merge statistics. For example, enter “=AVERAGEIF($B$2:$B$17,G2,$C$2:$C$17)” at H2 and fill down to H3, which can also combine the average salary of the corresponding department. This function method, when the data source of the data changes, the statistical results will also change. Only, it may be a little difficult for those who are not familiar with the function (Figure 3).

1820A-TPTJ-3

Leave a Comment