自動化輕松提高Excel數(shù)據(jù)處理效率

小文龍

一鍵合并Excel工作簿

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

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

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

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

自定義公式讓計(jì)算更快

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

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

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

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

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