1.SUBSTITUTE函數

說明

在文本字符串中用new_text替換old_text。如果需要在某一文本字符串中替換指定的文本，請使用函數SUBSTITUTE；如果需要在某一文本字符串中替換特定位置處的任意文本，請使用函數REPLACE。

語法

SUBSTITUTE(TEXT,old_text,new_text,[instance_num])

SUBSTITUTE函數語法具有下列參數：

text必需。需要替換其中字符的文本，或對含有文本（需要替換其中字符）的單元格的引用。

old_text必需。需要替換的文本。

new_text必需。用于替換old_text的文本。

Instance_num可選。指定要将第幾個old_text替換為new_text。如果指定了instance_num，則隻有滿足要求的old_text被替換。否則，文本中出現的所有old_text都會更改為new_text。

示例

我們在收集學生上交回來的表格時，經常會遇到班級上交過來的表格沒有仔細檢查，導緻裡面有的學号中間加了空格，學号前面加了空格，或者學号後面添加了空格，或者是學号是數值型，不是文本型。我們可以使用SUBSTITUTE函數删除學号中的任意地方的空格，并将數值型數字轉換為文本型數字。

如上圖：

A1單元格的内容為：文本型“2021 04 01 01 01”

A2單元格的内容為：文本型“ 202104010101”

A3單元格的内容為：文本型“202104010101 ”

A4單元格的内容為：數值型“202104010101”

如上圖：

在C1單元格輸入：=SUBSTITUTE(A1," ","")

函數的意思是将A1單元格裡面的所有" "空格内容替換成為""沒有内容。

然後下拉填充到C2，C3，C4單元格。

如上圖：

将C1，C2，C3，C4的内容選中複制，在E1單元格粘貼，粘貼之後選擇

。或者選中E1單元格，直接點擊開始裡面的粘貼（如下圖），選擇粘貼數值的值，就可以在E1，E2，E3，E4裡面得到不包含公式的純文本型學号了。

2.TEXT函數

說明

TEXT函數可通過格式代碼向數字應用格式，進而更改數字的顯示方式。

語法

TEXT(value,format_text)

TEXT函數語法具有下列參數：

value必需。字符串的内容。

format_text必需。指定格式的類型。

示例

我們在收集學生上交回來的表格時，經常會遇到班級上交過來的表格沒有仔細檢查，導緻裡面有的手機号是數值型，有的手機号是字符型。我們可以使用TEXT函數将數值型或字符型的手機号全部轉換為字符型。（針對全部是數字的學号也可以這麼處理）

如上圖：

A1單元格的内容為：數值型“18100000000”

A2單元格的内容為：文本型“18100000000”

如上圖：

在C1單元格輸入：=TEXT(A1,0)

函數的意思是将A1單元格裡面的所有内容轉換為文本型的數字，函數裡面的0就是表示轉換後的都是不含文字的純數字文本型内容。

然後下拉填充到C2單元格。

（和SUBSTITUTE函數的最後一步一樣）将C1，C2的内容選中複制，在E1單元格粘貼，粘貼之後選擇

。或者選中E1單元格，直接點擊開始裡面的粘貼，選擇粘貼數值的值，就可以在E1，E2裡面得到不包含公式的純文本型手機号了。

3.LEFT函數

說明

LEFT 從文本字符串的第一個字符開始返回指定個數的字符。

語法

LEFT(text, [num_chars])

LEFT函數語法具有下列參數：

text必需。字符串的内容。

[num_chars]可選。指定要由LEFT提取的字符的數量。

Num_chars必須大于或等于零。

如果num_chars大于文本長度，則LEFT返回全部文本。

如果省略num_chars，則假定其值為1。

示例

我們想要通過身份證号判斷學生出生在哪個城市。我們可以使用LEFT函數截取身份證号前6位，以此來判斷學生出生在哪個城市。

如上圖：

A1單元格的内容為：文本型“421111198701082222”

如上圖：

在C1單元格輸入：=LEFT(A1,6)

函數的意思是将A1單元格裡面的所有内容從文本字符串的第一個字符開始返回6個數的字符。

（和SUBSTITUTE函數的最後一步一樣）将C1的内容選中複制，在E1單元格粘貼，粘貼之後選擇

。或者選中E1單元格，直接點擊開始裡面的粘貼，選擇粘貼數值的值，就可以在E1裡面得到不包含公式的純文本型的6位數地區代碼了。

4.RIGHT函數

說明

RIGHT根據所指定的字符數返回文本字符串中最後一個或多個字符。

語法

RIGHT(text,[num_chars])

RIGHT函數語法具有下列參數：

text必需。字符串的内容。

num_chars可選。指定希望RIGHT提取的字符數。

Num_chars必須大于或等于零。

如果num_chars大于文本長度，則RIGHT返回所有文本。

如果省略num_chars，則假定其值為1。

示例

我們想要通過身份證号查看身份證後4位。我們可以使用RIGHT函數截取身份證号後4位。

如上圖：

A1單元格的内容為：文本型“421111198701082222”

如上圖：

在C1單元格輸入：=RIGHT(A1,4)

函數的意思是将A1單元格裡面的所有内容返回文本字符串中最後4個字符。

（和SUBSTITUTE函數的最後一步一樣）将C1的内容選中複制，在E1單元格粘貼，粘貼之後選擇

。或者選中E1單元格，直接點擊開始裡面的粘貼，選擇粘貼數值的值，就可以在E1裡面得到不包含公式的純文本型的4位數了。

5.MID函數

說明

MID返回文本字符串中從指定位置開始的特定數目的字符，該數目由用戶指定。

語法

MID(text, start_num, num_chars)

MID函數語法具有下列參數：

text必需。字符串的内容。

start_num必需。文本中要提取的第一個字符的位置。文本中第一個字符的start_num為1，以此類推。

如果start_num大于文本長度，則MID返回空文本。

如果start_num小于文本長度，但start_num加num_chars超過文本長度，則MID将返回直到文本末尾的字符。

如果start_num小于1，則MID返回#VALUE！錯誤值。

num_chars必需。指定希望MID從文本中返回字符的個數。

如果num_chars為負數，則MID返回#VALUE！錯誤值。

示例

我們想要通過身份證号查看出生日期8位。我們可以使用MID函數截取身份證号日期8位。

如上圖：

A1單元格的内容為：文本型“421111198701082222”

如上圖：

在C1單元格輸入：=MID(A1,7,8)

函數的意思是将A1單元格裡面的所有内容返回從第7位開始的8位字符。

（和SUBSTITUTE函數的最後一步一樣）将C1的内容選中複制，在E1單元格粘貼，粘貼之後選擇

。或者選中E1單元格，直接點擊開始裡面的粘貼，選擇粘貼數值的值，就可以在E1裡面得到不包含公式的純文本型的8位出生日期代碼了。

6.IF函數

說明

IF函數是Excel中最常用的函數之一，它可以對值和期待值進行邏輯比較。因此IF語句可能有兩個結果。

語法

IF(條件, 結果1, 結果2)

IF函數語法具有下列參數：

條件：必需。

結果1：必需。可以返回文本或者數字，由用戶指定。

結果2：必需。可以返回文本或者數字，由用戶指定。

示例

我們在收集學生上交回來的表格時，經常會遇到班級上交過來的表格沒有仔細檢查，導緻裡面有的姓名有可能是同音字或者相似的其他字。我們可以使用IF函數來判斷姓名是否填寫錯誤。

如上圖：

A1單元格的内容為：文本型“王芯蕊”

A2單元格的内容為：文本型“張炎焱”

C1單元格的内容為：文本型“王芯芯”

C2單元格的内容為：文本型“張炎焱”

如上圖：

在E1單元格輸入：=IF(A1=C1,"正确","錯誤")

函數的意思是如果A1單元格和C1單元格裡面的内容是一樣的，就返回正确兩個字，如果不一樣，就返回錯誤兩個字。這裡的函數也可以寫成=IF(A1=C1,1,0)，表示如果A1單元格和C1單元格裡面的内容是一樣的，就返回1，如果不一樣，就返回0。

7.VLOOKUP函數

說明

需要在表格或區域中按行查找内容時，請使用 VLOOKUP。 例如，按學号查找學生的姓名。

語法

VLOOKUP(要查找的内容,要查找的位置,包含要返回的值的範圍内的列号,FALSE)。

示例

我們在收集學生上交回來的表格時，可能隻收集到了學号，姓名，沒有收集到手機号。我們可以通過VLOOKUP函數從已有的學生學籍信息中查找并且添加手機号信息。

如上圖：

我們這次報名活動僅僅收集到了A列學号，B列姓名，沒有收集到手機号信息。而我們手上已有的另外一張學生學籍信息表中有學生的F列學号，G列姓名，H列班級，I列手機号等信息。

如上圖：

在D1單元格輸入：=VLOOKUP(A1,F:I,4,FALSE)

函數的意思是通過A1單元格（要查找的内容），在F到I列（要查找的位置）中查找學号為A1的學生的信息，查詢到後返回第4項（包含要返回的值的範圍内的列号）手機号信息。FALSE表示是精确查找，A1的學号必須和F裡面的學号一模一樣才能認為是查找成功。

★注意事項：

A.要查找的内容：必須是唯一不能重複的值。比如本案例當中的A1學号項目，每個學生隻能有唯一一個學号，不能有多個學号，而且每個人的學号都不重複。所以不能用姓名作為查找内容，因為姓名有可能出現多位同學重名的情況。身份證号是唯一的，所以可以作為要查找的内容。

B.要查找的位置：第一列必須與要查找的内容一緻，比如我們要通過學号來查找，要查找的位置第一列就必須是學号。比如我們要通過身份證号來查找，要查找的位置第一列就必須是身份證号。

C.包含要返回的值的範圍内的列号：需要通過學号查找該生的什麼信息，這裡就填寫對應的列數。比如需要通過學号查找該生的手機号，那麼我們看要查找的位置當中第一列是學号，第二列是姓名，第三列是班級，第四列是手機号，因此這裡填4。如果我們需要獲得該生的班級号，那麼第三列時班級号，就填3。

D.FALSE表示是精确查找，A1的學号必須和F裡面的學号一模一樣才能認為是查找成功。

E.返回值如果是#N/A，第一種原因是表示收集上來的表格中學号信息有錯誤或者學号沒有錯誤，但是和後面的學生學籍信息庫裡面的學号文本類型不同，需要先通過SUBSTITUTE函數删除空格并将學号轉為文本型。第二種原因是表示後面的學生學籍信息庫裡面沒有該生的信息，需要從學生基本信息中導出全部學生的學籍信息。

然後下拉填充到D2，D3，D4單元格。

（和SUBSTITUTE函數的最後一步一樣）将D1、D2、D3、D4的内容選中複制，在C1單元格粘貼，粘貼之後選擇

。或者選中C1單元格，直接點擊開始裡面的粘貼，選擇粘貼數值的值，就可以在C1裡面得到不包含公式的純文本型的手機号碼了。

8.IF和VLOOKUP函數聯合使用

示例

我們在收集學生上交回來的表格時，收集到了學号，姓名兩項信息。我們可以通過IF和VLOOKUP函數聯合查看收集上來的表格中學生姓名是否正确。

如上圖：

我們這次報名活動收集到了A列學号，B列姓名。而我們手上已有的另外一張學生學籍信息表中有學生的F列學号，G列姓名，H列班級，I列手機号等信息。

如上圖：

在D1單元格輸入：=IF(B1=VLOOKUP(A1,F:I,2,FALSE),1,0)

這裡有兩個函數：

第一個函數：VLOOKUP(A1,F:I,2,FALSE)，意思是通過A1單元格（要查找的内容），在F到I列（要查找的位置）中查找學号為A1的學生的信息，查詢到後返回第2項（包含要返回的值的範圍内的列号）姓名信息。FALSE表示是精确查找，A1的學号必須和F裡面的學号一模一樣才能認為是查找成功。

第二個函數：=IF(B1=VLOOKUP(A1,F:I,2,FALSE),1,0)，意思是如果B1單元格的内容和通過第一個函數查找返回的姓名一緻，則在D1裡面顯示為1，如果不一緻則顯示為0。

然後下拉填充到D2，D3單元格。

我們發現D3單元格顯示為0，說明本次信息收集上來的該生的姓名有誤。我們可以通過在E3單元格輸入第一個函數=VLOOKUP(A3,F:I,2,FALSE)，查看到學籍信息中，該生的姓名為王五，而我們本次報名活動收集到該生的姓名為王伍，多了一個單人旁，需要改成正确的姓名。

總結，VLOOKUP函數是我們在進行各種表格填寫時用的最多的函數，如果各位老師和同學能掌握該函數的使用方法，勢必會保證上交上來的各種表格，學生的學籍信息不回出現任何問題。因為導出的學籍信息中學号、姓名、身份證号、學院、專業、班級等這些數據是正确的，是不會出問題的。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!