EXCEL使用技巧培训

单击此处编辑母版标题样式,单击此处编辑母版文本样式,使用技巧培训,培训目标,提高,Excel,实际操作能力，提高工作效率,制作标准表格；数据汇总；数据统计与分析,重要函数综合使用方法,掌握如何制作漂亮的各类图表,课程内容介绍,EXCEL,入门,1,常用操作技巧,2,EXCEL,常用公式和函数,3,透视表及图表制作,4,宏录制及简单控件应用,5,学习方法技巧,A,要有几本好的工具书,B,要学会在网上寻找资源,C,从模仿开始，慢慢理解,D,经常使用，并且与同事交流,EXCEL,入门,1,1-1. EXCEL,发展历史,1-3. EXCEL,学习阶梯,1-2. EXCEL,功能特点,1-4. EXCEL2007,工作,界面简介,1-1. 历史,发展,升华,成长,诞生,从6.0开始，放弃以数字作为版本号的方式，开始使用年份定义版本。 95 7.0,1987年第一款适用于系统的 2.0,1985年第一款诞生，它只用于系统。,2007 2006年11月发布，在新的面向结果的用户界面中,Microsoft Excel 1.01,Microsoft Excel 1.5,Microsoft Excel 2.1,Microsoft Excel 2.2,Microsoft Excel 2.2a,Microsoft Excel 3.0,Microsoft Excel 4.0,Microsoft Excel 5.0,Microsoft Excel 4.0 a,Microsoft Excel 95,Microsoft Excel 97,Microsoft Excel 98,Microsoft Excel 2000,Microsoft Excel 2001,Microsoft Excel XP,Microsoft Excel v. X,Microsoft Excel 2003,Microsoft Excel 2004,Microsoft Excel 2007,1-1. 历史,它的图形用户界面将各种菜单、工具栏展现在屏幕中，允许同时使用鼠标和键盘发布命令，使不具备专业知识的用户也可以轻松自如的操作。,它的版面专为表格设计，因而很容易输入原始数据，形成整齐美观的表格。,它的运算功能极强，不仅可以进行四则运算、逻辑运算，还可以进行大量的函数运算，所能解决的计算问题已经远远超出了日常工作的范围。,它能根据原始数据和计算结果生成100多种图表，并且图表格式可以任意设置修改。,它的自动计算、自动更正、自动筛选等功能表现出超常的智能化水平。,它可以将数据清单自动视为数据库，对其排序、筛选、汇总，甚至建立数据透视表。,它还可以进行模拟运算、变量求解，并通过局域网和与其他用户共享中文版 的文档。,界面友好,易学易用,操作方便,功能完善,1-2. 功能特点,编程,透视表,函数、图表,基本操作,编程,透视表,函数、图表,基本操作,1-3. 学习阶梯,1-42007工作界面简介,1-52007特点简介,常用操作技巧,2,2-1.,EXCEL,工作簿修复,2-2,.,数据有效性,2-3.,实用的,F4,功能,2-4.,让不同类型数据用不同颜色显示,2-5.,&,连接符,2-6.,工作表“减肥”,2-1工作簿修复,第一步：在中打开工作表，并选择需要设置的区域或整行或整列，再单击“数据”功能区中的“数据有效性”按钮：,第二步：在弹出的“数据有效性”对话框中的“设置”选项卡下，选择“允许”下面的“序列”,2-2.数据有效性-1,第三步：根据需要设置是否“忽略空值”，在下面的“来源”文本框中输入待选的列表项，各数值间以半角逗号分隔，不需要加引号，再单击“确定”按钮即可：,第四步：返回工作表，刚才选择的区域中已经有下拉箭头了，点击这个箭头或按键可弹出下拉列表，如果后面的数据和目前单元格的数据一样，则我们可以将光标指向单元格选择框（黑色框）右下角的那一点上，变成十字光标时双击之可快速填充。,2-2.数据有效性-2,2-3.实用的F4功能,1、重复操作；,如果如需要插入一个新工作表，可以选择当前工作表，右键工作表标签选择插入新工作表；如果要插入更多的，可以按F4,插入第二个，再按插入第三个,另外，大部分对表格的操作都可以重复：常用于设置格式、插入/删除行列等。,2、转换公式中的单元格及区域的引用方式；,比如：对于某单元格所输入的公式为“(B48)”。选中整个公式，按下F4键，该公式内容变为“($B$4:$B$8)”，表示对横、纵行单元格均进行绝对引用。第二次按下F4键，公式内容又变为“(B$4$8)”，表示对横行进行绝对引用，纵行相对引用。第三次按下F4键，公式则变为“($B4:$B8)”，表示对横行进行相对引用，对纵行进行绝对引用。第四次按下F4键时，公式变回到初始状态“(B48)”，即对横行纵行的单元格均进行相对引用。如果继续按F4键，将再次进行循环。需要说明的一点是，F4键的切换功能只对所选中的公式段有作用。,注意：需要选择整个公式，然后按F4键才有作用。比如选择整个公式(E3160158)（选择单元格是不行的）,然后按下F4键，公式就变成($E$3:$E$160158)了,2-4.让不同类型数据用不同颜色显示,在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置。,第二步：按“添加”按钮，并仿照上面的操作设置好其它条件(大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”)。设置完成后，点击“确定”,第一步：打开“工资表”工作簿，选中“工资总额”所在列，执行“格式条件格式”命令，打开“条件格式”对话框。单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。,2-5连接符,连字符：起着连接的作用,我身边曾经有这样的朋友，就是在表格中一行有多个单元格，需要将他们连接在一起，她尽然非常有耐心的在每个格子中双击，然后在最终的单元格不断的粘贴.,方法和步骤：,1、在新的一个单元格输入=,2、点击第一个单元 然后按键盘上的&（就是7），再继续第二个单元格。如此重复所有的单元格连接起来。,第一种：关闭“启动实时预览”,以2007为例说明，首先打开2007，找到界面左上角的选项，如图,选择“常用”，在“启用实时预览” 前取消“”,2-6.工作表“减肥”-1,第二种：设置加载项找到界面左上角的选项，如图,出现选项的界面后，取消全部的勾选框，确定即可,2-6.工作表“减肥”-2,EXCEL,常用函数,3,3-1,.,学习,EXCEL,函数前要记住的事,3-2,条件统计函数,COUNTIF,3-3.,条件求和,SUMIF,3-4.,查找函数,VLOOKUP,3-1 学习函数前要记住的事,1,、所有的函数公式都是以号开头的,2,、一定要学会使用帮助文件,3,、了解功能比背住函数的写法更重要,4,、有些,07,版新增函数，,03,版是不识别的,功 能,语 法,：需要计算其中满足条件的单元格数目的单元格区域。,：确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 。,(),注 意事 项,非空白单元格区域,以文本形式表达的表达式,如“=90”，往往不能用于只为一个简单的条件表达式，不能用于复杂的统计=80,2”）,3-2 条件统计函数,解释：,1）在A1100之中，统计等于D2单元格里数值的个数,2）在A1100之中，统计大于2的个数,统计区域,条件,函数解释：,根据条件H2在统计区域E2：E11中，进行逐一比对（逻辑运算），最终汇总符合条件的个数,条件： A，A，A，A，A，A，A，A，A，A,统计区域：E，B，E，C，E，A，B，A，D，A,运算方式按条件,0，0，0，0，0，1，0，1，0，1,3-2 条件统计函数,最后对结果进行求和，如下(0,0,0,0,0,1,0,1,0,1) =3,：用于条件判断的单元格区域。,：确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 。,：需要求和的实际单元格。,功 能,指定区域中对满足条件的相对应的单元格求和,语 法,(),3-3 条件求和函数,只有在区域中相应的单元格符合条件的情下中的单元格才求和。,如果忽略了,则对区域中符合条件的单元格求和。,可用数组运算。作为数组运算,只能用列运算,不能用行运算。,注 意事 项,（，）,例子：,1）（A1100，D2，B1100）,2）（A1100，”A”，B1100）,3）（A1100，”3”，B1100）,解释：,1）汇总等于D2单元格的所有值,2）汇总等于”A”的所有值,3）汇总大于3的所有值,3-3 条件求和函数,H719=,M7，在函数中，自动转换成数组形式，即,0,0,0,0,0,1,0,0,0,1,0,1,0,1）按次序逐对进行比较。,2）相符（）为1，不相符（）为0。,第步得出的结果，再进行下一步运算,需要求A组的段取时间累计，怎么做呢？,3-3 条件求和函数,0,0,0,0,0,1,0,0,0,1,0,1,0,第步比较运算得出的结果：,再与,K7,：,K19,（如下）数据按顺序相乘,0.50,0.83,1.170.50,0.50,0.33,0.67,结果,0,0,0,0,0,0.50,0,0,0,0.50,0,0.33,0,最后求和得：1.33,11=1 01=0,3-3 条件求和函数,(),：条件值，是值，非区域，比如A1，而非 A1100，即使A1100，也只选择左上角值A1,：搜索的区域/范围，是一个多维数组，如 A1100之类,：需要返回的值所在列，从搜索范围的起始 列开始算,：匹配方式，一般使用精确匹配，即0或,功 能,语 法,函数能够从其它工作表（或本工作表）将某数 据列中的数据查找出需要的数据,3-4 查找函数,注 意事 项,如果在查找区域含有多个与相等的值，即含有重复项，只能返回首个值所对应的结果,如果中没有与相等的值，函数将返回错误值,(),例子：,在2返回1的数据，公式可以表示为,（A1, 11100,3,0）,解释：,在1表A1100区域的第一列查找2表单元格A1的值，查到后，返回这一行第3列的值。,3-4 查找函数,A1,A2,A3,A4,A5,A6,A7,B1,B2,B3,B4,B5,B6,B7,二维数组,D2,，,D2,，,D2,，,D2,，,D2,，,D2,，,D2, A1,，,A2,，,A3,，,A4,，,A5,，,A6,，,A7,首先,D2,与首列进行逐个比较运算,逐一按顺序进行比较,0，0，0，0，1，0,结果,3-4 查找函数,A1,A2,A3,A4,A5,A6,A7,B1,B2,B3,B4,B5,B6,B7,二维数组,第 步结果0，0，0，0，1，0,与第2列比较B1234567,返回1对应的数据,B60,3-4 查找函数,透视表及图表制作,4,4-1.,数据透视表的功能,4-3.,制作透视表的实例演示,4-4.,设置透视表格式,4-2.数据透视表制作的注意事项,4-1 透视表所需的规范数据源,所谓规范的数据源：,1、不能有合并单元格；,2、表头必须是一行，且中间不能有空白字段 ；,3、数据的输入要复核一定的规范；,4-1 数据透视表的功能,对数值数据进行分类汇总和聚合；,展开或折叠要关注结果的数据级别，查看感兴趣区域摘要数 据的明细；,将行移动到列或将列移动到行（或“透视”），以查看源数据的不同汇总；,对最有用和最关注的数据子集进行筛选、排序、分组和有条件地设置格式，突出显示重要信息；,提供简明、带有批注的联机报表或打印报表,数据区域的顶端行为字段名称(标题)。,避免在数据清单中存在有空行和空列。这里需指明以下，所谓空行，是指在某行的各列中没有任何数据，如果某行的某些列没有数据，但其他列有数据，那么该行就不是空行。同样，空列也是如此。,各列只包含一种类型数据。,避免在数据清单中出现合并单元格。,避免在单元格的开始和末尾输入空格。,尽量避免在一张工作表中建立多个数据清单，每张工作表最好仅使用一个数据清单。,工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。,4-2 数据透视表制作的注意事项,4-3 制作透视表的实例演示,假设某电视销售商场有6位职工，分别是劳得诺、令狐冲、陆大安、任我行、韦小宝、向问天，销售的电视产品有、长虹、康佳、创维、熊猫几种品牌。每天的销售数据都记录在的一个工作表中，如图所示，该数据表有300多行数据 。完成以下统计分析工作：,统计各种品牌的电视的总销售额；统计每位职工的总销售额；统计每位职工销售的各种类型的电视机的销售额；统计各种运输方式的总数。,1、建立数据透视表的过程,1、建立源数据表后，单击“插入”选项卡中的“数据透视表”弹出“创建数据透视表”对话框,2、 “创建数据透视表”对话框,3、 指定数据透视表的数据区域后，单击“确定”按钮，将弹出数据透视表的布局设置对话框,4-3 制作透视表的实例演示,5、根据第4步设置创建的数据透视表,4、用鼠标将数据字段拖放到行、列标签和数值汇总对应的文本框中,4-3 制作透视表的实例演示,其中数据透视表布局中的内容,4-3 制作透视表的实例演示,行标签,拖放到行中的数据字段，该字段中的第一个数据项将占据透视表的一行。,列标签,与行相对应，放置在列中的字段，该字段中的每个项将占一列。,行和列相当于X轴和Y轴，由它们确定一个二维表格，页则相当于Z轴。拖放在页中的字段，将按该字段的数据项对透视表进行分页。,报表筛选,数值,进行计数或汇总的字段名称。,2、统计各销售员销售数据的透视表布局,4-3 制作透视表的实例演示,3、统计各销售员销售数据的透视表,双击任一单元格，会得到这些数据的来源明细数据,4-3 制作透视表的实例演示,双击上表E5单元格得到的明细数据表,4-3 制作透视表的实例演示,4、利用报表筛选（即页字段）创建分页显示,页字段,用鼠标单击它，从下拉列表中选择某个日期，可以查看此日期的销售数据,4-3 制作透视表的实例演示,5、使用多重数据字段建立透视表,在前面所讨论的电视销售数据表中，要查看每位职工销售了多少种产品，每种产品的数量各是多少，而且还要查看其销售的产品中，每种运输方式各运输了多少数量。,在行标签中放置了”销售员姓名“和”运输方式“两字段,4-3 制作透视表的实例演示,6、修改数据透视表的汇总函数,数据透视表中使用的透视函数称为汇总函数，在默认情况下，数据透视表采用函数对其中的数值项进行汇总，用函数对表中的文本类型字段项进行计数。,提供了许多汇总函数，如下图所示。,单击“求和项”的下拉箭头，从弹出的菜单中选择“值字段设置”，弹出值字段设置对话框,在此选择需要的”汇总函数“,4-3 制作透视表的实例演示,7、修改透视表数据的显示方式,以百分比显示数据,单击“求和项”的下拉箭头，从弹出的菜单中选择“值字段设置”，弹出值字段设置对话框,单击此下拉箭头，从弹出的菜单中选择“占总和的百分比”,4-3 制作透视表的实例演示,1、以”销售员姓名“为行标签，“日期”为列标签制作数据透视表,2、单击列标签下面的任何一个日期，如“1997年2月27日”，然后单击“数据透视表工具”“选项”“将所选内容分组”，会弹出“分组”设置对话框，,3、选择一种分组的时间方式，如月,8、对日期进行分组透视的过程,4-3 制作透视表的实例演示,宏录制及简单控件应用,5,如果经常在中重复某项任务，那么可以用宏自动执行该任务。所谓宏，就是一组指令集，通过执行类似批处理的一组命令，来完成某种功能。宏可以在以下情况运用：,1、设定一个每个工作表中都需要的固定形式的表头； 2、将单元格设置成一种有自己风格的形式； 3、每次打印都固定的页面设置； 4、频繁地或是重复地输入某些固定的内容，比如排好格式的公司地址、人员名单等； 5、创建格式化表格； 6、插入工作表或工作薄等。,2024/8/27,51,谢 谢！,