可以發(fā)現(xiàn)，老外做的Excel表格，非常的漂亮。

再看看我們最常見的表格，難看的瞬間爆表

根據(jù)小編的了解，無論是在企業(yè)做財(cái)務(wù)，還是在四大做審計(jì)，有一項(xiàng)共通的技能就是：Excel要6到飛起！正所謂Excel玩得好，下班會(huì)很早。

那么，問題是，Excel的技能那么多，作為財(cái)務(wù)，怎樣才能算完全精通呢？達(dá)到什么樣的水平才能讓Excel表格又好看，又實(shí)用呢？

正是有這樣的疑問，所以才有今天這樣一篇文章。小編特意選在周末，給大家分享這篇干貨滿滿的文章，我搜集了財(cái)務(wù)最常用的Excel技巧，讓大家的Excel技能提升一個(gè)level！

Excel技能千千萬(wàn)，其實(shí)，說到底，做到精通的話，只要學(xué)會(huì)4+1，4個(gè)核心函數(shù)為基礎(chǔ)篇：SUM、IF、VLOOKUP、SUMIF，1個(gè)數(shù)據(jù)透視表為進(jìn)階篇。搞清楚這五個(gè)，在工作中需要Excel解決的，基本上問題都不大。

SUM函數(shù)（數(shù)學(xué)）

重要程度：★★★★★

學(xué)習(xí)難度：★★

在日常的工作中與學(xué)習(xí)中，SUM是一款應(yīng)用極為廣泛并且做為入門級(jí)的函數(shù)來學(xué)習(xí)的。所以，整體來說，SUM函數(shù)難度不高，應(yīng)用性卻很廣泛。但是不能因?yàn)楹?jiǎn)單就小瞧了SUM，它還有一些不為人知的小技巧！

如下圖，是某企業(yè)的每一天的銷售業(yè)績(jī)，要求計(jì)算每天的累計(jì)金額。

在D2單元格中輸入公式：=SUM(C$2:C2)，按Enter鍵完成后向下填充。

注意：這時(shí)的C$2一定是要鎖定行標(biāo)的，這樣在下拉的過程中才會(huì)產(chǎn)生從第二行一直到向下的行的一個(gè)引用區(qū)域。

合并單元格的求和，一直是一個(gè)比較讓新手頭疼的問題。

選中D2:D13單元格區(qū)域，然后在公式編輯欄里輸入公式：=SUM(C2:C13)-SUM(D3:D14)，然后按<Ctrl+Enter>完成，如下圖所示：

注：一定要注意第二個(gè)SUM函數(shù)的區(qū)域范圍要錯(cuò)位，不然就報(bào)錯(cuò)。

帶有小計(jì)的單元格到底怎么樣求和？在C9單元格里是輸入公式：=SUM(C2:C8)/2，按Enter鍵完成。如下圖所示：

注意：這里是自用了小計(jì)與求和的過程是重復(fù)計(jì)算了上面的數(shù)據(jù)，所以再除以2就可以得到不重復(fù)的結(jié)果，也正是想要的結(jié)果。

在一些比較不規(guī)范的表中呢，會(huì)有文字與數(shù)字的混合的問題，給求和帶來了一定了的難度。

在C12單元格中輸入公式：=SUM(--SUBSTITUTE(C2:C11,'元',''))

按組合鍵<>Ctrl+Shift+Enter>完成。

注意：這里的公式兩邊的花括號(hào)不是的手動(dòng)輸入的，而是在按組合鍵后系統(tǒng)自動(dòng)添加上去的。

下表中是4個(gè)月的業(yè)績(jī)統(tǒng)計(jì)，每個(gè)工作表的里面的張成的位置都是一樣的，求張成的1-4月的提成統(tǒng)計(jì)。

在F5單元格中輸入公式：=SUM('1月:4月'!C2),按Enter鍵完成填充。如下圖所示：

注意：在Excel中是支持上面的這種引用的，但是在WPS里面貌似不支持。并且要注意每個(gè)表中的結(jié)構(gòu)或者數(shù)字的固定的位置是一樣的，才可以使用這個(gè)公式。

六、多區(qū)域聯(lián)合計(jì)算

在有些工作表中，我們并不需要對(duì)連續(xù)的區(qū)域進(jìn)行求和，可能是對(duì)不連續(xù)的某幾個(gè)區(qū)域進(jìn)行求和。

如下圖所示，計(jì)算1-2月，3月以及5-6月的合計(jì)。

在H2單元格中輸入公式，按Enter鍵完成后向下填充。

=SUM(B2:C2,E2,G2)

注意：這里使用逗號(hào)將各個(gè)不連續(xù)的區(qū)域連接起來的引用方式叫聯(lián)合引用。

除了SUMIF，SUMIFS之外還可以使用SUM函數(shù)來進(jìn)行條件求和。

在G4單元格中輸入公式：=SUM((B2:B11='日用')*(E2:E11))

按組合鍵<Ctrl+Shift+Enter>鍵完成后向下填充。如下圖所示：

除了上面的求和之外，SUM函數(shù)還可以代替COUNTIFS，COUNIF函數(shù)進(jìn)行計(jì)數(shù)。

在G4單元格中輸入公式：=SUM(1*(B2:B11='日用'))

按組合鍵<Ctrl+Shift+Enter>鍵完成后向下填充。如下圖所示：

IF函數(shù)（邏輯）

重要程度：★★★★

學(xué)習(xí)難度：★★★

IF函數(shù)是Excel邏輯家族的扛把子，只要是邏輯判斷就可以說離不開IF函數(shù)。IF函數(shù)很簡(jiǎn)單，幾乎接觸過Excel的人看到都能理解。下面我分享一下財(cái)務(wù)人員最常用到的IF函數(shù)。

比如：如果已付清，則為“關(guān)賬”，如果未付清且金額大于3000元，則為“立即摧賬”，如果未付清且小于3000元，則為“狀態(tài)正常”。

在H4單元格中輸入公式：=IF(E2='否',IF(D2>=3000,'立即催賬','狀態(tài)正常'),'關(guān)賬')，按Enter鍵完成后向下填充。

注意：通常情況下，IF函數(shù)經(jīng)常與其他的邏輯函數(shù)一起使用，比如AND，OR，NOT等函數(shù)。該函數(shù)在使用的時(shí)候一定要注意前面的關(guān)聯(lián)的邏輯。

最后，IF函數(shù)配合VLOOKUP函數(shù)可以實(shí)現(xiàn)反向查找，雙條件查找。

VLOOKUP函數(shù)（篩選）

重要程度：★★★★★

學(xué)習(xí)難度：★★★★

VLOOKUP被稱為Excel中的效率之王，但是95%的Excel使用者都不能很好使用VLOOKUP。但是，VLOOKUP函數(shù)又是Excel中的大眾情人。有平臺(tái)曾經(jīng)做過“如果只能選擇學(xué)習(xí)Excel中的一項(xiàng)功能，你會(huì)選擇哪個(gè)”的調(diào)查，VLOOKUP函數(shù)竟然高居第二位。

在我們的工作中，基本每天都會(huì)遇到這樣的場(chǎng)景。比如從總表中，根據(jù)姓名匹配身份證號(hào)信息，根據(jù)考核等級(jí)確定獎(jiǎng)金比例。

這些工作本質(zhì)上都是匹配調(diào)用：匹配同樣的數(shù)據(jù)，調(diào)用出我們需要的數(shù)據(jù)。要解決這個(gè)問題，最常用到的就是VLOOKUP函數(shù)。

那么VLOOKUP函數(shù)究竟如何使用呢？

VLOOKUP函數(shù)語(yǔ)法結(jié)構(gòu)：VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP（查找值，查找范圍，返回的數(shù)值所在的列數(shù)，精確匹配還是模糊匹配）。

一、VLOOKUP函數(shù)精確匹配，返回你需要的唯一數(shù)據(jù)

比如根據(jù)姓名匹配身份證號(hào)信息，對(duì)于這種匹配調(diào)用唯一的數(shù)據(jù)，就要用到VLOOKUP函數(shù)的精確匹配了。

操作步驟：查找范圍為絕對(duì)引用，可按快捷鍵F4，精確匹配下參數(shù)為0或FALSE。

注意事項(xiàng)：查找范圍和要返回的數(shù)值所在的列數(shù)都是要從查找值所在的列開始計(jì)算。

VLOOKUP查詢調(diào)用精確匹配

二、VLOOKUP函數(shù)模糊匹配，返回你需要的區(qū)間數(shù)據(jù)

比如根據(jù)考核等級(jí)確定獎(jiǎng)金比例，對(duì)于這種在區(qū)間范圍內(nèi)匹配調(diào)用數(shù)據(jù)，就要用到VLOOKUP函數(shù)的模糊匹配了，這個(gè)功能完全可以替代掉IF函數(shù)的多層嵌套，再也不用為寫錯(cuò)順序發(fā)愁。

操作步驟：查找范圍依然為絕對(duì)引用，可按快捷鍵F4，模糊匹配下參數(shù)為1或TRUE。

注意事項(xiàng)：等級(jí)表的編制要從小到大

VLOOKUP查詢調(diào)用模糊匹配

說清楚大方向之后，我們來分享一下VLOOKUP的幾個(gè)常規(guī)操作方法：

1、常規(guī)查找

查找姓名對(duì)應(yīng)的銷售額。在F3單元格中輸入公式=VLOOKUP(E3,$A$2:$C$9,3,0)，按Enter鍵完成。如下圖所示：

2、日期查找

在查找日期的時(shí)候查找的結(jié)果通常會(huì)是一串?dāng)?shù)字，為了使日期能夠返回相應(yīng)的格式，那么需要配合TEXT函數(shù)才能完成查找需求。

在F3單元格中輸入公式=TEXT(VLOOKUP(E3,$A$2:$C$9,2,0),'yyyy/m/d')，按Enter鍵完成。如下圖所示：

注：如返回格式為2018/12/03，則TEXT的第二個(gè)參數(shù)的格式可以設(shè)置為“yyyy/mm/dd”即可。

3、查找的值為空時(shí)

在當(dāng)查找的值為空時(shí)，通常情況下會(huì)返回結(jié)果為0，那么如果讓結(jié)果返回空白呢，解決的方法就是在公式后面一個(gè)“”。

在F3單元格中輸入公式=VLOOKUP(E3,$A$2:$C$9,3,0)&''，按Enter鍵完成。如下圖所示：

4、當(dāng)查找的目標(biāo)格式不統(tǒng)一時(shí)報(bào)錯(cuò)如何解決

（1）如果查找的目標(biāo)值是文本格式，而數(shù)據(jù)區(qū)域中是數(shù)值格式。

如下圖所示，A列中的員工編號(hào)為數(shù)值格式，而F3單元格中的員工編號(hào)為文本格式。

在G3單元格中輸入公式：=VLOOKUP(--F3,$A$2:$D$9,4,0)，按Enter鍵完成。

注：--為兩個(gè)負(fù)號(hào)，即減負(fù)的意思，可以理解為負(fù)負(fù)得正，這里是把文本強(qiáng)制轉(zhuǎn)換為數(shù)值，所以問題就很容易被解決了。

（2）如果查找的目標(biāo)值是數(shù)值格式，而數(shù)據(jù)區(qū)域中是文本格式。

如下圖所示，A列中的員工編號(hào)為文本格式，而F3單元格中的員工編號(hào)為數(shù)值格式。

在G3單元格中輸入公式：=VLOOKUP(F3&'',$A$2:$D$9,4,0)，按Enter鍵完成。

注：&''是強(qiáng)制地把數(shù)值格式轉(zhuǎn)換成文本格式。

5、區(qū)域查找

有時(shí)候需要查找某一個(gè)值處于那個(gè)區(qū)間里。比如查找下列的銷售額對(duì)應(yīng)的銷售提點(diǎn)為多少。在E2單元格中輸入公式：=VLOOKUP(D2,$H$2:$I$8,2,1)，按Enter鍵完成。

注：這里使用該函數(shù)最后一個(gè)參數(shù)為1，即模糊查找，來確定查找的值處于給定的那一個(gè)區(qū)間。

6、模糊查找

VLOOKUP函數(shù)也是支持模糊查找，即支持通配符查找。

查找姓名中帶有“冰”字的員工的銷售額，在H3單元格中輸入公式：

=VLOOKUP('*'&G3&'*',$B$2:$D$9,3,0)，按Enter鍵完成。

注：如果要查找以“冰”開頭的那么公式的第一參數(shù)為：'*'&G3; 如果查找以“冰”結(jié)尾那么公式的第一個(gè)參數(shù)為：G3&'*'.

7、查找順序與數(shù)據(jù)區(qū)域中順序一致的多項(xiàng)時(shí)

VLOOKUP函數(shù)查找順序一致的多項(xiàng)時(shí)，可以借助COLUMN函數(shù)構(gòu)建查找序列。

在H2單元格中輸入公式：=VLOOKUP($G2,$A$2:$D$9,COLUMN(B1),0)，按Enter鍵后向右填充。

注：COLUMN函數(shù)是返回列號(hào)。第一個(gè)參數(shù)一定要鎖定列號(hào)，這樣才能正確的結(jié)果。

8、十字交叉查詢

VLOOKUP函數(shù)如果有兩個(gè)條件是呈現(xiàn)十字交叉時(shí)且順序與數(shù)據(jù)區(qū)域中的順序不一致時(shí)，可以與MATCH函數(shù)完成查詢。

在H2單元格中輸入公式：=VLOOKUP($G2,$A$2:$D$9,MATCH(H$1,$A$1:$D$1,0),0)，按Enter鍵完成后向下向右填充。

注：一定要鎖定VLOOKUP函數(shù)的第一個(gè)參數(shù)的列號(hào)，MATCH函數(shù)的第一個(gè)參數(shù)的行號(hào)，這樣才能得到正確的結(jié)果。

9、多條件查詢

VLOOKUP還能進(jìn)行多條件查詢，這個(gè)用法相信有很多人不知道吧。

在I2單元格中輸入公式：

{=VLOOKUP(G2&H2,IF({1,0},$A$2:$A$9&$B$2:$B$9,$D$2:$D$9),2,0)}

按組合鍵<Ctrl+Shift+Enter>完成后向下填充。

注：公式兩邊的花括號(hào)不是手動(dòng)輸入的，而是按組合鍵后自動(dòng)輸入的。VLOOKUP的第三個(gè)參數(shù)為2，第四個(gè)參數(shù)為0是固定的。

10、反向查找

VLOOKUP函數(shù)也可以進(jìn)行反向查找。

在H2單元格中輸入公式：{=VLOOKUP(G2,IF({1,0},$B$2:$B$9,$A$2:$A$9),2,0)}，按組合鍵<Ctrl+Shift+Enter>鍵完成后向下填充。

注：公式兩邊的花括號(hào)不是手動(dòng)輸入的，而是按組合鍵后自動(dòng)輸入的。

11、一對(duì)多查詢

VLOOKUP函數(shù)還能進(jìn)行一對(duì)多查詢，但是這個(gè)方法并不鼓勵(lì)大家去使用。

在H2單元格中輸入公式：

{=VLOOKUP($G$2&ROW(A1),IF({1,0},$A$2:$A$9&COUNTIF(INDIRECT('a2:a'&ROW($2:$9)),$G$2),$D$2:$D$9),2,0)}，按組合鍵<Ctrl+Shift+Enter>完向下填充。

注：公式兩邊的花括號(hào)不是手動(dòng)輸入的，而是按組合鍵后自動(dòng)輸入的。

SUMIF函數(shù)（條件計(jì)數(shù)）

重要程度：★★★★

學(xué)習(xí)難度：★★★

SUM的意思是“加和”，再加上“IF”，意思就是對(duì)范圍中符合指定條件的值求和，即滿足相應(yīng)的條件才進(jìn)行計(jì)算。

在工作中，大部分場(chǎng)景都不是對(duì)所有數(shù)值進(jìn)行求和，而是根據(jù)一定的條件篩選后在一定范圍內(nèi)進(jìn)行計(jì)算，比如統(tǒng)計(jì)某個(gè)產(chǎn)品的銷售額情況，統(tǒng)計(jì)某個(gè)部門的人員工資情況。

要解決這種條件求和問題就要用到SUMIF函數(shù)了。

SUMIF函數(shù)結(jié)構(gòu)：=SUMIF(條件范圍,條件,求和范圍)

SUMIF函數(shù)有一個(gè)強(qiáng)化版本，即多條件版本——SUMIFS。例如，下表是一份應(yīng)付賬款的借款明細(xì)表。請(qǐng)按右面的條件進(jìn)行統(tǒng)計(jì)。要求：已付清企業(yè)的應(yīng)付款額大于30萬(wàn)的平均值。

在G5單元格中輸入公式：=AVERAGEIFS(C:C,E:E,'是',C:C,'>300000')，按Enter鍵完成。

注意：該函數(shù)是求平均值的函數(shù)，如果除數(shù)為0的情況下會(huì)返回錯(cuò)誤值'#DIV/0!'，即沒有滿足條件的值的時(shí)候會(huì)報(bào)出錯(cuò)誤值。

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

數(shù)據(jù)透視表創(chuàng)建

讓Excel變得更加商務(wù)

以上，就是小編今天給大家分享的Excel的的基礎(chǔ)技能和進(jìn)階技能。完成了基礎(chǔ)的底子，我們?cè)賮砜紤]怎么讓Excel變得好看，以及讓我們的效率變得更高！現(xiàn)在我們?cè)倩氐轿恼麻_始的問題，怎么讓Excel變得更加商務(wù)，像老外的Excel那樣漂亮！

我們對(duì)老外的表格好看的原因進(jìn)行了歸納，下面我們按老外的思路改造我們的表格。

第1步：更換和弱化表格線，突出顯示數(shù)據(jù)。

• 去掉表格背景網(wǎng)絡(luò)線

• 除表頭和表尾外，數(shù)據(jù)部分用淺灰色表格線。

第2步：設(shè)置隔行背景色，可以選淺灰或淺藍(lán)色填充

• 填允一行后，用格式刷向下刷

• 稍增加行高

第3步：修改字體類型。

• 標(biāo)題用黑體

• 數(shù)字用Arial

• 漢字用微軟雅黑

• 合計(jì)行字體加粗

第4步：用條形圖增加的年合計(jì)的可視性。

選取G5:G11  -  條件格式 - 數(shù)據(jù)條

完工！

如果表格不需要打印，我們還可以換另外一種風(fēng)格：

• 表頭深色背景，白色字體

• 中間用淺色填充，表格線用白色細(xì)線

• 表尾灰色背景

或

也有同學(xué)說，為什么不直接套用表格？套用后你會(huì)發(fā)現(xiàn)，結(jié)果并不是你想要的。

另：老外還有3個(gè)常用法寶

• 填充色用同一個(gè)色系，讓數(shù)據(jù)和背景一體。

• 控件的使用方便篩選數(shù)據(jù)，又增強(qiáng)了商務(wù)感。（通過定義名稱引用控件數(shù)據(jù)生成動(dòng)態(tài)數(shù)據(jù)源）

• 小圖片的裝飾。

其實(shí)Excel漂亮并不意味著花梢，表格設(shè)計(jì)就是要突出和展示數(shù)據(jù)，達(dá)到這個(gè)目的，又能看上去很舒服。就是完美又好看的Excel表格。

Excel必備操作技巧分享

拒絕加班！