學不完．教不停．用不盡

在 Excel 中可以有多種方式產生數列清單(1,2,3,…)，例如以下三種以公式產生數列的方式：

第1種：利用將前一格儲存格值加1的方式，複製儲存格A2，往下貼上。

第2種：利用ROW()函數在複製公式時會自動加1的特性，複製儲存格B1，往下貼上。

第3種：計算前面儲存格有幾個數字，再加1，即為本儲存格的數值，複製儲存格C2，往下貼上。

如果我們做了刪除的動作，例如：將第5列刪除，則會發生以下的錯誤狀況(參考下圖)：只有第3種維持數列的變化。

台北市公眾區免費無線上網，現在已級開放申請，無論你是否為台北市人，只要你在台北市內而且在其服務範圍內者，都可以申請。服務範圍以台北市主要公共場所為主，其中室內公共場所部分，包括市府市政大樓、12個區行政中心、市立圖書館及各分館、市立聯合醫院各院區、臺北捷運車站及捷運地下街等場所；室外公共場所部分，則包括本市主要幹道、主要住商區域及人口密集區之公共場所，將透過「熱點(Hotspot)」方式提供服務。

申請網址：http://www.tpe-free.taipei.gov.tw/newaccountstep1.php

申請時，需要先輸入行動電話號碼，便於以手機收取認證碼：

輸入手機簡訊中的認證碼：

在 Excel 2010 中有進制轉換的函數：BIN2DEC、BIN2HEX、BIN2OCT、DEC2BIN、DEC2HEX、DEC2OCT等，非常方便好用。這次來自行以公式模擬將二進制、四進制、八進制轉成十進制，來練習SUMPRODUCT、INDIRECT等函數。

儲存格B2：=SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),2^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))))

LEN(A2)：取得儲存格A2中字串長度。(本例以字串長度6來練習)

INDIRECT("1:"&LEN(A2))：依儲存格A2中字串長度，產生一段列的範圍。

ROW(INDIRECT("1:"&LEN(A2))：例如儲存格A2中字串長度為6，則產生ROW(1:6)。

MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)：相當於MID(A2,1,1)、MID(A2,2,1)、…、MID(A2,6,1)。即可以將儲存格A2中的每一個字取出，變成一個6個元素的陣列。

在 Excel 中有提供自訂清單功能，如果想要以公式產生想要的清單，該如何處理呢？

儲存格A2：=CHOOSE(MOD(ROW(1:1)-1,7)+1,"星期日","星期一","星期二","星期三","星期四","星期五","星期六 ")

MOD(ROW(1:1)-1,7)+1：根據不同列得到1,2,3…數字，透過MOD取得和變化量(本例為7)相除的餘數，即可產生1,2,3,4,5,6,7,1,2,3,4,5,6,7,…。

再透過CHOOSE乙數對照得到對應的一個字串。

同理：

儲存格B2：=CHOOSE(MOD(ROW(1:1)-1,10)+1,"甲","乙","丙","丁","戊"," 己","庚","辛","壬","癸")

在 Excel 中取得一個物品進出料的記錄表，根據這個資料表來練習各種條件式加總的練習。

首先將A欄到E欄中有資料的儲存格，分別命名為：日期、經手人、進出、料號、數量。並將全部資料(儲存格A1:E27)命名為：資料。

(1) 計算經手人的經手次數

儲存格H2：=COUNTIF(經手人,G2)

複製儲存格H2，往下二列貼上。

(2) 計算進料/出料的小計

在 Excel 中取得一個通話的起迄時間表，而通話的前2分鐘免費，2分鐘至4分鐘為每12秒1元(不足12秒以12秒計)，超過4分鐘的部分每6秒鐘1元(不足6秒以6秒計)。該如何計算全部的通話費用？

先算出免費的秒數、減價時段的秒數和正常計費的秒數，再運算無條件進位的函數來運算。

儲存格G3：=ROUNDUP(E3/12,0)+ROUNDUP(F3/6,0)

複製儲存格G3，往下各列貼上。

在 Excel 中根據一個班級基本表，在一個申請夜讀的報表中，自動查詢得到班級基本資料(如下圖)。試著使用INDEX、LOOKUP、VLOOKUP、OFFSET、MATCH、INDIRECT等函數來練習查表。

先定義一些名稱：

班級：儲存格A2:A29；導師：儲存格B2:B29；人數：儲存格C2:C29，資料：儲存格A1:C29。

以下各式都可以得到正確的結果，將儲存格F2和儲存格G2複製後，往下各列貼上。

(1) 使用LOOKUP函數

在 Excel 中可以利用LEFT、MID、RIGHT等函數，將一個字串中的文字加以重新排列組合(如下圖)。你可以取用這些文字在其他地方來使用。

例如：

儲存格A2：=MID($A$1,ROW(1:1),1)

儲存格B2：=MID($A$1,ROW(1:1),ROW(1:1))

儲存格C2：=LEFT($A$1,ROW(1:1))

儲存格D2：=RIGHT($A$1,ROW(1:1))

在 Excel 中的活頁簿中，可能包含多個工作表，或許每個工作表有其特定的功能。如果能透過色彩來加以分組，或是以色彩來區隔其重要性等，將會在使用上帶來一些便利。

以 Excel 2010為例，如果要設定工作表索引標籤的色彩，參考以下步驟：

1. 在工作表名稱上按一下右鍵。

2. 在[索引標籤色彩]中挑選一個色彩。

在 Excel 中，如果要計算兩個時間(時：分：秒)的間隔(如下圖)，該如何處理？

其實只要將兩個時間(時間Y和時間X)相減即可(參考D欄)，例如：儲存格D2=C2-B2。你會發現當時間Y大於時間X時，這個公式會產生錯誤訊息。解決的方式很簡單：

儲存格E2：=IF(C2>B2,C2-B2,C2+1-B2)

因為以24小時來看，後者的時間小於前者時，應該是已經進入下一天的時間了，而Excel將1天切割成24小時，所以每小時為1/24；每小時再切割成60分，所以每分為1/24/60；每分再切割成60秒，所以每秒為1/24/60/60。

所以只要將後者的時間小於前者的部分，先加1再相減即可。

在 Excel 中取得某個月份的圖書借閱資料(如下圖，資料為虛擬)，根據這些資料來做一些統計與分析的練習。

先選取所有資料範圍，按一下 Ctrl+Shift+F3 鍵，選取「勾選頂端列」選項。定義「日期、登錄號、書名、班級、借書證號」等名稱。並且定義全部資料的儲存格範圍的名稱為「資料」。

(1) 計算各班及各天借閱次數

各班借閱次數

儲存格H2：=SUMPRODUCT(--(班級=G2))，複製儲存格H2，貼在儲存格H2:H16。

在 Excel 中有一個物品維修的記錄表(如下圖)，由於報表跨越數年，如何指定只列出某年、某月的記錄呢？

首先，要先定義一些「名稱」：

(1) 選取所有有資料的儲存格。

(2) 按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，按一下[確定]按鈕。

可得「編號、班級、項目、報修日、完成日、損壞原因」等名稱。

(3) 選取所有有資料的儲存格，在[名稱管理員]中新增名稱：「資料」。

一個老師將學生分成多組輪流上台報告，每個學生都要為各組給一個分數，而自己所在的組別不評分，每組成員的分數是相同的，都是全班其他組所給分數的平均。如何來設計這個表格呢？(參考下圖)

儲存格J2 ：=AVERAGE(OFFSET($D$2,,$C2-1,COUNT($C$2:$C$25),))

複製儲存格J2，往下貼在儲存格J2:J25。

COUNT($C$2:$C$25)：計算所有人員共有幾列。

OFFSET($D$2,,$C2-1,COUNT($C$2:$C$25),)：取得各組的位址，例如第1組為儲存格D2:D25，第2組為E2:E25，…。

再使用AVERAGE函數計算平均。

在 Excel 中取得一個物品領用的數量統計表，現在要利用這個資料表以公式運算方式列出(1)整年領取數為0者，(2)每個月都有被領取者。

因為需要用到一個「輔助」欄位，所以先輸入公式：

儲存格Q2：=COUNTIF(D2:O2,"<>0")，複製儲存格Q2，往下各列貼上。

將A欄有資料的部分定義名稱為「編號」；將B欄有資料的部分定義名稱為「請領物品」；將P欄有資料的部分定義名稱為「小計」；將Q欄有資料的部分定義名稱為「輔助」。

(1) 整年領取數為0者

在 Excel 中取得一串資料(如下圖A欄)，因為每筆資料中包含了「"」字元，所以當你使用 Excel 的排序工具時，將會變成2" → 21" → 3" → ‥‥，而非2" → 3" → ‥‥。如何解決這樣的問題呢？

這是因為 Excel 將 2" 視為文字而非數字來排序所造成的問題。

先將儲存格A2:A21定義名稱為「資料」

儲存格C2：{=SMALL(VALUE(SUBSTITUTE(資料,"""","")),ROW(1:1))&""""}

這是陣列公式，輸入完成時要按 Ctrl+Shift6+Enter 鍵。複製儲存格C2，貼至儲存格C2:C21。

SUBSTITUTE(資料,"""","")：將資料中的「"」字元消除。

在 Excel 如果要製作一個類似教師甄選的成績計算(如下圖)，該如何設計？

因為不同項目有不同的比重，而且比重的總和為100%，設計如下：

儲存格K4：=B4*$B$2+AVERAGE(C4:G4)*$C$2+AVERAGE(H4:J4)*$H$2

儲存格L4：=RANK(K4,$K$4:$K$24)

複製儲存格K4:L4，往下各列貼上。

在 Excel 中的「資料驗證」功能，可以幫助使用者在輸入資料時可以只接受符合準則的輸入內容。Excel 提供了整數、實數、清單、日期、時間、文字長度和自訂公式等幾種驗證項目。

其實你只要將平時使用在儲存格中的公式，稍加變化即可套用在儲存格的資料驗證上，會有很多的妙用哦！

以下例舉5種應用：

(1) 不接受未來日期

日期設定小於或等於今天的日期(=TODAY())

在 Excel 中有一串數字(如下圖左)，如何以公式篩選出不同位數的數字？操作前先執行名稱的定義：

編號：儲存格A2:26；數字：儲存格B2:B26。

儲存格D2：

{=IFERROR(INDEX(資料,LARGE(IF(LEN(數字)=COLUMN(A:A),編號,FALSE),ROW(1:1))+1,2),"")}

複製儲存格D2，貼至儲存格D2:G21。

原理：

在 Excel 中，針對各班班級幹部的資料表(如下圖)，來練習INDEX、MATCH、OFFSET函數。

如果要產生下圖的查詢結果，該如何處理？其中班級名稱和幹部的名稱要用下拉式清單來挑選，而姓名則是以公式來查詢。

(1) 定義以下三個名稱

班級：儲存格A2:A29；幹部：儲存格B1:H1；資料：儲存格A1:H29。

在 Excel 中取得學生量測身高和體重(參考下圖)(虛擬數據)，如何計算學生的BMI值和判定體位，如果要告訴學生適當的體重該如何計算？

(1) 因為BMI值為「體重(KG)÷身高(M)的平方」，所以：

儲存格G2：=F2/((E2/100)^2)

(2) 根據下圖右側的BMI範圍對照體位表：

儲存格H2：=LOOKUP(G2,$K$2:$L$7,$N$2:$N$7)

(3) 根據體重來求出適當的體重