上一篇文章《數(shù)據(jù)分析：常見(jiàn)的Excel函數(shù)全部涵蓋在這里了》教了大家常用的函數(shù)，今天講解Excel的技巧。

本次講解依然是提綱，圖文部分引用自百度經(jīng)驗(yàn)。如果有疑問(wèn)或建議，可以留言給我，也可以網(wǎng)上搜索。內(nèi)容方面照舊會(huì)補(bǔ)充SQL和Python。

快捷鍵

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

Crtl+方向鍵，對(duì)單元格光標(biāo)快速移動(dòng)，移動(dòng)到數(shù)據(jù)邊緣（空格位置）。

Crtl+Shift+方向鍵，對(duì)單元格快讀框選，選擇到數(shù)據(jù)邊緣（空格位置）。

Ctrrl+空格鍵，選定整列。

Shift+空格鍵，選定整行。

Ctrl+A，選擇整張表內(nèi)容。

Alt+Enter，換行。

Ctrl+Enter，以當(dāng)前單元格為始，往下填充數(shù)據(jù)和函數(shù)。

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

Ctrl+Z，撤回當(dāng)前操作。

如果是效率達(dá)人，可以學(xué)習(xí)更多快捷鍵。Mac用戶的ctrl一般需要用command替換。

格式轉(zhuǎn)換

Excel的格式及轉(zhuǎn)換很容易忽略，但格式會(huì)如影隨形伴隨數(shù)據(jù)分析者的一切場(chǎng)景，是后續(xù)SQL和Python數(shù)據(jù)類型的基礎(chǔ)。

通常我們將Excel格式分為數(shù)值、文本、時(shí)間。

數(shù)值常見(jiàn)整數(shù)型 Int和小數(shù)/浮點(diǎn)型 Float。兩者的界限很模糊。在SQL和Python中，則會(huì)牽扯的復(fù)雜，涉及運(yùn)算效率，計(jì)算精度等。

文本分為中文和英文，存儲(chǔ)字節(jié)，字符長(zhǎng)度不同。中文很容易遇到編碼問(wèn)題，尤其是Python2。Win和Mac環(huán)境也有差異。大家遇到的亂碼一般都屬于中文編碼錯(cuò)誤。

時(shí)間格式在Excel中可以和數(shù)值直接互換，也能用加減法進(jìn)行天數(shù)換算。

時(shí)間格式有不同表達(dá)。例如2016年11月11日，2016/11/11，2016-11-11等。當(dāng)數(shù)據(jù)源多就會(huì)變得混亂。我們可以用自定義格式規(guī)范時(shí)間。

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

YYYY代表通配的四位數(shù)年格式

MM代表通配的兩位數(shù)月格式

DD代表通配的兩位數(shù)日格式

HH代表通配的的兩位數(shù)小時(shí)（24小時(shí)）格式

hh代表通配的兩位數(shù)?。?2小時(shí)制）格式

mm代表通配的兩位數(shù)分格式

ss代表通配的兩位數(shù)秒格式

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

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

數(shù)組

數(shù)組很多人都不會(huì)用到，甚至不知道有這個(gè)功能。依舊是數(shù)據(jù)分析越往后用到越多，它類似R語(yǔ)言的Array和Python的List。

數(shù)組由多個(gè)元素組成。普通函數(shù)的計(jì)算結(jié)果是一個(gè)值，數(shù)組類函數(shù)的計(jì)算結(jié)果返回多個(gè)值。

數(shù)組用大括號(hào)表示，當(dāng)函數(shù)中使用到數(shù)組，應(yīng)該用Ctrl+Shift+Enter輸入，不然會(huì)報(bào)錯(cuò)。

先看數(shù)組的最基礎(chǔ)使用。選擇A1:D1區(qū)域，輸入={1,2,3,4}。記住是大括號(hào)。然后Ctrl+Shift+Enter。我們發(fā)現(xiàn)數(shù)組里的四個(gè)值被分別傳到四個(gè)單元格中，這是數(shù)組的獨(dú)有用法。

我們?cè)賮?lái)看一下數(shù)組和函數(shù)的應(yīng)用。利用{}，我們能做到1匹配a，2匹配b，3匹配c。也就是一一對(duì)應(yīng)。專業(yè)說(shuō)法是Mapping。

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

Excel的數(shù)組有同樣強(qiáng)大的玩法，大家可以搜索學(xué)習(xí)，提高一定的效率。但是Python的數(shù)組更為強(qiáng)大，重點(diǎn)就不放在這塊了。

分列

Excel可以將多個(gè)單元格的內(nèi)容合并，但是不擅長(zhǎng)拆分。分列功能可以將某一列按照特定規(guī)則拆分。常常用來(lái)進(jìn)行數(shù)據(jù)清洗。

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

出一個(gè)思考題，如果市和區(qū)都存在應(yīng)該如何分列？

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

合并單元個(gè)格

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

一旦使用合并單元格，絕大多數(shù)函數(shù)都不能正常使用，影響批量的數(shù)據(jù)處理和格式轉(zhuǎn)換。合并單元格也會(huì)造成Python和SQL的讀取錯(cuò)誤。

數(shù)據(jù)透視表

數(shù)據(jù)透視表是非常強(qiáng)大的功能，當(dāng)初學(xué)會(huì)時(shí)驚為天人。

數(shù)據(jù)透視表的主要功能是將數(shù)據(jù)聚合，按照各子段進(jìn)行sum( )，count( )的運(yùn)算。

下圖我選擇我選擇想要計(jì)算的數(shù)據(jù)，然后點(diǎn)擊創(chuàng)建透視表。

此時(shí)會(huì)新建一個(gè)Sheet，這是數(shù)據(jù)透視表的優(yōu)點(diǎn)，將原始數(shù)據(jù)和匯總計(jì)算數(shù)據(jù)分離。

數(shù)據(jù)透視表的核心思想是聚合運(yùn)算，將字段名相同的數(shù)據(jù)聚合起來(lái)，所謂數(shù)以類分。

列和行的設(shè)置，則是按不同軸向展現(xiàn)數(shù)據(jù)。簡(jiǎn)單說(shuō)，你想要什么結(jié)構(gòu)的報(bào)表，就用什么樣的拖拽方式。

聚合功能有一點(diǎn)類似SQL中的gorup by，python中則有更為強(qiáng)大的pandas.pivot_table( )。

刪除重復(fù)項(xiàng)

一種數(shù)據(jù)清洗和檢驗(yàn)的快速方式。想要驗(yàn)證某一列有多少個(gè)唯一值，或者數(shù)據(jù)清洗，都可以使用。

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

條件格式

條件格式可以當(dāng)作數(shù)據(jù)可視化的應(yīng)用。如果我們要使用函數(shù)在大量數(shù)據(jù)中找出前三的值，可能會(huì)用到rank( )函數(shù)，排序，然后過(guò)濾出1，2，3。

用條件格式則是另外一種快速方法，直接用顏色標(biāo)出，非常直觀。

凍結(jié)首行首列

Excel的首行一般是各字段名Header，俗稱表頭，當(dāng)行數(shù)和列數(shù)過(guò)多的時(shí)候，觀察數(shù)據(jù)比較麻煩。我們可以通過(guò)固定住首行，方便瀏覽和操作。

Header是一個(gè)較為重要的概念。在Python和R中，read_csv函數(shù)，會(huì)有一個(gè)專門的參數(shù)header=true，來(lái)判斷是否讀取表頭作為columns的名字。

自定義下拉菜單（數(shù)據(jù)有效性）

數(shù)據(jù)有效性是一種約束，針對(duì)單元格限制其輸入，也就是讓其只能固定幾個(gè)值。下拉菜單是一種高階應(yīng)用，通過(guò)允許下拉箭頭即可。

自定義名稱

自定義名稱是一個(gè)很好用的技巧，我們可以為一個(gè)區(qū)域，變量、或者數(shù)組定義一個(gè)名稱。后續(xù)要經(jīng)常使用的話，直接引用即可，無(wú)需再次定位。這是復(fù)用的概念。

我們將A1:A3區(qū)域命名為NUM。

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

新手們理解數(shù)據(jù)庫(kù)，可以將其想象成無(wú)數(shù)張表sheet。每一張表都有自己唯一的名字，就像上圖的NUM一樣。數(shù)據(jù)庫(kù)操作就是引用表名進(jìn)行查找、關(guān)聯(lián)等操作。使用sum，count等函數(shù)。

查找公式錯(cuò)誤

公式報(bào)錯(cuò)也不知道錯(cuò)在哪里時(shí)候可以使用該功能，尤其是各類IF嵌套或者多表關(guān)聯(lián)，邏輯復(fù)雜時(shí)。查找公式錯(cuò)誤是逐步運(yùn)算的，很方便定位。

分組和分級(jí)顯示

分組和分級(jí)顯示，常用在報(bào)表中，在報(bào)表行數(shù)多到一定程度時(shí)，通過(guò)分組達(dá)到快速切換和隱藏的目的。越是專業(yè)度的報(bào)表（咨詢、財(cái)務(wù)等），越可以學(xué)習(xí)這塊。在數(shù)據(jù)菜單下。

分析工具庫(kù)

分析工具庫(kù)是高階分析的利器，包含很多統(tǒng)計(jì)計(jì)算，檢驗(yàn)功能等工具。Excel是默認(rèn)不安裝的，要安裝需要加載項(xiàng)，在工具菜單下（不同版本安裝方式會(huì)有一點(diǎn)小差異）。

分析工具庫(kù)是統(tǒng)計(jì)包，規(guī)劃求解是計(jì)算最優(yōu)解，類似決策樹。這兩者的分析方法以后詳細(xì)論述。

Mac似乎有閹割。

第三方應(yīng)用

Excel是支持第三方插件的，第三方插件擁有非常強(qiáng)大的功能。甚至完成BI的工作。

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

第三方應(yīng)用商店Mac沒(méi)有，非?？上?。Win用戶請(qǐng)用最新版本，2010以前是沒(méi)有插件的。第三方應(yīng)用是可以深學(xué)的，如果是傳統(tǒng)行業(yè)的數(shù)據(jù)分析師，需要專注學(xué)習(xí)，互聯(lián)網(wǎng)分析就不需要了。

主要的Excel技巧和函數(shù)已經(jīng)都教授給大家。Excel博大精深，有一句說(shuō)的挺好，我們大部分實(shí)際用到的功能只有20%。熟練掌握這20%功能，日常工作足夠應(yīng)付。重要的還是解決問(wèn)題的能力。

接下來(lái)是Excel實(shí)戰(zhàn)內(nèi)容，下一篇文章會(huì)直接用到5000行真實(shí)的數(shù)據(jù)分析師的職位數(shù)據(jù)。沒(méi)錯(cuò)，用數(shù)據(jù)分析師的數(shù)據(jù)進(jìn)行分析，有點(diǎn)拗口。