Common Formulas of Hierarchical Coordinates

The commonly used formulas and descriptions for hierarchical coordinates are shown in the table below:

Formula	Description	Sample Figure
Cellx[!0]	The formula returns the data of all cells expanded from cell x (an arbitrary cell).
Cellx[!0]{Condition Expression}	The formula returns the data of cells expanded from cell x that meet the condition expression.
&Cellx	The formula returns the position of each cell expanded from cell x.
$Cellx	The formula returns the data of each cell expanded from cell x.	Example: When the cell data is in the numeric format, the formula for dividing cell C5 by C10 (both unidirectionally expanded) is $C5/$C10.

Template Creation

Create a general report, as shown in the following figure.

Data Preparation

Create a built-in dataset Embedded1, as shown in the following figure.

Report Design

Enter text in cell A1 and drag the corresponding data column to cell A2, as shown in the following figure.

Insertion of the Hierarchical Coordinate Formulas

Input text Cellx[!0] in cell B1, insert the formula A2[!0] in cell B2, input text Cellx[!0] {Condition Expression} in cell C1, insert the formula A2[!0] {A2!="Banana"} in cell C2, input text &Cellx in cell D1, insert the formula &A2 in cell D2, input text $Cellx in cell E1, and insert the formula $A2 in cell E2, as shown in the following figure.

Effect Preview

(1) PC

Save the template and click Pagination Preview. The following figure shows the effect.

(2) Mobile terminal

The template can be previewed on the DataAnalyst app and HTML5 terminals, as shown in the following figure.

Download the completed template by clicking Common Formulas of Hierarchical Coordinates.cpt.