網路城邦
Office學園
市長：
三狂
副市長：
加入本城市
｜
推薦本城市
｜
加入我的最愛
｜
訂閱最新文章
udn
／
城市
／
資訊科技
／
軟體應用
／
【Office學園】城市
／討論區／
你還沒有登入喔(
馬上登入
/
加入會員
)
本城市首頁
討論區
精華區
投票區
影像館
推薦連結
公告區
訪客簿
市政中心
(0)
討論區
／
Excel
字體：
小
中
大
111-隨你心意來詮釋Excel報表資料
瀏覽
7,414
｜回應
0
｜
推薦
0
三狂
等級：8
留言
｜
加入好友
善用「進階篩選+陣列轉換」功能，賦予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.按下「轉置」之後，即出現與使用「選擇性貼上」的轉置，相同的結果。
本文於 修改第 2 次
引用網址：https://city.udn.com/forum/trackback.jsp?no=61901&aid=3219528
本城市首頁
等級：8
