EXCEL技能培训

1、By Mike2021-12-1621、概述2、基本操作3、公式与函数4、数据管理5、Q&A2021-12-163Excel的常用功能1Excel的工作界面LOGO数据分析数据分析数据管理数据管理制表功能制表功能图表分析图表分析42021-12-165常用按钮编辑栏快速访问工具栏功能区状态栏数据有效性设置和预警提醒1数据保护设置2数据整理3其他工作技巧462021-12-1671） 数据有效性设置2）设置预警提醒3）案例演示4）练习题2021-12-1681) 对表格中一些需要输入的内容，设置输入限制，比如只能输入整数、只能在下拉列表中选择指定内容等 步骤:点功能区中的”数据”选项选数

2、据有效性点设置在有效性条件中根据需要选择 2021-12-1692)对于表格中的一些特殊数据设置特别提醒，比如当日期进入预警期间时自动变为红字醒目提示步骤:点功能区中的开始选项选条件格式突出显示单元格格式-然后根据需要选择(比如将当日前一个月的日期自动显示为蓝底红字) 案例演示案例演示2021-12-1611 4)练习题: 将定单跟踪表中的定单重量设置为必须是大于零的数值将定单跟踪表中的定单号长度限制为六位数在定单跟踪表中,当定单重量大于10的时候,单元格显示为蓝底红字2021-12-16121）对表格设置“保护”操作，使该表格不能被其他人修改；2）将工作表隐藏，并且限制其他人无法取消该隐藏。

3、3)有选择得保护工作表,开放部分区域允许修改4）案例演示5) 练习题2021-12-16131)对表格设置“保护”操作，使该表格不能被别人修改 步骤:点功能区中的”审阅”选项选保护工作表输入保护密码 2021-12-16142)将工作表隐藏，并且限制别人无法取消隐藏步骤:点功能区中的审阅选项选保护工作簿在结构项上打钩,然后输入保护密码-别人将不能将该工作表隐藏或取消原来的隐藏,如果是在窗口项上打钩的话别人将不能保存该文件2021-12-16153)有选择得保护工作表,开放部分区域允许修改步骤:点功能区中的“审阅”选项点”允许用户编辑区域” 选择需要编辑的区域然后选“保护工作表”输入密码 案例演

4、示案例演示2021-12-1617 5)练习题:1、对定单跟踪表进行保护,不允许做输入和修改,但可以对客户代码, 定单重量,实际到达时间进行输入和修改 2021-12-16181 1）数据定位与查找）数据定位与查找2 2）数据筛选）数据筛选3 3）删除重复数据）删除重复数据4 4）区域的定义）区域的定义5 5）案例演示）案例演示2021-12-16191)使用excel的“定位”功能，用以查找一些特殊条件和格式的内容步骤:按F5功能键弹出定位框可以在引用位置选项中输入具体单元格如A1551或者点定位条件按钮进行各类选择2021-12-16202)对表格内数据进行按任意条件组合筛选相关信息的操作

5、步骤:将光标点到数据的标题行点功能区中的数据选项点筛选在相应的筛选字段中选择需要的字段名字上打钩然后选择下一个字段选择，依此类推，可做几重组合筛选2021-12-16213)快速的将一列数据中重复的项找出来并自动删除点功能中的数据选项选删除重复项-然后根据需要选择要删除的重复的字段如果要有多重组合条件根据要求选择2021-12-16224)将表格内的某些内容、区域、公式等，定义为自定义的“名称”，以方便其他处理(比如快速定位,公式引用步骤:用鼠标拖动选择要定义区域,然后在编辑栏的名称框里输入名称即可.对于已经定义好的区域可以点到名称框里选择相应的区域,既可以快速转到该区域 案例演示案例演示 自

6、动显示表格内的所有公式函数隐藏自己定义的公式函数制作可以直接勾选的调研问卷各种快捷键的应用242021-12-16251)将表格内的所有公式函数都自动显示出来按Ctrl+键盘可以将公式函数都显示出来2021-12-16262)将自己定义的公式函数隐藏起来不让别人看到步骤:点功能的”开始”格式设置单元格格式-点保护-选隐藏 然后再将工作表设置为保护状态2021-12-16273) 制作让别人可以直接在表格上勾选的调研问卷，并能够自动统计调研结果步骤:点功能区的开发工具选插入控件选表单控件里面的复选框然后点右键选设置控件格式在控制中输入链接的单元格,然后用计函数来统计即可2021-12-1628C

7、trl+home :文件头Ctrl+end :文件尾Ctrl+page down:到后一个sheetCtrl+page up:到前一 个sheetCtrl+C: 复制Ctrl+V: 粘贴Ctrl+X: 剪切Ctrl+F: 查找和替换End+:当前位置到本列的最下一个非空单元格End+：当前位置到本列的最上一个非空单元格End+：当前位置到本行的最右边一个非空单元格式End+当前位置到本行的最左一个非空单元格Ctrl+Tab :可以在两个Excel文件之间切换4) 各类快捷键的应用2021-12-1629公式与运算符1常用函数22021-12-16301）公式的建立2）运算符3）绝对引用,相对引

8、用,混合引用2021-12-16311）公式的建立公式的格式: “=“+表达式公式的元素:常数,引用(单元格地址,区域地址或名称),函数,运算符如: =A1+A2*2 (数值型单元相加) =A1&A2 (字符型单元相加)2021-12-16322）运算符算术运算符: 负-,百分号%,乘除*/,加减+-文本运算符: &比较运算符: =,=,=,2021-12-16333）相对引用和绝对引用相对引用: =D2*F2 公式复制时,X轴和Y轴方向的引用地址都会相应改变绝对引用: =D2*$F$2 公式复制时, X轴和Y轴方向引用地址都固定不变混合引用: =A$2 公式复制引用时, Y轴

9、方向引用地址不变,X轴方向 引用地址相应改变混合引用: =$A2 公式复制引用时, X轴方向引用地址不变,Y轴方向 引用地址相应改变2021-12-16342021-12-16351）数学函数取绝对值：=ABS（数值）返回给定数值的绝对值。例：=ABS（-25)的返回值是25取整： =INT（数值)返回数值向下取整后的整数例：=INT（21.6)的返回值是21四舍五入：=ROUND（数值，数位)按指定的位数对数值进行四舍五入例：=ROUND（25.23,1)的返回值是25.2向下舍入数字: =ROUNDDOWN(数值,数位)按指定的位数对数值进行向下舍入例：=ROUNDDOWN（25.26,1

10、)的返回值是25.20向上舍入数字: =ROUNDUP(数值,数位)按指定的位数对数值进行向上舍入例：=ROUNDUP（25.26,1)的返回值是25.30普通求和： =SUM（A1：B10)统计单元格区域中所有数值之和条件求和: =SUMIF(Range,Criteria,Sum_range) 对满足条件的单元格求和组合条件求和: =SUMIFS(Sum_range,criteria_range,criteria,) 对一组给定条件的单元格求和2021-12-16362）统计函数数值单元计数： =COUNT（A1:B10）返回所选区域中数值型数据的个数非空单元计数： =COUNTA（A1:B

11、10）返回所选区域中非空单元格的个数有条件计数： =COUNTIF（A1:B10,条件）返回所选区域中满足给定条件的单元的个数求平均值： =AVERAGE（A1：B10）返回所选区域中一系列数值的平均值求最大值： =MAX（A1：B10）返回所选区域中一系列数值的最大值 求最小值： =MIN（A1：B10）返回所选区域中一系列数值的最小值2021-12-16373）条件函数条件函数：=IF（判断条件，参数1，参数2）符合判断条件时返回参数1，不符合则返回参数2多重条件判断：=IF（判断条件1，参数1，IF（判断条件2，参数2，参数3）例：IF(E23,3,IF(E2=5,4,IF(E2=8,5

12、,6)2021-12-16384）文本函数字符串长度：=LEN（字符串）返回字符串所含字符的个数，LEN和LENB的区别在于对汉字和全角的处理上前者是视为一个字符，后者视为两个字符举例：=LEN(“英语”)返回2 2=LENB(“英语”)返回4 4取子字符串：=MID（正文，起始，子字符串长度）从字符串中选取子字符串例：=MID（，7，4）返回的字符串就是”1989”1989”从字符串右边取字符：RIGHT（字符串，数字）从文本字符串的右边开始返回指定个数的字符串从字符串左边取字符：LEFT（字符串，数字）从文本字符串的左边开始返回指定个数的字符串例：=

13、RIGHT(“ENGLISH”,2)返回SHSH =LEFT(“ENGLISH”,2)返回ENEN2021-12-16395）常用日期和时间函数日期函数： =TODAY（）返回日期格式的当天日期月份函数： =MONTH（A1）返回日期的月份值年份函数： =YEAR（A1）返回日期的年份值日子函数： =DAY（A1）返回日期的月份中的第几天时间函数： =NOW（）返回日期格式的当前时间取小时数值：=HOUR（A1）返回时间中的小时数值取分钟数值：=MINUTE（A1）返回时间中的分钟数值取秒数值： =SECOND（A1）返回时间中的秒数值2021-12-16406）查找与引用函数 查找函数：=V

14、LOOKUP（lookup_value,table_array,clo_index_num,Range_lookup)功能：根据搜索区域内最左列的值，去查找区域内其他列的数据，并返回该列的数据lookup_value ： 为需要在数据表首列进行搜索的值Table_array： 为需要在其中搜索信息的数据表col_index_num：为满足条件的单元在数据表中的列的序号Range_lookup: 指定在查找时是精确匹配还是大致匹配，如果是true或忽略就是精确匹配2021-12-16417）函数应用实例 Sumif的应用举例Sumifs的应用举例vlookup与if函数的组合应用举例2021-12-1642Sumif的应用实例:对相同的物料进行合计2021-12-1643Sumifs的应用实例:库存进出帐统计2021-12-1644 vlookup与if函数的组合实例:某个客户华南区的费用明细表,首先要建立客户的基础资料,发往每个城市的不同重量的价格表然后在运费清单里面根据每天的发运情况,只要键入客户代码,及发运重量后,就可以自动带出相应的单价和计算出总费用2021-12-1645数据透视表1制作动态图表3建立图表分析数据22021-12-1646步骤:点功能区的”插入“选”数据透视表”,会弹出建立数据透视表框选择建立透