1. 概述编辑
本文的学习视频如下,如果您需要由浅入深地学习层次坐标可以使用:层次坐标专题
1.1 应用场景
同比是把当年每月与上一年相应月份的数据相除计算出的一个比率。即某年第 n 月与上一年第 n 月的比值,计算公式为:本期/同期*100%。
同比增长率是指和去年同期相比较的增长率,即某年第n月与上一年第n月相比较的增长率,计算公式为:(本期-同期)/同期*100%。
示例一:当年上年月份一一对应
示例二:当年上年月份不一一对应
2. 示例编辑
2.1 准备数据
1)新建普通报表,新增数据集 ds1,SQL 语句为:
select strftime('%Y',订购日期) as 年份, strftime('%m',订购日期) as 月份,应付金额 from 订单 where strftime('%Y',订购日期) = '2010' or strftime('%Y',订购日期) = '2011'
2)新建普通报表,新建内置数据集 Embedded1 ,如下图所示:
2.2 设计报表
2.2.1 月份一一对应
如下图所示设计表格,将 ds1 的年份、月份、应付金额拖入A3、B3、C3 单元格,将「年份」、「月份」设置为分组格式、「应付金额」设置为汇总格式, D3 单元格用于计算同期比。
D3 单元格输入公式:IF(&A3 > 1, C3 / C3[A3:-1], 0),公式拆解后的说明如下表所示:
公式拆解 | 说明 |
---|---|
C3[A3:-1] | 表示当前 C3,所在的 A3(年份)单元格往上偏移一个位置(去年)中与当前月在相同位置的月所对应的 C3 单元格的值,即等价于 C3[A3:-1,B3:+0]。 |
&A3>1 | 从第二年开始。 |
IF(&A3 > 1, C3 / C3[A3:-1], 0) | 父格为月份单元格,跟随月份扩展:当&A3>1即从第二年开始,当月金额比上上一年相应月金额,否则显示 0。 |
E3 单元格输入公式:IF(&A3 > 1, (C3 - C3[A3:-1]) / C3[A3:-1], 0),公式拆解后的说明如下表所示:
公式拆解 | 说明 |
---|---|
C3[A3:-1] | 表示当前 C3,所在的 A3(年份)单元格往上偏移一个位置(去年)中与当前月在相同位置的月所对应的 C3 单元格的值,即等价于 C3[A3:-1,B3:+0]。 |
&A3>1 | 从第二年开始。 |
IF(&A3 > 1, (C3 - C3[A3:-1]) / C3[A3:-1], 0) | 父格为月份单元格,跟随月份扩展:当&A3>1即从第二年开始,当月金额比上上一年相应月金额,否则显示 0。 |
2.2.2 月份不一一对应
如下图所示设计表格,将 Embedded1 中的年份、月份、总额拖入 A3、B3、C3 单元格,将「年份」、「月份」设置为分组格式、「总额」设置为汇总格式, D3 单元格计算同期比。
D3 单元格输入公式:IF(&A3>1,C3/C3[!0]{A3=$A3-1&&B3=$B3},0),公式拆解后的说明如下表所示:
公式 | 说明 |
---|---|
A3=$A3-1 | 上一年年份 |
B3=$B3 | 月度相同 |
C3[!0]{A3=$A3-1&&B3=$B3} | 取总额满足年份为上一年,且月份与当年相同的数据 |
C3/C3[!0]{A3=$A3-1&&B3=$B3} | 当月金额比上上一年相应月金额,且只有当年和上一年月份相同的情况下才会计算。 |
&A3>1 | 从第二年开始 |
IF(&A3>1,C3/C3[!0]{A3=$A3-1&&B3=$B3},0) | 从第二年开始,当月金额比上上一年相应月金额,且只有月份相同的情况下才会计算,即需要同时判断高级父格与下级父格,否则显示 0。 |
D3 单元格也可使用公式:IF(&A3>1,if(len(C3[!0]{A3=$A3-1&&B3=$B3}) =0,0,C3/C3[!0]{A3=$A3-1&&B3=$B3}),0) ,公式拆解后的说明如下表所示:
公式拆解 | 说明 |
---|---|
LEN(C3[!0]{A3=$A3-1&&B3=$B3})=0 | 判断上一年的相同月份值是否存在 |
if(len(C3[!0]{A3=$A3-1&&B3=$B3}) =0,0,C3/C3[!0]{A3=$A3-1&&B3=$B3}) | 如果不存在,则返回0 否则进行计算 |
IF(&A3>1,if(len(C3[!0]{A3=$A3-1&&B3=$B3}) =0,0,C3/C3[!0]{A3=$A3-1&&B3=$B3}),0) | 从第二年开始,当月金额比上上一年相应月金额,且只有月份相同的情况下才会计算,即需要同时判断高级父格与下级父格,否则显示 0。 |
E3 单元格输入公式:if(&A3>1,(C3 - C3[!0]{A3=$A3-1&&B3=$B3})/C3[!0]{A3=$A3-1&&B3=$B3},0),公式拆解后的说明如下表所示:
公式拆解 | 说明 |
---|---|
A3=$A3-1 | 上一年年份 |
B3=$B3 | 月度相同 |
C3[!0]{A3=$A3-1&&B3=$B3} | 取总额满足年份为上一年,且月份与当年相同的数据 |
(C3 - C3[!0]{A3=$A3-1&&B3=$B3})/C3[!0]{A3=$A3-1&&B3=$B3} | 当月金额与上一年相应月金额的差值,且只有当年和上一年月份相同的情况下才会计算。 |
&A3>1 | 从第二年开始 |
IF(&A3>1,(C3 - C3[!0]{A3=$A3-1&&B3=$B3})/C3[!0]{A3=$A3-1&&B3=$B3},0) | 从第二年开始,当月金额与上一年相应月金额的差值,且只有月份相同的情况下才会计算,即需要同时判断高级父格与下级父格,否则显示 0。 |
D3、E3 单元格设置条件属性,可避免存在「除数」为 0 时,结果出现无穷大,详情可参见:除数/被除数为零的处理 ,如下图所示:
2.3 效果预览
保存模板,选择分页预览,效果如下图所示:
示例一:当年上年月份一一对应
示例二:当年上年月份不一一对应
支持 App 端和 HTML5 端预览,效果如下图所示:
示例一:当年上年月份一一对应
示例二:当年上年月份不一一对应