學不完．教不停．用不盡

回答網友提問：如何在一個清單中（參考下圖)，在不同項目類別之間自動加入分隔線，而且在新增資料後，正確分隔不同類別。

大家可能都很熟悉要利用「設定格式化的條件」來達到這個效果。

1. 選取儲存格A1:C9。

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

3. 選取「使用公式來決定要格式化哪些儲存格」。

回答網友提問：如果在一個日期清單中，要找出每年日期介於4/1～8/1之間者，依年度的增量來修正(下圖右)，結果如下圖左，該如何在 Excel 中設定公式？

(1) 使用 CHOOSE 函數

儲存格C2：=B2+(A2>=DATE(YEAR(A2),4,1))*(A2<DATE(YEAR(A2),8,1))*CHOOSE(YEAR(A2)-1998,1,5,4,2,3,4,2,1,6,7,5)*100

(A2>=DATE(YEAR(A2),4,1))*(A2<DATE(YEAR(A2),8,1))：判斷日期是否介於4/1～8/1，結果為 (TRUE／FALSE)*(TRUE／FALSE)，依 AND 運算結果，TRUE=1、FALSE=0。

CHOOSE(YEAR(A2)-1998,1,5,4,2,3,4,2,1,6,7,5)*100：取出儲存格A2中的年份，減掉1988，得到一個數字，對照 CHOOSE 的選項結果，再將此結果乘以 100。

最近因為微軟的 Office OneNote 2013 開放「免費下載」，大家都把焦點放在數位記事本和雲端同步等概念上。不過，我常介紹 OneNote 中有一個辨識圖片中文字的功能給學校老師用來做講義，它真是個好幫手！

假設你已安裝好微軟的 OneNote，參考以下的範例說明：

1. 先掃描某一頁紙本講義的內容(請使用時自行注意尊重智慧財產權！)，我們要取出某一段內容做為個人講義的一部分：

(如果沒有掃描器，你可以使用智慧型手機或是相機來拍紙張，但要注意控制手不要晃動)

2. 將掃描檔拖曳至 OneNote 的一個新的筆記中。

回答網友提問：如果想要在以下表格的資料中，根據「貨物號碼」最後二碼是否為「99」，如果是就給予售價加上 99，否則維持原售價。

我們試著以 RIGHT 函數取出字串的最後二碼，要注意此時取出的數字被視為文字。

在此提供二種公式運算方式，不同的運算觀念，相同的運算結果：

(1) 儲存格E2：=(RIGHT(C2,2)="99")*99+D2

RIGHT(C2,2)="99"：判斷是否儲存格C2未二碼為「99」，得到一個 TRUE／FALSE 的結果，在運算過程中會轉為 1／0。

(2) 儲存格E2：=IF(RIGHT(C2,2)="99",99+D2,D2)

有人想要知道如果要求取兩個數的最小公倍數和最大公因數，如何使用 Excel 的公式來取得。(參考下圖)通常，你可以透過LCM和GCD兩個函數來直接取得結果，現在我們用公式來模擬這兩個函數的結果。

我參考了一些人的做法，提供建議的公式：

(1) 最小公倍數

儲存格C2：

{=MIN(IF(MOD(A2*ROW(INDIRECT("1:"&B2)),B2)=0,ROW(INDIRECT("1:"&B2))))*A2}

如果你要產生一個十六進制的數列，在 Excel 2010 以上版本，只要使用 DEC2HEX 函數即可產生，例如：

儲存格A2：=DEC2HEX(ROW(1:1)-1)

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

參考下圖左，如果你要產生的是兩位數的十六進制(第一碼可能為0)或是你的 Excel 版本無法使用 DEC2HEX 函數，則可以自行以公式來產生：

儲存格A2：

=CHOOSE(MOD((ROW(A1)-1)/16,16)+1,0,1,2,3,4,5,6,7,8,9,"A","B","C","D","E","F")

有學校同仁問到：如何在 Excel 的工作表(或是 Word )中直接產生條碼，在螢幕上或是列印後可以直接使用？例如下圖中的號碼，要產生一組對照的條碼。

要在文件中產生條碼，最簡便的方便是使用「條碼字型」。網路上有免費的條碼字型可以下載，將下載的條碼字型安裝在 Windows 的「fonts」資料夾之下，即可使用。

（要搜尋條碼字型可以在 Google 上搜尋「barcode 字型」）

以下載的「3 of 8 Barcode」字型為例，在使用時必須在號碼的前後加上「*」：

儲存格B2：="*"&A2&"*"

有網友想要了解如何在一個資料表中，刪除固定間隔的那幾列？方法有數種，這次用幾個步驟來刪除固定間隔列。假設要刪除間隔 3 的那幾列，例如第 3, 6, 9, … 列。

1. 在原始資料(下圖左)中插入一輔助欄(本例為Ａ欄)，並在儲存格A1和儲存格A2填入「*」，儲存格A3留下空白。

2. 選取儲存格A1:A3，複製後在以下各列貼上。(如下圖右)

3. 選取儲存格A1:A23，按一下 Ctrl+G 鍵，開啟「到」對話框。

4. 按一下「特殊」按鈕。

回答網友提問：如何在一個日期清單中(如下圖左)，計算所有日期中各年的個數(如下圖右)。

儲存格D2：=SUMPRODUCT((YEAR($A$2:$A$25)=C2)*1)

YEAR($A$2:$A$25)：在日期陣列中利用 YEAR 函數取出年的部分，組成年的陣列。

(YEAR($A$2:$A$25)=C2)*1：判斷年的陣列是否等於儲存格C2的內容，得到 TRUE／FALSE 組成的陣列，經由「*1」運算，變為 1／0 陣列。

再透過 SUMPRODUCT 函數，將上述 1／0 陣列計算總和，即為所求。

複製儲存格D2，貼至儲存格D2:D6。

最近見到同仁使用 Word 時，遇到的少許困擾，或許你也遇到過。我是這樣解決的：

(1) 當表格位於頁面第一行時，如何插入一行空白行

請將插入點移至表格的第一個儲存格：

按一下 Enter 鍵，即可在表格外插入一個空白行：

回答網友提問：當資料分處於多個工作表中(例如：99年、100年、101年、102年)，如果想要在某一個工作表中查詢各個工作表中的資料，該如何處理。當然各個工作表的格式都是一樣的！(參考以下四個圖)

現在來練習將其他工作表中的資料整合在一個工作表中，如下圖：

回答讀者提問：有一個資料表(參考下圖上半部)，其中每個月份所列的是價格資訊，現在要根據編號所對應的日期篩選出價格，其規則為：

如果日期在01日~10日搜尋前月資料；如果日期在11日~31日搜尋當月資料。

篩選結果如下圖的下半部，公式如何處理？

【準備工作】

選取含有編號資料的儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：編號。

在日常生活中，人的記憶對於某一天可能較有印象，但是對於經過幾天的概念比較不容易建構，因為還要經過計算才能得到，尤其每年的日數還不是固定的。

所以，你會用到和老婆結婚幾天、和女兒認識幾天等這些數字以製造一些驚喜。很好用喔，你懂的！

當然，你也可能計算離退休還有幾天、距離大考還有幾天等倒數的天數，以激勵自己。

因為 Excel 已經將每一天都數字化了，所以你只要利用加／減運算即可。

(1) 計算經過天數：儲存格B3：=B2-B1+1

(2) 計算預定日期：儲存格B7：=B5+B6+1

有網友詢問：如果在 Excel 的工作表中產生了錯誤訊息(參考下圖)，想要讓這些錯誤訊息不要顯示出來，或是列印的時候不列印出來，該如何處理？

如果你只是想要將工作表中的錯誤訊不要列印出來，則可以在「版面設定」中的［工作表］標籤中，在[儲存格錯誤為]下拉式清單中選取「空白」。

預覽列印時即可發現錯誤訊息的儲存格都以空白顯示了：

有網友問到，其資料表中(參考下圖)如果使用 SEARCH 函數要取出第1個數字時，遇到困難，先觀察其公式：

儲存格B2：=MID(A2,SEARCH("W",A2)+1,SEARCH("*",A2)-SEARCH("W",A2)-1)

得到錯誤訊息：#VALUE!

這是因為公式中的 SEARCH 函數中要搜尋的字元「*」為萬用字元，因為在搜尋該字元時，應使用「~*」，因為公式改為：

儲存格B2：=MID(A2,SEARCH("W",A2)+1,SEARCH("~*",A2)-SEARCH("W",A2)-1)

有網友問到類似以下的問題：參考下圖右的一個資料表，其中人員的排列是隨機的，每人有三個分數，比重分別為1,3,5，如何在下圖左中查詢人員名稱以對應計算出小計。

【準備工作】

選取儲存格D2:G11，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：人員、分數1、分數2、分數3。

選取儲存格D1:G1，按 Ctrl+Shift+F3 鍵，勾選「最左欄」，定義名稱：比重。

【輸入公式】

在 Excel 中有一個資料表(如下圖左)，略過空白儲存格將資料重整，重新組合如下圖右，該如何處理？

【準備工作】

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

【輸入公式】

儲存格C2：{=OFFSET($A$1,SMALL(IF(資料<>"",ROW(資料),999),ROW(1:1))-1,,,)}

解答網友問題：在下圖中的 Excel 資料表，如何由「人員」求得「單位」？

這類的查表方式，在本部落格文章中時常被使用，通常有下列數種公式類型：

儲存格E2：=INDEX(A2:B20,MATCH(D2,A2:A20,0),2)

儲存格E2：=OFFSET(B1,MATCH(D2,A2:A20,0),)

儲存格E2：=VLOOKUP(D2,A2:B20,2,FALSE)

有網友想要在 Excel 中，隨機產生特定一組文字／數字的其中一個，該如何處理呢？

參考下圖，我們來練習在一組數字和一組文字中，隨機產生其中的一個。

數字範例：3、6、7、10、13、15、17、19、20、25

文字範例：甲、乙、丙、丁、戊、己、庚、辛、壬、癸

【產生公式】

一直有網友有這種需求：在 Excel 的資料表，想要將一欄中的資料篩選為多欄後依順序列出(參考下圖)，該如何處理呢？

Excel 其實提供了多種的工具可以手動來完成這個工作，但是，因為資料會變動，所以還是以公式來處理比較好。

【準備工作】

選取儲存格A1:C26，按下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：人員、假別、時數。

【輸入公式】