拓展案例-Excel电子表格处理.doc

第5章 电子表格处理
拓展案例1——制作校历
【任务要求】
建立以下电子表格，要求：
(1) 表格标题为隶书、20磅大小，合并居中。
(2) 表格内部标题、周次、月份、为黑体、12磅大小。
(3) 表格内部标题、周次水平居中，月份竖排居中。
(4) 假日为红色、加粗。
(5) 外框线为粗框线、周次、月份和日期之间的竖线为粗线、各月之间的日期用双线间隔。
(6) 以“校历.xls”为文件名保存在【我的文档】文件夹中。

【操作提示】
Excel 2003可以方便地制作电子表格，利用填充柄可充等差数列的数据，利用【格式】工具栏中的工具可设置字体、字号、颜色、基本格式边框、合并居中等，利用【格式】/【单元格】命令，弹出【单元格格式】对话框，可设置单元格的自定义边框格式。

【操作步骤】
(1) 启动Excel 2003，输入表格标题，并设置格式为隶书、20磅大小。选定A1：I1单元格区域，单击【格式】工具栏中的【合并单元格】按钮。
(2) 输入表格内部标题，并设置格式为黑体、12磅大小，单击【格式】工具栏中的【居中】按钮。
(3) 输入周次的“1”和“2”，选定这两个单元格，拖动填充柄到A22单元格，填充周次，并设置为黑体、12磅大小，单击【格式】工具栏中的【居中】按钮。
(4) 在相应单元格内输入月份，并设置格式为黑体、12磅大小，单击【格式】工具栏中的【居中】按钮。合并各月份的单元格区域。选定这些单元格区域，选择【格式】/【单元格】命令，在【对齐】选项卡中设置文字方向为竖排。
(5) 对于每一周，先输入前两个日期，然后用填充的方法填充其余的日期。设置假日的格式为红色、加粗。
(6) 选定A2:I22单元格区域，选择【格式】/【单元格】命令，在【边框】选项卡中设置外围边框为粗边框。
(7) 选定A2:A22单元格区域，选择【格式】/【单元格】命令，在【边框】选项卡中设置右边框为粗边框。
(8) 选定I2:I22单元格区域，选择【格式】/【单元格】命令，在【边框】选项卡中设置左边框为粗边框。
(9) 选定B3单元格，选择【格式】/【单元格】命令，在【边框】选项卡中设置左边框和下边框为双线边框。用同样的方法，设置其余月份间的分割线。
(10) 选择【文件】/【保存】命令，在弹出的对话框中选择【我的文档】文件夹，输入保存的文件名为“校历.xls”，保存工作簿。

拓展案例2——产生序列和数阵
【任务要求】
建立以下文档，要求：
(1) 用公式填充的方法在Sheet1工作表中生成斐波那契数列的前20项，并重命名工作表名为“斐波那契数列”。

(2) 用公式填充的方法在Sheet2工作表中生成杨辉三角形的前10行，并重命名工作表名为“杨辉三角形”。

(3) 用公式填充的方法在Sheet1工作表中生成斐波那契数列的前20项，并重命名工作表名为“九九乘法表”。

(4) 以“序列和数阵.xls”为文件名保存在【我的文档】文件夹中。
【操作提示】
如果单元格的内容为公式，用填充柄可填充公式。在填充时，公式中的相对地址或混合地址的相对部分会根据填充单元格与原单元格的相对位移进行变化，公式中的绝对地址或混合地址的绝对部分会则不发生变化。用填充柄可填充公式时，样板公式一定要正确。
【操作步骤】
(1) 在Sheet1工作表的A1和A2单元格中输入1，在A3单元格中输入“=A1+A2”。
(2) 选定A3单元格，拖动填充柄到A20单元格。
(3) 双击Sheet1的工作表标签，在工作表标签内输入“斐波那契数列”。
(4) 在Sheet2工作表的B1、B2和C2单元格中输入1，在B3单元格中输入“=A2+B2”。
(5) 选定B3单元格，拖动填充柄到B10单元格。
(6) 选定B3单元格，拖动填充柄到D3单元格。用同样的方法，填充杨辉三角形的其余行。
(7) 双击Sheet2的工作表标签，在工作表标签内输入“杨辉三角形”。
(8) 在Sheet3工作表的B1、C1单元格中输入1和2，选定B1:C1单元格其余，拖动填充柄到J1单元格。
(9) 在Sheet3工作表的A2、A3单元格中输入1和2，选定A2:A3单元格其余，拖动填充柄到A10单元格。
(10) 在B2单元格内输入“=B$1*$A2”，拖动填充柄到B10单元格。
(11) 选定B3单元格，拖动填充柄到C3单元格。用同样的方法，填充九九乘法表的其余行。
(12) 双击Sheet3的工作表标签，在工作表标签内输入“九九乘法表”。
(13) 选择【文件】/【保存】命令，在弹出的对话框中选择【我的文档】文件夹，输入保存的文件名为“序列和数阵.xls”，保存工作簿。
拓展案例3——建立成绩分析表
【任务要求】
建立以下文档，要求：
(1) 平均成绩和总成绩用公式计算，保留2位小数。
(2) 平均分、最高分、最低分用公式计算，保留2位小数；优秀(>=90分)、良好（<90分，并且>=80分）、中等（<80分，并且>=70分）、及格（<70分，并且>=60分）、不及格（<60分）人数用公式统计。
(3) 不及格的成绩设置为红色、倾斜，90分以上的成绩加粗。
(4) 以“成绩分析表.xls”为文件名保存在【我的文档】文件夹中。

【操作提示】
Excel 2003提供了丰富的函数，可完成常用的计算和统计工作。选择【格式】/【条件格式】命令，弹出【条件格式】对话框，可设置单元格的条件格式。
【操作步骤】
(1) 在工作表中输入原始数据。
(2) 选定F4单元格，输入“=AVERAGE(C4:E4)”，拖动填充柄到F15单元格。
(3) 选定G4单元格，输入“=SUM(C4:E4)”，拖动填充柄到G15单元格。
(4) 选定C18单元格，输入“=AVERAGE(C4:C15)”，拖动填充柄到F18单元格。
(5) 选定C19单元格，输入“=MAX (C4:C15)”，拖动填充柄到F19单元格。
(6) 选定C20单元格，输入“=MIN(C4:C15)”，拖动填充柄到F20单元格。
(7) 选定C21单元格，输入“=COUNTIF(C4:C15,">=90")”，拖动填充柄到F21单元格。
(8) 选定C22单元格，输入“=COUNTIF(C4:C15,">=80")-COUNTIF(C4:C15,">=90")”，拖动填充柄到F22单元格。
(9) 选定C23单元格，输入“=COUNTIF(C4:C15,">=70")-COUNTIF(C4:C15,">=80")”，拖动填充柄到F23单元格。
(10) 选定C24单元格，输入“=COUNTIF(C4:C15,">=60")-COUNTIF(C4:C15,">=70")”，拖动填充柄到F24单元格。
(11) 选定C25单元格，输入“=COUNTIF(C4:C15,"<60")”，拖动填充柄到F25单元格。
(12) 选定A1:G1单元格区域，单击【格式】工具栏中的【合并单元格】按钮，设置字体为隶书、字号为20磅。
(13) 选定A3:G3单元格区域，设置字体为黑体、字号为12磅、水平居中。
(14) 选定B4:B15单元格区域，设置字体为楷体、字号为12磅。
(15) 选定C4:E15单元格区域，选择【格式】/【条件格式】命令，弹出【条件格式】对话框，在【条件格式】对话框中，设置【条件1】为“<60”，设置格式为红色、倾斜，设置【条件2】为“>=90”，设置格式为加粗，单击【确定】按钮。
(16) 选定F4:G15单元格区域，选择【格式】/【单元格】命令，弹出【单元格格式】对话框，在【数字】选项卡中，设置小数位数为2位，单击【确定】按钮。
(17) 选定C18:F20单元格区域，选择【格式】/【单元格】命令，弹出【单元格格式】对话框，在【数字】选项卡中，设置小数位数为2位，单击【确定】按钮。
(18) 选择【文件】/【保存】命令，在弹出的对话框中选择【我的文档】文件夹，输入保存的文件名为“成绩分析表.xls”，保存工作簿。

拓展案例4——建立公司利润表
【任务要求】
建立以下工作表。

在工作表中建立以下两张图表。

【操作提示】
在Excel 2003中，选定工作表的区域，选择【插入】/【图表】命令，弹出【图表向导】对话框，通过【图表向导】对话框，可一步一步地建立图表。
【操作步骤】
(1) 在工作表中输入原始数据。
(2) 单击D4单元格，输入“=(B4-B3)/B3”，拖动填充柄到D12单元格。单击E4单元格，输入“=(C4-C3)/C3”，拖动填充柄到E12单元格。
(3) 选择A2～C12单元格区域，选择【插入】/【图表】命令，弹出【图表向导】对话框。
(4) 在【图表向导】对话框中，在【图表类型】列表框中选择【柱形图】类型，再从【子图表类型】列表中选择第1个子类型，单击【下一步】按钮。
(5) 在【图表向导】对话框中，不做任何设置，单击【下一步】按钮。
(6) 打开【标题】选项卡，在【图表标题】文本框中输入“收入和利润总额”，在【分类（X）轴】文本框中输入“年度”，在【数值（Y）轴】文本框中输入“总额”，单击【下一步】按钮。
(7) 在【图表向导】对话框中，选择【作为其中的对象插入】单选钮，单击【完成】按钮。
(8) 单击“收入和利润总额”图表的图表标题，在【常用】工具栏中的【字体】下拉列表中选择“隶书”，在【常用】工具栏中的【字号】下拉列表中选择“16”。
(9) 单击“收入和利润总额”图表的分类轴标题，在【常用】工具栏中的【字体】下拉列表中选择“楷体”，在【常用】工具栏中的【字号】下拉列表中选择“14”。
(10) 单击“收入和利润总额”图表的数值轴标题，在【常用】工具栏中的【字体】下拉列表中选择“楷体”，在【常用】工具栏中的【字号】下拉列表中选择“14”。
(11) 单击“收入和利润总额”图表的分类轴，单击【常用】工具栏上的【加粗】按钮。
(12) 单击“收入和利润总额”图表的数值轴，单击【常用】工具栏上的【加粗】按钮。
(13) 单击“收入和利润总额”图表的绘图区，选择【格式】/【绘图区】命令，弹出【绘图区格式】对话框，在【区域】组中选择【无】单选钮，单击【确定】按钮。
(14) 用步骤(3)~(13)类似的方法，建立和设置“收入和利润增长”图表。
(15) 选择【文件】/【保存】命令，在弹出的对话框中选择【我的文档】文件夹，输入保存的文件名为“公司利润表.xls”，保存工作簿。

拓展案例5——销售表排序、筛选和汇总
【任务要求】
对以下销售表（部分）完成排序、筛选和汇总操作。

操作要求：
(1) 分别按“售货员”、“商品”、“金额”对销售表进行排序。
(2) 对销售表按售货员名称的笔画顺序从小到大进行排序，同一售货员再按商品名称的笔画顺序从小到大排序顺序进行排序，同一商品再按金额从大到小进行排序。
(3) 筛选出所有“电视机”的销售记录，以及筛选出“金额”介于6000～7000之间的销售记录。
(4) 筛选出“10月4日”售货员“李四”销售“电冰箱”的销售记录。
(5) 分别按“日期”、“售货员”和“商品”汇总金额。
(6) 按“日期”汇总金额，如是同一日期，再按“商品”汇总金额。

【操作提示】
通过【数据】/【排序】命令，弹出【排序】对话框，可对工作表进行排序。通过【数据】/【筛选】/【自动筛选】命令，打开自动筛选，可对工作表进行筛选。通过【数据】/【分类汇总】命令，打开【附录汇总】对话框，可对工作表进行分类汇总。

【操作步骤】
(1) 在Excel 2003中打开“销售表.xls”。
(2) 单击“售货员”列中的一个单元格，再单击【常用】工具栏上的按钮，按售货员姓名的拼音顺序从小到大进行排序。
(3) 单击“售货员”列中的一个单元格，再单击【常用】工具栏上的按钮，按售货员姓名的拼音顺序从大到小进行排序。
(4) 单击工作表数据区中的一个单元格，选择【数据】/【排序】命令，【排序】对话框。
(5) 在【排序】对话框中，在【主要关键字】下拉列表框中选择“售货员”，选择其右边的【递增】单选钮，单击【选项】按钮，【排序选项】对话框。
(6) 在【排序选项】对话框中，选择【笔画排序】单选钮，单击【确定】按钮，返回【排序】对话框。在【排序】对话框中，单击【确定】按钮。
(7) 用类似的方法完成对“商品”和“金额”的排序。
(8) 单击销售表数据区中的一个单元格，选择【数据】/【自动筛选】命令。
(9) 在字段名【商品】下拉列表中选择【电视机】，筛选出所有“电视机”的销售记录。
(10) 在字段名【商品】下拉列表中选择【（全部）】，工作表显示所有记录。
(11) 在字段名【金额】下拉列表中选择【（自定义）】，弹出【自定义自动筛选方式】对话框，在第1个条件的左边下拉列表框中选择【大于或等于】，在第1个条件的右边下拉列表框中输入“6000”，选择【与】单选钮，在第2个条件的左边下拉列表框中选择【小于或等于】，在第2个条件的右边下拉列表框中输入“7000”，单击【确定】按钮，筛选出“金额”介于6000～7000之间的销售记录。
(12) 在字段名【金额】下拉列表中选择【（全部）】，工作表显示所有记录。
(13) 在字段名【日期】下拉列表中选择【10月4日】，在字段名【售货员】下拉列表中选择【李四】，在字段名【商品】下拉列表中选择【电冰箱】。完成以上操作后，筛选出“10月4日”售货员“李四”销售“电冰箱”的销售记录。
(14) 选择【数据】/【自动筛选】命令，取消自动筛选，显示所有记录。
(15) 单击“日期”列中的一个单元格，再单击【常用】工具栏上的按钮，按日期从小到大进行排序。
(16) 选择【数据】/【分类汇总】命令，弹出【分类汇总】对话框，在【分类字段】下拉列表中选择【日期】，在【汇总发生】下拉列表中选择【求和】，在【选定汇总项】下拉列表中只选择【金额】，单击【确定】按钮，按日期进行分类汇总。
(17) 选择【数据】/【分类汇总】命令，在弹出的【分类汇总】对话框中单击【全部删除】按钮，删除新建立的分类汇总。
(18) 用类似方法，按“售货员”和“商品”汇总金额。
(19) 单击工作表数据区中的一个单元格，选择【数据】/【排序】命令，弹出【排序】对话框，在【排序】对话框中，在【主要关键字】下拉列表框中选择“日期”，选择其右边的【递增】单选钮，在【次要关键字】下拉列表框中选择“商品”，选择其右边的【递增】单选钮，单击【确定】按钮。
(20) 选择【数据】/【分类汇总】命令，弹出【分类汇总】对话框，在【分类字段】下拉列表中选择【日期】，在【汇总发生】下拉列表中选择【求和】，在【选定汇总项】下拉列表中只选择【金额】，单击【确定】按钮。
(21) 选择【数据】/【分类汇总】命令，弹出【分类汇总】对话框，在【分类字段】下拉列表中选择【商品】，在【汇总发生】下拉列表中选择【求和】，在【选定汇总项】下拉列表中只选择【金额】，取消选择【替换当前分类汇总】复选框，单击【确定】按钮，完成按“日期”汇总金额，如是同一日期，再按“商品”汇总金额。
(22) 选择【数据】/【分类汇总】命令，在弹出的【分类汇总】对话框中单击【全部删除】按钮，删除新建立的分类汇总。
(23) 至此，全部完成查询销售表的工资制作，不保存工作表，退出Excel 2003。