1、你你 的的 成成 功功 ， 是是 我我 们们 最最 大大 的的 收收 获获 !柯怩卡美能达商用科技柯怩卡美能达商用科技( (东莞东莞) )有限公司有限公司何公超何公超 编著编著2011-8-252 2.第一章：函数简介第二章：逻辑函数第三章：数学和三角函数第四章：文本/日期/时间函数第五章：信息函数第六章：查询与引用函数第七章：统计函数附录：常见的公式错误代码ExcelExcel函数应用讲课提纲讲课内容3 3.第一章：函数简介P什么是公式：F 公式是对工作表的数值进行计算或操作的等式，以“=”开始，可以包括运算符、单元格引用、值或常量及及工作表函数。公式的基本元素包括以下几种元素：P运算符：F
2、 运算符对公式的元素进行特定类型的计算，一个运算符就是一个符号，例如“*”、“+”等；P单元格引用：F 用于表示单元格在工作表所处位置的坐标值。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为B3“P值或常量：F 直接键入到单元格或公式中的数字或文本值，或由名称所代表的数字或文本值。例如，日期 10/9/96、数字 210 和文本Quarterly Earnings都是常量。公式或由公式得出的数值都不是常量。P工作表函数：包括一些函数或参数，可以返回一定函数值公式及其基本元素4 4.第一章：函数简介运算符及其优先级- 像其它运算一样，在EXCEL中括号(包括各类括号)可以覆盖其内
3、置优先顺序。括号中的表达式，将享有最高优先级从而被最先计算。5 5.第一章：函数简介认识函数P 什么是函数？FExcel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。.用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM 函数对单元格或单元格区域进行加法运算6 6.第一章：函数简介P什么是参数？参数可以是哪些数据类型？F参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元格引用。
4、(注：当参数为文本时需用英文状态下的双引用)F参数也可以是常量、公式或其它函数。还可以是数组、单元格引用等认识参数7 7.第一章：函数简介函数结构图1：函数基本结构图2：嵌套函数示例8 8.第一章：函数简介输入界面或P 公式选项板 -帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息9 9.第一章：函数简介数据引用关于引用需要了解如下几种情况的含义：P外部引用-不同工作簿中的单元格的引用称为外部引用。P远程引用-引用其它程序中的数据称为远程引用。P相对引用-在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。P绝对引用-如果在复制公式时不希望 Excel 调整引
5、用，那么请使用绝对引用。即加入美元符号，如$C$1。1010.第一章：函数简介相对引用/绝对引用P$美元符号在Excel单元格数据区域中,代表的是绝对引用。 绝对引用,一般是在函数的参数中使用。在填写其参数的数据区域的时候,可通过F4快捷键来快速自动的输入绝对引用。1111.第一章：函数简介PExcel函数一共有11类，分别是：F数据库函数F日期与时间函数F工程函数F财务函数F信息函数F逻辑函数F查询和引用函数F数学和三角函数F统计函数F文本函数以F用户自定义函数。 函数的种类1212.第二章：逻辑函数什么是逻辑函数？P判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。在Exce
6、l中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。 1313.第二章：逻辑函数AND、OR、NOT函数P语法：AND(logical1,logical2, .)P语法：OR(logical1,logical2, .)P语法： NOT(logical1,logical2, .)1414.第二章：逻辑函数IF函数.Value_if_false logical_test 为 FALSE 时返回的值。例如，如果本参数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE，则 IF 函数将显示文本“超出预算”。如果 logical_test 为 FAL
7、SE 且忽略了 Value_if_false（即 value_if_true 后没有逗号），则会返回逻辑值 FALSE。如果 logical_test 为 FALSE 且 Value_if_false 为空（即 value_if_true 后有逗号，并紧跟着右括号），则本参数返回 0（零）。Value_if_false 也可以是其他公式。.Value_if_true logical_test 为 TRUE 时返回的值。例如，如果本参数为文本字符串“预算内”而且 logical_test 参数值为 TRUE，则 IF 函数将显示文本“预算内”。如果 logical_test 为 TRUE 而 v
8、alue_if_true 为空，则本参数返回 0（零）。如果要显示 TRUE，则请为本参数使用逻辑值 TRUE。Value_if_true 也可以是其他公式。P执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。F语法：IF(logical_test,value_if_true,value_if_false).Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如，A10=100 就是一个逻辑表达式，如果单元格 A10 中的值等于 100，表达式即为 TRUE，否则为 FALSE。本参数可使用任何比较运算符。15
9、15.第二章：逻辑函数P实例一：P实例二：IF函数实例B10单元公式：IF(B989,优秀,IF(B979,良,IF(B969,中,IF(B959,及格,不及格)1616.第二章：逻辑函数P求闰年F 能够被4整除但不能被100整除，或能够被400整除- 公式1：IF(OR(A2/400=INT(A2/400),AND(A2/4=INT(A2/4),A2/100INT(A2/100),闰年,平年)- 公式2：IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)0),闰年,平年)IF/AND/OR综合应用PMOD：返回两数相除的余数。结果的正负号与除数相同
10、。F 语法 MOD(number,divisor) Number 为被除数，Divisor 为除数1717.P“数学与三角函数”包括正弦函数、余弦函数、取整函数等FSUM函数是Excel中使用最多的函数，利用它进行求和运算可以忽略存有文本、空格等数据的单元格，语法简单、使用方便。相信这也是大家最先学会使用的Excel函数之一。但是实际上，Excel所提供的求和函数不仅仅只有SUM一种，还包括SUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2几种函数。第三章：数学和三角函数与求和有关的函数1818.P返回某一单元格区域中所有
11、数字之和。F 语法： SUM(number1,number2, .) Number1, number2, . 为 1 到 30 个需要求和的参数。F说明：直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。请参阅下面的示例三。 如果参数为错误值或为不能转换成数字的文本，将会导致错误。 F示例：SUM函数第三章：数学和三角函数1919.第三章：数学和三角函数SUMIF函数P根据指定条件对若干单元格求和。F语法：SUMIF(range,criteria,sum_range)-Range 为
12、用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。Sum_range 是需要求和的实际单元格。F说明：只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格才求和。如果忽略了 sum_range，则对区域中的单元格求和。-Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额
13、返回销售红利），则可使用 IF 函数。2020.第三章：数学和三角函数SUM与SUMIF案例2121.第三章：数学和三角函数利用SUM进行多条件统计注意：数组公式在输入完成后需按组合键 Ctrl+Shift+Enter加大括号，而不能手动加入2222.第三章：数学和三角函数P返回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该列表进行修改。F 语法: SUBTOTAL(function_num,ref1,ref2, .) F 说明：SUBTOTAL分类汇总序号Function_n
14、um （包含隐藏值）Function_num （忽略隐藏值） 函数 11 AVERAGE 101 AVERAGE 22 COUNT 102 COUNT 33 COUNTA 103 COUNTA 44 MAX 104 MAX 55 MIN 105 MIN 66 PRODUCT 106 PRODUCT 77 STDEV 107 STDEV 88 STDEVP 108 STDEVP 99 SUM 109 SUM 1010 VAR 110 VAR 1111 VARP 111 VARP 2323.第三章：数学和三角函数P在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。F语法 SUMPRODU
15、CT(array1,array2,array3, .)FArray1, array2, array3, . 为 2 到 30 个数组，其相应元素需要进行相乘并求和。F说明- 数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 - 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。 SUMPRODUCT2424.第三章：数学和三角函数利用SUMPRODUCT跨行求和PROW：返回引用的行号。ROW(reference)PCOLUMN：返回给定引用的列标。 COLUMN(reference) FReference 为需要得到其行号的单元格或单
16、元格区域。2525.第三章：数学和三角函数SUM/SUMIF/SUMPRODUCT/SUBTOTA综合应用SUBTOTAL使用SUM使用SUMIF使用SUMIF使用SUM使用SUMPRODUCT使用2626.第三章：数学和三角函数ROUND函数P返回某个数字按指定位数取整后的数字。F语法：ROUND(number,num_digits)-Number 需要进行四舍五入的数字。Num_digits 指定的位数，按此位数进行四舍五入。F说明：如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits
17、 小于 0，则在小数点左侧进行四舍五入。F案例：2727.第三章：数学和三角函数INT函数P将数字向下舍入到最接近的整数。F语法：INT(number)-Number 需要进行向下舍入取整的实数。F示例：2828.第四章：文本/日期/时间函数2、显示时间 1）Now：显示当前系统的日期和时间; 如何只显示当前时间？ =now()-today() 语法：NOW( ) 或 TODAY() 2）Time：显示指定数值的时间 语法：time(hour,minute,second)2929.第四章：文本/日期/时间函数3、显示日期/时间的部分字段值 1）Today：显示当前系统的日期; 2）Date：显
18、示指定数值的日期； 3）Year：得到日期的年数 4）Month：得到日期的月数 5）Day：得到日期的天数4、如何确定指定日期星期几 Weekday函数 1）语法：weekday(serial_number,return_type) 2）return_type类型 1或省略：返回数字1（周日）到数字7（周六） 2：返回数字1（周一）到数字7（周日） 3：返回数字0（周一）到数字6（周日）3030.第四章：文本/日期/时间函数5、如何连接多个字符串？ 1）用运算符“&”连接； 2）用CONCATENATE函数连接；语法：concatenate（text1,text2text30） 6、如何转换
19、文本大小写？ 1）LOWER：所有大写转换为小写； 语法：lower（text） 2）UPPER：所有小写转换为大写；语法：upper（text） 3）PROPER：首字母及任何非字母后的首字段转换大写，其他字母转换为小写 语法：proper（text）3131.第四章：文本/日期/时间函数7、如何取出单元格中的部分字符？ 1）Left：从左边开始截取指定数目的字符； 语法：left（text,num_chars） 2）Right：从右边开始截取指定数目的字符；语法：right（text,num_chars） 3）Mid：从文本指定位置开始截取指定数目的字符 语法：mid（text,start
20、_num,num_chars） 4) Len: 返回文本字符串中的字符数。 语法LEN(text) 3232.第四章：文本/日期/时间函数3333.第四章：文本/日期/时间函数DATAVALUE：将字符 串表示的日期字符转换为数字序列；VALUE：将一个代表数字的字符转换为数字TEXT：根据指定的格式将数值转换为文本3434.第五章 信息函数P IS类函数是指用来检验数值或引用类型的工作表函数，在Excel中一共有九个此类函数。就几个函数包括：（1）ISBLANK 如果值为空，则返回 TRUE（2）ISERR 如果值为除 #N/A 以外的任何错误值，则返回 TRUE（3）ISERROR 如果值
21、为任何错误值，则返回 TRUE（4）ISLOGICAL 如果值为逻辑值，则返回 TRUE（5）ISNA 如果值为 #N/A 错误值，则返回 TRUE（6）ISNONTEXT 如果值不是文本，则返回 TRUE（7）ISNUMBER 如果值为数字，则返回 TRUE（8）ISREF 如果值为引用，则返回 TRUE（9）ISTEXT 如果值为文本，则返回 TRUE IS类函数3535.显示的错误代码含义建议纠错步骤#DIV/0!公式中除数为0或空单元格单击公式中的除数,确认其没有引用空单元格.对单元格添加错误处理语句=IF()或如本章前面所讲的条件格式#N/A公式中参数没有赋予有效值#N/A意为数值无
22、效.使用LOOKUP函数检查是否有问题.#NAME?公式包含非法文本,它不是有效函数,也不是有效工作表中定义的名称你可能将函数名或区域名拼写错误.仔细检查公式.自然语言公式中,该错误意为Excel不能识别一个或两个标志.#NULL!引用两个无交叉区域的交叉点你谋略通过使用无公共单元格的列或行标志计算公式.选择新行或列或两者的标志.#REF!公式包含不正确的引用你是不是删除了最初引用公式的单元格或区域?#VALUE!公式中包含错误类型的参数你可能在公式中混淆了两种不相容的数据类型,比如,添加文本到数值性参数中.第五章 信息函数3636.第五章 信息函数ISERROR3737.第六章：查询与引用函
23、数在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。VLOOKUP( lookup_value, table_array, col_index_num, range_lookup) P参数：Lookup_value代表需查找的值；Table_array代表查找的区域；Col_index_num为查询区域中待返回的匹配值的列序号；range_lookup为true进行模糊查找，为false则是精确查找，末找到相关值则返回#N/A.P特别提醒：Lookup_value必须在查找区域的首列中； Vlookup函数 3838.如果 range_lookup 为 FALSE，tabl
24、e_array 不必进行排序 为需要在数组第一列中查找的数值 为需要在其中查找数据的数据表 为 table_array 中待返回的匹配值的列序号 Vlookup函数实例第六章：查询与引用函数3939.几个查找函数的比较 1）Vlookup函数 2）Hlookup函数 3）Lookup函数 第六章：查询与引用函数4040.第六章：查询与引用函数返回列表或数组中的指定行序号和列序号的索引值。 语法：Index(array, row_num, column_num) P参数：Array代表单元格区域或数组常量；Row_num表示指定的行序号；Column_num表示指定的列序号(两项必选一项)P特别
25、提醒：此处的行参数和列参数是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号。P例：公式：=INDEX(A1:E5,3,4)，显示出A1至E5单元格区域中，第3行和第4列交叉处的单元格（即D3）中的内容。 Index函数 4141.第六章：查询与引用函数Match函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。 语法：MATCH( lookup_value, lookup_array, match_type) P参数：Lookup_value代表需要查找的数值；Lookup_array表示查找的区域；Match_type表示查找方式的值（-1、0或1）。P特别提醒：Lookup_array只能为一列或一行。 4242.第六章：查询与引用函数Match与Index组合使用(嵌套) 说明：Match 函数用来返回所查数据的位置信息，Index 函数用来返回指定位置的数据。4343.第六章：查询与引用函数IF、ISERROR、VLOOKUP、MATCH、INDEX综合应用制作风险抽出评价表4444.第七章：统计函数.5/20/2022Thank you very much!E-Mail:gongchao_.hk
