excel使用技巧讲座

办公office报表软件excel使用技巧讲座制作时间：2013年3月7日

制作人：刘凯Excel简介Excel在工作中起的作用，是不言而喻的。灵活的使用Excel可以极大的提高我们的工作效率，以下是我工作之余总结的Excel常用功能，希望对大家工作有所益处。

下面讲解我会以问题、概念、图例与实操演示等四个方式为大家讲解每项功能的使用方法，以便大家能快速掌握。

但是在讲解之前我先为大家介绍下Excel它到底具有哪些功能？我们对它的功能掌握到了哪一步？

Excel用户水平的5个层次初步的接触基本掌握常用功能熟练使用常用功能+数据透视表+函数宏+数组公式+VBA简单编程Excel技能+专业知识+行业经验新手

初级用户

中级用户

高级用户

专家数据操作录入与导出常规编辑格式化高级编辑自定义环境图表与图形标准图形组合图形图表美化高级图表交互式图表图示公式与函数公式基础函数应用函数嵌套数组公式自定义函数数据分析排序筛选列表数据透视表假设分析高级分析宏与VBA录制运行宏VBA基础Excel对象窗体与控件类模块Excel功能剖析Excel功能讲解Excel常用操作Excel基本函数Excel中插入图表Excel常用技巧函数高级应用创建数据透视表数据处理与分析复杂实例演示基础功能进阶功能第一讲

Excel常用操作1、excel常用功能界面介绍，07版与03版的区别与相似在哪里？07版界面03版界面5、设置单元格格式

鼠标左键选择所要改变的区域，右键菜单设置单元格格式。

这里可以对数字、对齐、字体、边框、图案等项目进行设置，助我们制作一个精美的报表。如演示所示3、如何设置具体数值的行高与列宽？

鼠标左键选择所要改变的行（或列），右键菜单

行高（列宽）。如图例所示2、Shift+Ctrl+方向键有什么功能呢？

它们实现的功能是：从当前单元格开始，按照选定的方向选取数据，遇到第一个空单元格时结束。如演示所示：4、快速的自动填充序列？

所谓自动填充简单的理解就是当行或列中的部分数据形成有一定的序列时，我们就可以使用自动填充功能来快速填充数据。

自动填充功能可以实现多种数据的填充，如：序列、单元格格式、年月份、数据的重复录入等。自动扩展与填充是excel中一项比较重要的功能，这里我会以演示的方式为大家讲解。如演示所示6、查找与替换功能如何快速调出？

查找与替换功能是一个常用的功能，这里我只介绍大家其键盘快捷键:ctrl+F注：此快捷方式在使用前需选定需要查找的数据区。此功能比较简单。

9、灵活使用状态栏

在Excel的左下角，有一个状态栏，提供了许多有用的功能与函数对选定的范围进行处理，如：记数，求和，求平均数，求最大/最小值等。在状态栏上，单击鼠标右键就可以调出这个功能，灵活运用这个功能可以节省我们很多工作时间。如下所示：7、如何进行批量数据清除？

Delete键.这是数据清除的快捷键。当数据量相当大的时候，对于误填，错填的数据，每次一个个的清除很是浪费时间，也不利于工作效率的提升，这里我们就是使用delete键，一键清除数据。这里的清除只包括清除单纯数据，不包括清除单元格的格式等。

注：我们可以搭配使用ctrl键，这样可以单独选择多个不连续单元格数据或图片清除。8、单元格数据的文本与数字格式的如何互换？

很多时候当我们从系统中导出的数据，它一般都会默认为文本格式，这样很不方便我们对数据的处理，这里就需要就数据转化为数字格式。如演示所示

10、筛选功能

Excel提供了二种不同的筛选方式:自动筛选和高级筛选

自动筛选功能：快捷键：Alt+D+F+F，只能用于条件简单的筛选操作,不能实现字段之间“与”“或”关系的操作，我这里不做介绍，主要说说他的高级筛选的使用办法。高级筛选功能：快捷键：Alt+D+F+A。这里我们要求筛选出所有员工中基本工资大于2，并且绩效工资小于7，以及奖励工资大于等于10的员工，并且隐藏条件不符的员工。

这里就包含有“与”“或”的关系，使用自动筛选是很难办到的，所以在这里使用高级筛选就会非常简单。这里是数据区这里是条件区11、选择性粘贴我们经常会使用一些函数，但是用完函数后，单元格中的数值会包含公式，此时我们会运用选择性粘贴去除函数。选择性粘贴是excel中一项比较重要的功能，使用它我们可以对数据进行多种格式的复制。

以下主要演示粘贴中得运算、行列倒置、粘贴的多种选择等。如图所示12、合并单元格数据许多情况下我们需要不单单只合并两个单元格，还要合并单元格中的数据，比如合并NCC和BCC，这样我们会用到“&”（和号）运算符。

如下所示这里我们使用了公式函数，函数一讲我会为大家详细讲解函数的使用办法13、窗口的冻结与拆分有时候我们在看数据时，希望将某一行数据的冻结不动，此时我们会使用冻结窗口功能，快捷键：Alt+W+F。

拆分功能是将数据区拆分成几块，方便我们对标题或其他数据的对比录入.如图所示14、强制换行

有些时候我们需要在一个单元格中输入较长的文字，此时我们就用到了强制换行功能。选择Alt+Enter即可进行强制换行，没必要再去用空格将文本挤到下一行了15、数据有效性

有时我们可能需要限制输入到单元格中得数据格式，这就需要数据有效性功能。

16、数据透视表数据透视表是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关。例如，可以水平或者垂直显示字段值，然后计算每一行或列的合计；也可以将字段值作为行号或列标，在每个行列交汇处计算出各自的数量，然后计算小计和总计。

若要创建数据透视表，请运行“数据透视表和数据透视图向导”。在向导中，从工作表列表或外部数据库选择源数据。向导然后为您提供报表的工作表区域和可用字段的列表。当您将字段从列表窗口拖到分级显示区域时，MicrosoftExcel自动汇总并计算报表。数据透视表是excel中一个我们经常会使用的功能，他的功能是非常强大的，我在这里为大家简单演示下其数据分析方面的使用办法。

如有以下数据，现在我们需要统计分析得到这样几个数据？1、员工姓名相同的重复有几个人？他们的保险编号是多少？2、姓名相同的人各自里面谁的工资最高，谁的最低？他们的保险编号是多少？3、姓名相同的人合计工资是多少？电话费是多少？所有总计各项费用多少？第二讲

Excel常用函数Excel是办公室自动化中非常重要的一款软件，很多企业都是依靠Excel进行数据管理。它不仅仅能够方便的处理表格和进行图形分析.其更强大的功能体现在对数据的自动处理和计算，然而很多对Excel强大数据处理功能不了解的人却难以进一步深入。而对Excel函数应用的不了解正是阻挡普通用户完全掌握Excel的拦路虎，希望我的讲解能够对大家对Excel函数学习有所帮助。Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。共计约500多个函数。

这里我会介绍8个我们工作中常用函数功能及用法，更为复杂的函数操作我在这里不做讲解。Sum函数（求和函数）主要功能：计算所有参数数值的和。使用格式：SUM（Number1,Number2……）参数说明：Number1、Number2……代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。应用举例：如右图所示，在A10单元格中输入公式：=SUM(A1:A9)，确认后即可求出以上单元格的和。Sumif函数主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：SUMIF（Range,Criteria,Sum_Range）参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。应用举例：如右图所示，分别求出各项费用的合计1、数学函数条件区Max函数（Min函数）主要功能：求出一组数中的最大值（或最小值）。使用格式：MAX(number1,number2……)参数说明：number1,number2……代表需要求最大值的数值或引用单元格（区域），参数不超过30个。简单的加减乘除运算的公式使用

这里使用的符号为:+-*/直接的公式是在这里输入这里我是以文本格式输入，故无法计算2、什么叫文本函数？Left函数

主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。（即从左开始）使用格式：Lift(text,num_chars)参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。Right函数

主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。（即从右开始）使用格式：Right(text,num_chars)参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。应用举例：使用方法同Left函数，但是只是提取的右边的字符。

应用具体：例如我要对比下列几家服务站报单量最大的服务站进行提取，但是前面带有服务站代码，无法进行直接对比排序，这里就需要截取处理单后三位的值，以便进行比对。如右下图Mid函数

主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。（即从中间任意位置开始）使用格式：Mid(text,start_num,num_chars)参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示

文本函数是指通过文本函数，可以在公式中处理文字串。3、逻辑函数If函数（如果）

主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。使用格式：=IF(Logical,Value_if_true,Value_if_false)参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。

举例说明：如右下图，当成绩大于60分的为“及格”，大于80分为“良”，大于90分为“优”=if(E120>60,(),“差”)第一级判断if(E120>80,(),“及格”)第二级判断if(E120>90,“优”，“良”）第三级判断公式拆分讲解注：此处使用了函数的嵌套功能，即复合函数，复合函数属于函数使用的高级用法，因为实际工作数据的复杂性，需要我们使用多个函数来共同完成某项数据分析。

这里我想说明下”if”函数搭配其它函数共同使用，是excel函数应用最为重要的一个函数。此处要显示的是文本，所以需要加双引号4、查找函数

Vlookup函数主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。使用格式：)Vlookup(lookup_value,table_array,col_index_num,range_lookup）参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值……）；Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。

应用举例：vLookup函数是非常重要的，可以说无时无刻不再用他。

比如通过单位（左）来索引表二的二月数据，在二月费用单元格中输入“=VLOOKUP函数”，表示在单位（右）列中查找需要的二月费用，返回二月列中匹配值，下拉单元格直接填充单元格即可。

如下所示：第三讲

Excel中插入图表我们生活的这个世界是丰富多彩的，几乎所有的知识都来自于视觉。也许无法记住一连串的数字，以及它们之间的关系和趋势。但是可以很轻松地记住一幅图画或者一个曲线。因此由于使用图表，会使得用Excel编制的工作表更易于理解和交流。图表可以用来表现数据间的某种相对关系，在常规状态下我们一般运用柱形图比较数据间的多少关系;用折线图反映数据间的趋势关系;用饼图表现数据间的比例分配关系。运用Excel的图表制作可以生成多种类型的图表。

下面以柱形图、折线图、饼图三种类型为例，分别介绍其制作方法，最后我会制造一个比较复杂的图表来作为图表讲座的结束。1、柱形图

柱形图比较数据间的多少关系下面我们以企业的收入与支出对比表来演示柱形图的建立。统计得到的数据表可以将其建立为柱形图表，从而更加直观的显示出收入与支出的对比情况，以及全年中收入与支出的发展趋势对比。右图所示图表需要四个步骤：1）数据区选择生成默认柱状表格2）图表中添加标题栏3）收入与支出柱状图上下对齐4）月份显示到轴坐标下方

下面我会以演示的方式为大家呈现此图表的制作。从图中可以看出全年中期为盈利淡季，年初与年末盈利较大，其中5、6月出现反弹收入与支出基本成比例生成完整柱型图表流程图首先要选择数据区<1><2><3><4><5>2、折线图用折线图反映数据间的趋势关系这里我们同样以企业收入与支出作为对比关系

制作流程与柱状图相似，只是在第二步选择折线图图表，程序会自动为我们生成好折线图图表。

这里我们要注意的是：默认的折线图，“月份”会在x坐标中轴线附近，这里就需要使用柱形图流程中4-5步奏设置月份到下方去3、饼状图用饼图表现数据间的比例分配关系

饼图有多种样式：突出型饼图、复合型饼图、双子饼图、母子饼图、动态母子饼图

这里我为大家制作一个相对比较复杂的饼图来认识学习饼图的制作：复合饼图

复合饼图制作中运用的办法，基本可以代表基础功能中各种饼图，所以熟练掌握复合饼图，我们在饼图制作中就可以游刃有余。复合饼状图制作流程原始数据注：在这里生成的饼形图界面是非常简单的，需要我们自己按照自己的喜好去美化它，07版Excel提供了多种美化方式，我们可以自己去学习

左图是已经美化好的效果图贷款还息动态图表最后在这里我制作一个那些已经买房或准备买二房的兄弟们准备的一个小工具，就是在贷款费用与年限各不同的情况下需要还的利息与本金的对比分析，希望各位为了国家经济发展赶紧买房吧。这里我制作这个图表的目的是准备将前面几讲中的函数、动态图表，还有目前不属于基础功能的财务函数、窗口开发工具—控件的使用融汇到一起，让大家可以对excel的高级功能有一个初步的认识，为我们下面的高级功能算是做一个开场吧。再将之前我先介绍下目前我们还没有接触的两个功能。

财务函数PMT——PMT顾名思义就是基于固定利率及等额分期付款方式，返回贷款的每期付款额。

开发控件“滚动条”与“组合框”至于它俩的作用我会在下面让大家看到。在制作贷款还息动态图标前，我们需要知道三个数据，即贷款金额，贷款年限，贷款年利息，这里先给出了三个数据，在后面制作图表的时候我们就会让这三个数据成为一组可以自动调整的值，以供我们对比不同金额，不同年限下的费用单击观看完成效果演示单击观看制作过程演示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-D10<0,0,B10-D10第一步：数据的录入第二步：图表的绘制图表的绘制这步我们需要制作两个图表，一个是利息与本金的柱状对比图。另一个是利息总额与本金综合所占百分比饼型图。这两个图都比较简单，我们只需要选择年份、利息、本金三纵列所有的数据，点击插入柱状图，便会生成我们需要的单一年限与