It is common to group and summarize data in a spreadsheet in data statistics. Through data manipulation in Excel, we can group, summarize, sort or filter data. But it is complicated to process data which has been grouped and summarized in an Excel spreadsheet, because we need to perform the operation on data block of each group. In this case, we’d better try the esCalc, which can process data, like sorting and filtering data, conveniently even in a grouped spreadsheet.
Take the following spreadsheet as an example, the employee data has been grouped by the states where employees come from:
In each group, the employee data has been sorted by ID in ascending order. Now it is required to rearrange the data according to the following criteria:
① Rearrange the data of employees of every state by sorting the data by Salary in descending order;
② For each state, only the data of employees whose salaries are in the top two is to be kept.
For the first problem, it is quite easy to re-sort the grouped data in esCalc: Select a cell where there is corresponding data and execute the sorting. We can select any cell in Column Salary, say F10, right-click and select on the menu the Operation->Sort, or click Operation->Sort on the menu bar, and then set the descending sorting order in the sort option window that pops up:
Confirm the information and complete the operation. Or select Desc in Quick operation on the right-click menu:
The resulting spreadsheet is as follows:
As can be seen from the result above, the data of every state has been sorted simultaneously. To sort data, esCalc can intelligently judge the aim of the operation and execute the sorting on all eligible data. Compared with this smart operation, in Excel, data can only be sorted in a continuous area, so there is no easy way to sort data that distributes in different groups.We can only perform the operation by hand group by group, or we can split each group, sort the data as required and regroup it.
Let’s focus on the second problem now. As for data filtering in a grouped spreadsheet, esCalc will still automatically execute the operation on all groups. Please note that the data of the employees who have the same rankings should be taken into consideration during the filtering by ranking. Still, we can select any cell in column Salary, say F5, right-click and select on the menu the Operation->Filter, or click Operation->Filteron the menu bar, and then set the expression for filtering as {F5}.ranki(F5)=2 in the filter option window that pop up:
In the filtering expression, {F5} represents the sequence of salaries of all employees in this state, {F5}.ranki(F5) represents the ranking of this employee’s salary in this state. The whole expression means the salary of this employee is in the top two in this state. Now click Delete and delete the data that is inconsistent with the expression. The resulting spreadsheet is as follows:
It can be seen that only the data of employees whose salaries are in the top two in the state is retained as required. We notice that, in the data of the state of Kansas, Howard Rivera’s salary and Rose Robinson’s salary both rank second. But if the tie is not counted, the ranking can be represented by extending sequence number # and the filtering expression can be written as #
In Excel, however, it is needed to filter data manually group by group. Or, add an auxiliary column, judge if the data in each group satisfy the filtering criterion through an expression and execute the filtering. When there are many groups, the whole operation will become especially complicated.