Excel函數集

Excel函數集

1.禁止自動運行宏的方法

　　你可能遇到過可惡的宏病毒，其中有一部分是在打開文件時自動運行併產生危害。

　　在您打開一個Excel文件時，可以很容易地阻止一個用VBA寫成的在打開文件時自動運行的宏的運行。從「文件」菜單中選擇「打開」，選擇您所要打開的文件的名字，在點擊 「打開」時按住［Shift］鍵，Excel將在不運行VBA過程的情況下，打開該工作薄。 按住［Shift］鍵阻止宏運行的方法同樣適用於選擇打開菜單底部的文件（最近打開的幾個文件） 。同樣，在您關閉一個Excel文件時，也可以很容易地阻止一個用VBA寫成， 在關閉文件時自動運行的宏的運行。從「文件」菜單中選擇「關閉」，在點擊 「關閉」時按住［Shift］鍵，Excel將在不運行VBA過程的情況下關閉這個工作薄。 （按住［Shift］鍵同樣適用於點擊窗口右上角的「×」關閉工作薄時阻止宏的運行。

2.顯示更多工作表的技巧

　　在屏幕上顯示許多工具欄使得你不能顯示更多的數據。你並不需要將工具條從屏幕中移去和極大化Excel窗口，而只需在「視圖」菜單中選擇「全屏顯示」。Excel工作表充滿整個窗口時，工具條將消失，只在屏幕頂部顯示菜單。（要想一次看到更多的單元格，可以從「視圖」菜單中選擇「顯示比例」，選定一個較小的百分比，然後點擊「確定」。）恢復原始的窗口大小和工具條顯示，只需通過點擊「全屏顯示」工具條中的「關閉全屏顯示」或從「視圖」再次選擇「全屏顯示」關閉全屏幕窗口。

3.計算Excel公式的一部分

　　下一次你需要在Excel中調試一個複雜的公式或者只是想知道一個在公式中引用的單元格的值時，試著這樣做：選定含有公式的單元格並按［F2］（或者簡單地雙擊那個單元格） 。 然後，拖亮等式中需要檢查的部分或公式引用的單元格，按［F9］Excel就會將被拖亮的部分替換成計算的結果。按［Ctrl＋Z］可以恢復剛才的替換。

　　你可以用同樣的方式替換其他部分直到你找到那個出現錯誤的公式結果為止。 （按［F2］後馬上按［F9］，或者拖過整個公式按［F9］。）

　　例如，假設你選定了含有公式「Κ（12×12）／（1＋Rate）λA20的單元格。按［F2］，拖亮等式12×12，並按［F9］。Excel將公式的那部分轉換為144。這時，公式就變成Κ（144）／（1＋Rate）λA20。同樣，拖亮單元格引用A20，並按［F9］，Excel將其替換成工作表中的值。 當你完成了公式的計算部分，如果想保留原來的公式， 按［Esc］，如果想替換公式，按［Enter］。你還可以按［Ctrl＋Z］來恢復修改。

4.使一個單元格合適全部字體

　　有時你並不希望單元格隨著其中的內容放大或縮小（如在使用折行排列時），需要時數據恰好充滿單元格事先設置好的高度和寬度。不用嘗試的方法去達到合適的字體大小， 你可以使得Excel的單元格自動地調整字體來變得合適。只需選擇一個或多個單元格選擇「格式－單元格」。選擇「對齊」標籤，選中「縮小字體填充」選項。但你不能同時選擇「縮小字體填充」和「自動換行」，因為他們是對立選項。

5.在拖放操作中使用［Alt］鍵的作用

當拖動單元格時，按住［Alt］鍵能夠起到以下兩個作用：

　　1．防止當你在工作表邊緣拖動時屏幕的滾動失去控制。

　　2．這樣你可以將單元格拖動到另外一個工作表中。按住［Alt］鍵，將你所要移動的單元格拖到目標工作表的標籤處。Excel會激活那個工作表使你能夠在其中選擇拖放點。在不同的單元格中進行複製，需要在拖動時同時按住［Ctrl］和［Alt］鍵（這種操作對拖動圖表和對象時無效）。

6.自動格式化工作表

　　EXCEL提供了16種格式供選擇， 在格式化之前要先選擇範圍，如果要對整個工作表格式化可不選擇， 因為用戶不選擇範圍時，EXCEL自動選擇整個工作表。方法是執行「格式」菜單下的「自動套用格式」，將出現「自動套用格式」對話框，每選擇一種格式可在右邊的示例中觀看效果，若滿意可單擊「確定」按鈕。

7.利用「自動套用格式」定製自已的格式

　　如果用戶要定製的格式和EXCEL提供某一格式類似但有少許不同， 可首先選擇某一個格式，然後單擊「選項」按鈕，將需要自己定製的格式不選擇，比如如果你想自己定義字體， 不使用EXCEL提供的字體，可將字體前的複選框清除，然後單擊「確定」按鈕，回到工作表中再定義自己的字體，這樣可減少手工定製格式的工作量。

8.自定義數字顯示格式

　　可自定義數字格式，方法是選擇好範圍之後，單擊滑鼠右鍵，在快捷菜單中選擇「單元格格式」，在對話框中選擇「數字」選項卡，單擊自定義，在右邊「類型」中可輸入自定義的數字格式，此處你需要了解自定義格式中常用符號的意義，可以通過選擇其它已有分類觀看「示例」來得知符號的意義。例如，筆者在對日期進行格式化時，想找到這樣的格式97－06－01，但已有格式中沒有找到這種格式，於是採用自定義的方法， 首先通過觀察發現y－表示年，m－表示月，d－表示日，然後在自定義框中輸入yy－mm－dd，這樣所有日期都變成8個字元了，便於查看。

9.定製數據對齊格式

　EXCEL提供的格式工具欄上提供了 「左對齊」、「右對齊」、「居中」、「跨行居中」四種方式，這只是常用的四種方式。如果你想使用其它的方式，可選擇好範圍之後，單擊滑鼠右鍵，在快捷菜單中選擇「單元格格式」，在對話框中選擇「對齊」選項卡，此處可提供水平對齊格式八種，垂直對齊格式五種，也可利用「格式」菜單中的「單元格格式」命令來完成。在「對齊」對話框中還有一個「數據自動換行」複選框，此選項主要用來當某一項單元格的內容較長時，為了數據表的美觀，將超過單元格列寬內容的字元串移到下一行。注意：自動換行對數字無效，若數字長度超過列寬，將出現＃＃＃＃字樣，以科學計數法表示。

10.格式拷貝技巧

　　格式拷貝的功能是將某一格式化操作複製到另一部分數據，具體方法是：選擇含有所需格式的單元，單擊工具條上的「格式刷」按鈕，此時滑鼠變成了刷子形狀，然後選擇要格式化的數據，放開滑鼠即可將格式拷貝過去。如果要用此格式進行多部分相同的複製操作，可以選定格式後雙擊格式「格式刷」按鈕，此後可進行多次複製操，直到再單擊「格式刷」按鈕或按ESC鍵退出當前格式化操作。

11.定製自己的特有格式

　　如果用戶想的數據表經常使用某一格式，可定義自己的樣式。方式是：執行「格式」菜單下的樣式命令，在樣式對話框中的樣式名文本框中輸入自己的樣式名稱，比如「我的樣式」，在下面的各種選項中可選擇需要自己定義的各種樣式，如果需要修改某一選擇的樣式，可單擊「再改」按鈕，將會彈出「單元格格式」對話框，在此處修改樣式後單擊「確定」返回，然後單擊「確定」按鈕保存自定義樣式，以後可隨時使用。

12.用特定函數實現快速輸入

　　在工作中，無論是進行文字處理或是製作報表，都可能要輸入大量重複的數據。

　　利用Word的「自動更正」命令(「工具」菜單)可以巧妙地實現數據的快速輸入，但是在Excel中卻沒有類似的菜單命令。但它自帶的VLOOKUP函數可巧妙地解決這個問題。 比如處理產品的銷售數據時， 往往要輸入大量同一單位的名稱，使用VLOOKUP函數可以使我們只需鍵入一個字母就可實現單位名稱的快速輸入。

　　1.創建源工作表

　　進入Excel 7， 單擊「文件」菜單，再單擊「新建」命令，創建一個新工作簿。在工作表Sheet1上建立產品銷售報表，方法是在A1單元格內輸入「日期」，在B1單元格內輸入 「代碼」 ，在C1單元格內輸入「購貨單位」，在D1單元格內輸入「產品型號」，在E1單元格內輸入「購貨數量」，在「F1單元格內輸入「單價」，在G1單元格內輸入「總價」。

　　2.創建代碼工作表

　　在工作表Sheet2上建立購貨單位的代碼表，方法是單擊Sheet2，在A1單元格內輸入 「代碼」，在B1單元格內輸入「購貨單位名稱」，在A2單元格內輸入「A」，在B2單元格內輸入「上海煤科機電技貿有限公司中煤液壓氣動技術中心」。按同樣的方法依次輸入各購貨單位的名稱及其相應的代碼(本例假設有49個單位)。

　　3.實現數據的快速輸入

　　單擊工作表Sheet1，在A2、A3、A4……單元格內輸入相應的日期，在B2、B3、B4……單元格內輸入相應的單位代碼，在D2、D3、D4……單元格內輸入相應的產品型號，在E2、E3、E4……單元格內輸入相應的產品購貨數量，在F2、F3、F4……單元格內輸入相應的產品單價，在G2單元格內輸入公式「=E2＊F2」，在C2單元格內輸入函數「=VLOOKUP (B2,Sheet2!＄A＄2:＄B＄50,2,0)」，用滑鼠單擊C3單元格右下角的填充句柄不放，向下拖動進行公式的複製，這時在「購貨單位」項下各單元格內已全部輸入了相應的購貨單位名稱。用同樣的方法向下拖拽G3單元格右下角的填充句柄即可。

13.SUMIF函數實現自動動態統計的方法

　　當我們用Excel 7對產品的銷售數據進行管理時， 常常需要知道各類產品的當前銷售情況以及當前庫存情況， 儘管Excel 7中的「分類匯總」命令(「數據」菜單)可以幫助我們方便地統計出各類產品的銷售情況，但對於當前庫存情況，用該命令統計就顯得有些力不從心了。其實只需用Excel 7中的SUMIF函數，就可以實現當前庫存情況的自動動態統計。

　　1.創建銷售工作表

　　進入Excel 7單擊 「文件」菜單，再單擊「新建」命令，創建一個新工作簿。在工作表Sheet1上建立產品銷售報表，方法是在A1單元格內輸入「日期」，在B1單元格內輸入 「購貨單位」，在C1單元格內輸入「產品型號」(為了介紹的方便，本例假設只有5種產品， 其型號分別為AA、 BB、CC、DD、EE。)，在D1單元格內輸入「購貨數量」，然後輸入相應的數據，本例假設一年的銷售記錄不會超過1000個數據。用滑鼠雙擊工作表Sheet1標籤，改名為「銷售」。

　　2.創建進貨工作表

　　在工作表Sheet2上建立各類產品進貨情況表，方法是單擊工作表標籤Sheet2，進入工作表Sheet2，在A1單元格內輸入「日期」，在B1單元格內輸入「產品型號」，在C1單元格內輸入「進貨數量」，再輸入相應的數據，本例假設一年的進貨記錄不會超過50個數據。用滑鼠雙擊工作表Sheet2標籤，改名為「進貨」。

　　3.創建庫存工作表

　　單擊工作表標籤Sheet3，進入工作表Sheet3，在A1單元格內輸入「產品型號」，在B1單元格內輸入「進貨數量」，在C1單元格內輸入「銷售數量」，在D1單元格內輸入「當前庫存量」，在A2、A3、A4、A5、A6單元格內依次輸入各產品型號的名稱AA、BB、CC、DD、EE，用滑鼠雙擊工作表Sheet3標籤，改名為「庫存」。

　　4.實現自動動態統計

　　在 「庫存」工作表中的B2單元格內輸入函數「=SUMIF(進貨!＄B＄2：＄B＄51，「AA」 ，進貨!＄C＄2：＄C＄51)」，用滑鼠單擊B2單元格右下角的填充句柄不放，向下拖動至B6單元格進行公式的複製，然後將B3單元格內公式中的「AA」改為「BB」，將B4單元格內公式中的「AA」改為「CC」，將B5單元格內公式中的「AA」改為「DD」，將B6單元格內公式中的 「AA」 改為「EE」。同理，在C2單元格內輸入函數「=SUMIF(銷售!＄C＄2：＄C＄1001，「AA」，銷售!＄D＄2：＄D＄1001)」，用滑鼠單擊C2單元格右下角的填充句柄不放，向下拖動至C6單元格進行公式的複製，然後將C3單元格內公式中的「AA」改為「BB」，將C4單元格內公式中的「AA」改為「CC」，將C5單元格內公式中的「AA」改為「DD」，將C6單元格內公式中的「AA」改為「EE」。在D2單元格內輸入公式 「=B2－C2」，用滑鼠單擊D2單元格右下角的填充句柄不放，向下拖動至D6單元格進行公式的複製。

　　至此，當前庫存情況的自動動態統計工作便完成了，以後，每當您在「銷售」工作表或「進貨」工作表中輸入一個數據，在「庫存」工作表中便自動統計出每一種產品的「進貨數量」、「銷售數量」和「當前庫存量」。

　 SUMIF函數是將給定條件所給定的單元格相加在公式 「=SUMIF(進貨＄B＄2：＄B＄51，「AA」，進貨＄C＄2：＄C＄51)」中，進貨!＄B＄2：＄B＄51表示希望計算的單元格區域，「AA」表示累加單元格的條件，進貨!＄C＄2：＄C＄51表示求和的實際單元格。僅當區域中相應的單元格滿足條件時，進貨!＄C＄2：＄C＄51中的單元格才被求和。

14.讓表格動起來的方法

　　在單位員工眾多， 又無別的動畫軟體可用的情況下，為了能將每日的數據(如工資核算情況)不停地循環顯示給員工們，可利用EXCEL的表格功能和運算功能，先把表格制好，再通過EXCEL的宏製作，編寫EXCEL BASIC程序，此表格就會循環滾動，放至LED大屏幕上，效果相當不錯。

　　首先，把表格的標題、表頭、日期等制好，再輸入內容，如：姓名、組別、計劃產量、實際產量、日資、實得工資、本月累計等，然後把標題、表頭、日期凍結起來，以便在滾動時能看見。接著，進入「插入」菜單，選擇「宏」選項，此時增加了一個MODULE1模塊，於是我們便可以在此模塊下輸入BASIC程序了。輸完以後，使表格回至頭， 選擇 「工具」菜單下的「宏」選項，彈出一窗口，選擇AUTO－OPEN宏名，再選「運行」按扭，表格便可從頭運行了，等它運行至尾，又會從尾到頭方向運行。就這樣不斷地滾動，直至按下ESC鍵中止它。以後只要每次打開此工作表，便可自動運行。如對下面的程序稍做修改，也可使其左右滾動。

　　程序清單如下(假設有1000名員工)：

Sub AUTO－OPEN( )

oldtime=Timer( )

newtime=Timer( )

A=0

While 1=1

newtime=Timer( )

If newtime－oldtime>2 Then

oldtime=newtime

If A<1000 Then

ActiveWindow.SmallScroll DOWN:=1

A=A＋1

End If

If A>=1000 Then

ActiveWindow.SmallScroll UP:=1

A=A＋1

If A>=2000 Then

A=0

End If

End If

End If

Wend

End Sub

　　注意：newtime和oldtime(即新、舊時間)不能相差太小，否則移動很快，無法看清楚；也不能相差太大，否則每移一行要等待很長的時間。

15.用VBA一次性取消隱藏工作表的方法

　　Excel可以完成工作簿中多個工作表的一次性隱藏， 但是卻不能使他們一次性消除隱藏，這裡介紹一種使用VBA使逆過程一次性完成的方法。

　　1.隱藏工作表

　　一次隱藏一個工作簿中的多個工作表十分簡單。如果選擇一個工作表，只需點擊它的標籤。要選擇多個時，需要選得一組連續工作表的最後一個標籤點擊，然後再按住 ［Shift］ 鍵， 點擊第一個標籤。 選擇不連續的工作表時， 需要在點擊時，按［Ctrl］鍵。選好後，選擇菜單「格式－工作表－隱藏」便可一次隱藏這些工作表。

　　2.重現一個工作表　

　　拉下「格式」菜單，選擇「工作表」並選擇「取消隱藏」命令。操作後，會出現一個對話框，取消隱藏列表框中會出現活動工作簿中已經隱藏的工作表的名字。要取消隱藏，選擇其中一個名字，點擊「確定」。Excel將重現那個工作表並關閉對話框。

　　3.重現所有工作表

　　不幸的是， Excel不能一次取消隱藏所有對話框中的工作表。這樣，如果你需要重現多個工作表，就要多次發出「格式－工作表－取消隱藏」的命令，對於工作表不多時，可能不是十分麻煩，但如果你想重現工作簿中所有被隱藏的工作表，你就不得不多次發出同樣的命令， 下面是一個簡單的VBA程序可以重現所有被隱藏的工作表這個問題。

Sub UnhideAllSheets（）

For Each Sheet In ActiveWorkbook．Sheets

Sheet．Visible=True

Next

EndSub

　　4.如何編製程序

　　要生成UnhideAllSheets過程，首先要按［Alt］［F11］切換到VisualBasic編輯器。 通常你需要在你的Personal．xls工作簿中生成它，以便於在每次打開工作簿時都能夠出現。然後，按［Ctrl］R顯示項目窗口，選擇與Personal．xls相關的工作簿的名字。現在你可以通過在「插入」菜單下選擇「模塊」項在項目中插入模塊。在新的模塊代碼窗口輸入上面的程序。 最後， 點擊「保存」工具條上的按鈕保存項目。（注意，保存項目的同時也將保存與其相關的工作簿Personal．xls。）

　　5.程序解析

UnhideAllSheets過程使用了一個簡單的循環來取消隱藏活動工作簿中的隱藏工作表。這個語句就是：

ForEachSheetInActiveWorkbook．Sheets

　該命令使Excel在活動工作簿中的每一個工作表中循環一次， 進行如下的命令操作：

SheetVisible=True

　　將每個工作表的可視屬性設置為True，這將顯示多個工作表。如果程序遇到的工作表的可視屬性已經是True，那麼命令就不起作用。第三句：Next

　　該語句同第一句共同組成循環完成顯示任務。

17. 查找數據公式兩個(基本查找函數為VLOOKUP,MATCH)

（1）、根據符合行列兩個條件查找對應結果

=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)

（2）、根據符合兩列數據查找對應結果(為數組公式)

=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0))

18、關於COUNTIF

COUNTIF函數只能有一個條件，如大於90，為=COUNTIF(A1:A10,">=90")

介於80與90之間需用減，為 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90")

19. 將多個列中的文本聯接起來

可以使用 & 運算符或 CONCATENATE 函數將多個列中的文本連綴或合併起來

$D$2:=CONCATENATE(A2," ",B2," ",C2＂)

$D$2:=A1&" "&B2&" "&C2＂

注意：單元格間的空格 (" ") 用於在顯示文本間插入空格。

20. 使用條件求和對數據求和

假設您在單元格 A1:A10 中創建了一列數據，而且希望對所有大於 50 且小於 200 的值求和。為此，請使用以下數組公式：

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,A1:A10,0),0))

注意：請務必按下 Ctrl+Shift+Enter，這樣才可以將公式作為數組輸入。執行此操作後，您會看到公式被花括弧 {} 括起。不要嘗試手動輸入括弧。

此公式對範圍內的每個單元格使用嵌套的 IF 函數，並且僅當兩個測試條件同時滿足時才追加單元格數據。

21. 使用條件求和對數據計數

假設您在單元格 A1:A10 中創建了一列數據，而且希望統計所有大於 50 且小於 200 的值的數目。為此，請使用以下數組公式：

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,1,0),0))

注意：請務必按下 Ctrl+Shift+Enter，這樣才可以將公式作為數組輸入。執行此操作後，您會看到公式被花括弧 {} 括起。不要嘗試手動輸入括弧。

此公式對範圍內的每個單元格使用嵌套的 IF 函數，並且僅當兩個測試條件同時滿足時才向總數中追加一。

22. 使用 INDEX 函數和 MATCH 函數查找數據

假設您在單元格 A1:C5 中創建了以下信息表，且此表包含單元格 C1:C5 中的年齡 (Age) 信息：

假設您希望根據某人的姓名 (Name) 查找此人的年齡 (Age)。為此，請按如下公式示例，配合使用 INDEX 函數和 MATCH 函數：

=INDEX($A$1:$C$5, MATCH("Mary",$A$1:$A$5,),3)

此公式示例使用單元格 A1:C5 作為信息表，並在第三列中查找 Mary 的年齡 (Age)。公式返回 22。

23. 利用函數COUNTA統計本班的應考人數(總人數)

　　因為函數COUNTA可以計算出非空單元格的個數，所以我們在利用此函數時，選取本班學生名字所在單元格區域(B3～B12)作為統計對象，就可計算出本班的應考人數(總人數)。

　　1．選取存放本班總人數的單元格，此單元格是一個經過合併後的大單元格(C18～G18)；

　　2．選取函數；單擊菜單「插入/函數」或工具欄中的函數按鈕f＊，打開「粘貼函數」對話框，在「函數分類」列表中選擇函數類別「統計」，然後在「函數名」列表中選擇需要的函數「COUNTA」，按「確定」按鈕退出「粘貼函數」對話框。

　　3．選取需要統計的單元格區域；在打開的「函數嚮導」對話框中，選取需要計算的單元格區域B3～B13，按下回車鍵以確認選取；「函數嚮導」對話框圖再次出現在屏幕上，按下「確定」按鈕，就可以看到計算出來本班的應考人數(總人數)了。

24、利用COUNT、COUNTBLANK和COUNTIF函數分別統計各科參加考試的人數、統計各科缺考人數、統計各科各分數段的人數

　　我們在輸入成績時，一般情況下，缺考的人相應的科目的單元格為空就可以了，是0分的都輸入0。

　　(一)統計語文科的參加考試人數、缺考人數、各分數段的人數。

　　1．用函數COUNT統計語文科的參加考試人數。單擊存放參加語文科考試人數的單元格C19，然後按照前面的操作步驟，首先在「函數分類」列表中選擇函數類別「統計」，在「函數名」列表中選擇需要的函數「COUNT」；其次按照上面「一、3」選取單元格區域的操作方法，選取需要統計的單元格區域(C3～C12)，然後回車確認，單擊「函數嚮導」對話框「確定」按鈕，就可以看到計算出來的結果。

2．用函數COUNTBLANK統計語文科的缺考人數。單擊存放語文科缺考人數的單元格C20，然後按照上面的操作方法，在「統計」類別中選取函數COUNTBLANK，並進行需要統計單元格區域(C3～C12)的選取，直到得出結果。

　 3．用函數COUNTIF分別統計出語文科各分數段的人數。

　　(1) 統計90分(包括90分)以上的人數(表中為「90分以上」)：單擊存放此統計人數的單元格C21，然後選取函數,即選取「統計」類別中的函數「COUNTIF」，然後單擊「函數嚮導」對話框中的「Ragane」右側的按鈕，以選取統計單元格的區域(C3～C12)後，回到「函數嚮導」對話框中，再輸入統計的條件：「$#@62;=90」,如圖2。單擊「確定」按鈕，就可以計算出結果了。

　　(2) 統計大於或等於80分而小於90分的人數(表中為「80～89分」)：雙擊單元格C21進入編輯狀態，可以看到統計90分以上的分數段的人數的公式如圖3所示是：=COUNTIF(C3:C12,″$#@62;=90″)，

　　要統計本分數段人數，我們只要雙擊C22，在其中輸入計算公式：

　　=COUNTIF(C3:C12,″$#@62;=80″)－COUNTIF(C3:C12,″$#@62;=90″)

　　回車後，即可計算出此分數段的人數。

　　(3)用同樣方法，只要在C23、C24、C25三個單元格中，分別輸入公式(可以通過複製粘貼後,修改數字快速完成)：

　　=COUNTIF(C3:C12,″$#@62;=70″)－COUNTIF(C3:C12,″$#@62;=80″)

　　=COUNTIF(C3:C12,″$#@62;=60″)－COUNTIF(C3:C12,″$#@62;=70″)

　　=COUNTIF(C3:C12,″$#@60;60″)

　　輸入完畢後，注意一定要以回車確定，即可分別統計出「大於或等於70分而小於80分」(表中為「70～79分」)、「大於或等於60分而小於70分」(表中為「60～69分」)、「小於60分」(表中為「不及格」)，這三個分數段的各自的人數。

　　(二)統計其餘各科的參加考試人數、缺考人數、各分數段的人數。

　　如前一期所述，用複製公式的方法，可以快速計算出其餘各科的有關數據。以上已經計算出語文科的應考人數、缺考人數及各分數段的人數，選取範圍(C19～C25)，把滑鼠指向剛才選取的單元格區域的右下方(即填充句柄)，待游標變為小黑十字時，按下滑鼠左鍵，並向右拖動，至G25鬆開滑鼠，各科要統計的結果都出來了。

前兩期對班級成績，分別作了總分、平均分、最高分、最低分、應考人數、缺考人數、分數段等數據統計,這些數據可以用來衡量這個班的成績的情況。這一期，將首先介紹用函數「RANK」以最快的速度把本班的名次排出來，作為衡量學生個人在本班的學習情況；另外再介紹用「MEDIAN」、「MODE」、「STDEVP」函數分別統計出各科成績的「中位數」、「眾數」、「標準差」，以此衡量各科試題的質量(如試題的難易程度、離散程度等)情況。

25、用函數「RANK」對總分排名次

　　(一)單元格區域的命名

　　先打開上期製作的表格（如圖1）， 如果用「總分」來排名次，首先選取所有「總分」數據的單元格區域(H3～H12)， 然後單擊菜單中的「插入/名稱/定義」，在彈出的「定義名稱」對話框中，在「當前工作簿的名稱」中輸入或修改名稱為「總分」；在「引用位置」欄中顯示的就是剛才選取的單元格區域(H3～H12)，當然也可以通過單擊其右側的按鈕重新選取單元格區域。如果只定義一個名稱，則可按「確定」按鈕退出；如果還要添加其它區域名稱，可單擊「添加」按鈕，待命名完畢後，再按「確定」按鈕結束單元格區域的命名。 在此我們可以比較一下前兩期用「選取」和現在用「命名」區域的不同方法及用途：利用「選取」確定區域，預選區域不是固定的，如果需要相對固定的區域，可以利用「命名」，則以後的操作會比較簡便，如果對某個區域一旦命名，利用函數的時候，就可以按以下的方法確定單元格的區域，無須再去選取區域了。

　　(二)選取函數確定排名

　　1．在圖1的「平均分」右邊的單元格（J2）中輸入「名次」。

　　2．單擊選取單元格J3，再選擇「統計」類的「RANK」函數，則在彈出的「粘貼函數」對話框中，一切設置如圖2(圖中的「H3」是存放第一個學生總分的單元格，「總分」則是剛才命名的單元格區域名稱。此時不能在「粘貼函數」對話框中，單擊圖2中「Ref」右邊的按鈕去選取單元格區域，否則後面利用複製方法統計其餘各人的名次時，單元格的區域會發生變化；利用命名的單元格區域，複製時其區域不會發生變化；如果只看其中一個人的名次，則可以利用「選取」的方法)，單擊「確定」按鈕，即可得出第一個學生的成績排名。然後選取單元格J3,拖動其填充句柄至最後一名學生，馬上得出全班的成績排名。而且名次是可以動態變化的，如果某人的某科成績發生變化，所有排名也會隨數據的變化而變化。如果想把名次按從低到高的順序進行排列，只要先選取範圍(J3～J12),然後利用菜單中的「數據/排序」命令，對「名次」進行「遞增」排序即可。

26、用「MEDIAN」、「MODE」、「STDEVP」函數分別計算各科成績的「中位數」、「眾數」、「標準差」

　　「中位數」、「眾數」、「標準差」是三個「統計類」的函數，也是統計學中三個十分常用的概念，它是分析數據的分布、離散程度等標誌的重要依據，下面通過對學生成績的分布情況，分析每科試題的有關情況。

　　1．計算「語文」的「中位數」

　　先選取存放數據的單元格C26，然後在「統計」類函數中選取函數「MEDIAN」，在彈出的「粘貼函數」對話框中，單擊「Nuber1」右邊的按鈕，選取需計算的單元格區域(C3～C12)，然後單擊「粘貼函數」對話框中的「確定」按鈕，就可計算出語文科的中位數(在選取的數據中，中位數是它們的平均數)。

　　2．計算「語文」的「眾數」、「標準差」

　　利用「MODE」、「STDEVP」兩個函數，按照上述的方法，即可計算出「語文」的「眾數」、「標準差」。

　　3．計算其餘各科的「中位數」、「眾數」、「標準差」

　　按照前兩期介紹的複製方法，相信各位可以熟練地操作，計算出其餘各科的「中位數」、「眾數」、「標準差」了，最後結果如圖3。

27、數據分析

　　學會使用Excel中的一些常用函數，是為了使我們的工作更科學、更迅速、更輕鬆。那麼上述對成績統計出來的數據，如何體現為教學服務呢？下面簡單介紹一下Excel中「圖表」的應用，以便我們對各科的試題進行分析：

　　在Excel中「圖表」是反映表格數據的直觀表現，通過圖表可以非常迅速直觀地對數據產生總體上的認識，這正是統計學中，最常用的對數據分布的表現方式。

　　(一)使用「圖表嚮導」建立「語文」分數的分布圖表

　　1． 選取圖表類型：可以先選取表格中某個空白的單元格，單擊菜單中的「插入圖表」，在彈出的「圖表嚮導－步驟1」對話框中，選擇一種圖表類型和子圖表類型，如我們選擇「折線圖」。單擊「確定」按鈕後，進入「圖表嚮導－步驟2」。

　　2．選擇圖表源數據：在彈出的步驟2對話框中，單擊數據區域右側的按鈕，對話框消失，按前面介紹過的方法選取數據區域(b21～c25),回車確定選取後，單擊「下一步」進入「圖表嚮導－步驟3」。

　　3．設置圖表選項：在彈出的步驟3對話框中，可以簡單設置如圖4，單擊「下一步」進入「圖表嚮導－步驟4」。

　　4．選擇圖表位置：在彈出的對話框中，需要選擇生成圖表放置的位置，此時單擊「完成」按鈕，把圖表嵌在當前的工作表中，圖5為完成的「語文」分數分布圖表。

　　具體的數據分析留給有興趣的讀者。

　　下期將用一個貨物銷售的例子，說明總計(SUMIF)和分類匯總(SUBTOTAL)兩個函數的用法。　　

前面介紹過求和函數(SUM)，它能對選取區域內的數據進行按行(或列)求和，但在實際應用中，經常需要進行有條件的數據匯總。本期介紹的總計函數(SUMIF)，將為你解決這個問題；另外介紹的分類匯總函數(SUBTOTAL)，可以很容易地計算分類匯總。這兩個函數的作用都十分強大，利用它可以簡化條件匯總和分類匯總。

　　如圖1的樣表，是某百貨公司下屬的四個門市部，都銷售同樣的商品；假設其中的四個售貨員，輪流在四個門市部工作，樣表是他們在一段時間內的營業銷售情況，(其中的「金額」可以用公式計算，方法是在F2中輸入公式「=D2＊E2」進行計算，然後按照前面講述過的拖動進行複製的方法，即可求出各門市部各種商品的銷售金額)。下面將舉例說明用總計函數(SUMIF)，分別按門市部、售貨員、某一商品、某一範圍進行有關的匯總；用分類匯總函數(SUBTOTAL)，分別計算某門市部商品數量的平均值、某門市部的商品數量之和、某門市部貨物類別的數目、某門市部最大銷售量和最便宜的單價。28、對固定的單元格區域進行命名

　　上期已介紹過對一定的單元格區域進行命名的方法，應該值得注意和學會運用，因為命名單元格區域對靈活運用函數是十分重要的，它將為運用函數帶來極大的方便。這裡先按上期介紹過的方法，利用菜單中的「插入/名稱/定義」命令，在「定義名稱」的對話框中，分別添加對如下區域的命名：把「A2～A17」命名為「門市部」，把「B2～B17」命名為「售貨員」，把「C2～C17」命名為「類別」，下面的幾項依次命名為「數量」、「單價」、「金額」。

28、用總計函數(SUMIF)分別按門市部、售貨員、商品類別、某一條件進行有關的匯總

　　1． 按門市部進行匯總

　　以「門市部3」進行金額的匯總為例：按我們都已熟悉的方法，首先選取存放數據的單元格，然後選取函數「SUMIF」，在彈出的「粘貼函數」對話框中，其中的「Range」為選取的區域，這個區域(前面已定義為「門市部」)是下麵條件判斷的依據、「Criteria」為計算條件，它將判斷選取區域中哪些單元格(即「門市部3」)符合計算要求、「Sum_range」為求和區域，這個區域中的數值(「金額」)用於真正的求和。

　　2． 按售貨員進行匯總

　　以「劉芳」的銷售數量為例，與上述方法一樣，只要在「粘貼函數」對話框圖中，在「Range」項輸入「售貨員」，在「Criteria」項中輸入「″劉芳″」，在「Sum_range」項中輸入「數量」。

　　3． 按商品類別進行匯總

　　以「茶葉」的銷售金額為例，只要在「粘貼函數」對話框圖中，在「Range」項輸入「類別」，在「Criteria」項中輸入「″茶葉″」，在「Sum_range」項中輸入「金額」。

　　4．按一定條件進行匯總

　　如計算除「礦泉水」之外的貨款：在「粘貼函數」對話框圖中，在「Range」項輸入「類別」，在「Criteria」項中輸入「″$#@60;$#@62;礦泉水″」、在「Sum_range」項中輸入「金額」。

以上按不同的條件進行了「數量」或「金額」的匯總，如果雙擊存放「門市部3」金額的單元格，其中的計算公式為：「=SUMIF(門市部,″門市部3″,金額)」，如果需要計算其它門市部的匯總金額，只須把計算公式利用「複製」和「粘貼」命令，複製至相對應的單元格後，把「門市部3」修改為其它門市部即可匯總出其它門市部的金額了。 同樣方法，可完成對各「售貨員」的匯總、各類商品的匯總。圖3是以上各項匯總數據的樣表，剩餘的各項(如各門市部、各種商品的銷售數量)的匯總，大家不妨一試。不難看出，利用函數進行計算時，相對固定的單元格區域命名後，減少了頻繁選取單元格區域的次數，給我們操作帶來了極大的方便。

29、分類匯總函數(SUBTOTAL)的應用

　　分類匯總函數(SUBTOTAL)不僅僅是一個求和函數，還能夠對給定區域內的數值進行其它計算(見圖4)，它的語法結構為：

　　SUBTOTAL(function_num,ref1)

　　其中的「function_num」是計算類型編號，為一個1到11的數字，它規定所要進行的計算類型，圖4為計算類型編號及具體含義；「ref1」為進行匯總數據的單元格區域。

　　應用舉例如下：

　　1． 計算「門市部2」的「數量」平均值

　　選定函數後，彈出「粘貼函數」對話框中，在「Function_num」項中輸入計算類型「1」，單擊「Ref1」右側的按鈕，選取「門市部2」對應的「數量」單元格區域(D6～D9)，如圖5，按「確定」按鈕完成。

　　2． 計算「門市部4」中的數量之和

　　與計算「門市部2」的「數量」平均值相似，計算類型(「Function_num」)為「9」，匯總單元格區域(「Ref1」)為「D14:D17」。

　　3． 計算「門市部2」的商品「類別」數目

　　與上例相似的操作，計算類型(「Function_num」)為「2」，匯總單元格區域為「C6:C9」。

　　4． 計算最大的銷售數量

　　計算類型(「Function_num」)為「4」，匯總單元格區域(「Ref1」)為「D2:D17」。

　　5． 計算 「門市部2」中的最便宜的單價

　　計算類型(「Function_num」)為「5」，匯總單元格區域(「Ref1」)為「E6:E9」。

　　分類匯總函數的應用是十分靈活的，這是它與使用菜單中「數據/分類匯總」命令的最大差別，如果應用菜單中的分類匯總命令，往往是對於有標題的某個區域而進行的分類匯總，這樣的應用有時缺乏必要的靈活性，不利於實際中的靈活運用，所以應該學會利用分類匯總函數進行分類匯總，這在我們的實際工作中是非常有用的。

　　總結：對指定條件的區域進行匯總和分類匯總，這是我們在實際工作中經常遇到的，利用總計函數(SUMIF)和分類匯總函數(SUBTOTAL)一般就能夠處理這些工作，而且具有比較強的靈活性。

　　說明：本文轉自電腦報電子版，未將實例圖片加上，若有不明白的地方，請參照電腦報原文。

30、IF函數

　　主要功能：根據對指定條件的邏輯判斷的真假結果，返回相對應的內容。

　　使用格式：=IF(Logical,Value_if_true,Value_if_false)

　　參數說明：Logical代表邏輯判斷表達式；Value_if_true表示當判斷條件為邏輯「真（TRUE）」時的顯示內容，如果忽略返回「TRUE」；Value_if_false表示當判斷條件為邏輯「假（FALSE）」時的顯示內容，如果忽略返回「FALSE」。

　　應用舉例：在C29單元格中輸入公式：=IF(C26>=18,"符合要求","不符合要求")，確信以後，如果C26單元格中的數值大於或等於18，則C29單元格顯示「符合要求」字樣，反之顯示「不符合要求」字樣。特別提醒：本文中類似「在C29單元格中輸入公式」中指定的單元格，讀者在使用時，並不需要受其約束，此處只是配合本文所附的實例需要而給出的相應單元格，具體請大家參考所附的實例文件。

31. SUMPRODUCT這個函數的意義和用法

1、該函數的意義是：在給定的幾組數組中，將數組間對應的元素相乘，並返回乘積之和

如：=SUMPRODUCT（A1:A9，B1:B9，C1:C9）=A1*B18C1+A2*B2*C2+......+A9*B9*C9

2、=SUMPRODUCT(D6:AH6,LOOKUP(D6:AH6,{0,600,800},{0.048,0.05,0.052}))

首先，先說LOOKUP(D6:AH6,{0,600,800},{0.048,0.05,0.052})：

1）當D6:Ah6中的數值<600時，返回0.048，

2）當D6:Ah6中的數值大於等於600,但<800時，返回0.05，

3）當D6:Ah6中的數值大於等於800時，返回0.052，

共返回與數組D6:Ah6相同個數的數值即31個數

其次，就是D6:Ah6中的數值依次與剛返回大的31個數值相乘求和即乘積和

3.

=SUM(IF(D6:AH6<600,D6:AH6*0.048,IF(D6:AH6<800,D6:AH6*0.05,IF(D6:AH6>=800,D6:AH6*0.052,0))))

最後這個0沒有意義，對結果沒有影響

該公式的意義是：

1）如果D6:AH6<600時，D6:AH6*0.048

2）如果D6:AH6>=600且<800時，D6:AH6*0.05

3）如果D6:AH6>=800，D6:AH6*0.052

該範圍已包括了(-∞,600),[600,800),[800,∞)的所有範圍，所以0沒有意義！

33. LOOKUP是個什麼函數

1、LOOKUP函數的意思是：從單行或單列區域或者從一個數組返回值

2、LOOKUP(D6:AH6,{0,600,800},{0.048,0.05,0.052})這裡面的0不能預設的原因是

當0=<D6:AH6<600時，返回0.048，而不是僅僅的D6:AH6<600時，返回0.048，

{0,600,800}屬於條件數組，{0.048,0.05,0.052}是結果數組，兩者的元素個數應該是對應的，並且條件數組中的的值必須以升序順序放置，否則，LOOKUP 可能無法提供正確的值。

3、SUMPRODUCT與looukup函數組合應用的時候應當注意：

兩者數據的個數應該相等，否則會出現錯誤。

32. VLOOKUP函數

在表格或數值數組的首列查找指定的數值，並由此返回表格或數組中該數值所在行中指定列處的數值。

這裡所說的「數組」，可以理解為表格中的一個區域。數組的列序號：數組的「首列」，就是這個區域的第一縱列，此列右邊依次為第2列、3列……。假定某數組區域為B2:E10，那麼，B2:B10為第1列、C2:C10為第2列……。

語法：

VLOOKUP（查找值，區域，列序號，邏輯值）

「查找值」：為需要在數組第一列中查找的數值，它可以是數值、引用或文字元串。

「區域」：數組所在的區域，如「B2:E10」，也可以使用對區域或區域名稱的引用，例如資料庫或數據清單。

「列序號」：即希望區域（數組）中待返回的匹配值的列序號，為1時，返回第一列中的數值，為2時，返回第二列中的數值，以此類推；若列序號小於1，函數VLOOKUP 返回錯誤值 #VALUE!；如果大於區域的列數，函數VLOOKUP返回錯誤值 #REF!。

「邏輯值」：為TRUE或FALSE。它指明函數 VLOOKUP 返回時是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小於「查找值」的最大數值；如果「邏輯值」為FALSE，函數 VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。如果「查找值」為文本時，「邏輯值」一般應為 FALSE 。另外：

?如果「查找值」小於「區域」第一列中的最小數值，函數 VLOOKUP 返回錯誤值 #N/A。

?如果函數 VLOOKUP 找不到「查找值」 且「邏輯值」為 FALSE，函數 VLOOKUP 返回錯誤值 #N/A。

下面舉例說明VLOOKUP函數的使用方法。

假設在Sheet1中存放小麥、水稻、玉米、花生等若干農產品的銷售單價：

A B

1 農產品名稱 單價

2 小麥 0.56

3 水稻 0.48

4 玉米 0.39

5 花生 0.51

…………………………………

100 大豆 0.45

Sheet2為銷售清單，每次填寫的清單內容不盡相同：要求在Sheet2中輸入農產品名稱、數量後，根據Sheet1的數據，自動生成單價和銷售額。設下表為Sheet2：

A B C D

1 農產品名稱 數量 單價 金額

2 水稻 1000 0.48 480

3 玉米 2000 0.39 780

…………………………………………………

在D2單元格里輸入公式：

=C2*B2 ；

在C2單元格里輸入公式：

=VLOOKUP（A2，Sheet1!A2:B100，2，FALSE） 。

如用語言來表述，就是：在Sheet1表A2:B100區域的第一列查找Sheet2表單元格A2的值，查到後，返回這一行第2列的值。

這樣，當Sheet2表A2單元格里輸入的名稱改變後，C2里的單價就會自動跟著變化。當然，如Sheet1中的單價值發生變化，Sheet2中相應的數值也會跟著變化。

其他單元格的公式，可採用填充的辦法寫入。

33.