HR办公常用EXCEL大全(69页)1.pptx
《HR办公常用EXCEL大全(69页)1.pptx》由会员分享，可在线阅读，更多相关《HR办公常用EXCEL大全(69页)1.pptx（69页珍藏版）》请在冰豆网上搜索。
1ClerkOffice2003TrainingMaterial文员Office2003培训教材Exceltrainingmanual-IntermediateMaterialExcel培训手册中级班教材培训手册中级班教材2使用公式使用公式公式中的运算符公式中的运算符公式中的运算顺序公式中的运算顺序引用单元格引用单元格创建公式创建公式创建公式的方法创建公式的方法创建简单公式创建简单公式创建包含引用或名称的公式创建包含引用或名称的公式创建包含函数的公式创建包含函数的公式使用函数使用函数常用函数常用函数财务函数财务函数日期和时间函数日期和时间函数数学与三角函数数学与三角函数统计函数统计函数函数的输入函数的输入手工输入手工输入利用函数向导输入利用函数向导输入求和计算求和计算扩充的自动求和扩充的自动求和条件求和条件求和建立数据清单建立数据清单建立数据清单的准则建立数据清单的准则使用记录单使用记录单数据排序数据排序对数据清单进行排序对数据清单进行排序创建自定义排序创建自定义排序数据筛选数据筛选自动筛选自动筛选高级筛选高级筛选分类汇总分类汇总分类汇总的计算方法分类汇总的计算方法汇总报表和图表汇总报表和图表插入和删除分类汇总插入和删除分类汇总插入图表表示数据插入图表表示数据复杂的数据链接复杂的数据链接使用数据透视表使用数据透视表创建数据透视表创建数据透视表编辑数据透视表编辑数据透视表controlltoolboxusage（控件工具箱控件工具箱）课程大纲课程大纲3使用公式使用公式公式中的运算符公式中的运算符1.1.算术运算符算术运算符4使用公式使用公式公式中的运算符公式中的运算符2.文本运算符文本运算符“&”号号,可以将文本连接起来可以将文本连接起来.3.比较运算符比较运算符5使用公式使用公式公式中的运算符公式中的运算符4.引用运算符引用运算符例:
A4=B4+C4+C4+E4+F4,可写为:
A4=SUM（B4:
F4）6使用公式使用公式公式中的运算顺序公式中的运算顺序如果在公式中要同时使用多个运算符如果在公式中要同时使用多个运算符,则应该了则应该了解运算符的优先级解运算符的优先级.算术运算符的优先级是先乘幂运算算术运算符的优先级是先乘幂运算,再乘、除运算再乘、除运算,最后为加、减运算。
相同优先级的运算符最后为加、减运算。
相同优先级的运算符按从左到右的次序进行运算。
按从左到右的次序进行运算。
公式中出现不同类型的运算符混用时公式中出现不同类型的运算符混用时,运算次序运算次序是：
引用运算符算术运算符文本运算符。
如果是：
引用运算符算术运算符文本运算符。
如果需要改变次序需要改变次序,可将先要计算的部分括上圆括号。
可将先要计算的部分括上圆括号。
7使用公式使用公式引用单元格引用单元格1.1.相对引用相对引用相对引用指公式中的单元格位置将随着公式单元格的位置而改变相对引用指公式中的单元格位置将随着公式单元格的位置而改变2.绝对引用绝对引用绝对引用是指公式和函数中的位置是固定不变的绝对引用是指公式和函数中的位置是固定不变的.绝对引用是在列字母和行数字之前都加上美元符号”绝对引用是在列字母和行数字之前都加上美元符号”$”,如如$A$4,$C$63.混合引用混合引用混合引用是指在一个单元格引用中混合引用是指在一个单元格引用中,既有绝对引用既有绝对引用,也有相也有相对引用对引用.例例:
行变列不变行变列不变:
$A4列变行不变列变行不变:
A$48创建公式创建公式公式是指对工作表中的数据进行分析和处理的一个等式公式是指对工作表中的数据进行分析和处理的一个等式,不不仅用于算术计算仅用于算术计算,也用于逻辑运算、日期和时间的计算也用于逻辑运算、日期和时间的计算等。
等。
一个公式必须具备一个公式必须具备3个条件：
个条件：
1.1.以等式开头以等式开头;2.含有运算符和运算对象含有运算符和运算对象;3.能产生结果能产生结果公式的公式的3个基本组成部分个基本组成部分:
1.1.数值数值:
包括数字和文本包括数字和文本2.2.单元格引用单元格引用3.3.操作符操作符9创建公式创建公式创建公式的方法创建公式的方法1.1.使用编辑栏使用编辑栏2.2.直接在单元格中输入直接在单元格中输入创建创建3种类型的公式种类型的公式1.1.创建简单公式创建简单公式;2.创建包含引用或名称的公式创建包含引用或名称的公式3.创建包含函数的公式创建包含函数的公式10使用函数使用函数函数是指预定义的内置公式函数是指预定义的内置公式,可以进行数学、文本和逻辑的可以进行数学、文本和逻辑的运算或者查找工作表的信息。
运算或者查找工作表的信息。
函数是一个已经提供给用户的公式函数是一个已经提供给用户的公式,并且有一个描述性的名并且有一个描述性的名称。
称。
函数结构函数结构1.1.函数名称：
如果要查看可用函数的列表函数名称：
如果要查看可用函数的列表,可以单击一个单元格并按可以单击一个单元格并按Shift+F3键；键；2.2.参数：
参数可以是数字、文本、逻辑值（参数：
参数可以是数字、文本、逻辑值（TRUE或或FALSE）、数）、数值、错误值（如值、错误值（如#N/A）或单元格引用。
）或单元格引用。
3.3.参数工具提示：
在输入函数时参数工具提示：
在输入函数时,会出现一个带有语法和参数的工具提会出现一个带有语法和参数的工具提示。
示。
4.4.输入公式：
“插入函数”对话框。
输入公式：
“插入函数”对话框。
函数的参数函数的参数函数的参数是一个函数体中括号内的部分函数的参数是一个函数体中括号内的部分逻辑值:
FALSE:
需要完全一致TRUE:
相似值亦可转换11使用函数使用函数函数的类型函数的类型1.常用函数常用函数:
常用函数指经常使用的函数,如求和或计算算术平均值等.包括SUM,AVERAGE,ISPMT,IF,HYPERLINK,COUNT,MAX,SIN,SUMIF和PMT.它们的语法和作用如下表:
12使用函数使用函数函数的类型函数的类型2.财务函数:
财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付的金额,它们的变量紧密相互关联。
如：
PMT（Rate,Nper,Pv,Fv,Type）=PMT（0.006,360,-300000）其中,0.006是每一笔贷款项的利率,360是款项的数目,-300000是360笔款项的当前金额（在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数）注:
Rate贷款利率。
Nper该项贷款的付款总数。
Pv现值,或一系列未来付款的当前值的累积和,也称为本金。
Fv为未来值,或在最后一次付款后希望得到的现金余额,如果省略fv则假设其值为零,也就是一笔贷款的未来值为零。
Type数字0或1,用以指定各期的付款时间是在期初还是期末。
13使用函数使用函数函数的类型函数的类型3.时间和日期函数时间和日期函数:
主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY和YEAR等。
例：
函数DATA语法：
DATE（year,month,day）14在单元格中插入当前日期和时间在单元格中插入当前日期和时间插入静态的日期和时间插入静态的日期和时间当前日期选取一个单元格当前日期选取一个单元格,并按并按Ctrl+;当前时间选取一个单元格当前时间选取一个单元格,并按并按Ctrl+Shift+；当前日期和时间选取一个单元格当前日期和时间选取一个单元格,并按并按Ctrl+;,然后按空格键然后按空格键,最后最后按按Ctrl+Shift+;插入会更新的日期和时间插入会更新的日期和时间使用使用TODAY和和NOW函数完成这一工作。
函数完成这一工作。
小贴士15使用函数使用函数函数的类型函数的类型4.数学与三角函数数学与三角函数:
5.统计函数统计函数:
统计函数是用来对数据区域进行统计分析。
统计函数是用来对数据区域进行统计分析。
主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN和TUUNC等。
例：
ABS函数语法：
ABS（number）16函数是一种特殊的公式函数是一种特殊的公式,因此所有的函数要以“因此所有的函数要以“=”=”开始。
开始。
使用方法如下：
使用方法如下：
1.1.选中要输入函数的单元格选中要输入函数的单元格2.2.单击单击InsertInsert菜单中的菜单中的命令命令,弹出如图所示的对话框：
弹出如图所示的对话框：
使用函数使用函数173.在上图的对话框中,选择函数分类下面的函数类型及函数名下面的对应函数名。
4.单击确定按钮,弹出如图所示的相应的调用函数对话框：
使用函数使用函数18函数的使用函数的使用（SUM）（SUM）SUMSUM函数函数函数名称：
函数名称：
SUMSUM主要功能：
计算所有参数数值的和。
主要功能：
计算所有参数数值的和。
使用格式：
使用格式：
SUMSUM（Number1,Number2Number1,Number2）参数说明：
参数说明：
Number1Number1、Number2Number2代表需要计算的值代表需要计算的值,可以是具体可以是具体的的数值、引用的单元格（区域）、逻辑值等。
数值、引用的单元格（区域）、逻辑值等。
特别提醒：
如果参数为数组或引用特别提醒：
如果参数为数组或引用,只有其中的数字将被计算。
数组只有其中的数字将被计算。
数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式修改为：
式修改为：
=SUM（LARGE（D2:
D63,1,2,3,4,5）,=SUM（LARGE（D2:
D63,1,2,3,4,5）,则可以求出前则可以求出前55名成绩名成绩的和。
的和。
19函数的使用函数的使用（AVERAGE）（AVERAGE）函数名称：
函数名称：
AVERAGEAVERAGE主要功能：
求出所有参数的算术平均值。
主要功能：
求出所有参数的算术平均值。
使用格式：
使用格式：
AVERAGE（number1,number2,）AVERAGE（number1,number2,）参数说明：
参数说明：
number1,number2,number1,number2,：
需要求平均：
需要求平均值的数值或引用单元格（区域）值的数值或引用单元格（区域）,参数不超过参数不超过3030个。
个。
特别提醒：
如果引用区域中包含“特别提醒：
如果引用区域中包含“0”0”值单元值单元格格,则计算在内；如果引用区域中包含空白或字符单则计算在内；如果引用区域中包含空白或字符单元格元格,则不计算在内。
则不计算在内。
20函数的使用函数的使用（IF）（IF）IFIF函数函数函数名称：
函数名称：
IFIF主要功能：
根据对指定条件的逻辑判断的真假结果主要功能：
根据对指定条件的逻辑判断的真假结果,返回相对应的内容。
返回相对应的内容。
使用格式：
使用格式：
=IF（Logical,Value_if_true,Value_if_false）=IF（Logical,Value_if_true,Value_if_false）参数说明：
参数说明：
LogicalLogical代表逻辑判断表达式；代表逻辑判断表达式；Value_if_trueValue_if_true表示当判断表示当判断条件为逻辑“真（条件为逻辑“真（TRUETRUE）”时的显示内容）”时的显示内容,如果忽略返回“如果忽略返回“TRUE”TRUE”；Value_if_falseValue_if_false表示当判断条件为逻辑“假（表示当判断条件为逻辑“假（FALSEFALSE）”时的显示内）”时的显示内容容,如果忽略返回“如果忽略返回“FALSE”FALSE”。
说明说明:
函数函数IF可以嵌套七层可以嵌套七层,用用value_if_false及及value_if_true参数可以构造复杂的检测参数可以构造复杂的检测条件。
条件。
在计算参数在计算参数value_if_true和和value_if_false后，函数后，函数IF返回相应语句执行后的返回返回相应语句执行后的返回值。
值。
如果函数如果函数IF的参数包含数组，则在执行的参数包含数组，则在执行IF语句时，数组中的每一个元素都将计算。
语句时，数组中的每一个元素都将计算。
21函数的使用函数的使用（COUNTIF&SUMIF）（COUNTIF&SUMIF）COUNTIFCOUNTIF函数函数函数名称：
函数名称：
COUNTIFCOUNTIF主要功能：
统计某个单元格区域中符合指定条件的单元格数目。
主要功能：
统计某个单元格区域中符合指定条件的单元格数目。
使用格式：
使用格式：
COUNTIF（Range,Criteria）COUNTIF（Range,Criteria）参数说明：
参数说明：
RangeRange代表要统计的单元格区域；代表要统计的单元格区域；CriteriaCriteria表示指定的条件表示指定的条件表达式。
表达式。
特别提醒：
允许引用的单元格区域中有空白单元格出现特别提醒：
允许引用的单元格区域中有空白单元格出现SUMIFSUMIF函数函数函数名称：
函数名称：
SUMIFSUMIF主要功能：
计算符合指定条件的单元格区域内的数值和。
主要功能：
计算符合指定条件的单元格区域内的数值和。
使用格式：
使用格式：
SUMIFSUMIF（Range,Criteria,Sum_RangeRange,Criteria,Sum_Range）参数说明：
参数说明：
RangeRange代表条件判断的单元格区域；代表条件判断的单元格区域；CriteriaCriteria为指定条件表达式；为指定条件表达式；Sum_RangeSum_Range代表需要计算的数值所在的单元格区域。
代表需要计算的数值所在的单元格区域。
22函数的使用函数的使用（SUBTOTAL）（SUBTOTAL）SUBTOTALSUBTOTAL函数函数函数名称：
函数名称：
SUBTOTALSUBTOTAL主要功能：
返回列表或数据库中的分类汇总。
主要功能：
返回列表或数据库中的分类汇总。
使用格式：
使用格式：
SUBTOTAL（function_num,ref1,ref2,.）SUBTOTAL（function_num,ref1,ref2,.）参数说明：
参数说明：
Function_numFunction_num为为11到到1111（包含隐藏值）或（包含隐藏值）或101101到到111111（忽略隐藏值）之间的数字（忽略隐藏值）之间的数字,用来指定使用什用来指定使用什么函数在列表中进行分类汇总计算（如图么函数在列表中进行分类汇总计算（如图66）；）；ref1,ref1,ref2,ref2,代表要进行分类汇总区域或引用代表要进行分类汇总区域或引用,不超过不超过2929个。
个。
应用举例：
如图应用举例：
如图77所示所示,在在B64B64和和C64C64单元格中分别输单元格中分别输入公式：
入公式：
=SUBTOTAL（3,C2:
C63）=SUBTOTAL（3,C2:
C63）和和=SUBTOTAL103,C2:
C63）,=SUBTOTAL103,C2:
C63）,并且将并且将6161行隐藏起来行隐藏起来,确认确认后后,前者显示为前者显示为6262（包括隐藏的行）（包括隐藏的行）,后者显示为后者显示为61,61,不包括隐藏的行。
不包括隐藏的行。
特别提醒：
如果采取自动筛选特别提醒：
如果采取自动筛选,无论无论function_numfunction_num参参数选用什么类型数选用什么类型,SUBTOTAL,SUBTOTAL函数忽略任何不包括在筛选函数忽略任何不包括在筛选结果中的行；结果中的行；SUBTOTALSUBTOTAL函数适用于数据列或垂直区域函数适用于数据列或垂直区域,不适用于数据行或水平区域。
不适用于数据行或水平区域。
23函数的使用函数的使用（VLOOKUP）（VLOOKUP）VLOOKUPVLOOKUP函数函数函数名称：
函数名称：
VLOOKUPVLOOKUP主要功能主要功能:
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
行中指定列处的数值。
使用格式使用格式:
VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）:
VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）参数说明：
参数说明：
Lookup_valueLookup_value为需要在数组第一列中查找的数值。
为需要在数组第一列中查找的数值。
Lookup_valueLookup_value可可以为数值、引用或文本字符串。
以为数值、引用或文本字符串。
Table_arrayTable_array为需要在其中查找数据的数据表。
可以使用对区域或区域为需要在其中查找数据的数据表。
可以使用对区域或区域名称的引用，例如数据库或列表。
名称的引用，例如数据库或列表。
如果如果range_lookuprange_lookup为为TRUETRUE，则，则table_arraytable_array的第一列中的数值必须按的第一列中的数值必须按升序排列：
、升序排列：
、-2-2、-1-1、00、11、22、-Z-Z、FALSEFALSE、TRUETRUE；否则，函；否则，函数数VLOOKUPVLOOKUP不能返回正确的数值。
如果不能返回正确的数值。
如果range_lookuprange_lookup为为FALSEFALSE，table_arraytable_array不必进行排序。
不必进行排序。
通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。
通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。
Table_arrayTable_array的第一列中的数值可以为文本、数字或逻辑值。
的第一列中的数值可以为文本、数字或逻辑值。
文本不区分大小写。
文本不区分大小写。
24函数的使用函数的使用（VLOOKUP）（VLOOKUP）VLOOKUPVLOOKUP函数函数Col_index_numCol_index_num为为table_arraytable_array中待返回的匹配值的列序号。
中待返回的匹配值的列序号。
Col_index_numCol_index_num为为11时，返回时，返回table_arraytable_array第一列中的数值；第一列中的数值；col_index_numcol_index_num为为22，返回，返回table_arraytable_array第二列中的数值，以此类推。
第二列中的数值，以此类推。
如果如果col_index_numcol_index_num小于小于11，函数，函数VLOOKUPVLOOKUP返回错误值值返回错误值值#VALUE!
#VALUE!
；如果如果col_index_numcol_index_num大于大于table_arraytable_array的列数，函数的列数，函数VLOOKUPVLOOKUP返回错误值返回错误值#REF!
#REF!
。
Range_lookupRange_lookup为一逻辑值，指明函数为一逻辑值，指明函数VLOOKUPVLOOKUP返回时是精确匹配还是近似匹返回时是精确匹配还是近似匹配。
如果为配。
如果为TRUETRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于则返回小于lookup_valuelookup_value的最大数值；如果的最大数值；如果range_valuerange_value为为FALSEFALSE，函数，函数VLOOKUPVLOOKUP将返回精确匹配值。
如果找不到，则返回错误值将返回精确匹配值。
如果找不到，则返回错误值#N/A#N/A。
说明说明如果函数如果函数VLOOKUPVLOOKUP找不到找不到lookup_valuelookup_value，且，且range_lookuprange_lookup为为TRUETRUE，则使用小，则使用小于等于于等于lookup_valuelookup_value的最大值。
的最大值。
如果如果lookup_valuelookup_value小于小于table_arraytable_array第一列中的最小数值，函数第一列中的最小数值，函数VLOOKUPVLOOKUP返回返回错误值错误值#N/A#N/A。
如果函数如果函数VLOOKUPVLOOKUP找不到找不到lookup_valuelookup_value且且range_lookuprange_lookup为为FALSEFALSE，函数，函数VLOOKUPVLOOKUP返回错误值返回错误值#N/A#N/A。
25函数的使用函数的使用（DATE&TRUE）（DATE&TRUE）DATEDATE函数函数函数名称：
函数名称：
DATEDATE主要功能：
给出指定数值的日期。
主要功能：
给出指定数值的日期。
使用格式：
使用格式：
DATE（year,month,day）DATE（year,month,day）参数说明：
参数说明：
yearyear为指定的年份数值为指定的年份数值（小于（小于99999999）；）；monthmonth为指定的月份数值为指定的月份数值（可以大于（可以大于1212）；）；dayday为指定的天数。
为指定的天数。
应用举例：
在单元格中输入公式：
应用举例：
在单元格中输入公式：
=DATE（2003,13,35）,=DATE（2003,13,35）,确认后确认后,显示出显示出2004-2004-2-42-4。
特别提醒：
由于上述公式中特别提醒：
由于上述公式中,月份为月份为13,13,多了一个月多了一个月,顺延至顺延至20042004年年11月；天月；天数为数为35,35,比比20042004年年11月的实际天数又多了月的实际天数又多了44天天,故又顺延至故又顺延至20042004年年22月月44日日。
TRUETRUE返回逻辑值返回逻辑值TRUETRUE。
语法语法TRUE（）TRUE（）说明说明可以直接在单元格或公式中键可以直接在单元格或公式中键入值入值TRUETRUE，而可以不使用此，而可以不使用此函数。
函数函数。
函数TRUETRUE主要用于与主要用于与其他电子表格程序兼容。
其他电子表格程序兼容。
26经验交流与分享经验交流与分享27课间休息课间休息55分钟分钟!
28ExcelExcel具备了数据库的一些特点具备了数据库的一些特点,可以把工作表中的数据做成一个类似可以把工作表中的数据做成一个类似数据库的数据清单数据库的数据清单,数据清单中可以使用（记录单）命令实现行的增数据清单中可以使用（记录单）命令实现行的增加加,修改修改,删除与查找等操作。
删除与查找等操作。
A.A.数据的自动计算数据的自动计算利用此功能利用此功能,我们可以进行包括求平均值、求和、求最大值、求最小我们可以进行包括求平均值、求和、求最大值、求最小值、计数等在内的数值计算。
而自动求和的计算最为简单。
其操作步骤值、计数等在内的数值计算。
而自动求和的计算最为简单。
其操作步骤如下：
如下：
选中要进行计算的区域选中要进行计算的区域;单击常用工具栏中的单击常用工
升级会员 