Excel常用函数与公式应用实验报告

实验课名称：计算机应用基础

实验项目名称：Excel常用函数与公式应用 专业名称： 班学

级： 号：

学生姓名： 教师姓名：

年 月 日

实验日期： 年 月 日 实验室名称： 明远 2205

一．实验名称：Excel常用函数与公式应用 二．实验目的与要求：（1）掌握Excel数据有效性设置方法。（2）掌握Excel利用公式和函数获取数据，更新数据，自动重算的特点。（3）能够利用Excel自带的函数库，根据实际需要，创建不同用途的公式，打造不同用途的表格。（4）了解Excel几个简单实用的工具。 三．实验内容：《大学计算机应用基础实验指导与习题集》第7章实验2 四．实验设备与软件环境： 1、硬件： CPU: 主板: 内存: 显卡: 硬盘: AMD Athlon（速龙） II P360 (双核) 戴尔 0PN9 (AMD RS880 + SB810/SB850) 2 GBytes ATI Mobility RADEON HD 30/50 [戴尔] WDC WD3200BPVT-75ZEST0 2.软件：操作系统windows 7，office 2007 五．实验步骤与结果： 1． 打开excel2007。选中“A1：C1”单元格区域，点击“经典菜单”选项卡下的“合并后居中”按钮，在下拉列表中选择“合并单元格“按钮。同理，分别对如习题集上的表格所相对应的地方做”合并”处理，并且输入相关内容。 2． 选取“B4：D13”单元格区域。单击“数据”选项卡“数据工具”组的“数据有效性”按钮，打开“数据有效性”对话框，选“设置”选项卡。点击”允许“的下拉列表，选择”整数“，在”最小值“中输入”0“，在”最大值“中输入”20“。 3． 选中“B4：D3”单元格区域，单击“数据“选项卡”数据工具“组的”数据有效性“按钮，打开”数据有效性“对话框，选”输入信息“选项卡。在”标题“下的空格里输入”必做题“，在”输入信息“下的空格里输入”满分20分“。选择”出错警告“选项卡，在”标题“下的空格里输入”无效“，在”错误信息“下的空格里输入”数据错误“。选中“E4：G13”单元格区域，单击“数据“选项卡”数据工具“组的”数据有效性“按钮，打开”数据有效性“对话框，选”设置“选项卡。点击“允许”右下方的下拉列表选择“整数”，“最小值“内输入”0“，”最大值“内输入”20“。选”输入信息“选项卡，在“标题”下的空格里输入“选做题”，在“输入信息”内输入“满分20分”。选“出错警告“选项卡， 在”标题“下的空格内输入”无效“，在”错误信息“下的空格内输入”数据错误“，点击”确定“。同理，选中”H4：H13“单元格区域，单击“数据“选项卡”数据工具“组的”数据有效性“按钮，打开”数据有效性“对话框。选“设置”选项卡，对“允许”设置为“整数”，“最小值”为“0”，“最大值”为“10”。选“输入信息”选项卡，在“标题”下输入“测验”，在“输入信息”下输入“满分10分”。选“出错警告”选项卡，在“标题”下输入“无效”，在“错误信息”下输入“数据错误“。选中”J4：J13”单元格区域，打开“数据有效性”对话框，选“设置“选项卡，在“允许”下选择“整数”，“数据”下选择“大于或等于”，“最小值”下输入“0”。选“输入信息“选项卡，在”标题”下输入”旷课“，在”输入信息“下输入”若无旷课记录，该项可为空白“。选”出错警告“选项卡，设置与前面相同。选定”I4：I13”单元格区域，打开”数据有效性“对话框，选设置“选项卡”，单击“允许”项右侧的下拉箭头按钮，在打开的列表中选择“序列”，在“来源”编辑框里输入“0,5,10”。选“输入信息”选项卡，在“标题”内输入“研讨“，在内容内输入“单击右侧下拉按钮，选择成绩值”，点击“确定”。 4． 选中“K4”单元格，输入“=B4+C4+D4+MAX(E4：G4)+H4+I4-J4*10”,按Enter键。单击”K4“单元格，拖动填充柄至”K13“单元格。选中”M4“单元格，输入”=ROUND(K4*30%+L4*70%,0)”,按Enter键，然后单击“M4”单元格，拖动填充柄至“M13”单元格。 5． 选中”M4：M13”单元格区域，单击“开始”选项卡“样式”组的“条件格式”按钮，从下拉列表中选择“新建规则”，打开“新建格式规则”对话框。在“选择规则类型“中选择第2项”只为包含以下内容的单元格设置格式“，在”编辑规则说明“中分别选择或输入“单元格值“，”小于“，”60“，单击”格式“按钮，打开”设置单元格格式“对话框，在“颜色”下拉列表中选择“红色“，在“字形”中选择“加粗”，点击“填充”选项卡，选择背景色为“淡蓝色”，单击”确定“按钮，返回”新建格式规则“对话框，单击“确定”按钮完成。 6． 选择“N4”单元格，单击“公式”选项卡”函数库“组中的”插入函数“按钮，打开”插入函数“对话框，在”选择函数“中选定”IF“，单击”确定“按钮，打开”IF函数参数”对话框,在“Logical_test”文本框中输入”M4>=90”,在Value_if_true”文本框中输入“优秀”，单击“Value_if_false”文本框，再单击编辑栏最左边函数列表右侧的下拉箭头按钮，选定”IF“，打开第二层”IF函数参数“对话框，在”Logical_test”文本框中输入“M4>=60”,在”Value_if_true”文本框中输入“通过”，单击“Value_if_false”文本框，输入”未过“。单击”确定“完成。单击”N4“单元格，拖动填充柄至”N13“单元格。 7． 选择“O4“单元格，单击“公式”选项卡”函数库“组中的”插入函数“按钮，打开”插入函数“对话框，在”选择类别“中选择”全部“，在”选择函数“中选定”RANK“，单击”确定“按钮，打开”RANK函数参数“对话框，单击“M4”单元格；单击”Ref”文本框，选定“M4：M13”单元格区域，单击”确定“按钮。点击”O4“单元格，在编辑栏里将”M4：M13”改为“$M$4:$M$13”。单击“O4”单元格，拖动填充柄至“O13”单元格。 8． 选定“D15“单元格，单击”公式“选项卡”函数库“组中的”自动求和“按钮，从下拉选项中选”平均值“函数，选定”M4：M13”单元格区域，按Enter键。选定“D16”单元格，单击”公式“选项卡”函数库“组中的”自动求和“按钮，从下拉选项中选择“最大值”函数，选定“M4：M13”单元格区域，按Enter键。同理，选定”D17“单元格，选择”最小值“函数，并选定”M4：M13”单元格区域，按回车键。选择“D18”单元格，点击”公式“选项卡”函数库“组中的”插入函数“，在选择函数中选定”COUNTIF”,打开“COUNTIF函数参数对话框”，选定“M4：M13”单元格区域，单击”Criteria”文本框，输入“>=90”，单击”确定“按钮完成。分别依次选定”D19”，”D20”,”D21”,”D22”单元格，在编辑栏中分别输入“=COUNTIF(M4:M13,\">=80\")-D18”“ =COUNTIF(M4:M13,\">=70\")-SUM(D18:D19)”“ =COUNTIF(M4:M13,\">=60\")-SUM(D18:D20) “=COUNTIF(M4:M13,\"<60\")”选定“E18：E22”单元格区域，单击“公式”选项卡”函数库“组中的”插入函数“按钮，打开”插入函数“对话框，在”选择函数“中选择”FREQUENCY”,单击“确定”按钮，打开“FREQUENCY函数参数”对话框，单击“Date_array”,文本框，选定”M14：M13”单元格区域，单击“Bins_array”文本框，输入”｛100,,79,69,59｝“，按”Ctrl+shift+Enter”完成设置。单击“D23”单元格，在编辑栏里输入”=SUM(D18:D21)/SUM(D18:D22)”,单击D24单元格，在编辑栏里输入“=D18/SUM(D18:D22)”。选中”D23”和”D24”单元格区域，点击”经典菜单“选项卡下的”百分比样式“，右击在快捷菜单中选择“设置单元格格式”，在“数字”选项卡下的“小数位数”中输入“1”，点击“确定”。 9． 依照习题集上的内容，在“工作表“内输入各项”平时考察记录“值及”期末成绩“。 10． 选中“A1：O1”单元格区域，点击“经典菜单”选项卡下的“合并后居中”按钮，再选中其中的字体，点击“经典菜单”选项卡下字体下拉列表的“华文隶书”，点击字号下拉列表的“14”，点击“下划线“。选中表格，点击“经典菜单”选项卡下的“边框”，下拉列表选择“其他边框”，点击“外边框”和“内部“，再点击”确定“。选中“A15：A24”单元格区域，点击“经典菜单”选项卡下的“格式”，在下拉列表中选择“行高”,在弹出的对话框中输入19“，点击”确定“。选中表格，点击”经典菜单“选项卡下的”居中“按钮。选中”B15：C24“单元格区域，点击”经典菜单“选项卡下的”文本向右对齐“按钮。 11． 点击“保存”后退出Excel，选中工作薄，右击在快捷菜单中选择“重命名”，在其中输入“实验7-2计算机成绩汇总”，按回车键结束。

六．实验中遇到的问题、解决方法及体会： 在制作公式的时候比较麻烦，对于习题集上要求掌握的一些工具，比如“插入函数“的使用，不是很熟练。在选择函数时，应选择什么样的函数比较迷惑，甚至不懂所选择的函数的意思。这些使我体会到上课的时候要认真听讲，不懂的要及时地问老师，并且自己应多上机操作练习。