Excel函数课件

1、2 231EXCEL操作界面及设置函数与公式通用技巧常用函数Where is itWhere is it？在“视图”选项卡中，通过勾选或取消勾选复选框来设置界面中显示或者隐藏编辑栏、行号列标、工作表标签等。“公式”选项卡中，有“公式审核”、“保护”等常用函数公式。Where is itWhere is it？右键单击工具栏“自定义快速访问工具栏”弹出自定义快速访问工具栏选项卡，可以设置自定义访问工具栏。2 231EXCEL操作界面及设置函数与公式通用技巧常用函数在工作表或工作簿中移动和滚动的快捷键在工作表或工作簿中移动和滚动的快捷键 l 箭头键箭头键向上、下、左或右移动单元格向上、下、左或右移

2、动单元格l CTRL+ 箭头键箭头键移动移动到当前数据区域的边缘到当前数据区域的边缘l CTRL+HOME移动到工作表的开头移动到工作表的开头l CTRL+END移动到工作表的最后一个单元格（右下角）移动到工作表的最后一个单元格（右下角）l CTRL+PAGE DOWN移动到工作簿中下一个工作表移动到工作簿中下一个工作表l CTRL+PAGE UP移动移动到工作簿中前一个工作表到工作簿中前一个工作表l CTRL+F6 或或 CTRL+TAB移动移动到下一工作簿或窗口到下一工作簿或窗口l F5或或 CTRL+G显示显示“定位定位”对话框对话框l SHIFT+F5或或CTRL+F显示显示“查找查找

3、”对话框对话框l SHIFT+F4重复重复上一次上一次“查找查找”操作（等同于操作（等同于“查找查找下下一一个个”）l TAB在在保护工作表中的非锁定单元格之间移动保护工作表中的非锁定单元格之间移动选择单元格、行或列的快捷键选择单元格、行或列的快捷键 SHIFT+ 箭头键箭头键将将选定区域扩展一个单元格宽度选定区域扩展一个单元格宽度CTRL+SHIFT+ 箭头键箭头键选定选定区域扩展到活动单元格同一行或同区域扩展到活动单元格同一行或同一列的最后一个非空白单元格一列的最后一个非空白单元格SHIFT+HOME将将选定区域扩展到行首选定区域扩展到行首CTRL+SHIFT+HOME将将选定区域扩展到工

4、作表的开始选定区域扩展到工作表的开始CTRL+SHIFT+END 将将选定区域扩展到表的最后一个使用选定区域扩展到表的最后一个使用单单元元格格CTRL+A选定整个工作选定整个工作表表SHIFT+PAGE DOWN将将选定区域向下扩展一屏选定区域向下扩展一屏SHIFT+PAGE UP将选定区域向上扩展一屏将选定区域向上扩展一屏插入工作表、图表、宏的快捷键插入工作表、图表、宏的快捷键 SHIFT+F11 或或 ALT+SHIFT+F1 插入插入新工作表新工作表F11 或或 ALT+F1创建创建使用当前区域的图表使用当前区域的图表ALT+F8显示显示“宏宏”对话框对话框ALT+F11显示显示“Vis

5、ual Basic 编辑器编辑器”符号符号说明说明实例实例优先顺序优先顺序：_(空格)，引用运算符=SUM(A1:B10) 区域运算符=SUM(A1:B5 A4:D9) 交叉运算符，相当于=SUM(A4:B5)=RANK(A1,(A1:A10,C1:C10) 联合运算符1+ -* /算术运算符=5+7-9/3*42&文本运算符=“Excel”&”home” 返回”Excelhome”3= = =比较运算符=(A1=A2) 判断A1与A2相等=(B1”ABC”) 判断B1”ABC”=(C1=5) 判断C154括号可以改变运算的优先级别，多组括号进行嵌套是，计算顺序是由最内层的括号逐级向外进行运算

6、。符号符号说明说明实例实例*通配符，表示任何字符=COUNTIF(A:A，“北京*有限公司”) ?通配符，表示任何单个字符=COUNTIF(A:A，“北京*有限?公司”)通配符，用于解除字符的通配性=COUNTIF(A:A，”北京*有限公司”) 限制：限制：突破：突破： 使用定义名称。使用定义名称。当参数为单元格区域引用时，当参数为单元格区域引用时，可使用一对括号来包含多个单可使用一对括号来包含多个单元格区域的引用部分形成合并元格区域的引用部分形成合并区域引用作为函数参数。区域引用作为函数参数。1 1、提前将单元格格式设置为文、提前将单元格格式设置为文本后再输入数字本后再输入数字2 2、在输入

7、数字时用半角单引号、在输入数字时用半角单引号开头。开头。公式内容的长度不能超过公式内容的长度不能超过10241024个个字符；字符；公式中函数的嵌套不能超过公式中函数的嵌套不能超过7 7层；层；公式中函数的参数不能超过公式中函数的参数不能超过3030个；个；数字计算精度为数字计算精度为1515位。位。 在实际工作中，许多用户在计算在实际工作中，许多用户在计算数字时会设置单元格格式为数字时会设置单元格格式为2 2为小数的为小数的数值、货币格式或者是整数值。但在数值、货币格式或者是整数值。但在使用公式进行数据处理的过程中，经使用公式进行数据处理的过程中，经常会发现几个单元格之和与合计单元常会发现几

8、个单元格之和与合计单元格显示的数据之和不相等。格显示的数据之和不相等。 因为因为excelexcel将数值按照将数值按照1515位计算精位计算精度带入计算所致。如图，在度带入计算所致。如图，在excelexcel选项选项中勾选中勾选“将精度设为所显示的精度将精度设为所显示的精度”，可以解决上述情况的发生，但这样也降可以解决上述情况的发生，但这样也降低了工作薄中所有工作表的计算精度，低了工作薄中所有工作表的计算精度，但不影响其他工作薄。但不影响其他工作薄。 此外，也可以用此外，也可以用ROUNDROUND函数对数值函数对数值进行修约后再求和。进行修约后再求和。方法一：方法一： 选中包含公式的单元

9、格，单击选中包含公式的单元格，单击“公式公式”选项卡中的选项卡中的“公式求值公式求值”按钮，弹出按钮，弹出“公式公式求值求值”对话框，然后多次单击对话框，然后多次单击“求值求值”按钮，按钮，“求值求值”域中将按照公式计算的顺序逐步地域中将按照公式计算的顺序逐步地显示公式的计算过程。显示公式的计算过程。方法二：方法二： 功能键除了可以重算工作薄外，功能键除了可以重算工作薄外，还可以用于查看公式的运算结果。还可以用于查看公式的运算结果。 当一个函数公式比较长时，可以用当一个函数公式比较长时，可以用键对各个部分进行分段计算，以便键对各个部分进行分段计算，以便公式差错和更正。在分段选择时，注意公式差错

10、和更正。在分段选择时，注意要包含一个完整的运算对象。要包含一个完整的运算对象。按按功能键切换功能键切换2 231EXCEL操作界面及设置函数与公式通用技巧常用函数 函数语法函数语法 由由函数名函数名+括号括号+参数参数组成组成 例：例： 求和函数求和函数: SUM(A1,B2,) 参数与参数之间用逗号参数与参数之间用逗号“ ”表示表示根据对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果。可以使用 IF 对数值和公式执行条件检测。Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算某单元格区域内某个文本字符串或数字出现的次数，则可使用

11、COUNTIF 和 COUNTIFS 电子表格函数。若要计算基于某区域内一个文本字符串或一个数值的总和，可使用 SUMIF 和 SUMIFS 工作表函数。 括号中为：括号中为：#N/A、#VALUE、#REF、#DIV/0、#NUM、#NAME？或？或#NULL时为时为TRUE 括号中为：括号中为：#N/A时为时为TRUE 常与常与IF嵌套使用嵌套使用 =IFERROR=IFERROR(valuevalue,value_if_errorvalue_if_error)如果公式计算出错误则返回您指定的值；否则返回公式结果。使用 IFERROR 函数来捕获和处理公式中的错误。Value 是需要检查是

12、否存在错误的参数。 Value_if_error 是公式计算出错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!。所有参数的计算结果为 TRUE 时，返回 TRUE；只要有一个参数的计算结果为 FALSE，即返回 FALSE。通过 AND 函数可以检验多个不同的条件，而不仅仅是一个条件。=AND(=AND(logical1logical1, , logical2logical2, , .) )=OR(=OR(logical1logical1, ,logical2logical2, ,.) )在其参数组中，任何

13、一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。注意：注意：u参数的计算结果必须是逻辑值（如参数的计算结果必须是逻辑值（如 TRUE TRUE 或或 FALSEFALSE），而参数必须是包含逻辑值的数组），而参数必须是包含逻辑值的数组u如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。u如果指定的单元格区域未包含逻辑值，则如果指定的单元格区域未包含逻辑值，则 函数将返回错误值函数将返回错误值 #VALUE!#VALUE!。逻辑函数逻辑函数 括号中条件值均为括号中条件值均为

14、TRUE，则为，则为TRUE；如果；如果任一个为任一个为FALSE，则为，则为FALSE 括号中条件值均为括号中条件值均为FALSE，则为，则为FALSE；如果；如果任一个为任一个为TRUE，则为，则为TRUE 括号中条件值为括号中条件值为FALSE，则为，则为TRUE ；如果为；如果为TRUE，则为，则为FALSE=LARGE=LARGE(arrayarray,k k)返回数据集中第 k 个最大值。使用此函数可以根据相对标准来选择数值。Array 为需要从中选择第 k 个最大值的数组或数据区域。K 为返回值在数组或数据单元格区域中的位置（从大到小排）。注解注解u如果数组为空，函数如果数组为空

15、，函数 LARGE LARGE 返回错误值返回错误值 #NUM!#NUM!。u如果如果 k 0 k 0 或或 k k 大于数据点的个数，函数大于数据点的个数，函数 LARGE LARGE 返回错误值返回错误值 #NUM!#NUM!。u如果区域中数据点的个数为如果区域中数据点的个数为 n n，则函数，则函数 LARGE(array,1) LARGE(array,1) 返回最大值，函数返回最大值，函数 LARGE(LARGE(array,narray,n) ) 返回最小值。返回最小值。=SMALL(=SMALL(arrayarray, ,k k) ) 返回数据集中第 k 个最小值。=MAX(=MA

16、X(number1number1, ,number2number2, ,.) )返回一组值中的最大值。注解u参数可以是数字或者是包含数字的名称、数组或引用。 u逻辑值和直接键入到参数列表中代表数字的文本被计算在内。 u如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。u如果参数不包含数字，函数 MAX 返回 0（零）。u如果参数为错误值或为不能转换为数字的文本，将会导致错误。 u如果要使计算包括引用中的逻辑值和代表数字的文本，请使用 MAXA 函数。=MIN(=MIN(number1number1, ,number2number2, ,.) )

17、 返回一组值中的最小值。=MEDIAN(=MEDIAN(number1number1, ,number2number2, ,.) ) 返回给定数值的中值。中值是在一组数值中居于中间的数值。=MODE(=MODE(number1number1, ,number2number2, ,.) ) 返回在某一数组或数据区域中出现频率最多（众数）的数值。=TRIMMEAN(=TRIMMEAN(arrayarray, ,percentpercent) )返回数据集的内部平均值。函数 TRIMMEAN 先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用

18、此函数。=AVERAGE(=AVERAGE(number1number1, , number2number2, ,.) )返回参数的平均值（算术平均值）。注释：u参数可以是数字或者是包含数字的名称、单元格区域或单元格引用。u逻辑值和直接键入到参数列表中代表数字的文本被计逻辑值和直接键入到参数列表中代表数字的文本被计算在内。算在内。u如果区域或单元格引用参数包含文本、逻辑值或空单如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。元格，则这些值将被忽略；但包含零值的单元格将被计算在内。u如果参数为错误值或为不能转换为数字的文本，将会导致错误。u若

19、要在计算中包含引用中的逻辑值和代表数字的文本，请使用 AVERAGEA 函数。u若要只对符合某些条件的值计算平均值，请使用 AVERAGEIF 函数或 AVERAGEIFS 函数。 A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =AVERAGE（A1：A3）=200 =AVERAGE（A1：A3，B1）=105=AVERAGEIF(=AVERAGEIF(rangerange, ,criteriacriteria, ,average_rangeaverage_range) )注解：u忽略区域中包含 TRUE 或 FALSE 的单元格。 u如果 a

20、verage_range 中的单元格为空单元格，AVERAGEIF 将忽略它。u如果 range 为空值或文本值，则 AVERAGEIF 会返回 #DIV0! 错误值。u如果条件中的单元格为空单元格，AVERAGEIF 就会将其视为 0 值。u如果区域中没有满足条件的单元格，则 AVERAGEIF 会返回 #DIV/0! 错误值。u您可以在条件中使用通配符，即问号 (?) 和星号 (*)。uAverage_rangeAverage_range 不必与不必与 range range 的大小和形状相同。求平均值的实际单元格是通过使用的大小和形状相同。求平均值的实际单元格是通过使用 average_

21、rangeaverage_range 中左上方的单元中左上方的单元格作为起始单元格，然后加入与格作为起始单元格，然后加入与 range range 的大小和形状相对应的单元格确定的。的大小和形状相对应的单元格确定的。range 是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。criteria 是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为 32、32、32、苹果 或 B4。 average_range 是要计算平均值的实际单元格集。如果忽略，则使用 range。求和求和:SUM A1=6 A2=7 A3=8 =SU

22、M(6,8)=14 =SUM(A1,A3)=6+8=14 =SUM(A1:A3)=6+7+8=14=SUM(=SUM(number1number1, ,number2number2, , .) )说明u直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的示例一和示例二。u如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。请参阅下面的第三个示例。u如果参数为错误值或为不能转换为数字的文本，将会导致错误。 A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000=SUMIF(A1:A3, “=20

23、0”,B1:B3)=100=SUMIF(A1:A3, “=200”,C1:C3)=5000=COUNT(=COUNT(value1value1, , value2value2, ,.) )注解u如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如 1），则将被计算在内。u逻辑值和直接键入到参数列表中代表数字的文本被计算在内。u如果参数为错误值或不能转换为数字的文本，则不会被计算在内。u如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计算数组或引用中的空单元格、逻辑值、文本或错误值。 u若要计算逻辑值、文本值或错误值的个数，请使用 COUNTA 函数。u若要只计算符合某一

24、条件的数字的个数，请使用 COUNTIF 函数或 COUNTIFS 函数。求求个数个数:COUNT A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =COUNTIF(=COUNTIF(rangerange, ,criteriacriteria) ) 计算某个区域中满足给定条件的单元格数。例如：=COUNTIF(A2:A7,=20) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =LEFT(=LEFT(texttext, ,num_charsnum_chars) )=MID(=MID(texttex

25、t, ,start_numstart_num, ,num_charsnum_chars) )=RIGHT(=RIGHT(texttext, ,num_charsnum_chars) )Text 是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars 指定希望函数从文本中返回字符的个数。根据所指定的字符数，LEFT 返回文本字符串中第一个字符或前几个字符。MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符

26、。文本函数截取函数截取函数LEFT RIGHT MID =LEFT（ “abcdef”,2）= ab =RIGHT（ “abcdef”,2 ）=ef = MID（ “abcdef”,2,3 ）= bcd=CONCATENATE (=CONCATENATE (text1text1, ,text2text2, ,.) )将两个或多个文本字符串合并为一个文本字符串。注解u您也可使用连接符号 (&) 计算运算符代替 CONCATENATE 函数来连接文本项。例如 =A1&B1 与 =CONCATENATE(A1,B1) 返回相同的值。计算字符长度计算字符长度LEN 计算字符串的长度计算字符串的长度 =

27、LEN（ “abcdef”）=6合并字符函数合并字符函数CONCATENATE 或或 & & 合并字符串合并字符串 =CONCATENATE（ “a”, “ef”）= aef =TEXT（ 1234 ,0）= 1234 查找一个字符在另一个字符串中的位置查找一个字符在另一个字符串中的位置 =FIND（ “a”, “abcaef”,1）= 1 =FIND（ “a”, “abcaef”,2）= 4 =EXACT（ “a”, “a”）= =EXACT（ “a”, “ab”）= =EXACT（ “a”, “A”）= =SUBSTITUTE(=SUBSTITUTE(texttext, ,old_text

28、old_text, ,new_textnew_text, ,instance_numinstance_num) )Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。Old_text 为需要替换的旧文本。New_text 用于替换 old_text 的文本。Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则将用 new_text 替换 Text 中出现的所有 old_text。在文本字符串中用 new_text 替代 old_text。=RAND

29、( )=RAND( )注解u若要生成 a 与 b 之间的随机实数，请使用： RAND()*(b-a)+au如果要使用函数 RAND 生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9，将公式永久性地改为随机数。返回大于等于 0 及小于 1 的均匀分布随机实数实数，每次计算工作表时都将返回一个新的随机实数。=RANDBETWEEN(=RANDBETWEEN(bottombottom, ,toptop) )返回位于指定的两个数之间的一个随机整数。每次计算工作表时都将返回一个新的随机整数整数。Bottom 函数 RANDBETWEEN 将返回

30、的最小整数。Top 函数 RANDBETWEEN 将返回的最大整数。=RANK(=RANK(numbernumber, ,refref, ,orderorder) )Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。注解u函数 RANK 对重复数的排位相同。但重复数的存在

31、将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。返回一个数字在数字列表中的排位。 1-升序升序,0-降序降序 A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000=RANK(A1,A1:A3,1)=1=RANK(A1,A1:A3,0)=3=ROUND(=ROUND(numbernumber, , num_digitsnum_digits) )ROUND 函数可将某个数字四舍五入为指定的位数。说明u如果 num_digits 大于 0（零），则将数字四舍五入到

32、指定的小数位。u如果 num_digits 等于 0，则将数字四舍五入到最接近的整数。u如果 num_digits 小于 0，则在小数点左侧进行四舍五入。u若要始终进行向上舍入（远离 0），请使用 ROUNDUP 函数。u若要始终进行向下舍入（朝向 0），请使用 ROUNDDOWN 函数。u若要将某个数字四舍五入为指定的倍数（例如，四舍五入为最接近的 0.5 倍），请使用 MROUND 函数。 A B C 1 150.45 2899 0.2345 =ROUND(C1,2)=0.23 =ROUNDUP(C1,2)=0.24 =ROUNDDOWN(A1,1)=150.4说明：u如果省略 refer

33、ence，则假定是对函数所在单元格的引用。u如果 reference 为一个单元格区域，并且函数作为数组输入，则函数 将以数组的形式返回 reference 的行号或列号。u如果参数 reference 为一个单元格区域，并且函数不是以数组公式的形式输入的，则 COLUMN 函数将返回最上侧的行号或最左侧列的列号。uReference 不能引用多个区域。=ROW(=ROW(referencereference) )=ROWS(=ROWS(arrayarray) )返回引用或的行数。 =COLUMNS(COLUMNS(arrayarray) )返回数组或引用的列数。=COLUMN(=COLUMN

34、(referencereference) )=MATCH(=MATCH(lookup_valuelookup_value, , lookup_arraylookup_array, , match_typematch_type) )MATCH 函数可在单元格区域 （区域:工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）中搜索指定项，然后返回该项在单元格区域中的相对位置。Match_typeMatch_type行为行为1 或被省略MATCH 函数会查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列，例如：.-2, -1, 0, 1,

35、 2, ., A-Z, FALSE, TRUE。0MATCH 函数会查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列。-1MATCH 函数会查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, .2, 1, 0, -1, -2, . 等等。数组形式数组形式: =INDEX(: =INDEX(arrayarray, ,row_numrow_num, ,column_numcolumn_num) )引用形式引用形式: =INDEX(: =INDEX(re

36、ferencereference, ,row_numrow_num, ,column_numcolumn_num, , area_numarea_num ) )Array 为单元格区域或数组常量。Reference 对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须将其用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数 row_num 或 column_num 分别为可选项。Row_num 引用中某行的行号，函数从该行返回一个引用。Column_num 引用中某列的列标，函数从该列返回一个引用。Area_num 选择引用中的一个区域，返回该区域中 row_num

37、和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，则函数 INDEX 使用区域 1。=OFFSET(=OFFSET(referencereference, ,rowsrows, ,colscols, ,heightheight, ,widthwidth) )以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。行数可为正数或负数。Cols 相对于偏移量参照系的左