电子表格制作(Excel

电子表格制作(Excel 2002)
一.参考资料
二.基本概念
三.单元格数据的输入和编辑
四.使用图表表达数据信息
五.美化工作表
六.使用公式和函数计算数据
七.管理数据信息
一.参考资料
1)参考网站: http://www.excelhome.net/ http://www.etcell.com/
2)Office Excel自带的帮助文档
3)参考书目:信息化办公――电子表格(人民邮电出版社)
4)Office Excel自带的两个电子表格文件:SAMPLES.XLS和SOLVSAMP.XLS(在Office安装目录下的Samples目录里)
二.基本概念
1)工作簿:一个工作簿就是一个电子表格文件,文件后缀是".xls", 一个工作簿可以由若干个工作表组成.系统默认新建的工作簿会有3个工作表,分别为Sheet1,Sheet2和Sheet3.
2)工作表:由众多的单元格组成
3)单元格:是工作表中的小方格,是工作表的基本单元.单元格的位置由列号和行号组合表示,如"A10"表示位于第A列第10行的单元格.单元格全部内容是指单元格中的数据内容,格式和批注等在内的所有内容.
4)单元格区域:是一组被选中的相邻或分离的单元格.
三.单元格数据的输入和编辑
有两种方法:1)在单元格中输入和修改.2)在编辑栏中输入和修改.
1.单元格中可以使用的数字类型
常规:不包含任何特定的数字格式
数值:有一个可供选择的千位分隔符和小数位数
货币:有可供选择的小数位数和货币符号
会计专用:可使一列数值符号和小数点对齐
日期:具有多种的显示方式
时间:具有多种的时间类型
百分比:以百分数的形式显示,小数数位可选
分数:有9种显示方式
科学计数:以科学计数的形式显示
文本:以文本的形式显示数字.在输入电话号码,邮政编码等数字时,如果希望Excel把输入的数字看作是文本,就应设为该格式.
特殊:如邮政编码等
自定义:用户可以建立自己的数据类型
对[格式]/[单元格]/[数字]命令的说明:该命令的适用对象是数字,如果单元格中输入的不是数字内容,如输入"姓名","用了30分钟",就不需要使用该命令设置格式.
2.选择单元格
选取单元格:单击该单元格.
编辑单元格:双击该单元格.
选取整行(列):单击行号或列标.
选取整个工作表:单击"全选"按钮.
选取连续区域:拖动鼠标;或单击起始单元格后按住Shift单击结束单元格.
选取不连续的区域:按住Ctrl键,然后单击所要选取的单元格即可.
3.
输入文本时换行的方法:ALT键+ENTER键.
要想输入分数,可先输入小数,然后再应用分数格式,也可以在分数前加一个零和空格.
默认情况下,单元格中的文本是右对齐,数字是左对齐.
单元格中过长的文本,会覆盖右边相邻的空白单元格,若相邻的单元格有内容,则过长的文本将被挡住而不能显示,选定该单元格,在编辑栏中可看到该单元格的全部内容.
插入多行的方法:选定与需要插入的新行下方相邻的若干行,选定的行数应与要插入的行数相等,在选定的单元格区域上单击鼠标右键,在弹出的命令中选择"插入".
清除单元格内容的快捷方法:在选定的单元格或单元格区域上单击鼠标右键,在弹出的命令中选择"清除内容".注意,它与"删除"命令的区别.
菜单栏中[编辑]/[清除]命令的用法.
菜单栏中[编辑]/[删除]命令,[插入]/[单元格]命令的用法.
要善于利用鼠标右键,在选定对象上单击鼠标右键,会弹出与选定对象有关的快捷菜单.例如,对着某行号单击右键,对着某列号单击右键,对着某单元格单击右键,对着工作表名单击右键等等.
如何移动单元格 如何复制单元格 如何复制单元格的数据内容
4.数据的自动填充
如何在相邻的7个单元格中输入"星期一","星期二",… ,"星期日"
图1 按预设序列自动填充效果
图2 初值选两个单元格时的自动填充
1)填充相同数据或数据序列的步骤:
a.选中已经输入数据的单元格.
b.在当前单元格黑框的右下角有一个小黑方块,称为填充柄,将鼠标指针移到填充柄上,这时鼠标指针变为黑十字形状.
c.按住鼠标左键并拖动.
2)自定义数据序列的步骤:
a.单击[工具]/[选项]/[自定义序列].
b.在"自定义序列"列表框中选择"新序列"选项.
c.在"输入序列"文本区中输入要自定义的数据序列,如"春","夏","秋","冬",每个数据要单独在一行.
d.单击"添加","确定"按钮.
5.如何用下拉列表输入数据
人事部经常要在Excel表格里录入部门,性别,城市等数据,这些数据重复性很高,比如,在【部门】这一列,可能需要多次输入【财务部】.如下图2所示,用下拉列表输入这些数据会方便很多,还不容易出错.
方法:单击[数据]/[有效性],再按下面右图所示进行设置即可.
6.给单元格加批注.
四.使用图表表达数据信息
1.绘制图形
Excel 2002的"绘图"工具栏提供了几十种常见的基本形状图形,可以根据需要来绘制这些图形.绘图方法与Word中的相似.
2.创建图表
图表就是将电子表格中的数据以图形方式显示出来,并与生成它们的工作表数据相链接.当修改工作表数据时,图表也会被更新.
创建图表的两种方法:
1)利用"图表"工具栏创建,只可创建嵌入式图表,步骤如下:
a.执行[视图]/[工具栏]/[图表]命令,显示"图表"工具栏;
b.选择要包含在图表中的数据单元格区域;
c.在"图表"工具栏的"图表类型"的下拉列表中选择一种图表.
2)利用"图表向导" 创建,既可以创建嵌入式图表,也可以创建图表工作表,步骤如下:
a.选择要包含在图表中的数据单元格区域;
b.单击"常用"工具栏中的[图表向导]按钮.或执行[插入]/[图表]命令,屏幕上将出现图表对话框;
c.按向导提示一步一步完成操作.
注:如果要在图表中显示行列标志,选择数据单元时需包含标志所在单元格.
五.美化工作表
一个完整的工作表通常由数据和格式两部分组成.设置单元格格式使得工作表更美观大方.格式设置包括数据的对齐方式,字体,颜色等.
[格式]菜单中包含了美化工作表的绝大部分命令
工具栏上有关的格式命令
边框的绘制
斜线表头的绘制
套用固定格式
使用格式刷:在"常用"工具栏中有一个"格式刷"按钮,用于复制选择的单元格的格式到其他单元格.
使用条件格式:对于满足一定条件的单元格,可以设置条件格式.设置后,只有当值符合设置的条件时,单元格的值才以设置的格式显示.
例如,在输入学生成绩表时,如果希望突出显示低于60分的成绩,就可以用条件格式一次设定.
六.使用公式和函数计算数据
1.在单元格中使用公式
公式:以"="号开头,它可以是简单的数学式,也可以是包含Excel函数的式子.例如"=100/2+5"
公式中可以包含的元素:
运算符:例如"+"或者"*"号.
单元格引用:例如"C57:I57"
数值或文本:例如,"100"或"新悦国际教育集团".
函数:如SUM或MAX,
括号:即"("和")".用来控制公式中各表达式被处理的优先权.
通常在输入公式后,单元格中反映的不是公式本身,而是由公式计算的结果,公式本身则在编辑栏中的输入框中反映.
公式输入完成后,要记住按"Enter"键确认完成输入.
2.单元格引用
在公式中可以用单元格引用来代替单元格中的实际数据.
1)引用运算符:
引用运算符
示例
引用的单元格
:
(冒号)
C2:I4
表示从C2到I4单元格
,
(逗号)
C2,E4
表示C2和E4单元格
冒号和逗号混合使用
C2:D4,F5:H7
表示从C2到D4单元格,以及从F5到H7单元格
2)引用方式:相对引用,绝对引用,混合引用,引用名字
相对引用是指当把一个含有单元格地址的公式复制到一个新的位置或者用一个公式填入一个区域时,公式中的单元格地址会随着改变.
例如,在J2中输入公式 "=SUM(C2:I2)",然后复制J2单元格到J3单元格,J3单元格就变成了"=SUM(C3:I3)"
绝对引用是指在把公式复制或填入到新位置时,其中的单元格地址保持不变.
设置绝对地址需在行号和列号前加 "$", 例如, "=SUM($C$2: $I$2)"
混合引用是指在一个单元格地址中,既有绝对地址引用又有相对地址引用, 例如, "=SUM(C$2: I2)"
引用名字:就是给单元格区域起一个名字,然后可以用该名字反复使用这个单元格区域.单元格命名的方法有如下三种:
用菜单命令:选定要命名的单元格区域,单击[插入]/[名称]/[定义]
用自动命名:选定文本和单元格区域,单击[插入]/[名称]/[指定]
使用"名称框"
建立了多个名称后,为了便于记录和跟踪错误,可以建立名称表:选定一个空白单元格,单击[插入]/[名称]/[粘贴]
3.在公式中使用函数
Excel的内置函数有几百个,在公式中灵活使用这些函数,可以极大地提高公式解决问题的能力,轻松胜任各种复杂的任务.
如果能记住函数的名称和参数,可以直接在单元格中输入函数,如果不能确定函数的拼写和参数,可以使用函数向导粘贴函数.
从函数列表中选择函数粘贴:
Excel中常用的函数:
(1)SUM:求和函数
(2)AVERAGE:求平均值函数
(3)MAX:求最大值函数
(4)MIN:求最小值函数
(5)ROUND:四舍五入函数
(6)SQRT:求平方根函数
(7)ABS:求绝对值函数
(8)LEN:测试字符串长度函数
(9)INT:取整函数
(10)COUNT:计数函数
(11)MOD:求模函数
(12)COUNTIF:统计某个单元格区域中符合指定条件的单元格数目.
(13)FREQUENCY:以一列垂直数组返回某个区域中数据的频率分布.
(14)IF:根据对指定条件的逻辑判断的真假结果,返回相对应条件触发的计算结果.
(15)RANK:返回某一数值在一列数值中的相对于其他数值的排位.
七.管理数据信息
1.排序
2.分类汇总
应先选定分类列,按分类列排序后再进行分类汇总.
分类汇总完成后,工作表左边将出现分类汇总标志,如下图所示.单击标志可以显示或隐藏明细数据.
3.筛选数据
筛选是查找和处理工作表中数据子集的快捷方法.筛选数据后仅显示满足条件的行.Excel 提供了两种筛选的命令:
自动筛选,包括按选定内容筛选,它适用于简单条件
高级筛选,适用于复杂条件,是在工作表上单独的条件区域中键入筛选条件.条件区域允许根据复杂的条件进行筛选.
与排序不同,筛选并不重排数据.筛选只是暂时隐藏不必显示的行.
"高级筛选"命令:
单列上具有多个条件
如果对于某一列具有两个或多个筛选条件,那么可直接在各行中从上到下依次键入各个条件.例如,下面的条件区域显示"销售人员"列中包含"Davolio",或"Buchanan",或"Suyama"的行.
销售人员
Davolio
Buchanan
Suyama
多列上具有单个条件
若要在两列或多列中查找满足单个条件的数据,请在条件区域的同一行中输入所有条件.例如,下面的条件区域将显示所有在"类型"列中包含"农产品",在"销售人员"列中包含"Davolio"且"销售额"大于 $1,000 的数据行.
类型
销售人员
销售
农产品
Davolio
>1000
某一列或另一列上具有单个条件
若要找到满足一列条件或另一列条件的数据,请在条件区域的不同行中输入条件.例如,下面的条件区域将显示所有在"类型"列中包含"农产品",或在"销售人员"列中包含"Davolio",或销售额大于 $1,000 的行.
类型
销售人员
销售
农产品



Davolio



>1000
练 习
打开 练习.xls 文件,进入"图表"表,将5位同学的成绩以图形方式显示在该表中.
打开 练习.xls 文件,进入 "教师信息"表,进行如下练习:
用下拉列表输入5位教师的职称,职称填写范围:教授,副教授,讲师,组教,无.使用命令:[数据]/[有效性],然后在"允许"下拉列表里选择"序列",在"来源"文本框里输入"教授,副教授,讲师,组教,无",注意每项间用逗号分隔.
按"学院"字段,以"求和"的方式分类汇总"加班天数".分类汇总前应先按"学院"字段进行排序.
打开 练习.xls 文件,进入 "成绩统计"表,进行如下练习:
锁定1-2行的显示位置,使我们在拖动右边的纵向滚动条时,1-2行始终显示在屏幕上.使用命令:[窗口]/[冻结窗格]
将低于60的分数显示为红色.使用命令:[格式]/[条件格式]
计算各位学生的总分.使用函数:SUM(C3:I3)
计算各位学生的名次.使用函数:RANK(J3,$J$3:$J$42).注意什么时候用绝对地址,什么时候用相对地址.
计算语文0-59分数段人数.使用函数:COUNTIF(C3:C42,"=90")
计算数学各个分数段人数.使用"填充柄"拖拉填充
计算英语各个分数段人数.使用函数:FREQUENCY(E3:E42,$O$44:$O$47)
步骤:
①分别选中O44至O47单元格,输入分数段的分隔数值(如下图所示).
②同时选中E43至E47单元格区域,在"编辑栏"中输入公式:=FREQUENCY(E3:E42,$O$44:$O$47),输入完成后,按下"Ctrl+Shift+Enter"组合键进行确认,即可一次性统计出"英语"学科各分数段的学生人数.
注意:
①实际上此处输入的是一个数组公式,数组公式输入完成后,不能按"Enter"键进行确认,而是要按"Ctrl+Shift+Enter"组合键进行确认.确认完成后,在公式两端出现一个数组公式的标志"{}"(该标志不能用键盘直接输入).
②数组公式也支持用"填充柄"拖拉填充:同时选中E43至E47单元格区域,将鼠标移至E47单元格右下角,成细十字线状时,按住左键向右拖拉,就可以统计出其它学科各分数段的学生数.
按名次排序(降序).注意:必须先在43行前插入一空行,再进行排序.
将"0"列隐藏起来,再取消隐藏.使用命令:[格式]/[列]/[隐藏],[格式]/[列]/[取消隐藏]
使工作表只显示出语文和历史成绩都小于60分的学生信息.
使用命令:[数据]/[筛选]/[高级筛选]
广东工业大学第五期教育技术培训 2007.5.27
网络信息与现代教育技术中心 巫桂梅