圖文 | 安偉星 來(lái)源 | 精進(jìn)Excel

設(shè)置單元格格式在Excel中應(yīng)該來(lái)說(shuō)是一種司空見(jiàn)慣的操作，幾乎任何接觸到Excel的人都會(huì)用到這一功能，但是能把單元格格式（特別是自定義格式）用好、用高級(jí)的人卻鳳毛麟角。

靈活利用 Excel 自定義格式，我們可以完成一些看起來(lái)非常神奇、非常高級(jí)的效果，比如：

①條件格式

②巧加單位

③輸入提速

……

這么好用的工具，多數(shù)人因?yàn)楹ε麻L(zhǎng)長(zhǎng)的格式代碼而放棄。事實(shí)上，自定義格式代碼并沒(méi)有想像中那么困難，只要掌握了它的規(guī)則，任何人都可以很容易讀懂和書(shū)寫(xiě)格式代碼。

接下來(lái)星爺就由淺入深，從0到1詳細(xì)講解 Excel 單元格自定義格式關(guān)鍵技術(shù)，相信看完本篇教程，你會(huì)驚呼：原來(lái)Excel自定義格式可以這樣玩！

001 自定義格式概述

01 調(diào)出單元格格式對(duì)話框

選中需要設(shè)置格式的單元格，按「CTRL+1」快捷鍵打開(kāi)「設(shè)置單元格格式」對(duì)話框。

在對(duì)話框的第一個(gè)分區(qū)「數(shù)字」中，左側(cè)的「分類」列表中有常規(guī)、數(shù)值、貨幣等項(xiàng)目，這些項(xiàng)目時(shí)預(yù)設(shè)的格式，根據(jù)提示很容易掌握。

我們重點(diǎn)要學(xué)習(xí)的就是最后一項(xiàng)「自定義」格式，選擇「自定義」格式后，在右側(cè)的「類型」編輯框，可以輸入自定義格式代碼。同時(shí)，可以看到下面有很多系統(tǒng)預(yù)設(shè)的代碼。

我們可以直接使用這些預(yù)設(shè)的代碼，也可以根據(jù)代碼的規(guī)則，寫(xiě)出更加符合實(shí)際需求的代碼。

需要特別說(shuō)明的是：「自定義數(shù)字格式」并不會(huì)改變數(shù)值本身，只改變數(shù)值的顯示方式（事實(shí)上所有類型的單元格格式都只是改變了數(shù)值的顯示方式）。

如果需要在改變格式的同時(shí)也改變實(shí)際內(nèi)容，需要借助TEXT或其他函數(shù)來(lái)實(shí)現(xiàn)。

02 自定義格式區(qū)段

自定義格式代碼，共有四個(gè)區(qū)段，在代碼中，用分號(hào)來(lái)分隔不同的區(qū)段，每個(gè)區(qū)段的代碼作用于不同類型的數(shù)值。完整格式代碼的組成結(jié)構(gòu)為：

在沒(méi)有特別指定條件值的時(shí)候，默認(rèn)的條件值為0，因此，默認(rèn)情況下格式代碼的四個(gè)區(qū)段對(duì)用的對(duì)象是這樣的：

正數(shù)格式;負(fù)數(shù)格式;零值格式;文本格式

實(shí)際使用中，自定義格式代碼的四個(gè)區(qū)段不一定全部使用，這四個(gè)區(qū)段使用一部分的情況，代碼的結(jié)構(gòu)如下表所示：

002 自定義格式基礎(chǔ)字符

自定義格式代碼是由占位符構(gòu)成的，各種各樣的占位符就像是自定義格式中通用語(yǔ)言一樣，Excel能很好地識(shí)別有占位符組成的語(yǔ)言。因此，掌握了占位符就相當(dāng)于掌握了自定義格式代碼。

輸入格式代碼的方法：

①「CTRL+1」快捷鍵打開(kāi)「設(shè)置單元格格式」

②在數(shù)字分區(qū)→「自定義」→「類型」，在類型編輯框，即可輸入格式代碼，如圖輸入的格式代碼為：#,##0

另外，在示例處可以時(shí)時(shí)預(yù)覽格式的效果。下文所講的所有字符都是按照上述方式進(jìn)行填寫(xiě)，就不在贅述。

01、"G/通用格式"

注釋：以常規(guī)的數(shù)字顯示,相當(dāng)于"分類"列表中的"常規(guī)"選項(xiàng)。

代碼：G/通用格式。

效果：52 顯示為 52；52.5 顯示為 52.5

02、 "#"

注釋：數(shù)字占位符。只顯有意義的零而不顯示無(wú)意義的零。小數(shù)點(diǎn)后數(shù)字如大于"#"的數(shù)量,則按"#"的位數(shù)四舍五入。

代碼：###.##

效果：181.2 顯示為 181.2, 18.4328 顯示為 18.43

03、"0"

注釋：數(shù)字占位符。如果單元格的內(nèi)容大于占位符,則顯示實(shí)際數(shù)字,如果小于點(diǎn)位符的數(shù)量,則用0補(bǔ)足。

代碼：00000。

效果：5201314 顯示為 5201314；521 顯示為 00521

（5201314的位數(shù)大于占位符的位數(shù)，因此顯示實(shí)際數(shù)字；520的位數(shù)小于占位符位數(shù)，因此用0補(bǔ)足）

用0補(bǔ)足的情況，不能改變?cè)袛?shù)字的大小，因此整數(shù)部分0補(bǔ)足在前面，小數(shù)部分，0補(bǔ)足在后面。

比如：代碼00.000。520.13顯示為 520.130, 5.2顯示為05.200

從上圖可見(jiàn)，可以利用代碼0來(lái)讓數(shù)值顯示前導(dǎo)零，并讓數(shù)值固定按指定位數(shù)顯示。下圖是使用#與0組合為最常用的帶小數(shù)的數(shù)字格式。

04、"?"

注釋：數(shù)字占位符。在小數(shù)點(diǎn)兩邊為無(wú)意義的零添加空格,以便當(dāng)按固定寬度時(shí),小數(shù)點(diǎn)可對(duì)齊；也可以用于具有不同位數(shù)的分?jǐn)?shù)。

05、"@"

注釋：文本占位符。如果只使用單個(gè)@, 作用是引用原始文本；如果使用多個(gè)@, 則可以重復(fù)文本。

注意：@符號(hào)的位置決定了Excel輸入的數(shù)字?jǐn)?shù)據(jù)相對(duì)于添加文本的位置

06、"*"

注釋：重復(fù)下一次字符,直到充滿列寬

代碼：@*-。123 顯示為 123-------------------

07、","

注釋：千位分隔符

代碼 #,### 。12000 顯示為 12,000

08、""

注釋：用文本格式顯示接下來(lái)的字符。"文本"：顯示雙引號(hào)里面的文本；""：用文本格式顯示接下來(lái)的字符。和 "" 用途相同都是顯示輸入的文本, 且輸入后會(huì)自動(dòng)轉(zhuǎn)變?yōu)殡p引號(hào)表達(dá)。

09、[]

注釋：條件格式代碼?？梢詫l件或者顏色（顏色代碼也是一種條件）寫(xiě)入[]，從而實(shí)現(xiàn)自定義條件。

（默認(rèn)情況下自定義格式的四個(gè)區(qū)段的條件是固定的，即分別為"正數(shù)格式;負(fù)數(shù)格式;零值格式;文本格式",使用[]可以自行設(shè)置每個(gè)區(qū)段的條件）

可以根據(jù)單元格內(nèi)容判斷后再設(shè)置格式。條件格式化只限于使用三個(gè)條件，其中兩個(gè)條件是明確的，另個(gè)是“所有的其他”。

10、顏色

注釋：用指定的顏色顯示字符。可有八種顏色可選：紅色、黑色、黃色、綠色、白色、藍(lán)色、青色和洋紅。

代碼：[藍(lán)色];[紅色];[黃色];[綠色]

效果：顯示結(jié)果為正數(shù)為藍(lán)色, 負(fù)數(shù)顯示紅色, 零顯示黃色, 文本則顯示為綠色

注：顏色代碼的另一種方式

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

代碼：[顏色1];[顏色2];[顏色3];[顏色4]

效果：顯示結(jié)果為正數(shù)為調(diào)色板上第1種顏色……

11、時(shí)間和日期代碼

"YYYY"或"YY"：按四位（1900~9999）或兩位（00~99）顯示年

"MM"或"M"：以兩位（01~12）或一位（1~12）表示月。

"DD"或"D"：以兩位（01~31）或一位（1-31）來(lái)表示天。

12、“_"

注釋：在數(shù)字格式中創(chuàng)建N個(gè)字符寬的空格

13、空

注釋：表示隱藏

最后，這些代碼可以進(jìn)行組合使用，從而創(chuàng)建出更加復(fù)雜的自定義格式。

003 典型應(yīng)用

01、巧顯示單位

如果將數(shù)值和單元寫(xiě)在同一個(gè)單元格中，那么數(shù)值將變?yōu)闊o(wú)法參與計(jì)算的文本形式，這在Excel數(shù)據(jù)表中是不允許的，如果確實(shí)需要顯示單位，可以這樣處理。

選中需要設(shè)置單位的數(shù)值列→“Ctrl+1”組合鍵→“自定義”→“類型”，在原有的“G/通用格式”后面加上單位即可，如圖所示，所需要顯示的單位為“臺(tái)”。

這樣設(shè)置之后，單元格中的顯示為“數(shù)值+單位”，但是實(shí)際上單元格中只有數(shù)字，格式為數(shù)值格式，可以參與數(shù)學(xué)運(yùn)算，如圖所示。

02、顏色分明

如圖我們希望評(píng)價(jià)各個(gè)指標(biāo)的達(dá)成情況，對(duì)于達(dá)成率>=1的指標(biāo)，使用“綠色”顯示；達(dá)成率>=0.95的指標(biāo)，使用“黃色”指標(biāo)顯示；低于0.95的用紅色顯示。

選中所需的單元格區(qū)域，自定義格式代碼中寫(xiě)入代碼

[綠色][>=1];[黃色][>=0.95];[紅色]

則各個(gè)指標(biāo)會(huì)根據(jù)設(shè)置的代碼顯示對(duì)于的顏色。

03、輸入提速

當(dāng)在想工作表中錄入數(shù)據(jù)時(shí)，如果遇到大量重復(fù)錄入的情況，我們可以將一部分?jǐn)?shù)據(jù)作為固定值進(jìn)行顯示。

比如我們編寫(xiě)代碼：

“微軟股份有限公司@”

現(xiàn)在，單擊剛才選中的單元格區(qū)域中一個(gè)單元格，并在其中輸入“銷售部”幾個(gè)字，按下回車鍵后，這個(gè)單元格中出現(xiàn)的是“微軟股份有限公司銷售部”。

這樣就能大幅提高輸入的效率。

04、條件格式

在Excel 自定義數(shù)字格式中用戶可以進(jìn)行條件格式的設(shè)置。當(dāng)單元格中數(shù)字滿足指定的條件時(shí)，Excel 可以自動(dòng)將條件格式應(yīng)用于單元格。

可以根據(jù)單元格內(nèi)容判斷后再設(shè)置格式。條件格式化只限于使用三個(gè)條件，其中兩個(gè)條件是明確的，另個(gè)是“所有的其他”。

05、顯示為密文

在Excel 工作表中，有時(shí)為了表格的美觀或者別的因素，我們希望將單元格中的數(shù)值隱藏起來(lái)，這時(shí)我們使用“;;;”(三個(gè)分號(hào))的自定義數(shù)字格式就可達(dá)到此目的。

注：這樣單元格中的值只會(huì)在編輯欄出現(xiàn)，并且被隱藏單元格中的數(shù)值還不會(huì)被打印出來(lái),但是該單元格中的數(shù)值可以被其他單元格正常引用。

關(guān)注「精進(jìn)Excel」，如果任意點(diǎn)開(kāi)三篇，沒(méi)有你想要的知識(shí)，算我耍流氓。