VIP學員從網上看到這張圖片,問裡面同比那一列怎麼做到的?
綠漲紅跌,看起來比較别扭,外國的就是這種。條件格式,圖标集,三角形就是這種,不過隻能完成前半部分,數字還是完成不了。
而中國剛好相反,紅漲綠跌。這種其實可以借助自定義單元格格式完全實現。
首先解決符号問題,這個搜狗輸入法裡面的符号大全就有。
再來看顔色的問題,設置單元格格式,數值裡面就有紅色的。
再看看自定義格式怎麼表示,在[]裡面輸入具體顔色就行。
依樣畫葫蘆。
[紅色]0.0%;[綠色]-0.0%自定義完就看到正數的變成紅色,負數的變成綠色。
再将三角形加進去,自定義完發現三角形沒有對齊,其他一切正常。
[紅色]▲0.0%;[綠色]▼-0.0%其實,盧子第一時間也想不起來,不過沒關系,這并不妨礙我解決問題。這時,我想起了會計專用格式,就是這種符号左對齊的效果。
老樣子,設置單元格為會計專用格式。
再查看自定義代碼,很長也很亂,不過沒關系,重點看前面的_ ¥* 。也就是說,加了這部分就能左對齊,¥可以換成其他符号。
按照這個思路,将原來的内容自定義為新的代碼。
[紅色]_ ▲* 0.0%;[綠色]_ ▼* -0.0%自定義完就看到,效果正是我們需要的。
說到這裡,突然想起了TEXT函數,這個函數第2參數變幻莫測,想怎麼變就怎麼變,很多人都記不住。其實,記不住也沒關系,可以先通過自定義單元格格式,然後查看代碼,稍作修改就可以。
這裡再通過一個案例進行說明,将數字轉換成大寫。
自定義單元格格式為特殊,中文小寫數字。
查看自定義代碼。
再将代碼複制出來作為TEXT的第2參數即可。
=TEXT(A1,"[DBNum1][$-zh-CN]G/通用格式")另外,再分享4個TEXT的用法,等你自定義熟練了,就能運用自如。
1.發票号都是8位,從系統導出後前面的0消失,如何補齊?
常規格式發票号前面的0都會消失,隻有文本格式情況下的0才不會消失。事先錄入的話,可以将整列設置為文本格式。針對已經錄入的,可以用TEXT函數來處理。需要多少位,第2參數就寫多少個0。
=TEXT(A2,"00000000")
2.将8位數的出生日期轉變成以-作為分隔符号的出生日期。
很多人首先想到的是用這樣的公式,可惜出錯。
這種是針對标準日期才可以這樣用的。
8位數字需要用其他方法處理。
0-00-00跟e-mm-dd是兩碼事,别亂用。
3.标準日期格式轉變成年月日星期的格式。
e代表4位的年,m代表月,d代表日,aaaa代表星期幾,這些都是固定的,要牢記。
标準日期你想轉變成任意一種日期格式都非常方便。比如轉換成中英文星期幾的簡寫。
3個a代表中文簡寫,3個d代表英文簡寫。
=TEXT(B2,"aaa")
=TEXT(B2,"ddd")
4.将金額大于0的顯示赢,小于0的顯示虧,0顯示平。
對于這種問題,我們首先想到的是IF函數。
=IF(B2>0,"赢",IF(B2=0,"平","虧"))
其實也可以用TEXT函數來實現。
=TEXT(B2,"赢;虧;平")
2個;隔開,用法是:正數的顯示值;負數的顯示值;零的顯示值。
其實最标準的用法是用3個;隔開,俗稱三分天下。單元格的内容除了數值還可能是文本。加一個;讓文本顯示成需要顯示的結果。為了方便說明用法,我們将标題的B1也算進去。
@就代表文本顯示本身,類似于數值格式中的0。
推薦:高效!Excel自定義單元格格式居然能幹出這種事兒!
上文:沒想到SUMPRODUCT還能這麼用,太贊了!
你一般怎麼記自定義格式和TEXT第2參數?
作者:盧子,清華暢銷書作者,《Excel效率手冊 早做完,不加班》系列叢書創始人,個人公衆号:Excel不加班(ID:Excelbujiaban)
有話要說...