现在位置:首页 > Excel相关 > Excel函数
  • 函数365之MMULT函数:矩阵相乘之美眉函数

    Excel函数2017-5-220评论546
    函数名称:MMULT 函数作用:MMULT用于返回两个数组的矩阵乘积。MMULT函数通常也被戏称为MM函数或美眉函数。 函数语法:MMULT(数组1, 数组2) 注意事项: 1、MMULT函数有且只有两个参数,都是必选参数,可以是单元格区域、数组常量或引用; 2、参数1的列数必须等于参数2的行数,并且参数中只能包含数值,否则结果返回#VALUE!错误值; 3、对于返回结果为数组的公式,必须以数组公式的形式输入。 函数应用: 实例1、某公司有十个候选人竞选优秀员工,由全体员工投票打分,投票结果如下,每个蓝球得1分,红球得2分,黑球得3分,绿球得4分,要求分别计算每一个候选人的得分,如F列所示。 函数公式:F2单元格输入=MMULT(B2:E2,{1;2;3;4}),公式下拉即可 解释说明:需要将每一个颜色的球的个数分别乘以对应的分数然后求和,这里可以用矩阵乘积来计算,我们用MMULT函数,也可以写成=MMULT(B2:E2,ROW($1:$4))。 实例2、下表为去年某公司业务员四个季度的销量明细,要求计算每个业务员总销量的年度排名,效果如F列所示。 函数公式:F2单元格输入 =SUM(N(SUM(B2:E2)<MMULT($B$2:$E$11,{1;1;1;1})))+1,公式下拉即可 解释说明: 1、MMULT($B$2:$E$11,{1;1;1;1})可以计算出每一个业务员的销量总和; 2、将当前业务员的销量总和与所有业务员的销量总和对比,判断有多少个业务员的销量总和大于当前业务员,其个数+1就是当前业务员的排名; 3、举个栗子验证第2点,假设有且只有3个人的销量大于当前业务员,则他就是第4名(3+1)。 实例3、以下是某公司业务员1-5月销量,公司要求每个业务员每月最低目标完成60吨,请计算出1-5月所有业务员超出60吨的销量总和,如H2单元格所示。 函数公式:H2单元格输入 =SUM(MMULT((B2:F11>=60)*(B2:F11-60),{1;1;1;1;1})) 解释说明: 1、先判断是否大于等于60,然后乘以对应的销量,得到的数作为MMULT的第1个参数数组; 2、由于第1参数有5列,所以第2参数必须有5行,这里用常量数组{1;1;1;1;1}; 3、最终得到的MMULT会产生多个结果,所以需要用SUM函数求和; 实例4、某部门竞选优秀员工,有3名候选人,由10人投票得票最多的将获选优秀员工,现在要求统计最高得票数和获奖结果,如D2和E2单元格所示。 函数公式: D2单元格输入 =MAX(MMULT(N(B2:B11=TRANSPOSE(B2:B11)),ROW(1:10)^0)),数组公式CTRL+SHIFT+回车键三键结束 E2单元格输入 =INDEX(B2:B11,MODE(MATCH(B2:B11,B2:B11,0))),数组公式CTRL+SHIFT+回车键三键结束 解释说明:、 1、先用TRANSPOSE(B2:B11)将投票结果列转置,然后再与投票列做比较,得到一个10行10列的数组,用N函数将数组的TRUE和FALSE分别转换为1和0; 2、由于第1参数有10列,所以第2参数必须有10行,这里用ROW(1:10)^0表示10行,且每行的数值是1(任何数的0次方等于1); 3、竞选结果用MODE函数,这里不详细解释,不理解可以公众号回复MODE获取该函数文章;
  • 函数365之WEEKNUM函数:今天是一年的第几周?让WEEKNUM函数告诉你吧

    Excel函数2017-5-210评论954
    函数名称:WEEKNUM 函数作用:WEEKNUM函数用于返回指定日期在一年中处于第几周。 函数语法:WEEKNUM(日期, 计算类型) 注意事项: 1、第1参数为必选参数,表示代表一周中的日期,该参数必须为日期或日期序列号或结果为日期的引用和公式; 2、第2参数为可选参数,通过第2参数确定星期从哪一天开始,默认值为 1。第2参数只能为以下类型 3、如果第1参数不是合法日期值,则结果为#NUM!错误值。 函数应用: 实例1、以包含1月1日的周该年的第一周且一周的第一天为星期一的规则,计算下列日期分别为一年中的第几周,如B列所示。 函数公式:B2单元格输入=WEEKNUM(A2,2),公式下拉即可 解释说明:计算一年中的第几周用WEEKNUM函数,根据题目要求WEEKNUM的第2参数应为2。 实例2、以包含1月1日的周该年的第一周且一周的第一天为星期一的规则,计算下列年份每年总共有多少周,如B列所示。 函数公式:B2单元格输入 =WEEKNUM(DATE(A2,12,31),2),公式下拉即可 解释说明: 1、计算一年中的第几周用WEEKNUM函数,根据题目要求WEEKNUM的第2参数应为2; 2、由于要求要计算的是一年总的周数,所以我们只要计算每年的12月31日,也就是一年的最后一天是本月的第几周即可; 3、用DATE函数来构建每年的12月31日的日期DATE(A2,12,31); 4、如果需要计算当前年的总的周数,只用用WEEKNUM("12-31",2)即可。 实例3、以包含1月1日的周该年的第一周且一周的第一天为星期一的规则,根据A列第周列出该周的所有日期,如B:H列所示。 函数公式:B2单元格输入 ="2017-1-1"+MAX((WEEKNUM("2017-1-1"+ROW($1:$365),2)=$A2)*ROW($1:$365))+COLUMN(A1)-7,数组公式CTRL+SHIFT+回车键三键结束,公式右拉下拉即可 解释说明: 1、计算一年中的第几周用WEEKNUM函数,根据题目要求WEEKNUM的第2参数应为2; 2、(WEEKNUM("2017-1-1"+ROW($1:$365),2)=$A2)*ROW($1:$365)这里可以得到从2017-1-1开始加上多少天就是该周的日期,然后用MAX取得最大天数; 3、用2017-1-1的日期加上前面得到的最大天数就可以得到这一周的最后一天的日期,然后-6就可以得到第一天的日期; 4、由于公式右拉日期要递增1天,所以要构造一个0,1,2,3,4,5,6的一个序列,用COLUMN(A1)-1表示。
  • 函数365之DDB函数:双倍余额递减法计算折旧值

    Excel函数2017-5-200评论441
    函数名称:DDB 函数作用:DDB函数用于使用双倍余额递减法或其他指定方法,计算一笔资产在给定期间内的折旧值。 函数语法:DDB(资产原值, 资产残值, 使用寿命, 折旧期间, 余额递减速率) 注意事项: 1、该函数总共有5个参数,且都不允许为负数; 2、第1-4参数为必选参数,其中第3参数和第4参数的单位必选相同; 3、第5参数为可选参数,当省略时则默认余额递减速率为2; 4、双倍余额递减法以加速的比率计算折旧。折旧在第一阶段是最高的,在后继阶段中会减少; 5、当折旧大于余额递减计算值时,如果希望转换到直线余额递减法,请使用 VDB 函数。 函数应用: 实例1、下表为某公司某项固定资产的资产信息情况,请用双倍余额递减法计算指定期间的折旧额,如F列所示。 函数公式:F2单元格输入=DDB($A$2,$B$2,$C$2,E2),公式下拉即可 释说明:用双倍余额递减法计算折旧可用DDB函数,且可以省略该函数的第5参数。 实例2、下表为某公司某项固定资产的资产信息情况,请用双倍余额递减法计算指定期间的折旧额,如F列所示。 函数公式:F2单元格输入 =DDB($A$2,$B$2,$C$2*12,E2),公式下拉即可 解释说明: 1、用双倍余额递减法计算折旧可用DDB函数; 2、由于第3参数使用寿命的单位是年,而要计算的期间单位是月,所以要将使用寿命的年份*12转换成月份数; 3、省略第5参数,默认为双倍余额递减法计算折旧。
  • 函数365之RATE函数:投资收益知多少,RATE函数告诉你

    Excel函数2017-5-190评论402
    函数名称:RATE 函数作用:RATE函数用于返回年金的各期利率。函数RATE通过迭代法计算得出,并且可能无解或有多个解。 函数语法:RATE(年金的付款总期数, 各期应付金额, 现值, 未来值, 付款时间类型, 预期利率)  注意事项: 1、第1参数为必选参数,表示年金的付款总期数; 2、第2参数为必选参数,表示各期所应支付的金额,其数值在整个年金期间保持不变。通常,该参数包括本金和利息,但不包括其他费用或税款。如果省略该参数,则第4参数为必选参数; 3、第3参数为必选参数,表示现值,也就是一系列未来付款现在所值的总金额; 4、第4参数为可选参数,表示未来值,或在最后一次付款后希望得到的现金余额。如果省略该参数,则假设其值为0; 5、第5参数为可选参数,一般是数字0或1,用以指定各期的付款时间是在期初还是期末。0或省略表示期末,1表示期初 ; 6、第6参数为可选参数,表示预期利率,如果省略预期利率,则假设该值为 10%; 7、如果函数RATE不收敛,请改变第6参数的值。通常当参数位于0到1之间时,函数RATE是收敛的,如果在进行20次迭代计算后,函数RATE 的相邻两次结果没有收敛于0.0000001,函数 RATE 将返回错误值 #NUM!。 函数应用: 实例1、张三进行一项投资,需要一次投资50000元,5年后可获取利润12000元,请计算每年的利率是多少,如下图D2所示。 函数公式:D2单元格输入=RATE(B2,0,-A2,A2+C2) 解释说明: 1、计算投资回报率用RATE函数; 2、其中第1参数为付款总期数也就是投资年限B2单元格; 3、因为是一次性投资所以各期应付为0,也就是第2参数为0; 4、第3参数为投资金额,这里投资金额是50000元,但从财务角度来说支出要用负数,所以是-A2; 5、第4参数表示未来值,也就是未来的投资收益(含本金),所以是A2+C2。 实例2、李四打算做一项理财,现在有A-J一共10个投资计划,如下图,要求计算回报率最高的投资计划及具体回报率,效果如F2和G2所示。 函数公式: F2单元格输入 =INDEX(A2:A11,MATCH(MAX(RATE(C2:C11,0,-B2:B11,D2:D11)),RATE(C2:C11,0,-B2:B11,D2:D11),)),数组公式CTRL+SHIFT+回车键三键结束 G2单元格输入 =MAX(RATE(C2:C11,0,-B2:B11,D2:D11)),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、计算投资回报率用RATE函数; 2、其中第1参数为付款总期数也就是投资年限C2:C11单元格; 3、因为是一次性投资所以各期应付为0,也就是第2参数为0; 4、第3参数为投资金额,从财务角度来说支出要用负数,所以是-B2:B11; 5、第4参数表示未来值,也就是未来的投资收益(含本金),所以是D2:D11。 6、计算出所有计划的投资收益后用MAX函数即可获得最高的投资收益率,也就是G2单元格要计算的年回报率; 7、用MATCH函数查找最高投资收益率MAX(RATE(C2:C11,0,-B2:B11,D2:D11))在所有收益RATE(C2:C11,0,-B2:B11,D2:D11)中的位置; 8、根据最高投资收益所在的位置用INDEX函数返回其所对应的A列的位置,也就是最优投资计划。
  • 函数365之CODE函数:汉字变数字其实很简单

    Excel函数2017-5-180评论906
    函数名称:CODE 函数作用:CODE函数用于返回文本字符串中第一个字符的数字代码,返回的代码对应于计算机当前使用的字符集。 函数语法:CODE(参数) 注意事项: 1、CODE函数有且只有一个参数,参数可以是任意文本字符串或结果为文本字符串的引用和公式; 2、如果参数为空单元格引用则结果为#VALUE!错误值。 函数应用: 实例1、根据A列数据获取相应的数字代码,效果如B列所示。 函数公式:B2单元格输入=CODE(A2),公式下拉即可 解释说明:根据字符返回相应的数字代码用CODE函数,注意空格(A5)和空单元格(A6)的区别,中文标点符号(A10)和英文标点符号(A11)的区别。 实例2、某学校规定学号必须要以A-Z这26个大写字母中的任意一个字母开头,请判断下面A列中学号是否符合规范,如B列所示。 函数公式:B2单元格输入 =IF(AND(CODE(A2)>=65,CODE(A2)<=90),"规范","不规范"),公式下拉即可 解释说明: 1、要判断第一个字符是否大写字母,用CODE函数,其中大写字母返回的数字代码应该是65-90; 2、所以要判断CODE(A2)的结果是否大于等于65并且小于等于90,这里用AND函数连接两个判断条件; 3、最后用IF函数嵌套判断,当AND的结果为TRUE就表示符合规范,否则就是不规范。
  • 函数365之DAY函数:获取日期天数之日复一日

    Excel函数2017-5-170评论588
    函数名称:DAY 函数作用:DAY函数用于返回日期或表示日期的序列号的日期值。 函数语法:DAY(日期或表示日期的序列号) 注意事项: 1、DAY函数有且只有一个参数,参数可以是日期或表示日期的序列号,也可以是结果为日期的单元格引用或公式; 2、如果参数结果不为日期或日期序列号,则DAY结果为#VALUE!错误值; 3、DAY函数一般返回的结果是数值,且是整数。 函数应用: 实例1、根据A列日期获取相应的日期值,效果如B列所示。 函数公式:B2单元格输入 =DAY(A2),公式下拉即可 解释说明: 1、根据日期返回日期值用DAY函数; 2、我们都知道4月份只有30天,最大日期是2017-4-30,所以A10单元格属于无效日期,相当于文本,DAY函数结果为错误值。 实例2、根据下表A列日期进行判断,每月1-10日为上旬,11-20日为中旬,21日以后为下旬,如B列所示。 函数公式:B2单元格输入 =LOOKUP(DAY(A2),{1,"上旬";11,"中旬";21,"下旬"}),公式下拉即可 解释说明: 1、首先要用DAY函数取得日期的日期值DAY(A2); 2、然后用LOOKUP函数对{1,"上旬";11,"中旬";21,"下旬"}进行区间判断; 3、如果不明白LOOKUP函数的计算原理,可以在公众号回复LOOKUP,获取相关函数文章。
  • 函数365之TRIM函数:去除文本中的空格

    Excel函数2017-5-160评论841
    函数名称:TRIM 函数作用:TRIM函数用于除了单词之间的单个空格外,清除文本中所有的空格。 函数语法:TRIM(参数) 注意事项: 1、TRIM函数有且只有一个参数,参数可以是文本或公式及引用; 2、在从其他应用程序中获取带有不规则空格的文本时,可以使用函数 TRIM; 3、如果文本字符串中间存在多个连续空格,则有一个空格无法清除; 4、有些TRIM无法清除的空格可以尝试用CLEAN函数清除。 函数应用: 实例1、下表A列数据中存在空格,其中有些是字符串前面有空格,有些是后面有空格,有些是中间有空格,要求清除空格,效果如C列所示,清除空格前后字符数如B列和D列所示。 函数公式:C2单元格输入 =TRIM(A2),公式下拉即可 解释说明: 1、清除文本字符串中的空格用TRIM函数; 2、A1:A2和A7:A11单元格区域都是文本前后有空格,用TRIM函数可以清除所有空格; 3、A4:A5单元格区域中文本字符串中间存在空格,所以用TRIM函数清除空格后最终会剩下一个空格,无法清除; 4、A6单元格文本中有两处地方存在空格,所以最终会剩下两个空格无法清除; 5、清除空格还可以用CLEAN函数和SUBSTITUTE函数,用SUBSTITUTE函数可以完美解决文本字符串中间的空格无法完全清除的问题,公式=SUBSTITUTE(A2," ","")。 实例2、根据下表A列信息,将姓名、籍贯和联系方式分别提取到B/C/D列,如下图所示。 函数公式:B2单元格输入 =TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",99)),100*COLUMN(A1)-99,100)),公式右拉下拉即可 解释说明: 1、首先用SUBSTITUTE函数将A列人员信息里面的“-”符号替换成99个空格SUBSTITUTE($A2,"-",REPT(" ",99)); 2、然后分别从替换后的字符的第1、101、201个位置开始截取字符串,每次截取100个字符; 3、截取出来的字符前后会有很多空格,所以需要用TRIM函数清除空格。
  • 函数365之SUBTOTAL函数:数据分析汇总哪家强,函数SUBTOTAL它最强

    Excel函数2017-5-150评论602
    函数名称:SUBTOTAL 函数作用:SUBTOTAL函数用于按照多种不同的汇总方式返回列表或数据库中的分类汇总。 函数语法:SUBTOTAL(汇总方式,参数2,参数3,…,参数254) 注意事项: 1、第1参数为必选参数,表示要汇总的方式,不同的参数值的汇总方式如下图; 2、第2参数为必选参数,表示要对其进行分类汇总计算的第一个命名区域或引用; 3、第3-254参数为可选参数,表示要对其进行分类汇总计算的区域或引用; 4、不论使用什么第1参数取任何值,SUBTOTAL函数忽略任何不包括在筛选结果中的行; 5、SUBTOTAL函数适用于数据列或垂直区域,不适用于数据行或水平区域。 函数应用: 实例1、下表为某公司部分业务员销量统计,要求根据A12单元格的不同汇总方式对销量进行分析,如B12单元格所示。 函数公式:B12单元格输入 =SUBTOTAL(VLOOKUP(A12,{"平均销量",1;"业务员人数",2;"最大销量",4;"最低销量",5;"总销量",9},2,0),B2:B11) 解释说明: 1、根据不同汇总方式汇总用SUBTOTAL函数; 2、用VLOOKUP函数查找汇总方式对应的SUBTOTAL函数的第1参数VLOOKUP(A12,{"平均销量",1;"业务员人数",2;"最大销量",4;"最低销量",5;"总销量",9},2,0) 实例2、下表为某公司业务员销量表,要求计算筛选结果的销量总数,如C12单元格所示。 函数公式:C12单元格输入 =SUBTOTAL(9,C2:C11) 解释说明:忽略筛选结果求和用SUBTOTAL函数,第1参数可以用9或者109,请看上面的注意事项第4点。 实例3、在下表A列输入公式,自动根据B列和C列数据生成序号,当筛选的时候序号会自动更新,如下图所示。 函数公式:A2单元格输入 =SUBTOTAL(3,B$2:B2),公式下拉即可 解释说明:忽略筛选结果计算非空单元格个数用SUBTOTAL函数,需要计算B列非空单元格个数,所以第1参数可以用3或者103,请看上面的注意事项第4点。 实例4、在下表E1单元格中设置公式,要求当A列姓名有筛选时可以显示筛选的内容,效果如图所示。 函数公式:E1单元格输入 =INDEX($A$2:$A$11,MATCH(1,SUBTOTAL(3,OFFSET(A1,ROW($1:$10),)),)),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、要求提取筛选内容,首先用OFFSET函数将每一个姓名提取出来OFFSET(A1,ROW($1:$10),); 2、OFFSET函数第2参数用了多个值所以属于多维数组,需要用SUBTOTAL函数降维,第1参数取3表示计算提取出来的每个单元格的非空单元格个数,这个时候会忽略筛选值之外的其他值,所以除了筛选内容之外,其他都会被忽略,计数的时候为0,只有筛选单元格在计数的时候为1; 3、用MATCH函数查找1在SUBTOTAL所有结果里面的位置,这个位置就是姓名在A2:A11单元格区域的位置; 4、根据位置用INDEX函数可以返回引用的内容; 5、关于SUBTOTAL函数还有很多经典的用法,以后的分享会陆续有介绍。 结束语 各位亲早上好,今天是函数365系列教程连载第84天,连载函数88个,本系列教程按照预定计划将于5月底暂时告一段落,6月份我们将开始全新的学习之旅,接下来是技巧、图表、函数、VBA等内容的随机分享,大家如果有什么问题或想学习哪方面的Excel知识可以给我留言,这里将为你实现私人订制式的学习体验。
  • 函数365之ROUNDDOWN、ROUNDUP函数:将数值向下/向上舍入指定位数小数

    Excel函数2017-5-140评论553
    函数名称:ROUNDDOWN、ROUNDUP 函数作用:ROUNDDOWN函数用于将某个数值向下舍入指定的位数,ROUNDUP函数用于将某个数值向上舍入指定的位数。 函数语法: ROUNDDOWN(数值或引用,指定位数) ROUNDUP(数值或引用,指定位数) 注意事项: 1、第1参数为必选参数,表示需要向下/上舍入的任意实数; 2、第2参数为必选参数,表示舍入后的数字的位数; 3、ROUNDDOWN、ROUNDUP函数和函数ROUND功能相似,不同之处在于ROUND函数是按照四舍五入的规则; 4、如果第2参数大于0,则向下/上舍入到指定的小数位; 5、如果第2参数等于0,则向下/上舍入到最接近的整数; 6、如果第2参数小于0,则在小数点左侧向下/上进行舍入整数倍。 函数应用: 实例1、根据下表A列数据,按照不同的指定位数向下舍入数值,效果如图所示。 函数公式:B2单元格输入 =ROUNDDOWN($A2,B$1),公式右拉下拉即可 解释说明: 1、按照指定位数向下舍入数值用ROUNDDOWN函数,注意第1参数要舍入的数值单元格要列绝对引用,第2参数指定舍入的位数单元格要用行绝对引用; 2、如果是按照指定位数向上输入数值则用ROUNDUP函数,效果如下图 3、初学ROUNDDOWN、ROUNDUP这两个函数的童鞋可以对比上面两个截图,对比舍入不同位数的效果,可以更深刻理解这两个函数的运用。 实例2、下表为某公司业务员4月份销量,公司规定每吨提成10元,不足1吨不计算,要求计算每个业务员的提成,如C列所示。 函数公式:C2单元格输入 =ROUNDDOWN(B2,0)*10,公式下拉即可 解释说明:不足1吨不计算,也就是要向下取舍0位小数(取整),用ROUNDDOWN函数,第2参数为0即可。 实例3、下表为某公司员工一周的加班时间,公司规定小时加班工资为20元,不足1小时按1小时计算,要求计算每个员工的加班工资,如C列所示。 函数公式:C2单元格输入 =ROUNDUP(B2,0)*20,公式下拉即可 解释说明:不足1小时按1小时计算,也就是要向上取舍0位小数(取整),用ROUNDUP函数,第2参数为0即可。
  • 函数365之ISERROR函数:让错误无所遁形

    Excel函数2017-5-140评论554
    函数名称:ISERROR 函数作用:ISERROR函数用于当值为任意错误值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!)时返回TRUE,否则返回FALSE。 函数语法:ISERROR(参数) 注意事项: 1、该函数有且只有一个参数,参数可以是任意类型的数据、单元格引用或者公式; 2、如果参数的值是错误值,则函数结果返回TRUE,否则返回FALSE。 函数应用: 实例1、根据下表A列数据判断是否为错误值,如果是返回TRUE,否则返回FALSE,如B列所示。 函数公式:B2单元格输入 =ISERROR(A2),公式下拉即可 解释说明:判断是否错误值用ISERROR函数,当参数为错误值或者参数的结果为错误值时,ISERROR函数的结果为错误值。 实例2、根据指定的编号从下表A、B两列数据中查找编号对应的姓名,如果查找的编号没有对应的姓名则显示“查无此人”,如E列所示。 函数公式:E2单元格输入 =IF(ISERROR(VLOOKUP(D2,$A$2:$B$11,2,0)),"查无此人",VLOOKUP(D2,$A$2:$B$11,2,0)),公式下拉即可 解释说明: 1、根据编号查找姓名用VLOOKUP函数,公式为VLOOKUP(D2,$A$2:$B$11,2,0); 2、我们都知道当VLOOKUP函数查找不到与条件对应的内容时会返回#N/A错误值,所以我们用ISERROR函数判断VLOOKUP的结果是否为错误值,如果是返回TRUE,否则返回FALSE; 3、嵌套IF,当ISERROR的结果为TRUE时,显示“查无此人”,否则就查找对应的姓名; 4、这里我们可以用IFERROR纠错函数,公式=IFERROR(VLOOKUP(D2,$A$2:$B$11,2,0),"查无此人"),更简单,但是IFERROR函数是Excel2007及以上高版本才有的函数,2003不支持,需要用IF+ISERROR函数嵌套使用。