Users often need to perform set operations in Excel, which, however, doesn’t directly support data of set types and thus cannot deal with set operations like intersection and union. The operations can only be realized using lookup functions like VLOOKUP or through special techniques, with great difficulty though.
esCalc supports explicit sets and can handle set operations with ease. For example, we are asked to make statistics of the insurance policies in the past six months and select salespersons whose insured value has been ranked in the top 5in each month. Suppose the data have been grouped by the month, each salesperson’s monthly sales amount has been computed and each group of data has been sorted according to the sales amount, as shown below:
First we need to select employees whose sales amount is in the top 5 for each month. This is a kind of filtering operation based on sets, which means selecting the top 5 pieces of data from each set. In esCalc, execute “Filter” once and all is done, as shown below:
The result is as follows:
Since Excel doesn’t support sets and the related sequence numbers, we cannot perform the computation directly. The data need to be converted into computed columns for computing, that is, row numbers (generally represented by row()) will be used as the sequence numbers of members in a set. Note that the title row should not be counted. D3’s row number, for instance, should be “row()-2”. Besides, at the joint of two groups, the count needs to be cleared to ensure that the row number of the first piece of data in the next group should be 1. Apparently this operation is too complicated to be expressed in Excel.
Let’s go back to esCalc to continue the computation of selecting the 5 employees whose sales amount has been ranked in the top 5 in each month. This is another type of set operation – the intersection of multiple sets. Enter the formula representing the sets into D2, as shown below:
Then enter the formula computing the intersection into D1 and the result will be displayed in the same cell, as shown below:
It can be seen that esCalc’s solution is very simple, requiring little effort.
Excel doesn’t support explicit sets and cannot compute intersection directly. A special technique needs to be used to realize the second step above. The method is this: Compute the number of times each employee appears. If the value is 6 (which means six months), the employee is eligible. But using this method requires dismantling the groups, which is difficult to understand for business staff. Moreover, regrouping data means that the original sorting result, summary values and row numbers in each group will become useless and cannot be reused for later data processing. In sum, it is a bad choice.