TEXT函數函數是一個超神奇的文本函數,到底有哪些神奇地方呢?
TEXT基本語法為
=TEXT (value,format_text)
其中Value可以為數值、能夠返回數值的公式,或者對數值單元格的引用Format_text為設置Value中格式。
看起來,感覺TEXT函數好像並不神奇。那麼,到底TEXT是何方神聖?都有哪些玩,今天給大家總結了4大玩法。具體如下:
-01- 四捨五入
要求:對數據四捨五入到小數點後1位。
方法:=TEXT(A1,"0.0")
解釋:該公式表示對A1單元格的數值,四捨五入到小數點後1位。如果四捨五入到小數點後2位,公式如何表達?=TEXT(A1,"0.00")其他實現數據四捨五入到小數點後1位的方法:=ROUND(A1,1),其中公式中的1表示A1的數據四捨五入到小數點後1位。
-02- 格式轉換
A. 日期格式轉換
① 修改日期格式為 yyyy-mm-dd
=TEXT(A2,"yyyy-mm-dd")
② 修改日期格式為yyyy年mm月dd日
=TEXT(A2,"yyyy年mm月dd日")
③ 提取月和日
方法:=TEXT(A2,"mm")
解釋:提取單元格A2中的月份
方法:=TEXT(A2,"dd")
解釋:提取單元格A2中的日
④ 轉換為星期
=TEXT(A2,"AAAA")
B.佔位符
強迫症福音——補齊位數。
要求:下圖中的數據,位數需要統一為6位。
語法:=TEXT(A1,"000000")
解釋:0表示佔位符,如果需要統一為6位,直接輸入公式 =TEXT(A1,"000000") 即可。其中0的個數為6個!
-03- 盈虧判斷
要求:如圖所示,D列數據收入-支出>0, 結果返回“賺”;如果收入-支出=0, 結果返回“平”;收入-支出<0, 結果返回“虧”。
方法:=TEXT(D2,"賺;虧;平")
解釋:語法為TEXT(D2,"正數的顯示值;負數的顯示值;零的顯示值") 。相當於:if(數據>0,"賺",if(數據<0,"虧","平"))。Text用來條件判斷確實是太好用了。
-04- 自定義多條件判斷
① 要求:如圖所示,成績大於等於90分,等級為“優”;成績在60~90分之間,等級為"及格";小於60分,等級為"差"! 如何利用函數進行判斷?
方法:=TEXT(B2,"[>=90]優;[>=60]及格;差")
解釋:函數=TEXT(B2,"[>=90]優;[>=60]及格;差") 表示 如果 B2單元格中的數據,成績>=90分,等級為“優”;>=60且<90,等級為及格;<60,等級為差。相當於if函數 = if(B2>=90,"優",if(B2<60,"差","及格"))。
② 學到這裡,有些同學可能會問:假如有4個條件,或者5個以上的條件,TEXT函數還適用嗎?
我們可以測試下。
要求:如圖所示,成績大於等於90分,等級為“優”;成績在>=70且 <90分,等級為“良”;成績在>=60且 <70分,等級為及格;小於60分,等級為“差”! 如何利用函數進行判斷?
方法:根據之前講解的思路=TEXT(B2,"[>=90]優;[>=70]良;[>=60]及格;差")。
結果如下,發現結果是 #VALUE。
解釋:原來TEXT函數,最多隻能添加2個條件,如果3個以上的條件,需要考慮if函數或TEXT嵌套函數!
③要求:如圖所示,成績大於等於90分,等級為“優”;成績在60~90分之間,等級為“及格”;小於60分,等級為“差”!如果沒參加考試,標記為“缺考”!如何利用函數進行判斷?
方法:=TEXT(B2,"[>=90]優;[>=60]及格;差;缺考")
解釋:=TEXT(B2,"[>=90]優;[>=60]及格;差;缺考") 公式表示>=90,返回“優”;B2分數>=60且<90,返回“及格”;<60,返回“差”;如果B2是文本,則返回文本“缺考”。
- TEXT 總結 -
用法一:四捨五入
公式:=TEXT(value,"0.0")
解釋:需要保留幾位小數,小數點後就加幾個0。
用法二:格式修改
公式:=TEXT(value,format_text)
解釋:format_text為所需要目標格式
用法三:Text函數盈虧判斷
TEXT(value,"正數的顯示值;負數的顯示值;零的顯示值")
解釋:如果value>0, 輸出內容“正數的顯示值”;如果value<0,輸出內容“負數的顯示值”;如果value=0,輸出內容“零的顯示值”
用法四:TEXT函數自定義多條件判斷
公式: =TEXT(value,"[條件1]顯示內容;[條件2]顯示內容;否則顯示內容;文本顯示格式")
解釋:①TEXT函數中,條件可以為1個,最多隻能添加2個條件。如果條件>=3,需要使用IF或者IFS或者嵌套的TEXT函數。②[文本] 部分可以省略不寫
閱讀更多 excel咖喱魚丸 的文章