excel引用其他sheet數(shù)據(jù) excel不同sheet引用

excel引用其他sheet數(shù)據(jù)excel不同sheet引用

一：[excel引用其他sheet數(shù)據(jù)]EXCEL中如何使用VLOOKUP函數(shù)查找引用其他工作表數(shù)據(jù)和自動填充數(shù)據(jù)

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

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

假設(shè)姓名在A列,在職月份在B列,兩個表的第一行都是表頭.

在B表插入一個新A列,這樣B表的姓名就在B列，月份在C列,在A2單元格輸入=B2C2

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

在H2輸入公式=IF(ISERROR(VLOOKUP(A2B2,Sheet2!A:A,1,FALSE)),"B表沒有此記錄","B表有此記錄")

如何在EXCEL中篩選出相同的名字？

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

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

方法二、在1000個用戶的sheet1!B1輸入（假設(shè)你的記錄在A1而且是豎列擴展）=if(isna(vlookup(a1,sheet2$a$1:$a$800,2,0)),"","重復(fù)“)

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

=MATCH(B1,A:A,0)

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

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

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

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

VLOOKUP(A2,SHEET2!$B:$D,2,FALSE)四個參數(shù)解釋

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

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

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

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

假如我們需要在輸入A列學(xué)號以后,B列與C列自動填充對應(yīng)的姓名與班級,那么只需要在B列,C列預(yù)先輸入公式就可以了。為了避開在A列學(xué)號輸入之前,B列與C列消失"#N/A"這樣錯誤值,可以增加一個IF函數(shù)推斷A列是否為空,非空則進行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))

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

課表是學(xué)校最基本的教學(xué)管理依據(jù)，課表形成的傳統(tǒng)方法是先支配好原始數(shù)據(jù)，再設(shè)計好表格的固定格式，一項項往表里填內(nèi)容。上百張課表的形成都要人工錄入或人工粘貼復(fù)制，既繁瑣又簡單出差錯，而且不利于檢索查詢。筆者介紹一種方法，在原始數(shù)據(jù)錄入后利用“數(shù)據(jù)透視表”，可以實現(xiàn)課表生成的自動化。一、功能1.一張“數(shù)據(jù)透視表”僅靠鼠標(biāo)移動字段位置，即可變換出各種類型的課表，例如：班級課表。每班一張一周課程表?？蛇x框內(nèi)選擇不同的學(xué)院和班號，即可得到不同班的課表。按老師索引。即每位老師一周全部的信息。按時間索引，即每天每節(jié)課有哪些老師來、上什么課。按課程索引。課程帶頭人可能只關(guān)懷和自己有關(guān)的內(nèi)容。按學(xué)院索引?？赡苤恍枰獌扇棓?shù)據(jù)，了解概況。按本專科索引。按樓層索引。專家組聽課時挨次走過每個教室，需要隨時隨地查看信息。按教室或機房索引。支配房間時要隨時查看。2.字段數(shù)量的選擇是任意的，即表格內(nèi)容可多可少，隨時調(diào)整。3.任何類型的表都能夠?qū)崿F(xiàn)連續(xù)打印或分頁打印。如班級課表可以連續(xù)顯示，也可快速、自動生成每班一張；某部門全部老師的課表可以匯總在一張表上，也可每個老師一頁紙，分別打印。4.遇到調(diào)課，只要更改原始表，再重新透視一次，可在瞬間完成，就意味著全部表的數(shù)據(jù)都已更新。而傳統(tǒng)的方法必需分別去改班級表、教室表、機房表、老師表……稍有疏忽就可能遺漏。5.全部的表都不用設(shè)計格式，能夠自動形成表格，自動調(diào)整表格大小，自動合并相同數(shù)據(jù)單元格。二、建立數(shù)據(jù)庫規(guī)范數(shù)據(jù)庫的建立是滿意查詢、檢索、統(tǒng)計功能的基本要求。1.基本字段：班級、星期、節(jié)次、課程、地點、老師。2.可選字段：學(xué)院、班級人數(shù)、同學(xué)類別、金工實習(xí)周次、老師單位、地點屬性、備注字段名橫向排列形成了“表頭”，每個字段名下是縱向排列的數(shù)據(jù)。3.庫中的數(shù)據(jù)必需規(guī)范。如“地點”中不能消失除樓號、房間號以外的任何文字(包括空格)；“課程”中必需是規(guī)范的課程名，不允許有“單、雙”等字樣。建議上機課增加一個字段“上機”，而不是在課程名中增加“上機”說明，后者不利于課程檢索。4.庫中的每條數(shù)據(jù)清單的每個格只要存在數(shù)據(jù)就必需填滿。不允許由于與上一行數(shù)據(jù)相同就省略了，更不能合并單元格。5.增加的整條記錄在庫中的位置可以任意。如規(guī)律課表的課程只有8節(jié)，某班增加“9～10節(jié)”或雙休日上課，新增記錄則可插在該班其他課的末尾，也可附在庫的最底端。無論在什么位置，都不影響透視后的效果。三、做數(shù)據(jù)透視表在選擇透視范圍時要包含全部原始數(shù)據(jù)庫，假如錄制“宏”，最好比原始表多增加若干行，以備增加記錄用。但字段的數(shù)量可依據(jù)需要選擇。把選中的字段分別放置在表的“行字段”中，在每個字段名上雙擊，彈出“字段設(shè)置”框，選擇“無”，即形成了顯示美觀的透視表。1.用鼠標(biāo)拖動各字段，重新支配左右挨次、上下位置(指行字段與頁字段之間的轉(zhuǎn)換)，或在可選框內(nèi)選中所需，即可形成各種各樣的新表。2.常用的班級課表可排好紙張版面、頁眉頁腳，特地供原始打印?！鞍嗉墶弊侄巫詈梅旁凇绊撟侄巍敝?，以便于每班打印1張。在“班級”字段的可選框內(nèi)選擇各班，即可顯示出全部的班級課表。每班課表的大小是自動調(diào)整的，如“節(jié)次”中的數(shù)據(jù)項只有8節(jié)，遇到增加“9～10節(jié)”課程的狀況，表格會在7～8節(jié)后自動增加1行，把9～10節(jié)的內(nèi)容填進去，下一個班則可自動恢復(fù)正常。既可以設(shè)置為無課顯示空格，也可以設(shè)成無課不顯示，即有哪節(jié)顯示哪節(jié)。Excel2024查找重復(fù)姓名方法兩則

每次統(tǒng)計班級同學(xué)基本狀況時都會由于同學(xué)姓名相同而導(dǎo)致張冠李戴的錯誤。以往為避開類似錯誤都要將Excel表格按姓名進行排序，然后依次檢查是否重名，特別麻煩還簡單出問題。假如您也遇到過類似狀況，那么在Excel中，我們可以采納以下的方法來區(qū)分那些有重復(fù)的姓名，以避開出錯。

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

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

確定后關(guān)閉此對話框，則可以將重名同學(xué)所在行的全部數(shù)據(jù)都填充此顏色，如圖3所示。有了此醒目的標(biāo)志，那么我們在以后的操作中就不太簡單出錯了。

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

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

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

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

=INDEX(C1：C7，MATCH(H1I1,A1:A7B1:B7,0)

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

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

假設(shè)您盼望依據(jù)某人的姓名(Name)查找此人的年齡(Age)。為此，請按如下公式示例，協(xié)作使用INDEX函數(shù)和MATCH函數(shù)：

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

此公式示例使用單元格A1:C5作為信息表，并在第三列中查找Mary的年齡(Age)。公式返回22

一些Excel公式的有用運用例子

=COUNTIF(D2:D10,"400")統(tǒng)計D2:D10的值大于400的個數(shù)=COUNTIF(B2:B10,"東北部")統(tǒng)計B2:B10的內(nèi)容為"東北部"的個數(shù)

=TODAY()顯示當(dāng)前系統(tǒng)日期=NOW()顯示當(dāng)前系統(tǒng)日期和詳細(xì)時間=YEAR(B2)獲得B2單元格內(nèi)(當(dāng)前系統(tǒng)日期和詳細(xì)時間)的年=MONTH(B2)獲得B2單元格內(nèi)(當(dāng)前系統(tǒng)日期和詳細(xì)時間)的月=DAY(B2)獲得B2單元格內(nèi)(當(dāng)前系統(tǒng)日期和詳細(xì)時間)的日=HOUR(B2)獲得B2單元格內(nèi)(當(dāng)前系統(tǒng)日期和詳細(xì)時間)的時

=RANK(D2,$D$2:$D$10)取D2的值在D2-D10范圍內(nèi)的排名是多少=MATCH(99,C2:C10,0)統(tǒng)計出C2-C10范圍內(nèi)值為99的個數(shù)

=EXACT(A4,B4)比較A4,B4兩個單元格內(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個單元格都要進行條件推斷

=VLOOKUP(B3,D2:G14,4,0)VLOOKUP（需在第一列中查找的數(shù)值,需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,需返回某列值的列號,規(guī)律值True或False）常常用Excel建立一些表格，有時我們需要給一些表格建立許多個副表，那么如何使這些復(fù)制表格中的數(shù)據(jù)隨原表的修改而修改呢？VLOOKUP函數(shù)可以幫我們做到這一點=HLOOKUP(B7,B1:F3,2,0)HLOOKUP與VLOOKUPHLOOKUP用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。VLOOKUP用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時，請使用函數(shù)HLOOKUP。當(dāng)比較值位于要進行數(shù)據(jù)查找的左邊一列時，請使用函數(shù)VLOOKUP。語法形式為：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ū)域，上面的查找值必需位于這個區(qū)域的最左列?？梢允褂脤^(qū)域或區(qū)域名稱的引用。Row_index_num為table_array中待返回的匹配值的行序號。Row_index_num為1時，返回table_array第一行的數(shù)值，row_index_num為2時，返回table_array其次行的數(shù)值，以此類推。Col_index_num為相對列號。最左列為1，其右邊一列為2，依此類推.Range_lookup為一規(guī)律值，指明函數(shù)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是否為數(shù)值(TRUE)

=ISNUMBER(A5)檢查COUNTRY是否為文本(TRUE)

=ISTEXT(A3)檢查5是否為偶數(shù)

ISEVEN(5)

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

ISODD(-1)

TRUE

2.如何去掉execl單元格中文字前面的數(shù)字?自己寫個函數(shù)放在模塊里，然后在單元格調(diào)用函數(shù)

=delnum(A1)

PublicFunctiondelnum(zifuAsString)AsStringDimlAsInteger,mAsInteger,nAsString,aAsStringl=Len(zifu)Form=1Toln=Mid(zifu,m,1)IfAsc(n)48OrAsc(n)57Thena=anEndIfNextmdelnum=aEndFunction

3.excel中,列許多,行很少,怎么能讓打印在一頁上?使用公式先進行一下轉(zhuǎn)換就是了。

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

B

C

D

E

F

G

H

I

J

K

L

M

N

O1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

先使用公式轉(zhuǎn)變?yōu)?行5列的數(shù)據(jù)，公式如下：[假設(shè)我們在A6單元格開頭輸入公式，轉(zhuǎn)變后的數(shù)據(jù)區(qū)域為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))并將該公式復(fù)制到數(shù)據(jù)區(qū)域A6:E11，我們可以看到，現(xiàn)在數(shù)據(jù)已經(jīng)進行了轉(zhuǎn)換。

結(jié)果為：A

B

C

D

E1

2

3

4

5F

G

H

I

J6

7

8

9

10K

L

M

N

O11

12

13

14

15

公式說明：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)當(dāng)進行一次推斷，如下：=If(Indirect(...)="","",Indirect(...))[Indirect(...)即上面示例中的公式]

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

A列為個人的身份證號或企業(yè)代碼，身份證包括2類：15位的身份證，18位身份證。15位（440105610202453）的身份證的生日為1956-10-20；18位nbsp;的身份證生日為1974-03-15。企業(yè)代碼不滿意15位或18位。

現(xiàn)在要求在B列得到A列身份證號人的誕生日期；若是企業(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位時,應(yīng)當(dāng)沒2000年后誕生的吧所以,以上應(yīng)當(dāng)行得通,試試看當(dāng)A列是企業(yè)代碼時,公式有問題.如:A1=21341231233210,得到的是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))

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

二：[excel引用其他sheet數(shù)據(jù)]Excel中如何使一個工作表的