Sets

A set is the basic concept of batched data. Excel is able to perform aggregate operations and create an association relationship (such as Lookup) based on a set of cell values. However, it doesn’t define the set data type. Sets can only exist during the calculation of a formula, and the set-typed results can’t be stored as cell values. It can’t perform many more set-oriented operations, like intersection, union, difference as well as searching and locating, too.

In fact, sets are a data type frequently seen in the process of analyzing data. Their absence makes many data analysis tasks difficult, even impossible. As a result, Excel users must write VBA code in order to accomplish the tasks. The problem is that is difficult and extremely inconvenient.

esCalc provides the set data type, and also various set functions and operations based specifically on sets, enabling users to carry out complex data analysis tasks. 

Row-wise sets

The following Performance table records performance grade of each employee in each month. We want to find out employees who get at least three A continuously.

As what we do with the preceding example, we perform filter operation on the detail data rows (select row 2 again) according to a filtering expression {A2}.pos(A2)==#. {A2} represents a set consisting of homo-cells of A2 inclusive. Find the position where value of A2 first appears. If the position isn’t where A2 sits, the name is a repeated one and the corresponding row will be deleted.

Column-wise sets

There are same names in the employee name list, we want to delete the rows where extra names stay, while keeping the original order.

As what we do with the preceding example, we perform filter operation on the detail data rows (select row 2 again) according to a filtering expression {A2}.pos(A2)==#. {A2} represents a set consisting of homo-cells of A2 inclusive. Find the position where value of A2 first appears. If the position isn’t where A2 sits, the name is a repeated one and the corresponding row will be deleted.

Expand operation

Transpose the Student Score table:

To a table like this:

We regard the operation that splits and expands one row into multiple rows as the inverse action of grouping. It’s almost impossible to realize this in Excel without turning to VBA, while merely a few steps will suffice in esCalc:

In column E, enter in E2 the formula =[B2:D2] to get sets composed of values in column B/C/D. Thus column E will have sets as its values.

esCalc provides expand operation performed based on a set. Now perform the operation on E2 to expand the row into multiple ones.

Add column F and fill it with subject names using the formula F2=[B2:D2]((#-1)%3+1). Here we get members from a set through their serial numbers.

Finally, delete column B/C/D and switch positions between column E and column F, and complete the headers.