《Excel函数课件.ppt》由会员分享，可在线阅读，更多相关《Excel函数课件.ppt（55页珍藏版）》请在三一办公上搜索。
1、日常EXCEL函数应用,EXCEL操作界面及设置,函数与公式通用技巧,常用函数,Where is it？,EXCEL操作界面及设置,在“视图”选项卡中，通过勾选或取消勾选复选框来设置界面中显示或者隐藏编辑栏、行号列标、工作表标签等。,“公式”选项卡中，有“公式审核”、“保护”等常用函数公式。,Where is it？,右键单击工具栏“自定义快速访问工具栏”弹出自定义快速访问工具栏选项卡，可以设置自定义访问工具栏。,EXCEL操作界面及设置,EXCEL操作界面及设置,函数与公式通用技巧,常用函数,在工作表或工作簿中移动和滚动的快捷键 按下 完成箭头键向上、下、左或右移动单元格CTRL+箭头键移动
2、到当前数据区域的边缘CTRL+HOME移动到工作表的开头CTRL+END移动到工作表的最后一个单元格（右下角）CTRL+PAGE DOWN移动到工作簿中下一个工作表CTRL+PAGE UP移动到工作簿中前一个工作表CTRL+F6 或 CTRL+TAB移动到下一工作簿或窗口F5或 CTRL+G显示“定位”对话框SHIFT+F5或CTRL+F显示“查找”对话框SHIFT+F4重复上一次“查找”操作（等同于“查找下一个”）TAB在保护工作表中的非锁定单元格之间移动,函数与公式通用技巧,选择单元格、行或列的快捷键 请 按 如果要SHIFT+箭头键将选定区域扩展一个单元格宽度CTRL+SHIFT+箭头键
3、选定区域扩展到活动单元格同一行或同一列的最后一个非空白单元格SHIFT+HOME将选定区域扩展到行首CTRL+SHIFT+HOME将选定区域扩展到工作表的开始CTRL+SHIFT+END 将选定区域扩展到表的最后一个使用单元格CTRL+A选定整个工作表SHIFT+PAGE DOWN将选定区域向下扩展一屏SHIFT+PAGE UP将选定区域向上扩展一屏,函数与公式通用技巧,插入工作表、图表、宏的快捷键 请 按如果要SHIFT+F11 或 ALT+SHIFT+F1 插入新工作表F11 或 ALT+F1创建使用当前区域的图表ALT+F8显示“宏”对话框ALT+F11显示“Visual Basic 编
4、辑器”,函数与公式通用技巧,实 例,函数与公式通用技巧,函数与公式通用技巧,限制：,突破：,函数与公式通用技巧,改变工作表的计算精度,在实际工作中，许多用户在计算数字时会设置单元格格式为2为小数的数值、货币格式或者是整数值。但在使用公式进行数据处理的过程中，经常会发现几个单元格之和与合计单元格显示的数据之和不相等。,因为excel将数值按照15位计算精度带入计算所致。如图，在excel选项中勾选“将精度设为所显示的精度”，可以解决上述情况的发生，但这样也降低了工作薄中所有工作表的计算精度，但不影响其他工作薄。此外，也可以用ROUND函数对数值进行修约后再求和。,函数与公式通用技巧,分步查看公式
5、计算结果,方法一：选中包含公式的单元格，单击“公式”选项卡中的“公式求值”按钮，弹出“公式求值”对话框，然后多次单击“求值”按钮，“求值”域中将按照公式计算的顺序逐步地显示公式的计算过程。,方法二：功能键除了可以重算工作薄外，还可以用于查看公式的运算结果。当一个函数公式比较长时，可以用键对各个部分进行分段计算，以便公式差错和更正。在分段选择时，注意要包含一个完整的运算对象。,函数与公式通用技巧,EXCEL操作界面及设置,函数与公式通用技巧,常用函数,函数语法 由函数名+括号+参数组成 例：求和函数:SUM(A1,B2,)参数与参数之间用逗号“,”表示,常用函数,1.条件函数IF、IFERROR
6、,根据对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果。可以使用 IF 对数值和公式执行条件检测。Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算某单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 和 COUNTIFS 电子表格函数。若要计算基于某区域内一个文本字符串或一个数值的总和，可使用 SUMIF 和 SUMIFS 工作表函数。,常用函数,ISERROR()括号中为：#N/A、#VALUE、#REF、#DIV/0、#NUM、#NAME？或#NULL时为TRUE ISNA()括号中为：#N/A时为TRUE
7、常与IF嵌套使用,条件函数IF、IFERROR,常用函数,=IFERROR(value,value_if_error),如果公式计算出错误则返回您指定的值；否则返回公式结果。使用 IFERROR 函数来捕获和处理公式中的错误。Value 是需要检查是否存在错误的参数。Value_if_error 是公式计算出错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。,条件函数IF、IFERROR,常用函数,2.逻辑函数AND、OR,所有参数的计算结果为 TRUE 时，返回 TRUE；只要有一个参数的计算结果为 FALS
8、E，即返回 FALSE。通过 AND 函数可以检验多个不同的条件，而不仅仅是一个条件。,=AND(logical1,logical2,.),=OR(logical1,logical2,.),在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。,注意：参数的计算结果必须是逻辑值（如 TRUE 或 FALSE），而参数必须是包含逻辑值的数组如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的单元格区域未包含逻辑值，则 函数将返回错误值#VALUE!。,常用函数,逻辑函数 AND(条件)括号中条件值均为TRUE，则
9、为TRUE；如果任一个为FALSE，则为FALSE OR(条件)括号中条件值均为FALSE，则为FALSE；如果任一个为TRUE，则为TRUE NOT(条件)括号中条件值为FALSE，则为TRUE；如果为TRUE，则为FALSE,常用函数,3.统计函数LARGE、SMALL,=LARGE(array,k),返回数据集中第 k 个最大值。使用此函数可以根据相对标准来选择数值。Array 为需要从中选择第 k 个最大值的数组或数据区域。K 为返回值在数组或数据单元格区域中的位置（从大到小排）。,注解如果数组为空，函数 LARGE 返回错误值#NUM!。如果 k 0 或 k 大于数据点的个数，函数
10、LARGE 返回错误值#NUM!。如果区域中数据点的个数为 n，则函数 LARGE(array,1)返回最大值，函数 LARGE(array,n)返回最小值。,=SMALL(array,k)返回数据集中第 k 个最小值。,常用函数,4.统计函数MAX、MIN,=MAX(number1,number2,.),返回一组值中的最大值。注解参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数不包含数字，函数 MAX 返回 0（零）。如果参数为错误
11、值或为不能转换为数字的文本，将会导致错误。如果要使计算包括引用中的逻辑值和代表数字的文本，请使用 MAXA 函数。,=MIN(number1,number2,.)返回一组值中的最小值。=MEDIAN(number1,number2,.)返回给定数值的中值。中值是在一组数值中居于中间的数值。=MODE(number1,number2,.)返回在某一数组或数据区域中出现频率最多（众数）的数值。,常用函数,5.统计函数AVERAGE,=TRIMMEAN(array,percent)返回数据集的内部平均值。函数 TRIMMEAN 先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分
12、析中剔除一部分数据的计算时，可以使用此函数。,=AVERAGE(number1,number2,.),返回参数的平均值（算术平均值）。,注释：参数可以是数字或者是包含数字的名称、单元格区域或单元格引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。若要在计算中包含引用中的逻辑值和代表数字的文本，请使用 AVERAGEA 函数。若要只对符合某些条件的值计算平均值，请使用 AVERAGEIF 函数或 AVERAGEIFS 函数。,常用
13、函数,AVERAGE(数值1,数值2,)A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000=AVERAGE（A1：A3）=200=AVERAGE（A1：A3，B1）=105,统计函数AVERAGE,常用函数,6.统计函数AVERAGEIF,=AVERAGEIF(range,criteria,average_range),注解：忽略区域中包含 TRUE 或 FALSE 的单元格。如果 average_range 中的单元格为空单元格，AVERAGEIF 将忽略它。如果 range 为空值或文本值，则 AVERAGEIF 会返回#DIV0!错误值。如果条
14、件中的单元格为空单元格，AVERAGEIF 就会将其视为 0 值。如果区域中没有满足条件的单元格，则 AVERAGEIF 会返回#DIV/0!错误值。您可以在条件中使用通配符，即问号(?)和星号(*)。Average_range 不必与 range 的大小和形状相同。求平均值的实际单元格是通过使用 average_range 中左上方的单元格作为起始单元格，然后加入与 range 的大小和形状相对应的单元格确定的。,range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。criteria是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。
15、例如，条件可以表示为 32、32、32、苹果 或 B4。average_range是要计算平均值的实际单元格集。如果忽略，则使用 range。,常用函数,求和:SUM SUM(数值1,数值2,)A1=6 A2=7 A3=8=SUM(6,8)=14=SUM(A1,A3)=6+8=14=SUM(A1:A3)=6+7+8=14,7.数学函数SUM,常用函数,数学函数SUM,=SUM(number1,number2,.),说明直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的示例一和示例二。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽
16、略。请参阅下面的第三个示例。如果参数为错误值或为不能转换为数字的文本，将会导致错误。,常用函数,条件求和:SUMIF SUMIF(范围,条件,要求和范围)A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000=SUMIF(A1:A3,“=200”,B1:B3)=100=SUMIF(A1:A3,“=200”,C1:C3)=5000,8.数学函数SUMIF,常用函数,9.统计函数COUNT,=COUNT(value1,value2,.),注解如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如 1），则将被计算在内。逻辑值和直接键入到参数列表中
17、代表数字的文本被计算在内。如果参数为错误值或不能转换为数字的文本，则不会被计算在内。如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计算数组或引用中的空单元格、逻辑值、文本或错误值。若要计算逻辑值、文本值或错误值的个数，请使用 COUNTA 函数。若要只计算符合某一条件的数字的个数，请使用 COUNTIF 函数或 COUNTIFS 函数。,常用函数,求个数:COUNT COUNT(数值1,数值2,)A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000=COUNT(A1:A3)=3=COUNT(B1:B3)=3,=COUNTIF(range,c
18、riteria)计算某个区域中满足给定条件的单元格数。例如：=COUNTIF(A2:A7,=20),常用函数,求符合条件个数:COUNTIF COUNTIF(范围,条件)A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000=COUNTIF(A1:A3,“=200”）=2=COUNTIF(A1:A3,“200”）=1,10.统计函数COUNTIF,常用函数,11.文本函数LEFT、MID、RIGHT,=LEFT(text,num_chars)=MID(text,start_num,num_chars)=RIGHT(text,num_chars),Text
19、 是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars 指定希望函数从文本中返回字符的个数。,根据所指定的字符数，LEFT 返回文本字符串中第一个字符或前几个字符。,MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。,RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。,常用函数,文本函数截取函数LEFT RIGHT MID LEFT(文本,数值)从左边截取 RIGHT(文本,数值)从右边截取 MID(文本,开始位，数值)从中间截取=LEFT（“ab
20、cdef”,2）=ab=RIGHT（“abcdef”,2）=ef=MID（“abcdef”,2,3）=bcd,常用函数,12.文本函数CONCATENATE,=CONCATENATE(text1,text2,.),将两个或多个文本字符串合并为一个文本字符串。,注解您也可使用连接符号(&)计算运算符代替 CONCATENATE 函数来连接文本项。例如=A1&B1 与=CONCATENATE(A1,B1)返回相同的值。,常用函数,计算字符长度LEN LEN(文本)计算字符串的长度=LEN（“abcdef”）=6合并字符函数CONCATENATE 或&CONCATENATE(文本1，)合并字符串=C
21、ONCATENATE（“a”,“ef”）=aef,常用函数,将数值转化为文本TEXT TEXT(数值,参数)将数值转化为文本,参数一般为0.=TEXT（1234,0）=1234还可用于补0,常用函数,在字符串中查找特定字符FIND FIND(文本,范围,数值)查找一个字符在另一个字符串中的位置 数值表示查找第几个.=FIND（“a”,“abcaef”,1）=1=FIND（“a”,“abcaef”,2）=4,常用函数,比较两个字符是否完全相符EXACT EXACT(文本1,文本2)比较两个字符是否完全相符,是则为TRUE,否则为FALSE.=EXACT（“a”,“a”）=TRUE=EXACT（“
22、a”,“ab”）=FALSE=EXACT（“a”,“A”）=FALSE,常用函数,文本函数SUBSTITUTE,=SUBSTITUTE(text,old_text,new_text,instance_num),Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。Old_text 为需要替换的旧文本。New_text 用于替换 old_text 的文本。Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则将用 new_text 替换 Text 中出现
23、的所有 old_text。,在文本字符串中用 new_text 替代 old_text。,常用函数,数学函数RAND、RANDBETWEEN,=RAND(),注解若要生成 a 与 b 之间的随机实数，请使用：RAND()*(b-a)+a如果要使用函数 RAND 生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9，将公式永久性地改为随机数。,返回大于等于 0 及小于 1 的均匀分布随机实数，每次计算工作表时都将返回一个新的随机实数。,=RANDBETWEEN(bottom,top),返回位于指定的两个数之间的一个随机整数。每次计算工作表时
24、都将返回一个新的随机整数。Bottom函数 RANDBETWEEN 将返回的最小整数。Top函数 RANDBETWEEN 将返回的最大整数。,常用函数,统计函数RANK,=RANK(number,ref,order),Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。注解
25、函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。,返回一个数字在数字列表中的排位。,常用函数,排位:RANKRANK(数值,范围,数值)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(number,num_digits),ROUND 函数可将某个数字四舍五入为指定的
26、位数。,说明如果 num_digits 大于 0（零），则将数字四舍五入到指定的小数位。如果 num_digits 等于 0，则将数字四舍五入到最接近的整数。如果 num_digits 小于 0，则在小数点左侧进行四舍五入。若要始终进行向上舍入（远离 0），请使用 ROUNDUP 函数。若要始终进行向下舍入（朝向 0），请使用 ROUNDDOWN 函数。若要将某个数字四舍五入为指定的倍数（例如，四舍五入为最接近的 0.5 倍），请使用 MROUND 函数。,常用函数,四舍五入函数:ROUND ROUND(数值,数位(+/-)A B C 1 150.45 2899 0.2345=ROUND(C1
27、,2)=0.23=ROUNDUP(C1,2)=0.24=ROUNDDOWN(A1,1)=150.4,常用函数,引用函数ROW、COLUMN,说明：如果省略 reference，则假定是对函数所在单元格的引用。如果 reference 为一个单元格区域，并且函数作为数组输入，则函数 将以数组的形式返回 reference 的行号或列号。如果参数 reference 为一个单元格区域，并且函数不是以数组公式的形式输入的，则 COLUMN 函数将返回最上侧的行号或最左侧列的列号。Reference 不能引用多个区域。,=ROW(reference),=ROWS(array)返回引用或的行数。=COL
28、UMNS(array)返回数组或引用的列数。,=COLUMN(reference),常用函数,查找函数MATCH,=MATCH(lookup_value,lookup_array,match_type),MATCH 函数可在单元格区域（区域:工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）中搜索指定项，然后返回该项在单元格区域中的相对位置。,常用函数,引用函数INDEX,数组形式:=INDEX(array,row_num,column_num)引用形式:=INDEX(reference,row_num,column_num,area_num),Array 为单元格区域或数组常量。
29、Reference 对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须将其用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数 row_num 或 column_num 分别为可选项。Row_num 引用中某行的行号，函数从该行返回一个引用。Column_num 引用中某列的列标，函数从该列返回一个引用。Area_num 选择引用中的一个区域，返回该区域中 row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，则函数 INDEX 使用区域 1。,常用函数,引用函数INDEX,常用函
30、数,查找函数OFFSET,=OFFSET(reference,rows,cols,height,width),以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。,Reference 作为偏移量参照系的引用区域。Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。行数可为正数或负数。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。列数可为正数或负数。Height 高度，即所要返回的引用区域的行数。Height 必须为正数。Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。,常用函数
31、,查找函数VLOOKUP、HLOOKUP,=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),Lookup_value为需要在表格数组第一列中查找的数值。Lookup_value 可以为数值或引用。Table_array为两列或多列数据。使用对区域或区域名称的引用。table_array 第一列中的值是由 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。Col_index_num为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 tabl
32、e_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。Range_lookup为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值：如果为 TRUE 或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。table_array 第一列中的值必须以升序排序；否则 VLOOKUP 可能无法返回正确的值。如果为 FALSE，VLOOKUP 将只寻找精确匹配值。在此情况下，table_array 第一列的值不需要排序。如果 table_array 第一列
33、中有两个或多个值与 lookup_value 匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。,常用函数,查找与引用函数:查找表格中的值以列的方式VLOOKUP Vlookup(文本,范围,列,FALSE)文本-条件 范围-条件所在的列 列-范围中对应列用数值表示 FALSE-精确查找另：HLOOKUP纵向查找,常用函数,查找函数VLOOKUP,常用函数,查找函数HLOOKUP,=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),常用函数,日期函数返回日期序列年份YEAR返回日期序列月份MONTH返回日期序列特定天DATE返回系统今天日期TODAY返回系统今天日期与时间NOW返回对应日期的星期数WEEKDAY,常用函数,条件格式可以输入公式或数值最多可以设三种条件格式，加无条件时的格式，即根据一个单元格的内容最多可自动设置四种格式,常用函数,
