資源描述:
《Excel數(shù)組公式應(yīng)用詳解.doc》由會(huì)員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。
1、Excel數(shù)組公式應(yīng)用詳解????????一、什么是數(shù)組公式????????直接看微軟的解釋,也許并不是很容易理解,根據(jù)個(gè)人的理解,讓其更直白一點(diǎn),可以這樣簡(jiǎn)單理解,引用了數(shù)組(可以是一個(gè)或多個(gè)數(shù)值,或是一組或多組數(shù)值),并在編輯欄可以看到以“{}”括起來的公式就是數(shù)組公式。而數(shù)組公式的作用就是對(duì)一組(單個(gè)數(shù)據(jù)可以看成是一組)、多組數(shù)據(jù)進(jìn)行處理,然后得到想要的結(jié)果。????????二、如何輸入數(shù)組公式???????既然數(shù)組公式是以“{}”括起來的,那是不是在編輯欄在公式的兩端分別輸入“{}”就可以了呢?答案是否定的,在Excel中要輸入數(shù)組
2、公式,必須以特定的方法來輸入,算是告訴Excel,我們這里輸入的是數(shù)組公式。???????在某個(gè)單元格輸入數(shù)組公式的方法如下:????????1.在編輯欄輸入完整的公式,并使編輯欄仍處在編輯狀態(tài);????????2.按下Ctrl+Shift+Enter快捷鍵???????經(jīng)過以上兩步操作以后,編輯欄會(huì)自動(dòng)脫離編輯狀態(tài),并且選中單元格后,在編輯欄可以看到公式的兩端有“{}”符號(hào)標(biāo)記,而雙擊進(jìn)入公式的編輯狀態(tài)時(shí),你會(huì)發(fā)現(xiàn)“{}”符號(hào)是不存在的。????????三、數(shù)組公式有什么用????????這里做了一個(gè)類似微軟官網(wǎng)上的例子,這里詳細(xì)說明一下
3、用法及好處。???????以上面圖片中的內(nèi)容為例,假設(shè)我一共買了三支股票,其股份及買入價(jià)格分別如圖中所示,現(xiàn)在我要計(jì)算我的總股本。???????正常情況下我應(yīng)該如何做?在B4輸入“=B2*B3”,然后填充至D4單元格,這樣B4、C4、D4就分別是每一支股票的股本了,然后在B5單元格再輸入“=SUM(B4:D4)”,這樣總股本就出來了。???????上面的計(jì)算過程可以說一點(diǎn)問題沒有,也絕對(duì)正確。但是試想一下,如果類似的數(shù)據(jù)有很多,如果不是計(jì)算我自己買的幾支股票而是其他類似情況的數(shù)據(jù)處理的時(shí)候,采用上面的方法時(shí),其工作量可想而知了。??????
4、?我們?cè)倩氐降谝环鶊D中的公式“{=SUM(B2:D2*B3*D3}”,此公式便是一個(gè)典型的數(shù)組公式的應(yīng)用,此公式的作用就是計(jì)算B2*B3、C2*C3以及D2*D3的和。而B2:D2*B3*D3便是一個(gè)數(shù)組,其中包含三個(gè)元素,各元素的值就分別是各項(xiàng)的乘積。???????為了更好的驗(yàn)證數(shù)組說法,分別在B4、C4、D4單元格中分別輸入=B2*B3、=C2*C3、=D2*D3,B7、C7、D7單元格中全部輸入=B2:D2*B3:D3,結(jié)果如下圖所示。???????從上面的圖中可以看到,第4行和第7行的計(jì)算結(jié)果是一樣的,這就是數(shù)組的效果,數(shù)組會(huì)根據(jù)當(dāng)
5、前單元格所在位置自動(dòng)取數(shù)組中對(duì)應(yīng)序列的數(shù)值,如果將“=B2:D2*B3:D3”算式輸入到其他的列中(非B、C、D列),這時(shí)你會(huì)看到“#VALUE!”的錯(cuò)誤數(shù)值結(jié)果,因?yàn)樵谄渌袝r(shí),Excel無法判斷該取數(shù)組中的哪一個(gè)數(shù)值。???????如果將第7行中的算式外面加上SUM,你會(huì)發(fā)現(xiàn)結(jié)果仍然一樣,因?yàn)槟J(rèn)情況下,數(shù)組算式只取對(duì)應(yīng)序列的值,再加上SUM也只是對(duì)應(yīng)的值,如果使用數(shù)組公式,便是告訴Excel計(jì)算數(shù)組中所有數(shù)值的和,也就是單元格B5中的結(jié)果。????????三、數(shù)組公式應(yīng)用進(jìn)階???????數(shù)組公式最典型的應(yīng)用應(yīng)該是使用SUM替代SUM
6、IF,雖然SUMIF很好用,但在Office2007之前,也就是SUMIFS函數(shù)出現(xiàn)之前,如果想利用SUMIF進(jìn)行一次多重條件判斷的求和計(jì)算是很難實(shí)現(xiàn)的。???????為了更便于理解,這里再用上面的例子進(jìn)行一個(gè)比較簡(jiǎn)單的運(yùn)算,上面只是列出了三支股票,如果我同時(shí)購(gòu)買了多支股票,現(xiàn)在想知道這些股票當(dāng)中,股價(jià)小于5元的股票有幾支(這一功能可以使用Countif函數(shù)來實(shí)現(xiàn),這里為了讓數(shù)組公式更便于理解,所以使用SUM、IF相結(jié)合的數(shù)組公式來實(shí)現(xiàn)。???????在B10單元格輸入公式“=SUM(IF(B3:D3<5,1))”,按鈕按下Ctrl+Shi
7、ft+Enter組合鍵,使公式變成數(shù)組公式“{=SUM(IF(B3:D3<5,1))}”。???????從上圖中可以看到,計(jì)算結(jié)果為2,計(jì)算正確。如果對(duì)此懷疑,可以擴(kuò)大數(shù)據(jù)區(qū)域,從而更容易理解。???????下面說一下公式的整個(gè)運(yùn)算過程。???????1.IF(B3:D3<5,1),計(jì)算B3:D3區(qū)域內(nèi)數(shù)值小于5的個(gè)數(shù),因?yàn)槭褂肐F判斷,數(shù)值小于5時(shí),取值1,所以該公式計(jì)算的結(jié)果是1,false,1,然后SUM進(jìn)行數(shù)組求和也就是1+false+1,所以結(jié)果為2。SUM在求值時(shí)會(huì)自動(dòng)忽略False,我們也可以把False直接當(dāng)作0來處理。或者
8、將公式改成IF(B3:D3<5,1,0),這樣計(jì)算的結(jié)果就是1,0,1了。???????如果要統(tǒng)計(jì)股價(jià)低于5的股價(jià)和要怎么辦呢?將上述公司修改成“{=SUM(IF(B3:D3<5