计算机办公软件Excel基础培训.ppt

《计算机办公软件Excel基础培训.ppt》由会员分享，可在线阅读，更多相关《计算机办公软件Excel基础培训.ppt（38页珍藏版）》请在三一办公上搜索。

1、Excel基础培训,主讲:肖蓉晖,陕西汽车集团温州云顶汽车有限公司,培训内容,本专题旨在让同事们掌握EXCEL在日常工作中的经常用到的一些功能及技巧，提高Excel实际操作能力的同时提高工作效率。,掌握并熟练运用Excel常用技巧,数据透视表的应用,图表的应用,常用菜单命令的使用,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函数即

2、是预先定义，执行计算、分析等处理数据任务的特殊公式。2、什么是公式函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。公式是以“=”号为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式，函数则是按特定算法进行计算的产生一个或一组结果的预定义特殊公式。在Excel2003中，函数共分为11个大类，函数与公式技巧比较熟练的用户在面对特定的问题时总是会自觉地想到所要使用的特定函数所在分类。,常用函数,1、MAX用途：返回数据集中的最大数值。语法：MAX(number1，number2，.)参数：Number1，numb

3、er2，.是需要找出最大数值的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=

4、国投罗钾，则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函数可以

5、用于双字节字符。语法：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根据所指定的字符数返回文

6、本串中最后一个或多个字符。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用途：将数值转换为按指定

7、数字格式表示的文本。语法：TEXT(value，format_text)。参数：Value是数值、计算结果是数值的公式、或对数值单元格的引用；Format_text是所要选用的文本型数字格式，即“单元格格式”对话框“数字”选项卡的“分类”列表框中显示的格式，它不能包含星号“*”。注意：使用“单元格格式”对话框的“数字”选项卡设置单元格格式，只会改变单元格的格式而不会影响其中的数值。使用函数TEXT可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。实例：如果A1=2986.638，则公式“=TEXT(A1，#，#0.00)”返回2，986.64。,常用函数,8、DATEDIF函数 函

8、数名称：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、、工龄等非常有效。,常用函数,9、LOOKUP用途：返回向量(单行区域或单列区域)或数组中的数值。该函数有两种语法形式：向量和数组，其向量形式是在单行区域或单列区域(向量)中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；其数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。语法1(向量形式)：LOOKUP(lookup_value，lookup_vector，result_vector),常用函数,语法2(数组形式)：LOOKUP(lookup_value，array)。参数1(向量形式)：Lookup_value为函数LOOKUP在第

10、一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector为只包含一行或一列的区域。Lookup_vector的数值可以为文本、数字或逻辑值。参数2(数组形式)：Lookup_value为函数LOOKUP在数组中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。如果函数LOOKUP找不到lookup_value，则使用数组中小于或等于lookup_value的最大数值。Array为包含文本、数字或逻辑值的单元格区域，它的值用于与lookup_value进行比较。注意：Lookup_vecto

11、r的数值必须按升序排列，否则LOOKUP函数不能返回正确的结果，参数中的文本不区分大小写。,lookup函数实例,常用函数,10、COUNTIF用途：统计某一区域中符合条件的单元格数目。语法：COUNTIF(range，criteria)参数：range为需要统计的符合条件的单元格数目的区域；Criteria为参与计算的单元格条件，其形式可以为数字、表达式或文本(如36、160和男等)。其中数字可以直接写入，表达式和文本必须加引号。实例：假设A1:A5区域内存放的文本分别为女、男、女、男、女，则公式“=COUNTIF(A1:A5，女)”返回3。,常用函数,11、IF函数：“=IF()”是条件函

12、数，最多可套嵌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”，也可以使用对区域或区域

13、名称的引用，例如数据库或数据清单。,常用函数,“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值#VALUE!；如果大于区域的列数，函数VLOOKUP返回错误值#REF!。“逻辑值”：为TRUE或FALSE。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。如果“

14、查找值”为文本时，“逻辑值”一般应为 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为需要在数据表中查找的数值，它可

15、以是数值(或数字、文本或逻辑值)、对数字、文本或逻辑值的单元格引用。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的最小数值。注意：MA

16、TCH函数返回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函数

17、：用途：1、（常规应用）在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。2、（变通应用）多条件求和。语法：SUMPRODUCT(array1，array2，array3，.)参数：Array1，array2，array3，.为2至30个数组，其相应元素需要进行相乘并求和。1 数量 单价2 1233 2454 478销售总额=SUMPRODUCT(A2:A6,B2:B6)=662,EXCEL日常操作中的常用技巧,大量的数据输入是一件比较繁琐的工作，除了提高输入的速度外，还可以使用Excel提供的几种用于辅助输入的方法：1、不连续单元格填充同一数据：选中一个单元格，按住Ctrl键，用鼠

18、标单击其他单元格，就将这些单元格全部都选中了。2、快速输入的技巧：输入范围的选定，Ctrl+单元格；如果需要输入的文本和上一个单元格的文本相同，直接按下“CtrlD（或R）”键就可以完成输入，其中“Ctrl+D”是向下填充，“Ctrl+R”是向右填充。如果多个单元格需要输入同样的文本，我们可以在按住Ctrl键的同时，用鼠标点击需要输入同样文本的所有单元格，然后输入该文本，再按下“Ctrl+Enter”键即可，右键下拉列表选项快速录入。,EXCEL日常操作中的常用技巧,3、单元格内容的合并：方法一：连字符“&”合并，方法二：利用CONCATENATE函数。=CONCATENATE(B2,C2)4

19、、单元格内容的分列：方法一：工具菜单（数据-分列），方法二：LEFT、RIGHT函数。例如aaa-111 B1=left（A1,3）C1=RIGHT(A1,3)5、格式：自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。灵活运用好自定义格式功能，将会给实际工作带来很大的方便。,EXCEL日常操作中的常用技巧,6、单元格错误信息函数处理：,EXCEL日常操作中的常用技巧,7、数字、文本的快速转换：数字转变为文本格式（如身份证号）数字前加“”，文本格式转变为数字格式：（1、选择性粘贴，2、函数：VALUE(TEXT)）。8、建立分类下拉列表：1、方法一2、方法二（分级菜单

20、INDIRECT()应用）9、“m2”、“m3”、“C”快速输入：ALT+178、179、186。10、禁止复制隐藏行或列中的数据：选中要复制的区域，单击“编辑定位”命令，在打开的对话框中点击“定位条件”按钮，选中“可见单元格”选项，然后复制粘贴。,EXCEL日常操作中的常用技巧,11、避开EXCEL误差：1、利用Round函数对中间值四舍五入2、正确设置Excel选项来避开“误差”。上述函数方法虽然方便，不过遇上大型Excel表格时，如果每个单元格都要设置这个函数，工作量未免太巨大，这时我们就可以进入Excel的工具菜单选项重新计算，在“工作簿选项”处勾选“以显示精度为准”，上述Excel“

21、误差”也可有效避免，而默认情况下这个小选项是没有被勾选的12、统计不重复数据个数：=SUM(1/COUNTIF(A1:A12,A1:A12)然后按下ctrl+shift+enter键可在选中单元格里看到统计值13、分级菜单 插入-名称-指定,选择首行然后返回B1数据有效性-序列在来源中输入=INDIRECT(A1),EXCEL日常操作中的常用技巧,在Excel中录入数据时，有时会要求某列单元格中的数据具有唯一性，例如身份证号码、发票号码之类的数据。但是我应该如何做才能确保不会输入重复一样的数据呢？【答】：为了保证数据的唯一性，我们可以这样做：选定目标单元格区域（这里假设为A1:A10），依次单

22、击“数据有效性”，打开“数据有效性”对话框。在“设置”选项卡中单击“允许”下拉列表，选择“自定义”。然后，在“公式”中输入“=countif($A$1:$A$10,$A1)=1”（不包括引号）。接着，单击切换到“出错警告”选项卡，在“样式”中选择“停止”，然后分别在“标题”和“错误信息”中输入错误提示标题和信息。设置完毕后单击“确定”退出。此时，我们再在目标单元格录入数据时，Excel就会自动对数据的唯一性进行校验。当出现重复数据时，Excel中会出现前面设置的错误提示信息。,EXCEL日常操作中的常用技巧,当 Microsoft Excel 导入或合并多条记录时，可能会建立重复的记录。本文介

23、绍如何 建立不重复记录的汇总列表。要建立不重复记录的汇总列表，请按照下列步骤操作：1.选择要排序的行或记录上方的列标题。2.在数据菜单中，指向筛选，然后单击高级筛选。如果收到如下提示，请单击确定。未检测到任何标题。能不能将选定区域的首行用作标题行？3.在方式下，单击“将筛选结果复制到其他位置”。4.单击以选中“选择不重复的记录”复选框。5.在“列表区域”框中，键入或选择一个记录范围（如 A3:H10）。6.在“条件区域”框中，键入或选择相同的记录范围（即 A3:H10）7.在“复制到”框中，键入用于存放信息的第一个单元格的地址，或直接选择该单元格。请确保该区域有足够的空间，以免覆盖已存在的数据

24、。留心：只能在活动的工作表中执行此操作。8.单击确定。如果原始数据的第一条记录是重复的，则该记录在新列表中将出现两次。只潜藏第一行。如果就地执行高级筛选命令，工作表仍包含所有记录。重复的记录将被潜藏。要变通解决这一问题，请指向“格式”菜单上的“行”，然后单击“取消潜藏”。,Excel图表应用,所谓制作图表就是把在Excel文件中的数据用图形表示出来，使之更直观醒目。制作出来的图表可以是柱形图、条形图、饼图、圆柱图、散点图、折线图甚至三维图等。在Excel中把图表做好后，就可以把它们复制到Word文档中去。制作图表也有专用的图表工具，在Excel窗口可以用“视图”“工具栏”的命令调出“图表”工具

25、。但是，如果不是经常作图表，最好利用“图表向导”来操作，只需四步操作即可完成一个图表。方法如下：,Excel图表应用,1.首先要在Excel窗口输入拟建立图表的数据（见下图，这是职工文化水平的数据，是我杜撰的），并选定它；2.用“插入”“图表”命令调出“图表向导 4步骤之1_图表类型”。本例选得是“柱形图”，而且还按下了“按下不放可查看示例”按钮，因此，在图中立刻就把数据变成图表的样子，供你参考了。“下一步”；,Excel图表应用,Excel图表应用,3.进入“图表向导 4步骤之2_图表源数据”。在“系列产生在”选项中选择一个你中意的后，“下一步”；,Excel图表应用,4.进入“图表向导 4

26、步骤之3_图表选项”。有“标题”、“网格线”等。可以根据需要做出选择。本例选择没有网格线，“下一步”；,Excel图表应用,5.进入“图表向导 4步骤之4_图表位置”，即你打算把这个图表往哪里插，本例选择了“作为新工作表插入”。选定后即按“完成”钮，这样图表就完成了。,数据透视表,什么是数据透视表可以把Excel想象成一个很大的工具箱，它包含我们要使用的各种工具。数据透视表本质上是Excel工具箱中的一个工具。通过数据透视表查看数据集时，有可能看到之前没有注意到的数据细节，此外还可以旋转数据透视表，以便从不同的透视角度查看数据。使用数据透视表能够建立数据集的交互视图。我们称这个视图为数据透视报

27、表，从而我们可以方便地将数据分组，汇总大量的数据形成有用的信息，在很很短的时间内进行各种计算。,数据透视表,何时应该使用数据透视表有大量交易数据，已很难通过有意义的方式分析和汇总需要找出数据内部的关系并分组需要找出数据中某一字段的一系列特定值需要利用各种时间周期找出数据趋势预期要频繁地查询数据分析的变化需要创建常常包含新增加部分数据的分类汇总需要将数据组织成易于制成图表的格式,数据透视表,为数据透视表报表准备数据确保数据以表格形式布局避免在分节标题中存储数据消除数据源中的空白和空单元格有效的数据源设计要点数据源的第一行由字段标签或者各列信息的标题组成数据源中的每一列表示惟一一类数据数据源中的每一行表示各列中的单独一项,