大学计算机基础实践教程

#大学计算机基础实践教程(文科)第4章电子表格处理软件Excel2003实验九Excel2003建立工作表操作实验目的1掌握建立工作表的一般方法。掌握管理工作表的一般方法。操作指导案例1如图4-1所示，按照下列内容建立一张工作表，并完成下列操作：合并A1:E1单元格，内容居中。删除Sheet3表。3.将Sheetl重命名为“差旅费”。AECDE1KXX差旅费一览表2到达地点交通费住宿费补助合计3北京-上海65022030011704北京-兰丽1.200.0015020015505北京-成祐1,100.001302001480图4-1工作表内容合并单元格操作步骤如下：选中A1:E1区域。单击工具栏上的“合并及居中” 按钮。删除和重命名工作表操作步骤如下：右键单击“Sheet3”工作表标签，选择“删除”命令。右键单击“SheetT工作表标签，选择“重命名”命令，输入“差旅费”实验十Excel2003数据计算及图表制作实验目的1.熟悉格式化操作和输入公式的基本方法。掌握图表的建立和编辑方法。操作指导案例1

如图4-2所示，按照下列内容建立一张工作表，并完成下列操作：1•计算“人数”列的“合计”项和“所占比例”列(所占比例 =人数/合计),结果保留2位小数。2•选取“某大学在校生人数情况表”的“年级”列(不含“合计”列)和“所占比例”列，建立“分离型饼图”(系列产生在“列”),标题为“在校生人数分年级比例图”，使该图表位于“某大学在校生人数情况表”的 A8:E18单元格区域内。1234512345G7某大学在校生人数情况表年级人数 所占t翦］V级 4650392535&831&0级级級图4-2某大学在校生人数情况表计算“人数”列的“合计”项和“所占比例”列操作步骤如下:(1)单击B7单元格，再单击工具栏上的“自动求和”(1)单击B7单元格，再单击工具栏上的“自动求和”按钮,或单击菜单【插入】选择“函数”命令，然后再选择“ SUM函数，如图4-3所示，在确认求和区域为B3:B6无误后，按回车键。AcD1某大学在校生人数情况表2年级AIL一，所占比例3一年级瘢q439255三年级6四年级7合计3IfSUMIjLwaba-rl^［nwXT］」.s,)图4-3使用SUM函数求和(2)如图4-4所示，在C3单元格中输入=B3/$B$7，因为求所占比例的分母在复制公式时，是不变化的，所以必须对B7单元格使用绝对引用。某大学在校生人数情况表年级级级级级46£0］二3925■356831某大学在校生人数情况表年级级级级级46£0］二3925■356831叫15303.图4-4输入公式(3)在B7单元格输入公式按回车键求得结果，对于“数值”型数据,Excel默认保留2位小数，否则，也可以单击菜单【格式】，选择“单元格”命令，在“单元格格式”对话框中的“数字”选项卡中，选择“数值”，对“小数位数”重新进行设置。其他单元格的公式用“填充柄”复制，如图4-5所示。ABC1棊大学在校生人数情况表2年级人数所占比例3—年级4S500.304二年级392&6265三年级35680.23&四年级31600.217合计15303图4-5求所占比例建立分离型饼图操作步骤如下：（1） 按下Ctrl键，同时选中“年级”列和“所占比例”列。（2） 单击【插入】，选择“图表”命令，在打开的“图表向导”对话框中，先选择“饼图”中的“分离型饼图”，再按下“按下不放可查看示例”按钮，观察最终作图效果，如图4-6所示，如果确认没有问题，按向导的步骤一步一步的往下做。图4-6图4-6“图表向导”对话框4-7（3）依题意，将完成的图表通过移动和压缩放置在 A8:E18单元格区域内，如图4-7所示。ABC二三4563925356S3160260,23二三4563925356S3160260,230.214图4-7移动图表到A8:E18区域2346500.案例2如图4-8所示，按照下列内容建立一张工作表，并完成下列操作：1•将标题行“职工工资表” （A1:G1）合并居中，并将格式设为黑体，字号 20。在“路程”与“沈梅”之间插入一条记录， 数据为：刘怡F230.60100.000.0015.86。用公式求出实发工资（实发工资 =基本工资+奖金+补贴-房租）。将所有性别为M的改为男，F改为女。将工作表Sheet2重命名为“工资表”，并复制职工工资表到该工作表 A2:G16区域（不包括标题）。6•在工作表“工资表”中以“姓名”和“实发工资”为数据区建立一个簇状柱形图图表，刻度最大值为1500,主要刻度单位为150,图表位置放在A17:J35区域内。基本工熒奖金补贴315.32253.00基本工熒奖金补贴315.32253.00100.)0285.12230.0Q1Q0.00490.34300.00200.00200.76100.000.00530.00320.00300.00腭租20.1518,001'5.002S.0010.00实发工贾891011121314N500*6025B.002OC.0015.00F300.80230.00100.0010.34F450.36280.00200.)015.57200.45100-001〕.0018,3SF230.45220.00so.00IB.酣F360.30240.00100.0022.QDP612.60450.00300.JO20.00II39678I240,00I150,00 20.00图4-8工作图表ABcDEG第1-5题的操作此处省略，具体操作可参考前面的例子或教材的相关内容， 注意第4题做完后，计算实发工资公式中的 F会改为女，应在公式中将女再改为 F,重新计算实发工资

即可。第6题经过相关的操作，可以得到原始的簇状柱形图图表，如图 4-9所示。此时只要用鼠标左键双击该图表左边的“数值轴” ，就会出现图4-10所示的“坐标轴格式”对话框，在“刻度”选项卡中，将“最大值”修改为1500，“主要刻度单位”修改为150即可（注意修改值后，该两处的复选框选中状态自动消失，不要再选中） 。1600.001400.001200,001000.00800.00600,00400,00200.000.00实发工资□实发工资此卜備雀1涇世抵塩砥弱巔吕吕.悴实发工资□实发工资图4-9原始的簇状柱形图图4-10“坐标轴格式"对话框㈱图4-9原始的簇状柱形图图4-10“坐标轴格式"对话框实验^一Excel2003公式与函数实验目的熟悉公式及函数的使用。掌握复制公式及函数的方法操作指导案例1如图4-11所示，按照下列内容建立一张工作表，并完成下列操作：1•计算所有学生的总分。用COUNTIF()统计各学科》90分的人数。将统计结果按科目用簇状柱形图表示出来存放到 sheet1中。ACEFGHIJ1学号姓窖语文数爭英侑生物历史政治地理21101李冰96?5938360808131102耿萌萌1029010693389190411036011272033&05251104牛栋9890919098929461105王衍银103959680S0909571106郝鹏1014662&590066181107刘红631817163757259沏盼人数图4-11各科成绩工作表计算总分操作步骤如下：在J2单元格中插入函数SUM。用“填充柄”复制其他单元格。使用函数COUNTIF作统计计算操作步骤如下：在C9单元格中插入统计函数COUNTIF，出现如图4-12所示的对话框，在范围Range的文本框中输入C2:C8，条件Criteria的文本框中输入＞=90,按“确定”按钮得到所求结果。求其他单元格统计结果，用“填充柄”复制。函數羞数图4-12“函数参数”对话框建立簇状柱形图表操作步骤如下：(1)根据题目分析可以知道，作图需要选择的数据源是各科目和》 90分人数的统计结果，如图4-13所示。(2)其余操作可仿照建立分离型饼图操作步骤， 只是在“图表向导”步骤之2,选择“系列”选项卡，使用鼠标单击“名称”文本框右侧的折叠按钮以折叠文本框，再单击工作表中A9单元格，然后再单击折叠按钮展开文本框 (也可以在“名称”文本框中输入》90分人数)，以替代图例“系列1”，如图4-14所示。ABCDEFGHLJ1kJ円宁可姓名谨数学英语生物历史政治地理总分］21101李冰9G75938360808156831102耿萌萌1029010&93S39L9066041103孙浩6011272033605226351.104牛栋9890919093929465361105王術镐10395拠8030S09563971106郝帕10146&26590866151181107刘红631S17163757252339>90^人数5342233图4-13选择数据源源我据T®敷据区域MOSTA®|口孑加対N救1容购（H源我据T®敷据区域MOSTA®|口孑加対N救1容购（H）-=Sbw■七盤幼系列电）[乔加面~]|刖险两分类㈡藉忻志①：[职梢][<上一步⑥）吓一券⑩讨〔完威無]图4-14选择数据源案例2如图4-15所示，按照下列内容建立一张工作表，并完成下列操作：用RANK（）函数根据平均分字段完成“名次”字段的填写 （注：一定要按照先填写12:15之中某一单元格公式后，其它单元格复制（可用填充柄）的方法完成此题，最高分填1（第1名），依次类推）。2•将A1:I5单元格内容居中，并将英语、数学、化学、总分、平均分五列单元格中小于60的单元格的字体用红色表示（注：随单元格中数据值的变化，该单元格字体发生变化）。■15-学号3418QLQ0341801013418U102341Gdli)3ri.o65.075.0O60.367.735.3■15-学号3418QLQ0341801013418U102341Gdli)3ri.o65.075.0O60.367.735.3明红90.088.020.040,05g010.0181,0203.Q106.0图4-15工作表使用RANK（）函数计算名次操作步骤如下：（1）在I2单元格中插入统计函数 RANK（），出现如图4-16所示的对话框，在指定的数字Number（用来比较该数字在一列数字中相对其他数值的大小 ）的文本框中输入G2，在Ref（对一组数的引用）的文本框中输入 $G$2:$g$5，单击“确定”按钮得到所求结果。图4-16“函数参数”对话框（2）求其他单元格统计结果，用“填充柄”复制。使用条件格式显示数据操作步骤如下：（1） 依题意选中C2:G5区域。（2）单击【格式】，选择“条件格式”命令，出现图 4-17所示的对话框，在左侧的下拉列表框中选择“小于”，在右侧的下拉列表框中输入60，单击“格式”按钮，在出现的“单元格格式”对话框中，选择字体颜色为“红色” ，再返回“条件格式”对话框，单击“确定”按钮。图4-17“条件格式”对话框案例3如图4-18所示，按照下列内容建立一张工作表，并完成下列操作：1•利用公式填写评语，条件是，月考 1与月考4成绩比较，如月考1大于月考4,由评语为“成绩退步，严重警告中”，如果月考4大于月考1成绩40分以上，则评语为“进步神速，希更加努力”，除此以外的情况评语都为“进步平平，望加倍努力” 。2•修正表格里设定的错误的公式，并重新更正原有数据（提示：使用 AVERAGE算出平均分）。3•将4次月考平均成绩大于550的同学的姓名以蓝色加粗突出表示（提示：利用求平均公式以及筛选功能）。

4次月考成绩升降情况的独立图4•根据表格的特点，给表格制作一张能够反映某同学4次月考成绩升降情况的独立图可取舍，图表名称为：图表 1)。5.删除工作表Sheet2、Sheet3。6.标题部分使用楷体_GB2312加粗。并给表格加上蓝色表格边框线(标题除外)，使表格看起来更美观。148910111213性男女女男男女男女禺男彼迷红曆华玉志名无A山寻禮蓉社方有用姓华万英李刘黄阿东余吴148910111213性男女女男男女男女禺男彼迷红曆华玉志名无A山寻禮蓉社方有用姓华万英李刘黄阿东余吴500520510530480490530540544530期530458440郷伽開45805關600477520530540498450&005063994585W翊488500S20530亨山中学高一3或2Q03笫一学期成绩表月考I 月考2月考孑 月考4 个人平均成绩评语46S 490 §50 500瞰TTT849E.666T516.3333526.5556平均分图4-18工作表使用逻辑函数IF填写评语操作步骤如下：单击H3单元格，根据题意在编辑栏内输入 =IF(F3-C3<0,"成绩退步，严重警告中",IF(F3-C3>40," 进步神速，希更加努力","进步平平，望加倍努力"))，按回车键完成H3单元格的评语(注意在英文输入法下，输入上述非中文符号) 。利用“填充柄”完成其他单元格的评语，如图 4-19所示。H3- - 匸二IF耐£!!«>."应缆退执严直翌古中：IF疔3如紙"逬挣神息器更加斜力逆去忤，望抑僭翼力"Dtit^is月考4HUA.円吐10.东力玉11亲有也.12.吴用平均廿13S.

卿.4B&.46S

■139.tit^is月考4HUA.円吐10.东力玉11亲有也.12.吴用平均廿13S.

卿.4B&.46S

■139.77774S3ooooo5305304QD450 6PQ叩0 4旳-&KT516.3533EZ6进步卜逋，希更抑鳄力进步神逋，希更打穷力进涉平平，望加蜩为进歩平平，望扭活错力图4-19填写评语使用函数AVERAGE求平均分及个人平均成绩操作步骤如下：在C13单元格插入函数AVERAGE，确认所求区域C3:C12无误后，按回车键。利用“填充柄”求得D3:F3单元格区域的平均分。与上面同理，使用函数AVERAGE，可以求得G3:G12区域的个人平均成绩。自动筛选及格式化操作这里省略。制作簇状柱形图的独立图表操作步骤如下：

(1)依据题意选中姓名和四次月考成绩作为制作图表的数据源，如图4-20所示。(2)其余操作可仿照建立分离型饼图操作步骤进行，注意在向导步骤之 34-20所示。项卡中，将“图表标题”内填写“月考成绩情况表” ，在“数据标志”选项卡中选中“值”在向导步骤之4，选中“作为新工作表插入”单选项，将“ Chartl”改为“图表1”，完成后如图4-21所示。23456?Sg10111213名菸專F杜鑫用姓华万英李刘黄阿杀亲吴射男女女畀另女畀女男男粉宁山申学高一珈2003第一学期成绩表000000^-800253d-s255o954545544542考23456?Sg10111213名菸專F杜鑫用姓华万英李刘黄阿杀亲吴射男女女畀另女畀女男男粉宁山申学高一珈2003第一学期成绩表000000^-800253d-s255o954545544542考月noO484789880-84567998&5454544344月0045800000134283002555545555543考个人平均威绩评语000000630034-300406306554655555537430.75533516.75458.5509.5,望加倍豁力.希更枷翳力咸绩退歩.严重警吿中咸绩退歩.严重警苦中进歩平平，望加倍翳力进歩神底希更和努力进歩平平，望加倍翳力进歩神凰希更加聲力迸歩神速，希更加努力487.6 497.S冈玄T523.9■望如倍努力那平平，望扣倍勞力图4-20选择数据源月号成”况衰M+IA□M43月号成”况衰M+IA□M43LUI寺4Ml**”\l9j^h/'：SMvtl£5hiHt2Z£baBtg£5h・t4/图4-21独立工作图表该题删除工作表及格式化操作省略，边框线的设置参见教材 422。实验十二Excel2003数据管理实验目的掌握工作表中筛选记录的方法。掌握排序及分类汇总的方法。操作指导

案例1如图4-22所示，按照下列内容建立一张工作表，并完成下列操作：1计算表中的实发工资=基本工资+补助工资-扣款。利用自动筛选，筛选出“人事科”中基本工资大于或等于 1000并且补助工资小于180的人员。3•将筛选出的符合条件的最后一行字体设置为黑体、字形设置为粗体。G2” ^D2+E2-F2ABC2345612345618123459-ndl_寻-弓「.宇-&J莓寻寻穿字密爭人人人敎教财财石扬石基本工资补助工资扣款 实发工贵1000.00lioToof15”ool_1105”U0l1030.00 珈00 逗D0「lg002m00n.002102.00 1阪00 70.1021g00 1如00 70,002000.00 220.00： 65.002000.00 190.00 365.00图4-22数据清单计算实发工资操作步骤如下：在G2单元格中输入=D2+E2-F2，按回车键后求得G2单元格的实发工资。再利用“填充柄”完成其他单元格的实发工资。自动筛选操作步骤如下：(1)在数据清单中单击任一个单元格，单击菜单【数据】 ，选择“筛选”|“自动筛选”命令后，如图4-23所示。編号El科室创姓名日基本工归补助工归rl1256編号El科室创姓名日基本工归补助工归rl12565798102341r=・匚、--Lr.=-V-事务务务务人教鞍财财il燕W平兰卫繁滕萍周畅石扬1000.00120.0015.001105.001030.00180.0015.001195.00100.00210.000.00310.002102.00150.0070.10218L902100.00120.0070.002150.002000.00220.00&5+002155.002000.00190.003阪001825.00FG扣款I二图4-23执行“自动筛选”后的数据清单单击“科室”右侧的按钮，在出现的下拉列表中选择“人事科”如图 4-24所示。单击“基本工资”右侧的按钮，在出现的下拉列表中选择“自定义”，打开如图4-25所示的“自定义自动筛选方式”对话框，在左侧的下拉列表框中选择“大于或等于” ，在右侧的下拉列表框中输入1000，单击“确定”按钮。

A1编号F申室A1编号F申室円21#序排列阵序排列3£（BU10亍…教务科4|~567810财务科CDEFG姓名s基本工込补助工Q扣款M实发工资E1105.00滕燕1000.00120.0015.001030.00180.0015.001195,00周平1呱00210.000.00310.00杨兰2102+00150.0070+102181.90石卫国2100.00120.0070.002150.00扬繁2000.00220.0065+002155.002000.00190.00365.001825.00)图4-24筛选人事科图4-25“自定义自动筛选方式”对话框（4）同理，可以筛选出补助工资小于 180的人员，筛选最终结果如图 4-26所示。ABCDEFG编号啓詁姓名B滕燕基本工辽1000.00补助工垃120+00扣款E15,00实发工资F1105.0012图4-26自动筛选最终结果Excel的格式化操作与Word相同，在这里字体格式化操作步骤不再赘述。案例2如图4-27所示，按照下列内容建立一张工作表，并完成下列操作：1.计算出每一种商品的价值（价值=单价*数量），填入相应的单元格中。2•将“单价”栏中的数据设置为货币样式，并将列宽设定为 20。按商品名称的拼音字母顺序进行升序排列，并按商品名称对数量进行分类汇总求和（替换当前分类汇总，汇总结果显示在数据下方） 。

ABCD1部分商品统计表2商品名称单价数量价值3电很锅135.00314高压锅6500225气压热水和32.0032S3压愍水瓶36.00127电饭锅158,00258崗压锅43.0032g气压熬水瓶35.002610高压锅58.004311电饭锅138.001512咼压锅75.0055图4-27工作表计算出每一种商品的价值操作步骤如下：(1)在D3单元格中输入=B3*C3，按回车键。(2)用“填充柄”复制公式求其他单元格的结果。设置货币样式及列宽操作步骤如下：(1)将B3:B12区域选中，单击菜单【格式】，选择“单元格”命令，出现图4-28所示的“单元格格式”对话框，在“数字”选项卡的“分类”中，选择“货币” ，在“货币符号”中，选择“Y”，单击“确定”按钮。(2)单击菜单【格式】，选择“列”|“列宽”命令，在出现的“列宽”对话框中，输入20,单击“确定”按钮。图4-28“单元格格式”对话框排序及分类汇总操作步骤如下：(1)在进行分类汇总前，应先对数据清单进行排序，在数据清单中单击任一单元格，单击菜单【数据】，选择“排序”命令，出现图4-29所示的对话框，在“主要关键字”的下拉列表框中，选择“商品名称”，单击“确定”按钮。

图4-29“排序”对话框（2）接着单击菜单【数据】，选择“分类汇总”命令，出现图4-30所