HR常用EXCEL技巧大全

1、Excel training manual- Intermediate Material Excel培训手册培训手册 中级班教材中级班教材2使用公式使用公式公式中的运算符公式中的运算符公式中的运算顺序公式中的运算顺序引用单元格引用单元格创立公式创立公式 创立公式的方法创立公式的方法创立简单公式创立简单公式创立包含引用或名称的公式创立包含引用或名称的公式创立包含函数的公式创立包含函数的公式使用函数使用函数常用函数常用函数财务函数财务函数日期和时间函数日期和时间函数数学与三角函数数学与三角函数统计函数统计函数函数的输入函数的输入手工输入手工输入利用函数向导输入利用函数向导输入求和计算求和计算扩充的

2、自动求和扩充的自动求和条件求和条件求和建立数据清单建立数据清单建立数据清单的准那么建立数据清单的准那么使用记录单使用记录单数据排序数据排序对数据清单进行排序对数据清单进行排序创立自定义排序创立自定义排序数据筛选数据筛选自动筛选自动筛选高级筛选高级筛选分类汇总分类汇总分类汇总的计算方法分类汇总的计算方法汇总报表和图表汇总报表和图表插入和删除分类汇总插入和删除分类汇总插入图表表示数据插入图表表示数据复杂的数据链接复杂的数据链接使用数据透视表使用数据透视表创立数据透视表创立数据透视表编辑数据透视表编辑数据透视表controll toolbox usage (控件工具箱控件工具箱)课程大纲课程大纲3使

3、用公式使用公式公式中的运算符公式中的运算符 算术运算符算术运算符算术运算符号算术运算符号运算符含义运算符含义示示 例例+(加号加号)加加2+3=5-(减号减号)减减3-1=2*(星号星号乘乘3*2=6/(斜杠斜杠)除除6/2=3%(百分号百分号)百分号百分号50%(脱字号脱字号)乘方乘方43=43=644使用公式使用公式公式中的运算符公式中的运算符2. 文本运算符文本运算符 “&号号,可以将文本连接起来可以将文本连接起来.3. 比较运算符比较运算符比较运算符比较运算符运算符含义运算符含义示示 例例=(等号等号)相等相等B1=C1,若若B1中单元格内的值确实与中单元格内的值确实与C1中的值相中的

4、值相等等,则产生逻辑真值则产生逻辑真值TRUE,若不相等若不相等,则产生逻辑则产生逻辑假值假值FALSE(小于号小于号)小于小于B1(大于号大于号)大于大于B1C1,若若B1中数值为中数值为6,C1中数值为中数值为4,则条件成则条件成立产生逻辑真值立产生逻辑真值TRUE,否则产生逻辑假值否则产生逻辑假值FALSE=(大于等于号大于等于号)大于等于大于等于B1=C1(不等号不等号)不等于不等于B1C1=(小于等于号小于等于号)小于等于小于等于B1=C15使用公式使用公式公式中的运算符公式中的运算符4. 引用运算符引用运算符引用运算符引用运算符运算符含义运算符含义示示 例例:区域运算符区域运算符,

5、产生对包产生对包括在丙相引用之间的括在丙相引用之间的所有单元格的引用所有单元格的引用(A5:A15),联合运算符联合运算符,将多个引将多个引用合并为一个引用用合并为一个引用SUM(A5:A15,C5:C15)(空格空格)交叉运算符交叉运算符,产生对两产生对两个引用构有的单元格个引用构有的单元格的引用的引用(B7:D7 C6:C8)例:A4=B4+C4+C4+E4+F4,可写为: A4=SUM(B4:F4)6使用公式使用公式公式中的运算顺序公式中的运算顺序 如果在公式中要同时使用多个运算符如果在公式中要同时使用多个运算符,那么应该了解运那么应该了解运算符的优先级算符的优先级.算术运算符的优先级是

6、先乘幂运算算术运算符的优先级是先乘幂运算,再乘、除再乘、除运算运算,最后为加、减运算。相同优先级的运算符按从左到右的最后为加、减运算。相同优先级的运算符按从左到右的次序进行运算。次序进行运算。 公式中出现不同类型的运算符混用时公式中出现不同类型的运算符混用时,运算次序是：引运算次序是：引用运算符用运算符算术运算符算术运算符文本运算符。如果需要改变次文本运算符。如果需要改变次序序,可将先要计算的局部括上圆括号。可将先要计算的局部括上圆括号。7使用公式使用公式 引用单元格引用单元格 相对引用相对引用 相对引用指公式中的单元格位置将随着公式单元格的位相对引用指公式中的单元格位置将随着公式单元格的位置

7、而改变置而改变 2. 绝对引用绝对引用 绝对引用是指公式和函数中的位置是固定不变的绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号绝对引用是在列字母和行数字之前都加上美元符号$,如如$A$4,$C$6 3. 混合引用混合引用 混合引用是指在一个单元格引用中混合引用是指在一个单元格引用中,既有绝对引用既有绝对引用,也也有相对引用有相对引用. 例例:行变列不变行变列不变: $A4 列变行不变列变行不变:A$48创立公式创立公式 公式是指对工作表中的数据进行分析和处理的一个等式公式是指对工作表中的数据进行分析和处理的一个等式,不仅不仅用于算术计算用于算术计算

8、,也用于逻辑运算、日期和时间的计算等。也用于逻辑运算、日期和时间的计算等。 一个公式必须具备一个公式必须具备3个条件：个条件： 以等式开头以等式开头; 2. 含有运算符和运算对象含有运算符和运算对象; 3. 能产生结果能产生结果 公式的公式的3个根本组成局部个根本组成局部: 数值数值:包括数字和文本包括数字和文本 单元格引用单元格引用 操作符操作符 9创立公式创立公式 创立公式的方法创立公式的方法 使用编辑栏使用编辑栏 直接在单元格中输入直接在单元格中输入 创立创立3种类型的公式种类型的公式 创立简单公式创立简单公式; 2. 创立包含引用或名称的公式创立包含引用或名称的公式 3. 创立包含函数

9、的公式创立包含函数的公式10使用函数使用函数 函数是指预定义的内置公式函数是指预定义的内置公式,可以进行数学、文本和逻辑的运可以进行数学、文本和逻辑的运算或者查找工作表的信息。算或者查找工作表的信息。 函数是一个已经提供给用户的公式函数是一个已经提供给用户的公式,并且有一个描述性的名称。并且有一个描述性的名称。 函数结构函数结构 函数名称：如果要查看可用函数的列表函数名称：如果要查看可用函数的列表,可以单击一个单元格可以单击一个单元格并按并按Shift+F3键；键； 参数：参数可以是数字、文本、逻辑值参数：参数可以是数字、文本、逻辑值TRUE或或FALSE、数值、错误值如数值、错误值如#N/A

10、或单元格引用。或单元格引用。 参数工具提示：在输入函数时参数工具提示：在输入函数时,会出现一个带有语法和参数的会出现一个带有语法和参数的工具提示。工具提示。 输入公式：输入公式：“插入函数对话框。插入函数对话框。 函数的参数函数的参数 函数的参数是一个函数体中括号内的局部函数的参数是一个函数体中括号内的局部逻辑值: FALSE :需要完全一致 TRUE: 相似值亦可转换11使用函数使用函数函数的类型函数的类型 1. 常用函数常用函数:常用函数指经常使用的函数,如求和或计算算术平均值等.包括SUM,AVERAGE, ISPMT,IF,HYPERLINK,COUNT,MAX,SIN, SUMIF和

11、PMT.它们的语法和作用如下表:语法语法作用作用SUM(number1,number2, )返回单元格区域中所有数值的和返回单元格区域中所有数值的和ISPMT(Rate,Per,Nper,Pv)返回普通返回普通(无提保无提保)的利息偿还的利息偿还AVERAGE(Number1,Number2, )计算参数的算术平均数计算参数的算术平均数,参数可以是数值或包含数值参数可以是数值或包含数值的名称数组和引用的名称数组和引用IF(Logical_test,Value_if_true,Value_if_false)执行真假判断执行真假判断,根据对指定条件进行逻辑评价的真假根据对指定条件进行逻辑评价的真假

12、而返回不同的结果。而返回不同的结果。HYPERLINK(Link_location,Friendly_name) 创建快捷方式创建快捷方式,以便打开文档或网络驱动器或连接连以便打开文档或网络驱动器或连接连接接INTERNETCOUNT(Value1,Value2, )计算参数表中的数字参数和包含数字的单元格的个数计算参数表中的数字参数和包含数字的单元格的个数MAX(number1,number2, )返回一组数值中的最大值返回一组数值中的最大值,忽略逻辑值和文字符忽略逻辑值和文字符SIN (number)返回纪念品定角度的正弦值返回纪念品定角度的正弦值SUMIF (Range, Criteri

13、a, Sum_range)根据指定条伯对若干单元格求和根据指定条伯对若干单元格求和PMT(Rate,Nper,Pv,Fv,Type)返回在固定利率下返回在固定利率下,投资或贷款的等额分期偿还额投资或贷款的等额分期偿还额12使用函数使用函数函数的类型函数的类型 2. 财务函数财务函数:财务函数用于财务的计算财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要它可以根据利率、贷款金额和期限计算出所要支付的金额支付的金额,它们的变量紧密相互关联。它们的变量紧密相互关联。 如：如：PMT(Rate,Nper,Pv,Fv,Type) =PMT0.006,360,-300000) 其中其中,0

14、.006是每一笔贷款项的利率是每一笔贷款项的利率,360是款项的数目是款项的数目,-300000是是360笔款项的当前金额笔款项的当前金额(在计算支付金额时在计算支付金额时,表示当前和将来金额的变量至表示当前和将来金额的变量至少有一个通常是负数少有一个通常是负数)注:Rate 贷款利率。Nper 该项贷款的付款总数。Pv 现值,或一系列未来付款的当前值的累积和,也称为本金。Fv 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv那么假设其值为零,也就是一笔贷款的未来值为零。Type 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。13使用函数使用函数函数的类型函数的类型

15、3. 时间和日期函数时间和日期函数:主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。例：函数DATA语法：DATE(year,month,day)14在单元格中插入当前日期和时间在单元格中插入当前日期和时间 插入静态的日期和时间插入静态的日期和时间当前日期当前日期 选取一个单元格选取一个单元格,并按并按 Ctrl+; 当前时间当前时间 选取一个单元格选取一个单元格,并按并按 Ctrl+Shift+；当前日期和时间当前日期和时间 选取一个单元格选取一个单元格,并按并按 Ctrl+;,然后按空格键然后按空格

16、键,最后按最后按 Ctrl+Shift+; 插入会更新的日期和时间插入会更新的日期和时间使用使用 TODAY 和和 NOW 函数完成这一工作。函数完成这一工作。15使用函数使用函数函数的类型函数的类型 4. 数学与三角函数数学与三角函数: 5. 统计函数统计函数: 统计函数是用来对数据区域进行统计分析。统计函数是用来对数据区域进行统计分析。主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。例：ABS函数语法：ABS(number)16函数是一种特殊的公式函数是一种特殊的公式, ,因此所有的函数要以因此所有的函数要以

17、“=“=开始。开始。使用方法如下：使用方法如下：1.1.选中要输入函数的单元格选中要输入函数的单元格 2. 2.单击单击InsertInsert菜单中的菜单中的 命令命令, ,弹出如下图的对话框：弹出如下图的对话框：使用函数使用函数173.在上图的对话框中,选择函数分类下面的函数类型及函数名下面的对应函数名。4. 单击确定按钮,弹出如下图的相应的调用函数对话框：使用函数使用函数18函数的使用函数的使用 (SUM)(SUM)SUMSUM函数函数函数名称：函数名称：SUMSUM主要功能：计算所有参数数值的和。主要功能：计算所有参数数值的和。使用格式：使用格式：SUMSUMNumber1,Numbe

18、r2Number1,Number2参数说明：参数说明：Number1Number1、Number2Number2代表需要计算的值代表需要计算的值, ,可以是具体的可以是具体的 数值、引用的单元格区域、逻辑值等。数值、引用的单元格区域、逻辑值等。特别提醒：如果参数为数组或引用特别提醒：如果参数为数组或引用, ,只有其中的数字将被计算。数组或只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式修改为：修改为：=SUM(LARGE(D2:D63,1,2,3,4,5),=SUM(LARGE(D2:

19、D63,1,2,3,4,5),那么可以求出前那么可以求出前5 5名成绩的和。名成绩的和。19函数的使用函数的使用 (AVERAGE)(AVERAGE)函数名称：函数名称：AVERAGEAVERAGE主要功能：求出所有参数的算术平均值。主要功能：求出所有参数的算术平均值。使用格式：使用格式：AVERAGE(number1,number2,)AVERAGE(number1,number2,)参数说明：参数说明：number1,number2,number1,number2,：需要求平均：需要求平均值的数值或引用单元格区域值的数值或引用单元格区域, ,参数不超过参数不超过3030个。个。特别提醒：如

20、果引用区域中包含特别提醒：如果引用区域中包含“0“0值单元格值单元格, ,那么计算在内；如果引用区域中包含空白或字符单元那么计算在内；如果引用区域中包含空白或字符单元格格, ,那么不计算在内。那么不计算在内。20函数的使用函数的使用 (IF)(IF)IFIF函数函数函数名称：函数名称：IFIF主要功能：根据对指定条件的逻辑判断的真假结果主要功能：根据对指定条件的逻辑判断的真假结果, ,返回相对应的内容。返回相对应的内容。使用格式：使用格式：=IF(Logical,Value_if_true,Value_if_false)=IF(Logical,Value_if_true,Value_if_fa

21、lse)参数说明：参数说明：LogicalLogical代表逻辑判断表达式；代表逻辑判断表达式；Value_if_trueValue_if_true表示当判断条件为表示当判断条件为逻辑逻辑“真真TRUETRUE时的显示内容时的显示内容, ,如果忽略返回如果忽略返回“TRUE“TRUE；Value_if_falseValue_if_false表示当判断条件为逻辑表示当判断条件为逻辑“假假FALSEFALSE时的显示内容时的显示内容, ,如果忽略返回如果忽略返回“FALSE“FALSE。说明说明: :函数函数 IF IF 可以嵌套七层可以嵌套七层, ,用用 value_if_false value

22、_if_false 及及 value_if_true value_if_true 参数可以构参数可以构造复杂的检测条件。造复杂的检测条件。 在计算参数在计算参数 value_if_true value_if_true 和和 value_if_false value_if_false 后，函数后，函数 IF IF 返回相应语句返回相应语句执行后的返回值。执行后的返回值。 如果函数如果函数 IF IF 的参数包含数组，那么在执行的参数包含数组，那么在执行 IF IF 语句时，数组中的每一个元语句时，数组中的每一个元素都将计算。素都将计算。 21函数的使用函数的使用 (COUNTIF & SUMIF

23、)(COUNTIF & SUMIF)COUNTIFCOUNTIF函数函数 函数名称：函数名称：COUNTIFCOUNTIF主要功能：统计某个单元格区域中符合指定条件的单元格数目。主要功能：统计某个单元格区域中符合指定条件的单元格数目。使用格式：使用格式：COUNTIF(Range,Criteria)COUNTIF(Range,Criteria)参数说明：参数说明：RangeRange代表要统计的单元格区域；代表要统计的单元格区域；CriteriaCriteria表示指定的条件表表示指定的条件表达式。达式。特别提醒：允许引用的单元格区域中有空白单元格出现特别提醒：允许引用的单元格区域中有空白单元

24、格出现SUMIFSUMIF函数函数函数名称：函数名称：SUMIFSUMIF主要功能：计算符合指定条件的单元格区域内的数值和。主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：使用格式：SUMIFSUMIFRange,Criteria,Sum_RangeRange,Criteria,Sum_Range参数说明：参数说明：RangeRange代表条件判断的单元格区域；代表条件判断的单元格区域；CriteriaCriteria为指定条件表达式；为指定条件表达式；Sum_RangeSum_Range代表需要计算的数值所在的单元格区域。代表需要计算的数值所在的单元格区域。22函数的使用函数的使

25、用(SUBTOTAL)(SUBTOTAL)SUBTOTALSUBTOTAL函数函数函数名称：函数名称：SUBTOTALSUBTOTAL主要功能：返回列表或数据库中的分类汇总。主要功能：返回列表或数据库中的分类汇总。使用格式：使用格式：SUBTOTAL(function_num, ref1, SUBTOTAL(function_num, ref1, ref2, .)ref2, .)参数说明：参数说明：Function_numFunction_num为为1 1到到1111包含隐藏值或包含隐藏值或101101到到111111忽略隐藏值之间的数字忽略隐藏值之间的数字, ,用来指定使用用来指定使用什么函

26、数在列表中进行分类汇总计算如图什么函数在列表中进行分类汇总计算如图6 6；ref1, ref2,ref1, ref2,代表要进行分类汇总区域或引用代表要进行分类汇总区域或引用, ,不超过不超过2929个。个。应用举例：如图应用举例：如图7 7所示所示, ,在在B64B64和和C64C64单元格中分别输入公单元格中分别输入公式：式：=SUBTOTAL(3,C2:C63)=SUBTOTAL(3,C2:C63)和和=SUBTOTAL103,C2:C63),=SUBTOTAL103,C2:C63),并且将并且将6161行隐藏起来行隐藏起来, ,确认后确认后, ,前者显示为前者显示为6262包括隐藏包括

27、隐藏的行的行, ,后者显示为后者显示为61,61,不包括隐藏的行。不包括隐藏的行。特别提醒：如果采取自动筛选特别提醒：如果采取自动筛选, ,无论无论function_numfunction_num参数参数选用什么类型选用什么类型,SUBTOTAL,SUBTOTAL函数忽略任何不包括在筛选结函数忽略任何不包括在筛选结果中的行；果中的行；SUBTOTALSUBTOTAL函数适用于数据列或垂直区域函数适用于数据列或垂直区域, ,不不适用于数据行或水平区域。适用于数据行或水平区域。23函数的使用函数的使用 (VLOOKUP)(VLOOKUP)VLOOKUPVLOOKUP函数函数函数名称：函数名称：VL

28、OOKUPVLOOKUP主要功能主要功能: :在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。中指定列处的数值。 使用格式使用格式: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) : VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参数说明：参数说明： Lookup_value Lookup_value 为需要在数组第一列中查找的数值。为需要在数组第

29、一列中查找的数值。Lookup_value Lookup_value 可以为数值、可以为数值、引用或文本字符串。引用或文本字符串。Table_array Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。用，例如数据库或列表。如果如果 range_lookup range_lookup 为为 TRUE TRUE，那么，那么 table_array table_array 的第一列中的数值必须按升序排的第一列中的数值必须按升序排列：列：、-2-2、-1-1、0 0、1 1、2 2、-Z

30、-Z、FALSEFALSE、TRUETRUE；否那么，函数；否那么，函数 VLOOKUP VLOOKUP 不能返不能返回正确的数值。如果回正确的数值。如果 range_lookup range_lookup 为为 FALSE FALSE，table_array table_array 不必进行排序。不必进行排序。通过在通过在“数据菜单中的数据菜单中的“排序中选择排序中选择“升序，可将数值按升序排列。升序，可将数值按升序排列。Table_array Table_array 的第一列中的数值可以为文本、数字或逻辑值。的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。文本不区分大小写。24

31、函数的使用函数的使用 (VLOOKUP)(VLOOKUP)VLOOKUPVLOOKUP函数函数Col_index_num Col_index_num 为为 table_array table_array 中待返回的匹配值的列序号。中待返回的匹配值的列序号。Col_index_num Col_index_num 为为 1 1 时，返回时，返回 table_array table_array 第一列中的数值；第一列中的数值；col_index_num col_index_num 为为 2 2，返回，返回 table_array table_array 第二列中的数值，以此类推。第二列中的数值，以此

32、类推。如果如果 col_index_num col_index_num 小于小于 1 1，函数，函数 VLOOKUP VLOOKUP 返回错误值值返回错误值值 #VALUE! #VALUE!；如果如果 col_index_num col_index_num 大于大于 table_array table_array 的列数，函数的列数，函数 VLOOKUP VLOOKUP 返回错误值返回错误值 #REF! #REF!。Range_lookup Range_lookup 为一逻辑值，指明函数为一逻辑值，指明函数 VLOOKUP VLOOKUP 返回时是精确匹配还是近似匹配。返回时是精确匹配还是近似

33、匹配。如果为如果为 TRUE TRUE 或省略，那么返回近似匹配值，也就是说，如果找不到精确匹配值，那或省略，那么返回近似匹配值，也就是说，如果找不到精确匹配值，那么返回小于么返回小于 lookup_value lookup_value 的最大数值；如果的最大数值；如果 range_value range_value 为为 FALSE FALSE，函数，函数 VLOOKUP VLOOKUP 将返回精确匹配值。如果找不到，那么返回错误值将返回精确匹配值。如果找不到，那么返回错误值 #N/A #N/A。说明说明如果函数如果函数 VLOOKUP VLOOKUP 找不到找不到 lookup_value

34、 lookup_value，且，且 range_lookup range_lookup 为为 TRUE TRUE，那么使用小于，那么使用小于等于等于 lookup_value lookup_value 的最大值。的最大值。 如果如果 lookup_value lookup_value 小于小于 table_array table_array 第一列中的最小数值，函数第一列中的最小数值，函数 VLOOKUP VLOOKUP 返回错返回错误值误值 #N/A #N/A。 如果函数如果函数 VLOOKUP VLOOKUP 找不到找不到 lookup_value lookup_value 且且 rang

35、e_lookup range_lookup 为为 FALSE FALSE，函数，函数 VLOOKUP VLOOKUP 返回错误值返回错误值 #N/A #N/A。 25函数的使用函数的使用 (DATE & TRUE)(DATE & TRUE)DATEDATE函数函数 函数名称：函数名称：DATEDATE主要功能：给出指定数值的日期。主要功能：给出指定数值的日期。使用格式：使用格式：DATE(year,month,day)DATE(year,month,day)参数说明：参数说明：yearyear为指定的年份数值为指定的年份数值小于小于99999999；monthmonth为指定的月份数为指定的月

36、份数值可以大于值可以大于1212；dayday为指定的天数。为指定的天数。应用举例：在单元格中输入公式：应用举例：在单元格中输入公式：=DATE(2003,13,35),=DATE(2003,13,35),确认后确认后, ,显示出显示出2004-2-42004-2-4。特别提醒：由于上述公式中特别提醒：由于上述公式中, ,月份月份为为13,13,多了一个月多了一个月, ,顺延至顺延至20042004年年1 1月；月；天数为天数为35,35,比比20042004年年1 1月的实际天数又多月的实际天数又多了了4 4天天, ,故又顺延至故又顺延至20042004年年2 2月月4 4日。日。TRUET

37、RUE返回逻辑值返回逻辑值 TRUETRUE。语法语法TRUE( )TRUE( )说明说明可以直接在单元格或公式中键可以直接在单元格或公式中键入值入值 TRUETRUE，而可以不使用此函，而可以不使用此函数。函数数。函数 TRUE TRUE 主要用于与其主要用于与其他电子表格程序兼容。他电子表格程序兼容。26经验交流与分享经验交流与分享27课间休息课间休息5 5分钟分钟! !28Excel Excel 具备了数据库的一些特点具备了数据库的一些特点, ,可以把工作表中的数据做成一个类似数可以把工作表中的数据做成一个类似数据库的数据清单据库的数据清单, ,数据清单中可以使用记录单命令实现行的增加数

38、据清单中可以使用记录单命令实现行的增加, ,修修改改, ,删除与查找等操作。删除与查找等操作。A.A.数据的自动计算数据的自动计算利用此功能利用此功能, ,我们可以进行包括求平均值、求和、求最大值、求最小值、我们可以进行包括求平均值、求和、求最大值、求最小值、计数等在内的数值计算。而自动求和的计算最为简单。其操作步骤如下：计数等在内的数值计算。而自动求和的计算最为简单。其操作步骤如下：选中要进行计算的区域选中要进行计算的区域; ;单击常用工具栏中的单击常用工具栏中的 Auto sum Auto sum按钮按钮, ,那么下一单元格中将出现计那么下一单元格中将出现计算的结果。算的结果。 数据的自动

39、计算与排序数据的自动计算与排序29如果要自动求平均值如果要自动求平均值, ,就可按下面的操作进行：就可按下面的操作进行：选中要进行计算的区域选中要进行计算的区域在状态栏中单击鼠标右键在状态栏中单击鼠标右键, ,弹出如下图的快捷键菜单。弹出如下图的快捷键菜单。单击单击AverageAverage命令命令, ,那么在状态栏中就会出现求均值的结果。那么在状态栏中就会出现求均值的结果。数据的自动计算与排序数据的自动计算与排序( (续续1)1)30数据的自动计算与排序数据的自动计算与排序( (续续2)2)B. B. 数据的排序数据的排序排序是根据某一指定的数据的顺序重排序是根据某一指定的数据的顺序重新对

40、行的位置进行调整新对行的位置进行调整, ,操作步骤如下：操作步骤如下：1 1单击某一字段名。单击某一字段名。2 2单击工具栏的单击工具栏的 升序按钮或升序按钮或 降序按钮降序按钮, ,那么整个数据库该列被排序。那么整个数据库该列被排序。3 3单击单击DataData菜单中的菜单中的sortsort命令命令, ,弹出弹出sortsort对话框如下图。对话框如下图。314) 4) 在在sort bysort by下面的编缉框中下面的编缉框中, ,选出需排序选出需排序的关键字的关键字, ,单击单击AscendingAscending或或DescendingDescending单单项选择框项选择框,

41、,再次单击再次单击OKOK按钮按钮, ,即可完成排序。即可完成排序。如果排序的是文本区域如果排序的是文本区域, ,那么它们的排序就是那么它们的排序就是按第一个字的拼音字母的升序或降序来排列。按第一个字的拼音字母的升序或降序来排列。数数据的自动计算与排序据的自动计算与排序( (续续3)3)32数据的筛选数据的筛选自动筛选自动筛选高级筛选高级筛选第一步第一步: 建立条件区域建立条件区域;第一行条件区域输入列标签。第一行条件区域输入列标签。说明说明: 请确保在条件区域与数据清单之间至少留了一请确保在条件区域与数据清单之间至少留了一个空白行个空白行,不能连接。不能连接。其他行条件区域输入筛选条件。其他

42、行条件区域输入筛选条件。说明说明:1.单列上具有多个条件单列上具有多个条件:如果对于某一列具有两个或多个筛选条件，那么可如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件直接在各行中从上到下依次键入各个条件;2.多列上具有单个条件多列上具有单个条件假设要在两列或多列中查找满足单个条件的数据，假设要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件请在条件区域的同一行中输入所有条件;3.某一列或另一列上具有单个条件某一列或另一列上具有单个条件假设要找到满足一列条件或另一列条件的数据，请假设要找到满足一列条件或另一列条件的数据，请在条件区域的不同

43、行中输入条件在条件区域的不同行中输入条件; 未完未完 下页续下页续33数据的筛选数据的筛选高级筛选高级筛选第一步第一步: 建立条件区域建立条件区域;输入筛选条件输入筛选条件:4.两列上具有两组条件之一两列上具有两组条件之一假设要找到满足两组条件每一组条件都包含针对假设要找到满足两组条件每一组条件都包含针对多列的条件之一的数据行，请在各行中键入条件多列的条件之一的数据行，请在各行中键入条件5.一列有两组以上条件一列有两组以上条件假设要找到满足两组以上条件的行，请用相同的列假设要找到满足两组以上条件的行，请用相同的列标包括多列。标包括多列。条件：所指定的限制查询或筛选的结果集中包含哪条件：所指定的

44、限制查询或筛选的结果集中包含哪些记录的条件。些记录的条件。34数据的筛选数据的筛选第二步：选定需要筛选的数据清单中任一单元格；第三步：选择菜单Data / Filter / Advanced Filter第四步：在“条件区域编辑框中，输入条件区域的引用，并包括条件标志。第五步：假设要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。注：如果需要将筛选结果放到另一位置，请选择Copy to another Location35数据的筛选数据的筛选请使用若要查找?（问号）任何单个字符例如，sm?th 查找“smith”和“smyth”*（星号）任何字符数例如，*east 查找“Northe

45、ast”和“Southeast”（波形符）后跟 ?、* 或 问号、星号或波形符例如，“fy91?”将会查找“fy91?”通配符通配符以下通配符可作为筛选以及查找和替换内容时的比较条件以下通配符可作为筛选以及查找和替换内容时的比较条件 :36分类汇总的概述分类汇总的概述分类汇总是对数据清单进行数据分析的一种方分类汇总是对数据清单进行数据分析的一种方法法. .Data/ SubtotalData/ Subtotal分类汇总分类汇总37确保要进行分类汇总的数据为以下格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。 单击要分类汇总的列中的单元格。在上面的例如中，应单

46、击“运动列列 B中的单元格。 单击“升序排序 或“降序排序。 在“数据菜单上，单击“分类汇总。 在“分类字段框中，单击要分类汇总的列。在上面例如中，应单击“运动列。 在“汇总方式框中，单击所需的用于计算分类汇总的汇总函数。 在“选定汇总项框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的例如中，应选中“销售列。 分类汇总分类汇总要分类汇总的列分类汇总步骤步骤:38如果想在每个分类汇总后有一个自动分页符，请选中“每组数据分页复选框。 如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请去除“汇总结果显示在数据下方复选框。 单击“确定。 注释 可再次使用“分类汇总命令来添

47、加多个具有不同汇总函数的分类汇总。假设要防止覆盖已存在的分类汇总，请去除“替换当前分类汇总复选框。分类汇总分类汇总要分类汇总的列分类汇总39删除分类汇总删除分类汇总删除分类汇总时，删除分类汇总时，Microsoft Excel 也删除分也删除分级显示以及随分类汇总一起插入列表中的所有级显示以及随分类汇总一起插入列表中的所有分页符。分页符。在含有分类汇总的列表中，单击任一单元格。在含有分类汇总的列表中，单击任一单元格。 在在“数据菜单上，单击数据菜单上，单击“分类汇总。分类汇总。 单击单击“全部删除。全部删除。 分类汇总分类汇总40删除分类汇总删除分类汇总删除分类汇总时，删除分类汇总时，Micr

48、osoft Excel 也删除分也删除分级显示以及随分类汇总一起插入列表中的所有级显示以及随分类汇总一起插入列表中的所有分页符。分页符。在含有分类汇总的列表中，单击任一单元格。在含有分类汇总的列表中，单击任一单元格。 在在“数据菜单上，单击数据菜单上，单击“分类汇总。分类汇总。 单击单击“全部删除。全部删除。 分类汇总分类汇总41 1.1.选中所需要的数据选中所需要的数据: :如图如图数据透视表的使用数据透视表的使用数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。 源数据第三季度高尔夫汇总的源值数据透视表C2 和 C8

49、中源值的汇总42数据透视表的使用数据透视表的使用( (续续1)1)何时应使用数据透视表何时应使用数据透视表如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。在上面所述报表中数字进行多种比较时，可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格，用户可以很清楚地看到单元格 F3 中第三季度高尔夫销售额是中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。由于如何通过其他运动或季度的销售额或总销售额计算出来的。由于数据透视表是交互式的，因此，您可以更改数据的视

50、图以查看更数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。多明细数据或计算不同的汇总额，如计数或平均值。 数据是如何组织的数据是如何组织的在数据透视表中，源数据中的每列或字段都成为汇总多行信息的在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，数据透视表字段。在上例中，“运动列成为运动列成为“运动字段，高运动字段，高尔夫的每条记录在单个高尔夫项中进行汇总。尔夫的每条记录在单个高尔夫项中进行汇总。数据字段如数据字段如“求和项：销售额提供要汇总的值。上述报表求和项：销售额提供要汇总的值。上述报表中的单元格中的单元

51、格 F3 包含的包含的“求和项：销售额值来自源数据中求和项：销售额值来自源数据中“运运动列包含动列包含“高尔夫和高尔夫和“季度列包含季度列包含“第三季度的每一行第三季度的每一行。43数据透视表的使用数据透视表的使用( (续续2)2)创立数据透视表创立数据透视表翻开要创立数据透视表的工作簿。翻开要创立数据透视表的工作簿。 请单击列表或数据库中的单元格。请单击列表或数据库中的单元格。在在“数据菜单上，单击数据菜单上，单击“数据透视表和数据透视图。数据透视表和数据透视图。 在在“数据透视表和数据透视图向导的步骤数据透视表和数据透视图向导的步骤 1 1 中，遵循以下指令，并单中，遵循以下指令，并单击击

52、“所需创立的报表类型下的所需创立的报表类型下的“数据透视表。数据透视表。 按向导步骤按向导步骤 2 2 中的指示进行操作。中的指示进行操作。 按向导步骤按向导步骤 3 3 中的指示进行操作，然后决定是在屏幕上还是在向导中设中的指示进行操作，然后决定是在屏幕上还是在向导中设置报表置报表版式。版式。 通常，可以在屏幕上设置报表的版式，推荐使用这种方法。只有通常，可以在屏幕上设置报表的版式，推荐使用这种方法。只有在从大型的外部数据源缓慢地检索信息，或需要设置页字段来一次一页在从大型的外部数据源缓慢地检索信息，或需要设置页字段来一次一页地检索数据时，才使用向导设置报表版式。如果不能确定，请尝试在屏地检

53、索数据时，才使用向导设置报表版式。如果不能确定，请尝试在屏幕上设置报表版式。如有必要，可以返回向导。幕上设置报表版式。如有必要，可以返回向导。44在屏幕上设置报表版式在屏幕上设置报表版式从从“数据透视表字段列表窗口中，将要在行中显示数据的字段拖到标有数据透视表字段列表窗口中，将要在行中显示数据的字段拖到标有“将行字段拖将行字段拖至此处的拖放区域。至此处的拖放区域。 如果没有看见字段列表，请在数据透视表拖放区域的外边框内单如果没有看见字段列表，请在数据透视表拖放区域的外边框内单击，并确保击，并确保“显示字段列表显示字段列表 被按下。被按下。假设要查看具有多个级别的字段中哪些明细数据级别可用，请单

54、击该字段旁的假设要查看具有多个级别的字段中哪些明细数据级别可用，请单击该字段旁的 。对于要将其数据显示在整列中的字段，请将这些字段拖到标有对于要将其数据显示在整列中的字段，请将这些字段拖到标有“将列字段拖至此处的将列字段拖至此处的拖放区域。拖放区域。 对于要汇总其数据的字段，请将这些字段拖到标有对于要汇总其数据的字段，请将这些字段拖到标有“请将数据项拖至此处的区域。请将数据项拖至此处的区域。 只有带有只有带有 或或 图标的字段可以被拖到此区域。图标的字段可以被拖到此区域。如果要添加多个数据字段，那么应按所需顺序排列这些字段，方法是：用鼠标右键单击如果要添加多个数据字段，那么应按所需顺序排列这些

55、字段，方法是：用鼠标右键单击数据字段，指向快捷菜单上的数据字段，指向快捷菜单上的“顺序，然后使用顺序，然后使用“顺序菜单上的命令移动该字段。顺序菜单上的命令移动该字段。将要用作为页字段的字段拖动到标有将要用作为页字段的字段拖动到标有“请将页字段拖至此处的区域。请将页字段拖至此处的区域。 假设要重排字段，请将这些字段拖到其他区域。假设要删除字段，请将其拖出数据透视假设要重排字段，请将这些字段拖到其他区域。假设要删除字段，请将其拖出数据透视表。表。 假设要隐藏拖放区域的外边框，请单击数据透视表外的某个单元格。假设要隐藏拖放区域的外边框，请单击数据透视表外的某个单元格。注释注释 如果在设置报表版式时

56、，数据出现得很慢，那么请单击如果在设置报表版式时，数据出现得很慢，那么请单击“数据透视表工具栏上数据透视表工具栏上的的“始终显示工程始终显示工程 来关闭初始数据显示。如果检索还是很慢或出现错误信息，请来关闭初始数据显示。如果检索还是很慢或出现错误信息，请单击单击“数据菜单上的数据菜单上的“数据透视表和数据透视图，在向导中设置报表布局。数据透视表和数据透视图，在向导中设置报表布局。数据透视表的使用数据透视表的使用( (续续) )45在向导中设置报表布局在向导中设置报表布局如果已经从向导中退出，那么请单击如果已经从向导中退出，那么请单击“数据菜单上的数据菜单上的“数据透视表和数据透视表和数据透视图

57、以返回该向导中。数据透视图以返回该向导中。在向导的步骤在向导的步骤 3 3 中，单击中，单击“布局。布局。 将所需字段从右边的字段按钮组拖动到图示的将所需字段从右边的字段按钮组拖动到图示的“行和行和“列区域中。列区域中。 对于要汇总其数据的字段，请将这些字段拖动到对于要汇总其数据的字段，请将这些字段拖动到“数据区。数据区。 将要作为页字段使用的字段拖动到将要作为页字段使用的字段拖动到“页区域中。页区域中。 如果希望如果希望 Excel Excel 一次检索一页数据，以便可以处理大量的源数据，请一次检索一页数据，以便可以处理大量的源数据，请双击页字段，单击双击页字段，单击“高级，再单击高级，再单

58、击“中选择页字段项时，检索外部数中选择页字段项时，检索外部数据源选项，再单击据源选项，再单击“确定按钮两次。该选项不可用于某些源数据，确定按钮两次。该选项不可用于某些源数据，包括包括 OLAP OLAP 数据库和数据库和“Office “Office 数据连接。数据连接。假设要重排字段，请将它们拖到其他区域。某些字段只能用于某些区域；假设要重排字段，请将它们拖到其他区域。某些字段只能用于某些区域；如果将一个字段拖动到其不能使用的区域，该字段将不会显示。如果将一个字段拖动到其不能使用的区域，该字段将不会显示。 假设要删除字段，请将其拖到图形区之外。假设要删除字段，请将其拖到图形区之外。 如果对版

59、式满意，可单击如果对版式满意，可单击“确定，然后单击确定，然后单击“完成。完成。 数据透视表的使用数据透视表的使用( (续续) )46删除数据透视表删除数据透视表单击数据透视表。单击数据透视表。 在在“数据透视表工具栏上，单击数据透视表工具栏上，单击“数据透视表，指向数据透视表，指向“选定，再选定，再单击单击“整张表格。整张表格。 在在“编辑菜单上，指向编辑菜单上，指向“去除，再单击去除，再单击“全部。全部。 注释注释 对于数据透视图报表，删除与其相关的数据透视表，将会冻结图对于数据透视图报表，删除与其相关的数据透视表，将会冻结图表，使得不可再对其进行更改。表，使得不可再对其进行更改。数据透视

60、表的使用数据透视表的使用( (续续) )47制作与使用图表制作与使用图表创立图表图表是Excel为用户提供的强大功能,通过创立各种不同类型的图表,为分析工作表中的各种数据提供更直观的表示结果。使用图表向导 下面我们先在工作薄中建立数据表如下页图所示,根据工作表中的数据,创立不同类型的图表。图表的编辑 1.插入数据标志及增加图表标题 2.改变图表的文字、颜色和图案48图49操作步骤如下： 1)选定工作表中包含所需数据的所有单元格。 2)单击常用工具栏中的图表向导按钮 ,弹出图1-16所示的对话框： 图表的使用图表的使用50图表的使用续图表的使用续1 13 3在显示的图表向导中在显示的图表向导中,