就地取材用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）。