學不完．教不停．用不盡

有網友問到想要在 Excel 中製如下圖的萬年月曆，可以使用微調按鈕來調整西元年和月份，即可產生各年各月的月曆表，該如何處理呢？

因為要使用「微調按鈕」，所以你的 Excel 必須先啟動「開發人員」功能表。

選取[開發人員／控制項]中的[插入]選單中的「微調按鈕」。

在工作表中建立一個微調按鈕來調整年的數字，並在[控制項格式]對話框中的[控制]標籤下設定：

有網友問到：在 Excel 中常會用到要將英文字母 A～Z 對應到數字 1～26，該如何設計公式呢？這的確是個常用到的狀況，例如：身份證的第一碼是英文字，常在信用卡繳費時需要做這樣的轉換。(參考下圖)

【公式設計】

以下提供四種方式來討論：

(1) 使用VLOOKUP函數

有網友問到一個 Excel 計算上的問題：如何滿足特定雙條件之四捨五入設計，當條件滿足資料個位數是偶數時，且小數點等於0.500，則無條件捨去至個位數，如果不滿足以上二個條件，則四捨五入到個位數。參考下圖。

(1) 判斷個位數是否為偶數

儲存格C2：=IF(MOD(INT(A2),2)=0,"V","")

INT(A2)：將儲存格A2的小數部分去除。

在 Excel 中的一個資料表，含有類似學號的數字列表，有網友想要將其第一碼置換為「s」，最後一碼去除後，再串接「@upup.edu.tw」，看起來像是一個 Email 位址的字串，該如何處理呢？(參考下圖)

儲存格C2：="s"&MID(A2,2,LEN(A2)-2)&"@upup.edu.tw"

LEN(A2)：計算儲存格A2的內容共有幾個字元。

MID(A2,2,LEN(A2)-2)：使用 MID 函數由第2個字起取全部字元再減2個字。

串接字串使用「&」運算元。

在 Excel 中有一個二欄構成的資料表(如下圖左)，有網友想要將其轉換為下圖右的二種呈現方式，該如何處理？

在 Excel 中其實有提供「轉置」的功能，也就是複製原始資料後，在貼上資料時選擇「轉置」即可。但是手動處理，對於資料筆數多時，形成很大的困擾，所以來試試如何建構公式來自動化處理。

【公式設計】

(1) 二欄直接轉置

在 Excel 中製作圖表是屬於操作方便的，但是如果重覆要製作多個類似的圖表，似乎在人性上一般人較無法接受，有沒有快一點的方法在類以的資料來源中，建立相似的圖表呢？

例如，在下圖中有一個取自於資料表製成的直條圖圖表：

經由多個步驟的操作之後，終於完成了以下的圖表格式：

要如何快速將另一個資料表建構的圖表也設定成和上圖一樣的圖表格式呢？

網友又問到之前時常被問到的類似問題：如何在 Excel 的資料表中將每個人有 12 個成績中挑出最佳幾個來平均？

以下圖為例：每個人有 12 個成績，如果要挑出最佳的前 8 個成績來平均，該如何處理？

【設計公式】

儲存格N1：｛=AVERAGE(LARGE(B2:M2,ROW($1:$8)))｝

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。

在一年的開始，很多人開始要對去年的報表加以統計了。常有人問到這類的例題，在 Excel 中有一個每天的數值記錄產生的報表(參考下圖左)，如何分星期、分月份來統計：天數、總和、平均、最大值、最小值等呢？(參考下圖右)

【準備工作】

選取所有基本資料，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、星期、數值。

在一個 Excel 的資料表中有一些含有小數的數值，網友想要依照自己的規則來處理小數部，例如下圖右的規則：0.1~04 以 0 計；0.5~0.9 以0.5計。要如何設定公式來處理所有的小數呢？

【設計公式】

儲存格C2：=INT(A2)+((A2-INT(A2))>=0.5)*0.5

INT(A2)：透過 INT 函數將小數部分全部去除。

在 Excel 中有一個人員最近二個月的成績等第表，如何利用這個成績等第表來評比是否進步呢？(參考下圖)

其中假設甲、乙、丙、丁分別對照分數 4、3、2、1。

評比要判斷出：進步、持平、退步

【公式設計】

在 Excel 的一個資料表中，每個儲存格的內容是由字串和數字構成(參考下圖)，有網友問到想要取出所有的字串來計算總和，該如何處理呢？

假設我們不使用輔助欄位來練習如何在一個儲存格中使用公式直接計算總和。

(1) 所有儲存格不含空白儲存格

觀察上圖，其中每個儲存格有共同的字串「Windows」。

儲存格A15：=SUMPRODUCT(MID(A2:A12,8,999)*1)

在 Excel 中有時會用到十六進制數，運算時也可能需要先轉換為十進制數，所幸 Excel 提供的 HEX2DEC 函數，可以直接轉換這兩種進制數。

儲存格E2：=HEX2DEC(D2)

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

但是，對於那些沒有 HEX2DEC 函數的 Excel 版本使用者，要如何轉換呢？藉著這個範例來順便練習陣列的應用。

儲存格E2：=｛SUM(N(OFFSET($B$1,MATCH(MID(D2,ROW($1:$4),1),$A$2:$A$17,0),0))

觀察下圖左，有網友想要在 Excel 中具有型式、尺寸、變化等構成的資料表中，再由不同長度來對照查詢到「時間」，就像下圖右這樣。

因為要用以比對的條件比較多個，所以沒有可以直接運用的單一公式。再觀察其資料表中長度部分是間隔 1000 的規則，而不是亂數分佈，還好，這樣就可以設計公式來查表。不過得用到「陣列公式」的做法。

【準備工作】

假設資料範圍是儲存格A2:E72，選取儲存格A2:C72，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：型式、尺寸、變化。定義名稱有助於縮短公式，並且增加公式理解。

有網友提供一個 Excel 工作表裡中一串由多個文數字組成的編號，希望藉由公式能自動產生最末一碼由 0 ~ 9 變化，該何處理呢？

因為儲存格A1中的這個文數字內容被 Excel 視為文字，無法使用「自動填滿方式」來產生和等差數列般的數值連續變化，只好藉由公式來幫忙了！

(1) 最後一碼產生 0 ~ 9

儲存格A3：=LEFT($A$2,LEN($A$2)-1)&ROW(1:1)

LEN($A$2)：使用 LEN 函數計算儲存格A2內容的文字長度(即為字數)。

有人問到一個在學校行政上的問題，不知如何處理。在 Excel 中有一個各班／各節的任課老師資料摘要表(如下圖)：

要轉換成以下格式的摘要表：

這個問題可以參考之前文章來處理：Excel-表格欄、列、資料的重組(MATCH,OFFSET)

本文還是再把做法和公式提一下，參考以下的步驟：

有網友想要在一個 Excel 資料表(如下圖)中，給予二個條件：地點和材數，查出對應的單價，再計算總金額，該如何處理呢？

【設計公式】

在這個問題中使用了二個條件來查詢對應的結果，一般查表函數不外乎使用 INDEX 和 VLOOKUP 等函數。

儲存格D8：

=INDEX(B2:E5,VLOOKUP(C8,{0,1;36,2;71,3;141,4},2,TRUE),MATCH(B8,B1:E1,0))

有網友問到：在 Excel 的一個資料表(如下圖左)，其中含有統編、姓名和金額，如果根據人名來加總，會有不同統編卻名字相同的情形(序號3,11,17)，該如何處理較好？

其實這個資料表中有一個欄位「統編」，其中的項目內容並不會重覆，即可用來做為小計的依據，不用擔心人名重覆問題。

現在來練習依上圖左的內容，製作成上圖右的結果。

1. 複製儲存格B2:B23，貼至儲存格F2。目前儲存格F2:F23為選取狀態。

2. 點選[資料／資料工具]選單中的「移除重複」按鈕。

有時候老師取得一次考試的答案，如何在 Excel 中根據標準答案，快速計算總分？以下圖為例，共有 25 個題目的答案，每一題答對得 4 分。

(1) 直向資料

檢視下圖中的學生答案和標準答案，經比對後可見有 6 題的答案不一致，所以應得分 76 分。設計公式時只要利用 SUMPRODUCT 函數即可求得分數。

儲存格E2：=SUMPRODUCT((A2:A26=B2:B26)*4)

因為 SUMPRODUCT 函數的每個參數都是陣列組成，A2:A26=B2:B26 運算後會傳回 TRUE／FALSE 的陣列，公式中經由「*4」運算後，TRUE／FALSE 陣列會轉換為 1／0 陣列。透過 SUMPRODUCT 函數執行乘積和運算，即為所得。

有網友問到類似加班費計算、停車費計算的問題，例如：以30分鐘為單位，超過5分鐘以30分鐘計，再合計為小時數，該如何處理呢？(參考下圖)

【輸入公式】

儲存格E2：

=HOUR(B2-A2)+INT(MINUTE(B2-A2)/30)/2+0.5*(MOD(MINUTE(B2-A2),30)>=5)

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

如下圖的 Excel 資料表，有網友想要計算以下三種狀況：符合條件的個數、符合條件排除N無資料的個數、符合條件N的總和，這三種計算都只要使用 SUMPRODUCT 函數來執行即可。就用這個例子來學習 SUMPRODUCT 函數吧！

SUMPRODUCT 函數主要是要將多個陣列執行「乘積和」，而公式中如果使用條件判斷(例如：A2:A14=F2)，則會傳回運算結果 TRUE／FALSE 陣列，當在 SUMPRODUCT 函數中以「*」運算時，會將TRUE／FALSE 陣列轉換為 1／0 陣列，所以這個乘積和的運算結果，相當於執行邏輯 AND 運算。

(1)符合條件的個數

儲存格I2：=SUMPRODUCT((A2:A14=F2)*(B2:B14=G2)*(C2:C14=H2))

(2)符合條件排除N無資料的個數