【轉】 Excel製表技巧(51)公式及函數的高級應用

EXCEL公式及函數的高級應用(1) 　　公式和函數是Excel最基本、最重要的應用工具，是Excel的核心，因此，應對公式和函數熟練掌握，才能在實際應用中得心應手。 2.1.1 數組公式及其應用 　　數組公式就是可以同時進行多重計算並返回一種或多種結果的公式。在數組公式中使用兩組或多組數據稱為數組參數，數組參數可以是一個數據區域，也可以是數組常量。數組公式中的每個數組參數必須有相同數量的行和列。 2.1.1.1 數組公式的輸入、編輯及刪除 　　1．數組公式的輸入 數組公式的輸入步驟如下： 　　（1）選定單元格或單元格區域。如果數組公式將返回一個結果，單擊需要輸入數組公式的單元格；如果數組公式將返回多個結果，則要選定需要輸入數組公式的單元格區域。 　　（2）輸入數組公式。 　　（3）同時按"Crtl+Shift+Enter"組合鍵，則Excel自動在公式的兩邊加上大括弧{ } 。 　　特別要注意的是，第（3）步相當重要，只有輸入公式後同時按"Crtl+Shift+Enter"組合鍵，系統才會把公式視為一個數組公式。否則，如果只按Enter鍵，則輸入的只是一個簡單的公式，也只在選中的單元格區域的第1個單元格顯示出一個計算結果。 　　在數組公式中，通常都使用單元格區域引用，但也可以直接鍵入數值數組，這樣鍵入的數值數組被稱為數組常量。當不想在工作表中按單元格逐個輸入數值時，可以使用這種方法。如果要生成數組常量，必須按如下操作： 　　（1）直接在公式中輸入數值，並用大括弧"{ }"括起來。 　　（2）不同列的數值用逗號","分開。 　　（3）不同行的數值用分號";"分開。 　　? 輸入數組常量的方法： 　　例如，要在單元格A1:D1中分別輸入10，20，30和40這4個數值，則可採用下述的步驟： 　　（1）選取單元格區域A1:D1，如圖2-1所示。 圖2-1 選取單元格區域A1:D1 （2）在公式編輯欄中輸入數組公式"={10,20,30,40}"，如圖2-2所示。 圖2-2 在編輯欄中輸入數組公式 （3）同時按Ctrl+Shift+Enter組合鍵，即可在單元格A1、B1、C1、D1中分別輸入了10、20、30、40，如圖2-3所示。 　　假若要在單元格A1、B1、C1、D1、A2、B2、C2、D2中分別輸入10、20、30、40、50、60、70、80，則可以採用下述的方法： 　　圖2-3 同時按Ctrl+Shift+Enter組合鍵，得到數組常量 （1）選取單元格區域A1:D2，如圖2-4所示。 　　圖2-4 選取單元格區域A1:D2 （2）在編輯欄中輸入公式"={10,20,30,40;50,60,70,80}"，如圖2-5所示。 　　圖2-5 在編輯欄中輸入數組公式 （3）按Ctrl+Shift+Enter組合鍵，就在單元格A1、B1、C1、D1、A2、B2、C2、D2中分別輸入了10、20、30、40和50、60、70、80，如圖2-6所示。 　　圖2-6 同時按Ctrl+Shift+Enter組合鍵，得到數組常量 ? 輸入公式數組的方法 　　例如，在單元格A3:D3中均有相同的計算公式，它們分別為單元格A1:D1與單元格A2:D2中數據的和，即單元格A3中的公式為"=A1+A2"，單元格B3中的公式為"=B1+B2"，…，則可以採用數組公式的方法輸入公式，方法如下： 　　（1）選取單元格區域A3:D3，如圖2-7所示。 　　（2）在公式編輯欄中輸入數組公式"=A1:D1+A2:D2"，如圖2-8所示。 圖2-7 選取單元格區域A3:D3 圖2-8 在編輯欄中輸入數組公式 （3）同時按Ctrl+Shift+Enter組合鍵，即可在單元格A3:D3中得到數組公式"=A1:D1+A2:D2"，如圖2-9所示。 圖2-9 同時按Ctrl+Shift+Enter組合鍵，得到數組公式 2．編輯數組公式 　　數組公式的特徵之一就是不能單獨編輯、清除或移動數組公式所涉及的單元格區域中的某一個單元格。若在數組公式輸入完畢後發現錯誤需要修改，則需要按以下步驟進行： （1）在數組區域中單擊任一單元格。 　　（2）單擊公式編輯欄，當編輯欄被激活時，大括弧"{ }"在數組公式中消失。 　　（3）編輯數組公式內容。 　　（4）修改完畢後，按"Crtl+Shift+Enter"組合鍵。要特別注意不要忘記這一步。 3．刪除數組公式 刪除數組公式的步驟是：首先選定存放數組公式的所有單元格，然後按Delete鍵。 2.1.1.2 數組公式的應用 　　1．用數組公式計算兩個數據區域的乘積 【例2-1】如圖2-10所示，已經知道12個月的銷售量和產品單價，則可以利用數組公式計算每個月的銷售額，步驟如下： 　　圖2-10 用數組公式計算銷售額 （1）選取單元格區域B4:M4。 　　（2）輸入公式"=B2:M2*B3:M3"。 　　（3）按"Crtl+Shift+Enter"組合鍵。 　　如果需要計算12個月的月平均銷售額，可在單元格B5中輸入公式"=AVERAGE(B2:M2*B3:M3)"，然後按"Crtl+Shift+Enter"組合鍵即可，如圖2-10所示。 　　在數組公式中，也可以將某一常量與數組公式進行加、減、乘、除，也可以對數組公式進行乘冪、開方等運算。例如在圖2-10中，每月的單價相同，故我們也可以在單元格B4:M4中輸入公式"=B2:M2*28"，然後按"Crtl+Shift+Enter"組合鍵；在單元格B5中輸入公式"=AVERAGE(B2:M2*28)"，然後按"Crtl+Shift+Enter"組合鍵。 　　在使用數組公式計算時，最好將不同的單元格區域定義不同的名稱，如在圖2-10中，將單元格區域B2:M2定義名稱為"銷售量"，單元格區域B3:M3定義名稱為"單價"，則各月的銷售額計算公式為"=銷售量*單價"，月平均銷售額計算公式為"=AVERAGE(銷售量*單價)"，這樣不容易出錯。 2．用數組公式計算多個數據區域的和 如果需要把多個對應的行或列數據進行相加或相減的運算，並得出與之對應的一行或一列數據時，也可以使用數組公式來完成。 　　【例2-2】某企業2002年銷售的3種產品的有關資料如圖2-11所示，則可以利用數組公式計算該企業2002年的總銷售額，方法如下： 　　圖2-11 某企業的月銷售總額計算 （1）選取單元格區域C8:N8。 　　（2）輸入公式"=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7"。 　　（3）按"Crtl+Shift+Enter"組合鍵。 3．用數組公式同時對多個數據區域進行相同的計算 【例2-3】某公司對現有三種商品實施降價銷售，產品原價如圖2-12所示，降價幅度為20%，則可以利用數組公式進行計算，步驟如下： 　　圖2-12 產品降價計算 （1）選取單元格區域G3:I8。 　　（2）輸入公式"=B3:D8*(1-20%)"。 　　（3）按Crtl+Shift+Enter組合鍵。 　　此外，當對結構相同的不同工作表數據進行合併匯總處理時，利用上述方法也將是非常方便的。有關不同工作表單元格的引用可參閱第1章的有關內容，關於數據的合併計算可參閱本章2.3.5節的內容。 2.1.2 常用函數及其應用 　　在第1章中介紹了一些有關函數的基本知識，本節對在財務管理中常用的一般函數應用進行說明，其他有關的專門財務函數將在以後的有關章節中分別予以介紹。 2.1.2.1 SUM函數、SUMIF函數和SUMPRODUCT函數 　　在財務管理中，應用最多的是求和函數。求和函數有三個：無條件求和SUM函數、條件求和SUMIF函數和多組數據相乘求和SUMPRODUCT函數。 1．無條件求和SUM函數 該函數是求30個以內參數的和。公式為 　　= SUM（參數1，參數2，…，參數N） 　　當對某一行或某一列的連續數據進行求和時，還可以使用工具欄中的自動求和按鈕 。 　　例如，在例2-1中，求全年的銷售量，則可以單擊單元格N2，然後再單擊求和按鈕 ，按回車鍵即可，如圖2-13所示。 圖2-13 自動求和 2．條件求和SUMIF函數 SUMIF函數的功能是根據指定條件對若干單元格求和，公式為 　　=SUMIF(range,criteria,sum_range) 　　式中 range-用於條件判斷的單元格區域； 　　criteria-確定哪些單元格將被相加求和的條件，其形式可以為數字、表達式或文本； 　　sum_range-需要求和的實際單元格。 　　只有當range中的相應單元格滿足條件時，才對 sum_range 中的單元格求和。如果省略 sum_range,則直接對 range 中的單元格求和。 　　利用這個函數進行分類匯總是很有用的。 　　【例2-4】某商場2月份銷售的家電流水記錄如圖2-14所示，則在單元格I3中輸入公式"=SUMIF(C3:C10,211,F3:F10)"，單元格I4中輸入公式"=SUMIF(C3:C10,215,F3:F10)"，在單元格I5中輸入公式"=SUMIF(C3:C10,212,F3:F10)"，單元格I6中輸入公式"=SUMIF(C3:C10,220,F3:F10)"，即可得到分類銷售額匯總表。 　　圖2-14 商品銷售額分類匯總 SUMIF函數的對話框如圖2-15所示。 　　圖2-15 SUMIF函數對話框 當需要分類匯總的數據很大時，利用SUMIF函數是很方便的。 3．SUMPRODUCT函數 SUMPRODUCT函數的功能是在給定的幾組數組中，將數組間對應的元素相乘，並返回乘積之和。公式為 　　= SUMPRODUCT(array1,array2,array3,…) 　　式中，array1,array2,array3,...為1至30個數組。 　　需注意的是，數組參數必須具有相同的維數，否則，函數 SUMPRODUCT 將返回錯誤值 #VALUE!。對於非數值型的數組元素將作為0處理。 　　例如，在例2-2中，要計算2002年產品A的銷售總額，可在任一單元格（比如O2）中輸入公式"=SUMPRODUCT(C2:N2,C3:N3)"即可。 　　Excel"條件格式"公式實例 　　我們知道，Excel"條件格式"功能可以根據單元格內容有選擇地自動應用格式，它為Excel增色不少的同時，還為我們帶來很多方便。如果讓"條件格式"和公式結合使用，則可以發揮更大的威力，下面提供幾個在"條件格式"中使用公式的應用實例，希望能給讀者朋友帶來一些啟發。 一、 判別輸入是否正確 　　在輸入如身份證等有固定位數的號碼，出現位數不正確的情形時，我們希望Excel能夠給出提示。雖然可以使用"數據有效性"設置實現，但是當輸入出錯時，Excel總會彈出一個提示的對話框，有朋友可能覺得這樣"唐突"的提醒有點影響心情，那就讓"條件格式"來"溫和"的提醒吧。 　　1、創建"條件格式"的公式 　　假設我們通過"條件格式"，把符合位數(15位或18位)的號碼所在單元格的填充色設置為綠色，輸入完成後，通過查看單元格的填充色是否變為綠色，就可以知道輸入的正確性了。 　　由於身份證號碼數據是屬於"文本"類型的，先選中需要存放身份證號碼的A2:A52單元格區域，將它們的數字格式設置為"文本"。然後在A2:A52單元格區域處於被選中的狀態下，選擇菜單"格式→條件格式"命令，打開"條件格式"對話框，單擊"條件1"下方的下拉箭頭，在彈出的下拉列表中選擇"公式"(圖 1)。 接著在其右邊的文本框中輸入公式"=OR(LEN(A2)=15,LEN(A2)=18)"，然後單擊"格式"按鈕，在打開的"單元格格式"對話框中選擇"圖案"選項卡，選擇綠色作為符合條件的單元格的填充色(圖 2)。 設置好後單擊"確定"按鈕，返回"條件格式"對話框，檢查無誤再次單擊"確定"就完成了條件格式的設置(圖 3)。 小提示：上面的操作，先選中了一個單元格範圍A2:A52，然後為這個單元格範圍設置條件格式的公式。在這種情況下，公式中應使用選擇範圍中左上單元格的引用，此例中為A2。公式輸入完成後，可以查看一下這個範圍中的其它單元格的條件格式公式，如A8單元格，為"=OR(LEN(A8)=15,LEN(A8)=18)"，這是由於上面的引用為相對應用，它會根據單元格的實際偏移量自動改變，從而得到適合其它單元格的公式。 　　2、實現的具體效果 　　現在來測試一下上面設置可以實現的效果，在A2：A52區域的單元格中輸入一些身份證號碼，當位數是18位或15位時，所在單元格的填充色自動變為"綠色"，而位數不對的身份證號碼，所在單元格的填充色不發生任何改變(圖 4)，從是否變色我們就可以判斷輸入的正確性了。 全部輸入並確認正確後，如果需要刪除單元格條件格式，則先選中A2:A52單元格區域，然後打開"條件格式"對話框，單擊如圖3中的"刪除"按鈕，在打開的"刪除條件格式"對話框中勾選"條件1"複選框，單擊確定即可(圖 5)。
推薦閱讀：