第184期(105年11月)│行政院人事行政總處公務人力發展中心發行 │發行人:城忠志 各期電子報           訂閱/取消電子報
 
習藝增能06-撰寫函數須知(上)....蕭丞舜

 

  MS Excel的函數應用,是許多人想學卻不得其門而入的領域。主要原因是不了解函數的定義、架構、應用時機與解決問題應有的邏輯,只覺得函數必須自己以文字撰寫的方式建構,其內容宛如程式碼般的複雜難懂,以至於心生怯意而裹足不前,最後只好放棄,白白浪費了一組能解決許多與數字相關難題的絕妙工具。

  就MS Excel而言,函數到底是什麼?根據微軟Office支援網站上的定義:函數是預先定義的公式,會使用稱為引數的特定值以特定的順序或結構來執行計算。函數可以用於執行簡單或複雜的計算。(https://support.office.com/zh-TW/article/Excel-公式概觀-ecfdc708-9162-49e8-b993-c311f47ca173

  此處所謂的公式,可以是一般人所熟知的數學的算術公式(如2+3=5),也可能是一組具有邏輯的方程式(如:若x2+y2=z2成立即為直角三角形),甚至可以自己加以定義——只要具有一定的邏輯即可。換句話說,函數是個事先定義好且具有邏輯性的計算方法,只要符合公式的邏輯,將其中的變數(x、y)帶入,自然就會得出應有的結果。反之,就會得到錯誤的訊息。

  MS Excel函數的定義和原理,真的就這麼簡單 。

公式與函數的基本架構

  MS Excel的函數繁多,依據官方的分類計有Cube函數、資料庫函數、日期及時間函數、工程函數、財務函數、資訊函數、邏輯函數、查閱與參照函數、數學與三角函數、統計函數、文字函數、Web 函數、與增益集一起安裝的使用者定義函數等13類。每一類均有數個至數十個不等的函數,總數有數百個之譜!如此多的函數,幾乎不可能有人能通曉全部!

  其實,不需要也沒必要全部都懂、都會,真正需要的是具備應用領域的專業知識(想用統計函數,當然要懂統計學)、掌握函數的基本架構和善用MS Excel內的輔助工具,就可「臨時抱佛腳」使用函數解決問題。是故,對於函數的初學者,想當然耳要先懂函數的架構。

  如前所述,函數就是一組定義好的公式,姑且以數學四則運算來解說。通常大家所熟知的算式如下:

  2+3=5

  然而,在MS Excel的儲存格內需要顯示的是運算後的結果。所以,想要在某一個儲存格內顯示2+3的結果,當然要寫成如下的樣子:

  =2+3

   意思就是說這個儲存格要顯示的內容,就是2+3的結果。緣此,在MS Excel內要撰寫任何公式或函數,第一個要輸入的字元就是「=」,只要有「=」,MS Excel就認定該儲存格的真實內容就是公式或函數,絕不會當作是文字或數值,而顯示的一定是運算後的結果而不是運算式。要檢視運算式只能到資料編輯列內去查看。

圖1:資料編輯列內的運算式和儲存格的運算結果。
圖1:資料編輯列內的運算式和儲存格的運算結果。

  2與3稱為運算元,也就是用來計算的對象。在此公式內是屬於不因其他儲存格影響而改變的內容,故被稱為「常數」。通常在公式內較少使用常數,除非是需要固定增減或使用一定倍數、百分比等。常見的情形是使用儲存格的名稱,例如在C1儲存格內輸入:

  =A1+B1

   如此只要A1或B1儲存格的內容改變,MS Excel會自動且及時計算出結果並顯示在C1儲存格。因此A1、B1儲存格稱為「參照」。

  「+」就是所謂的運算子,常用的運算子計有算數、比較、文字串連及參照,不同的運算子也有優先運算順序的差異,礙於篇幅,請參閱以下說明網頁:
https://support.office.com/zh-TW/article/Excel-公式概觀-ecfdc708-9162-49e8-b993-c311f47ca173

  至於函數的結構,與算術公式相較之下自然有所不同,且用先前已提過的四捨五入函數來說明:

  =ROUND(A2,0)

  ROUND是函數的名稱,通常是相應的英文單字或簡寫,其後必定接著左右括弧,其意為標示計算的起訖範圍,也就是ROUND要計算的內容限於括弧內。

  A2和0則稱為引數,也就是要計算的內容或條件。第一個引數的意思是要進行四捨五入計算的對象,在此就是儲存格A2的內容;第二個引數的用意就是要指明四捨五入的位數為何,0就是指取為整數(1就是小數點後1位,2是小數點後2位;-1則為取至個位數,-2是取至十位數,餘依此類推)。引數之間則以逗號「,」分開。

  先前曾提過計算委任第二職等本俸三級公務同仁的每小時加班費,現以同例詳細說明四捨五入ROUND函數的用法。該職等俸級的薪俸總額為34215元,每小時加班費依法令規定為143元,實際上是將34215除以240(為142.5625元)後再四捨五入為整數得來。

圖2:A3所顯示者,係將A2的內容四捨五入至整數的結果。
圖2:A3所顯示者,係將A2的內容四捨五入至整數的結果。

  範例中,在A1輸入34215,A2則輸入「=A1/240」,在A2就會顯示為142.5625,在A3輸入上述的函數「=ROUND(A2,0)」,就會得到143。此時若想知道薦任第七職等年功俸六級者的每小時加班費為何,只要將A1輸入其俸給總額60800,MS Excel就會及時重新計算為253並顯示於A3。

圖3:A2、A3的公式與函數之所以會跟著A1的內容變動,是因為採用了參照的方式。而A2內的公式使用240,則因法令規定的計算方式,故直接採用常數為之。
圖3:A2、A3的公式與函數之所以會跟著A1的內容變動,是因為採用了參照的方式。而A2內的公式使用240,則因法令規定的計算方式,故直接採用常數為之。

  如果配合前幾篇文章所述,將行政院人事行政總處所公布的俸表整理成資料庫格式,委任第一職等本俸一級的加班費處先輸入這樣的公式,再將公式往下複製,任一職等俸級的每小時加班費就全部算出來了。如果想要快速查找某一職等俸級的每小時加班費為何,可以在適當的儲存格內設定VLOOKUP查閱函數,協助大家快速找出加班費的「單價」。(有關VLOOKUP函數的妙用,之後會再介紹)

圖4:將俸表以資料庫格式建立,很輕易就能製作出所有職等俸級俸點的每小時加班費。
圖4:將俸表以資料庫格式建立,很輕易就能製作出所有職等俸級俸點的每小時加班費。

撰寫函數的要點

  引數的數目會因為函數的內容而不同,有些函數的引數有一定的限制,有些則無。如四捨五入、無條件進位、無條件捨去等,一定只有兩個引數。而最廣為熟知的加總函數SUM,則幾無限制。

  至於引數內容,可以是數值(如:2、8、-3、0.19等)、儲存格名稱(參照)、算數公式甚至是另一個函數等,以下即以ROUND各舉一例以明之:

=ROUND(3.1415926,4)
=ROUND(A1*B1,0)
=ROUND(SUM(A1:A10),0)

  由上可知,函數的應用範圍很廣,可用以解決較為複雜的運算,尤其是最後一種,能因應複雜的需要而將各種函數組合在一起使用,在MS Excel內被稱為巢狀結構。

圖5與6:實際函數內容與運算後結果的對照圖。

圖5與6:實際函數內容與運算後結果的對照圖。
圖5與6:實際函數內容與運算後結果的對照圖。

  說明至此,有關函數的撰寫可得到以下的結論:

一、 要弄清楚想要解決的問題及其運算邏輯為何,選擇適合的函數。
二、 函數要寫在將要顯示運算結果的儲存格內。
三、 公式或函數內如有使用多個運算子,要注意其運算先後順序為何。
四、 函數的撰寫以等號(=)開始,繼之以函數名稱(ROUND)、左括弧、決定運算範圍的引數(A1)、決定運算方式的引數(0),引數之間以逗號(,)分隔,最後再以右括弧作為結束。
五、 函數內的引數可以是其他函數,此之謂巢狀結構。

  巢狀結構是針對較複雜的運算問題,必須要清楚了解問題解決的程序和邏輯為何,以及所需使用的函數有哪些。故,必須對多種函數有所了解及應用經驗,才有靈活應用的可能。在常用函數的介紹告一段落後,會針對此一重要應用加以說明。

 

(作者為臺北市立南湖高級中學人事室組員)

 

 
民國86年1月創刊,95年2月改版為電子報  
10660臺北市大安區新生南路3段30號 電話:(02) 8369-1399 轉 8308 研究組