用excel电子表格实现流水账傻瓜式记录和统计

摘要:本文是excel在生活中做这些工作中的一个特例,用数组知识进行数据统计,高效为生活服务的一个典型应用。

关键词:数据管理;统计;数组

中图分类号:TP391.13 文献标识码:A文章编号:1007-9599 (2010) 04-0000-01

Fool Records&Statistics Realization of Running AccountS with Excel

Sun Lei, Yang Dan

(Capital Economic&Trade University,Miyun Campus,Beijing101500,China)

Abstract:This article is excel do the work in the life of a special case,with an array of knowledge and statistical data,efficient services for the life,as a typical application.

Keywords:Data management;Statistics;Array

记账算账是很多个体老板每日必须的工作。但是很多人因为专业水平有限,把excel电子表格当成了简单的记账本,很多计算统计功能没开发出来,所以效率不高。其实,常用函数中的SUM和IF函数可以实现流水账傻瓜式统计。

下面以雪糕店为例,如何实现流水账傻瓜式记录和统计。

业务流程很简单:采购-销售-给业务员提成-再采购。统计内容有以下几项:根据时间统计商品销售量、销售额、业务员提成和商品库存。

根据需求设计流水账单。本实例中把流水账分别记录在“采购流水账”和“销售流水账”两个表中。把采购统计功能和销售统计功能实现在“统计”表中,库存统计功能实现到“库存”表中。

一、销售流水账

这个表与采购流水账类似,多出B(业务员)和G(业务员提成)列。B列与采用下拉列表实现无错录入,实现方法与C列(商品名称)同样,但前提需要预先准备好的商品名称序列和业务员姓名序列。G列是业务员提成G3中输入=F3*0*.03(按照3%提成),向下自动填充。

二、统计

这个表中实现了以时间为根据的采购和销售的各类汇总。以销售部分为例:

B2和D2是统计的起止时间,是用户根据需要手动输入的,其他的数字都是通过公式得出的。

三、实现方法

B4:B8是每个业务员的销售总额。

1.选中B4单元格

2.在编辑栏中输入“=SUM(IF(销售流水账!$A$3:$A$65535统计!$D$2,0,IF(销售流水账!$B$3:$B$65535=统计!$A4,销售流水账!$F$3:$F$65535,""))))”

3.按住ctrl+shift+enter

4.向下填充直B8。

四、公式分析

这是一个SUM嵌套IF的数组公式,按照从外到内的方向逐层分析:

1.最外层SUM()函数,对其中的序列求和。

2.IF(销售流水账!$A$3:$A$65535统计!$D$2,0,IF(销售流水账!$B$3:$B$65535=统计!$A4,销售流水账!$F$3:$F$65535,"")))判断“销售日期”如果在统计开始时间以前,则返回0。

IF(销售流水账!$A$3:$A$65535

3.表达式2:IF(销售流水账!$A$3:$A$65535>统计!$D$2,0,IF(销售流水账!$B$3:$B$65535=统计!$A4,销售流水账!$F$3:$F$65535,"") “销售日期”如果在统计开始时间以后,则返回0。

IF(销售流水账!$A$3:$A$65535>统计!$D$2,0,表达式3)

4.表达式3:IF(销售流水账!$B$3:$B$65535=统计!$A4,销售流水账!$F$3:$F$65535,"")

如果业务员姓名与统计表中业务员姓名一致,返回销售量序列

5.销售量序列作为sum函数的参数,得到每个业务员的总销售量

同理,C4-C8单元格根据时间和业务员返回提成总额

B12:B30单元格根据时间和商品名称返回销售总量

C12:C30单元格根据时间和商品名称返回销售总额

采购部分同理,

G12-G30单元格根据时间和商品名称返回采购总量

H12-H30单元格根据时间和商品名称返回采购总额

“库存”表:根据“统计”表的方法,可以计算出商品总采购量和总销售量,之间的差额就是库存。

四、库存

根据“统计”表的方法,可以计算出商品总采购量和总销售量,之间的差量就是库存。

作者简介:孙雷1981.08首都经济贸易大学密云分校,助教,计算机方面的研究。

杨丹1983.09首都经济贸易大学密云分校,助教,信息管理方面的研究。