第191期(106年6月)│行政院人事行政總處公務人力發展中心發行
│發行人:城忠志

各期電子報    訂閱/取消電子報

習藝增能(13)-尋找極端值與特定條件的合計....蕭丞舜


  
  面對一份資料,有時總會需要知道最大、最小值或中位數等,甚至根據特定條件去計算總和,已得到所需的資訊。前者最常見的莫如主計總處定期公布的勞動薪資概況,後者則可計算出機關內一整年的加班費中,特定人或身份者的加班費共若干、社會救助中,特定案件所核發的補助費共若干等。


  這些業務上的需要當然能透過排序與篩選得到,然而當資料量大時,最大、最小值還能輕易取得,中位數則顯得困難許多。至於要從一堆資料中將所有具備某一特徵的相關數值加以合計,當然是更加困難。透過函數的應用,這些將都變得簡易無比。若能加上適當的架構設計,日後類似的業務還可直接套用,省下許多工作時間。


最大、最小值與中位數
  此三者最常應用的地方就是要知道某一組資料的極端值與中間值,以辨別資料離散或差距程度,對於某些政策評估是一個相當簡易的判斷指標。三者比較要注意的是中位數的意義。所謂中位數係指一組數值內有一半的數值大於某一數值A,而另一半則是小於該數值A,數值A即為此一組數值的中位數。它有可能是一組資料依大小排序後,位居中間那一個數值,也可能不是。以二例說明如下:


  例一:2、3、3、5、7及10的中位數為 4,因為在此一組數值中,有3個小於4,有3個大於4。
  例二:2、3、5、7與10的中位數是5。


  二者的主要差別是該組數值的個數是偶數還是奇數。故,如目標是找到排名中間的數值,那麼在奇數個數值內,中位數就是所需的答案,在偶數個數值內,將會是居於中間的兩個數值之平均值(當然,此二數值也可能剛好一樣,視整組數值的內容而定)。


  現以範例檔中的薪資來尋找極端值與中位數:
  =MIN(G:G)
  =MAX(G:G)
  =MEDIAN(G:G)


  範例檔內的最低薪是26642;最高薪是129965,薪資的中位數為46345,由得到的數值判斷,在此一機關的薪資分布,高薪者的離散程度似乎較高。如果再配合標準差(函數為STDEV),自然會得到更精準的分析。


  至於引數的用法,這三個函數均同,就是作為計算對象的一組數值,可以是個別的數值組合,除了上述的方式,還有如下的做法:
  =MAX(G2,G4,G6,G8,G10,G12,G14,G16)
  (採用此一作法,最多可使用255個引數)
  或是一定範圍:
  =MIN(G3:G505)


  第一個做法是以整欄為範圍。好處是如果資料有所增減,函數都能及時計算出結果,對於資料經常有所變動的情形下,可即時得到最新的結果,省去重新設定範圍的麻煩。


  這三個函數如運用得宜,可設計出個人購物與理財專用的比價檔案。如眾所知,在行銷與定價手段的包裝下,想知道所購之物是否為已知資料中最便宜者,其實並不容易。首先要將搜集而來的商品價格,換算為同一基準的單價,再使用前述三個函數找出最貴、最便宜的價格,而中位數則可判斷市面上同一商品的普遍行情為何。對於只在固定賣場、網站購物的人來說,是個相當實用的工具。


  作者以這三個函數為主所設計的工具中,最受工作夥伴與朋友歡迎的正是購物比價檔。總務單位在該檔案內輸入網路上蒐集到的資料,就可作為採購議價的參考。對於個人而言,可買到個人認定最值得的商品,若進一步將相關記錄留存到一定數量後,還可針對自己的購物行為進行深入分析,對於個人理財還有不小的幫助呢!


  當然,網路上也有比價網站,惟缺點是不能客製化且廣告眾多,常常會出現非目標或信用待商榷的網站,還是自行設計的比較好!

圖1:購物比價檔參考圖

合計(SUM)與符合特定條件的合計(SUMIF和SUMIFS)
  SUM應該是最廣為人知的函數了,即使不知道實際撰寫內容,各版本的MS Excel也有名為「自動加總」的按鈕可用。對於金額、研習時數等的加總,這是個非常實用的功能,用法很簡單,和MAX、MIN、MEDIAN差不多:
  =SUM(G2,G4,G6,G8,G10,G12,G14,G16)
  =SUM(G3:G505)
  =SUM(G:G)
  =SUM(G3:G10,G12:505)


  由上可知,如果要加總的內容是不連續的儲存格,應以逗號分隔各個引數(儲存格名稱),萬一要計算合計的是符合特定條件的內容,總不能用逗號不斷地加入儲存格名稱吧?當計算資料超過上萬筆,豈能有效率的完成工作?此時可根據特定條件加總的SUMIF,絕對是最佳的解決良方。


  假設要計算範例檔的技工薪資合計共多少,只要如此即可:
  =SUMIF(F:F,"技工",G:G)


  第一個引數是特定條件所在的資料範圍,在範例檔內就是包含技工的職稱欄F;第二個引數就是所謂的特定條件,在本例中就是技工,因為條件是文字,故前後都要加上英文的、雙引號;最後則是用以加總的範圍,也就是薪資欄G。換句話說,上述函數內容的意義就是「在F欄內找到“技工”的資料,然後將G欄內的對應儲存格內容加以合計」。


  萬一需要的條件超過一個呢?SUMIFS就是專為解決此一問題的函數。假設要計算女性幹事的薪資總合,可撰寫如下:
  =SUMIFS(G:G,D:D,"女",F:F,"幹事")


  雖然與SUMIF系出同門且名稱只差了一個字母,引數的位置卻有相當大的差異。用來加總的引數,改擺放至第一個;第二個引數則是包含第一個條件的範圍,第三個引數才是第一個條件;第四個引數是第二個條件所在的範圍,最後才是第二個條件。


  要注意的是此一函數的多條件,必須是每一項都符合,如果要擇一計算,必須採用它法。此外,函數內容較多,稍有不慎即會出錯,實際應用時最好邊參考函數的提示邊撰寫為宜。


用對時機才是重點

  作者介紹這幾個函數時,最常被提問的問題是上述5個函數使用的機會,實務上以SUM最高,SUMIF、SUMIFS居次,極端值的尋找則很少,為何介紹這些少用的函數?作者的理由是:函數的應用方式千變萬化,若僅使用常用的那些,久而久之會讓應用範圍與彈性受限,導致有「函數也只不過如此而已!」的錯覺。


  函數的存在,必有其用處,有些必須和其他函數整合應用,才會感到「沒有它還真是不行!」之所以會覺得SUM相關函數重要且常用,係因該類函數通常居於顯示最後結果的地位,而對資料分析而言,最後的結果才是使用者最想知道。至於找出極端值的用途相對不那麼明顯(尤其是中位數),所以會覺得用途不大。然而只要是多情況的選擇時,極端值的尋找與判斷就顯得相當重要。


  以每年的所得稅繳納這件例行公事為例,因每個人的各種收入來源不一、身份不同(如已婚、未婚)的緣故而適用法令規定各異,常常有多種計算方式可以選擇,在各種令人眼花撩亂的計算公式與結果,到底哪一種繳交的錢最少?財政部提供的計算軟體,所用的判斷方式就是自動找出最小值,也就是和MIN函數相同的概念。此外,公務體系內也常遇到要找出「對當事人最有利」的情形,此時應用的概念就和MAX函數一樣。


  在目前數據分析日趨重要,為了透析數據所隱藏的意涵,以各種方式加以處理直如家常便飯,否則從龐大的數據資料內真的是看不出個所以然來。以統計學最常見的資料常態分佈分析為例,當以該法檢驗政策結果和政策目標是否相近或一致時,在收集相關數據後,透過MIN和MAX找出「邊界」,再以中位數找出居中位置的數值,最後以標準差來檢視離散的程度。如此即能得到這組資料的分佈狀況,將之與政策目標的數據和預設的分佈相比較,即可得知政策施行是否取得應有的成效,該比較結果即能作為政策修正的參考。如果希望更容易分析與判讀,將數據轉換為圖表與數據分析並列,則更為佳妙。


  故,學習函數應重概念而非死背函數的架構。以作者的經驗而言,絕對沒有人能記住所有函數的架構與寫法,真正的高手都是先從釐清與定義問題著手,弄清楚問題所在後,再開始定義解決方法,然後才設定運算的邏輯與程序,等到這些都決定後,再根據需要尋找適用的函數。能否順利、快速找到需要的函數,關鍵則在於對各種函數的概念是否足夠。


  只要有概念,配合MS Excel內所提供的豐富提示、說明,解決問題自不是什麼難事(真正困難的是定義問題)。至於找提示、說明的方法,打從一開始就教過大家了哦!遇到函數撰寫問題時,千萬別忘了這些最基本與實在的技巧!


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

 

 

 

 


  
 

 

 

 

 

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