學不完．教不停．用不盡

根據前一篇教學：Excel-根據時間中的分決定是否進位(CHOOSE,INT,HOUR)，如果使用 FLOOR函數和 CEILING 函數，該如何處理？

【公式設計與解析】

儲存格E2：

=CHOOSE(INT(MINUTE(A2)/15)+1,FLOOR(A2,1/24/2),CEILING(A2,1/24/2),

在 Excel 中有一個關於時間的清單（參考下圖左），如何根據以下規則調整（參考下圖右）？

當「分」為：

若為00~14者要捨去

若為15~29要變為30

若為30~44者要變為30

若為45~59者要進位至小時

參考下圖在 Excel 中的資料表，如何計算每個人員的平均效能？

下圖中，上圖部分僅依效能欄位來平均，下圖部分另加入參考時數部分。

【公式設計與解析】

1. 依效能欄位計算平均效能

接續前一篇：Excel-模擬程式語言的廻圈運算(SUMPRODUCT,MOD,ROW)

如果你在 Excel 的工作表中要實現 Select … Case 多選一的程式邏輯，該如何處理？（參考下圖）

【程式】

Dim k, sum As Integer

近來，大家都在討論運算思維！今天上課的學生約略有程式設計的概念，所以用以下的例子來和 Excel 建立關聯。

【範例一】

參考下圖，若要計算1～100的總和，可以善用 Excel 試算的特性，於儲存格B3建立公式：=B2+A3，再複製儲存格B3，貼至儲存格B3:B101。（共100個）

其最後結果，儲存格B101的內容為5050，即為1+2+3+ … + 100的總和。

在 Excel 裡，如何在一個時間清單中，計算各個時段包含多少個？（參考下圖）

【公試設計與解析】

儲存格D2：=SUMPRODUCT((HOUR(時間)=C2)*1)

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

教師常會搜集資料來備課，如果要將網頁上的內容製作成講義，通常會希望內容聚焦在文章主體，而其他元素（例如：網站名稱、廣告和其他外掛等）最好能被過濾掉。（參考下圖）

你可以利用 Chrome 上的擴充程式來處理成為PDF檔。我的方式是利用 Chrome 結合Evernote 的擴充程式，選擇「簡化文章」，再儲存至自己的記事本。

回到電腦中的 Evernote 裡同步相關記事，選取這篇記事，再選取「檔案／列印預覽」選項。（或是選取直接另存為 PDF）

（練習題）最近又有一項大考放榜了！學校取得考試成績後，如何利用 Excel 來稍微分析（例如：計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標等），以利後續應用？

本篇簡單的做了一個範例來練習。（本例數據內容為模擬數值）

【公式設計與解析】

1. 定義名稱

（範例練習）本例要根據國健署提供的成年人健康體重對照表，利用 Excel 建構一個相同的報表。（參考下圖）

下圖是國健署提供的成年人健康體重對照表：

下圖是利用 Excel 建構的資料表：

（教學範例）在文書處理過程中，有很多的動作常會多次重覆，如何能以快速的方式產生想要的結果？除了可以加速作業流程之外，也可以避免因錯誤產生的不一致。

參考以下的做法，要以 F4 鍵和 Ctrl+D 鍵來加快速度。

例如在 Word 文件中，若要在幾個分開的文字上設定為紅色，可以如何處理才會快速？

1. 將「頁首」的字型色彩設定為紅色。

2. 接著選取「頁尾」，按 F4 鍵；再選取「封面」；按 F4 鍵。再選取「文字方塊設計」，按 F4 鍵。

練習：比較 SUMIF 函數和 SUMPRODUCT 函數。

根據下圖，每日會持續輸入多筆資料，如何計算項目的累計和同日的累計？

【公式設計與解析】

1. 計算項目累計

在 Excel 中如何設計公式來執行多欄位的排序？

以下圖為例，欄位：國文、英文、數學、社會、自然在總分相同時的比序順序為：國文→數學→英文→自然→社會，要設計公式以得到這樣的結果。（本例假設有 5 個欄位的資料要比序，且每一個資料為 0~100 之間的數值。）

下圖是利用 RANK 函數在H欄中產生的排序結果：

下圖是以排序欄位由小至大的排序結果。（先觀察其中排序相同者，目前尚未依指定比序來排序。）

在 Excel 中，如何以表單控制項的選項按鈕來設定儲存格的前景色彩和背景色彩？

以下圖為例，表單控制項的選項按鈕直接置於不同的色彩之上，當點選該選項時，即表示要選取對應的色彩。其中有前景色彩和背景色彩可以分開選取，該如何設計？

當選取不同色彩時，表格中的前景和背景色彩立即會對應而變動。

要使用表單控制項必須先啟用「開發人員」工具列。（選取「檔案／選項」中的「自訂功能區」，再勾選「開發人員」。）

今天為電腦網路相關課程的子網路切割做講義，利用試算表來轉換二進制和十進制，很方便就產生了想要的內容。

1. 以 Class C 的 IP 為例（/24），可用位址、網路位址、廣播位址如下：

儲存格E11：=LEFT(REPT("1",MOD(24,24))&"00000000",8)

儲存格E12：=BITAND(BIN2DEC(E11),E9)

（網友提問）在 Excel 中如何產生指定區間的亂數整數？

參考下圖，給予起始值和終止值，要產生該區間的亂數整數，並且包含起始值和終止值。

【公式設計與解析】

因為 RAND 函數可以產生大於或等於 0 且小於 1 的亂數值，所以將亂數乘以某一個數，即可放大亂數值，而如果要產生的是整數，則藉由 INT 函數來去除小數部分。

（網友提問）參考下圖，如何在一個資料表中，指定欄、列數值，在區間範圍中查詢？

例如：欄數值55是在46-65之間，列數值7在6-8之間，欄列交叉對應得到11。

【公式設計與解析】

儲存格H3：=INDEX(B2:E5,MATCH(H2,{1,3,6,9},1),MATCH(H1,{0,21,46,66},1))

（網友提問）在 Excel 中如果要使用公式以起始字串來篩選清單中的項目，該如何處理？

參考下圖，要以起始字串（本例：A135790）在一個號碼的清單中篩選。

【手動篩選】

如果你使用篩選工具，就可以使用「文字篩選」下的「開始於」功能來篩選。

（網友提問）參考下圖，如何在 Excel 中根據分組表將成員名單顯示每位成員所分的隊伍？

下圖中，右邊是分組表，左邊是成員名單，有些成員沒有被分到組別，要將有分到組別者，顯示其組名。

【公式設計與解析】

儲存格C3：=IFERROR(OFFSET($E$2,0,SUMPRODUCT(($E$3:$H$13=A3)*

在 Excel 中有時會用到多表格的查詢（如下圖），你可以使用各種查詢的公式，例如：INDEX、VLOOKUP、OFFSET等，本例以3種不同的做法來練習在多個表格中查詢，也要看看如何簡化公式。前提是多個表格的格式要是一致的。

若要在儲存格H2:J2中使用了3 個條件來搜尋（概念如下圖），可以看成是 3D 的搜尋。

【公式設計與解析】

觀察下圖！圖裡的 Excel 工作表有個資料清單（本例中的儲存格有部分是以亂數產生數值），完全無法辨識儲存格中是否有為含有：公式、註解、設定格式化的條件、常數、資料驗證等。如果想要對這些項目進行操作時，還得先找出各個項目所屬的儲存格才能處理。

還好，Excel 已內建搜尋這些項目的功能，可以用搜尋方式找到：公式、註解、設定格式化的條件、常數、資料驗證等

(1) 搜尋含有「公式」的儲存格（和搜尋含有「常數」的儲存格互為相反）