This issue adds another small example to the usage of COUNTIF and COUNTIFS, that is, the "conditions" of these two functions can also be expressions.
Assumption: It is necessary to count the number of people who passed each subject and scored ≥ 120 in the table below, and the number of people who scored ≥ 120 in all three subjects.
The statistical results are as follows:
Let's get to the topic of this issue: PivotTable.
According to the specific needs, the summary data table can be sorted by category, which is convenient to extract the most useful information from a variety of data, so as to analyze the most concerned data items.
It also takes the combing of patent information as an example to illustrate.
Suppose you want to count the following information from the patent information table:
(1) Number of patent applications by region (base);
(2) The number of patents filed each year from 2017 to 20 19;
(3) The patent distribution of each base in each state.
Step one:
Select the data area with the left mouse button, click the Insert menu in the menu bar, and then click Pivot Table under the Table tab to open the Create Pivot Table window.
Step 2: Choose the location where you want to put the PivotTable report.
If you select "New Worksheet", a new worksheet will be created to place the PivotTable report;
If you select Existing Sheet, you need to select a specific location in the current form.
Step 3:
Drag Cardinality to the Line field, drag Quantity to the Value field, and change the calculation type to Count in the Value field settings.
The first two steps are the same as above.
In the third step, drag the application year into the line field dragNo. Go to the value field and modify the calculation type in the value field settings to count.
The first two steps are the same as above.
Step 3: drag Base to the row field, drag Status to the column field, and drag "No."to the value field, and modify the calculation type to: count in the value field setting.