筆者的一位朋友在一所大學(xué)的學(xué)生信息中心工作，她常常需要用Excel制作如圖1所示的學(xué)生信息表?？梢钥闯鲞@張表格有來自不同年級、學(xué)院和系別的同學(xué)，由于這個原因，平時她基本上都是手動輸入這些信息的。
由于筆者也完成過類似的任務(wù)，知道位于第一列的學(xué)號是按一定規(guī)律編排的。為了提高工作效率，其實可以根據(jù)這個規(guī)律，結(jié)合Excel的公式和函數(shù)快速地從學(xué)號中提取出相應(yīng)的信息，自動填入到相應(yīng)的單元格中。當(dāng)數(shù)據(jù)量比較大時，這種方法的優(yōu)越性非常明顯。
一、前提條件：了解學(xué)號的編排規(guī)律
利用這個方法的前提條件是學(xué)號是按一定規(guī)律編排的，在實際情況中幾乎都是如此。例如，圖1表格中學(xué)號的編排規(guī)律是：學(xué)號的第一位數(shù)代表生源地區(qū)，如果是“5”就代表“外地生源”，如果是“4”就代表“本地生源”。第二至第三位代表年級，第四至第六位代表學(xué)院，比如“110”代表“數(shù)科院”，“111”代表“信息學(xué)院”等。第七至第八位代表“系別”，比如“24”代表“數(shù)學(xué)系”，“27”代表“計算機(jī)系”等，最后三位代表所在班級的學(xué)號。
二、 使用IF函數(shù)建立公式從學(xué)號中提取信息
1.由于“學(xué)號”的數(shù)據(jù)類型為文本，因此設(shè)置A列的單元格格式的數(shù)據(jù)類型為“文本”。
2.選中C2單元格，建立根據(jù)學(xué)號得出學(xué)生生源信息數(shù)據(jù)的公式。在C2單元格中輸入的公式為：“=IF(A2="","",IF(LEFT(A2,1)="5","外地生源",IF(LEFT(A2,1)="4","本地生源","無效的生源代碼")))”。這個公式的含義是：如果在A2中還沒填入學(xué)號，就返回一個空字符串，如果填入的學(xué)號的第一位字符為“5”，則返回字符“外地生源”，如果第一位字符為“4”，則返回“本地生源”，如果是其他字符，就返回“無效的生源代碼”。輸入公式后，利用公式填充柄復(fù)制公式到下方恰當(dāng)?shù)膯卧瘛：竺嬲埫看屋斎牍胶?，都?fù)制到恰當(dāng)?shù)膯卧瘛?(函數(shù)具體用法請參閱《Excel常用函數(shù)及實例剖析》)
小提示：由于學(xué)號的數(shù)據(jù)類型為文本，而不是數(shù)值，所以在條件“LEFT(A2,1)="5"”中，要對數(shù)字5加引號。下面的情況同理。
3.選中D2單元格，建立根據(jù)學(xué)號得出學(xué)生年級數(shù)據(jù)的公式。在D2單元格中輸入的公式為：“=IF(A2="","",MID(A2,2,2)&"級")”。其中MID(A2,2,2)的含義是，從A2學(xué)號中的第二位開始，提取二個字符。
4.選中E2單元格，建立根據(jù)學(xué)號得出學(xué)生學(xué)院的公式。在E2單元格中輸入的公式為：“=IF(MID(A2,4,3)="110","數(shù)科院",IF(MID(A2,4,3)="111","信息學(xué)院",IF(MID(A2,4,3)="112","外語學(xué)院","無效的學(xué)院代碼")))”。這個公式的含義是：如果代表學(xué)院的字符串為“110”，則返回“數(shù)科院”;若是“111”，則返回“信息學(xué)院”;若是“112”則返回“外語學(xué)院”，若是其他情況，則返回“無效的學(xué)院代碼”。
小提示：這里嵌套使用了IF函數(shù)，由于IF函數(shù)最多只能嵌套七層，因此最多可以根據(jù)代碼返回八個學(xué)院。如果實際情況中需要使用的學(xué)院數(shù)目超過了八個，則可以使用自定義函數(shù)。
由于筆者也完成過類似的任務(wù)，知道位于第一列的學(xué)號是按一定規(guī)律編排的。為了提高工作效率，其實可以根據(jù)這個規(guī)律，結(jié)合Excel的公式和函數(shù)快速地從學(xué)號中提取出相應(yīng)的信息，自動填入到相應(yīng)的單元格中。當(dāng)數(shù)據(jù)量比較大時，這種方法的優(yōu)越性非常明顯。
一、前提條件：了解學(xué)號的編排規(guī)律
利用這個方法的前提條件是學(xué)號是按一定規(guī)律編排的，在實際情況中幾乎都是如此。例如，圖1表格中學(xué)號的編排規(guī)律是：學(xué)號的第一位數(shù)代表生源地區(qū)，如果是“5”就代表“外地生源”，如果是“4”就代表“本地生源”。第二至第三位代表年級，第四至第六位代表學(xué)院，比如“110”代表“數(shù)科院”，“111”代表“信息學(xué)院”等。第七至第八位代表“系別”，比如“24”代表“數(shù)學(xué)系”，“27”代表“計算機(jī)系”等，最后三位代表所在班級的學(xué)號。
二、 使用IF函數(shù)建立公式從學(xué)號中提取信息
1.由于“學(xué)號”的數(shù)據(jù)類型為文本，因此設(shè)置A列的單元格格式的數(shù)據(jù)類型為“文本”。
2.選中C2單元格，建立根據(jù)學(xué)號得出學(xué)生生源信息數(shù)據(jù)的公式。在C2單元格中輸入的公式為：“=IF(A2="","",IF(LEFT(A2,1)="5","外地生源",IF(LEFT(A2,1)="4","本地生源","無效的生源代碼")))”。這個公式的含義是：如果在A2中還沒填入學(xué)號，就返回一個空字符串，如果填入的學(xué)號的第一位字符為“5”，則返回字符“外地生源”，如果第一位字符為“4”，則返回“本地生源”，如果是其他字符，就返回“無效的生源代碼”。輸入公式后，利用公式填充柄復(fù)制公式到下方恰當(dāng)?shù)膯卧瘛：竺嬲埫看屋斎牍胶?，都?fù)制到恰當(dāng)?shù)膯卧瘛?(函數(shù)具體用法請參閱《Excel常用函數(shù)及實例剖析》)
小提示：由于學(xué)號的數(shù)據(jù)類型為文本，而不是數(shù)值，所以在條件“LEFT(A2,1)="5"”中，要對數(shù)字5加引號。下面的情況同理。
3.選中D2單元格，建立根據(jù)學(xué)號得出學(xué)生年級數(shù)據(jù)的公式。在D2單元格中輸入的公式為：“=IF(A2="","",MID(A2,2,2)&"級")”。其中MID(A2,2,2)的含義是，從A2學(xué)號中的第二位開始，提取二個字符。
4.選中E2單元格，建立根據(jù)學(xué)號得出學(xué)生學(xué)院的公式。在E2單元格中輸入的公式為：“=IF(MID(A2,4,3)="110","數(shù)科院",IF(MID(A2,4,3)="111","信息學(xué)院",IF(MID(A2,4,3)="112","外語學(xué)院","無效的學(xué)院代碼")))”。這個公式的含義是：如果代表學(xué)院的字符串為“110”，則返回“數(shù)科院”;若是“111”，則返回“信息學(xué)院”;若是“112”則返回“外語學(xué)院”，若是其他情況，則返回“無效的學(xué)院代碼”。
小提示：這里嵌套使用了IF函數(shù)，由于IF函數(shù)最多只能嵌套七層，因此最多可以根據(jù)代碼返回八個學(xué)院。如果實際情況中需要使用的學(xué)院數(shù)目超過了八個，則可以使用自定義函數(shù)。