第一篇：EXCEL查找與引用函數(shù)VLOOKUP的應(yīng)用法及實(shí)例 詳解

EXCEL查找與引用函數(shù)VLOOKUP的應(yīng)用法及實(shí)例詳解

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

在 VLOOKUP 中的 V 代表垂直。語(yǔ)法

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP（查找XX，在YY區(qū)域中，與查找值相對(duì)的第ZZ列的值，方式）lookup_value

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

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

名稱的引用，例如數(shù)據(jù)庫(kù)或列表。

如下圖所示：C6：F10即為

table_array

為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤?duì)區(qū)域或區(qū)域

table_array

col_index_num

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

如下圖所示，4表示數(shù)據(jù)表的第4列，col_index_num

range_lookup

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

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

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

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

如下圖所示，F(xiàn)ALSE 即為range_lookup表示第一列的數(shù)值不必進(jìn)行排序

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

說明

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

VLOOKUP 返回錯(cuò)誤值 #N/A。

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

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

----------------------------對(duì)于可能產(chǎn)生錯(cuò)誤值的防范 將公式進(jìn)行以下修改

=IF(C3=“",”查詢值為空，請(qǐng)?zhí)顚懖樵冎怠?IF(ISERROR(VLOOKUP(C3,C6:F10,4,FALSE))=TRUE,”請(qǐng)檢查參數(shù)是否正確",VLOOKUP(C3,C6:F10,4,FALSE)))如下圖所示，如果查找時(shí)未指定名稱，則公式的計(jì)算結(jié)果為“查詢值為空，請(qǐng)?zhí)顚懖樵冎怠?/p>

如下圖所示，因使用的是FALSE，精確查找，當(dāng)查找值為“西瓜”時(shí)，因該值不在列表中，會(huì)產(chǎn)生#N/A 錯(cuò)誤，但使用公式用，可提示有參數(shù)錯(cuò)誤

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

呵呵，關(guān)于VLOOKUP就介紹到這里，祝大家工作愉快！

第二篇：關(guān)于VLOOKUP函數(shù)的用法

關(guān)于VLOOKUP函數(shù)的用法

“Lookup”的漢語(yǔ)意思是“查找”，在Excel中與“Lookup”相關(guān)的函數(shù)有三個(gè)：VLOOKUP、HLOOKUO和LOOKUP。下面介紹VLOOKUP函數(shù)的用法。

一、功能

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

二、語(yǔ)法

標(biāo)準(zhǔn)格式：

VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

三、語(yǔ)法解釋

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

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

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

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

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

如果 range_lookup 為 FALSE，table_array 不必進(jìn)行排序。

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

3.Col_index_num 為table_array 中待返回的匹配值的列序號(hào)。

Col_index_num 為 1 時(shí)，返回 table_array 第一列中的數(shù)值；

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

如果Col_index_num 小于 1，函數(shù) VLOOKUP 返回錯(cuò)誤值 #VALUE!；

如果Col_index_num 大于 table_array 的列數(shù)，函數(shù) VLOOKUP 返回錯(cuò)誤值 #REF!。

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

四、應(yīng)用例子

A B C D編號(hào) 姓名 工資 科室2005001 周杰倫 2870 辦公室2005002 蕭亞軒 2750 人事科2005006 鄭智化 2680 供應(yīng)科2005010 屠洪剛 2980 銷售科2005019 孫楠 2530 財(cái)務(wù)科2005036 孟庭葦 2200 工 會(huì)

A列已排序（第四個(gè)參數(shù)缺省或用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)等于“工 會(huì)”

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

若A列沒有排序，要得出正確的結(jié)果，第四個(gè)參數(shù)必須用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)等于“工 會(huì)”

五、關(guān)于TRUE和FALSE的應(yīng)用

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

用VLOOKUP查找數(shù)據(jù)也是這樣，當(dāng)?shù)谝涣幸雅判?，第四個(gè)參數(shù)用TRUE（或確省），Excel會(huì)很輕松地找到數(shù)據(jù)，效率較高。當(dāng)?shù)谝涣袥]有排序，第四個(gè)參數(shù)用FALSE，Excel會(huì)從上到下一條一條地查找，效率較低。

筆者覺得，若要精確查找數(shù)據(jù)，由于計(jì)算機(jī)運(yùn)算速度很快，可省略排序操作，直接用第四個(gè)參數(shù)用FALSE即可。

最近愛上了VLOOKUP，有人還對(duì)它進(jìn)行了更新。因?yàn)樗穆┒淳褪侵荒芊祷刂貜?fù)值得第一個(gè)值。下面就詳細(xì)來敘述一下吧！

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

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

Table_array 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。

可以使用對(duì)區(qū)域或區(qū)域名稱的引用、常數(shù)數(shù)組、計(jì)算后的內(nèi)存數(shù)組。對(duì)區(qū)域引用時(shí)，可以引用整列,excel會(huì)自動(dòng)判斷使用區(qū)域。

該參數(shù)的第一列必須包含查找的內(nèi)容，其它列包含需返回的內(nèi)容；返回內(nèi)容的列序號(hào)由下個(gè)參數(shù)指定。

Col_index_num 為table_array中待返回的匹配值的列序號(hào)。

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

如果col_index_num小于1，函數(shù) VLOOKUP 返回錯(cuò)誤值值 #VALUE!；

如果col_index_num大于table_array的列數(shù)，函數(shù) VLOOKUP 返回錯(cuò)誤值 #REF!。

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

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

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

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

此時(shí)，table_array不必進(jìn)行排序。如果找不到，則返回錯(cuò)誤值#N/A；可isna檢測(cè)錯(cuò)誤后使用if判斷去除錯(cuò)誤信息。

VLOOKUP 經(jīng)常會(huì)出現(xiàn)錯(cuò)誤的#N/A,下面是幾種可能性： 數(shù)據(jù)有空格或者數(shù)據(jù)類型不一致。

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

如果格式不一致，可以將數(shù)值強(qiáng)制轉(zhuǎn)換成文本，lookup_value之后用&跟""表示的空字符串。將文本轉(zhuǎn)換成數(shù)值，lookup_value*1進(jìn)行運(yùn)算。假設(shè)有以下兩個(gè)表格：表1：

姓名 身份證 學(xué)科 成績(jī)

張三 310111 物理 A

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

張三 310111 政治 C

趙六 310114 語(yǔ)文 B劉備 310115 英語(yǔ) D

表2：

姓名 身份證 住址 學(xué)科 成績(jī)

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

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

請(qǐng)問：如何快速的將表2的學(xué)科和成績(jī)自動(dòng)填入？直接使用VLOOKUP即可。示例如下：

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

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

$B1 條件值

Sheet1!$B$1C$5 要查找區(qū)間（注意：請(qǐng)使用絕對(duì)引用）2 查找的結(jié)果為區(qū)域中的第幾列FALSE 表示精確查找

*具體應(yīng)用中大家注意各個(gè)參數(shù)的意義！調(diào)整參數(shù)來滿足個(gè)人需要。

第三篇：EXCEL中如何使用VLOOKUP函數(shù)查找引用其他工作表數(shù)據(jù)和自動(dòng)填充數(shù)據(jù)

EXCEL中如何使用VLOOKUP函數(shù)查找引用其他工作表數(shù)據(jù)和自動(dòng)填充數(shù)據(jù)

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

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

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

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

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

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

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

方法

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

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

兩列數(shù)據(jù)查找相同值對(duì)應(yīng)的位置

=MATCH(B1,A:A,0)

EXCEL中如何使用VLOOKUP函數(shù)查找引用其他工作表數(shù)據(jù)和自動(dòng)填充數(shù)據(jù)

VLOOKUP函數(shù)，在表格或數(shù)值數(shù)組（數(shù)據(jù)表）的首列查找指定的數(shù)值（查找值），并由此返回表格或數(shù)組當(dāng)前行中指定列（列序號(hào)）處的數(shù)值。

VLOOKUP(查找值，數(shù)據(jù)表，列序號(hào)，[匹配條件]）

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

1、“A2”是查找值，就是要查找A2單元格的某個(gè)學(xué)號(hào)。

2、“SHEET2!$B:$D”是數(shù)據(jù)表，就是要在其中查找學(xué)號(hào)的表格，這個(gè)區(qū)域的首列必須是學(xué)號(hào)。

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

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

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

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

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

Excel課表生成中應(yīng)用的兩種方法

課表是學(xué)校最基本的教學(xué)管理依據(jù)，課表形成的傳統(tǒng)方法是先安排好原始數(shù)據(jù)，再設(shè)計(jì)好表格的固定格式，一項(xiàng)項(xiàng)往表里填內(nèi)容。上百?gòu)堈n表的形成都要人工錄入或人工粘貼復(fù)制，既繁瑣又容易出差錯(cuò)，而且不利于檢索查詢。筆者介紹一種方法，在原始數(shù)據(jù)錄入后利用“數(shù)據(jù)透視表”，可以實(shí)現(xiàn)課表生成的自動(dòng)化。

一、功能

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

2.字段數(shù)量的選擇是任意的，即表格內(nèi)容可多可少，隨時(shí)調(diào)整。

3.任何類型的表都能夠?qū)崿F(xiàn)連續(xù)打印或分頁(yè)打印。如班級(jí)課表可以連續(xù)顯示，也可快速、自動(dòng)生成每班一張；某部門所有教師的課表可以匯總在一張表上，也可每個(gè)老師一頁(yè)紙，分別打印。

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

5.所有的表都不用設(shè)計(jì)格式，能夠自動(dòng)形成表格，自動(dòng)調(diào)整表格大小，自動(dòng)合并相同數(shù)據(jù)單元格。

二、建立數(shù)據(jù)庫(kù)

規(guī)范數(shù)據(jù)庫(kù)的建立是滿足查詢、檢索、統(tǒng)計(jì)功能的基本要求。

1.基本字段：班級(jí)、星期、節(jié)次、課程、地點(diǎn)、教師。

2.可選字段：學(xué)院、班級(jí)人數(shù)、學(xué)生類別、金工實(shí)習(xí)周次、教師單位、地點(diǎn)屬性、備注字段名橫向排列形成了“表頭”，每個(gè)字段名下是縱向排列的數(shù)據(jù)。

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

4.庫(kù)中的每條數(shù)據(jù)清單的每個(gè)格只要存在數(shù)據(jù)就必須填滿。不允許因?yàn)榕c上一行數(shù)據(jù)相同就省略了，更不能合并單元格。

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

三、做數(shù)據(jù)透視表

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

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

2.常用的班級(jí)課表可排好紙張版面、頁(yè)眉頁(yè)腳，專門供原始打印。“班級(jí)”字段最好放在“頁(yè)字段”中，以便于每班打印1張。在“班級(jí)”字段的可選框內(nèi)選擇各班，即可顯示出所有的班級(jí)課表。每班課表的大小是自動(dòng)調(diào)整的，如 “節(jié)次”中的數(shù)據(jù)項(xiàng)只有8節(jié)，遇到增添“9～10節(jié)”課程的情況，表格會(huì)在7～8節(jié)后自動(dòng)增加1行，把9～10節(jié)的內(nèi)容填進(jìn)去，下一個(gè)班則可自動(dòng)恢復(fù)正常。既可以設(shè)置為無課顯示空格，也可以設(shè)成無課不顯示，即有哪節(jié)顯示哪節(jié)。

Excel 2003查找重復(fù)姓名方法兩則

每次統(tǒng)計(jì)年級(jí)學(xué)生基本情況時(shí)都會(huì)因?yàn)閷W(xué)生姓名相同而導(dǎo)致張冠李戴的錯(cuò)誤。以往為避免類似錯(cuò)誤都要將Excel表格按姓名進(jìn)行排序，然后依次檢查是否重名，非常麻煩還容易出問題。如果您也遇到過類似情況，那么在Excel中，我們可以采用以下的方法來區(qū)分那些有重復(fù)的姓名，以避免出錯(cuò)。

一、利用條件格式進(jìn)行彩色填充

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

確定后關(guān)閉此對(duì)話框，則可以將重名同學(xué)所在行的全部數(shù)據(jù)都填充此顏色，如圖3所示。有了此醒目的標(biāo)志，那么我們?cè)谝院蟮牟僮髦芯筒惶菀壮鲥e(cuò)了。

查找數(shù)據(jù)公式兩個(gè)(基本查找函數(shù)為VLOOKUP,MATCH)

（1）、根據(jù)符合行列兩個(gè)條件查找對(duì)應(yīng)結(jié)果

=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，F(xiàn)ALSE)

（2）、根據(jù)符合兩列數(shù)據(jù)查找對(duì)應(yīng)結(jié)果(為數(shù)組公式)

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

使用 INDEX 函數(shù)和 MATCH 函數(shù)查找數(shù)據(jù)

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

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

數(shù)和 MATCH 函數(shù)：

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

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

統(tǒng)計(jì)D2:D10的值大于400的個(gè)數(shù)

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

統(tǒng)計(jì)B2:B10的內(nèi)容為”東北部“的個(gè)數(shù)

=TODAY()

顯示當(dāng)前系統(tǒng)日期

=NOW()

顯示當(dāng)前系統(tǒng)日期和具體時(shí)間

=YEAR(B2)

獲得B2單元格內(nèi)(當(dāng)前系統(tǒng)日期和具體時(shí)間)的年

=MONTH(B2)

獲得B2單元格內(nèi)(當(dāng)前系統(tǒng)日期和具體時(shí)間)的月

=DAY(B2)

獲得B2單元格內(nèi)(當(dāng)前系統(tǒng)日期和具體時(shí)間)的日

=HOUR(B2)

獲得B2單元格內(nèi)(當(dāng)前系統(tǒng)日期和具體時(shí)間)的時(shí)

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

取D2的值在D2-D10范圍內(nèi)的排名是多少

=MATCH(99,C2:C10,0)

統(tǒng)計(jì)出C2-C10范圍內(nèi)值為99的個(gè)數(shù)

=EXACT(A4,B4)

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

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

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

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

與上例相似，只不過是2個(gè)單元格都要進(jìn)行條件判斷

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

VLOOKUP（需在第一列中查找的數(shù)值,需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,需返回某列值的列號(hào),邏輯值True或False）

經(jīng)常用Excel建立一些表格，有時(shí)我們需要給一些表格建立很多個(gè)副表，那么如何使這些復(fù)制表格中的數(shù)據(jù)隨原表的修改而修改呢？VLOOKUP函數(shù)可以幫我們做到這一點(diǎn)

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

HLOOKUP與VLOOKUP

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

當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時(shí)，請(qǐng)使用函數(shù) HLOOKUP。

當(dāng)比較值位于要進(jìn)行數(shù)據(jù)查找的左邊一列時(shí)，請(qǐng)使用函數(shù) VLOOKUP。

語(yǔ)法形式為：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

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

Table_array查找的區(qū)域，用于查找數(shù)據(jù)的區(qū)域，上面的查找值必須位于這個(gè)區(qū)域的最左列。可以使用對(duì)區(qū)域或區(qū)域名稱的引用。

Row_index_num為 table_array 中待返回的匹配值的行序號(hào)。Row_index_num 為 1 時(shí)，返回 table_array 第一行的數(shù)值，row_index_num 為 2 時(shí)，返回 table_array 第二行的數(shù)值，以此類推。

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

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

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

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

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

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

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

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

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

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

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

自己寫個(gè)函數(shù)放在模塊里，然后在單元格調(diào)用函數(shù)=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中,列很多,行很少,怎么能讓打印在一頁(yè)上?

使用公式先進(jìn)行一下轉(zhuǎn)換就是了。

以下為示例：

源數(shù)據(jù)為數(shù)據(jù)區(qū)域A1:O2，即一個(gè)2行15列的數(shù)據(jù)，如下：

ABCDEFGHIJKLMNO

***131415

先使用公式轉(zhuǎn)變?yōu)?行5列的數(shù)據(jù)，公式如下：

[假設(shè)我們?cè)贏6單元格開始輸入公式，轉(zhuǎn)變后的數(shù)據(jù)區(qū)域?yàn)锳6: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))

并將該公式復(fù)制到數(shù)據(jù)區(qū)域A6:E11，我們可以看到，現(xiàn)在數(shù)據(jù)已經(jīng)進(jìn)行了轉(zhuǎn)換。

結(jié)果為：

ABCDE

12345

FGHIJ

678910

KLMNO

1112131415

公式說明：

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

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

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

附加：如果不是正好滿列數(shù)，那么應(yīng)該進(jìn)行一次判斷，如下：

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

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

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

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

現(xiàn)在要求在B列得到A列身份證號(hào)人的出生日期；若是企業(yè)代碼的不需要。

=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位時(shí),應(yīng)該沒2000年后出生的吧所以,以上應(yīng)該行得通,試試看

當(dāng)A列是企業(yè)代碼時(shí),公式有問題.如:A1=2***10,得到的是3123-32-10

公式上做了點(diǎn)修改.=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))

當(dāng)A列是企業(yè)代碼時(shí),返回原企業(yè)代碼

第四篇：EXCEL中如何使用VLOOKUP函數(shù)查找引用其他工作表數(shù)據(jù)和自動(dòng)填充數(shù)據(jù)

EXCEL中如何使用VLOOKUP函數(shù)查找引用其他工作表數(shù)據(jù)和自動(dòng)填

充數(shù)據(jù)

2010-06-15 10:01:38|分類： 標(biāo)簽： |字號(hào)大中小 訂閱

VLOOKUP函數(shù)，在表格或數(shù)值數(shù)組（數(shù)據(jù)表）的首列查找指定的數(shù)值（查找值），并由此返回表格或數(shù)組當(dāng)前行中指定列（列序號(hào)）處的數(shù)值。

VLOOKUP(查找值，數(shù)據(jù)表，列序號(hào)，[匹配條件]）

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

1、“A2”是查找值，就是要查找A2單元格的某個(gè)學(xué)號(hào)。

2、“SHEET2!$B:$D”是數(shù)據(jù)表，就是要在其中查找學(xué)號(hào)的表格，這個(gè)區(qū)域的首列必須是學(xué)號(hào)。

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

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

查找。

如果我們需要在輸入A列學(xué)號(hào)以后,B列與C列自動(dòng)填充對(duì)應(yīng)的姓名與班級(jí),那么只需要在B列,C列預(yù)先輸入公式就可以了。為了避免在A列學(xué)號(hào)輸入之前,B列與C列出現(xiàn)“#N/A”這樣錯(cuò)誤值,可以增加一

個(gè)IF函數(shù)判斷A列是否為空,非空則進(jìn)行VLOOKUP查找.這樣B2與C2的公式分別調(diào)整為

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

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

點(diǎn)擊下面標(biāo)簽查看演示動(dòng)畫:

利用VLOOKUP函數(shù)查找引用其他工作表數(shù)據(jù)

利用VLOOKUP函數(shù)自動(dòng)填充數(shù)據(jù)

第五篇：COUNTIF函數(shù)的用法及實(shí)例

Excel統(tǒng)計(jì)函數(shù)COUNTIF的用法及實(shí)例

一：首先介紹COUNTIF函數(shù)的用法

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

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

1、返加包含值12的單元格數(shù)量

=COUNTIF（range,12）

2、返回包含負(fù)值的單元格數(shù)量

20-屯昌電視臺(tái)MSTP（總表處理后重復(fù)303條

=COUNTIF（range，“<0”）

3、返回不等于0的單元格數(shù)量

=COUNTIF（range，“<>0”）

4、返回大于5的單元格數(shù)量

=COUNTIF（range，“>5”）

5、返回等于單元格A1中內(nèi)容的單元格數(shù)量

=COUNTIF（range，A1）

6、返回大于單元格A1中內(nèi)容的單元格數(shù)量

=COUNTIF（range，“>”&A1）

7、返回包含文本內(nèi)容的單元格數(shù)量

=COUNTIF（range，“*”）

8、返回包含三個(gè)字符內(nèi)容的單元格數(shù)量

=COUNITF（range，“???”）

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

=COUNTIF（range，“GOOD”）

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

=COUNTIF（range，“*GOOD*”）

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

=COUNTIF（range，“AB*”）

12、返回包含當(dāng)前日期的單元格數(shù)量

=COUNTIF（range，TODAY()）

13、返回大于平均值的單元格數(shù)量

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

14、返回平均值上面超過三個(gè)標(biāo)準(zhǔn)誤差的值的單元格數(shù)量

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

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

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

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

=COUNTIF（range，TRUE）

二：COUNTIF函數(shù)應(yīng)用舉例

假設(shè)存在如下圖的學(xué)生成績(jī)表，現(xiàn)在，讓我們一起來使用COUNTIF函數(shù)進(jìn)行統(tǒng)計(jì)，下面統(tǒng)計(jì)的是不及格的人數(shù)！

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

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

E3:E55代表統(tǒng)計(jì)的數(shù)據(jù)區(qū)域是E3到E55，“<60“統(tǒng)計(jì)的條件是成績(jī)小于60；

知識(shí)擴(kuò)展：

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

表示的是，統(tǒng)計(jì)分?jǐn)?shù)小于80的人數(shù)；

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

表示的是：統(tǒng)計(jì)及格的人數(shù)！

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

中文通用格式是：COUNTIF（數(shù)據(jù)區(qū)域,條件表達(dá)式)上述格式中，數(shù)據(jù)區(qū)域和條件表達(dá)式，可根據(jù)需要自行構(gòu)造，靈活性非常大；