可变虚拟数据在Excel函数教学中的应用

摘要:在Excel的函数教学中,学生不用函数进行计算,而是凭借观察和手工计算得出结果,是一个普遍存在的问题。该文给出解决这一问题的有效方法――使用可变虚拟数据。

关键词:随机、函数、可变虚拟数据、Excel教学、应用

中图分类号:TP317文献标识码:A文章编号:1009-3044(2010)09-2188-01

函数一直是Excel教学的重点和难点,因为使用函数可以解决复杂的计算问题。但是,很多学生一想到数学中关于函数的讨论,就对这个词望而生畏,于是想方设法回避它。例如:在员工信息表中,根据员工参加工作的年份,计算其现在的工龄(见图1)。出此练习目的是让学生运用YEAR()函数,可是很多人不按要求作。他们会用观察和口算,直接填写结果。当你强调要使用函数时,会被认为是简单问题复杂化。因为对于只有6条记录的这个数据表而言,他们的做法确实是最便捷的。所以,老师要有迫使学生使用函数和公式的办法。我们不在A列单元格填写具体的日期值,而是填写能够得到可变日期值的计算公式。由此获得的数据就是本文所谓“可变虚拟数据”,其实质是一个包含随机函数的公式。

1 随机函数的基本用法

Excel提供的随机函数RAND(),返回大于等于0且小于1的均匀分布随机实数。于是,要产生一个0到X (包括端点)的随机整数,可以用这样的公式:“=INT(RAND()*(X+1))”;要产生X到Y(包括端点)的随机整数,可以用这样的公式:“=INT(RAND()*(Y-X+1))+X”。其中,X、Y是整数,且0

RAND()函数每次计算都将重新返回一个随机数。正是这一特点,使它产生可变的数据。

2 可变虚拟数据的构造方法

2.1 随机整数

在随机函数的基本用法里已经给出通用公式。例如:公式“=INT(RAND()*43)+18”可以随机产生一个18到60的虚拟年龄值;公式“=INT(RAND()*12)+1”可以随机产生一个1到12的虚拟月份值。

2.2 随机日期

构造虚拟日期的通用公式是“=DATE(随机的年份数,随机的月份数,随机的日号数)”。例如,引言中提到的参加工作时间可以这样构造:“=DATE(INT(RAND()*43)+1967,INT(RAND()*12)+1,INT(RAND()*30)+1)”。此公式随机产生一个1967年到2009年的日期值。

2.3 随机文本

公式“=CHAR(随机的ASCII码)”将得到一个基于本机字符集的随机字符。例如:

公式“=CHAR(INT(RAND()*26)+65)”将随机产生一个大写英文字母;

公式“=CHAR(INT(RAND()*26)+97)”将随机产生一个小写英文字母;

公式“= CHAR(INT(RAND()*26)+65) & CHAR(INT(RAND()*26)+97) & CHAR(INT(RAND()*26)+97)”可以随机产生由一个大写字母和两个小写字母组成的虚拟的公司名称(或者品名、产地、人名等)。

2.4 其他随机数据

公式“=IF(RAND()*2>1,"男","女")”可随机产生一个虚拟的性别值(“男”或“女”);公式“=ROUND(RAND()*100,2)” 可随机产生一个100以内的商品虚拟单价(包含2位小数的值)。此外还可以列举很多。

3 可变虚拟数据的应用

如前文所述,在需要的列构造出第一个虚拟数据。之后用拖动单元格控制柄的方法,向下填充任意多行。这样,既可以快速编制多行虚拟数据,又保证了数据的可变性。

当用户在工作表进行任何操作,那些包含随机函数的公式都会重新计算,生成新的结果。通过观察和口算来解决问题的方法,引用的是静态的数据,得到的也是静态的结果。而这静态的结果只要一写入,她所引用的虚拟数据就发生了变化,于是答案成为错误的了。这就使得不使用函数的学生无法完成题目。回到最初的例子中。 在A2单元格中,输入公式“=DATE(INT(RAND()*43)+1967,INT(RAND()*12)+1,INT(RAND()*30)+1)”。确认(即回车或点击编辑栏前面的“?”)后,向下拖动此单元格的填充柄到A6(或任意行)。就完成了虚拟数据的构造。然后让学生在此动态数据表中计算每个记录的工龄。

正确的做法是,先计算一个人(比如第一条记录)的工龄:在B2单元格输入公式“=2010-YEAR(A2)”。然后拖动句柄向下填充至B6。效果类似图2。如果重新拖动填充一次,所有的数据又都变了,结果也随着改变,但是计算结果总是与虚拟数据相适应的。不同的学生,用一样的公式、一样的步骤,得到的数据和结果也不同。这是正常的。

这种构造可变虚拟数据的做法还有一个好处。把该表所在工作簿的副本分发给学生,每个学生打开时所看到的数据是不同的,从而杜绝了抄袭别人数据的念头。

值得注意的是,不能在排序问题中使用可变虚拟数据,因为排序的结果不会随数据的改变而更新。解决办法是,编制好虚拟数据表之后,选中并复制数据区,选择性粘贴为数值。这样,各可变数据就变成了固定的数据。这时就可以进行排序练习了。

4 结论

“可变虚拟数据”是一个包含随机函数的计算公式。它在提供Excel教学或考试用虚拟数据表时,可以显著提高出题效率。在函数单元的教学和考试中,更是一种防止学生直接填写静态数据而不使用函数和公式的有效方法。

参考文献:

[1] Walkenbach J.Excel 2003公式与函数应用宝典[M].邱燕明,赵迎,译.北京:电子工业出版社,2004.

[2] Walkenbach J.Excel 2007公式与函数应用宝典[M].张兆心,译.北京:清华大学出版社,2008.

[3] 道奇,斯廷林.精通Excel 2007中文版[M].汪青青,傅鑫,陈宗斌,译.北京:清华大学出版社,2008.