當前位置:首頁 > 科技 > 正文

大神級函數TEXT,隻需會得這個方法,你也能秒懂!

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)

你可能想看:

有話要說...

取消
掃碼支持 支付碼