数据计算基础操作—完整实训指导

【实验目的】

➢掌握数据的有效性设置。

➢掌握公式的使用方法。

➢掌握简单函数的应用。

【实验技术要点】

1.数据的有效性设置

①设置数据的有效性；②测试数据有效性。

2.公式的使用

①公式的输入方法。②公式中单元格地址的引用：相对引用、绝对引用、混合引用。③公式的编辑方法。④公式的移动和复制。

3.简单函数的应用

（1）函数的语法。

（2）函数的参数。

（3）简单函数的应用。

①求和函数——SUM函数。

功能：计算参数的和。

语法：SUM（Number1，［Number2］，…）

Number1：必需参数，要相加的第一个数字，该选项可以是数字或单元格范围。

Number2：可选参数，要相加的第二个数字，后面依次类推。

②求平均值函数——AVERAGE函数。

功能：返回一组数中的平均值。

语法：AVERAGE（Number1，［Number2］，…）

Number1：必需参数，参与求平均值的第一个数字，该选项可以是数字或单元格范围。

Number2：可选参数，参与求平均值的第二个数字，后面依次类推。

③计算包含数字的单元格个数函数——COUNT函数。

功能：计算包含数字的单元格以及参数列表中数字的个数。

语法：COUNT（Value1，［Value2］，…）

Value1：必需参数，要计算其中数字的个数的第一项、单元格引用或区域。

Value2：可选参数，要计算其中数字的个数的其他项、单元格引用或区域，最多可包含255个。

④计算非空的单元格个数函数——COUNTA函数。

功能：计算区域中不为空的单元格的个数。

语法：COUNTA（Value1，［Value2］，…）

Value1：必需参数，表示要计数的值的第一个参数。

Value2：可选参数，表示要计数的值的其他参数，最多可包含255个参数。

⑤计算空白单元格的个数函数——COUNTBLANK函数。

功能：计算指定单元格区域中空白单元格的个数。

语法：COUNTBLANK（Range）

Range：必需参数，要计算其中空白单元格数目的区域。

⑥条件计数函数——COUNTIF函数。

功能：对区域中满足单个指定条件的单元格进行计数。

语法：COUNTIF（Range，Criteria）

Range：必需参数，要计算其中非空单元格数目的区域。

Criteria：必需参数，以数字、表达式或文本形式定义的条件。

⑦多条件计数函数——COUNTIFS函数。

功能：对区域中满足多个指定条件的单元格进行计数。

语法：COUNTIFS（Criteria_range1，Criteria1，Criteria_range2，Criteria2，…）

Criteria_range1：必需参数，为第一个需要计算其中满足某个条件的单元格数目的单元格区域（简称条件区域）。

Criteria_range2：第二个条件区域，Criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。

⑧排名函数——RANK函数。

功能：返回一个数字在数字列表中的排位。

语法：RANK（Number，Ref，［Order］）

Number：必需参数，需要排位的数字，即指定的数字。

Ref：必需参数，排位的范围，即列数字。

Order：可选参数，数字排位的方式。若为0，按降序排位，可省略；若不为0，则按升序排位。

⑨求最大值函数——MAX函数。

功能：返回一组数中的最大值。

语法：MAX（Number1，［Number2］，…）

Number1：必需参数，要求最大值的第一个数字，该选项可以是数字或单元格范围。

Number2：可选参数，要求最大值的第二个数字，后面依次类推。

⑩求最小值函数——MIN函数。

功能：返回一组数中的最小值。

语法：MIN（Number1，［Number2］，…）

Number1：必需参数，要求最小值的第一个数字，该选项可以是数字或单元格范围。

Number2：可选参数，要求最小值的第二个数字，后面依次类推。

⑪取整函数——INT函数。

功能：将数字向下舍入到最接近的整数。

语法：INT（Number）

Number：必需参数，要取整的数字，该选项可以是数字或单元格范围。

⑫四舍五入函数——ROUND函数。

功能：按指定的位数对数值进行四舍五入。

语法：ROUND（Number，Num_digits）

Number：需要进行四舍五入的数字。

Num_digits：指定的位数，按此位数进行四舍五入。

如果Num_digits大于0，则四舍五入到指定的小数位。

如果Num_digits等于0，则四舍五入到最接近的整数。

如果Num_digits小于0，则在小数点左侧进行四舍五入。

⑬绝对值函数——ABS函数。

功能：返回指定数字的绝对值。

语法：ABS（Number）

Number：必需参数，要求绝对值的数字，该选项可以是数字或单元格范围。

【实验内容】

一、任务描述

（1）对本节实验项目1中的工作表“成绩统计表”进行相应的编辑操作后，使表格变为如图1.103所示的形式。

（2）设置语文、数学、外语、物理、化学等各科成绩的数据有效性为：数值在0～100之间，允许有小数位。并进行相应的有效性验证操作。

（3）利用公式和函数对“成绩统计表”中的总分、名次、平均分、人数、最高分、最低分、优秀率、及格率进行计算。

（4）对表格进行相应的格式化设置。

图1.103　工作表内容

二、任务目标

实验任务完成后，最后的效果如图1.104所示。

图1.104　效果示意图

三、任务实施

（1）对本节实验项目1的工作表“成绩统计表”进行相应的编辑操作。

把Excel“成绩管理”工作簿中的工作表“成绩统计表”内容复制到“Sheet2”中，按图1.103所示内容对表格进行相应的编辑操作，编辑完成后保存，并把“Sheet2”重命名为“成绩统计表-计算1”。

（2）设置语文、数学、外语、物理、化学等各科成绩的数据有效性为：数值在0～100之间，允许有小数位。并进行相应的有效性验证操作。

①选中单元格区域D3：H12，单击【数据】选项卡，再在【数据工具】组中单击【数据有效性】按钮右边的小箭头，在弹出的下拉菜单中选中【数据有效性】功能，如图1.105所示。

图1.105　【数据有效性】功能

②在【数据有效性】对话框中进行数据有效性设置，如图1.106所示。

a.在【设置】选项卡中定义有效性条件：0～100。

b.在【输入信息】选项卡中输入所需要的输入提示信息：请输入0至100之间的数字。

图1.106　数据有效性设置

c.在【出错警告】选项卡中输入出错警告信息：数据无效，请重新输入！

③最后单击【确认】按钮。

④测试数据有效性以确保其正常工作。

测试方法：尝试在单元格区域D3：H12范围内输入有效和无效数据，验证设置结果，并且显示所预期的信息。

（3）利用公式和函数对“成绩统计表”中的总分、名次、平均分、人数、最高分、最低分、优秀率、及格率进行计算。

①计算课程总分。

方法一：使用自动求和命令计算课程总分。

a.选中单元格区域D3：H3后，单击【开始】选项卡，再在【编辑】组单击【∑自动求和】按钮后，在I3单元格就求出了学号为“0861200001”同学的总分。

b.单击I3单元格，将鼠标指向I3单元格右下角的填充柄处，当出现符号“+”时，拖动鼠标至I12单元格，I3：I12单元格就会分别显示出每位同学的课程总分。

方法二：使用SUM函数计算课程总分。

a.选中单元格I3，然后输入等号【=】。

b.单击编辑栏左端的函数框右边的小箭头，弹出常用函数列表，单击【SUM】函数，即可显示SUM【函数参数】对话框，且光标在【Number1】框中。

c.选择求总分的区域D3到H3，也可以直接在【函数参数】编辑框中输入“D3：H3”，如图1.107所示，单击【确定】按钮，在I3单元格求出了学号为“0861200001”同学的总分，然后利用数据填充功能求出其他同学的课程总分。

计算总分（I3单元格）的公式为：=SUM（D3：H3）。(www.chuimin.cn)

②计算各门课程的平均分。

方法一：使用自动求平均值命令计算课程平均分。

a.先求语文科目的平均分，选中单元格区域D3：D12，单击【开始】选项卡，再在【编辑】组中单击【∑自动求和】按钮右边的小箭头，在弹出的下拉菜单中选中【平均值】功能，在D13单元格求出了语文成绩的平均分。

b.单击D13单元格，将鼠标指向D13单元格右下角的填充柄处，当出现符号“+”时，拖动鼠标至H13单元格，D13：H13单元格就会分别显示出每个科目的平均分。

方法二：使用AVERAGE函数计算各门课程的平均分。

图1.107　SUM【函数参数】对话框

a.单击单元格D13，然后输入等号【=】。

b.单击编辑栏左端的函数框右边的小箭头，弹出常用函数列表，单击【AVERAGE】函数，即可显示【函数参数】对话框，且光标在【Number1】框中。

c.选中语文成绩的区域D3到D12，也可以直接在【函数参数】编辑框中输入“D3：D12”，如图1.108所示，单击【确定】按钮，在D13单元格求出了语文科目的平均分，然后利用数据填充功能求出其他科目的课程平均分。

图1.108　AVERAGE【函数参数】对话框

计算平均分（D13单元格）的公式为：=AVERAGE（D3：D12）。

③对课程的平均分进行四舍五入计算。

a.单击单元格D14，然后输入等号【=】。

b.单击编辑栏左端的函数框右边的小箭头，弹出常用函数列表，单击【其他函数】，即可显示【插入函数】对话框，在【搜索函数】下面的方框中输入round，然后单击【转到】按钮，在函数列表中选中并双击【ROUND】函数，进入ROUND【函数参数】对话框，且光标在【Number】框中。

c.单击单元格D13，也可以直接在【函数参数】编辑框中输入“D13”，移动鼠标光标至【Num_digits】框中，输入“0”，如图1.109所示。

d.单击【确定】按钮，在D14单元格求出了语文平均分四舍五入后的值，然后利用数据填充功能求出其他科目平均分四舍五入后的值。

计算平均分（四舍五入）（D14单元格）的公式为：=ROUND（D13，0）。

图1.109　ROUND【函数参数】对话框

④对课程的平均分进行取整计算。

a.单击单元格D15，然后输入等号【=】。

b.单击编辑栏左端的函数框右边的小箭头，弹出常用函数列表，单击【其他函数】，即可显示【插入函数】对话框，在【搜索函数】下面的方框中输入int，然后单击【转到】按钮，在函数列表中选中并双击INT函数，进入INT【函数参数】对话框，且光标在【Number】框中。

c.单击单元格D13，也可以直接在【函数参数】编辑框中输入“D13”，如图1.110所示。

d.单击【确定】按钮，在D15单元格求出了语文平均分取整后的值，然后利用数据填充功能求出其他科目平均分取整后的值。

计算平均分（取整）（D15单元格）的公式为：=INT（D13）。

图1.110　INT【函数参数】对话框

⑤计算总人数。

a.单击单元格D16，然后输入等号【=】。

b.用COUNT函数计算学生的人数，方法同AVERAGE函数。

计算人数（D16单元格）的公式为：COUNT（D3：D12）。

⑥计算各科目成绩的最高分。

a.单击单元格D17，然后输入等号【=】。

b.用MAX函数计算各科目成绩的最高分，方法同AVERAGE函数。

计算最高分（D17单元格）的公式为：MAX（D3：D12）。

⑦计算各科目成绩的最低分。

a.单击单元格D18，然后输入等号【=】。

b.用MIN函数计算各科目成绩的最低分，方法同MAX函数。

计算最低分（D18单元格）的公式为：MIN（D3：D12）。

⑧计算各科目成绩的优秀率。

优秀率的计算规则：某科目成绩在90分以上的人数/总人数。

a.选中单元格D19，然后输入等号【=】。

b.单击【公式】选项卡，再在【函数库】组中单击【插入函数】按钮，进入【插入函数】界面，在【搜索函数】下面的方框中输入countif，然后单击【转到】按钮，在函数列表中选中并双击【COUNTIF】函数，进入COUNTIF【函数参数】对话框，且光标在【Range】框中。

c.选中语文成绩的区域D3到D12，也可以直接在【函数参数】编辑框中输入“D3：D12”。

d.移动鼠标光标至【Criteria】框中，输入“＞=90”，如图1.111所示，单击【确定】按钮后，计算出语文成绩在90分以上的人数。

图1.111　COUNTIF【函数参数】对话框

e.单击单元格D19，在编辑栏“=countif（D3：D12，“＞=90”）”的后面输入“/D16”（D16：总人数），如图1.112所示。

图1.112　优秀率计算

f.然后利用数据填充功能求出其他科目成绩的优秀率。

g.最后把计算的结果利用单元格格式化设置方法转化为百分比格式。

计算语文成绩优秀率（D19单元格）的公式为：=COUNTIF（D3：D12，“＞=90”）/D16。

⑨计算各科目成绩的及格率。

及格率的计算规则：某科目成绩在60分以上的人数/总人数。

a.选中单元格D20，然后输入等号【=】。

b.及格率的计算方法和优秀率的计算方法相似，只需要把求优秀率的条件“＞=90”改为求及格率的条件“＞=60”即可，其他操作步骤完全一样。

计算语文成绩的及格率（D20单元格）的公式为：=COUNTIF（D3：D12，“＞=60”）/D16。

⑩计算总分的降序排位。

a.选中第三行单元格J3，然后输入等号【=】。

b.单击编辑栏左端的函数框右边的小箭头，弹出常用函数列表，单击【其他函数】，即可显示【插入函数】对话框，在【搜索函数】下面的方框中输入“rank”，然后单击【转到】按钮，在函数列表中选中【RANK】函数并双击鼠标后，即可显示RANK【函数参数】对话框，且光标在【Number】框中，选择第三行总分所在单元格I3，也可直接输入“I3”。

c.移动鼠标光标至【Ref】框中，选择总分的区域“I3：I12”，然后再把这个区域的单元格地址变为绝对地址$I$3：$I$12，也可直接输入“$I$3：$I$12”。

d.移动鼠标光标至【Order】框中，输入0（按降序方式排位），如图1.113所示。

图1.113　RANK【函数参数】对话框

e.单击【确定】按钮，在J3单元格求出了学号为“0861200001”同学的总分名次，然后利用数据填充功能求出其他同学的总分名次。

（4）对表格进行相应的格式美化设置。

根据上一个实验项目学习的表格格式化方法对表格进行相应的格式美化设置。

【实验练习】

（1）用Excel创建“期中成绩统计表”，内容如图1.114所示。按照题目要求完成后，用Excel的保存功能存盘。

要求：

①表格要有可视的边框，并将表中的列标题设置为宋体、14磅、居中，其他内容设置为宋体、12磅、居中。

②设置语文、数学、外语、物理、化学等各科成绩的数据有效性为：数值在0～100之间，允许有小数位。并进行相应的有效性验证操作。

③用函数计算总分。

图1.114　期中成绩统计表

④用函数计算出名次。

⑤用函数计算出每门课程的平均分。

⑥将每门课程低于平均分的成绩以红色显示。

（2）用Excel创建“职工工资表”，内容如图1.115所示。按照题目要求完成后，用Excel的保存功能存盘。

图1.115　职工工资表

要求：

①表格要有可视的边框，并将表中的列标题设置为宋体、14磅、居中，其他内容设置为宋体、12磅、居中。

②用函数计算实发工资，实发工资=基本工资+奖金+补贴。

③在“路程”与“沈梅”之间插入一条记录，数据为：刘怡、F、1230.60、100.00、0.00。数字均保留两位小数。

④将所有性别为M的改为男，F改为女。

⑤用函数统计实发工资大于2000的人数。

（3）用Excel创建“学生成绩表和分数段统计”，内容如图1.116所示。按题目要求完成之后，用Excel的保存功能直接存盘。

要求：

①为表格绘制浅绿、双线型边框，并为分数段统计表的科目和分数段表格绘制绿色斜线头。

图1.116　学生成绩表和分数段统计

②将表中的文字设置为宋体、深绿、12磅、居中、加粗。

③用函数计算总分，并将计算结果填入对应的单元格中。

④用函数计算名次，并将计算结果填入对应的单元格中。

⑤用COUNTIF、COUNTIFS函数统计各科目各分数段的人数，并将计算结果填入对应的单元格中。

（4）创建“教师工资表”，内容如图1.117所示，按照题目要求完成后，用Excel的保存功能存盘。

图1.117　教师工资表

要求：

①表格要有可视的边框，并将表中的内容设置为宋体、10.5磅、居中。

②为表中的列标题行设置“灰色底纹”图案格式。

③用公式计算表格中每人的系数合计和工资，其中，工资=系数合计×12×4+津贴。

④在相应的单元格中用函数计算平均工资。

⑤在相应的单元格中用函数计算最高工资。

（5）用Excel创建“家庭理财、使用量记录表和单价表”，内容如图1.118所示，按照题目要求完成后，用Excel的保存功能存盘。

图1.118　家庭理财、使用量记录表和单价表

要求：

①所有表格要有可视的边框，并将表中的列标题设置为宋体、14磅、居中，其他内容设置为宋体、12磅、居中。

②用“使用量记录表”和“单价表”中的相关数据计算“家庭理财”工作表中的相关费用，计算时必须使用绝对引用。

③用公式计算“家庭理财”工作表中的支出小计、收入小计和当月节余。

④用函数计算“家庭理财”工作表中的平均每月节余（平均每月节余=当月节余的总和/6，计算时必须使用函数，直接用公式计算不得分）。