如何七周成為數據分析師02:Excel技巧大揭秘

10 評論 49106 瀏覽 318 收藏 14 分鐘

本文是《如何七周成為數據分析師》的第二篇教程,如果想要了解寫作初衷,可以先行閱讀七周指南。溫馨提示:如果您已經熟悉Excel,大可不必再看這篇文章,或只挑選部分。

上一篇文章《數據分析:常見的Excel函數全部涵蓋在這里了》教了大家常用的函數,今天講解Excel的技巧。

本次講解依然是提綱,圖文部分引用自百度經驗。如果有疑問或建議,可以留言給我,也可以網上搜索。內容方面照舊會補充SQL和Python。

快捷鍵

Excel的快捷鍵很多,以下主要是能提高效率:

Crtl+方向鍵,對單元格光標快速移動,移動到數據邊緣(空格位置)。

Crtl+Shift+方向鍵,對單元格快讀框選,選擇到數據邊緣(空格位置)。

Ctrrl+空格鍵,選定整列。

Shift+空格鍵,選定整行。

Ctrl+A,選擇整張表內容。

Alt+Enter,換行。

Ctrl+Enter,以當前單元格為始,往下填充數據和函數。

Ctrl+S,快讀保存,你懂的。

Ctrl+Z,撤回當前操作。

如果是效率達人,可以學習更多快捷鍵。Mac用戶的ctrl一般需要用command替換。

格式轉換

Excel的格式及轉換很容易忽略,但格式會如影隨形伴隨數據分析者的一切場景,是后續SQL和Python數據類型的基礎。

通常我們將Excel格式分為數值、文本、時間。

數值常見整數型 Int和小數/浮點型 Float。兩者的界限很模糊。在SQL和Python中,則會牽扯的復雜,涉及運算效率,計算精度等。

文本分為中文和英文,存儲字節,字符長度不同。中文很容易遇到編碼問題,尤其是Python2。Win和Mac環境也有差異。大家遇到的亂碼一般都屬于中文編碼錯誤。

時間格式在Excel中可以和數值直接互換,也能用加減法進行天數換算。

時間格式有不同表達。例如2016年11月11日,2016/11/11,2016-11-11等。當數據源多就會變得混亂。我們可以用自定義格式規范時間。

這里了解一下時間格式的概念,列舉是一些較通用的范例(不同編程語言還是有差異的):

YYYY代表通配的四位數年格式

MM代表通配的兩位數月格式

DD代表通配的兩位數日格式

HH代表通配的的兩位數小時(24小時)格式

hh代表通配的兩位數?。?2小時制)格式

mm代表通配的兩位數分格式

ss代表通配的兩位數秒格式

例如2016/11/11可以寫成:yyyy/MM/dd。

2016-11-11 23:59:59可以寫成:yyyy-MM-dd HH:mm:ss。

Clipboard Image.png

數組

數組很多人都不會用到,甚至不知道有這個功能。依舊是數據分析越往后用到越多,它類似R語言的Array和Python的List。

數組由多個元素組成。普通函數的計算結果是一個值,數組類函數的計算結果返回多個值。

數組用大括號表示,當函數中使用到數組,應該用Ctrl+Shift+Enter輸入,不然會報錯。

先看數組的最基礎使用。選擇A1:D1區域,輸入={1,2,3,4}。記住是大括號。然后Ctrl+Shift+Enter。我們發現數組里的四個值被分別傳到四個單元格中,這是數組的獨有用法。

Clipboard Image.png

我們再來看一下數組和函數的應用。利用{},我們能做到1匹配a,2匹配b,3匹配c。也就是一一對應。專業說法是Mapping。

=lookup(查找值,{1,2,3},{“a”,”b”,”c”})

Excel的數組有同樣強大的玩法,大家可以搜索學習,提高一定的效率。但是Python的數組更為強大,重點就不放在這塊了。

分列

Excel可以將多個單元格的內容合并,但是不擅長拆分。分列功能可以將某一列按照特定規則拆分。常常用來進行數據清洗。

Clipboard Image.png

上文我有一列地區的數據,我想要將市和區分成兩列。通常做法是可以用mid和find函數查找“市”截取字符。但最快做法就是用“市”分列。

Clipboard Image.png

出一個思考題,如果市和區都存在應該如何分列?

SQL和Python中有類似的spilt ( )函數。

合并單元個格

單元格作為報表整理使用,除非是最終輸出格式,例如打印。否則不要隨意合并單元格。

一旦使用合并單元格,絕大多數函數都不能正常使用,影響批量的數據處理和格式轉換。合并單元格也會造成Python和SQL的讀取錯誤。

數據透視表

數據透視表是非常強大的功能,當初學會時驚為天人。

數據透視表的主要功能是將數據聚合,按照各子段進行sum( ),count( )的運算。

下圖我選擇我選擇想要計算的數據,然后點擊創建透視表。

Clipboard Image.png

此時會新建一個Sheet,這是數據透視表的優點,將原始數據和匯總計算數據分離。

數據透視表的核心思想是聚合運算,將字段名相同的數據聚合起來,所謂數以類分。

列和行的設置,則是按不同軸向展現數據。簡單說,你想要什么結構的報表,就用什么樣的拖拽方式。

Clipboard Image.png

聚合功能有一點類似SQL中的gorup by,python中則有更為強大的pandas.pivot_table( )。

刪除重復項

一種數據清洗和檢驗的快速方式。想要驗證某一列有多少個唯一值,或者數據清洗,都可以使用。

Clipboard Image.png

功能類似SQL中的distinct ,python中的set。

條件格式

條件格式可以當作數據可視化的應用。如果我們要使用函數在大量數據中找出前三的值,可能會用到rank( )函數,排序,然后過濾出1,2,3。

用條件格式則是另外一種快速方法,直接用顏色標出,非常直觀。

Clipboard Image.png

凍結首行首列

Excel的首行一般是各字段名Header,俗稱表頭,當行數和列數過多的時候,觀察數據比較麻煩。我們可以通過固定住首行,方便瀏覽和操作。

Header是一個較為重要的概念。在Python和R中,read_csv函數,會有一個專門的參數header=true,來判斷是否讀取表頭作為columns的名字。

自定義下拉菜單(數據有效性)

數據有效性是一種約束,針對單元格限制其輸入,也就是讓其只能固定幾個值。下拉菜單是一種高階應用,通過允許下拉箭頭即可。

Clipboard Image.png

自定義名稱

自定義名稱是一個很好用的技巧,我們可以為一個區域,變量、或者數組定義一個名稱。后續要經常使用的話,直接引用即可,無需再次定位。這是復用的概念。

Clipboard Image.png

我們將A1:A3區域命名為NUM。

直接使用=sum(NUM) ,等價于sum(A1:A3)。

Clipboard Image.png

新手們理解數據庫,可以將其想象成無數張表sheet。每一張表都有自己唯一的名字,就像上圖的NUM一樣。數據庫操作就是引用表名進行查找、關聯等操作。使用sum,count等函數。

查找公式錯誤

公式報錯也不知道錯在哪里時候可以使用該功能,尤其是各類IF嵌套或者多表關聯,邏輯復雜時。查找公式錯誤是逐步運算的,很方便定位。

Clipboard Image.png

分組和分級顯示

分組和分級顯示,常用在報表中,在報表行數多到一定程度時,通過分組達到快速切換和隱藏的目的。越是專業度的報表(咨詢、財務等),越可以學習這塊。在數據菜單下。

Clipboard Image.png

分析工具庫

分析工具庫是高階分析的利器,包含很多統計計算,檢驗功能等工具。Excel是默認不安裝的,要安裝需要加載項,在工具菜單下(不同版本安裝方式會有一點小差異)。

Clipboard Image.png

分析工具庫是統計包,規劃求解是計算最優解,類似決策樹。這兩者的分析方法以后詳細論述。

Clipboard Image.png

Mac似乎有閹割。

第三方應用

Excel是支持第三方插件的,第三方插件擁有非常強大的功能。甚至完成BI的工作。

Clipboard Image.png

應用商店里微軟官方的Power系列都挺好。下圖就是Power Map。

Clipboard Image.png

第三方應用商店Mac沒有,非??上?。Win用戶請用最新版本,2010以前是沒有插件的。第三方應用是可以深學的,如果是傳統行業的數據分析師,需要專注學習,互聯網分析就不需要了。

主要的Excel技巧和函數已經都教授給大家。Excel博大精深,有一句說的挺好,我們大部分實際用到的功能只有20%。熟練掌握這20%功能,日常工作足夠應付。重要的還是解決問題的能力。

接下來是Excel實戰內容,下一篇文章會直接用到5000行真實的數據分析師的職位數據。沒錯,用數據分析師的數據進行分析,有點拗口。

相關閱讀

互聯網數據分析能力的養成,需一份七周的提綱

如何七周成為數據分析師01:常見的Excel函數全部涵蓋在這里了

#專欄作家#

秦路,微信公眾號ID:tracykanc,人人都是產品經理專欄作家。

本文由 @秦路?原創發布于人人都是產品經理。未經許可,禁止轉載。

題圖來自PEXELS,基于CCO協議

更多精彩內容,請關注人人都是產品經理微信公眾號或下載App
評論
評論請登錄
  1. 如果要市存在怎么辦呢,把市替換為市市?

    來自內蒙古 回復
  2. 看不見圖片,怎么辦

    來自廣東 回復
  3. 我也看不見圖片

    來自北京 回復
  4. 為啥圖片 都看不了

    來自北京 回復
    1. 我也看不到圖片,有什么辦法嗎?

      來自上海 回復
  5. 往下填充數據和函數方式應該是:
    WPS:以當前單元格為始選中要填充的單元格,再按Ctrl+D
    office2016:以當前單元格為始選中要填充的單元格,再按Ctrl+D;或者直接Ctrl+E

    來自云南 回復
  6. 好多個ctrl都寫錯了。。。作者大大這個鍵是用到磨光了吧。

    來自四川 回復
  7. 贊作者

    來自湖北 回復
  8. 最近在做BI分析,受教了

    來自福建 回復
  9. 超贊!

    來自福建 回復