自動化輕松提高Excel數據處理效率

小文龍

一鍵合并Excel工作簿

學校要求以班為單位，匯總學生的家庭成員信息（圖1），由于表格中的信息牽扯到個人隱私，為避免泄密，以往都是學校印發相關表格，讓家長手工填好，然后老師收齊，手工輸入到Excel表格中。工作量大、累人不說，關鍵是容易出錯。如果直接讓家長填寫Excel表格并核對，再通過QQ或郵箱發給老師，老師只需一鍵完成相關Excel工作簿的合并，即可輕松搞定學生家庭成員信息的匯總工作。

運行Excel（本文以2016版為例），首先需要修改宏安全等級。單擊“文件→選項→信任中心”，在彈出的“Excel選項”對話框中單擊“信任中心設置”按鈕，打開“信任中心”并選中左側的“宏設置”選項（圖2），勾選右側的“啟用所有宏”單選框并單擊“確定”按鈕即可。

接下來從https：//dwz.cn/OD8NDF1k下載“模板.xlsm”并保存到D盤根目錄，然后在Excel中打開，將圖1所示的表頭內容復制進來（圖3）。按下Ctrl+A快捷鍵，選中所有單元格并右擊之，在彈出的右鍵菜單中選擇“設置單元格格式”，打開與之同名對話框的“數字”選項卡，選中“文本”，將所有單元格的數據類型設置為文本并單擊“確定”。

老師將學生家長發來的學生信息表全部保存到D盤根目錄下，然后單擊圖3中所示的“匯總”按鈕，稍等片刻，就會完成該目錄下所有學生家庭成員信息的合并操作（圖4）。全選圖4中所示的所有單元格，復制并粘貼到一個新的Excel文檔中，并以“某年級某班學生信息表.XLSX”保存之，最后把這個Excel文檔提交給學校主管部門就可以了。

自定義公式讓計算更快

為了解學生的考試情況，以及對教師工作進行量化考核，每次月考結束后，筆者所在學校都會以“外比成績”為標準，將老師的成績與全縣最好的進行排名。“外比成績”的計算方法（圖5），如果靠計算器來算，工作量無疑是相當大的。其實，我們完全可以將該繁瑣的公式轉換成Excel所能“聽懂”的公式，就可以快速計算出每位老師的“外比成績”了。

根據圖5所示的“外比成績”計算公式，可知表格中至少要有個人的及格率、平均分、優秀率，以及全縣最好的及格率、平均分、優秀率等不同的數據。據此設計出表格（圖6），然后從考試成績明細中，將對應的數據復制過來備用。

對比圖5所示的“外比成績”計算公式，以及圖6所示與之相關的數據，先在日4單元格中輸入公式“=D4/G4*20*55%*25%+B4/E4*20*55%*25%+C4/F4*20*55%*15%”（不包括外側弓}號），然后按下回車鍵，就可以計算出第一位老師的外比成績了。最后將光標定位到H4單元格，往下拖動填充手柄至最后一位老師“外比成績”所對應的日列單元格（如日22單元格），即可算出所有老師的外比成績（圖7）。

選中圖7所示Excel表格的任一單元格，然后按下Ctrl+G，陜捷鍵，并根據提示單擊“定位條件”按鈕，打開“定位條件”對話框（圖8），再選中該對話框中的“常量”單選框并單擊“確定”。按下Ctrl+1快捷鍵，打開“設置單元格格式”對話框的“保護”選項卡，取消勾選“鎖定”復選框并單擊“確定”，最后在“審閱”選項卡中，單擊“保護工作表”按鈕，根據提示輸入密碼就可以了。

經過以上步驟的操作，以后將鼠標定位到日列的包含公式的某個單元格，如果無意中修改了公式，則會提示輸入密碼，公式的修改才會生效，這就避免了公式被誤修改的可能性。而下次再計算“外比成績”時，只需把與個人以及全縣最好成績有關的及格率、平均分、優秀率等數據復制到圖7所示的B列至G列單元格中（無須輸入密碼），則會自動計算出每位老師新的外比成績，真的是非常方便。