excel培训课件

nullExcel基础培训Excel基础培训主讲: 肖蓉晖 陕西汽车集团温州云顶汽车有限公司培训内容培训内容本专题旨在让同事们掌握EXCEL在日常工作中的经常用到的一些功能及技巧，提高Excel实际操作能力的同时提高工作效率 。 掌握并熟练运用Excel常用技巧数据透视 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 的应用图表的应用常用菜单命令的使用常用菜单命令的使用1. 文件菜单 1.1 页面设置 2. 编辑菜单 2.1选择性粘贴 2.2. 定位 3. 插入菜单 4. 格式菜单 4.1 隐藏工作表 4.2 条件格式 4.3 表格样式 常用菜单命令的使用常用菜单命令的使用5. 工具菜单 5.1 自动更正 5.2 选项 6.数据菜单 6.1 分类汇总、有效性、分列、合并计算 6.2 数据透视表函数和公式函数和公式1、什么是函数 Excel函数即是预先定义，执行计算、 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 等处理数据任务的特殊公式。 2、什么是公式 函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。公式是以“=”号为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式，函数则是按特定算法进行计算的产生一个或一组结果的预定义特殊公式。 在Excel2003中，函数共分为11个大类，函数与公式技巧比较熟练的用户在面对特定的问题时总是会自觉地想到所要使用的特定函数所在分类。 常用函数常用函数1、MAX 用途：返回数据集中的最大数值。 语法：MAX(number1，number2，...) 参数：Number1，number2，...是需要找出最大数值的1至30个数值。 2、MIN 用途：返回给定参数表中的最小值。 语法：MIN(number1，number2，...)。 参数：Number1，number2，...是要从中找出最小值的1到30个数字参数。常用函数常用函数3、LEFT或LEFTB 用途：根据指定的字符数返回文本串中的第一个或前几 个字符。此函数用于双字节字符。 语法：LEFT(text，num_chars)或LEFTB(text，num_bytes)。 参数：Text是包含要提取字符的文本串；Num_chars指定 函数要提取的字符数，它必须大于或等于0。Num_bytes按字节数指定由LEFTB提取的字符数。 实例：如果A1=国投罗钾，则LEFT(A1，2)返回“国投”，LEFTB(A1，2)返回“国”。常用函数常用函数4、LEN或LENB 用途：LEN返回文本串的字符数。LENB返回文本串中所有 字符的字节数。 语法：LEN(text)或LENB(text)。 参数：Text待要查找其长度的文本。 注意：此函数用于双字节字符，且空格也将作为字符进 行统计。 实例：如果A1=国投罗钾，则公式“=LEN(A1)”返回4，=LENB(A1)返回8。常用函数常用函数5、MID或MIDB 用途：MID返回文本串中从指定位置开始的特定数目的字 符，该数目由用户指定。MIDB返回文本串中从指定位置开始 的特定数目的字符，该数目由用户指定。MIDB函数可以用于 双字节字符。 语法：MID(text，start_num，num_chars)或MIDB(text，start_num，num_bytes)。 参数：Text是包含要提取字符的文本串。Start_num是文 本中要提取的第一个字符的位置，文本中第一个字符的 start_num为1，以此类推；Num_chars指定希望MID从文本中 返回字符的个数；Num_bytes指定希望MIDB从文本中按字节返 回字符的个数。 实例：如果A1=国投罗钾，则公式“=MID(A1，3，2)” 返回“罗钾”，=MIDB(A1，3，2)返回“投”。常用函数常用函数6、RIGHT或RIGHTB 用途：RIGHT根据所指定的字符数返回文本串中最后一个 或多个字符。RIGHTB根据所指定的字节数返回文本串中最后 一个或多个字符。 语法：RIGHT(text，num_chars)，RIGHTB(text， num_bytes)。 参数：Text是包含要提取字符的文本串；Num_chars指定 希望RIGHT提取的字符数，它必须大于或等于0。如果 num_chars大于文本长度，则RIGHT返回所有文本。如果忽略 num_chars，则假定其为1。Num_bytes指定欲提取字符的字节 数。 实例：如果A1=国投罗钾，则公式“=RIGHT(A1，2)” 返回“罗钾”，=RIGHTB(A1，2)返回“钾”。常用函数常用函数7、TEXT 用途：将数值转换为按指定数字格式表示的文本。 语法：TEXT(value，format_text)。 参数：Value是数值、计算结果是数值的公式、或对数值 单元格的引用；Format_text是所要选用的文本型数字格式， 即“单元格格式”对话框“数字”选项卡的“分类”列表框 中显示的格式，它不能包含星号“*”。 注意：使用“单元格格式”对话框的“数字”选项卡设 置单元格格式，只会改变单元格的格式而不会影响其中的数 值。使用函数TEXT可以将数值转换为带格式的文本，而其结 果将不再作为数字参与计算。 实例：如果A1=2986.638，则公式“=TEXT(A1，"#， ##0.00")”返回2，986.64。常用函数常用函数8、DATEDIF函数 函数名称：DATEDIF 主要功能：计算返回两个日期参数的差值。 使用格式：=DATEDIF(date1,date2,"y")、=DATEDIF(date1,date2,"m")、=DATEDIF(date1,date2,"d") 参数说明：date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的年（月、天）数。 应用举例：在C23单元格中输入公式：=DATEDIF(A23,TODAY(),"y")，确认后返回系统当前日期[用TODAY()表示）与A23单元格中日期的差值，并返回相差的年数。 特别提醒：这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。常用函数常用函数9、LOOKUP 用途：返回向量(单行区域或单列区域)或数组中的数值。该函数有两种语法形式：向量和数组，其向量形式是在单行区域或单列区域(向量)中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；其数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。 语法1(向量形式)：LOOKUP(lookup_value，lookup_vector，result_vector)常用函数常用函数语法2(数组形式)：LOOKUP(lookup_value，array)。 参数1(向量形式)：Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector为只包含一行或一列的区域。Lookup_vector的数值可以为文本、数字或逻辑值。 参数2(数组形式)：Lookup_value为函数LOOKUP在数组中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。如果函数LOOKUP找不到lookup_value，则使用数组中小于或等于lookup_value的最大数值。Array为包含文本、数字或逻辑值的单元格区域，它的值用于与lookup_value进行比较。 注意：Lookup_vector的数值必须按升序排列，否则LOOKUP函数不能返回正确的结果，参数中的文本不区分大小写。lookup函数实例lookup函数实例常用函数常用函数10、COUNTIF 用途：统计某一区域中符合条件的单元格数目。 语法：COUNTIF(range，criteria) 参数：range为需要统计的符合条件的单元格数目的区域；Criteria为参与计算的单元格条件，其形式可以为数字、表达式或文本(如36、">160"和"男"等)。其中数字可以直接写入，表达式和文本必须加引号。 实例：假设A1:A5区域内存放的文本分别为女、男、女、男、女，则公式“=COUNTIF(A1:A5，"女")”返回3。常用函数常用函数11、IF函数： “=IF()”是条件函数，最多可套嵌7层。其语法是： =IF(logical_test,value_if_true,value_if_false) =IF(第1个条件，满足该条件返回值，IF(第2个条件，满足该条件的返回值......IF(最后一个条件，满足该条件的返回值，不满足所有前列条件的返回值)))。 12、VLOOKUP函数： VLOOKUP(lookup_value,table_array,col_index_num,rangelookup) VLOOKUP（查找值，区域，列序号，逻辑值） “查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。 “区域”：数组所在的区域，如“B2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据 清单 安全隐患排查清单下载最新工程量清单计量规则下载程序清单下载家私清单下载送货清单下载 。常用函数常用函数“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值 #VALUE!；如果大于区域的列数，函数VLOOKUP返回错误值 #REF!。 “逻辑值”：为TRUE或FALSE。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为 FALSE 。 逆向查询语法：=VLOOKUP(查找值,IF({1,0},查找区域,目标区域),2,0) 1、判断的条件 2、跟踪数据的区域 3、返回第几列的数据 4、是否精确匹配 例子：=vlookup(a2,sheet1!$a$2:$f$100,6,true) 常用函数常用函数13、MATCH 用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数。 语法：MATCH(lookup_value，lookup_array，match_type)。 参数：Lookup_value为需要在数据表中查找的数值，它可以是数值(或数字、文本或逻辑值)、对数字、文本或逻辑值的单元格引用。Lookup_array是可能包含所要查找的数值的连续单元格区域，Lookup_array可以是数组或数组引用；常用函数常用函数Match_type为数字-1、0或1，它说明Excel如何在lookup_array中查找lookup_value。如果match_type为1，函数MATCH查找小于或等于lookup_value的最大数值。如果match_type为0，函数MATCH查找等于lookup_value的第一个数值。如果match_type为-1，函数MATCH查找大于或等于lookup_value的最小数值。 注意：MATCH函数返回lookup_array中目标值的位置，而不是数值本身 比如：有一列数据：A1:A7 分别时 A B C D E F G ，B1的值为F 这个时候D1输入=match(b1,a1:a7,0) 返回值就是6("F"在A1:A7中的行号） 14、SUMIF函数： 用途：根据指定条件对若干单元格、区域或引用求和。 语法：SUMIF(range，criteria，sum_range)=sumif(条件区,条件,求和区) 参数：Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。常用函数常用函数15、SUMPRODUCT函数： 用途：1、（常规应用）在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 2、（变通应用）多条件求和。 语法：SUMPRODUCT(array1，array2，array3，...) 参数：Array1，array2，array3，...为2至30个数组，其相应元素需要进行相乘并求和。 1 数量 单价 2 1 23 3 2 45 4 4 78 销售总额 =SUMPRODUCT(A2:A6,B2:B6)=662EXCEL日常操作中的常用技巧EXCEL日常操作中的常用技巧 大量的数据输入是一件比较繁琐的工作，除了提高输入的速度外，还可以使用Excel提供的几种用于辅助输入的 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 ： 1、不连续单元格填充同一数据：选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。 2、快速输入的技巧：输入范围的选定，Ctrl+单元格；如果需要输入的文本和上一个单元格的文本相同，直接按下“Ctrl＋D（或R）”键就可以完成输入，其中“Ctrl+D”是向下填充，“Ctrl+R”是向右填充。如果多个单元格需要输入同样的文本，我们可以在按住Ctrl键的同时，用鼠标点击需要输入同样文本的所有单元格，然后输入该文本，再按下“Ctrl+Enter”键即可，右键下拉列表选项快速录入。 EXCEL日常操作中的常用技巧EXCEL日常操作中的常用技巧3、单元格内容的合并：方法一：连字符“&”合并， 方法二：利用CONCATENATE函数。=CONCATENATE(B2,C2) 4、单元格内容的分列：方法一：工具菜单（数据-分列…），方法二：LEFT、RIGHT函数。例如aaa-111 B1=left（A1,3）C1=RIGHT(A1,3) 5、格式：自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。灵活运用好自定义格式功能，将会给实际工作带来很大的方便。EXCEL日常操作中的常用技巧EXCEL日常操作中的常用技巧6、单元格错误信息函数处理： EXCEL日常操作中的常用技巧EXCEL日常操作中的常用技巧7、数字、文本的快速转换：数字转变为文本格式（如身份证号）数字前加“’”，文本格式转变为数字格式：（1、选择性粘贴，2、函数：VALUE(TEXT) ）。 8、建立分类下拉列表：1、方法一2、方法二（分级菜单INDIRECT()应用） 9、“m2”、“m3”、“°C”快速输入：ALT+178、179、186。 10、禁止复制隐藏行或列中的数据：选中要复制的区域，单击“编辑→定位”命令，在打开的对话框中点击“定位条件”按钮，选中“可见单元格”选项，然后复制粘贴。 EXCEL日常操作中的常用技巧EXCEL日常操作中的常用技巧11、避开EXCEL误差：1、利用Round函数对中间值四舍五入2、正确设置Excel选项来避开“误差”。上述函数方法虽然方便，不过遇上大型Excel表格时，如果每个单元格都要设置这个函数，工作量未免太巨大，这时我们就可以进入Excel的[工具]→[菜单]→[选项]→[重新计算]，在“工作簿选项”处勾选“以显示精度为准”，上述Excel“误差”也可有效避免，而默认情况下这个小选项是没有被勾选的 12、统计不重复数据个数：{=SUM(1/COUNTIF(A1:A12,A1:A12)}然后按下ctrl+shift+enter键可在选中单元格里看到统计值 13、分级菜单 插入-名称-指定,选择"首行" 然后返回B1 数据有效性-序列 在来源中输入 =INDIRECT(A1) EXCEL日常操作中的常用技巧EXCEL日常操作中的常用技巧在Excel中录入数据时，有时会要求某列单元格中的数据具有唯一性，例如身份证号码、发票号码之类的数据。但是我应该如何做才能确保不会输入重复一样的数据呢？ 【答】：为了保证数据的唯一性，我们可以这样做：选定目标单元格区域（这里假设为A1:A10），依次单击“数据→有效性”，打开“数据有效性”对话框。在“设置”选项卡中单击“允许”下拉列表，选择“自定义”。然后，在“公式”中输入“=countif($A$1:$A$10,$A1)=1”（不包括引号）。接着，单击切换到“出错警告”选项卡，在“样式”中选择“停止”，然后分别在“标题”和“错误信息”中输入错误提示标题和信息。设置完毕后单击“确定”退出。此时，我们再在目标单元格录入数据时，Excel就会自动对数据的唯一性进行校验。当出现重复数据时，Excel中会出现前面设置的错误提示信息。 EXCEL日常操作中的常用技巧EXCEL日常操作中的常用技巧当 Microsoft Excel 导入或合并多条记录时，可能会建立重复的记录。本文介绍如何 建立不重复记录的汇总列表。 要建立不重复记录的汇总列表，请按照下列步骤操作： 1. 选择要排序的行或记录上方的列标题。 2. 在数据菜单中，指向筛选，然后单击高级筛选。如果收到如下提示，请单击确定。 未 检测 工程第三方检测合同工程防雷检测合同植筋拉拔检测方案传感器技术课后答案检测机构通用要求培训 到任何标题。能不能将选定区域的首行用作标题行？ 3. 在方式下，单击“将筛选结果复制到其他位置”。 4. 单击以选中“选择不重复的记录”复选框。 5. 在“列表区域”框中，键入或选择一个记录范围（如 A3:H10）。 6. 在“条件区域”框中，键入或选择相同的记录范围（即 A3:H10） 7. 在“复制到”框中，键入用于存放信息的第一个单元格的地址，或直接选择该单元格。请确保该区域有足够的空间，以免覆盖已存在的数据。 留心：只能在活动的工作表中执行此操作。 8. 单击确定。 如果原始数据的第一条记录是重复的，则该记录在新列表中将出现两次。只潜藏第一行。 如果就地执行高级筛选命令，工作表仍包含所有记录。重复的记录将被潜藏。要变通解决这一问题，请指向“格式”菜单上的“行”，然后单击“取消潜藏 ”。 Excel图表应用Excel图表应用所谓制作图表就是把在Excel文件中的数据用图形表示出来，使之更直观醒目。制作出来的图表可以是柱形图、条形图、饼图、圆柱图、散点图、折线图甚至三维图等。在Excel中把图表做好后，就可以把它们复制到Word文档中去。 制作图表也有专用的图表工具，在Excel窗口可以用“视图”→“工具栏”的命令调出“图表”工具。但是，如果不是经常作图表，最好利用“图表向导”来操作，只需四步操作即可完成一个图表。方法如下： Excel图表应用Excel图表应用1.首先要在Excel窗口输入拟建立图表的数据（见下图，这是职工文化水平的数据，是我杜撰的），并选定它； 2.用“插入”→“图表”命令调出“图表向导 4步骤之1_图表类型”。本例选得是“柱形图”，而且还按下了“按下不放可查看示例”按钮，因此，在图中立刻就把数据变成图表的样子，供你参考了。“下一步”； Excel图表应用Excel图表应用Excel图表应用Excel图表应用3.进入“图表向导 4步骤之2_图表源数据”。在“系列产生在”选项中选择一个你中意的后，“下一步”； Excel图表应用Excel图表应用4. .进入“图表向导 4步骤之3_图表选项”。有“标题”、“网格线”等。可以根据需要做出选择。本例选择没有网格线，“下一步”；Excel图表应用Excel图表应用5. 进入“图表向导 4步骤之4_图表位置”，即你打算把这个图表往哪里插，本例选择了“作为新工作表插入”。选定后即按“完成”钮，这样图表就完成了。数据透视表数据透视表什么是数据透视表　 可以把Excel想象成一个很大的工具箱，它包含我们要使用的各种工具。数据透视表本质上是Excel工具箱中的一个工具。 通过数据透视表查看数据集时，有可能看到之前没有注意到的数据细节，此外还可以旋转数据透视表，以便从不同的透视角度查看数据。 使用数据透视表能够建立数据集的交互视图。我们称这个视图为数据透视报表，从而我们可以方便地将数据分组，汇总大量的数据形成有用的信息，在很很短的时间内进行各种计算。　数据透视表数据透视表何时应该使用数据透视表　 有大量交易数据，已很难通过有意义的方式分析和汇总 需要找出数据内部的关系并分组 需要找出数据中某一字段的一系列特定值 需要利用各种时间周期找出数据趋势 预期要频繁地查询数据分析的变化 需要创建常常包含新增加部分数据的分类汇总 需要将数据组织成易于制成图表的格式　　 数据透视表数据透视表为数据透视表报表准备数据 确保数据以表格形式布局　 避免在分节标题中存储数据　　 消除数据源中的空白和空单元格　 有效的数据源设计要点 数据源的第一行由字段标签或者各列信息的标题组成 数据源中的每一列表示惟一一类数据 数据源中的每一行表示各列中的单独一项　null