excel应用技能培训课件

excel应用技能培训课件excel应用技能培训课件excel应用技能培训课件课程目的一、了解excel基本功能操作和常用格式二、掌握excel操作技巧和基本公式插入运用三、灵活运用excel工具实现多种表单处理2021/3/21人有了知识，就会具备各种分析能力，明辨是非的能力。excel应用技能培训课件excel应用技能培训课件exce课程目的一、了解excel基本功能操作和常用格式二、掌握excel操作技巧和基本公式插入运用三、灵活运用excel工具实现多种表单处理2021/3/21课程目的一、了解excel基本功能操作和常用格式二、掌握ex课程目录一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCEL基础技巧二、EXCEL常用快捷键五、EXCEL常用格式2021/3/21课程目录一、EXCEL基础功能介绍三、EXCEL常用公式四、课程目录一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCEL基础技巧二、EXCEL常用快捷键五、EXCEL常用格式2021/3/21课程目录一、EXCEL基础功能介绍三、EXCEL常用公式四、1、实现分层分级显示——组合一、EXCEL基础功能介绍点击“数据”—“创建组”即可将所选中区域组合。2021/3/211、实现分层分级显示——组合一、EXCEL基础功能介绍点击“2、隐藏3、插入（快捷键：Ctrl+Shift+=）同时选定N行（或N列），右键点击“插入”，即可在所选定的最上一行（或最左一列）的前边插入N行（或N列）。在选定的行或列上点击右键选择“隐藏”即可隐藏该行或该列。同时选定被隐藏的行或列的前后相邻两行（或前后相邻两列），右键点击“取消隐藏”即可显示已经隐藏的行或列。若按CTRL同时选定不相邻的行或列再点击“插入”，则可以在每个选定的行或列前方插入一个行或列。2021/3/212、隐藏3、插入（快捷键：Ctrl+Shift+=）同时选4、查找/替换（快捷键：Ctrl+F）点击“开始”—“查找”或“替换”即可实现对工作薄中（或选定范围内）某些特定内容的查找或替换功能，可以同时选定多个工作簿一起进行。5、筛选（快捷键：Alt+D+F+F）6、冻结当你希望表头部分一直出现在视野范围内，不随右方或者下方的滑动键拖动而消失时，可对这部分内容使用冻结功能。选定所要筛选的区域，点击“数据”—“筛选”即可。筛选不能横向进行。操作：选择需冻结的区域下一行（或下一列），点击“视图”-“冻结窗格”-“冻结拆分窗格”即可。当选定单元格，点击“冻结拆分窗格”时，将对此单元格上方和左方的区域同时冻结。2021/3/214、查找/替换（快捷键：Ctrl+F）点击“开始”—“查找”7、排序点击“选项”可以选择更多排序方法点击“选项”可以选择更多排序方法点击“数据”→“排序”选择数据包含标题，按标题内容进行排序取消选择数据包含标题，按列进行排序注意：排序功能只能将选定的区域进行排序，如选择不完全则容易出现错误！2021/3/217、排序点击“选项”可以选择更多排序方法点击“选项”可以选择8、工作表保护保护工作表：点击“审阅”→“保护工作表”，在弹出的窗口中输入保护密码即可完成对工作表的保护。允许用户编辑区域：点击“设置单元格格式”→“保护”，取消“锁定”勾选→“允许用户编辑区域”→“保护工作表”。保护工作簿：点击“审阅”→“保护工作薄”。保护工作薄允许用户编辑区域保护工作表2021/3/218、工作表保护保护工作表：点击“审阅”→“保护工作表”，在弹一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCEL基础技巧二、EXCEL常用快捷键五、EXCEL常用格式课程目录2021/3/21一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCEEXCEL常用快捷键Alt+Enter作用：在同一个单元格内换行。切忌使用空格换行！Ctrl+（a/s/f/z/x/c/v）全选、保存、查找撤回、剪切、复制、粘贴F2编辑当前单元格Shift+F2插入/修改批注按一下Esc调整批注位置按两下退出编辑Ctrl+d向下复制F4重复前一步操作2021/3/21EXCEL常用快捷键Alt+Enter作用：在同一个单元格内功能

对应按键

备注

切换到下一个工作簿Alt+Tab

切换到工作簿中下一个工作表Ctrl+PageDown

切换到工作簿中上一个工作表Ctrl+PageUp

可以重复插入多张工作列表

Shift+F11

选择多个工作表的相同区域

在一张工作表中选择区域，按下Ctrl或Shift点击其他工作表

汇总多张相同格式表格的数据移动到行首Home

移动到工作表的开头

Ctrl+Home

移动到工作表的最后一个单元格

Ctrl+End

选择连续多行（多列）的末尾Ctrl+方向键

连续数据的末尾或下一段连续数据的开头选择连续多行（多列）Ctrl+Shift+方向键同上向下/向右填充序列向下填充Ctrl+D向右填充Ctrl+R复制首行/首列的公式切换显示公式与结果Ctrl+`即数字1键向左，Tab键向上查找/替换

查找Ctrl+F/Shift+F5替换

Ctrl+H

定位

F5/Ctrl+G

定位可见单元格Alt+;复制筛选后的内容查找拼写错误

F7

2021/3/21功能对应按键备注切换到下一个工作簿Alt+Tab

切换功能

对应按键

备注

撤销操作/恢复与重复操作撤销Ctrl+Z恢复与重复F4

单元格格式设置

Ctrl+1

字体加粗Ctrl+B

字体倾斜Ctrl+I

字体下划线Ctrl+U

筛选/取消筛选Ctrl+Shift+L

剪切/复制/粘贴剪切Ctrl+X复制Ctrl+C粘贴Ctrl+V

选择性粘贴Ctrl+Alt+V/Alt->E->S打开对话框后可选择粘贴方式：

V-仅数值

U-仅数值与数字格式F-仅公式R-仅公式和数字格式T-仅格式选择是否转置或跳过空单元格：E-勾选是否行列转置B-勾选是否跳过空单元格同时可选择运算方式：O-无特殊运算D-目标单元格加复制单元格S-目标单元格减复制单元格M-目标单元格乘复制单元格I-目标单元格除复制单元格2021/3/21功能对应按键备注撤销操作/恢复与重复操作撤销Ctrl课程目录一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCEL基础技巧二、EXCEL常用快捷键五、EXCEL常用格式2021/3/21课程目录一、EXCEL基础功能介绍三、EXCEL常用公式四、数学函数逻辑函数查找与引用数据透视表其他函数2021/3/21数学函数2021/3/21数学函数逻辑函数查找与引用数据透视表其他函数2021/3/21数学函数2021/3/21（1）SUM（number1,number2,…)求和函数数字区域中的单元格可以相邻或不相邻。以下图为例：公式=SUM(B3:D8)，E8单元格将对B3:D8区域内数字求和。2021/3/21（1）SUM（number1,number2,…)求和函数目的方法结果批量处理：对多列（或多行）数字同时计算选定要计算的区域，点击“求和”按钮在被选中区域下方（或右方）空白的行（或列）中输出结果在指定位置输出计算结果选定输出单元格，点击“求和”按钮，然后选择需计算的区域自动在选定的单元格套用公式、输出结果2021/3/21目的方法结果批量处理：对多列（或多行）数字同时计算选定要计算

与求和函数操作类似的还有：平均值（AVERAGE)、记数(COUNT)、最大值(MAX)、最小值(MIN)。2021/3/21与求和函数操作类似的还有：平均值（AVERAGE)、（2）条件求和：SUMIF(range,criteria)SUMIF：对区域中符合指定条件的值求和，函数参数如下：SUMIF（条件区域,求和条件,求和区域）SUMIF（range,criteria,sum_range）说明：（1）“条件计算的单元格区域”→用来进行条件比较的范围（2）“求和条件”→可以是数字、文字、比较（大于，小于），如为文字时，应用双引号将其包围(英文格式)（3）“求和区域”→要进行求和的单元格范围2021/3/21（2）条件求和：SUMIF(range,criteria)例如：“=SUMIF(D2:D99,”成都“,H2:H99)”表示对城市为“成都”的销量进行加总“=SUMIF(H2:H99,”>=10“,H2:H99)”表示对销量大等于10的部分进行加总注意：但是SUMIF条件求和局限于单个条件，若要进行多个条件下的求和可以用SUMPRODUCT实现。2021/3/21例如：注意：但是SUMIF条件求和局限于单个条件，若要进行多例1：“=SUMPRODUCT((D2:D99=”成都“)*(G2:G99>=H2:H99),G2:G99)”表示对城市为“成都”并且销量大等于目标销量的部分进行加总SUMPRODUCT：1、将把每个区域中对应单元格的数值相乘，再求和，公式写法为“=SUMPRODUCT(区域1,区域2,区域3,……)”例如：“=SUMPRODUCT(A1:A3,C1:C3)”等同于“A1*C1+A2*C2+A3*C3”2、此外还可以进行多个条件下的求和：例2：“=SUMPRODUCT((D2:D99=”成都“)*(G2:G99>=10),G2:G99)”表示对城市为“成都”并且销量大等于10的部分进行加总2021/3/21例1：“=SUMPRODUCT((D2:D99=”成都“)*数学函数逻辑函数查找与引用数据透视表其他函数2021/3/21数学函数2021/3/21IF（逻辑判断，满足条件时的值，不满足条件时的值）可以嵌套使用用法：格式含义IF(A2<=100,"预算内","超出预算")如果A2的值不超过100，返回“预算内”，否则返回“超出预算”If(A2<=100,If(A2>60,”中”,”低”),”高)如果A2值大于100，返回“高”，如果大于60且小于等于100，返回“中，如果小于60，返回低（1）IF:如果满足某一条件，则返回某一值，否则返回另一值2021/3/21IF（逻辑判断，满足条件时的值，不满足条件时的值）格式含义IIF嵌套：当需要的条件较多时，IF函数中可以建立子IF函数，如上图所示，成为嵌套。IF函数最多可嵌套7层。根据销售数据进行判断，对于销售>100判断为>100，>50判断为>50,>10判断为>10，不满足以上两个条件的判断为<102021/3/21IF嵌套：当需要的条件较多时，IF函数中可以建立子IF函数AND：所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSEOR：任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返回FALSENOT：如果逻辑值为FALSE，返回TRUE；如果逻辑值为TRUE，返回FALSE格式含义=AND(A2>A3,A2<A4)A2是否大于A3并小于A4？(FALSE)=OR(A2>A3,A2<A4)A2是否大于A3或小于A4？(TRUE)=NOT(A2+A3=24)A2加A3不等于24(FALSE)（2）AND,OR,NOT:2021/3/21AND：所有参数的逻辑值为真时，返回TRUE；只要一个参可以检验数值的类型并根据参数取值返回TRUE或FALSE格式含义ISBLANK值为空白单元格。ISTEXT值为文本。ISERROR值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）。ISLOGICAL值为逻辑值。ISNA值为错误值#N/A（值不存在）。ISNONTEXT值为不是文本的任意项（注意此函数在值为空白单元格时返回TRUE）。ISNUMBER值为数字。ISREF值为引用。（3）IS函数：2021/3/21可以检验数值的类型并根据参数取值返回TRUE或FALS数学函数逻辑函数查找与引用数据透视表其他函数2021/3/21数学函数2021/3/21说明：（1）“查找值”→需要在查找引用区域的第1列中搜索查找值（2）“查找引用区域”→是要在其中进行查找数据的数据区域

（3）“指定列号”→查找的数据区域，含第一行从左往右第几列的单元格内容；

（4）“匹配方式”→为匹配值，完全符合的值，参值为“0”，如果找不到，则出现错误值#N/AVLOOKUP:将在查找引用区域的第1列中搜索查找值，再查找引用区域指定列中对应单元格的值操作方法：公式写法为“=VLOOKUP(查找值,查找引用区域,指定列号,匹配方式”）/（lookup_value,table_array,col_index,range_lookup)（1）VLOOKUP:查找型函数2021/3/21说明：VLOOKUP:将在查找引用区域的第1列中搜索查找值，例如：在需要在“华南区大学生通讯录”中找到参加促销PK赛人员的联系方式，则可以用VLOOKUP，具体操作如下：表1：华南区大学生通讯录表2：华南区参加促销PK赛人员信息表2021/3/2129例如：表1：华南区大学生通讯录表2：华南区参加促销PK赛人员第一步：选择VLOOKUP的公式，点击确定2021/3/21

第一步：选择VLOOKUP的公式，点击确定2021/3/21第二步：选中填充位置G4，按照公式=VLOOKUP(查找值,查找引用区域,指定列号,匹配方式），确定“查找值”，点击“确定”2021/3/21第二步：选中填充位置G4，按照公式=VLOOKUP(查找值第三步：选定“查找引用”2021/3/21第三步：选定“查找引用”2021/3/21第四步：选定“指定列号”第五步：成果呈现2021/3/21第四步：选定“指定列号”第五步：成果呈现2021/3/21数学函数逻辑函数查找与引用数据透视表其他函数2021/3/21数学函数2021/3/211、数据透视表：Excel中非常强大的工具从数据中生成的一个动态汇总的报表用户可以按照任何想要的方式重新安排信息，更好地理解数据数据透视表可以使用外部数据当用户更改源数据时，数据透视表不会自动更新2021/3/21

1、数据透视表：Excel中非常强大的工具2021/3（1）数据透视表基本概念：列标签、行标签：数据透视表中拥有列、行方向的字段总计：数据透视表中一行或者一列所有单元格显示总和组：作为单一项目看待的一组项目的集合项目：字段中的一个元素，在数据透视表中作为行或列的标签显示源数据：用来创建数据透视表的数据，可以是外部数据数值区域：数据透视表中含汇总数据的单元格2021/3/212021/3/21（2）适合数据透视表的数据库数据：每行都是一条数据记录，包括数据类别和数值类别：首行是对每列数据的分类描述

适合建立数据透视表不适合建立数据透视表2021/3/21

（2）适合数据透视表的数据库适合建立数据透视表不适合建立数据（3）创建数据透视表选中数据源之后点击“插入”->“数据透视表”，键入或选定要建立数据透视表的数据源区域；选择透视表显示位置：新建工作表或现有工作表，点击“确定”。

2021/3/21

（3）创建数据透视表2021/3/21（4）设置数据透视表的属性右击数据透视表，点击“数据透视表选项”2021/3/21

（4）设置数据透视表的属性2021/3/21（5）设置数据透视表的布局将项目拖至字段列表下的不同区域2021/3/21

（5）设置数据透视表的布局2021/3/21（6）设置数据透视表的样式选中数据透视表->点击菜单“数据透视表工具”->“设计”，可以调整数据透视表的样式。2021/3/21

（6）设置数据透视表的样式2021/3/21（7）完成数据透视表设置完成后，数据透视表就会按给定的汇总方式和格式要求显示出数据。2021/3/21

（7）完成数据透视表2021/3/21举例：利用数据透视表分析数据可以通过筛选按钮，选择需要的数据

2021/3/21

举例：利用数据透视表分析数据2021/3/21数学函数逻辑函数查找与引用数据透视表其他函数2021/3/21数学函数2021/3/21（1）条件记数：COUNTIF（range,criteria)--range

用于条件计算的单元格区域。求销售额大于12000的清洁顾问人数--Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。2021/3/21（1）条件记数：COUNTIF（range,criteria函数类型语法公式输出结果DATE

DATE(year,month,day)=DATE(2013,11,11)2013/11/11DAYDAY(serial_number)

=DAY(2013/11/11)11TODAYTODAY(

)=TODAY()2013/11/11(假定今天是2013-11-11）WEEKDAYWEEKDAY(serial_number,return_type)=WEEKDAY(2013/11/11，1)=WEEKDAY(2013/11/11，2)2

1YEAR

YEAR(serial_number)=YEAR(2013/11/11)2013NOW

NOW(

)=NOW()2013/11/1116:39MONTHMONTH(serial_number)

=MONTH(2013/11/11)11（2）日期函数2021/3/21函数类型语法公式输出结果DATE

DATE(year,mon一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCEL基础技巧二、EXCEL常用快捷键五、EXCEL常用格式2021/3/21一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCE四、EXCEL基础技巧操作方法：（1）条件格式使用条件格式可以帮助你可视化的探索和分析数据，查找关键问题，以与识别模式和趋势。条件格式还可以方便的调整工作表的格式，增强表格的易读性第一步：选择要设置条件格式的区域2021/3/21四、EXCEL基础技巧操作方法：（1）条件格式使用条件格式可操作方法：第二步：选择要设置条件格式的区域2021/3/21操作方法：第二步：选择要设置条件格式的区域2021/3/21操作方法：第三步：选择“条件格式”——“突出显示单元格规则”—“小于”，可以按照需要可视化符合条件格式的数据。例如：对于使用率小于90%的数据，红色标注2021/3/21操作方法：第三步：选择“条件格式”——“突出显示单元格规则”（2）数据有效性使用数据有效性来控制数据的类型或者用户输入的数值的类型。例如你可以只允许输入某个范围的日期，只允许输入正数，或者使用下拉列表限制选项。2021/3/21（2）数据有效性使用数据有效性来控制数据的类型或者用户输入的操作方法：选择要输入的单元格序列，点击“数据”—“数据有效性”—在“设置”中的“允许”菜单中选择“序列”2021/3/21操作方法：选择要输入的单元格序列，点击“数据”—“数据有效性第一步：在单元格中创建下拉列表先将下拉列表的选项输入到工作表中选中要制作下拉列表的单元格区域，打开“数据有效性”对话框，“允许”选择序列，勾选“忽略空值”和“提供下拉箭头”，单击“确定”按钮。A.从下拉表中导入B.手动输入注意：手动输入时，分隔号为英文输入法下的“逗号”。2021/3/21第一步：在单元格中创建下拉列表A.从下拉表中导入B.手动输入第二步：输入提示的使用在下拉列表单元格处于活动状态时，显示输入提示。选中有下拉列表的单元格区域，打开“数据有效性”对话框，选择“输入信息”标签，在“标题”和“输入信息”中写入提示信息，单击“确定”按钮。2021/3/21第二步：输入提示的使用2021/3/21第二步：出错提醒的使用在下拉列表单元格内容不符合要求时，显示出错警告。选中有下拉列表的单元格区域，打开“数据有效性”对话框，选择“出错警告”标签，在“标题”和“输入信息”中写入警告信息，单击“确定”按钮。2021/3/21第二步：出错提醒的使用2021/3/21操作方法–动态数据源的下拉列表当下拉列表来源是一个动态增减的单元格区域时，就会用到与创建动态名称技巧类似的方法。前几步与制作普通下拉列表完全一致，不过“来源”不能直接选中，而要输入公式“=offset($B$2,,,counta($B:$B)-1)”单击“确定”按钮2021/3/21操作方法–动态数据源的下拉列表2021/3/21操作方法–录入符合某种规范的数据只允许录入首位以字母开头，长度为8的数据。选中作为数据有效性来源的单元格区域，点击菜单“数据”->“有效性”，在“数据有效性”对话框中，选择允许“自定义”，在“公式”框中输入=AND(AND(LEFT(A1,1)>"a",LEFT(A1,1)<"Z"),LEN(A1)=8)单击“确定”按钮2021/3/21操作方法–录入符合某种规范的数据2021/3/21（3）自定义序列“开始”-“排序和筛选”-“自定义排序”-“自定义序列”中输入常用的序列。后期应用时，只要输入该序列第一个值，自动填充下拉，即可得到输入的序列。第一步：选中待排序名单第二步：自定义排序2021/3/21（3）自定义序列“开始”-“排序和筛选”-“自定义排序”-“第三步：自定义排序设置主要关键字：条件筛选列；次序：自定义序列；点击“确定”—“输入序列”；点击“添加”—“确定”。2021/3/21第三步：自定义排序设置主要关键字：条件筛选列；2021/3/第四步：成果展示注意：1.选择排序区域时，不包括表头；2.自定义排序时，分隔号使用英文输入法下的“逗号”。2021/3/21第四步：成果展示注意：2021/3/21（4）格式刷（5）选择性粘贴

A单击格式刷：单击格式刷，然后单击选择的区域，则两者格式完全相同。B双击格式刷：双击格式刷，然后单击选择某个内容，则两者格式完全相同，可以连续使用，直到再次单击格式刷图标，鼠标恢复正常形状。A将表格的行列互换：复制要转置的部分数据→单击右键→“选择性粘贴”命令→选中“转置”命令，则完成操作。B粘贴数值：复制部分数据→单击右键→

“选择性粘贴”命令→选择“数值”或“123”，则完成操作。2021/3/21（4）格式刷（5）选择性粘贴

A单击格式刷：单击格式刷，一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCEL基础技巧二、EXCEL常用快捷键五、EXCEL常用格式课程目录2021/3/21一、EXCEL基础功能介绍三、EXCEL常用公式四、EXCE1.文件命名要反映文件核心内容，使人不用打开文件就明白文件的主要思想√×

标题格式五、EXCEL常用格式2.文件命名须包含标题、版本和日期Eg.Excel文件规范要求V1-201311182021/3/211.文件命名要反映文件核心内容，使人不用打开文件就明白文件的3.打开文件后，文件内容必须有标题，标题居中。2021/3/213.