內(nèi)容提要：Excel中text函數(shù)的用法蟹肘、text函數(shù)日期格式湾笛、text函數(shù)轉(zhuǎn)換文本漓骚、text函數(shù)轉(zhuǎn)換時(shí)間蝌衔、補(bǔ)零等等在本篇Excel教程中都有講解.
在Excel的函數(shù)里有一個(gè)神奇的函數(shù)，這個(gè)函數(shù)就是TEXT函數(shù)蝌蹂。函數(shù)的結(jié)構(gòu)非常簡(jiǎn)單噩斟，只需要兩個(gè)參數(shù)：TEXT（數(shù)據(jù)，格式代碼）孤个。今天為大家分享excel中text函數(shù)的用法以及這個(gè)函數(shù)的六個(gè)妙用剃允。
一、將日期變?yōu)樾瞧?/b>
有這樣的一個(gè)銷(xiāo)售明細(xì)表硼身，現(xiàn)在需要將周六周日的數(shù)據(jù)篩選出來(lái)做分析：
可能有些童鞋想到通過(guò)自定義格式將日期變成星期然后篩選硅急，這樣是很簡(jiǎn)單，對(duì)這一列設(shè)置自定義格式佳遂，代碼為aaaa：
確定后日期就顯示為為星期了营袜，可是當(dāng)你篩選的時(shí)候發(fā)現(xiàn)并不能達(dá)到想要的效果：
因?yàn)樵O(shè)置自定義格式只是改變了數(shù)據(jù)的顯示效果，實(shí)際內(nèi)容還是日期丑罪，這時(shí)候就需要TEXT函數(shù)出馬了荚板。
在日期后面插入一列，標(biāo)題為星期吩屹，然后輸入公式=TEXT(A3,"aaaa")跪另，就可以按星期篩選了：
這個(gè)公式里的代碼”aaaa”就是星期的效果，大家不妨自己再試試代碼”aaa”煤搜、 ”ddd”和”dddd”的效果是什么免绿，是不是很有意思呢？
再來(lái)看Text函數(shù)第二個(gè)妙用：
二擦盾、格式化員工工號(hào)
在公司的員工名單中嘲驾，原來(lái)的工號(hào)都是按部門(mén)排列的，每個(gè)部門(mén)的工號(hào)都是從1開(kāi)始迹卢，現(xiàn)在要求工號(hào)前面帶上部門(mén)名稱(chēng)辽故，同時(shí)工號(hào)統(tǒng)一為三位數(shù)，不足三位的前面補(bǔ)0腐碱，此時(shí)我們就可以使用TEXT函數(shù)來(lái)得到新的工號(hào)誊垢，B2單元格公式為：
=C2&TEXT(A2,"000")
這個(gè)地方用到了代碼”000”，0在TEXT中是數(shù)字占位符，一個(gè)0就代表一個(gè)數(shù)位喂走。這個(gè)方法可以用在很多需要前置加0的地方殃饿，這也是TEXT最常用的代碼之一。
接下來(lái)要看的這個(gè)妙用就更加奇妙了缴啡，使用TEXT來(lái)完成IF函數(shù)的工作壁晒，是什么問(wèn)題呢瓷们？一起來(lái)看看：
三业栅、設(shè)置盈虧平衡判斷
根據(jù)收入和支出數(shù)據(jù)設(shè)置盈虧平衡判斷。收入大于支出設(shè)置為盈利谬晕，收入小于支出設(shè)置為虧本碘裕，收入等于支出設(shè)置為平衡。通常這類(lèi)問(wèn)題我們會(huì)用IF函數(shù)來(lái)處理攒钳，其實(shí)TEXT也有這個(gè)功能帮孔，公式為：=TEXT(A2-B2,"盈利0.00萬(wàn);虧損0.00萬(wàn);平衡;")
這里的格式代碼就與之前的例子不同了，利用的是分段設(shè)置的方法不撑，TEXT函數(shù)可以將數(shù)據(jù)分為正數(shù)文兢、負(fù)數(shù)、零和文本四種類(lèi)型來(lái)分別指定顯示方式焕檬，類(lèi)型之間使用分號(hào)隔開(kāi)姆坚，標(biāo)準(zhǔn)格式為"正;負(fù);零;文本"，在本例中A2-B2得到的數(shù)字會(huì)出現(xiàn)正數(shù)实愚、負(fù)數(shù)和零兼呵，不會(huì)有文本的類(lèi)型，按照對(duì)應(yīng)的類(lèi)型進(jìn)行設(shè)置就是"盈利0.00萬(wàn);虧損0.00萬(wàn);平衡;"腊敲，文本的位置留空即可击喂。
四、日期的特殊處理
有時(shí)候我們需要根據(jù)表格的數(shù)據(jù)來(lái)編輯一些信息碰辅，例如：
可能有些朋友會(huì)說(shuō)懂昂，直接用&連起來(lái)啊，如果直接連起來(lái)的話(huà)没宾，結(jié)果是這樣的：
日期變成了數(shù)字凌彬，因此要想按照實(shí)際需求顯示的話(huà)，還得TEXT出馬榕吼，公式修改為：=TEXT(A2,"yyyy年m月d日")&B2&"銷(xiāo)售額為:"&C2
這里還是用了TEXT函數(shù)來(lái)強(qiáng)制顯示日期饿序，甚至可以用TEXT函數(shù)將日期也變成漢字的方式，公式修改為：
=TEXT(A2,"[DBNum1]yyyy年m月d日")&B2&"銷(xiāo)售額為:"&C2
在格式代碼前增加了[DBNum1]羹蚣，就可以將阿拉伯?dāng)?shù)字變成中文數(shù)字原探，這里的1還可以用2、3、4來(lái)代替咽弦，自己試試都是什么效果吧徒蟆。
五、對(duì)時(shí)間進(jìn)行求和
有時(shí)候會(huì)遇到對(duì)時(shí)間求和的問(wèn)題型型，例如在計(jì)算加班時(shí)間合計(jì)的時(shí)候段审，直接用sum函數(shù)得到的結(jié)果顯然是不對(duì)的：
因?yàn)闀r(shí)間在累計(jì)超過(guò)24小時(shí)的時(shí)候，會(huì)進(jìn)位到天闹蒜，并不是直接在小時(shí)數(shù)累加寺枉，這時(shí)候又該TEXT函數(shù)大顯身手了，只需要在SUM的外面加個(gè)TEXT绷落，公式修改為：=TEXT(SUM(C2:C20),"[h]:mm:ss")
求和結(jié)果正確姥闪，因?yàn)榇a[h]就是將數(shù)據(jù)鎖定到小時(shí)這一級(jí)，不會(huì)向上進(jìn)位了砌烁。
最后再來(lái)看看TEXT在遇到身份證號(hào)碼的時(shí)候筐喳，又會(huì)發(fā)生什么：
六、提取身份證號(hào)碼中的性別和出生日期
如何從身份證號(hào)碼中提取出生日期函喉，這是很多人都在問(wèn)的一個(gè)問(wèn)題避归，借助TEXT函數(shù)可以很容易的實(shí)現(xiàn)，C2單元格公式為：
=--TEXT(MID(B2,7,8),"0-00-00")
首先使用MID函數(shù)從身份證號(hào)碼中的第7位開(kāi)始提取8個(gè)數(shù)字出來(lái)管呵，這部分就是出生日期梳毙，再用TEXT將這個(gè)8位數(shù)字以"0-00-00"的格式顯示，此時(shí)得到結(jié)果只是表面像日期撇寞，并不是真正的日期格式顿天，還需要在TEXT函數(shù)前加上負(fù)負(fù)得正的運(yùn)算，將文本字符轉(zhuǎn)換為日期字符蔑担，最后設(shè)置單元格格式牌废。
在身份證號(hào)碼中，除了含有出生日期之外啤握，還能判斷性別鸟缕，倒數(shù)第二位表示性別，男性為奇數(shù)排抬，女性為偶數(shù)懂从。
根據(jù)這個(gè)規(guī)則，公式可以這樣寫(xiě)：=TEXT(MOD(MID(B2,17,1),2),"男;;女")
首先用MID函數(shù)提取18位身份證號(hào)碼中的第17位蹲蒲，MID(B2,17,1)番甩；
再用MOD函數(shù)判斷奇偶，簡(jiǎn)單來(lái)說(shuō)一下MOD函數(shù)届搁，這個(gè)函數(shù)有兩個(gè)參數(shù)缘薛，格式為：MOD（被除數(shù)窍育，除數(shù)），而結(jié)果是余數(shù)宴胧，本例中被除數(shù)是身份證號(hào)碼的第17位數(shù)字漱抓，除數(shù)是2，當(dāng)被除數(shù)是偶數(shù)時(shí)恕齐，余數(shù)為零乞娄，反之余數(shù)為1，利用TEXT的四段分類(lèi)顯示規(guī)則"正;負(fù);零;文本"显歧，將正數(shù)定義為“男”仪或，零定義為“女”，就實(shí)現(xiàn)了提取性別的目的追迟。
今天的教程就到這里咯溶其，大家到qq群：488925627下載課件進(jìn)行練習(xí)才會(huì)理解更深刻喲骚腥！
需要學(xué)習(xí)更多的excel教程敦间，請(qǐng)關(guān)注部落窩教育Excel微信公眾號(hào)，每天和小編一起學(xué)原創(chuàng)excel教程束铭。
excel教程相關(guān)推薦閱讀：Excel各年度同比增長(zhǎng)率和環(huán)比增長(zhǎng)怎么計(jì)算的方法和案例
日期怎么轉(zhuǎn)成星期廓块、數(shù)字怎么添加千分位、位數(shù)不同的編號(hào)怎么統(tǒng)一成相同的編號(hào)契沫、數(shù)字怎么自動(dòng)添加備注……
這些亂七八糟的問(wèn)題都和Text函數(shù)有關(guān)带猴，沒(méi)有想到吧？
Text函數(shù)可以說(shuō)是Excel函數(shù)中的百變王子懈万、化妝大師拴清，它可以輕輕松松地將一個(gè)數(shù)值轉(zhuǎn)換成另外的樣子。讓我們一起來(lái)看一看Text的變化手段吧会通。
Text函數(shù)的基本語(yǔ)法
公式：Text(單元格,轉(zhuǎn)換后的格式)作用：將指定單元格的內(nèi)容轉(zhuǎn)換成指定的格式口予。
日期和星期相關(guān)的
看起來(lái)好像表格中的公式挺多的，公式的作用主要是可以提取一個(gè)日期中的年涕侈、月沪停、日、星期裳涛。大致的規(guī)律可以歸納成下面兩點(diǎn)：
年木张、月、日的英文單詞是“year”端三、“month”舷礼、“day”，所以和年有關(guān)的用字母“yyyy”和“yy”郊闯；和月有關(guān)的用字母“m”妻献，1-3個(gè)“m”浮声，分別可以得到的月份的格式不同；和天數(shù)有關(guān)的用字母“d”旋奢。和星期有關(guān)的可以用“dd”泳挥、“ddd”、“aaa”和“aaaa”至朗，字母短的將得到星期縮寫(xiě)屉符，長(zhǎng)的得到完整的星期名稱(chēng)。時(shí)間相關(guān)的
和上面的日期類(lèi)似锹引。小時(shí)矗钟、分鐘、秒的英文單詞分別是“hour”嫌变、"minute”吨艇、“second”，所以可以用單詞首字母來(lái)提取相應(yīng)的數(shù)值腾啥。
至于是一個(gè)字母還是兩個(gè)字母东涡，就看你的需要了。例如“2018/1/4 6:2:00”倘待，用“hh”就會(huì)得到“06”疮跑，用“h”就會(huì)得到“6”，也就是說(shuō)兩個(gè)字母式凸舵，Excel會(huì)根據(jù)需要自動(dòng)在前面補(bǔ)零祖娘。
注意，不要用“Text(時(shí)間,"mm")”提取分鐘啊奄，因?yàn)樘崛≡路萦玫囊彩恰癿m”渐苏，這樣在提取分鐘時(shí)，就會(huì)出錯(cuò)菇夸。
千分位和小數(shù)位數(shù)相關(guān)的
看暈了吧琼富？又是“0”又是“#”的。
其實(shí)峻仇，很容易公黑。首先，“0”和“#”的區(qū)別在于摄咆，一個(gè)會(huì)在需要的時(shí)候補(bǔ)“0”凡蚜，一個(gè)不會(huì)，如前兩行所示吭从。
其次朝蜘，加了一個(gè)逗號(hào)“,”可以將數(shù)字轉(zhuǎn)換成英文形式，給數(shù)字每隔3位添加一個(gè)分隔符涩金。
數(shù)值轉(zhuǎn)換相關(guān)的
和上一個(gè)格式有點(diǎn)類(lèi)似谱醇，主要用到的也是0”和“#”暇仲，區(qū)別也是相同的。
想在數(shù)值前面添加貨幣符號(hào)副渴，就可以用第一個(gè)公式奈附。想轉(zhuǎn)換成百分比，就直接在格式后添加“%”煮剧，保留幾位小數(shù)斥滤，就看text函數(shù)中小數(shù)點(diǎn)后面有幾位。
手機(jī)號(hào)勉盅、銀行卡號(hào)分段顯示時(shí)佑颇，用0”和“#”寫(xiě)出分段后的效果即可達(dá)到目的。
條件格式的
格式1：=TEXT(A3,"A3為正數(shù)時(shí)顯示的文字;A3為負(fù)數(shù)時(shí)顯示的文字;A3為零時(shí)顯示的文字")格式2：=TEXT(A7,"[條件1]A7滿(mǎn)足條件1時(shí)顯示的文字;[條件2]A7滿(mǎn)足條件2時(shí)顯示的文字;A7不滿(mǎn)足條件1和2時(shí)顯示的文字")
這個(gè)公式就類(lèi)似于IF草娜，但比IF要簡(jiǎn)潔一些挑胸。
補(bǔ)充
Text函數(shù)上面說(shuō)的這些格式，是可以一起使用的宰闰。例如茬贵，可以用“=TEXT(B3,"yyyy-mm-dd AAAA 上午/下午")”將B3單元格的日期轉(zhuǎn)換成“年-月-日 星期 上午/下午”的格式。
最后议蟆，再回到文章開(kāi)頭的問(wèn)題“位數(shù)不同的編號(hào)怎么統(tǒng)一成相同的編號(hào)闷沥，例如你輸入的是“1、33咐容、445”怎么快速統(tǒng)一成“00001、00033蚂维、00445”戳粒？
