很多時候只要利用內建的工具,不用函數也可以事半功倍,再加上公式及函數的操作都已經十分簡化,只要觀念對了,自然就可以化繁為簡,輕鬆利用 Excel 製作分析報表。
以下就提供12個 Excel 實用技巧,不管你的 Office 是哪個版本,都可以找到對應的功能試試。從目錄可以快速到達想知道的技巧。
相關閱讀:Excel文字與公式串接合併,使用「””」及「&」符號,免用函數簡單好操作
本文目錄:
- 技巧1. 自訂數字格式,不怕 Key-in 錯
- 技巧2. 設定文字格式,儲存格正確顯示手機號碼
- 技巧3. 用資料驗證建立下拉選單,點選即可輸入
- 技巧4. 一個儲存格的資料分割到多個儲存格
- 技巧5. 自動刪除工作表中的重複資料
- 技巧6. 格式化條件設定,重點一目了然
- 技巧7. 格式化條件用圖示標記更直覺
- 技巧8. 指定多個條件,快速篩選結果
- 技巧9. 合併計算多個表格,數字也能保持連動
- 技巧10. 自訂排序規則,表格客製化呈現
- 技巧11. 匯入 Web 資料製作表格,免開網頁持續更新
- 技巧12. 瀑布圖掌握每個數字的即時變化
技巧1. 自訂數字格式,不怕 Key-in 錯
能夠活用儲存格,其實已經掌握 Excel 的使用,在資料輸入上,要輸入規律的文字,可以用滑鼠下拉儲存格的方式,完成大量公差的級數。但若是一組長串數字中,只需要變更最後幾個字碼,就可以利用自訂儲存格「數值」來簡化輸入,例如會員編號1060123,若10601為固定不變,只要設定好,日後只須輸入後面3碼即可。
▲1. 選取想要自訂的欄位後,於「字型」功能列表中,按下展開的圖示,開啟「儲存格格式」視窗。
▲2. 選擇「數字」分頁,並按下「自訂」後,於類型中輸入此例「10601000」,後方的「000」是日後可輸入的數字。
▲3. 回到工作表中,只要在步驟1選定的欄目中,鍵入任何3位數字,都會以「10601xxx」顯示。
技巧2. 設定文字格式,儲存格正確顯示手機號碼
在儲存格中輸入的數字若為「0」開頭時,在按下 Enter 後,0就會自動消失,最常見的例子就是輸入電話號碼,要能夠在儲存格中留下完整的電話號碼,就得讓儲存格辨識此為文字型式,因此在輸入0前,先加上「'」符號,然而,若是要輸入的電話較多,建議直接將儲存格設定為「文字」格式,日後就不用額外輸入「'」符號。
▲1. 在輸入電話號碼前, 先加上「'」符號, 例「'0911123113」,0就不會被取消。
▲2. 若有大量電話號碼要輸入,則在工作表中選定儲存格後,開啟「儲存格格式」視窗,選擇「數字」分頁,並按下「文字」。
▲3. 回到工作表中輸入電話號碼時,可見0同樣被保留下來。
技巧3. 用資料驗證建立下拉選單,點選即可輸入
在製作大型的資料表格時,經常會有一些固定的資料要填入儲存格裡,例如建立會員資料表時,性別、年齡、教育程度⋯等,常見會以選項的方式勾選,而回到活頁簿上要建立資料時,與其重複輸入特定的內容,不妨利用內建的「資料驗證」功能,將固定的選項建立為儲存格的選單,讓使用者用點選的方式即可完成。
▲1. 選取要建立選單的欄目後,找到「資料」標籤項下的「資料工具」,再點選「資料驗證」。
▲2. 在「儲存格內允許」選單下,找到「清單」,「來源」中輸入想要建立的選項,記得中間以半型「,」號格開。
▲3. 回到工作表就會發現儲存格旁有個灰色的三角型標示,按下就會出現上步驟建立的選單。
技巧4. 一個儲存格的資料分割到多個儲存格
如果想要將儲存格裡的資料分割為多個儲存格,一般人也許會選擇複製、貼上、刪除⋯,不斷的在儲存格間進行上述重複的動作,一來麻煩,二來也花費太多時間。其實只要利用 Excel 內建的「資料剖析」功能,就可以快速將儲存格裡的所有資料,依照需求分割至多個儲存格裡。
▲1. 選取想要分割的儲存格,在「資料工具」功能列表下,找到「資料剖析」,並勾選「固定寬度」。
▲2. 在「預覽分欄結果」下,按一下滑鼠叫出箭頭,再拉出想要分割的地方,且不限於只分割為兩欄。
▲3. 上步驟都選定完成後,回到工作表就會發現儲存格已經被切割為多個欄目。
技巧5. 自動刪除工作表中的重複資料
利用 Excel 製作的資料表格,經過長時間的使用後,不免會重複鍵入相同的資料,但當表格的資料愈來愈多,要在龐大的表格中,立即找到重複的項目並不容易,且一旦表格需要進行計算時,例如加總、平均、累計等等,重複的資料必會影響計算結果的正確性。因此,這裡介紹以 Excel 內建的「移除重複」功能,將表格中重複的資料自動刪除,以維護表格的正確定。不過,在進行「移除重複」功能,還可先利用「設定格式化的條件」,在龐大的表格裡,先找到「重複的值」後,再進一步選擇將重複的項目刪除。
▲1. 選取要比對的欄位,在「常用」標籤項下,找到「設定格式化的條件」,並於「醒目提示儲存格規格」下,選擇「重複的值」。
▲2. 在出現的視窗,選擇「重複」,就可自動標示重複儲存格,也可指定篩選重複值的顯示顏色。
▲3. 回到工作表中就可以看到,表格中已將找到相同的儲存格標示出來。
▲4. 要刪除重複的儲存格時,在「資料」標籤項下的「資料工具」裡,點選「移除重複」項目。
▲5. 於出現的視窗裡,勾選要刪除的資料欄位後,再按下「確定」。此例為106年會員編號、姓名。
▲6. 接著就會跳出提示視窗,告知已刪除的資料數,以及所保留的資料數,按下確定即完成。
還有很多 Excel 的技巧介紹哦 ~
技巧6. 格式化條件設定,重點一目了然
Excel 內建的「設定格式化的條件」實用性不小,預設功能中,就已經可以將龐大表格裡的資料進行挑選,這也算是資料篩選的一種,尤其對於不擅長使用函數或公式的人,幾個步驟就能立即找到工作表中所需的資料。例如將報表中超出預算的部分自動標示出來,當然,也可依照個人需求,活用大於、小於、等於的條件選項。
▲1. 選取要做為格式化的儲存格,並選擇「設定格式化的條件」下的「醒目提示儲存格規則」,此例以「大於」示範。
▲2. 跳出視窗後,先點選要做為比較條件的欄位,此例為預算金額,所以圖選B2,視窗裡便會出現「$B$2」。
▲3. 接著便可以在工作表中,一眼看出所有被格式化條件找出來的儲存格。
技巧7. 格式化條件用圖示標記更直覺
在「設定格式化的條件」裡,也可以選擇讓儲存格的資料以圖像化輔助數值顯示,讓儲存格裡的資料,更容易被解讀,例如繪製成所選「圖示集」是在設定格式化條件下,將選定的儲存格加上圖示標記,如此可以更直覺看出儲存格中資料的差異。此範例為檢視每月支出有沒有超出預算標準,超出顯示紅點,沒超出為綠點。
▲1. 選取要標記圖示的儲存格後,找到「設定格式化的條件」選項,選擇「圖示集」後找到「其他規格」。
▲2. 接著設定篩選的條件及顯示的圖示。此範以有沒有超出 B1儲存格數值為標準,圖示分別設定,類型則選擇「數值」。
▲3. 在進行上步驟的設定時,工作表其實會同步顯示,從中也可發現數值以圖示輔助後,更能突顯數字重點。
技巧8. 指定多個條件,快速篩選結果
雖然透過 Excel 內建的樞紐分析表可以化繁為簡,將複雜的 Excel 表格,依照所設定的條件,精簡呈現出所須要的資料,但如果需求沒有這麼複雜,只是要幾項簡單的篩選結果,其實透過 Excel 內建的資料篩選功能即可完成。而在篩選功能中,除了提供基本的篩選及排序外,在進階設定裡,還可以一次指定二個以上的條件,再由資料範圍中,快速搜尋到符合的資料。以下就以模擬要在會員名單中,找出年齡45歲以下的女性,而根據已有的會員資料表,設定條件就有二個,一個為女性、一個為<45,藉此快速篩選出符合的名單。
▲1. 開啟要進階篩選的會員資料表後,將想要篩選的條件欄目新增在活頁的空白處,此為性別及年齡。
▲2. 在上步驟建立的篩選欄目下方,設定篩選條件,此例在性別下輸入「女」,年齡為「<45」。
▲3. 找到「資料」標籤頁裡的「排序與篩選」,並按下「進階」選項。
▲4. 接著選擇「將篩選結果複製到其他地方」,Excel 2016後下方通常也會自動標示出篩選的資料範圍及條件位置。
▲5. 至於複製到的項目下,則於試算表空白處選擇篩選結果要置放的儲存位置。
▲6. 當上步驟都完成按下確定後,回到工作表上就會自動出現篩選的結果。
技巧9. 合併計算多個表格,數字也能保持連動
利用 Excel 製作表格很方便,但不免會遇到需要將多個相同表格,整合到一個表格的情況,常見的有加總、平均所有表格的數值,或是找出表格中最大值、最小值等等,以方便查詢或分析所有 Excel 表格的結果,但要來回切換不同的 Excel 表格,很容易發生數字輸入錯的人為失誤。這時不妨可以多加利用 Excel 內建的「合併彙算」功能,將所有表格的資料,自動整合到一個表格上,以減少人為出錯。以下以計算平均三年的非薪資收入為示範,透過合併彙算功能,將三個年度活頁簿上的資料,整合在同一個表格上呈現,並直接計算出平均值,且勾選「建立來源資料的連結」後,被合併的表格數值若有變更,整合的表格也會自動更新。
▲1. 先在 Excel 表格裡建立新的分頁,再於「資料」標籤項下的「資料工具」,找到「合併彙算」。
▲2. 於出現的「合併彙算」視窗裡,下拉「函數」選單,選擇表格要呈現的計算結果,此例為平均值。
▲3. 接著點選「參照位址」後方的圖示,選取要合併彙算的範圍,再按下「新增」。
▲4. 重覆上一個步驟,持續將要「合併彙算」的表格「新增」完成。
▲5. 勾選視窗下的「頂端列」、「最左欄」、「建立來源資料的連結」,日後單一表格更改時,也會連帶變動合併的表格資料。
▲6. 接著就會自動出現合併計算後的表格,而展開前方的「+」號,還能見到計算的明細。
別錯過最後一頁的教學囉 ~
技巧10. 自訂排序規則,表格客製化呈現
在分析 Excel 表格資料中,「排序」也是經常被使用到的功能,因此在功能表中,也有快捷功能鍵可以設定,不過預設的功能都只有由小排到大,或由大排到小,中文則會依照筆畫進行排序,如果想要的排序不是依照筆畫的多寡,就可以手動自訂排序規則,且還能夠依照需求,指定多個排序條件。這裡就以會員第一季的表現做為排序目標,也因為內建的排序清單中,沒有表格上的內容可以選擇,因此必須以手動新增排序清單後,Excel 才能進行判斷及排序。此外,為了讓性別也能排序,同樣以自訂排序規則的方式,新增第二個排序條件,當然,如果有其他需求,還是可以再新增更多排序規則。
▲1. 在工作表中選取要排序的欄位,記得標題也要選取,並在「資料」標籤項下,按下「排序」功能鍵。
▲2. 在出現的視窗中,排序方式選「第一季」,排序對象選「值」,排序選「自訂清單」。
▲3. 在清單項目中,輸入表格中要排序的內容,記得是一個項目一行,完成後按下「新增」。
▲4. 接著在自訂清單處,就可以看到上步驟新增的內容,點選後按下「確定」。
▲5. 回到步驟2的地方,按下「新增層級」,進行第二個排序規則的設定。
▲6. 完成後回到工作表,就會呈現排序後的結果,此例除了依第一季內容排序外,性別同時也完成排序。
技巧11. 匯入 Web 資料製作表格,免開網頁持續更新
在 Excel 內建的外部資料匯入功能中,可選擇匯入資料庫、文字檔、XML 等等,其中也支援 Web 匯入資料的選項,也就是可選擇從網路載入資料製作表格,且只要維持在網路連線的狀態下,就可以透過網路隨時更新資訊的特性,隨時按下「重新整理」,就能夠同步更新 Excel 裡的資料。這個功能的好處就是可以在同一個 Excel 分頁表中,掌握到不同訊息的變更,讓使用者在不同網頁間不斷切換,甚至還能進一步設計表格資料之間的計算方式,如此只要按下 Excel 表格中的「全部重新整理」,就可以立即獲得想要的資料。以下則以載入 Yahoo!奇摩股市的個人投資組為合示範,日後免開網頁也能更新股票金額。
▲1. 將要匯入 Web 資料的工作表開啟後,在「資料」標籤頁,找到「從 Web」選項。
▲2. 跳出的視窗就像小尺寸的瀏覽器一樣,輸入想要擷取資料的網址,再按下「到」,這裡以 Yahoo!奇摩股市為例。
▲3. 在載入的網頁上,將想要載入 Excel 工作表的內容前方,按下黃色箭頭圖示,變成藍色後再按「匯入」。
▲4. 回到 Excel 工作表上,於匯入資料的視窗裡,選擇儲存格的位置,再按下「確定」。
▲5. 靜待幾秒後,表格就會載入至工作表裡,平時按下「全部重新整理」就會同步更新網路上的資料。
▲6. 按下「全部重新整理」旁的「內容」,可選「每隔幾分鐘更新一次」,並手動設定分鐘數即可。
技巧12. 瀑布圖掌握每個數字的即時變化
雖然最新版本的 Excel 2016或是 office 365裡的 Excel,外觀看起來和 Excel 2013相似度極高,但內部新增許多功能,其中圖表也較過去更為豐富,包括新增瀑布圖、樹狀圖、放射環狀圖、直方圖、盒鬚圖、3D 地圖,每個圖表都有各別突顯的功能,讓使用者可更精確地呈現資料,且只要選擇原始資料,Excel 就會自動生成圖表,再細部調整即可。當中,瀑布圖可用來顯示一個數字到另一個字數間的變化過程,用在分析企業的財務資料更顯優勢,而一般使用者也能透過瀑布圖清楚呈現收支的變化。以下則示範以瀑布圖來觀察期存貨的變化,在期間內的存貨增減,都可以透過圖表來掌握。
▲1. 在工作表中選擇要製成圖表的欄位後,找到「插入」標籤頁下的圖表,按下「瀑布圖」。
▲2. 在出現的瀑布圖中,看到每項數字的變化結果,而此例的期末存貨應為總結,必須由0開始才對,因此點選兩下進行變更。
▲3. 進入編輯頁面後,按下右側的「數列選項」,並勾選「設為總計」。
▲4. 此時期末存貨就會變成另一個顏色,並且貼齊底部的起始線,也代表是表格中的總結數值。
▲5. 除了預設的瀑布圖樣式外,在上方的「圖表樣示」有不同的形式可以選擇。
▲6. 也可以按下右側的「效果」,調整色彩、大小、透明度,以及特效等視覺變化。
本文同步刊載於PC home雜誌
歡迎加入PC home雜誌粉絲團!
請注意!留言要自負法律責任,相關案例層出不窮,請慎重發文!