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

函數撰寫的基本方法

  函數常見的撰寫方式有四,此下仍以四捨五入函數ROUND為例來說明。

  第一,直接在儲存格內輸入函數及其內容。方法是選定顯示運算結果的儲存格並使之成為作用儲存格後,按函數的基本架構依序輸入等號(=)、函數名稱(ROUND)、左括弧、第一個引數(B2)、逗號(,),第二個引數(0),最後以右括弧作為結束,輸入完畢後按一下Enter鍵就完成。結果如下:

=ROUND(B2,0)

  此法於輸入函數名稱的第一個字母時,會出現以該字母為首的函數清單,可加以點選。優點是可防止輸入錯誤,二來也可節省時間,尤其是名稱較長的函數(例如:組合字串函數CONCATENATE)。輸入的過程中,還會有提示小窗格,顯示整個函數應該輸入的內容,同時也會顯示每個引數的性質(如number,意思就是該引數必須是個數值),對於撰寫出正確函數,其助益不言可喻。至於引數如欲使用儲存格,除了輸入儲存格名稱外,也可用滑鼠點選的方式。

圖1:函數清單可避免輸入錯誤。
圖1:函數清單可避免輸入錯誤。

  此一輸入函數的方法簡單、直覺且快速,然而前提是使用者必須對函數的架構瞭若指掌,而且對於問題的解決流程和邏輯也很請楚。對於初學者而言,並不算方便。

  其二,指定作用儲存格後,如上一方法一樣直接在資料編輯列上輸入函數。和前一方法不同的是,在此輸入時,不會遮住左右儲存格的內容,而且可任意動輸入點。

  輸入完畢後,除了按Enter鍵之外,也可按資料編輯列上的輸入鍵,完成函數的撰寫。

圖2:資料編輯列的優點就是可任意移動輸入點。
圖2:資料編輯列的優點就是可任意移動輸入點。

  第三種方式則是擇定作用儲存格後,按一下資料編輯列上的插入函數鈕,隨即會開啟插入函數的視窗,上方是搜尋函數,可用簡單的關鍵字進行搜尋(若輸入想做什麼樣的運算,直至2016版本,顯示的結果仍很難符合預期),建議直接使用「或選取(C):」,然後從下方的「選取函數」欄位內,選擇想用的函數。點選函數後,下方還會出現函數的結構與簡易說明。這對於第一次使用某一函數時,相當有幫助。

圖3:插入函數的功能便於使用較為陌生的函數。
圖3:插入函數的功能便於使用較為陌生的函數。

  按下確定後,緊接著會出現函數引數的視窗,顯示的欄位數就是該函數成立所需的引數數量。每個引數就是一個欄位,每個欄位左邊所示者均為該引數的性質,按一下欄位,下方還會有該引數的相關說明,協助使用者輸入正確的內容。

  除了在欄位內直接輸入外,如果該引數要使用的是特定的儲存格或儲存格範圍,可按一下右邊的範圍按鈕後,函數引數的視窗會縮小至僅顯示該引數的大小,接著點選儲存格或以拖曳的方式選擇儲存格範圍,選定後再按一下引數視窗右邊的範圍按鈕,就會回到原來的視窗。

  依序在各引數欄位內輸入完畢,按一下確定,就完成函數的輸入了。

圖4:輸入引數時亦可用範圍按鈕以選取某一或特定範圍的儲存格。
圖4:輸入引數時亦可用範圍按鈕以選取某一或特定範圍的儲存格。

  最後一種輸入函數方式就是切換到公式面板,此處已將常用公式、函數均以按鈕形式分類,直接點選後,就會和第三種方式一樣,開啟函數引數的視窗,之後的操作和前一方式同。

圖5:按類別旁的下拉式選單按鈕,就會出現該類別所有函數的清單。
圖5:按類別旁的下拉式選單按鈕,就會出現該類別所有函數的清單。

  對於初學者而言,第三、四種算是最友善的方式。不過這樣的方式耗費的時間較多,對於常用的函數,直接在儲存格或資料編輯列內輸入比較明快些。

撰寫函數時可用的輔助說明

  試算表軟體的多數使用者,對於函數的使用可說是敬而遠之,即使知道這是解決運算問題的神兵利器,卻因為對函數內容與意義的了解相對不易、必須使用非圖像化的文字撰寫模式等因素而怯步。除了下定決心學習之外,如何取得良好的輔助說明資源,絕對是重要關鍵。

  使用函數的過程中,隨時都可以按下F1取得使用說明,在說明視窗的搜尋欄位內輸入函數的名稱,即可找到相關資料。使用時此功能最好是連上網路。畢竟,微軟官網的說明資料是隨時可以更新,在實務應用中,曾有更新後的說明文字,反較易理解的情形。

  另外,採用插入函數的方式時所開啟的視窗左下角有「函數說明」的超連結,而函數引數視窗開啟後,左下角也有個「函數說明(H)」的超連結,點選之後就會顯示該函數的說明資料。善用這些輔助說明資源,撰寫合用的函數並非想像中的困難,累積的經驗足夠,對函數的應用自然就熟能生巧。

活用函數解決問題的關鍵與祕訣

  要了解函數的涵義、架構、撰寫的方法等,還不算困難,真正的困難是:使用者對於解決問題的重點、運算的邏輯與程序均不甚了解。實務經驗顯示,在試算表軟體內遇到運算難題時,許多使用者會聯想到該使用某函數,惟自己卻不清楚問題的核心重點與解決的邏輯為何。

以人事單位每個月必處理的待遇校對稽核業務而言,如果要將人事單位所產製的待遇校對清冊(自WebHR內產製)和出納單位所輸出的薪資清冊,每一筆資料都互相比對並確認無誤,其實是一件苦差事。畢竟大多數的機關人員都在百人上下,既要逐一比對官職等、俸級、俸點,還要比對本俸和各種加給的金額是否正確,以傳統的紙本校對稽核方式,非經日難竟其功。

  然而在員額精簡,業務量不減反增的現實下,人事人員實無法再以此低效能的方式辦理待遇校對稽核的業務。如果改以MS Excel的函數,則可輕鬆解決。

  問題的關鍵有三:一是基準和對照資料必須均為可再處理的MS Excel檔;其次是找到同一人的薪資資料並加以比對,同時要顯示比對結果是正確還是錯誤,如是錯誤又是何種原因所致?(某人的資料是否兩份清冊都有?還是俸給金額不一致?)最後則是各類人員與金額的數目以及總計,必須和機關內的現有員額和發放金額一致,整個待遇稽核校對才算完整。

  緣此之故,這樣的作業並非一個函數即可完成,而必須設計一個有系統的檢核機制方可。其中最困難的部分即是以基準資料(WebHR的校對清冊)為準,在對照資料內找到同一個人的資料後加以比對。找不到人的時候要顯示「薪資無此人」,比對結果不一致時則顯示「錯誤」。

  如果想要在特定儲存格內就希望有以上所有的功能,更不是採用數個函數就能解決,必須利用巢狀結構加以整合才能達到要求:

一、 運用查找函數VLOOKUP找到比對的目標。
二、 當找不到時,預設顯示的訊息是#N/A,這樣的訊息不容易懂,故需使用「是否錯誤函數ISERROR」,於找不到目標時,顯示「薪資無此人」。
三、 找到目標後再以數學的等號運算子比對基準和對照資料是否一致。
四、 上述的各種狀況,透過邏輯函數IF一一加以判斷並顯示相對應的結果,以利後續問題的解決。
五、

最後以巢狀結構將上述函數加以整合
最後得到內容如下:

  =IF(ISERROR(VLOOKUP(I4,Salary!A:F,6,0)),"薪資無此人",IF(AA4=VLOOKUP(I4,Salary!A:F,6,0),"正確","錯誤"))

  欲能熟練使用這樣的結構,所需當然非朝夕之功。惟有遇到問題時,就要思考問題的關鍵何在,想清楚解決問題的邏輯,必須進行的程序又是什麼,不見得要一次到位解決問題,可採分段的方式解決。以上述問題為例,可以先用查找函數找目標:

  VLOOKUP(I4,Salary!A:F,6,0)

  如顯示#N/A就是對照資料內無此人。如找到後再用IF函數和數學的等於運算子來比對,只要相同即使之顯示為正確,不一樣就顯示為錯誤:

  IF(AA4=AB4,"正確","錯誤")

  如此,同樣可以解決問題,只是版面上看起來比較複雜而已。

  再次強調,能解決問題才是首要目標,是否可以寫出繁複、看似技巧高深且華麗的巢狀結構整合函數並非重點。先求解決問題,行有餘力再來思考如何整合以簡化版面,才是使用應用軟體的正確態度。只有不斷嘗試解決各種問題,熟悉相關的函數,未來才有整合應用的可能。

  故經常思考與嘗試如何解決難題,是熟練函數的不二法門。

 

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

 

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