利用Excel进行自动分析计算方法.docVIP

利用Excel进行自动分析计算方法 　　［摘 要］ 通过对业务数据的归集整理分析，形成规范化的数据存储来积累基础数据，再利用添加的辅助数据项进行引用公式设计，从而实现随月份而动态变化的分析计算报表，为人们利用Excel报表工具来自动完成复杂的信息化管理工作提供了一个有效案例。 　　［关键词］ 动态分析计算；数据引用；公式设计 　　［中图分类号］F275；F270.7［文献标识码］A［文章编号］1673-0194（2007）04-0043-03 　　 　　一、问题的提出 　　 　　通常人们利用Excel进行报表编制时，只能实现简单的数据计算和表间数据引用处理。以财务管理中常见的存货分析表的编制为例，对于图1这样的分析计算要求，一般都是逐月输入本月数据，而对于实际发生和计划的两个“累计”数额还要利用上月表的“累计”与本月表的“本月”进行人工相加后再人工输入，自动计算只能完成“超降额”和“%”两项分析计算。许多人都尝试过编写公式“=B5+C5”来计算累计发生额单元C5，结果都以造成循环引用而告失败，最后只好人工计算后输入。 　　 　　如果能够根据报表月份自动从已经存在的“月份计划表”数据中自动提取“计划”的“本月”和“累计”数额，利用各个月份已经归集起来的“实际发生表”数据自动填写“实际发生”的“本月”和“累计”，不但可以减轻报表的编制工作量，更重要的是通过规范化的“实际发生表”、“月份计划表”的设计与使用积累了基础数据，实现了数据的自动引用，从而提高了数据分析计算的准确性，也扩展了应用复杂计算公式进行数据处理的思路。 　　 　　二、基础数据存储表的设计 　　 　　 　　为了实现上述目的，可以按12个月为表列，按图1的“项目”为表行来进行“实际发生表”和“月份计划表”的设计。利用“月份计划表”存储编制完成的月份计划数据，利用“实际发生表”逐月输入实际发生的数据，这样既能满足每月分析计算的要求，又实现了明细计划和实际发生的电子化数据存储，“实际发生表”的设计如图2左侧所示。当需要打印“存货月份实际发生表”时可以把右侧N到Q各列隐藏起来。 　　“月份计划表”可以参照图2的结构进行设计，文中不再详述。 　　当每月要求编制“存货分析表”时，只要输入该月份的“月份实际发生额”即可。 　　 　　三、动态输出表的设计 　　 　　首先确定图1中的“月份：”数存放在C2单元格，注意要保持数值型，用于输入编制报表的当前月份，并且在“月份实际发生表”的C2中编写引用公式“=存货分析表!C2”，让图2中C2单元格也等于报表月份。 　　然后假定图2右侧N列存放当前月份数据，也就是“本月占用”数据，如C2=8，则N列=I列；O列存放1至当前月份数据，则是1至8月份累计数据，也就是截止本月累计占用。 　　最后在图2的“存货分析表”中编写相应的引用计算公式如下： 　　实际发生―本月，B5单元格=月份实际发生表!N4，然后向下自动填充； 　　实际发生―累计，C5单元格=月份实际发生表!O4，然后向下自动填充； 　　计划―本月，B5单元格=月份计划表!N4，然后向下自动填充； 　　计划―累计，C5单元格=月份计划表!O4，然后向下自动填充。 　　这样就可以在C2单元格的“月份”数变化时，实现图1表格的动态的分析计算效果。 　　图3是存货分析表的公式视图显示。 　　 　　 　　四、动态引用数据的设计 　　 　　为了实现上述图2中的假定,还必须在图2右侧的N#65380;O#65380;P#65380;Q各列编写动态引用公式#65377; 　　(1)“当前月份所在列”用于存放本月所在列的单元格标号,形成“本月占用”数据的地址标号#65377;P4单元格=CHAR(65+￥C￥2)ROW(),然后向下自动填充#65377; 　　其中文本函数CHAR(65+$C$2)返回由数字参数所代表的ASCⅡ码字母,大写字母A的ASCⅡ码为65,而1月份所在列为B,例中的当前月份为8,CHAR(65+8)的返回结果为I,正好是8月份所在列,$C$2确保公式自动填充时绝对引用C2单元格的当前月份数,ROW()返回当前引用行的行号数,CHAR(65+$C$2)ROW()实现了字母I与数字4的连接,形成了当前月份第一行的单元格标号I4#65377; 　　(2)“1至当前月份区域”用于存放1至当前月份的区域标号,形成“截止本月累计占用”数据的地址标号#65377;Q4单元格=CHAR(66)ROW()“:”P4,然后向下自动填充#65377; 　　同理,公式CHAR(66)ROW()形成了1月份的单元格标号B4,P4中已经形成了当前月份第一行的单元格标号I4,公式CHAR(66)ROW()“:”P4连接形成了1月份至当前