esCalc Structure: Homologous Relationship

Course 1225 0

Some data in a spreadsheet contains the same type of topics. Through the structural and hierarchical way, esCalc creates homologous rows and cells – which are called homo-rows and homo-cells – containing data of the same information type. Here’s the explanation of what this homologous relationship is and the advantage of its design.

1. Homo-rows and homo-structure

First look at the city population table:


You can see that in this table data in some rows is of the “same category of topics”, such as the second, seventh and twelfth row, which contain state names, and the rows including 4, 6, 9, 11, 14, 16 and 18 store the population estimate in a certain city of a certain state in 2014.

By studying the second to the eighteenth row, you may find that the first row of each band of state data contains the state name, followed by sub-rows of city data. The data of each city is stored in two slave rows of level 2, respectively recording 2010 population census result and population estimate for 2014. Despite the different numbers of cities in different states, the city data is stored in the “same structure”. In esCalc, rows with this type of “same structure” described abstractly with the hierarchical structure are homo-structure rows. They are rows having sub-rows where each band contains the same number of slave rows, and at the same time, the sub-rows of each of them have the same structure, such as the second, the seventh and the twelfth row respectively containing data of the state of Florida, Illinois and California. All their sub-rows store city data and there are two slave rows in each band in each group of sub-rows that haven’t their own sub-rows. So they are of homo-structure. Note that it is the number of slave rows in each band of each group of sub-rows, rather than the number of sub-rows, that counts in judging whether rows are of homo-structure. This is why the three rows just mentioned can be called homo-structure rows even though the state of California has data of three cities while the other two states have data of two cities.

According to the definition of homo-structure, you may find that the nineteenth row also has sub-rows where each band has two slave rows and that don’t contain rows of inferior level. In this sense, it and the three rows mentioned above are homo-structure rows, even if they are not of the same level. With the same structure, the type of information in the nineteenth row is not the city data but the top 3 cities with the largest population.

esCalc uses homo-rows, the more specific word, to refer to rows holding data of the “same information type”. They are the rows having the same slave row number, of the same level, with the same parent row satisfying the same criteria, if described by the hierarchy. The homo-rows must meet the standard for homo-structure.

Here is an example. Among the above homo-structure rows – 2, 7, 12, 19, the first three are of level 1, with the same slave row number – 1 and the same parent row – the first row. Therefore they are the homo-rows. The nineteenth row is of level zero, with the slave row number being 2 and no parent row. It is not the homo-row to the other. Actually as a row of level 0 does not have a parent row, it does not have a homo-row either. Similarly, the rows, including 4,6,9,11,14,16 and 18, are of level 2, with the slave row number being 2 and homo-rows being their parent rows are homo-rows too. Note that the fourth row and the sixth row are homo-rows, but the fourth row and the third row are not. Because their slave row numbers are 1 and 2 respectively. There are other homo-rows in the table above, like the twentieth, the twenty-second and the twenty-fourth row.

You have learned from esCal Structure: The Hierarchy of Rows that a master row is a row with 1 being its slave row number. Master rows that are homo-rows are called master homo-rows, such as rows 2, 7, 12, and rows 3, 5, 8, 10, 13, 15, 17. The bands where master homo-rows reside are homo-bands, such as the following three bands – the second to the sixth row, the seventh to the eleventh row and the twelfth to the eighteenth row – which record population data of the state of Florida, Illinois and California respectively.

Below is a more complicated table:


Here the city population is recorded by states, and population census of 2010 also involves the percentage of population under the age of 18 and the percentage of female population. And at the end of the city data a slave row is added to hold the change of the population from 2010 to 2014.

Now look at if the second row and the eighth row are homo-structure rows. First, each of them has sub-rows containing three slave rows, in which the sub-row whose slave row number is 1 also has its own sub-rows that contain two slave rows. So the second and the eighth row are homo-structure rows. Likewise, the third, the ninth and the fourteenth row, the sixth, the twelfth and the seventeenth row, the fourth, the tenth and the fifteenth row are three groups of homo-structure rows.

But are the second and the eighth row the homo-rows? Both are rows of level 1 with the slave row number being 1 and the parent row being the first row, so they are homo-rows, as well as master homo-rows. The bands where they reside – rows from the second to seventh and rows from the eighth to eighteenth – are homo-bands, which are outlined in red. In the same way, you can know that the third, the ninth and the fourteenth row are master homo-rows, the bands where they reside are homo-bands holding population data different cities and outlined in blue. Equally, the population percentage rows in the 2010 population census for each city are also homo-bands, which are outlined in green.

2. Merged cell

esCalc allows merging cells. Of course you cannot merge neighboring cells at will. To merge cells in esCalc, two conditions concerning the structure must be met. One is that cells to be merged must be within the band where its first row resides; the other is that all the homo-cells of any one of the cells to be merged must be included in these to-be-merged cells.

The first row of the merged cell is called its dwelling row. Except for the cell used for merging, other cells in the first row will not use more than one row, so the dwelling row of the merged cell is the row where these other cells stay. The merged cell is represented by the first cell of the dwelling row, the principal cell.

To set a merged cell, select cells to be merged and click on the buttonon the tool bar, or click Edit>Merge on the menu bar. Thu the merged cell is created. A second click on the icon or Edit>Merge will split the merged cell. Below is such an example:


In esCalc, merging cells in the same row is free of the rules for a merged cell, such as A1 and A15.

Then the merged cell A10 will be used as an example to explain the above rules for merging cells crossing multiple rows.

About the first rule: The dwelling row of the merged cell A10 is the tenth row, which resides in a band from the tenth to the fourteenth row. So cells to be merged must be within this range. This explains why the merging does not work if you want to merge A10 with A15, for the latter is beyond the limit of the band to which the tenth row belongs.

About the second rule: Among the rows of the band where the merged cell A10 resides, those from the eleventh to the thirteenth are homo-rows. Including any one of them into the merged cell means the other two rows must be also included. That is to say, either all the three homo-rows – 11, 12 and 13 – are included, or none of them is included. The merged cell A10 in our example covers cells from A10 to A14, involving all the three homo-rows. It will work to try to merge cells from A10 to A13. But merging will fail if you just merge cells from A10 to A12 and do not include A13.

What if you try to merge the three cells, A11, A12 and A13? In this case the dwelling row where the merged cell resides is, instead of the tenth row, the eleventh row, which belongs to a level 2 band comprising a single row, and both A12 and A13 are beyond the limit, so the merging would fail.

3. Homo-cells

In the homo-rows, the cells that belong to the same column are homo-cells. Homo-cells use the same formula and have the same properties. When properties and formulas are set in esCalc, those in the homo-cells will change accordingly. And, an operation of setting a merged cell will be performed in all homo-bands. Across several homo-bands, any cell of the same level with the band has its homo-cells.

You would know better about the concept of homo-cells through the following esCalc table:


You’ve learned from the first section that the second and the eighth row are homo-rows, as are the rows 3, 6 and 14, and the rows 7, 13 and 18. The cells in the same column in homo-rows are homo-cells, such as A2/A8, A3/A9/A14, B7/B13 /B18, and C7/ C13/C18.

In esCalc, homo-cells have the same properties, like foreground/background color, font, font size, font style, font color and display format. If any of these properties for a certain cell is modified, this property of all its homo-cells will change too. For example, to change the background color for B7 and C7 in the above table to light blue, and merge cells from A2 to C2 and those from A3 to A7, and type formula =C6-C3 into C7 to calculate the change of the population in Chicago city. Here’s the result you’ll get after the modification:


You see that when the appearance property of a cell is modified, its homo-cells always keep up with it. That’s also the case when cells are merged. The merged cells are still homo-cells because the rows and columns they use are handled according to the principal cell, thus nothing has changed with regard to their relationship.

Here one thing worth your special attention is the formula entered to C7. Though similar to the property setting it is set for homo-cells of C7, the results of these homo-cells are different from each other. Click on C13 and C18, you can find that their formulas are =C12-C9 and =C7-C14 respectively, which means formulas for homo-cells are not exactly the same, but will automatically adjust according to their locations. The cells referenced in the original formula turn themselves into the corresponding homo-cells in the working band. More about the intelligent copy of formulas will be covered in esCalc Structure: The Smart Copying of Formulas.

4. Master cell

For a band, you can specify some cell in the slave row that is of the same level with the band as the master cell. A master cell is unique across the band. The master cells of the homo-bands are homo-cells. In esCalc if a master cell in a slave row is specified, all its homo-cells will be set as the master cells automatically.

To set a cell as a master cell, or to cancel a master cell setting, right-click the cell and select Cell>Set/Cancel master cell in the context menu, or press the shortcut key Ctrl+M at the same time. Set A2 and A3 in the preceding table as the master cells, for example, and you’ll get the following result:


Once a cell is set as the master cell, a little triangle will mark it at the top-right corner. Usually the triangle is blue, but its color will change according to the background color of the cell. You must have noticed that when you specify a cell as the master cell, all its homo-cells will become master cells as well, such as A9 and A14, which follow A3 to be set as master cells.

The master cell is unique across the band where the slave row resides. If you set master cell for a band that already has one, the original one will be invalidated.

You need the master cell when performing operations like aligning, grouping, joins, merge in an esCalc cellset. Documents on esCalc data manipulation will cover details.

Leave a Reply

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website