Use Excel functions to summarize multiple project expenses by category

How to classify and summarize multiple expenses of a project using functions in excel

First, we need to define the problem. You are given a set of raw data with three columns: company, employee name, and age. The company's identification is A, B, C, D, but these data are intertwined and confusing. Our goal is to categorize and aggregate the data by company for better analysis and understanding.

Secondly, sorting data is an important operation before classifying and summarizing data. In the menu bar of the Excel document, click the "Data" option and select the "Sort" function. In the pop-up dialog box, find the "Main Keyword" drop-down menu and select "Company" as the basis for sorting. After completing the settings, click the "OK" button to perform the sorting operation. This ensures that the data is arranged in the order of the company and facilitates subsequent data classification and summary work.

Third, sort the results. After performing the second step, you can see the sorting results of the data, as shown. Obviously, the data arrangement is much clearer than the original data arrangement. The company names are arranged in order A, B, C, and D. Perhaps, you will feel that this is enough, but when the amount of data is large, this is far from enough.

Fourth, data classification. At this time, still select "Data" in the excel menu bar, and then select "Classification and Summary". In the pop-up dialog box, the classification field is "Company Name", the classification method is "Count", and the summary item is selected. Select "Company Name", keep other options as default, and click "OK".

Fifth, classification results. As shown in the figure, the results of the classification and aggregation are very beautiful. The data is not only arranged in order A, B, C, and D, but also gives the number of employees in each company, which are 5, 4, 5, and 6 respectively, for a total of 20. When the amount of data is large and there are many projects, the above method can be used for effective classification and summary, which is very convenient.

How to perform classification and summary in excel

If the data is in column A, then the statistics are 0-59. Enter the formula: =COUNTIF(A$1:A$100,"

60-69 =COUNTIF(A$1:A$100,"

70-79 =COUNTIF(A$1:A$100,"

80-89 =COUNTIF(A$1:A$100,"

90-100 =COUNTIF(A$1:A$100,"

If the data range is there, just change A$1:A$100 in the formula to another range.

Please adopt the above.

How to classify and summarize data in excel

1. First sort the data by the column that needs to be classified and summarized (in this case, the "City" column).

Select any cell in the "City" column and click the sort button in the toolbar such as "A→Z" in Excel 2003. In Excel 2007, select the "Data" tab in the ribbon and click the "A→Z" button in the "Sort and Filter" group.

2. Select a cell in the data area and click the menu "Data → Subtotal" in Excel 2003. If this is Excel 2007, on the Data tab, in the Outline group, click Subtotals.

3. In the pop-up "Classification and Summary" dialog box, select "City" under "Category Field" and select a certain summary method in "Summary Method". The available summary methods are "And", "Count", "Average", etc. In this example, the default "Sum" is selected. Under Selected Summary Items, select only Sales.

4. Click OK and Excel will classify and summarize by city.

The above is the detailed content of Use Excel functions to summarize multiple project expenses by category. For more information, please follow other related articles on the PHP Chinese website!