{
,IF((Sheet2!B1:B100)>10,ROW(Sheet2!B1:B100)," ")),ROW( )),1))，
"",INDEX(Sheet2!$A:$B,SMALL(IF((Sheet2!B1:B100)<-10,ROW(Sheet2!B1:B100),IF((Sheet2!B1:B100)>10,ROW(Sheet2!B1:B100)," ")),ROW( )),1)）}
作用：用于将其中将一个表格中符合一定条件下的数值自动统计到另一个表格中。上述函数数组是将表Sheet2中B列数值大于10或小于-10的数据列到函数所在表格当中。
①ROW函数函数公式：ROW(reference)
解释：Reference 为需要得到其行号的单元格或单元格区域。上述函数中的ROW（Sheet2!$B$1:$B$100),即列出表Sheet2中B1到B100的行号，若单是该函数的数组（先选定一列的100个单元格，输入函数，按Ctrl+Shift+Enter)，得到的一列结果就是1 2 3 4 …100.若是ROW（Sheet2!$B$3:$B$100)，结果就是3~100
②IF函数函数公式：IF(logical_test,value_if_true,value_if_false)
解释：logical_test是判定的条件，value_if_true是判定条件为正确时所返回的值，value_if_false是判定条件为错误时所返回的值。上述函数中的IF((Sheet2!B1:B100)<-10,ROW(Sheet2!B1:B100),IF((Sheet2!B1:B100)>10,ROW(Sheet2!B1:B100)," "),即若表Sheet2中B1到B100中有单元格的数值大于10或小于-10，就列出该单元格的行号，否则为空格。该IF函数中有个子IF函数，按函数顺序来解释就是判定Sheet2中B1到B100的值是不是小于-10，若是则列出它的行号，若不是再进行第二次判定Sheet2中B1到B100的值是不是大于10，是则列出它的行号，若不是就为空格。
③SMALL函数函数公式：SMALL(array,k)
解释：array 为需要找到第k 个最小值的数组或数字型数据区域。k 为返回的数据在数组或数据区域里的位置(从小到大)。这个其实我也不是很懂，大概就是将一列数据按从小到大的顺序排序。上述函数中SMALL(IF((Sheet2!B1:B100)<-10,ROW(Sheet2!B1:B100),IF((Sheet2!B1:B100)>10,ROW(Sheet2!B1:B100)," ")),ROW( ))，简化一下就是SMALL（②号的IF函数，ROW（）），就是将②号IF函数所得的结果从小到大排序。原是1空空4空6空8空空，排序后的结果就是1468跟无效值（因空格不属于数据）
④INDEX函数函数公式：INDEX(array，row_num，column_num)
解释：返回数组中指定的单元格或单元格数组的数值。Array为单元格区域或数组常数；Row_num为数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有column_num；Column_num是数组中某列的列序号，上述函数中简化就是INDEX（Sheet2!$B$1:$B$100,SMALL函数）就是③号的SMALL组合函数所得出结果是从小排到大的行号，而INDEX函数就是将这些行号所在的原始数据列的数值列出来。
⑤ISERROR函数函数公式：ISERROR（expression）
解释：主要用于判断公式运行结果是否出错，expression就是一个表达式，ISERROR函数就是用来判断表达式是否正确，若表达式表示错误，ISERROR就返回TRUE，表达式正确就返回FALSE。上述函数简化就是ISERROR（④号INDEX函数），由上图可以看出后面第五行到第十行都是错误值，所以就返回下图的数据。
⑥IF函数
解释：上述函数的最终简化形式就是IF（ISERROR函数，””，INDEX函数），意思就是若函数ISERROR返回值是TURE，该单元格的数据就等于空格（也就是无数据），若ISERROR函数返回值是FALSE，就执行INDEX函数，从⑤号的图看，1-10行，其中1-4行为FALSE，就是只有这四行执行INDEX函数，即④号图里的前四行的数据；5-10行为TRUE，就直接是空值了，不需要运行INDEX函数。最终结果如下图：
总结：本函数数组就是用来将表Sheet2中大于10小于-10的数据返回到表Sheet1中。因为表Sheet2中有两列数据，可以将④号的INDEX函数：INDEX（Sheet2!$B$1:$B$100,SMALL函数）中的红色标注部分改成你想要返回数据的所在列的范围就行了，即可以在B列将INDEX函数改成是INDEX （Sheet2!$A$1:$A$100,SMALL函数）即可。
（前一个表是原始数据，后一个表是使用函数后的情形）
清洗处理类：3-10 关联匹配类：1-2，11-14 1）Vlookup() VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。 VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配） vlookup就是竖直查找，即列查找。通俗的讲，根据查找值参数，在查找范围的第一列搜索查找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所对应的值。 以后几乎都使用精确匹配，最后项的参数一定要选择为false。 需求：A分销商需要7/31销量数据 ） 操作：在I 3 单元格输入：=VLOOKUP(H3,$A$3:$F$19,5,FALSE) 分析： H3为我们想查找的型号，即iphone5。为什么要写H3，而不是直接写iphone5，因为方便公式进行拖拽填充，以及保证准确性。 $A$3:$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4和剩余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以对公式进行直接的拖拽。 5 从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列（即型号列）后面的第五列。注意这里的列数是从引用范围的第一列做为1，而不是以A列作为第一列，万万注意此处。
2）Lookup() 1 矢量形式的LOOKUP 矢量形式的LOOKUP 在一行或一列区域（称为矢量）中查找值，然后返回另一行或一列区域中相同位置处的值。如果要指定其中包含要匹配的值的区域，请使用这种形式的LOOKUP 函数。 矢量形式的语法 LOOKUP(lookup_value,lookup_vector,result_vector) Lookup_value 是LOOKUP 在第一个矢量中搜索到的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。 Lookup_vector 是一个仅包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。 重要说明：lookup_vector 中的值必须按升序顺序排列。例如，-2、-1、0、1、2 或A-Z 或FALSE、TRUE。否则，LOOKUP 返回的值可能不正确。大写和小写文本是等效的。 Result_vector 是一个仅包含一行或一列的区域。它的大小必须与lookup_vector 相同。 从A1:A11找D2（3），返回同行的C列的值a 注意： 如果LOOKUP 找不到lookup_value，它会匹配lookup_vector 中小于或等于lookup_value 的最大值。 A列找不到6 就返回A列5同行的 C列值g 如果lookup_value 小于lookup_vector 中的最小值，则LOOKUP 会返回#N/A 错误值。
常用清洗处理类函数 1、Trim 清除掉字符串两边的空格，将格式转化为文本，如需转为数值则=VALUE(TRIM(单元格))。substitute对指定的字符串进行替换，可去掉所有空格，=substitute(text,old_text,new_text,[instance_num])=substitute（需要替换的文本，旧文本，新文本，第N个旧文本） 2、Concatenate，=Concatenate(单元格1，单元格2……)合并单元格中的内容，也可以用&&&。 3、Replace=Replace（指定字符串，哪个位置开始替换，替换几个字符，替换成什么）替换掉单元格的字符串，清洗使用较多。如新文本为文本格式，需加“”且在英文状态下输入。 4、Left／Right／Mid =Mid(指定字符串，开始位置，截取长度) 5、Len／Lenb 返回字符串的长度，在len中，中文计算为一个，在lenb中，中文计算为两个。Find=Find（要查找字符，指定字符串，第几个字符） 查找某字符串出现的位置，可以指定为第几次出现，与Left／Right／Mid结合能完成简单的文本提取 6、Search和Find类似，区别是Search大小写不敏感，但支持＊通配符 7、Lookup =Lookup（查找的值，值所在的位置，返回相应位置的值） 最被忽略的函数，功能性和Vlookup一样，但是引申有数组匹配和二分法。 8、Index＝Index（查找的区域，区域内第几行，区域内第几列）和Match组
合，媲美Vlookup，但是功能更强大。 Match＝Match（查找指定的值，查找所在区域，查找方式的参数） 和Lookup类似，但是可以按照指定方式查找，比如大于、小于或等于。返回值所在的位置。 9、Row返回单元格所在的行；Column返回单元格所在的列； 每个季度合计行的余数都为1，利用这个特性进行跨行求和。 输入公式=SUM(IF(MOD(ROW(B2:B17),4)=1,B2:B17)) 10、Offset＝Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）建立坐标系，以坐标系为原点，返回距离原点的值或者区域。正数代表向下或向右，负数则相反。 逻辑运算类 1、IF、And、Or、IS系列 计算统计类 1、Sum／Sumif／Sumifs，统计满足条件的单元格总和。 2、Sumproduct统计总和相关，=sumproduct（A2:A8,B2:B8）可转化为 =sumproduct（数组1，数组2）=sumproduct（｛1；2；3；4；5；6；7｝，｛1；2；3；4；5；6；7｝）=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。 3、Count／Countif／Countifs统计满足条件的字符串个数 4、Max返回数组或引用区域的最大值；Min返回数组或引用区域的最小值；Rank排序，返回指定值在引用区域的排名，重复值同一排名。 5、Rand／Randbetween常用随机抽样，前者返回0~1之间的随机值，后者可以指定范围。
清洗处理类：3-10 关联匹配类：1-2, 11-14 1) Vlookup() VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。 VLOOKUP (查找值，查找范]韦I ，查找列数，精确匹配或者近似匹配) vlookup 就是竖直查找，即列查找。通俗的讲，根据查找值参数，在查找范围的第一列搜索 查找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所对应的 值。 以后几乎都使用精确匹配，最后项的参数一定要选择为false 。 需求：A 分销商需要於1销量数据 操作:在 13 单元格输入：二VLOOKUP(H3,$A$3:$F$19,5,FALSE) 分析： H3为我们想查找的型号，即iphone5o 为什么要写H3,而不是直接写iphone5,因为方 便公式进行拖拽填充，以及保证准确性。 $A$3:$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4 和剩 余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以対公式进行直 接的拖 拽。 5从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列(即型号 列)后面的第五列。注意这里的列数是从引用范围的第一列做为1,而不是以A 列作为第一 列，万万注 意此处。 iphoneS iM ip4ff 乐 phgb 小初 100 91 62 73 91 50 61 52 98 97 100 59 曲 82 63 $0 69 52 77 51 50 81 96 &0 67 $0 8) $5 62 3 100 52 86 57 53 50 84 79 60 78 20 “ 71 92 100 !5 91 70 69 曲 28 SO 67 89 62 乂 69 60 69 73 18 70 63 87 ?) 58 79 77 65 50 82 脯 &8 73 SO 79 100 67 28 趺 81 57 n W ： ShMtl/a?et2： Sheets . j-MkA- 护 :S B W “i 町? ? 百厦轻稔E 用乂各八Microsoft Ercei ? 7 >?；■ ■■■■?■■ ■ r ■ ■■ ■ i ?■ ■ i ■■ ■? ― —— ■?■ ■ ■ -7 ttA 55BB^? 公式 8? 古竟 fiffi ffStlJI Hl 7 note2 H? 900 800 700 600 20202020 14 15 16 20 w tX ：ik4?4d 源数据 分销商需要数据
必修：excel上机考试知识点 一、数据的计算 1、函数法（共四个函数） ：单击填和的单元格——插入——函数——SUM——修改参数范围：单击填平均值的单元格——插入——函数——AVERAGE——修改参数范围 ：单击填和的单元格——插入——函数——MAX——修改参数范围：单击填和的单元格——插入——函数——MIN——修改参数范围2、：单击填结果的单元格——输入公式例如：输入“=(B2-C2)*(E2-D2)” 二、：选择参与排序的所有数据——数据——排序——选择主要关 键字（例如按总分排序，关键字则选总分）——选择升序或降 序 三、：单击自动填充柄拖拽鼠标（即在选中的单元格右下角单击鼠 标拖拽） 四、：行标或列标上单击右键 五、右键点击——设置单元格格式（小数点精确几位，文本，百分比，字体对齐方式靠下，填充颜色合并居中，内边框和外边框等） 六、：选中数据区域——插入——图表（标题、分类标志、 行列选择、显示值、显示图例）上机主要考的图表有簇 状柱形图、三维饼图、折线图三种。
七、，填充颜色：插入——图片——自选图形——星星—— 在指定位置拖拽 八、：格式——条件格式 九、：在窗口左下脚双击Sheet1改为学生成绩。 十、选中数据区域——数据——筛选——自动筛选 十一、：视图——显示比例 第三章信息加工与表达 第四节数据信息的加工——计算和排序 一、数据的计算 （一）函数法 １、SUM( ) 定位置：单击要填写计算总和的单元格。 找函数：选择“插入”菜单中“函数”选项，在“插入函数”属性框中选择求和函数SUM，点击“确定”，如下图所示。或者直接单击工具栏中的求和函数按钮。 选范围：在“函数参数”属性框中观察求和数值的范围是否正确，如不正确，则手工修改下图中圈起的位置，将其改为正确的范围，或用鼠标直接在正确的数据区域上拖拽。最后执行“确定”按钮 2、AVERAGE( ) 定位置：单击要填写计算平均数的单元格。 找函数：选择“插入”菜单中“函数”选项，在“插入函数”属性框中选择求平均函数AVERAGE，点击“确定”，如下图所示。 选范围：在“函数参数”属性框(如下图所示)中观察求平均数值的范围是否正确，如不正确，则手工修改下图中圈起的位置，将其改为正确的范围，或用鼠标直接在正确的数据区域上拖拽。最后执行“确定”按钮。 3、MAX( ) 定位置：单击要填写计算最大值的单元格。 找函数：选择“插入”菜单中“函数”选项，在“插入函数”属性框中选择
----- 必修： excel 上机考试知识点 一、数据的计算 1、函数法（共四个函数） 求和函数： SUM( ) ：单击填和的单元格——插入——函数—— SUM
——修改参数范围 单击填平均值的单元格——插入——函数：)求平均函数： AVERAGE( —— AVERAGE ——修改参数范围
最大值函数： MAX( ) ：单击填和的单元格——插入——函数—— MAX ——修改参数范围 最小值函数： MIN( ) ：单击填和的单元格——插入——函数—— MIN ——修改参数范围 2、公式法：单击填结果的单元格——输入公式例如：输入 “=(B2-C2)*(E2-D2) ” 二、数据排序：选择参与排序的所有数据——数据——排序——选 择主要关键字（例如按总分排序，关键字则选总分）——选择
升序或降序 三、数据自动填充功能：单击自动填充柄拖拽鼠标（即在选中的单 元格右下角单击鼠标拖拽） 四、设置行宽列宽：行标或列标上单击右键 五、设置单元格格式：右键点击——设置单元格格式（小数 点精确几位，文本，百分比，字体对齐方式靠下，填充颜色
合并居中，内边框和外边框等）------ ----- 六、根据数据生成图表：选中数据区域——插入——图表 （标题、分类标志、行列选择、显示值、
显示图例）上 机主要考的图表有簇状柱形图、三维饼图、折线图三种。七、插入一个五角星，填充颜色：插入——图片——自选图 形——星星——在指定位置拖拽 八、条件格式：格式——条件格式 工作表名称：在窗口左下脚双击Sheet1 改为学生成绩。十、九、 筛选：选中数据区域——数据——筛
EXCEL常用函数公式及举例 一、相关概念 （一）函数语法 由函数名+括号+参数组成 例：求和函数：SUM（A1,B2,…) 。参数与参数之间用逗号“,”隔开（二）运算符 1. 公式运算符：加（+）、减（-）、乘（*）、除（/）、百分号（%）、乘幂（＾） 2. 比较运算符：大与（>）、小于（<）、等于（=）、小于等于（<=）、大于等于（>=）、不等于（<>） 3. 引用运算符：区域运算符（：）、联合运算符（,） （三）单元格的相对引用与绝对引用 例：A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 （一）数学函数 1. 求和=SUM（数值1,数值2,……） 2. 条件求和=SUMIF（查找的范围,条件(即对象),要求和的范围） 例：（1）=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思：对第A1栏至A4栏中，大于等于200的数值对应的第B1列至B4列中数值求和 （2）=SUMIF(A1:A4,”<300”,C1:C4)
函数意思：对第A1栏至A4栏中，小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数=COUNT（数值1,数值2,……） 例：（1）=COUNT(A1:A4) 函数意思：第A1栏至A4栏求个数（2）=COUNT(A1:C4) 函数意思：第A1栏至C4栏求个数 4. 条件求个数=COUNTIF（范围,条件） 例：（1）=COUNTIF(A1:A4，”<>200”) 函数意思：第A1栏至A4栏中不等于200的栏求个数 （2）=COUNTIF(A1:C4,”>=1000”) 函数意思：第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数=AVERAGE（数值1,数值2,……） 例：（1）=AVERAGE(A1,B2) （2）=AVERAGE(A1:A4) 6. 四舍五入函数=ROUND(数值,保留的小数位数) 7. 排位函数=RANK（数值,范围,序别）1-升序0-降序 例：（1）=RANK(A1,A1:A4,1) 函数意思：第A1栏在A1栏至A4栏中按升序排序，返回排名值。 （2）=RANK(A1,A1:A4,0) 函数意思：第A1栏在A1栏至A4栏中按降序排序，返回排名值。 8. 乘积函数=PRODUCT（数值1,数值2,……） 9. 取绝对值=ABS(数字) 10. 取整=INT(数字) （二）逻辑函数
excel公式笔记 一、vlookup 1.查找A列中第一个以”厦门”开头的记录对应B列的值。 =vlookup(H厦门性A:B20) 其中第一个参数为要寻找的文本，第二个参数为一个区域，第二个参数的第一列为要寻找的区域,第三个参数的2表示第二个参数的第二列显示出来,第四个参数的0表示精确查找。 二、countif 1.统计Al:A10区域中型号为" 2.5m*3m"的记录个数。 =countif(Al:A10,,,2.5m~*3m H) 在excel常用函数中,支持通配符的主要有vlookup、hlookup> match> sum讦、courttif、search> searchB,而find、findB> subsitute 不支持通配符。*表示任意字符,？表示单个字符解除字符的通配性。 2.统计Al:A10中不重复数的个数。 =SUMPRODUCT((1/COUNTIF(A1:A10,A1:A10)))
3?求小于60的数据有多少 二count(A2:AKVv6(T) sumproduct 1?求购物总花费,A列表示购买数量月列表示购买单价 =sumproduct(A2:A8,B2:B8) 意思为A2*B2+A3*B3oooo +A8*B8 2.求二班有多少学生学习了数学 =sumproduct((A2:A10=H~ B,,)*(B2:B10=H数学“)) 表示二班的数学有几个 3.求二班数学分数总和 =sumproduct((A2:A10=M Z：B M)*(B2:B10=H数学H)*(C2:C1O)) 4.统计“技术部”考试成绩为0的个数(缺考除外) =sumproduct((B2:B9=H技术部,,)*(E2:E9=0)*(E2:E9o,,H)) excel会将空值看成0,所以在统计成绩为0的考生时，需要把成绩为空的考牛去除。
第一章函数基础知识 1、按shift+F3是插入弹出“插入函数”对话框的快捷键。 2、当在单元格中键入“=”号时，在“名称框”中会显示出常用的函数列表，可以选择所需要的。 3、在单元格中输入公式时，当键入函数名称时，或者键入函数名称和左括号时，可按ctrl+shift+A组合键显示函数的参数说明。 4、如果在单元格中输入的公式返回错误的信息，想了解这个错误信息的含义，如“#NAME”，可以选中此单元格，鼠标光标移动到紧挨此单元格左侧的智能标记图标上，就会出现“公式中包含不可识别的文本”之类的错误信息说明。 第二章数学和三角函数 1、SUM函数的参数不能超过30个，如果需要30个以上参数时，可以在引用的参数两边多加一对括号，这样就突破了这个限制。如：计算A1：A32的和可以用公式： =SUM((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21, A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32)) 2、A VERAGE函数是求平均值的函数，如果参数引用中包含0值，则也会算在内，可以使用下面的公式实现求平均数时，只对不等于0的值求平均数： 用数组公式：{=A VERAGE(IF(A1:A10<>0,A1:A10))} 提示：计算时会先得到一个含有数值和逻辑值的数组，再对这个数组求平均，由于A VERAGE 函数会忽略逻辑值，所以就只对不等于0的数值求平均数。 3、INT（）是向下取整函数。即向数轴向左的方向取整。 例如：=INT(9.9) 结果是9 =INT(-9.9) 结果是-10 注意INT（）函数和TRUNC（）函数的区别。 4、TRUNC（数值或单元格引用，指定取整精度）函数是取整函数，且是真正的取整函数，即截取数字的整数部分，正数、负数同样对待。 如：=TRUNC(8.4) 结果是8 =TRUNC(-8.4) 结果是-8 ,而如果是=INT(-8.4) 则结果就是-9 注意： （1）TRUNC（）函数和INT（）函数的区别。 （2）取整精度默认为0，也可以指定，如：=TRUNC（4.867,2）结果是4.86 =TRUNC(-9.2389,3) 结果是-9.238 5、CEILING（要四舍五入的数值，是需要四舍五入的乘数）函数 用法：此函数是将第一个参数向上舍入（沿绝对值增大的方向）为最接近的第二个参数的倍数。 注意：第一个参数和第二个参数的正负号必须统一；无论数字符号如何，都按远离0 的方向向上舍入;最终结果肯定是第2个参数的整数倍。 例1：=CEILING(0.234, 0.01) 结果是将0.234向上舍入到最接近的0.01的24倍，即0.01*24
工作中最常用的excel函数公式大全，帮你整理齐了，拿来即用 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。 2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","")
说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。 2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。 四、求和公式 1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2
=SUMIF(A:A,E2,C:C) 说明：SUM IF函数的基本用法 3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 4、多条件模糊求和 公式：C11
EXCEL最常用的函数 1.ASC 用途：将字符串中的全角(双字节)英文字母更改为半角(单字节)字符。 语法：ASC(text) 参数：Text为文本或包含文本的单元格引用。如果文本中不包含任何全角英文字母，则文本不会被更改。 实例：如果A1=excel，则公式“=ASC(A1)”返回excel。 2.CHAR 用途：返回对应于数字代码的字符，该函数可将其他类型的电脑文件中的代码转换为字符(操作环境为MacintoshMacintosh字符集和WindowsANSI字符集)。 语法：CHAR(number)。 参数：Number是用于转换的字符代码，介于1～255之间(使用当前计算机字符集中的字符)。 实例：公式“=CHAR(56)”返回8，=CHAR(36)返回$。 3.CLEAN 用途：删除文本中不能打印的字符。对从其他应用程序中输入的字符串使用CLEAN函数，将删除其中含有的当前操作系统无法打印的字符。 语法：CLEAN(text)。 参数：Text为要从中删除不能打印字符的任何字符串。 实例：由于CHAR(7)返回一个不能打印的字符，因此公式“=CLEAN(CHAR(7)&"text"&CHAR(7))”返回text。 4.CODE 用途：返回文字串中第一个字符的数字代码(对应于计算机当前使用的字符集)。 语法：CODE(text)
参数：Text为需要得到其第一个字符代码的文本。 实例：因为CHAR(65)返回A，所以公式“=CODE("Alphabet")”返回65。 5.CONCATENA TE 用途：将若干文字串合并到一个文字串中，其功能与"&"运算符相同。 语法：CONCATENA TE(text1，text2，...) 参数：Text1，text2，...为1到30个将要合并成单个文本的文本项，这些文本项可以是文字串、数字或对单个单元格的引用。 实例：如果A1=98、A2=千米，则公式“=CONCATENATE(A1，A2)”返回“98千米”，与公式“=A1&A2”等价。 6.DOLLAR或RMB 用途：按照货币格式将小数四舍五入到指定的位数并转换成文字。 语法：DOLLAR(number，decimals)或RMB(number，decimals)。 参数：Number是数字、包含数字的单元格引用，或计算结果为数字的公式;Decimals是十进制的小数，如果Decimals为负数，则参数number从小数点往左按相应位数取整。如果省略Decimals，则假设其值为2。 实例：公式“=RMB(1586.567，2)”返回“￥1586.57”，=RMB(99.888)返回“￥99.89”。 7.EXACT 用途：测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE;否则返回FALSE。EXACT函数能区分大小写，但忽略格式上的差异。 语法：EXACT(text1，text2)。 参数：Text1是待比较的第一个字符串，Text2是待比较的第二个字符串。 实例：如果A1=物理、A2=化学A3=物理，则公式“=EXACT(A1，A2)”返回FALSE，=EXACT(A1，A3)返回FALSE，=EXACT("word"，"word")返回TRUE。 8.FIND 用途：FIND用于查找其他文本串(within_text)内的文本串(find_text)，并从within_text 的首字符开始返回find_text的起始位置编号。此函数适用于双字节字符，它区分大小写但
电子表格常用函数公式及用法 1、求和公式： =SUM(A2:A50) ——对A2到A50这一区域进行求和； 2、平均数公式： =AVERAGE(A2:A56) ——对A2到A56这一区域求平均数； 3、最高分： =MAX(A2:A56) ——求A2到A56区域（55名学生）的最高分； 4、最低分： =MIN(A2:A56) ——求A2到A56区域（55名学生）的最低分； 5、等级： =IF(A2>=90,"优",IF(A2>=80,"良",IF(A2>=60,"及格","不及格"))) 6、男女人数统计： =COUNTIF(D1:D15,"男") ——统计男生人数 =COUNTIF(D1:D15,"女") ——统计女生人数 7、分数段人数统计： 方法一： 求A2到A56区域100分人数：=COUNTIF(A2:A56,"100") 求A2到A56区域60分以下的人数；=COUNTIF(A2:A56,"<60") 求A2到A56区域大于等于90分的人数；=COUNTIF(A2:A56,">=90") 求A2到A56区域大于等于80分而小于90分的人数； =COUNTIF(A1:A29,">=80")-COUNTIF(A1:A29," =90")
求A2到A56区域大于等于60分而小于80分的人数； =COUNTIF(A1:A29,">=80")-COUNTIF(A1:A29," =90") 方法二： （1） =COUNTIF(A2:A56,"100") ——求A2到A56区域100分的人数；假设把结果存放于A57单元格； （2） =COUNTIF(A2:A56,">=95")－A57 ——求A2到A56区域大于等于95而小于100分的人数；假设把结果存放于A58单元格； （3）=COUNTIF(A2:A56,">=90")－SUM(A57:A58) ——求A2到A56区域大于等于90而小于95分的人数；假设把结果存放于A59单元格；（4）=COUNTIF(A2:A56,">=85")－SUM(A57:A59) ——求A2到A56区域大于等于85而小于90分的人数； …… 8、求A2到A56区域优秀率：=(COUNTIF(A2:A56,">=90"))/55*100 9、求A2到A56区域及格率：=(COUNTIF(A2:A56,">=60"))/55*100 10、排名公式： =RANK(A2，A$2:A$56) ——对55名学生的成绩进行排名； 11、标准差： =STDEV(A2:A56) ——求A2到A56区域(55人)的成绩波动情况（数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化）； 12、条件求和： =SUMIF(B2:B56,"男"，K2:K56) ——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；
E X C E L常用函数公式大 全及举例 This model paper was revised by the Standardization Office on December 10, 2020
EXCEL常用函数公式大全及举例 一、相关概念 （一）函数语法 由函数名+括号+参数组成 例：求和函数：SUM（A1,B2,…) 。参数与参数之间用逗号“,”隔开 （二）运算符 1. 公式运算符：加（+）、减（-）、乘（*）、除（/）、百分号（%）、乘幂（＾） 2. 比较运算符：大与（>）、小于（<）、等于（=）、小于等于（<=）、大于等于（>=）、不等于（<>） 3. 引用运算符：区域运算符（：）、联合运算符（,） （三）单元格的相对引用与绝对引用 例： A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 （一）数学函数 1. 求和 =SUM（数值1,数值2,……） 2. 条件求和 =SUMIF（查找的范围,条件(即对象),要求和的范围）
例：（1）=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思：对第A1栏至A4栏中，大于等于200的数值对应的第B1列至B4列中数值求和 （2）=SUMIF(A1:A4,”<300”,C1:C4) 函数意思：对第A1栏至A4栏中，小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数 =COUNT（数值1,数值2,……） 例：（1） =COUNT(A1:A4) 函数意思：第A1栏至A4栏求个数 （2） =COUNT(A1:C4) 函数意思：第A1栏至C4栏求个数 4. 条件求个数 =COUNTIF（范围,条件） 例：（1） =COUNTIF(A1:A4，”<>200”) 函数意思：第A1栏至A4栏中不等于200的栏求个数 （2）=COUNTIF(A1:C4,”>=1000”) 函数意思：第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数 =AVERAGE（数值1,数值2,……） 例：（1） =AVERAGE(A1,B2) （2） =AVERAGE(A1:A4) 6. 四舍五入函数 =ROUND(数值,保留的小数位数) 7. 排位函数 =RANK（数值,范围,序别） 1-升序 0-降序 例：（1） =RANK(A1,A1:A4,1)
求参数的和，就是求指定的所有参数的和。 1.条件求和，Excel中sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。函数语法是：SUMIF(range，criteria，sum_range) sumif函数的参数如下： 第一个参数：Range为条件区域，用于条件判断的单元格区域。 第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。 第三个参数：Sum_range为实际求和区域，需要求和的单元格、区域或引用。 当省略第三个参数时，则条件区域就是实际求和区域。 (注：criteria 参数中使用通配符（包括问号 ()和星号 (*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 (~)。) 3.实例：计算人员甲的营业额（$K$3:$K$26：绝对区域，按F4可设定） 1.用途：它可以统计数组或单元格区域中含有数字的单元格个数。 2.函数语法：COUNT(value1，value2，...)。 参数：value1，value2，...是包含或引用各种类型数据的参数(1～30个)，其中只有数字类型的数据才能被统计。
3.实例：如果A1=90、A2=人数、A3=〞〞、A4=54、A5=36，则公式“=COUNT(A1:A5) ”返回得3。 1.说明：返回参数组中非空值的数目。参数可以是任何类型，它们包括空格但不包括空白单元格。如果不需要统计逻辑值、文字或错误值，则应该使用COUNT函数。 2.语法：COUNTA(value1，value2，...) 3.实例：如果A1=、A2=，A3=“我们”其余单元格为空，则公式“=COUNTA(A1:A5)”的计算结果等于3。 1.用途：计算某个单元格区域中空白单元格的数目。 2.函数语法：COUNTBLANK(range) 参数：Range为需要计算其中空白单元格数目的区域。 3.实例：如果A1=88、A2=55、A3=(空格)、A4=72、A5=(空格)，则公式“=COUNTBLANK(A1:A5)”返回得2。 用途：计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(range，criteria)
Excel 常用技巧-- 工作中最常用的 30 个Excel 函数公式，帮你整理齐了！ 目录 一、数字处理 1、取绝对值 2、取整 3、四舍五入 二、判断公式 1、把公式产生的错误值显示为空 2、IF 多条件判断返回值 三、统计公式 1、统计两个表格重复的内容 2、统计不重复的总人数 四、求和公式 1、隔列求和 2、单条件求和 3、单条件模糊求和 4、多条件模糊求和 5、多表相同位置求和 6、按日期和产品求和 五、查找与引用公式 1、单条件查找公式 2、双向查找公式 3、查找最后一条符合条件的记录。 4、多条件查找 5、指定区域最后一个非空值查找
6、按数字区域间取对应的值六、字符串处理公式 1、多单元格字符串合并 2、截取除后 3 位之外的部分 3、截取- 前的部分 4、截取字符串中任一段的公式 5、字符串查找 6、字符串查找一对多七、日期计算公式 1、两日期相隔的年、月、天数计算 2、扣除周末天数的工作日天数 一、数字处理 1、取绝对值=ABS（数字） 2、取整=INT（数字） 3、四舍五入=ROUND数（字, 小数位数） 二、判断公式 1、把公式产生的错误值显示为空公式：C2 =IFERROR（A2/B2,""）说明：如果是错误值则显示为空，否则正常显示
2、IF 多条件判断返回值 公式：C2 =IF(AND(A2<500,B2=" 未到期")," 补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0 说明在另一个表中存在，0 则不存在
excel常用公式函数教程 1．求和函数SUM 语法：SUM（number1，number2，．．．）。 参数：number1、number2．．．为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。 注意：参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。如果参数为数组或引用，只有其中的数字参与计算，数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。 应用实例一：跨表求和 使用SUM函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域进行求和，可以采用以下方法：选中Excel XP“插入函数”对话框中的函数，“确定”后打开“函数参数”对话框。切换至第一个工作表，鼠标单击“number1”框后选中需要求和的区域。如果同一工作表中的其他区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。 应用实例二：SUM函数中的加减混合运算 财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。按照规定，工作表中的这些项目没有输入负号。这时可以构造“=SUM（B2:B6，C2:C9，-D2，-E2）”这样的公式。其中B2:B6，C2:C9引用是收入，而D2、E2为支出。由于Excel不允许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出正确结果。即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:B6，C2:C9，-D2，-D3，D4）”这样的形式。 应用实例三：及格人数统计 假如B1:B50区域存放学生性别，C1:C50单元格存放某班学生的考试成绩，要想统计考试成绩及格的女生人数。可以使用公式“=SUM（IF（B1:B50=″女″，IF（C1:C50>=60，1，0）））”，由于它是一个数组公式，输入结束后必须按住Ctrl+Shift键回车。公式两边会自动添加上大括号，在编辑栏显示为“{=SUM（IF （B1:B50=″女″，IF（C1:C50>=60，1，0）））}”，这是使用数组公式必不可少的步骤。 2.平均值函数AVERAGE 语法：AVERAGE（number1，number2，．．．）。 参数：number1、number2．．．是需要计算平均值的1～30个参数。 注意：参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。如果需要将参数中的零排除在外，则要使用特殊设计的公式，下面的介绍。 应用实例一：跨表计算平均值
Excel常用函数公式大全（实用） 各位读友大家好！你有你的木棉，我有我的文章，为了你的木棉，应读我的文章！若为比翼双飞鸟，定是人间有情人！若读此篇优秀文，必成天上比翼鸟！ Excel常用函数公式大全（实用）1.数据查询在G3单元格输入公式：=VLOOKUP(F3,$B$2:$C$12,2,0)2.条件求和在G3单元格输入公式：=SUMIF($A$2:$A$12,F3,$D$2:$D$12)3.多条件计数在G3单元格输入公式：=COUNTIF($A$2:$A$12,F3)4.条件求平均在F3单元格输入公式：=A VERAGEIF($A$2:$A$12,E3,$C$2:$C$12)5.查询符合条件的多个数据（此公式是数组公式，需按ctrl+shift+enter键结束公式）在E3单元格输入公式：=IFERROR(INDEX($B$2:$B$12,SMALL(IF($A$2:$A$12=$E$2,R OW($1:$11)),ROW(A1))),”“)6.判断数据是否重复在B2单元格输入公式：=IF(COUNTIF($A$2:$A$12,A2)>1,”重复”,”“)7.统计不重复数据个数在C3单元格输入公式：=SUM(1/COUNTIF(A2:A12,A2:A12))数组公式：按ctrl+shift+enter键结束公式8.查找最后一个数据在E3单元格输入公式：=LOOKUP(1,0/($A$2:$A$12=E2),$C$2:$C$12)9.全称查找简称在B2单元格输入公式：=LOOKUP(0,0/FIND($E$2:$E$5,A2),$E$2:$E$5)10.提取数值在B2单元格输入公式：=-LOOKUP(1,-LEFT(A2,ROW($1:$4)))
excel常用函数运用汇总 一、excel随机函数公式。 要求：随机生成3.0055~3.0100之间的数，保留小数点后4位。 下面的公式自己套随机数 =ROUND(最小值+(最大值-最小值)*rand(),3) 0.150~0.200之间的数，保留小数点3位。 =ROUND(0.15+(0.2-0.15)*rand(),3) 3.0305~3.0350之间的数 =ROUND(3.0305+(3.0350-3.0305)*rand(),3) 二、EXECL表中输入身份证号码后直接使用公式算出年龄、出生年月、性别？比如身份证号在A1，出生年月在B1输入公式： =TEXT(IF(LEN(MID(A1,7,6+(LEN(A1)=18)*2))=8,MID(A1,7,6+(LEN(A1)=18)* 2),19&MID(A1,7,6+(LEN(A1)=18)*2)),"00-00-00") 年龄在C1输入公式：(B1为出生年月日) =datedif(B1,now(),"y") 性别在D1输入公式： =IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2)=0,"女","男")
三、计算成绩，排名函数公式。 1. 在“总分”列的F2单元格输入“=SUM(C2:E2)”，然后按下回车键，第一个学生的总分就会计算出来了。 图1 原始成绩表 2. 在“名次”列的“G2”单元格输入“=RANK(F2,$F$2:$F$7)”，然后按下回车键，第一位学生的名次也就出来了。怎么，名次不对？不要紧，往后看。对了，还要说一句，那就是上面公式中那个“$F$7”是“总分”栏中的最后一个数据的位置，实际中可以根据表格中的实际情况来填写。 3. 选中F2和G2单元格，然后拖动填充柄向下至最后一个单元格，看到了吗？所有人的总分和名次都在瞬间完成了（如图2）。而且更妙的是，按照这种方法，如果总分相同，名次也是相同的。数据越多，这种方法就越能体现出它的优势。试试看吧！ 图2 排好序的表 最后要提醒的是，在用于排名次的“=RANK(F2,$F$2:$F$7)”公式中，可千万不要忽略了符号“$”呀！名次到底能不能排得准确无误，它可是起到很重要的作用呢。
清洗处理类：3-10 关联匹配类：1-2，11-141）Vlookup() VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。 VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配） vlookup就是竖直查找，即列查找。通俗的讲，根据查找值参数，在查找范围的第一列搜索查找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所对应的值。 以后几乎都使用精确匹配，最后项的参数一定要选择为false。 需求：A分销商需要7/31销量数据 操作：在I 3 单元格输入：=VLOOKUP(H3,$A$3:$F$19,5,FALSE) 分析： H3为我们想查找的型号，即iphone5。为什么要写H3，而不是直接写iphone5，因为方便公式进行拖拽填充，以及保证准确性。 $A$3:$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4和剩余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以对公式进行直接的拖拽。 5 从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列（即型号列）后面的第五列。注意这里的列数是从引用范围的第一列做为1，而不是以A列作为第一
列，万万注意此处。
2）Lookup() 1 矢量形式的LOOKUP 矢量形式的LOOKUP 在一行或一列区域（称为矢量）中查找值，然后返回另一行或一列区域中相同位置处的值。如果要指定其中包含要匹配的值的区域，请使用这种形式的LOOKUP 函数。 矢量形式的语法 LOOKUP(lookup_value,lookup_vector,result_vector) Lookup_value 是LOOKUP 在第一个矢量中搜索到的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。 Lookup_vector 是一个仅包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。 重要说明：lookup_vector 中的值必须按升序顺序排列。例如，-2、-1、0、1、2 或A-Z 或FALSE、TRUE。否则，LOOKUP 返回的值可能不正确。大写和小写文本是等效的。 Result_vector 是一个仅包含一行或一列的区域。它的大小必须与lookup_vector 相同。 从A1:A11找D2（3），返回同行的C列的值a 注意： 如果LOOKUP 找不到lookup_value，它会匹配lookup_vector 中小于或等于lookup_value 的最大值。 A列找不到6 就返回A列5同行的C 列值g 如果lookup_value 小于lookup_vector 中的最小值，则LOOKUP 会返回#N/A 错误值。