逻辑值和文本:false、true、字符串、文字文本 ————————————日期与时间-函数———————————— now():返回日期格式的当前日期和时间。 today():返回日期格式的当前日期。 date:返回Excel日期时间代码中代表日期的数字。 days:返回两日期之间的天数。 day/month/year:返回日期的天数/月份/年份。 weekday:返回代表一周中的第几天的数值,是一个1到7之间的整数。 time:返回特定时间的序列数。 hour/minuth/second:返回小时/分钟/秒钟数值,是一个0-23/0-59/0-59之间的整数。 *.excel函数-日期与时间_now(): 用途:返回日期格式的当前日期和时间。 格式: now() 解析:返回当前日期和时间 *.excel函数-日期与时间_today(): 用途:返回日期格式的当前日期。 格式: today() 解析:返回日期格式的当前日期 *.excel函数-日期与时间_date: 用途:返回Excel日期时间代码中代表日期的数字。 格式: date(Year,month,day) year:介于1900或1904到9999之间的数字; month:代表一年中月份的数字,其值在1到12之间; day:代表一个月中第几天的数字,其值在1到31之间; 范例:=date(year(now()),1,1) 解析:返回今年1月1日的日期 *.excel函数-日期与时间_days: 用途:返回两日期之间的天数。 格式: days(end_date,start_date) end_date:结束的日期; start_date:开始的日期; 范例:=days(date(2018,1,1),date(2017,1,1)) 计算结果:365 解析:计算日期(2017/1/1)到日期(2018/1/1)之间的天数 *.excel函数-日期与时间_day/month/year: 用途:返回日期的天数/月份/年份 格式:DAY/Month/Year(serial_number) serial_number:进行日期及时间计算的日期-时间代码 范例:=month(today()) 解析:计算今天所在月的月份 *.excel函数-日期与时间_weekday: 用途:返回代表一周中的第几天的数值,是一个1到7之间的整数。 格式: weekday(serial_number,Return_type) serial_number:进行日期及时间计算的日期-时间代码; Return_type:从 星期日=1 到 星期六=7,用1;从 星期一=1 到 星期日=7,用2;从 星期一=0 到 星期日=6 时,用3; 范例:=weekday(date(2018,3,1),2) 解析:计算今年3月1日的星期 *.excel函数-日期与时间_time: 用途:返回特定时间的序列数。 格式: time(hour,minute,second) hour:小时数,介于0到23之间的数字; minute:分钟数,介于0到59之间的数字; second:秒钟数,介于0到59之间的数字; 范例:=time(b1,b2,b3) 解析:返回时间(b1)时(b2)分(b3)秒的时间格式 *.excel函数-日期与时间_hour/minuth/second: 用途:返回小时/分钟/秒钟数值,是一个0-23/0-59/0-59之间的整数。 格式:hour/minuth/second(serial_number) serial_number:进行日期及时间计算的日期-时间代码; 范例:=hour(now()) 计算结果:23 解析:返回当前时间的小时数。 ————————————数学-函数———————————— 强调: rounddown与trunc区别:位数为0时,rounddown不可省略,trunc可以省略。 sum:计算单元格区域中所有数值的和。 Sumif:对满足条件的单元格求和。 Sumifs:对一组给定条件指定的单元格求和。 sumproduct:计算相应的数组或区域乘积的和。 int:将数值向下取整为最接近的整数。 mod:返回两数相除的余数。 round/rounddown/roundup:按指定的位数对数值进行四舍五入/向下舍入/向上舍入。 trunc:将数字截为整数或保留指定位数的小数。 quotient:返回除法的整数部分。 rand():返回一个大于或等于0且小于1的平均分布随机数。 randbetween:返回一个介于指定的数字之间的随机数。 pi():返回圆周率PI的值,3.14159265358979,精确到15位。 even:将正(负)数向上(下)舍入到最近的偶数。 odd:将正(负)数向上(下)舍入到最近的奇数。 sign:返回数值的正负号,为正时,返回1;为0时,返回0;为负时,返回-1。 abs:返回给定数值的绝对值,即不带符号的数值。 *.excel函数-数学_sum: 用途:计算单元格区域中所有数值的和。 格式:sum(number1,number2,...) number:1-255个待求和的数值。单元格中的逻辑值和文本将被忽略;但当做为参数键入时,逻辑值和文本有效;范例:=sum(a1:a3) 计算结果:66 解析:计算单元格(a1)(a2)(a3)的和。 *。excel函数-数学_Sumif: 用途:对满足条件的单元格求和。 格式:Sumif(Range,Criteria,[Sum_range]) Range:要进行计算的单元格区域。 Criteria:以数字、表达式或文本形式定义的条件。 Sum_range:用于求和计算的实际单元格。如果省略,将使用区域中的单元格。 范例:=SUMIF(A2:C5,"*上海*",B2:B5) =SUMIF(A2:C5,">30",B2:B5) 计算结果:76 / 66 解析:计算上海所有地方的发货量总量。 计算发货量大于30的发货量总量。 *。excel函数-数学_Sumifs: 用途:对一组给定条件指定的单元格求和。 格式:Sumifs(Sum_range,Criteria_range1,Criteria1,Criteria_range2,Criteria2,...) Sum_range:是求和的实际单元格。 Criteria_range1:是要为特定条件计算的单元格区域。 Criteria1:是数字、表达式或文本形式的条件,它定义了单元格求和的范围。 范例1:=sumifs(b2:b6,a2:a6,"*上海*",b2:b6,">50") 计算结果:119 解析:计算上海所有地方且发货量大于50的总发货量。 *.excel函数-数学_sumproduct: 用途:计算相应的数组或区域乘积的和。 格式:sumproduct(array1,array2,...) array:2-255个待求的数组。所有数组的维数必须一样;范例1:=sumproduct(a2:b3,d2:e3) 计算结果:40 解析:所有数组相同维数相乘,然后所有乘积再相加。即a2*d2+a3*d3+b2*e2+b3*e3=1*2+3*4+2*3+4*5; 范例2:求奇数行或偶数行之单元格数值之和: 1.公式: 奇数行求和:=SUMPRODUCT(MOD(ROW(1:10),2)*A1:A10) 偶数行求和:=SUMPRODUCT(MOD(ROW(1:10)-1,2)*A1:A10) 2.函数row(a):见“列不变,行下拉递增” 3.函数sumproduct(数组1,[数组2]...):多个数组同行列的乘积之和 1).为条件数组时,以真值=1,假值=0参与运算, 2).无条件单个数组:数组值总和 3).有条件单个数组:指定条件的条件数组与单个数组乘积之和 例:a1="a",a2="b",a3="c",b1=2,b2=3,b3=4 sumproduct((a1:a3="b")*b1:b3)运算过程: a1:a3为条件数组:a1不等于"a"->a1=0;a2等于"b"->a2=1;a3不等于"b"->a3=0; 完整运算过程:=a1*b1+a2*b2+a3*b3=0*2+1*3+0*4=3。 *.excel函数-数学_int: 用途:将数值向下取整为最接近的整数。 格式: int(number) number:要对其求绝对值的实数; 范例:=int(a1) =int(a2) 计算结果:2 / 2 解析:将数值(a1)向下取整。 将数值(a2)向下取整。 *.excel函数-数学_mod: 用途:返回两数相除的余数。 格式: mod(number,divisor) number:除数; divisor:被除数; 范例:=mod(a1,a2) 计算结果:2 解析:计算数值(a1)除于数值(a2)的余数。 *.excel函数-数学_round/rounddown/roundup: 用途:按指定的位数对数值进行(四舍五入/向下舍入/向上舍入)。 格式:round/rounddown/roundup(number,num_digits) number:要(四舍五入/向下舍入/向上舍入)的任意实数; num_digits:执行(四舍五入/向下舍入/向上舍入)时采用的位数,如果为负数,则将小数(四舍五入/向下舍入/向上舍入)到左边的位数;如果为0,则将小数部分(四舍五入/向下舍入/向上舍入)到整数部分; 范例:=round(a1,-1) / =roundup(a1,-1) / rounddown(a1,-1) =round(a2,0) / =roundup(a2,0) / =rounddown(a2,0) 计算结果:30 / 30 / 20 24 / 24 / 23 解析:计算数值(a1)取小数点左边1位的(四舍五入/向下舍入/向上舍入)值。 计算数值(a2)取整数的(四舍五入/向下舍入/向上舍入)值。 *.excel函数-数学_trunc: 用途:将数字截为整数或保留指定位数的小数。 格式:trunc(number,num_digits) number:要进行截尾操作的实数; num_digits:执行截尾的位数,如果为负数,则将截取小数点左边的位数;如果忽略,则为0; 范例:=trunc(a1,-1) =trunc(a1) 计算结果:20 / 27 解析:截取数值(a1)小数点左边1位。 截取数值(a1)整数值。 *.excel函数-数学_quotient: 用途:返回除法的整数部分。 格式: quotient(numerator,denominator) numerator:除数; denominator:被除数; 范例:=quotient(a1,a2) 计算结果:2 解析:计算数值(a1)除于数值(a2)的整数部分。 *.excel函数-数学_rand(): 用途:返回一个大于或等于0且小于1的平均分布随机数。 格式: rand() 范例:=rand() 计算结果:0.05939238 解析:返回一个大于或等于0且小于1的随机数。 *.excel函数-数学_randbetween: 用途:返回一个介于指定的数字之间的随机数。 格式: randbetween(botton,top) botton:能返回的最小整数; top:能返回的最大整数; 范例:=randbetween(4,9) 计算结果:5 解析:返回介于4到9的一个随机整数。 *.excel函数-数学_pi(): 用途:返回圆周率PI的值,3.14159265358979,精确到15位。 格式: pi() 范例:=pi() 计算结果:3.14159265358979 解析:返回圆周率PI的值。 *.excel函数-数学_even: 用途:将正(负)数向上(下)舍入到最近的偶数。 格式: even(number) number:需要取偶的数值; 范例:=even(a1) =even(a2) 计算结果:4 / -4 解析:计算数值(a1)的取偶值。 计算数值(a2)的取偶值。 *.excel函数-数学_odd: 用途:将正(负)数向上(下)舍入到最近的奇数。 格式: odd(number) number:需要取奇的数值; 范例:=odd(a1) =odd(a2) 计算结果:3 / -3 解析:计算数值(a1)的取奇值。 计算数值(a2)的取奇值。 *.excel函数-数学_sign: 用途:返回数值的正负号,为正时,返回1;为0时,返回0;为负时,返回-1。 格式: sign(number) number:任意实数; 范例:=sign(a1) =sign(a2) 计算结果:-1 / 1 解析:返回实数数值(a1)的符号值。 返回实数数值(a2)的符号值。 *.excel函数-数学_abs: 用途:返回给定数值的绝对值,即不带符号的数值。 格式: abs(number) number:要对其求绝对值的实数; 范例:=abs(a1) =abs(a2) 计算结果:-1 / 1 解析:返回实数数值(a1)的绝对值。 返回实数数值(a2)的绝对值。 ————————————统计-函数———————————— large/small:返回数据组中第K个(最大/最小)值。 max/maxa: min/mina: median count/counta:计算区域中(包含数字/非空)的单元格的个数。 countif/countifs:计算某个区域中满足给定条件(单条件/可多个条件)的单元格数目。 average/averagea:返回其参数的算术平均值,参数可以是数值或包含数值的名称、数组或引用,参数不能为或单引逻辑值和文本, average:引用时忽略逻辑值和文本。 averagea:引用时逻辑值和文本计算个数,false相当于0,true相当于1。 averageif:计算给定条件(单条件)指定的单元格的平均值(算术平均值),单元格区域忽略逻辑值和文本。 averageifs:计算一组给定条件(可多个条件)指定的单元格的平均值(算术平均值),单元格区域忽略逻辑值和文本。 *。excel函数-统计_large/small: 用途:返回数据组中第K个(最大/最小)值。 格式:large/small(array,k) array:用来计算第k个(最大/最小)值点的数值数组或数值区域。 k:所要返回的(最大/最小)值点在数组或数据区中的位置(从(最大/最小)值开始)。 范例: =large({2,3,4,5},2) / =small({2,3,4,5},2) 计算结果:4 / 3 解析:计算数组中第二个(最大/最小)值。 *。excel函数-统计_max/maxa: 用途:返回一组数值中的最大值,(忽略/不忽略)逻辑值及文本。 格式:max(number1,number2,...) maxa(value1,value2,...) number/value:用来求最大值的1到255个参数,可以是数值、空单元格或文本型数值。 范例: 解析: *。excel函数-统计_min/mina: 用途:返回一组数值中的最小值,(忽略/不忽略)逻辑值及文本。 格式:min(number1,number2,...) mina(value1,value2,...) number/value:用来求最小值的1到255个参数,可以是数值、空单元格或文本型数值。 范例: 解析: *。excel函数-统计_median: 用途:返回一组数值中的中值,引用时忽略逻辑值及文本。 格式:median(number1,number2,...) number:用来求中值的1到255个数字、名称、数组或数值引用。 范例: 解析: *。excel函数-统计_count/counta: 用途:计算区域中(包含数字/非空)的单元格的个数。 格式:count/counta(Value1,Value2,...) Value1:是1到255个参数,代表要进行计数的值和单元格,可以包含或引用各种不同类型的数据,值可以是任意类型的信息,(只对数字型数据/对所有)进行计数。 范例: =count(A1:A5,B2:B5) / =counta(A1:A5,B2:B5) 解析:计算单元格区域(A1:A5)和(B2:B5)中(包含数字/非空)的单元格的个数 *。excel函数-统计_countif/countifs: 用途:计算某个区域中满足给定条件(单条件/可多个条件)的单元格数目。 格式:countif(Range,Criteria) countifs(Range1,Criteria11,range2,criterial2,...) Range:要计算其中非空单元格数目的区域。 Criteria:以数字、表达式或文本形式定义的条件,它定义了单元格统计的范围。 范例: 解析: *。excel函数-统计_average/averagea: 用途:返回其参数的算术平均值,参数可以是数值或包含数值的名称、数组或引用,参数不能为或单引字符串、文本、false和true, average:引用时忽略字符串、文本、false和true。 averagea:引用时字符串、文本、false和true计算个数,false相当于0,true相当于1。 格式:average(number1,number2,...) number:是用于计算平均值的1到255个(数值参数/参数)。 范例: 解析:计算a1:a4的平均值。 *。excel函数-统计_averageif: 用途:计算给定条件指定的单元格的平均值(算术平均值),单元格区域忽略字符串、文本、false和true。 格式:averageif(range,criteria,average_range) range:是要进行计算的单元格区域。 criteria:是数字、表达式或文本形式的条件,它定义了用于查找平均值的单元格范围; average_range:是用于计算平均值的实际单元格,如果省略,则使用区域中的单元格区域; 范例: 解析: *。excel函数-统计_averageifs: 用途:计算一组给定条件指定的单元格的平均值(算术平均值),单元格区域忽略字符串、文本、false和true。 格式:averageifs(average_range,criteria_range1,criterial1,criteria_range2,criterial2,...) average_range:是用于计算平均值的实际单元格。 criteria_range:是要为特定条件计算的单元格区域。 criterial:是数字、表达式或文本形式的条件,它定义了用于查找平均值的单元格范围; 范例: 解析:计算单元格区域b2:b5中符合条件的平均值,条件1:b2:b5单元格区域中数值大于或等于3500,条件2:a2:a5单元格区域中含有“丰"字所对应的b2:b5单元格。 ————————————查找与引用-函数———————————— lookup:从单行或单列或从数组中查找一个值,条件是向后兼容性。 Vlookup :搜索数组区域首列满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。默认情况下,表是以升序排序的。Hlookup Index Match : Offset : 以指定的引用为参照第,通过给偏移量返回新的引用。 choose :根据给定的索引值,从参数串中选出相应值或操作。Row/Column : Rows/Columns : formulatext :将单元格或单元格区域左上角单元格内的公式以文本形式显示。hyperlink:创建一个快捷方式或链接,以便打开一个存储在硬盘、网络服务器或internet上的文档。 transpose:转置单元格区域。 *。excel函数-查找与引用_lookup: 用途:从单行或单列或从数组中查找一个值,条件是向后兼容性。 格式: lookup(Lookup_value,lookup_vector, result_vector Lookup_value:要在 lookup_vector中查找的值,可以是数值、文本、逻辑值、数值的名称或引用 lookup_vector result_vector lookup_vector大小相同 范例: 解析: *。excel函数-查找与引用_Vlookup: 用途:搜索数组区域首列满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。默认情况下,表是以升序排序的。 格式: Vlookup(Lookup_value,Table_array,Col_index_num,Range_lookup) Lookup_value:需要在数据表首列进行搜索的值,Lookup_value可以是数值、引用或字符串。 Table_array:需要在其中搜索数据的信息表。Table_array可以是对区域或区域名称的引用。 Col_index_num:满足条件的单元格在数组区域Table_array中的列序号,首列序号为1。 Range_lookup:指定在查找时是要求精确匹配,还是大致匹配。如果为FALSE:大致匹配。如果为TRUE或忽略:精确匹配。 范例: =VLOOKUP("访客数",c2:f17,3,0) 解析:在单元格区域(A2:C17)第(3)列(相对引用:单元格区域A2:C17的第3列,即e列)精确匹配(0)查找字符串("访客数")所在行的单元格的值。 *。excel函数-查找与引用_Hlookup: 用途:搜索数组区域首行满足条件的元素,确定待检索单元格在区域中的列序号,再进一步返回选定单元格的值。 格式:Hlookup(Lookup_value,Table_array,Row_index_num,Range_lookup) Lookup_value:需要在数据表首列进行搜索的值,Lookup_value可以是数值、引用或字符串。 Table_array:需要在其中搜索数据的信息表。Table_array可以是对区域或区域名称的引用。 Row_index_num:满足条件的单元格在数组区域Table_array中的行序号,表中第一行序号为1。 Range_lookup:指定在查找时是要求精确匹配,还是大致匹配。如果为FALSE:大致匹配。如果为TRUE或忽略:精确匹配。 范例:=HLOOKUP("访客数",c2:f17,3,0) 解析:在单元格区域(A2:C17)第(3)行(相对引用:单元格区域A2:C17的第3行,即行3)精确匹配(0)查找字符串("访客数")所在列的单元格的值。 *。excel函数-查找与引用_Index: 用途:返回指定位置中的内容。 格式: INDEX(array,row-num,column-num) array:要返回值的单元格区域或数组。 row-num:返回值所在的行号。 column-num:返回值所在的列号。 范例:=INDEX(A2:B13,2,3) 解析: 在单元格区域(A2:B13)第2行第3列所在的值。 注意:行列是相对于单元格区域而言,而非整个单元格 *。excel函数-查找与引用_Match: 用途:返回指定内容所在的位置 格式:MATCH(lookup-value,lookup-array,match-type) lookup-value:表示要在区域或数组中查找的值,可以是直接输入的数组或单元格引用。 lookup-array:表示可能包含所要查找的数值的连续单元格区域,应为数组或数组引用。 match-type:表示查找方式,用于指定精确查找(查找区域无序排列)或模糊查找(查找区域升序排列)。取值为-1、1、0 。其中0为精确查找。 范例:=MATCH("二月",A2:A13,0) 解析:精确查找(0)“二月”在单元格区域(A2:A13)所在的位置(顺序序号) *。excel函数-查找与引用_Offset: 用途:以指定的引用为参照第,通过给偏移量返回新的引用 格式:Offset(Reference,Rows,Cols,[Height],[Width]) Reference:作为参照系的引用区域,其左上角单元格是偏移量的起始位置。 Rows:相对于引用参照系的左上角单元格,上(下)偏移的行数。 Cols:相对于引用参照系的左上角单元格,左(右)偏移的行数。 Height:新引用区域的行数。 Width:新引用区域的列数。 注意:Height为负数:向上,Width为负数:向左 范例:=OFFSET(A5,2,4) =sum(OFFSET(A5,2,4,1,3)) 解析:引用以单元格(A5)为起始位置,向下移(2)行,向右移(4)列的值 计算以单元格(A5)为起始位置,向下移(2)行,向右移(4)列的向下(1)行,向左(3)列的单元格区域的总和 *。excel函数-查找与引用_choose: 用途:根据给定的索引值,从参数串中选出相应值或操作。 格式:choose(index_num,value1,value2,...) index_num:索引值即指出所选参数值在参数表中的位置,必须是介于1到254之间的数值,或是返回值介于1到254之间的引用或公式。 value:是1到254个数值参数、单元格引用、已定义名称、公式、函数,或是choose从中选定的文本参数。 注意:如果index_num是一个数组,则在计算函数choose时,将计算每一个值。 常常不是单独出现的,而是与其它函数嵌套使用。 范例1: 解析: 范例2:choose与vlookup查找函数嵌套使用实现更多功能。 *。excel函数-查找与引用_Row/Column: 用途:返回一个引用的(行号/列号)。 格式:Row/Column(Reference) Reference:准备求取其(行号/列号)的单元格或连续的单元格区域;如果忽略,则使用包含Row/Column函数的单元格。 注意:Reference若为单元格区域,则返回左上角单元格的(行号/列号) 范例1:=Row(b6:f8) 计算结果:6 解析:返回单元格区域(b6:f8)左上角单元格(b6)的行号 范例2:列不变,行下拉递增 需要依次取得a2、a5、a8、a11。。。的值: 1.左上角单元格:a1 2.第一个数公式:OFFSET(shujuku!$a$1:$a$300,row(a1)*3-2,0,1,1) 以工作表shujuku的绝对范围(a1到a300)为参照, 左上角单元格向下偏移1(b1值)行, 列不偏移 新引用行列数:1行1列 2.函数row(a):求所在单元格的行序号 1).a省略:返回公式所在单元格的行序号 2).a为某一单元格:返回指定单元格的行序号 3).a为一区域(行序号数组): 存储形式(一维数组:只存储行序号):如 row(b2:c3)={2;3}(错误:{2,2;3,3})。 单独使用时,返回区域最小行序号, 参与数组运算时:整个一维行序号数组参与运算,不能参与多维数组 3.函数offset:以指定的引用范围为参照,通过偏移取得新的引用 格式:offset(a,b,c,d,e) 1).a:指定单元格范围 2).b:相对于a范围左上角单元格,向上/下偏移行数。 3).c:相对于a范围左上角单元格,向左/右偏移列数。 4).d:新引用的行数 e:新引用的列数 *。excel函数-查找与引用_rows/columns: 用途:返回某一引用或数组的(行数/列数)。 格式:columns(array) array:要计算(行数/列数)的数组、数组公式或对单元格区域的引用。 范例: 解析: *。excel函数-查找与引用_formulatext: 用途:将单元格或单元格区域左上角单元格内的公式以文本形式显示。 格式:formulatext(reference) reference:是对公式的引用。 范例: 解析: *。excel函数-查找与引用_hyperlink: 用途:创建一个快捷方式或链接,以便打开一个存储在硬盘、网络服务器或internet上的文档。 格式:hyperlink(link_location,friendly_name) link_location:要打开的文件名称及完整路径,可以是本地硬盘、UNC路径或URL路径。 friendly_name:要显示在单元格中的数字或字符串,若忽略,则显示link_location的文本。 范例:=hyperlink("g:\1234.xlsx","表格") 解析:创建一个名称为“表格”的链接,链接到g:\1234.xlsx表格。 *。excel函数-查找与引用_transpose: 用途:转置单元格区域。 格式:transpose(array) array:工作表中的单元格区域或数组。 注意:需先选中与需要转置的单元格区域行列号对调的单元格区域。 如:原单元格为5行2列,则选中2行5列的单元格。 范例: 解析: 目的:需要把单元格区域a1:b5转置 方法:选中c4:g5 -》 在c4输入公式:transpose(a1:b5) -》 ctrl+shift+enter ————————————文本-函数———————————— 强调: 1.(文本字符/字节)说明: 1个字母、数字和符号是一个文本字符,一个字节, 1个汉字是一个文本字符,两个字节。 如果只取到1个汉字的1个字节,即为空,如:leftb("字节",1),等于" "。 1.通配符?和*,在字符串中的运用: ?:代表1个字符,*:代表多个字符。 len/lenb:返回文本字符串中的(文本字符/字节)个数。 left/leftb:从一个文本字符串的第一个字符开始返回指定个数的(文本字符/字节)。 right/rightb:从一个文本字符串的最后一个字符开始返回指定个数的(文本字符/字节)。 mid/midb:从文本字符串中指定的起始位置起返回指定长度的(文本字符/字节)。 lower/upper:将一个文本字符串中的所有字母转换为(小写/大写)形式。 char:根据本机中的ASCII字符集,返回由代码数字指定的字符。 code:返回文本字符串第一个字符在本机所用字符集(如:ASCII)中的数字代码。 Find/findb :返回一个字符串在另一个字符串中出现的起始位置(区分大小写),以(文本字符/字节)开始查找。concatenate:将多个文本字符串合成一个。 exact:比较两个字符串是否完全相同(区分大小写),返回true或false。 proper:将一个文本字符串中各个英文单词的第一个字母转换成大写,将其他字符转换成小写。 replace/replaceb:将一个字符串中的部分字符用另一个字符串替换,以(文本字符/字节)计算个数。 Substitute 将字符串中的部分字符串以新字符串替换 search/searchb: 返回一个指定字符或文本字符串中第一次出现的位置,从左到右查找(忽略大小写),, 以(文本字符/字节)计算位置 。 trim:删除字符串中多余的空格,但会在英文字符串中保留一个作为词与词之间分隔的空格。 rpet:根据指定次数重复文本,可用rpet在一个单元格中重复填写一个文本字符串。 value: text: clean:删除文本中的所有非打印字符。 t: *。excel函数-文本_len/lenb: 用途:返回文本字符串中的(文本字符/字节)个数。 格式: len/lenb(text) text:要计算长度的文本字符串。 范例:a1=ab九cd =len(a1) / =lenb(a1) 计算结果:5 / 6 解析: *。excel函数-文本_left/leftb: 用途:从一个文本字符串的第一个字符开始返回指定个数的(文本字符/字节)。 格式: left/leftb(text,num_chars) text:要提取字符的字符串。 num_chars:要提取的字符数量;如果忽略,为1。 范例:a1=ab九cd =left(a1,3) / =leftb(a1,3) 计算结果:ab九 / ab 解析: *。excel函数-文本_right/rightb: 用途:从一个文本字符串的最后一个字符开始返回指定个数的(文本字符/字节)。 格式: right/rightb(text,num_chars) text:要提取字符的字符串。 num_chars:要提取的字符数量;如果忽略,为1。 范例:a1=ab九cd =right(a1,3) / =rightb(a1,3) 计算结果:九cd / cd 解析: *。excel函数-文本_mid/midb: 用途:从文本字符串中指定的起始位置起返回指定长度的(文本字符/字节)。 格式: mid/midb(text,start_num,num_chars) text:准备从中提取字符串的文本字符串。 start_num :准备提取的第一个字符的位置,text中第一个字符为1。 num_chars:指定所要提取的字符串长度。 范例:a1=ab九天cd =mid(a1,3,4) / =midb(a1,3,4) 计算结果:九天cd / 九天 解析: *。excel函数-文本_lower/upper: 用途:将一个文本字符串中的所有字母转换为(小写/大写)形式。 格式: lower/upper(text) text:要对其进行转换的字符串,其中不是英文字母的字符不变。 范例:a1=abCde =lower(a1) / =upper(a1) 计算结果:abcde / ABCDE 解析: *。excel函数-文本_char: 用途:根据本机中的字符集(如:ASCII),返回由代码数字指定的字符。 格式: char(number) number:介于1到255之间的任一数字,该数字对应着要返回的字符。 范例:=char(66) 计算结果:B 解析:返回66在ASCII字符集中所对应的字符。 *。excel函数-文本_code: 用途:返回文本字符串第一个字符在本机所用字符集(如:ASCII)中的数字代码。 格式: code(text) text:要取第一个字符代码的字符串。 范例:=code("B") 计算结果:66 解析:返回字符"B"在ASCII字符集中所对应的数字。 *。excel函数-文本_Find/findb: 用途:返回一个字符串在另一个字符串中出现的起始位置(区分大小写),以(文本字符/字节)开始查找。 格式: Find/findb(Find_text,Within_text,[Start_num]) Find_text:要查找的字符串。用双引号(表示空串)可匹配Within_text中的第一个字符,不能使用通配符。 Within_text:要在其中进行搜索的字符串。 Start_num:起始搜索位置,Within_text中第一个字符的位置为1.如果忽略,Start_num=1。 范例:a1="一",a2="二一" =find(a1,a2) / =findb(a1,a2) 计算结果:2 / 3 解析:查找a1在a2的起始位置。 *。excel函数-文本_concatenate: 用途:将多个文本字符串合成一个。 格式: concatenate(text1,text2,...) text:是1到255个要合并的文本字符串,可以是字符串、数字或对某个单元格的引用。 范例: a1=ab,a2=12,a3=* =concatenate(a1,a3,a2) 计算结果:ab*12 解析:把a1,a2,a3合并成一个字符串。 *。excel函数-文本_exact: 用途:比较两个字符串是否完全相同(区分大小写),返回true或false。 格式: exact(text1,text2) text1:第一个字符串。 text2:第二个字符串。 范例: a1=abc,a2=abC =exact(a1,a2) 计算结果:false 解析:比较a1,a2的字符串是否相同。 *。excel函数-文本_proper: 用途:将一个文本字符串中各个英文单词的第一个字母转换成大写,将其他字符转换成小写。 格式: proper(text) text:所要转换的字符串数据,可以是包含在一对双引号中的字符串,能够返回字符串的公式,或是对文本单元格的引用。 范例: a1=this is =proper(a1) 计算结果:This Is 解析: *。excel函数-文本_replace/replaceb: 用途:将一个字符串中的部分字符用另一个字符串替换,以(文本字符/字节)计算个数。 格式: replace(old_text,start_num,num_chars,new_text) old_text:要进行字符替换的文本。 start_num:要替换为new_text的字符在old_text中的位置,以(文本字符/字节)计算个数。 num_chars:要从old_text中替换的(文本字符/字节)个数。 new_text:用来对old_text中指定字符串进行替换的字符串。 范例: a1= ab九天cdef =replace(a1,7,2,"gg") / =replaceb(a1,7,2,"gg") 计算结果:ab九天cdgg / ab九天ggef 解析: *。excel函数-文本_Substitute: 用途:将字符串中的部分字符串以新字符串替换 格式: Substitute(Text,Old_text,New_text,[Instance_num]) Text:包含有要替换字符的字符串或文本单元格引用 Old_text:要被替换的字符串。如果原有字符串中的大小写与新字符串中的大小写不匹配的话,将不进行替换。 New_text:用于替换Old_text的新字符串 Instance_num:若指定的字符串Old_text在父字符串中出现多次,则用本参数指定要替换第几个。如果省略,则全部替换。 范例: =Substitute("good","oo","rea")解析: 将字符串("good")中("oo")以新字符串("rea")替换 延伸1:计算单元格(字符串)不含空格的长度: =len(substitute(a1," ",)) 解析:计算单元格(字符串)(a1)不含空格的长度 延伸2:判断单元格(字符串)是否包含某字符: =if(substitute(a1,b,)<>a1,1,0) 结果:=1:包含;=0:不包含 解析:判断单元格(字符串)(a1)是否包含字符串(b) 延伸3:统计单元格内长字符串1含字符串2个数: 1.说明: 串1:单元格内的长字符串。 串2:要求数量的目标短字符串 2.原理:(串1字符个数 - 串1删除等于串2字符后的字符个数)/串2字符个数 如例:计算a1含a2个数 =(LEN(a1)-LEN(SUBSTITUTE(a1,a2,"")))/LEN(a2) 1).公式说明: 删除串1中等于串2的字符串(即用空格替代串2):SUBSTITUTE(a1,a2,"") 计算字符串长度:len(a1) 3.函数substitute:将字符串中的部分字符串以新字符串替代 *。excel函数-文本_search/searchb: 用途:返回一个指定字符或文本字符串中第一次出现的位置,从左到右查找(忽略大小写), , 以(文本字符/字节)计算位置 。 格式: search/searchb(find_text,within_text,start_num) find_text:要查找的字符串,可用通配符?和*,如果要查找?和*,可用~?和~*。 within_text start_num:数字值,用于指定从被搜索字符串左侧第几个字符开始查找,若忽略,则为1。 范例:=search("b","a九bcbd") / =searchb("b","a九bcbd") 计算结果:3 / 4 解析: *。excel函数-文本_trim: 用途:删除字符串中多余的空格,但会在英文字符串中保留一个作为词与词之间分隔的空格。 格式: trim(text) text:要删除空格的字符串。 范例:=trim("this is") 计算结果:this is 解析: *。excel函数-文本_rpet: 用途:根据指定次数重复文本,可用rpet在一个单元格中重复填写一个文本字符串。 格式: rpet(text,number_times) text:要重复的文本。 number_times:文本的重复次数(正数)。 范例:=rpet("abc",2) 计算结果:abcabc 解析: *。excel函数-文本_value: 用途:将一个代表数值的文本字符串转换成数值。 格式: value(text) text:带双引号的文本,或是一个单元格引用,该单元格中有要被转换的文本。 范例:=value("12")+3 计算结果:15 解析: *。excel函数-文本_text: 用途:根据指定的数值格式将数字转成文本。 格式: text(value,format_text) value:数值、能够返回数值的公式,或对数值单元格的引用。 format_text:文字形式的数字格式,文字形式来自于“单元格格式”对话框“数字”选项卡的“分类”框(不是“常规选择项卡)。 范例: =TEXT("2014/10/24","yyyy年m月") -》 2014年10月 =TEXT("17:30:00","h时mm分" / "h小时mm分" ) -》 17时30分 / 17小时30分 =TEXT("2014/10/25","dddd" / "ddd") -》 Sunday / Sun 计算结果: 解析: *。excel函数-文本_chean: 用途:删除文本中的所有非打印字符。 格式: chean(text) text:任何想要从中删除非打印字符的工作表信息。 范例:=chean("abc"&char(2)) 计算结果:abc 解析: *。excel函数-文本_t: 用途:检测给定值是否为文本,如果是按原样返回,如果不是返回双引号(空文本)。 格式: t(value) t:要检测的值。 范例:=t("12") / =t(12) 计算结果:12 / " " 解析: ————————————逻辑-函数———————————— if:判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值,最多可嵌套七层。 and:检查是否所有参数均为true(真),如果是,返回true,否则返回false。 or:如果任意一个参数为true(真),返回true;如果全部参数为false,返回false。 xor:返回所有参数的逻辑“异或(反值:true为false,false为true)“值,如果任意一个参数的逻辑”异或“值为true,返回true;如果全部参数的逻辑”异或“值为false,返回false。 not:对参数的逻辑值求反,参数为true时返回false,参数为false时返回true。 false()/true():返回逻辑值(false/true)。 Iferror:如果表达式是一个错误,则返回value_if_error,否则返回表达式自身的值。 ifna: 如果表达式解析为#N/A,则返回指定的值,否则返回表达式的结果。 *。excel函数-逻辑_if: 用途:判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值,最多可嵌套七层。 格式:if(logical_test,value_if_true,value_if_false) logical_test:是任何可能被计算为true或false的数值或表达式。 value_if_true:是logical_test为true时的返回值,如果忽略,则返回true。 value_if_true:是logical_test为false时的返回值,如果忽略,则返回false。 范例:a1=5 =if(a1>5,1,2) 计算结果:2 解析:a1>5为false,所以结果为2。 *。excel函数-逻辑_and: 用途:检查是否所有参数均为true(真),如果是,返回true,否则返回false。 格式:and(logical1,logical2,...) logical:是1到255个结果为true或false的检测条件,检测条件可以是逻辑值、数组或引用。范例:a1=5,a2=10 =and(a1>5,a2>9) 计算结果:false 解析:a1>5为false,a2>9为true,所以结果为false。 *。excel函数-逻辑_or: 用途:如果任意一个参数为true(真),返回true;如果全部参数为false,返回false。 格式:or(logical1,logical2,...) logical:是1到255个结果为true或false的检测条件,检测条件可以是逻辑值、数组或引用。范例:a1=5,a2=10 =or(a1>5,a2>9) 计算结果:true 解析:a1>5为false,a2>9为true,所以结果为true。 *。excel函数-逻辑_xor: 用途:返回所有参数的逻辑“异或(反值:true为false,false为true)“值,如果任意一个参数的逻辑”异或“值为true,返回true;如果全部参数的逻辑”异或“值为false,返回false。 格式:xor(logical1,logical2,...) logical:是1到255个结果为true或false的检测条件,检测条件可以是逻辑值、数组或引用。范例:a1=5,a2=10 =xor(a1>5,a2>9) / =xor(a1>4,a2>9) 计算结果:true / false 解析:a1>5为false,异或值为:true,a2>9为true,异或值为:false,所以结果为true; a1>4为true,异或值为:false,a2>9为true,异或值为:false,所以结果为false。 *。excel函数-逻辑_not: 用途:对参数的逻辑值求反,参数为true时返回false,参数为false时返回true。 格式:not(logical) logical:可以对其进行真(true)假(false)判断的任何值或表达式。范例:a1=5 =if(not(a1>5),1,2) 计算结果:2 解析:a1>5不成立(false),not(a1>5)即为true,所以结果为1。 *。excel函数-逻辑_false()/true(): 用途:返回逻辑值(false/true)。 格式:false/true() 范例:false() / true() 计算结果:false / true 解析: *。excel函数-逻辑_Iferror: 用途:如果表达式是一个错误,则返回value_if_error,否则返回表达式自身的值。 格式:Iferror(Value,Value_if_error) Value:是任意值、表达式或引用。 Value_if_error:是任意值、表达式或引用。 范例:=IFERROR(15/0,"除数不能为0") 解析:如果表达式(15/0)是错误的,则返回("除数不能为0")。 *。excel函数-逻辑_ifna: 用途:如果表达式解析为#N/A,则返回指定的值,否则返回表达式的结果。 格式:ifna(Value,Value_if_na) Value:是任何值、表达式或引用。 Value_if_na:是任何值、表达式或引用。 范例: 解析: ————————————信息-函数———————————— iseven/isodd:如果数字为(偶数/奇数)则返回true。 Phonetic:获取代表拼音信息的字符串(合并字符串)。 n:将不是数值形式的值转换为数值形式,数值则不变。日期转换成序列值,trut转换成1,其他值转换成0。 sheet:返回引用的工作表的工作表编号。 isblank:检测是否引用了空单元格(有空格不算空),返回true或false。 isnontext:检测一个值是否不是文本(空单元格不是文本),返回true或false。 istext:检测一个值是否为文本,返回true或false。 isnumber:检测一个值是否是数值,返回true或false。 islogical:检测一个值是否为逻辑值(true或false),返回true或false。 isref:检测一个值是否为引用,返回true或false。 na():返回错误值#N/A(无法计算出数值)。 iserr:检测一个值是否为#N/A以外的错误(#value!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!),返回true或false。 iserror:检测一个值是否为错误(#N/A、#value!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!),返回true或false。 isna:检测一个值是否为#N/A错误,返回true或false。 isformula:检测引用是否指向包含公式的单元格,并返回true或false。 *.excel函数-信息_iseven/isodd: 用途:如果数字为(偶数/奇数)则返回true。 格式:iseven/isodd(number) number:要检测的数字或单元格引用。 范例:=iseven(3) / =isodd(3) 计算结果:0(false) / 1(true) 解析: *.excel函数-信息_Phonetic: 用途:获取代表拼音信息的字符串(合并字符串) 格式:phonetic(Reference) Reference:单元格引用,该单元格中包含有拼音信息的字符串 注意:单元格内一定要含有字母或字符,不能只有数字,只有数字不合在内 范例:a1=ab,a2=88,a3=cd =PHONETIC(A1:a3) 计算结果:abcd 解析:将单元格区域(A1:a3)内的所有字符串合并到一起 *.excel函数-信息_n: 用途:将不是数值形式的值转换为数值形式,数值则不变。日期转换成序列值,trut转换成1,其他值转换成0。 格式:n(value) value:要进行转换的值。 范例:=n(true) / n("aa") 计算结果:1 / 0 解析: *.excel函数-信息_type: 用途:以整数形式返回参数的数据类型:数值=1;文字=2;逻辑值=4;错误值=16;数组=64。 格式:type(value) value:任何值。 范例:=type("a") 计算结果:2 解析: *.excel函数-信息_sheet: 用途:返回引用的工作表的工作表编号。 格式:sheet(value) value:是需要工作表编号的工作表或引用的名称,如果省略,则返回包含函数的工作表的编号。 范例:=sheet("sheet2") 计算结果:2 解析: *.excel函数-信息_isblank: 用途:检测是否引用了空单元格(有空格不算空),返回true或false。 格式:isblank(value) value:要检查的单元格或单元格名称。 范例:a1:空,a2=88 =isblank(a1) / isblank(a2) 计算结果:true / false 解析: *.excel函数-信息_isnontext: 用途:检测一个值是否不是文本(空单元格不是文本),返回true或false。 格式:isnontext(value) value:要检测的值,可以是单元格、公式;或是单元格、公式或数值的引用。 范例:=isnontext(12) / isnontext("aa") 计算结果:false / true 解析: *.excel函数-信息_istext: 用途:检测一个值是否为文本,返回true或false。 格式:istext(value) value:要检测的值,可以是单元格、公式;或是单元格、公式或数值的引用。 范例:=istext(12) / istext("aa") 计算结果:false / true 解析: *.excel函数-信息_isnumber: 用途:检测一个值是否是数值,返回true或false。 格式:isnumber(value) value:要检测的值,可以是一个单元格、公式;或是一个单元格、公式或数值的引用。 范例:=isnumber(12) / isnumber("aa") 计算结果:true / false 解析: *.excel函数-信息_islogical: 用途:检测一个值是否为逻辑值(true或false),返回true或false。 格式:islogical(value) value:检测值,可以是一个单元格,公式,或是一个单元格、公式、或数值的名称。 范例:=isformula(1) / =isformula(false) 计算结果:false / true 解析: *.excel函数-信息_isref: 用途:检测一个值是否为引用,返回true或false。 格式:isref(value) value:检测值,可以是一个单元格,公式,或是一个单元格、公式、或数值的名称。 范例:=isref(a1) / =isformula(1) 计算结果:true / false 解析: *.excel函数-信息_na(): 用途:返回错误值#N/A(无法计算出数值)。 格式:na() 范例:=na() 计算结果:#N/A 解析: *.excel函数-信息_iserr: 用途:检测一个值是否为#N/A以外的错误(#value!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!),返回true或false。 格式:iserr(value) value:要检测的值,可以是一个单元格、公式,也可以是引用单元格、公式或值的名称。 范例:=iserr(1/0) 计算结果:true 解析: *.excel函数-信息_iserror: 用途:检测一个值是否为错误(#N/A、#value!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!),返回true或false。 格式:iserror(value) value:要检测的值,可以是一个单元格、公式,也可以是引用单元格、公式或值的名称。 范例:=iserror(1/0) 计算结果:true 解析: *.excel函数-信息_isna: 用途:检测一个值是否为#N/A错误,返回true或false。 格式:isna(value) value:要检测的值,可以是一个单元格、公式,也可以是引用单元格、公式或值的名称。 范例: 计算结果: 解析: *.excel函数-信息_isformula: 用途:检测引用是否指向包含公式的单元格,并返回true或false。 格式:isformula(reference) reference:是对要测试的单元格的引用,引用可以是单元格引用、公式或引用单元格的名称。 范例:a1=11,a2=a1+1 =isformula(a1) / =isformula(a2) 计算结果:false / true 解析: ————————————其它-函数———————————— {}:数组函数。 *.excel函数-其它_{}: 用途:数组函数 格式:在输入公式后同时按下“ctrl+shift+enter”实现,{}不能手动输入 例1: 如图:计算a1中是否包含b1:b13的任意一个,比如下图就是不包含,如果把“中年”改成“学生”,因a1中也含有“学生”,所以就是包含了 方法: 输入:=IF(COUNT(FIND($B$1:$B$13,A1)),"包含","不包含") ——> 键盘同时按下:ctrl+shift+enter 延伸: 可以是多行多列 输入:=IF(COUNT(FIND($B$1:$d$13,A1)),"包含","不包含") ——> 键盘同时按下:ctrl+shift+enter |
|