Data in an esCalc worksheet is stored in hierarchical bands, allowing it to be analyzed through various operations such as grouping, filtering and sorting. This article deals with row expansion. The expand operation allows you to make copies of a record based on a sequence-type cell value. It is used to complete data entry.
1. Single-row expansion
You can perform the expand operation by selecting a cell whose value is a sequence and clicking Operation>Expand on the context menu. The operation will copy the band at the current level multiple times according to the length of the sequence that is a cell value, and assign members of the sequence to homo-cells in every band. Homo-cells of the selected cell will carry out the expansion concurrently. Another approach to perform the operation is to choose Operation>Expand on the menu bar, or use the shortcut key Ctrl+Alt+X.
Here is a simple cellset:
You might want to enter data of five employees in the sales department in the table, so you typed a constant sequence [1,2,3,4,5] in A2. Now select A2 and press Ctrl+Alt+X to perform Expand. Here is the result you’ll get:
As you can see, A2 in the original cellset has been expanded because its value is a set. The sequence in A2 has 5 members, according to which A2’s row has been copied and increased to 5 rows and every one of A2’s homo-cells has received one member as its value. Since the result of the expansion is the creation of homo-rows, the cells in these newly added rows have the same properties such as foreground and background colors as those in A2’s row. During the expansion, the other cell value has been duplicated to every homo-row.
What will happen if there is already a formula before the expansion? Look at the following cellset:
D2’s formula calculates age by generating a random integer within the range of [20,40]. Notice that it is a related formula headed by two equal signs.
You might still select A2 and perform Expand. Here’s the result you’ll get:
You can find that the related calculation cells in the newly generated homo-rows have been given the same formula yet have got their own results. This is because they are homo-cells. If D2 is a normal calculation cell before the expansion, the formulas won’t calculate independently and the homo-cells will end up having the same values. You can enter formulas after the expansion in the cases requiring the use of normal calculation cells in generating random data. About the difference between normal calculation cells and related calculation cells, please see esCalc Basics: Formulas.
2. Band expansion
An expand operation doesn’t necessarily copy one row of data. If the row where the selected cell resides has sub-rows or slave rows, the whole band will be copied. The following table is such an example:
A2 stores names of departments. The department band has a sub-row for storing employee information and a slave row for counting the number of employees. A2’s value is a constant sequence whose members are names of two departments. If selecting the cell to expand it, you’ll get result as follows:
This shows that when the row holding the selected cell has a master row or a slave row of the same level, the whole band to which the cell belongs will be copied during the expansion. On the other hand, you may notice that both results of related calculation cells B4 and B7 are zero. This is because the aggregate function A.count() calculates the number of non-null members in a sequence, which is different from A.len().
Then you can type a sequence consisting of employee names into each department, as shown below:
Now as B3 and B6 have already had data, the counts in both B4 and B7 are 1. Now select B3 to expand it, and you’ll get the following result:
Besides B3 itself, its homo-cell B6 has also expanded at the same time.
3. Multi-cell expansions
You can select several continuous cells in a row to perform the expand operation, as the following cellset shows:
B3 and B6 have been given the sequences of employee names; C3 and C6 have got sequences of states where the employees are settling. Here C3 only has two states and B6 is a single value, instead of a sequence.
Select B3 and C3 together, choose Expand and a dialog box will pop up:
The dialog box provides three ways of expansion: expand by minimum element numbers, expand by maximum element numbers and expand by the selected cell.
If selecting Expand by Min element numbers, you’ll get the following result:
In this case, C3, which has two members only, has fewer members than B3. Therefore the original row has expanded to two rows according to the minimum number of members. In its homo-row, B6 only has a single value Ryan, so the row hasn’t been copied. In every row generated by the expansion, cells are assigned values according to the order of the members in the sequences.
If selecting Expand by Max element numbers, you’ll get the following result:
Between B3 and C3, B3 has more members, which are 3. According to this, the original row has expanded to 3 rows. Between B6 and C6, C6 has two members, which are more than B6. If members in one of the sequences are not enough to fill in the cells generated from the expansion, values of these cells will be null. A single value, such as B6, will be copied.
Suppose you selected Expand by the selected cell, the expansion would be carried out according to the number of members in the selected cell. For instance, select B3 for the expansion and you’ll get the following result:
Between B3 and C3, the expansion has performed according to the number of members in B3 and the original row has expanded to 3 rows. The homo-row, where B6 and C6 sit, has remained one row according to the number of members in B6 – B3’s homo-cell