我是如何應(yīng)用Excel的vlookup函數(shù)和數(shù)據(jù)透視表進(jìn)行對(duì)賬操作的

我是如何應(yīng)用Excel的vlookup函數(shù)和數(shù)據(jù)透視表進(jìn)行對(duì)賬操作的假設(shè)你面對(duì)如下的賬單左面的是我方做出的賬單，右面是對(duì)方給你的賬單，我是用vlookup函數(shù)找出兩組數(shù)據(jù)的不同,然后利用數(shù)據(jù)透視表找出重復(fù)的紀(jì)錄,實(shí)際工作中Excel幫了我的大忙。寄件日期運(yùn)單編號(hào)費(fèi)用值寄件日期運(yùn)單編號(hào)費(fèi)用值2006-6-1222006-6-1222006-6-3182006-6-3182006-6-6352006-6-6352006-6-7202006-6-7202006-6-8282006-6-8282006-6-10452006-6-9182006-6-10182006-6-10452006-6-12202006-6-12202006-6-12302006-6-12302006-6-13252006-6-13252006-6-1521001681646402006-6-1521001681646402006-6-15202006-6-16182006-6-16182006-6-16182006-6-16182006-6-17352006-6-17352006-6-18202006-6-18202006-6-18202006-6-18202006-6-20202006-6-19202006-6-22152006-6-22152006-6-23502006-6-23502006-6-23402006-6-23402006-6-23402006-6-23402006-6-24202006-6-24202006-6-24252006-6-24252006-6-24202006-6-24202006-6-27202006-6-27202006-6-27202006-6-27202006-6-27182006-6-28502006-6-28502006-6-28182006-6-29182006-6-29652006-6-2965835833一、我在我方賬單的最后一行中插入vlookup函數(shù)。＝VLOOKUP(B31,$H$2:$I$37,2,FALSE)注意1、是絕對(duì)引用數(shù)組，且數(shù)組多加了幾行，2、第4個(gè)參數(shù)為FALSE,那是因?yàn)閿?shù)組沒有排序，如果排序了，就可以省略。在E列輸入公式＝C31-D31.那是用于比擬數(shù)值的。往上復(fù)制D/E兩列公式，就可發(fā)現(xiàn)發(fā)現(xiàn)寄件日期運(yùn)單編號(hào)費(fèi)用值2006-6-1222202006-6-3181802006-6-6353502006-6-7202002006-6-8282802006-6-10454502006-6-10181802006-6-12202002006-6-12303002006-66-15210016816463040-102006-6-1520#N/A#N/A2006-6-1618#N/A#N/A2006-6-16181802006-6666-19202002006-6-22151502006-6-23505002006-6-23404002006-6-23404002006-6-24202002006-6-24252502006-6-24202002006-6-27202002006-6-27202002006-6-28505002006-6-28181802006-6-29656502006-6-1618180有兩條記錄是對(duì)方?jīng)]有的，另外有一條費(fèi)用值不同，我方記賬為30，而對(duì)方記賬為40。通過查賬是我方記錯(cuò)。將30更改為40。另外在對(duì)方數(shù)據(jù)中參加〔用不同顏色表示〕我方記賬而對(duì)方?jīng)]記賬的兩條數(shù)據(jù)。至此對(duì)方共有32條記錄。同樣用vlookup函數(shù)也很容易就知道寄件日期運(yùn)單編號(hào)費(fèi)用值2006-6-1222202006-6-3181802006-6-6353502006-6-7202002006-6-8282802006-6-9181802006-6-10454502006-6-12202002006-6-12303002006-66-1521001681646404002006-6-16181802006-6-1618#N/A#N/A2006-6666-20202002006-6-22151502006-6-23505002006-6-23404002006-6-23404002006-6-24202002006-6-24252502006-6-24202002006-6-27202002006-6-27202002006-6-27181802006-6-28505002006-6-29181802006-6-29656502006-66-1618180我方漏記了這樣一條記錄，同樣在我方賬單里參加漏記的記錄后，發(fā)現(xiàn)，對(duì)方的總金額比我方的總金額多了18元，是什么原因造成的呢，雙方都參加了對(duì)方有賬而自己沒有記賬的記錄，應(yīng)該是相同的，況且單個(gè)金額又都相同，這時(shí)我是用數(shù)據(jù)透視表來幫我找出相同的記錄的。在Excel中點(diǎn)數(shù)據(jù)>數(shù)據(jù)透視表或數(shù)據(jù)透視圖.>選定區(qū)域>下一步>點(diǎn)布局在彈出的窗口中將運(yùn)單編號(hào)從右邊拖到左邊行那里，將費(fèi)用值從右邊拖到中間數(shù)據(jù)那里將鼠標(biāo)指向求和項(xiàng)：費(fèi)用值，雙擊它，在新窗口的匯總方式下選擇計(jì)數(shù)，然后點(diǎn)確定上圖中的求和項(xiàng)就變成了計(jì)數(shù)項(xiàng)。再點(diǎn)確認(rèn)完成。然后就可在新插入的數(shù)據(jù)表中看到我們想要的數(shù)據(jù)表了。計(jì)數(shù)項(xiàng):費(fèi)用值運(yùn)單編號(hào)匯總111111111111111121111111111111210016816461總計(jì)32看到標(biāo)記黃色的2記錄沒有，它就是重復(fù)記錄，最后兩邊的金額相同了，對(duì)賬完成。寄件日期運(yùn)單編號(hào)費(fèi)用值寄件日期運(yùn)單編號(hào)費(fèi)用值2006-6-1222202006-6-1222202006-6-3181802006-6-3181802006-6-6353502006-6-6353502006-6-7202002006-6-7202002006-6-8282802006-6-8282802006-6-10454502006-6-9181802006-6-10181802006-6-10454502006-6-12202002006-6-12202002006-6-12303002006-6-12303002006-666-1521001681646404002006-6-1521001681646404002006-66-16181802006-6-16181802006-6-16181802006-6-16181802006-6666666-20202002006-6-19202002006-6-22151502006-6-22151502006-6-23505002006-6-23505002006-6-23404002006-6-23404002006-6-23404002006-6-23404002006-6-24202002006-6-24202002006-6-24252502006-6-24252502006-6-24202002006-6-24202002006-6-27202002006-6-27202002006-6-27202002006-6-27202002006-6-27181802006-6-28505002006-6-28505002006-6-28181802006-6-292006-6-29656502006-6-29656502006-6-16181802006-6-15202008532006-6-1618180853需要注意的是如果使用Vlookup函數(shù)，如果查找方跟被查找方數(shù)據(jù)類型不一樣，查找就會(huì)不成功，為了排除這方面的困難，我實(shí)際操作時(shí)將所有的其它格式軟化為文本格式，插入一輔助列，輸入公式＝text(b32,0)然后向上復(fù)