愛華網(wǎng)兩張都是人員在職信息表，A表長，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ù)查找相同值對應(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個(gè)學(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) 四個(gè)參數(shù)解釋
1、“A2”是查找值，就是要查找A2單元格的某個(gè)學(xué)號。
2、“SHEET2!$B:$D”是數(shù)據(jù)表，就是要在其中查找學(xué)號的表格，這個(gè)區(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列出現(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))
Excel課表生成中應(yīng)用的兩種方法
課表是學(xué)校最基本的教學(xué)管理依據(jù)，課表形成的傳統(tǒng)方法是先安排好原始數(shù)據(jù)，再設(shè)計(jì)好表格的固定格式，一項(xiàng)項(xiàng)往表里填內(nèi)容。上百張課表的形成都要人工錄入或人工粘貼復(fù)制，既繁瑣又容易出差錯(cuò)，而且不利于檢索查詢。筆者介紹一種方法，在原始數(shù)據(jù)錄入后利用“數(shù)據(jù)透視表”，可以實(shí)現(xiàn)課表生成的自動化。
一、功能
1. 一張“數(shù)據(jù)透視表”僅靠鼠標(biāo)移動字段位置，即可變換出各種類型的課表，例如：班級課表。每班一張一周課程表?？蛇x框內(nèi)選擇不同的學(xué)院和班號，即可得到不同班的課表。按教師索引。即每位教師一周所有的信息。按時(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ù)打印或分頁打印。如班級課表可以連續(xù)顯示，也可快速、自動生成每班一張；某部門所有教師的課表可以匯總在一張表上，也可每個(gè)老師一頁紙，分別打印。
4. 遇到調(diào)課，只要更改原始表，再重新透視一次，可在瞬間完成，就意味著所有表的數(shù)據(jù)都已更新。而傳統(tǒng)的方法必須分別去改班級表、教室表、機(jī)房表、教師表……稍有疏忽就可能遺漏。
5. 所有的表都不用設(shè)計(jì)格式，能夠自動形成表格，自動調(diào)整表格大小，自動合并相同數(shù)據(jù)單元格。
二、建立數(shù)據(jù)庫
規(guī)范數(shù)據(jù)庫的建立是滿足查詢、檢索、統(tǒng)計(jì)功能的基本要求。
1. 基本字段：班級、星期、節(jié)次、課程、地點(diǎn)、教師。
2. 可選字段：學(xué)院、班級人數(shù)、學(xué)生類別、金工實(shí)習(xí)周次、教師單位、地點(diǎn)屬性、備注字段名橫向排列形成了“表頭”，每個(gè)字段名下是縱向排列的數(shù)據(jù)。
3. 庫中的數(shù)據(jù)必須規(guī)范。如“地點(diǎn)”中不能出現(xiàn)除樓號、房間號以外的任何文字(包括空格)；“課程”中必須是規(guī)范的課程名，不允許有“單、雙”等字樣。建議上機(jī)課增加一個(gè)字段“上機(jī)”，而不是在課程名中增添“上機(jī)”說明，后者不利于課程檢索。
4. 庫中的每條數(shù)據(jù)清單的每個(gè)格只要存在數(shù)據(jù)就必須填滿。不允許因?yàn)榕c上一行數(shù)據(jù)相同就省略了，更不能合并單元格。
5. 增加的整條記錄在庫中的位置可以任意。如規(guī)律課表的課程只有8節(jié)，某班增加“9～10節(jié)”或雙休日上課，新增記錄則可插在該班其他課的末尾，也可附在庫的最底端。無論在什么位置，都不影響透視后的效果。
三、做數(shù)據(jù)透視表
在選擇透視范圍時(shí)要包含全部原始數(shù)據(jù)庫，如果錄制“宏”，最好比原始表多增加若干行，以備增加記錄用。但字段的數(shù)量可根據(jù)需要選擇。把選中的字段分別放置在表的“行字段”中，在每個(gè)字段名上雙擊，彈出“字段設(shè)置”框，選擇“無”，即形成了顯示美觀的透視表。
1. 用鼠標(biāo)拖動各字段，重新安排左右順序、上下位置(指行字段與頁字段之間的轉(zhuǎn)換)，或在可選框內(nèi)選中所需，即可形成各種各樣的新表。
2. 常用的班級課表可排好紙張版面、頁眉頁腳，專門供原始打印?！鞍嗉墶弊侄巫詈梅旁凇绊撟侄巍敝校员阌诿堪啻蛴?張。在“班級”字段的可選框內(nèi)選擇各班，即可顯示出所有的班級課表。每班課表的大小是自動調(diào)整的，如 “節(jié)次”中的數(shù)據(jù)項(xiàng)只有8節(jié)，遇到增添“9～10節(jié)”課程的情況，表格會在7～8節(jié)后自動增加1行，把9～10節(jié)的內(nèi)容填進(jìn)去，下一個(gè)班則可自動恢復(fù)正常。既可以設(shè)置為無課顯示空格，也可以設(shè)成無課不顯示，即有哪節(jié)顯示哪節(jié)。
Excel 2003查找重復(fù)姓名方法兩則
每次統(tǒng)計(jì)年級學(xué)生基本情況時(shí)都會因?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ī)則”對話框，在“選擇規(guī)則類型”列表中點(diǎn)擊“使用公式確定要設(shè)置格式的單元格”，然后在“為符合此公式的值設(shè)置格式”下方的輸入框中輸入如下公式“=COUNTIF($B$2:$B$11,$B2)>=2”，然后點(diǎn)擊下方的“格式”按鈕，在打開的“設(shè)置單元格格式”對話框的“填充”選項(xiàng)卡中指定一種填充顏色，確定后如圖2所示。
確定后關(guān)閉此對話框，則可以將重名同學(xué)所在行的全部數(shù)據(jù)都填充此顏色，如圖3所示。有了此醒目的標(biāo)志，那么我們在以后的操作中就不太容易出錯(cuò)了。
查找數(shù)據(jù)公式兩個(gè)(基本查找函數(shù)為VLOOKUP,MATCH)
（1）、根據(jù)符合行列兩個(gè)條件查找對應(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(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)。為此，請按如下公式示例，配合使用 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ù)表,需返回某列值的列號,邏輯值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í)，請使用函數(shù) HLOOKUP。
當(dāng)比較值位于要進(jìn)行數(shù)據(jù)查找的左邊一列時(shí)，請使用函數(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ū)域，上面的查找值必須位于這個(gè)區(qū)域的最左列?？梢允褂脤^(qū)域或區(qū)域名稱的引用。
Row_index_num為 table_array 中待返回的匹配值的行序號。Row_index_num 為 1 時(shí)，返回 table_array 第一行的數(shù)值，row_index_num 為 2 時(shí)，返回 table_array 第二行的數(shù)值，以此類推。
Col_index_num為相對列號。最左列為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中,列很多,行很少,怎么能讓打印在一頁上?
使用公式先進(jìn)行一下轉(zhuǎn)換就是了。
以下為示例：
源數(shù)據(jù)為數(shù)據(jù)區(qū)域A1:O2，即一個(gè)2行15列的數(shù)據(jù)，如下：
A B C D E F G H I J K L M N O
1 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ū)域?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é)果為：
A B C D E
1 2 3 4 5
F G H I J
6 7 8 9 10
K L M N O
11 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)該進(jìn)行一次判斷，如下：
=If(Indirect(...)="","",Indirect(...))
[Indirect(...)即上面示例中的公式]
5.excel里A列為身份證號碼，要求在B列得出其出身日期？
A列為個(gè)人的身份證號或企業(yè)代碼，身份證包括2類：15位的身份證，18位身份證。15位（440105610202453）的身份證的生日為1956-10-20；18位 （440306197403150053）的身份證生日為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位時(shí),應(yīng)該沒2000年后出生的吧所以,以上應(yīng)該行得通,試試看
當(dāng)A列是企業(yè)代碼時(shí),公式有問題.
如:A1=21341231233210,得到的是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è)代碼
