1.分?jǐn)?shù)等級(jí)判斷
如下圖所示，根據(jù)右表的標(biāo)準(zhǔn)對(duì)左表的成績判斷等級(jí)。昨天也做過類似的題目，但是只有3個(gè)條件，今天這個(gè)題目有4個(gè)條件。在C2單元格中輸入公式=TEXT(B2,"[>=90]優(yōu);[>=80]良;"&TEXT(B2,"[>=60]中;差"))，向下填充。
這里是text的嵌套用法，和if函數(shù)的嵌套在作用上是一樣的，但是要注意它的寫法，用&連接起來。實(shí)際上就是將里邊的text函數(shù)的結(jié)果作為外邊的text的第3個(gè)條件。
如果有更多的條件判斷，多嵌套幾個(gè)text也能搞定。
第2種解法，在D2單元格中輸入公式=TEXT(0&B2-60,"[>=30]優(yōu);[>=20]良;中;差")，向下填充。這個(gè)text函數(shù)的第2參數(shù)有4部分代碼，前面的3部分是對(duì)數(shù)字設(shè)置格式，最后的差是對(duì)文本設(shè)置格式。
第1參數(shù)為0&B2-60，這個(gè)的作用是將小于60分的轉(zhuǎn)成文本，那么對(duì)應(yīng)的格式就是差。以40分為例說明，0&40-60就是0&-20，最后的結(jié)果是"0-20"，這樣就變成文本。如果是90，那么0&90-60的結(jié)果就是"030"，但是text會(huì)將"030"轉(zhuǎn)成30，然后再對(duì)其設(shè)置格式。
第1參數(shù)減去60，那么第2參數(shù)的每個(gè)等級(jí)也要減去60。這種方法比較奇特，充分運(yùn)用了text函數(shù)的特性和數(shù)學(xué)的思維方式，但是它只能對(duì)4個(gè)條件進(jìn)行判斷。如果是更多的條件判斷，就要text的嵌套或者是其他方法來完成。
2.提取單個(gè)數(shù)字并求和
如下圖所示，A列是一些字符串，有數(shù)字字母和漢字，求每個(gè)單元格的數(shù)字之和。以A12單元格為例說明，求2+5+0+3+4的和。在B11單元格中輸入公式=SUM(--TEXT(MID(A11,ROW($1:$20),1),"0;;0;!0"))，按ctrl+shift+enter三鍵，向下填充。
這里用的是數(shù)組用法，先用mid將每個(gè)字符一一提取出來，作為text的第1參數(shù)，然后對(duì)其設(shè)置格式。這里的關(guān)鍵是text的第2參數(shù)，"0;;0;!0"有4部分，正數(shù)顯示它本身，負(fù)數(shù)顯示為空，0顯示為0，文本強(qiáng)制顯示為0。
由于提取出來的都是1位，不可能有負(fù)數(shù)的，而文本通過!0強(qiáng)制顯示為0，這樣就將文本轉(zhuǎn)為0。由于text得出的結(jié)果是文本，直接用sum求和是0，所以用--轉(zhuǎn)為數(shù)字，再求和。
3.計(jì)算2個(gè)日期時(shí)間相差幾天幾小時(shí)幾分鐘
如下圖所示，有起始日期的時(shí)間和結(jié)束日期的時(shí)間，計(jì)算它們相差多少天多少小時(shí)多少分鐘。在C17單元格中輸入公式=TEXT(B17-A17,"d天")&TEXT(MOD(B17-A17,1),"h小時(shí)m分鐘")，向下填充。
先用結(jié)束日期減去起始日期，得到一個(gè)數(shù)字，這個(gè)數(shù)字就是它們相差的天數(shù)，整數(shù)部分就是整天，小數(shù)部分就是不滿一天的天數(shù)，將其化成小時(shí)數(shù)和分鐘數(shù)。
TEXT(B17-A17,"d天")這部分就是整天數(shù)，B17-A17可能是小數(shù)，為什么不用int取整呢？原因就是第2參數(shù)的d，它只會(huì)取整天數(shù)。
TEXT(MOD(B17-A17,1),"h小時(shí)m分鐘")這部分就是將不滿1天的時(shí)間轉(zhuǎn)成小時(shí)和分鐘數(shù)。MOD(B17-A17,1)是取2個(gè)日期的差的小數(shù)部分。
如果對(duì)你有所幫助或啟發(fā)，請(qǐng)打賞或分享一下，你的支持就是我最大的動(dòng)力！
關(guān)注解鎖更多函數(shù)的用法
聯(lián)系客服
