现在位置:首页 > 发表在 2017年04月 的所有文章
  • 函数365之PERMUT函数:概率问题之排列

    Excel函数2017-4-300评论438
    函数名称: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评论470
    函数名称: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评论407
    函数名称: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评论643
    函数名称: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评论432
    函数名称: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评论501
    函数名称: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评论517
    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评论466
    函数名称: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)全部相加,就可以得到我们要的结果,因为公司要下拉,所以区域要加绝对引用。
  • 函数365之SEARCHB函数:文本位置查找之查找字节位置

    Excel函数2017-4-230评论640
    函数名称:SEARCHB 函数作用:SEARCHB函数用于根据查找指定的文本字符在某个字符串中的字节位置 函数语法:SEARCHB(要查找的字符,在哪个字符里面查找,从第几个位置开始查找) 注意事项: 1、SEARCHB函数不区分字母大小写并且允许使用通配符。FINDB函数刚好相反! 2、如果要查找的文本为空文本 (""),则SEARCHB会返回查找字符的第1个位置; 3、如果第2参数中没有包含第1参数,则SEARCHB的结果会返回错误值 #VALUE!; 4、如果开始查找的位置小于等于0或者大于第2参数文本的字节个数,则SEARCHB返回错误值 #VALUE!。 5、SEARCHB函数的第3参数一般是省略的,表示要查找的位置从第2参数的文本的第1个位置开始查找。 6、SEARCHB函数与SEARCH函数的语法及用法相同,唯一不同的是SEARCH函数用于查找字符的在文本的第几个字符位置,而SEARCHB函数用于查找字符在文本的第几个字节位置。 函数应用: 实例1、根据下表指定字符在相应字符串中的字节位置,效果如C列所示。 函数公式:C2单元格输入 =SEARCHB(B2,A2),公式下拉即可 解释说明:查找某字符在字符串中的字节位置可以用SEARCHB函数,在Excel中,一个中文汉字或中文标点符号占2个字节,1个数字、字母或英文标点符号占1个字节。 实例2、根据下面表格A列的人员信息提取相应的联系方式,如B列所示。 函数公式:B2单元格输入 =MIDB(A2,SEARCHB("?",A2),99),公式下拉即可 解释说明: 1、在Excel中,一个中文汉字或中文标点符号占2个字节,1个数字、字母或英文标点符号占1个字节; 2、A列数据只有文字和数字两种类型,而数字属于单字节字符,可以用通配符“?”表示,SEARCHB函数支持通配符,所以我们可以用SEARCHB函数查找第一个数字出现的位置SEARCHB("?",A2); 3、然后用MIDB函数根据第1个数字出现的字节位置截取字符,截取的字节长度可以按照需求自行设置。
  • 函数365之SEARCH函数:文本位置查找之查找字符位置

    Excel函数2017-4-230评论484
    函数名称:SEARCH 函数作用:SEARCH函数用于根据查找指定的文本字符在某个字符串中的位置 函数语法:SEARCH(要查找的字符,在哪个字符里面查找,从第几个位置开始查找) 注意事项: 1、SEARCH函数不区分字母大小写并且允许使用通配符。FIND函数刚好相反! 2、如果要查找的文本为空文本 (""),则SEARCH会返回查找字符的第1个位置; 3、如果第2参数中没有包含第1参数,则SEARCH的结果会返回错误值 #VALUE!; 4、如果开始查找的位置小于等于0或者大于第2参数文本的字符个数,则SEARCH和SEARCHB返回错误值 #VALUE!。 5、SEARCH函数的第3参数一般是省略的,表示要查找的位置从第2参数的文本的第1个位置开始查找。 函数应用: 实例1、查找下面身份证号码中第1个“4”出现的位置,效果如B列所示。 函数公式:B2单元格输入 =SEARCH(4,A2),公式下拉即可 解释说明:查找某字符在字符串中的位置可以用FIND函数和SEARCH函数,这里我们用SEARCH函数,其中第1参数为要查找的字符,这里我们要查找数字4,如果需要查找的内容是文本,需要加双引号。 实例2、根据下面表格A列的人员信息提取相应的联系方式,如B列所示。 函数公式:B2单元格输入 =MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&1234567890)),99),公式下拉即可 解释说明: 1、查找文本字符的位置,所以用SEARCH函数分别查找0、1、2、3、4、5、6、7、8、9这10个数字分别在文本字符串中出现的位置; 2、为了防止查找的数字在文本字符串中不存在而出现错误值 #VALUE!,所以需要在文本字符串后面用连接符号“&”连接1234567890这10个数字,这样SEARCH函数就可以依次取得每个数字出现的位置,然后用MIN函数取最小值就可以得到第1个数字出现的位置。 3、然后用MID函数根据第1个数字出现的位置截取字符,由于不知道联系方式的长度,所以我们可以把数字设大一点,用99。 实例3、下表A列为某公司部分业务员的工作汇报记录,该公司要求规范的工作汇报格式应该以“今天”开头,内容要包含“客户”两个字,否则就是不规范的汇报,请判断A列工作汇报是否规范,效果如B列所示。 函数公式:B2单元格输入 =IF(ISERR(SEARCH("今天*客户",A2)),"不规范","规范"),公式下拉即可 解释说明: 1、规范的格式是以“今天”开头,包含“客户”两个字,我们可以用通配符表示“今天*客户”; 2、用SEARCH函数在A列工作汇报中查找“今天*客户”,如果可以返回位置则表示字符串符合我们要的格式,如果不符合我们要的格式SEARCH函数的结果将为错误值; 3、所以用ISERR函数判断SEARCH函数的结果是否为错误值,如果是则工作汇报不规范,否则为规范,用IF判断即可。