现在位置:首页 > Excel相关 > Excel函数
  • 函数365之ADDRESS函数:单元格引用与多表动态引用

    Excel函数2017-2-220评论1974
    函数名称: ADDRESS 函数作用: 在给出指定行数和列数的情况下,可以使用 ADDRESS 函数获取工作表单元格的地址。 函数语法: ADDRESS(行号, 列号, 引用类型, 引用样式, 引用的工作表名称) 注意事项: 引用类型主要有四类: 1或省略表示绝对单元格引用 2表示绝对行号,相对列标  3表示相对行号,绝对列标  4表示相对单元格引用 引用样式主要有两种: A1引用样式中,表示的是列和行将分别按字母和数字顺序添加标签,参数可用“1”表示 R1C1引用样式中,列和行均按数字顺序添加标签,参数可用“0”表示 注:第1、第2参数为必选参数,其中第3、第4、第5参数均为可选参数。 函数应用: 实例1、请用函数生成A-AZ的序列 函数公式: =SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,""),公式下拉即可 解释说明: ADDRESS第1参数用1表示取单元格第1行,第2参数用ROW(A1),公式下拉的时候会依次取得1,2,3,4…的序列,分别表示第1,2,3,4…列,所以整个公式可以取得A1-AZ1单元格,再用替换函数SUBSTITUTE将单元格A1-AZ1的行号“1”替换为空,即可得到答案。 实例2、下表中有多个工作表,每个工作表格式相同A/B两列为表格内容,A列姓名,B列销量,每个部门人员不超过100个,要求写一个公式,可以根据部门汇总不同部门的销量总和。 函数公式: =SUMPRODUCT(N(INDIRECT(ADDRESS(ROW(2:101),2,4,1,A2)))) 解释说明: 1、ADDRESS函数第1参数为ROW(2:101)表示取表格中的第2行至第101行,共100个单元格(因为题目说人员不超过100人); 2、ADDRESS函数第2参数为2表示固定取表格中的第2列(因为题目说表格B列为销量); 3、ADDRESS函数第3参数为4表示单元格引用为相对引用(本题这里选1-4任何一个数字都可以); 4、ADDRESS函数第4参数为1表示单元格引用样式为A1样式; 5、ADDRESS函数第5参数为A2表示引用A2单元格所在的部门为名称的工作表; 6、ADDRESS函数外面嵌套INDIRECT函数表示引用ADDRESS函数所表示的单元格的内容,因为涉及多个单元格,所以需要用INDIRECT函数外面需要嵌套N函数将多维引用将维处理; 7、最后用SUMPRODUCT函数将所有单元格的值相加。
  • 函数365之ABS函数:计算绝对值

    Excel函数2017-2-210评论419
    01 函数名称 ABS 02 函数作用 可用于返回数字的绝对值。 03 函数语法 ABS(number) 04 注意事项 Number  必需。需要计算其绝对值的实数。 05 函数应用 1 01 司机小李某天下午的营运全是在南北走向的鼓楼大街进行的.假定向南为正,向北为负,他这天下午行车里程如上图(单位:千米),若汽车耗油量为0.1 L/km,这天下午汽车共耗油多少升? 函数公式 =SUMPRODUCT(ABS(A2:A8))*0.1,结果等于8.3L 解释说明 将A2:A8每一个单元格的数值用ABS分别绝对值后再相加,等同于=(ABS(A2)+ABS(A3)+ABS(A4)+ABS(A5)+ABS(A6)+ABS(A7)+ABS(A8))*0.1 2 02 某工厂生产一批零件,根据零件质量要求:零件的长度可以有0.2㎝的误差,现抽查5个零件,检查数据记录如上表(超过规定长度的厘米数记为正数,不足规定长度的厘米数记为负数),请指出上面表中有多少个零件符合质量要求。 函数公式 =SUMPRODUCT(N(ABS(B2:G2)<0.2)),结果等于4 解释说明 用ABS函数分别将B2:G2单元格的每一个值绝对值,然后与0.2比较,得到TRUE或FALSE的结果,然后用N函数将TRUE转化为1,将FALSE转化为0,最后相加。 3 03 某钢铁厂承担了为奥运会鸟巢生产某种钢材零件的任务.其中一个车间生产一批圆形机器零件,从中抽取6件进行验证,比规定直径长的毫米数记作正数,比规定直径短的毫米数记作负数,检查记录如上表,请指出上面第几次抽查的零件好一些。 函数公式 =MATCH(MIN(ABS(B2:G2)),ABS(B2:G2),0),公式为数组公式,需CTRL+SHIFT+回车键三键结束,结果等于6,因为第6次的误差最小 解释说明 MIN(ABS(B2:G2))的意思是将B2:G2的每一个单元格绝对值后再取最小值,然后用MATCH判断该最小值在B2:G2的位置。 结束语 大家好,我是一村之长。公众号停发了三个多月了,突然觉得自己好颓废,这几天想了很多,终于下定决心给自己定了一个小目标,我要坚持365天公众号文章的不断更,坚持每天分享至少一个函数,论坛(www.1cunzhang.com)与博客(www.1excel.net)同步更新。内容主要有我们常见并常用的IF函数、SUMIF函数、VLOOKUP函数等,也有我们不常见的DB函数、FV函数、PV函数等我们不常用的财务函数,如果你想学习函数,如果你想知道函数能帮我们做些什么,那么你还在等什么呢?赶紧扫描二维码关注我的公众号,我坚持365天的分享,你坚持365天的阅读,我们都有满满的收获。
  • 什么是财务函数?

    Excel函数2017-1-260评论419
    作为一个会一点点函数的曾经的财务人员,总有人问我什么是财务函数,说想跟我学一点财务函数? 那么什么是财务函数呢?如果要专业的回答应该是 ACCRINT    返回定期付息证券的应计利息。 DB    使用固定余额递减法,计算一笔资产在给定期间内的折旧值。 DDB  使用双倍余额递减法或其他指定方法,计算一笔资产在给定期间内的折旧值。 ......... FV    基于固定利率及等额分期付款方式,返回某项投资的未来值。 当然,以上都是专业人士认为的财务函数,对于我这种水得一塌糊涂的曾经的财务人员来说,IF函数、VLOOKUP函数、COUNTIF函数、SUMIF函数等这些我用过的函数才是财务函数,其他都是不明觉厉! 今天有一个做财务很厉害的老同学问我一个问题 根据上表A列的数据查询B列的姓名是否有在A列出现过,根据他的数据我给他写的公式是: 公式:=IF(ISERROR(FIND(B2,PHONETIC(A$2:A$24))),"不存在","存在") 结果他用了我的公式计算的时候却告诉我公式报错,这是怎么回事呢?为什么我这边测试的公式没问题他那边用的时候却报错呢? 最后经检查发现,我看到的数据是24行,而他的数据源却有接近3000行,本身3000行如果算单元格也没什么,但是3000行的单元格的文本加起来就很恐怖了,总共有接近7万个字符,而在Excel中单元格容纳的字符数是有限制的,最多只能容纳32767个字符,所以当我们用PHONETIC函数将区域内所有单元格的字符连接起来的时候总的字符个数已经超出了单元格所能容纳的字符个数,系统就会提示资源不足,因此无法计算公式。那么要怎么办呢? 其实很简单,辅助列是简化公式的不二法门,万能的辅助列,让我们来看一下当数据过多无法连接字符串时该怎么解决这个问题: 我们可以通过一个辅助列,先把A列中的姓名提取出来,公式: =SUBSTITUTE(MID(A2,FIND("【客商辅助核算:",A2)+8,99),"】","") 然后在C列用公式判断B列姓名是否在D列出现即可,公式: =IF(COUNTIF(D:D,B2),"存在","不存在") 以上两种办法都可以解决这个问题,这是一个财务人员提出的问题,可是解决这个问题我们用的不是类似于DB或FV这类专业的财务函数,我们用了哪些函数呢? IF函数,这在Excel函数分类里属于逻辑函数; ISERROR、PHONETIC函数,这在Excel函数分类里属于信息函数; FIND、SUBSTITUTE、MID函数,这3个函数在Excel函数分类里属于文本函数; COUNTIF函数,这在Excel函数分类里属于统计函数。 以上这些函数都没有一个真正意义上的问题,然而组合到一起却能解决我们在财务工作中遇到的问题,所以在我看来,这些都是财务函数,除了这些函数之外,任何我们会用的函数都有可能是财务函数。都说不管黑猫白猫,抓到老鼠就是好猫,在我看来,不管什么函数,只要能解决财务工作中遇到的问题的都是财务函数。
  • 常用日期与时间函数

    Excel函数2017-1-260评论693
    1、返回当当前日期 =TODAY()    只是返回当前日期 =NOW()       返回当前日期和当前时间 2、计算当前时间距离2017年正月初一还有多少小时? =TEXT("2017-1-28"-NOW(),"[h]小时") 3、判断今年是否闰年 =IF(COUNT(DAY("2-29")),"闰年","平年") 4、计算当前月份有多少天 =DAY(EOMONTH(NOW(),0)) =DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,)) 5、计算两个日期相隔的天数、月数、年数 天数=DATEDIF(起始日期,截止日期,"D") 月数=DATEDIF(起始日期,截止日期,"M") 年数=DATEDIF(起始日期,截止日期,"Y") 6、返回上月同期时间 =TEXT(EDATE(TODAY(),-1),"YYYY-M-D")
  • 中国式排名与西式排名

    Excel函数2017-1-260评论577
    1、西式排名,使用排名函数RANK 公式如下: =RANK(B2,$B$2:$B$20) 语法: RANK 函数语法具有下列参数 (参数:为操作、事件、方法、属性、函数或过程提供信息的值。): Number   必需。需要找到排位的数字。 Ref   必需。数字列表数组或对数字列表的引用。Ref 中的非数值型值将被忽略。 Order   可选。一数字,指明数字排位的方式。 如果 order 为 0(零)或省略,Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。 如果 order 不为零,Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。 上面公式省略了第三参数,所以是将数据按从大到小进行排名,如果需要将数据按从小到大进行排名,则公式如下: =RANK(B2,$B$2:$B$20,1),效果如下图 以上就是Excel函数里面的专门用于排名的函数,不过大家看上面的第一个图圈出来的位置应该不难发现,西方人的排名方式和我们是有一定的区别的,当出现两个相同数字的时候,结果排名就会并列,并且缺少下一个名次,例如上面两个第1名,接下来就是第3名,缺少了第2名,那么,如果是中国式的排名会是怎么样的呢? 2、中国式排名公式 公式如下: =SUMPRODUCT(($B$2:$B$20>=B2)*(1/COUNTIF($B$2:$B$20,$B$2:$B$20))) 根据上面的截图我们不难发现中国式排名与西式排名的差异,中国排名不管有多少个并列排名,排名顺序都是连号的,但是西式排名不一样,一旦有并列排名,则排名顺序会出现断号,而我们实际工作中用到的排名往往是中国式的排名。
  • COUNT函数一家亲

    Excel函数2017-1-260评论506
    1、COUNT函数,计算包含数字的单元格以及参数列表中数字的个数 根据上图统计手机号码的个数,公式如下: =COUNT(B2:B10) 只要统计上图中包含数字的单元格的个数即可。 拓展,高级应用: 计算左侧数据中数字的个数,公式如下: =COUNT(-MID(A3,ROW($1:$99),1)),数组公式,三键结束 计算左侧数据中字母的个数,公式如下: =COUNT(N(INDIRECT(MID(A3,ROW($1:$99),1)&1))),数组公式,三键结束 2、COUNTA函数,计算区域中不为空的单元格个数 计算A列中的客户个数,公式如下: =COUNTA(A:A)-1 只要整列计算A列中的非空单元格,并减去标题行的1个单元格,即可得到客户个数。 3、COUNTBLANK函数,计算指定单元格区域中空白单元格的个数。 计算上图中空白单元格的个数,公式如下: =COUNTBLANK(A2:A12) 4、COUNTIF函数,对区域中满足单个指定条件的单元格进行计数。 统计上图中姓“张”的姓名个数,公式如下: =COUNTIF(A2:A10,"张*") 统计条件用了通配符“*”,“张*”表示以张字开头的任意字符串 5、COUNTIFS函数,将条件应用于跨多个区域的单元格,并计算符合所有条件的次数。  计算上图中日期在2016-5-18至2016-5-22之间的销量大于300的次数,公式如下: =COUNTIFS(A:A,">=2016-5-18",A:A,"<=2016-5-22",B:B,">300")
  • SUM函数之合并单元格求和

    Excel函数2017-1-260评论1200
    求和函数很简单,SUM函数相信很多人都会用,可是你确定你真的会求和吗?请看下图: 我们需要根据A列的区域对C列的销量进行求和,分别计算出每个区域的销量综合,结果如D列,公式如下: =SUM(C2:$C$20)-SUM(D3:$D$21) D2单元格输入公式,然后我们会发现公式得到的结果并不是1152,并不是我们要的正确结果,而且由于D列都是合并单元格,所以公式不能下拉填充,这时候我们要怎么办呢?别着急,遇到这种合并单元格填充公式我们可以选中D2:D19单元格区域,然后鼠标光标直到编辑栏,最后按CTRL+回车键即可填充公式,这时候的结果就是我们上图所看到的正确结果。 看完上面的合并单元格求和,我们再来看看下图的求和,又该如何运用求和公式呢? 公式如下: =IF(A2="","",SUM(C2:$C$20)-SUM(D3:$D$21)) D2输入公式后,双击填充柄填充公式即可得到答案。
  • Excel函数曰:舍得舍得,有舍才有得

    Excel函数2017-1-260评论550
    一、四舍五入,假设A1=1234.567 1、A1单元格的数值四舍五入取整数 公式:=ROUND(A1,0),结果为1235 2、A1单元格的数值四舍五入保留2位小数 公式:=ROUND(A1,2),结果为1234.57 3、A1单元格的数值四舍五入保留整百的数值 公式:=ROUND(A1,-2),结果为1200 注:ROUND是四舍五入函数,第二参数表示需要保留的小数位数。 二、向上取舍,假设A1=1234.123 1、A1单元格的数值向上取整数 公式:=ROUNDUP(A1,0),结果为1235 2、A1单元格的数值向上取舍保留2位小数 公式:=ROUNDUP(A1,2),结果为1234.13 3、A1单元格的数值向上取舍保留整百的数值 公式:=ROUNDUP(A1,-2),结果为1300 注:ROUNDUP是向上取舍函数,第二参数表示需要保留的小数位数 三、向下取舍,假设A1=1987.987 1、A1单元格的数值向下取整数 公式:=ROUNDDOWN(A1,0),结果为1987 2、A1单元格的数值向下取舍保留2位小数 公式:=ROUNDDOWN(A1,2),结果为1987.98 3、A1单元格的数值向下取舍保留整百的数值 公式:=ROUNDDOWN(A1,-2),结果为1900 注:ROUNDDOWN是向下取舍函数,第二参数表示需要保留的小数位数 四、截尾取整,假设A1=9.999999,需要截尾取整 公式:=INT(A1),结果为9 注:截尾取整函数也叫向下取整,就是不管需要取整的数值的小数位有多少,小数位的值有多大,一概舍去,保留整数。 五、向上舍入最接近指定值的倍数 1、假设A1=4.5,A1单元格的数值向上舍入最接近2的倍数的值 公式:=CEILING(A1,2),结果为6 2、假设A1=-4.5,A1单元格的数值向上舍入最接近-2的倍数的值 公式:=CEILING(A1,-2),结果为-6 注:CEILING是向上舍入指定倍数的函数,无论数字符号如何,都按远离 0 的方向向上舍入。 六、向下舍入最接近指定值的倍数 1、假设A1=4.5,A1单元格的数值向下舍入最接近2的倍数的值 公式:=FLOOR(A1,2),结果为4 2、假设A1=-4.5,A1单元格的数值向下舍入最接近-2的倍数的值 公式:=FLOOR(A1,-2),结果为-4 注:FLOOR是向下舍入指定倍数的函数
  • Excel时间都去哪了

    Excel函数2017-1-180评论467
    1、A1单元格为某个任意日期,判断该日期属于第几季度 公式:=TEXT(LEN(2^MONTH(A1)),"第0季度") 解释:MONTH函数可以取得日期的月份值,所以可以取得1-12的值,而2的1,2,3,...12次方的值分比是          2,4,8,16,32,64,128,256,512,1024,2048,4096,分别是1,1,1,2,2,2,3,3,3,4,4,4位数,对应每个月份值所属的季度。 2、判断今年是闰年还是平年 公式:=IF(COUNT("2-29"),"闰年","平年") 解释:2-29表示今年2月29日,如果今年是闰年则日期有效,用COUNT函数计算时会把该日期当数字计算,结果为1,如果今年是平年则该日期无效   (平年2月只有28日),所以日期结果为错误值,用COUNT计算会忽略错误值,结果为0,然后用IF即可判断闰年或平年。 3、计算今天距离元旦还有多少个小时 公式:=TEXT("2017-1-1"-NOW(),"[h]小时") 解释:元旦是2017-1-1,NOW函数可以返回当前的日期时间,用TEXT函数,格式[h]可以计算两个时间相隔的小时数。 4、计算本月最大天数是多少天 公式:=DAY(EOMONTH(TODAY(),0)) 解释:TODAY函数可以返回今天的日期,EOMONTH函数第2参数为0表示返回第1参数表示的月份的最后一天日期,所以EOMONTH(TODAY(),0)的结果表示   返回本月最后一天的日期,然后用DAY函数可以提取该日期的天数值。 5、用Excel函数公式做动态日历,请看下面动画 公式:=TEXT(TEXT(7*ROW(A1)+COLUMN(A1)-WEEKDAY(DATE($B$1,$D$1,1),1)-6,"[>0]0;"),"  [>"&DAY(DATE($B$1,$D$1+1,))&"] ")
  • 这样的SUMIF你用过吗?

    Excel函数2017-1-180评论571
    1、根据上表数据计算“张三”的销量总和 公式:=SUMIF(A2:A11,"张三",B2:B11) 这是很典型的SUMIF用法,第一参数求和的条件区域,第二参数是求和的条件,而第三参数是求和的区域,相信这种用法大家都用过; 2、根据上表数据计算姓“张”的员工的销量总和 公式:=SUMIF(A2:A11,"张*",B2:B11) 和1不同的是这里求和条件用了通配符“*”,“张*”表示以张字开头的任意字符串,这种用法相信大多数人也用过; 3、根据上表数据计算姓“张”和姓“李”的员工销量总和 公式:=SUM(SUMIF(A2:A11,{"张*","李*"},B2:B11)) 这题如果按照我们的常规做法应该是姓“张”和姓“李”的员工销量总和分部计算然后两个SUMIF的结果再相加,这里做法不一样,SUMIF的第二参数用了常量数组{"张*","李*"},然后SUMIF的结果也会是一个常量数组,该常量数组的两个值分别是姓“张”和姓“李”的员工的销量总和,最后用SUM函数将这个常量数组相加,这种用法你用过了吗? 4、根据下表数据计算姓“张”的员工销量总和 公式:=SUMIF(A2:F11,"张*",B2) 按照我们的常规思路公式应该是:=SUMIF(A2:A11,"张*",B2:B11)+SUMIF(C2:C11,"张*",D2:D11)+SUMIF(E2:E11,"张*",F2:F11),但是最优的公式明显是第一个,这种用法你用过吗?