學不完．教不停．用不盡

今天被問到一個很實用的問題：在 Excel 的資料表中，如果有輸入資料，則整列變色，該如何處理？(參考下圖)

通常，這需要用到「設定格式化的條件」。

1. 選取儲存格A2:C11。

2. 設定這些儲存格某種色彩。

3. 選取[常用/設定格式化的條件]選項。

在 Excel 中，如果給予二個日期區間，如何能自動列出這二個日期之間的日期序列？(參考下圖)

儲存格C2：=A2

儲存格C3：=IFERROR(IF(C2+1>$A$3,"",C2+1),"")

複製儲存格C3，往下各列貼上。

使用 IFERROR 函數，在產生錯誤訊息時，改為顯示空白字串。

有網友問到：如果在一個資料表中，會不斷的增加資料(參考下圖)。如何能在另一個工作表中，呈現最末10, 8, 6, 4, 2 筆的資料？(參考下下圖)

【輸入公式】

儲存格B1：=INDIRECT("data!A"&(COUNT(data!A:A)-10+ROW(1:1)*2))

在 Excel 中有一個依組別和男/女所記錄的數值資料表(如下圖左)，如何轉換成下圖右的格式呢？

【準備工作】

選取儲存格A1:C21，按一下 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：組別、性別、數值。

【公式-使用SUMPRODUCT】

儲存格F2：=SUMPRODUCT((組別=$E2)*(項目=F$1)*數值)

有網友留言問到：在 Excel 中，如何將 1-2 改設為 001-002。(參考下圖)

儲存格B2：=RIGHT("000"&LEFT(A3,FIND("-",A3)-1),3)& "-"&RIGHT("000"&RIGHT(A3,LEN(A3)-FIND("-",A3)),3)

複製儲存格B2，往下各列貼上。

FIND("-",A2)-1)：找尋「-」在字串中的位置。

LEFT(A2,FIND("-",A2)-1)：取出「-」左邊的字串。

"000"&LEFT(A2,FIND("-",A2)-1)：將上述字串的左邊串接「000」。

有同事問到，在 Excel 中如果要統計一個字串中每個字母的個數，該如何處理？(參考下圖)

儲存格C2：=LEN($A2)-LEN(SUBSTITUTE($A2,C$1,""))

複儲存格C2，貼至儲存格C2:F6。

(1) LEN($A2)：計算儲存格A2中的字串長度。

(2) SUBSTITUTE($A2,C$1,"")：將儲存格A2中的「A」字母，全部以空白取代。

在 Excel 中有一個資料表(如下圖左)，如果想要轉換為下圖右的型式，該如何處理？

【準備工作】

選取儲存格A1:C27，按一下 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：項目、日期、時段。

【輸入公式】

在 Excel 中有一個組別、姓名的基本資料表(參考下圖左)，如何根據這個資料表自動將姓名填入各個組別中？(參考下圖左)

【準備工作】

選取儲存格A1:B28，按一下 Ctrl+Shift+F3 鍵，定義名稱：組別、姓名。

【輸入公式】

在 Excel 中有一個資料表(如下圖左)，如果要根據勾選的結果，自動依序列出有勾選的人員名冊，並列出編號、姓名、得分(參考下圖右)，該如何處理？

【準備工作】

選取儲存格A1:C28，按一下 Ctrl+Shift+F3 鍵，定義名稱：勾選、姓名、得分。

【輸入公式】

在 Excel 中如果想要產生相同星期幾的日期數列，例如：星期一、星期四、星期一、星期四、…的日期數列，該如何處理？(參考下圖)

1. 在儲存格A1輸入第一個日期，例如2012/1/02，星期一。

2. 儲存格A2輸入公式：=A1+IF(WEEKDAY(A1,2)=1,3,4)。

判斷上個儲存格是否為星期一，如果是則加3(結果為星期四)，如果不是則加4(結果為星期一)。

3. 複製儲存格A2，往下各列貼上即可。