How to use excel formulas to classify and summarize data

Briefly introduce the two types of using formulas to complete classification and summarization.

Method: Hypothesis: You want to build a list of monthly transactions where each account** can occur more than once.

At the end of the month, you need to organize and segment your data by account**. The account number in the sample dataset is in column A, and the amount is in column B. The data region is A2:

b100, the data is currently unsorted.

Method. 1: Use creative.

if statement and "paste special".

The steps are as follows: 1. Sort the data by account (column a).

2. Create a formula in column C to continuously dynamically summarize each account. For example, create a formula in cell C2: =IF(A2=A1,C1+B2,B2).

3. Create a formula in column d to identify the last entry for a particular account, for example, create a formula in cell d2: =if(a2=a3,false,true).

4. Copy the formula in C2:D2 down to all rows.

5. Copy c2:d100. While this area is still selected, click Paste Special on the menu, click Numeric, and then click OK to change the formula in the C2100 to Numeric.

6. Sort by column d, in descending order.

Result: For rows with a value of true in column D, column A is a unique list of account numbers, and column C is the final dynamic summary of accounts.

Method 2: Use the "Combine Calculations" command.

With the Combine command, there are several requirements: the account number must be to the left of the numeric field to be summarized. There must be a header above each column.

You need to specify a region name for a rectangular block of cells that includes the account number in the left column and the top header. In this example, the region is a1:b100.

1. Highlight area A1:B100.

2. Specify a zone name for this area by clicking in the name box (on the left side of the bar) and typing a name such as totalme. (You can also click Name on the Insert menu.) ）

3. Place the cell pointer in the blank part of the worksheet.

4. On the Data menu, click Combine Calculations.

5. In the Reference Location box, type the name of the region (totalme).

6. In the "Flag Position" section, select "First Row" and "Left Column".

How to classify and summarize in Excel.

Hello, glad to answer for you.

In Excel, subtotalization can usually be achieved using sumif or sumifs functions. These two functions can summarize the data based on the specified criteria. The specific formula is as follows:

sumif function: used to summarize a single condition.

sumif(range, criteria, [sum_range])

Among them, range is the data range to be judged, criteria is the judgment condition, and sum range is the data range to be summarized.

For example, if you want to summarize the data in column A, the condition is that the data in column B is equal to"Apples", you can use the following formula:

sumif(b:b,"Apples",a:a)

sumifs function: used to summarize multiple conditions.

sumifs(sum range, criteria range1, criteria1, [criteria range2, criteria2], where sum range is the data range to be summarized, criteria range1, criteria range2 and so on are the data ranges of the judgment conditions, and curved criteria1 and criteria2 are the judgment conditions. You can bury the old difference to separate multiple conditions using a comma.

For example, if you want to summarize the data in column A, the condition is that the data in column B is equal to"Apples", the data in column c is equal to"Red", you can use the following formula:

sumifs(a:a,b:b,"Apples",c:c,"Red")

In Excel, you can use the sumifs function to sort and summarize. The basic syntax is as follows: =sumifs(summary domain, categorical domain 1, condition 1, categorical domain 2, condition 2...

Categorical field: A field used for classification, which can have multiple categorical fields and be summarized according to the classification ladder.

Condition: The condition that needs to be met by the classification domain to extract the data of a classification. For example, there is the following**: Product Sector Sales.

Computer Sales Department 200

Computer Marketing Department 300

Mobile phone sales 400

Mobile Marketing Department 500

Tablet R&D Department 600

To count the computer sales of each department, you can use the following formula: =sumifs(b2:b6,a2:a6,"Computers",c2:c6,"Sales Department")

sumifs(b2:b6,a2:a6,"Computers",c2:c6,"Marketing Department"The result is a cover:

300 In the same way, to count the sales volume of each product in the sales department, you can use: =sumifs(B2:B6,A2:A6,"Computers",c2:c6,"Sales Department")

sumifs(b2:b6,a2:a6,"Mobile phones",c2:c6,"Sales Department")

sumifs(b2:b6,a2:a6,"Flat plates",c2:c6,"Sales Department"The result is the wheel of repentance:

0Therefore, the sumifs function can realize the aggregation of data according to multiple classification standards, and only need to set each classification field and condition correctly. There can be multiple classifications to achieve multi-level classification and summary. This is a very useful tool in excel data analysis and report making.

Copy the first three columns to the G:I column, deduplicated.

The last column of the multi-ply relatives condition the sum of the letter to the number of sumifs to solve.

First, classify according to a certain standard, and then summarize the relevant data of each category on the basis of summing, averaging, numbering, maximum, and minimum on the basis of classification.

How to classify and summarize in Excel.

How to classify and summarize data in Excel.

Before doing the classification and summary, you need to sort the data first, otherwise you can't classify and summarize, as follows: if we want to classify and summarize the material code, you can sort this column first.

Select column D data, select ascending or descending order to sort the data under the Insert-Sort and Filter commands, and make sure that "Expand Selected Region" is selected in the "Sort Reminder" dialog box, so that the data of the same companion is still sorted in the same row.

The sorted result is as follows:

Click any cell in the data region, and click Classification Summary in the Classification Display group of the Data tab, and the Classification Summary dialog box will pop up, and the system will automatically select all the data.

In the Classification Summary dialog box, the classification field represents the field to be classified and summarized, and the summary method can be selected as count, sum, average, etc., and the summary item is selected: the data to be summarized according to the actual situation. The fields actually selected here are shown in the following figure.

After that, select OK.

The result at this time is as follows:

You can see that it is displayed in the upper left corner of the data

You can click on different numbers to display the summary results of different levels. If you no longer need to summarize the results, you can click Delete All in the Classification Summary dialog box.

How to classify and summarize in Excel.

How to classify and summarize data in Excel.