你加班2小時做的Excel表格,我只需1分鐘!(建議收藏)

今天老師要強烈推薦一個有深度也有趣的財稅學習平臺給你！

微信公眾號「稅政第一線」，致力於稅收知識通俗化，第一時間分享新鮮、準確、接地氣的財稅資訊。

稅政第一線主編——稅政君：長期致力於財稅領域理論和實務研究，曾為多家集團公司提供常年財稅顧問、Excel內訓工作！

稅政第一線還有專業答疑社群——有財社，聚集了500餘位財稅精英，絕大多數問題都可以得到快速、專業的解答。

這個號已成功幫助了近100萬+的同行朋友，趕緊掃碼加入吧

今日前100名關注，回復  免費領取  還能領取10G財稅資料包，包含1000份財務excel模板表格、華為會計報銷指南等等。

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

最近，老師發現老外做的Excel表格，非常的漂亮。

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

根據老師的了解，無論是在企業做財務，還是在四大做審計，有一項共通的技能就是：Excel要6到飛起！正所謂Excel玩得好，下班會很早。

那麼，問題是，Excel的技能那麼多，作為財務，怎樣才能算完全精通呢？達到什麼樣的水平才能讓Excel表格又好看，又實用呢？

正是有這樣的疑問，所以才有今天這樣一篇文章。老師特意選在周末，給大家分享這篇乾貨滿滿的文章，我搜集了財務最常用的Excel技巧，讓大家的Excel技能提升一個level！

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

① 800個會計最常用Excel表格模板

② 203個會計工作Excel快捷鍵大全

③ 168套財務最高逼格Excel分析圖表

④ 153個Excel視頻教程，1800分鐘，入門到精通

⑤ 37套會計做帳Excel全套表格

⑥ 2019全年工資表Excel工具包，帶公式，數據全自動生成

掃碼回覆：7月

 即可領取↓↓↓

部分資料展示詳細如下

 01 

SUM函數（數學）

重要程度：★★★★★

學習難度：★★

在日常的工作中與學習中，SUM是一款應用極為廣泛並且做為入門級的函數來學習的。所以，整體來說，SUM函數難度不高，應用性卻很廣泛。但是不能因為簡單就小瞧了SUM，它還有一些不為人知的小技巧！

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

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

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

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

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

註：一定要注意第二個SUM函數的區域範圍要錯位，不然就報錯。

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

注意：這裡是自用了小計與求和的過程是重複計算了上面的數據，所以再除以2就可以得到不重複的結果，也正是想要的結果。

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

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

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

注意：這裡的公式兩邊的花括號不是的手動輸入的，而是在按組合鍵後系統自動添加上去的。

下表中是4個月的業績統計，每個工作表的裡面的張成的位置都是一樣的，求張成的1-4月的提成統計。

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

注意：在Excel中是支持上面的這種引用的，但是在WPS裡面貌似不支持。並且要注意每個表中的結構或者數字的固定的位置是一樣的，才可以使用這個公式。

六、多區域聯合計算

在有些工作表中，我們並不需要對連續的區域進行求和，可能是對不連續的某幾個區域進行求和。

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

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

=SUM(B2:C2,E2,G2)

注意：這裡使用逗號將各個不連續的區域連接起來的引用方式叫聯合引用。

除了SUMIF，SUMIFS之外還可以使用SUM函數來進行條件求和。

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

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

除了上面的求和之外，SUM函數還可以代替COUNTIFS，COUNIF函數進行計數。

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

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

 02 

IF函數（邏輯）

重要程度：★★★★

學習難度：★★★

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

比如：如果已付清，則為「關帳」，如果未付清且金額大於3000元，則為「立即摧帳」，如果未付清且小於3000元，則為「狀態正常」。

在H4單元格中輸入公式：=IF(E2="否",IF(D2>=3000,"立即催帳","狀態正常"),"關帳")，按Enter鍵完成後向下填充。

注意：通常情況下，IF函數經常與其他的邏輯函數一起使用，比如AND，OR，NOT等函數。該函數在使用的時候一定要注意前面的關聯的邏輯。

最後，IF函數配合VLOOKUP函數可以實現反向查找，雙條件查找。

① 800個會計最常用Excel表格模板

② 203個會計工作Excel快捷鍵大全

③ 168套財務最高逼格Excel分析圖表

④ 153個Excel視頻教程，1800分鐘，入門到精通

⑤ 37套會計做帳Excel全套表格

⑥ 2019全年工資表Excel工具包，帶公式，數據全自動生成

掃碼回覆：7月

 即可領取↓↓↓

 03 

VLOOKUP函數（篩選）

重要程度：★★★★★

學習難度：★★★★

VLOOKUP被稱為Excel中的效率之王，但是95%的Excel使用者都不能很好使用VLOOKUP。但是，VLOOKUP函數又是Excel中的大眾情人。有平臺曾經做過「如果只能選擇學習Excel中的一項功能，你會選擇哪個」的調查，VLOOKUP函數竟然高居第二位。

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

 

這些工作本質上都是匹配調用：匹配同樣的數據，調用出我們需要的數據。要解決這個問題，最常用到的就是VLOOKUP函數。

 

那麼VLOOKUP函數究竟如何使用呢？

 

VLOOKUP函數語法結構：VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP（查找值，查找範圍，返回的數值所在的列數，精確匹配還是模糊匹配）。

 

一、VLOOKUP函數精確匹配，返回你需要的唯一數據

比如根據姓名匹配身份證號信息，對於這種匹配調用唯一的數據，就要用到VLOOKUP函數的精確匹配了。

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

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

VLOOKUP查詢調用精確匹配

 

二、VLOOKUP函數模糊匹配，返回你需要的區間數據

比如根據考核等級確定獎金比例，對於這種在區間範圍內匹配調用數據，就要用到VLOOKUP函數的模糊匹配了，這個功能完全可以替代掉IF函數的多層嵌套，再也不用為寫錯順序發愁。

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

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

VLOOKUP查詢調用模糊匹配

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

1、常規查找

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

2、日期查找

在查找日期的時候查找的結果通常會是一串數字，為了使日期能夠返回相應的格式，那麼需要配合TEXT函數才能完成查找需求。

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

註：如返回格式為2018/12/03，則TEXT的第二個參數的格式可以設置為「yyyy/mm/dd」即可。

3、查找的值為空時

在當查找的值為空時，通常情況下會返回結果為0，那麼如果讓結果返回空白呢，解決的方法就是在公式後面一個「」。

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

4、當查找的目標格式不統一時報錯如何解決

（1）如果查找的目標值是文本格式，而數據區域中是數值格式。

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

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

註：--為兩個負號，即減負的意思，可以理解為負負得正，這裡是把文本強制轉換為數值，所以問題就很容易被解決了。

（2）如果查找的目標值是數值格式，而數據區域中是文本格式。

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

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

註：&""是強制地把數值格式轉換成文本格式。

5、區域查找

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

註：這裡使用該函數最後一個參數為1，即模糊查找，來確定查找的值處於給定的那一個區間。

6、模糊查找

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

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

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

註：如果要查找以「冰」開頭的那麼公式的第一參數為："*"&G3; 如果查找以「冰」結尾那麼公式的第一個參數為：G3&"*".

7、查找順序與數據區域中順序一致的多項時

VLOOKUP函數查找順序一致的多項時，可以藉助COLUMN函數構建查找序列。

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

註：COLUMN函數是返回列號。第一個參數一定要鎖定列號，這樣才能正確的結果。

8、十字交叉查詢

VLOOKUP函數如果有兩個條件是呈現十字交叉時且順序與數據區域中的順序不一致時，可以與MATCH函數完成查詢。

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

註：一定要鎖定VLOOKUP函數的第一個參數的列號，MATCH函數的第一個參數的行號，這樣才能得到正確的結果。

9、多條件查詢

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

在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的第三個參數為2，第四個參數為0是固定的。

10、反向查找

VLOOKUP函數也可以進行反向查找。

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

註：公式兩邊的花括號不是手動輸入的，而是按組合鍵後自動輸入的。

11、一對多查詢

VLOOKUP函數還能進行一對多查詢，但是這個方法並不鼓勵大家去使用。

在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>完向下填充。

註：公式兩邊的花括號不是手動輸入的，而是按組合鍵後自動輸入的。

 04 

SUMIF函數（條件計數）

重要程度：★★★★

學習難度：★★★

SUM的意思是「加和」，再加上「IF」，意思就是對範圍中符合指定條件的值求和，即滿足相應的條件才進行計算。

在工作中，大部分場景都不是對所有數值進行求和，而是根據一定的條件篩選後在一定範圍內進行計算，比如統計某個產品的銷售額情況，統計某個部門的人員工資情況。

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

SUMIF函數結構：=SUMIF(條件範圍,條件,求和範圍)

SUMIF函數有一個強化版本，即多條件版本——SUMIFS。例如，下表是一份應付帳款的借款明細表。請按右面的條件進行統計。要求：已付清企業的應付款額大於30萬的平均值。

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

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

① 800個會計最常用Excel表格模板

② 203個會計工作Excel快捷鍵大全

③ 168套財務最高逼格Excel分析圖表

④ 153個Excel視頻教程，1800分鐘，入門到精通

⑤ 37套會計做帳Excel全套表格

⑥ 2019全年工資表Excel工具包，帶公式，數據全自動生成

掃碼回覆：7月

 即可領取↓↓↓

 05 

數據透視表

重要程度：★★★★★

學習難度：★★★★

 

數據透視表是數據分析的神器，我們日常工作中要統計的各種報表都可以通過這一功能來實現。

 

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

 

一、拖拖拽拽」，快速製作統計報表，完成數據統計

 

根據你需要統計的數據維度和表格結構，「拖拖拽拽」，快速製作出你需要的統計報表，完成相應的數據統計。

 

操作步驟：選中原始數據表中的任意單元格—【插入】—【數據透視表】—【數據透視表欄位及區間】—根據報表行列呈現需要，在欄位列表中選定該欄位並按住滑鼠左鍵拖放到下方的矩陣窗口中，數據透視表布局即完成。

數據透視表創建

 

二、多種數值統計方式，輕鬆完成

數據透視表提供了求和、計數、最值、平均值、標準差、百分比等多種數值統計方式，你想要的結果它都可以呈現

 

操作步驟：需要幾種統計方式就拖入幾次計算【值欄位設置】—【值顯示方式】—【百分比】。

數據透視：多數值計算

 

三、根據時間變化創建組，報表多元顯示

不只是日期，數據按照月份、季度、年度或者它們的組合展示，統統都可以。

 

操作步驟：選中任一日期數據，右鍵創建組，選中月份，按住CTRL,再選中年，可以隨意組合的。這個也可以進行年齡分段統計等問題。

數據透視-創建組-時間

 

四、城市組合成區域，只要手動創建一下

北京、天津、瀋陽，這些城市如何組合成【華北區】？老闆就要的大區級的數據統計，我該怎麼辦？不要擔心，手動創建一下，瞬間完成

 

操作步驟：選中要組合的標籤（CTRL進行多選）—右鍵創建組—修改數據標籤。

數據透視-創建組-區域組合

 

五、數據透視表下，數據排序依然有效

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

 

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

數據透視表：排序

 

六、數據也可篩選，想要什麼找出什麼

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

 

操作步驟：選中任一一數據標籤—右鍵篩選—【前10個】—修改為按照銷售額最大的3個。

數據透視表：篩選

 

七、數據變化了，刷新一下，數據透視表隨之而動

根據統計的維度，我們就可以製作數據透視表模板了。數據一有變化，我們就更新一下，統計結果馬上出來，連「拖拖拽拽」的功夫都省了，這就是自動化！

 

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

數據透視表：手動刷新

 

八、總表分多表，利用篩選器，告別複製粘貼

從系統內導出的總表數據，如何根據我們的需要，比如銷售城市、銷售部門等標籤分成多個分表呢？數據透視表中的篩選器瞬間實現

 

操作步驟：將分表的數據標籤拖入數據透視表中的篩選器—數據透視表選項—顯示報表篩選頁—確定。

 

雙擊各個報表的匯總值，符合要求的原始數據就顯現了！

數據透視表-篩選器-分頁

 

九、數據按照時間軸滾動，日程表來了

讓重要數據按照時間軸展現？怎麼可能實現得了。插入一個日程表，就足夠了。

 

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

數據透視表—插入日程表

 

十、數據的遙控器，切片器

數據演示的時候，老闆突然說要看看某個類別的數據，匆匆忙忙趕緊找。唉，能不能給我個數據遙控器，想看什麼，點擊個菜單。哈哈哈，切片器就是來滿足你這個要求了。

數據透視表—插入切片器

 

十一、切片器多報表連結，按一鍵即可掌控

切片器不僅能構建多個，而且一個切片器可以連結多個報表。同一個遙控器，按一鍵，控制的可是多個報表，數據展現輕鬆暢快。

操作步驟：選中切片器—右鍵選擇報表連結—選中你需要的數據透視表即可。

數據透視表—切片器—多表連結

 

十二、不只有表，還有圖形展示：數據透視圖

完成的數據報表不只是可以通過表格實現，直接還可以生成圖表。如果再配合一個切片器，圖表竟然動了起來。

操作步驟：選中數據透視表任一單元格—數據透視圖—選中你需要的圖表類型—結合切片器，圖表就成為了動態圖表。

數據透視表—插入數據透視圖

 

數據透視表的功能是不是很強大，如果再讓你完成100張數據統計報表，是不是工作效率瞬間倍增。

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

數據透視表好用，但原始數據一定要規範：數據標籤行只有一行、數據完整、不要匯總統計、不要合併單元格、數據格式規範。千萬要記住！

 06 

讓Excel變得更加商務

以上，就是注會菌今天給大家分享的Excel的的基礎技能和進階技能。完成了基礎的底子，我們再來考慮怎麼讓Excel變得好看，以及讓我們的效率變得更高！現在我們再回到文章開始的問題，怎麼讓Excel變得更加商務，像老外的Excel那樣漂亮！

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

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

去掉表格背景網絡線

除表頭和表尾外，數據部分用淺灰色表格線。

第2步：設置隔行背景色，可以選淺灰或淺藍色填充

第3步：修改字體類型。

標題用黑體

數字用Arial

漢字用微軟雅黑

合計行字體加粗

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

選取G5:G11  -  條件格式 - 數據條

完工！

如果表格不需要列印，我們還可以換另外一種風格：

表頭深色背景，白色字體

中間用淺色填充，表格線用白色細線

表尾灰色背景

或

也有同學說，為什麼不直接套用表格？套用後你會發現，結果並不是你想要的。

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

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

① 800個會計最常用Excel表格模板

② 203個會計工作Excel快捷鍵大全

③ 168套財務最高逼格Excel分析圖表

④ 153個Excel視頻教程，1800分鐘，入門到精通

⑤ 37套會計做帳Excel全套表格

⑥ 2019全年工資表Excel工具包，帶公式，數據全自動生成

掃碼回覆：7月

 即可領取↓↓↓

 07 

Excel必備操作技巧分享

拒絕加班！

此外，給大家準備了一份非常重磅的會計資料包，來幫助你了解紅包的做帳指南與實操手冊，非常有學習意義。

適合人群

會計、稅務、審計類在校大學生以及新職場人

會計說內容轉載聲明：長期以來，有大量的同行從業者，在未經得我們許可的前提下，私自轉載我們的內容，不僅給我們的權益造成了重大的損害，而且沒有做到對內容製作者報以尊重。今天起，所有原創內容以及整編內容必須授權，未經授權私自轉載將維權到底，後臺回復【轉載】，可查看轉載要求。

▎本文轉自24財務excel，Excel不加班，Excel聚焦，Excel精英培訓，LinkedIn，請註明以上信息。

免費送送書了

手把手教你做優秀會計↓