返回

实务交流

发帖

以最快的速度制作一份Excel出货管理表

Excel不加班楼主

2018-04-20 17:02:13 348 2

这是5年前我经常在用的出货管理表,文章分成上下两篇。


上篇主要讲如何模拟数据,对于公司的管理层,有一些数据比较敏感,不方便透露,这时随机数就起到了重要的作用。


不过,有一点需要说明,模拟数据也是需要技巧的,不是简单的一个RANDBETWEEN函数就可以,那样会闹出笑话。


为什么要写这篇文章,还有一个重要的原因,读者老是私聊我要数据源,今天就将我平常模拟数据的方法教你。授人以鱼不如授人以渔。


1.获取2012年到2013年的日期。


在A2单元格输入公式,下拉填充。

=RANDBETWEEN("2012-1-1","2013-12-30")


RANDBETWEEN函数的起始值跟终止值,除了可以是普通的数字,还可以是日期,这个估计很多读者都不知道。


将日期设置为常规格式,就变成了数字。2012-1-1就是40909,2013-12-30就是41638。


也就是说,随机日期,也可以这样输入公式:

=RANDBETWEEN(40909,41638)


不过,不管用哪一种,都必须将单元格格式设置为日期格式。


2.获取客户。


在B2单元格输入公式,下拉填充。

=INDEX({"王五";"李四";"张三";"Excel不加班"},RANDBETWEEN(1,4))


INDEX函数第一参数可以是区域引用,也可以是常量数组,经常跟MATCH函数组合起来一起用。


单独的函数用法,参考函数图解。


INDEX函数图解


MATCH函数图解

3.获取产品名称。


在C2单元格输入公式,下拉填充。

=CHAR(RANDBETWEEN(65,70))&"产品"


CHAR函数可以获取字母。


4.获取数量。


在D2单元格输入公式,下拉填充。

=RANDBETWEEN(1,40)


5.获取单价(元)。


这里就不能傻傻的用随机数了,同一个产品价格是一样的,这时就需要一个价格对应表。


在F2单元格输入公式,下拉填充。

=VLOOKUP(C2,参数表!A:B,2,0)


6.获取金额(元)。


在G2单元格输入公式,下拉填充。

=ROUND(E2*F2,0)


7.获取出(退)货。


在H2单元格输入公式,下拉填充。

=INDEX({"出货";"出货";"出货";"退货"},RANDBETWEEN(1,4))


可能有读者会问,这里怎么写了3个出货,是不是写错了?


这个真的没错,一般情况下,公司出货的次数肯定比退货的次数要多几倍,所以这里写3个,这样的数据更符合实际情况。


专业与业余,有的时候并不是在于对函数的理解水平,而在于这些细节上。专业的人会想得更全面,更符合实际。


下篇主要讲如何根据数据进行统计分析,敬请期待。

作者:卢子,清华畅销书作者;个人公众号:Excel不加班(ID:Excelbujiaban)

飞翎恋雪1楼

2018-05-09 20:10:05