文丨罌粟姐姐

今天，罌粟姐姐使出洪荒之力，為大家總結(jié)了Excel的10個核心技巧，基本可以輕松應(yīng)對日常工作的80%的需求。

規(guī)范性的數(shù)據(jù)輸入可以減少后期數(shù)據(jù)整理的工作量，也可以極大地提高數(shù)據(jù)統(tǒng)計工作的效率。

1.自定義單元格格式

利用Excel單元格的自定義格式我們可以實現(xiàn)一些看起來非常神奇和有用的效果。

操作步驟：點擊單元格-單擊右鍵-設(shè)置單元格格式-自定義-輸入根據(jù)數(shù)據(jù)規(guī)律填入的數(shù)據(jù)類型（或者使用快捷鍵Ctrl 1調(diào)出設(shè)置單元格格式）

主要規(guī)則：“#”，數(shù)字占位符，一個#代表一個數(shù)字；“@”，文本占位符；“，”，千位分隔符；“.”，小數(shù)點；“%”，百分比。

案例1：將數(shù)字以千分位格式輸入（###,###）

案例2：輸入員工的工號('ABC'###)

案例3：地名或者公司名稱全稱的快速輸入（'中國'@）

案例4：做多條件判斷，90分以上為優(yōu)秀，60分以上及格，60分以下不及格（[>=90]優(yōu)秀；[>=60]及格；不及格）

2.數(shù)據(jù)有效性

數(shù)據(jù)有效性是對單元格或單元格區(qū)域輸入的數(shù)據(jù)從內(nèi)容到數(shù)量上的限制，這樣就可以依靠系統(tǒng)檢查數(shù)據(jù)是否正確有效。

案例1：設(shè)置一級下拉列表

操作步驟：“數(shù)據(jù)-數(shù)據(jù)驗證-設(shè)置-允許-序列-選擇數(shù)據(jù)來源（也可手動輸入數(shù)據(jù)，用逗號分隔）

案例2：設(shè)置二級下拉列表

操作步驟：設(shè)置一級下拉列表-定義名稱-設(shè)置二級下拉列表（=INDIRECT($B2)）

注意：因為B2單元格最開始為空，所以提示“源當(dāng)前包含錯誤”，直接點擊確定即可。

將數(shù)據(jù)整理為規(guī)范的數(shù)據(jù)不僅可以使圖表更加整齊美觀，符合公司或者個人常用習(xí)慣，也能夠極大地方便后期的數(shù)據(jù)統(tǒng)計分析。

3.分列

案例1：刪除不需要的內(nèi)容

操作步驟：數(shù)據(jù)-分列-固定寬度（分隔符號）-選擇“不導(dǎo)入此列”

案例2：按關(guān)鍵字拆分

操作步驟：數(shù)據(jù)-分列-分隔符號-其它-輸入拆分的關(guān)鍵字（使用方法等同于其它分隔符號）

案例3：文本或數(shù)字轉(zhuǎn)換為日期

操作步驟：數(shù)據(jù)-分列-分隔符號-列數(shù)據(jù)格式-日期

4.自定義排序

當(dāng)排序規(guī)則首先定好，并且升序和降序都無法實現(xiàn)時，需要使用自定義排序工具。

操作步驟：文件-選項-高級-常規(guī)-編輯自定義列表-自定義序列-從單元格中導(dǎo)入序列-導(dǎo)入-確定，選擇排序數(shù)據(jù)-開始-排序與篩選-自定義排序-次序-自定義序列。

數(shù)據(jù)計算篇

Excel的核心功能就是數(shù)據(jù)計算，目前Excel所達到的計算能力已經(jīng)能夠滿足覺得大多數(shù)用戶的需求，目前計算方法有三種，一是工具欄上的自帶計算工具（中級），二是函數(shù)計算（高級），三是VBA（大神級）。

5.合并計算

案例1：單數(shù)據(jù)源的合并計算

操作步驟：點擊“一級區(qū)域”-數(shù)據(jù)-合并計算-輸入數(shù)據(jù)源-添加-選擇“首行”-選擇“最左列”-確定。

案例2：多數(shù)據(jù)源相同字段合并計算

操作步驟：點擊“一級區(qū)域”-數(shù)據(jù)-合并計算-分別輸入多個數(shù)據(jù)源-添加-選

擇“首行”-選擇“最左列”-確定。

案例3：多數(shù)據(jù)源不同字段合并計算

操作步驟：點擊“一級區(qū)域”-數(shù)據(jù)-合并計算-分別輸入多個數(shù)據(jù)源-添加-選擇“首行”-選擇“最左列”-確定。

案例4：使用通配符的合并計算

操作步驟：利用通配符構(gòu)建原始表格區(qū)域-單擊表格區(qū)域-數(shù)據(jù)-合并計算-輸入數(shù)據(jù)源-添加-選擇“首行”-選擇“最左列”-確定。

6.VLOOKUP函數(shù)

題外話：罌粟姐姐最近被問的最多的問題就是“我應(yīng)該哪個V哪個？”

VLOOKUP函數(shù)是Excel工具里公認的大神級函數(shù)，各位小伙伴一定要掌握！今天給大家分享3個進階應(yīng)用。

英文語法：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

中文語法：VLOOKUP（找誰，在什么范圍找，在范圍里的第幾列，精確查找或模糊查找)

案例1：多列查找時公式一次性輸入

第三個參數(shù)在范圍里的第幾列有兩種最常用的表達方法：

①1=COLUMN(A1)；2=COLUMN(B1)……

②1=MATCH(I6,$C$1:$F$1,0)；

2=MATCH(J6,$C$1:$F$1,0)……

最終公式有兩種：

①=VLOOKUP($H7,$B$1:$F$18,COLUMN(B2),0)

②=VLOOKUP($H7,$B$1:$F$18,MATCH(I$6,$B$1:$F$1,0),0)

案例2：多條件查找

公式=

VLOOKUP(E3&F3,IF({1,0},A1:A13&B1:B13,C1:C13),2,0)

E3&F3：將多條件合并

IF({1,0},A1:A13&B1:B13,C1:C13)：利用if函數(shù)第一個參數(shù)的數(shù)組化特征，將多條件合并后的內(nèi)容與查詢的列結(jié)合起來，組成新的兩列數(shù)據(jù)。

輸入公式結(jié)束后同時按Ctrl Shift Enter三鍵出來結(jié)果。

案例3：模糊查找

注意：引用的數(shù)字區(qū)域必須由小到大排列，輸出結(jié)果是和查找值最接近但比它小的那個值。

公式=VLOOKUP(D4,$A$1:$B$10,2,1)

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

這里再給大家強調(diào)一下數(shù)據(jù)透視表的基礎(chǔ)應(yīng)用。

操作步驟：插入-數(shù)據(jù)透視表-選擇數(shù)據(jù)源-創(chuàng)建透視表

8.迷你圖

迷你圖是放入單個單元格中的小型圖，每個迷你圖代表所選內(nèi)容中的一行數(shù)據(jù)。

操作步驟：插入-迷你圖-選擇迷你圖類型。

9.利用輔助數(shù)據(jù)作圖

作為一個工作多年的職場老司機，最頭疼的就是年終報告，而年終報告里最讓人頭疼的就是數(shù)據(jù)圖表。

曾經(jīng)，我的數(shù)據(jù)圖表里柱狀圖是這樣的：

現(xiàn)在是這樣的：

區(qū)別主次坐標軸的柱狀圖

傳說中的兩極圖

根據(jù)數(shù)據(jù)大小變化填充顏色的柱形圖

不同季度不同顏色的柱狀圖

10.動態(tài)可視化圖表

動態(tài)可視化圖表可以讓數(shù)據(jù)更生動，更能體現(xiàn)數(shù)據(jù)的變化規(guī)律。

曾經(jīng)，我的折線圖是這樣的：

現(xiàn)在我的折線圖是這樣的：