• 动画演示拆解的方式与嵌套的方式写函数公式

      Excel函数2017-8-130评论232
      拆解的方式写公式 嵌套的方式写公式
    • 函数365之CELL函数:不可思议的信息函数之CELL函数

      Excel函数2017-5-310评论252
      函数名称:CELL 函数作用:CELL函数可用于返回有关单元格的格式、位置或内容的信息。 函数语法:CELL(返回信息类型,单元格或单元格区域引用) 注意事项: 1、该函数有且只有两个参数; 2、第1参数为必选参数,一个文本值,表示指定要返回的单元格信息的类型; 3、第2参数为可选参数,表示需要其相关信息的单元格; 4、如果省略第2参数,则将第一参数中指定的信息返回给最后更改的单元格; 5、如果第2参数是某一单元格区域,则函数CELL只将该信息返回给该区域左上角的单元格; 6、第1参数主要有以下可选值。 函数应用: 实例1、判断A列单元格格式是否会计专用格式(带千分号且保留两位小数),效果如B列所示。 函数公式:B2单元格输入 =IF(CELL("format",A2)=",2","是","不是"),公式下拉即可 解释说明: 1、判断单元格格式用信息函数CELL,第1参数为"format"; 2、CELL返回结果为“,2”时表示会计专用格式,格式代码“#,##0.00”; 3、所以用IF判断CELL的结果是否等于“,2”,如果是就返回“是”,否则返回“不是”; 4、有一点要注意的是当单元格的格式发生变化后,公式必须手动刷新,否则结果不会更新; 5、CELL函数第1参数为"format"时,主要可以判断以下格式。 实例2、用函数公式返回当前工作簿的名称,如下图A2单元格所示。 函数公式:A2单元格输入 =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1) 解释说明: 1、当CELL函数第1参数为"filename"时可以返回指定的文件名称,省略第2参数则表示返回当前工作簿名称; 2、CELL这个返回的文件名称是包含路径的,所以我们要把路径去掉,通过观察规律我们发现文件名称就包含在符号“[]”里面; 3、FIND("[",CELL("filename"))+1表示我们要提取的文件名称; 4、FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1表示我们要提取的文件名称的总长度 实例3、请根据下表A列的学校明细清单做出如动态图中的模糊输入效果,写明操作步骤。 操作步骤: 1、在B2单元格输入公式=IFERROR(INDEX(A:A,SMALL(IF(ISERROR(FIND(CELL("contents"),$A$2:$A$2880)),9^9,ROW($2:$2880)),ROW(A1))),""),数组公式CTRL+SHIFT+回车键三键结束,公式下拉填充; 2、选中B2:B2880单元格区域,然后在名称框输入“动态引用”,然后按回车键结束(这一步是快速给单元格区域定义一个名称); 3、选择D2:D11单元格区域(这里区域也可以选更大一点),然后输入有效性,选择“序列”,输入“=动态引用”; 4、具体操作请看下面动态图; 5、对于输入公式后弹出的循环引用不用管,另外为了不影响使用体验我们可以把B列整列设置成白色文字。 6、公式解释:CELL("contents")可以引用到最后一次修改的单元格的内容。
    • 函数365之FREQUENCY函数:分区间计算频率之它的作用超乎你想象

      Excel函数2017-5-300评论278
      函数名称:FREQUENCY 函数作用:FREQUENCY函数用于计算数值在某个区域内的出现频率,然后返回一个垂直数组。 函数语法:FREQUENCY(计算数据区域, 分布区间) 注意事项: 1、该函数有且只有两个必选参数; 2、第1参数表示一个值数组或对一组数值的引用,我们要为它计算频率; 3、第2参数表示一个区间数组或对区间的引用,该区间用于对第1参数中的数值进行分组; 4、如果第2参数中不包含任何数值,函数FREQUENCY返回的值与第1参数中的元素个数相等; 5、函数FREQUENCY将忽略空白单元格、逻辑值和文本; 6、返回的数组中的元素个数比第2参数中的元素个数多1个,多出来的元素表示最高区间之上的数值个数; 7、对于返回结果为数组的公式,必须以数组公式的形式输入。 函数应用: 实例1、下表为某公司业务员销量信息表,要求计算销量小于等于100,销量大于100小于等于300,销量大于300小于等于600,以及销量大于600的人数分别有多少,如E列所示。 函数公式:选中E2:E5单元格区域,输入公式=FREQUENCY(B2:B11,D2:D4),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、按区域计算频率分布是FREQUENCY的拿手好戏; 2、其中第1参数要计算的数据区域,也就是B2:B11单元格区域; 3、第2参数为要计算的区间划分,这里的区间是D2:D4,至于为什么不是D2:D5,请看语法注意事项第6点,我们要返回的是4个结果的值,所以参数只要3个单元格划分区间即可; 4、FREQUENCY函数对于分段区间的计算规则是含尾不含头,例如区间100-300就是大于100小于等于300。 实例2、根据下表A列数据计算不重复数值个数,效果如C2单元格所示。 函数公式:C2单元格输入 =SUM(N(FREQUENCY(A2:A11,A2:A11)>0)),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、分别以每一个数值作为区间的一个分段点计算分布频率FREQUENCY(A2:A11,A2:A11); 2、根据FREQUENCY函数的区间含尾不含头的计算规则我们可以发现当出现重复值的时候FREQUENCY的结果为0; 3、所以我们用IF判断FREQUENCY的结果是否大于0,如果是就是不重复的,否则就是重复的,然后用N函数将TRUE转换为1,FALSE转换为0然后相加。 实例3、下表为某公司部分业务员姓名,要求将不重复姓名提取出来,如C列所示。 函数公式:C2单元格输入 =IFERROR(INDEX(A:A,SMALL(IF(FREQUENCY(MATCH($A$2:$A$11,$A$2:$A$11,0),MATCH($A$2:$A$11,$A$2:$A$11,0))>0,ROW($2:$11)),ROW(A1))),""),数组公式CTRL+SHIFT+回车键三键结束,公式下拉即可 解释说明: 1、首先用MATCH函数计算出每一个姓名在A2:A11单元格区域中的位置MATCH($A$2:$A$11,$A$2:$A$11,0); 2、然后用FREQUENCY函数分别统计每一个区间在MATCH($A$2:$A$11,$A$2:$A$11,0)中的频率分布,大于0则为第1次出现的,等于0则为重复出现的; 3、接着就是我们最常用的组合IF+SMALL+INDEX+IFERROR组合使用。 实例4、下表为某班部分学生的测试成绩,要求对这些分数进行中国式排名,效果如C列所示。 函数公式:C2单元格输入 =SUM(N(FREQUENCY(IF($B$2:$B$11>B2,$B$2:$B$11),$B$2:$B$11)>0))+1,数组公式CTRL+SHIFT+回车键三键结束,公式下拉即可 解释说明: 1、首先用IF函数判断B2:B11单元格区域的每个值是否大于当前判断的单元格,如果是就返回单元格内容,否则返回FALSE,IF($B$2:$B$11>B2,$B$2:$B$11); 2、然后用FREQUENCY函数分别统计B2:B11的每一个单元格每一个区间在IF($B$2:$B$11>B2,$B$2:$B$11)中的频率分布; 3、然后判断FREQUENCY的结果是否大于0,最后用N函数将TRUE和FALSE分别转换成1和0然后相加。 实例5、根据下表A列姓名分别计算指定姓名连续出现最多的次数,效果如D列所示。 函数公式:D2单元格输入 =MAX(FREQUENCY(IF($A$2:$A$11=C2,ROW($1:$10)),IF($A$2:$A$11=C2,,ROW($1:$10)))),数组公式CTRL+SHIFT+回车键三键结束,公式下拉即可 解释说明: 1、首先用IF判断把不等于当前姓名的姓名转换成1-10的序列号,等于当前姓名的变成0,作为FREQUENCY的第2参数IF($A$2:$A$11=C2,,ROW($1:$10)); 2、然后用IF判断把等于当前姓名的转成1-10的序列号,不等于当前姓名的变成FALSE,作为FREQUENCY的第1参数IF($A$2:$A$11=C2,ROW($1:$10)); 3、最后用MAX函数取FREQUENCY函数结果中的最大值,就是我们要计算的连续出现最多次数。 祝所有喜欢学习Excel的朋友端午安康
    • 函数365之VDB函数:计算任意期间内的资产折旧总额

      Excel函数2017-5-290评论192
      函数名称:VDB 函数作用:VDB函数用于使用双倍余额递减法或其他指定的方法,返回指定的任何期间内(包括部分期间)的资产折旧值,函数 VDB 代表可变余额递减法。 函数语法:VDB(资产原值, 资产残值, 使用寿命, 开始计算折旧时间, 停止计算折旧时间, 余额递减速率, 折旧方法) 注意事项: 1、第1参数为必选参数,表示资产原值; 2、第2参数为必选参数,表示资产在折旧期末的价值(有时也称为资产残值),此值可以是0; 3、第3参数为必选参数,表示资产的折旧期数(有时也称作资产的使用寿命); 4、第4参数为必选参数,表示进行折旧计算的起始期间,该参数必须使用与第3参数的单位相同; 5、第5参数为必选参数,表示进行折旧计算的截止期间,该参数必须使用与第3参数的单位相同; 6、第6参数为可选参数,表示余额递减速率,如果参数被省略,则假设为2(双倍余额递减法); 7、第7参数为可选参数,TRUE或者FALSE,表示指定当折旧值大于余额递减计算值时,是否转用直线折旧法; 8、如果第7参数为TRUE,即使折旧值大于余额递减计算值,计算方法也不转用直线折旧法,如果为FALSE或被忽略,且折旧值大于余额递减计算值时,计算方法将转用线性折旧法。 函数应用: 实例1、下表为某公司某项固定资产的信息,要求根据下表信息计算该资产在第3年到第7年之间的折旧总额,如C5所示。 函数公式:C5单元格输入 =VDB(A2,B2,C2,A5,B5) 解释说明: 1、计算资产在某一个期间内的折旧值用SYD函数; 2、其中第1参数为资产原值,也就是A2单元格; 3、第2参数为资产残值,也就是B2单元格; 4、第3参数为使用寿命,也就是C2单元格; 5、第4参数为开始计算时间,也就是A5单元格; 6、第5参数为截止计算期间,对应B5单元格; 7、这里省略第6和第7参数,具体意义请看上面语法。 实例2、下表为某公司某项固定资产的信息,要求根据下表信息计算该资产在第20个月到第60个月之间的折旧总额,如C5所示。 函数公式:C5单元格输入 =VDB(A2,B2,C2*12,A5,B5) 解释说明: 1、计算资产在某一个期间内的折旧值用SYD函数; 2、其中第1参数为资产原值,也就是A2单元格; 3、第2参数为资产残值,也就是B2单元格; 4、第3参数为使用寿命,也就是C2单元格,但由于使用寿命是年而计算单位是月所以需要将年份转成月份,所以使用寿命需要乘以12; 5、第4参数为开始计算时间,也就是A5单元格; 6、第5参数为截止计算期间,对应B5单元格; 7、这里省略第6和第7参数,具体意义请看上面语法。 实例3、下表为某公司某项固定资产的信息,该资产购买并开始计提折旧的时间是2015-5-1,现在要求计算2017-1-1到2017-5-31这个时间段内的折旧总额,如B8所示。 函数公式:B8单元格输入 =VDB(B2,B3,B4*365,DATEDIF(B5,B6,"d"),DATEDIF(B5,B7,"d")) 解释说明: 1、计算资产在某一个期间内的折旧值用SYD函数; 2、其中第1参数为资产原值,也就是B2单元格; 3、第2参数为资产残值,也就是B3单元格; 4、第3参数为使用寿命,也就是B4单元格,但由于使用寿命是年而计算单位是需要按天计算,所以需要将年份转成天数,所以使用寿命需要乘以365; 5、第4参数为开始计算时间,这里我们用DATEDIF(B5,B6,"d")计算出计算开始日期距离开始计提折旧日期相差的天数; 6、第4参数为开始计算时间,这里我们用DATEDIF(B5,B7,"d")计算出计算截止日期距离开始计提折旧日期相差的天数; 7、这里省略第6和第7参数,具体意义请看上面语法。
    • 函数365之SYD函数:按年限总和折旧法计算固定资产的指定期间的折旧值

      Excel函数2017-5-280评论249
      函数名称:SYD 函数作用:SYD函数用于返回某项资产按年限总和折旧法计算的指定期间的折旧值。 函数语法:SYD(资产原值, 资产残值, 使用寿命, 折旧期间) 注意事项: 1、SYD函数有且只有4个参数,且4个参数均为必选参数; 2、第1参数表示资产原值; 3、第2参数表示资产在折旧期末的价值(有时也称为资产残值); 4、第3参数表示资产的折旧期数(有时也称作资产的使用寿命); 5、第4参数表示折旧的期间,其单位与第3参数相同。 函数应用: 实例1、下表为某公司某项固定资产的信息,要求根据下表信息按年限总和折旧法计算每一年的折旧额,如F列所示。 函数公式:F2单元格输入 =SYD($A$2,$B$2,$C$2,E2),公式下拉即可 解释说明: 1、按年限总和折旧法计算折旧用SYD函数; 2、其中第1参数为资产原值,也就是A2单元格; 3、第2参数为资产残值,也就是B2单元格; 4、第3参数为使用寿命,也就是C2单元格; 5、第4参数为折旧期间,对应E列单元格。 实例2、下表为某公司某项固定资产的信息,要求根据下表信息按年限总和折旧法计算指定月份的折旧额,如F列所示。 函数公式:F2单元格输入 =SYD($A$2,$B$2,$C$2*12,E2),公式下拉即可 解释说明: 1、按年限总和折旧法计算折旧用SYD函数; 2、其中第1参数为资产原值,也就是A2单元格; 3、第2参数为资产残值,也就是B2单元格; 4、第3参数为使用寿命,也就是C2单元格,但由于计算的折旧期间是以月为单位,所以使用寿命需要转换成月为单位,所以需要将年份数乘以12; 5、第4参数为折旧期间,对应E列单元格。
    • 函数365之ISNUMBER函数:判断是否数值,教你如何用函数模糊查询所有结果

      Excel函数2017-5-270评论357
      函数名称:ISNUMBER 函数作用:ISNUMBER函数用于判断参数是否数值,如果是数值则结果返回TRUE,否则返回FALSE。 函数语法:ISNUMBER(参数) 注意事项: 1、该函数有且只有一个参数,参数可以是任何值或者单元格引用; 2、如果参数是文本型数值时将会被当作文本,结果返回FALSE; 函数应用: 实例1、判断下表A列是否数值,如果是返回TRUE,否则返回FALSE,如B列所示。 函数公式:B2单元格输入 =ISNUMBER(A2),公式下拉即可 解释说明:判断是否数值用ISNUMBER函数,数值型数值和日期会返回TRUE,其他类型的数据则会返回FALSE,详细如上图C列解释。 实例2、根据下表AB两列数据,按照指定的商品名称计算销售总和,如E2单元格所示。 函数公式:E2单元格输入 =SUMPRODUCT(ISNUMBER(FIND(A2:A11,D2))*B2:B11) 解释说明: 1、首先用FIND函数分别查找A2:A11单元格区域里面的每一个单元格的内容在D2里面的位置FIND(A2:A11,D2); 2、如果FIND函数返回具体位置(数值)则表示该商品名称包含在D2单元格里面,否则就是不包含,所以这里需要用ISNUMBER函数判断FIND的结果是否为数值; 3、将ISNUMBER函数的结果分别乘以B2:B11单元格然后相加,就得到了总销量。 实例3、根据下表A列的公司名称,然后根据C2单元格的关键字查询所有包含关键字的公司名称,效果如D列所示。 函数公式:D2单元格输入 =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(FIND($C$2,$A$2:$A$17)),ROW($2:$17)),ROW(A1))),""),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、首先用FIND函数查找C2单元格在A2:A7单元格区域里面的每一个单元格内容的位置FIND($C$2,$A$2:$A$17); 2、如果FIND函数返回具体位置(数值)则表示该公司名称包含有C2单元格的关键字,否则就是不包含,所以这里需要用ISNUMBER函数判断FIND的结果是否为数值; 3、用IF函数对ISNUMBER函数的结果进行处理,当ISNUMBER函数的结果为TRUE时,IF的结果就是公司名称相应的行号ROW($2:$17),否则为FALSE; 4、接着用SMALL函数分别从小到大取得符合条件的公司名称所在行号,然后用INDEX函数获取该行号的内容,最后用IFERROR函数对错误值进行纠错。
    • 函数365之LEFTB、RIGHTB函数:截取字节之左右互搏术

      Excel函数2017-5-260评论165
      函数名称:LEFTB、RIGHTB 函数作用:LEFTB函数用于从字符串最左边第一个字节开始往右边方向截取指定个数字节,RIGHT函数用于从字符串最右边第一个字节开始往左边方向截取指定个数字节。 函数语法: LEFTB(需截取的文本字符串,截取的字节个数) RIGHTB(需截取的文本字符串,截取的字节个数) 注意事项: 1、截取的字节个数必须大于或等于零; 2、如果截取的字节个数大于文本总字节长度,则LEFTB/RIGHTB函数结果返回全部文本; 3、如果省略截取的字节个数,则默认其值为1。 函数应用: 实例1、根据下表A列字符串,然后按照指定要求截取字符,如B列和C列所示。 函数公式: B2单元额输入 =LEFTB(A2,5),公式下拉即可 C2单元格输入 =RIGHTB(A2,4),公式下拉即可 解释说明: 1、从左边截取5个字节用LEFTB函数,第2参数为5,从右边截4个字节用RIGHTB函数,第2参数为4; 2、在字符串中,一个中文汉字或中文标点符号=2个字节,1个字母拼音或数字或英文标点符号=1个字节。 实例2、根据下表中A列数据,提取相应的联系人和联系电话,效果如B列和C列所示。 函数公式: B2单元格输入 =LEFTB(A2,SEARCHB("?",A2)-1),公式下拉即可 C2单元格输入 =RIGHTB(A2,LENB(A2)-SEARCHB("?",A2)+1),公式下拉即可 解释说明: 1、首先我们要判断数据的规律,我们发现联系人和联系电话都是连在一起的,也就是说汉字和数字是连在一起的,汉字在左数字在右; 2、利用SEARCHB函数可以用通配符查找的特性,我们可以查找数据中第一个单字节(用"?"表示)的位置SEARCHB("?",A2); 3、第一个单字节的位置也就是第一个数字的位置,-1就可以得到汉字的字节个数,用LEFTB函数从左往右截取即可; 4、总的字节个数LENB(A2)减去第一个数字出现的位置SEARCHB("?",A2)再加1就可以得到数字的字节个数,然后用RIGHTB函数截取即可; 5、在提取联系人之后再提取联系方式我们可以用替换的方法,在A列数据中把姓名替换为空就可以得到联系电话,公式更简单=SUBSTITUTE(A2,B2,"")。
    • 函数365之FV函数:投资本息知多少,FV函数告诉你

      Excel函数2017-5-250评论168
      函数名称:FV 函数作用:FV函数用于基于固定利率及等额分期付款方式,返回某项投资的未来值。 函数语法:FV(各期利率,付款总期数,各期应付款,现值,付款时间方式) 注意事项: 1、第1参数为必选参数,表示各期利率; 2、第2参数为必选参数,表示年金的付款总期数; 3、第3参数为必选参数,表示各期所应支付的金额,其数值在整个年金期间保持不变。通常,该参数值包括本金和利息,但不包括其他费用或税款。如果省略 参数值,则必须包括第4参数; 4、第4参数为可选参数,表示现值,或一系列未来付款的当前值的累积和。如果省略该参数的值,则假设其值为 0(零),并且必须包括第2参数; 5、第5参数为可选参数,一般是数字0或1,用以指定各期的付款时间是在期初还是期末。如果省略该参数,则假设其值为0; 6、第1参数的利率与第2参数的付款期数的时间单位要保持一致(年/月/日); 7、对于所有参数,支出的款项,如银行存款,表示为负数;收入的款项,如股息收入,表示为正数。 函数应用: 实例1、某公司部门员工做了一项投资计划,每个月的月初会固定存入一定金额,如下图,要求计算最终的本息之和,如F列所示。 函数公式:F2单元格输入 =FV(D2/12,E2,-C2,-B2,1),公式下拉即可 解释说明: 1、根据投资期限和利率计算未来收益(本息)可以用FV函数; 2、其中第1参数为各期利率,对应D列,且由于每期付款和存款期间的单位都是月,所以需要把年利率折算成月利率D2/12; 3、第2参数为付款期数,也就是E列存款期限; 4、第3参数各期应付款对应C列的每月存款,且由于支出应做负数,所以是-C2; 5、第4参数现值对应B列的现值,且由于支出应做负数,所以是-B2; 5、第5参数表示付款时间是在期末还是期初,题意要求是每个月月初,所以第5参数的值是1或TRUE。 实例2、张三打算做一项理财,现在有A-J一共10个存款计划,每年存款均在期末完成,如下图,要求计算本息之和最高的投资计划及最高的本息之和,效果如G2和H2所示。 函数公式: G2单元格输入 =INDEX(A2:A11,MATCH(MAX(FV(B2:B11,C2:C11,-D2:D11,-E2:E11)),FV(B2:B11,C2:C11,-D2:D11,-E2:E11),0)),数组公式CTRL+SHIFT+回车键三键结束 H2单元格输入 =MAX(FV(B2:B11,C2:C11,-D2:D11,-E2:E11)),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、计算本息之和用FV函数; 2、其中第1参数为各期利率也就是B列的利率B2:B11单元格; 3、第2参数表示付款总期数对应存款年限C2:C11; 4、第3参数为各期应付款对应每年存款D2:D11,因为从财务角度来说支出要用负数,所以是-D2:D11; 5、第4参数表示现值对应E2:E11,因为从财务角度来说支出要用负数,所以是-E2:E11; 6、计算出所有计划的本息之和后用MAX函数即可获得最高的本息之和,也就是H2单元格要计算的最高本息之和; 7、用MATCH函数查找最高本息之和MAX(FV(B2:B11,C2:C11,-D2:D11,-E2:E11))在所有本息之和FV(B2:B11,C2:C11,-D2:D11,-E2:E11)中的位置; 8、根据最高本息之和所在的位置用INDEX函数返回其所对应的A列的位置,也就是最高本息之和的计划。
    • 函数365之CEILING函数:将数值向上舍入(沿绝对值增大的方向)到最接近的指定数值的倍数

      Excel函数2017-5-240评论192
      函数名称:CEILING 函数作用:CEILING函数用于将数值向上舍入(沿绝对值增大的方向)到最接近的指定数值的倍数。 函数语法:CEILING(需要舍入的数值,舍入倍数的指定值) 注意事项: 1、该函数有且只有两个参数,且两个参数都是必选参数; 2、如果任一参数为非数值型,则CEILING将返回错误值#VALUE!错误值; 3、无论数字符号如何,都按远离0的方向向上舍入,如果数字已经为指定值的倍数,则不进行舍入; 4、如果两个参数均为负数,则对值按远离0的方向进行向下舍入; 5、如果第1参数为负,第2参数为正,则对值按朝向0的方向进行向上舍入。  函数应用: 实例1、根据A列数值和B列指定的舍入值,计算向上舍入最接近指定数值的倍数,效果如C列所示。 函数公式:C2单元格输入=CEILING(A2,B2),公式下拉即可 解释说明:将数值向上舍入指定值的倍数用CEILING函数,具体含义如上图D列所示。 实例2、端午节快到了,某公司为了给员工发福利,给每个部门都单独买了一些粽子,但分发的时候发现不能均分,请根据下表计算每个部门需要增加购买多少粽子才可以均分,效果如D列所示。 函数公式:D2单元格输入 =CEILING(C2,B2)-C2,公式下拉即可 解释说明: 1、首先用CEILING(C2,B2)公式计算每个员工均分需要的粽子总数,也就是最接近已购数量且比已购数量大的数值,是部门人数的倍数; 2、然后用需要的粽子总数减去已购粽子个数,就得到了需要增加的粽子的个数。 实例3、某公司规定员工下班时间为下午六点钟,超过后算加班,为了激励员工,加班时间以每隔15分钟计算一次,不足15分钟也算15分钟,请根据下表每个员工的实际下班时间计算加班时间,如C列所示。 函数公式:C2单元格输入 =CEILING((B2-"18:00")*24,15/60),公式下拉即可 解释说明: 1、下午六点也就是18:00,所以实际下班时间减去18:00就是加班时间B2-"18:00"; 2、由于每隔15分钟计算,也就是15/60小时,所以加班时间应该是15/60的倍数,这里用CEILING函数可以将加班时间向上取舍最接近的15/60的倍数。
    • 函数365之FLOOR函数:将数值向下取舍到某个指定数值的倍数

      Excel函数2017-5-230评论163
      函数名称:FLOOR 函数作用:FLOOR函数用于将数值向下舍入(向零的方向)到最接近的指定数值的倍数。 函数语法:FLOOR(需要舍入的数值,舍入的值的倍数) 注意事项: 1、该函数有且只有两个参数,且两个参数都是必选参数; 2、如果任一参数为非数值型,则FLOOR将返回错误值#VALUE!错误值; 3、如果第1参数的符号为正,第2参数的符号为负,则FLOOR将返回错误值#NUM!; 4、如果第1参数的符号为正,函数值会向靠近零的方向舍入;如果第1参数的符号为负,函数值会向远离零的方向舍入;如果第1参数恰好是第2参数的整数倍,则不进行舍入。 函数应用: 实例1、根据A列数值和B列指定的舍入倍数,计算向下舍入最接近指定数值的倍数,效果如C列所示。 函数公式:C2单元格输入=FLOOR(A2,B2),公式下拉即可 解释说明:将数值向下舍入指定倍数用FLOOR函数,具体含义如上图D列所示。 实例2、端午节快到了,某公司为了给员工发福利,给每个部门都单独买了一些粽子,下表是每个部门的人数与粽子的个数,要求计算每个人均分粽子后剩余的粽子个数,如D列所示。 函数公式:D2单元格输入 =C2-FLOOR(C2,B2),公式下拉即可 解释说明: 1、首先用FLOOR(C2,B2)公式计算每个员工均分后得到的粽子总数,也就是粽子个数向下舍入最接近的员工的倍数; 2、然后用粽子个数减去员工分到的粽子总数,就得到了剩余粽子的个数。 实例3、某公司规定员工下班时间为下午六点钟,超过后算加班,但是小于0.5小时不计算,请根据下表每个员工的实际下班时间计算加班时间,如C列所示。 函数公式:C2单元格输入 =FLOOR((B2-"18:00")*24,0.5),公式下拉即可 解释说明: 1、下午六点也就是18:00,所以实际下班时间减去18:00就是加班时间B2-"18:00"; 2、由于低于0.5小时的部分不算加班,所以加班时间应该是0.5的倍数,这里用FLOOR函数可以将加班时间向下取舍最接近的0.5的倍数。