學不完．教不停．用不盡

在 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) 搜尋含有「公式」的儲存格（和搜尋含有「常數」的儲存格互為相反）

在 Excel 中己提供了將儲存格範圍複製作圖片的功能，但是沒有提供將該圖片儲存成圖片檔的功能，只能以間接的方式存檔了。

以下圖為例，選取一個儲存格範圍（不接受不連續的儲存格範圍），再選取「剪貼簿／複製／複製成圖片」功能。

設定選項：

然後，開啟一個 Word 文件，貼上剛剛複製的圖片。

在 Excel 中以欄名列號當為儲存格名稱，如果想要根據欄的名稱轉換為欄的數值，該如何處理？（參考下圖）

【公式設計與解析】

參考下圖，在 Excel 中是以A～Z、AA~AZ、BA~BZ、...為欄名的順序。欄的數值為A=1、B=2、C=3、...、AA=27欄。

（網友提問）在 Excel 中常會用到要執行查詢的工作，或許你會用 VLOOKUP 函數或是 INDEX 函數，以欄、列交會之處查詢。但是如果要列出相同者的清單，就無法直接使用這兩個函數。這次來試試 OFFSET 函數。

【公式設計與解析】

本題已假設同機種的物料是連續排列。

儲存格E2：=IF(ROW(1:1)<=COUNTIF($A$2:$A$24,$D$2),OFFSET($B$1,

許多網友對於 Excel 中的 VLOOKUP 函數或許不陌生，但如果要在公式中使用雙條件或多條件，可能就會產生一些困擾，甚至不知如何下手。

因為 VLOOKUP 函數是藉由將要查詢的內容和資料陣列的第一欄比對，查到第幾列時，再由公式中指定的欄數，以欄列交會查出對應的儲存格內容。在此，若要使用雙條件，的確很不方便。該如何解決？

【公式設計與解析】

我的部落格上有許多應用雙條件的查詢公式可參考：

網友問到關於 Excel 中 WEEKDAY 函數的使用：

在公式中使用 WEEKDAY 函數，可以依傳回的數值來判斷為星期幾。

WEEKDAY的參數與傳回值：

1：適用星期日為每週第一天者（傳回值以 1 為起始）

2：適用星期六為每週第一天者（傳回值以 2 為起始）

3：適用星期一為每週第一天者（傳回值以 0 為起始）