前言
很多人在用Excel做報表時,往往都花費很多時間,卻因為不懂方法去驗算每個數字的對錯,所以常常會出現錯誤的資訊。這是很讓人挫折的經驗。
其實Excel是一個隻要學習部分功能就能發揮強大效益的工具。盡管系統内置了許多功能和函數,但是隻要掌握最基礎的觀念和做法,就足以應付職場裡大部分的資料分析需求,讓你的作業效率大幅提升,快速交出報表,還能确保報表數字的正确性。
在接下來的内容裡,我們将按照輸入資料、整理資料、資料運算、分析資料和制作圖表這5個商務場景,介紹Excel裡9個一定會用到的功能。
1.設定單元格格式
選對格式、準确輸入,更改類别設定不用重複輸入
1-文字格式/手機号碼要這樣輸入,第一個0才會出現
不需要計算的資料,最好設定為文字格式,才不會被Excel自動判斷為數值,改變呈現的樣貌。 最常見的例子是,你想輸入員工編号,明明輸入的是088xx,最後顯示出來卻變成88xx,首位0莫名其妙的消失了。這是因為Excel誤判為數值,而0在數值首位是沒有意義的數字,會被直接舍棄。其他像辦公電話、統一發票号碼等,都是屬于可以設定為文字的格式的類型。
方法一:加個單引号
要讓數值變數字,可以在輸入時先鍵入單引号['],例如['0193'],此時電話号碼的數字就會顯示最前面的0
方法二:統一改為文字格式
①先點選要改變格式的儲存格 ②按右鍵選擇[設置單元格格式] ③把[數值]改成[文本] ④按下确定即可
2-數值格式:想用來運算的資料,都要設置為數值
有時候運用函數計算卻發現結果有誤,可能就是儲存格式格式設定錯誤導緻的。比如說:常用來求和的SUM函數,碰到不是數組格式的數字會自動視為0,所以如果你輸入的資料要用來運算,最好設置為數值格式。 除了方便計算,數值格式裡還有許多呈現資料的格式,可以幫助你凸顯報表的重點。
3-日期格式/年、月、日、星期,一個鍵就自由切換
excel内置的日期和時間格式有很多種,方便你切換資料呈現的樣子,唯一的關鍵就是輸入資料時必須按照标準打法[年/月/日 24小時制的時間],例如2020/01/01 14:00:00,這樣Excel就能辨别出這個資料是時間而非數值。之後就可以仰賴Excel儲存格式的功能,變換需要的呈現形式,不需要重新輸入資料。
2.儲存單元格
搞懂相對引用與絕對引用,複制公式不出錯
1、相對位置:複制函數到其他單元格,Excel會自動調整公式行列設定
一般來說,你可以适用SUM函數計算總銷售,例如在C6單元格輸入函數【=SUM(C3:C5)】,就能得到産品A的銷售總和。 想接着計算計算産品B的銷量,隻要複制黏貼上A的銷量和函數到D6就好了。Exceld的預設狀态就會幫助你調整公公式,方便你在第一時間輸入函數,可以直接複制和套用。不過,這份貼心的設計,有時候就是造成公式【跑掉】的原因。 比方說:你在同個工作表裡設計了另一個表格【年度銷售統計表】,打算把剛剛算出來的産品A第四季度的銷量複制到年度統計裡,一複制黏貼上卻發現,明明要C13顯示第四季度銷量,缺出現了自動調整的函數【SUM(C10:C12)】!Excel的善意反而成為了困擾。
2、混合位置:無論怎麼複制,都要計算制定的單元格
如果想要确保公式在複制黏貼上之後,會呈現你想要的樣子,就要靠你的手工調整,告訴Excel不論把公式複制到哪裡,都要計算你指定的哪一個單元格,别随便變動位置。 這個功能的【暗号】就死金錢符号【$】,表示[固定]。例如原本在輸入公式時,單元格表示方式為A1,如果改成輸入[$A$1],表示行不動、列不動。之後複制公式到其他單元格時,都是以A1來計算。假如你隻希望固定行,但列依然要請Excel自動調整,就隻要在行位旁邊加上金錢符号,如【$A1】;若隻想固定列,行位依然要請Excel自動調整,就隻要在列位旁邊加上金錢符号 ,如【A$1】。
想要确認自己是不是弄清楚【位置】的概念,不妨來玩一個小測試:
你可以再C3設定一個公式,再經過複制黏貼上,不用手工修改公式,就完成[九九乘法表]嗎?沒有位置觀念的人會直接輸入[=C2*B3],要是直接往下和往右複制公式,Excel的自動調整公司行業功能,會導緻所有該相乘的數字都跑掉,無法得出九九乘法表應該有的結果。 那究竟怎麼設定公式才會正确?其實隻要掌握一個事就好;【不管公式估值到哪裡,永遠用B行的數字和第2列的數字相乘】,所有看到行号是B的就使用金錢符号,列位是2的也用金錢符号固定住就好了。
3.單一/多重行列排序
把資料分門别類排列整齊,讓業績一目了然
如果你的資料是随機輸入的、沒有特别整理過,通常很難從中看出數據的脈絡,得出有助于商業判斷的結果。Excel裡最常被用到的資料整理功能,叫做【排序】,專門協助你将資料分門别類,将同類型的資料排列在一起,方便閱讀。
1、單一列排序:依照業績來排名
假如公司規定,隻有當月業績排名前3名才能平分獎金。那可以先按照每個業務員的銷售金額高低進行排序,銷售額越高的員工,名詞越靠前,這種功能叫做【單一列排序】
2、多個列排序:在特定銷售區域裡,看出業務員的業績排名
如果是因為第一個區的業績增長最高,公司額外獎勵一筆激勵費用,那光按照銷售金額排名還不夠的。Excel裡設有【多個列排序】,可以針對兩個以上的分類來排序,也就是一次性将2個列都排出順序來,讓資料更細緻的分類歸納。回到上述例子,主管必須将【銷地區】作為第一層的排序分類,而同個銷售地區的資料要以【銷售金額】當做第二層的排序分類,由大到小的排序
4.自動篩選
從上萬筆資料中,快速挑出需要的信息
面對上萬筆資料,篩選絕對是你的好幫手。它可以快速挑出你想看的資料内容,而你不想要的資料則會被隐藏起來。 下面是一份人事資料表,裡面包含公司裡每個員工的編号、姓名、入職時間、工齡、生日等信息。身為人事主管,你要挑出幾個人選,作為可能派往其他分公司的名單。
1、文字資料篩選:隻顯示戶籍地為台中的員工資料
2、數字資料篩選:資曆在兩年以下的人選全部排除
5.SUMIF與COUNTIF
想指定項目計數和求和,用函數公式設計你的條件
學會輸入和整理資料後,接下來就進入Excel的核心功能:數據運算。不過,有時候隻想針對【特定條件】的項目做求和或者計數,這時候可以使用SUMIF和COUNTIF函數來實現。
SUMIF:隻有符合條件的項目,才會被[加總]計算
SUMIF(數據範圍,條件,要求和的區間)
COUNTIF:隻有符合指定條件的項目,才會被計算【個數】
6.小計
不用函數也可以做運算,9步驟完成資料分類統計
還沒學會許多函數,就要交報表,該怎麼樣先做簡單的資料運算和整理?為了方便使用者建立資料的摘要,Excel内置【小計】的功能,可以幫你把資料分門别類、快速統計。完全不需要用到任何函數!
1、小計:一次算出各部門的申請總額
①将資料按照[申購部門]進行排序;
②點選[數據]功能選項卡中的[分類彙總];
③設定分組:分類字段/小計設置為【申購部門】,這是告訴Excel請按照部門來分類資料,再進行統計;
④設定使用函數為【求和】;
⑤新增小計位置勾選【求和】;
⑥勾選【替代當前分類彙總】(取代當前小計);
⑦視情況勾選【每組數據分頁】;
⑧勾選【彙總結果顯示在數據下方】;
⑨按下【确定】,完成分類彙總。
7.樞紐分析
精通樞紐功能,8成分析需求迎刃而解
要想再精進Excel技巧,别着急投入研究複雜的函數,而是要先弄清楚Excel内置的【樞紐分析】,就是excel表格中的數據透視表。這個項目操作起來簡單、方便上手,但功能卻十分強大,可以說是Excel最重要的精髓。幾乎可以解決8成的Excel分析需求,幫你洞察資料内真正有意義的信息。
1、建立樞紐分析表:設定資料分析的範圍,建立新的工作表
假定A公司需要請銷售部分做一次市場調查,以決定是否進入新的行業。
4步完成透視表的新建;【快捷鍵為ALT-D-P】
①在【插入】中選擇【數據透視表】; ②确認數據範圍; ③選擇放置分析表的位置; ④确定
2、設定樞紐分析表的組成:選擇需要的字段,擺到對應的位置
樞紐分析表分為上下兩個區塊,上半部是勾選想出現在報表上的資料字段,下半部分則是你希望資料出現在報表的哪個位置。
3、樞紐分析表的資料分組:時間/金錢等數字資料,可以合并為一組
8.數據可視化
把樞紐分析表做成圖,變身互動式圖表
Excel和其他Office系列的軟件一樣,都有内置的繪制圖表的功能,可以将數字表格化為可視化圖表。
1、建立樞紐分析圖:在一般工作表中,就能建立新圖表
2、設定樞紐分析表的組成:将字段拖放到對應的位置,作出想要的圖表
3、整理樞紐分析圖:運用篩選功能,過濾不想看到的字段
9.地圖功能
數據與地圖對照,利于比較、掌握各地概括
如果你想利用Excel圖表呈現某産品在全球的銷售概括,但是又嫌直線圖、餅圖過于簡單,那麼你可以适應office365訂閱版本中内置的Excel[地圖]功能來繪制視覺化圖表,用地理位置來對照數值或類别,更容易理解與溝通。
以上為本章介紹的9個Excel小技能,快快動手實際操作起來吧。
,
更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!