頁: [1] 
|
 |
|
 作者
|
主題: 陣列公式之說明 (閱讀 2480 次)
|
oobird
凸槌的木工
論壇維護群
 離線
文章: 4679
|
 |
陣列公式之說明
« 於: 2005-05-21, 20:13:06 » |
|
陣列公式說明集~作者：Kevin Fu 公司：Eraser2000 提供者：gong
[附件已被管理員刪除]
|
|
|
|
« 最後編輯時間: 2005-05-21, 20:16:24 由 oobird »
|
已記錄
|
結廬待慧君
|
|
|
jack001
小學生
離線
文章: 2
|
可惜等級不夠不能下載
|
|
|
|
|
已記錄
|
|
|
|
bosco_yip
版主
離線
文章: 697
|
Eraser2000 自說自話之 :陣列公式
Part I
簡述:
陣列公式:
是用於建立可以產生多個結果, 或對可以存放在行和列中, 的一組參數進行運算的單個公式。
陣列公式的特點就是, 可以執行多重計算, 它返回的是一組資料結果。
由於一個單格內, 只能儲存一個數值, 所以當結果是一組資料時, 單格只返回第一個值, 如:
={23,24,25,22} ----> 返回第一個值 (23)
如果你需要用到所有的運算結果時, ,要麼用多個單格, 去分別返回, 如:
=INDEX({23,24,25,22},1) ------> 返回 23
=INDEX({23,24,25,22},2) ------> 返回 24
=INDEX({23,24,25,22},3) ------> 返回 25
=INDEX({23,24,25,22},4) ------> 返回 22
要麼, 用某些函數, 來取其共性, 如 SUM MAX / MIN 等
=SUM({23,24,25,22}) ------> 返回 94
=MAX({23,24,25,22}) ------> 返回 25
參數：
陣列公式, 最大的特徵, 就是所引用的參數是, 陣列參數, 包括區域陣列, 和常量陣列。
區域陣列, 是一個矩形的單格區域, 如 $A$1:$D$5
常量陣列, 是一組給定的常量, 如 {1,2,3} 或 {1;2;3} 或 {1,2,3;1,2,3}
陣列公式中的參數, 必須為 "矩形", 如 {1,2,3;1,2} 就無法引用了
輸入：
同時按下 CTRL+SHIFT+ENTER
陣列公式的外面, 會自動加上大括弧 { } 予以區分
{=SUM(($I$23:$I$25>10)*($J$23:$J$25))}
有的時候, 看上去是一般應用的公式, 也應該是屬於陣列公式, 只是它所引用的是 陣列常量
對於參數為常量陣列的公式, 則在參數外有大括弧 { }, 公式外則沒有 , 輸入時, 也不必按 CTRL+SHIFT+ENTER
=SUM(({10;20;30}>10)*{11;22;33})
可以看到, 這兩個公式的結果, 是一樣的。
接下來, 讓我們看看陣列公式, 是如何進行工作的。
Part I End ( 完 )
備注, Eraser 說:
陣列公式, 可以用幾個一般公式, 和一些過渡資料來代替, 從而達到同樣的目的。
不用陣列公式, 地球同樣轉, 所以千萬不要迷信陣列公式, 以免走火入魔!呵呵。
|
|
|
|
« 最後編輯時間: 2007-11-30, 07:40:01 由 bosco_yip »
|
已記錄
|
|
|
|
bosco_yip
版主
離線
文章: 697
|
Eraser2000自說自話之:陣列公式Part II產品編號……產品單價……產品數量
AA………...….1………...….100
BB………...….2………...….150
CC………...….3………...….200
AA………...….1………...….250
BB………...….2………...….300問題: 求產品 AA 的總價{=SUM(IF(($B$4:$B$8="AA"),($C$4:$C$8)*($D$4:$D$8),0))} ------> 返回 350 它的運算過程, 是這樣的:1. IF函數先判斷 $B$4:$B$8 裏 =AA 的參數, 返回的結果為 : AA……--> TRUE..……1……100
BB……--> FALSE……2……150
CC……--> FALSE……3……200
AA……--> TRUE..……1……250用公式, 表述為 :{=SUM(IF({TRUE;FALSE;FALSE;TRUE;FALSE},($C$4:$C$8)*($D$4:$D$8),0))} 2. ($C$4:$C$8)*($D$4:$D$8) 對於返回 FALSE 的, 則直接返回 0 TRUE………...….1………...….100………..--> =1*100 >> 返回 100
FALSE……….….2………...….150………..--> =0 >> 返回 0
FALSE……….….3………...….200………..--> =0 >> 返回 0
TRUE………...….1………...….250………..--> =1*250 >> 返回 250
FALSE……….….2………...….300………..--> =0 >> 返回 03. {100;0;0;250;0} 4. 最後用 SUM 函數, 把這一組陣列常量相加, 得到最後結果 =SUM({100;0;0;250;0}) ------> 返回 350該公式可簡化為 {=SUM(($B$4:$B$8="AA")*$C$4:$C$8*$D$4:$D$8)} ------> 返回 350在加減乘除的運算中, TRUE=1, FALSE=0, 所以邏輯值, 能直接參與運算。 但是, SUM函數中, 如果引用的資料區域中, 含有邏輯值的話, 是忽略邏輯值的, 除非是直接作為參數。 所以, 當你需要在 SUM 中, 使資料區域中的 TRUE=1, FALSE=0 的話, 必須要使 TRUE / FALSE 先參與一次四則運算, 轉換成數值。 看看下面的例子能幫助你理解邏輯值是如何出來的,並能更好地理解上面這段話。 =(1=1) ------> 返回 TRUE
=(1+1=1) ------> 返回 FALSE
=TRUE+FALSE ------> 返回 1
=FALSE-TRUE ------> 返回 -1
=TRUE*FALSE ------> 返回 0
=SUM(TRUE,FALSE) ------> 返回 1
=SUM(C41,C42) ------> 返回 0
=SUM(C41*1,C42*1) ------> 返回 1
IF()的判斷可以被簡化,也就是利用了這一點,在Part IV裏,還有更詳細的例子。 相信你現在已經對陣列公式有了個大概的瞭解了,下面讓我們來看一些陣列公式的應用。 Part II End ( 完 )備注, Eraser 說: TRUE 和 FALS E的問題, 在非陣列公式中 也是著很廣泛的運用的, 瞭解這些 是非常有幫助的。 所以, 我在這裏也說了許多, 希望你們不要說我囉嗦哦 
|
|
|
|
|
已記錄
|
|
|
|
bosco_yip
版主
離線
文章: 697
|
Eraser2000自說自話之:陣列公式Part III
Row()函數, 在陣列公式中的運用
談到陣列公式, 我們不得不說一下 ROW( ) 這個函數, 它在陣列公式中, 起到了很大的作用, 許多公式中, 都需要用到它, 來作為參數。 我們先來做一個題目： 求正整數列 1,2,3,4……10 0這100個數字之和 (首先假設, 你不知道等差數列求和公式, 呵呵) {=SUM(ROW($A$1:$A$100))}------> 返回 6322我們都知道, 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))}------> 返回 5050但是, 相對於 ROW( ) 的引用方式來說, A1:A100 要比 A1:CV1 更直觀地, 體現出所引用的是 100行 還是100列 所以, ROW ( ) 一般來說, 使用得更普遍些, 當然也不排除, 有時候需要用到 COLUMN( ), 這就要看具體情況了。 舉個例子吧, 我們在 Part I 裏好象說了個, 把一組4個資料, 分別列出 {23,24,25,22}, 當是我是用了 =INDEX({23,24,25,22},1), 每行的參數, 都是手工改的, 但是現在用了 ROW( ) 的話, 那麼, 只需要輸入第一個公式, 其他的拖拽就行了, 如下: =INDEX({23,24,25,22},ROW(A1))------> 返回 23=INDEX({23,24,25,22},ROW(A2))------> 返回 24=INDEX({23,24,25,22},ROW(A3))------> 返回 25=INDEX({23,24,25,22},ROW(A4))------> 返回 22如果, 是需要分列來顯示, 那麼就用 COLUMN( ) 來的方便了 23………...….24………...….25………..22Part III End ( 完 )備注, Eraser說: 我偏不用 ROW( ) 函數來返回數列, 我偏要自己, 一個個輸入進去, 這個嘛...也是可以的哦
|
|
|
|
« 最後編輯時間: 2007-11-30, 19:57:10 由 bosco_yip »
|
已記錄
|
|
|
|
oobird
凸槌的木工
論壇維護群
離線
文章: 4679
|
=INDEX({23,24,25,22},ROW(A1))------> 返回 23
=INDEX({23,24,25,22},ROW(A2))------> 返回 25
=INDEX({23,24,25,22},ROW(A3))------> 返回 #REF!
=INDEX({23,24,25,22},ROW(A4))------> 返回 #REF!
這個不對吧？
|
|
|
|
|
已記錄
|
結廬待慧君
|
|
|
bosco_yip
版主
離線
文章: 697
|
Eraser2000自說自話之:陣列公式Part IV利用陣列公式進行條件求和條件求和的公式, 我們一般都會使用 SUMIF , 但是 SUMIF 只能進行單個條件求和, 而陣列公式, 能幫助你進行多條件求和。 產品編號……….生產日期………...產品數量………...產品數量
AA………..…....2000/06/15……..….100…………..….100
AA………..…....2000/06/20……..….125…………..….125
BB………..…....2000/06/30……..….150…………..….150
BB………..…....2000/07/10……..….175…………..….175
CC………..…....2000/07/15……..….200…………..….200
CC………..…....2000/07/20……..….225…………..….225
AA………..…....2000/07/30……..….250…………..….250
AA………..…....2000/08/10……..….275…………..….275
BB………..…....2000/08/15……..….300…………..….300
BB………..…....2000/08/20……..….325…………..….325
CC………..…....2000/08/30……..….350…………..….350
CC………..…....2000/10/10……..….375…………..….375
DD………..…....2000/10/15……..….400…………..….400
DD………..…....2000/10/30……..….425…………..….4251. 求產品BB的8月份產量 {=SUM(IF(($B$7:$B$20="BB")*(MONTH($C$7:$C$20)=8),($E$7:$E$20),0))}------> 返回 625我們是這樣來考慮的: 用IF()來判斷,如果$B$7:$B$20="BB"而且MONTH($C$7:$C$20)=8的話, 那麼我們就取$E$7:$E$20中對應的值,否則就讓它等於0。 公式中的"*"相當於AND,即同時滿足條件該公式可以作如下簡化 {=SUM(($B$7:$B$20="BB")*(MONTH($C$7:$C$20)=8)*$E$7:$E$20)}------> 返回 6252. 求產品 BB 和 CC 的總產值 {=SUM(IF(($B$7:$B$20="BB")+($B$7:$B$20="CC"),($E$7:$E$20))*($D$7:$D$20))}------> 返回 5350公式中的 "+" 相當於 OR, 即滿足條件之一即可該公式, 可以作如下簡化 {=SUM((($B$7:$B$20="BB")+($B$7:$B$20="CC"))*$E$7:$E$20*$D$7:$D$20)}------> 返回 53503. 求8月份之前 AA 的產量, 和8月份之後, 不包括 AA 的產品產量之和 {=SUM(IF((MONTH($C$7:$C$20)>=8)<>($B$7:$B$20="AA"),$E$7:$E$20))}------> 返回 2650公式中的"<>"相當於, 不同時滿足, 即要麼滿足條件1, 要麼滿足條件2, 同時滿足的不算以下公式, 和上面的公式等價 {=SUM(IF((MONTH($C$7:$C$20)>=8)<>($B$7:$B$20="AA"),$E$7:$E$20))}------> 返回 2650"<>" 還可以用 "-" 來代替{=SUM(IF(($B$7:$B$20="AA")-(MONTH($C$7:$C$20)>=8),$E$7:$E$20))}------> 返回 2650該公式, 可以作如下簡化 {=SUM((($B$7:$B$20="AA")<>(MONTH($C$7:$C$20)>=8))*$E$7:$E$20)}------> 返回 2650簡化時, 注意只能用 "<>" 而不能用 "-"(有興趣的朋友, 可以試試, 如果一定要用 " -" 來進行簡化, 該怎麼做呢？) 看完了這些後, Eraser2000 再給你佈置個家庭作業: 求產品 AA 和 BB 在8月份的總產值答案在N34單格, 不過先自己做, 別先看哦 Part IV End ( 完 )備注, Eraser說: 對於公式的簡化問題, 我個人認為 如果不是很熟練的情況下, 最好還是保留 IF( ) 語句。 因為有IF在的話 你對公式將很容易理解, 否則時間一長, 不熟練的人 可能自己都想不起來 當時的公式是怎麼回事了
|
|
|
|
« 最後編輯時間: 2007-12-02, 21:53:04 由 bosco_yip »
|
已記錄
|
|
|
|
|
頁: [1] 
|
|
|
|
有贊助本論壇經營的會員,請務必來信通知小誌,這樣才能為您更新會員群組喔!!
