[EXCEL] 淺談陣列公式－高標低標的計算

　
　　相關函數：IF() / AVERAGE()

　　什麼是「陣列公式」？套用 Excel 的說明檔：「針對一或多組值執行多個計算，然後傳回單一結果或多個結果的公式。陣列公式括在大括弧 { } 中，且藉由按 CTRL+SHIFT+ENTER 輸入。」

　　舉例來說，一般公式 =IF(A1>60,A1,"") 是以單一儲存格 A1 為輸入值，計算結果也是單一值。這時，如果把 A1 改成陣列 A1:A5，再按 CTRL+SHIFT+ENTER 完成輸入，成為陣列公式 {=IF(A1:A5>60,A1:A5,"")}，結果會如何？

##CONTINUE##

　　答案是 Excel 會做 5 次 IF() 計算，每次的輸入值分別是 A1/A2/A3/A4/A5，最後得到 5 組答案，組成一個結果陣列。這個結果陣列，可以進一步當做其他函數的輸入值，或是把結果陣列的第一組結果，也就是針對 A1 運算所得的結果，顯示在儲存格中。

　　舉個實際的例子，如下表，

　　10 組分數，如果要計算平均分數，B12 =AVERAGE(B2:B11) 即可。但是如果要計算高標呢？

　　所謂高標，是指分數高於平均分數的所有人的平均分數。傳統的做法是，在 C 欄輸入公式把高於平均分數的人先挑出來，C2 =IF(B2>$B$12,B2,"")，公式往下複製，再將 C 欄做平均即可，結果如 C12 =AVERAGE(C2:C11)。

　　但是，如果不想多出一個 C 欄，想直接算出結果呢？那就得靠「陣列公式」了。如 B13，輸入陣列公式

=AVERAGE(IF(B2:B11>B12,B2:B11,""))

　　再按 CTRL+SHIFT+ENTER 完成輸入，就可以直接得到答案。

　　看出奧妙了嗎？B13 的陣列公式其實就是 C12 和 C2:C11 的組合！C2:C11 的內容，和陣列公式做了十次 IF() 運算之後產生的結果陣列一樣，只是在陣列公式中，這個結果陣列在 Excel 內部就做掉了，外面就不必再多用一個 C 欄來計算。

　　看懂了陣列公式的奧妙，那低標就不困難了，B14

=AVERAGE(IF(B2:B11<B12,B2:B11,""))

　　再按 CTRL+SHIFT+ENTER 完成輸入。

　　陣列公式注意事項：

• 一定要以 CTRL+SHIFT+ENTER 完成輸入
• 公式中用到的陣列大小一定要一樣

2007-3-29 補充：

　　如果高標的定義是「全班分數前50%的平均」，則可以利用函數 QUARTILE() 來取得全班前 50% 的分數分界點。新的公式為

=AVERAGE(IF(B2:B11>QUARTILE(B2:B11,2),B2:B11,""))

　　記得一樣要按 CTRL+SHIFT+ENTER 成為陣列公式。

丫丫的水彩畫

　
　　丫丫喜歡到處塗塗畫畫，所以媽媽在牆上貼了張大白報紙，讓她自由創作。今天的水彩創作主題是－「海底世界」。

##CONTINUE##

　　從上面看下來，右邊最大隻的魚是「藍鯨」，左邊三角頭的是「大王魷魚」。這是上次去台中科博館看３Ｄ立體電影的內容－「藍鯨大戰大王魷魚」，丫丫至今印象深刻。順帶一提，大王魷魚下面那隻顏色較淺，體積較小的三角頭，是大王魷魚的寶寶－「小王魷魚」是也（嗯...有點像賣魷魚羹的）。

　　藍鯨肚子中的咖啡色點點，是藍鯨的...蛋！？丫丫，對不起，是爸爸不好，我沒有教你藍鯨是哺乳動物，胎生的，不生蛋。

　　藍鯨下面的三個大中小橢圓形怪物，是大龍貓，中龍貓，和小龍貓。宮崎駿的卡通看過吧？就是那三隻。我知道不太寫實，但是創意比較重要，對吧！

　　再往下是小丑魚。本來它不是小丑魚的，但是後來變成小丑魚尼莫了。尼莫頭上和肚子裡的咖啡色方塊，是...巧克力！？嗯...「有人的巧克力不小心掉到海裡，砸到尼莫，就被尼莫吃掉了」，這是丫丫的說法。尼莫肚子中的咖啡色點點，是小丑魚的蛋！答對了，小丑魚才會生蛋！！

　　再來看看配角。右上方的紅色和藍色小人，是「小紅」和「小藍」，他們是丫丫的隱形小精靈朋友。小紅射出來的是「紅色雷劍」，是小紅的武器。至於左邊的咖啡色手，是丫丫要求我畫的，就叫「阿扁下台」，呃...又是爸爸的錯，不應該看太多新聞，讓你幼小純潔的心靈受到骯髒政治的污染。

　　導讀完畢，謝謝收看。

彩虹花田－大溪花海農場

　
　　頂著暖暖的冬陽，到戶外散步是最棒的了，這次選的，是位於桃園大溪，慈湖附近的「大溪花海農場」。

　　花海農場人如其名，迎面而來的就是一大片花海：一整片純紫色的鼠尾草，薰衣草，一大片各色花卉組成的彩虹般的花田，金黃色的金針花海（現在季節不對，改種黃色小花），隨風搖曳的波斯菊田，如果你喜歡花，一定會喜歡這裡。

［彩虹花田中的造型風車（不會動啦），丫丫一看到就堅持要將它入鏡。］

##CONTINUE##

［這張是在風車旁拍的，可以看出花田有「彩虹」的味道。］

［波斯菊花田的面積沒有彩虹花田大，但把拍照角度拉低一點，拍起來也頗有花海的感覺。］

　　和桃園其他純粹種花的農場不同的是，花海農場另外有一個動物區，餵養牛，馬，羊，豬，免子，還有蜜蜂，數量都不多，但是小朋友有的看有的摸有的餵，一樣很興奮，來一趟農場，幾乎把可以看的動植物都一次看光了。大小通吃，物超所值，適合全家老小共遊。

• 大溪花海農場官方網站，內有詳細地圖及票價。
• 網友遊記：http://winroad.blogspot.com/2006/03/blog-post.html
  

[EXCEL] 公式所在欄位名稱

　
　　相關函數：COLUMN() / CHAR() / CODE()

　　有時候，我們會想要知道公式所在儲存格的欄位名稱或列數名稱，用來做一些判斷或變化，例如... 老實說，我想了半天，想不出比較好的例子，麻煩大家看一下知識+上的網友實際需求的例子吧！或許有一天我們也會用到...

　　如何判斷公式所在列數呢？這個比較簡單，直接呼叫函數 =ROW() 即可得到列數 1,2,3 ...

　　如何判斷公式所在欄位名稱呢？直接呼叫函數 =COLUMN() 也可以，只是它傳回的也是欄位序號 1,2,3, 而不是習慣使用的 A,B,C。如何把 1,2,3 轉成 A,B,C？請參考下列表格:

##CONTINUE##

　　函數 =CODE() 可以把字母轉成 ASCII code，函數 =CHAR() 則 可以把 ASCII code 轉回成字母。利用這樣的特性，我們可以把字母拿來做數學運算。CODE("A") 會固定傳回 ASCII code 65，所以第二列的公式可以分別得到 67,68,69, 轉回字母就是第三列的 C,D,E, 為了要和欄位名稱配合, 所以把 ASCII code 多減一，也就是第四列的公式，就可以得到和欄位名稱相配合的 B,C,D 了。

幼兒國歌

　
　　丫丫的生活圈因為幼稚園而擴大不少，園裡的老師，同學逐漸成為日常生活的話題，前陣子的中秋親子大烤肉，剛過完的萬聖節化粧遊行，生活變得更多采多姿。

　　當然，偶而還會學回來一些老師沒教的事情，就像那天他的要求：「爸爸，我要聽『老鼠愛大米』！」

##CONTINUE##

　　「老鼠愛大米」，嗯，我聽說過，好像是從網路上紅起來的歌，歌名不怎麼文雅，但旋律好像不錯。上網找找，果然找出一大票，連 MP3 檔都有，那當然就老實不客氣啦，也能在女兒面前顯顯我這個有求必應的老爸的威風！

　　丫丫當場乖乖坐好連聽半小時，一聽再聽，欲罷不能，雖然只會唱一句「我愛你，愛著你，就像老鼠愛大米」，卻是怡然自得其樂！我也樂得輕鬆。

　　那天，鄰居小孩來家裡玩，聽到這首歌，馬上跟著哼唱；丫丫的表哥，表姐，堂姐，堂弟，也是個個朗朗上口... 這，這根本就是國小幼兒圈的國歌嘛！！

　　讓人慶幸的是，「老鼠愛大米」真的不錯聽，大人跟著重覆連聽一小時，還不至於受不了，真是老天保佑！阿彌陀佛！阿門！