學不完．教不停．用不盡

網友問到的 Excel 問題：如何在一個時間的資料清單中如何計算合於某個範圍內的數量？（如下圖）

【公式設計與解析】

選取儲存格A1:A25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：資料。

1. 計算 13:15~16:15 的數量

在 Excel 中有一個資料表（如下圖），如果想要讓資料的標題欄能依每天不同星期幾而顯示不同色彩，該如何處理？

選取儲存格A2:A17，設定格式化的條件：

規則公式：=WEEKDAY(TODAY(),2)=1；格式：儲存格底色為星期一的色彩。

其中 WEEKDAY 函數的參數『2』乃指定傳回「1～7」對應「星期一～星期日」。

網友問到 Excel 的問題：公司上班時間為08:00-17:00，下班超過30分鐘開始算加班（如下圖），加班前二小時算加班1，超過二個小時算加班2，如何標記假日加班，該如何處理？

【公式設計與解析】

1. 加班1

儲存格E2：=IF((D2<>"")*(D2>=TIME(17,30,0)),IF(D2-TIME(17,30,0)<

在 Excel 的工作表中（如下圖），如果想要根據日期清單列出不重覆的日期，該如何處理？

手動方式可以參考以下二篇文章：

Excel-手動產生不重覆項目(樞紐分析表)

Excel-使用篩選工具列出不重覆的資料

如果你想要使用公式，則參考以下的做法。

網友問到 Excel 的問題：在工作表中的一個日期和金額清單，網友想要將合於某個日期區間中的金額予以加總，該如何處理？

如下圖，以「01月03日」為例，觀察起日和迄日的日期區間，有 7 個區間中含有「01月03日」，要將這些的金額予以加總。

【公式設計與解析】

選取儲存格A1:C21，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：起日、迄日、金額。

網友問到：在 Excel 中，如果想要將數字的小數點後第3位，調整為不大於該位數的最大偶數，該如何處理？

（參考下圖）例：7.46545→7.464，7.55870→7.558

【公式設計與解析】

儲存格C2：=INT(A2*1000)/1000-(MOD(INT(A2*1000),2)=1)*0.001

(1) INT(A2*1000)/1000

網友問到：在 Excel 中有一個資料表（如下圖左），由日期和數值欄位組成。當繪製其統計圖表時，卻出現了原本日期清單中沒有的日期（星期六日），該如何取消顯示這些日期？

在新增圖表時，Excel 判斷了A欄是日期時，即自動以連續的日期來顯示圖表，所以必須要在圖表的座標軸格式設定中處理。

選取座標軸後，進入座標軸格式設定，在座標軸選項中的「座標軸類型」選取「文字座標軸」，即可隱藏這些原本日期清單中沒有的日期。

結果如下：

在 Excel 中的「篩選」功能是很好用的資料處理工具。當你篩選資料後，如果想要得知篩選出來的數量，並不需要再做任何處理即可得知，只是這些資訊常被忽略而已。

當你執行了篩選的動作：

當顯示篩選結果時，狀態列上即可得知本次篩選出來的資料數量。

不管你執行幾層篩選，都會顯示篩選出的數量。

有網友問到：如下圖，在 Excel 中有一個成績表，如果想要將有一次以上缺考者的成績不列入排名，該如何處理？

如果你的成績表中有出現文字（例如：缺考），則利用 SUM 函數運算時，會將文字視為 0，所以仍可得一個成績。

【公式設計與解析】

1. 將一次以上缺考者的總分列為「缺考」

最近有些網友不約而同的問到在 Excel 中使用下拉式清單的問題。如下圖，在一個進貨商的資料清單A1～A7中，每一個包含的資料清單數量並不相同。

【方法一：清單內容不變動】

如果清單項目的內容不會變動，則可以使用名稱定義+INDIRECT函數來處理。

1. 選取儲存格A2:H8。

網友問到：如何將一個數字的『個位數』轉換為 0 和 5？其規則為「 0~4：0」和「5~9：5」。

【公式設計與解析】

儲存格C2：=ROUNDDOWN(A2,-1)+(MOD(A2,10)>4)*5

複製儲存格C2，貼至儲存格C2:C16。

網友想要根據 Excel 工作表中的基本資料（如下圖），列出含有指定字串的資料清單，該如何處理？

例如下圖中，要列出每一項目中含有「BBB」字串者，其中有可能某一項中有一個以上符合。為了方便說明，並且簡化公式，特別使用「輔助欄位」。

【公式設計與解析】

1. 輔助欄位

每隔一段時間就會有網友問到關於在 Excel 中如何製作多層的下拉式清單的問題。參考下圖，當選取了「類別」中的一個項目後，在項目1中顯示該項目所屬的清單。在項目2中跟著顯示項目所選的項目來顯示所選取的清單，該如何處理？

本例先以三層的下拉式清單為例，讀者再自行擴充成你要的層數。本例的作法需要轉換表格，雖然比較費工，卻是對大多人較容易接受。參考以下的操作步驟：

1. 將表一轉換至表二、表三、表四。

2. 選取儲存格H1:K14。

網友提問：如果工作滿1年有7天假期，工作不滿3個月0天假期，工作滿3個月可以按比率得到7天的假期，該如何設計公式（參考下圖）？ （比例計算公式＝受僱天數/365天 * 7=得到的假期 （進位至整數））

【公式設計與解析】

儲存格C2：=ROUND((B2>=90)*(IF(B2>365,365,B2)/365)*7,0)

複製儲存格C2，貼至儲存格C2:C11。

如果你要使用 Excel 來抓取網頁上的表格資料，可以參考以下的文章：

Excel-從網頁上取得外部資料

Excel-在工作表中匯入網頁中的動態資料

Excel-取得部落格網頁的標題至工作表中

相同的動作，如果要在Google 試算表中執行，則可以 IMPROTHTML函數。

（參考：https://support.google.com/docs/answer/3093339?hl=zh-Hant）

網友問到 Excel 的問題：在下圖中有二個清單包含了天和時的資料，如何將兩個數據予以加總？其中 1min = 0.1H（1分鐘＝0.1小時）

以下圖中的第一列為例：1天14.41時+2天21.21時＝4天12.02時。

【公式設計與解析】

1. 計算天數

網友問到 Excel 的問題：如何將一個儲存格中裡數值中的每一個數字填入對應的儲存格？

例如：在下圖中有一個最多 9 位數的數字，如何將其每個位元的數字填填入對應的儲存格，其中包含負數的符號。

【公式設計與解析】

儲存格J2：=IFERROR(MID($L2,LEN($L2)-(10-COLUMN(J:J)),1),"")

網友問到 Excel 的問題：在工作表中的資料表有 9 個字元，分別對應數字 1～9（參考下圖右），如何將數個字元的字串轉換為數字？

在原始的欄位中的儲存格裡有多個不同數量的字元要轉換為數字，例如：在下圖左中 FCCG 要轉換為數字 6377，該如何處理？

【公式設計與解析】

儲存格B2：

網友問到：在 Excel 的工作表中（如下圖），如何在D欄、E欄、F欄中，只能輸入同一列的A欄、B欄、Ｃ欄的內容？（共有1000列要套用同一規則）

參考下圖，例如在儲存格D1:F25範圍，利用下拉式清單，讓每儲存格只能輸入A1:C25中對應同一列中內容。

【設計與解析】

基本上這是要利用「資料驗證」的方式來產生下拉式清單。如下操作：

1. 選取儲存格D1。

同事常會問到 Excel 真的那麼有用？很多公式我也不懂，學 Excel 要做什麼？我用以下的例子做了一部分的回應，重點是資料的處理。

例如：在圖書館的流通系統中匯出了一個200頁的文件，其中是學生的圖書借用資料。現在高三要畢業了，必須找出那些學生沒有還清圖書。如果系統有這個功能，那就不用傷腦筋了，問題是沒有。所以打算根據學生的借用資料來得到想要的結果，然而由系統匯出的資料並不是可以直接處理的資料（如下圖）。

其中的文字沒有排的很整齊，更不用說進一步的處理了。

這個時候，Excel 就派上用場了！