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