二月 08, 2010
Excel 的數字前自動加 0 (放大絕)
有時我們會拿到一些已經輸入完成的 Excel 檔案，我們想將裡面月份、日期都修改為兩位數，甚至再加以合併。
但因為檔案已經輸入完成，所以將儲存格改為文字格式已經來不及了，那怎麼辦呢？
針對上述問題，基礎篇教大家怎麼亡羊補牢，進階篇教大家利用 REPT、LEN 兩個指令將分成三欄的年、月、日資料匯整在一起。
不過進階篇提到的指令雖然讓我們對於 Excel 有更多的瞭解，但每次要將資料匯整成一格時恐怕都忘記怎麼用了，得再查閱一下文章解說才會下指令，使用上不是那麼便利。
其實要將年、月、日資料整合在同一格有更快的方法：把檔案存成 TXT 檔再讀入 Excel 即可。
底下我們先用基礎篇教的方法，先將年、月、日的儲存格格式都設定好並存檔：
然後從檔案選單中選擇『另存新檔』
存檔時，檔案格式設定為 Unicode 文字 (.txt) 檔。
雖然說存為『文字檔 (Tab 字元分隔)(*.txt)』也是可以的，但是怕實際應用時 Excel 檔內有些學生的名字用到特殊字，貯存為普通文字檔的話，那些名字會變成亂碼，所以還是貯存為 Unicode 文字檔吧，這樣特殊字都可以保留。
設定存為文字檔後，Excel 會給你警告畫面，按下『確定』就好了。
之後還會再來一個警告畫面，一樣不管它，按『是』即可。
存好後，將檔案關掉，然後再重新開啟這個 TXT 檔。
這時候就會跳出一個畫面，問我們要對這個 TXT 檔做什麼樣的處理。因為我們之前存的 TXT 檔是以 TAB 鍵來區分欄位，所以就選擇第一項『分隔符號』吧！
再來，Excel 會問我們使用的分隔符號是什麼，勾選『Tab 鍵』及『逗號』兩個選項，再按下一步。
再來就是重點了，我們要在這邊設定每一個欄位的格式。原本 Excel 內定欄位格式為『一般』，我們要將它改成『文字』。
點選一下『文字』，將第一欄的格式設定為文字。
滑鼠點選一下第二欄，也將它設為文字格式。
第三欄的日期也是相同的處理。三個欄位都將格式設定為文字之後，按下完成鍵。
你可以發現匯入的資料這時候都是以『文字』的格式貯存 (在 Office2003 以後左上角有綠色小三角型)，表示這邊呈現的符號是像電話號碼一樣，是文字而不是數字，不能用來做加減乘除計算的。
因為貯存格裡面的資料是文字資料，所以要將它們匯整起來，祇要用 & 直接串接起來就可以了。
串接起來的結果完全正常：
之後利用複製的方式，將所有格子都複製好，再另存新檔為 Excel 檔案即可完工。
會有這樣的結果是因為，Excel 面對數字時，會將其前方的 0 刪除；但是面對文字時，就會把 0 保留 (總不能把電話號碼前面的 0 刪除吧？那就無法記錄了啊。) 所以我們就利用這個特性達成我們想要的結果。
那可不可以存成 CSV (*.csv) 檔再來開啟呢？
答案是不行。
因為 Excel 開啟 CSV 檔時，不會出現詢問欄位格式的畫面。而是直接將所有欄位設定為『一般』來開啟，所以前面的 0 都會被移除。
『可是，可是，別人交給我的檔案就是 .csv 啊，怎麼辦？』
沒關係，先將它的副檔名改為 .txt，這對檔案不會有影響的，祇是改副檔名而已。改好名稱後，再用 Excel 開啟它，就會出現詢問的畫面。然後依照上面的做法，將欄位都設定為『文字』就一切 OK 啦！！:D
這個方法比較取巧，但是速度比較快，實際應用上應該會比前一次介紹的更方便！大家試用看看吧！:)
二月 07, 2010
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
二月 06, 2010
Excel 的數字前自動加 0 (基礎篇)
今天早上註冊組問我：『要怎樣把 Excel 的數字前面自動加 0 呢？』
因為註冊組在輸入學生資料時，希望生日月份、日期都以兩位數呈現，比方說出生在一月的要登錄為 01。可是在 Excel 裡面，輸入 01 會自動變成 1，造成註冊組不少的困擾。
如果是要新建一個 Excel，那麼最簡單的方法就是直接將欄位設定為文字格式，這時候再輸入資料就不會 01 變成 1 了。
但是，現在的問題是註冊組已經將檔案輸入完畢了，檔案裡面已有幾千筆的資料，既然木已成舟，來不及設定為文字格式再輸入，那麼祇能想辦法亡羊補牢了。
其實要把已經輸入完成的 Excel 檔案數字前自動加 0 滿簡單的，也是設定一下儲存格格式就可以了。
首先將要自動補零的欄位標記起來，以我們的例子來說，就是月份與日期這兩欄。
然後按滑鼠右鍵，選擇『儲存格格式……』進行設定。
進入儲存格格式的設定頁中，選擇『自訂』，依據我們自己的需求來設定 Excel 的顯示格式。
Excel 預設將儲存格設定為『G/通用格式』，其他還有一些奇奇怪怪的設定，看起來有點嚇人。不過不必擔心，這其實還滿簡單的：
0 表示確定要顯示的位數，每多一個 0 表示要增加一個位數。若輸入的位數較少，則前面自動補零
# 也是設定要顯示的位數，但是輸入位數較少時不會補零
因為我們想要能夠自動補 0 ，所以選用『0』。另外，因為希望月份與日期都是兩位數，所以就在『類型』那邊輸入兩個『0』(00) 表示要兩位數，不滿兩位數時自動補 0：
按下確定之後，就可以看到月份與日期都以二位數呈現了。
OK，那如果要把出生年變成三位數呈現的話該怎麼辦呢？想一下吧！！:D
