Record

区块链记录你的初心
地产创业创新求职面试Word技巧金融职场工作区块链Excel教程财经PPT教程产品运营
Excel教程:这个函数组合轻松解决你的工作难题!

Excel教程:这个函数组合轻松解决你的工作难题!

函数函数组合COUNTIF

在Excel中有一些非常经典的函数组合,大家比较熟悉的有INDEX-MATCH组合,还有INDEX-SMALL-IF-ROW组合(也叫万金油组合),当然还有很多其他的组合,今天分享的这个组合同样非常有用,下面会通过四个常见的问题让大家见证这对组合所带来的美妙时刻,当然还是要先认识一下今天的两个主角:COUNTIF和IF,这两个大伙都非常熟悉的函数。COUNTIF函数的用法:COUNTIF(范围,条件),函数可以得到符合条件的数据在范围中出现的次数,简单来说这个函数就是条件计数用的;IF函数的用法:IF(条件,满足条件的结果,不满足条件的结果),用一句话来说,如果给IF一个条件(第一参数),当条件成立的时候给返回一个结果(第二参数),当条件不成立的时候返回另一个结果(第三参数)。关于这两个函数的基本用法,之前的教程多次讲过,不再赘述,下面先来看看他们两相遇以后发生的第一个问题:核对订单时遇上的问题假设A列是全部的订单号,D列是已经发货的订单号,现在需要在B列对已发货的订单进行标记(为了防止大家眼花,箭头仅指出了两个对应的订单号):对于这个问题,我想各位一定不陌生,这问题在对账的时候经常用吧,也可能有些小伙伴已经迫不及待的喊着VLOOKUP了,实际上B列的公式是这样的:=IF(COUNTIF(D:D,A2)0,已发货,)首先用COUNTIF进行统计,看A2单元格的订单号在D列出现了几次,如果没有出现的话
Excel教程:不会用加了*号的sumproduct函数?亏大了!

Excel教程:不会用加了*号的sumproduct函数?亏大了!

函数公式函数公式

今天带大家来认识一个简单而又复杂的函数:SUMPRODUCT。01SUMPRODUCT的实质要说SUMPRODUCT函数的话,真的非常简单,就是得到两列数据的乘积之和,我们用一个简单的例子来说明函数的基本功能:上图是一个非常简单的表格,要算出总价一般都是将单价*数量算出来再求和,结果如D8所示。如果使用了SUMPRODUCT函数的话,就可以直接利用单价和数量计算出总价,公式1为:=SUMPRODUCT(B2:B7,C2:C7),结果如D9所示。在这个公式里,使用了两个参数,分别是单价区域(B2:B7)和数量区域(C2:C7),函数的作用就是将第一参数(单价)与第二参数(数量)中的数据对应相乘后再求和。02大多数错误的原因很多朋友在使用这个函数的时候,经常会得到错误值,大多数是因为区域大小选择不一致,例如下面这种情况,第一个参数有7个单元格而第二个参数只有6个单元格:使用SUMPRODUCT函数必须要确保每个参数的区域大小相同,但很多朋友没有注意到这一点。03另一种常见写法,逗号变乘号(*)就这个例子来说,还有一种写法更为常见,公式是这样的:=SUMPRODUCT(B2:B7*C2:C7)可以看到其计算结果与=SUMPRODUCT(B2:B7,C2:C7)是一致的。一致的结果导致了很多朋友都百思不得其解的一个问题:二者有何差别?04逗号和乘号(*)的差别虽然只是将第一个公式里的逗号变成了乘号(*),但是公式的意义发生了变化。第一个公式(SUMPRODUCT(B2:B7,C2:C7))有两个参数,而第二个公式(B2:B7*C2:C7)是一个参数。(判断有几个参数要看是不是有逗号去分隔开。)第一个公式中,两个区域相乘这一步是由函数来完成的,函数做了两件事,先让两个区域的数据对应相乘,再把乘积相加。在第二个公式中,两个区域相乘是由数组计算来完成的,函数只做了一件事,就是把乘积值相加。意义的变化有何影响呢?我们还是通过例子来看:在上图这个公式中用的是逗号(,),有两个独立的参数。SUMPRODUCT函数首先让两组数据对应相乘,相乘的时候会检查数据并把非数值型数据作为0处理,然后在把乘积相加。因此,B1单价和C1数量会当成0来处理,公式可以得到正确结果。当我们把逗号换成*号后,公式结果错误。为什么呢?SUMPRODUCT函数这时只负责把乘积相加。参数B2:B7*C2:C7是数组乘法运算,因为计算的区域中包含了文字(文字是不能进行乘法运算的),所以在这个数组的计算结果里就有错误值了。选中公式中的B2:B7*C2:C7按F9可以查看B2:B7*C2:C7的运算结果:可以看到第一个(单价*数量)运算结果就是错误值。接下来SUMPRODUCT对包含了错误值的数据进行求和,结果肯定就是错误了。05SUMPRODUCT用乘号(*)的要点以上内容所要表达的意思有两点:第一,使用逗号和使用*号有时候结果相同,但是意义完全不一样,希望大家可以理解。第二,SUMPRODUCT函数使用乘
Excel教程:这位95后做的excel图表遭曝光……

Excel教程:这位95后做的excel图表遭曝光……

图表图表美化图表

日月轮回,斗转星移,江湖上,新一代武林盟主厌倦世事纷扰,已生退意,但一直未能寻到德高望重之接班人,故下令,一年为限,哪位正义人士弑杀魔教人最多,将接任新的武林盟主之位。从此,武林杀机四起,江湖腥风血雨。时间已过半,某日大殿之上,探子前来报告:盟主,半年以来,各路英雄奋勇杀敌,已令魔教之徒胆战心惊、死伤过半,这是前6位英雄杀敌数量。盟主接过ipad见一张excel表,喜上眉梢,洋洋得意。但瞬间表情却由喜转怒,大发雷霆:你们的excel水平怎如此不堪,甚至不及魔教十分之一功力,如此下去,太平江湖终毁尔手,呜呼,痛哉,悲哉众人顿感羞愧,却又不敢吱声,盟主无奈,当场投影演示道:尔等辅助打理事宜,虽已尽力,但更需尽心,原始数据分析,尽交我手,此并非高效之做法矣,今老夫举一例,望能达抛砖引玉之效。以下GIF效果,方是我等追求的最低标准。常言道:练气不练功,到老一场空;练功不练气,到老白费力。修炼excel之道分为内功和外功,今天,老衲就带领大家修炼内功心法之入门篇动态图表最简单的效果制作。动态图表第一式:制作下拉菜单第一式本质上是外功修炼的内容,所以说内功修炼要有一定的外功修炼基础,内外结合,方可练就上称功力。下拉菜单的制作非常简单,点中A10
Excel教程:选择性粘贴有这么多功能,你还只会ctrl+V?

Excel教程:选择性粘贴有这么多功能,你还只会ctrl+V?

选择性粘贴

一说起复制粘贴,很多小伙伴脑袋里的第一个想法就是ctrl+C和ctrl+V,其实这样的操作实现的功能是很有限的。当我们学会了excel中的选择性粘贴工具,就可以满足实际工作中的各种需求。 1.选择性粘贴做数据运算 每到月初,小伙伴辛辛苦苦做完了表格,马上就要上交表格发工资了,老板却突然说所有的员工工资要增加100元,直接增加在工资列数据中。这个时候小伙伴的第一个想法是用sum函数来解决,其实用选择性粘贴更加便利。 在表格旁边的空白单元格输入数字100,选中该单元格,按ctrl+c复制。 选中工资列的所有数据,在“开始”选项卡下单击“粘贴”按钮下拉菜单,点击“选择性粘贴”。 在弹出的对话框中,选中“数值”和“加”。 此时,工资列的数据都增加了100。 其实除了可以实现加法运算,乘除法和减法的也可以实现,操作和上面类似。 2、选择性粘贴实现数据核对 不知道在前面地讲解中,小伙伴有没有注意到,“选择性粘贴”对话框中有一个“跳过空单元格”的选项,你知道怎么用吗? 在公司中,经常会两个人或多个人一起做工资的统计,之后就需要做数据的汇总。下面就是两个人做的表格,我们需要将数据汇总合并到一列,并找出还没有被统计工资的人员。当数据非常多时,人眼核对是不可能实现的,而小伙伴第一个想法是用VOOLKUP函数来解决,其实同样可以用选择性粘贴来实现。 很简单,选中第二列工资区域,按ctrl+c复制。 再选中第
“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

Excel公式

之前给大家推送了一篇用PQ完成跨表数据核对的教程:《我折腾到半夜,同事用这个Excel技巧,30秒跨表核对数据交给领导!》,但由于版本限制,好多伙伴都无法使用,今天给大家介绍3个公式,同样可以完成数据核对。问题是如何根据单据编号和物料长代码返回对应的含税数额。如下表:其实这位学员的问题就是如何实现多条件查询。下面通过一个实例跟大家分享一下常用的几种多条件查询方法。下表是某电商公司的客户投诉表,现在需要通过A表中的客户姓名与地区两个条件来查询B表中的产品型号,返回到A表的E列中。1.lookup函数函数公式:=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)公式解析:首先通过A3单元格与B表I列数据做对比,同时用B3单元格与B表J列信息做对比。在excel中如果两个单元格对比,相等则返回TRUE,在四则运算中用1表示。如果不相等则返回FALSE,使用0表示。那么(A3=$I$3:$I$19)*(B3=$J$3:$J$19)这部分运算的结果就只有0或者1两种情况,因为只有0*1、1*1、1*0这三种情况。用0来除以0和1,由于分母不能为0,所以0/0返回的是错误,0/1返回的结果为0。Lookup函数在查找的时候是忽略错误的,所以只有数据运算结果为1的公式满足条件。那么我们就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是将正确结果用0表示,其他的变成错误值,利用函数查找忽略错误这个特点完成查找。总结:本函数由于使用了二分法原理查找,所以如果数据量较大时运算会很慢。2.VLOOKUP函数使用G2单元格在A列中查找,如果查找到对应单元格则返回A列向右第二列的数据。简而言之:=VLOOKUP(查找什么,在哪查找,从条件所在列算起找到后返回对应的第几列数据,精确或模糊查找)。那vlookup如何才能完成多条件查询呢?。还以客户投诉表为例,按照姓名地区来匹配产品型号返回到E里中。其实我们是可以将A、B两表中插入辅助列,将姓名和地区都合并到一个
VLOOKUP&LOOKUP双雄战(三):LOOKUP守得云开见月明

VLOOKUP&LOOKUP双雄战(三):LOOKUP守得云开见月明

LOOKUP

在前三个回合的较量中,LOOKUP处于下风。第四回合的比赛题目是“区间查询”,简单来说,就是判断某个数值属于哪个区间哪个等级。虽说VLOOKUP和LOOKUP都将利用二分法原理完成区间查询,但二分法是LOOKUP唯一的、根本的内功心法,LOOKUP能否凭此守得云开见月明呢? “年少万兜鍪,坐断东南战未休。”VLOOKUP和LOOKUP的故事还在继续,刀锋未休!连续吃瘪的LOOKUP试图挽住颓势,“二分法”千呼万唤始出来,精彩马上开始! ROUND 04 区间查询 在数值查询中,我们经常需要查找数值所对应的区间。一个经典的问题就是学生成绩等级评定,0-60(不含60)为不及格,60-75(不含75)为及格,75-85(不含85)为良好,85以上为优秀。面对这种问题,你是否还在用IF函数反复嵌套? 太OUT了!!!快来看看VLOOKUP和LOOKUP是怎么做的吧? 在使用这两个函数之前,我们必须按下图所示,对各区间及对应值进行升序排列: 1.将数值区间的分界值按升序依次填入连续的单元格,即从最小值到最大值,自上而下填入同一列单元格中。 2.各区间分界值采用区间下界值,例如,“及格”区间数值60,“优秀”区间数值85。 3.如果最小的数值区间无下界,也必须赋予一个足够小的数值,例如-8∧8,否则公式可能会报错。 图3.2:区间查询——查找范围 接下来就是VLOOKUP和LOOKUP展现战斗力的时候了!!! VLOOKUP:"一"字之差,难得模糊 VLOOKUP函数的解决之道和其基础用法非常类似,只需将最后一个参数更改为1或TRUE即可。 =VLOOKUP(B2,$E$2:$F$5,2,1) 图3.3:区间查询——VLOOKUP"一"字之差 公式说明 最末参数是0的时候,VLOOKUP精确查找,采用遍历法原理将查找