1、1,在实际工作当中的运用,EXCEl,2,第一部分 Excel 基本概述,3,一、数据输入,在 Excel中可以输入数字、字符、日期、时间和公式等。输入的内容有四种类型：文本类型：字符、数字和键盘符号等。数值类型：数字0-9、日期和时间等。逻辑类型:只有两个值，True和False。出错值：单元格中显示#DIV/0！出错值。,4,1、数值型数据：当输入数字时，数字会自动右对齐。2、字符型数据：当输入汉字、字母、特殊符号、空格等时，字符会自动左对齐。注意：若输入的内容是电话号码、邮政编码、身份证等时，必须在输入的最前面先输入单引号( )。,5,3、日期、时间型数据输入当按日期或时间的格式输入时，
2、输入的数据会自动转换为相应的格式，并自动右对齐。若需要输入当前的日期，只要按 Ctrl+；,6,若想更改当前输入的日期或时间的显示格式时，操作的方法是：选中单元格用鼠标击菜单栏上的“格式”菜单，执行“单元格”选项，打开一个“单元格格式”对话框后进行操作。,7,设置日期格式对话框,单击,单击,选择格式,8,二、数据的自动输入,Excel 具有自动输入一些有规律的数据，可以用智能填充功能来实现快速输入。在活动单元格的右下角有一个小黑块，称为填充句柄。,填充句柄,9,操作方法：将鼠标移到初始值所在活动单元格的右下角的“填充句柄”上，待其指针变为一个黑十字型，按住鼠标左键向下、向上或向右、向左拖动填充
3、句柄，直到所需单元格，即可完成自动输入功能。,10,1、自动填充：(1).若初始值为纯数字或纯字符，填充相当于数据复制，即重复填充。,自动填充效果,11,注意：当输入递增或递减的纯数字时，可以先按住键盘上的Ctrl键，再按住鼠标左键向下、向右或向上、向左拖动填充句柄。,12,(2).若初始值为字符和数字混合体，填充时字符不变，数字递增。,自动填充效果,13,(3).初始值选两个单元格字符或数字混合体时的自动填充。,自动填充效果,14,(4).用Excel提供的一些常用序列时的自动填充。,自动填充效果,15,2、用户自定义序列用户可以自定义序列并存储起来供以后填充使用。操作方法：单击“工具”菜单
4、中的“选项”命令项，打开一个“选择”对话框，再单击“自定义序列”标签。,16,自定义序列对话框,现有常 用序列,添加的 新序列,17,例一：创建一个新工作簿，输入表一的数据，以“商品销售统计”为文件名保存。,18,三、公式与函数,Excel 电子表格提供了对数据进行计算、统计的功能。1、公式：Excel的公式与数学表达式基本相同，由参与运算的数据、运算符和等号三部分组成。,19,(1).等号：它是公式的标志，公式必须以等号开头。例如：=12+15=B1*B2=(B1+B2)/B3=SUM (A1:A5),20,(2).运算符：算术运算符：完成基本的数学运算。比较运算符：比较两个数大小，它们的返
5、回值为逻辑值 True(真)和 False(假)。比较运算符有：等于(=)、大于()、小于(=)、小于或等于()。,21,文本运算符(&)：将两个以上两个本文连接为一个组合文本。如：“湖南”&“长沙市”，结果为：湖南长沙市。 引用运算符( : )：用于合并多个单元格区域。如：A1:F1（表示从A1到F1之间的所有单元格区域）。,22,(3).运算数参与运算的元素，包括：常数、单元格名称、单元格区域、函数等。,23,例二：用电子表格的输入公式功能计算表二的“应交税”。,对计算方法类似的单元格，可以采用自动填充和复制公式的方法进行计算。,输入公式,24,2、自动求和：选定进行求和计算的单元格区域，
6、单击常用工具栏上的“自动求和”按钮，即可完成该操作。,自动求和按钮,25,26,例三：用电子表格的输入公式和自动求和功能计算表一的“全年合计”和“季度平均”,27,3、数组公式的输入、编辑及删除,1数组公式的输入 数组公式的输入步骤如下： （1）选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。 （2）输入数组公式。 （3）同时按“Crtl+Shift+Enter”组合键，则Excel 自动在公式的两边加上大括号 。,28,例如，要在单元格A1:D1中分别输入10，20，30和40这4个数值，则可
7、采用下述的步骤： （1）选取单元格区域A1:D1，（2）在公式编辑栏中输入数组公式“=10,20,30,40”， （3）同时按Ctrl+Shift+Enter组合键，即可在单元格A1、B1、C1、D1中分别输入了10、20、30、40,29,假若要在单元格A1、B1、C1、D1、A2、B2、C2、D2中分别输入10、20、30、40、50、60、70、80，则可以采用下述的方法： （1）选取单元格区域A1:D2。 （2）在编辑栏中输入公式 （3）按Ctrl+Shift+Enter组合键，就在单元格A1、B1、C1、D1、A2、B2、C2、D2中分别输入了10、20、30、40和50、60、70
8、、80，,30,4、相对引用与绝对引用,相对引用是指公式所在的单元格与公式中引用的单元格之间的位置是相对的，单元格的引用与函数的位置有关。即如果公式所在的单元格的位置发生了变化，那么引用的单元格的位置也会相应在发生变化。,31,绝对引用：绝对引用是指被引用的单元与公式所在的单元格的位置是绝对的。即不管公式被复制到什么位置，公式中所引用的单元格的位置不变。,32,混合引用：在引用单元格的行和列之中一个是相对的，一个是绝对的。,33,数据的跨工作表操作 工作表间数据的调用格式: 工作表名!单元格名 例如:=sheet1!a1*5 工作簿间数据的调用 调用格式：工作簿名称工作表名称!单元格名称。,3
9、4,5、函数：函数的构成一般由两部分组成：函数名、括号内的参数；函数与公式一样，必须以等号开头。 例如： =SUM (B5 : E5) （求和）=AVERAGE (B5 : E5) （求平均值）=MAX(B5 : E5) （求最大值）=COUNT(B5 : E5) （统计记录个数）,35,函数输入的操作方法：选定单元格位置，单击菜单栏上的“插入”菜单，执行“函数”命令项，打开“粘贴函数”对话框；或单击常用工具栏上的“粘贴函数”按钮。例如：计算红星商场的商品全年合计和季度平均,36,选定,37,单击,38,求和函数对话框,数据区域,39,（1）、SUM函数、SUMIF函数,在财务管理中，应用最多
10、的是求和函数。求和函数有三个：无条件求和SUM函数、条件求和SUMIF函数和多组数据相乘求和SUMPRODUCT函数。无条件求和SUM函数 该函数是求30个以内参数的和。公式为 = SUM（参数1，参数2，参数N） 当对某一行或某一列的连续数据进行求和时，还可以使用工具栏中的自动求和按钮 。,40,条件求和SUMIF函数 SUMIF函数的功能是根据指定条件对若干单元格求和，公式为 =SUMIF(range,criteria,sum_range) 式中 range用于条件判断的单元格区域； criteria确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本； sum_range需要
11、求和的实际单元格。 只有当range中的相应单元格满足条件时，才对 sum_range 中的单元格求和。如果省略 sum_range,则直接对 range 中的单元格求和。 利用这个函数进行分类汇总是很有用的。,41,【例4】某商场2月份销售的家电流水记录如图下图所示，,42,则在单元格I3中输入公式 “=SUMIF(C3:C10,211,F3:F10)”，单元格I4中输入公式“=SUMIF(C3:C10,215,F3:F10)”，在单元格I5中输入公式“=SUMIF(C3:C10,212,F3:F10)”，单元格I6中输入公式“=SUMIF(C3:C10,220,F3:F10)”，即可得到分
12、类销售额汇总表。,43,（2） AVERAGE和AVERAGEA函数求平均值,AVERAGE函数的功能是计算给定参数的算术平均值。公式为 = AVERAGE（参数1，参数2，参数N） 函数中的参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。,44,AVERAGEA函数的功能是计算所有非空单元格的算术平均值。公式为 = AVERAGEA（参数1，参数2，参数N） 函数中的参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字作0处理、逻辑值为TRUE的参数作为1处理，
13、为FALSE的单元格，则作0处理。,45,（3 ） MIN函数和MAX函数,MIN函数的功能是给定参数表中的最小值，MAX函数的功能是给定参数表中的最大值。公式为 = MIN（参数1，参数2，参数N） = MAX（参数1，参数2，参数N） 函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。 例如，MIN（3，5，12，32）=3；MAX（3，5，12，32）=32。,46,（4 ） COUNT函数和COUNTIF函数,COUNT函数的功能是计算给定区域内数值型参数的数目。公式为 = COUNT（参数1，参数2，参数N） COUNTIF函数的功能是计算给定区域内满足特定条件的单元格的
14、数目。公式为 = COUNTIF（range，criteria） 式中 range需要计算其中满足条件的单元格数目的单元格区域； criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 COUNT函数和COUNTIF函数在数据汇总统计分析中是非常有用的函数。,47,例:EXCEL中进行统计分组,48,（5） IF函数,IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。在实践中，经常使用函数IF对数值和公式进行条件检测。公式为 = IF(logical_test,value_if_true,value_if_false）,49,(6) AND函数、OR函数和
15、NOT函数,这3个函数的用法如下： = AND（条件1，条件2，条件N） = OR（条件1，条件2，条件N） = NOT（条件）,50,【例6】某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于300000元且销售费用占销售额的比例不超过1%，则奖金提取比例为15%，否则为10%,51,【例5】例如，某企业对各个销售部门的销售业绩进行评价，评价标准及各个销售部门在2004年的销售业绩汇总如图下图所示，,练习：计算个人所得税,52,7、使用lookup函数查找数据,使用lookup函数可以返回向量或数组中的数值.它的向量形式是在单行或单列区域中查找数值，然后返回第二个单
16、行区域或单列区域中相同位置的数值。向量形式如下： LOOKUP(lookup_value,lookup_vector,result_vector),53,Lookup_value为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。 Lookup_vector为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。 需要注意的是Lookup_vector 的数值必须按升序排序：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分
17、大小写。 Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。 如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。 如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。,54,55,、使用match函数实现在数组中查找值,MATCH函数有两方面的功能，两种操作都返回一个位置值。 一是确定区域中的一个值在一列中的准确位置，这种精确的查询与列表是否排序无关。 二是确定一个给定值位于已排序列
18、表中的位置，这不需要准确的匹配. 语法结构为：MATCH(lookup_value,lookup_array,match_type),56,lookup_value为要搜索的值。 lookup_array：要查找的区域(必须是一行或一列)。 match_type：匹配形式，有0、1和1三种选择：“0”表示一个准确的搜索。“1”表示搜索小于或等于查找值的最大值，查找区域必须为升序排列。“1“表示搜索大于或等于查找值的最小值，查找区域必须降序排开。以上的搜索，如果没有匹配值，则返回N/A。,57,、使用index函数得到指定内容,函数 INDEX() 有两种形式：数组和引用。数组形式通常返回数值或
19、数值数组；引用形式通常返回引用。 （1）INDEX(array,row_num,column_num) 返回数组中指定单元格或单元格数组的数值。 Array为单元格区域或数组常数。Row_num为数组中某行的行序号，函数从该行返回数值。Column_num为数组中某列的列序号，函数从该列返回数值。需注意的是Row_num 和 column_num 必须指向 array 中的某一单元格，否则，函数 INDEX 返回错误值 #REF!。,58,（2）INDEX(reference,row_num,column_num,area_num) 返回引用中指定单元格或单元格区域的引用。 Reference
20、为对一个或多个单元格区域的引用。 Row_num为引用中某行的行序号，函数从该行返回一个引用。 Column_num为引用中某列的列序号，函数从该列返回一个引用。 需注意的是Row_num、column_num 和 area_num 必须指向 reference 中的单元格；否则，函数 INDEX 返回错误值 #REF!。如果省略 row_num 和 column_num，函数 INDEX 返回由 area_num 所指定的区域。,59,10、使用countblank函数统计空白单元格的个数,格式：countblank(range),60,11、使用FREQUENCY函数统计频率分布,格式：
21、FREQUENCY（data_array,bins_array） 其中： data_array表示用来计算频率的数组或者是一组数值。如果data_array中不包含任何数值，函数将返回零数组。 bins_array：表示某一个间隔，该间隔用于对参数data_array中的数值进行分组。由于函数 FREQUENCY 返回一个数组，必须以数组公式的形式输入。,61,12、使用mode函数返回出现频率最多的数值,格式：mode(number1,number2,）,62,13、使用median函数返回中值,格式： median（number1,number2,） 如果参数中共含有偶数个数值，函数将返回
22、中间两个数的平均值。,63,14、使用QUARTILE函数计算四分位数,格式： QUARTILE（array,quart) 其中：array表示需用要从中求四分位数的区域。Quart表示返回哪一个四分位值，它可以取5个值，即0,1,2,3,4，分别表示最小值，第一个四分位数，第二个四分位数，即中值，第三个四分位数和最大值。,64,15、使用large和small函数按条件返回最值,Large函数 格式：large(array,k) 作用：返回从大到小排列的第k个位置的数值 Small函数 格式small(array,k) 作用：返回第K个最小的数值,65,16、使用RANK函数返回数字的排位,
23、格式：rank（number,ref,order) 其中：number表示等待排位的数字；ref表示组数或者引用，但必须是数值型;order是一个数字，用以指明排位的方式。如果为0或者省略，函数返回降序排列的结果，如果不为0，函数返回升序排列的结果。,66,17、使用GROWTH 函数预测指数增长值,格式：GROWTH(known_ys,known_xs,new_xs,const) 作用：根据给定的数据预测指数增长值 其中： known_ys表示满足指数回归拟合曲线y=b*mx的一组已知的y值； known_xs表示满足指数回归拟合曲线y=b*mx的一组已知的x值，它是可选参数； new_xs
24、表示需要通过GROWTH函数返回的对应y值的一组新X值； Const是一个逻辑值，如果为TRUE或者省略，曲线中的b将按正常值计算；如果为FALSE，b将设计为1。 注：要以数组公式的形式输入。,67,18、使用var和vara函数计算样本方差,Var函数 Var函数的作用是计算基于给定样本的方差，其表达式如下： Var(number1, number2,) 参数可以是多种形式，但参数中的逻辑值和文本将被忽略,68,Vara函数 Vara函数的作用是计算基于给定样本的方差，其表达式如下： Vara(number1, number2,) 参数可以是多种形式，但参数中的逻辑值true作为1计算在内
25、，逻辑值false和文本作为0计算在内,69,19、使用stdev和stdeva函数计算样本的标准偏差,Stdev函数的作用是估算基于样本总体的标准偏差，其表达式如下： stdev(number1, number2,) 参数中的逻辑值和文本将被忽略 Stdeva函数的作用是估算基于样本总体的标准偏差，其表达式如下： stdeva(number1, number2,) 参数中的逻辑值true作为1计算在内，逻辑值false和文本作为0计算在内,70,标准偏差主要是用来衡量观测值与平均值的离散程度,其值越小表示总体的齐质性越高。 例如：stdev(90,80,85,90,87)的值为4.15932
26、7stdev(90,65,85,90,5)的值为36.15937,71,20、描述统计,72,21、使用poisson函数得到泊松分布,Poisson函数用于返回泊松分布，泊松分布通常用于预测一段时间内事件发生的次数。该函数的表达式格式如下： Poisson(x,mean,cumulative) 从表达式中可以看出该函数有3个参数。X表示事件数，如果X不是整数；计算时将截尾取整，如果X不是数值型，函数将返回”#VALUE”；如果X0，函数将返回错误值”#NUM!”。Mean表示期望值。如果mean为非数值型，函数将返回”#VALUE”；如果mean=0，将返回错误值“！”。Cumulative
27、是一个逻辑值，确定返回的概率表示形式。如果为true，表示函数返回泊布松累积分布概率；如果为FALSE,表示函数返回泊松概率密度函数。 例如：poisson(2,1,true),73,22、使用Normdist函数得到正态分布,Normdist函数用于返回指定平均值和标准偏差的正态分布，其表达式如下： Normdist(x,mean,standard_dev,cumulative) 其中：X表示要计算其分布的数值。Mean表示正态分布的算术平均值，如果它是非值型则返回错误值“#VALUE”。standard_dev表示正态分布的标准偏差。若它是非数值型，同样返回错误值“#VALUE”；若sta
28、ndard_dev=0,函数将返回错误值“#NUM！”。Cumulative是一个逻辑值，指明函数返回的形式。如果为TRUE，函数将返回累积分布函数值；如果为FALSE,则返回概率密度函数值。 例如： =Normdist(24,20,3,true),74,23、使用NORMSDIST函数返回标准正态分布,Normsdist函数返回标准正态分布函数，该分布的平均值为0,标准偏差为1。该函数的表达式如下： Normsdist(z) 其中z表示需要计算其分布的数值。如果z为数值型，那么函数将返回错误值“#VALUE”。,75,24、使用FDIST函数得到F概率分布,函数格式： Fdist(x,deg
29、ree_freedom1, degree_freedom2) 其中X表示参数值，如果X为负数，函数将返回错误值；degree_freedom1表示分子自由度; degree_freedom2表示分母自由度；如果第二、三两个参数不是整数，就将被截尾取整；如果=1010,函数将返回错误值;在这3个参数中,只要有一个是非数值型,函数将返回错误值.,76,25、使用FINV函数得到服从概率的F值,函数格式：Fdist(probability,degree_freedom1, degree_freedom2) Probability表示与累积分布相关的概率值，如果probability1,函数将返回错误
30、值；degree_freedom1表示分子自由度，degree_freedom2表示分母自由度。如果第二、三两个参数不是整数，就将被截尾取整；如果=1010,函数将返回错误值;在这3个参数中,只要有一个是非数值型,函数将返回错误值.,77,第二部分 EXCEL图表基础,78,一、创建图表,图表就是将单元格中数据以各种统计图表的形式显示；Excel中的图表分两种：嵌入式的图表：图表和数据源放置在同一工作表中。独立图表：是独立的图表工作表。,79,嵌入式,独立式,80,操作方法：选定需创建图表的数据区域，单击常用工具栏中的“图表向导”按钮，或执行“插入”菜单中的“图表”命令项。,图表向导,图表命令
31、,81,图表向导对话框一：图表类型,图表向导对话框二：数据源,82,图表向导对话框三：图表选项,83,图表向导对话框四：图表位置,84,例8：用红星商场2003年电器商品销售量表创建图表。,85,例题9：某企业2004年12个月的销售量与销售费用的有关数据如图所示，绘制各月销售额与销售费用之间关系的图表,86,87,地区销售分布图表的建立,企业的产品销往全国各地及世界各地，各地的销售量是不同的，我们可以利用Excel的地图分析工具建立销售数据地图，从而可以将企业产品在各地的销售情况更加直观地表示出来。,88,例10：某企业在某些省份的销售数据如图所示，建立数据地图：,89,90,练习11：根据
32、2004年红星国税局税收收统计表建立结构图,91,92,练习12：,93,二、图表的编辑,移动图表 改变图表的位置 调整图表的大小 设置图表区字体大小 修改图表的类型 为图表添加或者删除数据系列方法1：单击图表-添加数据菜单项弹出添加数据对话框,94,方法2：直接选择要被添加的数据系列单元格区域，并拖曳到图表内。当释放鼠标时，EXCEL将就拖入的数据对图表进行更新。这种方法只对嵌入在工作表中的图表有效。 方法3：选定要被添加的数据系列单元格区域复制到剪切板上，然后激活图表，单击菜单栏中的“编辑选择性粘贴”命令，根据需要，设置相关参数，单击“确定”按钮即可。,95,为图表添加趋势线 趋势线不仅可
33、以用来显示某个数据系列中数据的变化趋势，从而使用清晰地了解数据的变化情况，而且可以帮助用户预测未来的数据。用户可以向非堆积型二维面积图、条形图、柱型图、折腾线图、股价图、气泡图以及XY散点图的数据系列中添加趋势图。,96,丢失数据的处理: 操作步骤:1 选中图表,单击菜单栏中的“工具选项“命令 2、在弹出的对话框中单击“图表”标签，切换到“图表”选项卡,97,根据需要设置相关的参数,98,使用次坐标轴创建组合图表,99,第三部分 数据的获取与建立数据清单,100,一、建立与数据源的连接,步骤1 新建数据源。单击“数据导入外部数据 导入数据”命令 步骤2 单击“新建源”按钮，在数据库列表中选择“
34、其他/高级”选项 步骤3 单击“下一步”按钮，选择ODBC（开放数据库连接）数据源的驱动程序。,101,步骤4 单击“测试连接”按钮，若弹出“测试连接成功”消息框，则说明与数据的连接成功。单击“确定”按钮，会弹出“数据连接向导”对话框。在其中选择想要连接的数据表。单击“下一步”按钮，对刚刚新建的连接进行命名。单击“完成”按钮，即可完成建立与数据源的连接工作。,102,二、获取数据源的数据,步骤1 单击菜单栏中“数据导入外部数据新建数据库查询”命令（注：一定要选择“使用查询向导创建/编辑查询”复选框 步骤2 单击“确定”按钮，然后选择需要的列 步骤3 点击“下一步”按钮进行高级筛选 步骤4 选择
35、按关键字进行查询结果排序，单击“下一步”按钮，将获取的数据返回到EXCEL工作表中，单击完成按钮即可。,103,三、数据的分列,方法1 直接分列法 方法2 查找提取法（使用LEFT函数、RIGHT函数和FIND函数） 方法3 统计提取法(使用LEFT函数、RIGHT函数、LEN和LENB函数，其中LEN、LENB函数的作用是统计字符串的字符数，对于双字节字符如汉字，LENB按数值2进行统计，LEN按数值1进行统计）,104,第四部分 数据的编辑与查询,105,1、窗口的冻结与拆分2、数据的移动与复制3、查找与替换4、使用选择性粘贴5、数据的转置,一、编辑,106,二、数据分析,数据分析是对按一
36、定结构组织的数据进行排序、筛选、分类汇总等操作。 字段：数据表的一列称为字段。 记录：数据表的一行称为记录。 字段名：数据表的顶行称为字段名，字段名是字段内容的概括和说明。,107,字段,记录,字段名,108,1、数据排序：排序是指按照字母的升序或降序以及数值顺序来重新组织数据。操作方法：选中数据区，单击 “数据”菜单中的“排序”命令项，打开一个“排序”对话框，再进行排序设置。或单击常用工具栏上的“升序”或“降序”按钮。,109,选取,设置单列排序,110,2、筛选数据：(1).自动筛选：操作方法：选中有数据的某单元格，单击菜单栏上的“数据”菜单，选择“筛选”命令，再选择子菜单中的“自动筛选”
37、命令项，则每个字段名的右边都增加一个下拉箭头按钮。,111,下拉箭头按扭,选取,单击,112,(2).自定义筛选： 操作方法：单击某字段名的右边的下拉箭头按钮，选择其中的“自定义”项，会打开一个“自定义自动筛选方式”对话框，输入你的条件值。,113,输入筛选条件,选取自定义,114,（3）高级筛选 输入筛选条件 执行高级筛选命令 显示筛选条件 注:如果进行多条件筛选，分两种情况：“与”的关系，则条件放在同一行；“或”的关系，则条件放在不同的行。,115,4、分类汇总：注意:在进行分类汇总之前,必须对数据清单进行排序,数据清单的第一行里必须有列标记。操作方法：选中要分类汇总的数据单元格，单击菜单
38、栏上的“数据”菜单，选择“分类汇总”命令，打开一个“分类汇总”对话框，再进行汇总设置。,116,选取,设置分类汇总,117,分类汇总的结果,118,练习13：数据筛选 练习14：分类汇总,119,5、数据合并,在很多情况下，企业的销售数据分门分类记录，到年末时需要汇总计算到一张年度报表上，这时最简单的方法就是对它们进行合并计算，尤其是在需要合并的数据很多时，利用合并计算工具能节省很大的工作量，并且也不容易出错。现举一个简单的例子来说明合并计算的方法和步骤。,120,【例15】某企业四个季度的销售量统计如图下图所示，它们分别存放在不同的工作簿销售统计-1.xls，销售统计-2.xls，销售统计-
39、3.xls和销售统计-4.xls中，现要将它们汇总合并到另外一张名为销售统计-年度.xls）的工作簿中，则合并计算步骤如下：,121,（1）在“销售统计-年度.xls”的工作簿中选取单元格区域B3:B9。 （2）单击【数据】菜单，选取【合并计算】项，则弹出【合并计算】对话框 （3）在【函数】项中选“求和”。 （4）在【引用位置】中输入第一季度销售统计工作簿“销售统计-1.xls”的数据区域B3:B9，最好用鼠标选取，即：激活工作簿“销售统计-1.xls”，然后选取该工作表的B3:B9单元格区域，输入完毕后，单击【添加】按钮，则该单元格区域自动加入【所有引用位置】中去，并在【引用位置】的文字呈反
40、黑显示。,122,（5）仿照上述方法输入其他三个季度的资料。 （6）选中【创建连至数据源的连接】，这是为了能够查看最终汇总数据的来源，若没有这个要求，可不选此项。 （7）单击【确定】按钮，则最后的合并计算结果,123,6、保护数据,1 保护工作表 单击菜单“工具”“保护”“保护工作表”,124,选择保护功能：“内容”，即禁止更改工作表中的单元格与图表项；“对象”，即在工作图表中禁止删除、移动、编辑或缩放图形对象；“方案”，即在工作表中，禁止更改方案的定义。“密码”，即输入密码可防止别人取消对工作表的保护。 完成选择后，按“确定”按钮。,125,2 保护工作簿 保护工作簿操作如下： 单击菜单“工
41、具”“保护”“保护工作簿”，出现对话框。 选择保护功能：“结构”，即保护工作簿结构，避免删除、移动、隐藏、重命名工作表和插入工作表；“窗口”，即保护工作表窗口不被移动、缩放和关闭等。“密码”，即输入密码可防止别人取消对工作簿的保护。完成后按“确定”。,126,3 保护指定的单元格 指定单元格保护操作如下： 选择那些不需要保护的单元格。 选择“单元格格式”对话框的“保护”，注销“锁定”，最后单击“确定”。 保护整个工作表。 这样，除了刚才解除保护的单元格以外，其余单元格均处于保护状态。,127,第五部分 使用数据工具处理数据,128,安装分析工具,129,数据有效性标注无效数据,130,方法分析
42、工具,一、方差分析工具1、单因素方法分析：可对两个或更多样本进行简单的方差分析2、无重复的双因素分析：此分析工具可用于数据按照二维进行分类时的情况,131,3、可重复的双因素分析 此分析工具可用于数据按照二维分类且包含重复的双因素的情况。,样本数据,132,对该样本数据进行可重复双因素方法的步骤为： 步骤1、,133,步骤2：,134,分析结果,135,4、相关系数工具 例如：现观测同一现象的两个因素。试确定这两个因素的相关系数,136,步骤1：,137,步骤2：,138,相关系数的值,139,5、协方差工具6、描述统计工具,140,7、指数平滑工具,阻尼系数,141,8、F-检验工具,142
43、,9、直方图工具,143,10、移动平均工具,144,11、回归分析工具,145,第六部分 数据透视分析,146,147,数据透视表,一、选择合适的数据透视表数据在使用数据透视表之前,你必须知道你想做什么,确定希望分析的数据,并确定如何组织它. 二、创建数据透视表步骤1：选择数据源类型步骤2：选择数据区域步骤3：完成数据透视表的基本创建,148,步骤4：对数据透视表进行布局,149,步骤5：选项设置举例:求每个销售员的销售额是多少?源数据的检查应注意: 1、第一行必须包括标题 2、数据区不能有空行或空列 3、每一列中只能有一种类型数据 4、创建前不要有分类汇总,150,总结： 1、“行字段”垂
44、直显示数据，每行一项 2、“列字段”水平显示数据，每列一项 3、“页字段”以页显示数据。 4、“数据项”是显示和汇总数值的位置,151,透视表的编辑 1、修改字段名称 2、当求和不够用时，可以有多种计算方式 3、获取最新数据 4、设置报表的格式,152,数据的高级分析 1、添加字段 2、字段的交换 3、插入新的字段（计算项）,153,第七部分 EXCEL应用技巧,154,1、如何一次在多个单元格中输入相同的内容 操作方法： 在工作表中选中要填充内容的单元格，按住Ctrl键，可以选中不连续的单元格，选择好后，直接在最后一个选中的单元格中输入内容，然后按下Ctrl+Enter组合键，所有选中的单元
45、格中就会被同时填充了相同的内容。,155,2、如何实现行、列转换,操作方法：选中工作表中要转置的数据，按下Ctrl+C组合键将其复制到剪贴板，将光标移动要目的地，单击编辑菜单，选择选择性粘贴，在弹出的选择性粘贴对话中，选中转置项前的复选框，单击确定按钮即可。,156,3、在Excel中验证数据录入的惟一性,下面以录入员工身份证号码为例介绍一下操作的具体步骤。 1、设置有效性条件验证 假设G列为员工“身份证号”字段， G2单元格为第一个员工的身份证号码所在的单元格。在未输入之前，我们可先设置该列的有效性条件来确保该列数据的惟一性。 选中G2单元格，单击“数据”菜单中的“有效性”命令，弹出“数据有
46、效性”对话框，选择“设置”选项卡，在“允许”下拉列表中选择“自定义”，在“公式”框内输入“=COUNTIF(G:G,G2)=1” 2、设置出错警告提示信息 设置出错警告提示信息的目的在于提醒用户正确输入数据。具体步骤是：单击“数据有效性”对话框中的“出错警告”选项卡，在“标题”框内输入“数据输入错误”，在“错误信息”框内输入“你刚才输入的数据已经存在，请检查数据的惟一性！”。设置完之后，单击“确定”按钮,157,3、输入身份证信息 以上设置完成之后我们就可以向G列中输入员工的身份证号了。每输入一个员工的身份证号，Excel会自动对该数据进行有效性验证，如果该数据已经存在，系统将弹出出错警告提示
47、框 上述功能只能验证数据的惟一性，若数据位数输入错误，系统则检测不出这一错误。若在输入时需要同时验证数据的位数，还是以身份证号为例，可将图一中的公式改为“=AND(COUNTIF(G:G,G2)=1,OR(LEN(G2)=15,LEN(G2)=18)”，图二中的错误信息改为“请检查数据的惟一性或输入数据位数错！”。设置完后重新复制G2单元格的公式至G列其他的单元格。该公式的含义是：在G列输入的数据必须是惟一的且数据位数必须是15位或18位。 最后还需要提醒大家，由于G列输入的是身份证号，位数超过了11位数据，所以最好在输入数据之间，选将G列全部选定，设置“单元格格式”中的“数字分类” 格式为“
48、文本”格式，这样才能保证身份证号以正确形式输入。,158,4、在Excel中突出显示重复数据,同时选中数据所在的单元格区域（如B2至J20），执行“格式条件格式”命令，打开“条件格式”对话框。 单击最左侧方框右边的下拉按钮，在随后弹出的下拉列表中选择“公式”选项，然后在后面的方框中输入公式：=COUNTIF($B$2:$B$20,$B2)1（此处假定姓名存放在B2至B20区域内）。 再单击其中的“格式”按钮，弹出“单元格格式”对话框，在“字体”标签下，按“颜色”右边的下拉按钮，展开调色板，选中“红色”；再切换到“图案”标签下，选中一种颜色。 设置完成后，两次按确定按钮返回，相同数据所在的数据行
49、就被突出显示出来了,159,5、Excel中快速互换两列数据,用鼠标选定A列数据区域；把鼠标放在A列数据区域的右边；按下“Shift”键的同时，按下鼠标左键，这时鼠标变为向左的箭头；拖动鼠标至B列数据区域的右边，看到一条垂直的虚线（如果看到一条水平的虚线，表示在B列插入数据），同时松开“Shift”键和鼠标左键，这样就实现了A、B列的数据互换。同样，也可以实现两行数据的互换,160,6、在信息输入前就给予提示,希望在用户为单元格输入信息时，系统能自动的给予一些必要的提示，这样不但可以减少信息输入的错误，还可以减少修改所花费的时间 具体操作如下：首先选择需要给予输入提示信息的所有单元格。然后执行“数据”菜单中的“有效性”命令，在弹出的对话框中选择“输入信息”选项卡。接着在“标题”和“输入信息”文本框中输入提示信息的标题和内容即可,
