excel使用技巧讲座实用教案

《excel使用技巧讲座实用教案》由会员分享，可在线阅读，更多相关《excel使用技巧讲座实用教案（34页珍藏版）》请在金锄头文库上搜索。

1、Excel简介(jinji)Excel在工作中起的作用，是不言而喻的。灵活的使用Excel可以极大的提高我们的工作效率，以下是我工作之余总结的Excel常用功能，希望对大家工作有所益处。下面讲解( jingji)我会以问题、概念、图例与实操演示等四个方式为大家讲解( jingji)每项功能的使用方法，以便大家能快速掌握。但是在讲解( jingji)之前我先为大家介绍下Excel它到底具有哪些功能？我们对它的功能掌握到了哪一步？第1页/共33页第一页，共34页。Excel用户(yngh)水平的5个层次初步的接触 基本掌握常用功能 熟练使用常用功能+数据透视表+函数 宏+数组公式+VBA简单编程

2、Excel技能+专业知识+行业经验 新手 初级用户 中级用户 高级用户 专家 第2页/共33页第二页，共34页。数据操作录入与导出常规编辑格式化高级编辑自定义环境图表与图形标准图形组合图形图表美化高级图表交互式图表图示公式与函数公式基础函数应用函数嵌套数组公式自定义函数数据分析排序筛选列表数据透视表假设分析高级分析宏与VBA录制运行宏VBA基础Excel对象窗体与控件类模块Excel功能(gngnng)剖析第3页/共33页第三页，共34页。Excel功能(gngnng)讲解Excel常用操作Excel基本函数(hnsh)Excel中插入图表Excel常用技巧函数(hnsh)高级应用创建数据透视

3、表数据处理与分析复杂实例演示基础功能进阶功能进阶功能第4页/共33页第四页，共34页。第一(dy)讲Excel常用操作1、excel常用功能界面介绍，07版与03版的区别(qbi)与相似在哪里？07版界面(jimin)03版界面第5页/共33页第五页，共34页。5、设置单元格格式(g shi) 鼠标左键选择所要改变的区域，右键菜单 设置单元格格式(g shi)。 这里可以对数字、对齐、字体、边框、图案等项目进行设置，助我们制作一个精美的报表。如演示所示3、如何设置具体数值(shz)的行高与列宽？ 鼠标左键选择所要改变的行（或列），右键菜单 行高（列宽）。如图例所示 2、 Shift+Ctrl+

4、方向键有什么功能呢？ 它们实现(shxin)的功能是：从当前单元格开始，按照选定的方向选取数据，遇到第一个空单元格时结束。 如演示所示：4、快速的自动填充序列？ 所谓自动填充简单的理解就是当行或列中的部分数据形成有一定的序列时，我们就可以使用自动填充功能来快速填充数据。 自动填充功能可以实现多种数据的填充，如：序列、单元格格式、年月份、数据的重复录入等。 自动扩展与填充是excel中一项比较重要的功能，这里我会以演示的方式为大家讲解。 如演示所示第6页/共33页第六页，共34页。6、查找与替换功能如何快速调出？ 查找与替换功能是一个常用的功能，这里我只介绍大家(dji)其键盘快捷键:ctrl+

5、F 注：此快捷方式在使用前需选定需要查找的数据区。此功能比较简单。 9、灵活使用状态栏 在Excel 的左下角，有一个状态栏，提供了许多有用的功能与函数对选定的范围进行处理，如：记数，求和，求平均数，求最大/最小值等。在状态栏上，单击鼠标右键就可以调出这个(zh ge)功能，灵活运用这个(zh ge)功能可以节省我们很多工作时间。如下所示：7、如何进行批量数据清除？ Delete键.这是数据清除的快捷键。当数据量相当大的时候，对于误填，错填的数据，每次一个个的清除很是浪费时间，也不利于工作效率的提升，这里我们就是使用delete键，一键清除数据。这里的清除只包括清除单纯数据，不包括清除单元格的

6、格式等。 注：我们可以(ky)搭配使用ctrl键，这样可以(ky)单独选择多个不连续单元格数据或图片清除。8、单元格数据的文本与数字格式的如何互换？ 很多时候当我们从系统中导出的数据，它一般都会默认为文本格式，这样很不方便我们对数据的处理，这里就需要就数据转化为数字格式。如演示所示 第7页/共33页第七页，共34页。10、筛选功能 Excel提供了二种不同(b tn)的筛选方式:自动筛选和高级筛选 自动筛选功能：快捷键：Alt+D+F+F，只能用于条件简单的筛选操作,不能实现字段之间“与”“或”关系的操作，我这里不做介绍，主要说说他的高级筛选的使用办法。 高级筛选功能：快捷键：Alt+D+F+

7、A。 这里我们要求筛选出所有这里我们要求筛选出所有员工中基本工资员工中基本工资(j bn n (j bn n z)z)大于大于2 2，并且绩效工资小于，并且绩效工资小于7 7，以及奖励工资大于等于，以及奖励工资大于等于1010的员的员工，并且隐藏条件不符的员工。工，并且隐藏条件不符的员工。 这里就包含有这里就包含有“与与”“”“或或”的关系，使用自动筛选是很的关系，使用自动筛选是很难办到的，所以在这里使用高级难办到的，所以在这里使用高级筛选就会非常简单。筛选就会非常简单。这里(zhl)是数据区这里是条件区第8页/共33页第八页，共34页。11、选择性粘贴 我们经常会使用一些函数，但是用完函数后

8、，单元格中的数值会包含公式，此时我们会运用选择性粘贴去除函数。选择性粘贴是excel中一项比较重要的功能(gngnng)，使用它我们可以对数据进行多种格式的复制。 以下主要演示粘贴中得运算、行列倒置、粘贴的多种选择等。如图所示12、合并单元格数据 许多情况下我们需要不单单只合并两个单元格，还要合并单元格中的数据，比如(br)合并NCC和BCC，这样我们会用到“&”（和号）运算符。 如下所示这里我们使用了公式函数，函数一讲我会为大家(dji)详细讲解函数的使用办法第9页/共33页第九页，共34页。13、窗口的冻结与拆分 有时候我们在看数据时，希望将某一行数据的冻结不动，此时我们会使用冻结窗口功能

9、，快捷键：Alt+W+F。 拆分功能是将数据区拆分成几块，方便(fngbin)我们对标题或其他数据的对比录入.如图所示14、强制换行 有些时候我们需要在一个单元格中输入较长的文字，此时我们就用到了强制换行功能。选择Alt+Enter即可进行(jnxng)强制换行，没必要再去用空格将文本挤到下一行了15、数据有效性 有时我们可能需要限制输入(shr)到单元格中得数据格式，这就需要数据有效性功能。 第10页/共33页第十页，共34页。16、数据透视表 数据透视表是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关。 例如，可以水平或者垂直显示字段值，然后计

10、算每一行或列的合计；也可以将字段值作为行号或列标，在每个行列交汇处计算出各自的数量，然后计算小计和总计。 若要创建数据透视表，请运行“数据透视表和数据透视图向导”。在向导中，从工作表列表或外部数据库选择源数据。向导然后为您提供报表的工作表区域和可用字段的列表。当您将字段从列表窗口拖到分级显示区域时，Microsoft Excel 自动汇总(huzng)并计算报表。 数据透视表是excel中一个我们经常会使用的功能，他的功能是非常强大的，我在这里为大家简单演示下其数据分析方面的使用办法。第11页/共33页第十一页，共34页。 如有以下数据，现在我们需要统计分析得到这样几个数据？1、员工姓名相同(

11、xin tn)的重复有几个人？他们的保险编号是多少？2、姓名相同(xin tn)的人各自里面谁的工资最高，谁的最低？他们的保险编号是多少？3、姓名相同(xin tn)的人合计工资是多少？电话费是多少？所有总计各项费用多少？第12页/共33页第十二页，共34页。第二讲Excel常用(chnyn)函数 Excel是办公室自动化中非常重要的一款软件(run jin)，很多企业都是依靠Excel进行数据管理。它不仅仅能够方便的处理表格和进行图形分析.其更强大的功能体现在对数据的自动处理和计算，然而很多对Excel强大数据处理功能不了解的人却难以进一步深入。而对Excel函数应用的不了解正是阻挡普通用户

12、完全掌握Excel的拦路虎，希望我的讲解能够对大家对Excel函数学习有所帮助。 Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。共计约500多个函数。 这里我会介绍8个我们工作中常用函数功能及用法，更为复杂的函数操作我在这里不做讲解。第13页/共33页第十三页，共34页。Sum函数（求和函数）主要功能：计算所有参数数值的和。 使用格式：SUM（Number1,Numb

13、er2） 参数说明：Number1、Number2代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。 应用举例：如右图所示，在A10单元格中输入(shr)公式：=SUM(A1:A9)，确认后即可求出以上单元格的和。Sumif函数主要功能：计算符合指定条件的单元格区域内的数值(shz)和。 使用格式：SUMIF（Range,Criteria,Sum_Range） 参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值(shz)所在的单元格区域。 应用举例：如右图所示，分别求出各项费用的合计 1、数学(shxu)函数条

14、件区第14页/共33页第十四页，共34页。Max函数 （Min函数）主要功能：求出一组数中的最大值（或最小值）。 使用格式：MAX(number1,number2) 参数说明(shumng)：number1,number2代表需要求最大值的数值或引用单元格（区域），参数不超过30个。 简单的加减乘除运算的公式使用(shyng) 这里使用(shyng)的符号为: + - * /直接的公式(gngsh)是在这里输入这里我是以文本格式输入，故无法计算第15页/共33页第十五页，共34页。2、什么(shn me)叫文本函数？Left函数主要功能：从一个文本字符串的第一个字符开始，截取指定数目(shm)

15、的字符。（即从左开始）使用格式：Lift(text,num_chars) 参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目(shm)。Right函数 主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。（即从右开始） 使用格式：Right(text,num_chars) 参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。 应用举例：使用方法同Left函数，但是只是提取的右边的字符。 应用具体：例如我要对比下列几家服务站报单量最大的服务站进行(jnxng)提取，但是前面带有服务站代码，无法进行(jnxng)直接对比排序，这里就

16、需要截取处理单后三位的值，以便进行(jnxng)比对。如右下图Mid函数 主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。 （即从中间任意位置开始）使用格式：Mid(text,start_num,num_chars) 参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示 文本函数是指通过文本函数，可以在公式中处理文字串。第16页/共33页第十六页，共34页。3、逻辑(lu j)函数If函数（如果）主要功能：根据(gnj)对指定条件的逻辑判断的真假结果，返回相对应的内容。 使用格式：=IF(Logical,Value_if_true,V

17、alue_if_false) 参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。 举例说明：如右下图，当成绩大于60分的为“及格”，大于80分为“良 ”，大于90分为“优”=if(E12060,( ),“差”) 第一级判断(pndun) if(E12080,( ),“及格”) 第二级判断(pndun) if(E12090,“优”，“良”） 第三级判断(pndun)公式拆分讲解 注：此处使用

18、了函数的嵌套功能，即复合函数，复合函数属于函数使用的高级用法，因为实际工作数据的复杂性，需要我们使用多个函数来共同完成某项数据分析。 这里我想说明下”if”函数搭配其它函数共同使用，是excel函数应用最为重要的一个函数。此处要显示的是文本，所以需要加双引号第17页/共33页第十七页，共34页。4、查找(ch zho)函数Vlookup函数 主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。使用格式：) Vlookup(lookup_value,table_array,col_index_num,range_lookup） 参数说明：Lookup_value 代表

19、(dibio)需要查找的数值； Table_array 代表(dibio)需要在其中查找数据的单元格区域； Col_index_num 为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值）； Range_lookup 为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。 应用举例：vLookup函数是非常重要的，可以说无时无刻不再用他。 比如通

20、过单位（左）来索引表二的二月数据，在二月费用单元格中输入“=VLOOKUP函数”，表示在单位（右）列中查找需要的二月费用，返回二月列中匹配值，下拉单元格直接填充单元格即可。 如下所示：第18页/共33页第十八页，共34页。第三(dsn)讲Excel中插入图表 我们生活的这个世界是丰富多彩的，几乎所有的知识都来自于视觉。也许无法记住一连串的数字，以及它们之间的关系和趋势。但是可以很轻松地记住一幅图画或者(huzh)一个曲线。因此由于使用图表，会使得用Excel编制的工作表更易于理解和交流。 图表可以用来表现数据间的某种相对关系，在常规状态下我们一般运用柱形图比较数据间的多少关系;用折线图反映数据

21、间的趋势关系;用饼图表现数据间的比例分配关系。运用Excel的图表制作可以生成多种类型的图表。 下面以柱形图、折线图、饼图三种类型为例，分别介绍其制作方法，最后我会制造一个(y )比较复杂的图表来作为图表讲座的结束。第19页/共33页第十九页，共34页。1、柱形图 柱形图比较数据间的多少(dusho)关系下面我们以企业的收入与支出对比表来演示柱形图的建立。 统计(tngj)得到的数据表可以将其建立为柱形图表，从而更加直观的显示出收入与支出的对比情况，以及全年中收入与支出的发展趋势对比。右图所示图表需要四个步骤：1）数据区选择生成默认柱状表格2）图表中添加标题栏3）收入(shur)与支出柱状图上

22、下对齐4）月份显示到轴坐标下方 下面我会以演示的方式为大家呈现此图表的制作。 从图中可以看出全年中期为盈 利淡季，年初与年末盈利较大，其中5、6月出现反弹 收入与支出基本成比例第20页/共33页第二十页，共34页。生成完整柱型图表流程图首先(shuxin)要选择数据区第21页/共33页第二十一页，共34页。2、折线图 用折线图反映数据间的趋势关系 这里我们同样以企业收入(shur)与支出作为对比关系 制作流程与柱状图相似，只是在第二步选择折线图图表，程序会自动(zdng)为我们生成好折线图图表。 这里我们要注意的是：这里我们要注意的是： 默认的折线图，默认的折线图，“月份月份”会会在在x x坐

23、标中轴线附近坐标中轴线附近(fjn)(fjn)，这里就需要，这里就需要使用柱形图流程中使用柱形图流程中4-54-5步奏步奏设置月份到下方去设置月份到下方去第22页/共33页第二十二页，共34页。3、饼状图 用饼图表现(bioxin)数据间的比例分配关系 饼图有多种样式：突出型饼图、复合型饼图、双子饼图、母子饼图、动态母子饼图 这里我为大家制作一个相对比较复杂的饼图来认识学习饼图的制作：复合饼图 复合饼图制作中运用的办法，基本可以代表基础(jch)功能中各种饼图，所以熟练掌握复合饼图，我们在饼图制作中就可以游刃有余。第23页/共33页第二十三页，共34页。复合(fh)饼状图制作流程原始数据 注：

24、在这里生成的饼形图界面是非常简单的，需要我们自己按照自己的喜好去美化它，07版Excel提供了多种美化方式(fngsh)，我们可以自己去学习 左图是已经美化好的效果图第24页/共33页第二十四页，共34页。贷款还息动态(dngti)图表 最后在这里我制作一个那些已经买房或准备买二房的兄弟们准备的一个小工具，就是在贷款费用(fi yong)与年限各不同的情况下需要还的利息与本金的对比分析，希望各位为了国家经济发展赶紧买房吧。 这里我制作这个图表的目的是准备将前面几讲中的函数、动态图表，还有目前不属于基础功能的财务函数、窗口开发工具控件的使用融汇到一起(yq)，让大家可以对excel的高级功能有一

25、个初步的认识，为我们下面的高级功能算是做一个开场吧。 再将之前我先介绍下目前我们还没有接触的两个功能。 财务函数PMTPMT顾名思义就是基于固定利率及等额分期付款方式，返回贷款的每期付款额。 开发控件“滚动条”与“组合框”至于它俩的作用我会在下面让大家看到。第25页/共33页第二十五页，共34页。 在制作贷款还息动态(dngti)图标前，我们需要知道三个数据，即贷款金额，贷款年限，贷款年利息，这里先给出了三个数据，在后面制作图表的时候我们就会让这三个数据成为一组可以自动调整的值，以供我们对比不同金额，不同年限下的费用单击观看(gunkn)完成效果演示单击观看(gunkn)制作过程演示第26页/

26、共33页第二十六页，共34页。C5=PMT (C4，$C$3，MAX(0，$C$2*10000)C6=SUM(C9:C33)B9=C2*10000C9=$C$4*B9D9=MIN(B9+C9,$C$5-C9B10=B9-D9B11=IF(B10-D100,0,B10-D10第一步：数据(shj)的录入第27页/共33页第二十七页，共34页。第二步：图表(tbio)的绘制 图表的绘制这步我们需要制作两个图表，一个是利息与本金的柱状对比图。另一个是利息总额与本金综合所占百分比饼型图。 这两个图都比较简单，我们只需要选择年份(ninfn)、利息、本金三纵列所有的数据，点击插入柱状图，便会生成我们需要

27、的单一年限与贷款金额对比图。 至于饼状图需要选择两个值的总和插入饼图就可以了。 注：需要注意的是两个图在生成后是重叠的，需要我们对其进行美化，这里不做讲解。第28页/共33页第二十八页，共34页。第三步：制作不同年限、金额( jn)动态柱状图关键所在关键所在第29页/共33页第二十九页，共34页。第三步：制作不同年限(ninxin)、金额动态柱状图 这里(zhl)我们设置此控件最小值、最大值和步进值，以及此控件调节的单元格对应地址。 这是贷款金额(jn )设置栏第30页/共33页第三十页，共34页。第四讲Excel常用(chnyn)技巧1、Ctrl+箭头键 移动到当前数据区域的边缘。2、Ctr

28、l+Home 移动到工作表的开头。3、Ctrl+End 移动到工作表的最后一个单元格，该单元格位于数据所占用的最 右列的最下行(xixng)中。4、Alt+Enter 在单元格中强制换行。5、Crl+Z 撤消上一次操作。6、Ctrl+C 复制选定的单元格。7、Ctrl+X 剪切选定的单元格。 8、Ctrl+V 粘贴复制的单元格。9、Alt+E+D 强制换行10、Ctrl+A 选定整张工作表。 11、Ctrl+1 显示单元格格式对话框，对单元格格式进行设置12、Alt+D+F+F 自动筛选13、Alt+W+F 冻结窗口14、Alt+Tab 各个程序窗口熟练掌握键盘与鼠标操作有助于我们更加快速的制

29、作表格，下面(xi mian)我将一些常用的键盘快捷方式列出来，供大家参考第31页/共33页第三十一页，共34页。 到此Excel基础功能讲解就算完成了，以上只是对EXCEL中最常用的一些功能进行简单(jindn)的介绍，希望能对大家起个引导作用。希望大家在使用EXCEL时多使用快捷键，灵活的使用函数，这样可以极大的提高我们的工作效率。 再次感谢大家，对以上讲解有不对的地方请大家多提意见。小结(xioji)第32页/共33页第三十二页，共34页。感谢您的欣赏(xnshng)！第33页/共33页第三十三页，共34页。内容(nirng)总结Excel简介。处理，这里就需要就数据转化为数字格式。Excel提供了二种不同的筛选方式:自动筛选和高级(goj)筛选。使用格式：SUMIF（Range,Criteria,Sum_Range）。财务函数PMTPMT顾名思义就是基于固定利率及等额分期付款方式，返回贷款的每期付款额。C9=$C$4*B9。D9=MIN(B9+C9,$C$5-C9。这里我们设置此控件最小值、最大值和步进值，以及此控件调节的单元格对应地址第三十四页，共34页。