EXCEL常用功能讲解(共66张)课件

1、EXCEL常用功能讲解蒋芳 2011-10-25第1页，共67页。EXCEL水平测试10个EXCEL问题（每个2分） 1、您懂得使用数据透视功能进行灵活、快速的数据汇总2、您很清楚EXCEL2003表格具有256列的数据能力3、您可以区分柱状图与饼形图的数据表述区别4、您可以区分普通图柱状图和百分比堆积柱状图数据表述区别5、您经常使用CRTL+1这个快捷键6、您善于使用IF函数进行数据条件处理7、您善于使用VLOOKUP函数进行数据处理8、您懂得通过条件格式进行录入字符的控制9、您懂得通过保护工作表控制单元格录入限制10、您清楚EXCEL2003文件格式为“.xls”,而EXCEL2007为“

2、.xlsx”第2页，共67页。EXCEL水平测试结果测试评定A类：0-3分您可以转头走了，就当没参加过这个测试吧。如果您真想在EXCEL实战方面有所提高，或者必须面对EXCEL工作，那您可真需要下大功夫了。 B类：4-8分您应该具有一些EXCEL实战能力，不过经验尚有不足，当您面对稍有艰巨的EXCEL业务数据处理或PPT商务呈现任务的时候可能会稍许感觉头疼，胸闷，无从下手，一般得借助别人支持或援助才可渡过难关。 C类：9-12分您应该具备了一定的EXCEL实战经验，或者参加过专业培训学习，具有一的EXCEL商务办公实战能力，可以应对日常商务OFFICE工作。 D类：13-16分您应该是企业中的

3、OFFICE高手，同事中的榜样，您可以较为自如的处理EXCEL日常工作问题，或找到解决建议。经过深入训练，应该可以达到专业级EXCEL水平。 E类：17-20分E类：高手，绝对的OFFICE高手！第3页，共67页。EXCEL高手的必修课第4页，共67页。1234特殊输入及字符处理提高工作效率的快捷方式提高工作效率的快捷键操作EXCEL大礼包：Excel提高实例快速输入技巧5数据处理第5页，共67页。1、直接输入字符串的方法，先输入单引号2、输入分数，0 加空格；分式运算3、巧用字符连接运算符 &（1）给每个数字（字符）前添加相同字符串 abc =abc &A2（2）把每个数字转换成9位字符，不

4、足者前面添0 =REPT(0,9-LEN(A2) &A24、在相对引用、绝对引用和混合引用间切换 （ $ ）。$A$1绝对列和绝对行 A$1相对列和绝对行$A1绝对列和相对行 A1相对列和相对行一、特殊输入及字符处理注意：输入字符串时，请确保是否在英文的半角状态下输入。第6页，共67页。5、填充，例如：（1）如果您希望使用序列 1、 2、 3、 4，请在前两个单元格中键入 1 和 2；（2）如果您希望使用序列 2、 4、 6、 8，请在前两个单元格中键入 2 和 4；（3）如果您希望使用序列 1、1、1、1 ，请在前两个单元格中键入 1 和 1；（4）如果您希望在一个区域内全部输入1，请选中该

5、区域，输入1，,按 Ctrl+Enter 键, 此法可对多工作表多区域同时输入一、特殊输入及字符处理第7页，共67页。1、高效选择区域（1）当区域较小时，可直接用鼠标拖动选择（2）区域稍大，但在屏幕范围内，用鼠标点击左上角单元格，按住Shift键点击右下角单元格（3）不连续区域选择：按住 Ctrl键用鼠标选择或在名称框输入各区域名称，区域名称间用逗号分隔如： A3:B7,A10:D17（4）选定从工作表开始到结尾的方法：选中开始单元格（如A21），同时按下ctrl+shift+end. ctrl+shift+home从工作表结尾到开始.（5）ctrl+shift+（向左小箭头）， ctrl+s

6、hift+（向右小箭头）， ctrl+shift+（向上小箭头）， ctrl+shift+（向下小箭头）二、提高工作效率的快捷方式请记住：（1）CLTR+箭头表示快速到达；CHTRL+SHIFT+箭头表示快速选定。（2）用Shift配合鼠标选择连续区域或连续的工作表，用Ctrl键配合鼠标选择不连续区域或不连续的工作表第8页，共67页。2、录入与公式填充键盘：Ctrl+D 复制上一单元格的公式或内容；填充纵向区域（与双击黑十字功能相仿）键盘：Ctrl+R;复制左边单元个的公式或内容；填充横向区域3、批量处理数据（1）给区域每个单元格的值均输入100000（2）将区域的数字折成千元在区域外的任意单

7、元格（如：A22）输入1000,并复制该单元格；选中区域，选择性粘贴-除，确定4、工作表（1）按住Ctrl,用鼠标左键拖动屏幕下边的工作表标签，可复制一个相同的表格；（2）选中几个工作表再进行画表等操作，可对多个工作表进行操作；（3）选中多个工作表设置页脚页码并打印预览，工作簿页码连续；二、提高工作效率的快捷方式第9页，共67页。5、如何使空白单元格等于上一个单元格 （1）选中数据区域（2）CTRL+G打开定位窗口，定位条件，空值（3）输入“+”。（4）选中上一单元格。 即：=+要等于的单元格二、提高工作效率的快捷方式第10页，共67页。6、建立分类下拉列表填充项。“数据有效性”(1)、在Sh

8、eet2中，将企业建立一个企业名称数据库。(2)、选中A列(“工业企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中，”名称所在列)，在“名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。 仿照上面的操作，将B、C列分别命名为“商业企业”、“个体企业”(3)、切换到Sheet1中，选中需要输入“企业类别”的列(如C列)，执行“数据有效性”命令，打开“数据有效性” 对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”序列(各元素之间用英文逗号隔开)，确定退出。 再选中需要输

9、入企业名称的列(如D列)，再打开“数据有效性”对 话框，选中“序列”选项后，在“来源”方框中输入公式：=INDIRECT(C1)，确定退出。二、提高工作效率的快捷方式第11页，共67页。6、建立分类下拉列表填充项。“数据有效性”(4)、选中C列任意单元格(如C4)，单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。然后选中该单元格对应的D列单元格(如D4)，单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。 提示：在以后打印报表时，如果不需要打印“企业类别”列，可以选中该列，右击鼠标，选“隐藏”选项，将该列隐藏起来即可。二、提高工作效率的快捷方式第12页，共67

10、页。1、ALT系列快捷键（1）Alt+enter拆分同一单元格内多行文字（2）Alt+=，自动求和（3）Alt+0165 = （4）ALT+0178=（5）ALT+0179=（6）ALT+0177=（7）ALT+0137=2、SHIFT系列快捷键（1）按住shift拖动单元格=剪切+插入单元格+粘贴（2）选中一个单元格，按shift+F2键，可以插入批注。三、提高工作效率的快捷键第13页，共67页。3、CTRL系列快捷键（1）按住ctrl+shift拖动单元格=复制+插入单元格+粘贴（2）Ctrl +9隐藏一行单元格 Ctrl+Shift+9取消隐藏行（3）Ctrl+0隐藏一列单元格 Ctrl

11、+Shift+0取消隐藏列（4）Ctrl+;输入今天的日期 Ctrl+Shift+;输入当前的时间（5）Ctrl+“-”（减号）删除单元格/行/列 Ctrl+Shift +“+”（加号）插入单元格/行/列（6） Ctrl+1快速打开格式对话框（7）+可以将当前工作表中的公式显示出来,再按+则恢复.（8）Ctrl+f9最小化窗口（9）Ctrl+f10最大化和还原窗口 (10) Ctrl+Shift+_删除选定单元格区域的边框三、提高工作效率的快捷键第14页，共67页。3、CTRL系列快捷键（1）Ctel+Tab 可在程序与程序，表簿与表簿之间转换打开。（2)使用ctrl+PageDown和ctr

12、l+PageUp可以在工作表之间向前向后切换（3)如果你用的是带滚轮的鼠标，那么先按住“Ctrl”键，再拨动滚轮吧！可以快速改变显示比例哟！注意:有时在中文输入状态下会有所冲突,将输入法转换为英文即可.三、提高工作效率的快捷键第15页，共67页。4、功能键系列快捷键 F2用来修改单元格内容，像改文件名一样简单 F3用来打开粘贴名称对话框 F4重复上一步操作.这招在隔行删除、多点复制等方面用起来很爽 F5用来打开定位对话框5、工具选项使用技巧（1）工具-选项-视图中,在“窗口选项”下去掉“零值”后,余额为0就不显示了。（2）工具选项视图网格线前面的对勾去掉可以不显示网格线（3）工具选项批注.三、

13、提高工作效率的快捷键第16页，共67页。1、快速输入大量含小数点的数字 单击“工具”/“选项”/“编辑”标签，在弹出的对话框中选中“自动设置小数点”复选框，然后在“位数”微调编辑框中键入需要显示在小数点右面的位数就可以了。以后我们再输入带有小数点的数字时，直接输入数字，而小数点将在回车键后自动进行定位2、快速输入大量相同数据 首先同时选中需要填充数据的单元格。若某些单元格不相邻，可在按住Ctrl键的同时，点击鼠标左键，逐个选中；其次输入要填充的某个数据，按住Ctrl键的同时，按回车键，则刚才选中的所有单元格同时填入该数据3、快速删除工作表中空行 4、快速输入多个重复数据 （录制宏）5、快速处理

14、多个工作表 四、快速输入技巧第17页，共67页。五、数据处理1、自动填充2、定义名称3、数据有效性4、自定义格式5、条件格式6、分类汇总7、合并计算8、数据透视表第18页，共67页。五、数据处理1、自动填充 等差填充 日期填充 文字填充 特定填充第19页，共67页。五、数据处理2、定义名称1、插入名称。 “插入”-“名称”-“定义”2、使用名称框命名3、指定名称。 “插入”-“名称”-“指定”第20页，共67页。五、数据处理2、定义名称全局定义 与 局部定义：1、默认情况下，为全局定义2、局部定义命名方法：工作表名称+半角感叹号+名称命名规则：1、可用数字与字母组合，但不能以数字开头；2、不能

15、以R、C、r、c作为名称，如R、C在R1C1引用样式中表示行、列；3、名称中不能包含空格，可用下划线或点号代替；4、不能使用除了下换线、点号和反斜杠（）意外的其他符号。允许使用问号，但不能作为名称的开头；5、名称字符不能超过255个；6、字母不区分大小写。第21页，共67页。五、数据处理3、数据有效性第22页，共67页。五、数据处理3、数据有效性停止不能输入不符合条件的数据，点击“重试”可重新输入，点击“取消”则取消输入。警告可以选择是否继续输入数据，点击“是”可以强行输入不符合条件数据，点击“否”可以重新输入数据，点击“取消”则取消输入。信息可选择是否输入数据，点击“确定”可以直接输入数据，

16、点击“取消”可以取消输入。第23页，共67页。五、数据处理4、自定义格式 可为4种类型的数值指定不同的格式：正数、负数、零值和文本。“大于条件值”格式；“小于条件值”格式；“等于条件值”格式；文本格式 格式代码组成结构：注：没有特别指定条件值的时候，默认条件值为0 节代码与节代码之间用英文状态下的分号“;”隔开。快捷键 Ctrl+1实战练习：编制设备编号，不足10位，编号前补0，并在10位编号前统一加“No：”。第24页，共67页。五、数据处理5、条件格式格式条件格式第25页，共67页。五、数据处理6、分类汇总可手动创建分级显示和自动生成分级显示。 注意：1、一个工作表内在同行方向上或列方向上

17、只能创建一个分级显示；2、在同一个分级显示中，最多允许有8个层次关系 第26页，共67页。五、数据处理7、合并计算例：年销售产品的统计计算第27页，共67页。五、数据处理8、数据透视表 数据透视表是一个功能非常强大的数据分析工具，它是集排序、筛选、分类汇总及合并计算为一体的综合性数据分析工具。第28页，共67页。1234查找和引用函数逻辑运算符日期与时间函数如何利用函数操作文本函数5计数和求和函数EXCEL大礼包：EXCEL函数大全6其它函数第29页，共67页。一、Vlookup函数1、用途：VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。2

18、、语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)3、参数： Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。Table_array为需要查找的区域，可以使用对区域或区域名称的引用。Col_index_num为查找区域中待返回的匹配值的列序号。Range_lookup为一逻辑值，指明查找时是精确匹配，还是近似匹配。 一、查找和引用函数第30页，共67页。二、 Match函数1、用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置。2、语法：MATCH(lookup_value，look

19、up_array，match_type)3、参数： lookup_value ：为要搜索的值。 lookup_array：要查找的区域(必须是一行或一列)。 match_type：匹配形式，有0、1和1三种选择：0表示一个准确的搜索。1表示搜索小于或等于查换值的最大值，查找区域必须为升序排列。1表示搜索大于或等于查找值的最小值，查找区域必须降序排开。以上的搜索，如果没有匹配值，则返回N/A。 一、查找和引用函数第31页，共67页。三、 Transpose函数1、用途：返回区域的转置。2、语法：TRANSPOSE(array)3、参数：Array是需要转置的数组或工作表中的单元格区域 注意：必须

20、以数组的公式输入。请选中以公式单元格开始的区域，按 F2，再按 Ctrl+Shift+Enter一、查找和引用函数第32页，共67页。四、 ADDRESS、COLUMN、ROW函数1、 ADDRESS函数 用于按照给定的行号和列标，建立文本类型的单元格地址。 ADDRESS(row_num,column_num,abs_num,a1,sheet_text)如： ADDRESS(4,5,1,FALSE,“Book1Sheet1”)， Book1Sheet1!R4C5 ADDRESS(4,5,2,FALSE,“Book1Sheet1”)， Book1Sheet1!R4C5 ADDRESS(4,5,

21、3,FALSE,“Book1Sheet1”) ，Book1Sheet1!R4C5 ADDRESS(4,5,4,FALSE,“Book1Sheet1”) ，Book1Sheet1!R4C5 ADDRESS(4,5,1,TRUE,“Book1Sheet1”) ，Book1Sheet1!$E$4 2、 COLUMN函数 用于返回给定引用的列标3、 ROW函数 用于返回给定引用的行号一、查找和引用函数第33页，共67页。一、 AND、OR、NOT函数AND：所有参数的逻辑值为真时返回 TRUE；OR： 任何一个参数逻辑值为 TRUE，即返回 TRUE；NOT：对参数值求反二、IF函数用途： 执行真假值

22、判断后，根据逻辑测试的真假值返回不同的结果语法：IF(logical_test，value_if_true，value_if_false)。注意：IF函数可以嵌套七层COUNTIF用途：统计某一区域中符合条件的单元格数目。语法：COUNTIF(range，criteria)二、逻辑运算符第34页，共67页。1、日期和时间都仅仅是数字而已 日期：从1900-1-1以1开始;时间：从0:00:00以0开始2、显示时间 1）Now：显示当前系统的日期和时间; 如何只显示当前时间？=now()-today() 2）Time：显示指定数值的时间 语法：time(hour,minute,second)3、

23、显示日期 1）Today：显示当前系统的日期; 2）Date：显示指定数值的日期； 3）Year：得到日期的年数 4）Month：得到日期的月数 5）Day：得到日期的天数三、日期与时间函数第35页，共67页。1、如何删除Excel不能打印的字符？ Clean函数，语法：clean（text）2、如何确定两单元格是否相同？ 1）用运算符“=”判断；不区分大小写 返回结果：“TURE”、“FALSE” 2）用EXACT函数比较；区分大小写语法：exact（text1,text2） 返回的结果：“TURE”、“FALSE”3、如何连接多个字符串？ 1）用运算符“&”连接； 2）用CONCATENA

24、TE函数连接；语法：concatenate（text1,text2text30）四、如何利用函数操作文本第36页，共67页。4、如何转换文本大小写？ 1）LOWER：所有大写转换为小写；语法：lower（text） 2）UPPER：所有小写转换为大写；语法：upper（text） 3）PROPER：首字母及任何非字母后的首字段转换大写，其他字母转换为小写；语法：proper（text）5、如何取出单元格中的部分字符？ 1）Left：从左边开始截取指定数目的字符； 语法：left（text,num_chars） 2）Right：从右边开始截取指定数目的字符；语法：right（text,num_c

25、hars） 3）Mid：从文本指定位置开始截取指定数目的字符 语法：mid（text,start_num,num_chars）四、如何利用函数操作文本第37页，共67页。1、常用的计数函数 1）Count：统计时不包括空白、文本、 逻辑值和错误; 语法：count（value1,value2） 2）Counta：统计时仅不包括空白单元格； 语法： counta（value1,value2） 3）两者区别2、指定条件的计数函数 1）Countif 语法：countif（range,criteria） 2）举例五、计数与求和函数第38页，共67页。3、常用的求和函数 1）Sum：进行基本数值求和;

26、 语法：sum（number1, number2） 2）Sumif：根据条件求和； 语法：sumif（range,criteria,sum_range）4、分类汇总求和（计算） Subtotal函数 1）语法：subtotal(function_num,ref1,ref2) 2）Function_num的常用类型 1 求平均(average) 4 求最大值(max) 2 计数(count) 5 求最小值(min) 3 计数(counta) 9 求和(sum)五、计数与求和函数第39页，共67页。1、常用的数学函数 1）Rand:生成随机数; 2）Int:将数值向下取整为最接近的整数； 3）Tr

27、unc:向下舍入（沿绝对值减少的方向） 4）Round：按指定位数四舍五入 5）Roundup：向上舍入（沿绝对值增大的方向） 6）Rounddown：向下舍入（沿绝对值减小的方向） 7）Abs:生成绝对值; 8）Mod:求两数相除后的余数；2、其他数学函数 1）Average:求平均数,注意不包括空白和字符 2）Max：查找一组数的最大值 3）Min：查找一组数的最小值六、其它函数第40页，共67页。3、IS类函数用途：其中包括用来检验数值或引用类型的九个工作表函数。函数如果为下面的内容，则返回 TRUEISBLANK值为空白单元格。ISERR值为任意错误值（除去 #N/A）。ISERROR

28、值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。ISLOGICAL值为逻辑值。ISNA值为错误值 #N/A（值不存在）。ISNONTEXT值为不是文本的任意项（注意此函数在值为空白单元格时返回 TRUE）。ISNUMBER值为数字。ISREF值为引用。ISTEXT值为文本。六、其它函数第41页，共67页。12数据透视表图表的制作图表EXCEL大礼包：数据处理与分析第42页，共67页。一、数据透视表想看各种数据之间的交叉、组合关系BOSS随时要求你提供某一数据汇总每周、每月要编制数据报告每天上班第一件事，打开Excel，输入数据

29、1、ERP?2、OA？3、专用统计软件？4、Excel筛选、运算、摘录？数据透视表 Excel自带 独立于原始数据 快速汇总 想看啥就看啥第43页，共67页。一、数据透视表为数据透视表报表准备数据确保数据以表格形式布局避免在分节标题中存储数据消除数据源中的空白和空单元格有效的数据源设计要点数据源的第一行由字段标签或者各列信息的标题组成数据源中的每一列表示惟一一类数据数据源中的每一行表示各列中的单独一项第44页，共67页。一、数据透视表创建数据透视表1、选中数据源（默认数据源区域为已选中的整个列表）2、菜单选择及步骤说明（数据数据透视表和数据透视图）3、框架及工具栏说明4、内容拖放5、格式设置第

30、45页，共67页。一、数据透视表图示第46页，共67页。一、数据透视表工具应用系列by WQY47数据透视表框架数据透视表工具栏数据透视表的框架数据透视表字段列表对话框第47页，共67页。一、数据透视表数据透视表的结构值区域行区域列区域报表筛选区域第48页，共67页。一、数据透视表内容拖放第49页，共67页。一、数据透视表格式设置数据汇总方式设置确保正确报告格式设置美观第一第50页，共67页。一、图表的8项要素图表标题 坐标轴Y 坐标轴X 图例 绘图区 图表区 数据系列 1、常见的6项要素二、图表的制作第51页，共67页。二、图表的制作2、数据表要素数据表 第52页，共67页。第53页二、Ex

31、cel的各种图表类型 Excel提供有14种标准图表类型（73种子图表类型）及20种自定义图表类型。二、图表的制作第53页，共67页。第54页1、柱形图 柱形图反映一段时间内数据的变化，或者不同项目之间的对比，是最常见的图表之一，是Excel的默认图表。2、条形图 条形图也是显示各个项目之间的对比，与柱形图不同的是其分类轴设置在纵轴上，而柱形图则设置在横轴上。3、折线图 折线图按照相同的间隔显示数据的趋势。二、图表的制作第54页，共67页。第55页4、饼图 饼图显示组成数据系列的项目在项目总和中所占的比例，通常只显示一个数据系列。5、XY散点图 XY散点图显示若干个数据系列中各个数值之间的关系

32、，或者将两组数据绘制为XY坐标的一个系列。6、面积图 面积图显示数值随时间或类别的变化趋势。二、图表的制作第55页，共67页。第56页7、圆环图 与饼图一样，圆环图也显示部分和整体之间的关系，但是圆环图可包含多个数据系列。8、雷达图 雷达图显示数值相对于中心点的变化情况。9、曲面图 曲面图在连续曲面上跨两维显示数据的变化趋势。10、气泡图 气泡图是一种XY散点图，以3个数值为一组对数据进行比较。气泡的大小表示第3个变量的值。二、图表的制作第56页，共67页。第57页11、股价图股价图通常用于显示股票价格及其变化的情况，但也可以用于科学数据（如表示温度的变化）。12、圆柱图圆柱图使用圆柱图形来增

33、强柱形图、条形图和三维柱形图等的立体效果。13、圆锥图 圆锥图使用圆锥图形来增强柱形图、条形图和三维柱形图等的立体效果。14、棱锥图 棱锥图使用圆锥图形来增强柱形图、条形图和三维柱形图等的立体效果。二、图表的制作第57页，共67页。第58页三、认清坐标轴的4种类型 一般情况下图表有两个坐标轴：X轴（刻度类型为时间轴、分类轴或数值轴）和Y轴（刻度类型为数值轴）。 三维图表有第3个轴：Z轴（刻度类型为系列轴）。 饼图或圆环图没有坐标轴。 雷达图只有数值轴，没有分类轴。二、图表的制作第58页，共67页。第59页1、时间轴 时间具有连续性的特点。 在图表中应用时间轴时，若数据系列的数据点在时间上为不连

34、续的，则会在图表中形成空白的数据点。要清除空白的数据点，必须将时间轴改为分类轴。 时间轴和分类轴相互转化的方法：单击图表，再单击菜单栏上的“图表”“图表选项”命令，在弹出的“图表选项”对话框的“坐标轴”选项卡中单击“分类”或“时间刻度”单选按钮。二、图表的制作第59页，共67页。第60页2、分类轴 分类轴显示数据系列中每个数据点对应的分类标签。 若分类轴引用的单元格区域包含多行（或多列）文本，则可能显示多级分类标签。二、图表的制作第60页，共67页。第61页 除了饼图和圆环图外，每幅图表至少有一个数值轴。 若数据系列引用的单元格包含文本格式，则在图表中绘制为0值的数据点。二、图表的制作3、数值轴第61页，共67页。第62页 三维图表的系列轴仅是显示不同的数据系列的名称，不能表示数值。二、图表的制作3、系统轴第62页，共67页。七、分享几个好用的个人宏1、个人宏的文件名及存放地址 文件名： PERSONAL 存放地址： C:Documents and SettingsMidnight SoulApplication DataMicrosoftExcelXLSTART目录下2、自定义菜单文件名及存放地址 文件名：Excel11.XL