第181期(105年8月)│行政院人事行政總處公務人力發展中心發行 │發行人:城忠志 各期電子報           訂閱/取消電子報
 
習藝增能加班少02-學函數之前,先重新認識Excel(下)....蕭丞舜

文字123與數值123真的不一樣  

  文字和數值間的差異,在MS Excel是很重要的,所涉者包括資料能否被計算、可計算的範圍和計算方式,如果在建構資料時未注意此一差異,常常就是公式與函數無法得到預想結果的主因。

  數值資料的外觀表現形式,MS Excel採用的是阿拉伯數字,其餘均屬文字。一旦建構的資料將來會進行與數學有關的運算,就必須確定這些資料均為數值資料(需留意的是,儲存格內的資料若有文字,即使內含阿拉伯數字,也會被視為文字)。不過,阿拉伯數字也可以透過格式設定或特定的技巧,將之指定為文字以符合特定用途。

  像是透過勞保局的線上e化服務系統辦理勞工保險業務時,輸入的生日必須是民國紀元的7碼數字,如民國75年8月1日,要輸入0750801。在建立勞保業務管制檔時,必須指定該欄位為文字格式,否則最前面的0將會被自動消除,無益於辦理單筆加退保業務或建立批次檔。此無他,在MS Excel內建立的數值,第一位不能為0。

  欲將數值指定為文字有哪些方法?第一種是輸入「’」此一字元後再輸入數值,MS Excel就會將該儲存格的數值視為文字。惟缺點有二,當要轉換為數值時,只能仰賴VALUE函數轉換(手動逐一刪除絕非良方)。其次是在2010以前的版本,許多函數的應用將因此一字元而失效,除非確定該資料完全不會用在計算上,否則儘量不要使用 。

  另一種方法則是將儲存格格式直接設定為「文字」,如此在該儲存格輸入的內容將一律視為文字。好處是日後這些內容如果要轉換為數值,只要將儲存格格式改為「通用格式」、「數值」、「貨幣」、「會計專用」等與數值有關的格式即可。

  在外觀上,使用者要如何辨別是否為文字的123呢?MS Excel預設值是數值資料向右對齊,文字資料向左對齊,當然這可透過對齊方式來改變。如果想要精準確定,就點選儲存格後檢視「常用」面板>「數值」區塊>「數值格式」欄位,就知道該儲存格被設定為何種格式 。

格式設定:幫資料梳妝打扮

  儲存格的格式設定,當然是為了改變資料顯示的外觀,目的有三,一是為了易讀性,尤其是要凸顯重要的資料時;其次是美觀,除了自己感到賞心悅目外,輸出文件時,外觀也是評價製作者專業程度的標準之一;最後是符合資料特性,達到資料建構目的亦便於區辨資料可否用於公式或函數。故格式只要設定得宜,可以兼顧資料的美觀、可讀性並隨時可被計算。

  上述的格式設定分為兩大部分,一是儲存格本身,可設定者包括為儲存格填滿顏色或網底、加上框線(有粗、細、單雙框線和各種虛線)。

  二是儲存格內的資料。除了將字體改為粗、斜體、加底線、改變字體顏色等基本項目外,還有各種數值特定的表現方式(貨幣、會計專用、百分比、分數、時間、日期、行動電話、郵遞區號、國字大寫的數字等),甚至自訂特定的格式。而儲存格預設的格式是通用格式,此一格式意指沒有特定的數字或文字格式,輸入什麼就是什麼。

  試舉一例以明之。在儲存格A1數入數值資料「53104」,若希望顯示的結果是「新台幣伍萬參仟壹佰零肆元整」,該如何設定?

  做法很簡單,在A1被選取的狀況下,以滑鼠按一下「常用」面板>「數值」區塊右下方的「數字格式」鈕,在出現的「儲存格格式」視窗點選「數值」標籤>左方的「類別」欄位>「特殊」>「壹萬貳仟三佰肆拾伍」,在右方的「範例」裡就會顯示A1內的數值將會以數字的國字大寫來顯示。

圖1:將數值設定為國字大寫
圖1:將數值設定為國字大寫

  接著點選「類別」欄位內的「自訂」,在右方的「類型」欄位會顯示出該儲存格所用的特殊內碼,將滑鼠游標移到該欄位的最前方按一下,確定游標出現在該內碼的最前方後,輸入「“新台幣”」,接著把游標移到最後面,輸入「“元整”」,按確定後就會得到所需要的結果。

圖2:加入固定顯示的文字
圖2:加入固定顯示的文字


圖3:設定後的結果

  製作加班費明細表時,將最後的總金額作如此之設定,就會符合相關單位要求以國字大寫數字顯示總金額的規範。當然,之後如有新的計算結果,總金額一樣會跟著改變且不必額外手動輸入固定的文字內容。

資料的本質和實際內容不受格式設定影響

  依據各機關加班費支給要點第2點規定,每小時的加班費是以本俸、專業加給、職務加給(主管人員和簡任(派)非主管人員比照主管職務核給職務加給有案者)總和除以240,如無法整除,則四捨五入取整數發給(行政院主計處100年1月修訂之「支出標準及審核作業手冊」第貳篇原始憑證之審核二、(五)內部憑證)。此時該怎麼做?

  如果僅止於顯示數值結果,可以透過設定小數位數來解決。不過如果還要進行計算,則不可採取此法。假設要計算委任第二職等本俸三級的同仁,加班7小時的加班費究竟為何?依照前述法規,該員的薪俸總額為34215元,每小時加班費為143元,7小時的加班費就是1001元。改以MS Excel計算時,在通用格式的情形下,每小時加班費為142.5625元(34215/240),總金額為997.9375元。

  此時將每小時加班費和總金額的小數點位數均改為0(點選「常用」面板>「數值」區塊>「減少小數位數」鈕,直到沒有小數位數為止),每小時加班費雖然顯示為143,可是總金額顯示的卻是998不是1001!這是因為改變數值的顯示方式並不影響數值的本質和實際內容,MS Excel計算總金額時,仍以每小時142.5625元計算所致。因此要計算出正確的結果,就要以四捨五入的函數ROUND來解決:

=ROUND(B12/240,0)
此一函數的意義就是:在C12此一儲存格要顯示的內容,是B12除以240所得的數值,其小數點以下四捨五入並以整數顯示。得到的結果就是143,當然總金額就是143*7=1001囉!

圖4:加班費計算,通用格式、變更小數點位數和使用函數計算後的結果比較
圖4:加班費計算,通用格式、變更小數點位數和使用函數計算後的結果比較

  換句話說,ROUND函數會將原來的數值以四捨五入的方式「改變」為143,後續當然是以「改變」後的結果來計算。至於函數的意義、基本結構和撰寫方法,別擔心,之後會詳細介紹。這裡只要先了解:格式的各種設定只會變更資料的外觀,其本質和內容不會因此而改變!


(作者為臺北市立南湖高級中學人事室組員)
 
民國86年1月創刊,95年2月改版為電子報  
10660臺北市大安區新生南路3段30號 電話:(02) 8369-1399 轉 8308 研究組