學不完．教不停．用不盡

這次要練習的查表動作，需要較多的公式，感覺會較難，所以要有耐心和毅力才能有助於學會。

(一)

在 Excel 中有一個全校班級幹部的摘要表(如下圖)，如何自動轉換至學生名單中註記幹部名稱(如下下圖)？

儲存格F2：

最近被問到：如果取得一個一年級選組後的報表，如何針對各類組的男、女生人數和各班的選組人數，製作一個摘要表？(參考下圖)

這兩個工作只要交給SUMPRODUCT函數即可解決：

儲存格I2：=SUMPRODUCT(--($E$2:$E$484=1),--($F$2:$F$484=$H2))

--($E$2:$E$484=1)：判斷E欄中是否為「1」(男生)的 True/False 陣列，其中「--」乃是將 True/False 陣列轉換成 1/0 的陣列。

--($F$2:$F$484=$H2)：判斷F欄中是否為「1」(第1類組)的 True/False 陣列，其中「--」乃是將 True/False 陣列轉換成 1/0 的陣列。

SUMPRODUCT函數會將這兩個陣列相乘，再將這些 1/0 的結果加總。(其實是兩者條件皆成立時，相乘結果才會為1，也才會被加總。)

在 Excel 中取得一個學校社團的基本資料表(如下圖)，本例要根據學生選社的結果(如下下圖)，來產生學生選社的結果報表，並進一步分析。

首先將社團基本資料表定義一個名稱：社團。

根據以上的兩個資料表，現在要來產生如下圖的選社結果。本次以VLOOKUP函數來做為查表的工具。

VLOOKUP：http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx (參考微軟網站的說明)

在 Excel 中常會使用INT和TRUNC兩個函數來轉換數值為整數。而TRUNC 函數與 INT 函數非常相似，兩者都可以將帶小數的數值(實數)轉換成整數，但TRUNC 函數可以指定某個位數以下的部分全部移除，而 INT 函數則是找尋最接近但不大於原數值的整數。

這兩個函數的此二函數唯一的差別是在處理負數時，「可能」產生差異。而正數部分，結果會完全相同。(參考下圖)

觀察以下三種變化：

儲存格B2：=TRUNC(A2,0)和儲存格C2：=INT(A2)

儲存格D2：=TRUNC(A2,1)和儲存格E2：=INT(A2*10)/10

儲存格F2：=TRUNC(A2,2)和儲存格G2：=INT(A2*100)/100

在 Excel 中取得一個成績資料表，現在要根據加權平均成績，來以「無條件進位」修正成績，及如果成績達58和60之間者均以60計，該如何處理？(參考下圖)

(1) 計算加權平均成績

儲存格G3：=SUMPRODUCT(B3:F3,$B$1:$F$1)/SUM($B$1:$F$1)

加權平均=(國文X4+英文X4+數學X4+社會X3+自然X3)/(4+4+4+3+3)

(2) 以無條件進位來修正成績

儲存格H3：=ROUNDUP(G3,0)

在 Excel 中，如果想要產生多個範圍的亂數，例如：產生1~40, 101~140, 200~240的亂數，該如何處理？

因為這是三組範圍為0~40的亂數，試試以下公式：

(1) 儲存格B2：{=LARGE(ROW($1:$40)+{0,100,200},INT(RAND()*120+1))}

或

(2) 儲存格B2：{=SMALL(ROW($1:$40)+{0,100,200},INT(RAND()*120+1))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。將儲存格B2複製到儲存格B2:F4。

在 Excel 中的 REPLACE 和 SUBSTITUTE 函數都是用來取代字串中的某些特定文字之用，其用法有那些差異呢？(參考下圖)

REPLACE 函數主要是根據指定的字元起始位置，指定被取代的字元數，然後以新的字串來取代。

(1) 儲存格E2：=REPLACE(A2,5,7,"_^_")

在儲存格A2中的字串中，由第5個字元開始，一共7個字元，以「_^_」取代。

(2) 儲存格E3：=REPLACE(A3,7,4,"999")

(3) 儲存格E4：=REPLACE(A4,11,5,"Word")

在 Excel 中，Find 函數和 Search 函數都是用來找尋某一字串在另一字串中的位置，並且可以指定開始尋找的位置。參考以下範例：

其中 FIND 函數會區分大小寫，且不支援使用萬用字元。並會將每個單一位元組字元及雙位元組字元都計算為 1。如果想要將雙位元組字元都計算為 2，則可以使用FINDB函數。

Search函數中的尋找字元可以使用萬用字元 ，例如：問號 (?)、及星號 (*)。問號代表任何單一字元；星號代表任何字元序列。如果想要尋找「?」或「*」，則在該字元前輸入波狀符號「~」。

【延伸閱讀】

* Excel-產生Google查詢英文單字的中文翻譯和英文發音的超連結

在取得的網頁連線的IP位址資料中，雖然有日期和時間的欄位，但是其格式並非 Excel 接受的日期時間格式(下圖左)，該如何轉換成正確的格式呢(下圖右)？

儲存格E2：=DATEVALUE(LEFT(A2,LEN(A2)-3))

因為日期資料(A欄)的最後三個字是星期幾，所以透過LEFT函數將其濾除。再使用DATEVALUE將日期的文字轉換成正確的日期格式。

儲存格F2：=IF(LEFT(B2,2)="下午",TIMEVALUE(RIGHT(B2,LEN(B2)-3))+
TIMEVALUE("12:00:00"),TIMEVALUE(RIGHT(B2,LEN(B2)-3)))

有網友問到，隨著縣市合併、升格，如果要在 Excel 的工作表中將地址中的原來縣裡面的「鄉、鎮、市」要改為「區」，而「縣」要改為「市」，要如何處理？(參考下圖)

其實只要使用一個函數即可完成：SUBSTITUTE。

儲存格B2：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"市","區"),"鎮","區"),"鄉","區"),"縣","市")

複製儲存格B2，往下貼上即可。

其公式是由最內層開始運算：

(1) SUBSTITUTE(A2,"市","區")：將地址中的「市」改為「區」。(修改後的結果暫時稱為NN)

有人問到：在 Excel 的工作表中，如果要根據考試分數發獎學金，該如何設計呢？(參考下圖)

最簡單的方式是利用Lookup函數。

首先，要建立分數和獎金的對照表(儲存格J2:K8)。

儲存格H2：=LOOKUP(G2,$J$2:$J$8,$K$2:$K$8)

複製儲存格H2，在H欄中各個儲存格貼上。

或許你也可以試試陣列形式的公式：

在 Excel 的工作表中，如果將一個取自其他文件的日期(格式如下圖，其為民國年的日期表示)，要貼至 Excel 使用，該如何調整西洋年和民國年日期？

複製這些日期貼至工作表中的A欄，很明顯的，顯示的日期暨不是正確的西洋年也不是民國年的日期。所以要來改變一下。

儲存格B2：=DATE(YEAR(A2)-1900+1911,MONTH(A2),DAY(A2))

由A欄資料可看出，在貼上資料時，Excel將年份自動加上1900了，所以必須將年份-1900+1911，即相差了11年，才是正確的西洋年日期。

在 Excel 中如果要在多列中產生序號(1,2,3,…)，可以透過ROW函數。

(1) =ROW(A1)

(2) =ROW(1:1)

(3) =ROW()

不管那一列中輸入(1)和(2)這二個公式，當往下複製時，都會自動產生1,2,3, … 的數列。如果改變參數A1為B1,C1,…，或是改變參數1:1為2:2,3:3,…，都可以改變數列的起始數值。而ROW()則以儲存格的位址當為參數，若位於第6列，則會傳回6。

參考下圖，如果你將某一列(例如：第10列)刪除，其數列結果不會受到影響。

在 Excel 中有一個工讀費的資料表，如何計算工讀費所需鈔票及硬幣？這次要運用INT和MOD函數來運算。

儲存格C3：=INT(B3/C$2)

INT函數可以求取指定數字的不大於之最大整數，所以INT(B3/1000)，相當於求取被1000除盡之整數。

儲存格D3：=INT(MOD($B3,C$2)/D$2)

在求100元鈔時，MOD($B3,1000)會計算薪資除以1000所得的餘數，即求得千元鈔以外所需的錢數。再透過INT函數求得所需的100元鈔數量。

複製儲存格D3至儲存格D3:G3，再複製儲存格C3:G3，貼至儲存格C3:G28。

在 Excel 中，如果在一個儲存格中填入一個公式，在複製公式時，Excel 會自動依欄或列的變化，來變換公式中的位址。以實例來練習，將下圖中的左邊的表格轉換成右邊的表格：

撰寫公式時，要先考量好複製公式時所產生的位址變化，例如：

儲存格G3：=IF($B2=G$1,$C2,"")

因為要複製儲存格，所以第一列和B欄和C欄的位址不能改變，因此加上「$」。(使用F4鍵，可以切換四種位址變化，例如：B2、$B$2、$B2、B$2)

複製儲存格G3至儲存格G3:I3，再複製儲存格G3:I3至儲存格G3:I28，便可完成表格的轉換。

善用相對位址和絶對位址的變化，可以一個公式，即可完成一個表格。

在 Excel 提供了排序的函數，例如：RANK，這次來練習不一樣的排序方式。

先定義各種資料名稱：姓名、分數、輔助分別為A欄、B欄、G欄有資料的部分。

(1) RANK排序

儲存格C3：=RANK(B3,分數)

(2) COUNTIF排序

在 Excel 中取得一些姓名的資料，如果想重組這些姓名，例如將姓和名任意組合成新名字，該如何處理呢？

首先，要建立二個輔助欄位，產生一些亂數值：

儲存格B2：=RAND()

儲存格C2：=RAND()

複製儲存格B2:C2到儲存格B2:C27，產生的這些亂數值幾乎不會重覆。

透過這些亂數的排序結果，可以將A欄的姓名打散。

網友問到：「計算年齡時，如果是42Y5M6D則轉換為42Y5M，如果D>=15時，例如42Y5M15D則轉換為42Y6M，不知如何使用excel做這件事情?」

請先參考「Excel-計算實際年齡(年月日)-DATEDIF」的做法。

網址：http://isvincent.blogspot.com/2011/05/excel-datedif.html

儲存格B3：=DATEDIF(A3,TODAY(),"Y")

儲存格C3：=IF(DATEDIF(A3,TODAY(),"MD")>=15,DATEDIF(A3,TODAY(),"YM")+1,DATEDIF(A3,TODAY(),"YM"))

DATEDIF(A3,TODAY(),"MD")>=15：判斷年齡中的「日數」，如果大於15，則月數加1，否則保持原來的月數。

在 Excel 中取得一個部門、產品、業績的資料表，來試著練習資料庫函數中的DSUM函數。

先定義名稱，資料：儲存格A1:D28，再定義姓名、部門、

(1) 計算部門：業務二科的業績小計

使用SUMPRODUCT函數，儲存格G2：=SUMPRODUCT(--(部門=F2),業績)

使用陣列公式，儲存格G2：{=SUM(IF(部門=F2,業績,FALSE))}

輸入完成，請按 Ctrl+Shift+Enter 鍵。

在 Excel 中指定一個日期，要來找出最近的一個星期日，該如何處理？試試WEEKDAY函數。

儲存格C2：=A2+7-WEEKDAY(A2,2)

WEEKDAY(A2,2)：根據儲存格A2，參數2，表示星期一傳回1、星期二傳回2、…、星期日傳回7。

7-WEEKDAY(A2,2)：得到和下個星期日的差距天數。

A2+7-WEEKDAY(A2,2)：將指定日期加上差距天數。

複製儲存格C2，往下各個儲存格貼上。