學不完．教不停．用不盡

在 Excel 中有一個價格資料表(如下圖左)，因為有了新的價目表，其中只有部分的價格做了調整(如下圖右)，如何製作新的價目表呢？其中沒有調價的項目維持原價，而有調價項目以新價格取代。

【準備工作】

選取儲存格G1:H12，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：調價項目和新價格。接著，定義名稱：資料，範圍為儲存格G1:H12。

【輸入公式】

有個有趣的題目是：台灣證劵交易市場規定股票成交價格只能在前一個交易日的收盤價的漲、跌7%範圍內變動。如果某支股票前一個交易日的收盤價是每股100 元，假設該股票次日起連續五個交易日以跌停板收盤(即每日跌7%)，接著連續五個交易日以漲停板收盤(即每日漲7%)。請問經過這十個交易日後，該支股票每股的收盤價是大於、等於或小於100 元?

或許有人以直覺來看，答案應是：等於100元。如果你以試算表來驗算(參考下圖)，以四種不同的漲跌順序，但都是漲7%有五次，而跌7％也有五次，結果都一樣是：「小於100元」。

如果你想以一個公式即求得儲存格C11的結果，公式為何？

{=B1*PRODUCT((MOD(ROW(1:10),2)*2-1)*ROW(1:10)/ROW(1:10)*7%+1)}

這是陣列公式，輸入完成請按 Ctrl+Shift+Enter 鍵。

在 Excel 中的一些資料數列，其中有部分數字是重覆的(如下圖)，如何能快速產生不重覆的數列呢？

這次不要使用公式，要使用進階篩選工具，參考以下步驟：

1. 選取[資料/排序與篩選]中的「進階...」選項。

2. 在[資料範圍]方塊中選取資料，本例為儲存格A1:A18。

有網友問到如果指定一個數 A，要產生± N 的亂數，例如 70±10，要產生 60~80 之間的亂數，該如何處理？(參考下圖)

儲存格C2：=$A2-INT(RAND()*($B2*2+1))+$B2

複製儲存格C2，貼至儲存格C2:F13。

RAND 函數：可以產生 0~0.99999999999 的亂數

INT 函數：可以產生不大於的最大整數

在 Excel 中有一個資料表(如下圖左)，其資料排列是依 A,B,C,D 順序，並各有 6 筆資料，如果要計算 A~D 各組和 1~6 各組的小計(如下圖右)，該如何處理？

【解法】

(1) 計算 A~D 各組的小計

儲存格E2：=SUMPRODUCT(((MOD(ROW($B$2:$B$25)-1,4)=MOD(ROW(E2)-1,4))*$B$2:$B$25))

複製儲存格E2，貼至儲存格E2:E5。

最近又有人問到要計算奇數或偶數列(欄)儲存格的值等問題，只要要計算的儲存格有規律，就可以使用一個公式來完成。你可以分別以陣列公式或 SUMPRODUCT 函數來完成。

參考下圖的數值，試著找出第1列奇數欄的和、第1列偶數欄的和、第1欄奇數列的和、第2欄偶數列的和、所有列奇數欄的和、所有欄偶數列的和、偶數列和偶數欄交會的和等的值。

【運算公式】

以下分別以兩種方式來運算，如果為陣列公式，則輸入完成要按 Ctrl+Shift+Enter 鍵。以下均以 MOD 函數來求某一欄(列)除以 2 的運算結果，來判斷是偶數或奇數。

(1) 計算第1列奇數欄的和

假設在 XXX、YYY、ZZZ 三個工作表中(如下三個圖)，都有 AAA、BBB、CCC、DDD、EEE 五個人的資料，如果想要將每個工作表的小計(位於第8列中)列，整理成一個「小計」的摘要表，該如何處理？其中摘要表要以 XXX、YYY、ZZZ 為欄標題，AAA、BBB、CCC、DDD、EEE 為列標題。

XXX 工作表：

YYY 工作表：

ZZZ 工作表：

在 Excel 中，大家比較熟悉同時檢視兩個活頁簿中的不同工作表，但有時會想要同時檢視「同一活頁簿」中的兩個不同工作表，可以做比較或是方便複製等動作。該如何操作呢？

1. 開啟要檢視的活頁簿，切換至第一個工作表。

2. 按一下[檢視/視窗]中的「開新視窗」按鈕。

3. 按一下[檢視/視窗]中的「並排顯示」按鈕。

4. 選取一種排列方式，例如：水平並排。

來練習陣列公式。有人想要將一群數列中的小數部分取出並計算其總和，該如何處理？你可能會先將每一個數的小數找出來，再求其總和。

儲存格C2：=B2-INT(B2)

複製儲存格C2，往下各列貼上。再求這些小數的總和。

而你如果使用陣列公式，將可以不用這麼多步驟，只要一個公式即可完成：

儲存格E2：{=SUM(B2:B21-INT(B2:B21))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

大家都知道身體質量指數(BMI)的計算公式是「體重(kg)/(身高(M))^2」，這次要設計一個簡單的工具來找合理的體重範圍。

動作是希望輸入身高值和體重值後，能自動顯示一組身高範圍和體重範圍組成的 BMI 值。並且設計以微調按鈕來控制不同體重值，而能自動標示這個身高和體重所在的 BMI 值，並且將合於標準範圍(18.5~24)的部分以淺綠色表示(落在淺紅色區域即為不標準)。(參考下圖)

【表格數值製作】

1. 產生身高數列

儲存格B2：=＄C＄1-5；儲存格C2：=B2+1