用活Text函數(shù)　數(shù)據(jù)變身更高效

俞木發(fā)

1. 日期格式變身

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

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

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

公式解釋：

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

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

2.時間格式變身

在考勤統(tǒng)計中，常常需要對時間進行運算，比如要計算員工加班時間，需要按小時進行統(tǒng)計。但Excel默認的時間統(tǒng)計超過24小時會自動進位為天，這樣統(tǒng)計的數(shù)據(jù)是不準確的，因為加班費都是按總計小時乘以單價來計算的（圖5）。

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

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

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

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

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