就地取材用Excel做个单词随机默写器

俞木发

1.制作单词随机排序数字

首先准备单词默写素材。素材的获取既可以手动将课本上的单词输入到Excel表格中，也可以直接到“http：//www.171english.cn/”去下载电子版（注意选择正确的版本）。接着在Excel中选中所有输入的单词，右击并选择“翻译”，在右侧的窗格中全选中文翻译结果并复制，再将其粘贴到C列（图1）。

然后定位到D2单元格，输入公式“=SMALL（IF（COUNTIF（D$1：D1，ROW（$1：$21））=0，ROW（$1：$21）），INT（R AND（ ）*（21-ROW（1：1））+1））”并按下“Ctrl+Enter”键，下拉填充到D21单元格，获得1-2 0之间不重复的随机数。如果有更多数量的单词，请更改其中的数字“21”为对应的数字（图2）。

公式解释：

先使用IF和COUNTIF函数来实现此单元格上面的区域判断，如果有，就返回False，而如果没有就返回这个数字，从而生成一组由上面单元格没有出现的数字和False组成的序列，序列的数字是利用随机函数RAND和ROW函数生成。然后再使用SMALL函数从这个序列中取最小值，这样通过数组公式就实现了生成一组不重复的随机数字。最后将其作为需要随机抽取单词的序号。

2.提取随机单词

通过上述生成的随机数可以实现单词的随机排序。接下来就可以按照自己的需要使用VLOOKUP函数提取随机单词，这里以每次随机提取10个单词为例。

新建工作表2，定位到B2单元格并输入公式“=VLOOKUP（Sheet1！D2，Sheet1！A$2：C$21，2，0）”，表示以工作表1的D2單元格中显示的数字为查找目标，查找的区域是工作表1的A$2：C$21区域（注意对行的绝对引用），查找的顺序是从左开始的第2列（即单词列）。将公式下拉到B11单元格（每次提取10个单词），这样根据工作表1的D列显示的随机数字，就可以在工作表2的B2：B11区域随机显示抽取的单词了。同理在A 2单元格中输入公式“=V LOOKUP（B2，Sheet1！B$2：D$21，2，0）”并下拉填充，提取中文到A列（图3）。

因为上述的随机数字是通过RAND函数生成的，这样每次在单元格中输入新的内容后，引用的单词都会发生变化。我们需要将引用的单词固定下来，可以使用宏的方法来实现。依次点击菜单栏中的“开发工具→录制宏”，为录制的新宏添加一个快捷键“Ctrl+W”，接着开始执行下列的操作：

⑴ 在工作表2中选中A2：B11区域中的内容并复制;

⑵ 定位到E2单元格，依次点击“开始→粘贴→粘贴数值→值和源格式”，将随机提取的单词以数值的形式粘贴到E2：F11区域中;

⑶ 选中G2：G11区域，右击并选择“清除内容”，这样运行宏后会自动清除用户输入的数据;

⑷ 点击“停止录制”，完后宏的录制。

最后在打开的宏编辑窗口中可以看到录制的代码。如果要选择其他的区域，请参照代码自行修改（图4）。

3.添加自动批改和得分统计

在工作表2中定位到H 2单元格，输入公式“=IFS（G2=""，"请填入单词"，G2=F2，"√"，G2<>F2，"×"）”并下拉填充，表示使用I FS函数对G列输入的单词内容和F列的答案进行比较，并根据比较结果显示对错符号。为了让结果更醒目，还可以使用条件格式对包含对错符号的单元格进行设置，如“√”符号设置为红色字体显示。继续定位到H12单元格并输入公式“=COUN TI F（H2：H11，"√"）* 10&"分"”，表示使用COUNTIF函数对H列的对勾符号数量进行统计并和10相乘，最终得到统计分数（图5）。

4.优化和最终使用

完成上述的操作后，在工作表2中选中A：D列、F列并右击将其隐藏，选中工作表1也将其隐藏。接着依次点击菜单栏中的“审阅→允许编辑的区域”，在打开的窗口中选择E2：G11为可编辑的区域（即用来粘贴提取到的随机单词和用户输入），点击“确定”返回工作表2（图6）。

继续依次点击“审阅→ 保护工作表”和“保护工作簿”，分别为工作表2和本工作簿添加保护密码。这样孩子在使用这个文档时，他们是看不到答案的，也无法对设置的公式进行更改（图7）。

完成上述所有的操作并保存后，以后需要让孩子默写单词时，他们只要打开上述的文档，然后根据随机抽取的中文翻译输入单词即可，完成默写后会自动批改并算出得分情况。每次完成测试后按下“Ctrl +W”又可以清空输入，并且重新随机出现新的单词（图8）。