學不完．教不停．用不盡

有同仁問到在 Excel 中，關於日期的顯示格式設定問題，以下稍做整理。

這是先前的文章可參考：http://isvincent.pixnet.net/blog/post/38519565

關於日期的基本格式：

範例：

有網友問到在 Excel 的工作表中，若是在同一欄中，如何避免輸入重覆的內容？這是在輸入資料時常見的問題，一般可以使用二種不同的方式。

1. 使用設定格式化的條件

參考下圖，若在同一欄中輸入了重覆的內容時，就讓重覆內容的儲存格顯示不同的格式。這個方法僅是提示作用，無法強制不輸入重覆的內容。假設一欄中有資料的儲存格範圍是A2:A200。

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

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

網友問到：如何在 Excel 工作表裡將一個資料清單中為民國年格式的日期轉換為西元年格式?

參考下圖左為民國年格式，轉換為西元年格式，如下圖右。

【公式設計與解析】

藉著例子來練習：RIGHT、MID、LEFT、DATE、TEXT函數。

儲存格C2：=TEXT(DATE(LEFT(RIGHT("0"&A2,7),3)+1911,MID(

在 Excel 中的公式中可以使用萬用字元『*、?』，可以很方便的查詢到不只一個的數量，例如：=COUNTIF(K1:K25,"李*")，可以在儲存格K1:K25中查詢，姓「李」的人有幾個，不管其名字為一個字、二個字、三個字等，都能併入計算。

但是如果是儲存格內容中已含有『*、?』，則處理上會不一樣。

1. 查詢時使用變數(*、?、~置於儲存格中)

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

(O) 儲存格E3：=VLOOKUP(D3,A2:B21,2,FALSE)

在 Excel 中如果有多個工作表，若要將其摘要在同一個工作表，該如何設計公式？

以二個工作表為例，參考下圖，有工作表：104年和105年。

下圖中是在另一個工作表(整理)中以下拉式選單選取一個工作表，並將所對應的工作表摘要成下圖的清單。

有同仁問到：在 PowerPoint 中，對於 SmartArt 中建立的內容，如果在其中的項目建立『超連結』，當在實際執行簡報時，會因為超連結本身和是否點選過這個超連結，而文字顯示的色彩會和原先的設定有差異，而且超連結的文字會自動加上底線，該如何避免呢?

好問題！而且解決之道也很簡單！而且也適用 Word 和 Excel。

通常一般人的做法是：選取文字後再設定超連結，選取一個『連結至』的目的地(參考下圖)。

很明顯，在投影片上即可看出設定超連結的文字預設為藍色並且加上了底線，目前看來已破壞了原先的色彩配置。

今天聽到老婆大人和兒子在吵著全台和外島的鄉鎮市區到底去過幾個了？一時算也算不清楚啊！當下想一想，那就先建個完整的縣市鄉鎮市區表，有空時好好的算一算，算清楚了，沒去過的就努力的安排時間去一下，也算是愛台灣啊！

首先，到維基百科抓到『中華民國自由地區鄉鎮市區列表』：

網址：https://zh.wikipedia.org/wiki/中華民國自由地區鄉鎮市區列表

其中有一個綜合統計資料列表，其中已有所有的行政區名，選取並複製其中的內容：

當你在製作比較多頁數的文件時，有時候在切換頁面時會比較不容，或許你使用滑鼠滾輪拼命滾動，或許你使用 PageUp／PageDown 鍵來切換，以循序式的方式來瀏覽和尋找所要的頁面。關於切換，因為寬螢幕之下的文件，螢幕高度小於頁面的長，所以我建議可以試試 Ctrl+ PageUp／PageDown 鍵，因為 Word 會以版面的『一頁』為單位來切換。

畢竟使用循序式的切換是速度比較慢、比較不方便的做法。一般還是會建議同仁們要設定動態連結等操作，在後續的作業中會帶來一些便利之處。

當你已經設定了段落樣式，選取[檢視／顯示]功能表中的「功能窗格」後，你可以在功能窗格中切換到你想要的位置。其中的標題看起來是有階層架構的。

有位網友每天都要在 Excel 工作表中 Keyin 很多的資料(如下圖)，而這些資料之後又要依姓名分別列到各個工作表中。我們能做到在主工作表輸入好資料時，其他分頁工作表的內容就自動到位嗎？因為每天耗時 Keyin 二次，真是浪費生命啊！

其實你只要使用「篩選」功能，分幾次複製到各個工作表就可以解決這個問題了。而你如果想要讓公式來代勞，也想省去操作「篩選」的步驟。參考以下的做法。

在下圖中，假設工作表名稱為 DATA。而資料範圍為儲存格A1:C25。

在主工作表DATA中，目前有甲、乙、丙、丁、戊五個人，如何在五個工作表中篩選各自的資料呢？以下用「甲」工作表為例來設計公式。

儲存格A1：{=OFFSET(DATA!$A$1,SMALL(IF(DATA!$A$2:$A$25=甲!$A$1,

有同事為了取得一段日期區間，在 Excel 中的工作表中如下圖的A,B,C三欄，現在如果想要把這三欄合併成一欄，該如何處理？

同仁原本使用的方式是用字串串接的方式，所以使用公式：

儲存格E2：=A2&B2&C2

結果顯然是錯的，所有日期都被轉回原型(一個數字)，該如何調整公式呢？

儲存格G2：=TEXT(A2,"yyyy/mm/dd")&B2&TEXT(C2,"yyyy/mm/dd")

網友問到：下圖左是一個年齡、仰臥起坐次數及得分的對照表，如何依據測試人員的年齡和仰臥起坐次數求得對應的積分(下圖右)？

【公式設計與解析】

儲存格L2：=INDEX($A$2:$A$7,MATCH(K2,OFFSET($A$1,1,MATCH(J2,
$B$1:$G$1,0),6,1),1),1)

如下圖，網友想要在 Excel 的資料清單中依據日期欄位中的內容，以第一個空白者(未填日期)為第一個預備人選，由上而下把空白者列為優先名單，該如何處理？

【公式設計與解析】

選取儲存格C1:C17，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期。

儲存格F2：{=OFFSET($A$1,SMALL(IF(日期="",ROW(日期),999),ROW(1:1))-1,0)}

最近有二個網友不約而同問到：在 Excel 的工作表中常會有一些不可見的『亂碼』隱藏其中，造成格式無法對齊、搜尋無法準確等現象，如何快速消掉這些造成亂碼的字元呢？

我其實也沒有特別好的方法！但是在 Excel 2010/2013/2016 等版本中有一個 CLEAN 函數，可以消除字串中無法列印的元字，可以拿拿試試是否可用。

例如：在下圖中的儲存格C2，其公式：=A2&CHAR(10)&B2。

這個公式可以將兩個字串放在同一個儲存格中，並且分成二列呈現。

其中 CHAR(10) 即為不可見、無法列印的字元。

儲存格D2：=CLEAN(C2)，利用 CLEAN 函數即會消除這些不可見的字元。

在 Excel 的公式中使用定義『名稱』是非常實用的方式，用名稱來代表儲存格，就好像你較能接受使用 Domain Name(例如：www.edu.tw)，而較無法直接使用 IP(例如：140.111.14.180)。

例如，在下圖中有二個工作表(DATA1和DATA2)，其中有兩個類似的表格。

我們試著在名稱管理員中建立以下這些名稱：

觀察這些定義的名稱，你可以發現：

同仁遇到這樣的問題：在下圖左，是一個 Excel 的資料表，如果要將這個資料表轉換成下圖右的樣式，該如何處理？

面對這樣的問題，只要判斷儲存格內是否為『V』，如果成立，則印出欄標題，如果不成立，則印出空白。

儲存格J2：=IF(B2="V",B$1,"")

複製儲存格J2，貼至其他每個儲存格。

特別提醒：在 Excel 的判斷式中，雖然是使用『B2="V"』，但是儲存格B2不管輸入『Ｖ』或是『v』，結果都是成立的。

有網友根據這篇 Excel 的教學文章：

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

想要變換一下，在下圖中想要根據某一欄的一個數值，來反推『列標題』，該如何處理？

【公式設計與解析】

一般使用者習慣的 Excel 表格應該如下的格式，其中共有三個欄位，如果你要排序，只要指定某一個欄位，再透過排序的工具即可輕易完成。

但是有人的表格如下圖(上)，在不想調整表格的前提下(轉置90度)，該如何排序成下圖(下)？本例是以第9列的成績資料來排序。

雖然大家都習慣取用『欄』的資料來排序，但是 Excel 也是有提供取『列』來排序的功能。

1. 選取儲存格B7:M9。

最近在公務上有些文件需要跨處室、多單位的同事一起來填寫，大家不知道如何操作可以最快速且最方便？有人建議使用 Word 中的「追蹤修訂」來處理，但是最後又得將 Word 文件彙整到某一人的電腦中來處理，整個過程也不是很方便。

最後是建議使用 Google 雲端硬碟的 Google 文件來共筆輸入資料，過程不需要使用 Word，也不用進行彙整工作，並且任何地點、任何時間都可以處理，最後再匯出成 Word 檔，再利用 Word 的排版功能加以美工處理。

(因為還有些同事不太熟悉 Google 文件的共用功能，以下稍微簡介說明。)

如何來啟用文件和他人共筆呢？在 Google 文件中點選「共用」，你就可以藉由輸入其他人的 Google 帳號(Email)來邀請加入來協作。被邀請的使用者，只要登入 Google 帳號，即可找到這個被分享的文件。記得：如果要讓使用者可以輸入資料，得挑選「可以編輯」。

有人建議多人使用同一個 Google 帳號來登入，這個作法並不建議，因為無法區隔出每一個做了那些修訂。

在下圖中有一個 Excel 的資料表，其中要依女和男的年齡分別找出對應的組別，該如何處理？

本例是儲存格E11:G15的內容為例子來設計，並練習使用 VLOOKUP、OFFSET、MATCH 等函數。

【公式設計與解析】

儲存格C2：=LOOKUP($B2,OFFSET($E$1,0,MATCH($A2,$E$1:$F$1,0)-1,5,4-

有同仁提到：在 Google 雲端硬碟中使用 Google 文件來編輯時，在表格方面要做出漂亮的表格並不方便，時常會造成困擾，該如何解決這樣的問題？

在 Google 文件中新增一個表格，的確是看起來比較單調，要設定出美觀的表格，真是耗時費工的苦差事。

如果是在 Word 中就不一樣了，好多套色好的表格樣式可供選取：

何不把這個工具配套在一起呢？你只要在 Word 中複製表格，直接在瀏覽器中的 Google 文件中貼上即可。Word 表格的格式會被帶到 Google 文件中，而你可以繼續在 Google 文件中使用其提供的工具列來編輯表格格式。