七夕專題:自定義格式--不管我的容顏如何變幻,愛你的心至死不渝!
親愛的姑娘,不管我的容顏如何變幻,但愛你的心至死不渝!
--------- 龍逸凡
上面圖片中十六個單元格,內容不一,有阿拉伯數字,漢字大寫的數字,百分點時間、日期、星期,還有肉麻的情詩,大家可能會覺得奇怪,這與今天的文章有何關係?
實際上,上面圖片單元格中的值都是同一個值:42975,那為什麼能顯示出不同的內容呢?
這就用到了Excel中一個常用且重要的功能:自定義格式。
從廣義上來講,Excel中的數據類型分為二類:數值、文本。朋友們可能會說,不是還有日期、時間、百分比等數據類型嗎?實際上,這些都是數值型。我們可以在Excel中同時按下Ctrl和分號,輸入系統今天的日期(「2017/8/25」),然後複製它,將其「選擇性粘貼-數值」到一個空白單元格,粘貼後的值就是42972。
或者,也可以在單元格中輸入數字42972,點擊右鍵-設置單元格格式,將單元格設置為日期格式,剛才的數字就會變為「2017/8/25」。(如果輸入1,設置為日期格式就是1900/1/1)
同樣的,我們可通過設置單元格格式,將其設置為貨幣、時間、百分比、分數、大寫金額等等。如下圖:
上面都是系統預先自定義好的一些格式,我們還可根據需要,按照自定義格式的規則,打造自己的格式,比如題圖中的那些示例。
註:
由於自定義格式較為複雜,在本文中不展開,大家可參考文章最後附的文章或《「偷懶」的技術:打造財務Excel達人》中的第三章。
在Excel中,實際值和顯示值兩者可以是分離的,可以是不一樣的,比如上圖中的C17、C18單元格,使用自定義格式將42975顯示為12345678、99999,甚至顯示為C12:C16單元格區域的文本。這樣就解答了新手們常問的幾個問題:
1、為什麼我點擊千位分隔符樣式,將下圖I列的值,設置為添加千位分隔符且只顯示二位小數後,我在其他地方編製公式="金額"&I4&「萬元",會顯示「金額3580.2188萬元」,而不是我要的
」金額3,580.22萬元「
2、為什麼在下圖中編製公式:
=D2&"凈利潤"&C18&"萬元"
希望顯示為「2017年1月凈利潤67.30萬元」,卻顯示為「42766凈利潤67.3011萬元」??
可以將上面的公式改為一下,將其顯示為「2017年1月凈利潤67.30萬元」:
=TEXT(D2,"yyyy年m月")&"凈利潤"&TEXT(C18,"#,###.00")&"萬元"
對新手來講,這一點似乎不可理解。
要理解這一點的話,我們記住下面這一句話就是了:
數據是臉,格式是面具,同一張臉,可以戴不同的面具,不管面具是怎麼樣,但臉還是那張臉。
七夕情人節快到了,套用一句應景的情詩,就是文前的那句話:
不管我的容顏如何變幻,但愛你的心至死不渝!海枯石爛不變心!
-------附幾則自定義格式的技巧------------------------
技巧2:數字格式中的智多星——自動設置滿足指定條件的數字格式。
在格式代碼中加入帶中括弧的條件,條件由比較運算符和數值兩部分組成。例如,下面的格式以紅色字體顯示小於和等於 100 的數字,而以藍色字體顯示大於 100 的數字:
[紅色][<=100];[藍色][>100]
龍逸凡注:此功能有點類以於條件格式。但此條件僅限於由比較運算符和數值兩部分組成
(此類運算符包括:= 等於、> 大於、< 小於、>= 大於等於、<=小於等於和 <> 不等於)。而條件格式的適用範圍更廣,還可使用由函數組成的公式組成的條件,格式範圍也更多更廣。
技巧3:自動顯示固定寬度:如將格式自定義成000000,則輸入小於6位的數字,自動在前面以0填充,輸入大於6位的數字或文本則不變。
技巧4:如果單元格區域既含有五位數的郵政編碼又含有九位數的郵政編碼,則可以使用自定義格式以便同時正確顯示這兩種類型的郵政編碼。方法是:單擊「分類」列表中的「自定義」,然後在「類型」框中鍵入下列內容:
[<=99999]00000;00000-0000技巧5:Excel中的障眼法
1、在【格式】菜單上,單擊〖單元格〗子菜單,再單擊「數字」選項卡, 在「分類」列表中,單擊「自定義」, 在「類型」框中鍵入「"Excel偷懶的技術"」試試,不管輸入什麼,顯示出來的都是「Excel偷懶的技術」了。
2、如需將輸入的所有數字均以*顯示,自定義格式為「**;**;**」,同理,將輸入的所有數字均以A顯示,自定義格式為「*A;*A;*A」。
技巧6: Excel中的隱身術
方法1:將單元格字體顏色設置成與底色一樣的顏色
方法2: 【格式】→〖單元格〗→「數字」選項卡, 在「分類」列表中,單擊「自定義」, 在「類型」框中鍵入「;;;」(三個分號)(此格式隱藏單元格所有的數值或文本);格式「;;」(兩個分號)隱藏數值而不隱藏文本。格式「##;;;」只顯示正數;格式「;;0;」只顯示零值。格式「""」隱藏正數和零值,負數顯示為-,文字不會隱藏;格式「???」僅隱藏零值,而不隱藏非零值和文本。(格式「???」有四捨五入顯示的功能,故格式「???」不僅隱藏0值,連小於0.5的值都隱藏了,同理,它也將0.6顯示為1,將1.8顯示為2。建議用:工具>選項>視圖>窗口選項,零值前的對勾去掉,但此方法的缺點是整個表不顯示零值,而不是某區域不顯示零值。)
技巧7:Excel中的變形術
1、在輸入數字前加一個單引號』,數字就「變形」為文本了。輸入的單引號不會顯示出來,也不計入字元串的長度(你可用len函數試試)。如果要再次將該文本格式的數字轉換為數字格式,請「使用選擇性粘貼→乘」的功能(請參見「選擇性粘貼→粘貼時運算」部分)。
2、將數字直接以百元為單位顯示,自定義格式為:「#!.00"百元"」或「0".00""百元"」;直接以千元為單位顯示,自定義格式為:「#,"千元"」或「0.00,"千元"」;直接以萬元為單位顯示,自定義格式為:「#!.0,"萬元"」或「#!.0000"萬元"」;將數字直接以百萬元顯示,自定義格式為「#,,"百萬元"」或「0.00,,"百萬元"」。財務人員、統計人員一定要掌握這幾個自定義格式,很有用哦。
大家如果想學習更多的函數知識和Excel實戰經驗,歡迎購買《「偷懶」的技術:打造財務Excel達人》
《「偷懶」的技術》穩踞噹噹網辦公類暢銷榜前五名,好評率99.7%的Excel暢銷書,你值得擁有!
購買地址:
http://product.dangdang.com/23626444.html
--------------------
本文首發於微信公眾號「Excel偷懶的技術「,
本公眾號堅持分享原創Excel文章,求實用、接地氣、不炫技。歡迎大家關注!
推薦閱讀:
※教你5秒破解密碼,從此,沒有你打不開的excel
※Excel VBA 基礎(01.3)
※Excel中如何製作雙層餅圖
※如何解決Power BI在線版和本地版的時區差異問題
※百分比堆積柱形圖
TAG:MicrosoftExcel |