【绝密干货】商场营运最常用EXCEL函数公式大全，帮你整理齐了，拿来即用！
我的图书馆
【绝密干货】商场营运最常用EXCEL函数公式大全，帮你整理齐了，拿来即用！
商场营运工作必会的EXCEL函数公式与营运实际操作案例，个个都很实用，一定要注意收藏哦！ & & & & & & &by杨叫兽叫兽开场白EXCEL中的函数很多，功能也非常强大，如能掌握一些常用的函数，将给日常的营运数据处理带来很大的便利，帮助节省时间，提高工作效率。下面，杨叫兽通过实际案例为各位营运小伙伴介绍一些非常实用的Excel技巧和公式，希望对大家有所帮助，让大家一秒变工作小能手！& & & & ——（注：本文所有数据均为假设虚拟数据）Happy Class一、判断公式[11/11]1【IF】单条件判断★案例——计算商铺销售完成率如下图，达到目标销售额就写完成，未达到则列出差额，公式为：完成',D2-C2)★案例——可能负值的完成率计算如下图，根据预算和实际，计算完成率，公式为：=IF(B3（*关注上述预算是负值的完成率计算结果不一样）★案例——可能负值的完成率计算如下图，根据2016年和2015年，计算同比增长率，公式为：=(B3-C3)/IF(C3>0,C3,-C3)&（*关注上述2015年是负值的同比增长率计算结果不一样。）2【Iferror】处理错误值★案例——处理公式产生的错误值如下图，处理同比增长率出现错误的单元格，如果是错误值则显示为空，否则正常显示。把错误值显示为空，公式为：=IFERROR(C3/D3-1,'')把错误值显示为“新开业”，公式为：=IFERROR(C3/D3-1,'新开业')3【IF、AND、OR】，多条件判断说明：两个条件同时成立用AND,任一个成立用OR函数。★案例——判断销售额在一个区间：如下图，判断销售额在100万-150万之间的商铺，显示“是”与“否”，公式为： =IF(AND(D2>是','否')案例延伸：同样是上图，判断业态是餐饮且销售额大于100万，公式为：=IF(AND(D2>='餐饮'),'是','否')判断业态是餐饮或快时尚，公式为：=IF(OR(C2='餐饮',C2='快时尚'),'是','否')判断业态是餐饮或快时尚，且销售额大于100万，公式为：=IF(and(OR(C2='餐饮',C2='快时尚'),D2>1000000),'是','否')Happy Class二、求和公式[11/11]1【Sum】重点关注以下第6)项与第7）项，多个工作表求和和合并单元格求和：Sum主要有以下6种用法：1）对数字求和:=sum(1,2,3,4)2）对几个单元格求和：=sum(A2,C4,B3,B4,D2)3）对连续单元格求和：=sum(A2:A6)4）对列或行求和：=sum(A:A)=sum(6:6)5）对区域求和=sum(A2:F6)=sum(A2:F6,C2:G6)6）多个工作表求和如下图，假设各个sheet的格式一致，每个sheet 代表一个楼层，且该楼层的总销售额都在C1单元格，要求各楼层的总和，公式为：=SUM(负一层:四层!C1)（注：保证合计单元格在各个sheet的位置要完全一致）7）合并单元格求和如下图所示，要求在D列对C列的类别求和：=SUM(C2:C$10)-SUM(D3:D$10)公式输入方法：先选取D2:D10,在编辑栏中输入上述公式，再按ctrl+enter完成批量输入。2【Sumif】单条件求和★案例——求各业态的销售额如下图，已知各商铺的销售额，要对各业态的销售额求和，公式为：=SUMIF(C$2:C$8,F2,D$2:D$8)案例延伸：——同是上图，假设商铺号01-01中的前面两位数是楼层号，已知各商铺号，要对1层的商铺销售额进行求和，公式为：=SUMIF(B2:B8,'01*',E2:E8)——同是上图，计算商铺名称为三个字的销售额之和，公式为：=SUMIF(B2:B8,'???',E2:E8)s——注：”*”和”?”都属于通配符：*可以代表任何文字或字符（任意个数）仅代表单个文字或字符★案例——隔列求和如下图，已知各商铺的每月实际和目标销售额，要隔列求和，计算第一季度的合计公式为：=SUMIF($C$2:$H$2,I$2,$C3:$H3)（注：隔列的标题必须完全一致）3【Sumifs】多条件求和★案例——求多种条件下（如各楼层、各业态等）的销售额之和如下图，已知各商铺的销售额，要对各楼层各业态的销售额求和，公式为：=SUMIFS(E$2:E$8,A$2:A$8,G2,D$2:D$8,H2)案例延伸：（如上图）——统计”除快时尚以外”的销售额之和，公式为：=SUMIF(D2:D8,'快时尚',E2:E8)——也可以去掉行号，写成整列引用，但必须前后一致：=SUMIF(D:D,'快时尚',E:E)——统计”销售额大于100万”的销售额之和，公式为：=SUMIF(E2:E8,'>1000000',E2:E8)——sumifs可以用于无限个条件，语法为：SUMIFS(统计区域，第一条件区域，条件，[第二条件区域，第二条件....])Sumif与Sumifs易错点解析：1）sumifs与sumif语法格式几乎是相反的。Sumif的统计区域在最后，Sumifs的统计区域在最前面。2）要搞清楚绝对引用和相对引用，导致下拉公式时，需要固定的数据区域发生了变化；3）原始表格的条件区域表格要规范（不能有时是“服饰”，有时是“普通服饰“，必须严格一致）4【Sumproduct】乘积★案例——求各业态的销售额如下图，已知各商铺的面积和租金单价，要对总租金求和，公式为：=SUMPRODUCT(D2:D8,E2:E8)案例延伸：sumproduct函数也可以用于多条件求和，和多条件计数，但是数据量非常大时运行速度较慢，所以不推荐使用。运行速度最快请用：多条件求和——sumifs,多条件计数——countifs.Happy Class三、统计公式[11/11]1【Count/Counta/Countblank】简单统计如下图所示，三个函数的不同功能和结果：2【COUNTIF】单条件的统计★案例——统计符合单一条件的商铺数量如下图所示，用countifs做相关统计：计算纯保底的公式是：（注：G3单元格=”纯保底”）如上图，延伸计算：销售额大于100万的商铺数：=COUNTIF(D$2:D$19,'>1000000') & & &销售额小于等于75万的商铺数=COUNTIF(D$2:D$19,'销售额大于75万且小于100万的商铺数=COUNTIF(D$2:D$19,'3【COUNTIFS】多条件的统计在上述countif的案例中，销售额大于75万且小于100万的商铺数也可以用countifs(多条件求和)来处理：=COUNTIFS(D$2:D$19,'>750000',D$2:D$19,'★案例——统计符合多条件的商铺数量统计餐饮业态销售额大于50万的商铺数量：=countifs(C2:C19,'餐饮”,E2:E19, '>500000”)——countifs可以用于无限个条件，语法为：countifs(第一条件区域，条件，[第二条件区域，第二条件....])4【Average】【Averageif】【平均值】★案例——用average时要注意空值与0值对结果的影响。通过上图结果（销售额为0和空值在此处的情况是一样的，均为商铺未开业），空值也数值为0的单元格，会影响average的计算，空值不参加平均计算，而0值会参加平均。此处正确公式应为：=SUM(B2:D2)/COUNTIF(B2:D2,'>0')★案例——用averageifs计算工作日、节假日平均如上图，计算工作日的日均公式是：=AVERAGEIF(B2:B15,'工作日',C2:C15)——AVERAGEIF可以用于无限个条件，语法为：AVERAGEIF (统计区域，第一条件区域，条件，[第二条件区域，第二条件....])——AVERAGE与AVERAGEIF语法格式几乎是相反的。5【Rank】★案例——商铺销售额排名如上图，已知各商铺销售额，对其进行销售排名，公式为：=RANK(D2,D$2:D$19,0)Rank函数易错点解析：1）要搞清楚绝对引用和相对引用，导致下拉公式时，需要固定的数据区域发生了变化；2）后面的数字0代表降序，如果改成1则为升序。3）函数RANK对重复数值的排位相同。但重复数的存在将影响后续数值的排位。如在一列整数中，若整数60出现两次，其排位为5，则61的排位为7(没有排位为6的数值)。案例延伸：如果是多条件的排名，用的是 countifs，统计比数值大的商铺有多少个，再+1 。如下图，即公式为：=COUNTIFS(C$2:C$19,C2,D$2:D$19,'>'&D2)+16【Max、Min】两者与average的语法相同。Max找出最大值，Min找出最小值。★案例——找出商铺的最大的月销售额数值（或最小值），公式为：=MAX(B2:D2) （最小值为：=MIN(B2:D2)）7【Large、Small】★案例——找出前三名的销售额如下图，已知各商铺明细，找出前三名销售额的公式为：=LARGE(D$2:D$19,F2)如果改成后三名的公式为：=SMALL(D$2:D$19,F2)Happy Class四、查找与应用[11/11]1Lookup语法：LOOKUP(要查找的数值,查找区域,结果区域)要点： 这些数值必须按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。通常情况下，最好使用函数 HLOOKUP 或函数 VLOOKUP 来替代函数 LOOKUPV=Vertical 垂直&&&&&&&&即列H=Horizontal水平& &即行2【Vlookup】★案例——找出对应的上月销售额如下图，有本月（2月）和上月（1月）两个sheet，但两者商铺列表不一致，需要把1月销售额对应到2月的表上：公式为：=VLOOKUP(B2,上月!B:C,2,0)Vlookup函数易错点解析：以上面公式为例：=VLOOKUP(B2,上月!B:C,2,0)（注:”上月！”是指跨表之间引用；如果是同一个表引用可以省略 ）1）第一个参数“B2”，是查找对象；2）第二个区域是查找区域，查找的对象必须在查找区域对应的第一列（即上月sheet的B列）。如果在查找区域里没有找到B2，则会返回错误“#N/A”；3）第三个参数“2“，指的是返回查找区域里相应的第“2”列。查找区域必须至少包括2列，可以多，但不可以少，例如可以写成”B:Z“，但不可以写成”B:B。如果区域设置错误，会返回错误“#REF!”；4）第四个参数“0“，表示精确查找，为1或省略时表示模糊查找。如果忘了设置第4个参数则会被公式误以为是故意省略，按模糊查找进行。当区域也不符合模糊查找规则时，公式就会返回错误值；5）要搞清楚绝对引用和相对引用，导致下拉公式时，需要固定的数据区域发生了变化。3HlookupHlookup的语法和Vlookup的语法是一致的。具体用法及注意事项说明请参考Vlookup。★案例——通过行去查找以下图为例，通过行去查找管理费单价，公式为：=HLOOKUP(B2,F$1:J$2,2,0)4【Index+match】双向查找/纵横查找Vlookup、Hlookup只能应用于列与列、行与行之间查找（只能从左到右，从上到下），如果要列+行纵横查找或反向查找（从右到左，从下到上），就必须要用到【Index+match】。★案例——Index，Match分别的用法（如上图的数据案例，以下是公式功能解释：）语法：index（区域，第几行，第几列）语法：match（目标值，查找区域，0）注：这里的0指的是精确查找。★案例——Index+MatchIndex和Match单用的意义不大，要配合起来用才会彰显价值。说明：利用MATCH函数查找位置，用INDEX函数取值。如下图，多条件查找商铺销售额，公式为：=INDEX(B3:E8,MATCH(C12,A3:A8,0),MATCH(A12,B2:E2,0))（注：通过Match找到相应的行号和列号）★案例——Index+Match进阶多条件查找如下图，通过多条件去查找，需要用以数组公式：=INDEX(C3:F8,MATCH(C12&D12,A3:A8&B3:B8,0),MATCH(A12,C2:F2,0))由于公式中含有数组运算（一组数同另一组数同时运算），所以公式需要按ctrl+shift+enter三键完成输入。Happy Class五、数字处理[11/11]1.&&&& 【ABS】取绝对值=ABS(数字)2.&&&& 【INT】取整=INT(数字)3.&&&& 【Round】四舍五入=ROUND(数字,小数位数)上述三个函数的例子如下——Happy Class六、日期与时间[11/11]1日期计算1）日期有固定的输入模式，以下都可以：日1/1或1-1或1月1日(这时是如果不输年份，是默认为当前年份)更多日期格式可以参考——2）月份显示也是用日期表示，默认是每月1日。&& & & & & & & & & & & & & & & & & & & & & &&3）日期和时间的相关函数2【Date】语法：DATE(year,month,day)3【Time】语法：TIME(hour，minute，second)参数：Hour是0到23之间的数，代表小时;Minute是0到59之间的数，代表分;Second是0到59之间的数，代表秒。实例：公式“=TIME(12，10，30)”返回序列号0.51，等价于12:10:30 PM。=TIME(9，30，10)返回序列号0.40，等价于9:30:10AM。=TEXT(TIME(23，18，14)，'h:mm:ss AM/PM')返回“11:18:14 PM”。4【Dateif】=DATEDIF(开始日期,结束日期,返回什么结果)第三参数写成'm'，就是计算两个日期之间的整月数。第三参数写成'Y'，就是计算两个日期之间的整年数，这个在计算工龄的时候经常用到的。下面这几种写法，用到的比较少，大家简单了解一下就可以了。第三参数写成“MD”，返回日期中天数的差。忽略日期中的月和年。第三参数写成“YM”，返回日期中月数的差。忽略日期中的日和年。第三参数写成“YD”，日期中天数的差。忽略日期中的年。Happy Class七、文本处理[11/11]常用的文本函数如下：【LEN】LEN(text) 返回字符串中的字符数【TEXT】TEXT(value,format_text)将一数值转换为按指定数字格式表示的文本。【MID】MID(text,start_num,num_chars) 返回字符串中从指定位置开始的特定数目的字符【RIGHT】RIGHT(text,num_chars) 根据指定的字符数返回文本串中最后一个或多个字符【LEFT】LEFT(text,num_chars) 基于所指定的字符数返回文本串中的第一个或前几个字符。【Find】FIND(find_text,within_text,start_num)按指定的小数位数进行四舍五入，利用句点和逗号，以小数格式对该数设置格式，并以文字串形式返回结果。【Value】VALUE(text) 将代表数字的文字串转换成数字★案例——通过身份证号提取营业员生日=--TEXT(MID(B2,7,8),'0-00-00')公式解释：MID(B2,7,8)的意思是提取从B2单元第7位开始往后的8位数字，即，TEXT函数前的两个减号，负负为正的意思，被称为减负运算。然后用TEXT函数把数据转换成★案例——文本函数制作会议时间议程表功能：只要修改任意时长或开始时间，后面的时间全部自动生成。结束时间=开始时间+时长下一阶段的开始时间=上一阶段的结束时间时间的整体显示公式如下：=TEXT(A4,'H:MM')&'-'&TEXT(C4,'H:MM')函数大全总结前面的七大部分函数如下，方便各位小伙伴按需查找：附——函数报错的类型：【最后，关于EXCEL的函数应用】理解：因为函数是英文，所以英文转化为汉语，汉语转化为真实含义，都是一个过程。理解之后，使用起来就会得心应手。运用：许多函数忘记了，是因为基本用不上，把好用的函数用起来，后面就会越用越上瘾。目标：是偷懒，偷懒的心，其实是前期设置好，后期自动化。心法：是解决问题的总思路。这个学会了，所有的方法技巧，都可以随便就能拿过来。希望大家可以灵活运用以上套路，提高工作效率，告别不必要的加班~~~最后，EXCEL祝大家光棍节快乐~~~~~~EXCEL你丫是故意的吗？编辑：营运部杨琪琪
TA的最新馆藏
喜欢该文的人也喜欢查看: 542|回复: 2
把c列的拼音根据E列或者G列的字符复制到H列
阅读权限10
在线时间 小时
(1.02 MB, 下载次数: 10)
14:17 上传
点击文件名下载附件
(46.41 KB, 下载次数: 6)
14:17 上传
阅读权限20
在线时间 小时
=VLOOKUP(G2,B:F,2,)复制代码
阅读权限20
在线时间 小时
I列：=VLOOKUP(G2,B:F,5,)
最新热点 /1
ExcelHome每周都有线上直播公开课，
国内一流讲师真身分享，高手贴身答疑，
赶不上直播还能看录像，
关键居然是免费的！
厚木哥们都已经这么努力了，
你还好意思说学不好Office。
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量-学路网-学习路上 有我相伴
如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量
来源：互联网 &责任编辑：王小亮 &
如何用sumif求和对于这类问题,最好的办法是创建一个隐藏的工作表用第1列计算汇总值,从第2列开始,第1行输入各工作表名在B2输入公式=SUMIF(INDIRECT(B$1&&!M3:M7&),&...在WPS中如何用SUMIF函数求和举个例子吧,公式:=SUMIF(A:A,&A&,B:B)意思就是求A列的名称是字母A所对应的B列的价格求和。公式的格式是:=SUMIF(关键字所在的区域,&关键字&,对应...用EXCEL的SUMIF求和怎样用?=SUMIF(B:B,&二组&,E:E)如何用sumif计数而不是求和白名字现5订单页面合计5用countif函数怎么用格sumif函数求和物料名称相同的数量和金额?提问也太笼统了吧,应该用sumif函数分别求出数量、金额的和如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量(图2)如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量(图9)如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量(图13)如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量(图19)如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量(图21)如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量(图24)这是用户提出的一个学习问题,具体问题为:如何用sumif求和已知：A列为产品名,B列为第一个单位购买的数量,C列为第一个单位购买数量对应的金额,D列为数量,E列为金额,F列为数量,G列为金额,H列为产品名所对应的总数量,如何用sumif求H列的总数量怎么用格sumif函数求和物料名称相同的数量和金额?提问也太笼统了吧,应该用sumif函数分别求出数量、金额的和防抓取，学路网提供内容。我们通过互联网以及本网用户共同努力为此问题提供了相关答案,以便碰到此类问题的同学参考学习,请注意,我们不能保证答案的准确性,仅供参考,具体如下:excel中如何用sumif函数实现,如下条件求和=sumproduct(isnumber(find(&其他&,a1:a10))*(len(a1:a10)=8))07版用sumif防抓取，学路网提供内容。用户都认为优质的答案:请告诉我在EXCEL表格中,怎样用Sumif公式求和啊?=sumif(B:B,&张三&,D:D)公式意思是B列是张三的取D列对应值的合计。防抓取，学路网提供内容。对于这类问题,最好的办法是创建一个隐藏的工作表excel用SUMIF函数进行条件求和SUMIF(条件区域,条件表达式,求和区域)例如下面的公式,是求当A列里面大于3的同行的C列的和sumif(A1:A10,&&3&,C1防抓取，学路网提供内容。用第1列计算汇总值,从第2列开始,第1行输入各工作表名EXCELSUMIF求和请看附件中A1单元格中的公式!!!附件:答EXCELSUMIF求和.xls防抓取，学路网提供内容。在B2输入公式怎样用sumif求和,比如A列名称为各种物资,B列为各种型号,C列...=sumif(A:A,&物资名称&,D:D)这个的得数是输入的物资名称的物资,被领用的总和防抓取，学路网提供内容。=SUMIF(INDIRECT(B$1&"!M3:M7"),'0'!$B4,INDIRECT(B$1&"!N3:N7"))linux中su怎么用问：我用Ubuntulinux无法加载U盘，用fdisk_l命令无法打开sda硬盘,U盘应该...答：1.命令作用su的作用是变更为其它使用者的身份，超级用户除外，需要键入该使用防抓取，学路网提供内容。向右,向下复制su使用方法答：su使用方法：第一步：把图片导入PS，抠掉多余的背景。第二步：“图象”――“图象大斜，设置宽度或高度，常把乔木设置为800至1000像素，灌木设置为500以内。否则制作出的SU组件文件防抓取，学路网提供内容。在A2输入公式“supersu”怎么用？答：一、首先打开SuperSU界面会有三个选项：1.应用程序2.日志3.设置1、打开应用程序：列表上是授权或者没授权的程序2、点开程序弹出对话框有权限、通知、日志、用户角色等防抓取，学路网提供内容。=SUM(B2:IV2)Linux下su命令怎么使用答：脚本内容:cattest.sh代码如下:#!/bin/bashsu-test防抓取，学路网提供内容。向下复制在汇总表中,直接引用这个辅助表的A列数据就可以了.CAD中,SU这个命令该怎么用答：SU是差集命令，画两个大小不一的圆，拉伸成实体，用SU命令大圆减小圆，中间就变成了空的，差集命令，第一次要求选择对象时选择父体（被减对象），回车；第二次要求选择对象时防抓取，学路网提供内容。为了看起来比较方便,可以把工作表隐藏起来3dmax的模型如何用sketchup打开？问：3DS文件，请问如何转成SU能打开的格式。答：并不需要另外转换。打开sketchup，然后点击文件-导入，在弹出的对话框里的文件类型选择3DS(*.3D防抓取，学路网提供内容。格式→工作表→隐藏googlesketchup8插件如何使用答：1、*.exe格式的直接双击安装就可以了；2、*.rb格式的直接复制到SketchUp的安装目录下的Plugins文件夹内就可以了！3、*.rbz格式的插防抓取，学路网提供内容。如果将来要修改这个辅助表,还可以取消隐藏怎么用sketchup画不规则弧线啊?答：SU默认的弧线其实是多段短小的直线构成的，和圆是一样的，可以选择弧线工具之后输入数字在决定短直线的条数，另外，我觉得题目有2个歧义，一种就是“为什么用SU画的防抓取，学路网提供内容。格式→工作表→取消隐藏,选择要取消隐藏的工作表,确定.sketchup如何清理未使用材质问：sketchup如何清理未使用材质答：1、打开【窗口】；2、选择【模型信息】；3、找到【统计信息】；4、选择【整个模型】；5、点击【清理未使用项】即可。防抓取，学路网提供内容。excel中如何用sumif函数实现,如下条件求和=sumproduct(isnumber(find(&其他&,a1:a10))*(len(a1:a10)=8))07版用sumifs请告诉我在EXCEL表格中,怎样用Sumif公式求和啊?=sumif(B:B,&张三&,D:D)公式意思是B列是张三的取D列对应值的合计。excel用SUMIF函数进行条件求和SUMIF(条件区域,条件表达式,求和区域)例如下面的公式,是求当A列里面大于3的同行的C列的和sumif(A1:A10,&&3&,C1:C10)EXCELSUMIF求和请看附件中A1单元格中的公式!!!附件:答EXCELSUMIF求和.xls
相关信息：
- Copyright & 2017 www.xue63.com All Rights Reserved