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