第三章 Excel应用基础
【应知要点】
1、 能够输入和编辑数据的方法。
2、 能够对数据进行简单处理并插入图表的方法。
3、 打印输出数据表的方法。
【应会要点】
1、 输入和编辑数据的方法。
2、 数据的简单处理及插入图表的方法。
3、 打印输出数据表的方法。
【知识精讲】
§输入和编辑数据的方法
输入数据 在EXCEL中,每个单元格都可以存储包括文字、数字、日期、时间、图形、图像、声音等多种形式的数据。输入的数据保存在当前的单元格中。输入过程中,如果发现有错,可以按Backspace 键或Delete 键删除错误的内容;可以按Enter 键,确定输入的数据。 1.输入文字 选定单元格(单击某单元格,使其变为活动单元格)即可输入文字,一个单元格内最多可放置32000个字符。Excel中,单元格默认的宽度可以放置8 个字符。如果输入的字符超过默认的宽度,则该单元格内容将溢出到右边的单元格中(只是表面上占据右边单元格的位置,实际上内容仍在本单元格中。如果右边单元格再输入数据,则其内容只以默认单元格宽度显示。对于数字组成的文字,如电话号码、邮政编码等,可以在数字前加“‘” ,符号,以区分数字字符或非数字字符。例如电话号码“ " ,可输入为“' ”。 2.输入数字 输入的数字可以是整数、小数(小数点用英文字符的句点替代)、分数、百分数、人民币数值等。若数字超过8 个字符宽,则以科学表示法显示。 3.输入日期和时间 输入日期时,用“一”或“/ ”作分隔符。如果使用12 小时显示方式,输入时可以用“a " 或“p ”代表“AM ”或“PM " ,但要在时间和字母之间加一个空格。 4.输入序列 Excel可以自动输入一个序列,如星期一、星期二、星期三… … ,1 、2 、3 、4 、… … ,一、二、三、四… … 等,也可以自定义特殊的序列。
数据编辑
1.数据的选定
选定一个单元格:用鼠标左键单击;选定一行:用鼠标左键在行号上单击;选定一列:用鼠标左键在列号在单击;选定整张工作表:用鼠标左键单击工作表格区的最左上角的[全选]按钮;选择相邻区域:选择时按住SHIFT键;选择不相邻区域:选择时按住CTRL键。
2.数据的追加 (1)插入单元格 (2)插入行和列
3.数据的移动和复制 (1)用鼠标移动 (2)使用剪贴板进行移动 (3)使用剪贴板或快捷键复制: CTRL+C表示对象复制 CTRL+V表示对象粘贴 CTRL+X表示对象剪切 (4)用鼠标右键来移动和复制
4.数据的删除与恢复
§ 数据的简单处理
数据的处理
1.数据计算
(1)自动计算:用工具栏上的自动求和 按钮;
(2)函数与公式计算:需掌握的函数有求和函数Sum()和Sumif()、求平均函数Average()、求最大值Max()、求最小值Min()、统计函数COUNT()、条件统计函数COUNTIF()、条件选择函数IF()。
2.数据排序
排序可按升序或按降序排序。操作方法:可以按工具栏上的升序 或降序 按钮进行排序;或可单击[数据]菜单中的[排序]命令。
3.数据格式的设置编排
格式设置包括字体、字型、字号的设置,对齐方式的设置、页面设置,数字格式的设置,单元格格式的设置等。
§图表
插入图表
1.创建图表的方法:
(1)选择要包含在图表中的单元格数据;
(2)单击[常用]工具栏中的[图表向导]按钮,屏幕上将出现如下对话 框。对话框中列出了EXCEL中可以建立的所有图表类型,可以任意选择一个,然后单击[下一步]。见图-1。
图-1
(3)此时屏幕上出现[图表数据源]对话框,数据区域中显示了要包含在图表中的所有数据单元格的所在范围,若要更改,则可以重新设定,设置后单击[下一步]。见图-2
(4)执行上一步操作后,将出现[图表选项]对话框,可以输入图表的标题及其他内容。设置后单击[下一步]。见图-3
(5)此时,屏幕上出现[图表位置对话框,选择图表要放的位置后单击[完成]按钮。见图-4
图-4
打印输出数据表的方法
在EXCEL中要将编辑好的表格打印出来,必须先对页面进行设置,包括左右边距,上下边距及打印区域等;打印时,只需单击[文件]中的[打印]命令即可。
【典型例题】
【例1】在Excel97 的工作表中,列标记和行标记是( ) 。A 字母;数字 B 数字;字母C 汉字;数字 D 数字;汉字
【分析】在EXCEL工作表中,行标记是横向用数字表示的,列标记是垂直方向用字母表示的。
【答案】:A
【例2】 Excel 97 的基本文档称为( ) 。A 工作表 B 工作簿C 单元格区域 D 电子表格
【分析】在EXCEL中文档称为工作簿,而工作簿则是由工作表构成的。
【答案】:B
【例3】按照Excel 对单元格命名规则,下列正确的描述为( ) A、A6 : ES B、 A23 C、D D、IF
【答案】:B
【例4】在EXCEL中,单元格的引用(地址) 、 、
混合引用等三种。
【答案】:相对引用、绝对引用
【分析】:在EXCEL中相对引用是指将一个含有单元格地址的公式拷
贝到新的位置或在某公式中填入一个单元格区域时,公式中的单
元格地址会随之改变。绝对引用是指将公式拷贝到一个新的位置
或填入一个单元格区域时,其中的单元格地址保持不变。
【例5】在记录单的右上角显示“3/30”,其意义是 。
A、 当前记录单仅允许30用户访问
B、 您是访问当前记录单的第3个用户
C、 当前记录是第3号记录
D、 当前记录是第30号记录
【答案】:C
【分析】:记录单上的“3/30”表示是该表格中共有30条记录,现在光标所处位置是第三条记录。
【例6】在Excel中,如果单元格中有公式,则公式通常显示在( )。 A 、名字框 B、 标题栏 C 、工具栏 D、公式栏
【答案】:D
【例7】在EXCEL中,一个数据清单由三个部分组成: 。
A、公式、记录和数据库 B、区域、记录和字段
C、工作表、数据和工作簿 D、数据、公式和函数
【答案】:B
【例8】在EXCEL中,新建一个工作簿时,缺省包含 个工作表。
A、3 B、16 C、255 D、以上都不是
【分析】工作簿是由工作表组成的,一个工作簿中默认情况下有3个工作表组成,最多包含255个工作表。
【答案】A
【例9】在EXCEL中,下面关于分类汇总的叙述错误的是()。
A、分类汇总前必须按关键字段排序数据库
B、分类汇总的关键字段只能是一个字段
C、分类汇总可以被删除,但删除汇总后排序操作不能撤消
D、汇总方式只能是求和
【答案】D
【例10】操作题:
A | B | C | D | E | F | |
1 | 姓名 | 职称 | 基本工资 | 奖金 | 补贴 | 工资总额 |
2 | 金迈 | 315 | 253 | 100 | ||
3 | 王景灏 | 285 | 230 | 100 | ||
4 | 刘希每 | 490 | 300 | 200 | ||
5 | 李若一 | 300 | 100 | 50 | ||
6 | 赵永强 | 580 | 320 | 300 | ||
7 |
1) 写出求“工资总额”的公式。
2) 在D7单元格中统计出“奖金”,大于250的人数。
3)如果“基本工资”大于400,在“职称”处填上“高工”;如果“基 本工资”大于350且小于等于400,则在“职称”处填上“工程 师”;若小于等于350的,则在“职称”处填上“技术员”。
【答案】:1)【分析】:工资总额=基本工资+奖金+补贴F2=C2+D2+E2或用自动求和公式F2=SUM(C2:E2)其余单元格用填充柄填充即可。
2)【分析】:这个题目要求统计人数而且是有条件的,因此不能用COUNT()函数,要用COUNTIF(),统计的范围是D2—D6,条件是大于250。D7=COUNTIF((D2:D6)>250)
3)【分析】:这个题目难度比较大,首先确定是用条件选择函数,然后再根据题,IF()函数格式中只能用一个条件来判断真假,而题目中有三个条件,因此必须用嵌套的方式来解决。B2=IF(C2>400;“高工”,IF(C2>350,,“工程师”,“技术员”)
【能力训练】
一、 单项选择题:
1、在EXCEL2000中,汇总明细数据可以使用 方法。
A、1 B、2 C、3 D、4
2、在EXCEL2000中,新建一个工作簿时,缺省包含 个工作表。
A、3 B、16 C、255 D、以上都不是
3、如果要查找数据清单中的内容,可以通过筛选功能, 包含指定
内容的数据行。
A、部分隐藏 B、只隐藏 C、只显示 D、部分显示
4、在EXCEL2000中,时间和日期可以 ,并可以包含到其他运
算当中。如果要在公式中使用时期或时间,请用带引号的文本形式输
入日期或时间值。
A、相减 B、相加、相减 C、相加 D、相乘、相加
5、在EXCEL2000中,运算符有四类,算术运算符、比较运算符、文本运
算符和引用运算符,其中“:”属于 。
A、算术运算符 B、比较运算符
C、文本运算符 D、引用运算符
6、在EXCEL2000中,当使用错误的参数或运算对象类型时,或者当自
动更正公式功能不能更正公式时,将产生错误值 。
A、##### B、# div/0 C、#name D、#VALUE!
7、在EXCEL2000中,函数可以作为其它函数的 ,称为嵌套参
数。当函数嵌套时,它必须返回与当前参数使用的数值类型相同的
数值。
A、变量 B、参数 C、公式 D、表达式
8、在EXCEL工作画面上,“状态栏”在是屏幕的 。
A、上面 B、下面 C、左面 D、右面
9、在EXCEL2000中,删除当前工作表的某列的正确操作步骤是 。
A、 选定该列:执行“编辑”菜单中的“删除”命令
B、 选定该列:执行“编辑”菜单中的“剪切”命令
C、 选定该列:执行“编辑”菜单中的“清除”命令
D、 选定该列:执行键盘中的“DELETE”命令
10、在EXCEL2000中,删除当前工作表的某列的正确操作步骤是 。
A、选定该行:执行“编辑”菜单中的“删除”命令
B、选定该行:执行“编辑”菜单中的“剪切”命令
C、选定该行:执行“编辑”菜单中的“清除”命令
D、选定该行:执行键盘中的“DELETE”命令
11、在单元格输入负数时,两种可使用的表示负数的方法是 。
A、 在负数前加一个减号或用圆
B、 斜杠(/)或反斜杠(\)
C、 斜杠或连接符(-)
D、 反斜杠或连接符(-)
12、为了区别“数字”与“数字字任串”,EXCEL要求输入项前添加
号来区别。
A、“ B、‘ C、# D、@
13、在单元格输入日期时,两种可使用的年、月、日间隔符是 。
A、圆点(.)或竖线(|) B、斜杠(/)或反斜杠(\)
C、斜杠(/)或连接符 (-) D、反斜杠(\)或连接符(-)
14、任何输入到单元格内的字符集,只要不被系统解释成数字、公式、
日期、时间、逻辑值,则EXCEL将其视为 。
A、表格 B、图表 C、文字 D、地址
15、单元格中的数字,可以在“格式”菜单的“单元格”选项中为其设
定显示格式,能将其显示成的格式是 。
A、删除线 B、上标 C、数组 D、文本
16、某公式中引用一组单元格,它们是(C3:D7,A2,F1),该公式引
用的单元格总数为 。
A、4 B、8 C、12 D、16
17、在单元格中输入公式时,编辑栏上的“√”按钮的表示 。
A、取消 B、确认 C、函数向导 D、拼写检查
18、在建立数据清单时需要命名字段。字段名必须遵循的规则之一是:
字段名只能包含 。
A、文字、数字 B、文字、文字公式
C、数字、数值公式 D、文字公式、逻辑值
19、在单元格中输入公式时,输入的第一个符号是 。
A、= B、+ C、- D、$
20、EXCEL中有多个常用的简单函数,其中函数AVERAGE(范围)的功能
是 。
A、求范围内所有数字的平均值 B、求范围内数据的个数
C、求范围内所有数字的和 D、返回函数中的最大值
21、EXCEL中有多个常用的简单函数,其中函数COUNTIF(范围)的功能
是 。
A、求范围内所有数字的平均值 B、求范围内符合条件数据的个数
C、求范围内所有数字的和 D、返回函数中的最大值
22、EXCEL中提供的工作表都以“sheet1…..”来命名,重新命名工作表的
正确操作是 。
A、 双击选中的工作表标签,在“重新命名工作表”对话框输入新名,
按“确定”按钮
B、 单击选中的工作表标签,在“重新命名工作表”对话框输入新名,
按“确定”按钮
C、 选中“插入”菜单的“名称”的“定义”,在其对话框输入名称,
按“确定”按钮
D、 选中“插入”菜单的“名称”的“指定”,在其对话框输入名称,
按“确定”按钮
一、 填空题:
1、 EXCEL中输入数据时,如果输入的数据具有某种内在规律,则可以利用它的 功能。
2、EXCEL产生的文件是一种三维电子表格,该文件又称 ,它由若
干个 构成。
3、EXCEL中,假定存在一个数据库工作表,内含系科、奖学金、成绩等项
目,现要求出各系科发放的奖学金总和,则应先对系科进行 ,
然后执行 菜单的“分类汇总”命令。
4、在EXCEL中,公式=SUM(Sheet1:Sheet5$E$6)表示 。
5、在EXCEL中,若只需打印工作表的一部分数据时,应先 。
6、如果B2单元格为“计算机”,B3单元格中为“网络”,要在F4单元格中显
示“计算机网络”,则应在该单元格中键入公式 。
7、在EXCEL中,单元格A1其中A表示 ,1表示 ;
单元格B9是 引用,$B$9 是引用,$B9是引
用,单元格区域A1:C5共有 个单元格,B3,B4,B9共有 个
单元格。
8、EXCEL2000中文版的窗口由标题栏、 、工具栏、
、 、 、状态栏、
及滚动条。
9、在EXCEL中最小的单位是 ,在它里面可以输入 、
或 等信息;工作表是由众多的 排列在
一起构成的,一个工作簿内最多可以有 个工作表。
10、在EXCEL中文件的扩展名为 ;在打开文件时,在[打开]对话框中
配合使用 或 键可以选中多个不连续或连续的工作簿文
件,并打开它们。
二、 操作题:
(一)根据要求,完成下列操作:
1.打开Excel,输入如下的表格:
姓名 | 职称 | 基本工资 | 奖金 | 补贴 | 工资总额 |
金成安 | 工程师 | 315 | 253 | 100 | |
王景灏 | 工程师 | 285 | 230 | 100 | |
刘希敏 | 高工 | 490 | 300 | 200 | |
李若云 | 临时工 | 200 | 100 | 0 | |
陈立新 | 高工 | 580 | 320 | 300 | |
赵永强 | 工程师 | 390 | 240 | 150 | |
林芳萍 | 高工 | 500 | 258 | 200 | |
吴道临 | 工程师 | 300 | 230 | 100 | |
杨高升 | 临时工 | 230 | 100 | 0 | |
郑文杰 | 高工 | 450 | 280 | 200 | |
徐守敬 | 临时工 | 200 | 100 | 0 | |
何建华 | 技术员 | 280 | 220 | 80 |
2.计算出表中的“工资总额”;
3.给表格添加一个标题:一月份工资表,要求设置为蓝色、黑体、加下划
线;
4.在每个姓名前填充序列号,1、2、3、…;
5.将所有人的工资从高到低进行的排序;
6.将表格的所有内容复制到第二张工作表中;
7.给第一张工作表命名为表一,第二张命名为表二;
8.将第二张工作表中职称为“临时工”的所有行删除掉;
9.将文件保存到D盘自己文件夹中(没有自己建),取名为工作簿1。
(二)创建如下表格,并根据要求完成下列步骤:
1、在EXCEL中创建如下表格:
合同号 | 销售员 | 产品代号 | 数量 | 价格 | 金额 |
A-4574 | Smith | AB-123 | 100 | ||
B-3783 | Jones | CD-456 | 50 | ||
A-3837 | Bobcat | EF-789 | 200 | ||
B-5478 | Andrew | AB-123 | 75 | ||
C-3473 | Jones | AB-123 | 45 | ||
A-4783 | Smith | GH-012 | 100 | ||
C-9283 | Andrew | CD-456 | 400 | ||
A-2740 | Bobcat | AB-123 | 150 | ||
A-1736 | Smith | EF-789 | 300 | ||
数量总和 | |||||
价格平均值 |
2、 第一行前插入一行,并填入“产品销售情况一览表”,并跨列居中于表格中央,设为宋体,24号,粗体
3、计算出金额,数量总和,价格平均值
4、对价格列应用贷币样式,数量列设置成一位小数,金额列加上千分位
4、 将销售员Smith的所有记录复制到sheet2中
5、 为“合同号”以A打头的所有记录设置格式为:淡蓝色底纹
6、 创建一柱形图,数据区为数量列与金额,最大刻度为10007、在“产品代号” 、列中代号为“AB-123”上的批注删除
(三)根据表格,完成下列操作:
1997年销售情况 | |||||
分公司名 | 一季度 | 二季度 | 三季度 | 四季度 | 总额 |
广州公司 | 1200000 | 1100000 | 9800000 | 1000000 | |
上海公司 | 800000 | 950000 | 600000 | 700000 | |
北京公司 | 500000 | 650000 | 300000 | 550000 | |
合计 |
1、 用函数计算各公司1997年的销售“总额”。
2、 用公式计算该集团每季度的销售总额及1997年全年的销售总额。
3、 数值显示要求:采用千位分割符,在数值前加上人民币符号。
4、 主标题“1997年销售情况”,设置在表格的中间,字体采用黑体16号。
5、 给所有的“总额”数值加上“红色”底纹。
6、 给表格加上边框线,其中外框线用“双线”,内框线用“单线”。
7、 为该表格创建一个图表,图表类型为柱形图,图表标题为:1997年销售情况,图表放入Sheet3中。
(四)根据表格,完成下列操作:
姓名 | 语文 | 数学 | 英语 | 平均分 | 升留级 | 是否二门以上不及格 | |
张大林 | 55 | 45 | 52 | ||||
刘芳 | 77 | 76 | 78 | ||||
刘添 | 60 | 56 | 40 | ||||
罗一民 | 92 | 73 | 60 | ||||
李莉 | 44 | 95 | 67 | ||||
王守道 | 80 | 62 | 76 | ||||
杨洋 | 67 | 74 | 59 | ||||
欧阳哲 | 80 | 76 | 67 | ||||
李小红 | 78 | 88 | 98 | ||||
余娜 | 90 | 44 | 67 |
1、 求出平均分,小数部分四舍五入
2、 建立自动筛选,筛选出“英语”为67分的同学
3、 筛选出平均分最低的三名同学
4、 选出语文90分以上(含90分)或者不及格的同学
5、 选出英语67分的同学,在此基础上再选出数学不及格的同学
6、 取消自动筛选
7、 用高级筛选选出三门课都不及格的同学
8、 用高级筛选选出至少一门课在90及90以上的同学
9、 用高级筛选选出语文和数学都不及格的同学
10、 在“升留级”列中 ,如果平均分及格的填“升级”,不及格的填“留级”
11、 在“是否两门以上不及格”一列中填“是”或“否”
帮助说明:
第10小题:if(f16<60,"留级","升级")
第11小题:if(countif(c16:e16,"<=60")>=2,"是","否")
(五)根据表格,完成下列操作:
职员登记表 | ||||||
部门 | 员工编号 | 性别 | 年龄 | 籍贯 | 工龄 | 工资 |
开发部 | K12 | 男 | 30 | 浙江 | 5 | 2000 |
测试部 | C24 | 男 | 32 | 江苏 | 6 | 1600 |
文档部 | W24 | 女 | 24 | 陕西 | 2 | 1200 |
市场部 | S21 | 男 | 26 | 江西 | 4 | 1800 |
市场部 | S20 | 女 | 25 | 河北 | 5 | 1900 |
开发部 | K01 | 女 | 26 | 山东 | 3 | 1400 |
文档部 | W08 | 男 | 24 | 江西 | 5 | 1200 |
测试部 | C04 | 男 | 22 | 湖南 | 6 | 1500 |
测试部 | D12 | 女 | 24 | 广东 | 4 | 1300 |
市场部 | S14 | 男 | 26 | 上海 | 2 | 1400 |
市场部 | S22 | 女 | 35 | 南京 | 3 | 1500 |
测试部 | C16 | 女 | 24 | 北京 | 6 | 1300 |
文档部 | W04 | 男 | 21 | 山西 | 5 | 1532 |
开发部 | K02 | 男 | 35 | 浙江 | 2 | 1450 |
测试部 | C29 | 女 | 24 | 辽宁 | 3 | 1700 |
(1) 在第一列之前插入一空列,并填入“序号”及阿拉伯数字。
(2) 删除表格中序号为9的一行的内容。
(3) 标题文字的样式:隶书、20、合并居中。
(4) 设置第二行文本的格式,楷体粗体
(5) 更改表格对齐方式:“工资”一列的数据居右,表头和其余各列居中。
(6) 对工资应用货币样式。
(7) 为“部门”一列中所有“市场部”添加浅灰色底纹。
(8) 为“员工编号”一列中的K12单元格加上批注“优秀员工”
(9) 计算平均年龄和工资总额,结果放在相应列的下面。