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