当前位置:首页 >> >>

网优文档131:【第二讲】网优表哥表姐们常用的Excel技巧(常用函数篇)

【第二讲】网优「表哥」 「表姐」常用的 Excel 技巧(常用函数篇)

1、数学函数
1) Sum 函数 主要功能:计算所有参数数值的和。 使用格式:SUM(Number1,Number2……) 参数说明:Number1、Number2……代表需要计算的值,可以是具体的数值、引用的单 元格(区域) 、逻辑值等。 应用举例:如下图所示,在 A10 单元格中输入公式:=SUM(A1:A9),确认后即可求出 以上单元格的和。

2)

Subtotal 函数 主要功能:返回列表或数据库中的分类汇总。 使用格式:SUBTOTAL(function_num, ref1, ref2, ...) 参数说明:Function_num 为 1 到 11(包含隐藏值)或 101 到 111(忽略隐藏值)之间的

数字, 用来指定使用什么函数在列表中进行分类汇总计算; ref1, ref2,……代表要进行分类汇 总区域或引用,不超过 29 个。

应用举例:一般情况下我们利用这个函数对筛选的单元格进行求和,比如我们在 B11 单元格中输入“=SUBTOTAL(9,A2:A10)” ,就会求出当前筛选结果值的和。如下图所示:

特别提醒:如果采取自动筛选,无论 function_num 参数选用什么类型,SUBTOTAL 函 数忽略任何不包括在筛选结果中的行;SUBTOTAL 函数适用于数据列或垂直区域,不适用 于数据行或水平区域。 3) Sumif 函数 主要功能:计算符合指定条件的单元格区域内的数值和。 使用格式:SUMIF(Range,Criteria,Sum_Range) 参数说明:Range 代表条件判断的单元格区域;Criteria 为指定条件表达式;Sum_Range 代表需要计算的数值所在的单元格区域。 应用举例: 比如我们从某个 BSC 所有小区的话务量的数据中,提取某小区载频级一周 的话务量。如下图所示:在 F3 单元格中输入“=SUMIF(C:C,F2,D:D)”即可,意思是:如果 在 C 列中有值等于 F2 的,就对 D 列中的值进行求和。

4)

Average 函数 主要功能:求出所有参数的算术平均值。 使用格式:AVERAGE(number1,number2,……) 参数说明:number1,number2,……:需要求平均值的数值或引用单元格(区域) ,参数

不超过 30 个。 应用举例:同 SUM 函数用法相同,不再举例。 特别提醒:如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白 或字符单元格,则不计算在内。 5) Max 函数 主要功能:求出一组数中的最大值。 使用格式:MAX(number1,number2……) 参数说明:number1,number2……代表需要求最大值的数值或引用单元格(区域) ,参数 不超过 30 个。 应用举例:同 SUM 函数用法相同,不再举例。 特别提醒:如果参数中有文本或逻辑值,则忽略。 6) Min 函数 主要功能:求出一组数中的最小值。 使用格式:MIN(number1,number2……) 参数说明:number1,number2……代表需要求最小值的数值或引用单元格(区域) ,参数 不超过 30 个。 应用举例:同 SUM 函数用法相同,不再举例。 特别提醒:如果参数中有文本或逻辑值,则忽略。 7) ABS 函数 主要功能:求出相应数字的绝对值。

使用格式:ABS(number) 参数说明:number 代表需要求绝对值的数值或引用的单元格。 应用举例:如果在 B2 单元格中输入公式:=ABS(A2),则在 A2 单元格中无论输入正数 (如 100)还是负数(如-100) ,B2 中均显示出正数(如 100) 。 特别提醒:如果 number 参数不是数值,而是一些字符(如 A 等) ,则 B2 中返回错误值 “#VALUE!” 8) Hex2dec 函数 主要功能:将十六进制数转换为十进制数。 使用格式:HEX2DEC(number) 参数说明:number 代表需转化的数值或引用的单元格。 应用举例:一般情况下,BSC32 中 CGI 为十六进制的,这样我们就利用此函数进行转 换,比如在 B2 单元格中输入公式:= HEX2DEC (783),确认后就会输出 1923。 9) Dec2hex 函数 将十进制数转换为十六进制数,用法同 Hex2dec 函数 10) OCT2DEC 函数 将八进制数转换为十进制数,用法同 Hex2dec 函数 11) OCT2HEX 函数 将八进制数转换为十六进制数,用法同 Hex2dec 函数 12) Counta 函数 计算区域中非空单元格个数 13) CONCATENATE 函数 将两个或多个文本字符串合并为一个文本字符串。 可使用连接符号 (&) 计算运算符代替 CONCATENATE 函数来连接文本项。例如 =A1&B1 与 =CONCATENATE(A1,B1) 返回相同的值。 14) TEXT 函数 将数值格式转换为文本格式。 在 C2 单元格中输入公式=TEXT(B2,〃0〃) ,即可把 B2 单元格数字转换为文本型 数字。由于在单元格中输入超过 15 位的数字时,超过的位数自动以 0 替换,所以上述 公式只能转换位数不超过 15 位的数字,而无法正确还原 18 位的身份证号码。

2、文本函数
1) LEft 函数 主要功能:从一个文本字符串的第一个字符开始,截取指定数目的字符。 使用格式:LEFT(text,num_chars) 参数说明:text 代表要截字符的字符串;num_chars 代表给定的截取数目。 应用举例:在制作工参时,我们会提取小区名左边的几个字作为地区名,例如:在 B2 中输入“=LEFT(A2,2)”就会分离出地区名。如下所示:

2)

LEN 函数

主要功能:文本字符串中的字符数 使用格式:LEN(text) 参数说明:Text 是要查找其长度的文本。空格将作为字符进行计数。 Left 与 len 配合使用 =left(A1,len(A1)-3)解释:

在 A 列单元格有一些数据, 在 A1 中有数据“123C30”, 那么如何在 B1 中写一个公式,使得 A1 的后三位被删除,得到 123
3) Right 函数 主要功能:从一个文本字符串的最后一个字符开始,截取指定数目的字符。 使用格式:RIGHT(text,num_chars) 参数说明:text 代表要截字符的字符串;num_chars 代表给定的截取数目。 应用举例:使用方法同 Left 函数,但是只是提取的右边的字符。 4) Mid 函数 主要功能:从一个文本字符串的指定位置开始,截取指定数目的字符。

使用格式:MID(text,start_num,num_chars) 参数说明:text 代表一个文本字符串;start_num 表示指定的起始位置;num_chars 表示 要截取的数目。 应用举例:在系统优化时经常会提取一段字符中的一段字符,比如提取 A2 单元格“我 很爱网优”中间的三位字符,只需要输入“=MID(A2,2,3) ”即可。 5) Substitute 函数 主要功能:需要在某一文本字符串中替换指定的文本。 使用格式:SUBSTITUTE(text,old_text,new_text,instance_num) 参数说明: Text 为需要替换其中字符的文本,或对含有文本的单元格的引用。 为需要替换的旧文本。 用于替换 old_text 的文本。 为一数值,用来指定以 new_text 替换第几次出现的 old_text。如果指

Old_text New_text

Instance_num

定了 instance_num,则只有满足要求的 old_text 被替换;否则将用 new_text 替换 Text 中 出现的所有 old_text。 应用举例: 有些情况下在制作工参时,提取的频点信息中包含 BCCH 频点,因此制作 TCH 频点时需要将 BCCH 频点替换掉, 例如在 C2 中输入 “=SUBSTITUTE(A:A,";"&B2,"")” , 即可把 BCCH 频点替换掉,如下图所示:

3、逻辑函数
1) If 函数 主要功能:根据对指定条件的逻辑判断的真假结果,返回相对应的内容。 使用格式:=IF(Logical,Value_if_true,Value_if_false) 参数说明: Logical 代表逻辑判断表达式; Value_if_true 表示当判断条件为逻辑 “ 真

(TRUE)”时的显示内容,如果忽略返回“TRUE”;Value_if_false 表示当判断条件为逻辑“假 (FALSE)”时的显示内容,如果忽略返回“FALSE”。 应用举例:我们经常利用 IF 函数进行数据的分类,如果分类较多的情况下我们会利用 IF 函数嵌套。 比如, 要筛选出 E 列中小于等于 3 的值为 “小” , 大于 3 小于等于 9 的为 “大” , 大于 9 的为“很大” ,只需输入公式“=IF(E2>3,IF(E2<10,"大","很大"),"小")”然后向下填充 即可。如下图所示:

2)

And 函数 主要功能: 返回逻辑值: 如果所有参数值均为逻辑“真 (TRUE) ”, 则返回逻辑“真 (TRUE) ”,

反之返回逻辑“假(FALSE)”。 使用格式:AND(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这 30 个。 应用举例: 在 C5 单元格输入公式: =AND(A5>=60,B5>=60), 确认。 如果 C5 中返回 TRUE, 说明 A5 和 B5 中的数值均大于等于 60,如果返回 FALSE,说明 A5 和 B5 中的数值至少有 一个小于 60。 3) Or 函数 主要功能:返回逻辑值,仅当所有参数值均为逻辑“假(FALSE)”时返回函数结果逻辑 “假(FALSE)”,否则都返回逻辑“真(TRUE)”。 使用格式:OR(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这 30

个。 应用举例:在 C62 单元格输入公式:=OR(A62>=60,B62>=60),确认。如果 C62 中返回 TRUE,说明 A62 和 B62 中的数值至少有一个大于或等于 60,如果返回 FALSE,说明 A62 和 B62 中的数值都小于 60。

4、查找函数
1) Vlookup 函数 主要功能: 在数据表的首列查找指定的数值, 并由此返回数据表当前行中指定列处的数 值。 使用格式:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参数说明: Lookup_value 代表需要查找的数值; Table_array 代表需要在其中查找数据的 单 元 格 区 域 ; Col_index_num 为 在 table_array 区 域 中 待 返 回 的 匹 配 值 的 列 序 号 ( 当 Col_index_num 为 2 时,返回 table_array 第 2 列中的数值,为 3 时,返回第 3 列的值……) ; Range_lookup 为一逻辑值,如果为 TRUE 或省略,则返回近似匹配值,也就是说,如果找 不到精确匹配值,则返回小于 lookup_value 的最大数值;如果为 FALSE,则返回精确匹配 值,如果找不到,则返回错误值#N/A。 应用举例:Lookup 函数是非常重要的,可以说无时无刻不再用他。比如通过 CI 来索引 小区的 BCCH,在 I2 单元格中输入“=VLOOKUP(H2,E:F,2,0)” ,表示在 E 列中查找需要的 CI,返回 F 列中匹配值,下拉单元格直接填充单元格即可。如下所示: