第一篇：EXCEL查找與引用函數VLOOKUP的應用法及實例 詳解

EXCEL查找與引用函數VLOOKUP的應用法及實例詳解

在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。當比較值位于數據表首列時，可以使用函數 VLOOKUP 代替函數 HLOOKUP。

在 VLOOKUP 中的 V 代表垂直。語法

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP（查找XX，在YY區域中，與查找值相對的第ZZ列的值，方式）lookup_value

為需要在數組第一列中查找的數值。Lookup_value 可以為數值、引用或文本字符串。

如下圖所示，C3單元格即為lookup_value

名稱的引用，例如數據庫或列表。

如下圖所示：C6：F10即為

table_array

為需要在其中查找數據的數據表。可以使用對區域或區域

table_array

col_index_num

為table_array中待返回的匹配值的列序號。col_index_num為1時，返回table_array第一列中的數值； col_index_num為2，返回table_array第二列中的數值，以此類推。如果col_index_num小于1，函數VLOOKUP返回錯誤值值#VALUE!； 如果col_index_num大于table_array的列數，函數VLOOKUP返回錯誤值#REF!。

如下圖所示，4表示數據表的第4列，col_index_num

range_lookup

如果 range_lookup 為 TRUE，則 table_array 的第一列中的數值必須按升序排列：?、-

2、-1、0、1、2、?、-Z、FALSE、TRUE；否則，函數 VLOOKUP 不能返回正確的數值。如果 range_lookup 為 FALSE，table_array 不必進行排序。

（通過在“數據”菜單中的“排序”中選擇“升序”，可將數值按升序排列。）為了方便起見，一般選用 FALSE，即表格不必要再按升序又排列一次。

table_array 的第一列中的數值可以為文本、數字或邏輯值。文本不區分大小寫。

如下圖所示，FALSE 即為range_lookup表示第一列的數值不必進行排序

range_lookup 為一邏輯值，指明函數 VLOOKUP 返回時是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于 lookup_value 的最大數值；如果 range_value 為 FALSE，函數 VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。

說明

如果函數 VLOOKUP 找不到 lookup_value，且 range_lookup 為 TRUE，則使用小于等于 lookup_value 的最大值。

VLOOKUP 返回錯誤值 #N/A。

如果 lookup_value 小于 table_array 第一列中的最小數值，函數

如果函數 VLOOKUP 找不到 lookup_value 且 range_lookup 為 FALSE，函數 VLOOKUP 返回錯誤值 #N/A。

----------------------------對于可能產生錯誤值的防范 將公式進行以下修改

=IF(C3=“",”查詢值為空，請填寫查詢值“,IF(ISERROR(VLOOKUP(C3,C6:F10,4,FALSE))=TRUE,”請檢查參數是否正確",VLOOKUP(C3,C6:F10,4,FALSE)))如下圖所示，如果查找時未指定名稱，則公式的計算結果為“查詢值為空，請填寫查詢值”

如下圖所示，因使用的是FALSE，精確查找，當查找值為“西瓜”時，因該值不在列表中，會產生#N/A 錯誤，但使用公式用，可提示有參數錯誤

上述公式，使用了函數ISERROR檢查是否有錯誤值，如果有興趣你也可以使用函數ERROR.TYPE返回錯誤值對應的數字，從而明確顯示是哪一類型的錯誤，錯誤值對應的數字如下表所示

呵呵，關于VLOOKUP就介紹到這里，祝大家工作愉快！

第二篇：關于VLOOKUP函數的用法

關于VLOOKUP函數的用法

“Lookup”的漢語意思是“查找”，在Excel中與“Lookup”相關的函數有三個：VLOOKUP、HLOOKUO和LOOKUP。下面介紹VLOOKUP函數的用法。

一、功能

在表格的首列查找指定的數據，并返回指定的數據所在行中的指定列處的數據。

二、語法

標準格式：

VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

三、語法解釋

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以寫為：

VLOOKUP(需在第一列中查找的數據,需要在其中查找數據的數據表,需返回某列值的列號,邏輯值True或False)

1.Lookup_value為“需在數據表第一列中查找的數據”，可以是數值、文本字符串或引用。

2.Table_array 為“需要在其中查找數據的數據表”，可以使用單元格區域或區域名稱等。

⑴如果 range_lookup 為 TRUE或省略，則 table_array 的第一列中的數值必須按升序排列，否則，函數 VLOOKUP 不能返回正確的數值。

如果 range_lookup 為 FALSE，table_array 不必進行排序。

⑵Table_array 的第一列中的數值可以為文本、數字或邏輯值。若為文本時，不區分文本的大小寫。

3.Col_index_num 為table_array 中待返回的匹配值的列序號。

Col_index_num 為 1 時，返回 table_array 第一列中的數值；

Col_index_num 為 2 時，返回 table_array 第二列中的數值，以此類推。

如果Col_index_num 小于 1，函數 VLOOKUP 返回錯誤值 #VALUE!；

如果Col_index_num 大于 table_array 的列數，函數 VLOOKUP 返回錯誤值 #REF!。

4.Range_lookup 為一邏輯值，指明函數 VLOOKUP 返回時是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value 的最大數值；如果 range_value 為 FALSE，函數 VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。

四、應用例子

A B C D編號 姓名 工資 科室2005001 周杰倫 2870 辦公室2005002 蕭亞軒 2750 人事科2005006 鄭智化 2680 供應科2005010 屠洪剛 2980 銷售科2005019 孫楠 2530 財務科2005036 孟庭葦 2200 工 會

A列已排序（第四個參數缺省或用TRUE）

VLOOKUP(2005001,A17,2,TRUE)等于“周杰倫”

VLOOKUP(2005001,A17,3,TRUE)等于“2870”

VLOOKUP(2005001,A17,4,TRUE)等于“辦公室”

VLOOKUP(2005019,A1:D7,2,TRUE)等于“孫楠”

VLOOKUP(2005036,A1:D7,3,TRUE)等于“2200”

VLOOKUP(2005036,A1:D7,4,TRUE)等于“工 會”

VLOOKUP(2005036,A1:D7,4)等于“工 會”

若A列沒有排序，要得出正確的結果，第四個參數必須用FALAEVLOOKUP(2005001,A1:D7,2,FALSE)等于“周杰倫”VLOOKUP(2005001,A1:D7,3,FALSE)等于“2870”

VLOOKUP(2005001,A1:D7,4,FALSE)等于“辦公室”VLOOKUP(2005019,A1:D7,2,FALSE)等于“孫楠”VLOOKUP(2005036,A1:D7,3,FALSE)等于“2200”

VLOOKUP(2005036,A1:D7,4,FALSE)等于“工 會”

五、關于TRUE和FALSE的應用

先舉個例子，假如讓你在數萬條記錄的表格中查找給定編號的某個人，假如編號已按由小到大的順序排序，你會很輕松地找到這個人；假如編號沒有排序，你只好從上到下一條一條地查找，很費事。

用VLOOKUP查找數據也是這樣，當第一列已排序，第四個參數用TRUE（或確?。?，Excel會很輕松地找到數據，效率較高。當第一列沒有排序，第四個參數用FALSE，Excel會從上到下一條一條地查找，效率較低。

筆者覺得，若要精確查找數據，由于計算機運算速度很快，可省略排序操作，直接用第四個參數用FALSE即可。

最近愛上了VLOOKUP，有人還對它進行了更新。因為它的漏洞就是只能返回重復值得第一個值。下面就詳細來敘述一下吧！

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value 為需要在Table_array第一列中查找的數值。

可以為數值、引用或文本字符串。需要注意的是類型必須與table_array第一列的類型一致。查找文本時，文本不區分大小寫；可以使用通配符“*”、“?”。

Table_array 為需要在其中查找數據的數據表。

可以使用對區域或區域名稱的引用、常數數組、計算后的內存數組。對區域引用時，可以引用整列,excel會自動判斷使用區域。

該參數的第一列必須包含查找的內容，其它列包含需返回的內容；返回內容的列序號由下個參數指定。

Col_index_num 為table_array中待返回的匹配值的列序號。

如為1時，返回table_array第一列中的數值；為2，返回table_array第二列中的數值，以此類推。

如果col_index_num小于1，函數 VLOOKUP 返回錯誤值值 #VALUE!；

如果col_index_num大于table_array的列數，函數 VLOOKUP 返回錯誤值 #REF!。

Range_lookup 為一邏輯值，指明函數VLOOKUP返回時是精確匹配還是近似匹配。

如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數值；

近似匹配查詢一般用于數值的查詢，table_array的第一列必須按升序排列；否則不能返回正確的結果。

如果range_value為FALSE（或0），函數VLOOKUP將返回精確匹配值。

此時，table_array不必進行排序。如果找不到，則返回錯誤值#N/A；可isna檢測錯誤后使用if判斷去除錯誤信息。

VLOOKUP 經常會出現錯誤的#N/A,下面是幾種可能性： 數據有空格或者數據類型不一致。

可以在lookup_value 前用TRIM（）將空格去除。

如果格式不一致，可以將數值強制轉換成文本，lookup_value之后用&跟""表示的空字符串。將文本轉換成數值，lookup_value*1進行運算。假設有以下兩個表格：表1：

姓名 身份證 學科 成績

張三 310111 物理 A

李斯 310112 化學 B王五 310113 政治 A趙六 310114 物理 B

張三 310111 政治 C

趙六 310114 語文 B劉備 310115 英語 D

表2：

姓名 身份證 住址 學科 成績

曹操 319723 陜西馬超 310197 甘肅趙云 319784 黑龍江

李斯 310112 浙江劉備 310115 江蘇張三 310111 上海

請問：如何快速的將表2的學科和成績自動填入？直接使用VLOOKUP即可。示例如下：

=VLOOKUP($B1,Sheet1!$B$1C$5,2,FALSE)

涵義：在Sheet1的單元格區間B1:C5，查找符合條件為：第一列與當前工作表單元格B1的值相等的第二列的值。

$B1 條件值

Sheet1!$B$1C$5 要查找區間（注意：請使用絕對引用）2 查找的結果為區域中的第幾列FALSE 表示精確查找

*具體應用中大家注意各個參數的意義！調整參數來滿足個人需要。

第三篇：EXCEL中如何使用VLOOKUP函數查找引用其他工作表數據和自動填充數據

EXCEL中如何使用VLOOKUP函數查找引用其他工作表數據和自動填充數據

如何在EXCEL中對比兩張表（不是對比兩列）？

兩張都是人員在職信息表，A表長，B表短，A表中的記錄比較多，有的人A表中有而B表中沒有，有的人AB兩表都有但是在A表中的行數比B表中多（舉例說明，就是這個人在A表中可能有三行，分別是7.8.9三月的在職信息，同樣的人在B表中可能只有7月一個月的在職信息），如何把A表中有而B表中沒有的行挑選出來單列成一張表？

假設姓名在A列,在職月份在B列,兩個表的第一行都是表頭.在B表插入一個新A列,這樣B表的姓名就在B列，月份在C列,在A2單元格輸入 =B2&C2

在A表表頭的最后一個空白列(假設為H1)寫上“與B表的關系”

在H2輸入公式 =IF(ISERROR(VLOOKUP(A2&B2,Sheet2!A:A,1,FALSE)),“B表沒有此記錄”,“B表有此記錄”)如何在EXCEL中篩選出相同的名字？

我現在有2張表：一張有1000個用戶，另一張有800個用戶；如何快速的找出兩張表中相同的名字啊。方法

一、sheet!b1輸入 =IF(COUNTIF(Sheet2!$A$1:$A$1000,A1)>=1,“重複”,“")

方法

二、在1000個用戶的sheet1!B1輸入（假設你的記錄在A1而且是豎列擴展）

=if(isna(vlookup(a1, sheet2$a$1:$a$800,2,0)), ” “, ”重復“)

兩列數據查找相同值對應的位置

=MATCH(B1,A:A,0)

EXCEL中如何使用VLOOKUP函數查找引用其他工作表數據和自動填充數據

VLOOKUP函數，在表格或數值數組（數據表）的首列查找指定的數值（查找值），并由此返回表格或數組當前行中指定列（列序號）處的數值。

VLOOKUP(查找值，數據表，列序號，[匹配條件]）

例如在SHEET2表中有全部100個學生的資料，B列為學號、C列為姓名、D列為班級，現在在SHEET1表的A列有學號，我們需要使用該函數，將SHEET2表中對應學號的姓名引用到SHEET1表的B列。我們只需在SHEET1的B2輸入以下公式 =VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)（或者=VLOOKUP(A2,SHEET2!$B$2:$D$101,2,0)，就得到了A2單元格學號對應的學生姓名。同理, 在SHEET1表的C2輸入公式 =VLOOKUP(A2,SHEET2!$B:$D,3,FALSE),即可得到對應的班級.VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)四個參數解釋

1、“A2”是查找值，就是要查找A2單元格的某個學號。

2、“SHEET2!$B:$D”是數據表，就是要在其中查找學號的表格，這個區域的首列必須是學號。

3、“2”表示我們最后的結果是要“SHEET2!$B:$D”中的第“2”列數據，從B列開始算第2列。

4、“FALSE”（可以用0代替FALSE）是匹配條件，表示要精確查找，如果是TRUE表示模糊查找。如果我們需要在輸入A列學號以后,B列與C列自動填充對應的姓名與班級,那么只需要在B列,C列預先輸入公式就可以了。為了避免在A列學號輸入之前,B列與C列出現“#N/A”這樣錯誤值,可以增加一個IF函數判斷A列是否為空,非空則進行VLOOKUP查找.這樣B2與C2的公式分別調整為

B2=IF(A2=“",”“,VLOOKUP(A2,SHEET2!$B:$D,2,0))

Model=IF(G311=”“,”“,VLOOKUP(G311,數據表!$B:$I,4,FALSE))

C2=IF(A2=”“,”“,VLOOKUP(A2,SHEET2!$B:$D,3,0))

Excel課表生成中應用的兩種方法

課表是學校最基本的教學管理依據，課表形成的傳統方法是先安排好原始數據，再設計好表格的固定格式，一項項往表里填內容。上百張課表的形成都要人工錄入或人工粘貼復制，既繁瑣又容易出差錯，而且不利于檢索查詢。筆者介紹一種方法，在原始數據錄入后利用“數據透視表”，可以實現課表生成的自動化。

一、功能

1.一張“數據透視表”僅靠鼠標移動字段位置，即可變換出各種類型的課表，例如：班級課表。每班一張一周課程表?？蛇x框內選擇不同的學院和班號，即可得到不同班的課表。按教師索引。即每位教師一周所有的信息。按時間索引，即每天每節課有哪些教師來、上什么課。按課程索引。課程帶頭人可能只關心和自己有關的內容。按學院索引?？赡苤恍枰獌扇棓祿?，了解概況。按本?？扑饕０礃菍铀饕?。專家組聽課時順序走過每個教室，需要隨時隨地查看信息。按教室或機房索引。安排房間時要隨時查看。

2.字段數量的選擇是任意的，即表格內容可多可少，隨時調整。

3.任何類型的表都能夠實現連續打印或分頁打印。如班級課表可以連續顯示，也可快速、自動生成每班一張；某部門所有教師的課表可以匯總在一張表上，也可每個老師一頁紙，分別打印。

4.遇到調課，只要更改原始表，再重新透視一次，可在瞬間完成，就意味著所有表的數據都已更新。而傳統的方法必須分別去改班級表、教室表、機房表、教師表……稍有疏忽就可能遺漏。

5.所有的表都不用設計格式，能夠自動形成表格，自動調整表格大小，自動合并相同數據單元格。

二、建立數據庫

規范數據庫的建立是滿足查詢、檢索、統計功能的基本要求。

1.基本字段：班級、星期、節次、課程、地點、教師。

2.可選字段：學院、班級人數、學生類別、金工實習周次、教師單位、地點屬性、備注字段名橫向排列形成了“表頭”，每個字段名下是縱向排列的數據。

3.庫中的數據必須規范。如“地點”中不能出現除樓號、房間號以外的任何文字(包括空格)；“課程”中必須是規范的課程名，不允許有“單、雙”等字樣。建議上機課增加一個字段“上機”，而不是在課程名中增添“上機”說明，后者不利于課程檢索。

4.庫中的每條數據清單的每個格只要存在數據就必須填滿。不允許因為與上一行數據相同就省略了，更不能合并單元格。

5.增加的整條記錄在庫中的位置可以任意。如規律課表的課程只有8節，某班增加“9～10節”或雙休日上課，新增記錄則可插在該班其他課的末尾，也可附在庫的最底端。無論在什么位置，都不影響透視后的效果。

三、做數據透視表

在選擇透視范圍時要包含全部原始數據庫，如果錄制“宏”，最好比原始表多增加若干行，以備增加記錄用。但字段的數量可根據需要選擇。把選中的字段分別放置在表的“行字段”中，在每個字段名上雙擊，彈出“字段設置”框，選擇“無”，即形成了顯示美觀的透視表。

1.用鼠標拖動各字段，重新安排左右順序、上下位置(指行字段與頁字段之間的轉換)，或在可選框內選中所需，即可形成各種各樣的新表。

2.常用的班級課表可排好紙張版面、頁眉頁腳，專門供原始打印?！鞍嗉墶弊侄巫詈梅旁凇绊撟侄巍敝?，以便于每班打印1張。在“班級”字段的可選框內選擇各班，即可顯示出所有的班級課表。每班課表的大小是自動調整的，如 “節次”中的數據項只有8節，遇到增添“9～10節”課程的情況，表格會在7～8節后自動增加1行，把9～10節的內容填進去，下一個班則可自動恢復正常。既可以設置為無課顯示空格，也可以設成無課不顯示，即有哪節顯示哪節。

Excel 2003查找重復姓名方法兩則

每次統計年級學生基本情況時都會因為學生姓名相同而導致張冠李戴的錯誤。以往為避免類似錯誤都要將Excel表格按姓名進行排序，然后依次檢查是否重名，非常麻煩還容易出問題。如果您也遇到過類似情況，那么在Excel中，我們可以采用以下的方法來區分那些有重復的姓名，以避免出錯。

一、利用條件格式進行彩色填充

選中圖1所示表格中數據所在單元格區域A2:I11，點擊功能區“開始”選項卡“樣式”功能組中的“條件格式”按鈕，在彈出的菜單中點擊“新建規則”命令，打開“新建格式規則”對話框，在“選擇規則類型”列表中點擊“使用公式確定要設置格式的單元格”，然后在“為符合此公式的值設置格式”下方的輸入框中輸入如下公式“=COUNTIF($B$2:$B$11,$B2)>=2”，然后點擊下方的“格式”按鈕，在打開的“設置單元格格式”對話框的“填充”選項卡中指定一種填充顏色，確定后如圖2所示。

確定后關閉此對話框，則可以將重名同學所在行的全部數據都填充此顏色，如圖3所示。有了此醒目的標志，那么我們在以后的操作中就不太容易出錯了。

查找數據公式兩個(基本查找函數為VLOOKUP,MATCH)

（1）、根據符合行列兩個條件查找對應結果

=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)

（2）、根據符合兩列數據查找對應結果(為數組公式)

=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0)

使用 INDEX 函數和 MATCH 函數查找數據

假設您在單元格 A1:C5 中創建了以下信息表，且此表包含單元格 C1:C5 中的年齡(Age)信息：

假設您希望根據某人的姓名(Name)查找此人的年齡(Age)。為此，請按如下公式示例，配合使用 INDEX 函

數和 MATCH 函數：

=INDEX($A$1:$C$5, MATCH(”Mary“,$A$1:$A$5,),3)

此公式示例使用單元格 A1:C5 作為信息表，并在第三列中查找 Mary 的年齡(Age)。公式返回 22 一些Excel公式的實用運用例子 =COUNTIF(D2:D10,”>400“)

統計D2:D10的值大于400的個數

=COUNTIF(B2:B10,”東北部“)

統計B2:B10的內容為”東北部“的個數

=TODAY()

顯示當前系統日期

=NOW()

顯示當前系統日期和具體時間

=YEAR(B2)

獲得B2單元格內(當前系統日期和具體時間)的年

=MONTH(B2)

獲得B2單元格內(當前系統日期和具體時間)的月

=DAY(B2)

獲得B2單元格內(當前系統日期和具體時間)的日

=HOUR(B2)

獲得B2單元格內(當前系統日期和具體時間)的時

=RANK(D2,$D$2:$D$10)

取D2的值在D2-D10范圍內的排名是多少

=MATCH(99,C2:C10,0)

統計出C2-C10范圍內值為99的個數

=EXACT(A4,B4)

比較A4,B4兩個單元格內的字符串內容是否相等，返回布爾值TRUE/FALSE

=IF(C2>=60,IF(C2>=90,”優秀“,”及格“),”不及格“)

如果C2>=60(如果C2>=90則顯示”優秀“否則顯示”及格“)否則顯示”不及格“

=IF(AND(B2>=60,C2>=60),IF(OR(B2>=90,C2>=90),”優秀“,”及格“),”不及格“)

與上例相似，只不過是2個單元格都要進行條件判斷

=VLOOKUP(B3,D2:G14,4,0)

VLOOKUP（需在第一列中查找的數值,需要在其中查找數據的數據表,需返回某列值的列號,邏輯值True或False）

經常用Excel建立一些表格，有時我們需要給一些表格建立很多個副表，那么如何使這些復制表格中的數據隨原表的修改而修改呢？VLOOKUP函數可以幫我們做到這一點

=HLOOKUP(B7,B1:F3,2,0)

HLOOKUP與VLOOKUP

HLOOKUP用于在表格或數值數組的首行查找指定的數值，并由此返回表格或數組當前列中指定行處的數值。VLOOKUP用于在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。

當比較值位于數據表的首行，并且要查找下面給定行中的數據時，請使用函數 HLOOKUP。

當比較值位于要進行數據查找的左邊一列時，請使用函數 VLOOKUP。

語法形式為：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中，Lookup_value表示要查找的值，它必須位于自定義查找區域的最左列。Lookup_value 可以為數值、引用或文字串。

Table_array查找的區域，用于查找數據的區域，上面的查找值必須位于這個區域的最左列?？梢允褂脤^域或區域名稱的引用。

Row_index_num為 table_array 中待返回的匹配值的行序號。Row_index_num 為 1 時，返回 table_array 第一行的數值，row_index_num 為 2 時，返回 table_array 第二行的數值，以此類推。

Col_index_num為相對列號。最左列為1，其右邊一列為2，依此類推.Range_lookup為一邏輯值，指明函數 HLOOKUP 查找時是精確匹配，還是近似匹配。

檢查單元格 A2 是否為空白(FALSE)=ISBLANK(A2)

檢查 #REF!是否為錯誤值(TRUE)=ISERROR(A4)

檢查 #REF!是否為錯誤值 #N/A(FALSE)=ISNA(A4)

檢查 #N/A 是否為錯誤值 #N/A(TRUE)=ISNA(A6)

檢查 #N/A 是否為錯誤值(FALSE)=ISERR(A6)

檢查 10.72 是否為數值(TRUE)=ISNUMBER(A5)

檢查 COUNTRY 是否為文本(TRUE)=ISTEXT(A3)

檢查 5 是否為偶數ISEVEN(5)FALSE

檢查-1 是否為奇數ISODD(-1)TRUE

2.如何去掉execl單元格中文字前面的數字?

自己寫個函數放在模塊里，然后在單元格調用函數=delnum(A1)

Public Function delnum(zifu As String)As String

Dim l As Integer, m As Integer, n As String, a As String

l = Len(zifu)

For m = 1 To l

n = Mid(zifu, m, 1)

If Asc(n)< 48 Or Asc(n)> 57 Then

a = a & n

End If

Next m

delnum = a

End Function

3.excel中,列很多,行很少,怎么能讓打印在一頁上?

使用公式先進行一下轉換就是了。

以下為示例：

源數據為數據區域A1:O2，即一個2行15列的數據，如下：

ABCDEFGHIJKLMNO

***131415

先使用公式轉變為6行5列的數據，公式如下：

[假設我們在A6單元格開始輸入公式，轉變后的數據區域為A6:E11]

在單元格A6輸入以下公式：

=INDIRECT(ADDRESS(IF(MOD(ROW(),2)=0,1,2),IF(MOD(COLUMN(),5)=0,5,MOD(COLUMN(),5))+INT((ROW()-6)/2)*5))

并將該公式復制到數據區域A6:E11，我們可以看到，現在數據已經進行了轉換。

結果為：

ABCDE

12345

FGHIJ

678910

KLMNO

1112131415

公式說明：

1.由于假定從單元格A6開始，因此IF(MOD(ROW(),2)=0,1,2)的結果為若為偶數行則指向第一行，否則指向第二行。

2.MOD(COLUMN(),5)由于示例中指定了為5列。

3.INT((ROW()-6)/2)*5)，示例中是從A6單元格開始的，因此減6行，5為列數。

附加：如果不是正好滿列數，那么應該進行一次判斷，如下：

=If(Indirect(...)=”“,”“,Indirect(...))

[Indirect(...)即上面示例中的公式]

5.excel里A列為身份證號碼，要求在B列得出其出身日期？

A列為個人的身份證號或企業代碼，身份證包括2類：15位的身份證，18位身份證。15位（***）的身份證的生日為1956-10-20；18位（***053）的身份證生日為1974-03-15。企業代碼不滿足15位或18位。

現在要求在B列得到A列身份證號人的出生日期；若是企業代碼的不需要。

=if(len(A1)=15,”19“ & mid(A1,7,2)& ”-“ & mid(A1,9,2)& ”-“ & mid(A1,11,2),mid(A1,7,4)& ”-“ & mid(A1,11,2)& ”-“ & mid(A1,13,2))

為15位時,應該沒2000年后出生的吧所以,以上應該行得通,試試看

當A列是企業代碼時,公式有問題.如:A1=2***10,得到的是3123-32-10

公式上做了點修改.=IF(OR(LEN(A1)={15,18}),IF(LEN(A1)=15,”19“ & MID(A1,7,2)& ”-“ & MID(A1,9,2)& ”-“ & MID(A1,11,2),MID(A1,7,4)& ”-“ & MID(A1,11,2)& ”-“ & MID(A1,13,2)),”“)

=IF(LEN(A1)=15,”19“ & MID(A1,7,2)& ”-“ & MID(A1,9,2)& ”-“ & MID(A1,11,2),IF(LEN(A1)=18,MID(A1,7,4)& ”-“ & MID(A1,11,2)& ”-" & MID(A1,13,2),A1))

當A列是企業代碼時,返回原企業代碼

第四篇：EXCEL中如何使用VLOOKUP函數查找引用其他工作表數據和自動填充數據

EXCEL中如何使用VLOOKUP函數查找引用其他工作表數據和自動填

充數據

2010-06-15 10:01:38|分類： 標簽： |字號大中小 訂閱

VLOOKUP函數，在表格或數值數組（數據表）的首列查找指定的數值（查找值），并由此返回表格或數組當前行中指定列（列序號）處的數值。

VLOOKUP(查找值，數據表，列序號，[匹配條件]）

例如在SHEET2表中有全部100個學生的資料，B列為學號、C列為姓名、D列為班級，現在在SHEET1表的A列有學號，我們需要使用該函數，將SHEET2表中對應學號的姓名引用到SHEET1表的B列。我們只需在SHEET1的B2輸入以下公式 =VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)（或者=VLOOKUP(A2,SHEET2!$B$2:$D$101,2,0)，就得到了A2單元格學號對應的學生姓名。同理, 在SHEET1表的C2輸入公式 =VLOOKUP(A2,SHEET2!$B:$D,3,FALSE),即可得到對應的班級.VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)四個參數解釋

1、“A2”是查找值，就是要查找A2單元格的某個學號。

2、“SHEET2!$B:$D”是數據表，就是要在其中查找學號的表格，這個區域的首列必須是學號。

3、“2”表示我們最后的結果是要“SHEET2!$B:$D”中的第“2”列數據，從B列開始算第2列。

4、“FALSE”（可以用0代替FALSE）是匹配條件，表示要精確查找，如果是TRUE表示模糊

查找。

如果我們需要在輸入A列學號以后,B列與C列自動填充對應的姓名與班級,那么只需要在B列,C列預先輸入公式就可以了。為了避免在A列學號輸入之前,B列與C列出現“#N/A”這樣錯誤值,可以增加一

個IF函數判斷A列是否為空,非空則進行VLOOKUP查找.這樣B2與C2的公式分別調整為

B2=IF(A2=“",”“,VLOOKUP(A2,SHEET2!$B:$D,2,0))

C2=IF(A2=”“,”",VLOOKUP(A2,SHEET2!$B:$D,3,0))

點擊下面標簽查看演示動畫:

利用VLOOKUP函數查找引用其他工作表數據

利用VLOOKUP函數自動填充數據

第五篇：COUNTIF函數的用法及實例

Excel統計函數COUNTIF的用法及實例

一：首先介紹COUNTIF函數的用法

英文通用格式是：COUNTIF（range,criteria)

中文通用格式是：COUNTIF（數據區域,條件表達式)=COUNTIF（A$2：A$55，A2）(注釋：A2單元格在A2：A55區域中出現的次數（0：沒有；1：出現1次；2：出現2次。）)

1、返加包含值12的單元格數量

=COUNTIF（range,12）

2、返回包含負值的單元格數量

20-屯昌電視臺MSTP（總表處理后重復303條

=COUNTIF（range，“<0”）

3、返回不等于0的單元格數量

=COUNTIF（range，“<>0”）

4、返回大于5的單元格數量

=COUNTIF（range，“>5”）

5、返回等于單元格A1中內容的單元格數量

=COUNTIF（range，A1）

6、返回大于單元格A1中內容的單元格數量

=COUNTIF（range，“>”&A1）

7、返回包含文本內容的單元格數量

=COUNTIF（range，“*”）

8、返回包含三個字符內容的單元格數量

=COUNITF（range，“???”）

9、返回包含單詞“GOOD”(不分大小寫)內容的單元格數量

=COUNTIF（range，“GOOD”）

10、返回在文本中任何位置包含單詞“GOOD”字符內容的單元格數量

=COUNTIF（range，“*GOOD*”）

11、返回包含以單詞“AB”(不分大小寫)開頭內容的單元格數量

=COUNTIF（range，“AB*”）

12、返回包含當前日期的單元格數量

=COUNTIF（range，TODAY()）

13、返回大于平均值的單元格數量

=COUNTIF（range，“>”&AVERAGE(DATA))

14、返回平均值上面超過三個標準誤差的值的單元格數量

=COUNTIF（range，“>"&AVERAGE(DATA)+STDEV(DATA)*3)

15、返回包含值為或-3的單元格數量

=COUNTIF（range，3）+COUNIF（DATA，-3）

16、返回包含值;邏輯值為TRUE的單元格數量

=COUNTIF（range，TRUE）

二：COUNTIF函數應用舉例

假設存在如下圖的學生成績表，現在，讓我們一起來使用COUNTIF函數進行統計，下面統計的是不及格的人數！

如下圖，首先，選擇F列中的F3單元格，之后，在其上方fx函數輸入區域，直接輸入=COUNTIF(E3:E55,“<60“)，之后按回車鍵確認，這樣，就得到不及格的人數了！

=COUNTIF(E3:E55,“<60“)分析：

E3:E55代表統計的數據區域是E3到E55，“<60“統計的條件是成績小于60；

知識擴展：

“<60“改為：“<80“是什么意思呢？

表示的是，統計分數小于80的人數；

“<60“改為“>60“是什么意思呢？

表示的是：統計及格的人數！

英文通用格式是：COUNTIF（range,criteria)

中文通用格式是：COUNTIF（數據區域,條件表達式)上述格式中，數據區域和條件表達式，可根據需要自行構造，靈活性非常大；