计算机应用基础win7志远教育教材征订与服务电话01082477073V1志远教育作品LOGO1模块四Excel电子表格的应用课件LOGO*过渡页Excel2010工作表工作表数据输入与工作表格式化过渡页2模块四Excel电子表格的应用课件LOGO*Excel2010基本概念1启动Excel后，其操作界面如图4-1所示。Excel的窗口主要包括快速访问工具栏、标题、窗口控制按钮、选项卡、功能区、名称框、编辑栏、工作区、行号、列标、状态栏和滚动条等。Excel2010的用户界面图4-1Excel的工作界面3模块四Excel电子表格的应用课件LOGO*Excel2010基本概念1Excel2010专业术语单元格引用工作簿单元格地址单元格和单元格区域工作表工作簿:一般由3个工作表组成,分别命名为：Sheet1Sheet3,最多可有255个工作表。当前活动的白色,其余灰色，其扩展名为.xlsx工作表:工作的主要对象,由行,列组成的表格,有：A,B,.Z,AA,AB,.AZ,.IA,IB,.IV(256)列;1,2.65536行;单元格:行和列的交叉处，表格的最小单位,每个单元格最多可填写32767字符活动单元格:当前正在操作的单元格，被黑线框住。单元格区域：是一个矩形块，由工作表中相邻的若干个单元格组成。单元格地址：每个单元格的行列地址，如，B2,A3;单元格区域：B2:E5。引用B3、A2单元格,C4：F7区域，就用B3,A2,C4:F7表示。单元格引用：绝对坐标：$B,$2,$D$6;相对坐标：A1，C4;混合坐标：A$44模块四Excel电子表格的应用课件LOGO*正文.模块四Excel2010工作表目 一Excel2010基本操作1选择“文件”“新建”命令，在弹出的窗口中可看到有“可用模板”和“Office.com模板”两大部分，如图4-2所示，双击“可用模板”中的“样本模板”可以看到本机上可用的模板。“Office.com模板”是放在指定服务器上的资源，用户必须联网才能使用这些功能。项工作簿的操作图4-2Excel模板5模块四Excel电子表格的应用课件LOGO*正文.模块四Excel2010工作表目 一Excel2010基本操作1（1）新建工作表新建工作表最快捷的方法是在现有工作表的末尾单击屏幕底部的“新建工作表”按钮。（2）移动或复制工作表移动工作表最快捷的方式：选中要移动的工作表，然后将其拖动到想要的位置。复制工作表，右击需要复制的一个或多个工作表，单击右键，在弹出的快捷菜单中选择“移动或复制工作表”命令，弹出如图4-3所示的对话框，按图示操作即可。项工作表的操作6模块四Excel电子表格的应用课件LOGOExcel2010工作表目 一1（3）删除工作表选中要删除的一个或多个工作表，右击，在弹出的快捷菜单中选择“删除”命令。（4）重命名工作表选中要重命名的工作表，右击，在弹出的快捷菜单中选择“重命名”命令，或者双击工作表标签，均可对工作表表标进行重命名。（5）改变工作表标签颜色选中要改变标签颜色的工作表，右击，在弹出的快捷菜单中选择“工作表标签颜色”命令。（6）更改新工作簿中的默认工作表数选择“文件”“选项”命令，然后在“常规”类别中的“新建工作簿时”下的“包含的工作表数”文本框中，输入新建工作簿时默认情况下包含的工作表数。默认新建工作表数最少为1，最多为255。项工作表的操作7模块四Excel电子表格的应用课件LOGO*Excel2010基本操作1单元格及单元格区域的操作选中单元格或单元格区域单元格（或单元格区域）的插入与删除：“开始”-“单元格”清除：单击“编辑”功能区的橡皮擦按钮8模块四Excel电子表格的应用课件1单元格及单元格区域的操作（4）移动与复制移动操作：移动操作会移走除单元格本身之外的所有信息，包括公式及其结果值、单元格格式和批注等，粘贴时也包括所有信息。操作方法为：选中要移动的单元格或单元格区域，单击“开始”选项卡上的“剪贴板”功能区的“剪切”按钮（也可以使用【Ctrl+X】组合键）。选中目标单元格，再单击粘贴按钮即可。在移动公式时，无论使用哪种单元格引用，公式内的单元格引用都不会更改。保留源列宽保留源列宽粘贴粘贴公式公式无边框无边框数值数值格式格式粘贴链接粘贴链接保留源格式保留源格式公式和数字公式和数字格式格式转置转置值和源格式值和源格式数值和数字格式数值和数字格式图片链接图片链接图片图片9模块四Excel电子表格的应用课件LOGO*Excel2010工作表目 一Excel2010基本操作1项单元格及单元格区域的操作（5）查找与替换单击“编辑”功能区的“查找”按钮可对工作表进行查找、替换、定位等操作，操作方法与Word类似，在此不再赘述。10模块四Excel电子表格的应用课件LOGO*Excel2010工作表目 一Excel2010基本操作1项工作表的保护和共享（1）保护工作簿的结构和窗口单击“审阅”选项卡，在“更改”功能区单击“保护工作簿”按钮，弹出如图4-12所示的对话框，用户可以锁定工作簿的结构，以禁止用户添加或删除工作表，或显示隐藏的工作表。同时，还可禁止用户更改工作表窗口的大小或位置。工作簿结构和窗口的保护可应用于整个工作簿中的所有工作表。11模块四Excel电子表格的应用课件LOGO*Excel2010工作表目 一Excel2010基本操作1项工作表的保护和共享（2）保护工作表单击图4-11中的“保护工作表”按钮，弹出如图4-13所示的对话框，在该对话框中可以设置“允许此工作表的所有用户进行”列表中，选择希望用户能够进行的操作。12模块四Excel电子表格的应用课件Excel2010工作表目 一1项工作表的保护和共享（3）保护单元格或单元格区域设置不受保护的区域（即用户可以更改的区域），操作步骤如下：选中要解除锁定（或隐藏）的单元格或单元格区域。在“开始”选项卡上的“单元格”功能区中，单击“格式”按钮，然后选择弹出菜单最下端的“设置单元格格式”命令。在“保护”选项卡上，根据需要选择清除“锁定”复选框或“隐藏”复选框，然后单击“确定”按钮。在“审阅”选项卡上的“更改”功能区中单击“保护工作表”按钮。设置不受保护区域也可以通过单击“允许用户编辑区域”按钮来设置选择希望用户能够更改的元素。设置受保护的区域（即用户不可以更改的区域），操作步骤如下：选中整张工作表中的所有单元格。在“开始”选项卡上的“单元格”功能区中，单击“格式”，然后选择“设置单元格格式”命令。单击“保护”选项卡，根据需要选择清除“锁定”复选框或“隐藏”复选框，然后单击“确定”按钮。则此状态下该工作表中所有的单元格都处于未被保护的状态。选中需要保护的单元格区域，例如A3:D5。再次单击“保护”选项卡，根据需要选中“锁定”复选框或“隐藏”复选框，然后单击“确定”按钮，则该工作表中仅A3:D5单元格区域处于被保护的状态。在“审阅”选项卡上的“更改”功能区中选择“保护工作表”命令。13模块四Excel电子表格的应用课件LOGO*过渡页Excel2010工作表输入Word文稿过渡页工作表数据输入与工作表格式化14模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1要用Excel2010进行数据信息的统计和分析，首先要建立一个二维表格。二维表格可以称为数据库中的关系型数据库。因此，一定要根据自己工作的目标对数据进行统计或分析前，选定要进行统计的各个项目，并冠以名字，在数据库中称为“字段”，在二维表格中是“列名称”，而参加统计的各元素，在同一行的所有单元格称为“记录”。项工作表数据输入基础建立工作表15模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1使用Excel工作表的目的是要利用工作表进行数据的统计和分析。所以，在统计和分析之前，必须建立一个以统计或分析为目标的工作表格。工作过程如下：根据工作目标要求，建立二维表格。尤其对目标统计或分析的项目，要建立完整的列字段名称。对表格内的数据进行统计或分析。可在表格中增加图表，以增加形象说明的力度。工作表格格式化，增加工作表的视觉效果。项工作表数据输入基础Excel2010数据统计分析过程16模块四Excel电子表格的应用课件LOGO*正文.模块四工作表数据输入与工作表格式化目 二1Excel2010中的文本通常是指字符或者是任何数字和字符的组合。任何输入到单元格内的字符集，只要不被系统识别成数字、公式、日期、时间、逻辑值，则Excel一律将其视为文本。在Excel中输入文本时，默认对齐方式是单元格内靠左对齐。在一个单元格内最多可以存放32767个字符。对于全部由数字组成的字符串，如邮政编码、身份证号码、电话号码等这类字符串，为了避免输入时被Excel认为是数值型数据，Excel2010提供了在这些输入项前添加“”（英文的单引号）的方法，来区分是“数字字符串”而非“数值”数据。例如，要在“B5”单元格中输入非数字的电话号“02088886666”，则可在输入框中输入“02088886666”。项文本输入文本输入17模块四Excel电子表格的应用课件LOGO*正文.模块四1在Excel2010中，当建立新的工作表时，所有单元格都采用默认的通用数字格式。通用格式一般采用整数（无千位分隔符）、小数（二位，如7.89）、负数格式，而当数字的长度超过单元格的宽度时，Excel将自动使用科学计数法来表示输入的数字。在Excel中，输入单元格中的数字按常量处理。输入数字时，自动将它沿单元格右对齐。有效数字包含09、+、-、（）、/、$、%、.、E、e等字符。输入数据时可参照以下规则：可以在数字中包括逗号，以分隔千分位。输入负数时，在数字前加一个负号（-），或者将数字置于括号内。例如，输入“-20”和“（20）”都可在单元格中得到-20。Excel忽略数字前面的正号（+）。输入分数（如2/3）时，应先输入“0”及一个空格，然后输入“2/3”。如果不输入“0”，Excel会把该数据作为日期处理，认为输入的是“2月3日”。当输入一个较长的数字时，在单元格中显示为科学计数法（如2.56E09），意味着该单元格的列宽不能显示整个数字，但实际数值仍不变。数字输入18模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项数字输入减少小数位数使用货币样式增加小数位数使用千位分隔样式使用百分比样式使用“开始”选项卡“数字”功能区按钮快速格式化数字“格式”详见：详见：P143-P14419模块四Excel电子表格的应用课件LOGO*1数字输入使用“设置单元格格式”对话框设置数字格式分分 类类说说 明明常规常规不包含特定的数字格式不包含特定的数字格式数值数值可用于一般数字的表示，包括千位分隔符、小数位数，还可以指定负数的显示方式可用于一般数字的表示，包括千位分隔符、小数位数，还可以指定负数的显示方式货币货币可用于一般货币值的表示，包括使用货币符号￥、小数位数、还可以指定负数的显可用于一般货币值的表示，包括使用货币符号￥、小数位数、还可以指定负数的显示方式示方式会计专用会计专用与货币一样，只是小数或货币符号是对齐的与货币一样，只是小数或货币符号是对齐的日期日期把日期和时间序列数值显示为日期值把日期和时间序列数值显示为日期值时间时间把日期和时间序列数值显示为时间值把日期和时间序列数值显示为时间值百分比百分比将单元格值乘以将单元格值乘以100并添加百分号，还可以设置小数点的位置并添加百分号，还可以设置小数点的位置分数分数以分数显示数值中的小数，还可以设置分母的位数以分数显示数值中的小数，还可以设置分母的位数科学计数科学计数以科学计数法显示数字，还可以设置小数点位置以科学计数法显示数字，还可以设置小数点位置文本文本在文本单元格格式中，数字作为文本处理在文本单元格格式中，数字作为文本处理特殊特殊用来在列表或数据中显示邮政编码、电话号码、中文大写数字、中文小写数字用来在列表或数据中显示邮政编码、电话号码、中文大写数字、中文小写数字自定义自定义用于创建自定义的数字格式用于创建自定义的数字格式右击使用快捷菜单或“数字”功能区右下角的下拉按钮，可打开“设置单元格格式”对话框。20模块四Excel电子表格的应用课件LOGO1公式输入算术运算符算术运算符可以完成基本的数学运算，如加、减、乘、除等，还可以连接数字并产生数字结果。算术运算符包括：加号（+）、减号（-）、乘号（*）、除号（/）、百分号（%）以及乘幂（）.文本运算符在公式中使用文本运算符（）时，以等号开头输入文本的第一段（文本或单元格引用），加入文本运算符，输入下一段（文本或单元格引用）。例如，用户在单元格A1中输入“第一季度”，在A2中输入“销售额”。在C3单元格中输入“A1累计A2”，结果会在C3单元格显示“第一季度累计销售额”。比较运算符比较运算符可以比较两个数值并产生逻辑值TRUE或FALSE。比较运算符包括=（等于）、（大于）、（不等于）、=（大于等于）引用运算符一个引用位置代表工作表上的一个或者一组单元格，引用位置告诉Excel在哪些单元格中查找公式中要用的数值。有三种引用运算符：冒号(区域运算符)、逗号(联合运算符)以及空格(交叉运算符)。见P146。在Excel工作表的单元格输入公式时，必须以一个等号(=)作为开头。等号后面的“公式”中可以包含各种运算符号、常量、变量、函数以及单元格的引用。21模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项公式和函数输入公式输入公式的修改和编辑在Excel2010编辑公式时，被该公式所引用的所有单元格及单元格区域的引用都将以彩色显示在公式单元格中，并在相应单元格及单元格区域的周围显示具有相同颜色的边框。当用户发现某个公式中含有错误时，需单击选中要修改公式的单元格。按【F2】键使单元格进入编辑状态，或直接在编辑栏中对公式进行修改。此时，被公式所引用的所有单元格都以对应的彩色显示在公式单元格中，使用户很容易发现哪个单元格引用错了。编辑完毕后，按【Enter】键确定或单击编辑栏中的按钮确定。如果要取消编辑，按【Esc】键或单击编辑栏中的按钮退出编辑状态。22模块四Excel电子表格的应用课件1函数输入函函 数数 名名 称称函函 数数 功功 能能SUM(number1,number2,)计算参数中数值的总和计算参数中数值的总和AVERAGE(number1,number2,)计算参数中数值的平均值计算参数中数值的平均值MAX(number1,number2,)求参数中数值的最大值求参数中数值的最大值MIN(number1,number2,)求参数中数值的最小值求参数中数值的最小值COUNT(value1,value2,)统计指定区域中有数值数据的单元格个数统计指定区域中有数值数据的单元格个数COUNTA(value1,value2,)统计指定区域中非空值（即包括有字符的单元格）的单元格数目（空值统计指定区域中非空值（即包括有字符的单元格）的单元格数目（空值是指单元格是没有任何数据）是指单元格是没有任何数据）COUNTIF(range,criteria)计算指定区域内满足特定条件的单元格的数目计算指定区域内满足特定条件的单元格的数目RANK(number,ref,order)求一个数值在一组数值中的名次求一个数值在一组数值中的名次YEAR(date)取日期的年份取日期的年份TODAY()求系统的日期求系统的日期IF(logical_test,valuel_if_true,value_if_false)本函数对比较条件式进行测试，如果条件成立，则取第一个值本函数对比较条件式进行测试，如果条件成立，则取第一个值，否则取，否则取第二个值第二个值VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)搜索表区域首行满足条件的元素，确定待检索单元格在区域中的行序号，搜索表区域首行满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值再进一步返回选定单元格的值FV(rate,nper,pmt,pv,type)求按每期固定利率及期满的本息总和求按每期固定利率及期满的本息总和PMT(rate,nper,pv,fv,type)求固定利率下贷款等额的分期偿还额求固定利率下贷款等额的分期偿还额LOGO23模块四Excel电子表格的应用课件LOGO*1公式和函数输入函数输入【例1】函数的使用。要求使用函数，求出图4-15所示工资表中职工号为51001职工的实发工资。使用函数的操作:第一步，选中需要使用函数的单元格F2，单击编辑栏中的插入函数按钮。第二步，根据工作目标，找到最适合目标要求的函数，如本例要求求出工资表中第一个职工的实发工资，实际上，实发工资是由职务工资与补贴之和组成，所以要用求和函数SUM()。第三步，要将合适的数据“填入”函数的括号中的参数，就可以完成或求出函数操作的结果数值。24模块四Excel电子表格的应用课件LOGO1公式和函数的复制单元格公式引用（1）相对引用在输入公式的过程中，除非用户特别指明，Excel一般是使用相对地址来引用单元格的位置。所谓相对地址是指：如果将含有单元地址的公式复制到另一个单元格时，这个公式中的各单元格地址将会根据公式移动到的单元格所发生的行、列的相差值，作同样的改变，以保证这个公式对表格其他元素的运算的正确。例如，将如图4-17所示的F2单元格复制到F3:F9，把光标移至F5单元格，会发现公式已经变为“=(C5+D5+E5)/3”，因为从F2到F5，列的偏移量没有变，而行作了一行的偏移，所以公式中涉及的列的数值不变而行的数值自动加3。其他各个单元格也做出了改变。25模块四Excel电子表格的应用课件LOGO1公式和函数的复制单元格公式引用（2）绝对地址的使用如果公式运算中，需要某个指定单元格的数值是固定的数值，在这种情况下，就必须使用绝对地址引用。所谓绝对地址引用，是指对于已定义为绝对引用的公式，无论把公式复制到什么位置，总是引用起始单元格内的“固定”地址。在Excel中，通过在起始单元格地址的列号和行号前添加美元符“$”，如$A$1来表示绝对引用。例如，在如图4-17所示的例子中，如果将F2中输入的相对地址改为绝对地址，当F2复制到F3:F9时，会出现如图4-18所示的结果，所有的学生的平均成绩都是“李晓科”的平均成绩。26模块四Excel电子表格的应用课件LOGO1公式和函数的复制单元格公式引用（3）混合地址引用单元格的混合引用是指公式中参数的行采用相对引用、列采用绝对引用；或列采用绝对引用、行采用相对引用，如$A3、A$3。当含有公式的单元格因插入、复制等原因引起行、列引用的变化时，公式中相对引用部分随公式位置的变化而变化，绝对引用部分不随公式位置的变化而变化。例如，制作九九乘法表。步骤如下：在B2单元格中输入“=B$1&*&$A2&=&B$1*$A2”。将B2复制到B3:B10。将B3复制C3，再将C3复制到C4:C10。将C4复制到D5，再将D5复制到D6:D10。依此类推，可完成九九乘法表的制作，如图4-19所示。27模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项创建迷你图什么是迷你图迷你图是工作表单元格中的一个微型图表（不是对象），可提供数据的直观表示。使用迷你图可以显示一系列数值的趋势（例如，季节性增加或减少、经济周期），或者可以突出显示最大值和最小值。在数据旁边放置迷你图可达到最佳效果。下面将以一个市场咨询发布的20082014中国网购市场交易规模及预测数据（见图4-20）为例来创建“较易额”的迷你图。28模块四Excel电子表格的应用课件LOGO*1创建迷你图迷你图的创建选择要在其中插入迷你图中的一个空白单元格或单元格区域。本例选中I3。单击“插入”选项卡，在“迷你图”功能区选择要创建的迷你图的类型“柱形图”，如图4-21所示。弹出如图4-22所示的对话框。在“创建迷你图”对话框时“数据范围”框中，输入包含创建迷你图所基于的数据单元格区域B3:H3。”位置范围”为$I$3，单击“确定”按钮。注：如果需要在多个单元格创建相同类型的迷你图，此处也可以选择一个单元格区域。用同样的方法可在I4单元格中创建一个“同比增大”折线图，结果如图4-23所示。迷你图的创建29模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项创建迷你图迷你图的编辑与格式化在工作表中选中一个或多个迷你图，将会出现“迷你图工具”，并显示“设计”选项卡。在“设计”选项卡中包括“迷你图”、“类型”、“显示”、“样式”和“分组”等多个功能区，如图4-24所示。30模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法自动完成在同一列中，对于在上面单元格曾经输入过的字符，在紧接的单元格如输入其中的第一个字时，Excel能自动填入其后的字符，如图4-25所示。当在C6单元格中输入“人”后，在“人”后Excel能自动填入“事部”，并以反白显示，按【Enter】键即可，否则无须理会，继续输入其他字符。31模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法选择列表在同一列中反复输入相同的几个字段值，如要反复输入“人事部”和“销售部”。可以在输入了“人事部”和“销售部”以后，在待输入的新单元格上右击，在弹出的快捷菜单中选择“从下拉列表中选择”命令，单元格下方就会弹出一下拉列表框，该列表中记录了该列出现过的所有数据，如“人事部”和“销售部”，如图4-26所示，只要从列表中选择“人事部”即可完成输入。32模块四Excel电子表格的应用课件LOGO*1提高数据输入正确性和效率的方法利用“自定义序列”自动充填数据对于需要经常使用的特殊数据序列，例如一组多次重复使用的字符或中文序列号，可以将其定义为一个序列，在输入表格数据时，可使用“自动填充”功能，将数据自动输入到工作表中。（1）使用自动填充功能之前，必须利用“自定义序列”增加本次要输入的数据系列。操作步骤如下：选择“文件”“选项”命令，弹出“Excel选项”对话框，选择“Excel选项”对话框中的“高级”选项，在右侧窗口中单击“编辑自定义列表”按钮，如图4-27所示。33模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法利用“自定义序列”自动充填数据在“输入序列”文本框中输入“主机”，然后按【Enter】键，然后输入“显示器”，再次按【Enter】键，重复操作该过程，直到输入所有的数据。单击“添加”按钮，就可以看到自定义的序列已经出现在对话框中，如图4-28所示。34模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法数据的自动填充字符自动填充功能可以把单元格的内容复制到同行或同列的相邻单元格，也可以根据单元格的数据自动产生一串递增或递减序列。例如，在图4-29中，把光标移至C6单元格右下角的填充柄（此时鼠标会变成十字形状），拖动至C8单元格，那么C6单元格的内容就被复制到C7:C8区域，如图4-29所示。35模块四Excel电子表格的应用课件LOGO*1提高数据输入正确性和效率的方法序列填充上面所列的自动填充一般是以列（或以行）为填充对象进行有规律的填充。但对于等比数列或工作日的自动填充上述方法就很难完成。对于特殊情况可用下面的方法完成。选择初始单元格A2，填入第一个序列号，如输入10001。单击“开始”选项卡“编辑”功能区的“填充”按钮，选择“序列”命令，弹出如图4-30所示的对话框。在对话框的“序列产生在”选项区域中选中“列”单选按钮，之后在“类型”选项区域中选中“等差序列”单选按钮。在“步长值”文本框中输入“3”，终止值填入10019，单击“确定”按钮，就能看到如图4-31所示的序列。36模块四Excel电子表格的应用课件LOGO*1提高数据输入正确性和效率的方法数据有效性输入（1）数据有效性的设置数据有效性的输入提示信息和出错提示信息功能，是利用数据有效性功能，在用户选定的限定区域的单元格，或在单元格中输入了无效数据时，显示自定义的提示信息或出错提示信息。例如，在工作表中，为C4:C11单元格区域按如下步骤进行数据有效性设置。选择单元格区域C4:C11。选择“数据”选项卡，单击“数据工具”功能区的“数据有效性”按钮，如图4-32所示。在弹出的对话框中选择“设置”选项卡，在“有效性条件”选项区域的“允许”下拉列表框中选择“整数”选项，然后完成如图4-33所示的设置。37模块四Excel电子表格的应用课件LOGO*1提高数据输入正确性和效率的方法数据有效性输入（1）数据有效性的设置单击“输入信息”选项卡，在“标题”文本框中输入“成绩”，在“输入信息”文本框输入“请输入口语成绩”。单击“出错警告”选项卡，在“标题”文本框中输入“错误”，在“出错信息”文本框输入“必须介于0100之间”。单击“确定”按钮。设置完成后，当指针指向该单元格时，就会出现如图4-34所示的提示信息。如果在其中输入了非法数据，系统还会给出警告信息。38模块四Excel电子表格的应用课件LOGO*1提高数据输入正确性和效率的方法数据有效性输入（2）特定数据序列利用数据有效性功能，设置特定的数据系列。例如，在“加班情况登记”工作表中，当鼠标指针指向C2:C19单元格区域任意一个单元格的时候，显示下拉列表框，提供“技术部”、“销售部”、“办公室”三个数据供选择，如图4-35所示。设置特定数据序列的操作步骤如下：选择单元格区域C2:C19。选择“数据”选项卡，单击“数据工具”功能区的“数据有效性”按钮，在弹出的对话框中选择“设置”选项卡，在“有效性条件”选项区域的“允许”下拉列表框中，选择“序列”选项，在“来源”文本框中输入“技术部,销售部,办公室”，需要注意的是各选项之间要用英文的逗号相隔。单击“确定”按钮，如图4-36所示。39模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法特定区域内一组数字的快速输入【例2】请输入学生成绩表中6个同学语文、数学、英语和生物四科的成绩，如图4-37所示。鼠标在需要输入的起始单元格C2开始拖动到F9。这个区域已变成蓝色，只有起始单元格C2是白色。在白色单元格输入第一个学生的语文成绩80后按【Tab】键，白色区域右移，再输入该学生的数学成绩，按【Tab】键，依此类推，直至全部成绩输入完成。40模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法自定义输入在数据输入的过程中有许多的重复项，但又没有很强的规律性，无法使用前面所讲的方法进行快速录入。图4-38所示的第F列的数据，如果每一个数据都重复录入“p00-000-”等内容，将是一件很繁琐的事。解决这个问题可以使用“单元格格式”对话框中的“数学”选项卡下的“自定义”。操作步骤如下：41模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法自定义输入选中第F列需要自定义格式的单元格区域，在本例中可选中F2:F7。在“开始”选项卡下单击“数字”功能区右下角的下拉按钮弹出如图4-39所示的对话框。42模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法自定义输入在“数字”选项卡下选“自定义”选项，在“类型”文本框中输入“p00-000-0000”，单击“确定”按钮。在F2:F7中分别输入：123、24、8即可得到如图4-38所示的结果。自定义格式有很多的格式设置，限于篇幅本节不再讲述，希望本例能起到抛砖引玉的作用。43模块四Excel电子表格的应用课件LOGO*工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法获取外部数据【例3】将记事本文件中的“学生信息.txt”导入到Excel表中。操作步骤如下：选择“数据”选项卡，在“获取外部数据”功能区单击“自文本”按钮，如图4-40所示。44模块四Excel电子表格的应用课件LOGO*正文.模块四工作表数据输入与工作表格式化目 二1项提高数据输入正确性和效率的方法获取外部数据在弹出如图4-41所示的对话框中，找到需要导入的数据源“学生信息.txt”，单击“导入”按钮弹出“文本导入向导”的三个步骤，可以分别对源文本的分隔符、导入起始行、目标数据区的格式等项目进行设置。单击“完成”按钮弹出如图4-42所示的对话框。45模块四Excel电子表格的应用课件LOGO*1提高数据输入正确性和效率的方法获取外部数据在如图4-42所示的对话框中设置导入数据的位置为“现有工作表”的$A$1单元格，单击“确定”按钮。操作结果如图4-43所示。46模块四Excel电子表格的应用课件LOGO*1工作表格式化【例4】请对“餐馆进货单”进行格式设置，设置前后的效果如图4-44所示。具体要求如下：将A1单元格中的表格标题“某餐馆进货单”格式设置为12号宋体加粗，并使用A1:D1单元格合并居中；将A2:D2单元格区域跨列合并，单元格内容右对齐；将单价(即B4:B14)和金额(即D4:D15)设置为保留两位小数，并在数据前加人民币符号。将A3:D14单元格区域的内边框设置为虚线，外加边框设置为双实线。将B4:B14区域中超过5的数据设置为加粗倾斜并加12.5%的浅灰色底纹。47模块四Excel电子表格的应用课件LOGO*1工作表格式化获取外部数据操作步骤如下：选中A1:D1单元格区域，在“开始”选项卡中单击“对齐方式”功能区中的“合并后居中”按钮，在“字体”功能区将字体设置为“宋体”，字号设置为“12”，单击按钮使字体加粗。选中A2:D2单元格区域，在“开始”选项卡，单击“对齐方式”功能区中右面的下拉按钮，在弹出的选项中选“跨越合并”按钮，再单击使单元格中的数据右对齐。48模块四Excel电子表格的应用课件LOGO*1工作表格式化获取外部数据同时选中B4:B14和D4:D15单元格区域(方法为：先选中B4:B14单元格区域，按住Ctrl键不放，再选中D4：D15单元格区域)，在“数字”功能区单击按钮“中文(中国)”按钮。选中A3:D14单元格区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，在弹出的窗口中单击“边框”选项卡，如图4-45所示。选中B4:B14单元格区域，在如图4-46所示的“样式”功能区选择“条件格式”“突出显示单元格规则”“大于”命令，可弹出如图4-47所示的对话框。49模块四Excel电子表格的应用课件LOGO*过渡页工作表的数据统计和分析输入Word文稿过渡页50模块四Excel电子表格的应用课件LOGO*要用好数据统计和数据分析，首先必须熟悉函数的使用功能以及参数设置规则，有针对性地利用单元格复制（地址引用）的相对地址和绝对地址的运用，准确输入函数统计分析用的数值或数据区域，才能使函数的统计分析有满意的运行结果。“数据”选项卡中的排序、筛选、合并计算、模拟分析、分类汇总和“插入”选项卡中的数据透视表等分析工具一般是对整个工作表的统计或数据分析，所以在统计分析前，要选取连字段名在内的工作表的全部区域。数据统计与分析概述工作目标工作目标使使 用用 工工 具具应应 用用 说说 明明求和、平均值、最大值、求和、平均值、最大值、最小值最小值使用对应的函数使用对应的函数SUM(),AVERAGE(),MAX(),MIN()函数括号内的数值或区域就是要求统计的数值或数据所在的区域函数括号内的数值或区域就是要求统计的数值或数据所在的区域求统计有数字的单元格个求统计有数字的单元格个数数COUNT()函数括号内的区域就是统计数据所在的区域函数括号内的区域就是统计数据所在的区域求非空单元格个数求非空单元格个数COUNTA()函数括号内的区域就是要统计的数据所在的区域函数括号内的区域就是要统计的数据所在的区域求符合条件的单元格个数求符合条件的单元格个数COUNTIF()在参数对话框中先设条件，再设区域在参数对话框中先设条件，再设区域给符合条件的单元格冠个给符合条件的单元格冠个名字名字IF()如如IF(D2=60，及格，不及格）。，及格，不及格）。 判断判断D2 单元格中的数据如大于等单元格中的数据如大于等于于60分，在指定单元格（如分，在指定单元格（如E2）写上）写上“合格合格”，否则写上，否则写上“不合格不合格”查找和复制数据查找和复制数据VLOOKUP()根据工作表指定待复制列的单元格的各元素，查找被复制的区域与根据工作表指定待复制列的单元格的各元素，查找被复制的区域与指定列相同项后，与被复制的某列数据一起复制到工作表相同的数指定列相同项后，与被复制的某列数据一起复制到工作表相同的数据列中据列中对某一列的数值进行对某一列的数值进行“排排位位”RANK()在指定列的表中某一列的数据进行排名次，排名次序整个工作表的在指定列的表中某一列的数据进行排名次，排名次序整个工作表的结构不会改动结构不会改动从某年月计算年龄从某年月计算年龄YEAR(date)和和NOW() 年龄的计算年龄的计算=YEAR(NOW()-YEAR(data)51模块四Excel电子表格的应用课件LOGO*数据统计与分析概述工作目标工作目标使使 用用 工工 具具应应 用用 说说 明明求还贷款利息PMT()求固定贷款在固定利率下的每期还贷数求存款的本息FV()求按每期固定利率及期满的本息总和对某一列的数值进行顺序的“排序”“数据”-“排序”根据工作表中的某列的数值大小，按从小到大或从大到小，重新排列工作表的记录。工作表的记录结构将重新排列找出符合条件的记录“数据”-“筛选”求符合一、二个简单条件的数据，选择“筛选”-“自动筛选”命令，较复杂的选择条件，选择“筛选”-“高级筛选”命令对工作表的某个类别数据进行求和、平均、求最大或最小等汇总分析“数据”-“分类汇总”首先对工作表中要进行“分类”的列，用排序的方法，实现按“类别”排列。再按分析要求，在“分类汇总”对话框中选择统计类别进行分类汇总。可设立或改动“三维”的数据进行数据分析“插入”-“数据透视表”能灵活地设立页、行、列字段对工作表的数据进行多种汇总统计分析求表达式或函数中一个或两个变量变化时对结果产生的系列分析“数据”-“模拟分析”首先定义一个或二个变量所在的单元格，定义表达式（函数），再决定运算结果放在的行或列位置将相同表格结构的多个表格汇总合并“数据”-“合并分析”需要合并的表格的行、列结构大致相同，合并计算的效果才好。操作中在对话框中应选择标签位置的对一组数据进行统计分析“工具”-“数据分析”本教材只要求掌握“方差”、“描述统计”、“直方图”、“排位和百分比排位”、“抽样”等五个统计分析的操作52模块四Excel电子表格的应用课件LOGO* 求平均分：求考试成绩的平均值，用函数AVERAGE()，结果放在单元格D11。 函数AVERAGE()的用法是：AVERAGE(number1,number2，），括号内的参数可以是一组数字，最多为30个。如要求15、38、90的平均数，就可以写AVERAGE(15,38,90），或数值所在单元格区域，如本例中，数学考试成绩的区域在D2:D10，语文成绩在E2:E10，就可用AVERAGE(D2:D10）和AVERAGE(E2:E10）。 （请大家分析数学考试中有个字符“缺考”，用了D2:D10区域，对数学的平均数有没有影响？回答是没有，因为区域内只有数值才能参加统计运算）在D11中直接输入=AVERAGE(D2:D10）或者选中D11，单击编辑栏的插入函数按钮，在弹出的对话框中选择“统计”中的“AVERAGE函数”选项，在参数中输入D2:D10，单击“确定”按钮。求学生每科的平均分、合格率、最高分、最低分、两科总分统计函数、IF函数、BANK函数、排序、筛选的应用53模块四Excel电子表格的应用课件LOGO* 求最高分：用MAX(number1,number2,函数，用法同平均值。在D15中输入=MAX(D2:D10)。 求最低分：用MIN(number1,number2,)函数。在D16中输入=MIN(D2:D10)。 求参加考试人数：用COUNT(value1,value2, .)，求区域内有数值的单元格个数，“有数值”是表示有考试成绩，“缺考”不是数值，是字符。参加数学考试的人数：在D12中输入COUNT(D2:D10)。 求合格人数：用COUNTIF(range,criteria)函数，求符合条件的单元格个数，放在D13单元格中。求学生每科的平均分、合格率、最高分、最低分、两科总分统计函数、IF函数、BANK函数、排序、筛选的应用54模块四Excel电子表格的应用课件LOGO* 求合格率：合格率是参加考试的合格人数除以参加考试的人数。本案例数学考试人数在D12，合格人数在D13，在单元格D14中输入公式=D13/D12，“/”表示“除以”。 求每个学生的语文和数学两科成绩总分，有两个方法：方法1：如在F2单元格中输入=D2+E2。方法2：用函数SUM()。本题是在F2中输入=SUM（D2:E2）。求学生每科的平均分、合格率、最高分、最低分、两科总分统计函数、IF函数、BANK函数、排序、筛选的应用55模块四Excel电子表格的应用课件LOGO* 将语文成绩高于80分的学生选出。 操作步骤如下：选中数据区域A1:F10或其中的任意一个单元格。选择“数据”选项卡，在“排序与筛选”功能区单击“筛选”按钮，此时每一个字段名的右边都有一个下拉按钮。如图4-51所示，单击“语文”中的下拉按钮，在下拉菜单中选择“数字筛选”-“大于”命令，打开图4-52 所示的对话框。在对话框左边的输入框中选择“大于”选项，在右边的输入框中输入80，单击“确定”按钮。挑选出符合分析条件的学生统计函数、IF函数、BANK函数、排序、筛选的应用56模块四Excel电子表格的应用课件LOGO* 将语文成绩大于60分，且小于90分的学生选出。分析：语文成绩大于60分，且小于90分，是同一字段列中的“与”条件。因为是在同一列，可以用“自动筛选”完成。操作步骤如下：单击“数据”选项卡，在“排序与筛选”功能区单击“自动筛选”按钮，取消先前的筛选结果。选中A1:F11单元格区域或其中的任意一个单元格。单击“数据”选项卡，在“排序与筛选”功能区单击“自动筛选”按钮。单击“数字筛选”-“自定义筛选”按钮。在图4-54所示的对话框中分别输入大于60，第二行输入小于90，选中“与”单选按钮，单击“确定”按钮，结果如图4-55所示。挑选出符合分析条件的学生统计函数、IF函数、BANK函数、排序、筛选的应用57模块四Excel电子表格的应用课件LOGO* 将语文成绩大于70分的男生选出。 分析：要将符合两个以上不同列的条件的数据筛选出来，如果使用自动筛选来完成，需要对“语文”和“性别”两个字段分别进行筛选。方法与上两例相似，在此不再赘述。 在“高级筛选”操作中，必须在工作表的一个位置设置“条件区域”。 “与”条件：将两个条件放在同一行，表示语文成绩大于70分的男生，如图5-56所示。 “或”条件：将两个条件放在不同行，表示语文成绩大于70的学生或者是男生，如图4-57所示。统计函数、IF函数、BANK函数、排序、筛选的应用58模块四Excel电子表格的应用课件 将语文成绩大于70分的男生选出。操作步骤如下：输入条件区域：在D12处输入“语文”，E12处输入“性别”，对照在其下面的单元格D13中输入“70”和E13中输入“男”。选中A1:F11单元格区域或其中的任意一个单元格。单击“数据”选项卡，在“排序与筛选”功能区单击“高级”按钮。弹出如图4-58所示的对话框。在打开的对话框中选中“将筛选结果复制到其他位置”单选按钮。如果列表区域为空白，可单击“列表区域”右边的按钮，用鼠标从条件区域的A1拖动到F10，输入框中出现“$A$1:$F$10”。再单击“条件区域”右边的按钮，用鼠标从条件区域的D12拖动到E13，输入框中出现“$D$12:$E$13”。单击“复制到”右边的按钮后，选择筛选结果显示区域的第一个单元格，如A15。单击“确定”按钮，结果如图4-59所示。挑选出符合分析条件的学生59模块四Excel电子表格的应用课件LOGO* 对两科总分从高分到低分进行排序。（用“数据”功能区的“排序”命令按钮） 对两科总分进行排位操作。（用函数BANK()操作。） 对两科总分高于和等于150分的同学设置“录取”，小于150分的同学设置“不录取”。（用函数IF()操作）请分别对图4-48的成绩表完成以下操作任务统计函数、IF函数、BANK函数、排序、筛选的应用60模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三【例5】对两科总分从高分到低分进行排序。 单击F1:F10数据区域的任意一个单元格。 单击“数据”选项卡中“排序和筛选”功能区的“排序”按钮（如果是升序则单击按钮）即可，如图4-61所示。项统计函数、IF函数、BANK函数、排序、筛选的应用61模块四Excel电子表格的应用课件LOGO* 如果是对多个关键字排序或者需要按别的方式排序，需要单击“排序”按钮，弹出图4-62所示的对话框。在该对话框中：单击“添加条件”按钮可设置多个关键字排序。单击“排序依据”按钮可以设置按“数值”或“单元格颜色”或“字体颜色”排序。单击“次序”按钮可以分别对每一个关键字设置排序方式。单击“选项”按钮可以将排序“方向”设置为“按列排序”或“按行排序”。将排序“方法”设置为“字母”排序或“笔划”排序。统计函数、IF函数、BANK函数、排序、筛选的应用62模块四Excel电子表格的应用课件【例6】对两科总分进行排位操作。（用RANK()函数） 在单元格G2中输入函数RANK()，弹出图4-63所示的对话框。 在第一个输入框中输入“F2”，即需要排位的数值所在的单元格。在第二个输入框中输入需要对它进行排位的数值区域F2:F10，但必须用绝对地址表示，要写成$F$2:$F$10。 单击“确定”按钮，结果如图4-64所示。 选取G2后，用填充柄下拉，结果如图4-65所示。63模块四Excel电子表格的应用课件LOGO*【例3】对两科总分高于或等于150分的同学添加“录取”，小于150分的同学添加“不录取”。（用IF()函数）本例有两种解决方法：方法1：在G2单元格中直接输入：“=IF(F2=150,“录取”,“不录取”)”。注意：输入的字符常量都要有英文的双引号。方法2：选中G2单元格，插入IF函数IF()，在如图4-66所示的对话框第一个输入框中输入“F2=150”，在第二个输入框中输入“录取”，第三个输入框中输入“不录取”。单击“确定”按钮，结果如图4-67所示。（在该对话框中输入的字符常量不必写上英文的双引号，系统会自动添加）统计函数、IF函数、BANK函数、排序、筛选的应用64模块四Excel电子表格的应用课件LOGO*举一反三：以工资表求“预扣基金”为例，IF()的判断条件是两个以上，应该如何操作？例如在应发工资中，如果应发工资在4000以上，扣500做预扣基金；应发工资在30004000，扣400；20003000，扣300。在F2单元格中输入完整的IF参数：=IF(E24000,500,IF(E23000,400,IF(E22000,300)E2是“应发工资”列的第一个单元格。请大家注意三个IF参数格式的标准写法，不能有错误，结果如图4-68所示。统计函数、IF函数、BANK函数、排序、筛选的应用65模块四Excel电子表格的应用课件LOGO*题目：对图4-69所示的工资表从入职时间算出该职工的在职工龄。如果应发工资在4000以上，扣应发工资的20%做预扣基金；应发工资在30004000，扣应发工资的15%做预扣基金；20003000，扣应发工资的10%做预扣基金。计算每一位职工的实发工资，并按部门分类求出各部门的实发工资总数和全体职工的实发工资总额。 在职工龄的计算步骤： 在D列的右边增加一列，在E2中输入“工龄”。 在E3中输入=YEAR(NOW()-YEAR(D3)，单击“确定”按钮，再将E3中的分工复制到全列，结果如图4-70所示。日期函数和分类汇总的应用从入职时间算出该职工的在职工龄66模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三 操作：在G3中输入“=IF(F34000,F3*20%,IF(F33000,F3*15%,IF（F32000,F3*10%)”。单击编辑栏中的“”，结果如图4-71所示。预扣基金的计算项日期函数和分类汇总的应用67模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三 操作：在单元格H3中输入表达式=F3-G3，结果如图4-72所示。实发工资的计算项日期函数和分类汇总的应用68模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三 操作步骤如下： 选取A2:F12区域中的任意一个单元格。先对“部门”进行排序(“数据”-“排序”)，使相同的部门排在一起，以便于按“部门”进行汇总。 单击“数据”选项卡“分级显示”功能区的“分类汇总”按钮，打开对话框如图4-73所示，在“分类字段”下拉列表中选择“部门”选项，在“汇总方式”下拉列表中选择“求和”选项，在“选定汇总项”下拉列表中选择“实发工资”复选框，单击“确定”按钮，运算结果如图4-74所示。各部门实发工资总数和全体职工的实发工资总额的计算项日期函数和分类汇总的应用69模块四Excel电子表格的应用课件LOGO*题目：制作一个文具公司的季度办公用品销售表，并计算每个品种的利润和销售额。用数据透视表以销售人员、品种名称和季度对销售额进行求和分析。原工作表如图4-75所示。VLOOKUP函数和数据透视表的应用日期函数和分类汇总的应用70模块四Excel电子表格的应用课件LOGO*VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)中： lookup_value是销售表中要查找的第一个品种所在的单元格，如本例是B2。 table_array是被查找的价格表所在的区域，如本例是在D14:G19，但写到参数区时，必须用绝对地址表示$D$14:$G$19。也可用选择“插入”“名称”命令定义区域D14:G19为“价格表”后，在参数table_array输入框中输入“价格表”。 col_index_num是价格参数表区域中要输入的列数，如第一个操作要输入“单位”，这个“单位”在D14:G19区域中的第2列，所以输入2。如要输入进货价，这个“进货价”在D14:G19区域中的第3列，则输入3，输入销售价，则输入4。 range_lookup中输入“FALSE”。这样就完成VLOOKUP()函数对品种名称的复制操作的定义。VLOOKUP函数日期函数和分类汇总的应用71模块四Excel电子表格的应用课件LOGO*“单位”列的完整的输入是：在E2中输入=VLOOKUP(B2,$D$14:$G$19,2,false)。 在D14:G19区域建立如图4-76所示的价格表，并按图输入所有数据。 在单元格E2中输入=VLOOKUP(B2,$D$14:$G$19,2,false)，用拖动填充柄复制到E11，如图4-76所示。VLOOKUP函数日期函数和分类汇总的应用72模块四Excel电子表格的应用课件LOGO* 同理，要输入“进货价”，在单元格F2中输入=VLOOKUP(B2,$D$14:$G$19,3,false)，并拖动填充柄复制到F11。 要输入“销售价”，在单元格G2中输入=VLOOKUP(B2,$D$14:$G$19,4,false)，用拖动填充柄复制到G11。 求“利润”。在H2中输入=D2*(G2-F2)，用拖动填充柄复制到H11。 求“销售额”，在I2中输入=D2*G2，用拖动填充柄复制到I11。VLOOKUP函数日期函数和分类汇总的应用73模块四Excel电子表格的应用课件LOGO*数据透视表是比“分类汇总”更为灵活的一种数据分析方法。它可以同时灵活变换多个需要统计的字段对一组数值进行统计分析，统计可以是求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差。题目：制作一个数据透视表，用图4-77所示的办公用品销售表中的品种名称、销售人员、季度对销售额作“求和”统计。操作步骤： 选择操作区域A1:I11中的任意一个单元格。 在“插入”选项卡上的“表格”功能区中，单击“数据透视表”按钮，弹出如图4-78所示的对话框。数据透视表日期函数和分类汇总的应用图图4-78“创建数据透视图表创建数据透视图表”对话框对话框74模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三 对要分析的数据可以是当前工作簿中的一张数据表或者一张表中的部分区域，甚至可以是外部数据源。数据透视表的位置可以放在现有工作表中也可以新建一张工作表来单独存放数据透视表。 本例按图示设置后单击“确定”按钮，弹出如图4-79所示的布局对话框。数据透视表项日期函数和分类汇总的应用75模块四Excel电子表格的应用课件LOGO* 在如图4-79所示的布局的对话框中，将“销售人员”拖到“筛选字段”框，将“品种名称”拖到“行”区域，将“季度”拖到“列”区域，将“销售额”拖到“数据”区域，结果如图4-80所示。数据透视表日期函数和分类汇总的应用76模块四Excel电子表格的应用课件LOGO* 如果在如图4-79所示的布局对话框中将“季度”拖到“筛选字段”框，将“销售人员”拖到“行”区域，将“品种名称”拖到“列区域”，数据区仍是“销售额”，得出透视表的另一个结果，如图4-81所示。数据透视表日期函数和分类汇总的应用77模块四Excel电子表格的应用课件LOGO*建立透视表后，可以通过三种方式进行编辑和格式化。例如将求销售额的和改为求平均值。方法1：使用“数据透视表字段列表”，如图4-82所示。可以通过该对话框更改透视表布局、设计分类汇总方式等操作。数据透视表日期函数和分类汇总的应用78模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三方法2：通过“数据透视表工具”来操作。选中数据透视表会自动弹出“数据透视表工具”，如图4-83所示。“选项”选项卡可用于透视表的编辑，“设计”选项卡可用于透视表的格式化。方法3：通过快捷菜单，选中操作对象，右击弹出对该对象的相关操作。数据透视表项日期函数和分类汇总的应用79模块四Excel电子表格的应用课件LOGO* 单击数据透视表数据区域内的任意一个单元格，激活“数据透视表工具”，单击“选项”选项卡，如图4-83所示。 单击“选项”功能区中的“排序和筛选”组中的“插入切片器”按钮，打开“插入切片器”对话框。根据要筛选数据的类别，选中要创建切片器的字段名称“季度”、“品种名称”和“销售人员”复选框，如图4-84所示。 单击“确定”按钮，将在当前工作表中创建与所选字段对应的切片器，结果如图4-85所示。切片器日期函数和分类汇总的应用80模块四Excel电子表格的应用课件LOGO* 此时可以单击不同切片器中的选项来筛选当前数据透视表，其效果与直接单击数据透视表中的字段筛选按钮是相同的，只是在切换器中进行该操作更直观也更方便。例如：单击“李四”、“一季度”和“传真机”，使用切片器对数据透视表筛选后的结果如图4-86所示，它筛选出了所有“李四”在“一季度”销售的“传真机”情况。日期函数和分类汇总的应用切片器81模块四Excel电子表格的应用课件LOGO*在银行每月存款1000元，银行每年的利息是2.25，求2年后可以得本金和利息共多少？分析：求本息可用函数FV()计算。FV()函数基于固定利率及等额分期付款方式，返回某项投资的未来值。FV()函数的参数格式是FV(rate,nper,pmt,pv,type)。其中： rate为各期（以月计息）利率，是一固定值。如本案例年利率是2.25%,月息就为（2.25/12）%。 nper为总存款（或贷款）期。本例为2年，nper值为24。 pmt为各期所应交的存款（或得到）的金额，本案例为1000元。其数值在整个年金期间（或投资期内）保持不变。本案例是求存款，pmt值应写-1000，计算结果是正数。如果是借款，写1000，计算结果是负数，表示应还金额。 pv为现值，指该项投资开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金，如果省略pv，则假设其值为零。本例可不设定。 type为数字0或1，用以指定各期的付款时间是在期初还是期末，1为期初，0为期末，如果省略type，则假设其值为零。（期初表示一个会计期间的初期，期末表示一个会计期间的末期，不同会计期存入，所发生的利息不同），本例可不设定或设为0。依本例要求，FV()函数的设置是：FV（2.25/12,24,-1000）或FV（2.25/12,24,-1000，0，0）。得本金和利息合计324432.43。财务函数应用财务函数应用82模块四Excel电子表格的应用课件LOGO*例8：存款200000元，年利率是4.5%，求年利息是多少？如年利率改为4.2%、4.4%、4.8%、5.0%、5.2%的年利息分别是多少？如存款改为300000、400000、500000、600000、700000、800000元,年利率仍为4.5%，求年息各有多少？如果年利率和存款本金都不同，如将上面两问综合成一个统计分析，可以求出各种组合的年利息吗？例8中的题操作：在单元格A1中输入200000，单元格A2中输入4.5%，在单元格A3中输入=A1*A2。在单元格B5至F5中分别输入4.2%、4.4%、4.8%、5.0%、5.2%。在单元格A6中输入=A1*A2。选择区域A5:F6。选择“数据”选项卡，在“数据工具”功能区选择“模拟分析”“模拟运算表”命令，打开对话框如图4-87所示，在对话框中的“输入引用行的单元格”中输入“$A$2”后，单击“确定”按钮，结果如图4-88所示。模拟运算表应用83模块四Excel电子表格的应用课件LOGO*说明：$A$2表示产生系列变化的变量所在的单元格，必须用绝对地址表示。运算分析的结果以行形式表达，所以在对话框中，选用“输入引用行的单元格”输入。如果分析的结果用列形式表示，变量变化以列形式输入，并在对话框中选用“输入引用列的单元格”输入，如图4-89所示，运算结果如图4-90所示。模拟运算表应用84模块四Excel电子表格的应用课件LOGO*例8中的题操作：求年利率和存款数两个变量同时变化时产生的系列结果。在单元格A1中输入200000，单元格A2中输入4.5%，单元格A3中输入=A1*A2。在单元格B5至F5中分别输入4.5%、4.4%、4.8%、5.0%、5.2%。A6至A10分别输入200000、300000、400000、500000、600000。在单元格A5中输入=A1*A2。这个单元格定义很重要，是形成行与列变量运算结果的依据。选择单元格B5:F10区域。选择“数据”选项卡，在“数据工具”功能区选择“模拟分析”“模拟运算表”命令，打开如图4-91所示的对话框，在对话框中的“输入引用行的单元格”中输入“$A$2”，“输入引用列的单元格”中输入“$A$1”后，单击“确定”按钮，结果如图4-92所示。模拟运算表应用85模块四Excel电子表格的应用课件LOGO*【例9】贷款购房，需要资金90万，部分房款可由银行贷款取得，年利率假设为4%，采取每月等额还款的方式，贷款数额分别是90万、80万、70万、60万、50万、40万、30万，还款期限分别是10年、15年、20年、25年、30年，求贷款额和还款期不同组合的月还款额？模拟运算表应用86模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三【例10】华工计算机有限公司每三个月需要统计下属星星公司和新华公司的销售情况，请用“合并计算”将两个公司的报表合并统计，如图4-94和图4-95所示。项报表合并计算应用87模块四Excel电子表格的应用课件LOGO*分析：“合并计算”是将Excel中多个表格汇总统计的操作。“合并计算”可以将Excel中多达256个表格的数据进行汇总统计。统计分析有求和、平均、求最大、求最小、计数等运算，也可以进行统计分析如标准偏差、方差等。操作： 在新的表格中，首先确定新表格位置，光标定位于新表格的左上角。选择 “数据”选项卡，在“数据工具”功能区单击“合并计算”按钮，弹出如图4-96的对话框。 在对话框中的“函数”下拉列表框选择汇总方式。本例为求和。 选择需要合并的表格的单元格区域，并添加到对话框中的“所有引用位置”。因为两个公司的表格在不同的表格中，所以要输入星星！$A$2:$D$7、新华！$A$2:$D$6。 选中对话框中的“标签位置”中的“首行”和“最左列”复选框。 单击“确定”按钮，操作结果如图4-97所示。报表合并计算应用88模块四Excel电子表格的应用课件LOGO* 如本例中，星星公司和新华公司三个月的销售表如图4-94和图4-95所示，合并操作的对话框设置如图4-96所示，单击“确定”按钮后结果如图4-97所示。请注意，星星公司和新华公司三个月的销售表中一、二、三月的列字段是相同的，只是星星公司销售中多了“传真机”，因为选择“最左列”复选框而被汇总了。按位置统计：对话框中的“标签位置”中的“首行”和“最左列”两个复选框框都不选择。“合并计算”将以划分好的单元格区域的左上角开始，只对有数据的列汇总统计。如果星星公司的报表如图4-98所示，新华公司报表不变，合并计算的结果（星星公司的引用位置要重新添加）如图4-99所示。报表合并计算应用89模块四Excel电子表格的应用课件LOGO*数据统计分析应用统计分析工具统计分析意义方差方差反映了分析对象数据的分散或波动的程度描述统计描述统计是通过图表或数学方法，对数据进行整理、分析，并对数据的分布状态、数字特征和随机变量之间关系进行描述的统计分析方法直方图直方图是一种统计报告图，由一系列高度不等的纵向柱形图表示数据分布的情况，横轴表示数据类型，纵轴表示数据分布情况排位和百分比排位排位和百分比排位是对分析对象进行从高到低的排位，并显示对该位置从最低位起，位于总数据的百分比值抽样抽样是在数据分析中对分析对象进行随机抽取统计分析对象的方法数据统计分析应用用Excel进行数据统计分析，是通过“数据”选项卡“分析”功能区的“数据分析”按钮完成的。注：如果在“数据”选项卡看不到“分析”功能区，请选择“文件”-“选项”-”加载项“命令；然后选中”分析工具库“复选框单击“确定”即可。在“数据分析”对话框中有23个统计分析的功能选择，我们只对其中五个统计分析工具进行入门学习，如下表。90模块四Excel电子表格的应用课件LOGO*数据统计分析应用【例11】用方差分析，统计分析工作表中两个班的数学考试成绩。操作：单击“数据”选项卡“分析”功能区的“数据分析”按钮，选择“方差分析：单因素方差分析”分析工具；在对话框中按图4-102所示的填写。单击“确定”按钮后，结果如图4-103所示。方差分析的意义在于：它反映了一组或多组数据的分散、波动或稳定的程度。比如本例从结果中可以看到，一班和二班的平均分都差不多，一个是79分，另一个是75分，它们两个的方差分别是一班144，二班219。二班的方差比一班高，说明二班的成绩分布比较散，高低成绩差别较大。方差分析91模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三项数据统计分析应用 【例12】请用描述统计统计一班的数学考试成绩。操作：单击“数据”选项卡“分析”功能区的“数据分析”按钮，选择“描述统计”分析工具，在对话框中按图4-104所示进行设置，单击“确定”按钮后，结果如图4-105所示。在输入框中，选择输入区域要包括第一行的“数学成绩”，并选中“标志位于第一行”复选框。这样在分析结果里的第一行就会出现“数学成绩”。描述统计92模块四Excel电子表格的应用课件LOGO*数据统计分析应用【例13】请用直方图分段统计一班的数学考试分段成绩。操作步骤如下：单击“数据”选项卡“分析”功能区的“数据分析”按钮，弹出“数据分析”对话框如图4-100所示，在对话框中选择需要的分析工具。单击“确定”按钮后，打开该分析工具的“直方图”对话框，如图4-101所示。在对话框中必须选择和填写的有以下几部分。直方图93模块四Excel电子表格的应用课件在“输入”选项区中定义以下单元区域：“输入区域”中选择参加分析的数据区域，这个区域可以不包含首个单元格的字段名。在本例中选“B2:B18”，如果是手工输入应该加“$”，即为“$B$2:$B$18。“接收区域”是指要进行分析依据项设置所在的区域。如在直方图分析中设置成绩分类“59、69、79、89、99”的区域是在“C2:C6”。在分析统计中区域地址都是以绝对地址表示的，如“C2:C6”，应输入“$C$2:$C$6”。“标志”复选框，一般不选择。在“输出选项”选项区中须定义：输出结果有3个单选按钮“输出区域”、“新工作表组”、“新工作簿”，一般选择“输出区域”并加以定义，定义输出区域的左上角单元格地址即可。其他复选框，用户可按分析的需要选择。如直方图必选“图表输出”。结果如图4-106所示。直方图94模块四Excel电子表格的应用课件LOGO*数据统计分析应用 直方图分析：直方图是用柱状图表示数据分组的情况，横坐标表示分组，纵坐标表示该类别的数量。用直方图分析的关键在于选择数据分组的“类别”，分组得当，对数据的分析较为准确。如本例统计的结果是不合格人数1人，6070分有3人等。阅读直方图，可从图形中呈现的形状分析数据特性分布的状况，进而分析造成该形状图形的原因。这些图形有标准型、孤岛型、双峰型、折齿型、陡壁型、偏态型、平顶型等。利用直方图，还可以用来对一组有规律的数据进行计数。图4-107所示为在一组数字中挑选出有多少个5、2、1。用直方图很方便地统计结果：5有7个，2有5个，1有3个。直方图95模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三项数据统计分析应用【例14】请对一班的数学成绩进行名次排位操作步骤如下：选择“数据”选项卡，在“分析”功能区选择“数据分析”“排位和百分比排位”命令，在如图4-108所示对话框中进行设置，在输入框中，选择输入区域要包括的“数学成绩”，并选中“标志位于第一行”复选框，单击“确定”按钮后，统计结果如图4-109所示。排位和百分比排位分析96模块四Excel电子表格的应用课件LOGO*工作表的数据统计和分析目 三项数据统计分析应用 【例15】请对一班17个同学随机抽样10个同学参加活动。操作步骤如下：选择“数据”选项卡，在“分析”功能区选择“数据分析”“抽样”命令，在如图4-110所示对话框中进行设置，在抽样方法框中，选择“随机”单选按钮，样本数为10，其他设置同上。单击“确定”按钮后，统计结果如图4-111所示。排位和百分比排位分析97模块四Excel电子表格的应用课件LOGO*Excel图表应用目 四项建立图表一般有以下步骤：阅读、分析要建立图表的工作表数据，找出“比较”项。通过“插入”选项卡中的“图表”功能区命令按钮创建图表。选择合适的图表类型。最后对建立的图表通过“图表工具”进行编辑和格式化。Excel2010中提供了11种基本图表类型如图4-112所示。每种图表类型中又有几种到十几种不等的子图表类型，在创建图表时需要针对不同的应用场景，选择不同的图表类型。点评图表概述图表概述98模块四Excel电子表格的应用课件LOGO*Excel图表应用目 四项以一个简单的学生成绩表（见图4-113）为例，以其中一到三班的成绩建立一个柱形图。建立图表结果及图表各部分的说明如图4-114所示。点评图表建立操作99模块四Excel电子表格的应用课件LOGO*建立图表操作步骤如下：选取工作表中需要建立图表的区域，如本例选取A1:D4。单击“插入”选项卡。在“图表”功能区选择所需要的图表类型，如图4-115所示；本例单击“柱形图”的下拉按钮选择其下的子类型“三维簇状柱形图”。生成的图表如图4-116所示。图表建立操作100模块四Excel电子表格的应用课件LOGO*Excel图表应用目 四项图表建立以后，如果对图表的显示效果不满意，可以利用“图表工具”功能区按钮或在图表任何位置右击弹出快捷菜单对图表进行编辑或对图表进行格式化设置。下面以用“图表工具”功能区为例对如图4-116所示的图表进行编辑和格式化。选中图表的任一位置即可弹出“图表工具”功能区。单击“设计”选项卡可弹出如图4-117所示的功能区。图表编辑和格式化101模块四Excel电子表格的应用课件LOGO*Excel图表应用目 四项单击“数据”功能区的“切换行/列”按钮将“班级”转化为横坐标轴，“课程”转化为图例。单击“图表布局”功能区的按钮，选择“布局3”单击“图表样式”功能区的“样式2”。图表编辑和格式化102模块四Excel电子表格的应用课件LOGO*单击“图表工具”“布局”选项卡，“布局”工具栏如图4-118所示。单击“标签”功能区的“坐标轴标题”按钮-“主要横坐标轴标题”-“坐标轴下方标题”，为图表添加横轴坐标标题。再次单击“标签”功能能区的“坐标轴标题”按钮选择“主要纵坐标轴标题”“竖排标题”命令，为图表添加纵轴坐标标题。分别将“图表标题”改为“各班平均分图表”；横“坐标轴标题”改为“班级”；纵“坐标轴标题”改为“平均分”。选择“图表工具”“格式”选项卡，“格式”工具栏如图4-119所示。图表编辑和格式化103模块四Excel电子表格的应用课件LOGO*Excel图表应用目 四项选中图表标题“班级平均分图表”，在“形状样式”功能区选择“强烈效果-橙色，强调颜色6”。选中“图表区”，在“形状样式”功能区选择“形状填充”“纹理”“羊皮纸”。选中“图例”，在“艺术字样式”功能区选择“填充-白色，轮廓-强调文字颜色1”。再次选中“图表区”，在“大小”功能区将图表的大小修改为：形状高度为8cm，形状宽度为12cm。点评图表编辑和格式化104模块四Excel电子表格的应用课件LOGO*【例16】根据图4-120的图表，删除三班的图例，增加四、五班的图例。操作：鼠标移至三班的柱型图表处右击，在快捷菜单中选择“删除”命令，三班的柱型图形即被删除。或直接建立四、五班三科的成绩：在柱型图处，点击右键，在快捷菜单中选择“选择数据”，在对话框中的“图表数据区域”输入区域A1:D3和A5:D6，如图4-121所示，单击“确定”按钮，结果如图4-122所示。图表编辑和格式化105模块四Excel电子表格的应用课件LOGO*Excel图表应用目 四项图4-123所示为某房产公司2010年销售数量及平均房价统计表。由于该表中的两组数据“数量”和“平均价”相差非常大，使用该表中的数据做出的柱型图只能看到“平均价”的变化情况而完全看不到“数量”的变化情况，如图4-124所示。点评复杂图表106模块四Excel电子表格的应用课件LOGO对于这种情况需要增加一个Y轴用于显示“数量”并需要修改该数据系列的图表类型以使图表更加清晰生动。操作方法如下：选中图4-123中的A2:F4单元格区域，切换到“插入”选项卡，单击“图表”功能区中的“柱形图”按钮，在弹出的对话框中选择“二维柱形图”“簇状柱形图”选项。操作结果如图4-124所示。选中“数量”数据系列，右击，在弹出的快捷菜单中选择“设置数据格式”命令。在弹出的快捷菜单中选择“系统选项”“次坐标轴”命令。单击“关闭”按钮。再次选中“数量”数据系列，右击，在弹出的快捷菜单中选择“更改系列图表类型”命令。在弹出的快捷菜单中选择“XY（散点图）”“带平滑线的散点图”命令。单击“确定”按钮。操作结果如图4-125图所示。复杂图表107模块四Excel电子表格的应用课件课件制作课件制作：志远教育教材服务于征订电话01082477073谢谢观看108模块四Excel电子表格的应用课件