Excel單元格自定義格式一

一、代碼結(jié)構(gòu)組成

結(jié)構(gòu)組成分為四個部分，中間用“;”號分隔，具體如下：【正數(shù)格式;負數(shù)格式;零格式;文本格式】

二、各個參數(shù)的涵義

1、【G/通用格式】：以常規(guī)的數(shù)字顯示，相當于“分類”列表中的“常規(guī)”選項。

例：

• 代碼：【G/通用格式】10顯示為10;10.1顯示為10.1。

2、“0”：數(shù)字占位符。如果單元格的內(nèi)容大于占位符，則顯示實際數(shù)字，如果小于點位符的數(shù)量，則用0補足。

例：

• 代碼：【00000】。1234567顯示為1234567;123顯示為00123
• 代碼：【00.000】。100.14顯示為100.140;1.1顯示為01.100

3、“#”：數(shù)字占位符。只顯有意義的零而不顯示無意義的零。小數(shù)點后數(shù)字如大于“#”的數(shù)量，則按“#”的位數(shù)四舍五入。

例：

• 代碼：【###.##】,12.1顯示為12.10;12.1263顯示為：12.13

4、“?”：數(shù)字占位符。在小數(shù)點兩邊為無意義的零添加空格，以便當按固定寬度時，小數(shù)點可對齊，另外還用于對不等到長數(shù)字的分數(shù)。

例：

• 代碼：分別設(shè)置單元格格式為【??.??】和【???.???】,對齊結(jié)果為以小數(shù)點對齊。
• 代碼：設(shè)置單元格自定義格式【# ??/???】。“1.25”顯示為數(shù)學上的1又4分之1的寫法。

5、“.”：小數(shù)點。如果外加雙引號則為字符。

例：

• 代碼：【0.#】“11.23”顯示為“11.2”

6、“%”：百分比。

例：

• 代碼：【#%】“0.1”顯示為“10%”

7、“，”：千位分隔符。數(shù)字使用千位分隔符。如時在代碼中“，”后空，則把原來的數(shù)字縮小1000倍。

例：

• 代碼：【#，###】“10000”顯示為“10，000”
• 代碼：【#，】10000顯示為10。
• 代碼：【#，，】“1000000”顯示為“1”。

8、”@”：文本占位符。如果只使用單個@，作用是引用原始文本，要在輸入數(shù)字數(shù)據(jù)之前自動添加文本，使用自定義格式為："文本內(nèi)容"@;要在輸入數(shù)字數(shù)據(jù)之后自動添加文本，使用自定義格式為：@"文本內(nèi)容"。@符號的位置決定了Excel輸入的數(shù)字數(shù)據(jù)相對于添加文本的位置。如果使用多個@，則可以重復文本。

例：

• 代碼：【集團"@"部】財務(wù) 顯示為：集團財務(wù)部
• 代碼：【@@@】財務(wù) 顯示為：財務(wù)財務(wù)財務(wù)

9、“/”：顯示下一個字符。和“”””用途相同都是顯是輸入的文本，且輸入后會自動轉(zhuǎn)變?yōu)殡p引號表達。不同的“/”是顯后面的文本，雙引號是顯示雙引中間的文本。

例：

• 代碼：【\ABC】或【”ABC”】均顯示為“ABC”

10、“*”：重復下一次字符，直到充滿列寬。

例：

• 代碼：【@*-】“ABC”顯示為“ABC-------------------”

11、“_”(下劃線)：留下一個和下一個字符同等寬度的空格“文本”：顯示雙引號中的文本。

例：

• 代碼：【”中國”@】單元格內(nèi)容 河南顯示為“中國河南”

12、[顏色]：用指定的顏色顯示字符。可有八種顏色可選：紅色、黑色、黃色，綠色、白色、蘭色、青色和洋紅。

例：

• 代碼：【[青色];[紅色];[黃色];[蘭色]】顯示結(jié)果為正數(shù)為青色，負數(shù)顯示紅色，零顯示黃色，文本則顯示為蘭色

13、[顏色N]：是調(diào)用調(diào)色板中顏色，N是0~56之間的整數(shù)。

例：

• 代碼：【[顏色3]】單元格顯示的顏色為調(diào)色板上第3種顏色。

14、[條件]：可以單元格內(nèi)容判斷后再設(shè)置格式。條件格式化只限于使用三個條件，其中兩個條件是明確的，另個是“所有的其他”。條件要放到方括號中。必須進行簡單的比較。

例：

• 代碼：【[>0]”正數(shù)”;[=0];”零”;負數(shù)】顯示結(jié)果是單元格數(shù)值大于零顯示正數(shù)，等于0顯示零，小于零顯示“負數(shù)”。

15、“!”：顯示“"”。由于引號是代碼常用的符號。在單元格中是無法用"""來顯示出來“"”。要想顯示出來，須在前加入“!”

例：

• 代碼：【#!"】“10”顯示“10"”
• 代碼：【#!"!"】“10”顯示“10""”

16、時間和日期代碼

代碼：

• 【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”
• 或：顯示大于24小時的小時或顯示大于60的分或秒。

三、下面介紹幾個常遇到的實例

例1：把12345顯示為1.2

代碼：【0.,】

例2：設(shè)置千元顯示且四舍五入保留兩位小數(shù)要求：把“12345”顯示為：12.35

代碼：【#.00,】

例3：在數(shù)字中任意插入字符要求：把“20050512”設(shè)置為：2005-05-12

代碼：【0000-00-00】

例4：在文本或數(shù)字前添加字符要求：“鄭州市”顯示為“河南省鄭州市”

代碼：【"河南省"@】

例5：要求：“1035010121”顯示為“手機號碼1034010121”

代碼：【"手機號碼"@】或【"手機號碼"#】

四、關(guān)于特殊數(shù)字的顯示

中文小寫數(shù)字 [DBNum1][$-804]G/通用格式

例：

• 代碼：【[DBNum1][$-804]G/通用格式】。“1”顯示為“一”
• 代碼：【[DBNum1][$-804]G/通用格式】。“13”顯示為“一十三”

中文小寫數(shù)字 [DBNum2][$-804]G/通用格式

例：

• 代碼：【[DBNum2][$-804]G/通用格式】。“1”顯示為“壹”
• 代碼：【[DBNum2][$-804]G/通用格式】。“13”顯示為“壹拾叁”

中文小寫數(shù)字 [DBNum3][$-804]G/通用格式

例：

• 代碼：【[DBNum3][$-804]G/通用格式】。“123”顯示為“1百2十3”

五、特殊說明

因為參數(shù)的特殊性，所以自定義的參數(shù)也是有關(guān)鍵字的。

如函數(shù)=TEXT(A1,"b0000")就會顯示錯誤。因為“b”就是保留的關(guān)鍵字，在自定義格式輸入“b”系統(tǒng)就會自動填入“bb”。

bb就是佛歷年份，即以公元前543年為紀年元年，對1900年以后的日期有效。“bbbb”就是四位佛歷年份。

要解決=TEXT(A1,"b0000")的錯誤問題，需要這樣定義函數(shù)=TEXT(A1,"""b""0000")，在自定義格式中定義就是“"b"0000”。

其它的關(guān)鍵字自己體會如：“d”、“e”............

Excel單元格自定義格式二

在Excel中自定義數(shù)字格式

雖然Excel為用戶提供了大量的數(shù)字格式，但還是有許多用戶因為工作、學習方面的特殊要求，需要使用一些Excel未提供的數(shù)字格式，這時我們就需要利用Excel的自定義數(shù)字格式功能來幫助實現(xiàn)用戶的這些特殊要求。

一、在Excel中創(chuàng)建自定義數(shù)字格式的方法

• 1、選擇要設(shè)置格式的單元格或單元格區(qū)域。
• 2、單擊“格式”菜單中的“單元格”命令，然后單擊“數(shù)字”選項卡。
• 3、在“分類”列表中，單擊“自定義”選項。
• 4、在“類型”框中，編輯數(shù)字格式代碼以創(chuàng)建所需的格式。

在Excel自定義數(shù)字格式的格式代碼中，用戶最多可以指定四個節(jié);每個節(jié)之間用分號進行分隔，這四個節(jié)順序定義了格式中的正數(shù)、負數(shù)、零和文本。

如果用戶在表達方式中只指定兩個節(jié)，則第一部分用于表示正數(shù)和零，第二部分用于表示負數(shù)。

如果用戶在表達方式中只指定了一個節(jié)，那么所有數(shù)字都會使用該格式。如果在表達方式中要跳過某一節(jié)，則對該節(jié)僅使用分號即可。

二、自定義數(shù)字格式實例

(一)自動添加文本

(1)要在輸入數(shù)據(jù)之后自動添加文本，使用自定義格式為：@"文本內(nèi)容";要在輸入數(shù)據(jù)之前自動添加文本，使用自定義格式為："文本內(nèi)容"@。@符號的位置決定了Excel輸入的數(shù)字數(shù)據(jù)相對于添加文本的位置，雙引號得用英文狀態(tài)下的。

實例一：

• 在學生姓名前添加“初二(2)班”字樣，就可以創(chuàng)建："初二(2)班"@

(2)在輸入數(shù)字之后自動添加文本。

實例二：

• 如在日常財務(wù)工作中，常常需要在金額數(shù)字后加單位“元”，這時就可以使用：0.00"元"或0"元"

(二)在自定義數(shù)字格式中使用顏色

要設(shè)置格式中某一部分的顏色，只要在該部分對應位置用方括號鍵入顏色名稱或顏色編號即可。Excel中可以使用的顏色名稱有[黑色]、[藍色]、[青色]、[綠色]、[洋紅]、[紅色]、[白色]、[黃色]八種不同的顏色，此外Excel還可以使用[顏色X]的方式來設(shè)置顏色，其中X為1-56之間的數(shù)字，代表了56種不同的顏色。

例如：

• 當用戶需要將單元格中的負數(shù)數(shù)字用藍色來表示，只要使用“#,##0.00;[藍色]-#,##0.00”自定義數(shù)字格式，用戶在單元格中錄入負數(shù)時，Excel就會將數(shù)字以藍色顯示。

(三)在自定義數(shù)字格式中使用條件格式

在Excel自定義數(shù)字格式中用戶可以進行條件格式的設(shè)置。當單元格中數(shù)字滿足指定的條件時，Excel可以自動將條件格式應用于單元格。

Excel自定義數(shù)字格式中可以使用如下六種標準的比較運算符：

例如：

• 在學生成績工作表中，當我們想以紅色字體顯示大于等于90分的成績，以藍色字體顯示小于60分的成績時，其余的成績則以黑色字體顯示，這時只需將自定義數(shù)字格式設(shè)置為“[紅色][>=90];[藍色][<60];[黑色]”即可。

值得注意的是，當你在以后需要繼續(xù)使用剛才所創(chuàng)建的成績條件自定義數(shù)字格式時，你會發(fā)現(xiàn)在“單元格格式”的“自定義”分類類型中找不到“[紅色][>=90];[藍色][<60];[黑色]”格式，這是因為Excel自動將你所創(chuàng)建的“[紅色][>=90];[藍色][<60]”格式修改成“[[紅色][>=90]G/通用格式;[藍色][<60]G/通用格式;[黑色]G/通用格式”，你只需選擇此格式即可達到同樣的使用效果。

實例一：

在工人獎金列表中，當我們想以小于50的顯示“低”，50至100間顯示“中等”，大于100的顯示“高”。這時只需將自定義數(shù)字格式設(shè)置為：【[<50]"低";[>100]"高";"中等"】

實例二：

把“數(shù)學”、“語文”成績中90分以上替換成“優(yōu)”：【[>=90]"優(yōu)"】

(四)隱藏單元格中的數(shù)值

在Excel工作表中，有時為了表格的美觀或者別的因素，我們希望將單元格中的數(shù)值隱藏起來，這時我們使用“;;;”(三個分號)的自定義數(shù)字格式就可達到此目的。這樣單元格中的值只會在編輯欄出現(xiàn)，并且被隱藏單元格中的數(shù)值還不會被打印出來,但是該單元格中的數(shù)值可以被其他單元格正常引用。在Excel中要想設(shè)置滿足指定條件數(shù)字的格式，在自定義數(shù)字格式代碼中必須加入帶中括號的條件，條件由比較運算符和數(shù)值兩部分組成。

Excel單元格自定義格式三

一、自定義格式主要說兩點

• ①創(chuàng)建或刪除自定義數(shù)字格式;
• ②條件格式，因為這一部分在Excel的幫助中的解釋已非常清楚，就將幫助的內(nèi)容復制于此。

二、創(chuàng)建自定義數(shù)字格式

• ① 選擇要設(shè)置格式的單元格。
• ② 在“格式”菜單上，單擊“單元格”，再單擊“數(shù)字”選項卡。
• ③ 在“分類”列表中，單擊與所需格式相似的某個分類，再設(shè)置與所需格式相似的內(nèi)置格式(不必更改內(nèi)置格式，可以創(chuàng)建一個自定義的備份)。
• ④ 在“分類”列表中，單擊“自定義”。
• ⑤ 在“類型”框中，編輯數(shù)字格式代碼以創(chuàng)建所需的格式。

在格式代碼中最多可以指定四個節(jié)。這些格式代碼是以分號分隔的，它們順序定義了格式中的正數(shù)、負數(shù)、零和文本。如果只指定兩個節(jié)，則第一部分用于表示正數(shù)和零，第二部分用于表示負數(shù)。如果只指定一個節(jié)，則該節(jié)用于所有的數(shù)字。如果要跳過某一節(jié)，則對該節(jié)僅使用分號即可。

可以使用格式代碼來描述如何顯示以下內(nèi)容：數(shù)字、日期或時間、貨幣、百分比或科學記數(shù)以及文本或空格。

三、格式代碼

(一)顯示文本和數(shù)字

若要在單元格中同時顯示文本和數(shù)字，可將文本字符括在雙引號 ("") 中，或在單個字符前加反斜線 (\)。應將字符放在格式代碼的合適部分中。

例如：

• 鍵入格式“$0.00 "剩余";$-0.00 "短缺"”來顯示一個“$125.74 剩余”的正值和一個“$-125.74短缺”的負值。下面的字符不用引號：$、-、+、/、( )、:、!、^、&、’(左單引號)、’(右單引號)、~、{ }、=、<、>和空格符。

包括文本輸入部分 如果包含文本部分，則文本部分總是數(shù)字格式的最后一個部分。若要在數(shù)字格式中包括文本部分，請在要顯示輸入單元格中的文本的地方加入符號 (@)，如果文本部分中沒有 @，所輸入文本將不會顯示出來。如果要一直顯示某些帶有輸入文本的指定文本字符，則應將附加文本用雙引號 ("") 括起來。

例如："grossreceipts for"@。

如果格式中不包含文本部分，那么輸入的文本將不受格式代碼的影響。

(二)添加空格

若要在數(shù)字格式中創(chuàng)建一個字符寬的空格，請在字符前加上一條下劃線 (_)。

例如：在下劃線后跟上一個右括號 (_))，可使正數(shù)和括號內(nèi)的負數(shù)對齊。

(三)重復的字符

在數(shù)字格式代碼中使用星號 (*)，可使星號之后的字符填充整個列寬。

例如：鍵入 0*- 可在數(shù)字后包含足夠的短劃線以填充整個單元格。

(四) 小數(shù)點位置和有效數(shù)字

若要設(shè)置分數(shù)或帶小數(shù)點的數(shù)字的格式，請在格式代碼的相應部分中包含下面的數(shù)字位置標識符。如果某一數(shù)字小數(shù)點右側(cè)的位數(shù)大于所設(shè)定格式中位置標識符的位數(shù)，該數(shù)字將按位置標識符位數(shù)進行舍入。如果數(shù)字小數(shù)點左側(cè)的位數(shù)大于位置標識符的位數(shù)，那么多余的位數(shù)也會顯示出來。如果所設(shè)定的格式中小數(shù)點左側(cè)只有一個數(shù)字符號 (#)，那么小于 1 的數(shù)字將以小數(shù)點開始。

# 只顯示有意義的數(shù)字而不顯示無意義的零。

如果數(shù)字位數(shù)少于格式中的零的個數(shù)，則 0(零)將顯示無意義的零。

? 在小數(shù)點兩邊為無意義的零添加空格，以便當按固定寬度字體設(shè)置格式(例如Courier New)時，小數(shù)點可對齊。還可對具有不等長數(shù)字的分數(shù)使用 ?。

(五)顏色

若要設(shè)置格式中某一部分的顏色，請在該部分對應位置用方括號鍵入[黑色]、[藍色]、[青色]、[綠色]、[洋紅]、[紅色]、[白色]、[黃色]八種顏色名稱之一，此外Excel還可以使用[顏色X]的方式來設(shè)置顏色，其中X為1-56之間的數(shù)字，代表了56種不同的顏色。顏色代碼必須為該部分的第一項。

(六)條件

若要設(shè)置滿足指定條件的數(shù)字的格式，格式代碼中應加入帶中括號的條件。條件由比較運算符和數(shù)值兩部分組成。

例如，下面的格式以紅色字體顯示小于等于 100 的數(shù)字，而以藍色字體顯示大于100 的數(shù)字。

[紅色][<=100];[藍色][>100]

若要對單元格應用條件格式(例如：取決于單元格值的顏色陰影)，請使用“格式”菜單上的“條件格式”命令。

(七)千位分隔符

在數(shù)字格式中包含逗號，可使逗號顯示為千位分隔符，或?qū)��?shù)字放大一千倍。

(八) 貨幣符號

若要在數(shù)字格式中輸入下面的貨幣符號之一，請先打開 Num Lock（數(shù)字鎖定指示燈，小鍵盤區(qū)域指示燈），然后使用數(shù)字鍵盤輸入貨幣符號的 ANSI 代碼。

￡ 英鎊、￥ 人民幣、€ 歐元、$ 美元、￠ 歐元和美元貨幣單位中的“分”。