編按：

Hello Everyone~我們經(jīng)常說(shuō)精通Excel可以幫助我們提高工作效率，減少加班！但精通Excel不是一朝一夕的事情，當(dāng)我們Excel水平是只會(huì)簡(jiǎn)單的IF函數(shù)和數(shù)據(jù)透視表的情況下，就完全不能解決工作中復(fù)雜的問(wèn)題了嗎？當(dāng)然不是！用不好Excel也許不是你技術(shù)不行而是思維被局限了！今天我們就來(lái)學(xué)習(xí)一下如何用簡(jiǎn)單的IF函數(shù)與數(shù)據(jù)透視表搞定復(fù)雜的報(bào)銷金額核對(duì)問(wèn)題。

小劉是某公司財(cái)務(wù)勤雜工，主要負(fù)責(zé)整理各項(xiàng)報(bào)銷申請(qǐng)，每天的工作就是核對(duì)每個(gè)報(bào)銷單的申請(qǐng)金額與實(shí)際報(bào)銷金額是否一致。簡(jiǎn)化后的數(shù)據(jù)如圖所示。

核對(duì)報(bào)銷常見(jiàn)的就三種情況：

①是實(shí)際報(bào)銷金額可能會(huì)略高于申請(qǐng)金額，不超過(guò)一定比例也是認(rèn)可的；

②是實(shí)際報(bào)銷金額小于申請(qǐng)金額；

③則是最常見(jiàn)的申請(qǐng)金額與報(bào)銷金額一致。

8月的報(bào)銷核對(duì)完后，經(jīng)理吩咐小劉，把8月份的數(shù)據(jù)按照三種情況標(biāo)一下，他要看看。8月份一共兩百多張單子，小劉用了半小時(shí)就搞定了，然后找經(jīng)理匯報(bào)。

“經(jīng)理，標(biāo)好了，紅色是實(shí)際報(bào)銷金額高于申請(qǐng)金額的，黃色是實(shí)際報(bào)銷金額低于申請(qǐng)金額的，沒(méi)有顏色的就是相等的。200多條數(shù)據(jù)，我用了16分鐘做好，又花了10分鐘檢查，剩余4分鐘我還抽樣了，保證沒(méi)問(wèn)題！”做好的表格大概就是這樣子。

經(jīng)理打開(kāi)表格掃了幾眼，沖小劉說(shuō)：把實(shí)際高于計(jì)劃的單數(shù)以及高出的金額算出來(lái)。小劉回去就開(kāi)始折騰，看了幾分鐘實(shí)在是頭大，因?yàn)閿?shù)的眼睛都疼了，也沒(méi)統(tǒng)計(jì)出來(lái)，結(jié)果老是對(duì)不上。于是就開(kāi)始百度，也無(wú)結(jié)果，實(shí)在沒(méi)辦法了就在微信群里發(fā)了求助信息：“各位大神，老師們，請(qǐng)問(wèn)如何通過(guò)顏色來(lái)匯總??？”

大神們倒是很熱情，搶著回復(fù)：VBA啊、宏表函數(shù)啊、顏色篩選啊，輔助列啊。

聽(tīng)的小劉一臉懵逼，沒(méi)辦法，還是老老實(shí)實(shí)按計(jì)算器吧。又30分鐘過(guò)去了，結(jié)果終于正確了，趕緊回復(fù)經(jīng)理：一共91單，金額是12995。

接著經(jīng)理又陸續(xù)提出這樣幾個(gè)要求：

低于計(jì)劃的和等于計(jì)劃的各是多少單？

公司規(guī)定實(shí)際報(bào)銷不能超過(guò)計(jì)劃金額5%的，如果超了最多按5%報(bào)銷，看下有多少單超標(biāo)了，超標(biāo)的金額一共是多少？

最好再按周統(tǒng)計(jì)一下，每一周的單數(shù)和金額？

……

場(chǎng)景是不是很熟悉？那么問(wèn)題來(lái)了，你覺(jué)得小劉多久才能完成這些工作呢？我猜小劉晚上加班是肯定了，至于多久，不好說(shuō)，也許是第二天下午了吧。

問(wèn)題出在哪里？是經(jīng)理的要求太麻煩嗎，還是小劉的技術(shù)太菜了？都不是！就拿這個(gè)簡(jiǎn)化后的數(shù)據(jù)來(lái)說(shuō)，經(jīng)理的要求都是很常規(guī)的，如果加上報(bào)銷人、報(bào)銷部門、報(bào)銷事由等等信息，可以統(tǒng)計(jì)的就更多了。

是小劉的水平太菜了？是也不是，因?yàn)樾⒁婚_(kāi)始就選擇一種注定是低效率的方法：標(biāo)顏色。標(biāo)顏色這種操作給人的就是直觀的視覺(jué)感受，但要想進(jìn)一步做統(tǒng)計(jì)，基本也只能靠視覺(jué)，也就是靠眼睛來(lái)看了。與之類似的低效率操作還有合并單元格。

那么，什么方法才是高效率的呢？首先要明確一點(diǎn)，涉及到數(shù)據(jù)統(tǒng)計(jì)的問(wèn)題，就得用適合數(shù)據(jù)統(tǒng)計(jì)的形式呈現(xiàn)。在Excel的表里，數(shù)據(jù)統(tǒng)計(jì)最基礎(chǔ)的就是對(duì)字段，也就是列。具體的統(tǒng)計(jì)方式就是計(jì)數(shù)或者求和。所以，如何在數(shù)據(jù)源里得到統(tǒng)計(jì)所需要用到的列，才是解決問(wèn)題的關(guān)鍵。

其實(shí)實(shí)現(xiàn)起來(lái)并不難，就是一些輔助列的事情。按照小劉所掌握的函數(shù)，構(gòu)造輔助列是足夠了，就是一個(gè)IF的事情。之后的數(shù)據(jù)統(tǒng)計(jì)靠數(shù)據(jù)透視表就能完成，這也是小劉會(huì)做的。

對(duì)于僅僅會(huì)用幾個(gè)基本函數(shù)（例如IF）和拖來(lái)拽去的數(shù)據(jù)透視表的小劉來(lái)說(shuō)，怎樣才是正確的操作方式？

首先要做的就是基礎(chǔ)數(shù)據(jù)的完善，通常可以結(jié)合自己的業(yè)務(wù)經(jīng)驗(yàn)、公司的管理要求、領(lǐng)導(dǎo)的習(xí)慣這幾個(gè)方面入手。結(jié)合自己的業(yè)務(wù)經(jīng)驗(yàn)來(lái)說(shuō)，首先是對(duì)單據(jù)做基本的分類標(biāo)注：

①實(shí)際大于計(jì)劃：實(shí)際報(bào)銷金額可能會(huì)略高于申請(qǐng)金額，不超過(guò)一定比例也是認(rèn)可的；

②實(shí)際小于計(jì)劃：是實(shí)際報(bào)銷金額小于申請(qǐng)金額；

③實(shí)際等于計(jì)劃：則是最常見(jiàn)的申請(qǐng)金額與報(bào)銷金額一致。

這個(gè)用IF函數(shù)很容易就能做到，公式為：=IF(D2>C2,"實(shí)際大于計(jì)劃",IF(D2=C2,"實(shí)際等于計(jì)劃","實(shí)際小于計(jì)劃"))

接下來(lái)，按照公司規(guī)定：實(shí)際報(bào)銷不能超過(guò)計(jì)劃金額5%的，如果超了最多按5%報(bào)銷。所以繼續(xù)用IF函數(shù)來(lái)判斷，是否超標(biāo)。公式為：=IF(D2/C2>=1.05,"是","否")

判斷完是否超標(biāo)以后，就應(yīng)該想到還需要算出審批金額，也就是超了最多按5%報(bào)銷，沒(méi)超就按實(shí)際金額報(bào)銷。還是用IF來(lái)實(shí)現(xiàn)，公式為：=IF(F2="否",D2,C2*1.05)

到這里已經(jīng)添加了三個(gè)輔助列，分別是按照自己的業(yè)務(wù)經(jīng)驗(yàn)和公式的相關(guān)規(guī)定設(shè)計(jì)的，那么領(lǐng)導(dǎo)的習(xí)慣呢？對(duì)于超標(biāo)的單據(jù)，超過(guò)了多少金額是不是應(yīng)該考慮加上，萬(wàn)一領(lǐng)導(dǎo)問(wèn)起來(lái)就不用返工了。要算出超標(biāo)的金額，連函數(shù)都不需要，直接用實(shí)際金額-審批金額就行。

好了，暫時(shí)想到可以完善的數(shù)據(jù)都添加了，就等著看領(lǐng)導(dǎo)有什么要求吧，可以沖杯咖啡休息一下了……

領(lǐng)導(dǎo)：小劉，統(tǒng)計(jì)一下實(shí)際大于計(jì)劃、實(shí)際等于計(jì)劃和實(shí)際小于計(jì)劃的各有多少單，多少金額。

小劉：好的，馬上就有結(jié)果。

動(dòng)手點(diǎn)幾下鼠標(biāo)就搞定了，真爽，繼續(xù)喝咖啡。

五分鐘以后

領(lǐng)導(dǎo)：小劉，統(tǒng)計(jì)一下實(shí)際大于計(jì)劃里超過(guò)5%的有多少單。

小劉：好的，馬上就有結(jié)果。

動(dòng)手點(diǎn)幾下鼠標(biāo)就搞定了，真爽，繼續(xù)喝咖啡。

又過(guò)了五分鐘

領(lǐng)導(dǎo)：小劉，統(tǒng)計(jì)一下符合標(biāo)準(zhǔn)的報(bào)銷金額，還有實(shí)際報(bào)銷一共超了多少。

小劉：好的，馬上就有結(jié)果。

動(dòng)手點(diǎn)幾下鼠標(biāo)就搞定了，真爽，繼續(xù)喝咖啡。

又過(guò)了五分鐘

領(lǐng)導(dǎo)：小劉，按周統(tǒng)計(jì)一下審核后的報(bào)銷金額。

小劉：好的，馬上就有結(jié)果。

動(dòng)手點(diǎn)幾下鼠標(biāo)就搞定了，真爽，繼續(xù)喝咖啡。

領(lǐng)導(dǎo)：小劉，最近效率很高啊，繼續(xù)加油，我會(huì)把你的表現(xiàn)告訴老板的。

小劉：謝謝領(lǐng)導(dǎo)的肯定，我會(huì)繼續(xù)努力的。

小劉心里美美的繼續(xù)喝咖啡……

像小劉這樣的表哥表姐們還有很多，甚至?xí)谋刃⒏?，但是卻沒(méi)有小劉這樣的效果，為什么？

三個(gè)建議給大家：

第一，不要利用一些基礎(chǔ)的操作來(lái)解決復(fù)雜的問(wèn)題。比如最常見(jiàn)的就是利用篩選來(lái)統(tǒng)計(jì)，利用顏色來(lái)標(biāo)記與統(tǒng)計(jì)，利用奇奇怪怪的方法來(lái)實(shí)現(xiàn)數(shù)據(jù)匹配等等；

第二，統(tǒng)計(jì)就用數(shù)據(jù)透視表。學(xué)透視表是干嘛用？就是為了統(tǒng)計(jì)數(shù)據(jù)！

第三，靈活使用輔助列方法。配合透視表做統(tǒng)計(jì)的時(shí)候，如果沒(méi)有需要統(tǒng)計(jì)的字段，那這個(gè)時(shí)候使用輔助列是最好了，另外還有其他的一些情況需要我們靈活使用。

Excel用得不好真的會(huì)加很多班！很多時(shí)候真的不是你的技術(shù)不行……

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

７個(gè)Excel小技巧，提高表格查看效率

Excel運(yùn)用規(guī)范1：一個(gè)單元格只記錄一條信息

快速整理不規(guī)范的Excel表格的7個(gè)公式

9條最實(shí)用的計(jì)算excel中關(guān)于日期的公式?。ńㄗh收藏）

版權(quán)申明：

本文作者郅龍；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。