俗話說“Excel用得好，下班會很早”。作為財務(wù)會計，工作中最常用到的工具就是Excel，如何利用它提高工作效率？這是我們今天討論的話題！

你們有沒有發(fā)現(xiàn)？老外做的Excel表格，一般都很漂亮。

再看看我們常見的表格，難看度一言難盡：

無論是在企業(yè)做財務(wù)，還是在四大做審計，有一項共通的技能就是：Excel要666到飛起！

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

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

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

重要程度：★★★★★

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

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

一、累計求和

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

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

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

二、帶有合格單元格的求和

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

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

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

三、帶有小計的單元格求和

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

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

四、文本如何求和

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

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

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

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

五、多工作表求和

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

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

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

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

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

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

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

=SUM(B2:C2,E2,G2)

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

七、條件求和

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

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

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

八、條件計數(shù)

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

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

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

IF函數(shù)（邏輯）

重要程度：★★★★

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

IF函數(shù)是Excel邏輯家族的扛把子，只要是邏輯判斷就可以說離不開IF函數(shù)。IF函數(shù)很簡單，幾乎接觸過Excel的人看到都能理解。下面我分享一下財務(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ù)在使用的時候一定要注意前面的關(guān)聯(lián)的邏輯。

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

VLOOKUP函數(shù)（篩選）

重要程度：★★★★★

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

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

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

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

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

VLOOKUP函數(shù)語法結(jié)構(gòu)：VLOOKUP

(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP（查找值，查找范圍，返回的數(shù)值所在的列數(shù)，精確匹配還是模糊匹配）。

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

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

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

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

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

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

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

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

注意事項：等級表的編制要從小到大

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

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

1、常規(guī)查找

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

2、日期查找

在查找日期的時候查找的結(jié)果通常會是一串?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的第二個參數(shù)的格式可以設(shè)置為“yyyy/mm/dd”即可。

3、查找的值為空時

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

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

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

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

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

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

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

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

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

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

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

5、區(qū)域查找

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

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

6、模糊查找

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

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

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

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

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

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

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

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

8、十字交叉查詢

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

在H2單元格中輸入公式：

=VLOOKUP($G2,$A$2:$D$9,MATCH(H$1,$A$1:$D$1,0),0)，按Enter鍵完成后向下向右填充。

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

9、多條件查詢

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

在I2單元格中輸入公式：

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

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

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

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

重要程度：★★★★

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

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

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

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

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

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

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

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

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

重要程度：★★★★★

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

數(shù)據(jù)透視表是數(shù)據(jù)分析的神器，我們?nèi)粘９ぷ髦幸y(tǒng)計的各種報表都可以通過這一功能來實(shí)現(xiàn)。

作為一種交互式的圖表，它允許用戶根據(jù)需要對各類數(shù)據(jù)維度進(jìn)行劃分，通過快捷地拖動各類數(shù)據(jù)維度，將他們進(jìn)行不同的重組，實(shí)現(xiàn)我們想要的結(jié)果。

一、拖拖拽拽”，快速制作統(tǒng)計報表，完成數(shù)據(jù)統(tǒng)計

根據(jù)你需要統(tǒng)計的數(shù)據(jù)維度和表格結(jié)構(gòu)，“拖拖拽拽”，快速制作出你需要的統(tǒng)計報表，完成相應(yīng)的數(shù)據(jù)統(tǒng)計。

操作步驟：選中原始數(shù)據(jù)表中的任意單元格—【插入】—【數(shù)據(jù)透視表】—【數(shù)據(jù)透視表字段及區(qū)間】—根據(jù)報表行列呈現(xiàn)需要，在字段列表中選定該字段并按住鼠標(biāo)左鍵拖放到下方的矩陣窗口中，數(shù)據(jù)透視表布局即完成。

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

二、多種數(shù)值統(tǒng)計方式，輕松完成

數(shù)據(jù)透視表提供了求和、計數(shù)、最值、平均值、標(biāo)準(zhǔn)差、百分比等多種數(shù)值統(tǒng)計方式，你想要的結(jié)果它都可以呈現(xiàn)

操作步驟：需要幾種統(tǒng)計方式就拖入幾次計算【值字段設(shè)置】—【值顯示方式】—【百分比】。

數(shù)據(jù)透視：多數(shù)值計算

三、根據(jù)時間變化創(chuàng)建組，報表多元顯示

不只是日期，數(shù)據(jù)按照月份、季度、年度或者它們的組合展示，統(tǒng)統(tǒng)都可以。

操作步驟：選中任一日期數(shù)據(jù)，右鍵創(chuàng)建組，選中月份，按住CTRL,再選中年，可以隨意組合的。這個也可以進(jìn)行年齡分段統(tǒng)計等問題。

數(shù)據(jù)透視-創(chuàng)建組-時間

四、城市組合成區(qū)域，只要手動創(chuàng)建一下

北京、天津、沈陽，這些城市如何組合成【華北區(qū)】？老板就要的大區(qū)級的數(shù)據(jù)統(tǒng)計，我該怎么辦？不要擔(dān)心，手動創(chuàng)建一下，瞬間完成

操作步驟：選中要組合的標(biāo)簽（CTRL進(jìn)行多選）—右鍵創(chuàng)建組—修改數(shù)據(jù)標(biāo)簽。

數(shù)據(jù)透視-創(chuàng)建組-區(qū)域組合

五、數(shù)據(jù)透視表下，數(shù)據(jù)排序依然有效

在數(shù)據(jù)透視表下，將數(shù)據(jù)升序、降序或者你自己定義的順序排序？

操作步驟：選中要排序的任一一數(shù)據(jù)—右鍵選擇排序—選擇升序或者降序。如果是自定義排序，先通過【選項】嵌入自定義排序，然后再選擇升序或者降序操作。

數(shù)據(jù)透視表：排序

六、數(shù)據(jù)也可篩選，想要什么找出什么

找出銷售量TOP3的明星銷售員？篩選一下，就是這么簡單

操作步驟：選中任一一數(shù)據(jù)標(biāo)簽—右鍵篩選—【前10個】—修改為按照銷售額最大的3個。

數(shù)據(jù)透視表：篩選

七、數(shù)據(jù)變化了，刷新一下，數(shù)據(jù)透視表隨之而動

根據(jù)統(tǒng)計的維度，我們就可以制作數(shù)據(jù)透視表模板了。數(shù)據(jù)一有變化，我們就更新一下，統(tǒng)計結(jié)果馬上出來，連“拖拖拽拽”的功夫都省了，這就是自動化！

操作步驟：選中數(shù)據(jù)透視表中任一數(shù)據(jù)—右鍵點(diǎn)擊刷新。這個刷新操作是無法自動完成，手動一下，手動一下就好。

數(shù)據(jù)透視表：手動刷新

八、總表分多表，利用篩選器，告別復(fù)制粘貼

從系統(tǒng)內(nèi)導(dǎo)出的總表數(shù)據(jù)，如何根據(jù)我們的需要，比如銷售城市、銷售部門等標(biāo)簽分成多個分表呢？數(shù)據(jù)透視表中的篩選器瞬間實(shí)現(xiàn)

操作步驟：將分表的數(shù)據(jù)標(biāo)簽拖入數(shù)據(jù)透視表中的篩選器—數(shù)據(jù)透視表選項—顯示報表篩選頁—確定。

雙擊各個報表的匯總值，符合要求的原始數(shù)據(jù)就顯現(xiàn)了！

數(shù)據(jù)透視表-篩選器-分頁

九、數(shù)據(jù)按照時間軸滾動，日程表來了

讓重要數(shù)據(jù)按照時間軸展現(xiàn)？怎么可能實(shí)現(xiàn)得了。插入一個日程表，就足夠了。

操作步驟：選中數(shù)據(jù)透視表任一單元格—插入日程表，拖拉一下日程表下方的滾動軸，想看哪個月就看哪個月，想看哪幾個月就看哪幾個月。

數(shù)據(jù)透視表—插入日程表

數(shù)據(jù)透視表的功能是不是很強(qiáng)大，如果再讓你完成100張數(shù)據(jù)統(tǒng)計報表，是不是工作效率瞬間倍增。

但在這里，還是要給大家一個小貼士：

數(shù)據(jù)透視表好用，但原始數(shù)據(jù)一定要規(guī)范：數(shù)據(jù)標(biāo)簽行只有一行、數(shù)據(jù)完整、不要匯總統(tǒng)計、不要合并單元格、數(shù)據(jù)格式規(guī)范。千萬要記??！

讓EXCEL變得更專業(yè)

以上，是今天給大家分享的Excel的的基礎(chǔ)技能和進(jìn)階技能。完成了基礎(chǔ)的底子，我們再來考慮怎么讓Excel變得好看，以及讓我們的效率變得更高！現(xiàn)在我們再回到文章開始的問題，怎么讓Excel變得更加商務(wù)，像老外的Excel那樣漂亮！

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

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

• 去掉表格背景網(wǎng)絡(luò)線
• 除表頭和表尾外，數(shù)據(jù)部分用淺灰色表格線。

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

• 填允一行后，用格式刷向下刷
• 稍增加行高

第3步：修改字體類型。

• 標(biāo)題用黑體
• 數(shù)字用Arial
• 漢字用微軟雅黑
• 合計行字體加粗

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

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

完工！

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

• 表頭深色背景，白色字體
• 中間用淺色填充，表格線用白色細(xì)線
• 表尾灰色背景

或

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

另：老外還有3個常用法寶

• 填充色用同一個色系，讓數(shù)據(jù)和背景一體。
• 控件的使用方便篩選數(shù)據(jù)，又增強(qiáng)了商務(wù)感。（通過定義名稱引用控件數(shù)據(jù)生成動態(tài)數(shù)據(jù)源）
• 小圖片的裝飾。

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