|
第一部分 學(xué)習(xí)心得
本節(jié)課程，ychexcel 老師以VLOOKUP函數(shù)為主，著重介紹了VLOOKUP函數(shù)的參數(shù)和查詢方式、查詢技巧。 VLOOKUP函數(shù) 用途：使用 VLOOKUP 函數(shù)搜索某個(gè)單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何單元格中的值。 語法：VLOOKUP(查找值,查找區(qū)域,列數(shù),[查找方式]) 參數(shù)： 查找值：必需。要在表格或區(qū)域的第一列中搜索的值。查找值 參數(shù)可以是值或引用。如果為查找值 參數(shù)提供的值小于查找區(qū)域 參數(shù)第一列中的最小值，則 VLOOKUP 將返回錯(cuò)誤值 #N/A。 查找區(qū)域：必需。包含數(shù)據(jù)的單元格區(qū)域。可以使用對(duì)區(qū)域（例如，A2:D8）或區(qū)域名稱的引用。查找區(qū)域 第一列中的值是由 查找值 搜索的值。這些值可以是文本、數(shù)字或邏輯值。文本不區(qū)分大小寫。 列號(hào)：必需。查找區(qū)域 參數(shù)中必須返回的匹配值的列號(hào)。列數(shù) 參數(shù)為 1 時(shí)，返回 查找區(qū)域 第一列中的值；列號(hào) 參數(shù)為 2 時(shí)，返回 查找區(qū)域第二列中的值，依此類推。 如果 列號(hào) 參數(shù)： 小于 1，則 VLOOKUP 返回錯(cuò)誤值 #VALUE!。 大于 查找區(qū)域 的列數(shù)，則 VLOOKUP 返回錯(cuò)誤值 #REF!。 查找方式：可選。一個(gè)邏輯值，指定希望 VLOOKUP 查找精確匹配值還是近似匹配值： 如果 查找方式 為 TRUE 或被省略，則返回精確匹配值或近似匹配值。如果找不到精確匹配值，則返回小于查找值 的最大值。 要點(diǎn) 如果 查找方式 為 TRUE 或被省略，則必須按升序排列 查找區(qū)域 第一列中的值；否則，VLOOKUP 可能無法返回正確的值。 如果 查找方式 為 FALSE，則不需要對(duì)查找區(qū)域 第一列中的值進(jìn)行排序。 如果 查找方式 參數(shù)為 FALSE，VLOOKUP 將只查找精確匹配值。如果 查找區(qū)域 的第一列中有兩個(gè)或更多值與 查找值 匹配，則使用第一個(gè)找到的值。如果找不到精確匹配值，則返回錯(cuò)誤值 #N/A。 注釋： 在 查找區(qū)域 的第一列中查找文本值時(shí)，要確保 查找區(qū)域 第一列中的數(shù)據(jù)不包含前空格、尾空格、非打印字符或者未使用不一致的直引號(hào)（' 或 "）與彎引號(hào)（‘ 或 “）。在搜索數(shù)字或日期值時(shí)，應(yīng)確保查找區(qū)域 第一列中的數(shù)據(jù)未存儲(chǔ)為文本值。否則，VLOOKUP 可能返回不正確或意外的值。 如果 查找方式 為 FALSE 且 查找值 為文本，則可以在查找值 中使用通配符 － 問號(hào) (?) 和星號(hào) (*)。問號(hào)匹配任意單個(gè)字符；星號(hào)匹配任意字符序列。如果要查找實(shí)際的問號(hào)或星號(hào)，要在問號(hào)（？）或星號(hào)（*）字符前鍵入波形符 (~)。 通過反復(fù)學(xué)習(xí)查找函數(shù)的幫助文件，本人深沉體會(huì)到： 1、學(xué)習(xí)EXCEL，幫助文件是離自己最近的老師。 2、弄懂函數(shù)參數(shù)的意義，是學(xué)好函數(shù)、熟練使用函數(shù)的先決條件，否則，在構(gòu)建函數(shù)時(shí)可能得不到正確的結(jié)果，特別是在函數(shù)嵌套時(shí)，往往出錯(cuò)。 3、多在EH論壇上查看別人回答提問者帖子，拜讀賢哲們的公式構(gòu)建技巧、理解大師們的解題思路，是學(xué)好EXCEL函數(shù)的捷徑。 4、參與回答別人的提問，是幫助別人解決問題、提高自己EXCEL水平的兩利的行為。同時(shí)又是加深自己對(duì)函數(shù)的記憶和理解的最佳方法。 通過本次學(xué)習(xí)對(duì)查找引用函數(shù)參數(shù)重新加深了認(rèn)識(shí)： 1、reference參數(shù)一般地為單元格區(qū)域的引用;也可以為INDIRECT函數(shù)或OFFSET函數(shù)構(gòu)建的單元格或單元格區(qū)域。 ２、Array參數(shù)可以為 單元格區(qū)域、數(shù)組常量。 ３、Lookup_value為查找值【可以為數(shù)值、引用或文本字符串】;table_array為查找區(qū)域【可以為區(qū)域、常量數(shù)組、區(qū)域名稱或數(shù)組名稱】;row_index_num為查找的行號(hào)【一般為數(shù)字】;col_index_num為查找的列號(hào)【一般為數(shù)字】、range_lookup為查找方式【一般為邏輯值】;row_num、col_num、index_num等這些含num的參數(shù)【一般為數(shù)值】。 對(duì)OFFSET函數(shù)的基本理解： 語法 OFFSET(reference, rows, cols, [height], [width]) 
① Reference ② Rows ③ Cols ④ Height ⑤ Width ②~⑤的數(shù)值設(shè)置一定不可越出EXCEL的邊界，否則，就會(huì)出現(xiàn)#REF!
第二部分 課后作業(yè)與課前練習(xí)解題思路 A、課后作業(yè)鑰匙思路 第一題：暫時(shí)做了六種解法 １、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A5),VLOOKUP($A5,數(shù)據(jù)區(qū)!$A:$G,COLUMN(),),"不存在") 采用VLOOKUP函數(shù)普通用法，用COLUMN()獲得動(dòng)態(tài)列號(hào)是這一公式的核心，設(shè)置絕對(duì)引用和混合引用是關(guān)鍵。 ２、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A$5),OFFSET(數(shù)據(jù)區(qū)!$A$1,MATCH($A$5,數(shù)據(jù)區(qū)!$A:$A,)-1,MATCH(B$4,數(shù)據(jù)區(qū)!1:1,)-1),"不存在") 采用OFFSET函數(shù)，用MATCH函數(shù)獲取偏移的動(dòng)態(tài)行、列數(shù)。 ３、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A5),INDEX(數(shù)據(jù)區(qū)!$A:$G,MATCH($A$5,數(shù)據(jù)區(qū)!$A:$A,),COLUMN()),"不存在") 采用INDEX函數(shù)數(shù)組形式，用MATCH函數(shù)獲得動(dòng)態(tài)行數(shù)，用COLUMN()獲得動(dòng)態(tài)列數(shù)。 ４、=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A$5),INDIRECT("數(shù)據(jù)區(qū)!R"&MATCH($A$5,數(shù)據(jù)區(qū)!$A:$A,)&"C"&MATCH(B$4,數(shù)據(jù)區(qū)!1:1,),),"不存在") 采用INDIRECT函數(shù)的R1C1－樣式構(gòu)建公式，用MATCH函數(shù)獲取R1C1－樣式的動(dòng)態(tài)數(shù)字。 ５、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A5),VLOOKUP($A5,數(shù)據(jù)區(qū)!$A:$G,COLUMN(B:G),),"不存在") 采用了VLOOKUP函數(shù)的數(shù)組解法，而COLUMN(B:G)函數(shù)是構(gòu)成本數(shù)組函數(shù)的關(guān)鍵。 ６、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A$5),HLOOKUP(B$4,數(shù)據(jù)區(qū)!$B:$G,MATCH($A5,數(shù)據(jù)區(qū)!$A:$A,),),"不存在") 采用HLOOKUP函數(shù)的普通解法，關(guān)鍵在于選對(duì)MATCH函數(shù)的Lookup_value參數(shù)的值。 以上公式的排錯(cuò)方法避免了一直延用的IF(ISNA(公式),”不存在”,公式)的用法，而采用了IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A$5),公式,"不存在")的方式，從而縮短了公式長(zhǎng)度，減少了計(jì)算機(jī)內(nèi)存的使用，提高了EXCEL的運(yùn)算速度。 第二題、暫時(shí)做了五種解法 １、解題公式=INDIRECT(ADDRESS(MATCH(A4,數(shù)據(jù)區(qū)!A:A,),MATCH(B4,數(shù)據(jù)區(qū)!$1:$1,),,,"數(shù)據(jù)區(qū)")) 用INDIRECT+ADDRESS+MATCH構(gòu)建公式，而兩個(gè)MATCH函數(shù)是核心公式。 ２、解題公式=OFFSET(數(shù)據(jù)區(qū)!$A$1,MATCH(A4,數(shù)據(jù)區(qū)!A:A,)-1,MATCH(B4,數(shù)據(jù)區(qū)!$1:$1,)-1) 采用OFFSET函數(shù)，用兩個(gè)MATCH函數(shù)獲得動(dòng)態(tài)的偏移量是關(guān)鍵。 ３、鑰匙公式=INDEX(數(shù)據(jù)區(qū)!$B$2:$G$11,MATCH(A4,數(shù)據(jù)區(qū)!A:A,)-1,MATCH(B4,數(shù)據(jù)區(qū)!$1:$1,)-1) 采用INDEX函數(shù)的數(shù)組形式，用兩個(gè)MATCH函數(shù)獲得動(dòng)態(tài)的行數(shù)和列數(shù)。 ４、解題公式=HLOOKUP(B4,數(shù)據(jù)區(qū)!$B:$G,MATCH(A4,數(shù)據(jù)區(qū)!A:A,),) 采用HLOOKUP函數(shù)普通用法，用MATCH函數(shù)獲取動(dòng)態(tài)行號(hào)是核心。 ５、解題公式=VLOOKUP(A4,數(shù)據(jù)區(qū)!A:G,MATCH(B4,數(shù)據(jù)區(qū)!$1:$1,),) 采用VLOKOKUP函數(shù)普通用法，用MATCH函數(shù)獲取動(dòng)態(tài)列號(hào)是本公式的核心公式。 從上面的公式來看，MATCH函數(shù)，在查找引用函數(shù)中有著非常重要的作用。 第三題、=CHOOSE(MOD(ROW()-3,3)+1," ",數(shù)據(jù)區(qū)!A$1,INDEX(數(shù)據(jù)區(qū)!A:A,ROUNDUP((ROW()-3)/3,)+1)) 采用CHOOSE函數(shù)特性構(gòu)建公式，CHOOSE函數(shù)第一參數(shù)有“如果 index_num為小數(shù)，則在在使用前將被截尾取整”的特性，因而，在此用MOD(ROW()-3,3)+1計(jì)算出CHOOSE函數(shù)取數(shù)的位置，由于要求的結(jié)果表格位于第四行，故而MOD函數(shù)中用了ROW()-3與３[３是受工資條設(shè)置要求影響的]的余數(shù)，然后加１，加１的作用是為了排錯(cuò)，因?yàn)槿鏜OD(６,3)，其結(jié)果為0，將導(dǎo)致公式出錯(cuò)[ 幫助文件中：如果 index_num 小于 1 或大于列表中最后一個(gè)值的序號(hào)，函數(shù) CHOOSE 返回錯(cuò)誤值 #VALUE!]。 解決了第一參數(shù)的問題，剩余的就是設(shè)置CHOOSE函數(shù)的第二、三、四參數(shù)了。 第二參數(shù)設(shè)置了空行。 第三參數(shù)也非常簡(jiǎn)單，就是為了求出工資條的表頭。 第四參數(shù)用INDEX函數(shù)構(gòu)建，其核心公式為ROUNDUP(ROW()-3)/3,)，ROUNDUP函數(shù)是按要求向上舍入的函數(shù)，從而取得INDEX函數(shù)的取值行號(hào)。
B、課前練習(xí)解題思路 練習(xí)一、
練習(xí)二
練習(xí)三
練習(xí)四
二〇一〇年九月二十三日 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
