Board logo

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

作者: 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/)