原來Excel自定義格式可以這樣玩

圖文 | 安偉星 來源 | 精進Excel

設置單元格格式在Excel中應該來說是一種司空見慣的操作,幾乎任何接觸到Excel的人都會用到這一功能,但是能把單元格格式(特別是自定義格式)用好、用高級的人卻鳳毛麟角。

靈活利用 Excel 自定義格式,我們可以完成一些看起來非常神奇、非常高級的效果,比如:

①條件格式

②巧加單位

③輸入提速

……

這麼好用的工具,多數人因為害怕長長的格式代碼而放棄。事實上,自定義格式代碼並沒有想像中那麼困難,只要掌握了它的規則,任何人都可以很容易讀懂和書寫格式代碼。

接下來星爺就由淺入深,從0到1詳細講解 Excel 單元格自定義格式關鍵技術,相信看完本篇教程,你會驚呼:原來Excel自定義格式可以這樣玩!

001 自定義格式概述

01 調出單元格格式對話框

選中需要設置格式的單元格,按「CTRL+1」快捷鍵打開「設置單元格格式」對話框。

在對話框的第一個分區「數字」中,左側的「分類」列表中有常規、數值、貨幣等項目,這些項目時預設的格式,根據提示很容易掌握。

我們重點要學習的就是最後一項「自定義」格式,選擇「自定義」格式後,在右側的「類型」編輯框,可以輸入自定義格式代碼。同時,可以看到下面有很多系統預設的代碼。

我們可以直接使用這些預設的代碼,也可以根據代碼的規則,寫出更加符合實際需求的代碼。

需要特別說明的是:「自定義數字格式」並不會改變數值本身,只改變數值的顯示方式(事實上所有類型的單元格格式都只是改變了數值的顯示方式)。

如果需要在改變格式的同時也改變實際內容,需要藉助TEXT或其他函數來實現。

02 自定義格式區段

自定義格式代碼,共有四個區段,在代碼中,用分號來分隔不同的區段,每個區段的代碼作用於不同類型的數值。完整格式代碼的組成結構為:

在沒有特別指定條件值的時候,默認的條件值為0,因此,默認情況下格式代碼的四個區段對用的對象是這樣的:

正數格式;負數格式;零值格式;文本格式

實際使用中,自定義格式代碼的四個區段不一定全部使用,這四個區段使用一部分的情況,代碼的結構如下表所示:

002 自定義格式基礎字元

自定義格式代碼是由佔位符構成的,各種各樣的佔位符就像是自定義格式中通用語言一樣,Excel能很好地識別有佔位符組成的語言。因此,掌握了佔位符就相當於掌握了自定義格式代碼。

輸入格式代碼的方法:

①「CTRL+1」快捷鍵打開「設置單元格格式」

②在數字分區→「自定義」→「類型」,在類型編輯框,即可輸入格式代碼,如圖輸入的格式代碼為:#,##0

另外,在示例處可以時時預覽格式的效果。下文所講的所有字元都是按照上述方式進行填寫,就不在贅述。

01、"G/通用格式"

注釋:以常規的數字顯示,相當於"分類"列表中的"常規"選項。

代碼:G/通用格式。

效果:52 顯示為 52;52.5 顯示為 52.5

02、 "#"

注釋:數字佔位符。只顯有意義的零而不顯示無意義的零。小數點後數字如大於"#"的數量,則按"#"的位數四捨五入。

代碼:###.##

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

03、"0"

注釋:數字佔位符。如果單元格的內容大於佔位符,則顯示實際數字,如果小於點位符的數量,則用0補足。

代碼:00000。

效果:5201314 顯示為 5201314;521 顯示為 00521

(5201314的位數大於佔位符的位數,因此顯示實際數字;520的位數小於佔位符位數,因此用0補足)

用0補足的情況,不能改變原有數字的大小,因此整數部分0補足在前面,小數部分,0補足在後面。

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

從上圖可見,可以利用代碼0來讓數值顯示前導零,並讓數值固定按指定位數顯示。下圖是使用#與0組合為最常用的帶小數的數字格式。

04、"?"

注釋:數字佔位符。在小數點兩邊為無意義的零添加空格,以便當按固定寬度時,小數點可對齊;也可以用於具有不同位數的分數。

05、"@"

注釋:文本佔位符。如果只使用單個@, 作用是引用原始文本;如果使用多個@, 則可以重複文本。

注意:@符號的位置決定了Excel輸入的數字數據相對於添加文本的位置

06、"*"

注釋:重複下一次字元,直到充滿列寬

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

07、","

注釋:千位分隔符

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

08、""

注釋:用文本格式顯示接下來的字元。"文本":顯示雙引號裡面的文本;"":用文本格式顯示接下來的字元。和 "" 用途相同都是顯示輸入的文本, 且輸入後會自動轉變為雙引號表達。

09、[]

注釋:條件格式代碼。可以將條件或者顏色(顏色代碼也是一種條件)寫入[],從而實現自定義條件。

(默認情況下自定義格式的四個區段的條件是固定的,即分別為"正數格式;負數格式;零值格式;文本格式",使用[]可以自行設置每個區段的條件)

可以根據單元格內容判斷後再設置格式。條件格式化只限於使用三個條件,其中兩個條件是明確的,另個是「所有的其他」。

10、顏色

注釋:用指定的顏色顯示字元。可有八種顏色可選:紅色、黑色、黃色、綠色、白色、藍色、青色和洋紅。

代碼:[藍色];[紅色];[黃色];[綠色]

效果:顯示結果為正數為藍色, 負數顯示紅色, 零顯示黃色, 文本則顯示為綠色

註:顏色代碼的另一種方式

[顏色N]:是調用調色板中顏色, N 是 0~56 之間的整數。

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

效果:顯示結果為正數為調色板上第1種顏色……

11、時間和日期代碼

"YYYY"或"YY":按四位(1900~9999)或兩位(00~99)顯示年

"MM"或"M":以兩位(01~12)或一位(1~12)表示月。

"DD"或"D":以兩位(01~31)或一位(1-31)來表示天。

12、「_"

注釋:在數字格式中創建N個字元寬的空格

13、空

注釋:表示隱藏

最後,這些代碼可以進行組合使用,從而創建出更加複雜的自定義格式。

003 典型應用

01、巧顯示單位

如果將數值和單元寫在同一個單元格中,那麼數值將變為無法參與計算的文本形式,這在Excel數據表中是不允許的,如果確實需要顯示單位,可以這樣處理。

選中需要設置單位的數值列→「Ctrl+1」組合鍵→「自定義」→「類型」,在原有的「G/通用格式」後面加上單位即可,如圖所示,所需要顯示的單位為「台」。

這樣設置之後,單元格中的顯示為「數值+單位」,但是實際上單元格中只有數字,格式為數值格式,可以參與數學運算,如圖所示。

02、顏色分明

如圖我們希望評價各個指標的達成情況,對於達成率>=1的指標,使用「綠色」顯示;達成率>=0.95的指標,使用「黃色」指標顯示;低於0.95的用紅色顯示。

選中所需的單元格區域,自定義格式代碼中寫入代碼

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

則各個指標會根據設置的代碼顯示對於的顏色。

03、輸入提速

當在想工作表中錄入數據時,如果遇到大量重複錄入的情況,我們可以將一部分數據作為固定值進行顯示。

比如我們編寫代碼:

「微軟股份有限公司@」

現在,單擊剛才選中的單元格區域中一個單元格,並在其中輸入「銷售部」幾個字,按下回車鍵後,這個單元格中出現的是「微軟股份有限公司銷售部」。

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

04、條件格式

在Excel 自定義數字格式中用戶可以進行條件格式的設置。當單元格中數字滿足指定的條件時,Excel 可以自動將條件格式應用於單元格。

可以根據單元格內容判斷後再設置格式。條件格式化只限於使用三個條件,其中兩個條件是明確的,另個是「所有的其他」。

05、顯示為密文

在Excel 工作表中,有時為了表格的美觀或者別的因素,我們希望將單元格中的數值隱藏起來,這時我們使用「;;;」(三個分號)的自定義數字格式就可達到此目的。

註:這樣單元格中的值只會在編輯欄出現,並且被隱藏單元格中的數值還不會被列印出來,但是該單元格中的數值可以被其他單元格正常引用。

item.jd.com/12125533.ht (二維碼自動識別)


推薦閱讀:

開始Python的新手教程
用Python預測NBA常規賽結果
利用XGboost簡單粗暴zillow競賽25%
《R語言實戰》第7章 筆記
胡說八道互聯網,他讓馬雲又愛又恨

TAG:MicrosoftExcel | MicrosoftOffice | 数据分析 |