Excel工作表可以理解為一個(gè)由行、列組成的二維數(shù)組。Excel公式支持?jǐn)?shù)組和數(shù)組公式。數(shù)組可以是常量數(shù)組，也可以是單元格區(qū)域的引用。使用數(shù)組和數(shù)組公式時(shí)，可以逐數(shù)組元素或逐單元格處理后返回結(jié)果。

下面直接從一個(gè)實(shí)例入手，再去討論相關(guān)知識(shí)點(diǎn)。

相關(guān)數(shù)據(jù)及需要達(dá)到的效果：

序號(hào)

記錄

金額

1

吃飯15元

15

2

朋友過(guò)生日送禮金300元

300

3

買米95

95

4

火車票200塊回家

200

5

房租900元

900

6

水費(fèi)200塊錢

200

7

電費(fèi)250元

250

關(guān)鍵是要獲得第一個(gè)數(shù)字出現(xiàn)的位置。

試著使用下面的公式

=FIND(1,B2,1)

可以返回3，表示字符1在字符串“吃飯15元”中的位置。

如果要查找0,1,2,3,4,5,6,7,8,9十個(gè)字符呢？這時(shí)，數(shù)組及數(shù)組公式就可以派上用場(chǎng)了。使用數(shù)組{0,1,2,3,4,5,6,7,8,9}作為參數(shù)，可以逐步處理每一個(gè)數(shù)字在字符串中出現(xiàn)的位置，返回的也是一個(gè)數(shù)組（10個(gè)值）。由此，我們選擇一個(gè)有10個(gè)單元格的連續(xù)區(qū)域D2:M2，輸入=FIND({0,1,2,3,4,5,6,7,8,9},B2,1)，同時(shí)按下CTRL+SHIFT+ENTER，然后復(fù)制數(shù)組公式，得到如下效果：

錯(cuò)誤值#VALUE!表示某數(shù)字在字符串中不存在，所以也就不能返回位置值。

為了避免出現(xiàn)錯(cuò)誤值，可以在字符串后串一個(gè)包含10個(gè)數(shù)字字符的數(shù)字字符串：

上表中每行最小的數(shù)字就是字符串中每一個(gè)數(shù)字出現(xiàn)的位置，為此我們可以使用MIN()函數(shù)。=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789",1))

下一步要獲取數(shù)字長(zhǎng)度。

函數(shù)LEN()可以返回字符數(shù)，函數(shù)LENB()可以返回字符串的字節(jié)數(shù)，一個(gè)數(shù)字一個(gè)字節(jié)，一個(gè)漢字兩個(gè)字節(jié)，組合使用可以獲取數(shù)字的長(zhǎng)度：=LEN(B2)-(LENB(B2)-LEN(B2))

使用mid函數(shù)即可以截取數(shù)字，在公式前使用兩個(gè)負(fù)號(hào)，可將文本轉(zhuǎn)換為數(shù)字：

=--MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789",1)),LEN(B2)-(LENB(B2)-LEN(B2)))

以下公式可以做一個(gè)改良：

1{0,1,2,3,4,5,6,7,8,9}可以用ROW($A$1:$A$10)-1代替；

2"0123456789"可以用5/19代替。

5/19=0.26315789474

得到的下面公式具有同樣效果，更通用和簡(jiǎn)潔：

=--MID(B5,MIN(FIND(ROW($A$1:$A$10)-1,B5&5/19,1)),LEN(B5)-(LENB(B5)-LEN(B5)))

2.1數(shù)組公式

數(shù)組就是一組數(shù)據(jù)，數(shù)組公式可以進(jìn)行多重運(yùn)算（對(duì)每一個(gè)數(shù)組元素或區(qū)域的每一個(gè)單元格都應(yīng)用操作），減少了多次寫于單元格的過(guò)程，可以實(shí)現(xiàn)常用公式較煩鎖的操作，一步到位。

2.2刪除數(shù)組公式

當(dāng)我們對(duì)一個(gè)區(qū)域應(yīng)用數(shù)組公式時(shí)，如D2:D6，使用此區(qū)域，輸入{=B2:B6*C2:C6}，按CTRL+SHIFT+ENTER，會(huì)在D2:D6的每個(gè)單元格輸入{=B2:B6*C2:C6}，都是一樣，此時(shí)數(shù)組公式是一個(gè)整體，不能更改數(shù)組的一部分，也就是不能單獨(dú)刪除其中的一個(gè)單元格的數(shù)組公式，我們要?jiǎng)h除數(shù)組公式要全部選中，當(dāng)我們有時(shí)修改一個(gè)單元格的數(shù)組公式，不能退出時(shí)，大家記得按ESC鍵。

2.3一個(gè)單元格顯示數(shù)組的情況

由于一個(gè)單元格內(nèi)只能儲(chǔ)存一個(gè)數(shù)值，所以當(dāng)結(jié)果是一組數(shù)據(jù)時(shí)，單元格只返回第一個(gè)值。

2.4數(shù)組分類

可分為橫向數(shù)組、縱向數(shù)組、區(qū)域數(shù)組（多行多列），也可以按維來(lái)分，橫向數(shù)組和縱向數(shù)組都屬于一維數(shù)組，區(qū)域數(shù)組屬于二維數(shù)，像這種｛1,2,3,1}就是常量數(shù)組

2.5怎樣查看數(shù)組的結(jié)果（也就是選擇公式的一部分求結(jié)果），當(dāng)然是獨(dú)孤九劍F9或使用“公式求值”來(lái)按順序求值復(fù)合公式的每一部分。

2.6數(shù)組成員中間有時(shí)有分號(hào)，有時(shí)用逗號(hào)是怎么回事？橫向數(shù)組用逗號(hào)分開，縱向數(shù)組用分號(hào)分開。

2.7常量數(shù)據(jù)在函數(shù)里的應(yīng)用

a)23=INDEX({23,24,25,22},1,1)

b)24=INDEX({23,24,25,22},1,2)

c)25=INDEX({23,24,25,22},1,3)

d)22=INDEX({23,24,25,22},1,4)

2.8要么用某些函數(shù)來(lái)取其共性,如SUMMax/Min,Small/Large等

a)94=Sum({23,24,25,22})

b)25=Max({23,24,25,22})

c)22=Min({23,24,25,23})

d)25=Large({23,24,25,22},ROW(A1))

e)22=SMALL({23,24,25,22},ROW(A1))

f)23=SMALL({23,24,25,22},ROW(A2))

g)24=SMALL({23,24,25,22},ROW(A3))

h)25=SMALL({23,24,25,22},ROW(A4))

2.9參數(shù)

a)數(shù)組公式最大的特征就是所引用的參數(shù)是數(shù)組參數(shù)，包括區(qū)域數(shù)組和常量數(shù)組。

b)區(qū)域數(shù)組，是一個(gè)矩形的單元格區(qū)域,如$A$1:$D$5

c)常量數(shù)組，是一組給定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3}

d)數(shù)組公式中的參數(shù)必須為"矩形"，如{1,2,3;1,2}就無(wú)法引用了

2.10輸入

同時(shí)按下CTRL+SHIFT+ENTER，數(shù)組公式的外面會(huì)自動(dòng)加上大括號(hào){}予以區(qū)分。

3.1求表一區(qū)域F42:G44大于10數(shù)據(jù)和

公式：{=SUM((F42:G44>10)*(F42:G44))}

公式解釋：F42:G44>10會(huì)返回{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}，在運(yùn)算時(shí)True=1False=0。

然后數(shù)組{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}和區(qū)域數(shù)組(F42:G44))相對(duì)應(yīng)的數(shù)據(jù)進(jìn)行相乘，相乘之后得到一個(gè)新的數(shù)組{20,0;0,50;30,0}，然后用求和函數(shù)Sum進(jìn)行求和，最后記得三鍵一齊下CTRL+SHIFT+ENTER

3.2求表二產(chǎn)品AA的總價(jià)

公式：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))

c)公式解釋：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))

先用判斷區(qū)域C50:C54是否有等于AA的，結(jié)果返回{TRUE;FALSE;FALSE;TRUE;FALSE}，因?yàn)樵谶\(yùn)算過(guò)程中True=1，F(xiàn)alse=0這樣就得到{=SUM({TRUE;FALSE;FALSE;TRUE;FALSE}*(D50:D54)*(E50:E54))}，然后因?yàn)槿魏螖?shù)字和0相乘都等于0，也就是符合為AA的就不為0，不符合的就為0上面三個(gè)數(shù)組相乘最后得到一個(gè)新的數(shù)組{100;0;0;250;0}，最后用Sum求和，三鍵一齊下得到結(jié)果為350

a)談到數(shù)組公式，我們不得不說(shuō)一下ROW()這個(gè)函數(shù)，它在數(shù)組公式中起到了很大的作用，許多公式中都需要用到它來(lái)作為參數(shù)。

b)我們先來(lái)做一個(gè)題目：求正整數(shù)列1,2,3,4……100這100個(gè)數(shù)字之和(首先假設(shè)你不知道等差數(shù)列求和公式，呵呵)，=SUM(ROW($A$1:$A$100))。

c)我們利用Row(A1:A100)來(lái)產(chǎn)生1到100自然數(shù)，然后用Sum求和。我們都知道，ROW()是用于返回單元格行號(hào)的函數(shù)，通常它只能引用一個(gè)參數(shù)。但是在數(shù)組公式中，該函數(shù)就能引用多個(gè)單元格作為參數(shù)，對(duì)于整個(gè)引用區(qū)域進(jìn)行分別運(yùn)算，從而就能返回一組數(shù)據(jù)。

ROW(A1)=1

ROW(A2)=2

……

ROW(A100)=100

ROW($A$1:$A$100)={1;2;3……100}

知道了這一點(diǎn)以后,我們就能在數(shù)組公式中利用這一個(gè)功能來(lái)得到一組連續(xù)的正整數(shù)。

當(dāng)然COLUMN()的作用和ROW()是相同的，上面的計(jì)算也能用以下公式：

{=SUM(COLUMN($A$1:$CV$1))}

但是相對(duì)于ROW()的引用方式來(lái)說(shuō)，A1:A100要比A1:CV1更直觀地體現(xiàn)出所引用的是100行還是100列，所以ROW()一般來(lái)說(shuō)使用得更普遍些，當(dāng)然也不排除有時(shí)候需要用到COLUMN(),這就要看具體情況了。

－End－