學不完．教不停．用不盡

有網友提到這樣的困擾：在 Excel 中有一些各種電話的格式，如果想要把其中的「(、)、-」去除，而且要保留第一碼的「0」。又因為自己的區域號碼是 02，所以想要把 02 開頭的電話號碼，全部去除 02。該如何處理？

這是個很多人需要解決的問題，不過只要使用一個函數：SUBSTITUTE，即可輕鬆去除所有符號。

【公式設計】

1. 去除符號

有網友問到：在下圖中有多個不同系的資料，如何以一個公式，自動列出各系的排名？且積分相同時應列相同名次。

一般人要計算排名，大概都會利用 RANK 函數，不過在此，主要是要使用 SUMPRODUCT 函數即可搞定。

【準備工作】

選取儲存格B1:D29，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：系別、積分。

【設計公式】

有網友問到：參考下圖，在Excel 中當使用 TEXT 函數時，使用「"yyyymmdd"」參數時，為何輸入 1889/12/31 時，結果沒有把「/」消掉，也就是說公式失效了？

這是因為 Excel 認定 1900/1/1 轉換為數字時視為 1，每多 1 天，數值加 1。在上圖中，日期為 1921/10/10 為有效日期，所以被視為數字，因此預設靠右對齊。而日期為 1889/12/31 並不是有效日期，所以被視為文字，因此預設靠左對齊。

即然 1900/1/1 是有效日期的最小值，那最大值呢？經由實驗，可得 9999/12/31，再多一天就會視為無效日期。而在 Macintosh Excel 中是以 1904/1/1 為第 1 天。

可參閱微軟提供的說明：

Excel 中 1900 和 1904 日期系統的差異

網友問到：在下圖中，根據表一的清單內容，只要表三出現的內容，就不會出現在表二，該如何處理？

研究題意：也就是表二和表三的內容互相不重覆，組合起來即為表一。

【公式設計】

(1) 根據G欄查詢得到H欄內容

網友問到在以前的一篇文章：Excel-萬年月曆(WEEKDAY,DAY,DATE)中，可以製作萬年月曆，但是要如何讓上一個月／下一個月的日期動變為淺灰色呢？

一般這種操作都是透過「設定格式化的條件」來處理，以本例來操作：

1. 選取儲存格A3:G3。

2. 設定格式化的條件如下：

選取規則類型：使用公式來決定格式化哪些儲存格

網友問到：在 Excel 的資料表中有個文數字的集合，如何取出數字部分來加總呢？參考下圖，所有文數字混合均是文字在前、數字在後，或是沒有包含文字、只有數字。

【公式設計】

儲存格B2：｛=MID(A2,MIN(IF(ISNUMBER(MID(A2,ROW($1:$20),1)*1),
ROW($1:$20),99)),99)*1｝

網友想要由一個基本表(下圖右)中查詢在下圖左裡每個料號是否已登錄，還是為一個新產品？即只要比對前幾碼不符合者，就是一個新的料號，在新產品欄位中標示「NEW」。該如何處理？

【設計公式】

(1)

依據圖示，每個料號之後有一個「-」符號，用以找尋料號。

儲存格B2：=IF(COUNTIF($D$2:$D$25,LEFT(A2,FIND("-",A2)-1)),"","NEW")

繼上一篇文章：Excel-根據某欄摘要出符合條件的結果(陣列公式,OFFSET)，又有網友問到類似問題，在下圖中，想要挑出Ｂ欄中非空白的儲存格內容。由此可知這方面的問題真的困擾了很多人。

一般，我都使用「陣列公式」來處理比較多，但是很多人又對陣列公式沒有概念，無法充分理解公式的做法。所以，如果你有非用陣列公式的做法，不妨也提供廣大讀者參考。

【設計公式】

儲存格D2：｛=OFFSET($B$1,SMALL(IF(B$2:B$20<>"",ROW(B$2:B$20),9999),
ROW(1:1))-1,,,)｝

網友想要根據一個 Excel 的資料表中的資料(下圖上)，藉由查表方式填入另一個資料表中(下圖下)，問到該如何處理？

在 Excel 中，可以使用來做為查表的函數有好幾個，例如：INDEX, OFFSET, MATCH, VLOOKUP 等，請者可以將函數名稱在我的部落格中查到很多的相關範例。

【準備工作】

選取儲存格A2:A15，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：編號。

選取儲存格B1:I1，在[公式／名稱管理員]功能表中，自行定義名稱：代號。

網友想要根據一個 Excel 資料表(下圖左)，由某一欄位(點數)挑出符合條件者(>=60)的摘要表(下圖右)。

目前我的做法是要透過陣列公式來處理，如果資料數量很大時，或許速度會變慢，但仍不失一個好的做法。

先選取儲存格A1:A27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：序號。

先選取儲存格D1:D27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：點數。

有網友問到：在 Excel 中如何快速在儲存格A1:A10000中填入 00001 ~ 10000 的數字，並且希望在刪除某些列時，該數列仍能維持 00001 ~ 10000？

這樣看來無去使用自動填滿某個常數數列的方式，我個人是都這樣做：

1.在名稱方式中輸入：A1:A10000，按下 Enter 鍵，此時即會選取儲存格A1:A10000。

2. 輸入公式：=TEXT(ROW(),"00000")

網友想要將 Excel 工作表中的數值，依自訂的規則調整數值大小，該如何處理？

其修正規則如下，要將數值：介於1001~1015者以1000計，介於1016~1064者以1050計，介於1065~1100者以1100計。(參考下圖)

【公式處理】

儲存格C2：=LOOKUP(A2-1000,{0,16,65},{0,50,100})+1000

網友問到：在 Excel 的資料表中有一個資料數列，其中的資料含有 5"、6"、7" 的 A管、B管、C管 的組合，每一種組合都設有一個長度，如何能根據這些沒有排列規則的數列，來計算各種排列的個數和總度？(參考下圖，以紅色字為例求 5" 的 B 管之個數和總長度。)

雖然儲存格內容是任意排列的資料，不過透過 SUBSTITUTE 和 SUMPRODUCT 函數，可以使用很短的公式即可求得答案。

【準備工作】

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

網友問到：在 Excel 的工作表中，如何可以使用某一字元將儲存格內容補足固定長度的字元數？例如：在下圖中，用 # 字元(代替空格)和 0，分別在原儲存格內容向左或向右補足字元長度。　

填滿固定長度的位元數可以補在儲存格內容的右側或是左側，這是在資料處理時常見的做法。

【公式設計】

(1) 用 # 向右補滿10位元

網友想要在一個 Excel 的日期清單中，利用某一儲存格的內容來標示逾期 7 天者，該如何處理？

如下圖，如果儲存格C5置入「V」，則在日期清單中會將逾期(以今天為準)7天的日期以紅色字標示。

這個動作還是要透過設定規式化條件來處理。

1. 選取儲存格A2:Ａ19。

2. 選取[常用／設定格式化的條件]的「新增規則」。

3. 在[編輯格式化規則]對話框中，設定：

選取規則類型：使用公式來決定要格式化哪些儲存格

在編輯規則中輸入規則：=($A2<TODAY()-7)*($C$5="V")

格式：紅色字

其中「*」運算子相當執行邏輯 AND 運算，TODAY 函數可以找出今日日期，當儲存格C5為「V」時，將小於今日日期 7 天者，標示為紅色字。

若是要在儲存格C5為空白時顯示逾期 7 日的日期，則可以修改格式化的條件為：

=($A2<TODAY()-7)*($C$5="")

有網友問到：如下圖中的 Excel 資料表，職稱欄位裡有多個不同的職稱，如何取出想要的職稱計算項目個數？

假設職稱的資料範圍在：儲存格B2:B123。例如：使用以下公式：

儲存格D2：=COUNTIF(B2:B123,"會員")+COUNTIF(B2:B123,"總教練")+
COUNTIF(B2:B123,"理事")+COUNTIF(B2:B123,"總幹事")+COUNTIF(B2:B123,
"第七八屆理事長")+COUNTIF(B2:B123,"副理事長")+COUNTIF(B2:B123,"監事")
+COUNTIF(B2:B123,"後備理事")+COUNTIF(B2:B123,"常務理事")+

如果你想將 Excel 工作表中計算結果為 0 者，不要顯示 0，而是顯示空白，該如何處理？又如果計算結果為錯誤訊息者，也要將其顯示為空白，該如何處理？

參考下圖，其中B欄－A欄的結果為0者，要以空白內容顯示；B欄／A欄的結果為錯誤訊息者，要以空白容內顯示。

(1) 儲存格為０者顯示空白

原先儲存格C2：=B2-A2，複製儲存格C2，貼至儲存格C2:C20。

網友想要在一個含有日期數列的 Excel 工作表中，給予起始日期和終止日期，希望能自動標示這二個日期之間的儲存格，該如何處理？

例如：在下圖中的日期和數值清單中，依據起始日期和終止日期來判斷，自動以紅色字標示出在這二個日期之間的日期和數值。

參考以下的做法：

1. 選取儲存格A2:B24。

2. 選取[常用／設定格式化的條件]的「新增規則」。

網友問到：如何在 Excel 的工作表中將一串數列，於原儲存格位置將每個數值加上特定的數？例如，在下圖的數列中要將每一個數值加上 5。

雖然這是個不難的問題，但也真的是難為了一些人。不過，Excel 已經有提供解決的方案，可以藉助一個輔助欄位來處理。

參考以下的步驟：

1. 選取儲存格C2:C18，輸入 5，按 Ctrl+Enter 鍵，儲存格C2:C18中每一個都輸入5。

2. 選取儲存格C2:C18，按 Ctrl +C 鍵，執行複製的工作。

有網友問到在 Excel 中將數字填入儲存格後，卻顯示 E+15 之類的數字，是什麼原因？

(觀察下圖)根據微軟網站提供的資料：

Microsoft Excel 會保留 15 個有效位數。若要顯示所有的 15 個位數，必須使用非一般的數字格式 (自訂或內建)。一般的數字格式顯示最多 11 的數字字元，以計算數字的字元為十進位小數點。因此，如果數字包含小數點，Excel 可以顯示最多 10 個有效位數，但如果數字沒有小數點，Excel 可以顯示最多 11 個有效位數。

(1)

若你輸入一個位元數較大的數字，例如：12345678901234500000，若在預設的數值格式之下，你將會看到被顯示為指數型表示：1.23457E+19，其含小數點只顯示 7 個有效位數。