您使用的是較舊的瀏覽器版本。若要獲得最佳 MSN 體驗,請使用支援的版本

搞定10個EXCEL難題,不再抓狂

Cheers Cheers 2016/4/15 許紫寧
© 由 Cheers 提供

很多人都會使用Excel,但很少人能將它用到“Excel-lent”(卓越)。Excel不只是能填數字的工具,它的強大功能可以節省許多整理表格的力氣與時間,讓工作更順利!

「要學好Excel,觀念絕對比技巧更重要,」Microsoft Office專家王作桓指出。王作桓現為台灣微軟Microsoft Office內部訓練資深講師,連續11年榮獲「微軟全球最有價值專家」(Microsoft MVP Award)殊榮,擁有超過20年企業電腦課程規劃、訓練資歷,著有《Excel工作現場實戰寶典》、《Office2013提昇企業效率達人實戰技》等書。

請王作桓來解答一般工作者最常見的Excel問題,搞懂觀念再學技巧,你也能晉級Excel達人。

Q1使用Excel的基本觀念是什麼?

報表格式要正確:

Excel被創造的目的是資料分析,所以正確的「資料庫格式」(database)是報表能否製作完善的關鍵。正確的資料庫格式就是以第一列為資料標題,每筆資料再由上往下、一列列輸入,中間不可留空,也不要插入函數,保持資料完整乾淨。

盡量使用樞紐分析:

善用Excel的內建工具,8成報表都可以用樞紐分析(詳見Q10),有效率又好上手,剩下2成不能用樞紐分析製成的報表,才使用函數。

設對函數才能提高效率:

函數的相對、絕對位址(address)設定要正確。因為Excel在複製貼上公式時,都會自動調整運算範圍,函數位址設立正確,就能提高效率。

Q2哪些快捷鍵一定要記起來?

Ctrl+上/下/左/右鍵:

不用點擊滑鼠,就可以快速跳到資料表4個角落的儲存格。

Ctrl+Shift+數字鍵:

圈選要換單位的儲存格後,按搭配鍵與數字,即可轉換數字單位。

F4功能鍵:

可重複上一個步驟。假設最後一個動作是在上方插入空白行,再按一次F4就能重複上方插入空白行。

Alt鍵+「=」:

快速算出數字總和。在要填入總和的儲存格,按下「Alt」和「=」,就可快速得到數字總和。

Q3如何用Excel做數學運算?

運用Excel做基本加、減、乘、除很簡單,只要在答案格中先輸入「=」,游標圈選要運算的數值,再輸入四則運算公式,就能得到答案了。

例如,想計算B2、C2儲存格數字相乘,只要在答案格D2輸入「=B2*C2」的乘法公式,就能算出答案;接著可以拖曳這個儲存格來填滿其他空格,不用一格格計算,公式會自動調整範圍。

Q4如何合併兩格文字、又不用重新打字?

針對這個問題,使用「&」運算符號或是CONCATENATE函數,就能將多個列中的文本連接起來。例如,想合併右表的姓名與部門名稱,在單元格C1中鍵入公式「=A1&" "&B1」,或者是CONCATENATE(A1," ",B1)即可。公式中的空格代表在顯示文字間插入空格。

Q5如何把數據資料變成圖表?

有時一連串的數字令人暈頭轉向,表格化的資訊反而清楚。想快速建立圖表,只要圈選想做成圖表的儲存格數據,然後按F11功能鍵,就能馬上完成一張圖表。

Q6下拉式選單如何使用?

製作大型報表時,常需要輸入固定重複的資料,例如是否出席的「是」、「否」、「未回覆」等。如果把資料轉換成下拉式選單,就不用一一複製貼上,直接點選清單,省時又能提高正確率。

1.選取要做下拉式清單的儲存格。點上方工具列「資料」的「資料驗證」。

2.接著在「設定」中,儲存格內允許欄位選擇「清單」;在來源欄位填入你希望顯示的項目「是,否,未回覆」,選項間須用英文逗號分隔。按下確定即完成。

Q7從A到Z大家都會排序,但換成中文字怎麼排序?

碰到想用中文字排序或自訂順序時,很多人會傷腦筋,其實解決方法很簡單:

1.先點選欲排序的儲存格,接著點選上方工具列「資料」的「排序」,選擇「自訂排序」。

2.在跳出來的對話框中,點選「順序」下拉選單中的「自訂清單」。清單中可以找到如星期、月分等類別,也可以新增清單,點選後按下確定,即可排序完成。

Q8表格想從橫式改直式,怎麼做最快?

好不容易做完橫式表格,老闆卻堅持要改直式的窘境,不少人會摸摸鼻子,一個個重新複製貼上。但其實Excel有個超好用功能:轉置表格。

首先,「複製」要使用的表格,選取要貼上的位置後,按右鍵清單的「選擇性貼上」,勾取「轉置」再按確定即大功告成。

Q9出現像亂碼的數值代表什麼意思?

輸入公式時,常常會出現看似亂碼的文字,但每串亂碼都有其意義,只要看懂並修正,就可以順利解決。

亂碼:#DIV/0!

原因:公式中的除數為0或為空白值,如500/0

解決方法:避免除數為0或空白的狀況,或使用If等函數控制

亂碼:#NULL!

原因:使用不正確或沒有相交集的範圍運算

解決方法:修正範圍運算

亂碼:#NUM!

原因:公式中使用不正確的參數,或公式結果的值太大或太小

解決方法:逐步修正公式中的錯誤

亂碼:#NAME?

原因:在公式中使用了Excel不能辨別的文本或運算範圍時,產生錯誤值

解決方法:檢查名稱,找出並修正公式中的錯誤

亂碼:#REF!

原因:公式引用的欄位被刪除或是欄位中有誤

解決方法:修正欄位中的錯誤,或點選上一步復原正確的狀態

亂碼:#VALUE

原因:1.公式中本該數字與數字運算,卻將數字與文字運算,造成錯誤

2.公式本該引用單一數值,卻不小心引用了一個區域

解決方法:1.修改公式中的文字格式,輸入正確數字

2.修正公式為引用區域的函數,或修正引用的區域為單一數值

Q10「樞紐分析」是什麼?

最被王作桓推薦必學的內建工具就是「樞紐分析」。

樞紐分析一般用來快速合併或比較大量資料,用樞紐分析製成的報表,可依不同需求隱藏或露出欄位,設定好的儲存格公式也能自動進行運算。

以採買用品的資料為例,建立樞紐分析表的步驟如下:

1.先建立正確的資料庫格式,確認資料具有欄標題或表格標題,而且不含空白列。

2.選取表格範圍,接著點選上方工作列「插入」的「樞紐分析表」功能,按確定後,新增到新工作表。

3.在右方樞紐分析表的欄位清單中,勾選想呈現的欄位,並可在右下方拖曳欄位名稱,自行排列資料出現在左方報表的位置順序。

如此一來,報表就不像原始資料沒有分類,樞紐分析過的資料既分類呈現,數值也自動運算完成,一眼即可掌握。

更多來自Cheers的文章

image beaconimage beaconimage beacon