|
Excel成长学院2017-02-24阅读原文 院长大大丨图文 教程基于Excel 2016 对于销售岗位的小伙伴来说，月底就像一道坎，每天加班加点做统计，做报表。 但其实你有没有发现，每个月的统计工作都是大同小异的。但哪怕只变动一点，都会让你重做一遍又一遍。 那是因为，你没真正掌握常用的统计场景和函数应用。 今天，院长特意精选了六个销售岗位的实用场景，带你一齐玩转月末统计。 1 在销售表中自动返回产品单价 我们在做销售管理时，通常都会建立一张产品成本对照表。而在登记产品销售表时，经常需要输入产品成本价，从而求出销售利润。 这时，我们可以利用产品成本对照表，使用VLOOKUP函数做查找匹配，那在登记销售表时，就能实现自动返回产品单价。
在单元格E2输入公式【=IFERROR(VLOOKUP(D2,$K$1:$L$7,2,0),'')】，查找单元格D2中【键盘】，在产品成本对照表中的价格。 避免匹配区域K1：L7在公式复制时发生变动，造成查找错误，使用绝对引用固定区域； 部分数据还没有填写时，查找引用会出现#N/A报错，使用IFERROR函数转换为空值。 2 统计各店面手写板的销售记录条数 统计销售记录条数，我们需要使用COUNTIFS函数，实现多条件统计。 这里，统计的条件有两个，第一，店面，第二，手写板。看看具体的函数写法。
在单元格L2输入公式【=COUNTIFS($B$1:$B$51,K2,$D$1:$D$51,'手写板')】，查找出各店面对应的手写板销售记录条数。 使用绝对引用固定B列和D列的查找区域； 第一个条件，店面采用单元格引用的方式，直接选择K2，第二个条件，直接输入手写板，但必须注意使用英文引号。 3 计算键盘、鼠标两种产品的销售总额合计 产品销售表中有多个产品，有时候，我们根据需要，只统计其中的几个产品的销售情况。 一般而言，这种简单的统计可以通过自动筛选完成。那通过函数怎么实现呢？使用SUM函数进行数组计算。 输入公式【=SUM((D2:D51={'键盘','鼠标'})*(H2:H51))】按【Ctrl】+【Shift】+【Enter】确定，进行数组计算。 第一个条件【D2:D51={'键盘','鼠标'}】，判断产品为键盘或鼠标分别是哪几行，第二个条件【H2:H51】，取出成交金额，条件用【*】号相连。 第一个条件中使用【{'键盘','鼠标'}】形式表达，表示满足其中一个条件即可。 满足多个条件的判断，条件之间使用【*】号相连。 计算范围不能包含标题行，不然会报错哦！为什么？自己想想看！ 4 统计各销售员无线网卡的销售金额 这里跟场景2有点类似，也是多条件统计，但统计的是销售金额的合计，我们需要使用SUMIFS函数。 满足销售员和产品两个条件，计算成交金额的合计。看看具体的函数写法。
在单元格L2输入公式【=SUMIFS($H$1:$H$51,$C$1:$C$51,K2,$D$1:$D$51,'无线网卡')】，统计各销售员无线网卡的销售金额。 第一个条件选择成交金额范围，即$H$1:$H$51； 第二、三组条件，分别是销售员与产品名称，销售员采用单元格引用，产品名称直接输入无线网卡。 5 根据产品名称查询库存数量 根据库存流水表，如何根据产品名称，找出最新的库存数量。我们使用LOOKUP函数，根据产品名称查找最后一条数据。 在单元格F2输入公式【=IFERROR(LOOKUP(1,0/(B2:B25=E2),C2:C25),'')】，在E2输入产品名称，查询出最新的库存数量。
输入手写板，查询到库存为91，即1月26日的数据；输入DVD光驱，查询到库存为16，即1月29日的数据。 公式中【B2:B25=E2】，相等为1，不相等为0，即返回一个1和0的数组；然后，用0除，即可得到0和#DIV/0!的数组。 LOOKUP函数查找的值是1，由于返回值中没有1，则查找0和#DIV/0!的数组中小于1的值，根据函数特性，返回最后一个0值的位置。 6 库存量过小时提示补货 当产品库存量小于50，即为缺货状态。这时，可以使用条件格式设置高亮显示，提示补货。 选择区域F2：F7，点击条件格式，新建规则。 选择规则类型为【使用公式确定要设置格式的单元格】，输入公式【=F2 即可得到下图结果。 好几天没给大家写原创了，是不是很想念院长。希望今天的文章能帮助你跨过月底的那道坎吧！！！ 教程源文件链接：https://pan.baidu.com/s/1eSMJBXo 如果你有更多的方法，可以在文章底部留言哦~么么哒~
Excel成长学院 高效有趣学Excel |
|
|
来自： CCCCCCCCCkmsrp > 《技能》
