财务会计与管理会计（四）

月度数据统计分析

OFFSET函数在图表分析中的应用

=INDEX(汇总月统计!SAS3:$AS36,MATCH(B2, N(OFFSET(汇总月统计!$A$2,ROW(S1:$34),MATCH(分析表!SBS1,汇总月统计!$B$2:SMS2,0))),0))
=LARGE(N(OFFSET(汇总月统计!SAS2,ROW(S1:$34),MATCH(SBS1,汇总月统计!$B$2:$MS2,0))),ROW(1:1))
通过控件选择指定月份，调取该月份下的费用数据并按照从小到大的顺序排列出来，仅对排在前十的费用数据显示在图表中，为财务分析例会提供直观的分析素材。

月度数据统计分析

多种费用组合分析图

SUMPRODUCT函数

A2=0FFSET(基础资料!$A$1,ROW(2:2)-1,$C$1)
B2=SUMPRODUCT((数据源!D$2:D$520=A2)*(数据源!B$2:B$520=A$1)*数据源!E$2:E$520)
B2-SUMPRODUCT((数据源!D$2:D$520=A2)*数据源!B$2:B$520=A$1)*数据源!E$2:E$520)

多种费用组合分析图

省公司全年数据分析模板

INDIRECT、OFFSET函数

B2=OFFSET(INDIRECT("'"&B$1&"'!$A$2"),ROW(1:1),MATCH($A$1,'1'!$A$2:$C$2,0)-1)
B2=OFFSET(起始点,向下偏移量,向右偏移量)

INDIRECT("'"&B$1&"'!$A$2")

 ='1'!A2='B1'!A2="'"&B1&"'!A2"=INDIRECT("'"&B1&"'!A2")

ROW(1:1)
1,2,3,4,5,……

MATCH($A$1,'1'!$A$2:$C$2,0)-1
MATCH(查找值,查找区域,查找方式)-1

P2=OFFSET(A$1,ROW(1:1),P$1)
B2=OFFSET(起始点,向下偏移量,向右偏移量)

省公司全年数据分析模板

多公司分季度数据筛选

VLOOKUP、IFERROR函数的应用

A2=IF(B2=查询表!$K$1,MAX(A$1:A1)+1,"”
A2=IF(判断表达式,成立时返回的值,不成立时返回的值)

A3=IFERROR(VLOOKUP(ROW(1:1),数据源!$A$2:$C$50,1.0),0)
A3=IFERROR(VLOOKUP(查找值,查找区域,返回的列数,精确查找),0)

A3=IFERROR(VLOOKUP(ROW(1:1),数据源!$A$2:$C$50,1,0),0)
B3=IFERROR(VLOOKUP(ROW(1:1),数据源!$A$2:$C$50,2,0),0)
C3=IFERROR(VLOOKUP(ROW(1:1),数据源!$A$2:$C$50,3,0),0)

D3=IFERROR(VLOOKUP(ROW(1:1),数据源!$A$2:$0$50,$K$2*3+1,0),0)
D3=IFERROR(VLOOKUP(查找值,査找区域,返回的列数,精确査找),0)

D3=IFERROR(VLOOKUP(ROW(1:1),数据源!$AS2:$0850,$K$2*3+1.0).0)
E3=IFERROR(VLOOKUP(ROW(1:1),数据源!$A$2:$0850,$K$2*3+2,0),0)
F3=IFERROR(VLOOKUP(ROW(1:1),数据源!SAS2:80850,SKS2*3+3,0),0)

多公司分季度数据筛选

淘宝后台数据分析

OFFSET函数在跨表取数中的应用

D4=AVERAGEIF(E4:AI4,">0”,E4:AI4)
D4=AVERAGEIF(条件区域,"指定条件",数据区)

E4=0FFSET(明细!$C$1,ROW(1:1)+(COLUMN(A1)-1)*15,MATCH($B$1,明细!$D$1:$F$1,0))
E4=0FFSET(起始点，向下偏移量，向右偏移量)

向右偏移量=MATCH($BS1,明细!SDS1:$F$1,0)
向右偏移量=MATCH(查找值,查找区域,查找方式)

向下偏移量=ROW(1:1)+(COLUMN(A1)-1)*15

=ROW (1:1)+COLUMN (A1)
=ROW (1:1)+(COLUMN(A1)-1)
=ROW(1:1)+(COLUMN(A1)-1)*15

淘宝后台数据分析

燃气消耗台账数据统计分析图

SUMPRODUCT函数在制作图表数据中的应用

M4=SUMPRODUCT((SDS3:$D8500=L82)*(SBS3:$B$500=$M$2)*($C$3:$C$500=M$3)*$G$3:$G$500)
M4=SUMPRODUCT((气表型号=指定值)*(年份=指定年)*(月份=指定月)*消耗气量)

M4=SUMPRODUCT(($D$3:$D8500-$L82)*($B83:$B$500-$M$2)*($C83:$C$500=M$3)*$G$3:$G$500)
M5=SUMPRODUCT((SDS3:SD8500=SL82)*(SB83:SB8500=SMS2)*(SC83:8CS500=MS3)*8183:$I8500

E2-SUMPRODUCT((YEAR($A$2:$A$13)=$D$1)*(MONTH($A$2:$A$13)=E1)*$B$2:$B$13)

燃气消耗台账数据统计分析图

管理费用分析

SUMIFS函数在制作图表数据中的应用

C2=SUMIFS( 2017’!F:F,’2017’!B:B,A2,’2017’!C:C, A$1)
C2=SUMIFS(数据区,科目列,指定科目,部门列,指定部门)
C2-SUMIFS( 2017’ !F:F,’2017’!B:B, A2,’2017’!C:C,A$1)
D2=SUMIFS( 2018’!F:F,’2018 !B:B,A2,’2018 !C:C,A$1)

A1=0FFSET(基础数据!C1,B1,)

C15-"2017年"&ROUND(C14/10000,2)&”万'
D15-"2018年"&ROUND(D14/10000,2)&”万’

1、添加控件列表框，关联单元格
2、添加科目名称和部门名称
3、编写A1单元格公式，用于图表标题
4、编辑C、D列公式

G2=SUMIFS(' 2017’!F:F,’2017’!C:C, F2)
G2=SUMIFS(数据区,部门列,指定部门)

G2-SUMIFS( 2017 !F:F, 2017 !C:C, F2)
H2-SUMIFS( 2018’!F:F,’2018’!C:C, F2)

I2=IF(F2=A$1,G2,0)
J2=IF(F2=A$1,H2,0)

同期数据对比

OFFSET函数在制作图表数据中的应用

P2=SUM(OFFSET(B2,,,,COUNT(B$6:M$6)))
P2=SUM(OFFSET(定位,下,右,高,宽))

Q2=SUM (OFFSET(B2,,,,COUNT (B$6:M$6)))/COUNT(B$6:M$6)
Q2=SUM (OFFSET(B2,,,,COUNT(B$6:M$6)))/COUNT(B$6:M$6)

AAA=OFFSET(数据统计表!$A$1,数据统计表!$R$1,1,1,12)
AAA=OFFSET(定位,下,右,高,宽)

AAA=OFFSET(数据统计表!$A$1,数据统计表!$R$1,1,1,12)
BBB=0FFSET(数据统计表!$A$1,数据统计表!$R$1+1,1,1,12)