| 作者：彭茶林 來源：eNet矽谷動力 加入時間：2006-08-10訪問次數：8615次 |
|
| 說起是否會使用Excel，估計只要用過電腦的人都說自己會用。真的是這樣嗎？就筆者的多年用戶技術支援的經驗，很多用戶所謂的「會用」，真的只是僅僅局限於「會用」而己。輸入一些內容，加上一些表格格線，點一下存檔，瞧，文件做好了。 如果只是這樣，微軟公司就不用煞費苦心，開發什麼新版本了。早期的Excel 5.0就己經綽綽有餘了。 其實，Excel是一個功能非常強大的數據處理系統，決不只是一個拿來畫個表格的表格繪製工具。如果真正掌握了Excel的所有功能，你會發現，很多重複的，煩悶的數據處理過程，會變得非常美妙，你只需點點或拖拖滑鼠，然後靠在椅背上啜口咖啡，本來會忙得你暈天黑地的任務己讓系統幫你處理好了。這時，看著仍忙得一塌湖塗的同事，你只有一個感覺：酷…… 要真正發揮Excel的威力，你必須掌握Excel的靈魂―函數。不會使用Excel的函數，你就不是一個真正的會用Excel的用戶。正是一個個功能各異的函數，組成的不同的公式，才讓Excel這位大俠，有了絕世武功。每一個函數，便是他的一招絕學，但只單單學會他的招術，也還是不夠，只有把所有招術練熟了，並綜合運用，融會貫通，做到招中有招，才能真正掌握他的絕世武功，讓Excel自動幫我們完成無法完成的任務。 下面，我們便來開始學心Excel大俠的絕學。我會先一招一招地介紹，幾招之後，再演示怎麼綜合應用所學的幾招，創建新招。在實際對敵中，我們要知道，招是死的，人是活的，招由心生，綿綿不絕。 第一招：大海撈針（Vlookup函數） 招如其名。此招用來在一個茫茫的數據源中，自動讓電腦找出你要的某個數據的相關資料並填在指定的地方。也是就是，可以讓電腦在一個表格或指定的一個區域中查找某一指定的值，並由此返回該值相對應當前行中指定列處的數值。此招還有相應的變化，分別為lookup和Hlookup兩式。當查找的數據是水平排列時，可以使用函數 HLOOKUP 代替函數 VLOOKUP。但用到的情況比較少，這裡不做介紹。 例如：你有一個工作表，上面近萬項貨品名稱，每項貨品的代碼，價格，購買日期等。如你要做另一份報表，且其中部分貨品在這份表中己有數據，則可以利用此招，只輸入貨品的名稱或者代碼，其餘的讓電腦自動查找並返回相應的價格，購買日期等。 使用語法 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value 為需要查找的值。Lookup_value 可以為數值、引用或文本字元串。Table_array 為需要在其中查找數據的數據表。可以使用對區域或區域名稱的引用，例如資料庫或列表。 如果 range_lookup 為 TRUE，則 table_array 的第一列中的數值必須按升序排列：…、-2、-1、0、1、2、…、-Z、FALSE、TRUE；否則，函數 VLOOKUP 不能返回正確的數值。如果 range_lookup 為 FALSE，table_array 不必進行排序。 通過在「數據」菜單中的「排序」中選擇「升序」，可將數值按升序排列。 Table_array 的第一列中的數值可以為文本、數字或邏輯值。文本不區分大小寫。 Col_index_num 為 table_array 中待返回的匹配值的列序號。Col_index_num 為 1 時，返回 table_array 第一列中的數值；col_index_num 為 2，返回 table_array 第二列中的數值，以此類推。如果 col_index_num 小於 1，函數 VLOOKUP 返回錯誤值值 #VALUE!；如果 col_index_num 大於 table_array 的列數，函數 VLOOKUP 返回錯誤值 #REF!。 Range_lookup 為一邏輯值，指明函數 VLOOKUP 返回時是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小於 lookup_value 的最大數值；如果 range_value 為 FALSE，函數 VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。 說明如果函數 VLOOKUP 找不到 lookup_value，且 range_lookup 為 TRUE，則使用小於等於 lookup_value 的最大值。 如果 lookup_value 小於 table_array 第一列中的最小數值，函數 VLOOKUP 返回錯誤值 #N/A。 如果函數 VLOOKUP 找不到 lookup_value 且 range_lookup 為 FALSE，函數 VLOOKUP 返回錯誤值 #N/A。 應用示例： 
上圖中，為方便比較，我將原始數據區域放在了同一工作表中（E1:F5）,實際使用時，原始數據可以在不同的工作表，甚至不同的工作簿（即不同的Excel文件）。當被查找的內容與原始內容在不同的工作表，table_array前面需加上工作表的名稱，寫法為 「表名! 」區域範圍，如「Sheet2!$A$1:$B$12」，而若在不同的工作簿，則還得加上文件名，如「[文件名]sheet1!$A$1:$B$12」。 詳細解釋 公式「=Vlookup(A2,$E$2:$F$5,2,FALSE)」中A2表示要查找的值為A2單元格的內容，即「Apple」, 「$E$2:$F$5」告訴電腦，應該去$E$2:$F$5這個數據區域中查找，「2」表示找到後，應傳回該區域第二列的值，即數量列，最後「FALSE」參數系統，查找區域內容未進行排序，需使用精確查找，找不到就算了，不返回近似匹配值。 特別要注意的是，通常我們都是使用滑鼠拖動的方法來填充公式，而拖動時，Excel對公式中區域的引用，處理方法是不一樣的。如果是相對參照，即欄名列號前沒有「$」符號，則Excel會對該區域作相對位移，如上欄是E2:B5,拖到下欄後，即會自動成為E3:B6，這種處理方法在很多公式中是必要的，但在這個公式中卻是致命的，因為它更改了查找的原始數據的區域，導致實際上包含有的數據，因己不在查找的區域中而漏網。這也是很多用戶在實際應用中犯的錯誤，引致查找結果不真實。要解決這個問題，我們可以利用Excel對區域引用的第二種方法：絕對參照。即在欄名列號前加上「$」，這樣，系統就不會作相對的位移，無論怎樣拖，區域範圍都不變。（在很多情況下，我們會使用「名稱」來代替直接的區域指定方式，使用更為方便。這一內容將在其他章節中介紹） 相對參照與絕對參照的寫法，可以讓電腦作自動轉換。方法是，先將當前單元格定位在要修改的單元格上，然後在資料編輯列，用滑鼠塗黑（英文的說法叫Highlight）要轉換的部分，再按「F4」即可。見下圖： 
通過上圖可以看出，能找到的，系統己自動填入了找到的值，如Apple & cherry，對於找不到的（Plum & Pear），則顯示#N/A。 第二招：左右逢源（If函數）此招用來對某一條件執行的真假值進行判斷，根據邏輯計算的真假值，返回不同結果。如果結果為真，則返回一個真，如果為假，則返回另一值，可謂左右逢源。 使用語法 IF(logical_test,value_if_true,value_if_false) Logical_test 表示計算結果為 TRUE 或 FALSE 的任意值或表達式。例如，A1>=60 就是一個邏輯表達式，如果單元格 A1 中的值大於或等於 60，表達式即為 TRUE，否則為 FALSE。本參數可使用任何比較運算符。 Value_if_true logical_test 為 TRUE 時返回的值。例如，如果本參數為文本字元串「預算內」而且 logical_test 參數值為 TRUE，則 IF 函數將顯示文本「預算內」。如果 logical_test 為 TRUE 而 value_if_true 為空，則本參數返回 0（零）。如果要顯示 TRUE，則請為本參數使用邏輯值 TRUE。Value_if_true 也可以是其他公式。Value_if_false logical_test 為 FALSE 時返回的值。例如，如果本參數為文本字元串「超出預算」而且 logical_test 參數值為 FALSE，則 IF 函數將顯示文本「超出預算」。如果 logical_test 為 FALSE 且忽略了 Value_if_false（即 value_if_true 後沒有逗號），則會返回邏輯值 FALSE。如果 logical_test 為 FALSE 且 Value_if_false 為空（即 value_if_true 後有逗號，並緊跟著右括弧），則本參數返回 0（零）。Value_if_false 也可以是其他公式。說明 函數 IF 最多可以嵌套七層，用 value_if_false 及 value_if_true 參數可以構造複雜的檢測條件。 在計算參數 value_if_true 和 value_if_false 後，函數 IF 返回相應語句執行後的返回值。 應用示例： 
第三招：投石問路（IS函數）此招用來對某個單元格的當前值的類型進行判斷，以便知道其類型後，再採取下一部行動，因此稱為投石問路。IS函數共有九個工作表函數。概括為 IS 類函數，可以檢驗數值的類型並根據參數取值返回 TRUE 或 FALSE。例如，如果數值為對空白單元格的引用，函數 ISBLANK 返回邏輯值 TRUE，否則返回 FALSE。 使用語法 ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) Value 為需要進行檢驗的數值。分別為：空白（空白單元格）、錯誤值、邏輯值、文本、數字、引用值或對於以上任意參數的名稱引用。 
應用示例： 
詳細解釋 公式「=ISBLANK(A1)」，表示對A1單元格是否為空進行判斷。如是是空的，則返回「True」值，如果不為空，則返回 「False)的值。上面示例圖中，B1及B2單元格中的函數分別對A1及A2單元格進行是否為空白的判斷。結果顯示一個為真，一個為假。OK，我們己經學了三招，現在我們要將這三招組合起來，自創一招新招。第四招：瞞天過海（自創組合招數） 我們來看學第一招時用的例子， 
對於找不到的項目，系統顯示#N/A，但這樣的報告交給上司，未免太難看了些。用什麼方法，可以讓其不顯示出錯誤值呢？對了，先來一招投石問路，對系統返回的值做一個判斷，看看系統到底找到沒有。再來一招左右逢源，對於找到的就顯示原值，找不到的，就乾脆讓它顯示空白（當然，也可讓設置其他的值如No等），豈不妙哉？ 因此，對於原單一公式：=VLOOKUP(A2,$E$2:$F$5,2,FALSE)，可以結合IF和IS函數來使用。大家剛才看到，對於投石問路，共有九種變化，其中第三式（ISERROR）或第五式（ISNA）均適合這種情況，可以使用。因此，組合後的公式就變成：=IF(ISNA(VLOOKUP(D2,$G$2:$H$5,2,FALSE)),"",VLOOKUP(D2,$G$2:$H$5,2,FALSE)) 或 =IF(ISERROR(VLOOKUP(D3,$G$2:$H$5,2,FALSE)),"",VLOOKUP(D3,$G$2:$H$5,2,FALSE)) 下圖顯示了這種情況。紅框中用的就是組合的公式，而其中的Plum & Pear沒有再顯示難看的#N/A，報表因此漂亮多了。 
因為是公式，只要寫好第一個單元格的公式，其餘的一拖就好了。好了，新創的這招叫什麼呢？就叫瞞天過海吧！ |
|
|
