俞木發(fā)

1. 日期格式變身

Excel中默認(rèn)便用類似2020-07-01、2020/07/01的日期格式，但這種格式在進(jìn)行篩選操作時(shí)卻可能會(huì)失效。比如下表是某商品7月銷售的統(tǒng)計(jì)，現(xiàn)在需要通過(guò)日期篩選找出當(dāng)月所有周六、周日銷售的明細(xì)（圖1）。

由于默認(rèn)的日期格式并不會(huì)顯示星期字樣，我們無(wú)法直接按照日期進(jìn)行周六、周日的篩選，這時(shí)就可以借助于Text函數(shù)進(jìn)行日期格式的轉(zhuǎn)換。在A列后插入一個(gè)新列，接著在B2中輸入公式“=TEXT（A3，"aaaa"）”（表示將A3中的文本轉(zhuǎn)換為“aaaa”格式，即Excel默認(rèn)的中文星期格式），下拉填充即可顯示星期。如果要轉(zhuǎn)換為英文星期形式，則可以輸入“=TEXT（A3，"dddd"）”?，F(xiàn)在選中B列點(diǎn)擊“數(shù)據(jù)→篩選”，在打開(kāi)的篩選窗口中勾選“星期六”、“星期天”選項(xiàng)，然后在C35中輸入公式“=SUBTOTAL（109，C3：C34）”（109表示只對(duì)篩選后的數(shù)據(jù)求和，忽略隱藏?cái)?shù)據(jù)），這樣即可求出周末銷售之和了（圖2）。

使用Text函數(shù)還可以將非標(biāo)準(zhǔn)日期數(shù)據(jù)轉(zhuǎn)換為標(biāo)準(zhǔn)格式。比如很多朋友會(huì)使用類似“20200701”、“20200702”的格式輸入日期，這種不規(guī)范的日期在Excel中會(huì)被識(shí)別為數(shù)字，無(wú)法參與正常的運(yùn)算?，F(xiàn)在只要在原來(lái)的數(shù)據(jù)列后插入一列，在B2中輸入公式“=TEXT（A3，"0年00月00日"）”，下拉即可變?yōu)轭愃啤?020年07月01日”的標(biāo)準(zhǔn)形式，之后同樣可以使用上述函數(shù)顯示星期等信息（圖3）。

公式解釋：

0是占位符，使用年月日的形式將8位數(shù)字分成三段。日期格式是按照從右到左依次劃分，最右邊2位為日，中間2位為月，最左邊4位為年。

如果要將標(biāo)準(zhǔn)日期如“2020年07月01日”轉(zhuǎn)換為“20200701”的形式（比如很多公司的數(shù)據(jù)庫(kù)系統(tǒng)的日期使用的就是這樣的8位數(shù)字格式，這樣要將Excel生成的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)就得更改格式）。同上，在B2中輸入公式“=TEXT（A2，"yyyymmdd"）”（表示將A2的日期按照年yyyy、月mm、日dd的形式進(jìn)行組合），公式下拉后就可以變?yōu)?位數(shù)字的格式（圖4）。

2.時(shí)間格式變身

在考勤統(tǒng)計(jì)中，常常需要對(duì)時(shí)間進(jìn)行運(yùn)算，比如要計(jì)算員工加班時(shí)間，需要按小時(shí)進(jìn)行統(tǒng)計(jì)。但Excel默認(rèn)的時(shí)間統(tǒng)計(jì)超過(guò)24小時(shí)會(huì)自動(dòng)進(jìn)位為天，這樣統(tǒng)計(jì)的數(shù)據(jù)是不準(zhǔn)確的，因?yàn)榧影噘M(fèi)都是按總計(jì)小時(shí)乘以單價(jià)來(lái)計(jì)算的（圖5）。

借助于Text函數(shù)可以將天轉(zhuǎn)為小時(shí)。定位到C2并輸入公式“=TEXT（SUM（B2：B13），"[h]：mm：ss"）”（表示對(duì)求和的數(shù)值按照時(shí)分秒的格式顯示），然后下拉，這樣就不會(huì)按天進(jìn)位，直接顯示為總的加班時(shí)間了（圖6）。

3.數(shù)字格式變身

財(cái)務(wù)人員經(jīng)常要對(duì)數(shù)字進(jìn)行大小寫(xiě)的轉(zhuǎn)換，如在金額總計(jì)里要填寫(xiě)大寫(xiě)金額。如果要將普通數(shù)字更改為大寫(xiě)，同樣可以借助Text函數(shù)進(jìn)行轉(zhuǎn)換。比如上圖中需要計(jì)算總的加班費(fèi)（假設(shè)每小時(shí)加班費(fèi)為4.33元），并且使用大寫(xiě)數(shù)字進(jìn)行表示，只需定位到B16并輸入公式“=B15*4.33*24”（因?yàn)锽15是時(shí)間格式，它和24相乘后會(huì)顯示為實(shí)際小時(shí)數(shù)字），這樣得到加班費(fèi)為225.16元。繼續(xù)在B17輸入公式“=TEXT（B16*100，"[DBnum2]0佰0拾0圓0角0分"）”（因?yàn)樨?cái)務(wù)上大寫(xiě)數(shù)字沒(méi)有小數(shù)點(diǎn)，這里B16*100后就變?yōu)檎麛?shù)，[DBnum2]表示使用百圓角分的形式定義數(shù)據(jù)），這樣就可以按照財(cái)務(wù)圓角分的要求顯示加班費(fèi)了（圖7）。

4.運(yùn)算結(jié)果轉(zhuǎn)換為文本顯示

在一些統(tǒng)計(jì)匯報(bào)中，文字說(shuō)明可以更直觀地表示最終結(jié)果，比如下表中直接顯示盈利××元、虧損××元或保本。這類文本的顯示同樣可以使用Text函數(shù)進(jìn)行轉(zhuǎn)換，在C2中輸入公式“=TEXT（B2-A2，”盈利0.00元：虧損-0.00元：保本”）”（這里將B2-A2的運(yùn)算結(jié)果以盈利、虧損、保本的形式顯示），下拉公式，即可顯示直觀的結(jié)果（圖8）。