技巧6. 格式化條件設定,重點一目了然

Excel 內建的「設定格式化的條件」實用性不小,預設功能中,就已經可以將龐大表格裡的資料進行挑選,這也算是資料篩選的一種,尤其對於不擅長使用函數或公式的人,幾個步驟就能立即找到工作表中所需的資料。例如將報表中超出預算的部分自動標示出來,當然,也可依照個人需求,活用大於、小於、等於的條件選項。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲1. 選取要做為格式化的儲存格,並選擇「設定格式化的條件」下的「醒目提示儲存格規則」,此例以「大於」示範。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲2. 跳出視窗後,先點選要做為比較條件的欄位,此例為預算金額,所以圖選B2,視窗裡便會出現「$B$2」。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲3. 接著便可以在工作表中,一眼看出所有被格式化條件找出來的儲存格。

技巧7. 格式化條件用圖示標記更直覺

在「設定格式化的條件」裡,也可以選擇讓儲存格的資料以圖像化輔助數值顯示,讓儲存格裡的資料,更容易被解讀,例如繪製成所選「圖示集」是在設定格式化條件下,將選定的儲存格加上圖示標記,如此可以更直覺看出儲存格中資料的差異。此範例為檢視每月支出有沒有超出預算標準,超出顯示紅點,沒超出為綠點。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲1. 選取要標記圖示的儲存格後,找到「設定格式化的條件」選項,選擇「圖示集」後找到「其他規格」。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲2. 接著設定篩選的條件及顯示的圖示。此範以有沒有超出 B1儲存格數值為標準,圖示分別設定,類型則選擇「數值」。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲3. 在進行上步驟的設定時,工作表其實會同步顯示,從中也可發現數值以圖示輔助後,更能突顯數字重點。

技巧8. 指定多個條件,快速篩選結果

雖然透過 Excel 內建的樞紐分析表可以化繁為簡,將複雜的 Excel 表格,依照所設定的條件,精簡呈現出所須要的資料,但如果需求沒有這麼複雜,只是要幾項簡單的篩選結果,其實透過 Excel 內建的資料篩選功能即可完成。而在篩選功能中,除了提供基本的篩選及排序外,在進階設定裡,還可以一次指定二個以上的條件,再由資料範圍中,快速搜尋到符合的資料。以下就以模擬要在會員名單中,找出年齡45歲以下的女性,而根據已有的會員資料表,設定條件就有二個,一個為女性、一個為<45,藉此快速篩選出符合的名單。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲1. 開啟要進階篩選的會員資料表後,將想要篩選的條件欄目新增在活頁的空白處,此為性別及年齡。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲2. 在上步驟建立的篩選欄目下方,設定篩選條件,此例在性別下輸入「女」,年齡為「<45」。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲3. 找到「資料」標籤頁裡的「排序與篩選」,並按下「進階」選項。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲4. 接著選擇「將篩選結果複製到其他地方」,Excel 2016後下方通常也會自動標示出篩選的資料範圍及條件位置。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲5. 至於複製到的項目下,則於試算表空白處選擇篩選結果要置放的儲存位置。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲6. 當上步驟都完成按下確定後,回到工作表上就會自動出現篩選的結果。

技巧9. 合併計算多個表格,數字也能保持連動

利用 Excel 製作表格很方便,但不免會遇到需要將多個相同表格,整合到一個表格的情況,常見的有加總、平均所有表格的數值,或是找出表格中最大值、最小值等等,以方便查詢或分析所有 Excel 表格的結果,但要來回切換不同的 Excel 表格,很容易發生數字輸入錯的人為失誤。這時不妨可以多加利用 Excel 內建的「合併彙算」功能,將所有表格的資料,自動整合到一個表格上,以減少人為出錯。以下以計算平均三年的非薪資收入為示範,透過合併彙算功能,將三個年度活頁簿上的資料,整合在同一個表格上呈現,並直接計算出平均值,且勾選「建立來源資料的連結」後,被合併的表格數值若有變更,整合的表格也會自動更新。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲1. 先在 Excel 表格裡建立新的分頁,再於「資料」標籤項下的「資料工具」,找到「合併彙算」。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲2. 於出現的「合併彙算」視窗裡,下拉「函數」選單,選擇表格要呈現的計算結果,此例為平均值。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲3. 接著點選「參照位址」後方的圖示,選取要合併彙算的範圍,再按下「新增」。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲4. 重覆上一個步驟,持續將要「合併彙算」的表格「新增」完成。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲5. 勾選視窗下的「頂端列」、「最左欄」、「建立來源資料的連結」,日後單一表格更改時,也會連帶變動合併的表格資料。

【Office 實用技能精選】Excel 篇- 12招活用 Excel 的增值技
▲6. 接著就會自動出現合併計算後的表格,而展開前方的「+」號,還能見到計算的明細。

別錯過最後一頁的教學囉 ~

使用 Facebook 留言

發表回應

謹慎發言,尊重彼此。按此展開留言規則