Excel应用培训201X1016ppt课件.ppt

主讲人：ExcelExcel实操应用培训实操应用培训.目录目录I IIIIIIIIIIIIVIV基本操作基本操作原来可以如此简单原来可以如此简单表格之美表格之美表格也是门艺术表格也是门艺术函数图表函数图表ExcelExcel的精髓之处的精髓之处实战演练实战演练玩转玩转excelexcel不是梦不是梦excel.前言前言ExcelExcel学习进级有哪些阶段？学习进级有哪些阶段？VBA编程VBAVBA编程编程函数、图表函数、图表函数、图表数据透视表基本操作基本操作基本操作数据透视表数据透视表.常用快捷键常用快捷键排序与筛选排序与筛选分列分列数据有效性数据有效性条件条件格式格式删除重复项删除重复项不定行数据填充不定行数据填充基本操作基本操作表格之美表格之美函数图表函数图表实战演练实战演练基本操作基本操作 原来可以如此简单原来可以如此简单.基本操作基本操作常用快捷键常用快捷键 快快 捷捷 键键qCtrl+C复制选定区域复制选定区域qCtrl+v粘贴选定区域粘贴选定区域qCtrl+x裁剪选定区域裁剪选定区域q选定区域选定区域+Ctrl+d纵向填充第一列单元格内容纵向填充第一列单元格内容q选定区域选定区域+Ctrl+r横向填充第一行单元格内容横向填充第一行单元格内容qWindows键键+d显示桌面显示桌面qCtrl+b加粗单元格字体加粗单元格字体qCtrl+f/h查找与查找与替换替换qTab+Alt 当前程序与上个程序间快速切换当前程序与上个程序间快速切换qTab+Ctrl同类型程序间同类型程序间快速快速切换切换qAlt+Enter 单元格内换行单元格内换行 qCtrl+End 移到文本框的移到文本框的末尾末尾qCtrl+Home移到文本框移到文本框的的开头开头qShift+Ctrl+End 选定自光标所在单元格以选定自光标所在单元格以下下所有非所有非空空单元格单元格qShift+Ctrl+Home 选定自光标所在单元格选定自光标所在单元格以有所以以有所以非空非空单元格单元格qCtrl+A选定所有非空选定所有非空单元格单元格q选定对象选定对象+Shift+箭头状鼠标拖动箭头状鼠标拖动任意行间任意行间拖动拖动q选定对象选定对象+Ctrl+箭头状鼠标拖动箭头状鼠标拖动任意行间复任意行间复制粘贴制粘贴.基本操作基本操作排序与筛选排序与筛选 几类典型排序方法几类典型排序方法q排序法自定义序列法典型案例：按职务高低进行排序q排序法对排序选项进行设定典型案例：按单元格填充颜色排序（若存在多种颜色可通过“复制条件”选项来进行次关键字排序）q排序注意事项：对带公式的单元格进行排序注意事项筛选的基本操作筛选的基本操作q筛选的类型按文本特征筛选、按数字特征筛选、按日期特征筛选、按字体颜色或单元格颜色筛选q筛选的基本操作选定区域、筛选、搜索关键字q自定义自动筛选方式q筛选的注意事项：双行标题的工作表筛选q若只对一列单元格数据进行筛选，则需选中标题所在单元格及其下方至少一个单元格，否则默认为整个工作表。.操作与技巧进阶操作与技巧进阶分列分列q利用分列主要功用：解决从别处粘贴的数据无法运算的问题按规则拆分标示显示路径：菜单栏【数据】-【分列】.操作与技巧进阶操作与技巧进阶数据有效性数据有效性q数据有效性的作用限制单元格的输入或圈识无效数据做单元格下拉表q利用数据有效性对单元格输入限制q数据有效性的来源可文本也可公式，若是文本，需英文状态下的逗号作为分隔符。q路径：菜单栏【数据】-【数据有效性】.操作与技巧进阶避免重复值录入实战实战 日常工作中，身份证号码、工作证编码等日常工作中，身份证号码、工作证编码等ID编号是唯一的，不允许重编号是唯一的，不允许重复，若在复，若在excel中重复录入中重复录入ID，就会给信息管理带来不便，我们可以通，就会给信息管理带来不便，我们可以通过数据有效性来解决这个问题：过数据有效性来解决这个问题：u切换到“设置”选项卡，打开“允许”下拉菜单，选择【自定义】u在公式处输入=countif(a:a,a1)=1（英文状态下输入）u切换到“出错警告”选项卡，选择出错警告信息的样式，填写标题和错误信息，然后点击【确定】uCOUNTIF函数可以统计单元格区域中满足给定条件的单元格的个数。u该函数的语法结构为：COUNTIF(rage,criteria),其参数range表示需要统计其中满足条件的单元格数目的单元格区域；criteria表示指定的统计条件，其形式可以为数字、表达式、单元格引用或文本。在运用COUNTIF函数时要注意，当参数criteria为表达式或文本时，必须用引号引起来，否则将提示出错。.操作与技巧进阶操作与技巧进阶条件格式条件格式q利用条件格式主要功用：按规则突出显示：数值、文本、如期等规则按规则建立标示显示快速标示重复项快速标示重复项路径：菜单栏【开始】-【条件格式】注意使用“项目选取规则、数据条、色阶、图标集”等功能时，所选择的范围仅能包含数值数据，不能存在文本的单元格。.操作与技巧进阶删除重复项拓展拓展删除重复值删除重复值路径：菜单栏【数据】-【删除重复项】.操作与技巧进阶不定行数据填充选定需要填充的数据按“F5”，弹出定位选项框，点击【定位条件】，选择【空值】，点击确定。在公式栏输入选定区域的第一个单元格，同时按Ctrl+Enter。.数据透视表的制作数据透视表的制作如何用含有合并单元格的数据如何用含有合并单元格的数据来创建数据透视表？来创建数据透视表？基本操作基本操作表格之美表格之美函数图表函数图表实战演练实战演练表格之美表格之美 表格也是门艺术表格也是门艺术.数据透视表数据透视表面对一篓筐的数据，你还在一个个按计算器求和吗？或者还在用SUM函数吗？如果是，你奥特曼了！.数据透视表数据透视表功能介绍：数据透视表能够将筛选、排序和分类汇总等操作依次完成，数据透视表能够将筛选、排序和分类汇总等操作依次完成，并生成汇总表格。并生成汇总表格。Example:资金日报表资金日报表 1、透视表的建立（单击excel2010版插入按钮）2、选项及设计功能介绍 3、存在合并单元格透视表会提示字段名无效 .数据透视表数据透视表如何用含有合并单元格的数据来如何用含有合并单元格的数据来创建数据透视表创建数据透视表？特殊特殊应用应用.数据透视表数据透视表解决方法：取消合并单元格，填充相同内容，然后创建数据透视表。取消合并单元格，填充相同内容，然后创建数据透视表。附加学习点：相隔不规则行数填充内容。相隔不规则行数填充内容。.数据透视表数据透视表步骤1如图所示，选中合并单元格区域A2:A21单击“开始”选项卡“对齐方式”组“合并后居中”，取消合并单元格。.数据透视表数据透视表步骤2如图所示，选中取消合并后的单元格区域A2:A21按F5，在弹出的“定位条件”对话框中选择“空值”“确定”，即可定位并选中单元格区域A2:A18中所有空值的单元格.数据透视表数据透视表步骤3如图所示，在选中的空白单元格中输入“=A2”，然后按【Ctrl+Enter】组合键，即可实现空白单元格填充相同项。.数据透视表数据透视表步骤4按之前讲诉方法创建数据透视表。.数据透视表数据透视表如何在数据透视表中将两个求和如何在数据透视表中将两个求和项再求和项再求和？特殊特殊应用应用选择求和项-在【数据透视表工具】-【选项】-【域、项目合集】.数据透视表数据透视表如何两个求和项再求和？如何两个求和项再求和？在弹出计算字段选项框中，把名称改为“合计”，在公式框中，从下面字段框中双击选择“金额”，输入“+”，在双击选中“滞纳金”，点击确定即可。.VLOOKUP函数的应用函数的应用SUMIF函数函数应用应用COUNTIF函数应用函数应用IF函数函数应用应用基本操作基本操作表格之美表格之美函数图表函数图表实战演练实战演练函数图表函数图表 ExcelExcel的精髓之处的精髓之处.VLOOKUP函数函数快速查找指定数值快速查找指定数值从懂得从懂得VLOOKUP开始开始视频视频教学教学功能介绍：在表格数值或数组的首列查找指定的数值，并由此返在表格数值或数组的首列查找指定的数值，并由此返回表格或者数组中该数值所在行中指定列处的数值。回表格或者数组中该数值所在行中指定列处的数值。.VLOOKUP函数函数注意事项：1、如果“查找值”小于“区域”第一列中的最小值，函数VLOOKUP返回错误值#N/A。2、如果函数VLOOKUP找不到“查找值”，且“逻辑值”为FALSE,函数VLOOKUP返回错误值#N/A。3、VLOOKUP的查找值如果在索引区域内有多个对应值时，只能返回首条记录的对应索引数据。4、VLOOKUP的查找值相对应的索引区域的对应列，必须位于索引区域的首列。.SUMIF函数函数案例：请根据房产资料工作表，用Sumif函数统计出“项目名称”为“高尔夫三期”的所有房间建筑面积之和。演练演练SUMIF函数是Excel常用函数。使用 SUMIF 函数可以对报表范围中符合指定条件的值求和。Excel中sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。u第一个参数：Range为条件区域，用于条件判断的单元格区域。u第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。u第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。答案：答案：=SUMIF(=SUMIF(房产资料房产资料!A:A,!A:A,高尔夫三期高尔夫三期,房产资料房产资料!G:G)!G:G).COUNTIF函数函数案例：请根据房产资料工作表，用Countif函数统计出“项目名称”为“高尔夫二期”的房间个数(房产户数)。演练演练Countif函数是Microsoft Excel中对指定区域中符合指定条件的单元格计数的一个函数u参数：range 要计算其中非空单元格数目的区域u参数：criteria 以数字、表达式或文本形式定义的条件答案：答案：=COUNTIF(=COUNTIF(房产资料房产资料!A:A,!A:A,高尔夫二期高尔夫二期).IF函数函数案例：请用If 函数判别，若上题“项目名称”为“高尔夫二期”的房间个数超出370个，则提示“统计正确”，否则提示“统计错误”。演练演练IF函数一般是指Excel中的IF函数，根据指定的条件来判断其“真”（TRUE）、“假”（FALSE），根据逻辑计算的真假值，从而返回相应的内容。IF(logical_test,value_if_true,value_if_false)uLogical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。uValue_if_true logical_test 为 TRUE 时返回的值。uValue_if_false logical_test 为 FALSE 时返回的值答案：答案：=IF(C9370,=IF(C9370,统计正确统计正确,统计错误统计错误).图表图表图表的制作过程图表的制作过程理解数据理解数据分析分析数据数据提炼信息提炼信息明确主题明确主题提炼信息提炼信息选择图表选择图表制作图表制作图表数据图表数据图表确定确定比较关系比较关系确定图表确定图表类型类型确定图表处理确定图表处理方式方式准备做图数据准备做图数据制作美化图表制作美化图表检查确认图表检查确认图表原始数据原始数据原始文件原始文件.图表图表如何同时显示分类轴，两种图表形式如何同时显示分类轴，两种图表形式 通常在Excel图表中，仅仅在图表的下方显示一个分类（X）轴，有时需要在图表的上方再显示一个分类（X）轴（次分类轴），可以更加方便地查看数据的变化情况。下图为三大管家收入及占比情况表，图中分别用了两个分类轴，实现步骤如下：.图表图表同时显示柱状图和折线图同时显示柱状图和折线图l在excel表中输入需要插入图表的区域范围l选中该工作表点击插入二维柱形图（不能选择三维图），会弹出以下图表：注意：如选择的数据范围需重新选择，可右击鼠标，【选择数据】，重新选择数据范围或者把多余的系列删除，点击保存即可。l可通过图表工具，对图表进行美化，如改变标题、柱状图的颜色、图例的摆放位置、数据标签的显示位置等。.图表图表同时显示柱状图和折线图同时显示柱状图和折线图 上述操作已经选择数据并做好了柱状图，要三大管家占比制作成折线图，操作如下：l将excel表中柱形图适当放大，可看见显示为占比的柱子（蓝色），右键选择增长率的柱子选择“设置数据系列格式”“系列绘制在”选择“次坐标轴”，图标如下所示：.图表图表同时显示柱状图和折线图同时显示柱状图和折线图点击年增长率柱子选择“更改图标类型”更改为折线图确定。图表如下所示：.图表图表同时显示柱状图和折线图同时显示柱状图和折线图在图标中，选中柱形或者折线项目右键点击“添加数据标签”或者选择菜单栏【图表工具】-【布局】-数据标签，即可在图表中显示该项具体数据，如下图所示：.感谢聆听，敬请指导！感谢聆听，敬请指导！.