麻辣家族討論版版 - Powered by Discuz! Board

標題: [分享] 儲存格自訂格式 [打印本頁]

作者: Hsieh 時間: 2011-11-4 16:04 標題: 儲存格自訂格式

儲存格自訂格式是經常被問到的問題
以下為一般自訂格式的規則提供大家分享
本文源自http://www.51testing.com/?uid-72607-action-viewspace-itemid-155054
翻譯成繁體後供大家參考
儲存格格式的自訂詳解
1、”G/通用格式”：以常規的數字顯示，相當於”分類”清單中的”常規”選項。
例：代碼：”G/通用格式”。10顯示為10；10.1顯示為10.1。
2、 “#”：數字預留位置。只顯有意義的零而不顯示無意義的零。小數點後數字如大於”#”的數量，則按”#”的位數四捨五入。
例：代碼：”###.##”,12.1顯示為12.10;12.1263顯示為：12.13
3、”0”：數字預留位置。如果儲存格的內容大於預留位置，則顯示實際數位，如果小於點位元符的數量，則用0補足。
例：代碼：”00000”。1234567顯示為1234567；123顯示為00123
代碼：”00.000”。100.14顯示為100.140；1.1顯示為01.100
4、”@”：文本預留位置，如果只使用單個@，作用是引用原始文本，
要在輸入數位資料之後自動添加文本，使用自訂格式為：”文本內容”@；要在輸入數位資料之前自動添加文本，使用自訂格式為：@”文本內容”。@符號的位置決定了Excel輸入的數位資料相對于添加文本的位置。
如果使用多個@，則可以重複文本。
例：代碼”；；；"集團"@"部"“，財務顯示為：集團財務部
代碼”；；；@@@“，財務顯示為：財務財務財務
5、”*”：重複下一次字元，直到充滿列寬。
例：代碼：”@*-”。”ABC”顯示為”ABC-------------------”
可就用於模擬密碼保護：代碼”**;**;**;**”，123顯示為：************
6、”，”：千位分隔符號
例：代碼” #,###“，12000顯示為：12,000
7、\:用這種格式顯示下一個字元。"文本"，顯示雙引號裡面的文本。
“\”：顯示下一個字元。和“”””用途相同都是顯示輸入的文本，且輸入後會自動轉變為雙引號表達。
例：代碼"人民幣 "#,##0,,"百萬"，與\人民幣 #,##0,,\百萬，
輸入1234567890顯示為:人民幣 1,235百萬
8、“？”：數字預留位置。在小數點兩邊為無意義的零添加空格，以便當按固定寬度時，小數點可對齊，另外還用於對不等到長數位的分數
例：分別設置單元格格式為”??.??”和”???.???”,對齊結果如下：
輸入12.1212 顯示12.12 12.121
9、顏色：用指定的顏色顯示字元。可有八種顏色可選：紅色、黑色、黃色，綠色、白色、藍色、青色和洋紅。
例：代碼：“[青色];[紅色];[黃色];[藍色]”。顯示結果為正數為青色，負數顯示紅色，零顯示黃色，文本則顯示為藍色
[顏色N]：是調用調色板中顏色，N是0~56之間的整數。
例：代碼：“[顏色3]”。儲存格顯示的顏色為調色板上第3種顏色。、
10、條件：可以儲存格內容判斷後再設置格式。條件格式化只限於使用三個條件，其中兩個條件是明確的，另個是“所有的其他”。條件要放到方括號中。必須進行簡單的比較。
例：代碼：“[>0]”正數”；[=0];”零”；負數”。顯示結果是儲存格數值大於零顯示正數，等於0顯示零，小於零顯示“負數”。
11、 “！”：顯示“"”。由於引號是代碼常用的符號。在儲存格中是無法用"""來顯示出來“"”。要想顯示出來，須在前加入“！”
例：代碼：“#!"”。“10”顯示“10"”
代碼：“#!"!"”。“10”顯示“10""”
12、時間和日期代碼常用日期和時間代碼
“YYYY”或“YY”：按四位（1900~9999）或兩位（00~99）顯示年
“MM”或“M”：以兩位（01~12）或一位（1~12）表示月。
“DD”或“D”：以兩位（01~31）或一位（1-31）來表示天。
例：代碼：“YYYY-MM-DD”。2005年1月10日顯示為：“2005-01-10”

excel儲存格自訂格式1
代碼結構組成代碼碼分為四個部分，中間用“；”號分隔，具體如下：
正數格式；負數格式；零格式；文本格式
各個參數的涵義
“G/通用格式”：以常規的數字顯示，相當於“分類”清單中的“常規”選項。
例：代碼：“G/通用格式”。10顯示為10；10.1顯示為10.1。

“0”：數字預留位置。如果儲存格的內容大於預留位置，則顯示實際數位，如果小於點位元符的數量，則用0補足
例：代碼：“00000”。1234567顯示為1234567；123顯示為00123
代碼：“00.000”。100.14顯示為100.140；1.1顯示為01.100
“#”：數字預留位置。只顯有意義的零而不顯示無意義的零。小數點後數字如大於“#”的數量，則按“#”的位數四捨五入。
例：代碼：“###.##”,12.1顯示為12.10;12.1263顯示為：12.13
“？”：數字預留位置。在小數點兩邊為無意義的零添加空格，以便當按固定寬度時，小數點可對齊，另外還用於對不等到長數位的分數
例：分別設置單元格格式為“??.??“和“???.???”,對齊結果如下：
例：設置儲存格自訂格式“# ??/???”。“1.25”顯示為“1 1/4”
“.”：小數點。如果外加雙引號則為字元。
例：代碼“0.#”。“11.23”顯示為“11.2”
“%”：百分比。
例：代碼“#%”。“0.1”顯示為“10%”
“，”：千位分隔符號。數位使用千位元分隔符號。如時在代碼中“，”後空，則把原來的數字縮小1000倍。
例：代碼：“#，###”。“10000”顯示為“10，000”
代碼：“#，”。10000顯示為10。
代碼：“#，，”。“1000000”顯示為“1”。
“/”：顯示下一個字元。和“”””用途相同都是顯是輸入的文本，且輸入後會自動轉變為雙引號表達。不同的“/”是顯後面的文本，雙引號是顯示雙引中間的文本。
例：代碼“\ABC”或“”ABC””。均顯示為“ABC”
“*”：重複下一次字元，直到充滿列寬。
例：代碼：“@*-”。“ABC”顯示為“ABC-------------------”
“_”（底線）：留下一個和下一個字元同等寬度的空格“文本”：顯示雙引號中的文本。
例：代碼：“”中國”@”顯示為“中國河南”
[顏色]：用指定的顏色顯示字元。可有八種顏色可選：紅色、黑色、黃色，綠色、白色、藍色、青色和洋紅。
例：代碼：“[青色];[紅色];[黃色];[藍色]”。顯示結果為正數為青色，負數顯示紅色，零顯示黃色，文
本則顯示為藍色
[顏色N]：是調用調色板中顏色，N是0~56之間的整數。
例：代碼：“[顏色3]”。儲存格顯示的顏色為調色板上第3種顏色。
[條件]：可以儲存格內容判斷後再設置格式。條件格式化只限於使用三個條件，其中兩個條件是明確的，另個是“所有的其他”。條件要放到方括號中。必須進行簡單的比較。
例：代碼：“[>0]”正數”；[=0];”零”；負數”。顯示結果是儲存格數值大於零顯示正數，等於0顯示零，小於零顯示“負數”。
“！”：顯示“"”。由於引號是代碼常用的符號。在儲存格中是無法用"""來顯示出來“"”。要想顯示出來，須在前加入“！”
例：代碼：“#!"”。“10”顯示“10"”
代碼：“#!"!"”。“10”顯示“10""” 2.1.1.2 時間和日期代碼常用日期和時間代碼
“YYYY”或“YY”：按四位（1900~9999）或兩位（00~99）顯示年
“MM”或“M”：以兩位（01~12）或一位（1~12）表示月。
“DD”或“D”：以兩位（01~31）或一位（1-31）來表示天。
例：代碼：“YYYY-MM-DD”。2005年1月10日顯示為：“2005-01-10”
代碼：“YY-M-D”。2005年10月10日顯示為：“05-1-10”
“AAAA”：日期顯示為星期。
“H”或“HH”：以一位（0~23）或兩位（01~23）顯示小時
“M”或“MM”：以一位（0~59）或兩位（01~59）顯示分鐘
“S”或“SS”：以一位（0~59）或兩位（01~59）顯示秒
例：代碼：“HH:MM:SS”。“23:1:15”顯示為“23:01:15”
[H]或[M]或[SS]：顯示大於24小時的小時或顯示大於60的分或秒。
下面介紹幾個常遇到的實例
把12345顯示為1.2
代碼：“0.,”
設置千元顯示且四捨五入保留兩位元小數要求：把“12345”顯示為：12.35
代碼：“#.00,”
在數位中任意插入字元要求：把“20050512”設置為：2005-05-12
代碼：“0000-00-00”
在文本或數位前添加字元要求：“鄭州市”顯示為“河南省鄭州市”
代碼：“"河南省"@”
要求：“1035010121”顯示為“手機號碼1034010121”
代碼：“"手機號碼"@”或“"手機號碼"#”
另外日期格式的顯示方法
"e":顯示四位元年份，2003版本有效。
"bbbb":顯示四位元佛歷年份，即以西元前543年為紀年元年，對1900年以後的日期有效。
"mmm":顯示英文月份的簡稱。
"mmmm":顯示英文月份的全稱。
"ddd":顯示英文星期幾的簡稱。
"dddd":顯示英文星期幾的全稱。
關於特殊數位的顯示
中文小寫數位 [DBNum1][$-804]G/通用格式
例：代碼：“[DBNum1][$-804]G/通用格式”。“1”顯示為“一”
代碼：“[DBNum1][$-804]G/通用格式”。“13”顯示為“一十三”
中文小寫數位 [DBNum2][$-804]G/通用格式
例：代碼：“[DBNum2][$-804]G/通用格式”。“1”顯示為“壹”
代碼：“[DBNum2][$-804]G/通用格式”。“13”顯示為“壹拾三”
中文小寫數位 [DBNum3][$-804]G/通用格式
例：代碼：“[DBNum3][$-804]G/通用格式”。“123”顯示為“1百2十3”
特殊說明
因為參數的特殊性，所以自訂的參數也是有關鍵字的。如函數=TEXT(A1,"b0000")就會顯示錯誤。因為“b”就是保留的關鍵字，在自訂格式輸入“b”系統就會自動填入“bb”。bb就是佛歷年份，即以西元前543年為紀年元年，對1900年以後的日期有效。“bbbb”就是四位佛歷年份。要解決=TEXT(A1,"b0000")的錯誤問題，需要這樣定義函數=TEXT(A1,"""b""0000")。在自訂格式中定義就是“"b"0000”。其它的關鍵字自己體會如：“d”、“e”............

儲存格自訂格式2
在Excel中自訂數位格式
雖然Excel為使用者提供了大量的數位格式，但還是有許多用戶因為工作、學習方面的特殊要求，需要使用一些Excel未提供的數字格式，這時我們就需要利用Excel的自訂數位格式功能來説明實現用戶的這些特殊要求。
一、在Excel中創建自訂數字格式的方法
1、選擇要設置格式的儲存格或儲存格區域。
2、按一下“格式”功能表中的“儲存格”命令，然後按一下“數位”選項卡。
3、在“分類”列表中，按一下“自訂”選項。
4、在“類型”框中，編輯數位格式代碼以創建所需的格式。
在Excel自訂數位格式使用如下的表達方式：

正數的格式負數的格式零的格式文本的格式
#,##0.00 [Red]-#,##0.00 0.00 "TEXT"@

在Excel自訂數位格式的格式代碼中，用戶最多可以指定四個節；每個節之間用分號進行分隔，這四個節順序定義了格式中的正數、負數、零和文本。如果使用者在表達方式中只指定兩個節，則第一部分用於表示正數和零，第二部分用於表示負數。如果使用者在表達方式中只指定了一個節，那麼所有數位都會使用該格式。如果在表達方式中要跳過某一節，則對該節僅使用分號即可。
二、自訂數位格式實例
(一)自動添加文本
（1）要在輸入資料之後自動添加文本，使用自訂格式為：@＂文本內容＂；要在輸入資料之前自動添加文本，使用自訂格式為：＂文本內容＂@。@符號的位置決定了Excel輸入的數位資料相對于添加文本的位置，雙引號得用英文狀態下的。
實例一：
在學生姓名前添加“初二（2）班”字樣，就可以創建：
"初二（2）班"@
（2）在輸入數位之後自動添加文本。
實例二：
如在日常財務工作中，常常需要在金額數位後加單位“元”，這時就可以使用：
0.00"元"
或
0"元"
(二)在自訂數位格式中使用顏色
要設置格式中某一部分的顏色，只要在該部分對應位置用方括號鍵入顏色名稱或顏色編號即可。Excel中可以使用的顏色名稱有[黑色]、[藍色]、[青色]、[綠色]、[洋紅]、[紅色]、[白色]、[黃色]八種不同的顏色，此外Excel還可以使用[顏色X]的方式來設置顏色，其中X為1-56之間的數字，代表了56種不同的顏色。
例如：當使用者需要將儲存格中的負數數位用藍色來表示，只要使用“#,##0.00;[藍色]-#,##0.00”自訂數字格式，使用者在儲存格中錄入負數時，Excel就會將數位以藍色顯示。
(三)在自訂數位格式中使用條件格式
在Excel自訂數位格式中使用者可以進行條件格式的設置。當儲存格中數位滿足指定的條件時，Excel可以自動將條件格式應用於儲存格。Excel自訂數位格式中可以使用如下六種標準的比較運算子：

運算子含義
= 等於
> 大於
< 小於
>= 大於等於
<= 小於等於
<> 不等於

例如：在學生成績工作表中，當我們想以紅色字體顯示大於等於90分的成績，以藍色字體顯示小於60分的成績時，其餘的成績則以黑色字體顯示，這時只需將自訂數字格式設置為“[紅色][>=90];[藍色][<60];[黑色]”即可。
值得注意的是，當你在以後需要繼續使用剛才所創建的成績條件自訂數位格式時，你會發現在“單元格格式”的“自訂”分類類型中找不到“[紅色][>=90];[藍色][<60];[黑色]”格式，這是因為Excel自動將你所創建的“[紅色][>=90];[藍色][<60]”格式修改成“[[紅色][>=90]G/通用格式;[藍色][<60]G/通用格式;[黑色]G/通用格式”，你只需選擇此格式即可達到同樣的使用效果。
實例一：
在工人獎金列表中，當我們想以小於50的顯示“低”，50至100間顯示“中等”，大於100的顯示“高”。這時只需將自訂數位格式設置為：
[<50]"低";[>100]"高";"中等"
實例二：
把“數學”、“語文”成績中90分以上替換成“優”：
[>=90]"優"
(四)隱藏儲存格中的數值
在Excel工作表中，有時為了表格的美觀或者別的因素，我們希望將儲存格中的數值隱藏起來，這時我們使用“;;;”(三個分號)的自訂數字格式就可達到此目的。這樣儲存格中的值只會在編輯欄出現，並且被隱藏儲存格中的數值還不會被列印出來,但是該儲存格中的數值可以被其他儲存格正常引用。在Excel中要想設置滿足指定條件數位的格式，在自訂數位格式代碼中必須加入帶中括弧的條件，條件由比較運算子和數值兩部分組成。

作者: HUNGCHILIN 時間: 2011-11-5 01:49

http://forum.twbts.com/viewthrea ... amp;extra=#pid28194
http://forum.twbts.com/viewthrea ... amp;extra=#pid28193
http://forum.twbts.com/thread-5029-1-1.html

作者: bhsm 時間: 2011-11-5 09:34

感謝版主詳細的說明,否則每次都要試做看看

作者: aer 時間: 2012-7-6 13:22

找了很久的週幾的格式設定，現在終於找到了，謝謝分享

作者: man65boy 時間: 2012-8-23 21:35

回復 1# Hsieh
感謝版主無私的分享，收下寶貴的知識了，謝謝!

作者: simon0712 時間: 2012-10-22 17:06

雖然眼睛有點花了，但是仔細看，其實發現很多問題好像都能夠釐清。謝謝版大！

作者: kuoliang123 時間: 2012-10-23 01:12

難得看到一篇文字格式完整的分享，感謝分享~~~

作者: aaaaa388 時間: 2012-12-9 15:42

剛好須要,感謝版大

作者: keovin790916 時間: 2012-12-9 16:10

回復 1# Hsieh

詢問，如何複製儲存格內容但是不會覆蓋過原本的函數

作者: Hsieh 時間: 2012-12-9 17:16

回復 9# keovin790916

選擇性貼上格式
或用工具列的黃刷子刷過即可

作者: mead 時間: 2012-12-10 08:18

感謝版大分享蠻實用的

作者: gerald2 時間: 2012-12-16 10:25

又學到了一些東西
快來試試看
感謝感謝

作者: sunbao2k 時間: 2012-12-21 11:43

真是齊全,收起來慢慢研究好了,謝謝囉

作者: pichice 時間: 2013-8-6 13:18

又學到了很多基礎的應用...
在很多需要的時候非常好用

作者: abc9gad2016 時間: 2016-5-14 16:08

請問大大，小弟看完教學後，嘗試想讓字體若負數顯示紅色、正數顯示藍色
自訂儲存格卻怎麼也沒辦法設定兩種同時顯示，請問應該要如何輸入呢?
[attach]24284[/attach]

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)