數(shù)組函數(shù)講座.doc

數(shù)組公式講座（一）：數(shù)組公式入門(mén)數(shù)組公式是我自學(xué)的，下面寫(xiě)的東西可能和外面的相關(guān)參考書(shū)有出入，但結(jié)果和思路是正確的。如有錯(cuò)誤，請(qǐng)大家指正。1、 數(shù)組公式的實(shí)現(xiàn)方法：在論壇中?？梢钥吹皆S多在頭尾帶有“”的公式，有些朋友把這些公式直接復(fù)制粘貼到單元格中，但沒(méi)有出現(xiàn)正確的結(jié)果， 這是為什么呢？其實(shí)這些都是數(shù)組公式，數(shù)組公式的輸入方法是將公式輸入后，不要直接按回車(chē)鍵（Enter），而是要同時(shí)按Ctrl+Shift+Enter，這時(shí)電腦自動(dòng)會(huì)為你添加“”的。在論壇上，為了告訴大家這是數(shù)組公式，故在公式的頭尾都加上了“”。如果不小心按回車(chē)了，可以用鼠標(biāo)點(diǎn)一下編輯欄中的公式，再按Ctrl+Shift+Enter。2、數(shù)組公式的原理（自編）：數(shù)組公式，說(shuō)白了就是同時(shí)對(duì)一組或幾組數(shù)同時(shí)處理，然后得到需要的答案。運(yùn)用數(shù)組公式的最重要的原理是數(shù)于數(shù)之間一一對(duì)應(yīng)下面舉一列說(shuō)明一下（例1）： A1:A10有下列一排數(shù)： 32 14 34 25 36 21 33 12 12 16第一問(wèn)：求大于20的數(shù)的和（此題亦可用SUMIF函數(shù)來(lái)處理，這里為的是講清數(shù)組公式的原理）。答案為：181，公式為=SUM(IF(A1:A1120,A1:A11)，EXCEL處理的原理：首先由IF函數(shù)進(jìn)行判斷，一一對(duì)應(yīng)，如果大于20，則為本身，否則為FALSE。經(jīng)過(guò)IF函數(shù)的處理，原來(lái)的A1:A10處理后變成： 32 FALSE 34 25 36 21 33 FALSE FALSE FALSE 然后用SUM函數(shù)進(jìn)行求和，SUM函數(shù)忽略文本。第二問(wèn)：求大于20的數(shù)的個(gè)數(shù)（此題亦可用COUNTIF函數(shù)來(lái)處理，這里為的是講清數(shù)組公式的原理）。答案為：6，公式為=SUM(IF(A1:A1120,1)，EXCEL處理的原理：首先由IF函數(shù)進(jìn)行判斷，如果大于20，則為1，否則為FALSE。經(jīng)過(guò)IF函數(shù)的處理，原來(lái)的A1:A10處理后變成： 1 FALSE 1 1 1 1 1 FALSE FALSE FALSE 然后用SUM函數(shù)進(jìn)行求和,亦可用COUNT函數(shù)進(jìn)行計(jì)數(shù)。第三問(wèn)：求大于20的數(shù)的個(gè)數(shù)減去小于等于20的數(shù)的個(gè)數(shù)的值。答案為：1，公式為=SUM(IF(A1:A1120,1,-1)，EXCEL處理的原理：首先由IF函數(shù)進(jìn)行判斷，如果大于20，則為1，否則為-1。經(jīng)過(guò)IF函數(shù)的處理，原來(lái)的A1:A10處理后變成：1-111111-1-1-1然后用SUM函數(shù)進(jìn)行求和。數(shù)組公式入門(mén)講座之二（上）10月23日給大家出了三道題，其實(shí)就題目的難度來(lái)說(shuō)，如果既會(huì)用函數(shù)，又懂些數(shù)組公式使用的話(huà)，那么我想這些題目應(yīng)該都能解決的。下面通過(guò)對(duì)這幾道題目的分析，來(lái)講講數(shù)組公式的運(yùn)用。第一題：求1到1000的和。1+2+3+4+.+997+998+999+1000=？這題，思路非常簡(jiǎn)單，就是如何生成一個(gè)1到1000這樣子的數(shù)組，然后全部加起來(lái)。所以，如果要用數(shù)組公式的話(huà)，你就得先問(wèn)自己，到底需要一個(gè)怎樣的數(shù)組才能達(dá)到目的。那么，接下來(lái)，就要考慮，通過(guò)什么函數(shù)，才能達(dá)到這個(gè)目的。就這題來(lái)說(shuō)，就會(huì)使用ROW()函數(shù)，一個(gè)取得引用行號(hào)的函數(shù)，=ROW(1:1000)，通過(guò)這個(gè)函數(shù)就能生成這樣一個(gè)數(shù)組1;2;3;998;999;1000，然后用SUM()進(jìn)行求和，就得到答案了。額外提醒大家的是，通過(guò)SUM()這個(gè)函數(shù)，出來(lái)的結(jié)果就是一個(gè)值，不會(huì)是數(shù)組的。（COLUMN()函數(shù)也可以，這里就不講了）。答案：=SUM(ROW(1:1000)（CHENJUN兄另一種使用INDIRECT()的做法，是為了避免行范圍有行刪除、插入，這是在實(shí)際使用中出現(xiàn)問(wèn)題地解決辦法，這次暫不討論=SUM(ROW(INDIRECT(1:1000)）。第二題：求在1到1000中，是7的倍數(shù)的數(shù)的和，包括7在內(nèi)。7+14+21+28+.=？這題，是在上題的基礎(chǔ)上，演變而來(lái)的，也就是一個(gè)數(shù)組的處理的問(wèn)題。既然得到了1到1000這樣子的數(shù)組，那么如何得到7的倍數(shù)的數(shù)的數(shù)組？方法很多，下面一一講解：用的最多的方法是：=SUM(ROW(1:1000)*(MOD(ROW(1:1000),7)=0)因?yàn)槭乔蠛?，把不符合條件的數(shù)字，變成0，然后求和，就可以了，EXCEL是如何處理的，請(qǐng)看看數(shù)組講座一。把公式寫(xiě)的標(biāo)準(zhǔn)化一些：=SUM(IF(MOD(ROW(1:1000),7)=0,ROW(1:1000),0)其中，MOD(ROW(1:1000),7)是對(duì)ROW(1:1000)生成的數(shù)組1;2;3;998;999;1000的再處理，再生成的數(shù)組，從表面上來(lái)看新生成的數(shù)組1;1;1;1;1;1;0;1;1;1，一點(diǎn)用都沒(méi)有，但實(shí)際上包含了1;2;3;998;999;1000能不能被7整除的信息，即如果是1，那么對(duì)應(yīng)的這個(gè)數(shù)不能被7整除，而如果是0的話(huà)，能被7整除，這樣IF(MOD(ROW(1:1000),7)=0,ROW(1:1000),0)再次生成一個(gè)我們都想要得數(shù)組，那就是0;0;0;0;0;0;7;0;0;0;0;0;0;14;0;0;0，這樣在用SUM()就能得到索要的答案了。從上面可以看出，在數(shù)組公式中，如果有IF()參與，條件是數(shù)組公式，對(duì)或錯(cuò)有一個(gè)是數(shù)組，或都是數(shù)組的話(huà)，需要一一對(duì)應(yīng)。這例中，對(duì)是數(shù)組，他就和條件一一對(duì)應(yīng)，才會(huì)產(chǎn)生0;0;0;0;0;0;7;0;0;0;0;0;0;14;0;0;0的結(jié)果。其他的方法：=SUM(IF(ROW(1:200)*71000,ROW(1:200)*7,0)思路：直接生成一個(gè)7倍數(shù)的數(shù)組，然后做一個(gè)1000的判斷，再求和。=SUM(TRUNC(WEEKDAY(ROW(1:1000)/7)*ROW(1:1000)思路：使用WEEKDAY()將數(shù)組轉(zhuǎn)化為一個(gè)以1;2;3;4;5;6;7作為輪回的數(shù)組，正好和7的倍數(shù)相呼應(yīng)（當(dāng)然如果用=SUM(IF(WEEKDAY(ROW(1:1000)=7, ROW(1:1000),0)也是可以的）。然后除以7后，再TRUNC()后，變成0;0;0;0;0;0;1;0;0;0這樣的數(shù)組，然后和1;2;3;4;5;6;7;998;999;1000相乘，得出結(jié)果。這里，需指明的是，我在用WEEKDAY()的時(shí)候，忽略了一個(gè)參數(shù)，那是默認(rèn)值，星期一是1，以此類(lèi)推；而TRUNC()也忽略了一個(gè)參數(shù)，默認(rèn)值為0，即取整。所以，思路從某種程度上講，決定公式的使用方法，有些人排出來(lái)的公式很復(fù)雜，有些人很簡(jiǎn)單，就是這個(gè)道理。大家在平時(shí)學(xué)用公式的時(shí)候，不僅要學(xué)習(xí)函數(shù)的使用方法，更主要的是要學(xué)習(xí)思路，不要只求結(jié)果，不求過(guò)程。最后一題，我想詳細(xì)分析一下，加上最近有點(diǎn)忙，準(zhǔn)備放在下周末，請(qǐng)大家諒解。CELL 全部顯示全部隱藏返回某一引用區(qū)域的左上角單元格的格式、位置或內(nèi)容等信息。語(yǔ)法CELL(info_type,reference)Info_type 為一個(gè)文本值，指定所需要的單元格信息的類(lèi)型。下面列出 info_type 的可能值及相應(yīng)的結(jié)果。如果 MICROSOFT EXCEL 的格式為CELL 返回值常規(guī)G0F0#,#0,00.00F2#,#0.00,2$#,#0_);($#,#0)C0$#,#0_);Red($#,#0)C0-$#,#0.00_);($#,#0.00)C2$#,#0.00_);Red($#,#0.00)C2-0%P00.00%P20.00E+00S2# ?/? 或 # ?/?Gyy-m-d 或 yy-m-d h:mm 或 dd-mm-yyD4d-mmm-y