計(jì)算機(jī)2003Excel技巧之自定義數(shù)字格式

1、數(shù)字格式是單元格格式中最有用的功能之一，專門用于對單元格數(shù)值進(jìn)行格式化。單擊菜單“格式”“單元格”，或者按<Ctrl+1>組合鍵，用戶可以在“單元格格式”對話框的“數(shù)字”選項(xiàng)卡中看到有關(guān)數(shù)字格式的各項(xiàng)設(shè)置，如圖68-1所示。通過選擇不同的格式設(shè)置，甚至應(yīng)用自定義的格式，能夠讓單元格的顯示更加符合它的表達(dá)目標(biāo)。圖681 “單元格格式”對話框中的“數(shù)字”選項(xiàng)卡注意無論為單元格應(yīng)用了何種數(shù)字格式，都只會改變單元格的顯示形式，而不會改變單元存儲的真正內(nèi)容。反之，用戶在工作表上看到的單元格內(nèi)容，并不一定是其真正的內(nèi)容，而可能是原始內(nèi)容經(jīng)過各種變化后的一種表現(xiàn)形式。如果用戶需要在改變格式的同時(shí)

2、也改變實(shí)際內(nèi)容，需要借助TEXT或其他函數(shù)來實(shí)現(xiàn)。 681  Excel內(nèi)置的數(shù)字格式Excel內(nèi)置的數(shù)字格式多種多樣，能夠滿足用戶在一般情況下的需要。下面用幾個(gè)例子來說明同樣的數(shù)字被設(shè)置為不同的數(shù)字格式后，顯示效果會有哪些改變。假設(shè)一個(gè)單元格中有數(shù)字1023.4，在默認(rèn)情況下，Excel不對單元格設(shè)置任何數(shù)字格式，此時(shí)的格式名稱為“常規(guī)”，數(shù)值按照它的真實(shí)面貌顯示出來。用戶在“單元格格式”對話框的“數(shù)字”選項(xiàng)卡中可以看到一個(gè)“示例”項(xiàng)目，它可以在用戶單擊“確定”按鈕以前就顯示當(dāng)前單元格的數(shù)值在應(yīng)用了指定的數(shù)字格式以后的樣式，如圖682所示。圖682 “常規(guī)”數(shù)字格式就是沒有任何格式

3、在“分類”列表框中選擇“數(shù)值”項(xiàng)，然后勾選“使用千位分隔符”復(fù)選框，示例內(nèi)容會發(fā)生改變，如圖683所示。圖683 “數(shù)值”數(shù)字格式在“分類”列表框中選擇“貨幣”項(xiàng)，然后在“貨幣符號（國家/地區(qū)）”列表框中選擇“￥中文（中國）”，則示例內(nèi)容將再次發(fā)生改變，如圖684所示。圖684 “貨幣”數(shù)字格式以下是在“分類”列表框中選擇“百分比”項(xiàng)后的示例內(nèi)容，如圖685所示。圖685 “百分比”數(shù)字格式在“分類”列表框選擇“分?jǐn)?shù)”項(xiàng)，示例內(nèi)容中的數(shù)值以分?jǐn)?shù)形式顯示，如圖68-6所示。圖686 “分?jǐn)?shù)”數(shù)字格式“分類”列表框中還有許多其他項(xiàng)目，每個(gè)項(xiàng)目中又有多個(gè)參數(shù)可選，讀者可以自己進(jìn)行嘗試，以找到符合需要

4、的最佳格式。682  創(chuàng)建自定義數(shù)字格式如果Excel內(nèi)置的數(shù)字格式無法滿足用戶在實(shí)際工作中的需求，還可以創(chuàng)建自定義數(shù)字格式。在“單元格格式”對話框中，選擇“分類”為“自定義”，如圖687所示。圖687  自定義數(shù)字格式在“類型”文本框中輸入自定義的數(shù)字格式代碼，或者修改原有格式代碼。單擊“確定”即完成。在“類型”下方的列表框中，已經(jīng)有許多的格式代碼，這些代碼就是Excel內(nèi)置的數(shù)字格式所對應(yīng)的格式代碼，或是由用戶成功創(chuàng)建的自定義數(shù)字格式的格式代碼。如果用戶先在“分類”列表框中選定一個(gè)內(nèi)置的數(shù)字格式，然后再選定“自定義”項(xiàng)，就能夠在“類型”文本框中看到與之對應(yīng)的格式代碼。在

5、原有格式代碼的基礎(chǔ)上進(jìn)行修改，能夠更快速地得到自己的自定義格式代碼。683  自定義數(shù)字格式的代碼組成規(guī)則許多Excel用戶可能不了解自定義數(shù)字格式能夠讓他們幾乎隨心所欲地顯示單元格數(shù)值，或者因?yàn)楹ε旅鎸﹂L長的格式代碼而放棄這個(gè)有用的工具。而實(shí)際上，自定義數(shù)字格式代碼并沒有想像中那么復(fù)雜和困難，只要掌握了它的規(guī)則，就很容易讀懂和書寫格式代碼來創(chuàng)建自定義數(shù)字格式。自定義格式代碼可以為4種類型的數(shù)值指定不同的格式：正數(shù)、負(fù)數(shù)、零值和文本。在代碼中，用分號來分隔不同的區(qū)段，每個(gè)區(qū)段的代碼作用于不同類型的數(shù)值。完整格式代碼的組成結(jié)構(gòu)為：“大于條件值”格式;“小于條件值”格式;“等于條件值”格

6、式;文本格式在沒有特別指定條件值的時(shí)候，默認(rèn)的條件值為0，因此，格式代碼的組成結(jié)構(gòu)也可視作：正數(shù)格式;負(fù)數(shù)格式;零值格式;文本格式用戶并不需要每次都嚴(yán)格按照4個(gè)區(qū)段來編寫格式代碼，只寫1個(gè)或2個(gè)區(qū)段也是可以的。表681中列出了沒有按4區(qū)段寫代碼時(shí)，代碼結(jié)構(gòu)的變化。表681                           &#

7、160;          自定義數(shù)據(jù)格式代碼結(jié)構(gòu)規(guī)則區(qū)  段   數(shù)代 碼 結(jié) 構(gòu)1格式代碼作用于所有類型的數(shù)值2第1區(qū)段作用于正數(shù)和零值，第二區(qū)段作用于負(fù)數(shù)3第1區(qū)段作用于正數(shù)，第二區(qū)段作用于負(fù)數(shù)，第三區(qū)段作用于零值下面有一個(gè)自定義數(shù)字格式代碼的例子，它針對4種不同類型的數(shù)值定義了不同的格式：#,#0.00_;紅色 #,#0.00;綠色G/通用格式;"“""”"圖688是Excel中應(yīng)用了這種數(shù)字格式的單元格的顯示。圖688

8、0; 自定義數(shù)字格式的單元格的顯示684  常用自定義數(shù)字格式的代碼與示例表682                                  常用自定義數(shù)字格式的代碼與示例代    碼注釋與示例G/通用格式不設(shè)置任何格式，

9、按原始輸入的數(shù)值顯示#數(shù)字占位符，只顯示有效數(shù)字，不顯示無意義的零值0數(shù)字占位符，當(dāng)數(shù)字比代碼的數(shù)量少時(shí)，顯示無意義的0從上圖可見，可以利用代碼0來讓數(shù)值顯示前導(dǎo)零，并讓數(shù)值固定按指定位數(shù)顯示。下圖是使用#與0組合為最常用的帶小數(shù)的數(shù)字格式?數(shù)字占位符，需要的時(shí)候在小數(shù)點(diǎn)兩側(cè)增加空格；也可以用于具有不同位數(shù)的分?jǐn)?shù).小數(shù)點(diǎn)%百分?jǐn)?shù),千位分隔符E科學(xué)計(jì)數(shù)符號顯示格式里的下一個(gè)字符續(xù)表代    碼注釋與示例*重復(fù)下一個(gè)字符來填充列寬_留出與下一個(gè)字符等寬的空格利用這種格式可以很容易地將正負(fù)數(shù)對齊“文本”顯示雙引號里面的文本文本占位符，如果只使用單個(gè)，作用是引用原始文本如

10、果使用多個(gè)，則可以重復(fù)文本顏色顏色代碼顏色可以是black黑色、white白色、red紅色、cyan青色 、blue藍(lán)色、yellow黃色、magenta紫紅色或green綠色要注意的是，在英文版用英文代碼，在中文版則必須用中文代碼顏色 n顯示Excel調(diào)色板上的顏色，n是056之間的一個(gè)數(shù)值條件值設(shè)置格式的條件有關(guān)日期與時(shí)間方面的自定義格式方法，請參閱技巧69。日期與時(shí)間是用戶在Excel中經(jīng)常需要處理的一類數(shù)值，在不同的報(bào)告中，會使用不同的日期格式來表示它們。在默認(rèn)情況下，當(dāng)用戶在單元格中輸入日期或時(shí)間的時(shí)候，Excel會使用系統(tǒng)短日期格式來顯示。更改系統(tǒng)日期格式設(shè)置的方法如下。單擊Win

11、dows的開始菜單“設(shè)置”“控制面板”。在控制面板”對話框中雙擊“區(qū)域和語言選項(xiàng)”圖標(biāo)。在“區(qū)域和語言選項(xiàng)”對話框中單擊“自定義”按鈕，然后可以分別對系統(tǒng)日期格式與時(shí)間格式進(jìn)行設(shè)置，如圖691所示。圖691  系統(tǒng)日期與時(shí)間格式的設(shè)置注意在這里的更改，會影響到多個(gè)應(yīng)用程序而不僅僅是Excel。 Excel也內(nèi)置了許多日期與時(shí)間類的格式，在“單元格格式”對話框的“數(shù)字”選項(xiàng)卡中，用戶可以在“分類”列表框中選擇“日期”或“時(shí)間”項(xiàng)下面的某種格式并應(yīng)用它們?nèi)鐖D69-2所示。用戶還可以自定義日期與時(shí)間類的數(shù)字格式，只是格式代碼會簡單很多。表691介紹了用于定義日期與時(shí)間的代碼，關(guān)于如何創(chuàng)建自

12、定義數(shù)字格式，請參閱682小節(jié)。圖692  Excel內(nèi)置的日期與時(shí)間格式表691                                  日期與時(shí)間類自定義數(shù)字格式代碼代    碼注   

13、 釋m使用沒有前導(dǎo)零的數(shù)字來顯示月份（112）mm使用有前導(dǎo)零的數(shù)字來顯示月份（0112）mmm使用英文縮寫來顯示月份（JanDec）mmmm使用英文全稱來顯示月份（JanuaryDecember）mmmmm顯示月份的英文首字母（JD）d使用沒有前導(dǎo)零的數(shù)字來顯示日期（131）dd使用有前導(dǎo)零的數(shù)字來顯示日期（0131）ddd使用英文縮寫來顯示星期幾（SunSat）dddd使用英文全稱來顯示星期幾（SundaySaturday）aaaa使用中文來顯示星期幾（星期一星期日）aaa使用中文顯示星期幾（一日），不顯示“星期”兩字yy使用兩位數(shù)顯示年份（0099）yyyy使用4位數(shù)顯示年份（19009

14、999）h使用沒有前導(dǎo)零的數(shù)字來顯示小時(shí)（023）hh使用有前導(dǎo)零的數(shù)字來顯示小時(shí)（0023）m使用沒有前導(dǎo)零的數(shù)字來顯示分鐘（059）mm使用有前導(dǎo)零的數(shù)字來顯示分鐘（0059）s使用沒有前導(dǎo)零的數(shù)字來顯示秒鐘（059）ss使用有前導(dǎo)零的數(shù)字來顯示秒鐘（0059） 顯示超出進(jìn)制的時(shí)間（如大于24的小時(shí)數(shù)或大于60的分與秒）AM/PM上午/下午使用12小時(shí)制顯示小時(shí)圖69-3中用上述代碼創(chuàng)建了一些常用的實(shí)例，其中的“原始數(shù)值”是日期或時(shí)間的序列值。圖693  日期/時(shí)間自定義格式實(shí)例在本技巧中，讀者可以學(xué)習(xí)到一些自定義數(shù)字格式的經(jīng)典實(shí)例。有關(guān)自定義數(shù)字格式的創(chuàng)建與應(yīng)用方法，請參閱技巧

15、68。701  零值不顯示單擊菜單“工具”“選項(xiàng)”，在“選項(xiàng)”對話框的“視圖”中，如果取消勾選“零值”復(fù)選框，Excel將不顯示當(dāng)前工作表中的零值。利用自定義數(shù)字格式，用戶不但能夠?qū)崿F(xiàn)同樣的效果，而且能夠讓工作表中的一部分單元格不顯示零值而其他的單元格仍然顯示零值。G/通用格式;G/通用格式;上面的格式代碼中，第3區(qū)段留空，這樣就可以使零值顯示為空白。當(dāng)然，對應(yīng)于正數(shù)與負(fù)數(shù)的第1、2區(qū)段，也可以另行定義格式。702  快速放縮數(shù)值許多用戶在工作中常常需要處理很大的數(shù)字，利用下面的自定義數(shù)字格式，能夠在不改變數(shù)值本身的同時(shí)，把它們進(jìn)行縮放，如圖701所示。圖701 

16、 使用自定義數(shù)字格式縮放數(shù)值703  智能顯示百分比下面的自定義數(shù)字格式只讓小于1的數(shù)字按“百分比”格式顯示，大于等于1的數(shù)字使用標(biāo)準(zhǔn)格式顯示，同時(shí)還讓所有的數(shù)字排列整齊，如圖702所示。圖702  智能顯示百分比格式代碼：<10.00%;#.00_%這段代碼有兩個(gè)區(qū)段，第1個(gè)區(qū)段使用了一個(gè)判斷，對應(yīng)數(shù)值小于1時(shí)的格式，第2個(gè)區(qū)段則對應(yīng)不小于1時(shí)的格式。在第2個(gè)區(qū)段中，百分號前使用了一個(gè)下劃線，目的是保留一個(gè)與百分號等寬的空格。704  顯示分?jǐn)?shù)Excel內(nèi)置了一些分?jǐn)?shù)的格式，用戶還可以使用自定義數(shù)字格式得到更多分?jǐn)?shù)的表示方法，比如在顯示的時(shí)候加上“又”字、

17、加上表示單位的符號，或者使用一個(gè)任意的數(shù)字作為分母，如圖703所示。圖703  更多顯示分?jǐn)?shù)的自定義數(shù)字格式有關(guān)分?jǐn)?shù)的其他信息，請參閱技巧54。705  隱藏某些類型的內(nèi)容可以使用自定義數(shù)字格式隱藏某些類型的輸入內(nèi)容，或者把某些類型的輸入內(nèi)容用特定的內(nèi)容來替換，如圖704所示。圖704  使用自定義數(shù)字格式隱藏某種類型的數(shù)值在技巧68中談到過：無論為單元格應(yīng)用了何種數(shù)字格式，都只會改變單元格的顯示內(nèi)容，而不會改變單元存儲的真正內(nèi)容。因此，Excel沒有提供直接的方法來讓用戶得到自定義數(shù)字格式的顯示值。如果復(fù)制一個(gè)有格式的單元格到另一個(gè)單元格，用戶只能得到原始數(shù)值。但是，仍有多種技巧可以幫助用戶達(dá)到這一目的，下面介紹一種最簡單有效的方法。選定應(yīng)用了數(shù)字格式的單元格或單元格區(qū)域?？焖俚剡B續(xù)兩次按<Ctrl+C>組合鍵，Office剪貼板出現(xiàn)在任務(wù)窗