电子表格含内容两篇

培训：1夏Excel在人力资源管理中的应用数据录入01认识Excel函数02认识数据透视表03CONTENTS目录数据录入单元格的引用\数据有效性\条件格式1-1单元格的引用相对引用相对引用指公式中的单元格位置将随着公式单元格的位置而改变绝对引用绝对引用是指公式和函数中的位置是固定不变的.绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$6混合引用GO混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.例:行变列不变:$A4列变行不变:A$41-2数据的有效性

控制单元格输入文本长度GO选中区域---数据---数据有效性---允许---文本长度可防止身份证/银行卡号/手机号码输入多一位或者少一位数序列选中区域---数据---数据有效性---序列---在“来源”输入（A,B,C,D)注意逗号要半角格式禁止输入重复数据

=countif(区域,起始单元格)=1选中区域---数据---数据有效性---允许（自定义)---公式填写=COUNTIF(A:A,A1)=11-2数据的有效性如何取消对于“数据有效性”的设置？选中区域---数据---数据有效性---允许---任何值GO1-3条件格式利用突出的格式，经常能起到提醒的功能如：突出不满勤的人GO突出年龄大于30的人等如何取消条件格式？认识Excel函数Excel函数的结构/常见的几类函数/应用实例2-1.EXCEL函数的结构Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。Excel函数结构:函数名语法函数结构函数作用例子运行结果1、Left1、LEFT(text,num_chars)其中：Text

是包含要提取字符的文本字符串。Num_chars

指定要由LEFT所提取的字符数。=LEFT(A1,2)从A1最左边向右取两位2、Right2、RIGHT(text,num_chars)其中：Text

是包含要提取字符的文本字符串。Num_chars

指定希望RIGHT提取的字符数。=RIGHT(A1,2)从A1最右边向左取两位3、Mid3、MID(text,start_num,num_chars)其中：Text

是包含要提取字符的文本字符串。Start_num

是文本中要提取的第一个字符的位置。Num_chars

指定希望MID从文本中返回字符的个数。=MID(A1,2,3)从A1左边第2位开始取3位4、&（连接符号）XX&XX=A2&B2将A2和B2内容合并在一起常用函数之截取、合并类函数应用实例从身份证号码提取出生日期在C2单元格中输入=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)公式解析：MID(B2,7,4)表示提取B2单元格中从第7位开始连续4位的字符，即出生年；MID(B2,11,2)表示提取B2单元格中从第11位开始连续2位的字符，即出生月；MID(B2,11,2)表示提取B2单元格中从第13位开始连续2位的字符，即出生日；在公式中加入&可以用来连接两个文本字符串。函数名语法函数结构函数作用FIND=FIND("龙",C6)在c6中查找龙，结果显示龙在c6中的位数，查不到显示#VALUE!常用函数之查找类函数参数简单说明输入数据类型lookup_value要查找的值数值、引用或文本字符串table_array要查找的区域数据表区域col_index_num返回数据在查找区域的第几列数正整数range_lookup精确匹配/模糊匹配0或1函数VOOKUP=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)常用函数之查找类函数常用函数之查找类函数

函数VOOKUP选择区域里最好不要有合并的单元格（先“取消单元格合并”）如果单元格里有空格，有可能导不出来

（提示：函数TRIM（A1)可以去除字符串首部和尾部的空格）注意重复的名字查找区域首列必须含有查找的内容（第一个参数）介绍vookup函数与IFERROR函数组合使用(以从8月工资表导入考勤到8月考核表为例）GO=IFERROR(VLOOKUP(A4,'8月工资'!B:F,5,0),0)

如果VOOKUP函数运行后找不到对应的数据，就会显示为#N/A，IFERROR的作用就是把这些#N/A都变为第2个参数。函数名语法/例子函数结构函数作用Value=value(b1)将b1数值化Numberstring=NumberString(7890,1）运行结果：七千八百九十=NumberString(7890,2)运行结果：柒仟捌佰玖拾=NumberString(7890,3)运行结果：七八九○=numberstring(b1,2)将b1数字转为大写汉字Row=ROW(reference)其中：Reference

为需要得到其行号的单元格或单元格区域。=row(b1)求b1是第几行（值为1)Column=COLUMN(reference)=column(b1)求b1是第几列(值为2)常用函数之文本类函数函数名语法函数结构函数作用len=LEN(text)其中：Text

是要查找其长度的文本。空格将作为字符进行计数。=len(C2)计算C2的字符长度rankRANK(number,ref,order)Number

为需要找到排位的数字。Ref

为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order

为一数字，指明排位的方式。=rank(b1,b1:b10,0)=rank(b1,b1:b10,1)计算b1在b1:b10区域中从大到小排第几名；计算b1在b1:b10区域中从小到大排第几名。常用函数之文本类函数如何根据A1、B1得出C1？应用实例在C1输入=A1&"+"&B1如何根据A1得出B1和C1？在B1输入=LEFT(A1,FIND("+",A1)-1)分析：思路是从左边开始，截取“+”号前的位数。在C1输入=RIGHT(A1,LEN(A1)-FIND("+",A1))分析：思路是截取“+”号后的位数。函数名语法函数结构函数作用IFIF(logical_test,value_if_true,value_if_false)=if(a2>0,1,0)当a2>0时，显示1，否则显示0IFREEOR第一个参数一般为一个函数，当这个函数运行是个错误值时（#N/A

#DIV/0!

#N/A

#VALUE!#NAME

#NULL!

#NUM!

#REF!），运行结果等于第二个参数=iferror(vookup(A1,A:B,2,0),0)如果VLOOKUP函数运行值是个错误值的话（找不到数据），就等于0，否则，就等于VLOOKUP函数运行的值。ANDAND(logical1,logical2,...)Logical1,logical2,...

表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。=and(a3>0,b3<0)当A3>0,并且b3<0时，显示true，否则显示falseOROR(logical1,logical2,...)Logical1,logical2,...

表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。=or(a4>0,b4<0)当a4>0,或者b4<0时，都显示true，否则显示false常用函数之判断类函数分数段等级分数≥90分A80分≤分数＜90分B70分≤分数＜80分C分数＜70分D区域段自动算出对应等级=IF(K4>=90,"A",IF(K4>=80,"B",IF(K4>=70,"C","D")))嵌套了两个IF函数IF函数的应用实例函数名语法函数结构函数作用SUMIFSUMIF(range,criteria,sum_range)其中：Range

为用于条件判断的单元格区域。Criteria

为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、"32"、">32"或"apples"。Sum_range

是需要求和的实际单元格。=sumif(B:B,H1,E:E)当B列满足了条件（HI），对应的E列的单元格的数值之和GOROW求余数的函数，当第二个参数为2时，常用于判断奇偶数，第二个参数为2时，运行结果为0是偶数，1是奇数=ROW(5，2)求5除以2的余数是多少，本例的运行结果是1常用函数之求和类函数应用实例从身份证号码中提取性别（身份证倒数第2位若为偶数为女性，奇数为男性）在C2单元格中输入=IF(MOD(MID(B2,17,1),2)=1,"男","女")公式分析：MID(B2,17,1)是从身份证第17位起取1位字符；在本例即为数字5，MOD函数就是取余数的函数，在本例中就是MOD(5，2),5除以2的余数，最外面是一个IF函数，当这个余数为1时，即为奇数，奇数就是男性，否则为女性。函数名语法函数结构函数作用1、NOW=now()显示当前的日期+时间2、TODAY=today()显示今天的日期3、WEEKDAY=weekday(b1,2)判断b1为星期几4、DAY=day(b1)显示b1的号数5、MONTH=month(b1)显示b1的月数6、YEAR=year(b1)显示b1的年数常用函数之日期类函数函数名以单元格b1为2013-4-5为例的运行结果函数结构函数作用WEEKDAY6=weekday(b1,2)判断b1为星期几DAY5=day(b1)显示b1的号数MONTH4=month(b1)显示b1的月数YEAR2013=year(b1)显示b1的年数常用函数之日期类函数常用函数之日期类函数DATEDIF函数常用于计算两日期的时间差=DATEDIF(较早的日期，较晚的日期，第三个参数）第三个参数：“Y”---两日期间共多少年。“M”----两日期间共多少个月。“D”----两日期间共多少天。“MD”是两日期中天数的差。忽略日期中的月和年。“YM”是两日期中月数的差。忽略日期中的年。“YD”是两日期中天数的差。忽略日期中的年。常用函数之日期类函数举例:算两个日期间差几年、几月、几日开始时间是2012-2-21结束时间是2014-2-19C2=DATEDIF(A2,B2,"Y")D2=DATEDIF(A2,B2,"YM")E2=DATEDIF(A2,B2,"MD")F2=DATEDIF(A2,B2,"D")G2=DATEDIF(A2,B2,"M")H2=DATEDIF(A2,B2,“YD")I2=C2&"年"&D2&"个月"I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“ym”)&“个月"常用函数之日期类函数应用实例3从出生日得出年龄在D2单元格中输入=DATEDIF(C2,NOW(),“y”)因为从身份证提取出生日的C2=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)所以若直接从身份证得出年龄D2=DATEDIF(MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2),NOW(),"y")培训协议服务期限完成提醒思路：过期了可以显示“协议期限完成”没过期的，显示“没过期”=IFERROR(IF(DATEDIF(B2,TODAY(),"d")>=0,"协议期限完成",),"没过期")分析：DATEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天，如果“今天”在B2之前，就会产生错误值，如果“今天”在B2之后，这个函数就会算出B2距离“今天”有多少天。当这个天数≥0时，就会执行IF函数，显示“协议期限完成”，当DATEDIF运行是一个错误值时，这时候就执行IFERROR函数，显示“没过期”。如果我想到期前一个月就提醒呢？应用实例思路：=IFERROR(IF(DATEDIF(TODAY(),B2,"D")<=31,"少于31天后就会过期","多于31天"),"已过期")多于今天超过31天的显示“多于31天”多于今天不超过31天的显示“少于31天后就会过期”比今天早的显示“已过期”DATEDIF函数能运行，说明B2晚于今天能执行IF函数当B2晚于今天≤31天显示“少于31天后就会过期”当B2晚于今天＞31天显示“多于31天”不能运行，B2早于今天（培训期限过期了）DATEDIF函数会产生错误值IF函数也会产生错误值执行IFERROR函数显示“已过期”函数名语法函数结构函数作用1、COUNT=Count(a1:a10)计a1:a10中数字的个数2、COUNTA=Counta(a1:a10)计a1:a10中非空单元格的个数，（0也算）3、COUNTBLANK=Countblank(a1:a10)计a1:a10中空白单元格的个数常用函数之计数类函数常用函数之计数类函数COUNTIF函数COUNTIF(range,criteria)区域条件标准是一个数数的函数，可以用于数出参数1区域内，符合参数2的条件的单元格个数。如：=countif(F3:F10,“女”)统计F3:F10中女的个数。COUNTIF函数的应用实例应用实例在9月流动表中找出有相同名字的人步骤1：打开9月流动表，找出9月在职的工作表，在姓名右边新建一列，插入函数，找到COUNTIF函数，选择“确定”。COUNTIF函数的应用实例这个的意思就是在9月在职的人中只有一个叫严邦平的点击Range的框格，选中E列，点击Criteria的框格，点击E2单元格，点击“确定”。点击F2单元格，把鼠标移到F2右下角，鼠标箭头变为黑色十字的时候双击。COUNTIF函数的应用实例步骤4：选中F列---排序与筛序---筛选---点击F1左上角小箭头---去掉1前的小勾---确定GOCOUNTIF函数的应用实例统计一定区间的个数如：统计9月流动表“9月在职”工作表中，≥50岁以上的人数：=COUNTIF(F:F,">=50")20岁≤年龄＜40岁的人数：=COUNTIF(F:F,">=20")-COUNTIF(F:F,">=40")（注意：如果要统计的数据是有一个绿色的左角的，

要先选择要统计的数据，点击符号，选择

“转换为数字”，不然是统计不了个数的）GO课程大纲数据录入单元格的引用数据有效性条件格式认识Excel函数Excel函数的结构常见的几类函数应用实例认识数据透视表数据透视表是什么数据透视表的几个简单应用实例认识数据透视表数据透视表是什么/数据透视表的几个简单应用实例认识数据透视表一种快速分析数据的工具数据透视表是一种交互，交叉制作的报表，一组具有多个字段的数据进行多立体的分析汇总，用于对多种来源的数据进行汇总和分析，从而可以快速合并和比较大量数据。当数据规模比较大时，这种分析的意义就显得尤为突出。说人话前提这些数据使用数段表进行管理认识数据透视表统计9月入职的招聘来源分布统计离职原因分布个数统计各部门平均工资统计各部门学历分布情况数据透视表可以快速解决这些事情统计9月入职的招聘来源分布步骤1：打开工作表---选择数据来源区域（A1：i79）---插入---数据透视表---确定统计9月入职的招聘来源分布步骤2：把招聘来源拖到下面的“行标签”框，把序号拖到下面的“数值”框，就能快速生成下图。统计离职原因分布个数通过双击B3，出来“值字段对话框”，选择“计数”同理，可根据前面介绍的步骤在辞工名单的表格中快速做出离职原因分布的表格GO拖“离职原因”到“行标签”框，拖“序号”到“数值”框注意：要留意“数值”框，是求和，还是计数，还是其他，左图的默认是“求和”，而我们是想“计数”。这是原数据这是生成的数据透视表统计出不同部门工资总和、平均工资行标签---部门数值---求和项：工资，平均值项(值字段设置可找到）：工资想想：如果在列标签先拖入性别，再拖入最高学历，数据透视表会怎样统计各部门学历分布情况行标签---部门，列标签---最高学历数值---计数项：最高学历Excel在人力资源管理中的应用EXCEl的使用技巧.新.46EXCEL学习介绍EXCEL基本操作EXCEL函数EXCEL图表内容提纲.新.47EXCEL基本操作掌握EXCEL菜单命令用法掌握常用快捷键.新.48EXCEL基本操作单元格了解

主要了解单元格周围的小图标鼠标放在单元格边框上可以拖动单元格，放在右下角可以向上/下或者左/右复制单元格，放在右下角按住SHIFT可以插入单元格。.新.49EXCEL基本操作数据录入

1.身份证号码输入（设置单元格属性为“文本”或者前面加’）

2.分数输入（直接输1/3EXCEL默认为1月3日，应该写成0空格1/3）

3.平方、立方的输入(右键选择单元格格式，选上标、下标)4.单元格里回车（alt+enter）.新.50EXCEL基本操作查找

1.模糊查找按CTRL+F进入查找，在查找里直接输查找内容为模糊查找。

2.精确查找在查找里点开选项，将单元格匹配的勾打上为精确查找

3.值查找在查找里点开选项，查找范围选择值为值查找。（如果单元格里是公式那必须用值查找）.新.51EXCEL基本操作常用功能

1.冻结窗口光标放在我们需要固定范围旁边的单元格上，选择菜单“窗口”-“冻结窗口”。

2.排序选择“数据”-“排序”

3.筛选选择“数据”-”筛选“4.插入对象选择“插入”-“对象”

5.下拉菜单制作点到你要制作下拉菜单的单元格，选择“数据”-“有效性”，在允许框中选“序列”，在来源框中选择数据范围或者直接输入值，主要在直接输入值时要用“，”隔开。.新.52EXCEL函数什么是函数

Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。什么是公式

函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符。.新.53EXCEL函数基础概念—引用

1.认识工作表区域

EXCEL共有256列，65536行。

2.单元格引用方式

相对应用A1、B13.单元格引用分类一维引用A1:A10,A1:F1

二维引用A1:C10

三位引用sheet1：sheet3！A1.新.54EXCEL函数常用函数

SUM，SUMIF，MAX，COUNT/COUNTA/COUNTIF，ROUND/ROUNDUP/ROUNDDOWN，HEX2DEC/DEC2HEX，LEFT/RIGHT，AVERAGE，VLOOKUP，LEN，MID，SUBSTITUTE，IF，AND/OR.新.55EXCEL函数SUM用途：返回某一单元格区域中所有数字之和。语法：SUM(number1，number2，...)。参数：Number1，number2，...为1到N个需要求和的数值。用法举例：.新.56EXCEL函数SUMIF用途：根据指定条件对若干单元格、区域或引用求和。语法：SUMIF(range，criteria，sum_range)参数：Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。用法举例：.新.57EXCEL函数MAX用途：返回数据集中的最大数值。语法：MAX(number1，number2，...)参数：Number1，number2，...是需要找出最大数值的1至N个数值。用法举例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、