1、EXCEL基础功能与运用,总公司客服部 2011年8月,1-30,2,1-30,3,Excel基础应用,编辑工作表,设置工作表的格式,简单分析,1、选择性粘贴2、单元格输入3、相对/绝对引用,1、数据分列2、数据筛选3、数据排序4、数据透视表,1、工作表表头设计和冻结窗格使用2、单元格格式设置3、条件格式,1-30,4,一、Excel基础应用编辑工作表,1、选择性粘贴步骤:选择菜单栏“编辑”“选择性粘贴”进行选择后点“确定”,多种数字格式可供选择,将被复制数据中的数值与原单元格中的数值进行运算,将被复制数据的行列在粘贴时进行对换,1-30,5,一、Excel基础应用编辑工作表,2、单元格输入输
2、入单元格时要注意合理地利用自动填充功能,先输入一个,然后把鼠标放到单元格右下角的方块上,看鼠标变成一个黑色的十字时就按下左键向下拖动,到一定的数目就可以了。,输入序号时,先输入1、2,然后选中1、2所在的单元格,把鼠标放到单元格右下角的方块上,看鼠标变成一个黑色的十字时就按下左键向下拖动,到一定的数目就可以了。,1-30,6,一、Excel基础应用编辑工作表,3、相对引用和绝对引用通常通过复制或剪切来异动或复制公式时,公式中的单元格通常是会变化的,即“相对引用”;如果需要保持单元格固定不变,则需要采用“绝对引用”。通常用“$”来固定绝对引用的位置。举例:A1单元格中的公式,复制到C3,应用不同
3、的的引用形式所得到的对应内容如下表所示;,小窍门:在用“$”来固定绝对引用位置时,可采用快捷键F4。以A1单元格为例,按一下F4是固定行和列($A$1),按两下F4是固定行(A$1),按三下F4是固定列($A1 ),按四下F4是取消固定(A1)。,1-30,7,二、Excel基础应用设置工作表的格式,1、常用斜线表头设计 操作步骤:选中需要制作斜线表头的单元格选择菜单栏“格式”“单元格”“边框”,选择“左斜线”切换到“对齐”类别,将“水平对齐”选择“靠左(缩进)”,“垂直对齐”选择“居中”,点击“确定”在制作斜线表头的单元格中,双击鼠标左键,显示输入焦点使用空格键,将输入焦点定位到单元格靠右的
4、位置,输入“指标”使用Alt+回车,使输入焦点定位到单元格的下一行输入“机构”,单击回车2、冻结窗格的使用在屏幕上查看超过一页的数据表,需要使用“冻结窗格”的功能,以保证在滚动屏幕时,可以一直保持数据表行列标题的位置不变。在操作“冻结窗格”时,需要将鼠标焦点置于需要冻结的行列标题交汇单元格,右下角的单元格选择菜单栏“窗口”“冻结窗格”;如需要取消“冻结窗格”时,选择菜单栏“窗口”“取消冻结窗格”。,1-30,8,二、Excel基础应用设置工作表的格式,3、单元格格式设置用途:使表格更规范,看起来更有条理、更清楚。步骤:首先选中需要设置单元格的数据范围选择菜单栏“格式”“单元格”“数字”,1-3
5、0,9,二、Excel基础应用设置工作表的格式,4、条件格式用途:根据单元格内容不同显示不同的格式。步骤:先选中所有要设置条件格式的单元格选择菜单栏“格式”“条件格式”,1-30,10,三、Excel基础应用简单的数据分析和管理,1、数据分列步骤:首先选中需要分列的那列数据选择菜单栏“数据”“分列”文本分列向导”选择“固定宽度”根据需求调节标尺,注:如需要分列的数据中包含逗号、分号、括号等分隔符,可在文本分列向导中选择“分隔符号”。,1-30,11,三、Excel基础应用简单的数据分析和管理,2、数据筛选自动筛选步骤:将鼠标焦点置于数据表的任一位置,选择“数据”“筛选”“自动筛选”对筛选出来的
6、结果进行统计时,可按照下面例子执行相关操作。,高级筛选在处理清单数据时如需要筛选大量重复数据时,可用Countif函数和自动筛选两种方法结合实现。语法如下:Countif(range,criteria )重复数据筛选举例:第一步Countif(A$1:A2,A2) 第二步自动筛选所需结果。,1-30,12,三、Excel基础应用简单的数据分析和管理,3、数据排序 步骤:选中需要排序的数据范围选择菜单栏“数据”“排序”,1-30,13,三、Excel基础应用简单的数据分析和管理,4、数据透视表 创建数据透视表:选择菜单栏中的“数据”“数据透视表和数据透视图”命名,开启向导第一步:制定数据源位置第
7、二步:指定数据 单击“下一步”按钮,进入步骤2。此步骤用于指定引用的数据范围。如图所示:,1-30,14,三、Excel基础应用简单的数据分析和管理,4、数据透视表第三步:完成数据透视表。 在此步骤中,指定数据透视表的位置,可以选择新建一个工作表,或者在当前工作表中插入。(此例中选择的是新建一个工作表) 完成向导后,数据透视表会出现在新建的工作表中,如图所示:,1-30,15,三、Excel基础应用简单的数据分析和管理,4、数据透视表从“数据透视表字段列表”工具栏中拖放字段到模板上,即可得到汇总记录。举例:从“数据透视表字段列表”中选择需要作为行的字段,本例中是“管理机构代码”,拖放到“将行字
8、段拖至此处”框中选择需要作为列的字段,本例中是“销售渠道”,拖放到“将列字段拖至此处”框中选择需要统计的字段,本例中是“期交保费”拖放至“请将数据项拖放至此处”框中完成后的数据透视表如图。,如果想查询汇总的明细情况,如想看管理机构代码为“8601”的“个险销售”渠道的“807926.72”是哪些明细数据汇总的,可双击B5单元格。,1-30,16,三、Excel基础应用简单的数据分析和管理,4、数据透视表更改数据透视表数据透视表的结构更改,只需要拖不同的字段名称到模板的相应位置上即可。当添加或移动字段时,Excel会更新数据透视表。如果错了,只需要简单的把字段拖出模板,放在工作表中,Excel会
9、从数据透视表的模板中删除它。当原始数据表的数据更改后,数据透视表的汇总不会自动更新,需要点击“数据透视表”工具栏上“刷新数据”按钮,手动刷新数据。“汇总项”的汇总方式,当汇总项字段是数字类型时,默认为“求和”;汇总项字段是文本类型时,默认为“计数”。可手动控制汇总方式。步骤:选择“求和项:销售额”,单击右键,选择“字段设置”出现的“数据透视表字段”对话框,如图所示:在“名称”框中,修改显示的字段名称在“汇总方式”列表中选择指定的汇总运算方式,1-30,17,三、Excel基础应用简单的数据分析和管理,4、数据透视表组合数据透视表中的项目选择想要组合的项目,单击鼠标右键,从出现的快捷菜单中,选择
10、“组及显示明细数据”组合命令。如果字段含有数据,Excel能自动创建组合。步骤:先制作出数据透视表将“应交日期”置于“行字段”框中,将“期交保费”置于“数据项中”,制作出数据透视表,如下方左侧图所示:选择数据透视表中的“应交日期”,单击鼠标右键,选择“组及显示明细数据”“组合”,出现“分组”对话框,如下方中间图所示,选择“月”和”季度“,点击“确定”,完成数据分组,如下方右侧图所示。,1-30,18,Excel函数应用Excel中的函数是指一些预先定义好的公式,用户可以直接使用这些公式对某个区域内的数值进行一系列运算,如Sum 函数可以对单元格或单元格区域进行加法运算等。Excel函数大致可分
11、为:逻辑函数、数学和三角函数、统计函数、查找和引用函数、文本函数、信息函数、财务函数、工程函数、日期和时间函数、数据库函数、兼容性函数、多维数据集函数、自定义函数等几个大类。其中逻辑函数、数学函数、统计函数、查找和引用函数、文本函数中有一些函数是我们日常办公中经常用到的。这些常用函数如下表所列:,1-30,19,一、Excel函数应用逻辑函数,函数说明,函数示例=AND(ture,2+2=5) 等于 False=AND(true,true) 等于 True=OR(true,2+2=5) 等于 True=OR(true,true) 等于 True=OR(False,False) 等于 False
12、,注:AND、OR函数常用于多重条件判断,可与IF、COUNTIF等函数组合使用,以达成多重判断条件,1-30,20,一、Excel函数应用逻辑函数,函数说明,函数示例=IF(“13月继续率”90%,”达标”,“不达标”) 公式表达的内容:如果机构的13月继续率大于90%,则“达标”,否则“不达标”。=IF(and(“13月继续率”90%),(“25月继续率”95%),“有获奖资格”,“没有获奖资格”)公式表达的内容:如果机构的13月继续率大于90%并且25月继续率大于95%,则“有获奖资格”,否则“没有获奖资格”。=IF(OR(“13月继续率”90%),(“25月继续率”95%),“优秀”,
13、“良好”)公式表达的内容:如果机构的13月继续率大于90%或者25月继续率大于95%,则机构达成“优秀”,否则机构达成“良好”。,注:1、IF函数的条件和返回值可以为具体数值、文字或函数;,1-30,21,二、Excel函数应用数学函数,函数说明,函数示例=SUM(A1,B2,C3) 等于 A1+B2+C3=SUM(A1:A3) 等于 A1+A2+A3=SUMIF(条件区域,选择条件,结果累计区域),举例如下:,1-30,22,二、Excel函数应用数学函数,函数说明函数示例 示例:95.4567% 保留1位小数:=Round(95.4567%,3) 结果返回95.5% 保留3位小数:=Rou
14、nd(95.4567%,5)结果返回95.457%,1-30,23,二、Excel函数应用统计函数,函数说明,函数示例,注:函数公式中用到的引号必须是英文格式下的引号,否则公式会提示错误。,1-30,24,二、Excel函数应用统计函数,函数说明,函数示例,1-30,25,二、Excel函数应用统计函数,函数说明,函数示例 举例:=Rank(B2,$B$2:$B$5,0) 0表示从高到低排名 =Rank(B2,$B$2:$B$5,1) 1表示从低到高排名,25,1-30,26,二、Excel函数应用查询函数,函数说明,函数式列:,在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行
15、中指定列处的数值。,查找对象: 为需要在“查找数据范围”的第一列中查找的对象。查找数据范围: 为需要在其中查找数据的数据表。返回值列数:在“数据范围” 中需要查找的结果值在“查找对象”那列开始起算的列数。其中VLOOKUP中V代表垂直;HLOOKUP中H代表行。,1-30,27,二、Excel函数应用文本函数,函数说明 函数示例以“86010009”字符串为例需要截取“8601”公式=Mid(86010009,1,4) 或者是Left(86010009,4)需要截取“09”公式=Mid(86010009,7,2)或者是Right(86010009,2)需要截取“100”公式=Mid(86010009,4,3),1-30,28,二、Excel函数应用信息函数,函数说明如果 expression 参数表示一个错误,则 IsError 返回 True;否则返回 False。expression,可以是任何有效表达式。函数示例:示例:如果计算结果显示为错误值(即”#VALUE! ”),可用公式=If(Iserror(H3/G3),”,H3/G3)把错误值转化成空值,如下所示:,1-30,29,三、EXCEL常用快捷键一览表,1-30,30,结 束,