• 函数365之MID函数:字符提取哪家强,文本函数MID最强

    Excel函数2017-4-140评论334
    函数名称:MID 函数作用:MID函数用于从字符串中指定的位置开始从左往右截取指定的字符个数。 函数语法:MID(文本字符串或者单元格引用,从第几个字符开始,截取多少个字符) 注意事项: 1、第1参数为必选参数。表示要提取字符的文本字符串或者单元格引用。 2、第2参数为必选参数。表示要在文本中第几个字符的位置开始提取字符,最小值为1,如果该值大于文本的总长度,则MID结果为空文本""。 3、第3参数为必选参数。表示总共需要截取多少个字符,最小值为0,如果该值大于总的文本长度,则最多也只会截取到文本的最后一个字符。 4、如果第2参数小于1或者第3参数小于0,MID的结果都为#VALUE!。 函数应用: 实例1、根据下表数据,从身份证号码中提取出相应的出生年月日。 函数公式:C2单元格输入 =MID(B2,7,8),公式下拉即可 解释说明: 1、根据身份证规则,从第7位数字开始往后8位数字表示出生年月日,其中年份4位,月份2位,日期2位; 2、所以这里用MID函数从身份证号码的第7个位置开始截取8个字符,也就是第2和第3参数分别为7和8. 实例2、根据下表A列的诗句,分别将每一句时提取到一个单元格中,如BCDE列所示。 函数公式:B2单元格输入 =MID($A2,8*COLUMN(A1)-7,7),公式右拉下拉即可 解释说明: 1、根据观察A列的都是七言诗,也就是每句7个字,所以第1句诗的位置是1-7,由于还有一个标点符号,所以第2句诗的位置是9-15,同理第3句诗位置是17-23,第4句诗的位置是25-31; 2、根据上面分析每一句诗的起始位置分别是1、9、17、25,MID函数公式右拉的时候第二参数要分别是这几个数,这就涉及我们前面讲的等差数列。(可以微信公众号回复“COLUMN”获取相关函数文章); 3、我们可以将1、9、17、25看成是首项是1,公差为8的等差数列,需要右拉,所以公式为8*COLUMN(A1)-7,这个作为MID的第二参数,然后每句诗7个字,所以第三参数为7。 实例3、从A列的员工信息中提取出其中的电话号码,如B列所示。 函数公式:B2单元格输入 =MID(A2,FIND(":",A2)+1,99),公式下拉即可 解释说明: 1、我们看到每一个员工信息里面都有一个冒号“:”,所以我们可以用FIND函数查找这个“:”在字符里面的位置FIND(":",A2),而这个位置的下一个位置FIND(":",A2)+1就是第一个电话号码数字的位置; 2、第一个电话号码数字的位置就是MID函数的第2参数,也就是前面提到的FIND(":",A2)+1,由于我们不知道电话号码有多少个字符,所以只能多不能少,这里用了99,也可以更大一点999。 实例4、根据下表A列的文本字符串,计算有多少个数字,效果如B列。 函数公式:B2单元格输入 =COUNT(-MID(A2,ROW($1:$99),1)),数组公式需要CTRL+SHIFT+回车键三键结束,公式下拉 解释说明: 1、要判断字符串里面数字的个数,那么就要判断每个字符是否数字,所以先要将每个字符单独提取出来判断; 2、用MID函数依次从第1,2,3,4,…,99个位置开始截取字符,每次截取一个字符,就可以把每个字符提取MID(A2,ROW($1:$99),1); 3、在每个提取出来的字符前面加个负号-,这样就可以将数字变成负数,将文本变成错误值#VALUE!,然后再用COUNT函数计算数字的个数。 4、这里利用了COUNT函数计数时只会计算数字,忽略错误值的特点。
  • 函数365之IFERROR函数:人生不可以重来,但是IFERROR可以

    Excel函数2017-4-130评论512
    函数名称:IFERROR 函数作用:IFERROR函数用于当公式的计算结果为错误值时返回您指定的值,否则将返回公式的结果。 函数语法:IFERROR(需要计算的公式,当公式错误时指定返回的内容) 注意事项: 1、两个参数都为必选参数,如果省略参数值则默认为空""; 2、公式错误指的的公式的结果为#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!。 函数应用: 实例1、根据下表2月份和3月份的销量计算同比增长率,同比增长率=(本月销量-上月销量)/上月销量,要求效果如D列。 函数公式:D2单元格输入 =IFERROR((C2-B2)/B2,"上月无销量"),公式下拉即可 解释说明: 1、根据D列效果要求上月销量为0的时候显示“上月无销量”; 2、根据计算公式,当上月销量为0时则是除数为0,同比增长率将为#DIV/0!错误值; 3、所以同比增长率公式作为IFERROR的第一个参数,“上月无销量”作为IFERROR的第二参数,这样就满足了当同比增长率计算为错误值时显示“上月无销量”。 实例2、根据下表的数据,然后根据指定的姓名查找相应的销量。 函数公式:G2单元格输入 =IFERROR(VLOOKUP(F2,$A$2:$B$11,2,0),VLOOKUP(F2,$C$2:$D$11,2,0)),公式下拉即可 解释说明: 1、本题的难点在于姓名有两列,如果我们用VLOOKUP函数查找时无法确定查找区域; 2、我们可以先在第一列姓名的$A$2:$B$11区域进行查找,如果查找不到VLOOKUP函数将会返回#N/A错误值,我们再在$C$2:$D$11区域查找; 3、所以VLOOKUP(F2,$A$2:$B$11,2,0)作为IFERROR函数第一参数,VLOOKUP(F2,$C$2:$D$11,2,0)函数作为IFERROR函数第二参数。 实例3、根据下表的数据,其中A列编号有数值型数字和文本型数字两种混杂一起,然后根据指定的编号(数值型数字)查找相应的姓名。 函数公式:F2单元格输入 =IFERROR(VLOOKUP(E2,$A$2:$B$11,2,0),VLOOKUP(E2&"",$A$2:$B$11,2,0)),公式下拉即可 解释说明: 1、查找用VLOOKUP函数,当时同样一个数字,文本型的和数值型的是无法匹配的,这个时候VLOOKUP函数就会返回#N/A错误值; 2、这里就考我们如果将查找条件的数值型数值转换成文本型数值,数值型数值转换文本型数值只需要在在数值型数值后面用连接符号&连接空格""即可,如果是文本型数值转换成数值型数值那就需要在文本型数值前面加双负号--; 3、所以这里通过IFERROR函数让VLOOKUP函数查找两次,第一次直接匹配条件查找,当出错时就将查找条件连接空格E2&""转换数值类型再查找一次。 实例4、根据下表数据对B列的销量进行求和。 函数公式:B12单元格输入 =SUM(IFERROR(B2:B11,0)),数组公式需要CTRL+SHIFT+回车键三键结束 解释说明: 1、因为B列中存在错误值#N/A和#NAME?,所以我们无法直接用SUM进行求和,否则SUM结果也会是错误值; 2、我们可以用IFERROR函数分别判断B2:B11的每一个单元格是否为错误值,如果是则指定其结果为0,最后将所有结果用SUM求和。
  • 为什么这么帅这么标准的条件计数公式结果会不准确?

    Excel基础2017-4-120评论425
           看到上面的图片你发现什么问题了吗?要求根据入职时间计算人数,这明明是很简单的条件计数,函数公式也写得中规中矩,=COUNTIF($D$2:$D$21,F2),第1参数计数区域,第2参数计数条件,一切看起来很帅很标准嘛,可是我们再看一下结果,what?<3个月的人数居然有20人?那不是全部人数都是小于3个月?另外>3年的人数居然是0,可是D列的数据可不是这样的啊,这到底是怎么了?        问题就出在数据D列里面的“<3个月”和“>3年”里面以及条件里面的比较运算符“>”和“<”,公式=COUNTIF($D$2:$D$21,F2)相当于=COUNTIF($D$2:$D$21,“<3个月”),计算的时候Excel会认为我们是需要统计D列里面小于“3个月”的数据个数,就会将D2:D21里面的每一个单元格与“3个月”做比较,例如“="1年~3年"<"3个月"”,我们在单元格输入就会发现它返回的是TRUE,也就是将“1年~3年”与“3个月”做比较的时候,它认为“3个月”比较大一点,至于这是什么见鬼的逻辑我们就不深究了,反正文本之间比较大小都是很复杂的,所以最终计算就会得出这些乱七八糟稀奇古怪的结果,那么我们要怎么做才能得到正确的结果呢? 我们现在再来看上面这张图与最开始的时候有什么区别?我们会发现两个表里面原来第一个表的“>”和“<”都分别变成了“>”和“<”,这是插入里面的中文的符号,并不是比较运算符,虽然看起来很相似,这个时候我们在用刚才的公式进行计数就可以得到正确的结果了。 结束语  一路走来,太累了,停下来歇歇吧,多休息一会,是为了走更远的路。抬头看看天,看乌云的缝隙里钻出的斑驳阳光,重新拾起前行的勇气和信心;回首望望来路,想想丢下了什么,还有什么可以丢下的,只要心灵轻松些,任何放弃都是一种努力。坚定地走吧,毕竟梦想在远方,未来在远方,终点也在远方。
  • 函数365之COLUMN函数:欲问列数何人知,函数COLUMN来相告

    Excel函数2017-4-120评论339
    函数名称:COLUMN 函数作用:COLUMN函数用于返回参数引用的单元格区域所在的列号。 函数语法:COLUMN(单元格引用或区域引用) 注意事项: 1、COLUMN函数最多只有一个参数,且为可选参数,当省略该参数时,表示返回函数所在单元格的所在列号,例如=COLUMN(),公式如果在A10单元格,则结果为1,公式如果在F10,则结果为6; 2、COLUMN函数的参数可以是单个的单元格引用,或者区域引用,但是不能多个不连续单元格或不连续的区域引用; 函数应用: 实例1、在B1单元格输入公式,公式右拉后得到1,2,3,4,…,10的一个序列。 函数公式:B1单元格输入 =COLUMN(A1) 或者 =COLUMN()-1,公式右拉即可 解释说明: 1、凡是与右拉序列有关的第一时间考虑COLUMN函数,我们都知道相对引用单元格A1右拉后会依次得到B1,C1,D1…单元格,所以我们只要依次返回这些单元格所在的列号就可以得到我们要的结果,而返回单元格所在列号正是COLUMN函数的拿手好戏; 2、因为公式是从B1单元格开始的,如果我们直接返回当前单元格所在行号COLUMN()那么将会得到2,而要求是从1开始,所以公式也可以写成=COLUMN()-1。 3、如果要构造下拉序列的请用ROW函数。 实例2、用公式构造如下图第一行等差数列,首项是2,公差是4(也就是后面一个数字比前面一个数字大4)。 函数公式:B1单元格输入 =4*COLUMN(A1)-2,公式右拉即可 解释说明: 1、这里就不说解说原理了,直接上公式定理,要右拉公式构造“首项是M,公差是N的等差数列”,直接套用公式“=N*COLUMN(A1)+(M-N)”,万能公式,不灵可以随时找我。 2、同样的,如果要下拉公式构造“首项是M,公差是N的等差数列”,直接套用公式“=N*ROW(A1)+(M-N)”。 实例3、在B2单元格输入公式,公式右拉下拉后生成如下图的九九乘法表。 函数公式:B2单元格输入 =IF(AND(ROW(A1)>=COLUMN(A1),ROW(A1)<10),ROW(A1)&"x"&COLUMN(A1)&"="&ROW(A1)*COLUMN(A1),""),公式右拉下拉即可 解释说明: 1、通过观察上面的九九乘法表会有一个规范,第1个乘数最大只能是9,而且每个单元格的等式第1个乘数都是大于等于第2个乘数的,所以AND(ROW(A1)>=COLUMN(A1),ROW(A1)<10)才有等式,否则为空; 2、分别用ROW函数和COLUMN函数构造等式的第1个和第2个乘数,然后用连接符号&连接等式与结果。 实例4、根据下表数据按照指定姓名查找对应月份的数值。 函数公式:=VLOOKUP($A9,$A$2:$F$6,COLUMN(B1),0),公式右拉即可 解释说明:本题主要考的是VLOOKUP函数第3参数返回列数,这里需要依次返回第2,3,4,5,6列,所以用COLUMN(B1)表示,右拉即可获得我们需要的数值,另外还要注意查找条件必须要列绝对引用或绝对引用。
  • 为什么你的公式无法双击填充?

    Excel基础2017-4-110评论1583
    我们学公式的时候都知道,当公式完成后鼠标放到单元格右下角会有个黑色的十字,双击左键可以快速填充公式。但是当我们遇到下图这种情况的时候,直接双击公式却无法填充,这是为什么呢? PS:千万别想着手动下拉填充公式,这个表可是有25000行数据,手动下拉那可不是三两分钟能搞定的事 Excel双击填充公式是依据公式前面一列的数据来进行填充的,我们之所以无法双击填充公式,是因为公式前面的一列是空的没有任何数据,所以Excel就傻了不知道该怎么办了,那么这个时候我们该怎么做才能快速填充公式呢?下面我们来介绍三种常用的解决办法。 1、删掉空白列 最简单的办法当然是直接删掉公式前面的两列空白列,然后双击填充公式啦。当然可能有些实际情况不允许我们删列,那么我们接着看下一种办法。 2、剪切插入列 我们可以直接选中公式那一列,剪切,然后选中B列,插入剪切,双击就可以填充公式了,然后再把公式列剪切回原来的位置,如下图。当然这个办法比较麻烦,操作过程中还变动了字段的顺序,有没有更好一点的办法呢?我们接着看下一种办法。 3、快速选定单元格区域 我们可以直接在名称框里面输入“H2:H25000”,然后按回车键就可以快速的选中这一个区域,接着按CTRL+D就可以快速填充这个区域的公式。这是一个非常实用的操作,我们很多情况下需要快速选定一个范围很大的区域的时候可以直接这么操作,快速而精准。 PS:上面几种办法或许还不是最简单的,但是一定是最常用的。 结束语 这些天的公众号关注人数一直在缓缓增长,可是阅读量却一直停滞不前,少得可怜,看来我还有很多地方做得不够好,没关系,继续努力,加油吧!村长!你一定可以越做越好的!
  • 函数365之ROW函数:行有行规之ROW有ROW法

    Excel函数2017-4-110评论391
    函数名称:ROW     函数作用:ROW函数用于返回参数引用的单元格区域所在的行号。 函数语法:ROW(单元格引用或区域引用) 注意事项: 1、ROW函数最多只有一个参数,且为可选参数,当省略该参数时,表示返回函数所在单元格的所在行号,例如=ROW(),公式如果在A10单元格,则结果为10,公式如果在F15,则结果为15; 2、ROW函数的参数可以是单个的单元格引用,或者区域引用,但是不能多个不连续单元格或不连续的区域引用; 函数应用: 实例1、在A2单元格输入公式,公式下拉后得到1,2,3,4,…,10的一个序列。 函数公式:A2单元格输入 =ROW(A1) 或者 =ROW()-1,公式下拉即可 解释说明: 1、凡是与下拉序列有关的第一时间考虑ROW函数,我们都知道相对引用单元格A1下拉后会依次得到A2,A3,A4…单元格,所以我们只要依次返回这些单元格所在的行号就可以得到我们要的结果,而返回单元格所在行号正是ROW函数的拿手好戏; 2、因为公式是从A2单元格开始的,如果我们直接返回当前单元格所在行号ROW()那么将会得到2,而要求是从1开始,所以公式也可以写成=ROW()-1。 实例2、用公式构造如下图A列等差数列,首项是2,公差是3(也就是后面一个数字比前面一个数字大3)。 函数公式:A2单元格输入 =3*ROW(A1)-1,公式下拉即可 解释说明:这里就不说解说原理了,直接上公式定理,要下拉公式构造“首项是M,公差是N的等差数列”,直接套用公式“=N*ROW(A1)+(M-N)”,万能公式,不灵可以随时找我。 实例3、下表是某工厂员工工资条的部分信息,要求根据ABCD四列数据提取信息到FGHI列,效果如图所示。 函数公式:F2单元格输入 =INDEX(A:A,3*ROW(A1)-1),公式右拉下拉即可 解释说明: 1、本题主要考的我们对INDEX函数的掌握和对等差序列的构造能力,我们可以看到我们需要的信息分别在第2,5,8,11…行,这是一个首项是2,公差是3的等差序列,看实例2; 2、根据数据所在的行号返回所在的内容这是INDEX函数的拿手好戏,所以直接 =INDEX(A:A,3*ROW(A1)-1)即可,注意A:A不要用绝对引用。 实例4、根据下表A列数据提取相应的数值,效果如B列所示。 函数公式:B2单元格输入 =LOOKUP(9^9,--LEFT(A2,ROW($1:$99))),公式下拉即可 解释说明: 1、首先通过观察我们发现每个字符串都是数字在最左边,所以我们可以用LEFT函数依次从左边截取1,2,3,4…99个字符,1-99可以表示成ROW($1:$99),这里1:99表示整行引用,第1到第99行; 2、然后我们将LEFT函数的结果用双负号“--”将文本型的数字转换成数值型的数字,将文本转换成错误值; 3、最后我们利用LOOKUP函数二分法查找的特性,在--LEFT(A2,ROW($1:$99))里面查找9^9,因为都没有一个数字比9^9大,所以最终的结果会取到--LEFT(A2,ROW($1:$99))里面的最后一个数字; 实例5、根据下表A、B、C三列数据,然后按照指定姓名查找对应的科目及分数,效果如F列和G列所示。 函数公式:F2单元格输入 =IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$11=$E$2,ROW($2:$11),9^9),ROW(A1))),""),公式右拉下拉即可 解释说明: 1、用IF判断$A$2:$A$11区域中的每个单元格是否与指定姓名E2相等,如果相等则返回单元格所在相应的行号ROW($2:$11),否则返回9^9; 2、然后用SMALL函数将IF的所有结果从小到大取值,再用INDEX函数根据SMALL的值返回所在行的内容; 3、为避免出现错误值,INDEX外面还要嵌套一个IFERROR纠错函数。
  • Excel单元格地址四种引用样式(入门必看,高手请无视之)

    Excel基础2017-4-100评论338
    单元格地址的引用形式是所有想要学习Excel函数与公式的朋友都必须要知道和理解的,那么单元格的引用形式有哪几种呢? 1、相对引用 例如单元格A1,这就是相对引用,如果输入公式=A1,公式右拉会依次变成B1,C1,D1....,公式下拉会依次变成A2,A3,A4....,这是“墙头草引用样式”。 2、绝对引用列 绝对引用列是混合引用的一种,例如单元格$A1,在列标的前面加了个美元符号$,有了钱之后公式再怎么右拉依然会是$A1,因为这一列有钱它不舍得离开,但是行号前面没有钱$,所以它要造反啊,公式下拉的时候它就直接转行了,依次变成A2,A3,A4....,这是“列有钱行穷逼引用样式”。 3、绝对引用行 绝对引用行也是混合引用的一种,例如单元格A$1,在行号的前面加了个美元符号$,有了钱之后公式再怎么下拉依然会是A$1,因为这一行有钱打死它也不会离开的,这时候又成了列标前面没有钱$,所以它也要造反,公式右拉的时候它就换队列了,依次变成了B1,C1,D1....,这是“列穷逼行有钱引用样式”。 4、绝对引用行和列 绝对引用行和列也叫绝对引用,例如单元格$A$1,在行号和列标的前面都加了美元符号$,所以这个地方巨有钱,公式你随便拉单元格就是赖在$A$1,毕竟谁都想待在巨有钱的地方,这是“巨有钱引用样式”。 重要:F4是在四种引用间相互转换的快捷键,在编辑栏输入公式时按下F4功能键可进行快速切换,不需要手动输入美元符号。 结束语 万丈高楼平地起,不要觉得这个单元格的引用样式太过于简单就不重视它,只有深刻理解单元格的四种引用样式,我们才能灵活多变的写出我们想要的函数公式,这是所有学函数的朋友都必须要第一时间掌握的,它最简单,同时也最重要,如果理解不了我说的是什么,可以多看几遍动态图里面单元格的变化情况,结合实际情况自己动手练习一下。
  • 函数365之SUM函数:虽然千变万化,一心一意只为求和

    Excel函数2017-4-100评论316
    函数名称:SUM 函数作用:SUM函数主要用于计算所有参数之和。 函数语法:SUM(参数1,参数2,参数3,...,参数255) 注意事项: 1、SUM函数有1-255个参数,除第一参数是必选参数之外,其他的都是可选参数。 2、SUM函数的参数支持绝大多数数据类型,例如:SUM(A1,A3,B4:B10),其参数包括单个单元格和单元格区域;SUM(99,A1:A99,1+2),其参数包括数字,单元格区域,表达式;SUM(A:A,sheet2!A:A,{1,2}),其参数包括整列引用,跨表整列引用,常量数组。 3、SUM函数在求和计算时会忽略单元格引用的文本、逻辑值和文本型数字,但是直接写在参数中的文本、逻辑值和文本型数字将不会被忽略。 =SUM(1,"你好"),结果为#VALUE!错误值,因为这里文本作为一个参数进行求和计算将无法计算; =1+"你好",结果为#VALUE!错误值; =SUM(A1:A2),注:其中A1=1,A2=你好,结果为1,此时虽然A2是一个文本,但是作为单元格引用,求和时将被忽略; =SUM(1,TRUE,1>2),结果为2,此处TRUE将被当做1进行计算,1>2结果为FALSE将会被当做0进行计算; =SUM(1,"2"),结果为3,文本型的数字2将会被转换成数值型的数字参与计算; =SUM(1,{TRUE}),结果为1,此处忽略常量数组里面的逻辑值。 函数应用: 实例1、根据下表按数据,计算所有公司1月销量总和。 函数公式:B12单元格输入 =SUM(B2:B11),结果为5546 解释说明:一般计算多个单元格的和直接用SUM函数,求和的区域为B2:B11单元格区域。 实例2、根据下表数据快速对每个公司和每个月的总销量进行求和。 操作步骤:选中B2:G12单元格区域(该区域包括第12行的求和行和G列的求和列),然后按“Alt+=”快捷键,或者点“开始”选项卡右侧的“自动求和”即可对选中区域的行列分别进行求和。 实例3、下表是某公司部分业务员的销售记录,要求按照每个区域在合并的单元格用公式对该区域的总销量进行计算。 函数公式:选中D2:D14单元格区域,输入 =SUM(C2:$C$14)-SUM(D3:$D$14),然后按CTRL+回车即可。 解释说明: 1、本题主要考的是逆推思维,例如计算A区域的原理就是用全部区域的总和SUM(C2:$C$14),然后减去除去A区域之外其他区域的总和SUM(D3:$D$14),就得到了A区域的总和; 2、这里涉及到了另外一个知识点,那就是合并单元格只有最左上角一个单元格有值,其他为空; 3、因为本题有合并单元格,且合并的单元格大小不一样,所以公式没办法直接下拉,我们需要先选定区域,然后按CTRL+回车键填充公式。 实例4、下表是某公司部分业务人员的销量信息,要求按照指定区域计算总的销量。 函数公式:F2单元格输入 =SUM((A2:A11=E2)*C2:C11),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、本题主要考的是逻辑值TRUE在与数值相乘的时候等同于1,FALSE在与数值相乘的时候等同于0。 2、判断A列区域的单元格分别与B区域是否相等A2:A11=E2,如果相等返回TRUE,不相等返回FALSE,然后分别与对应的销量相乘然后用SUM函数将所有值求和。 实例5、根据下表按数据,按照指定班级与性别计算学生人数。 函数公式:H2单元格输入 =SUM((A2:A11=F2)*(C2:C11=G2)),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、本题主要考的是逻辑值TRUE与FALSE在相乘的时候分别等同于1和0,所以当两个条件同时为TRUE相乘=1,只要有任意一个条件不成立为FALSE时相乘=0。 2、判断A列区域的单元格分别与1班是否相等A2:A11=F2,判断C列区域的单元格是否为“女”字C2:C11=G2,然后分别对应相乘然后相加。 实例6、根据下表按数据,计算销量小于300或者大于700的销量总和。 函数公式:公式 =SUM(((B2:B11<300)+(B2:B11>700))*B2:B11),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、本题主要考的是逻辑值TRUE与FALSE在相加的时候分别等同于1和0; 2、因为比较运算符的运算优先级大于算术运算符,所以这里注意判断条件B2:B11<300和B2:B11>700都要分别加上括号。
  • Excel奇思妙想之脑洞大开(一)

    Excel VBA2017-4-90评论415
    1、脑洞大开之Excel中国象棋 路人甲:Excel,你也能和象棋扯上关系?大哥,你逗我玩呢? Excel:我可没有逗你玩哦,不信你往下看,说到下象棋你可不一定能够下得赢我哦? 2、脑洞大开之Excel俄罗斯方块 路人甲:好吧,没想到你居然是这么不正经的Excel,居然会下象棋,说吧,你还会些什么? Excel:我还会玩俄罗斯方块 路人甲:.......... 3、脑洞大开之Excel打台球 路人甲:Excel,你说你怎么就只会玩这些智力游戏呢,有本事你做个运动给我看看。 Excel:我还会打台球,这算不算? 路人甲:............... 结束语 路人甲:Excel,你居然除了超级无敌强大的数据分析功能之外,还搞这么多游戏做副业,实在是太丧尽天良了,还有什么是你不会的吗? Excel:生孩子我还不会! 路人甲:为什么你不会生孩子? Excel:因为
  • 函数365之COUNTIFS函数:我就是COUNTIF函数的超级加强版,有了我再多条件都不怕不怕啦

    函数名称:COUNTIFS 函数作用:COUNTIFS函数对区域中满足单个或多个指定条件的单元格进行计数。 函数语法:COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域3,条件3,...,条件区域127,条件127) 注意事项: 1、第1参数为必选参数,表示在其中计算关联条件的第一个区域; 2、第2参数为必选参数,表示需要与第1参数条件区域相对应的条件,条件的形式为数字、表达式、单元格引用或文本; 3、第3参数到第254参数为可选参数,分别表示第2个条件区域,第2个条件,...,第127个条件区域,第127个条件,所以如果出现均为两两出现的条件对; 4、COUNTIFS的条件之间为并列关系,并且条件支持通配符。 函数应用: 实例1、根据下表按数据,按照指定公司与区域计算人员个数。 函数公式:G2单元格输入 =COUNTIFS(A2:A11,E2,B2:B11,F2),结果为4。 解释说明:因为是要根据两个条件计算人数,所以本题用COUNTIFS函数,公司名称(A列)和区域名称(B列)分别是计数的两个条件区域,对应E2和F2条件。 实例2、根据下表数据计算大于等于500并且小于900的数字的个数。 函数公式:C2单元格输入 =COUNTIFS(A2:A11,E2,B2:B11,F2),结果为5 解释说明:大于等于500并且小于900属于两个条件,所以用多条件计数函数COUNTIFS,第1个条件区域与第2个条件区域均为A列,注意条件因为有比较运算符,所以需要加双引号""。 实例3、下表是某工厂一个时间段内的入职记录,要求按照指定的开始日期和结束日期分别计算营销部和人资部的入职人数。 函数公式:B7单元格输入 =COUNTIFS($B$1:$K$1,">="&$B$5,$B$1:$K$1,"<="&$D$5,$B$3:$K$3,A7),公式下拉即可 解释说明: 1、本题主要考的是以行作为区域进行计算,第1行为条件区域,而且这个区域对应两个条件,一个是开始日期,一个是结束日期,第3行也是条件区域,对应部门; 2、这里因为公式要下拉,所以要注意单元格区域与单元格的绝对引用关系,另外还要注意第2行姓名是干扰信息,与计数无关。 实例4、下表是某公司部分业务人员的人员名单,要求按照指定公司名称和区域以及姓名包含的字符统计人数。 函数公式:H2单元格输入 =COUNTIFS(A2:A11,E2,B2:B11,F2,C2:C11,"*"&G2&"*"),结果等于3 解释说明: 1、本题主要考的是通配符的运用,“*”表示一个或多个任意字符,所以“"*"&H2&"*"”表示包含H2单元格字符的任意内容。 2、因为“*”属于文本,所以与单元格连接要加双引号和&连接符,如果是直接统计包含“雪”字的话可以直接写成“*雪*”。