本帖最后由 佛山小老鼠 于 2012-10-20 15:30 編輯 第九講 數(shù)組基礎知識和從文本里提取數(shù)字
一．數(shù)組公式
a) 數(shù)組就是一組數(shù)據(jù)，數(shù)組公式可以進行多重運算，減少了多次寫于單元格的過程，可以實現(xiàn)常用公式較煩鎖的操作，一步到位
二．刪除數(shù)組公式
a) 因為數(shù)組公式不能刪除一個，我們要刪除數(shù)組公式要全部選中，當我們有時修改一個單元格的數(shù)組公式，不能退出時，大家記得按ESC鍵
三．一個單元格顯示數(shù)組的情況
a) 由于一個單元格內(nèi)只能儲存一個數(shù)值,所以當結果是一組數(shù)據(jù)時,單元格只返回第一個值
四．數(shù)組分類
a) 橫向數(shù)組；縱向數(shù)組，區(qū)域數(shù)組（多行多列），也可以按維來分，橫向數(shù)組和縱向數(shù)組都屬于一維數(shù)組，區(qū)域數(shù)組屬于二維數(shù)，像這種｛1,2,3,1}就是常量數(shù)組
五．怎樣查看數(shù)組的結果，當然是獨孤九劍F9
六．數(shù)組成員中間有時有分號，有時用逗號是怎么回事？，答:橫向數(shù)組用逗號分開，縱向數(shù)組用分號分開
七．常量數(shù)據(jù)在函數(shù)里的應用
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)
八．要么用某些函數(shù)來取其共性,如SUM Max/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))
九．.參數(shù)
a) 數(shù)組公式最大的特征就是所引用的參數(shù)是數(shù)組參數(shù),包括區(qū)域數(shù)組和常量數(shù)組。
b) 區(qū)域數(shù)組,是一個矩形的單元格區(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}就無法引用了
十．輸入
a) 同時按下CTRL+SHIFT+ENTER，數(shù)組公式的外面會自動加上大括號{}予以區(qū)分
十一． 實例一：求表一區(qū)域F42:G44大于10數(shù)據(jù)和
a) 公式：{=SUM((F42:G44>10)*(F42:G44))}
公式解釋：F42:G44>10會返回{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}，我們以前的課件里講過在運算時True=1 False=0, 然后數(shù)組{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}和區(qū)域數(shù)組(F42:G44))相對應的數(shù)據(jù)進行相乘，相乘之后得到一個新的數(shù)組{20,0;0,50;30,0}，然后用求和函數(shù)Sum進行求和，最后記得三鍵一齊下CTRL+SHIFT+ENTER
十二． 實例二：求表二產(chǎn)品AA的總價 （如圖25）
a) 公式：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))
a) 公式解釋：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))
先用判斷區(qū)域C50:C54是否有等于AA的，結果返回
{TRUE;FALSE;FALSE;TRUE;FALSE}，因為在運算過程中True=1，F(xiàn)alse=0這樣就得到{=SUM({TRUE;FALSE;FALSE;TRUE;FALSE}*(D50:D54)*(E50:E54))}，然后因為任何數(shù)字和0相乘都等于0，也就是符合為AA的就不為0，不符合的就為0上面三個數(shù)組相乘最后得到一個新的數(shù)組{100;0;0;250;0}，最后用Sum求和，三鍵一齊下得到結果為350
十三．Row()函數(shù)在數(shù)組公式中的運用
a) 談到數(shù)組公式,我們不得不說一下ROW()這個函數(shù),它在數(shù)組公式中起到了很大的作用,許多公式中都需要用到它來作為參數(shù)
b) 我們先來做一個題目：求正整數(shù)列1,2,3,4……100這100個數(shù)字之和(首先假設你不知道等差數(shù)列求和公式,呵呵)，=SUM(ROW($A$1:$A$100))
c) 我們利用Row(A1:A100)來產(chǎn)生1到100自然數(shù)，然后用Sum求和。我們都知道,ROW()是用于返回單元格行號的函數(shù),通常它只能引用一個參數(shù)。但是在數(shù)組公式中,該函數(shù)就能引用多個單元格作為參數(shù),對于整個引用區(qū)域進行分別運算,從而就能返回一組數(shù)據(jù)
ROW(A1)=1
ROW(A2)=2
……
ROW(A100)=100
ROW($A$1:$A$100)={1;2;3……100}
知道了這一點以后,我們就能在數(shù)組公式中利用這一個功能來得到一組連續(xù)的正整數(shù)
當然COLUMN()的作用和ROW()是相同的,上面的計算也能用以下公式:
{=SUM(COLUMN($A$1:$CV$1))}
但是相對于ROW()的引用方式來說,A1:A100要比A1:CV1更直觀地體現(xiàn)出所引用的是100行還是100列, 所以ROW()一般來說使用得更普遍些,當然也不排除有時候需要用到COLUMN(),這就要看具體情況了。
十四．從文本里提取數(shù)字
a) 效果圖28
=--MID(D7,MIN(FIND(ROW($A$1:$A$10)-1,D7&5/19,1)),LEN(D7)-(LENB(D7)-LEN(D7)))
公式解釋：先輸入函數(shù)Mid從中間提取字符，這個函數(shù)有三個參數(shù)，第一個參數(shù)好說，從那里取，從D7單元格取，第二參數(shù)從幾個位置提取，這個頭痛，為什么頭痛呢，因為數(shù)字的位置沒有規(guī)律，怎么辦呢？當然要想辦法，多看看，再想想，無非就是要找到第一個數(shù)字出現(xiàn)的位置，數(shù)字有那么多個，有10個阿拉伯數(shù)字（0123456789），想到了Find函數(shù)去找，以前我們學得時候是一個一個找，剛才我們不是學了數(shù)組，我們把10個數(shù)字全部一起放進去找，這就對了，這就是數(shù)組優(yōu)勢，怎樣才能產(chǎn)生10個阿位伯數(shù)字呢，我們可以輸入常量數(shù)組{0,1,2,3,4,5,6,7,8,9} 這個打的辛苦，因此我們可以用Row(A1:A10)-1,記得要給它絕對引用，10個數(shù)字一起拿進去找，有的單元格沒有這10個阿拉伯數(shù)字公式會報錯，因此用了D7&5/19，因為5/19會產(chǎn)生這個10個阿拉伯數(shù)字，當然1/17也會產(chǎn)生這10個阿拉伯數(shù)字，怎么記住它了，我叫大家一句話“我要喝酒”，也就5/19，F(xiàn)ind查找之后，會得到一個數(shù)組{6;3;8;10;16;4;9;13;14;15}，也就是這10個阿位伯數(shù)字的位置，然后我們用Min函數(shù)把這個新數(shù)組{6;3;8;10;16;4;9;13;14;15}取出來是3，這個3就是數(shù)字1出現(xiàn)的位置?；剡^頭來看Find函數(shù)三個參數(shù)，第一個查找用了Row($A$1:$A$10)-1,
第二個參數(shù)D7&5/19，第三鼐參數(shù)用1，表示從第一個位置開始查找?，F(xiàn)在我們來看Mid函數(shù)的第三個參數(shù)，也就是要得到數(shù)字的個數(shù)，前面我們學過計算字符個數(shù)的函數(shù)Len和Lenb這兩個函數(shù)，這兩個函數(shù)區(qū)別是，Lenb函數(shù)漢字算2個，字母和數(shù)字算一個，因些我們得知有一個漢字就會多1，有二個漢字就會多2，這樣可以用Lenb(D7)-Len(D7)得到漢字的個數(shù)，現(xiàn)在知道字的總數(shù)用Lenb(D7)，數(shù)字的個數(shù)就等于字的總數(shù)減去漢字的個數(shù)就是數(shù)字的個數(shù)，計算公式：
LEN(D7)-(LENB(D7)-LEN(D7)) ，函數(shù)Mid這個三個函數(shù)我們都解決了
最后有的學生可能會問：老師你的Mid前面怎么會還有兩個負號呢？
呵呵，函數(shù)高手都是這樣的，都會給新手，徒弟留一手，讓新朋友，新手看懂的地方，不和大家開玩笑了，言歸正傳了，打個比方給大家聽一下，9乘以-1等于-9，然后-9再乘以-1得到9，經(jīng)過運算還是還來的9呢？，沒有變，對，確是沒有變，但是經(jīng)過乘法運算，而我們的文本型數(shù)字經(jīng)過運算就會轉為數(shù)值型數(shù)字。這一點在大家要記住它，另外大家還要記住一點，一般情況我們的文本是左對齊，而數(shù)字是右對齊。我們的文本函數(shù)Right，Left，Mid取出來的數(shù)字都是文本型的數(shù)字，所以要記得把它轉為數(shù)值型數(shù)字，不轉的化，你求和結果都是0，你做圖表時，做不出圖表。切記
