第四章 电子表格软件的使用.doc

第四章 电子表格软件的使用本章学习目标：l 掌握工作簿、工作表及单元格的基本操作l 掌握数学公式和常用函数的使用l 掌握Excell的数据表操作l 掌握电子表格的图表操作l 掌握电子表格的高级筛选及数据透视l 了解工作表的打印操作一. 基本知识介绍1、启动与退出. 2、窗口界面. 3、工作簿与工作表：（1）EXCELL的一个工作簿就是Excell的文件，工作簿名就是文件名，Excell允许打开多个工作簿，但任一时刻只允许对一个工作簿操作当前工作簿（2）工作表：是显示在工作簿窗口中的表格，一个工作表可以由65536行和256列组成，行号：165536 列号：A、B、C、IV。默认一个工作簿有三个表，可添加，最多可以有255个不同类型的工作表。、单元格与活动单元格：活动单元格的地址显示在编辑栏的“名称框”数据显示在编辑栏的“编辑框”任何时刻只有一个活动单元格。、选择单元格: 单击单元格;较小单元格区域:左键拖动;较大单元格:先单击左上角单元格,再按shift 单击右下角单元格;选择不相邻的区域:ctrl;全选:行号和列号相叉处的全选按钮、单元格区域与地址：(1)单元格地址列号在前，行号在后，如A1、B3、C4(2)单元区域地址：左上角单元格地址：右下角单元格地址，如：B3:E7（3）绝对地址：如$E$5二. 编辑工作簿的基本知识.插入行,列,单元格：选择插入位置的行或列“插入”菜单“行”或“列”.删除行,列,单元格.清除单元格（与删除单元格的区别，Delete）(4)查找与替换(5).工作表的命令:选择、重命名、插入,删除三、格式化单元工作表主要是设置数据的字符格式，对齐格式和显示格式，设置表格的行高、列宽和表格线。可在输入数据前，也可在输入数据后格式化工作表。1、格式化数据：（字体、字形、颜色）A.设置字符选择设置区域“格式”单元格字体（字号单位是磅，系统默认是12磅）B.设置数字选择区域“格式”单元格数字（分类列表框）C、设置数据的对齐格式 水平对齐：系统默认文本数据居左对齐，数字数据居右对齐 对齐 垂直对齐：系统默认是靠下对齐注意：“格式”工具栏左对齐、居中、右对齐，合并及居中 菜单可以设置水平和垂直对齐及数据的倾斜角度： 选择单元格或区域“格式”单元格对齐2、格式化表格（1）设置行高和列宽 鼠标拖动（列号、行号之间）单行 先选多行多列 精确设置选择行右列格式行行高输入新的行高值确定 说明：最适合的行高可设置行高为刚好容纳该行最高的字符 最适合的列宽可设置列宽为刚好容纳该列最宽的字符 标准列宽默认值8.38 （2）设置表格线（格式工具栏、菜单栏）四、数据自动填充1. 数值:学号ctrl拖动学号递增,直接拖动一样 文本:学号(前面加小撇)拖动学号递增(按ctrl学号一样)2.自定义序列工具选项自定义标签新序列输入序列:ONE.TWO,THREC,FOUR,FIVE,SIX.每输入一个序列成员就按一次回车,输入完毕后单击”添加”3.产生一个序列首先在单元格中输入初始值并回车,然后单击该单元格编辑菜单填充序列序列产生在(行或列)类型等差等数列或等到比数列4.输入有效数据: 记忆输入功能:如语言文字(输过后)再输”语”回车就出现”语言文字”.选取要定义有效数据的单元格.”数据”有效数据”许可”数据输入上下限 学习成绩 0-100分 输入信息 请在这里选择举例演示 “错误警告”工具审核显示审核工具栏图视无效数据5.选择性粘贴 (运算 行列转置)例: 在工作表中英语成绩*0.9.选任意单元格填入0.9,复制0.9,再选定要先算的区域编辑选择性粘贴乘.选择区域复制空白格编辑选择性粘贴转置6. 制表日期制表日期的单元格粘贴函数Fx日期与函数now确定(当前日期输入)工具选项网格(去掉)便于看五、作业演示1. 材料表.调整列宽,行高: 选中行或列右键具体行高和列宽; 鼠标拖动.插入行.设置单元格选中单元格格式对齐方式垂直(靠上)自动换行(举例: 要位置上多输入一些文字).序号,单位,数量.居中.向下移(两种方法) 制作日期: 制表人 生产编号:MAT7228 产品名称其中:制作日期粘贴函数日期与函数now确定,当前日期输入.合并单元格填上表后(在编辑栏,也可在A1单元格)设置字体,字号,边框底纹,预览,居中,横向(最后合并)材料表:格式(字体,字号; 边框粗线)仔细操作: 外线 内线注意: .在表头插入四行(选中四行,插入).先选中单元格再先格式边框 (粗线外边框; 细线内线).工具选项网格(去掉)便于看.序号/数量: 分制选中居中.打印预览 页面设置(横向)2、材料表设置副本.单击工作表标签:复制或移动工作表, 移到最后建立副本.”材料表”改成”拨料单”.”位置”改成”总数量”.输入 1280(选一个单元格).计算总数量(输入公式.拖动).备注:1280*数量.设置区域: “数量”列居中 “=”左侧输入”每台数量”（回车） 最后一列: 1280*每台数量即可3.证券行情表设置代码格式为4个0代号格式单元格数字自定义0000条件格式选中格式条件格式大于0格式字体:红色; 添加小于 0格式填充色:绿色(图案)打印标题行视图页眉.页脚打印图预览自动套用格式 选中格式自动套用格式.裴波那契数列(免子数列)1 1 2 3 5 8 13 21 2 2 4 6 10 16 26 32 公式: a =a + a (n3)6. 两位整数的加法题(随机出题,自动判对错,自动打分,列隐藏)A. 随机出题:.在其它任意列单击选中粘贴函数全部Rand确定接着输入*100括号括起来前面输入int(取整)即int (Rand( )*100) 确定.选中此列数字复制到第一列第一单元格编辑选择性粘贴数值确定.第二列输入”+”拖动.用同的的方法输入第三列的加数B. 自动判对错“对错列选中第一个单元格Fx用鼠标点A1,输入”+”,点C, 输入”=”,点E,这在Logical test内显示(表达式)value tvue输入”V”(右键点键盘)数字字符号”V”确定valse false:输入”X”“对错列”选中格式列隐藏C. 选中”对错列”最下面单元格粘贴函数count ifRange要计算的区域”对错列”crteria标准”V”确定(计算出对的题数)D. 得分: 输入公式: “V”个数/20 * 100=107. 销售表 .自动求和: 填公式. 拖动 求和 .格式单元格数字G(货币)或工具按钮8. 工资表 .书报费均为100(拖动填上) .工会费=基本工资 * 0.01 .所得税=(基本工资+奖金+津贴+书报费-2000)*5% .实发工资=基本工资+奖金+津贴+书报费公基金保险水电费-房租-所得税-工会费 .所有数据以货币(G)形式出现: 数字单元格G形式.%. ;小数位 .窗格冻结 .分页符(预览) 插入 移动 删除 .工作表(shcet右键重命名.移动.复制)9. 成绩表 用Rand 函数填各成绩, 按百分比计算综合成绩,不及格的用红色背景;10. 发货单 .纸张: A4 .视图工具栏绘图 .在shect2k 仿制此表 .选中自选图形新月格式(图形不随文字的大小改变)自选图形固定(大小.位置) .在下行的一列选中到A4纸边缘合并单元格设置边框(上粗下细 蓝色)拉近 .输入文本框设置格式边框(无线条颜色)”发货单” .两个自选图形(复制)输入内容 “自选图形”:画上”_”如: 名称_ 地址_ 电话_ 邮箱_.左上角选中选择对象右键组合.下行输入表头内容:B.C列合并格式列.拖动 :产品名称加上表格线 设置单元格格式注意: 行情表: 点右侧最上边 最上面滑块双箭头移动拖动到表头下面 ;最后面的滑块(下面)第一列后面窗口冻结窗口六、图表的应用:.电子工业产值变动概览表1.选中数据区域(要放入图表的区域)年份和电子工业产值2.点图表向导按提示操作3.对图表拖动可以改变大小,拖动绘图区也可改变大小,对图表的编辑改变字大小4.移动图例,改变图例大小,改变数值和字号,分类轴字号5.设置图表区格式(右键)背景过滤效果.阴影.圆角.颜色.图表区绘图区格式:背景颜色.过滤效果6.柱上双击(右键)数据标志显示值对齐方式拉动箭头数值在柱子里双击数值字体右击柱添加趋势线 多项式 2阶趋势线右击趋势线格式显示格式R2值. 公式.产品销售表 (添加数据)1. 6月1日-6月15日(M1.M2产品)上半月销售情况(柱形图)2. 标题:产品销售情况3. 添加系列: 拖动:选中绘图区-点表中”+”拖动(点柱图) 点图表数据区域重新拖动(ctrl.M1.M2.M3) 选中M4:复制.点边缘.拖入图表(鼠标变箭头时)M4加入等系列注: 图表和数据是相连的,修改其中一个与一个同时反映变化例: 6月13日 420220 点13日的柱,点柱上边的帽,用鼠标往下拖.变小.其中表也变化.修改其中数字.柱高度变化学生成绩单 (鼠标在表内点一下.通过数据下的记录单操作完成)1. 数据记录单新建2. 修改数据. 删除数据 同上3. 查找(语文成绩90分的同学) 同上操作: 条件在语文后输入90然,后点上一条或下一条4. 排序:. 语文成绩升序工具栏简单排序(点标题”语文”列下的任意单元格)简单排序. 选中数据区域(整个表)数据排序主关键字 总分 次关键字 语文5. 筛选 (自动):.语文成绩排序后前10个(最大或最小).自定义 语文.数学 80.去消筛选 (把”去掉)6. 高级筛选 在表外复制粘贴”语文”数学”字段”,输入条件80选中所有数据区域条件区域如下注: A. 语文 数学 物理 并排,三者是”与”的关系 80 80 80 同一行,三科均大于等于80分B.语文 数学 物理80 只有表头在同一行,其它不在同一行 80 是”或”的关系,只要一科大于等于80分即可 80 可选择把结果放在工作表的下方某一区域(其它位置)7. 分类求和 按班级分析那个班 语文成绩最好:.先对分类字段排序(班级) 分类字段:班级 .数据分类汇总 汇总方式:平均 选定汇总项:语文. 商品销售情况表数据透视表 选中整个数据区域数据数据透视表下一步下一步新建工作表/现有工作表完成将数据透视表上的”售货员”.”商品名称”拖入行字段.列字段. 将”数据”拖入数据区域. “余额”也可拖入.请 请将列字段拖至此处 (商品名称)将行字 售拖 货 请将数据项拖至此处 (数量. 余额)至 员此处 (或先选定某一位置数据数据透视表)七. 补充. 填充系列1. 选定一列先:输入: 优秀 . 良好. 合格. 不合格2. 选中成绩评定列数据有效性允许序列 来源:优秀.良好.合格,不合格输入信息:请在这里选择. 数据地图如果Exleu没有完全安装,可添加: 安装删除或添加microsofe地图要地运行即可1.选中数据区域2.”插入”对象数据地图3.把不想用的标志格式拖出去 拖入需要的标志(柱形图)4.拖入数据系列: 一月. 二月. 三月八、嵌套函数（一）、以一年级第一学期成绩为例，用函数计算男生英语课程平均分。操作步骤：在单元格C14输入公式：SUMIF（C2:C10,”男”,D2:D10）/COUNTIF(C2:C10,”男”)按回车键后，在单元格C14中得到男生的英语平均分。(二)用下表数据建立工作表，将工作表所在工作簿以文件名产量统计表.XLS保存。98年产量统计表月份一车间二车间116132151531316416135111461216711138111591316101117111218121118（1） 在当前工作表中建立曲线图，横坐标为月份，纵坐标为产量。（2） 将图形移到表格的下方。（3） 分别设置图例格式、曲线图的标题、坐标格式。清除图中的网格线、背景颜色和边框。（4） 设置产量曲线的线型和颜色，其中一车间曲线用蓝色，数据标记用方块、前景用白色，背景用蓝色，大小为4磅；二车间的曲线用绿色，数据标记用三角形，前景用白色，背景用绿色，大小为4磅。（三）工作表中数据如图所示，对工作表作如下操作：（1）分别在单元格F2和G2中填写计算总分和平均分的公式，用公式复制的方法分别求出各学生的总分和平均分。（2）根据平均分用IF函数求出每个学生的等级。等级的标准：平均分60分以下为D；平均分60分以上（含60分）、75分以下为C；平均分75分以上（含75分）、90分以下为B；平均分90分以上为A。操作步骤为：在H2单元格内输入公式：IF(H2=90,”A”,IF(H2=75,”B”,IF(H2=60,”C”,”D”),然后按回车键得出等级，再复制公式就可以了。（3）筛选出姓王、并且性别为女的同学。（4）筛选出平均分在75分以上，或性别为女的同学。（5）筛选出1980-08-08后出生的女同学。（6）按性别对平均分进行分类汇总。（四）、学校职工情况表1、建立学校职工情况表，并用复制公式的方法计算各职工的实发工资。计算公式：实发工资基本工资-水电费2、打开此表，进行格式设置：（1）设置纸张大小为B5,方向为纵向，页边距为2厘米。（2）将“基本工资“和”水电费“的数据设置为保留1位小数。（3）设置标题的字号为18磅，字体为黑体，颜色为深绿，对齐选合并单元格，垂直、水平均为居中。（4）设置各列的格式，其中：“编号”列格式：14磅和、斜宋体，黑色，底纹为湖蓝加6.25灰色；“姓名”列格式：14磅宋体，海绿色；“性别”列格式：12磅幼圆，蓝色；“职称”列格式：12磅宋体，梅绝色；（5） 设置各列的宽度，要求：A、B列为5,C、D两列为6,E、F、G列为11.（6） 设置表头文字的格式为：16磅常规楷体，垂直与水平均居中，行高为27,底纹为6.25灰色。颜色与所在列数据相同。3、对第1题建立的工作表进行以下操作：（1）在表中增加“补贴”、“应发工资”和“科室”3列。（2）用函数计算每个职工的补贴，计算方法：助讲的补贴为50元，讲师的补贴为70元，副教授的补贴为90元，教授的补贴为110元。操作步骤：输入公式：=IF(D2=助讲,50,IF(D2=讲师,70,IF(D2=副教授,90,IF(D2=教授,110,IF(D2=,)，然后点回车键，再复制公式就可以了。（3）用函数统计基本工资、水电费、补贴和应发工资的合计与平均。（4）用函数求出水电费的最高值和最低值。（5）用函数从编号中获得每个职工的科室，计算方法：编号中的第1个字母表示科室，A-基础室，B-计算机室，C-电子室。（6）用函数统计教授与副教授两种职称的总人数。操作步骤：输入公式：=IF(LEFT(A2,1)=a,基础室,IF(LEFT(A2,1)=b,计算机室,电子