Excel 佛山小老鼠說Excel函數

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

先用判斷區域C50:C54是否有等於AA的，結果返回

{TRUE;FALSE;FALSE;TRUE;FALSE}，因為在運算過程中True=1，False=0這樣就得到{=SUM({TRUE;FALSE;FALSE;TRUE;FALSE}*(D50:D54)*(E50:E54))}，然後因為任何數字和0相乘都等於0，也就是符合為AA的就不為0，不符合的就為0上面三個數組相乘最後得到一個新的數組{100;0;0;250;0}，最後用Sum求和，三鍵一齊下得到結果為350

十三．Row()函數在數組公式中的運用

a) 談到數組公式,我們不得不說一下ROW()這個函數,它在數組公式中起到了很大的作用,許多公式中都需要用到它來作為參數

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

c) 我們利用Row(A1:A100)來產生1到100自然數，然後用Sum求和。我們都知道,ROW()是用於返回單元格行號的函數,通常它只能引用一個參數。但是在數組公式中,該函數就能引用多個單元格作為參數,對於整個引用區域進行分別運算,從而就能返回一組數據

ROW(A1)=1

ROW(A2)=2

……

ROW(A100)=100

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

知道了這一點以後,我們就能在數組公式中利用這一個功能來得到一組連續的正整數

當然COLUMN()的作用和ROW()是相同的,上面的計算也能用以下公式:

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

但是相對於ROW()的引用方式來說,A1:A100要比A1:CV1更直觀地體現出所引用的是100行還是100列, 所以ROW()一般來說使用得更普遍些,當然也不排除有時候需要用到COLUMN(),這就要看具體情況了。

十四．從文本里提取數字

a) 效果圖28

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

公式解釋：先輸入函數Mid從中間提取字元，這個函數有三個參數，第一個參數好說，從那裡取，從D7單元格取，第二參數從幾個位置提取，這個頭痛，為什麼頭痛呢，因為數字的位置沒有規律，怎麼辦呢？當然要想辦法，多看看，再想想，無非就是要找到第一個數字出現的位置，數字有那麼多個，有10個阿拉伯數字（0123456789），想到了Find函數去找，以前我們學得時候是一個一個找，剛才我們不是學了數組，我們把10個數字全部一起放進去找，這就對了，這就是數組優勢，怎樣才能產生10個阿位伯數字呢，我們可以輸入常量數組{0,1,2,3,4,5,6,7,8,9} 這個打的辛苦，因此我們可以用Row(A1:A10)-1,記得要給它絕對引用，10個數字一起拿進去找，有的單元格沒有這10個阿拉伯數字公式會報錯，因此用了D7&5/19，因為5/19會產生這個10個阿拉伯數字，當然1/17也會產生這10個阿拉伯數字，怎麼記住它了，我叫大家一句話「我要喝酒」，也就5/19，Find查找之後，會得到一個數組{6;3;8;10;16;4;9;13;14;15}，也就是這10個阿位伯數字的位置，然後我們用Min函數把這個新數組{6;3;8;10;16;4;9;13;14;15}取出來是3，這個3就是數字1出現的位置。回過頭來看Find函數三個參數，第一個查找用了Row($A$1:$A$10)-1,

第二個參數D7&5/19，第三鼐參數用1，表示從第一個位置開始查找。現在我們來看Mid函數的第三個參數，也就是要得到數字的個數，前面我們學過計算字元個數的函數Len和Lenb這兩個函數，這兩個函數區別是，Lenb函數漢字算2個，字母和數字算一個，因些我們得知有一個漢字就會多1，有二個漢字就會多2，這樣可以用Lenb(D7)-Len(D7)得到漢字的個數，現在知道字的總數用Lenb(D7)，數字的個數就等於字的總數減去漢字的個數就是數字的個數，計算公式：

LEN(D7)-(LENB(D7)-LEN(D7)) ，函數Mid這個三個函數我們都解決了

最後有的學生可能會問：老師你的Mid前面怎麼會還有兩個負號呢？

呵呵，函數高手都是這樣的，都會給新手，徒弟留一手，讓新朋友，新手看懂的地方，不和大家開玩笑了，言歸正傳了，打個比方給大家聽一下，9乘以-1等於-9，然後-9再乘以-1得到9，經過運算還是還來的9呢？，沒有變，對，確是沒有變，但是經過乘法運算，而我們的文本型數字經過運算就會轉為數值型數字。這一點在大家要記住它，另外大家還要記住一點，一般情況我們的文本是左對齊，而數字是右對齊。我們的文本函數Right，Left，Mid取出來的數字都是文本型的數字，所以要記得把它轉為數值型數字，不轉的化，你求和結果都是0，你做圖表時，做不出圖表。切記