1、在人事表單中使用函數edateemonthdatedifmonthroundup轉換中文大小寫金額迅速查找和跨表引用indexmatchindirect工資的管理vlookup批量制作工資條排序郵件合并創建工資查詢系統int人事信息到期提醒表序號 員工編號 員工姓名參工時間身份證號出生日期01AY0001胡 軍2003年4月15日19年月日02AY0003陳 波2004年4月16日19年月日03AY0002閆曉洪 2005年4月20日19年月日04AY0004敖雪東 2006年3月12日19年月日05AY0005陳小萍 2004年5月18日19年月日06AY0006鄭安平 2003年5月10日

2、19年月日07AY0007滿 雄2005年4月21日19年月日08AY0008廖 彬2004年7月19日19年月日09AY0009鄧 勇2006年10月8日19年月日10AY0010陳 霞2005年3月26日19年月日11AY0011鄭青松 2004年5月15日19年月日12AY0012廖挺鴻 2003年4月16日19年月日13AY0013王燕玲 2006年2月17日19年月日14AY0014鄧 群2005年6月27日19年月日15AY0015閆 波2003年4月19日19年月日16AY0016王 霞1993年4月15日19年月日17AY0017張伯蓉 1984年3月18日19年月日18AY0

3、018王 艷1997年8月19日19年月日19AY0019張 菁2002年6月29日19年月日20AY0020陳 好2005年6月27日19年月日21AY0021黃大秀 1971年6月26日19年月日22AY0022陳道華 1982年9月12日19年月日23AY0023陳紅林 1969年10月9日19年月日24AY0024王志剛 1970年11月8日19年月日25AY0025陳進軍 1992年11月9日19年月日26AY0026鄧欣雨 1994年5月26日19年月日27AY0027張林強 1980年10月8日19年月日28AY0028鄭克強 1980年6月25日19年月日29AY0029張榮恩

4、 2003年6月26日19年月日30AY0030謝雨欣 2002年5月16日19年月日進入公司時間試用期限簽訂合同日期合同期限性別年齡工齡生日到期#3個月2006年9月30日3年14#3個月2007年8月12日3年13#3個月2008年9月12日3年12#3個月2007年4月26日3年12#2006年2月6日3個月2006年5月6日3年13#3個月2008年4月12日3年14#3個月2008年8月24日3年12#3個月2006年8月23日3年13#2009年2月6日3個月無無11#3個月2008年6月26日3年12#3個月2008年9月30日3年13#3個月2007年8月30日3年14#3個月

5、2009年3月1日3年12#3個月2007年1月8日3年12#3個月2008年9月30日3年14#3個月2004年7月26日無固定24#3個月1996年9月30日無固定33#3個月2006年7月15日無固定20#3個月2006年7月25日3年15#3個月2006年5月26日3年12#3個月1986年7月12日無固定46#3個月2004年9月24日無固定35#3個月2005年5月16日無固定48#3個月2007年7月25日無固定47#3個月2007年6月16日3年25#3個月2006年7月15日無固定23#3個月2006年7月25日3年37#2006年2月6日3個月2006年5月6日3年37#3

6、個月2007年1月8日3年14#3個月2008年9月30日3年15#人事信息到期提醒表試用到期合同到期退休到期退休日期退休日期工齡第幾季度簽訂合同#VALUE!#VALUE!1673#3#3#3#2#2#目標值：5188.1499999999996各部分轉換值整數部分角位分位角分位判斷條件是否為零是否只有整數部分是否沒有整數部分是否只有角是否只有分轉換值：=IF(D11,數值為零,IF(D12,D5&整,IF(D13,IF(D14,D6&整,IF(D15,D7,D8),IF(D14,D5&D6&整,IF(D15,D5&零&D7,D5&D8)分三次轉換元位角分位=IF(TRUNC(C2,2)=0

7、,0,IF(TRUNC(C2,2)=INT(C2),TEXT(TRUNC(C2),DBNUM2)&元整,IF(TRUNC(C2)=0,TEXT(TRUNC(C2),DBNUM2)&元)=IF(OR(B21=0,TRUNC(C2,2)=INT(C2),0,IF(RIGHT(TEXT(TRUNC(C2,2),0.00),1)=0,TEXT(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1),DBNUM2)&角整,IF(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1)=0,TEXT(RIGHT(TEXT(TRUNC(C2,2),0.00),1),

8、DBNUM2)&分,TEXT(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1),DBNUM2)&角&TEXT(RIGHT(TEXT(TRUNC(C2,2),0.00),1),DBNUM2)&分)=IF(D11,數值為零,IF(D12,D5&整,IF(D13,D5)=IF(D12,IF(D14,D6,IF(D15,IF(D13,D7,零&D7),D8)=TEXT(TRUNC(C2),DBNUM2)&元=TEXT(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1),DBNUM2)&角=TEXT(RIGHT(TEXT(TRUNC(C2,2),0.

9、00),1),DBNUM2)&分=D6&D7=IF(TRUNC(C2,2)=0,TRUE,FALSE)=IF(TRUNC(C2,2)=TRUNC(C2),TRUE,FALSE)=IF(TRUNC(C2)=0,TRUE,FALSE)=IF(RIGHT(TEXT(TRUNC(C2,2),0.00),1)=0,TRUE,FALSE)=IF(MID(C2,LEN(TEXT(TRUNC(C2,2),0.00)-1,1)=0,TRUE,FALSE)轉換值=IF(AND(OR(B21=0,B21=),C21=),數值為零,IF(C21=0,B21,IF(MID(C2,LEN(TEXT(TRUNC(C2,2)

10、,0.00)-1,1)=0,B21&零&C21,B21&C21)=IF(D11,數值為零,B22&C22)=IF(D11,數值為零,IF(D12,D5&整,IF(D13,IF(D14,D6&整,IF(D15,D7,D8),IF(D14,D5&D6&整,IF(D15,D5&零&D7,D5&D8)目標值：200.11各部分轉換值整數部分貳佰元角位壹角分位壹分角分位壹角壹分判斷條件是否為零FALSE是否只有整數部分FALSE是否沒有整數部分FALSE是否只有角FALSE是否只有分FALSE轉換值：貳佰元壹角壹分分三次轉換元位角分位轉換值貳佰元壹角壹分 貳佰元壹角壹分貳佰元壹角壹分 貳佰元壹角壹分部門

11、生產部費用項目水電費金額1464四月份各部門費用明細表費費用用項項目目辦辦公公室室財財務務部部信信息息部部銷銷售售部部生生產產部部人人力力資資源源部部合合計計薪金234324334532576876659854532455318018廣告費355512655876524975水電費23554367612414643453387保險費248502224422816159717166681899865通訊費53457632448766455234415919培訓費878670760111787910215325雜費1234235465487924342437798費用總額5471869732607

12、2612717711094351991475287姓名照片唐僧豬八戒孫悟空沙和尚姓名照片沙和尚沙和尚廣東省浙江省遼寧省四川省河北省湖南省安徽省廣州市杭州市沈陽市成都市石家莊市 長沙市合肥市深圳市寧波市大連市自貢市唐山市株洲市蕪湖市珠海市溫州市鞍山市攀枝花市 秦皇島市 湘潭市蚌埠市省份汕頭市嘉興市撫順市瀘州市邯鄲市衡陽市淮南市廣東省韶關市湖州市本溪市德陽市邢臺市邵陽市馬鞍山市佛山市紹興市丹東市綿陽市保定市岳陽市淮北市江門市金華市錦州市廣元市張家口市 常德市銅陵市湛江市衢州市營口市遂寧市承德市張家界市 安慶市茂名市舟山市阜新市內江市滄州市益陽市黃山市肇慶市臺州市遼陽市樂山市廊坊市郴州市滁州市惠州市

13、麗水市盤錦市南充市衡水市永州市阜陽市梅州市鐵嶺市宜賓市懷化市宿州市汕尾市朝陽市廣安市婁底市巢湖市河源市葫蘆島市 達州市湘西州六安市陽江市眉山市亳州市清遠市雅安市池州市東莞市巴中市宣城市中山市資陽市潮州市阿壩州揭陽市甘孜州云浮市涼山州城市廣州市Quarter1Office63Windows66Xbox70Quarter2Office93Windows90Xbox99Quarter3Office77Windows58Xbox60OfficeWindows XboxQuarter1636670Quarter4Quarter2939099Office97quarter3775860Windows56q

14、uarter4975695Xbox95ABCDEFGHIJ123456789101112131415161718192021員工銷售獎金的計算就可以查找出來稅率，稅款表2查找業務員不同商場的銷售額表六批量制作工資條名稱為工資表工資查詢對月度工資按部門匯總工資發放零鈔備用表加班記錄表-如何計算上班小時數今天前后星期幾員工年假表員工銷售產品工作業績冰箱洗衣機空調 彩電李辰1210520￥117,045.40劉欣259315￥135,566.11楊建國6211019￥136,243.79王心語2116931￥195,134.44田宏濤15141225￥174,465.16張小麗138616￥112,

15、987.82劉保國30201410￥206,783.40單價￥3,050.50￥1,560.99￥4,489.90￥2,119.00總庫存量200100100150現庫存量7824114獎金基本工資總工資#￥600.00￥12,305.00公公式式#￥600.00￥14,157.004#￥600.00￥14,225.0077#￥600.00￥20,114.003.142#￥600.00￥18,047.00-3.2#￥600.00￥11,899.0031500#￥600.00￥21,279.00說說明明（結結果果）將 3.2 向上舍入，小數位為 0(4)將 76.9 向上舍入，小數位為 0(77

16、)將 3.14159 向上舍入，保留三位小數(3.142)將-3.14159 向上舍入，保留一位小數(-3.2)-3.1 不同于round將 31415.92654向上舍入到小數點左側兩位(31500)VLOOKUPVLOOKUP(lookup_valuelookup_value,table_arraytable_array,col_index_numcol_index_num,range_lookup)Lookup_valueLookup_value 為需要在Table_array第一列中查找的數值。可以為數值、引用或文本字符串。需要注意的是類型必須與table_array第一列的類型一致。

17、查找文本時，文本不區分大小寫；可以使用通配符“*”、“?”。Table_arrayTable_array 為需要在其中查找數據的數據表。可以使用對區域或區域名稱的引用、常數數組、計算后的內存數組。對區域引用時，可以引用整列,excel會自動判斷使用區域。該參數的第一列必須包含查找的內容，其它列包含需返回的內容；返回內容的列序號由下個參數指定。Col_index_numCol_index_num 為table_array中待返回的匹配值的列序號。如為1時，返回table_array第一列中的數值；為2，返回table_array第二列中的數值，以此類推。如果col_index_num小于1，函數

18、 VLOOKUP 返回錯誤值值#VALUE!；如果col_index_num大于table_array的列數，函數 VLOOKUP 返回錯誤值#REF!。Range_lookup 為一邏輯值，指明函數VLOOKUP返回時是精確匹配還是近似匹配。如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數值；近似匹配查詢一般用于數值的查詢，table_arraytable_array的的第第一一列列必必須須按按升升序序排排列列；否則不能返回正確的結果。如果range_value為FALSE（或0），函數VLOOKUP將返回精確匹配值。此時，t

19、able_array不必進行排序。如果找不到，則返回錯誤值#N/A；可isna檢測錯誤后使用if判斷去除錯誤信息。處理查找錯誤例1精確匹配查詢之基本運用例8在同一單元格按不同字段查詢例2數值近似匹配查詢之基本運用例9返回多個符合條件的查詢結果例3文本近似匹配查詢之基本運用例10有兩個并列條件（不同字段）的查詢例4區別查詢結果的空白與0值例11使用數組常量的查詢例5依次序返回同一查詢結果的多列內容例12從右向左查詢例6返回同一查詢結果的多列（不按原次序）的內容例7按部分內容模糊查詢以上舉例是vlookup的基本運用，再結合其它函數，可以發揮其更多作用。可以為數值、引用或文本字符串。需要注意的是類

20、型必須與table_array第一列的類型一致。查找文本時，文本不區分大小寫；可以使用通配符“*”、“?”。可以使用對區域或區域名稱的引用、常數數組、計算后的內存數組。該參數的第一列必須包含查找的內容，其它列包含需返回的內容；返回內容的列序號由下個參數指定。如為1時，返回table_array第一列中的數值；為2，返回table_array第二列中的數值，以此類推。如果col_index_num小于1，函數 VLOOKUP 返回錯誤值值#VALUE!；如果col_index_num大于table_array的列數，函數 VLOOKUP 返回錯誤值#REF!。如果為TRUE或省略，則返回近似匹配

21、值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數值；近似匹配查詢一般用于數值的查詢，table_arraytable_array的的第第一一列列必必須須按按升升序序排排列列；否則不能返回正確的結果。如果range_value為FALSE（或0），函數VLOOKUP將返回精確匹配值。此時，table_array不必進行排序。如果找不到，則返回錯誤值#N/A；可isna檢測錯誤后使用if判斷去除錯誤信息。在同一單元格按不同字段查詢返回多個符合條件的查詢結果有兩個并列條件（不同字段）的查詢vlookupvlookup應應用用實實例例返返回回精精確確匹匹配配查查詢詢之之基

22、基本本運運用用精確查找是vlookup最基本也是最常用的功能，對于數據量大的查找，其速度比菜單中的查找還快。設置vlookup第四個參數為false或0，即為精確查找。精確查找適用于文本，也適用于數值；但對數值查查找找時時須須注注意意格格式式一一致致，否則會出錯表表一一姓姓名名工工號號性性別別籍籍貫貫出出生生年年月月張三豐KT001男北京1970年8月李四光KT002女天津1980年9月王麻子KT003男河北1975年3月趙六兒KT004女河南1985年12月姓姓名名籍籍貫貫趙六兒河南根據姓名在$B$9:$F$12中查找籍貫，$B$9:$F$12區域最好使用絕對引用（可在公式中選定區域按F4轉

23、換），便于復制。查找的姓名必須位于$B$6:$F$9的第一列；籍貫在$B$6:$F$9位于第四列，因此第三個參數為4；精確查找，第四個參數FALSE姓姓名名性性別別李四光女根據姓名在【表一】中查找籍貫【表一】是定義的單元格區域$B$9:$F$12的名稱，在名稱框（編輯欄左邊）可以選定；也可在插入名稱定義中查看修改工工號號籍籍貫貫KT002天津根據工號在$C$8:$E$12中查找籍貫注意范圍已改為$C$8:$E$12，確保工號在第一列；由于范圍的改變，籍貫位于該范圍的第3列，因此第三個參數為3工工號號出出生生年年月月KT00125781根據工號在$C$8:$F$12中查找出生年月，但此時返回的是

24、時間序列值（即格式沒有帶過來）1970年8月需要重新設定單元格格式1970年8月或在公式中使用text設定格式數數值值近近似似匹匹配配查查詢詢之之基基本本運運用用近似匹配查找通常情況下用于累進數值的查找。此時第四個參數省略，或為true，或為非0數值。（EXCEL中0等同FALSE，非零值均視為TRUE）這是一張個所稅稅率表，對于不確定的收入，可以利用近似匹配查找稅率及速扣數表表二二級級數數 應應稅稅所所得得超超過過 且且不不超超過過稅稅率率(%)速速算算扣扣除除數數 150050250020001025320005000151254500020000203755200004000025137

25、56400006000030337576000080000356375880000100000401037591000004515375應應稅稅所所得得稅稅率率速速算算扣扣除除數數 稅稅款款5360203756975360在表中未列出，因此EXCEL查找小于5360的最大值即5000，并返回對應的稅率等。注意公式中第四個參數省略，或為true，或為非0數值。并且查找的第一列必須升序排列，否則不會返回期望的結果（需要注意的是此時不一定返回錯誤）。試著輸入5000，你會發現稅率為20%，應是15%（由條件指定），雖然對最后計算結果沒有影響。以下公式可以解決這個問題：應應稅稅所所得得稅稅率率速速算算

26、扣扣除除數數 稅稅款款5000151256251520文文本本近近似似匹匹配配查查詢詢之之基基本本運運用用表表三三對于文本，一般不使用近似匹配查找（非模糊查找）。吖A并非文本不能用于近似查找，主要是沒有太多用途。除了下面這個例子：八B嚓C這個例子利用文本近似匹配查找漢字的第一個拼音字母咑D由于EXCEL中漢字字符是按拼音排序的，因此漢字字符比較時的大小與拼音同序（一般情況下）鵽E左邊的表是根據漢字拼音首字母排列，并取各字母的臨界點漢字字符（該字母的“最小漢字”）發F猤G漢漢字字字字符符拼拼音音首首字字母母鉿H家J夻J咔K通過將“家”與表中字符比較后，找到比“家”小的最大值“夻”，返回J垃L這個

27、例子一般用于姓名的縮寫，而上面的公式只找第一個字符，因此需要修改嘸M旀N漢漢字字字字符符拼拼音音首首字字母母噢O普利卡PLK妑P七Q這個公式取得前三個漢字字符的拼音字母。使用MID依次取出字符后由Vlookup查詢到字母，最后“&”連接文本囕R仨S使用該例關鍵是【表三】的建立；同理，如果要查找漢字的完整拼音，可以建立一個由各種拼音組成的類似列表。他T但這個方法并不一直正確，由于多音字及文字大小排序與其拼音不完全一致的原因，有時返回的字母可能不是你要的屲W夕X丫Y帀Z區區別別查查詢詢結結果果的的空空白白與與0 0值值表表四四姓姓名名內內容容張三豐0vlookup對于查找到的0值會返回0；張三豐0

28、李四光0對于空白單元格（非空字符串）也會返回0；李四光王麻子對空字符串仍返回空字符串。王麻子趙六兒趙六兒張三豐0如要對0值和空白單元格區別，可以直接判斷是否，或使用len函數測試返回值的長度；李四光如不需要0值，可直接判斷0，或在【工具】【選項】中將【零值】去掉。依依次次序序返返回回同同一一查查詢詢結結果果的的多多列列內內容容按姓名在【表一】中查詢全部內容姓姓名名工工號號性性別別籍籍貫貫出出生生年年月月李四光KT002女天津1980年9月王麻子KT003男河北1975年3月當查詢某個結果的多列內容，并要依次取出，可使用column()函數作為index_num參數column()函數返回公式所

29、在列的列號，當公式復制時，會隨單元格的移動相應改變lookup_value參數使用混合引用，如$B96。即對列標絕對引用（列標前加$）、行號相對引用(行號前沒有$)；復制時引用單元格會改變。上面公式只要輸入C96中的公式，然后向右、向下復制就可以。返返回回同同一一查查詢詢結結果果的的多多列列（不不按按原原次次序序）的的內內容容按姓名及不同項目（不依次序）在【表一】中查詢姓姓名名性性別別籍籍貫貫出出生生年年月月工工號號先由match函數確定項目內容在B8:F8區域的位置，將返回值作為vlookup的第三個參數。趙六兒女河南#KT004注意公式中Match函數的第一個參數使用行號絕對引用、列標相對

30、引用；王麻子男河北1975年3月KT003按按部部分分內內容容模模糊糊查查詢詢按姓在【表一】中查詢姓姓姓姓名名工工號號性性別別籍籍貫貫出出生生年年月月王王麻子KT003男河北1975年3月vlookup支持通配符【*】和【?】，因此當查找內容不全時，可以使用通配符；如例公式中使用【&】將查找文本與通配符連接后查找，注意通配符連接位置注意查找中，有多個符合條件的結果時，只會返回第一個在在同同一一單單元元格格按按不不同同字字段段查查詢詢在同一單元格按姓名或工號在【表一】中查詢姓姓名名或或工工號號性性別別籍籍貫貫出出生生年年月月KT002女天津1980年9月 使用countif判斷，如果在姓名區中有

31、C131單元格的內容，按姓名查找；否則按工號查找李四光女天津1980年9月 或者使用isna判斷，如果按姓名查找出錯，就按工號查找；王麻子男河北1975年3月 或者使用一個vlookup，第二、三個參數由判斷結果決定（查找范圍、列號）。返返回回多多個個符符合合條條件件的的查查詢詢結結果果表表五五查找的結果不止一個，并要全部返回姓姓名名工工號號vlookup對于包含多個結果的查找只會返回第一個結果（數據區由上向下第一個）1張三豐KT001但有時需要返回所有的結果。如例，需要按姓名查找同名的各個工號1李四光KT002這種情況一般使用數組公式解決。但數據量大時，使用數組公式會由于計算量大，速度會很慢

32、1李四光KT003vlookup沒有直接的解決方法，需要添加輔助列（注意輔助列需添加在左側，因為要根據輔助列的內容查找）2張三豐KT0043張三豐KT005表五左側白色區域為輔助列，B141公式為=($C$148=C141)+B140；等同=IF($C$148=C141,1,0)+B140姓姓名名工工號號該公式結果為：如姓名列內容與查找姓名相同，則按順序顯示1、2、3，從而將相同姓名區分張三豐KT001然后，在查詢中查找數值1、2、3即可查找到對應查找內容的多個結果(注意查找區域由輔助列開始）KT004為便于復制，vlookup第一個參數數值1、2、3使用函數row()返回的行號產生KT005

33、當然，輔助列中的公式可以改為其它，只要達到區別相同內容的目的有有兩兩個個并并列列條條件件（不不同同字字段段）的的查查詢詢表表六六：查找某業務員對某客戶的銷售額業業務務員員客客戶戶銷銷售售額額對于此類根據兩個或多個并列條件查找某個結果的問題，使用vlookup也需要添加輔助列張三豐商場A張三豐商場A100B158公式=C158&D158，即將被查找的內容合并作為一列，并以此作為查找列。張三豐超市B張三豐超市B200李四光店鋪C李四光店鋪C300有些數據需要在兩個數據間插入一特殊符號(查詢數據中沒有的任意符號)以示區別李四光超市B李四光超市B400例如兩個條件分別是：張三豐店鋪C張三豐店鋪C500

34、李四光商場A李四光商場A600此時就需要用其他符號將其區別：業業務務員員客客戶戶銷銷售售額額李四光超市B400使用【&】將要查詢的兩個條件合并后作為查詢值，在區域$B$158:$E$163中查詢第4列內容使使用用數數組組常常量量的的查查詢詢一般，使用vlookup前需要有一個對應的表格供查找，即作為vlookup的Table_array參數的表格區域；但有時這個表格可能會顯得累贅；如果數據不是太多而且比較固定，可以考慮使用數組常量代替單元格區域的方法；所謂數組常量就是直接輸入的一組數組，以“”開始結束、同一行的數值用逗號“，”分隔、同一列的數值用分號“；”分隔如10,20,30，是一行三列（橫

35、向）、包含三個元素的一維數組常量10;20;30;40，是一列四行（縱向）、包含四個元素的一維數組常量10,20,30;40,50,60，是兩行三列、包含六個元素的二維數組常量；二維數組常量需要行列等長如果你覺得輸數組常量麻煩（要區分【,】、【;】，文本要加【】），可以先將數據輸入單元格區域，然后在其它單元格輸入【=】，用鼠標選定該區域后按F9，顯示的就是數組常量，然后復制到公式中其實將以前使用的vlookup公式中第三個參數（區域不要太大）選定按F9，再回車，可以看到公式正常運作；此時已使用了數組常量以下舉例中的公式并不是數組公式，輸入時不用Ctrl+Shift+Enter一齊按搬一個由ch

36、enjun版主提供的計算個人所得稅的例子：應應稅稅所所得得稅稅率率速速算算扣扣除除數數 稅稅款款650020375925再搬一個前面的例子漢漢字字字字符符拼拼音音首首字字母母海H是否被公式中的數組常量嚇一跳？不是一個個輸的，按F9得到的；漢漢字字字字符符拼拼音音首首字字母母螺絲扣LQK對于此類固定的數組常量可以將其定義一個名稱，公式就簡潔多了，還不占用單元格公式中的【拼音】就是一個定義的名稱，可以自【插入】【名稱】【定義】中查看從從右右向向左左查查詢詢我們知道，vlookup查找的列必須位于查找區域的最左列，有時會需要按右面的查找左邊的值以【表一】為例，要按【姓名】查詢【工號】很容易；但反過來

37、按【工號】查找【姓名】直接使用vlookup就不行了此時可以使用的公式很多，如lookup、index+match、offset+match、indirect+match等但由chenjun版主提供的使用vlookup的解法我覺得很有參考價值，介紹這個用法的目的主要是學會將兩個一維數組合并為一個二維數組工工號號姓姓名名KT002李四光這個公式先由【IF(1,0,$C$9:$C$12,$B$9:$B$12)】計算出內存數組：KT001,張三豐;KT002,李四光;KT003,王麻子;KT004,趙六兒可以看到，在該內存數組中，【工號】位于【姓名】的左側，Vlookup在該內存數組中查找注意1,0

38、是一行二列（橫向）常量數組，后面的兩個區域是多行一列（縱向），即兩個數組的方向不同，這樣才會生成兩列多行數組張三豐李四光王麻子趙六兒KT001KT002KT003KT004三個結果是不一樣的精確查找是vlookup最基本也是最常用的功能，對于數據量大的查找，其速度比菜單中的查找還快。設置vlookup第四個參數為false或0，即為精確查找。根據姓名在$B$9:$F$12中查找籍貫，$B$9:$F$12區域最好使用絕對引用（可在公式中選定區域按F4轉換），便于復制。查找的姓名必須位于$B$6:$F$9的第一列；籍貫在$B$6:$F$9位于第四列，因此第三個參數為4；精確查找，第四個參數FALS

39、E【表一】是定義的單元格區域$B$9:$F$12的名稱，在名稱框（編輯欄左邊）可以選定；也可在插入名稱定義中查看修改注意范圍已改為$C$8:$E$12，確保工號在第一列；由于范圍的改變，籍貫位于該范圍的第3列，因此第三個參數為3根據工號在$C$8:$F$12中查找出生年月，但此時返回的是時間序列值（即格式沒有帶過來）近似匹配查找通常情況下用于累進數值的查找。此時第四個參數省略，或為true，或為非0數值。（EXCEL中0等同FALSE，非零值均視為TRUE）并且查找的第一列必須升序排列，否則不會返回期望的結果（需要注意的是此時不一定返回錯誤）。試著輸入5000，你會發現稅率為20%，應是15%

40、（由條件指定），雖然對最后計算結果沒有影響。以下公式可以解決這個問題：對于文本，一般不使用近似匹配查找（非模糊查找）。并非文本不能用于近似查找，主要是沒有太多用途。除了下面這個例子：這個例子利用文本近似匹配查找漢字的第一個拼音字母由于EXCEL中漢字字符是按拼音排序的，因此漢字字符比較時的大小與拼音同序（一般情況下）左邊的表是根據漢字拼音首字母排列，并取各字母的臨界點漢字字符（該字母的“最小漢字”）通過將“家”與表中字符比較后，找到比“家”小的最大值“夻”，返回J這個例子一般用于姓名的縮寫，而上面的公式只找第一個字符，因此需要修改這個公式取得前三個漢字字符的拼音字母。使用MID依次取出字符后由

41、Vlookup查詢到字母，最后“&”連接文本使用該例關鍵是【表三】的建立；同理，如果要查找漢字的完整拼音，可以建立一個由各種拼音組成的類似列表。但這個方法并不一直正確，由于多音字及文字大小排序與其拼音不完全一致的原因，有時返回的字母可能不是你要的vlookup對于查找到的0值會返回0；對于空白單元格（非空字符串）也會返回0；對空字符串仍返回空字符串。如要對0值和空白單元格區別，可以直接判斷是否，或使用len函數測試返回值的長度；如不需要0值，可直接判斷0，或在【工具】【選項】中將【零值】去掉。當查詢某個結果的多列內容，并要依次取出，可使用column()函數作為index_num參數colum

42、n()函數返回公式所在列的列號，當公式復制時，會隨單元格的移動相應改變lookup_value參數使用混合引用，如$B96。即對列標絕對引用（列標前加$）、行號相對引用(行號前沒有$)；復制時引用單元格會改變。先由match函數確定項目內容在B8:F8區域的位置，將返回值作為vlookup的第三個參數。注意公式中Match函數的第一個參數使用行號絕對引用、列標相對引用；Vlookup函數的第一個參數使用行號相對引用、列標絕對引用；中國人民直接合并后相同中國人民中國人民中國人民vlookup支持通配符【*】和【?】，因此當查找內容不全時，可以使用通配符；如例使用countif判斷，如果在姓名區中

43、有C131單元格的內容，按姓名查找；否則按工號查找或者使用isna判斷，如果按姓名查找出錯，就按工號查找；或者使用一個vlookup，第二、三個參數由判斷結果決定（查找范圍、列號）。vlookup對于包含多個結果的查找只會返回第一個結果（數據區由上向下第一個）但有時需要返回所有的結果。如例，需要按姓名查找同名的各個工號這種情況一般使用數組公式解決。但數據量大時，使用數組公式會由于計算量大，速度會很慢vlookup沒有直接的解決方法，需要添加輔助列（注意輔助列需添加在左側，因為要根據輔助列的內容查找）表五左側白色區域為輔助列，B141公式為=($C$148=C141)+B140；等同=IF($C

44、$148=C141,1,0)+B140該公式結果為：如姓名列內容與查找姓名相同，則按順序顯示1、2、3，從而將相同姓名區分然后，在查詢中查找數值1、2、3即可查找到對應查找內容的多個結果(注意查找區域由輔助列開始）為便于復制，vlookup第一個參數數值1、2、3使用函數row()返回的行號產生當然，輔助列中的公式可以改為其它，只要達到區別相同內容的目的對于此類根據兩個或多個并列條件查找某個結果的問題，使用vlookup也需要添加輔助列B158公式=C158&D158，即將被查找的內容合并作為一列，并以此作為查找列。有些數據需要在兩個數據間插入一特殊符號(查詢數據中沒有的任意符號)以示區別例如

45、兩個條件分別是：此時就需要用其他符號將其區別：使用【&】將要查詢的兩個條件合并后作為查詢值，在區域$B$158:$E$163中查詢第4列內容一般，使用vlookup前需要有一個對應的表格供查找，即作為vlookup的Table_array參數的表格區域；但有時這個表格可能會顯得累贅；如果數據不是太多而且比較固定，可以考慮使用數組常量代替單元格區域的方法；所謂數組常量就是直接輸入的一組數組，以“”開始結束、同一行的數值用逗號“，”分隔、同一列的數值用分號“；”分隔如10,20,30，是一行三列（橫向）、包含三個元素的一維數組常量10;20;30;40，是一列四行（縱向）、包含四個元素的一維數組常

46、量10,20,30;40,50,60，是兩行三列、包含六個元素的二維數組常量；二維數組常量需要行列等長可以先將數據輸入單元格區域，然后在其它單元格輸入【=】，用鼠標選定該區域后按F9，顯示的就是數組常量，然后復制到公式中其實將以前使用的vlookup公式中第三個參數（區域不要太大）選定按F9，再回車，可以看到公式正常運作；此時已使用了數組常量是否被公式中的數組常量嚇一跳？不是一個個輸的，按F9得到的；對于此類固定的數組常量可以將其定義一個名稱，公式就簡潔多了，還不占用單元格公式中的【拼音】就是一個定義的名稱，可以自【插入】【名稱】【定義】中查看我們知道，vlookup查找的列必須位于查找區域的

47、最左列，有時會需要按右面的查找左邊的值以【表一】為例，要按【姓名】查詢【工號】很容易；但反過來按【工號】查找【姓名】直接使用vlookup就不行了此時可以使用的公式很多，如lookup、index+match、offset+match、indirect+match等但由chenjun版主提供的使用vlookup的解法我覺得很有參考價值，介紹這個用法的目的主要是學會將兩個一維數組合并為一個二維數組這個公式先由【IF(1,0,$C$9:$C$12,$B$9:$B$12)】計算出內存數組：KT001,張三豐;KT002,李四光;KT003,王麻子;KT004,趙六兒可以看到，在該內存數組中，【工號】

48、位于【姓名】的左側，Vlookup在該內存數組中查找注意1,0是一行二列（橫向）常量數組，后面的兩個區域是多行一列（縱向），即兩個數組的方向不同，這樣才會生成兩列多行數組處處理理查查找找錯錯誤誤返返回回vlookup使用中一般會產生以下錯誤：#VALUE!和#REF!是由于col_index_num設置不當造成的，雖然可以使用ISERROR剔除，但建議不要這么做，因為這類錯誤需要糾正。有時col_index_num參數使用column()函數生成，當設置不當造成的錯誤用ISERROR剔除時，會與#N/A錯誤混淆，不利于公式查錯。#N/A是常見的錯誤信息，需要針對不同情況處理：精確匹配查找時出現

49、，是由于未找到完全匹配的值。近似匹配查找時出現，是由于查找值小于數據區的最小值。如果vlookup的參數產生錯誤，vlooukp也返回相同錯誤，那不在本討論范圍內。精確匹配查找時出現#N/A錯誤首先檢查引用范圍是否正確，查詢值是否在數據區的第一列。當確定引用正確、不應出現#N/A（即應該查找到值）時，檢查是否存在空格或其它字符、格式是否一致表表一一張三豐100對于空格或其他不可見字符，可以使用len函數測試文本長度來確定，或使用code測試其ASCII碼李四軍 200B18單元格長度4數據含空格等張三豐#N/A該錯誤由于查詢字符“張三豐”后含空格，可能看不出，但在精確匹配查找時造成不完全匹配1

50、00對于lookup_value中的空格，將其中的空格替換掉，或使用TRIM去除空格后查找李四軍#N/A該錯誤是由于查詢表表四中“李四軍”后含空格，在精確匹配查找時不完全匹配200對于Table_array中的空格，也可以使用trim。這個公式中的Table_array已不是直接引用單元格區域，而是引用對$B$87:$C$88運算trim后的內存數組因此，這是一個數組公式，需要按Ctrl+Shift+Enter輸入如果數據區比較大，數組公式會很慢；不如直接使用替換將數據區的空格替換后使用普通公式。李四軍#N/A該錯誤由于查詢字符“李四軍”后含不可見字符（非空格），使用trim、clean、替換

51、空格都不能去除。由網頁復制、其他程序轉出的表格，經常會出現這類字符，CODE可以測試其并非空格對于這類不可見字符，可以先復制該字符，然后替換該字符為空白表表二二100A查找內容格式不符，也會造成N/A錯誤200B可以使用鼠標選定幾個單元格，看狀態欄的合計。有，則為數值，無，則為文本。數據格式不一致100#N/A這兩個錯誤都是由于格式不一致造成，一個是按數值查文本，一個是按文本查數值200#N/A對于此類錯誤，可以修改查找值、數據區的格式，使之統一；也可以在公式中處理A這個公式將數值100用&連接空字符串，強制轉為文本B這個公式將文本200用*1運算，強制轉為數值要注意的是采用修改格式的方法，僅

52、僅將單元格格式更改還不行：例如對于包含數值的常規單元格將格式改為文本后，單元格中的值仍是數值形式,需要激活（雙擊）才會真正轉為文本。單元格很多時，采用逐個激活的方法肯定會累死。可以采用分列或選擇性粘貼的方法：當某列數據需要全部轉換時，采用分列是個好辦法。它可以將文本轉為數值，也可將數值轉為文本。分列位于菜單欄數據分列由文本轉數值也可以復制一空白單元格，選定需轉換的數據區，選擇性粘貼加當確定應該出現#N/A（即查找值不存在）時，如果不要顯示錯誤，可以使用條件格式或直接在公式中處理表表三三A100注意使用條件格式處理后單元格內的值仍是#N/A，其他單元格引用該單元格也會返回#N/A錯誤。C200當

53、C55選B時，顯然應該返回錯誤（數據區沒有B）。處理錯誤B#N/A這個單元格使用條件格式處理，條件格式公式isna(D55)，并設定條件字體顏色與底色相同。使用ISNA測試vlookup函數是否返回#N/A，如vlookup函數返回#N/A，則ISNA函數返回TRUE。再使用IF函數判斷，即可去除錯誤。精確查找時，也可以直接判斷查找值是否存在來去除#N/A錯誤，如此例使用COUNTIF測試數據區是否有查找值。近似匹配查找時出現#N/A錯誤近似匹配查找時，除了前述的格式等原因，查找值小于數據區的最小值將返回#N/A。對于此類錯誤，只要數據區設計合理就可避免（建立可能的最小值）,當然也可以使用IS

54、NA去除。處理錯誤B18第四個字符碼32#VALUE!和#REF!是由于col_index_num設置不當造成的，雖然可以使用ISERROR剔除，但建議不要這么做，因為這類錯誤需要糾正。有時col_index_num參數使用column()函數生成，當設置不當造成的錯誤用ISERROR剔除時，會與#N/A錯誤混淆，不利于公式查錯。當確定引用正確、不應出現#N/A（即應該查找到值）時，檢查是否存在空格或其它字符、格式是否一致對于空格或其他不可見字符，可以使用len函數測試文本長度來確定，或使用code測試其ASCII碼該錯誤由于查詢字符“張三豐”后含空格，可能看不出，但在精確匹配查找時造成不完全

55、匹配對于lookup_value中的空格，將其中的空格替換掉，或使用TRIM去除空格后查找該錯誤是由于查詢表表四中“李四軍”后含空格，在精確匹配查找時不完全匹配這個公式中的Table_array已不是直接引用單元格區域，而是引用對$B$87:$C$88運算trim后的內存數組因此，這是一個數組公式，需要按Ctrl+Shift+Enter輸入如果數據區比較大，數組公式會很慢；不如直接使用替換將數據區的空格替換后使用普通公式。該錯誤由于查詢字符“李四軍”后含不可見字符（非空格），使用trim、clean、替換空格都不能去除。由網頁復制、其他程序轉出的表格，經常會出現這類字符，CODE可以測試其并非

56、空格對于這類不可見字符，可以先復制該字符，然后替換該字符為空白可以使用鼠標選定幾個單元格，看狀態欄的合計。有，則為數值，無，則為文本。這兩個錯誤都是由于格式不一致造成，一個是按數值查文本，一個是按文本查數值對于此類錯誤，可以修改查找值、數據區的格式，使之統一；也可以在公式中處理要注意的是采用修改格式的方法，僅僅將單元格格式更改還不行：例如對于包含數值的常規單元格將格式改為文本后，單元格中的值仍是數值形式,需要激活（雙擊）才會真正轉為文本。單元格很多時，采用逐個激活的方法肯定會累死。可以采用分列或選擇性粘貼的方法：當某列數據需要全部轉換時，采用分列是個好辦法。它可以將文本轉為數值，也可將數值轉為

57、文本。復制一空白單元格，選定需轉換的數據區，選擇性粘貼加當確定應該出現#N/A（即查找值不存在）時，如果不要顯示錯誤，可以使用條件格式或直接在公式中處理注意使用條件格式處理后單元格內的值仍是#N/A，其他單元格引用該單元格也會返回#N/A錯誤。這個單元格使用條件格式處理，條件格式公式isna(D55)，并設定條件字體顏色與底色相同。使用ISNA測試vlookup函數是否返回#N/A，如vlookup函數返回#N/A，則ISNA函數返回TRUE。再使用IF函數判斷，即可去除錯誤。精確查找時，也可以直接判斷查找值是否存在來去除#N/A錯誤，如此例使用COUNTIF測試數據區是否有查找值。近似匹配查

58、找時，除了前述的格式等原因，查找值小于數據區的最小值將返回#N/A。對于此類錯誤，只要數據區設計合理就可避免（建立可能的最小值）,當然也可以使用ISNA去除。本月員工工資表員工編號 員工姓名 性別 年齡所屬部門職位固定工資 福利津貼 全勤獎金NO.0001胡林男26人力資源部助理￥2,500￥800￥0NO.0002鄭擇意男39行政部經理￥4,500￥1,200￥200NO.0003熊雨寒女35銷售部經理￥4,700￥1,200￥200NO.0004王安娜女28人力資源部專員￥2,500￥800￥0NO.0005范國毅男27技術部主管￥2,500￥1,200￥0NO.0006姜志剛男34技術部

59、經理￥5,100￥1,200￥0NO.0007王肖男30銷售部主管￥2,500￥1,200￥200NO.0008鄧小欣女31人力資源部主管￥2,500￥1,200￥200NO.0009蹇楓霞女25銷售部業務員￥2,000￥800￥0NO.0010李良慧女29銷售部主管￥2,500￥1,200￥200NO.0011戴文蘭女31行政部助理￥2,500￥800￥200NO.0012王珊女28行政部文員￥1,700￥800￥200NO.0013蘇美珍女30技術部主管￥2,500￥1,200￥200NO.0014徐雙男29技術部技術員￥3,000￥800￥0NO.0015張坤男28銷售部業務員￥2,00

60、0￥800￥0NO.0016鄧小強男34人力資源部經理￥4,700￥1,200￥200NO.0017鄭青松男26技術部技術員￥3,000￥800￥0NO.0018王曉東男29銷售部業務員￥2,000￥800￥200NO.0019王強男30銷售部業務員￥2,000￥800￥0NO.0020張玲琳女27行政部文員￥1,700￥800￥0應扣工資 應繳社保 實發工資￥70￥275￥2,955￥0￥495￥5,405￥0￥517￥5,583￥10￥275￥3,015￥20￥275￥3,405￥102￥561￥5,637￥0￥275￥3,625￥0￥275￥3,625￥80￥220￥2,500￥0￥27

61、5￥3,625￥0￥275￥3,225￥0￥187￥2,513￥0￥275￥3,625￥30￥330￥3,440￥10￥220￥2,570￥0￥517￥5,583￥120￥330￥3,350￥0￥220￥2,780￥20￥220￥2,560￥64￥187￥2,249員工工資查詢表請選擇員工姓名:王珊性別女年齡28所屬部門行政部職位文員固定工資1700福利津貼800全勤獎金200應扣工資0應繳社保187實發工資2513部部門門金金額額業務1部76,400業務1部109,800業務1部88,400業務1部137,000業務1部42,600業務1部56,200業業務務1 1部部 匯匯總總510,40

62、0業務2部109,000業務2部97,200業務2部111,400業務2部35,000業務2部14,400業務2部4,800業務2部9,000業務2部22,600業業務務2 2部部 匯匯總總403,400業務3部37,600業務3部33,200業務3部37,000業務3部155,000業務3部14,200業務3部64,200業務3部9,800業務3部9,600業務3部114,400業業務務3 3部部 匯匯總總475,000總總計計1,388,800工資發放零鈔備用表員工編號 員工姓名本月應發工資100元張數50元張數20元張數10元張數5元張數NO.0001胡 林2,955.63291001N

63、O.0002鄭擇意5,405.83540001NO.0003熊雨寒5,583.53551110NO.0004王安娜3,015.63300011NO.0005范國毅3,405.85340001NO.0006姜志剛5,637.83560111NO.0007王 肖3,625.53360101NO.0008鄧小欣3,625.52360101NO.0009蹇楓霞2,500.53250000NO.0010李良慧3,625.53360101NO.0011戴文蘭3,225.63320101NO.0012王 珊2,513.63250010NO.0013蘇美珍3,625.38360101NO.0014徐 雙3,4

64、40.83340200NO.0015張 坤2,570.63251100NO.0016鄧小強5,583.83551110NO.0017鄭青松3,350.63331000NO.0018王曉東2,780.83271110NO.0019王 強2,560.83251010NO.0020張玲琳2,249.83220201本月工資發放所需各面值鈔票張數7057147112元張數1元張數5毛張數2毛張數1毛張數0010100111返回商的整數部分，該函數可用于舍掉商的小數部分11100001010011110111001000010000100001000010111101000110011100101111

65、11001010011100111201116319915返回商的整數部分，該函數可用于舍掉商的小數部分起始日終止日特殊間隔具體數值2001/1/12003/1/1相差年數22001/6/12002/8/15計算實際相差天數4402001/6/12002/8/15忽略年份計算天數752001/6/12002/8/15忽略年份和月份計算天數14起起始始日日期期計計算算要要求求計計算算結結果果2006/10/2510個工作前日期2006/10/112006/10/2510個工作后日期2006/11/8起起始始日日期期終終止止日日期期節節假假日日結結果果計計算算2005/5/12005/5/3120

66、05/5/1202005/5/22005/5/3加加班班記記錄錄表表日期姓名所屬部門 起始時間結束時間累計小時數加班費1/Jun/09劉琴玉生產部7:30:00 PM10:00:00 PM 2:30:00￥375.003/Jun/09黃平峰行政部6:20:00 PM9:45:30 PM 3:25:30￥513.755/Jun/09楊玉楷行政部6:10:12 PM11:10:11 PM 4:59:59￥749.967/Jun/09何醉雨人事部6:21:22 PM7:11:20 PM 0:49:58￥124.928/Jun/09劉琴玉生產部7:15:20 PM11:21:11 PM 4:05:51

67、￥614.639/Jun/09黃平峰行政部7:55:30 PM11:20:22 PM 3:24:52￥512.1710/Jun/09楊玟玟研發部7:20:33 PM10:30:25 PM 3:09:52￥474.6712/Jun/09何醉雨人事部6:23:20 PM11:25:20 PM 5:02:00￥755.0013/Jun/09羅峰研發部6:20:30 PM10:24:25 PM 4:03:55￥609.7913/Jun/09何溴顏生產部7:30:20 PM12:20:00 AM 4:49:40￥724.1713/Jun/09陳哲宇行政部7:55:00 PM10:44:22 PM 2:4

68、9:22￥423.4218/Jun/09何醉雨人事部6:22:23 PM7:20:25 PM 0:58:02￥145.0818/Jun/09羅平平研發部6:26:25 PM11:21:00 PM 4:54:35￥736.4619/Jun/09謝玉漺研發部7:44:20 PM11:22:11 PM 3:37:51￥544.6320/Jun/09羅峰研發部8:30:22 PM10:24:00 PM 1:53:38￥284.0820/Jun/09劉琴玉生產部7:20:11 PM11:20:22 PM 4:00:11￥600.4621/Jun/09楊玨人事部8:10:10 PM10:30:00 PM

69、2:19:50￥349.5823/Jun/09鄧容光生產部7:20:20 PM11:20:00 PM 3:59:40￥599.1723/Jun/09劉淸湨行政部8:25:20 PM10:20:20 PM 1:55:00￥287.5024/Jun/09黃平峰行政部7:22:23 PM11:10:10 PM 3:47:47￥569.4625/Jun/09何峰生產部7:22:22 PM11:21:22 PM 3:59:00￥597.5028/Jun/09何勇生產部8:10:11 PM11:10:14 PM 3:00:03￥450.1328/Jun/09陳哲宇行政部8:21:00 PM10:20:10

70、 PM 1:59:10￥297.9229/Jun/09陳好人事部8:21:23 PM10:10:20 PM 1:48:57￥272.3729/Jun/09劉碟研發部8:41:10 PM10:23:20 PM 1:42:10￥255.42核準人事由劉康玉新項目2:30:00 AM 2:30劉康玉新項目3:25:30 AM 3:25劉康玉新項目4:59:59 AM 4:59劉康玉新項目12:49:58 AM 0:49劉康玉新項目4:05:51 AM 4:05劉康玉新項目3:24:52 AM 3:24劉康玉新項目3:09:52 AM 3:09劉康玉返回項目5:02:00 AM 5:02何玉嵧返回項目

71、4:03:55 AM 4:03何玉嵧返回項目4:49:40 AM 4:49何玉嵧返回項目2:49:22 AM 2:49何玉嵧新項目何玉嵧新項目劉康玉新項目比較2個的區別劉康玉新項目劉康玉新項目劉康玉新項目劉康玉新項目何玉嵧返回項目何玉嵧返回項目何玉嵧返回項目何玉嵧返回項目何玉嵧新項目劉康玉新項目劉康玉新項目4#2017/3/12#2017/3/19weekday返回某日期為星期幾。默認情況下，其值為 1（星期天）到 7（星期六）之間的整數。今天星期幾今天減掉幾天，即為前一個周日員員 工工 年年 假假 表表日日期期2017/3/162017/3/16 13:4113:41編編號號姓姓名名性性別別

72、部部門門入入司司時時間間工工齡齡年年假假天天數數0001張燁男市場部2002/7/114230002吳畏男行政部2002/7/1514230003李佳女辦公室2004/3/113220004王凱男策劃部2001/6/1015240005劉愷男市場部2000/2/2517260006蔡卓女辦公室2004/3/113220007曾蒙男市場部2000/5/1016250008鄭文女服務部2003/6/913220009黃采男行政部1999/8/2317260010王柳女人資部2002/7/1214230011章冬男人資部 2003/10/1513220012孫蕊女服務部2003/4/213220013柳風男市場部2001/6/315240014李東男行政部2003/5/11322