Grouping and summarizing data in sheets is what we often need to do during performing data analysis and statistics. We can group and summarize data in Excel, but actually, it is seldom that this function is used. Reasons for this include several aspects: Excel is strict about the format of data to be grouped and summarized and the styles of sheet after computing are limited; only the same operation can be used in summarizing, for example, summing up data of all columns; besides, it is inconvenient to move data as needed after it is grouped and summarized. With esCalc, however, the operation will become simple and the format will be free.
The following sheet contains data of food stock:
Now it is required to group the data according to the month of production, compute the types and inventory of the foods of each month and then sort the data of each month by the inventory in descending order.
First, compute the month when each kind of foods is produced. To do this, we choose a cell corresponding to the food stock arbitrarily in column E, say E10, and type the expression =month(C10) in it to compute the months of production of all foods:
Then, choose a cell arbitrarily among the data cells of column E after the computation is executed, say E3; choose Operation->Group on the right-click menu or on the menu bar and execute data grouping using default grouping option:
The result is as follows:
In esCalc, the expression for summarizing should be typed in the grouping row to perform the operation of grouping and summarizing. The grouping row is chose arbitrarily, say the 16th row. Type ={B17}.count() in B16 to compute the food types of each month and the ={D17}.sum() to compute the inventory of products manufactured per month. Then type ={B17}.count(), the same expression as typed in B16, in E1 and perform the computation. The resulting sheet is as follows:
We can see from the result that the summarizing result of every grouping row will be computed simultaneously. Yet in E1 and B16, the computed results of the same expression are different: B16 contains the number of food types of each month and E1 gets the number of all food types. This is because the 16throw is the grouping row of each month and the first row is the heading row of all data, and esCalc can identify intelligently the data range in an expression. By comparison, the similar operation will be much more difficult in Excel, though it can choose the type of summarizing before grouping. For the situations where multiple types of summarizing are involved, modification has to be done by hand, restriction of format is strict and the computation is not flexible.
Now choose arbitrarily a cell containing the inventory of a certain month, say D12, choose Operation->Sort on the right-click menu or on the menu bar and execute sorting in descending order which can be set in the sort option:
The sorting result is as follows:
We can see that, in esCalc, the data of each group will move automatically along with the grouping row when data of the grouping row is sorted. In Excel, data of a group should be concealed before sorting is executed on it, which is an advanced technique. In addition, when layers of grouping are required, the operation will be complicated and easy to get wrong.