Excel一些好用的技巧

什么时候用excel?

实际上我只想用一个功能,只是记录一下数据,算个总值等等需求较为单一,也可以单二,这种用excel就会很高效.比如本文主要讲的销货清单,只是一个清单对账,记录我的销售单据,顶多再算个总值.

事例需求:

想要根据纸质单据,登记销售单(或直接做销售单,打印纸质一样的),与采购商对下账,又不想每次商品信息全部都打全,想要提高录单效率,这就是本篇内容了.

本篇主要用到的函数:IFERROR ,VLOOPUP,OFFSET,MATH,COUTIF

新建excel,新建表单”商品信息”,再建表单”销售数据”.在商品信息中将售卖的商品写入,分别为编号列(需求编号查询商品的),品名列,单位(规格)列,单价列,进价(如需计算成本)列.本篇不讲成本,因为无论成本,利润,单品总额,总利润,都是公式的重复运用.这里讲什么呢?讲如何输入名称,自动带入商品规格,单价等信息,提高输入效率.

上图便是本文实例了,做到输入商品,表格之中自动带出单位和价格,输入数量,自动计算金额,根据日期,自动统计当天商品总金额,如果想要计算毛利,商品信息中添加毛利数据列,即销售列减去成本列,加一个数据透视表统计商品总数量,利用OFFSET函数带出毛利,数量*毛利就是了.

IFERROR(判断的值, 判断的值为错误值返回的结果),主要用于空值不报错

Vlookup(需要查找的值，查找范围，列序数，[匹配条件]):

根据需要查找的值,在查找范围内,然后获取 “需要查找的值”在查找范围第几列的 值,精确匹配),根据事例来讲就是根据销售单品名单元格的值(B4),在商品列表(B列到D列)中,获取B列到D列中第三列中对应的值,返回精确数值.

销售单的单价和单位就是以下两个公式

查询单价

=IFERROR(VLOOKUP(B4,商品列表!B:E,3,0),””)

查询单位

=IFERROR(VLOOKUP(B4,商品列表!B:E,2,0),””)

计算商品总金额,这个没啥好说的.

=IFERROR(D4*E4,””)

MATCH函数返回指定值在数组中的位置，如果在数组中没有找到该值则返回#N/A。

MATCH(需要查找的值, 查找范围, 偏移量) ,”*”&H2&”*”代表模糊查找.

位置 =MATCH(“*”&B4&”*”,商品列表!$B$2:$B$798,0),返回相当于商品列表B2的相对位置的值,即数值为10,就是B2下移10个.如果偏移量取值1,取值就是9.

COUNTIF 计算包含“查找值”的数量,这个在offset中作用是显示个数,我输入品名数据后,查找范围有5个,下拉菜单就显示5个值.

数量 =COUNTIF(商品列表!$B$2:$B$798,”*”&B4&”*”)

下拉菜单取值

=OFFSET(商品列表!$B$1,MATCH(“*”&B4&”*”,商品列表!$B$2:$B$798,0),,COUNTIF(商品列表!$B$2:$B$798,”*”&B4&”*”))

直译就是根据商品列表B1,向下移动MATCH取值的格数,左移空,显示COUNTIF值的数量的格子.

销售单品名列,在选项卡栏选择数据-数据验证-设置,在这之中,来源输入offset公式,其他的和图片一样,这个就是下拉菜单的设置.

这样你在品名里输入即可在下拉列表模糊查询品名,选择品名,自动带出单位和单价,这几个公式可满足基本记账需求,根据需要排列组合即可,如需循环引用,就在文件-选项-公式-勾选迭代计算.

根据日期计算金额

=SUMIFS(F:F,A:A,H3) 这个是计算F列的和,取A列与H3单元格同数据的单元格范围)