學不完．教不停．用不盡

學校老師用 Excel 記錄學生小考成績時，要善用試算表的設定格式化的條件功能。製作動態效果的成績表，達到最大的提醒作用。

例如：將不及格的分數用紅色標示，並且將累計超過三次不及格者，以紅色底色顯示。

(1) 標示不及格分數

1. 選取儲存格C2:H26。

2. 在設定格式化的條件中，新增一個規則：

參考下圖，有網友想要將 VIP 滿一年(到期日)者，在到期前二個月(60天)內給予警示，例如以紅色字顯示，而已經過期者以灰色顯示，該如何處理？一般這種案例都是以「設定格式化條件」來操作即可。

(1) 只設定日期欄位給予不同色彩

1. 選取儲存格E2:E22。

2. 設定格式化的條件為：使用公式來決定要格式化哪些儲存格。

3. 輸入公式：=E2<TODAY()

(2022 年有新版本：Excel-將民國年格式轉換為西洋年格式)

前言：先前有一篇將民國年轉西元年的文章，因為只考慮民國年只有二位數所寫出的公式，在三位數的民國年會產生錯誤，特別重新撰寫了新的公式。

在如下的民國年欄位中(年月日以「.」分隔)，想要產生西元年的表示法(年月日以「/」分隔)，該如何處理？

可行的做法：先把第一個和第二個「.」的位置找出來，再分別取出年月日的值，將其以「/」串接。

(1) 第一個「.」的位置

有網友問到：如果要將某個數值依間隔值分割，依序列出，最後列出不足數，該如何處理？(參考下圖)

【輸入公式】

儲存格B3：=IF(COLUMN(A:A)>INT($A3/$B$1)+1,"",IF(COLUMN(A:A)<=INT($A3/$B$1),$B$1,MOD($A3,$B$1)))

INT($A3/$B$1)：計算儲存格A3內容共含有幾個間隔值。

COLUMN(A:A)<=INT($A3/$B$1)：判斷儲存格所在欄是否小於間隔值數。

有網友問到：若要為原始數值加上000或是去除000來表示，該如何處理？(參考下圖)

(1) 加上 000

若要為數值加上 000，表示該數值必須乘以 1000，或是以 TEXT 函數來處理：

儲存格B2：=TEXT(A2&"000","#,###,000")

(2) 去除 000

有位網友問到，想要將一些地址摘要顯示只到路名或街名，不知該如何處理？(參考下圖)

在台灣的地址，好像有路就不會有街，有街就不會有路，如果真是如此，則利用 FIND 函數即可順利解決該問題。

儲存格B2：=LEFT(A2,IFERROR(FIND("路",A2),1)*IFERROR(FIND("街",A2),1))

IFERROR(FIND("路",A2),1)：運用 FIND 函數找尋「路」在儲存格A2中的字串位於第幾個字，如果找不到，運用 IFERROR 函數將結果取「1」。

IFERROR(FIND("街",A2),1)：運用 FIND 函數找尋「街」在儲存格A2中的字串位於第幾個字，如果找不到，運用 IFERROR 函數將結果取「1」。

有網友問到，如果要將一欄的資料轉換成多行，該如何處理？一般這種問題，可能有二種轉換方式，分述如下。

若要將一欄資轉換為多欄資料，只要藉助 OFFSET 函數，配合 ROW 和 COLUMN 函數即可完成。

(1) 取出連續固定數值(例如10)儲存格為 1 欄

例如：取儲存格A1:A10為 1 欄、取儲存格A11:A20為 1 欄，依此類推。。

儲存格C1：=OFFSET($A$1,ROW(1:1)+(COLUMN(A:A)-1)*10-1,,)

複製儲存格C1，貼至儲存格C1:G10。

大家已經很熟悉如何使用 Office 2010 來製作 PDF 檔，有同事遇到一個現象(參考下圖)！

當你的 Word 檔中的放了一張圖片，再另存新檔製成 PDF 檔後，將滑鼠移至 PDF 上的圖片時，會顯示圖片原始的檔案路徑，這個結果帶來了一些困擾，不知道可不可以不要顯示檔案路徑呢？

跟著以下這樣做：

在另存為 PDF 檔時，選取「選項」按鈕：

有網友問到一個將表格摘要統計的問題。參考下圖左的資料清單，其中有非常多筆記錄，而 ID 資料共有三類，現在，要摘要出下圖右的統計表。通常這類題目都是交給 SUMPRODUCT 函數來處理！

【準備工作】

選取A欄至E欄中含有資料的儲存格，按一下 Ctrl+Shift+F3 鍵，定義名稱：ITEM、ID、SOH、REC、SOLD。

【輸入公式】

儲存格H3：=SUMPRODUCT((ITEM=$G3)*(ID=$H$1)*INDIRECT(H$2))

有網友問到：如果在儲存格A1~A3中填入數字 1，如何使儲存格A4~A6自動填入數字2及儲存格A7~A9自動填入數字3，以此類推至10。

假設在儲存格A1中填入一個起始值，在儲存格A2中輸入公式，再將儲存格A1複製後，貼至下方各列儲存格。

儲存格A2：=A$1+INT(ROW(1:1)/3)

INT(ROW(1:1)/3)：ROW(1:1)=1，往下複製後會產生 ROW(2:2)=2、ROW(3:3)=3、…。所以INT(ROW(1:1)/3) 可以產生 0,0,1,1,1,2,2,2,…。