2010/02/06

Excel 的數字前自動加 0 (進階篇)

前一篇文章提到利用儲存格格式設定,就可以讓 Excel 的數字前面自動補零

今天註冊組把資料傳給出納組時,出納組說她那邊的系統需要將原本年、月、日分三個儲存格的資料全部放進同一個儲存格裡面才有辦法作業。

要達成出納組的需求也不難,我們知道利用 & 這個符號可以將不同的儲存格的內容串接起來。

利用 & 這個符號可以將不同的儲存格串接起來

我們在原本的 A、B、C 三欄旁邊再多設一欄 D 來放新的年月日資料。比方說把 A2、B2、C2 的資料串接起來呈現在 D2 這一格。

按下 Enter 之後,發現出來的竟然不是我們想要的內容:

原來,在 A、B、C 這三欄我們雖然設定顯示的格式了,但畢竟骨子裡的資料還是沒變啊,利用 & 這個運算符號把它們串接起來就一切破功了。

那怎麼辦呢?沒關係,既然前一篇文章用的虛招沒辦法過關,我們就叫 Excel 幫我們確確實實的補 0 進去。

特別說明:底下方法是針對『已經輸入完成的 Excel 檔』做事後補救。如果還沒有開始 Keyin 資料,那麼先將欄位設為文字格式再輸入就不會有上述問題了。

Excel 有一個指令叫做 REPT,它可以依照我們的需求,把文字重覆顯示。它的用法是這樣子的:

REPT("想要重覆的文字",次數)

比方說,我下 rept("☆★", 10) 這樣的指令

結果就是把☆★重覆十次

所以我們就利用 REPT 這個指令幫我們在月份前面自動補 0 就好了。但問題是要補幾個 0 呢?

像 1 月份的 1 祇有一位數,而我們要呈現二位數,所以我們要補 2-1 = 1 個零。10 月份有二位數,所以我們要補 2-2 = 0 個零。

剛好 Excel 有 LEN 這個指令可以計算儲存格的內容有幾個字,所以我們要計算 B2 這一格要補幾個零才能變成二位數時,就可以用 2 減掉 LEN(B2),就可以知道要補多少零進去 (如果要補成十位數,就用 10- LEN(B2) )。

知道要補幾個零之後,就把這個數字丟給 REPT 指令,叫它幫我們補上 0 吧:

REPT("0", 2-LEN(B2))

但是,上述的指令祇是決定要輸出幾個 0 而已,還得用 & 串接原本的 B2 資料,才能成為真正二位數的月份:

REPT("0", 2-LEN(B2))&B2

利用相同的方法,可以將年份轉變成三位數、日期轉變成二位數,再串接起來:

REPT("0", 3-LEN(A2))&A2 &REPT("0", 2-LEN(B2))&B2 &REPT("0", 2-LEN(C2))&C2

按下 Enter 鍵之後,就會得到我們所需要的年月日資料了。

最後,再利用複製功能,就可以把所有學生的出生年、月、日資料都轉變成為七位數,並儲存在一個欄位中。

學校內用的系統要求並不統一,有的系統要求年、月、日要分開,有的要求要集中在一起(如出納組的系統),每個系統要求的位數還不一致;有的要求要使用西元年、有的要民國年份……這些各式各樣系統間的資料轉換常常造成負責人員的困擾。

我個人比較喜歡將年、月、日分開成為三欄,然後再依需求用 & 把三欄的資料串接起來就好。不同的系統要求,我就串不一樣的年月日格式給它,事情總是能解決的!!: )

希望這一篇文章能解決許多人的困擾!:D

Technorati : , , ,

5 則留言:

  1. 真的太感謝了
    我就是在處理已經拿到的資料很頭大
    感謝大大的解答
    幫我省下很多的時間

    回覆刪除
  2. 不客氣!

    這其實也是我自己的小抄!:P 每次註冊組問我要怎麼處理時,我也是趕緊上來偷看這個小抄才能回答。這種每年祇做一次、兩次的事真的記不得。

    所以寫這個文章是利人利己啊!能幫到您真是太好了!:D

    回覆刪除
  3. 我個人認為還有一個更為簡便的做法:
    =TEXT(A2,"000")&TEXT(B2,"00")&TEXT(C2,"00")

    回覆刪除
  4.  感謝!真的更方便了!:D

    回覆刪除
  5. 請問一下我的儲存格內是
    55/12/23,若要變成055/12/23要如何設定

    回覆刪除