Office高级应用案例第4章Excel 2010综合案例
第4章Excel 2010应用
技巧点滴
若要得到2、4、6……的填充效果，则应先输入“2”、“4”，再选中 2、4所在的单元格，用鼠标指针指向4单元格右下角的填充柄，拖动即可。 若要得到递增数据1、2、3……的填充效果，可先输入“1”，用鼠标 指针指向1单元格右下角的填充柄，并同时按住【Ctrl】键不放向下或向右 拖动鼠标至要填充的最后一个单元格即可。若要得到递减数据，则可通过 向上或向左拖动鼠标来实现。
4.1.3 项目实施 新生入校时，辅导员首先要查阅和建立所带班级 的学生档案，以便快速地掌握学生的基本情况，提高 管理学生的效率。对以表格形式存在的数据采用Excel 软件来进行存储和管理，是非常合适的。首先，在 Excel中无须手工绘制表格，行、列的增减非常容易； 其次，对于具有规律性的数据，Excel的自动填充功能 能减少重复输入数据的烦恼；再次，Excel强大的数据 管理功能可方便用户对档案数据的查询和管理；最后， Excel易学易用，不太熟悉计算机的人也能很快上手操 作。 下面介绍如何利用Excel软件创建如图4-1-15所示的 学生电子档案表。
技巧点滴
如果要输入电话号码或邮政编码等特殊文本，只需在数字前加上一个 英文方式下的“’”单引号即可。 如果要在同一单元格中显示多行文本，则单击“开始”功能区的“对 齐方式”组中的按钮 即可。
如果要在单元格中输入“硬回车”，则按【Alt+Enter】组合键。
4.1.2 项目知识准备
（3）日期/时间输入 Excel内置了一些日期时间的格式，当输入的数据与这些格式 相匹配时，Excel将识别它们。Excel常见的日期时间格式为“年月-日”、“日-月-年”、“月-日”，年、月、日之间用斜杠（/） 或减号（-）分隔。日期和时间在单元格中默认向右对齐。
4.1.2 项目知识准备
Office高级应用：Excel2010综合应用习题与答案
一、单选题1、在Excel中，函数RANK(A2, $A$2:$A$18, 0)的功能是（）。
A、返回单元格A2中的数据值在区域$A$2:$A$18中的降序排名B、返回单元格A2中的数据值是否是区域$A$2:$A$18中的最小值C、返回单元格A2中的数据值在区域$A$2:$A$18中的升序排名D、返回单元格A2中的数据值是否是区域$A$2:$A$18中的最大值正确答案：A2、如果在单元格A1中输入了1，则区域E1:E5中的公式{=if($A$1=1, $B$1:$B$5, $C$1:$C$5)}的计算结果为（）。
A、FalseB、区域$C$1:$C$5的值C、区域$B$1:$B$5的值D、True正确答案：C3、函数OFFSET(Sheet1!$C$3, 2, 3, 1, 1)的功能是（）。
A、返回区域C3:C6的数据值B、返回单元格E4的值C、返回区域C2:C6的数据值D、返回单元格F5的值正确答案：D4、在Excel中，在己打开工作簿中移动一张工作表，不正确操作是（）。
A、在要移动的工作表标签上，直接按住鼠标左键拖动到指定位置B、在要移动的工作表标签上右键单击，执行快捷菜单中的“移动或复制(M)…”命令C、按住Ctrl键，在要移动的工作表标签上按住鼠标左键拖动到指定位置D、按住Shift键，在要移动的工作表标签上按住鼠标左键拖动到指定位置正确答案：C5、只复制工作表中汇总数据，不复制明细数据，以下不正确的操作是（）。
A、隐藏明细数据，按Ctrl+G，在“定位”对话框中单击“定位条件”，在“定位条件”对话框中选择“可见单元格”，确定，然后按Ctrl+C 复制，在目的地按Ctrl+V粘贴B、按住Ctrl，依次单击各个汇总数据，选择汇总数据，然后按Ctrl+C 复制，在目的地按Ctrl+V粘贴C、隐藏明细数据，单击“开始”选项卡下的“编辑”组中的“查找和选择”中的“定位条件”，在“定位条件”对话框中选择“可见单元格”，确定，然后按Ctrl+C复制，在目的地按Ctrl+V粘贴D、隐藏明细数据，按Ctrl+A选择数据，然后按Ctrl+C复制，在目的地按Ctrl+V粘贴正确答案：D6、在工作表Sheet1中，使用嵌套“分类汇总”，依次按C列和E列分类，对G列数据汇总方式为：计数，正确的操作是（）。
Excel 2010 应用教程第4章
设置边框与填充颜色
设置填充颜色 的不同方式
单击“填充颜色”按钮快 速设置：在【开始】→【 字体】组中单击“填充颜 色”按钮可为所选单元格 或单元格区域应用当前显 示的填充颜色；单击“填 充颜色”按钮右侧的下拉 按钮，在打开的下拉列表 中可根据需要选择相应的 填充颜色。
通过“设置单元格格式” 对话框设置：打开“设 置单元格格式”对话框， 单击“填充”选项卡， 在“背景色”栏的颜色 块中可选择所需的颜色； 单击“其他颜色”和 “填充效果”按钮，可 进行填充颜色设置。
2
通过“浮动工具栏”设 置： 通过浮动工具栏也可设 置字体、字号、字形、 字体颜色等，浮动工具 栏中的相应按钮及下拉 列表框的作用与“字体” 组中相同。
3
通过“设置单元格格式” 对话框设置：选择单元 格或单元格区域后，在 【开始】→【字体】组 的右下角单击“对话框 启动器”按钮。 打开 “设置单元格格式”对 话框的“字体”选项卡， 在其中可进行设置。
主要内容
1
2 3 设置单元格格式 设置其他表格格式 使用模板与样式
4
课堂练习
设置单元格格式
设置字体格式
设置对齐方式
设置数字格式
设置边框与填充
1
通过“字体”组设置： 在工作表中选择要设置 字体格式的单元格、单 元格区域、文本或字符 后，在【开始】→【字 体】组中单击相应的按 钮或在其下拉列表框中 选择相应的选项可快速 地设置字体格式。
2
新建条件格式：在“条 件格式”下拉列表中选 择“新建规则”选项， 在打开的“新建格式规 则”对话框的“选择规 则类型”栏中选择不同 的规则类型，在“编辑 规则说明”栏中将出现 不同的参数设置框。
3
删除条件格式：在工作 表中选择任意单元格， 在“条件格式”下拉列 表中选择相应的选项可 进行删除。
Office 2010高级应用项目化教程教案 第4、5讲 教学设计
课前布置任务—课前资讯—课前计划—课中决策、实施—检查—评价
教学过程设计详案
教学
环节
时间分配
教师行为
学生活动
设计意图
【考勤】
1*2min
汇总填写考勤表
由各组组长报告
出勤情况
检查学生出勤情况培养学生守时意识
【新课导入】
2*2min
介绍本次课要学习和巩固的知识点，并通过情景教学，引出本次课程任务。
评分学生用1到两句评价后给分。
培养学生总结、评价的能力。
【作品提交】
检
3*2min
要求每名同学提交计划单和任务作品。
远程提交作品。
培养学生远程提交作品能力。
【教师总结】
1*2min
总结各组表现情况及优点。
【工作结尾】
键盘、鼠标、显示器及凳子的摆放
打扫卫生
【课后记】
教学难点
Excel数据分析。
教学环境
机房。
主要教学方法
基于工作过程的项目化教学。
典型学习方法
行动导向过程操作和小组讨论、展示法。
知识点
1、各种数据类型的使用；
2、数据及单元格格式化设置；
3、数据转置和数据填充；
4、条件格式、数据有效性、数据分列；
5、图表创建与编辑；
6、数据排序、筛选、分类汇总及透视表。
培养学生比较分析的能力。
【完善】
做
8*2min
指导学生完善任务
针对任务中出现的问题进行改正完善。
培养学生认真对待问题的态度。
【检查】
展示
5*2min
引导学生总结、展示或展示优秀的作品。
总结自己的思路，展示自己的作品，学习其他同学的长处。
Office 2010 办公软件应用案例教程第四章
板的形式。 选择【文件】【保存】菜单项，或者单击工具栏中 的保存按钮，弹出【另存为】对话框，在【文件名】 下拉列表文本框中输入“员工培训成绩统计表”，在 【保存类型】下拉列表中选择【Excel表格】选项，单 击【保存】按钮。 此时工作簿名称变为“员工培训成绩统计表.xlt。
《Office 2010实用培训教材》
《Office 2010实用培训教材》
5. 设置字体
选中单元格区域“A1：J1”，在【格式】工具栏中的
【字体】下拉列表中选择【隶书】选项，在【字号】 下拉列表中选择【20】选项，单击【字体颜色】按钮 右侧的下箭头按钮，在弹出的下拉列表中选择【黄色】 选项。 按照相同的方法将单元格区域“A2：J3”中的字体设 置为【白色】。
《Office 2010实用培训教材》
选中单元格区域“A2：A3”，然后按住【Ctrl】键不
放依次选中单元格区域“B2：B3”、“C2：G2”、 “H2：H3”、“I2：I3”、和“J2：J3”，单击【格 式】工具栏中的【合并及居中】按钮。
此时即可将选中的单元格区域中的内容合并居中显示。
然后选中单元格区域“A4：J16”,单击【格式】工具 栏中的【居中】按钮，使表格中的内容居中对齐。
最终效果
《Office 2010实用培训教材》
4.1.1 设计公司员工培训成绩基本表
员工培训成绩主要包括员工代号、姓名、培训科目、
平均成绩、总成绩以及名次等，下面介绍如何创建员 工培训成绩基本表。 1. 建立新工作簿并重命名工作表
在桌面空白处单击鼠标右键，在弹出的下拉菜单中选
《Office 2010实用培训教材》
4. 设置边框和背景色
为了使表格更加清晰美观，用户可以为其添加表格边
一级office第4章 Excel2010的使用
Excel数据输入和编辑须先选定某单元格使其成为 当前单元格，输入和编辑数据要在当前单元格中进行， 也可以在数据编辑区进行。
利用用“数据有效性”功能可以控制单元格可接 受的数据类型和数值范围。具体操作是利用“数据”选 项卡的“数据工具”命令组。
6
4.1 Excel 2010概述
7
4.1 Excel 2010概述
8
4.1 Excel 2010概述
9
3.工作簿、工作表和单元格
1) 工作簿 工作簿是一个Excel文件（其扩展名为.xlsx)。 工作簿最多可以含有255个工作表。 启动 Excel 后，默认有3个工作表：Sheetl、
Sheet2和Sheet3。 2) 工作表
5. 使用帮助
在使用Excel过程中,若遇到不了解的操作，可以求助 于Excel的联机帮助功能。
4.1 Excel 2010概述
13
4.1.3 Excel 主要用途
❖ 创建数据表格 数据输入形成电子表格 数据导入形成电子表格
❖ 实现复杂计算 利用公式实现计算 利用函数实现计算
❖ 建立各种统计图表 直观显示数据间的关系
4.2 基本操作
24
❖ 方式二： 利用“自定义序列”对话框填充数据序列，可自己
定义要填充的序列。
首先选择“文件”选项卡下的“选项”命令，打开 “Excel选项”对话框，单击左侧的“高级”选项，在“常 规”栏目下点击并打开“编辑自定义序列”对话框中；选 择“自定义序列”标签所对应的选项卡，在右侧“输入序 列”下输入用户自定义的数据序列，单击“添加”和“确 定”按钮即可；或利用右下方的折叠按钮，选中工作表中 已定义的数据序列，按“导入”按钮即可。
大学计算机office高级应用(win7+office2010版) excel
Excel1）：在考生文件夹%testdir%中，完成下列操作。
文涵是大地公司的销售部助理，负责对全公司的销售情况进行统计分析，并将结果提交给销售经理。
年底，她根据各门店提交销售报表进行统计分析。
打开“计算机设备全年销量统计表.xlsx”，帮助文涵完成以下操作。
1．将“sheet1”工作表命名为“销售情况”，将“sheet2”命名为“平均单价”。
2．在“店铺”列左侧插入一个空列，输入列标题为“序号”，并以001、002、003……的方式向下填充该列到最后一个数据行。
3．将工作表标题跨列合并后居中并适当调整其字体、加大字号，并改变字体颜色，适当加大数据表行高和列宽，设置对齐方式及销售额数据列的数值格式（保留2位小数），并为数据区域增加边框线。
4．将工作表“平均单价”中的区域B3:C7定义名称为“商品均价”（范围为工作簿）。
运用公式计算工作表“销售情况”中F列的销售额，要求在公式中通过VLOOKUP函数自动在工作表“平均单价”中查找相关商品的单价，并在公式中引用所定义的名称“商品均价”。
5．为工作表“销售情况”中的销售数据创建一个数据透视表，放置在一个名为“数据透视分析”的新工作表中，要求针对各类商品比较各门店每个季度的销售额。
其中：商品名称为报表筛选字段，店铺为行标签，季度为列标签，并对销售额求和。
最后对数据透视表进行格式设置，使其更加美观。
6．根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对各门店四季度笔记本的销售额进行比较。
7．保存“计算机设备全年销量统计表.xlsx”文件。
2）：在考生文件夹%testdir%中，完成下列操作。
小蒋是一位中学教师，在教务处负责初一年级学生的成绩管理。
由于学校地处偏远地区，缺乏必要的教学设施，只有一台配置不太高的PC可以使用。
他在这台电脑中安装了Microsoft Office，决定通过Excel来管理学生成绩，以弥补学校缺少数据库管理系统的不足。
实验4-8Excel2010的综合应用
实验4-9 Excel 2010的综合应用【实验目的】1）掌握Excel 2010的各个知识点。
【相关知识】【实验内容】1）成绩分析小李是北京某政法学院教务处的工作人员，法律系提交了2012级4个法律专业教学班的期末成绩单，为更好地掌握各个教学班学习的整体情况，教务处领导要求她制作成绩分析表，供学院领导掌握宏观情况。
请根据已有数据，帮助小李完成2012级法律专业学生期末成绩分析表的制作。
具体要求如下：①打开"年级期末成绩分析.xlsx”文件。
②在“ 2012级法律”工作表最右侧依次插入“总分” 、“平均分”、“年级排名”列;将工作表的第一行合并为一个单元格，并设置黑体、16磅，使其成为工作表的标题。
对班级成绩区套用带标题行的“表样式中等深浅15”的表格格式。
设置所有列的对齐方式为居中，其中排名为整数，其他成绩的数值保留1位小数。
③在“ 2012级法律”工作表中，利用公式分别计算“总分”、“平均分”、“年级排名”列的值。
对学生成绩不及格（小于60）的单元格套用格式突出显示为“黄色（标准色）填充色红色（标准色）文本”。
提示：“黄色（标准色）填充色红色（标准色）文本”默认的设置格式中没有，需要进行自定义。
表格工具T设计T样式T条件格式T突出显示单元格规则T小于，在“小于”对话框设置“自定义格式”，在“填充”选项卡中设置背景色为黄色（标准色），在“字体”选项卡中设置字体颜色为红色（标准色）。
④在“ 2012级法律”工作表中，利用公式根据学生的学号将其班级的名称填入“班级”列，规则为：学号的第三位为专业代码、第四位为班级序号，即：提示：使用MID+IF函数。
⑤根据“ 2012级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分” 的新工作表中，工作表的标签颜色设置为红色。
要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分，其中行标签为班级。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
4.1 综合案例1
一、案例效果展示(部分)
销售量统计及迷你图
透视表及统计结果
4.1 综合案例1
二、了解案例分析与要求
销售部的助理小王需要根据2014年和2015年的 图书产品销售情况进行统计分析，以便制订新一年 的销售计划和工作任务。 本电子表格共有3个工作表，一个工作表为销售 订单表；一个工作表为2015年图书销售分析表；一 个工作表为图书编目表。(表略)
4.1 综合案例1
四、学习过程（P120—P124）
1．vlookup函数 公式“=VLOOKUP(D3,图书编目表!$A$2:$B$9,2,FALSE)”的含义 是：返回D3单元格数据与图书编目表中$A$2:B$9单元格区域数 据精确匹配后，所对应的B列的值。 vlookup函数的使用方法：“Lookup”的汉语意思是“查找”， 在Excel中与“Lookup”相关的函数有三个：VLOOKUP、 HLOOKUO和LOOKUP。Vlookup函数的作用为在表格的首列查找 指定的数据，并返回指定的数据所在行中的指定列处的数据。 其语法格式为： VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)
基本要求：
本章以制作图书产品销售情况表格和应收 账款账龄分析表为例，综合上章所学的Excel数 据处理与分析的知识，进一步巩固利用Excel相 关操作来进行数据处理与分析的知识，以便熟 练制作各种数据表格，利用表格进行数据分析 与处理，并将相关数据转换成图表。
4.1 综合案例1 4.2 综合案例2 4.3 综合案例3
4.1 综合案例1
具体操作要求:
• 1．在“销售订单”工作表的“图书编号”列中，使用 VLOOKUP函数填充所对应“图书名称”的“图书编号”， “图书名称”和“图书编号”的对照关系请参考“图书编 目表”工作表。 • 2．将“销售订单”工作表的“订单编号”列按照数值升 序方式排序，并将所有重复的订单编号数值标记为紫色 （标准色）字体，然后将其排列在销售订单列表区域的顶 端。 • 3．在“2015年图书销售分析”工作表中，统计2015年各 类图书在每月的销售量，并将统计结果填充在所对应的单 元格中。为该表添加汇总行，在汇总行单元格中分别计算 每月图书的总销量。
4.1 综合案例1
三、知识储备（相关概念）
Excel中所提的函数其实是一些预定义的公式，它们使用 一些称为参数的特定数值按特定的顺序或结构进行计算。 Excel自带函数一共有11类，分别是数据库函数、日期与时间 函数、工程函数、财务函数、信息函数、逻辑函数、查询和 引用函数、数学和三角函数、统计函数、文本函数以及用户 自定义函数。
4.1 综合案例1
具体操作要求:
• 4．在“2015年图书销售分析”工作表中的N4:N11单元格 中，插入用于统计销售趋势的迷你折线图，各单元格中迷 你图的数据范围为所对应图书的1月～12月销售数据。并 为各迷你折线图标记销量的最高点和最低点。 • 5．根据“订单销售”工作表的销售列表创建数据透视表， 并将创建完成的数据透视表放置在新的工作表中，以A1单 元格为数据透视表的起点位置。将工作表重新命名为 “2014年书店销量”。 • 6．在“2014年书店销量”工作表的数据透视表中，设置 “日期”字段为列标签，“书店名称”字段为行标签， “销量（本）”字段为求和汇总项。并在数据透视表中显 示2014年期间各书店每季度的销量情况。
4.1 综合案例1
• （1） VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)， 可以写为：VLOOKUP(需在第一列中查找的数据,需要在其中查 找数据的数据表,需返回某列值的列号,逻辑值True或False)。 • （2）lookup_value为“需在数据表第一列中查找的数据”，可 以是数值、文本字符串或引用。 • （3）table_array为“需要在其中查找数据的数据表”，可以 使用单元格区域或区域名称等。如果range_lookup为TRUE或省 略，则table_array的第一列中的数值必须按升序排列，否则， 函数VLOOKUP不能返回正确的数值。如果range_lookup为FALSE， table_array不必进行排序。Table_array的第一列中的数值可以 为文本、数字或逻辑值。若为文本时，不区分文本的大小写。
4.1 综合案例1
四、学习过程（P120—P12３）
２．month函数 为日期函数，此处公式“=month($B$3:$B$678)”的返回值是3 单元格中日期数据的月份数值“3”。 ３．sumifs函数
多条件求和函数，用于对某一区域内满足多重条件（两个条件 以上）的单元格求和。 B4单元格中的公式“=SUMIFS(销售订单!$H$3:$H$678,销售订 单!$E$3:$E$678,A4,销售订单!$C$3:$C$678,1)”是用来对“销售 订单”工作表中H3:H678单元格区域数据进行求和，但条件是1 月份（条件：销售订单!$C$3:$C$678,1）的《Office商务办公好 帮手》（条件：销售订单!$E$3:$E$678,A4）这本书。
4.1 综合案例1
• （4）col_index_num为table_array中待返回的匹配值的列序号。 Col_index_num为1时，返回table_array第一列中的数值； Col_index_num为2时，返回table_array第二列中的数值，以此 类推。如果Col_index_num小于1，函数VLOOKUP返回错误值 #VALUE!；如果Col_index_num大于 table_array 的列数，函数 VLOOKUP返回错误值 #REF!。 • （5）range_lookup为一逻辑值，指明函数VLOOKUP返回时是精 确匹配还是近似匹配。如果为 TRUE或省略，则返回近似匹配 值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果range_value 为FALSE，函数 VLOOKUP将返回精确匹配值。如果找不到，则返回错误值 #N/A。