1.1在C3(占比)单元格中直接使用占比公式:=PROPORTION(B3);占比:当前值占总值的比例
1.2 计组内占比注:C2[!0]{A2=$A2},表示C2扩展出来地区相同的单元格。sum(C2[!0]{A2=$A2}),表示相同地区内的金额总和,用C2相除是得出的是销量在对应地区内的占比。 层次坐标条件写法B2[!0]{A2=$A2} 表示b2按相同的单元条件扩展出来的的单元格
2.比较:当前值与第一个值做比较 计算公式为:当前值减去第一个值(C2/C2[A2:1])
3.环比:当前值比上月份的值; 计算公式为:当前值除上一个值(Iif(&A2>1,C2/C2[A2:-1],0) 或=MOM(A2, B2) A2为月份,B2为金额
4.同期比: 今年/去年-1 坐标公式;EVAL(CONCATENATE("if(&A3>1,C3/C3[!0]{A3=",A3-1,"&&B3=$B3},0)"))
含义解释:{A3=",A3-1,"&&B3=$B3} 这里表达的意思为:
A3=",A3-1," (指上一年年份)
B3=$B3(月度相同)
5.累计,跨层累计 :写入累计公式=D3[B3:-1] + C3;其中D3[B3:-1]表示B3扩展出来的上一格(上月)中D3的值。
或使用累计=LAYERTOTAL(B3, C3, D3) ; 跨层累计:写跨层累计公式=CROSSLAYERTOTAL(A3, B3, C3, D3)
6.横向交叉累计:
在C3单元格中,写入公式:=B3 + C3[;B2:-1]:当前月份的金额加上一月累计金额。
其中C3[;B2:-1]表示上父格B2扩展出来的上一格(上月)的C3的值。
7.条件汇总:
在C3单元格中,写入公式:SUM(B2{B2 < 1}),B2扩展出来的列中,满足属于A2的B2小于1的值的和。
其中B2{B2 < 1}表示B2单元格扩展出来的列B2小于1的值。
{B2 < 1},{}里面是筛选条件,B2小于1的值。
D2的公式为:COUNT(B2{B2 > 1})
E2的公式为:AVERAGE(B2)
8.排名、组内排名: SORT(cell) 等同于count(b2[!0]{b3>$b3})+1 ;组内排名=count(c2[!0]{a2=$a2&&c2>$c2})+1。
9.循环引用:
上日库存
假设6月份前留下的余额为0,则在B3单元格中,输入公式:=if(&A3 = 1, 0, B3[A3:-1] + C3[A3:-1] - D3[A3:-1]):当&A3=1,第一天的上日库存为0,否则从第二天开始,上日库存就等于前天库存+上日入库-上日出库。
本日库存
在E3单元格中,输入公式=B3 + C3 - D3:本日库存就为上日库存+本日入库-本日出库。
方法二:
直接在B3单元格中输入循环引用公式=CIRCULAR(A3, B3, C3, D3),等同于公式=if(&A3 = 1, 0, B3[A3:-1] + C3[A3:-1] - D3[A3:-1]),如果为横向扩展。则传递第五个参数false,例如:=CIRCULAR(A3, B3, C3, D3,false)。
10.字母序号:
大写字母排序:char(seq()+64)
小写字母排序;char(seq()+96
11.COUNT统计有效条数
在单元格中统计出产品名称有值的个数,输入公式=count(GREPARRAY(array(C3),len(item)!=0))或=count(GREPARRAY(array(C3),item!=""&&item!=null)):
公式的作用是首先将C3转为数组,过滤掉其中为空的记录,最后统计剩余记录即不为空的个数。