實用Excel技巧分享：神奇的「自訂格式」功能

在先前的文章《實用Excel技巧分享：批次建立/分割工作表、批次重新命名工作表》中，我們學習了兩個設定工作表的方法。而今天我們來學習「自訂格式」功能，學會了自訂格式，處理很多問題都可以不必再使用函數。

在EXCEL軟體的預設儲存格格式中，一般是數值、日期或文字等等。那有沒有什麼方法，可以讓儲存格內的字元按照我們想要的格式來顯示呢？今天要跟大家介紹一個很好用的功能：自訂格式。

1.自訂格式的開啟方式

#選取任意儲存格，點選「開始」選項卡，在「數字群組」右下角有個擴充按鈕，點選可調出「設定儲存格格式」對話方塊。

也可以直接按下快速鍵Ctrl 1就可以調出如下圖的對話框。點擊“數字”-“自訂”，右邊的“類型”下方就是我們要填寫自訂格式代碼的地方，點擊確定後，儲存格內字元就會顯示成我們想要的樣子。

2.自訂基礎功能介紹

自訂格式的程式碼分為四個區段，區段之間以半角分號（;）隔開，每個區段的程式碼作用於不同類型的數值：

正數顯示格式; 負數顯示格式;零值顯示格式; 文字顯示格式

此外，利用自訂格式讓字元顯示顏色也很簡單，只要在自訂程式碼中加上[紅色]等字樣就能使字元顯示對應的顏色。顏色可以顯示以下幾種：[黑色]、[綠色]、[白色]、[藍色]、[洋紅色]、[黃色]、[藍綠色]、[紅色]。

3.自訂格式主要應用場景

#3.1百分比的增幅顯示

在如下圖所示的表格中，最後一列資料正數負數標示不同顏色，以上下箭頭代表正負符號。

自訂格式代碼為：

#[紅色]↑0%;[綠色]↓0%;0%;

解析：分號隔開的四個區域，第一個區域表示正數區域，顯示紅色；第二個是負數區域顯示綠色，0是數值佔位符，顯示數值本身，後面加上%即百分比顯示；第三個區域是零值顯示格式，不填顏色則預設為黑色；最後一個區域是文本，表格中沒有文本，則什麼都不填。

3.2新增文字顯示

如下圖所示的表格中，想把第一列的數字設定為第二列的顯示格式。

通常大家會使用文本連接符號&來連接文本，這裡我們直接用自訂格式代碼設定為：第0部分

解析：0表示數字的佔位符，前後加上文字即可。

如下所示，如果第一列的數字是中文數字，則需要設定為依照第二列格式顯示。

則自訂格式設定為：第@部分

##解析：@表示文字的佔位符。

3.3數字顯示為中文

如果我們需要把如下圖所示的第1列數字顯示為第2列和第3列的中文樣式，一般會直接在輸入的時候輸入中文，當然，自訂格式也能很方便地幫你實現這個功能。

中文小寫自訂格式程式碼設定為：

# [DBNUM1]

中文大寫自訂格式代碼設定為： [DBNUM2]

#3.4數字分段顯示

在工作中我們也會需要將不同長度的數值分段顯示，如下所示：

#分段顯示的自訂格式代碼設定為：

0000 0000 0000

解析：使用0進行佔位，加上空格，則為分段顯示。

分段顯示前面不加0的自訂格式程式碼設定為：

##??00 0000 0000

解析：如果我們不想讓數值最前面自動加0，同時又想讓數值對齊顯示的話，可以用?代替前面顯示的0，這裡？的個數取決於前面顯示0的最大個數。

不過由於EXCEL中能完全顯示數字的為11位以內的數字；數值位數大於11位不超過15位，EXCEL顯示為9.03525E 13這種科學計數法，可以使用上方的自定義格式代碼，使其完全顯示；超過15的數字將顯示為0，所以這裡使用的方法就不適用於超過15位的數值。超過15位的數值我們只能修改格式為文字使其全部顯示。

3.5日期時間顯示

如下圖所示的表格中，需要將第一列的日期和時間顯示為第二列的格式。

上方表格中自訂格式程式碼設定分別為：

第一個：[DBNum1]yyyy"年"m"月"d"日" aaaa

第二：d/mmm/yy,dddd

第三個：今天是aaaa

第四個：上午/下午  h"點"m "分"s"秒"

第五個：h:m:s  am/pm

#第六個：[m]分s秒

解析：這裡面的英文字母分別是年、月、日、小時、分鐘、秒鐘的英文首字母。其中字母的個數則決定了顯示格式的字元數。

在日期格式裡，yy表示18，而yyyy則表示2018；m和mm表示以數值顯示的月份7和07，mmm和mmmm表示英文顯示的月份Jul（簡稱）和July（全名）；同理d和dd表示以數值顯示的日期8和08，ddd和dddd表示英文顯示的星期Sun（簡稱）和Sunday（全名）。

而時間格式裡，m如果需要表示分鐘，就需要跟在h小時後面一起顯示，或者加上[ ] 表示從當天零點開始累積到現在的分鐘數, 意味著把小時也換算成分鐘。

若需要表示中文星期幾，則需要用字母a, aaaa顯示的是星期日，而aaa顯示的只有日。

4.注意的問題

自訂格式需要注意的是它並不會改變儲存格本身的格式，只改變它的顯示方式。

如下所示的表格中，C列的大寫中文數字是由A列數字設定自訂格式代碼轉換而來，我們來試試C列的中文數字是否可以求和呢？

在D列輸入公式：

SUM($C$30:C30)，雙擊向下填充，可以看到累積求和結果如下。

由此可得，雖然C列單元格顯示為中文大寫，但實際上仍是數值，可以進行數學運算。

當然如果需要讓自訂格式轉換為實際的數值格式，我們可以使用剪貼簿複製，然後將剪貼簿裡的資料複製到儲存格。

或複製到word或txt文件裡再貼上回儲存格也可以完成格式的轉換。

好了，EXCEL的自訂格式就全部介紹完啦

相關學習推薦：

excel教學#

以上是實用Excel技巧分享：神奇的「自訂格式」功能的詳細內容。更多資訊請關注PHP中文網其他相關文章！