EXCEL技能培训

ByMike2020/4/2221、概述2、基本操作3、公式与函数4、数据管理5、Q&A2020/4/223Excel的常用功能1Excel的工作界面2常用按钮编辑栏快速访问工具栏功能区状态栏数据有效性设置和预警提醒1数据保护设置2数据整理3其他工作技巧462020/4/2271）数据有效性设置2）设置预警提醒3）案例演示4）练习题2020/4/2281)对表格中一些需要输入的内容，设置输入限制，比如只能输入整数、只能在下拉列表中选择指定内容等步骤:点功能区中的”数据”选项→选数据有效性→点设置→在有效性条件中根据需要选择2020/4/2292)对于表格中的一些特殊数据设置特别提醒，比如当日期进入预警期间时自动变为红字醒目提示步骤:点功能区中的开始选项→选条件格式→突出显示单元格格式→-然后根据需要选择(比如将当日前一个月的日期自动显示为蓝底红字)案例演示2020/4/22114)练习题:①将定单跟踪表中的定单重量设置为必须是大于零的数值②将定单跟踪表中的定单号长度限制为六位数③在定单跟踪表中,当定单重量大于10的时候,单元格显示为蓝底红字2020/4/22121）对表格设置“保护”操作，使该表格不能被其他人修改；2）将工作表隐藏，并且限制其他人无法取消该隐藏。3)有选择得保护工作表,开放部分区域允许修改4）案例演示5)练习题2020/4/22131)对表格设置“保护”操作，使该表格不能被别人修改步骤:点功能区中的”审阅”选项→选保护工作表→输入保护密码2020/4/22142)将工作表隐藏，并且限制别人无法取消隐藏步骤:点功能区中的审阅选项→选保护工作簿→在结构项上打钩,然后输入保护密码→-别人将不能将该工作表隐藏或取消原来的隐藏,如果是在窗口项上打钩的话别人将不能保存该文件2020/4/22153)有选择得保护工作表,开放部分区域允许修改步骤:点功能区中的“审阅”选项→点”允许用户编辑区域”→选择需要编辑的区域然后选“保护工作表”→输入密码案例演示2020/4/22175)练习题:1、对定单跟踪表进行保护,不允许做输入和修改,但可以对客户代码,定单重量,实际到达时间进行输入和修改2020/4/22181）数据定位与查找2）数据筛选3）删除重复数据4）区域的定义5）案例演示2020/4/22191)使用excel的“定位”功能，用以查找一些特殊条件和格式的内容步骤:按F5功能键→弹出定位框→可以在引用位置选项中输入具体单元格如A1551或者点定位条件按钮进行各类选择2020/4/22202)对表格内数据进行按任意条件组合筛选相关信息的操作步骤:将光标点到数据的标题行→点功能区中的数据选项→点筛选→在相应的筛选字段中选择需要的字段名字上打钩→然后选择下一个字段选择，依此类推，可做几重组合筛选2020/4/22213)快速的将一列数据中重复的项找出来并自动删除点功能中的数据选项→选删除重复项-然后根据需要选择要删除的重复的字段→如果要有多重组合条件根据要求选择2020/4/22224)将表格内的某些内容、区域、公式等，定义为自定义的“名称”，以方便其他处理(比如快速定位,公式引用步骤:用鼠标拖动选择要定义区域,然后在编辑栏的名称框里输入名称即可.对于已经定义好的区域可以点到名称框里选择相应的区域,既可以快速转到该区域案例演示自动显示表格内的所有公式函数隐藏自己定义的公式函数制作可以直接勾选的调研问卷各种快捷键的应用其他工作技巧及快捷键的应用242020/4/22251)将表格内的所有公式函数都自动显示出来按Ctrl+~键盘可以将公式函数都显示出来2020/4/22262)将自己定义的公式函数隐藏起来不让别人看到步骤:点功能的”开始”→格式→设置单元格格式--点保护---选隐藏然后再将工作表设置为保护状态2020/4/22273)制作让别人可以直接在表格上勾选的调研问卷，并能够自动统计调研结果步骤:点功能区的开发工具→选插入控件→选表单控件里面的复选框→然后点右键选设置控件格式→在控制中输入链接的单元格,然后用计函数来统计即可2020/4/2228Ctrl+home:文件头Ctrl+end:文件尾Ctrl+pagedown:到后一个sheetCtrl+pageup:到前一个sheetCtrl+C:复制Ctrl+V:粘贴Ctrl+X:剪切Ctrl+F:查找和替换End+↓:当前位置到本列的最下一个非空单元格End+↑：当前位置到本列的最上一个非空单元格End+→：当前位置到本行的最右边一个非空单元格式End+←当前位置到本行的最左一个非空单元格Ctrl+Tab:可以在两个Excel文件之间切换4)各类快捷键的应用2020/4/2229公式与运算符1常用函数22020/4/22301）公式的建立2）运算符3）绝对引用,相对引用,混合引用2020/4/22311）公式的建立公式的格式:“=“+表达式公式的元素:常数,引用(单元格地址,区域地址或名称),函数,运算符如:=A1+A2*2(数值型单元相加)=A1&A2(字符型单元相加)2020/4/22322）运算符算术运算符:负-,百分号%,乘除*/,加减+-文本运算符:&比较运算符:=,,,=,=,2020/4/22333）相对引用和绝对引用相对引用:=D2*F2公式复制时,X轴和Y轴方向的引用地址都会相应改变绝对引用:=D2*$F$2公式复制时,X轴和Y轴方向引用地址都固定不变混合引用:=A$2公式复制引用时,Y轴方向引用地址不变,X轴方向引用地址相应改变混合引用:=$A2公式复制引用时,X轴方向引用地址不变,Y轴方向引用地址相应改变2020/4/22342020/4/22351）数学函数取绝对值：=ABS（数值）——返回给定数值的绝对值。例：=ABS（-25)的返回值是25取整：=INT（数值)——返回数值向下取整后的整数例：=INT（21.6)的返回值是21四舍五入：=ROUND（数值，数位)——按指定的位数对数值进行四舍五入例：=ROUND（25.23,1)的返回值是25.2向下舍入数字:=ROUNDDOWN(数值,数位)——按指定的位数对数值进行向下舍入例：=ROUNDDOWN（25.26,1)的返回值是25.20向上舍入数字:=ROUNDUP(数值,数位)——按指定的位数对数值进行向上舍入例：=ROUNDUP（25.26,1)的返回值是25.30普通求和：=SUM（A1：B10)——统计单元格区域中所有数值之和条件求和:=SUMIF(Range,Criteria,Sum_range)——对满足条件的单元格求和组合条件求和:=SUMIFS(Sum_range,criteria_range,criteria,…)——对一组给定条件的单元格求和2020/4/22362）统计函数数值单元计数：=COUNT（A1:B10）——返回所选区域中数值型数据的个数非空单元计数：=COUNTA（A1:B10）——返回所选区域中非空单元格的个数有条件计数：=COUNTIF（A1:B10,条件）——返回所选区域中满足给定条件的单元的个数求平均值：=AVERAGE（A1：B10）——返回所选区域中一系列数值的平均值求最大值：=MAX（A1：B10）——返回所选区域中一系列数值的最大值求最小值：=MIN（A1：B10）——返回所选区域中一系列数值的最小值2020/4/22373）条件函数条件函数：=IF（判断条件，参数1，参数2）——符合判断条件时返回参数1，不符合则返回参数2多重条件判断：=IF（判断条件1，参数1，IF（判断条件2，参数2，参数3））……例：IF(E23,3,IF(E2=5,4,IF(E2=8,5,6)))2020/4/22384）文本函数字符串长度：=LEN（字符串）——返回字符串所含字符的个数，LEN和LENB的区别在于对汉字和全角的处理上前者是视为一个字符，后者视为两个字符举例：=LEN(“英语”)返回2=LENB(“英语”)返回4取子字符串：=MID（正文，起始，子字符串长度）——从字符串中选取子字符串例：=MID（“310107198901031280”，7，4）返回的字符串就是”1989”从字符串右边取字符：RIGHT（字符串，数字）——从文本字符串的右边开始返回指定个数的字符串从字符串左边取字符：LEFT（字符串，数字）——从文本字符串的左边开始返回指定个数的字符串例：=RIGHT(“ENGLISH”,2)返回SH=LEFT(“ENGLISH”,2)返回EN2020/4/22395）常用日期和时间函数日期函数：=TODAY（）——返回日期格式的当天日期月份函数：=MONTH（A1）——返回日期的月份值年份函数：=YEAR（A1）——返回日期的年份值日子函数：=DAY（A1）——返回日期的月份中的第几天时间函数：=NOW（）——返回日期格式的当前时间取小时数值：=HOUR（A1）——返回时间中的小时数值取分钟数值：=MINUTE（A1）——返回时间中的分钟数值取秒数值：=SECOND（A1）——返回时间中的秒数值2020/4/22406）查找与引用函数查找函数：=VLOOKUP（lookup_value,table_array,clo_index_num,Range_lookup)功能：根据搜索区域内最左列的值，去查找区域内其他列的数据，并返回该列的数据lookup_value：为需要在数据表首列进行搜索的值Table_array：为需要在其中搜索信息的数据表col_index_num：为满足条件的单元在数据表中的列的序号Range_lookup:指定在查找时是精确匹配还是大致匹配，如果是true或忽略就是精确匹配2020/4/22417）函数应用实例Sumif的应用举例Sumifs的应用举例vlookup与if函数的组合应用举例2020/4/2242Sumif的应用实例:对相同的物料进行合计2020/4/2243Sumifs的应用实例:库存进出帐统计2020/4/2244vlookup与if函数的组合实例:某个客户华南区的费用明细表,首先要建立客户的基础资料,发往每个城市的不同重量的价格表然后在运费清单里面根据每天的发运情况,只要键入客户代码,及发运重量后,就可以自动带出相应的单价和计算出总费用2020/4/2245数据透视表1制作动态图表3建立图表分析数据22020/4/2246步骤:点功能区的”插入“→选”数据透视表”,会弹出建立数据透视表框→选择建立透视表的区域→然后在数据透视字段列表中选取要统计的相应字段并拖动到相应的行和列的位置2020/4/2247步骤:用鼠标拖动选择图表的区域→点功能区的插入→图表→选择图表类型如折线对于两组数据大小相差悬殊的数据，那个较小的数据可能显示不清楚，则可以选择双轴线图来制图步骤：在完成上面的图表后，点在小的那组数据的折线上→点右键→在弹出框上选“设置数据系列格式”→在系列选项中选“次坐标轴”案例演示!