EXCEL实操应用培训

Excel实操应用培训演讲人:目录

IIIIIIIV基本操作——原来可以如此简单表格之美——表格也是门艺术函数图表——Excelde精髓之处实战演练——玩转excel不是梦基本操作常用快捷键排序与筛选分列数据有效性条件格式删除重复项不定行数据填充基本操作——原来可以如此简单

基本操作表格之美函数图表实战演练基本操作——常用快捷键Ctrl+C——复制选定区域Ctrl+v——粘贴选定区域Ctrl+x——裁剪选定区域选定区域+Ctrl+d——纵向填充第一列单元格内容选定区域+Ctrl+r——横向填充第一行单元格内容Windows键+d——显示桌面Ctrl+b——加粗单元格字体Ctrl+f/h——查找与替换Tab+Alt——当前程序与上个程序间快速切换Tab+Ctrl——同类型程序间快速切换Alt+Enter——单元格内换行Ctrl+End——移到文本框de末尾Ctrl+Home——移到文本框de开头Shift+Ctrl+End——选定自光标所在单元格以下所有非空单元格Shift+Ctrl+Home——选定自光标所在单元格以有所以非空单元格Ctrl+A——选定所有非空单元格选定对象+Shift+箭头状鼠标拖动——任意行间拖动选定对象+Ctrl+箭头状鼠标拖动——任意行间复制粘贴基本操作——排序与筛选

几类典型排序方法排序法——自定义序列法典型案例:按职务高低进行排序排序法——对排序选项进行设定典型案例:按单元格填充颜色排序（若存在多种颜色可通过“复制条件”选项来进行次关键字排序）排序注意事项:对带公式de单元格进行排序注意事项筛选de基本操作筛选de类型——按文本特征筛选、按数字特征筛选、按日期特征筛选、按字体颜色或单元格颜色筛选筛选de基本操作——选定区域、筛选、搜索关键字自定义自动筛选方式筛选de注意事项:双行标题de工作表筛选若只对一列单元格数据进行筛选,则需选中标题所在单元格及其下方至少一个单元格,否则默认为整个工作表.操作与技巧进阶——数据有效性数据有效性de作用

①

限制单元格de输入或圈识无效数据②做单元格下拉表利用数据有效性对单元格输入限制数据有效性de来源可文本也可公式,若是文本,需英文状态下de逗号作为分隔符.路径:菜单栏【数据】--【数据有效性】操作与技巧进阶——避免重复值录入

日常工作中,身份证号码、工作证编码等ID编号是唯一de,不允许重复,若在excel中重复录入ID,就会给信息管理带来不便,我们可以通过数据有效性来解决这个问题:切换到“设置”选项卡,打开“允许”下拉菜单,选择【自定义】在公式处输入=countif(a:a,a一)=一（英文状态下输入）切换到“出错警告”选项卡,选择出错警告信息de样式,填写标题和错误信息,然后点击【确定】COUNTIF函数可以统计单元格区域中满足给定条件de单元格de个数.该函数de语法结构为:COUNTIF(rage,criteria),其参数range表示需要统计其中满足条件de单元格数目de单元格区域；criteria表示指定de统计条件,其形式可以为数字、表达式、单元格引用或文本.在运用COUNTIF函数时要注意,当参数criteria为表达式或文本时,必须用引号引起来,否则将提示出错.操作与技巧进阶——条件格式利用条件格式主要功用:按规则突出显示:数值、文本、如期等规则按规则建立标示显示快速标示重复项路径:菜单栏【开始】--【条件格式】注意使用“项目选取规则、数据条、色阶、图标集”等功能时,所选择de范围仅能包含数值数据,不能存在文本de单元格.表格之美数据透视表面对一篓筐de数据,你还在一个个按计算器求和吗?或者还在用SUM函数吗?如果是,你奥特曼了!数据透视表

功能介绍:

数据透视表能够将筛选、排序和分类汇总等操作依次完成,并生成汇总表格.Example:资金日报表

一、透视表de建立（单击excel二零一零版插入按钮）二、选项及设计功能介绍三、存在合并单元格透视表会提示字段名无效数据透视表解决方法:取消合并单元格,填充相同内容,然后创建数据透视表.附加学习点:相隔不规则行数填充内容.数据透视表步骤一如图所示,选中合并单元格区域A二:A二一→单击“开始”选项卡→“对齐方式”组→“合并后居中”,取消合并单元格.步骤二

如图所示,选中取消合并后de单元格区域A二:A二一→按F五,在弹出de“定位条件”对话框中选择“空值”→“确定”,即可定位并选中单元格区域A二:A一八中所有空值de单元格步骤三

如图所示,在选中de空白单元格中输入“=A二”,然后按【Ctrl+Enter】组合键,即可实现空白单元格填充相同项.步骤四

按之前讲诉方法创建数据透视表.数据透视表—如何两个求和项再求和?在弹出计算字段选项框中,把名称改为“合计”,在公式框中,从下面字段框中双击选择“金额”,输入“+”,在双击选中“滞纳金”,点击确定即可.函数图表VLOOKUP函数快速查找指定数值从懂得VLOOKUP开始功能介绍:

在表格数值或数组de首列查找指定de数值,并由此返回表格或者数组中该数值所在行中指定列处de数值.VLOOKUP函数注意事项:一、如果“查找值”小于“区域”第一列中de最小值,函数VLOOKUP返回错误值#N/A.二、如果函数VLOOKUP找不到“查找值”,且“逻辑值”为FALSE,函数VLOOKUP返回错误值#N/A.三、VLOOKUPde查找值如果在索引区域内有多个对应值时,只能返回首条记录de对应索引数据.四、VLOOKUPde查找值相对应de索引区域de对应列,必须位于索引区域de首列.SUMIF函数案例:请根据《房产资料》工作表,用Sumif函数统计出“项目名称”为“高尔夫三期”de所有房间建筑面积之和.SUMIF函数是Excel常用函数.使用SUMIF函数可以对报表范围中符合指定条件de值求和.Excel中sumif函数de用法是根据指定条件对若干单元格、区域或引用求和.第一个参数:Range为条件区域,用于条件判断de单元格区域.第二个参数:Criteria是求和条件,由数字、逻辑表达式等组成de判定条件.第三个参数:Sum_range为实际求和区域,需要求和de单元格、区域或引用.答案:'=SUMIF(房产资料!A:A,"高尔夫三期",房产资料!G:G)COUNTIF函数案例:请根据《房产资料》工作表,用Countif函数统计出“项目名称”为“高尔夫二期”de房间个数(房产户数).Countif函数是MicrosoftExcel中对指定区域中符合指定条件de单元格计数de一个函数参数:range要计算其中非空单元格数目de区域参数:criteria以数字、表达式或文本形式定义de条件答案:'=COUNTIF(房产资料!A:A,"高尔夫二期")IF函数案例:请用If函数判别,若上题“项目名称”为“高尔夫二期”de房间个数超出三七零个,则提示“统计正确”,否则提示“统计错误”.IF函数一般是指Excel中deIF函数,根据指定de条件来判断其“真”（TRUE）、“假”（FALSE）,根据逻辑计算de真假值,从而返回相应de内容.IF(logical_test,value_if_true,value_if_false)Logical_test表示计算结果为TRUE或FALSEde任意值或表达式.Value_if_truelogical_test为TRUE时返回de值.Value_if_falselogical_test为FALSE时返回de值答案:'=IF(C九>三七零,"统计正确","统计错误")图表——同时显示柱状图和折线图在excel表中输入需要插入图表de区域范围选中该工作表点击插入——二维柱形图（不能选择三维图）,会弹出以下图表:注意:如选择de数据范围需重新选择,可右击鼠标,【选择数据】,重新选择数据范围或者把多余de系列删除,点击保存即可.可通过图表工具,对图表进行美化,如改变标题、柱状图de颜色、图例de摆放位置、数据标签de显示位置等.图表——同时显示柱状图和折线图上述操作已经选择数据并做好了柱状图,要三大管家占比制作成折线图,操作如下:将excel表中柱形图适当放大,可看见显示为占比de柱子（蓝色）,右键选择增长率de柱子——选择“设置数据系列格式”——“系列绘制在”选择“次坐标轴”,图标如下所示:图表——同时显示柱状图和折线图点击年增长率柱子选择“更改图标类型”——更改为折线图——确定.图表如下所示:图表——同时显示柱状图和折线图在图标中,选中柱形或者折线项目——右键点击“添加数据标签”或者选择菜单栏【图表工具】--【布局】--数据标签,即可在图表中显示该项具体数据,如下图所示:实战演练感谢观看Excel实用培训教程培训:一提高篇目录一、Excel熟练操作de基础二、Excel工作组de妙用三、Excel表格格式de美观四、Excel样表/辅助表de制作五、Excel常用复杂函数详解六、Excel中对负增长de讨论与解决七、Excel图表分析八、Excelde加密与表格de锁定九、Excel简单de宏操作十、Excel表格打印一、Excel熟练操作de基础一、Excel熟练操作de基础复制:Ctrl+C粘贴:Ctrl+V剪贴板de使用:在使用电子表格进行工作时,用剪贴板可以把工作效率提高几倍；熟练使用剪贴板,还可以实现许多功能.打开剪贴板:编辑

Office剪贴板；或者在表格中双击复制健（即双击“Ctrl+C”）复制进入剪贴板de数据按先后顺序是从下到上,避免搞错.（具体看实例）一、Excel熟练操作de基础剪贴板只要是实现数据和字符串de复制粘贴,会把公式过滤掉.同时它有一个很大很实用de功能是否显示不粘贴,非常方便选择性数据de复制粘贴.具体看实例一、Excel熟练操作de基础拖动＆复制:一、数字,日期,星期de复制二、如果不希望复制de数字,日期或星期自动累加,实现复制de数字,日期或星期一样,如何实现?三、公式de拖动,公式de复制,公式（复制）拖动de锁定,$行列号锁定符de使用.一、Excel熟练操作de基础选择性粘贴de功用:可以实现数值,公式,格式,转置,各种运算等功能.（具体请看操作示范）二、Excel工作组de妙用二、Excel工作组de妙用大多数大型de工作表格,都是由很多小表组成de,这时为了提高工作效率,节约时间,使用工作组来工作是非常必要de.一、工作组de选择,工作表de新建,重命名,移动,复制,删除等操作de熟悉.二、工作组复制粘贴de熟悉.三、工作组之间de超链接熟悉.三、Excel表格格式de美观三、Excel表格格式de美观设置单元格格式de使用:三、Excel表格格式de美观一、单元格格式中de数字格式.二、单元格格式中de对齐格式.三、单元格格式中de字体格式.四、单元格格式中de边框格式.五、单元格格式中de背景格式.六、单元格格式中de保护功能.三、Excel表格格式de美观格式刷de使用:一、双击格式刷.二、工作组格式刷de功用.三、区域格式刷新.四、Excel样表/辅助表de制作四、Excel样表/辅助表de制作根据工作需要de不同,我们在工作中需要制作各种Excel表格样表,一可以给自己工作带来方便,二是给别人做参考学习,三是便于规范各个分公司或部门de工作.样表主要是从实际需要出发,多考虑实用性,美观性.如果是给别人参考de,多从对方de角度出发,方便对方快速接受.如果是统一规范工作de,应从严谨规范,大体上考虑.四、Excel样表/辅助表de制作在工作中,有时候数据量较大,整理起来比较麻烦,因而常常需要使用辅助表格.辅助表格有两种,一种是独立de辅助表,此表只是在完成一项工作de一个中间过程,就像一个工具一样,只要通过此表得到我们需要de结果即可.另外一种辅助表是和工作表在一起de,一般连接在工作表后面,是一种数据de来源,也叫数据源表.此类表在我们工作中是很常用de.具体请看实例!五、Excel常用复杂函数详解五、Excel常用复杂函数详解一、SUM（）函数de使用:SUM（）返回某一单元格区域中所有数字之和.语法SUM(number一,number二,...)Number一,number二,...

为一到三零个需要求和de参数.注意:其最大限度只能累加三零个数据段de求和,超出三零就产生溢出.此函数de符号∑在工具栏里可以直接找到,是电子表格中最常用de函数之一!具体看示范操作.五、Excel常用复杂函数详解AVERAGE()函数de使用:返回参数de平均值（算术平均值）.语法AVERAGE(number一,number二,...)Number一,number二,...

为需要计算平均值de一到三零个参数.注意:其最大限度只能累加三零个数据段de求和,超出三零就产生溢出.说明参数可以是数字,或者是包含数字de名称、数组或引用.如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略；但包含零值de单元格将计算在内.此函数在工具栏∑里也可以直接找到,是电子表格中最常用de函数之一!具体看示范操作.五、Excel常用复杂函数详解INT()函数de使用:将数字向下舍入到最接近de整数.语法INT(number)Number

需要进行向下舍入取整de实数.例如int（六.九九）所得结果是六,int（六.一二）所得结果也是六.具体看示范操作.五、Excel常用复杂函数详解COUNTIF()函数de详解:计算区域中满足给定条件de单元格de个数.语法COUNTIF(range,criteria)Range

为需要计算其中满足条件de单元格数目de单元格区域.Criteria

为确定哪些单元格将被计算在内de条件,其形式可以为数字、表达式或文本.例如,条件可以表示为六零、“六零”、“>＝六零”或“数学".说明MicrosoftExcel提供其他函数,可用来基于条件分析数据.若要使公式返回两个基于条件de值之一,例如:算出某门课程有多少学生及格,有多少学生九零分以上等等,都可以使用该函数实现.五、Excel常用复杂函数详解SUBTOTAL()函数详解:返回数据清单或数据库中de分类汇总.通常,使用“数据”菜单中de“分类汇总”命令可以容易地创建带有分类汇总de数据清单.一旦创建了分类汇总,就可以通过编辑SUBTOTAL函数对该数据清单进行修改.SUBTOTAL(九,C二:C二四)该函数在表格中使用最多,也是做很多表格最有用de一个函数,它de意思是汇总再求值,如果其中有此函数出现,它比SUM（）函数de好处就是不会重复相加,（如一个店有食品,生鲜,非食品,家电,百货这几个区,我用SUBTOTAL(九,C二:C二四)在各个区累加汇总了,然后我在最后再用该函数汇总整个店de销售总额,不会出现重复计算,看示例!五、Excel常用复杂函数详解SUBTOTAL()函数中de参数有一-九；其中九代表相加,一是求平均值,四是求最大值,五是求最小值,我常常使用de就这几个；其中后面两个数字是所要汇总de数据de区间,如C二:C二四就是要汇总C列中第二格到第二四格de数值.此函数主要使用在分类汇总de表格里.所以在修改有分类汇总de工作表de时候,必须要弄懂该函数de功用,才不会出错.五、Excel常用复杂函数详解SUMIF()函数de详解:根据指定条件对若干单元格求和；此公式是判断求和,非常实用语法SUMIF(range,criteria,sum_range)Range

为用于条件判断de单元格区域.Criteria

为确定哪些单元格将被相加求和de条件,其形式可以为数字、表达式或文本.例如,条件可以表示为六零、“六零”、“>六零”或“数学".Sum_range

是需要求和de实际单元格.五、Excel常用复杂函数详解SUMIF()函数de说明只有在区域中相应de单元格符合条件de情况下,sum_range中de单元格才求和.如果忽略了sum_range,则对区域中de单元格求和.例如:SUMIF(E二:E二四,”毛利”,C二:C二四),E二:E二四是指判断区间,”毛利”是判断条件,C二:C二四是求和区间（意思就是在E二:E二四有“毛利”这个字段对应deC二:C二四de数字相加）判断条件可以是字符串,也可以是数字,但是是字符串必须使用英文de双引号,数据就不需要.具体看示范操作.五、Excel常用复杂函数详解IF()函数de详解:执行真假值判断,根据逻辑计算de真假值,返回不同结果.可以使用函数IF对数值和公式进行条件检测.语法IF(logical_test,value_if_true,value_if_false)IF(条件,真,假)；如果条件判断为真,则执行第一个命令,反之执行第二个命令.该函数重要一个使用功能是嵌套,最多可以嵌套七层.说明函数IF可以嵌套七层,用value_if_false及value_if_true参数可以构造复杂de检测条件.在计算参数value_if_true和value_if_false后,函数IF返回相应语句执行后de返回值.五、Excel常用复杂函数详解IF()嵌套de实用性很广,可以用于作各种判断,例如:已知张三同学某课de成绩,求出他de成绩等级.可以用此函数实现,IF(B二<六零,“不及格”,IF(B二<八零,“及格”,IF(B二<九零,“良好”,IF(B二>一零零,“成绩有误”,“优秀”)))).用IF()函数嵌套其他函数:例如嵌套sum,sumif,AVERAGE,countif等等常用de函数.例如根据条件（求和或求平均值）进行数据运算,并反馈运算结果.可以这样实现:IF(D五="合计",SUM(B二:B一二),IF(D五="平均",AVERAGE(B二:B一二),"条件不符"))具体操作,看其他示例!六、Excel中对负增长de

讨论与解决六、Excel中对负增长de讨论与解决通过以上对IF函数de学习,我们可以通过它来实现我们在日常遇到de一些难题,比如负增长de问题.（负增长问题和一些完成率问题de事例）在我们平时工作中,增长de公式一般是:（当前数据－对比数据）/对比数据*一零零％但是该公式如果用在计算利润de时候,就会出现问题,因为我们常常会遇到负利润de情况（即亏损情况）；这里就不能仅仅把公式往表格里面一套就了事,而是需要通过if函数进行一些判断运算；如附件例子所示.六、Excel中对负增长de讨论与解决对利润de完成率情况也有类似de问题,比如上个月de利润计划是-一零零万（即允许亏损一零零万）,但是实际完成是-一二零万,该完成率是多少?而我们平时使用de完成率公式很简单,即:实际/计划*一零零％；但是把此公式对上面提到de情况进行运算,结果是完成了一二零％,可能吗?如果上面de实际值是零万呢?完成率又是多少?具体看实例,目前我们解决此问题de方法；七、Excel图表分析七、Excel图表分析图表de概念:指将工作中de数据用图形表示出来,图表便于我们阅读和分析数据.建立图表:在菜单栏里:插入－>图表,即弹出如图所示de界面:七、Excel图表分析Excel图表图形有:柱形图,条形图,折线图,饼图,XY散点图,面积图,圆环图,雷达图,曲面图,气泡图,股价图,圆柱图,圆锥图,棱锥图；各种图形都有自己de特点和不同de功用,我们只能根据自己de需要选择可用de图形,现在我们常用de图形有柱行图,折线图,饼图,圆环图,雷达图；另外还有根据一些特别需要自己创建de图形,不过这些不是