论文部分内容阅读
做统计财务工作的朋友都知道,平常与各种统计表打交道,如销售、生产、库存等各种统计报表,每日(或每月)的生产销售都需要进行汇总,单纯的手工计算太麻烦,虽然现在利用Excel能够节省不少时间,但是往往还不能够达到自动的效果。
其实利用Excel的各种函数,结合一些功能,加上合理设置工作表,就能够实现自动汇总,自动生成领导需要的报表,并可以避免一些差错的出现,从而可以达到事半功倍的效果。我们单位的统计员,就是因为采用此类诸多自动化的措施,才实现了工作加倍而人员不增的效果的。
下面就以2009年度生产销售统计日报表为例,来看看如何通过Excel,来提高我们的工作效率!
编辑产品资料表,为数据统计奠定坚实基础
第一步:作为一份产销售统计日报表,必须包括产品的编号、名称等基本资料。启动Excel 2003,新建一个工作簿,将其命名为“2009年度生产销售日报表”,并新建一个工作表命名为“基础资料”,在这个工作表的A1-E1单元格区域内输入产品编码、产品名称、规格、单位、生产车间等列名,然后在下面的相应单元格区域输入相关产品基本信息(见图1)。 br>
第二步:为了能够在以后的数据处理中,便于调用产品编号信息,执行“插入→名称→定义”命令,在弹出的对话框中,输入定义名称为“产品编号”,引用位置为“=OFFSET(基础资料!$A$2,,,(COUNTA(基础资料!$A:$A)-1),)” (见图2)。
第三步:同时进一步便于调用日期,在此工作表的K1:K3区域中,输入统计年份,如2009年～2011年,并执行“插入→名称→定义”命令,将此区域定义为“年份”。采用类似的方法,在L1:L12区域中输入月份,并定义为“月份”;M1:M31区域中输入日期,定义为“日”,如图1的右边部分。
巧用函数,快速输入日常生产销售数据
在前面编辑的基础资料表上,就可以进行日常数据表的设计了,新建一个工作表,将其命名为“2009年日常数据输入”(见图3),在此工作表中,设置步骤如下。
第一步:在C1单元格中输入文字“请选择年月日:”,然后单击其后的D1单元格,执行“数据→有效性”命令,在弹出的“数据有效性”对话框中,从“设置”选项卡的“有效性条件”下面的“允许”列表框中选择“序列”选项,在“来源”中输入公式“=年份”,然后选中“提供下拉箭头”复选项,单击“确定”按钮(见图4)。
第二步:采用相同方法,将E1、F1单元格的设置为“=月份”、“=日”。在A1单元格中输入文字“输入单据日期:”,B1单元格中输入公式“=D1&E1&F1”,这样当需要选择输入日期时,只需要单击C1、E1、F1单元格右侧的下拉按钮,然后用下拉菜单选择相应的年月日即可,在B1单元格中就会自动显示当前输入日期,如“2009年7月9日”。
第三步:此外,由于在以后的日报自动汇总过程中需要使用此表的日期,因此必须新添加3个定义,分别为:
“输入日期”,其引用位置为“=OFFSET('2009年日常數据输入'!$B$3,,,(COUNTA('2009年日常数据输入'!$B:$B)-2),)”;
“日报时间”,其引用位置为“=OFFSET(基础资料!$H$1,,,MATCH("end",基础资料!$H:$H,0)-1)”;
cc,其引用位置为“=OFFSET('2009年日常数据输入'!$C$3,,,(COUNTA('2009年日常数据输入'!$C:$C)-2),)”。
第四步:根据实际统计需要,在此工作表的C1:J1区域中输入统计列名,如单据号、日期、产品编号、产品名称、本日产出、本日入库、本日出库、本日销售等。然后单击日期下面的B3单元格,将其的数据有效性条件设置为“=$B$1”;将产品编号下面的C3单元格,将其的数据有效性条件设置为“=产品编号”;在产品名称、规格、单位下面的D3、E3、F3单元格中依次输入公式,并将这些公式依次复制到下面的相应单元格中:
“=IF(ISERROR(VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,2,FALSE)),"",VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,2,FALSE))”
“=IF(ISERROR(VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,3,FALSE)),"",VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,3,FALSE))”
“=IF(ISERROR(VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,4,FALSE)),"",VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,4,FALSE))”
这样日常输入统计数据时,只需要单击B列中的单元格,如B3,就会弹出下拉列表,从其中选择当前日期,然后单击C3,从弹出的列表中选择产品编号,在D3、E3、F3就会自动显示相应产品名称、规格、单位等信息,然后在后面的单元格中输入具体生产销售数据即可(见图5)。
精心设计,实现自动汇总
日常统计工作除了输入基本数据外,进行每日汇总是每天必做工作之一,比如需要统计每月1日至当天的生产销售数据,这些操作都可以通过设置,实现自动汇总。
第一步:新建一个工作表,将其命名为“日报表”,在此表的F1单元格中输入标题“产品日报表”,将其前面的D1单元格的数据有效性设置为“=日报时间”。在B3:M3区域中输入编号、名称、本日产出、本日产出累计等列名,然后在其下面的单元格中依次输入公式。
编号下面的B4中输入:
“{=IF(SUM(1/COUNTIF(cc,cc))>=ROW(C1),INDEX(cc,SMALL(IF(ROW(cc)-2=MATCH(cc,cc,0),ROW(cc)-2,"0"),ROW(C1))),"") }”
名称下面的C4输入:
“=IF(ISERROR(VLOOKUP(B4,基础资料!$A$1:$D$1000,2,FALSE)),"",VLOOKUP(B4,基础资料!$A$1:$D$1000,2,FALSE))”
规格下面的D4输入:
“=IF(ISERROR(VLOOKUP(B4,基础资料!$A$1:$D$1000,3,FALSE)),"",VLOOKUP(B4,基础资料!$A$1:$D$1000,3,FALSE))”;
单位下面的E4输入:
“=IF(ISERROR(VLOOKUP(B4,基础资料!$A$1:$D$1000,4,FALSE)),"",VLOOKUP(B4,基础资料!$A$1:$D$1000,4,FALSE))”;
本日产出下面的F4输入:
“{=SUM(--(('2009年日常数据输入'!$B$3:$B$100=$D$1)*(B4='2009年日常数据输入'!$C$3:$C$100)*'2009年日常数据输入'!$G$3:$G$100))}”
在本日产出累计下面的G4输入:
“{=SUM(--('2009年日常数据输入'!$B$3:$B$100>=YEAR($D$1)&"年"&MONTH($D$1)&"月1日")*('2009年日常数据输入'!$B$3:$B$100<=$D$1)*(B4='2009年日常数据输入'!$C$3:$C$100)*'2009年日常数据输入'!$G$3:$G$100)}”
后面的几个单元格依次类推即可(见图6)。
其实利用Excel的各种函数,结合一些功能,加上合理设置工作表,就能够实现自动汇总,自动生成领导需要的报表,并可以避免一些差错的出现,从而可以达到事半功倍的效果。我们单位的统计员,就是因为采用此类诸多自动化的措施,才实现了工作加倍而人员不增的效果的。
下面就以2009年度生产销售统计日报表为例,来看看如何通过Excel,来提高我们的工作效率!
编辑产品资料表,为数据统计奠定坚实基础
第一步:作为一份产销售统计日报表,必须包括产品的编号、名称等基本资料。启动Excel 2003,新建一个工作簿,将其命名为“2009年度生产销售日报表”,并新建一个工作表命名为“基础资料”,在这个工作表的A1-E1单元格区域内输入产品编码、产品名称、规格、单位、生产车间等列名,然后在下面的相应单元格区域输入相关产品基本信息(见图1)。 br>
第二步:为了能够在以后的数据处理中,便于调用产品编号信息,执行“插入→名称→定义”命令,在弹出的对话框中,输入定义名称为“产品编号”,引用位置为“=OFFSET(基础资料!$A$2,,,(COUNTA(基础资料!$A:$A)-1),)” (见图2)。
第三步:同时进一步便于调用日期,在此工作表的K1:K3区域中,输入统计年份,如2009年～2011年,并执行“插入→名称→定义”命令,将此区域定义为“年份”。采用类似的方法,在L1:L12区域中输入月份,并定义为“月份”;M1:M31区域中输入日期,定义为“日”,如图1的右边部分。
巧用函数,快速输入日常生产销售数据
在前面编辑的基础资料表上,就可以进行日常数据表的设计了,新建一个工作表,将其命名为“2009年日常数据输入”(见图3),在此工作表中,设置步骤如下。
第一步:在C1单元格中输入文字“请选择年月日:”,然后单击其后的D1单元格,执行“数据→有效性”命令,在弹出的“数据有效性”对话框中,从“设置”选项卡的“有效性条件”下面的“允许”列表框中选择“序列”选项,在“来源”中输入公式“=年份”,然后选中“提供下拉箭头”复选项,单击“确定”按钮(见图4)。
第二步:采用相同方法,将E1、F1单元格的设置为“=月份”、“=日”。在A1单元格中输入文字“输入单据日期:”,B1单元格中输入公式“=D1&E1&F1”,这样当需要选择输入日期时,只需要单击C1、E1、F1单元格右侧的下拉按钮,然后用下拉菜单选择相应的年月日即可,在B1单元格中就会自动显示当前输入日期,如“2009年7月9日”。
第三步:此外,由于在以后的日报自动汇总过程中需要使用此表的日期,因此必须新添加3个定义,分别为:
“输入日期”,其引用位置为“=OFFSET('2009年日常數据输入'!$B$3,,,(COUNTA('2009年日常数据输入'!$B:$B)-2),)”;
“日报时间”,其引用位置为“=OFFSET(基础资料!$H$1,,,MATCH("end",基础资料!$H:$H,0)-1)”;
cc,其引用位置为“=OFFSET('2009年日常数据输入'!$C$3,,,(COUNTA('2009年日常数据输入'!$C:$C)-2),)”。
第四步:根据实际统计需要,在此工作表的C1:J1区域中输入统计列名,如单据号、日期、产品编号、产品名称、本日产出、本日入库、本日出库、本日销售等。然后单击日期下面的B3单元格,将其的数据有效性条件设置为“=$B$1”;将产品编号下面的C3单元格,将其的数据有效性条件设置为“=产品编号”;在产品名称、规格、单位下面的D3、E3、F3单元格中依次输入公式,并将这些公式依次复制到下面的相应单元格中:
“=IF(ISERROR(VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,2,FALSE)),"",VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,2,FALSE))”
“=IF(ISERROR(VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,3,FALSE)),"",VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,3,FALSE))”
“=IF(ISERROR(VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,4,FALSE)),"",VLOOKUP('2009年日常数据输入'!C3,基础资料!$A$1:$D$100,4,FALSE))”
这样日常输入统计数据时,只需要单击B列中的单元格,如B3,就会弹出下拉列表,从其中选择当前日期,然后单击C3,从弹出的列表中选择产品编号,在D3、E3、F3就会自动显示相应产品名称、规格、单位等信息,然后在后面的单元格中输入具体生产销售数据即可(见图5)。
精心设计,实现自动汇总
日常统计工作除了输入基本数据外,进行每日汇总是每天必做工作之一,比如需要统计每月1日至当天的生产销售数据,这些操作都可以通过设置,实现自动汇总。
第一步:新建一个工作表,将其命名为“日报表”,在此表的F1单元格中输入标题“产品日报表”,将其前面的D1单元格的数据有效性设置为“=日报时间”。在B3:M3区域中输入编号、名称、本日产出、本日产出累计等列名,然后在其下面的单元格中依次输入公式。
编号下面的B4中输入:
“{=IF(SUM(1/COUNTIF(cc,cc))>=ROW(C1),INDEX(cc,SMALL(IF(ROW(cc)-2=MATCH(cc,cc,0),ROW(cc)-2,"0"),ROW(C1))),"") }”
名称下面的C4输入:
“=IF(ISERROR(VLOOKUP(B4,基础资料!$A$1:$D$1000,2,FALSE)),"",VLOOKUP(B4,基础资料!$A$1:$D$1000,2,FALSE))”
规格下面的D4输入:
“=IF(ISERROR(VLOOKUP(B4,基础资料!$A$1:$D$1000,3,FALSE)),"",VLOOKUP(B4,基础资料!$A$1:$D$1000,3,FALSE))”;
单位下面的E4输入:
“=IF(ISERROR(VLOOKUP(B4,基础资料!$A$1:$D$1000,4,FALSE)),"",VLOOKUP(B4,基础资料!$A$1:$D$1000,4,FALSE))”;
本日产出下面的F4输入:
“{=SUM(--(('2009年日常数据输入'!$B$3:$B$100=$D$1)*(B4='2009年日常数据输入'!$C$3:$C$100)*'2009年日常数据输入'!$G$3:$G$100))}”
在本日产出累计下面的G4输入:
“{=SUM(--('2009年日常数据输入'!$B$3:$B$100>=YEAR($D$1)&"年"&MONTH($D$1)&"月1日")*('2009年日常数据输入'!$B$3:$B$100<=$D$1)*(B4='2009年日常数据输入'!$C$3:$C$100)*'2009年日常数据输入'!$G$3:$G$100)}”
后面的几个单元格依次类推即可(见图6)。