第4章-数据统计和分析excel-2003

第4章电子表格(中文Excel2003)用途：以二维表格作为基本操作界面，供用户方便地填写和编辑表格内容，可对表格中的数据完成很多数据处理功能，如进行各种数学运算，制作各种统计图表和进行一些数据分析工作本章基本内容Excel基本知识工作表的编辑和格式化公式与函数图表的制作数据管理与统计其他有关功能4.1概述4.1.1Excel的启动和退出4.1.2Excel窗口当前工作簿名称为Book1；行号栏和列标栏工作表标签Sheet1,Sheet2等标题栏菜单栏“常用”工具栏“格式”工具栏编辑栏工作表标签Office助手状态栏滚动条任务窗格“全选”按钮1.工作簿工作簿由一至多个相关的工作表组成2.工作表工作表是由行和列组成的一张表格。工作表中的行以数字1，2，3…来表示，列以一个英文字母A，B，C…来表示每个工作表最大可达256列×65,536行切换当前工作表的方法3.单元格单元格是指工作表中的一个格子。每个单元格都有自己的坐标(即行列位置)，也称为单元格地址。单元格地址表示为：列标+行号行号单元格及其地址(如A1，B3等)4.单元格区域一组相邻的呈矩形的单元格。如A1:C3，B2:D5等)4.1.3基本概念工作表工作表工作表......工作簿示例统计学生的总评成绩4.2工作簿的基本操作4.2.1新建一个工作簿单击“常用”工具栏上的“新建”按钮选择“文件”菜单中的“新建”命令，打开“新建工作簿”任务窗格，可以从中选择一个命令来创建新工作簿新建的工作簿文件取名为Book2,Book3等文件名默认的扩展名为.xls4.2.2在工作表中输入数据1.选定单元格2.输入/编辑数据进入修改状态：按F2或双击编辑栏用来指明当前被编辑单元的名称和编辑内容在单元格中输入数据时，键入的内容会同时出现在单元格和编辑栏中当前单元格内容函数按钮确定按钮取消按钮当前单元格地址编辑栏A1B2当前单元格3.数据类型处理数据时，会遇到各种不同类型的数据。例如，一串文本(如姓名)、数值(如成绩、年龄和体重等)、逻辑值(如是否大学毕业)等等五种基本数据类型数值型、文本型、日期/时间型、逻辑型和数组型(1)数值型数据(简称数字)用0～9和特殊字符构成的数字。右对齐科学记数法，如1.23457E+11、3.27835E-08(2)文本型数据任何数字、字母、汉字及其他字符的组合。左对齐(3)日期/时间型数据表示各种不同格式的日期和时间如21-Oct-96、1999-5-29等(4)逻辑型数据描述事物之间的成立与否的数据只有两个值：FALSE(假值)和TRUE(真值）(5)数组型数据作为一个整体处理的若干个数据通常一个数组用一个区域表示数据两种形式：常量固定不变的量公式可用于计算输入数据时要注意的一些问题(1)输入分数，应在分数前加入0和空格。如“02/3”(2)如果输入的数字有效位超过单元格的宽度，必须调整列宽(3)把输入的数字作为文本处理，可在数字前加单引号。如“´1234”(4)在单元格中既要输入日期又要输入时间，则用空格分隔开(5)输入日期时，可按yyyy-mm-dd的格式输入(6)在单元格中输入当前系统日期时按Ctrl+；键输入当前系统时间时按Ctrl+Shift+；键(7)希望以12小时制来显示时间，需在键入的时间后加AM或PM如“8:20PM”(8)按Alt+Enter组合键可强制在某处换行(9)在一个选定区域内输入数据，输入结束后按Ctrl+Enter组合键确认4.2.3保存工作簿4.2.4打开工作簿例4.1建立如下所示的工作表内容4.3工作表的编辑4.3.1选定单元区“先选定，后操作”是Excel重要的工作方式1.选定单元格常用的方法有：(1)单击；(2)使用光标移动键2.选定一个单元格区域3.4.5.6.撤消选定4.3.2单元格的插入和删除1.(1)选定要插入新单元格的位置或区域。(2)选择“插入”菜单中的“单元格”命令，打开对话框(3)从对话框中选择一种合适的插入方式2.插入空白行或列例4.2在图4.3所示工作表中的第4行处插入2行3.4.删除行或列4.3.3数据的复制、移动和清除1.复制数据方法二：用鼠标拖放来复制数据例4.3把A3:B4区域的数据复制到G4:H5区域中2.移动数据移动单元格区域的数据，其操作方法与复制操作基本相同3.清除数据4.选择性粘贴一个单元格含有数值、格式、公式等多种特性，数据复制时通过“选择性粘贴”可以只需要复制它的部分特性先将数据复制到剪贴板，再选定待粘贴目标区域中的第一个单元格，选择“编辑”菜单中的“选择性粘贴”命令，打开对话框。从对话框中选择相应选项即可4.3.4表格数据的查找与替换4.3.5撤消与恢复4.4.1文本格式的设置字体、字号、字形、对齐方式等4.4.2数字格式的设置例如，数字1234.56在格式#,###.#下为1,234.64.4.3调整单元格的行高和列宽方法一：采用菜单命令选择“格式”菜单中的“行”命令(或“列”命令)根据需要进行设置方法二：采用拖放操作拖动鼠标来调整行高(或列宽)4.4单元格格式设置4.4.4表格框线的设置使用“格式”工具栏上的“边框”各按钮，或执行“格式”菜单中的“单元格”命令，可以为表格设置线条和边框线单击“格式”工具栏上“边框”按钮右边的小箭头，屏幕出现“边框”各小图标4.5公式和函数4.5.1公式的使用公式是用运算符将数据、单元格地址、函数等连接在一起的式子，以等号“=”开头=5*10-20常数运算=A6+B1对单元格A6和B1中的值相加=SQRT(10+A2)使用Excel函数，SQRT表示求开方根1.算术运算符、文本运算符和关系运算符=8^3*25%表示8的立方再乘以0.25，结果为128=5*8/2^3-B2/4若单元格B2的值是12，则结果为2=”计算机”&”电脑”运算结果是”计算机电脑”=A1&B2把单元格A1和A2中的文本进行连接=B2＜42比较结果为TRUE=B2＞=32比较结果为TRUE=B2＜30比较结果为FALSE=A1+80假设A1为日期型数据，则A1+80表示日期A1加上80天公式举例2.公式的输入和复制当在一个单元格中输入一个公式后，Excel会自动加以运算，并将运算结果存放在该单元中。以后当公式中引用的单元格数据发生变动时，公式所在单元格的值也会随之变动例4.4计算出学生的总评成绩(=机试×40%+笔试×60%)(1)单击总评成绩第一个单元格E2(2)键入计算公式“=C2*0.4+D2*0.6”后按回车键，此时在单元格E2处显示出计算结果82.2(3)再次单击单元格E2使之成为活动单元格；单击“复制”按钮(4)选定区域E3:E6；单击“粘贴”按钮三种表示方式(1)相对地址：以列标和行号组成，如A1，B2，C3等公式复制时，若引用公式的单元格地址发生变动，公式(2)绝对地址：以列标和行号前加上符号“$”构成，如$A$1，$B$2(3)混合地址：它是上述两种地址的混合使用方式如$A1(绝对列相对行)，A$1(相对列绝对行)等。公式复制时，公式中相对行和相对列部分会随引用公式的单元格地址变动而变动，而绝对行和绝对列部分保持不变4.出错信息当公式(或函数)表达不正确时，系统将显示出错信息3.单元格地址表示方式格式：=函数名(参数1,参数,…..)举例：=SUM(C2:C6)=AVERAGE(A1:A3,C2:C6)SUM求和数AVERAGE求平方值MAX求最大值MIN求最小值COUNT求数值数据的个数COUNTIF条件求数值数据的个数FREQUENCY频率分布统计IF条件选择4.5.2函数的使用函数应用举例例4.5计算基本工资总数，并将结果存放在单元格C7(1)单击单元格C7(2)键入公式“=SUM(C2:C6)”，并按回车键例4.6计算补贴总数和扣除总数，并将结果分别存放在单元格D7和E7中(1)选定D2:E7(2)单击“自动求和”按钮∑，即可对这两列分别求和，和数分别存放在两列的最后一个单元格上函数应用举例例4.7计算基本工资的平均值，并将结果存放在C8中例4.8利用函数MAX求出基本工资数的最大值，并将结果存放在单元格C9中例4.9假设单元格A1，A2，A3，A4的内容分别为1，2，空，“ABC”，则COUNT(A1:A4)的值为2例4.10若要求出基本工资数大于等于500的职工人数，可以采用：=COUNTIF(C2:C6,”=500”)例4.11在工作表(A1:D9)中，要求出班号为21的班学生人数，可以采用：=COUNTIF(D2:D9,”21”)或=COUNTIF(D2:D9,D3)频率分布统计函数FREQUENCY频率分布统计函数用于统计一组数据在各个数值区间的分布情况格式：FREQUENCY(data_array,bins_array)功能：计算一组数(data_array)分布在指定各区间(由bins_array确定)的个数例4.12在成绩表A1:D9中，统计出成绩＜60，60≤成绩＜70，70≤成绩＜80，80≤成绩＜90，成绩≥90的学生数各有多少(1)在区域(如F4:F7)中建立统计的间距数组(59,69,79,89)(2)选定作为统计结果的数组输出区域G4:G8(3)键入函数“=FREQUENCY(C2:C9，F4:F7)”(4)按下Ctrl+Shift+Enter组合键格式：IF(logical_test,value_if_true,value_if_false)功能：本函数对比较条件式(logical_test)进行测试，如果条件成立，则取第一个值(value_if_true)，否则取第二个值(value_if_false)例4.13已知单元格E3中存放考试分数，现要根据该分数判断学生是否及格=IF(E360,”不及格”,”及格”)例4.14成绩等级与分数的关系：成绩≥80——优良；60≤成绩＜80——中；成绩＜60——不及格。假设成绩存放在单元格D3=IF(D3＞=80,”优良”,IF(D3＞=60,”中”,”不及格”))条件选择函数IF图表是工作表的一种图形表示，它使工作表中数据表示形式更为清晰直观。当工作表中的数据被修改时，Excel会自动更新相关的图表内容图表的基本知识可以生成嵌入式图表和图表工作表数据源中的数据可以按行或者按列分成若干个数据系列依据工作表区域A2:D6中的数据绘制的一个图表4.6图表的制作柱形图图表。有3个数据系列，分别对应于数据源中的3个列：B2:B6(计算机系)，C2:C6(电子系)，D2:D6(信管系)。每个数据系列中都有4个数据点，因而构成4个数据分类:99级、00级、01级和02级图表的基本元素图表标题绘图区图表区域数值轴标题数值轴分类轴数据点数据系列图表标题图例标示图例网络线分类轴标题Excel提供了多种图表类型，有平面图表，也有立体图表。常用的图表类型主要有柱形图、折线图、饼图和XY散点图等(1)柱形图：柱形图是用柱形块表示数据的图表，通常用于反映数据之间的相对差异(2)饼图:饼图用于表示部分在整体中所占的百分比，能显示出部分与整体的关系(3)折线图:折线图是用点以及点与点之间的连成的折线表示数据的图表，它可以描述数值数据的变化趋势(4)XY散点图:散点图中的点一般不连续，每一点代表了两个变量的数值，适用于分析两个变量之间是否相关图表类型4.6.2使用图表向导来制作图表例4.15利用1996年的数据来制作“生产产值表”柱形图先选定区域A1:B5(其中包括列标题)，然后单击“常用”工具栏上的“图表向导”按钮。用户可以在“图表向导”指引下一步一步地实现图表制作4.6.2在图表中增加数据系列图表完成后，用户还可以对它加入同类型的数据系列例4.16将1997年某工厂各车间的生产产值加入到例5.19所生成的图表中4.6.3图表的调整图表完成后，可以改变图表的大小、位置、类型等(1)改变图表的位置(2)改变图表的大小(3)改变图表类型(4)改变图表的图案和字体(5)删除图表4.7数据管理与统计Excel数据库又称数据清单，它按行和列来组织数据(二维表)行————记录；列———