现在位置:首页 > 发表在 2017年03月 的所有文章
  • 函数365之HLOOKUP函数:横看成岭侧成峰之横向查询

    Excel函数2017-3-310评论500
    函数名称:HLOOKUP 函数作用:HLOOKUP函数用于根据指定条件在某个单元格区域从上(首行-条件行)往下查找相对应的值(结果行)。 函数语法:=HLOOKUP(查找条件,查找区域,返回查找区域第几行,匹配方式) 注意事项: 1、第3参数必须大于等于1,否则结果返回#VALUE!错误值。 2、第3参数必须小于等于查找区域的总行数,否则结果返回#REF!错误值。 3、第4参数为必选参数,当第4参数为0或者FALSE时表示精确查找,否则就是模糊查找,精确查找时属于遍历法查找,模糊查找时属于二分法查找。 4、当精确查找时,查找区域的第1行必须包含查找条件的值,如果查找条件在查找区域中第1行找不到相同项,则HLOOKUP将返回错误值 #N/A。 5、当模糊查找时,查找区域的第1行必须按照升序排列,否则查找结果可能不正确。 函数应用: 实例1、根据下表数据按照指定姓名查找对应的销量。(一般查找) 函数公式:=HLOOKUP(A5,B1:K2,2,0) 解释说明:上表第1行(B1:K1)是姓名,包含了查找的条件,第2行(B2:K2)是销量,对应了查找的结果,所以可以用HLOOKUP函数进行查找,查找区域为B1:K2,结果返回该区域的第2行。 实例2、根据下表数据按照指定姓名和月份查找对应的销量。(行列双条件查找) 函数公式:=HLOOKUP(A13,$B$1:$K$10,MATCH(B13,$A$1:$A$10,0),0),公式下拉即可 解释说明:本题主要考的是HLOOKUP函数第3参数返回行数,这里用MATCH函数查找月份在A1:A0单元格区域的位置,该位置就是HLOOKUP函数要返回的行数。 实例3、根据下表信息然后按照指定的学号查找相应的姓名。(反向查找) 函数公式:=HLOOKUP(A5,IF({1;0},B2:K2,B1:K1),2,0) 解释说明: 1、因为查找的结果在第1行(B1:K1),查找条件对应的区域在第2行(B2:K2),所以不能直接用HLOOKUP函数; 2、这里用了IF({1;0},B2:K2,B1:K1)将第1行和第2行的顺序进行了置换,重新构建了一个两行的区域,所以第3参数为2表示返回新区域的第2行。 实例4、根据下表数据信息,然后按照指定姓名和部门查找相应的员工工号。(多条件查找) 函数公式:=HLOOKUP(A6&B6,IF({1;0},B1:K1&B2:K2,B3:K3),2,0),数组公式CTRL+SHIFT+回车键三键结束公式。 解释说明: 1、要根据姓名和部门两个条件进行查找,所以用“&”连接符将两个条件连接起来; 2、用IF({1;0},B1:K1&B2:K2,B3:K3)将两个条件的行区域用“&”连接起来然后与结果行重新构建一个两行的区域。 实5、根据下表级别分段信息和每个人的分数,判断每个人的级别。(模糊查找,二分法查找) 函数公式:=HLOOKUP(B2,$F$1:$I$2,2),公式下拉即可 解释说明:根据分段进行查找匹配可以用HLOOKUP的模糊查找,也就是二分法查找,省略最后一个参数。 实例6、根据下表的姓名和对应姓名及分数,然后按照指定姓名查找所有对应的科目及分数。(多结果查找) 函数公式:=IFERROR(HLOOKUP($A$6&ROW(A1),IF({1;0},$B$1:$K$1&TRANSPOSE(COUNTIF(OFFSET($A$1,,1,1,ROW($1:$10)),$A$6)),OFFSET($A$2,COLUMN(A1)-1,1,,10)),2,0),""),数组公式CTRL+SHIFT+回车键三键结束,公式右拉下拉即可 解释说明: 1、COUNTIF(OFFSET($A$1,,1,1,ROW($1:$10)),$A$6)可以分别计算出当前的姓名是第几次出现,对应查找的条件姓名连接ROW(A1)表示依次查找第1,2,3…次出现的姓名的对应值; 2、用IF({1;0},区域1&区域2,区域3)模式可以重新构建一个新的区域,因为COUNTIF函数的结果为1列10行,所以要用TRANSPOSE函数转换成1行10列; 3、IFERROR函数用于当HLOOKUP函数查找不到结果时返回空格,避免出现错误值。 实例7、根据下表的姓名和分数然后按照指定的姓名查找计算分数之和。(多维度查找) 函数公式:=SUMPRODUCT(HLOOKUP(T(IF({1},A5:A8)),B1:K2,2,0)) 解释说明: 1、HLOOKUP函数一般第1参数不支持数组,这里用了数组,所以用T(IF({1},区域)对数组进行降维; 2、本来数组公式需要用三键结束的,但是这里用了SUMPRODUCT函数对结果进行相加,因为该函数本来就默认执行数组运算,所以不需要三键结束。
  • 函数365之TEXT函数:兵无常势,我无常形,千变万化尽在TEXT

    Excel函数2017-3-300评论520
    函数名称:TEXT 函数作用:TEXT函数可将数值转换为文本,并可使用户通过使用特殊格式字符串来指定显示格式。 函数语法:TEXT(数值或单元格,特定格式) 注意事项: 1、TEXT函数的结果为文本,如果用其返回的文本型数据继续以后的运算时,应该将其转化为数值型数据; 2、TEXT函数第一参数如果是文本,必须在两端加上""; 3、TEXT只能支持15位数。 函数应用: 实例1、根据下面一系列的格式转换初步认识TEXT函数的无穷变化。 1、A1=123456.78,数值加千分号,四舍五入保留1位小数,将A1变成“123,456.8”。 函数公式:=TEXT(A1,"#,##0.0") 2、A1=68,加个百分号%,将A1变成“68%”。 函数公式:=TEXT(A1/100,"#%") 3、A1=1.25,整数分开,小数变成分数,将A1变成“1 1/4”。 函数公式:=TEXT(A1,"? ?/?") 4、A1=20170330,变成标准日期格式,将A1变成“2017-03-30”。 函数公式:=TEXT(A1,"00-00-00") 5、A1=168.68,在数值前面加上文本“今天买了”,数值后面加上文本“元的东西”,将A1变成“今天买了168.68元的东西”。 函数公式:=TEXT(A1,"今天买了0.00元的东西") 6、A1=2345678,转换成中文小写数字,将A1变成“二百三十四万五千六百七十八”。 函数公式:=TEXT(A1,"[DBNum1][$-804]G/通用格式") 7、A1=251314,转成成中文大写数字,将A1变成“贰拾伍万壹仟叁佰壹拾肆”。 函数公式:=TEXT(A1,"[DBNum2][$-804]G/通用格式") 8、A1=2017-3-30,将A1变成“2017年3月30日我爱学习”。 函数公式:=TEXT(A1,"yyyy年m月d日我爱学习") 9、A1=2017-3-30,计算该日期是星期几,将A1变成“星期四”。 函数公式:=TEXT(A1,"aaaa") 10、A1=12:00,用秒来表示这个时间,将A1变成“43200秒”。 函数公式:=TEXT(A1,"[s]秒") 实例2、判断下表A列的每个单元格的数据类型,效果如B列所示。 函数公式:=TEXT(A2,"正数;负数;零值;非数值"),公式下拉即可 解释说明:这里主要考一个知识点,Excel单元格里面的数据类型主要分为四部分,用分号“;”分开,分别是“正数;负数;0;非数值”。 实例3、根据下表B列的分数判断,当分数小于60分为“不及格”,当分数大于90分为“优秀”,其他为“良好”,效果如C列。 函数公式:=TEXT(B2,"[>90]优秀;[<60]不及格;良好"),公式下拉即可 解释说明:学了TEXT函数,我们终于知道并不是所有的逻辑判断都用IF函数,有时候用TEXT函数更简单。 实例4、根据下表B列的分数判断,以60分为标准,每少1分扣20元,每多1分奖励20元,奖罚都不超过300元,如C列所示。 函数公式:=TEXT((60-B2)*20,"[>300]""300"";[<-300]""-300"";0"),公式下拉即可 解释说明: 1、根据题意,(60-B2)*20可以计算出扣款情况,正数为扣款,负数为奖励; 2、这里300和-300都要用双引用“""”,否则其中的0就会被误认为占位符。
  • 函数365之LARGE函数:名列前茅与从大到小降序排列

    Excel函数2017-3-290评论733
    函数名称:LARGE 函数作用:LARGE函数用于返回一组数据中的第K个最大值。 函数语法:LARGE(单元格区域或数组,K) 注意事项: 1、如果第1参数为空,函数LARGE返回错误值#NUM!。 2、K为大于0的数字,如果K≤0或K大于数据的个数,函数LARGE返回错误值#NUM!。 3、当K不为整数时,计算时会截尾取整,例如K=9.99,计算时会被默认为9。 函数应用: 实例1、根据下表数据计算销量前三名之和,效果如D2单元格。 函数公式:=SUM(LARGE(B2:B11,{1,2,3}))或者=LARGE(B2:B11,1)+LARGE(B2:B11,2)+LARGE(B2:B11,3) 解释说明:要计算前三名之和,就是要将第1大,第2大,第3大的数值相加,所以用LARGE函数,第2参数分别是1,2,3,这里可以用常量数组{1,2,3}来表示LARGE的第2参数,最后用SUM相加。 实例2、根据下表A/B两列数据,将B列的分数从高到低进行升序排列,效果如D/E列所示。 函数公式:=INDEX(A:A,MOD(LARGE($B$2:$B$11*100+ROW($2:$11),ROW($1:$10)),100)),区域数组公式,选中D2:D11区域后输入公式然后按CTRL+SHIFT+回车键三键结束,然后公式右拉到E列。 解释说明: 1、将B2:B11单元格的每一个数都放大100倍然后分别加上各自所在的行号,用$B$2:$B$11*100+ROW($2:$11)表示; 2、将$B$2:$B$11*100+ROW($2:$11)按从大到小进行排序,用LARGE函数,第2参数row(1:10); 3、将重新排序后的数值分别除以100然后求余数(MOD函数),这个余数就是该数值所在的行号,根据行号求内容可以用INDEX函数。
  • 函数365之PHONETIC函数:文本连接技术哪家强,Excel函数找PHONETIC

    Excel函数2017-3-280评论403
    函数名称:PHONETIC 函数作用:PHONETIC函数可以用于将多个连续文本字符串连接成一个文本字符串。 函数语法:PHONETIC(单元格区域) 注意事项: 1、PHONETIC函数只有一个参数,可以是单个单元格或者一个连续的单元格区域; 2、PHONETIC函数只对文本字符有效,如果单元格是数值型数字或者公式,将会被忽略。 函数应用: 实例1、根据下表A、B两列数据将所有姓名连接到一个单元格,结果如下图黄色区域所示。 函数公式:=PHONETIC(A2:B11) 解释说明:如果需要串联多个连续单元格的文本字符,可以直接用PHONETIC函数,简单高效。 实例2、下表B列数据是某业务员在一段时间内的客户拜访记录,请统计指定客户的拜访次数,效果如E列所示。 函数公式:=(LEN(PHONETIC($B$2:$B$11))-LEN(SUBSTITUTE(PHONETIC($B$2:$B$11),D2,"")))/LEN(D2),公式下拉即可 解释说明: 1、解题的思路是将所有客户姓名全部连接成一个字符串PHONETIC($B$2:$B$11),然后统计该字符串的字符总数LEN(PHONETIC($B$2:$B$11)); 2、接着把连接后的字符串里面的要统计的那个客户的姓名替换为空SUBSTITUTE(PHONETIC($B$2:$B$11),D2,""),然后再统计字符总数LEN; 3、将原字符串总数减去去掉要统计的客户的字符串后的字符总数,这个差值就是我们要统计的客户所占的字符个数,然后再判断客户姓名占几个字符,客户字符总数/客户姓名字符数=客户姓名次数。 实例3、下表左侧是区域与业务员的划分明细表,要求将每个区域的所有人员合并到一个单元格,如F列所示。 函数公式:=PHONETIC(OFFSET($A$1,MATCH(E2,$A$2:$A$11,0),1,COUNTIF($A$2:$A$11,E2),2)),公式下拉即可 解释说明: 1、解题思路,我们可以用OFFSET函数构造一个新的单元格区域,例如A区域姓名所在的区域B2:B3,然后用PHONETIC函数对字符进行连接; 2、首先我们要计算出每个区域从第几行开始,这里用MATCH(E2,$A$2:$A$11,0); 3、因为我们选择的参照点是A1,但是我们需要的内容在B列,所以OFFSET第3参数(偏移的列数)为1; 4、要计算每个区域有多少人,所以用COUNTIF($A$2:$A$11,E2),这个区域的人数就是OFFSET函数的第4参数(新区域的高度); 5、OFFSET函数的第5参数为2(新区域的宽度)是因为新区域包含了B和C两列,这里C列是辅助列,C列每个单元格里面都有一个空格(眼睛是看不见的)。 实例4、提取A列字符串中的数值并相加,答案如C列所示。 函数公式:=SUMPRODUCT(TEXT(LEFT(TEXT(MID(PHONETIC(A2:A4)&"M",ROW($1:$80),COLUMN($A:$FT)),),COLUMN($A:$FT)-1),"G/通用格式;-G/通用格式;0;!0")*ISERR(-MID(PHONETIC(A2:A4),ROW($1:$80)-1,2))) 解释说明:原理太过于复杂不做详细说明,想要理解请学完每个函数的用法后自行拆解公式,更多精彩敬请期待。
  • 函数365之VLOOKUP函数:千变万化之众里寻她千百度

    Excel函数2017-3-270评论451
    函数名称:VLOOKUP 函数作用:VLOOKUP函数用于根据指定条件在某个单元格区域从左往右查找相对应的值。 函数语法:=VLOOKUP(查找条件,查找区域,返回查找区域第几列,匹配方式) 注意事项: 1、第3参数必须大于等于1,否则结果返回#VALUE!错误值。 2、第3参数必须小于等于查找区域的总列数,否则结果返回#REF!错误值。 3、第4参数为必选参数,当第4参数为0或者FALSE时表示精确查找,否则就是模糊查找,精确查找时属于遍历法查找,模糊查找时属于二分法查找。 4、当精确查找时,查找区域的第1列必须包含查找条件的值,如果查找条件在查找区域中第1列找不到相同项,则VLOOKUP将返回错误值 #N/A。 5、当模糊查找时,查找区域的第1列必须按照升序排列,否则查找结果可能不正确。 函数应用: 实例1、根据下表A、B列数据按照指定姓名查找对应的年龄。(一般查找) 函数公式:=VLOOKUP(D2,A2:B11,2,0) 解释说明:上表A列是姓名,包含了查找的条件,B列是年龄,对应了查找的结果,所以可以用VLOOKUP函数进行查找,查找区域为A2:B11,结果返回该区域的第2列。 实例2、根据下表数据按照指定姓名查找对应的月份的销量。(多列查找) 函数公式:=VLOOKUP($A14,$A$2:$F$11,COLUMN(B1),0),公式右拉即可 解释说明:本题主要考的是VLOOKUP函数第3参数返回列数,这里需要依次返回第2,3,4,5,6列,所以用COLUMN(B1)表示,右拉即可获得我们需要的数值,另外还要注意查找条件必须要列绝对引用或绝对引用。 实例3、根据下表信息然后按照指定的业务员查找相应的客户姓名。(反向查找) 函数公式:=VLOOKUP(E2,IF({1,0},C2:C11,A2:A11),2,0) 解释说明: 1、因为查找的结果在A列,查找条件对应的区域在C列,所以不能直接用VLOOKUP函数; 2、这里用了IF({1,0},C2:C11,A2:A11)将C列和A列的顺序进行了置换,重新构建了一个两列的区域,所以第3参数为2表示返回新区域的第2列。 实例4、根据下表A、B、C列信息,然后按照指定月份和姓名查找相应的业绩考评结果。(多条件查找) 函数公式:=VLOOKUP(E2&F2,IF({1,0},A2:A11&B2:B11,C2:C11),2,0),数组公式CTRL+SHIFT+回车键三键结束公式。 解释说明: 1、要根据月份和姓名两个条件进行查找,所以用“&”连接符将两个条件连接起来; 2、用IF({1,0},A2:A11&B2:B11,C2:C11)将两个条件列用“&”连接起来然后与结果列重新构建一个两列的区域。 实5、根据下表税率分段信息及应纳税所得额信息,计算每个人的个人所得税。(模糊查找,二分法查找) 函数公式:=B2*VLOOKUP(B2,$E$2:$G$8,2)-VLOOKUP(B2,$E$2:$G$8,3),公式下拉即可 解释说明: 1、根据分段进行查找匹配可以用VLOOKUP的模糊查找,也就是二分法查找,省略最后一个参数。 2、分别二分法查找相应税率和相应的速算扣除数,应纳税所得额*税率-速算扣除数=个人所得税 实例6、根据下表的区域和对应姓名然后按照指定区域查找所有对应的姓名。(多结果查找) 函数公式:=IFERROR(VLOOKUP($D$2&ROW(A1),IF({1,0},$A$2:$A$11&COUNTIF(OFFSET($A$1,1,,ROW($1:$10)),$D$2),$B$2:$B$11),2,0),""),数组公式CTRL+SHIFT+回车键三键结束,公式下拉即可 解释说明: 1、COUNTIF(OFFSET($A$1,1,,ROW($1:$10)),$D$2)可以分别计算出当前的A区是第几次出现,对应查找的条件A区连接ROW(A1)表示依次查找第1,2,3…次出现的A区的对应值; 2、用IF({1,0},区域1&区域2,区域3)模式可以重新构建一个新的区域; 3、IFERROR函数用于当VLOOKUP函数查找不到结果时返回空格,避免出现错误值。 实例7、根据下表的姓名和销量然后按照指定的姓名查找计算销量的综合。(多维度查找) 函数公式:=SUMPRODUCT(VLOOKUP(T(IF({1},D2:D4)),A2:B11,2,0)) 解释说明: 1、VLOOKUP函数一般第1参数不支持数组,这里用了数组,所以用T(IF({1},区域)对数组进行降维; 2、本来数组公式需要用三键结束的,但是这里用了SUMPRODUCT函数对结果进行相加,因为该函数本来就默认执行数组运算,所以不需要三键结束。
  • 函数365之MOD函数:两数相除求余数之缘M求余

    Excel函数2017-3-260评论510
    函数名称:MOD 函数作用:MOD函数可用于返回两数相除的余数。 函数语法:MOD(被除数,除数) 注意事项: 1、如果第2参数为0,MOD结果返回#DIV/0!。 2、如果第2参数可以被第1参数整除,则MOD结果为0。 3、MOD函数的结果的正负号与第2参数相同。 函数应用: 实例1、用函数公式构造如下表循环序列1,2,3,1,2,3,1,2,3,…,1,2,3 函数公式:=MOD(ROW(A1)-1,3)+1,公式下拉即可 解释说明: 1、从0开始,每个整数依次除以3得到的余数分别为0,1,2,0,1,2,…,0,1,2,将这个结果+1就得到我们需要的数列,所以我们需要得到0,1,2,3,…这样的一个序列,也就是ROW(A1)-1下拉。 2、构建类似循环数列可以套用公式“MOD(ROW(A1)+1,3)+首项”,如果是横向序列就将ROW改成COLUMN 实例2、在一个大于1的自然数中,如果只有1和它本身才可以被它整除,那么这个数我们称为质数,否则为合数,根据下表A列数字判断是质数还是合数,效果如B列。 函数公式:=IF(SUMPRODUCT(N(MOD(A2,ROW(INDIRECT("1:"&A2)))=0))>2,"合数","质数"),公式下拉即可 解释说明: 1、要判断质数或者合数,就要依次将1至该数字(用ROW(INDIRECT("1:"&A2))表示)分别与该数字相除然后求余数,用MOD函数; 2、如果除数可以被被除数整除,则MOD的结果为0 3、用IF判断如果SUMPRODUCT函数的结果大于2则为合数,否则为质数。 实例3、将下表中A、B、C三列姓名合并成一列,效果如E列所示。 函数公式:=INDEX($A$2:$C$5,MOD(ROW(A1)-1,4)+1,INT((ROW(A1)-1)/4)+1),公式下拉即可 解释说明: 1、因为要转置行列,所以我们可以用INDEX函数,因为INDEX函数就是用来根据行和列返回引用内容的; 2、第2参数表示返回的行号,这里需要1,2,3,4,1,2,3,4,1,2,3,4的一个循环,所以用MOD(ROW(A1)-1,4)+1表示; 3、第3参数表示返回的列号,这里需要1,1,1,1,2,2,2,2,3,3,3,3的一个循环,所以用INT((ROW(A1)-1)/4)+1表示。 知识拓展:如下图将三列转换成一列又该如何写公式呢?可以参考上面公式学,有想法才能有方法,举一反三多想多练才能学好函数。
  • 函数365之CONCATENATE函数:把你的心我的心串一串,串一个同心圆

    Excel函数2017-3-250评论547
    函数名称:CONCATENATE 函数作用:CONCATENATE函数可将最多255个文本字符串联接成一个文本字符串。 函数语法:CONCATENATE(字符串1,字符串2,字符串3,…,字符串255) 注意事项: 1、第1参数为必选参数,第2-255参数为可选参数。 2、参数可以是文本、数字、单元格引用或这些项的组合。 函数应用: 实例1、根据下表A、B、C列数字连接成日期,结果如D列所示。 函数公式:=CONCATENATE(A2,"-",B2,"-",C2),公式下拉即可 解释说明:连接字符可以用CONCATENATE函数,这里每两个数字之间要加个“-”,参数之间用逗号“,”隔开 实例2、根据下表A、B、C列数字连接成日期,结果如D列所示。 函数公式:=CONCATENATE(TEXT(A2,"yyyy-m-d"),B2,TEXT(C2,"#,##0.00元")) 解释说明: 1、这里不能直接连接字符,否则就会变成“42819销售A产品总金额123456.78”,不符合我们要求; 2、在连接的时候日期直接会变成序列号,所以我们要用TEXT函数将日期格式化,然后金额我们需要用TEXT函数将它变成有千分位的,单位为元,表示为“#,##0.00元”。
  • 函数365之RMB函数:我不是人民币,我只是人民币的翻译员

    Excel函数2017-3-240评论455
    函数名称:RMB 函数作用:RMB函数用于将数值转换成货币格式,并将小数四舍五入到指定的位数并转换成文本。使用的格式为 (¥#,##0.00_);(¥#,##0.00)。 函数语法:RMB(数值,小数位) 注意事项: 1、第1参数为必选参数。数值或者结果为数值的单元格引用或公式。 2、第2参数为可选参数。表示小数点右边的位数。如果为负数,则表示从小数点往左按相应位数四舍五入。如果省略,则默认为2。 3、RMB的结果为文本。 函数应用: 实例1、根据下表金额和数量,计算单价,结果如C列所示。 函数公式:=RMB(A2/B2,1) ,公式下拉即可 解释说明:根据上图发现单价的前面有人民币符号“¥”,数值保留1位小数,所以用RMB函数,第2参数为1。 实例2、根据A列金额将其转换成金额大写形式,如B列所示。 函数公式:=SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零分","整"),"零元整"),"零角",IF(A2^2<1,,"零")) 解释说明: 1、既然涉及金额那么小数位最多只有两位小数,所用用RMB函数第2参数为2或省略; 2、单元格主要有四种格式类型,一般用分号“;”分开,类型为“正数;负数;0;文本”; 3、本题主要将整数部分和小数部分分别转换中文大写,TEXT函数用“[dbnum2]G/通用格式元”格式可以将数字转中文大写,这里用来转换整数部分,用“"[dbnum2]0角0分;;整"”格式转换小数部分。
  • 函数365之WORKDAY函数:以后的以后,某年某月的某一天

    Excel函数2017-3-230评论595
    函数名称:WORKDAY 函数作用:WORKDAY函数用于返回起始日期之前或之后相隔指定工作日的某一日期的序列号。 函数语法:WORKDAY(开始日期值,天数,排除的日期) 注意事项: 1、第1参数为必选参数。表示一个开始日期或日期的值; 2、第2参数为必选参数。表示之前或之后不含周末及节假日的天数。正数生成未来日期;负数生成过去日期。 3、第3参数为可选参数。表示需要排除的除了周六周日及节假日之外的其他日期。 4、如果任何参数为非法日期值,则函数 WORKDAY 将返回错误值 #VALUE! 。 5、如果WORKDAY函数的结果为非法日期值,则返回错误值 #NUM! 。 6、如果第2参数不是整数,将截尾取整,WORKDAY函数的结果为日期序列号,非标准日期。 函数应用: 实例1、有一项工程2017-2-1开工,预计需要270个工作日才能完工,请计算完工日期是哪一天。 函数公式:=TEXT(WORKDAY("2017-2-1",270),"yyyy-m-d"),结果为2017-2-14 解释说明:根据开始日期和需要天数计算某个日期值用WORKDAY函数,因为结果为非标准日期所以需要用TEXT函数将日期变成标准格式。 实例2、某工程需要365个工作日,计划于2017-3-23日开工,除了每个周末休息两天外,每个月末的最后一天也休息,计算工程完工的日期是哪一天。 函数公式:=TEXT(WORKDAY("2017-3-23",365,EOMONTH("2017-3-23",ROW(1:99)-1)),"yyyy-m-d"),数组公式CTRL+SHIFT+回车键三键结束。 解释说明:函数本身计算的时候是不包含周末的天数的,所以不需要考虑,我们要考虑的是怎么排除每个月末的最后一天,可以用EOMONTH函数取得每个月的最后一天的天数,这里用ROW(1:99)-1取得0-98的数值,分别表示本月至第99个月的最后一天的日期。 实例3、某培训课程2017-3-23日结束,总共耗时180个工作日,计算课程开始日期是哪一天。 函数公式:=TEXT(WORKDAY("2017-3-23",-180),"yyyy-m-d"),结果为2016-7-14 解释说明:要根据一个日期计算指定天数之前的某个日期,用WORKDAY函数,其中第2参数为负数,表示之前的日期值,所以这里是“-180”。
  • 函数365之COUNTIF函数:仅仅只是计数,不要把我想得太复杂

    Excel函数2017-3-220评论515
    函数名称:COUNTIF 函数作用:COUNTIF函数主要用于对区域中满足单个指定条件的单元格进行计数。 函数语法:COUNTIF(统计区域,统计条件) 注意事项: 1、第1参数为必选参数。要对其进行计数的一个或多个单元格,其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。 2、第2参数为比选参数。用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。 3、在条件中可以使用通配符,即问号 (?) 和星号 (*)。问号匹配任意单个字符,星号匹配任意一系列字符。若要查找实际的问号或星号,请在该字符前键入波形符 (~)。 4、COUNTIF进行计算时字母不区分大小写。 函数应用: 实例1、根据下表计算分数大于60分的人员个数。 函数公式:=COUNTIF(B2:B10,">60") 解释说明:题目要求计算分数大于60分的个数,也就是根据条件计算个数,用COUNTIF函数,条件为“>60”。 实例2、在A2:A10单元格区域中设置数据有效性,当输入重复数据时禁止输入并提示“此处禁止录入重复数据,请核对后重新输入。”。 操作步骤: 1、选定区域A2:A10,选择“数据”选项卡,然后选择“数据有效性-设置-(允许)自定义”; 2、输入公式=COUNTIF($A$2:$A$10,A2)=1,此处需要注意第2参数必须为相对引用,如下图; 3、在出错警告处的错误信息输入“此处禁止录入重复数据,请核对后重新输入。”,然后保存。 实例3、根据下表按要求计算:(1)计算姓名中包含“明”字的姓名个数;(2)计算不重复姓名个数。 函数公式1:=COUNTIF(A2:A10,"*明*") 解释说明:要计算包含某字符的个数,就要用到COUNTIF函数和通配符“*”,包含“明”字可以表示为“*明*”,其中“*”表示任意单个或多个字符。 函数公式2:=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)) 解释说明:用COUNTIF函数分别计算A2:A10单元格区域里面的每一个单元格的姓名在A2:A10单元格区域中的个数,然后用1除以COUNTIF的每一个结果然后再相加,原理如下图。