层次坐标入门视频：层次坐标

层次坐标进阶视频：层次坐标进阶

层次坐标是实现复杂报表的一个重要概念。在进行报表设计时，如果涉及到了扩展单元格的计算，由于单元格尚未进行扩展，那么该如何表达扩展后数据的单元格位置进而用于计算公式的书写呢？

例如：A1 单元格扩展成 A1-A5，如果某个单元格的值希望对扩展后的 A2 和 A3 进行求和。在扩展前，表达式很难描述这样的关系，而对于扩展后单元格的唯一性的定义，就需要使用到单元格的层次坐标。

简单来说，扩展单元格运算时需要使用层次坐标来表示单元格扩展后的表格位置，进而方便运算公式的书写。

层次坐标有两种类型，分别是「相对层次坐标」和「绝对层次坐标」。下面将详细介绍它们的概念和使用方法。

2.1 相对层次坐标

许多时候，报表设计者并不知道目标单元格的具体位置，仅仅知道目标单元格相对于当前单元格的位移，因此给出了位移坐标。

位移坐标的运用非常广泛，例如报表中常常需要计算同期比、比上期之类的与时间相关的数据，而这些运算往往需要用到下一行的数据减上一行数据，后一列数据减前一列数据等等。这种涉及到相邻n行或者n列的行间、列间的运算，称为位移运算，相关的表达式称为位移表达式。

相对位移坐标是用来描述目标单元格和当前单元格之间的位置关系的表达式。概念图如下图所示：

注1：Cellx表示需要返回结果的单元格，Celly表示位移时参考的单元格。Cellx[Celly:-z]的运算逻辑：记录位移前Celly和Cellx的对应关系,记为关系1，当Celly完成-z个位置的位移后，按照‘关系1’返回对应的Cellx。

注2：相对后移需要使用“-”符号，相对前移需要使用“+”符号。

注3：z 代表相对位移的位置。

2.1.1 坐标规则

相对层次坐标定位规则如下图所示：

规则说明：

• Cellx：指目标单元格，是 Lk,Lk-1...L1;Tk,Tk-1...T1 的附属格。

• Lk：指某个目标单元格 Cellx 的左父格，Lk 的次序是从远到近的，也就是从离当前格最远的左父格开始，也可以理解为从最高级别的分组格开始。

• Tk：指某个目标单元格 Cellx 的上父格，Tk 的次序是从远到近的，也就是从离当前格最远的上父格开始，也可以理解为从最高级别的分组格开始。

• lk：指左父格 LK 扩展后的次序，即扩展后的第几个单元格，如果不指定 lk 或 lk 为 0，则表示为当前表达式所在单元格所属的当前左父格 LK。

• tk：指上父格 Tk 扩展后的次序，即扩展后的第几个单元格，如果不指定 tk 或 tk 为 0，则表示为当前表达式所在单元格所属的当前上父格 TK。

注1：如果只有左父格没有上父格，分号可以省略，即Cellx[Lk:±lk,LK-1:±lk-1,......,L1:±l1]。

注2：如果只有上父格没有左父格，分号不能省略，即Cellx[;Tk:±tk,Tk-1:±tk-1......,T1:±t1]。

注3：当相对引用小于-1，即-2，-3，-4，...等时，扩展数据可能出现循环相对。必要时需要用其他条件进行过滤。

2.1.2 示例

1）A1 单元格插入公式range(5)，B1 单元格插入公式A1[A1:-1]。根据相对层次坐标的概念，A1 单元格扩展之后的数据相对后移一位显示在 B1 单元格中。

注1：A1 单元格需要纵向扩展。下面同理设置。

注2：B1 单元格也可以直接使用层次坐标公式：HIERARCHY(A1)，实现上述效果。

2）A1 单元格插入公式range(2,3)，B1 单元格插入公式range(4,6)，C1 单元格插入公式A1*B1，D1 单元格插入公式C1[B1:-1]。C1 单元格扩展之后的数据相对后移一位显示在D1 单元格中。

2.2 绝对层次坐标

在进行报表设计时，单元格尚未扩展，但是其它单元格的计算表达式可能需要此单元格扩展后某个数据的精确位置，这时就需要使用绝对层次坐标精确表达该数据的位置。

层次坐标概念图如下图所示：

注：反向获取第 z 个数的意思是，取倒数第 z 个数。

2.2.1 坐标规则

绝对层次坐标就是通过绝对位置来精确定位扩展后的单元格，定位规则如下：

规则说明：

• Cellx：指目标单元格，是 Lk,Lk-1...L1;Tk,Tk-1...T1 的附属格。

• Lk：指某个目标单元格 Cellx 的左父格，Lk 的次序是从远到近的，也就是从离当前格最远的左父格开始，也可以理解为从最高级别的分组格开始。

• lk：指左父格 LK 扩展后的次序，即扩展后的第几个单元格，如果不指定 lk 或 lk 为 0，则表示为当前表达式所在单元格所属的当前左父格 LK。

• Tk：指某个目标单元格 Cellx 的上父格，Tk 的次序也是从远到近的，也就是从离当前格最远的上父格开始，也可以理解为从最高级别的分组格开始。

• tk：指上父格 Tk 扩展后的次序，即扩展后的第几个单元格，如果不指定 tk 或 tk 为 0，则表示为当前表达式所在单元格所属的当前上父格 TK。

注1：如果只有左父格没有上父格，分号可以省略，即 Cellx[Lk:lk,LK-1:lk-1,......,L1:l1]。

注2：如果只有上父格没有左父格，分号不能省略，即 Cellx[;Tk:tk,Tk-1:tk-1......,T1:t1]。

2.2.2 示例

1）A1 单元格插入公式 range(5)，B1 单元格插入公式 A1[A1:3]，B1 单元格中的公式是为了获取 A1 单元格扩展后第三个数据的值。

注：A1单元格纵向扩展，B1单元格左父格为无。

2）A1单元格插入公式range(2,3)，B1单元格插入公式range(4,6)，C1单元格插入公式A1*B1，D1单元格插入公式C1[A1:2,B1:3]。

C1[A1:2,B1:3]表示：取C1中     与A1第2个单元格对应的所有B1单元格中的第3个     相对应的那个单元格

表达式	效果	示例	图示
Cellx[!0]	获取单元格 Cellx 扩展出来的所有值	A1[!0]
Cellx[!0]{条件表达式}	根据条件表达式获取单元格 Cellx 扩展出来的部分值	A1[!0]{A1!=3}
&Cellx	获取单元格 Cellx 扩展后每个值对应的位置	&A1
$Cellx	获取单元格 Cellx 扩展后对应位置上的值	$A1

注：A1 单元格纵向扩展，B1 单元格跟随 A1 单元格纵向扩展。

层次坐标的具体应用参见文档：动态格间运算。