就地取材用Excel做個單詞隨機(jī)默寫器

俞木發(fā)

1.制作單詞隨機(jī)排序數(shù)字

首先準(zhǔn)備單詞默寫素材。素材的獲取既可以手動將課本上的單詞輸入到Excel表格中，也可以直接到“http：//www.171english.cn/”去下載電子版（注意選擇正確的版本）。接著在Excel中選中所有輸入的單詞，右擊并選擇“翻譯”，在右側(cè)的窗格中全選中文翻譯結(jié)果并復(fù)制，再將其粘貼到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之間不重復(fù)的隨機(jī)數(shù)。如果有更多數(shù)量的單詞，請更改其中的數(shù)字“21”為對應(yīng)的數(shù)字（圖2）。

公式解釋：

先使用IF和COUNTIF函數(shù)來實現(xiàn)此單元格上面的區(qū)域判斷，如果有，就返回False，而如果沒有就返回這個數(shù)字，從而生成一組由上面單元格沒有出現(xiàn)的數(shù)字和False組成的序列，序列的數(shù)字是利用隨機(jī)函數(shù)RAND和ROW函數(shù)生成。然后再使用SMALL函數(shù)從這個序列中取最小值，這樣通過數(shù)組公式就實現(xiàn)了生成一組不重復(fù)的隨機(jī)數(shù)字。最后將其作為需要隨機(jī)抽取單詞的序號。

2.提取隨機(jī)單詞

通過上述生成的隨機(jī)數(shù)可以實現(xiàn)單詞的隨機(jī)排序。接下來就可以按照自己的需要使用VLOOKUP函數(shù)提取隨機(jī)單詞，這里以每次隨機(jī)提取10個單詞為例。

新建工作表2，定位到B2單元格并輸入公式“=VLOOKUP（Sheet1！D2，Sheet1！A$2：C$21，2，0）”，表示以工作表1的D2單元格中顯示的數(shù)字為查找目標(biāo)，查找的區(qū)域是工作表1的A$2：C$21區(qū)域（注意對行的絕對引用），查找的順序是從左開始的第2列（即單詞列）。將公式下拉到B11單元格（每次提取10個單詞），這樣根據(jù)工作表1的D列顯示的隨機(jī)數(shù)字，就可以在工作表2的B2：B11區(qū)域隨機(jī)顯示抽取的單詞了。同理在A 2單元格中輸入公式“=V LOOKUP（B2，Sheet1！B$2：D$21，2，0）”并下拉填充，提取中文到A列（圖3）。

因為上述的隨機(jī)數(shù)字是通過RAND函數(shù)生成的，這樣每次在單元格中輸入新的內(nèi)容后，引用的單詞都會發(fā)生變化。我們需要將引用的單詞固定下來，可以使用宏的方法來實現(xiàn)。依次點擊菜單欄中的“開發(fā)工具→錄制宏”，為錄制的新宏添加一個快捷鍵“Ctrl+W”，接著開始執(zhí)行下列的操作：

⑴ 在工作表2中選中A2：B11區(qū)域中的內(nèi)容并復(fù)制;

⑵ 定位到E2單元格，依次點擊“開始→粘貼→粘貼數(shù)值→值和源格式”，將隨機(jī)提取的單詞以數(shù)值的形式粘貼到E2：F11區(qū)域中;

⑶ 選中G2：G11區(qū)域，右擊并選擇“清除內(nèi)容”，這樣運行宏后會自動清除用戶輸入的數(shù)據(jù);

⑷ 點擊“停止錄制”，完后宏的錄制。

最后在打開的宏編輯窗口中可以看到錄制的代碼。如果要選擇其他的區(qū)域，請參照代碼自行修改（圖4）。

3.添加自動批改和得分統(tǒng)計

在工作表2中定位到H 2單元格，輸入公式“=IFS（G2=""，"請?zhí)钊雴卧~"，G2=F2，"√"，G2<>F2，"×"）”并下拉填充，表示使用I FS函數(shù)對G列輸入的單詞內(nèi)容和F列的答案進(jìn)行比較，并根據(jù)比較結(jié)果顯示對錯符號。為了讓結(jié)果更醒目，還可以使用條件格式對包含對錯符號的單元格進(jìn)行設(shè)置，如“√”符號設(shè)置為紅色字體顯示。繼續(xù)定位到H12單元格并輸入公式“=COUN TI F（H2：H11，"√"）* 10&"分"”，表示使用COUNTIF函數(shù)對H列的對勾符號數(shù)量進(jìn)行統(tǒng)計并和10相乘，最終得到統(tǒng)計分?jǐn)?shù)（圖5）。

4.優(yōu)化和最終使用

完成上述的操作后，在工作表2中選中A：D列、F列并右擊將其隱藏，選中工作表1也將其隱藏。接著依次點擊菜單欄中的“審閱→允許編輯的區(qū)域”，在打開的窗口中選擇E2：G11為可編輯的區(qū)域（即用來粘貼提取到的隨機(jī)單詞和用戶輸入），點擊“確定”返回工作表2（圖6）。

繼續(xù)依次點擊“審閱→ 保護(hù)工作表”和“保護(hù)工作簿”，分別為工作表2和本工作簿添加保護(hù)密碼。這樣孩子在使用這個文檔時，他們是看不到答案的，也無法對設(shè)置的公式進(jìn)行更改（圖7）。

完成上述所有的操作并保存后，以后需要讓孩子默寫單詞時，他們只要打開上述的文檔，然后根據(jù)隨機(jī)抽取的中文翻譯輸入單詞即可，完成默寫后會自動批改并算出得分情況。每次完成測試后按下“Ctrl +W”又可以清空輸入，并且重新隨機(jī)出現(xiàn)新的單詞（圖8）。