[Excel] 運用Vlookup查找新舊班級重複的學生(新生/舊生)

若學生升上下個年級後,班級的人或多或少會有異動,過去通常都是用人眼比對出新舊學生,但現在有更快、正確律更高的方法,就是用Excel常見的Vlookup函數來幫你查找相同的學生姓名、身份證字號或是學號。

範例題:

一年一班學生升上二年級後,有兩個學生轉走了,有兩個新生轉入班級,要找出哪兩位新生在這個班級。

首先,若是兩份班級Excel檔案的話,建議先把檔案合併成一份,合併方式很簡單,可以用複製貼上在同一個Excel頁籤中或是在同一個Excel分兩個頁籤放資料都可以。

使用VLOOKUP比對原班級舊生

範例公式:
=vlookup(D3, B:B, 1, 0)

範例公式解釋

  • =vlookup(D3的意思是:新班級要比對的第一筆資料此例中為學生的身份證字號,
  • B:B的意思是:查找整個B列欄位的資料,若你要找特定範圍例如:B3到B7範圍,則可這樣寫:B$3:B$7 ($的符號是把範圍鎖定,才不會你拉複製公式的時候查找範圍跑掉),
  • 1的意思是:查找範圍內的第1欄,
  • 0的意思是:查找完全符合的結果

從下圖解法中,可以看到,若你用上述公式後,遇到舊生的時候,會跑出相同的身份證字號;遇到新生的時候,會跑出#N/A,也就是系統找不到的意思。

解法示範

進階解法

因為原本的查找方式只會顯示出舊學生的身份證字號以及新學生的#N/A,若報表要提供給其他人看,這樣就有點不足,所以我們再把剛才的公式延伸一下,就可以顯示出新學生/就學生的字樣囉!

範例公式:
=IF(ISNA(VLOOKUP(D3,B:B,1,0)),"新學生","舊學生")
  • =IF(ISNA( 前面的VLOOKUP的公式 ),”#N/A要顯示的文字”,”查找到符合條件的文字”)

從下圖範例可以看到,原本查找出符合的身份證字號,因為套用上述公式後,順利顯示成所有人都可以輕易閱讀的文字描述了。

IF+ISNA+VLOOKUP解法示範

以上方法,若你管理的班級人數很大,或是你是管理整個學校、補習班、部門員工等等,會大幅增加你的工作效率,同樣的工作,卻可以省下超多時間、也不會像肉眼核對容易對錯的狀況發生。以上方法,各位參考看看囉。

本文同步適用於微軟Excel, Office 365 Excel(含網頁版), Google文件-Google Sheet。