學不完．教不停．用不盡

​在 Excel 中有許多工作表，通常會有相同的架構，如果想要取用相同工作表的同一儲存格時，該如何下公式？

如果你輸入的公式是：

儲存格D1：=工作表1!A2

則當你複製儲存格D1，往下各列貼上時會產生：

儲存格D2：=工作表1!A3

儲存格D3：=工作表1!A4

在 Excel 的工作表中有些時候我們可能會隱藏某些欄(列)，如果我們複製的儲存格區域包含了隱藏的儲存格(如下圖中的B欄被隱藏了)：

在貼上時會將被隱藏的資料也一併複製貼上了：

如果想要在貼上時不要包含被隱藏的資料該如何處理呢？其實只要多一個動作即可。

1. 先選取要複製的儲存格區域(其中包含了被隱藏的儲存格(B欄))。

在網路上發現有人用公式來處理資料部析的方式不錯，很有創意，可以來介紹一下。參考下圖，在A欄中有一些字串，其中的文字以「/」做為分隔，現在希望將每段分隔的文字，分別放在右側的儲存格中，該如何處理？

假設字串中不含「*」字元。

儲存格B2：=SUBSTITUTE(MID(SUBSTITUTE($A2,"/",REPT("*",30)),30*(COLUMN(A2)-1)+1,30),"*","")

複製儲存格B2，貼至儲存格B2:D5。

公式中的「30」是假設在A欄中的字串不會超過30個字元。

當你在 Excel 的工作表中輸一個網址或 Email 位址，在按下 Enter 鍵時，其預設會自動建立超連結。可是，有時你並不想要這個超連結，要如何不要這個超連結呢？方法很簡單：你只要在輸入好網址後，隨即按一下 Ctrl+Z 鍵，可以馬上移除超連結。

在 Word 中相同方式也可行。

在 Word 中如果你想將段落依每段的第一個字來排序，可以使用［常用/段落］中的「排序」功能。

1. 選取要排序的段落。

2. 按一下［常用/段落］中的「排序」按鈕。

3. 選取要排序的類型，例如「筆劃、數字、日期、注意」等，並選取「遞增/遞減」。

4. 按一下[確定]按鈕。

在 Excel 的函數提供了 NOT、AND、OR 等函數，但是沒有提供 XOR 運算(互斥或運算)的相關函數，該如何執行這個邏輯函數的運算呢？

XOR 邏輯函數的基本概念是輸入 X, Y ，兩者相同時，輸出為 False；兩者不同時，輸出為 True。我們要利用 NOT、AND、OR 三個函數來執行 XOR 運算。

XOR(X,Y) = OR(AND(X, NOT Y), AND(Y, NOT X))

【不處理空白】

在 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

在 Excel 中自動篩選是個不錯的工具，不用寫程式即可以篩選出想要的內容。原始資料如下：

例如：在 AAA 欄位中篩選大於 50 的資料：

如果要將篩選後的內容再加以運算，例如要做加總(SUM)：

儲存格B17：=SUM(B4:B15)

【網友問題】

有網友問到：=COUNTIF('0'!F5,B2)+COUNTIF('1'!F5,B2)+COUNTIF('2'!F5,B2)+.......

請問像這樣各工作表的加總有沒有比較簡便的公式，因為目前有一百頁左右的工作表需要加總，麻煩您了，我嘗試過用"*"但是沒有用，算不出答案。

【題目假設】

我把題目假設為如下的工作表(參考下圖)共有多個工作表(名稱1~6)，每個工作表的B欄都有10個數字。如果要根據儲存格E1的條件，要把這 6 個工作表中符合條件的數字找出來並加總。

一年又將結束，又有好多單位送來新的年曆，有時候想要製作自己想要的形式，或許利用 Excel 就可以如願。例如：在一張A4大小的紙張上，置放有兩個月的月曆(如下圖)。

【題目需求】

1. 要能產生萬年曆。

2. 一次產生兩個月。

3. 星期六、日要以不同色彩標示。

下圖左是在 Excel 中常見的一種資料表，想要執行各種運算(如下圖右)，而且全部以 SUMPRODUCT 函數來練習。

【準備工作】

選取儲存格A1:D26，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項，建立「類別、售價、打折、數量」四個名稱。

【建立公式】

(1) 所有售出的原價總和