學不完．教不停．用不盡

在使用 Excel 時，加總運算是最平常的公式，所以 Excel 提供了加總按鈕，可以快速完成。通常你要執行加總運算，可以在儲存格中輸入：=SUM(number1, [number2], …)，其中要加入運算的儲存格可以使用選取儲存格方式代入。

如果你選取了如下的表格範圍(連同要顯示加總的儲存格也一併選取)。

或是只選取要顯示小計的儲存格。

上一篇：Excel-產生連續的數列來運算(http://isvincent.blogspot.com/2010/11/excel_18.html)提到連續數列的產生，這次拿它進一步做一些運算。

例如：傳統上要計算1-1/2+1/3-1/4+1/5-1/6 …，根據下圖右的運算，

儲存格B3：=-1*((MOD(ROW(A3),2))*2-1)*(1/A3)

儲存格C3：=SUM($I$2:I3)，複製儲存格B3:C3，並往下貼上。可求得累加的結果。

如果運用連續的數列來運算，則可以這樣做：

儲存格F2：

在 Excel 中的基本單位是儲存格，以程式的角度來看，每個儲存格相當於一個記憶體，而每個記憶體可以當成一個變數使用，也可以將儲存格中的內容當為常數使用。所以當你要計算1+2+3+ … +100的運算時，可以在100個儲存格中產1~100的數字，再將這些儲存格作SUM運算，很容易就可以得到答案。

你可以有更簡單的做法。配合陣列運算和ROW、INDIRECT函數，可以產生連續的數列。

例如：

存格陣列公式：{ROW($A$1:INDIRECT("A"&A2))}

若儲存格A2的內容為：10，則

ROW($A$1:INDIRECT("A"&A2))

在 Excel 計算成績的加權平均是一件簡單的事，即每個「科目X學分」的總和，再除以「學分的總和」(例如下圖)。公式如何設定？

(一)學分數以數字表示(如下圖上半部)

儲存格G3：{=SUM(B3:F3*B2:F2)/SUM(B2:F2)}

此為陣列公式，輸入完成要按 Ctrl+Alt+Enter。

如果不使用陣列公式，則改為：

儲存格G3：=SUMPRODUCT(B3:F3,B2:F2)/SUM(B2:F2)

在 Excel 的工作表中如果想要產生每月是同一天的日期數列，如何操作較為方便呢？

方法一：

1. 在儲存格B1中輸入第一個日期，例如：2010/01/05。

2. 在儲存格B2中輸入第一個日期，例如：2010/02/05。

3. 選取儲存格B1:B2。

4. 向下拖曳填滿控制點至想要的終止儲存格(拖曳過程中即可預覽產生的數列)。

如果你在 Excel 中取得一個成績的資料表，而想要計算某百分比內的平均分數，該如何處理？

輸入公式：

儲存格F2：="前"&ROW(1:1)*10&"%"

ROW(1:1)=1，ROW(2:2)=2，…，ROW(9:9)=9。

儲存格G2：=AVERAGEIF($D$2:$D$34,">="&PERCENTILE($D$2:$D$34,1-ROW(1:1)/10))

利用 PERCENTILE 函數從一個範圍裡，找出位於其中第 k 個百分位數的值。

在 Excel 取得如下的基本資料，如果想要對年級和名次的細項分類來排序，該如何做呢？

(一) 分年級排序

儲存格E3：{=IF($A3=E$2,SUM(($A$3:$A$25=E$2)*($D$3:$D$25>$D3))+1,"")}

這是陣列公式，輸入完成必須按 Ctrl+Shift+Enter。將儲存格E3複製到儲存格E3:G25。

在此無法直接以 RANK 函數來找名次，而是將A欄中合於第2列的年級名稱者，挑出來運算。

公式中的SUM(($A$3:$A$25=E$2)*($D$3:$D$25>$D3))+1，原理為：

如果你在 Excel 的工作表中取得如下的資料表，想要將各校、各年級的數量統計出來，該如何做？

這次來練習使用樞紐分析表工具：

1. 選取資料表中的任一儲存格。

2. 選取[插入/樞紐分析表]中的[樞紐分析表]按鈕。

在 Excel 的工作表中建立以下的表格，想要設計成表格有底色，但是輸入資料的儲存格會取消底色(本例為白色)，該如何處理？藉由格式化條件設定就很容易達成。

(1)選取儲存格(本例為儲存格B2:C17)，設定字型色彩。(注意：預設的儲存格底色為白色)

(2)設定格式化的條件如下圖：(其中設定當儲存格為空白或是空字串時，顯示一種底色。)

如果你在 Excel 中取得一個資料表(如下圖左)，想要將資料垂直和水平轉置(如下圖右)，除了使用 Excel 中的複製功能，在貼上時選取[轉置]選項來完成之外，可否以公式來完成呢？

其實只要一個公式就可以完成：

儲存格F1：=OFFSET($A$1,COLUMN()-6,ROW()-1)

將儲存格F1向右和向下複製即可。

其中公式：=OFFSET($A$1,COLUMN()-n,ROW()-m)，m為轉置後的第一個位置(例如：儲存格F1)為第幾欄，n為轉置後的第一個位置(例如：儲存格F1)為第幾列。