第183期(105年10月)│行政院人事行政總處公務人力發展中心發行 │發行人:城忠志 各期電子報           訂閱/取消電子報
 
習藝增能 05-基本的資訊提取術....蕭丞舜

 

  資料庫要能發揮作用,必備的要件是:單筆資料的完整度、資料的數量是否能滿足該專業領域的各種應用需求。資料完整度不夠,就會發生「應有卻沒有」的結果;資料量多則難以提取出需要的資訊;資料量小,應用的範圍與深度就多所限制。資料建構並無捷徑,僅能「一步一腳印」,平實的去建構。而從資料庫提取資訊,則有眾多的方法可資採用,最常見的就是各種統計方法與技巧。

  通常資料庫應用軟體都會提供一些實用的工具,讓使用者能快速得到結果,解決最常見的應用問題, MS Excel自然也不例外,先學會這些技巧,不僅有利於工作,對於函數的應用概念也頗有助益。

排序與篩選

  如果想知道薪資清冊內薪資最高的前三名是誰,薪資各若干,該怎麼找呢?

  開啟範例檔案(註:範例檔案係作者因應教學所需,以WebHR人事資訊系統之彈性選員所得結果為範本,重新自行設計製作,與任何個人、機關或團體無涉),將滑鼠游標移到俸給總額上點一下,使之成d作用中儲存格,接著依序按「常用」面板>排序與篩選>自訂排序。

圖1:若直接點選「從A到Z排序」或「從Z到A排序」,就會以所選的俸給總額為排序基準依金額大小排序。
圖1:若直接點選「從A到Z排序」或「從Z到A排序」,就會以所選的俸給總額為排序基準依金額大小排序。

  在「排序」的對話視窗內,勾選「我的資料有標題」,再按一下「排序方式」右邊的下拉式選單按鈕,點選俸給總額,在順序的欄位內選擇「最大到最小」後按確定。

圖2:設定排序方式。此處也可改變排序對象、增加多種排序方式(即新增層級)。
圖2:設定排序方式。此處也可改變排序對象、增加多種排序方式(即新增層級)。

  完成排序後,再加以篩選,只顯示所需要的資訊。同樣點選常用面板>排序與篩選>篩選。

圖3:排序後進行篩選。
圖3:排序後進行篩選。

  標題列上每一個項目的右邊此時會出現下拉式選單按鈕,按一下後從清單內取消勾選「全選」,並將捲軸拖曳到最下方。

圖4:篩選清單內的項目是由小排到大。
圖4:篩選清單內的項目是由小排到大。

  勾選數字最大的三項,按一下確定。

圖5:勾選所需顯示的項目。
圖5:勾選所需顯示的項目。

  最後將只顯示俸給總額前三高的資料,其餘則先隱藏起來,避免干擾資料檢視。

圖6:篩選後只顯示所需的資料。
圖6:篩選後只顯示所需的資料。

  透過上述的方法,很輕易就可找到所需的資料,不必使用什麼“高深”的技巧。採用應用軟體的目的,即是以簡單有效率的方法解決問題,不需要華麗但複雜、費時的手段。

  在排序的對話視窗中,可設定的項目還很多,例如先依照俸給總額排序,排完之後再以職稱排序等多條件的排序(排序視窗的「新增層級」就是增加一種排序條件,參閱圖2),排序對象也不僅限於「值」(數值或字母順序、文字筆畫等),還有儲存格色彩、字型色彩、儲存格圖示等選項可用,滿足特定排序需求。

  至於篩選的選單內也會視資料內容而有不同的條件可選擇,如依照色彩篩選、特定數字、文字或邏輯判斷式(大於、等於、不等於、介於、包含、不包含)可用,足以協助使用者篩選出符合特定條件的資料,大家不妨在各種應用情境中嘗試看看。

欄列的隱藏、取消隱藏與窗格的凍結

  單筆資料的項目愈多,檢視特定項目間的關聯時,愈顯得困難。以本範例而言,想要檢視一下周戊的到職日,找到名字時往右檢視到職日時,往往會看錯行,尤其二者之間如果還間隔著超出螢幕畫面的其他眾多資料時。簡單的做法就是將二者之間的資料隱藏起來,需要檢視時再顯示出來(也就是取消隱藏)。

  開啟範例檔案後,在欄名上以拖曳的方式將想隱藏的欄選取起來(B欄至G欄),在欄名上按滑鼠右鍵,從快顯功能表上點選「隱藏」。

圖7:將暫時不想檢視的欄位選取後設定隱藏。
圖7:將暫時不想檢視的欄位選取後設定隱藏。

  將不需要檢視的資料隱藏起來,找尋目標資料(特定人的到職日),將顯得容易許多。又,如何取消隱藏呢?畢竟這些資料在不同場合仍有需要啊!檢視一下欄名之間,會有數道灰色的實線,這表示其間隱藏著資料,將相鄰的欄位選取起來,在欄名上按滑鼠右鍵後選取快顯功能表的「取消隱藏」,這些資料就會再次顯示出來。

圖8:取消隱藏,讓資料回復到原來的狀態。
圖8:取消隱藏,讓資料回復到原來的狀態。

  隱藏的功能並不限於「欄」,「列」亦可用同樣的方法操作以達目的。

  另一種檢視資料的情形則是希望具有說明性的資料如標題列能固定顯示,以便於單筆資料的項目眾多時,仍能對照標題列找到想要的資料項目(尤其資料的性質相似,如日期、金額等)。此時可以利用凍結窗格,直接將標題列凍結起來,如此當移動捲軸時,標題列仍然會固定顯示在最上方,便於知道檢視的欄位性質為何。

  將滑鼠移到標題欄、列交會的第一個儲存格(B2)按一下,使之成為作用儲存格,接著依序按「檢視」面板>凍結窗格>凍結窗格。

圖9:凍結窗格有多種方式,可依需要選擇。
圖9:凍結窗格有多種方式,可依需要選擇。

  此時不論拖曳縱向或橫向的捲軸,標題欄、列宛如冰凍般的顯示在最上、最左方,如此要檢閱資料就不需要上上下下、左左右右的反覆拖曳捲軸,確認沒看錯資料囉!

圖10:凍結窗格後,不論捲軸如何拖曳,都能檢視被凍結的內容。
圖10:凍結窗格後,不論捲軸如何拖曳,都能檢視被凍結的內容。

  之後若要取消凍結窗格,同樣在「檢視」面板>凍結窗格內可以看到原有的選項會變成「取消凍結窗格」,點選之即可取消。

  一旦凍結了窗格,其範圍就將固定不能調整,要變更範圍得重新設定。惟,缺點也是優點,凍結窗格也不會因誤調窗格大小而影響資料的檢視或編輯。大抵來說,單筆資料的項目較多或整體資料量大,無論編輯或檢視,凍結窗格都是必要的選擇。

樞紐分析表的應用與限制

  樞紐分析表是將建構好的資料加以統計、分析的簡易實用工具,操作簡單、直覺,只需用點選與拖曳的方式就可以將資料在四種性質的欄位之間任意組合,且分析結果會同步顯示,使用者很輕易就能嘗試各種排列組合直至得到需要的結果。

  此外,還可設定群組的區間範圍,以便統計出一定區間內的特定資料。例如機關內人員,年齡最低為25,最高為65歲,每10歲的間距內各有多少人?甚至是每一年齡區間內的男女性各多少人?透過這樣的統計很輕易就能了解機關內人員在年齡與性別的分布狀況是平均或失衡。就算採用多層次排序後再計算,也顯得有些麻煩。然採用樞紐分析表,顯得簡單許多。

  開啟範例檔案後,依序按「插入」面板>樞紐分析表。

圖11:建立樞紐分析表。
圖11:建立樞紐分析表。

  首先要決定資料的範圍。若未事先選取,就是將整個工作表相連的資料全部納入分析的範圍。如果只想要分析資料的一部份,可按右邊的選擇範圍鈕,以拖曳的方式加以選取。接著選擇放置樞紐分析表的位置,通常建議產生新工作表,避免與原有的資料混淆。

圖12:建立樞紐分析表的前置作業:選擇資料範圍、確定樞紐分析表放置之工作表。
圖12:建立樞紐分析表的前置作業:選擇資料範圍、確定樞紐分析表放置之工作表。

  此時會自動切換到樞紐分析表及其相關功能的面板,本範例希望能統計機關內俸給總額於每萬元區間內,男、女性各多少人。所以在右邊的樞紐分析表欄位,先勾選要新增到報表的項目:身分證字號(統計人數用)、性別、俸給總額。然後到下方的窗格以拖曳的方式將各項目移到適合的區域(如圖13)。此時樞紐分析表會根據各項目所在的區域性質,自動產生樞紐分析表。

  接著要設定萬元的區間。讓列標籤下方任一儲存格成為作用儲存格後,按一下面板上的「群組選取項目」。

圖13:指定的項目拖曳到不同的區域,樞紐分析表都會即時顯示變更後的結果。
圖13:指定的項目拖曳到不同的區域,樞紐分析表都會即時顯示變更後的結果。

  在「群組」的對話視窗,在「開始點」與「結束點」右方的欄位輸入需要計算的起迄數值,隨之在「間距值」輸入10000後按確定。

圖14:預設的群組起迄值是資料的最低和最高者。
圖14:預設的群組起迄值是資料的最低和最高者。

  此時就會計算出每萬元區間各有男、女性若干人。

圖15:完成分類統計的樞紐分析表。
圖15:完成分類統計的樞紐分析表。

  要注意的是,樞紐分析表在原始資料更動後,並不會自動更新,甚至增加須納入計算之資料後,還必須手動變更資料來源並按一下重新整理,才能得到最新的結果,對於可能隨時增減的資料(例如人員異動)而言,並不是那麼方便!如果是經常性使用的資料,不如透過各種函數建立一個分析、統計專用的結果顯示用工作表,無論資料如何增減,計算結果都會即時自動更新!

  為以函數建構的工作表,再怎麼說還是比較慢。有時需要的統計或分析結果,具有急迫性、臨時性、一次性且非常用的統計、分析項目,此時當然選擇樞紐分析表以求時效性。若是經常性使用,竊以為函數才是比較好的解決方法。

 

 

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

 

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