• 函数365之REPLACE函数:替换字符哪家强,REPLACE函数真是强

      函数名称:REPLACE 函数作用:REPLACE函数可用于使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。 函数语法:REPLACE(需要进行替换的文本, 开始替换的位置, 替换多少个字符, 替换的文本) 注意事项: 1、REPLACE函数有4个参数,其中每个参数都属于必选参数; 2、第2参数必须大于0且第3参数必须大于等于0,否则结果为#VALUE!错误值; 3、第2、第3参数如果不为整数,则计算时会截尾取整; 4、如果开始替换的位置大于文本的字符总数,则结果等同于两个文本的连接; 5、如果第3参数为替换0个字符,则结果等同于插入字符。 函数应用: 实例1、根据下表A列姓名,用“*”替换姓名的第2个字符,效果如B列所示。 函数公式:B2单元格输入 =REPLACE(A2,2,1,"*"),公式下拉即可 解释说明:要替换指定位置的字符,所以用REPLACE函数,第2参数为2,因为只要替换第2个字符,也就是说只替换1个字符,所以第3参数为1,替换的文本为“*”。 实例2、根据下表A列手机号码,从第4个数字开始将4个数字替换为“****”,效果如B列所示。 函数公式:B2单元格输入 =REPLACE(A2,4,4,"****"),公式下拉即可 解释说明:数据排位用RANK函数,这里要求按照分数高低升序排位,所以RANK第3参数不为0也不能省略,我们这里用1作为第3参数。 实例3、将下表B列的单位插入到A列的数据中,效果如C列所示。 函数公式:C2单元格输入 =REPLACE(A2,LOOKUP(9^9,-MID(A2,ROW($1:$99),1),ROW($1:$99))+1,0,B2),公式下拉即可 解释说明: 1、要在某个位置插入指定字符用REPLACE函数,这里要在数字后面添加单位,所以我们要查找到最后一个数字的位置; 2、首先我们将数据的每一个字符分别提取MID(A2,ROW($1:$99),1),然后在截取的字符前面加负号“-”,如果字符是数字依然会是数据,如果字符是文本则为#VALUE!错误值; 3、用LOOKUP函数查找9^9(一个极大值)可以查找到-MID(A2,ROW($1:$99),1)里面的最后一个数字,然后返回相应的位置ROW($1:$99); 4、因为我们只是要插入字符并不是真的替换某个字符,所以REPLACE函数第3参数为0。 实例4、将下表A列的金额按照单位元、角、分分别提取相应数值到B、C、C列,效果如下图所示。 函数公式:B2单元格输入 =REPLACE(REPLACE($A2,FIND(B$1,$A2),99,""),1,IFERROR(FIND(A$1,$A2),0),""),公式右拉下拉即可 解释说明: 1、首先用FIND(B$1,$A2)找到当前单位在金额的位置,然后把后面所有字符全部替换为空REPLACE($A2,FIND(B$1,$A2),99,""); 2、然后用FIND(A$1,$A2)查找前一个单位的位置,把第1个位置开始到前一个单位的位置的字符也全部替换为空; 3、由于第一个单位元的前一个金额单位是没有的,所以FIND(A$1,$A2)会返回错误值,这时候需要用纠错函数IFERROR(FIND(A$1,$A2),0)强制为0
    • 函数365之COMBIN函数:概率问题之组合

      函数名称:COMBIN 函数作用:COMBIN函数可用于返回从给定数目的对象集合中选取的若干对象的组合数。 函数语法:COMBIN(待选的数据总数,选多少个数据组合) 注意事项: 1、所谓组合,就是指从给定个数的元素中取出指定个数的元素作为组合,COMBIN函数第1参数就是给定个数的元素,第2参数就是取出组合的元素个数; 2、两个参数将被截尾取整; 3、如果两个参数为非数值型,函数COMBIN返回错误值#VALUE!。 4、如果两个参数小于0或者第1参数小于第2参数,则结果为#NUM!错误值。 5、PERMUT函数计算排列,COMBIN函数计算组合,语法一样,不一样的是排列与顺序有关,组合与顺序无关。 函数应用: 实例1、某公司计划从10个员工中选择其中7个组成一个特别小组,要求计算有多少种不同的选法。 函数公式:C2单元格输入 =COMBIN(10,7) 解释说明:计算组合概率事件用函数COMBIN,10个员工选择7个,所以10是第1参数,7是第2参数。 实例2、双色球是从1-33的数字中选择任意6个数字(红球),同时从1-16的数字中任意选择一个数字(蓝球),只要6个红球和1个蓝球一致则是一等奖,要求计算一等奖的中奖几率。 函数公式:公式 =1/(COMBIN(33,6)*COMBIN(16,1)) 解释说明: 1、33选6公式为COMBIN(33,6),16选1公式为COMBIN(16,1),同时满足则两个公式相乘COMBIN(33,6)*COMBIN(16,1),这是组合的方法; 2、1除以组合的方法就可以得到中奖的几率,结果为0.0000056%,千万分之0.5,所以,买双色球确实是做福利事业献爱心。
    • 函数365之PERMUT函数:概率问题之排列

      Excel函数2017-4-300评论150
      函数名称:PERMUT 函数作用:PERMUT函数可用于返回从给定数目的对象集合中选取的若干对象的排列数。 函数语法:PERMUT(待选的数据总数,选多少个数据排列) 注意事项: 1、所谓排列,就是指从给定个数的元素中取出指定个数的元素进行排序,PERMUT函数第1参数就是给定个数的元素,第2参数就是取出排列的元素个数; 2、两个参数将被截尾取整; 3、如果两个参数为非数值型,函数 PERMUT 返回错误值#VALUE!。 4、如果第1参数小于0或者第2参数小于0,则函数PERMUT返回错误值#NUM!。 5、如果第1参数小于第2参数,则函数PERMUT返回错误值#NUM!。 函数应用: 实例1、某公司计划举办一次晚会,需要从10个候选的节目中选择其中8个节目先后进行表演,要求计算有多少种不同的排法。 函数公式:D2单元格输入 =PERMUT(10,8) 解释说明:计算排列概率事件用函数PERMUT,10个候选节目中选择8个,所以10是第1参数,8是第2参数。 实例2、某公司计划举办一次晚会,需要从10个候选的节目中选择其中8个节目先后进行表演,但是第1个节目不能是小品,要求计算有多少种不同的排法。 函数公式:D2单元格输入 =PERMUT(10,8)-PERMUT(2,1)*PERMUT(9,7) 解释说明: 1、如果不限制小品为第1个节目,则全部的排列方法为PERMUT(10,8); 2、我们根据上图可以发现有2个小品节目,如果其中1个小品节目为第1个节目,则排列方法为PERMUT(2,1)*PERMUT(9,7); 3、将全部排列方法减去不符合的排列方法,就可以得到结果。
    • 函数365之RANK函数:排名函数之西式排名

      Excel函数2017-4-290评论187
      函数名称:RANK 函数作用:RANK函数可用于返回一个数字在数字列表中的排位。 函数语法:RANK(需要排位的数字,需要排位的区域,排位的方式) 注意事项: 1、第1参数为必选参数。表示需要找到排位的数字; 2、第2参数为必选参数。表示数字列表数组或对数字列表的引用,区域中的非数值型数据将被忽略; 3、第3参数为可选参数。表示数字排位的方式; 4、第3参数为0或省略时,数字按从大到小降序排位; 5、第3参数不为0时,数字按从小到大升序排位。 函数应用: 实例1、下表为某班部分学生的考试成绩,要求按照分数的高低进行降序的排位,效果如C列所示。 函数公式:C2单元格输入 =RANK(B2,$B$2:$B$11),公式下拉即可 解释说明:数据排位用RANK函数,这里要求按照分数高低降序排位,所以RANK第3参数为0或者省略,我们这里省略第3参数。 实例2、下表为某班部分学生的考试成绩,要求按照分数的高低进行升序的排位,效果如C列所示。 函数公式:C2单元格输入 =RANK(B2,$B$2:$B$11,1),公式下拉即可 解释说明:数据排位用RANK函数,这里要求按照分数高低升序排位,所以RANK第3参数不为0也不能省略,我们这里用1作为第3参数。 实例3、下表为某公司营销各部每个人的销售业绩,要求按照销量根据每个部门的人员划分各自排名,效果如D列所示。 函数公式:D2单元格输入 =RANK(C2,OFFSET($C$1,MATCH(A2,$A$2:$A$16,0),,COUNTIF($A$2:$A$16,A2))),公式下拉即可 解释说明: 1、数据排位用RANK函数,但是这里要计算排名的区域并不固定,也就是RANK的第2参数并不固定; 2、用OFFSET函数以C1单元格为参照点,然后查找当前部门名称在A2:A16单元格第一次出现的位置,这就是计算区域的起点; 3、用COUNTIF函数统计当前部门的个数,也就是当前部门业务人员的个数,这就是要计算区域的单元格个数,作为OFFSET函数的第4参数,最终OFFSET函数的结果就是计算排名的区域。
    • 函数365之COUNTBLANK函数:计算空单元格个数

      Excel函数2017-4-280评论151
      函数名称:COUNTBLANK 函数作用:COUNTBLANK函数可用于计算指定单元格区域中空白单元格的个数。 函数语法:COUNTBLANK(单元格区域) 注意事项: 1、COUNTBLANK函数有且只有一个必选参数。表示需要计算其中空白单元格个数的区域。 2、即使单元格中含有返回值为空文本 ("") 的公式,该单元格也会计算在内,但包含零值的单元格不计算在内。 函数应用: 实例1、计算下图A2:A11单元格区域的空单元格个数,如C2单元格所示。 函数公式:C2单元格输入 =COUNTBLANK(A2:A11) 解释说明:计算空单元格个数用COUNTBLANK函数,其中A2:A11单元格区域为要计算的区域。注意我们看到A5单元格有公式="",这在计算中也属于空单元格。 实例2、下表为某公司部分员工一个星期的考勤记录,“√”为出勤,如果为空则表示缺勤,计算每个人的缺勤次数。 函数公式:I2单元格输入 =COUNTBLANK(B2:H2),公式下拉即可 解释说明: 1、根据要求其实是要求我们计算个姓名对应的B到H列的单元格的空单元格个数,即为缺勤次数; 2、计算空单元格个数用COUNTBLANK函数,B2:H2为需要计算的区域。
    • 函数365之TRANSPOSE函数:转换时空维度之转置行列

      Excel函数2017-4-270评论247
      函数名称:TRANSPOSE 函数作用:TRANSPOSE函数可返回转置单元格区域,即将行单元格区域转置成列单元格区域,反之亦然。 函数语法:TRANSPOSE(数组或区域引用) 注意事项: 1、TRANSPOSE函数有且只有一个参数。表示需要进行转置的数组或工作表上的单元格区域。所谓数组的转置就是,将数组的第一行作为新数组的第一列,数组的第二行作为新数组的第二列,以此类推。 2、TRANSPOSE 函数必须在与源单元格区域具有相同行数和列数的单元格区域中作为数组公式分别输入。使用 TRANSPOSE 可以转置数组或工作表上单元格区域的垂直和水平方向。 函数应用: 实例1、将下表A1:B11单元格区域的数据转置行列,效果如C1:M2区域所示。 函数公式:选中C1:M2单元格输入 =TRANSPOSE(A1:B11),然后同时按CTRL+SHIFT+回车键结束公式 解释说明:转置行列的内容用TRANSPOSE函数,注意要选中区域后三键结束。 实例2、根据下表第1和第2行的数据,然后根据指定的姓名查找对应的分数。 函数公式:B5单元格输入 =VLOOKUP(A5,TRANSPOSE(B1:K2),2,0),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、根据题目其实可以用HLOOKUP函数解决,我们这里用另一种VLOOKUP+TRANSPOSE的方法; 2、VLOOKUP函数是按照列查找,所以可以用TRANSPOSE函数将B1:K2区域转行行列,然后再查找。 实例3、根据下表数据计算每个人的总分排名,效果如下图第8行所示。 函数公式:B8单元格输入 =1+SUM(N(MMULT(TRANSPOSE($B$2:$K$7),ROW($1:$6)^0)>SUM(B2:B7))),数组公式CTRL+SHIFT+回车键三键结束,公式右拉即可 解释说明: 1、用TRANSPOSE函数将$B$2:$K$7区域的行列转置,然后用ROW(1:6)^0构建一个{1,1,1,1,1,1}的常量数组; 2、用MMULT函数将TRANSPOSE($B$2:$K$7)与ROW($1:$6)^0进行矩阵相乘,然后再与当前人员的总分数SUM(B2:B7)比较; 3、假设有1个人的总分数大于当前人员的总分数,则当前人员的排名应该是1+1,也就是第2名,所以最后结果是1+SUM。
    • 函数365之CHAR函数:你会将数字变成字符吗?

      Excel函数2017-4-270评论195
      函数名称:CHAR 函数作用:CHAR函数可用于返回对应于数字代码的字符。 函数语法:CHAR(数字或结果为数字的引用) 注意事项: 1、CHAR有且只有必须有一个参数; 2、参数数字或结果为数字的单元格引用,如果数字为小数则去尾取整,如果该数字不存在字符集中,则CHAR结果为#VALUE!错误值; 3、CHAR函数的结果是你的计算机所用字符集中的字符。 函数应用: 实例1、用公式生成A-Z的26个英文字母,如下图A列所示。 函数公式:A2单元格输入 =CHAR(ROW(A65)),公式下拉即可 解释说明: 1、在计算机字符集中,编码65-90分别表示字母A-Z; 2、所以我们需要构建一个65-90的序列,这里用ROW(A65)表示,公式下拉可以依次获得65-90的数字; 3、最后用CHAR函数获取65-90的数字编码所代表的字符; 实例2、下表A列中为某公司部分人员信息,现在要求将该人员信息格式转换成B列格式,也就是同一个单元格中姓名和联系方式各占一行。 函数公式:B2单元格输入 =REPLACEB(A2,SEARCHB("?",A2),,CHAR(10)),公式下拉即可 解释说明: 1、首先我们如果需要将姓名和联系分析分两行,那么我们就需要在姓名和联系方式中间插入一个换行符,在计算机字符集中10表示换行符CHAR(10); 2、我们需要找到第一个数字的位置,用公式SEARCHB("?",A2)可以获取第1个单字节字符的字节位置; 3、REPLACEB函数第3参数为0后者不写时表示在指定的位置后面前面插入指定字符,所以这里用REPLACEB根据第1个数字的位置插入了换行符; 4、写完公式后一定要将B列设置为自动换行(开始选项卡,选择自动换行),否则显示不出效果。
    • 函数365之PV函数:投资收益知多少,PV函数少不了

      Excel函数2017-4-250评论180
      函数名称:PV 函数作用:PV函数用于返回投资的现值,现值为一系列未来付款的当前值的累积和。 函数语法:PV(各期利率, 年金的付款总期数, 各期所应支付的金额, 未来得到的现金余额, 指定各期付款方式) 注意事项: 1、第1参数为必选参数。表示各期利率; 2、第2参数为必选参数。表示年金的付款总期数。其中期数与利率的时间维度必须一致; 3、第3参数为必选参数。表示各期所应支付的金额,其数值在整个年金期间保持不变,通常表示包括本金和利息,但不包括其他费用或税款。如果省略该参数,则第4参数必选; 4、第4参数为可选参数。表示未来值,或在最后一次支付后希望得到的现金余额,如果省略 fv,则假设其值为; 5、第5参数为可选参数。数字0或1,用以指定各期的付款时间是在期初还是期末,1表示期初,0表示期末,也可以用TRUE或FALSE表示。 函数应用: 实例1、某人想要做一项投资,每月回报率为4.5%,计划投资5年后可以收到回报50000元,请计算出他需要投资多少钱,如下表所示。 函数公式:D2单元格输入 =PV(A2,B2*12,0,C2) 解释说明: 1、题目要求根据未来收益计算投资现值,所以用PV函数; 2、第1参数为各期的利率,也就是题目的月利率,所以第1参数为A2单元格; 3、第2参数为年金的付款总期数,因为利率是月利率,所以付款期数也需要按月计算,每年12个月,所以第2参数为B2*12; 4、第3参数为各期的投入,这里为0,表示一次性投入; 5、第4参数为希望得到的收益,所以第4参数为C2; 6、第5参数省略,表示默认为1,付款时间默认期初; 7、在财务运算中,投资是负数,支出属于减少资产,所以采用负数来记录。 实例2、某公司想要做一项投资,期望收益是8万元,但由于公司资金紧张所以希望能最少投入金额,下表为该公司10个待选投资计划,要求计算最少投资金额及最优计划。 函数公式: E2单元格输入 =INDEX(A2:A11,MATCH(MAX(PV(B2:B11,C2:C11/12,0,80000)),PV(B2:B11,C2:C11/12,0,80000),0)),数组公式CTRL+SHIFT+回车键三键结束 F2单元格输入 =MAX(PV(B2:B11,C2:C11/12,0,80000)),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、首先我们需要计算每一个计划的投资金额PV(B2:B11,C2:C11/12,0,80000); 2、因为投资是用负数做记录,所以最少的投资应该是负数的最大值=MAX(PV(B2:B11,C2:C11/12,0,80000)); 3、用MATCH函数查找最小的投资=MAX(PV(B2:B11,C2:C11/12,0,80000))在所有投资PV(B2:B11,C2:C11/12,0,80000)中的位置; 4、根据最小投资的位置用INDEX函数返回该位置所对应的计划。 5、这里要注意的一点是年利率对应的期数也应该是年,所以PV的第2参数需要除以12。
    • 文本与数值的那些事儿(一)

      Excel基础2017-4-240评论222
      1、文本型数值转换成数值型数值,如下图。 方法1:在原单元格转换。首先选择A2:A11任意一个单元格,然后按CTRL+A全选单元格,点下图中的感叹号,然后选择“转换为数字”即可。 方法2:辅助列转换。在B2单元格输入公式=--A2或者=A2*1,公式下拉即可。 注:一般情况下从ERP或者其他系统导出来的很多数值都会是文本型数值,如果直接用SUM进行求和运算得到的结果将会是0,所以必选先转换成数值型数值,然后才能进行求和运算。 2、数值型数值转换成文本型数值,如下图。 方法1:在原单元格转换。首先我们先把A2:A11单元格格式设置为文本格式,然后随便打开一个记事本,将A2:A11复制粘贴到记事本中,然后再将记事本中的数据复制粘贴回A2:A11单元格区域中。 方法2:辅助列转换。在B2单元格输入=A2&"",公式下拉即可。 注:当我们需要将一些数据通过Excel导入ERP或者其他系统的时候,数值型数值往往会不被认可,某些系统的导入只识别文本,所以这个时候就需要我们将数值型数值先转换成文本型数值。可能有些人会说那我直接将单元格格式设置为文本不就行了吗,为什么还要这么复杂的操作呢?实际情况并不是这么简单,当一个单元格的内容格式已经确定,我们直接变更单元格格式,并不能实际改变数据类型,不相信的朋友可以试一下。 小结:有位关注公众号的朋友说希望出一期文本与数值间相互转化的文章,所以周末抽空写了一下文本与数值的那些事儿,先把第一部分放出来,下周一再放第二部分关于文本与数值的替换、提取,敬请期待。
    • 函数365之SUMPRODUCT函数:不仅仅是SUM+PRODUCT,我还会多条件求和、查找、计数

      Excel函数2017-4-240评论162
      函数名称:SUMPRODUCT 函数作用:SUMPRODUCT函数可用于将一组或多组数组间对应的元素相乘,并返回乘积之和。 函数语法:SUMPRODUCT(参数1, 参数2,参数3,…,参数255) 注意事项: 1、每一个参数都是其相应元素需要进行相乘并求和的数组参数,其中第1参数必选,其他参数可选,最多可支持255个参数; 2、数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!; 3、函数SUMPRODUCT将非数值型的数组元素作为 0 处理; 4、任意参数中都不允许出现错误值,只要有任意一组参数出现任意一个或多个错误值,则SUMPRODUCT结果为错误值; 5、虽然像数组公式需要用CTRL+SHIFT+回车键三键结束,但是SUMPRODUCT执行的都是数组公式的运算,所以如果要用到这个函数需要尽可能的选择合理的区域范围,否则公式运算将会非常缓慢。 函数应用: 实例1、根据下表A、B两列的数量和金额计算出总金额,结果如D2列所示。(一般应用) 函数公式:D2单元格输入 =SUMPRODUCT(A2:A11,B2:B11) 解释说明: 1、要计算总金额,就要将数量分别乘以相应金额然后相加A2*B2+A3*B3+A4*B4+…+A11*B11; 2、这里我们可以用SUMPRODUCT函数更快更方便的对数量和金额进行相乘后求和,其中A2:A11为第1参数,B2:B11为第2参数。 实例2、根据下表的销量信息,计算A产品中A-2型号的总销量,如G2所示。(多条件求和) 函数公式:G2单元格输入 =SUMPRODUCT((A2:A11=E2)*(B2:B11=F2),C2:C11) 解释说明: 1、首先判断A列产品名称是否等于A产品A2:A11=E2,如果相等返回TRUE,不相等返回FALSE; 2、其次判断B列型号是否等于A-2型号B2:B11=F2,如果相等返回TRUE,不相等返回FALSE; 3、A2:A11=E2的结果与B2:B11=F2的结果分别相乘,其中TRUE*TRUE=1,表示两个条件同时成立,任何数与FALSE相乘都等于0,表示条件不成立; 4、用SUMPRODUCT函数将(A2:A11=E2)*(B2:B11=F2)的结果(1或0)分别与C2:C11相乘然后相加,就可以得到我们要的结果。 实例3、下表是某公司部分业务人员1-6月的销量情况,根据姓名及月份查找相应的销量,如K2单元格所示。(多条件查找) 函数公式:K2单元格输入 =SUMPRODUCT((A2:A11=I2)*(B1:G1=J2),B2:G11) 解释说明: 1、首先判断A列姓名是否等于条件的姓名A2:A11=I2,如果相等返回TRUE,不相等返回FALSE; 2、其次判断B1:G1月份是否等于指定的月份B1:G1=J2,如果相等返回TRUE,不相等返回FALSE; 3、A2:A11=I2的结果与B1:G1=J2的结果分别相乘,其中TRUE*TRUE=1,表示两个条件同时成立,任何数与FALSE相乘都等于0,表示条件不成立; 4、用SUMPRODUCT函数将(A2:A11=I2)*(B1:G1=J2)的结果(1或0)分别与B2:G11相乘然后相加,就可以得到我们要的结果。 实例4、下表是某集团旗下多个分公司部分员工信息,其中有公司名称和区域名称,要求根据公司及区域名称分别计算人数,如G列所示。(多条件计数) 函数公式:G2单元格输入 =SUMPRODUCT(($A$2:$A$15=E2)*($B$2:$B$15=F2)),公式下拉即可 解释说明: 1、首先判断A列公司是否等于条件的姓名A2:A15=E2,如果相等返回TRUE,不相等返回FALSE; 2、其次判断B列区域是否等于条件的区域B2:B15=F2,如果相等返回TRUE,不相等返回FALSE; 3、A2:A15=E2的结果与B2:B15=F2的结果分别相乘,其中TRUE*TRUE=1,表示两个条件同时成立,任何数与FALSE相乘都等于0,表示条件不成立; 4、用SUMPRODUCT函数将(A2:A15=E2)*(B2:B15=F2)的结果(1或0)全部相加,就可以得到我们要的结果,因为公司要下拉,所以区域要加绝对引用。