Excel數(shù)組公式從入門到精通之精通篇

1、Excel 數(shù)組公式從入門到精通之精通篇一、課程回憶什么是數(shù)組公式呢？顧名思義就是公式中包含數(shù)組的了，詳細(xì)含義請參看前文。但這里重點提醒的一點就是，如果要使用數(shù)組公式， 在編輯欄輸入完公式以后一定要按下“ Ctrl+Shift+Enter ”組合鍵，使編輯欄的公式處在“ ”之中。二、數(shù)組公式繼續(xù)深入印象中是好幾年前了，當(dāng)時看過的一篇掃盲貼中，作者舉的例子真是太實用了。 具體細(xì)節(jié)記不太清楚了， 大致意思就是使用函數(shù)計算1 到 100 的和。 這里同樣以此為例。1. 求 1 到 100 的和在往下看之前，大家想一下，如果讓你來處理該如何來處理呢？只用一個函數(shù)解決 1 到 100 的和， 當(dāng)然也可以

2、是1000、 10000 甚至更多。 討論具體的數(shù)值沒有太大意義，此處只是希望通過此例讓大家更進(jìn)一步的了解數(shù)組公式的用法。解答： =SUM(ROW(1:100)問題分析：求1至ij 100的和，答案是5050 (小學(xué)生都知道 )，但Excel必須是你告訴了它正確的方法，它才能知道。計算從1 到 100 的和，實際上就是計算1+2+3+4+98+99+100,好了，答案出來了，在編輯欄中輸入“=sum(1+2+3+4+98+99+100) ” 。 相信聰明的一定對此答案不滿意， 雖然能得到正確的結(jié)果， 但很明顯是 “錯誤”的方法。要得到1至ij 100的正確數(shù)列，最簡單的方法就是使用Row()或

3、是Column()函數(shù)，由于個人習(xí)慣，我比較習(xí)慣于Row() ，所以這里以 Row() 函數(shù)為例。熟悉Row()函數(shù)：在A1單元格中輸入“=Row()”，使用填充柄填充至 A5,文件二忤臂-Mier看到什么結(jié)果？是不是每一個單元格中值就是其對應(yīng)的行數(shù)。再貼板匕Ijri=ROW 0)播入, 史畫布同公S工AlA1234毛驚喜：Row()表示單前行，如果使用Row(1:100)就表示一個數(shù)組，其中包含的便是第一到第一百行的行號，即1、2、3、98、99、100這些數(shù)值，現(xiàn)在我們就把這個數(shù)組應(yīng)用到公式中。在工作表的任意一個單元格中輸入“=sum(Row(1:100) :然后按Ctrl+Shift+E

4、nter組合鍵，你會驚喜的發(fā)現(xiàn)，我們要的結(jié)果出現(xiàn)了。二- Microsoft Exce福入頁面育局公式苗用 崛圖二一鼻冒室與至第”電幫玷72.在 Excel2003 中享受 “ SUMIFSSUMIF函數(shù)應(yīng)該很多人都用過，非常好用。但如果遇到多條件判斷的怎么辦呢？從Office 2007 開始，引入了 SUMIFS函數(shù)，可以解決這種多條件求和問題。但如果仍然使用Office 2003 怎么辦呢？其實使用 SUM IF再結(jié)合數(shù)組公式即可實現(xiàn) SUMIFSB勺效 果。如下圖所示，某教師有一張任教的幾個班級的學(xué)生成績表。班一二-I二一三二一二.三 一.1一123456?891 01 11 2名三四五

5、六七A朝汗龍虎昭一 姓張李王就錢超王馬張趙展績貳優(yōu)良優(yōu)良良優(yōu)優(yōu)良優(yōu)良世任務(wù)：統(tǒng)計出“一班”、“二班”共計多少人?此題要如何解決？ SUMIF用兩次？或是 COUNT!兩次？這里還是演示數(shù)組公式的用法，所以先用 SUM口 IF組合的形式。在任一單元格中輸入 “ =SUM（IF（A2:A12= 一班）+（A2:A12=二班）,1,0）”公式按下回車鍵，是不是發(fā)現(xiàn)結(jié)果是“ #VALUE ”，再次進(jìn)入編輯欄然后按下“Ctrl+Shift+Enter ”快捷鍵，是不是發(fā)現(xiàn)正確的結(jié)果出來了?- MicroExcel福入m，AT = SUM （IF （一2 :虱拄 一班）十（A2: A12一二班1；。） 1

6、廣插 尹刑除 二A殳月后K任2 A0到丑N到期到s到到到 班二一I-二二二二三c二I1234567891 onl21 3B名三四五六七八朝汗龍虎昭 姓張李王起錢趙王馬張趙展c成優(yōu)良優(yōu)良良優(yōu)優(yōu)良優(yōu)良優(yōu)J b丈面布舄公式 腿 畝間 視圖這里再次解釋一下這個公式“=SUM（IF（A2:A12= 一班）+（A2:A12=二班”）,1,0）: 外側(cè)SUM沒什么好用的了，就是求（）內(nèi)各數(shù)的和。中間的“IF（A2:A12=一班）+（A2:A12=二班）,1,0）”的運算過程是這樣的，判斷A2:A12區(qū)域內(nèi)單元格的值是否是“一班”，如果是則結(jié)果為1,則此公式計算的結(jié)果依次是“1、0、0、1、0、0、1、0、0

7、、1、0”，因為第一個條件為真，第二個條件肯定就不為真了，因為一個單元格不可能同時等于“一班”和“二班”，所以第一個數(shù)組就是“1、0、0、1、0、0、1、0、0、1、0”。這時再判斷A2:A12區(qū)域內(nèi)單元格的值是否是“二班”，如果是結(jié)果則為1,否則為0,所以這個數(shù)組條件計算的結(jié)果就是“0、1、0、0、0、1、0、1、0、0、0”，中間的加號就是將這兩個數(shù)組相加，也就是說最終的數(shù)組為“1、1、0、1、0、1、1、1、0、1、0”，然后使用SUMt和，結(jié)果就為7 了。TfEl - Microscrft Excel滕卷人反面在意公式 翠挺 而閱 視圖A*一唯入T臼塔式*a：軟年序和曜港2,字口.單元

8、格=SUM （IF （ +。比 A12=二班1. 0）H12345L-7S510 u12131415161718C績一一-四五.班二一-E姓名?。ˋ2A12 ;“一班1二if（A2：A12三二班”/,0）二 D2：D=sum（F2:F12IBOffl 100% W .： 二從上面的圖中標(biāo)注可以看出，所以的公式我全部使用了數(shù)組（A2:A12這就是一個數(shù)組），并且上圖上的沒有使用數(shù)組公式的公式中的數(shù)組全部可以使用單個單元格替 代，之所以全部列出，還是希望大家更好的理解一下數(shù)組。在Excel中，數(shù)組如果不放在數(shù)組公式中使用，通常數(shù)組在特定單元格中只代表與其特定單元格所對應(yīng)的一個值（數(shù)組中的一個元素）

9、，放在數(shù)組公式中使用時， 通常整個數(shù)組元素都會參與運算。三、數(shù)組公式精通這里是一個實際工作中的例子，只是我稍微變化了一下，還是 SUW用的 例子。需求：如下圖所示，現(xiàn)在要統(tǒng)計員工張三在 1號加工所有機(jī)器的“實績”，也就是說在右側(cè)的數(shù)據(jù)中先過濾日期為1,然后再過濾人員為張三的數(shù)據(jù)，最后統(tǒng)計實績的結(jié)果。如果使用一個公式完成這一需求，你能想到嗎？當(dāng)然SUMIFS是除外的，因為SUMIFS是Ofice 2007 以后的產(chǎn)物。126 龍4ECDF1日期人員實績?nèi)掌跈C(jī)器號21張三12152637心1210111412233m11151567-17e181191201211Z223G =員一二四五上八七龍三

10、虎三朝汗三昭一二四五八一二三四 人張李王趙錢張張趙張王馬張展張李王趙張張李 12 3 15 G 789012315 & 78901* IX IX 1- 1- 1 1X 1- 11實4343443423742363654 23224334332 5 644-5 4 74542 3 2 3 包了 3323834 3答案：在上圖所示的 C2單元格中輸入“=SUM(E2:E21=A2)*(G2:G21=B2)*(H2:H21)” 公式，然后按下 “Ctrl+Shift+Enter ”組合鍵，你會發(fā)現(xiàn)想要的結(jié)果已經(jīng)出現(xiàn)了。數(shù)組公式就是這么簡單，解決問題也是這么簡單。這次用的公式可以看出， 比之前用的公式

11、還要簡單， 連 IF 都不要了， 實際上這里的“=”符號就是起到了一個類似IF的效果。這里再說明一下公式的執(zhí)行過程， 公式中 E2:E21 表示數(shù)組區(qū)域， 這個相信已經(jīng)不需要再說明了， 放到數(shù)組公式中就是依次取數(shù)組中的各個數(shù)值， 也就是依次取日期中的值。E2:E21=A2,實際上就是拿日期中的每一個值依次與A2中的日期進(jìn)行比對，如果相等則結(jié)果為 True ，即1，如果不相等則為False 即為0。到了這里也許你有一點明白了，如果第一不相等，則后面的無需再繼續(xù)下去了，因為公式里用的全部是“ * ”乘積符號，任何數(shù)乘 0 等于 0。如果此項符合再繼續(xù)判斷G2:G21 區(qū)域，也就是用姓名依次比對，如果和B2 中的姓名相同， 則為 Ture ， 即 1 ，如果為 False ， 即 0，繼續(xù)下一個回合。如果此項也為Ture ，很明顯前面兩項的結(jié)果為1*1=1,再乘以H2:H21數(shù)組中對應(yīng)的數(shù)字，即符