excel如何顯示農(nóng)歷？

雷哥給大家推薦text函數(shù)，把公歷變農(nóng)歷的方法！

如圖所示，A列是公歷日期，我們在B2單元格輸入公式：

=TEXT(A1,"[$-130000]yyyy年m月"&IF(LEN(TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")

可以發(fā)現(xiàn)，陽歷日期變?yōu)殛帤v了。

公式解析

假設(shè) MM=IF(LEN(TEXT(A1,"[$-130000]dd"))=1,"初","")，那么該公式就可以轉(zhuǎn)化為

=TEXT(A1,"[$-130000]yyyy年m月"&MM,"")&"d")

①首先解釋MM公式

TEXT(A1,"[$-130000]dd")，A1公歷日期轉(zhuǎn)換為陰歷，提取日。比如2017/1/25轉(zhuǎn)為陰歷后為2016年十二月二十八，那么TEXT(A1,"[$-130000]dd")處理后的結(jié)果是28

因此MM公式就表示，如果日（例如，28）字符數(shù)=1，那么就輸出“初”，否則輸出空內(nèi)容

②

=TEXT(A1,"[$-130000]yyyy年m月"&MM,"")&"d") 就表示把陽歷日期轉(zhuǎn)換為陰歷日期，如果日是個位數(shù)，前面就加上“初”字。

是不是感覺text函數(shù)特別好玩呢？

① 數(shù)值的四舍五入

案例：需要對數(shù)據(jù)四舍五入到小數(shù)點后1位。

方法：=TEXT(A1,"0.0")

解釋：如果四舍五入到小數(shù)點后2位，公式如何表達？ =TEXT(A1,"0.00")

② 日期格式的互相轉(zhuǎn)換

① 日期格式轉(zhuǎn)為 yyyy-mm-dd

=TEXT(A2,"yyyy-mm-dd")

② 日期格式轉(zhuǎn)為yyyy年mm月dd日

=TEXT(A2,"yyyy年mm月dd日")

③ 提取月和日

方法：=TEXT(A2,"mm")

解釋：提取單元格A2中的月份

方法：=TEXT(A2,"dd")

解釋：提取單元格A2中的日

④ 轉(zhuǎn)換為星期

=TEXT(A2,"AAAA")

③ 強迫癥的福音-占位符

案例：下圖中的數(shù)據(jù)為某公司的員工工號，位數(shù)需要統(tǒng)一為6位。

方法：=TEXT(A1,"000000")

解析：0表示占位符，若需統(tǒng)一為6位，直接輸入公式 =TEXT(A1,"000000") 即可。其中0的個數(shù)為6個！

④ 賺虧判斷

案例：如圖所示，D列數(shù)據(jù)收入減去支出>0, 結(jié)果返回“賺”；如果收入減去支出=0, 結(jié)果返回“平”；收入減去支出<0, 結(jié)果返回“虧”。

思路：=TEXT(D2,"賺;虧;平")

解釋：語法為 TEXT(D2,"正數(shù)的顯示值;負數(shù)的顯示值;零的顯示值") 。相當于：if(數(shù)據(jù)>0,"賺",if(數(shù)據(jù)<0,"虧","平"))。

是不是感覺text函數(shù)太好用了？

⑤ 自定義多條件判斷

案例一 ：如圖所示，成績>=90分，等級為“優(yōu)”；成績在60~90分之間，等級為及格；小于60分，等級為差！ 如何利用函數(shù)進行判斷？

方法：=TEXT(B2,"[>=90]優(yōu);[>=60]及格;差")

解析：相當于if函數(shù) = if(B2>=90,"優(yōu)",if(B2<60,"差","及格"))。

注意：TEXT函數(shù)，最多只能添加2個條件，如果多個（3個）條件，需要考慮if函數(shù)或text嵌套函數(shù)！

案例二： 要求：如圖所示，成績大于等于90分，等級為“優(yōu)”；成績在60~90分之間，等級為“及格”；小于60分，等級為“差”！如果沒參加考試，標記為“缺考”！ 如何利用函數(shù)進行判斷？

方法：=TEXT(B2,"[>=90]優(yōu);[>=60]及格;差;缺考")

解析：=TEXT(B2,"[>=90]優(yōu);[>=60]及格;差;缺考") 公式表示>=90，返回“優(yōu)”；B2分數(shù)>=60且<90，返回“及格”；<60，返回“差”；如果B2是文本，則返回文本“缺考”。

各位朋友，對于text函數(shù)的用法，大家學會了嗎？

歡迎關(guān)注頭條號【雷哥office】，更多精彩等你來！