隨你心意來詮釋Excel報表資料

善用「進階篩選+陣列轉換」功能,賦予Excel資料新涵義


作者/Anita duanmuzhi@gmail.com

對中小企業來說,管理人事資料可能面臨一種狀況,就是員工人數說多不多,說少卻又不少,在這種情況下,若是要建立一個資料庫來管理人事,好像有點「殺雞焉用牛刀」的感覺。可是如果用Excel來建立人事資料,又會覺得沒有資料庫的方便,例如:要在所有的人事資料中,以單一條件搜尋出符合設定條件的人員,只要員工資料有六、七十筆以上,光是找一項特定資料,就得花去不少時間了,而且,還得擔心是否有疏漏之處呢!

其實,只要知道如何在Excel中設定進階篩選條件,就可以輕鬆的在眾多筆資料中,快速找出符合需求的資料,另外,再加上陣列轉換功能,更可以對篩選出來的資料進行資料轉置,以便應用在簡報、製作薪資結構分析的圖表…等場合,因此,以下就讓我們來介紹並示範如何設定進階篩選條件,以及資料表的轉置。



藉由進階篩選的功能,可以更容易掌控資料,不僅僅在於人事資料表上頭,連銷售分析表,損益表等,都可以運用。

設定進階篩選的條件

談到進階篩選,就得先知道如何輸入、設定進階篩選的條件。在Excel中,我們最常用來篩選的方法,不外乎是「and」和「or」這兩個篩選邏輯。

舉例來說,在銷售報表中,想要得知「吹風機銷售數目大於5萬支」的地區時,就得利用「and」邏輯,在同一列但不同欄的儲存格裡,分別輸入「>50,000」、「吹風機」這兩個篩選條件,來篩選出同時符合這兩個條件的地區。

但是,假如你想要從銷售報表中,得知「產品銷售總數大於5萬」或「產品銷售總金額大於50,000,000」的地區時,就得利用「or」邏輯,在不同列、不同欄的儲存格裡,分別輸入「>50,000」、「>50,000,000」這兩個篩選條件,來篩選出符合其中一個條件的地區。


1.在設定條件之前,我們當然得先有一份已建好資料的資料表,如範例中的人事資料表。


2.在這一份資料表中,假設我們要篩選年資在二十年以上,「and」薪資在三萬五千元以下的人員,可以先在旁邊的空白儲存格中輸入篩選條件,而篩選條件輸入在何處,並不影響篩選結果,所以,我們分別在I5、J5同一列但不同欄的儲存格裡,輸入「>=20」和「<=35000」這兩個篩選條件。


3.如果設定的篩選條件是年資超過二十五年,「or」薪資未滿三萬元的人員時,那麼,就得在不同列不同欄的儲存格裡輸入篩選條件,所以,我們分別在I5、J6儲存格裡輸入「>25」和「<30000」這兩個篩選條件。

為進階篩選訂範圍

進階篩選的工作窗格,除了選擇執行結果的地方之外,最重要的就是設定資料和準則的範圍,所謂資料範圍指的就是原本要進行篩選的整張工作表格,而準則範圍則是我們所設定的條件。另外,若是你選擇執行的結果,不覆蓋原有資料的話,可能還要多設定一個將結果複製到何處的範圍。了解這些,有助於我們進行以下的操作:


1.選擇「資料」/「篩選」/「進階篩選」,進入進階篩選的工作窗格中。


2.輸入資料範圍,可以在右側空白處直接輸入,也可以按下右方的按鈕之後,直接以滑鼠選擇範圍。


3.接著輸入準則範圍,同步驟二一樣,可直接在空白處輸入或按下右方鈕,以滑鼠選擇範圍。


4.假使執行的選項不是選擇「在原有範圍顯示篩選結果」,而是選擇「將篩選結果複製到其他地方」,則必定要選擇「複製到」何處(可存放於不同工作表或同一工作表的任一地方),步驟則同二、三。


5.之後按下確定,會發現篩選的資料已出現在你設定複製的位置上了。

如何使用陣列轉換函數

有時或許為了將結果製成圖表,或是要將之放在簡報中來呈現,必須做列與欄之間的轉換時,可以使用TRANSPOSE函數,此函數的功能主要是轉換陣列,在此我們必須知道陣列公式是針對一或多組值執行多個計算,然後傳回單一結果或多個結果的公式。它括在大括弧{}中,藉由按 CTRL+SHIFT+ENTER來輸入,因此只要選定轉置位置,在選定的儲存格中輸入公式即可。


1.要將I8:O32的資料表做列與欄的轉置,首先先在選定的位置上,如範例中的位置是在「人事」工作表中的A1儲存格,打上「=TRANSPOSE(人事資料!I8:O32)」。


2.接著按照要轉置的那張工作表的欄與列以相反數複製,如原表中列有二十五列,欄有七欄,現在就把步驟一打的公式往下複製六列,共七列,往右複製二十四列,共二十五列。


3.完成之後,將游標留在資料編輯列上,按下CTRL+SHIFT+ENTER,就可以看到轉置後的資料出現了。


4.在轉置之後,日期列的格式因為未設定,所以,我們選取B5:Y5的儲存格,按滑鼠右鍵,進入儲存格格式設定數值類別為日期。

從「選擇性貼上」的工作窗格進行轉置

以陣列公式來轉置資料,可以保持資料的一致性,但是如果想要編輯運用轉置後的資料,就顯示有點綁手綁腳了;因此,在這邊我們介紹另一種轉置方式,並不會將轉置後的資料規劃成陣列,只是單純的做列與欄的轉置,既可以做到資料轉置,又可以保持原來資料表的基本格式設定。


1.首先先回到存放篩選後資料的「人事資料」表,複製I8:O32的內容。


2.接著選擇轉置後要放置的位置,如範例,我們會將資料放在另一張工作表上;因此選取A10儲存格之後,選擇「編輯」/「選擇性貼上」,進入「選擇性貼上」的工作窗格。


3.在「選擇性貼上」的工作窗格最後一列右方,有個選項是「轉置」,勾選它之後按下「確定」,轉置成功。

最容易的轉置方式

在凡事講求效率的工作場合中,如何能在完成工作的同時,又要求完成時間快速呢?其實很多功能,不只有一種執行方式;因此,以下就來了解,轉置除了以陣列公式來處理或是利用選擇性貼上的方式之外,其實還有更快速的方式。


1.首先仍然先複製要轉置的資料,因此我們對人事資料表中I8:O32的資料進行複製後,就到我們選擇放置轉置後的資料位置。


2.此「一般」工具列上,有個「貼上」的按鈕右側,有個黑色的小倒三角形,將它按下,會出現一張選單,選擇第四個選項「轉置」。


3.按下「轉置」之後,即出現與使用「選擇性貼上」的轉置,相同的結果。


創作者介紹

異想夢遊

peiyunlin 發表在 痞客邦 PIXNET 留言(0) 人氣()