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