我是如何应用Excel的vlookup函数和数据透视表进行对账操作的.doc

我是如何应用Excel的vlookup函数和数据透视表进行对账操作的假设你面对如下的账单左面的是我方做出的账单，右面是对方给你的账单，我是用vlookup函数找出两组数据的不同,然后利用数据透视表找出重复的纪录,实际工作中Excel帮了我的大忙。寄件日期运单编号费用值寄件日期运单编号费用值2006-6-111000072829222006-6-111000072829222006-6-311000058331182006-6-311000058331182006-6-611000081961352006-6-611000081961352006-6-711000086318202006-6-711000086318202006-6-811000081972282006-6-811000081972282006-6-1011000063329452006-6-911000086360182006-6-1011000086360182006-6-1011000063329452006-6-1211000086339202006-6-1211000086339202006-6-1211000091036302006-6-1211000091036302006-6-1311000099423252006-6-1311000099423252006-6-1521001681646402006-6-1521001681646402006-6-1511000086330202006-6-1611000063183182006-6-1611000063439182006-6-1611000086309182006-6-1611000063183182006-6-1711000053608352006-6-1711000053608352006-6-1811000099042202006-6-1811000099042202006-6-1811000099127202006-6-1811000099127202006-6-2011000086366202006-6-1911000086366202006-6-2211000088207152006-6-2211000088207152006-6-2311000060302502006-6-2311000060302502006-6-2311000093745402006-6-2311000093746402006-6-2311000093746402006-6-2311000093745402006-6-2411000052826202006-6-2411000052826202006-6-2411000088014252006-6-2411000088014252006-6-2411000088435202006-6-2411000088435202006-6-2711000091441202006-6-2711000091441202006-6-2711000091918202006-6-2711000091918202006-6-2711000086660182006-6-2811000091732502006-6-2811000091732502006-6-2811000086660182006-6-2911000086660182006-6-2911000099227652006-6-291100009922765835833一、我在我方账单的最后一行中插入vlookup函数。VLOOKUP(B31,$H$2:$I$37,2,FALSE)注意1、是绝对引用数组，且数组多加了几行，2、第4个参数为FALSE,那是因为数组没有排序，如果排序了，就可以省略。在E列输入公式C31-D31.那是用于比较数值的。往上复制D/E两列公式，就可发现发现寄件日期运单编号费用值2006-6-111000072829222202006-6-311000058331181802006-6-611000081961353502006-6-711000086318202002006-6-811000081972282802006-6-1011000063329454502006-6-1011000086360181802006-6-1211000086339202002006-6-1211000091036303002006-6-1311000099423252502006-6-15210016816463040-102006-6-151100008633020#N/A#N/A2006-6-161100006343918#N/A#N/A2006-6-1611000063183181802006-6-1711000053608353502006-6-1811000099042202002006-6-1811000099127202002006-6-1911000086366202002006-6-2211000088207151502006-6-2311000060302505002006-6-2311000093746404002006-6-2311000093745404002006-6-2411000052826202002006-6-2411000088014252502006-6-2411000088435202002006-6-2711000091441202002006-6-2711000091918202002006-6-2811000091732505002006-6-2811000086660181802006-6-2911000099227656502006-6-161100008630918180有两条记录是对方没有的，另外有一条费用值不同，我方记账为30，而对方记账为40。通过查账是我方记错。将30更改为40。另外在对方数据中加入（用不同颜色表示）我方记账而对方没记账的两条数据。至此对方共有32条记录。同样用vlookup函数也很容易就知道寄件日期运单编号费用值2006-6-111000072829222202006-6-311000058331181802006-6-611000081961353502006-6-711000086318202002006-6-811000081972282802006-6-911000086360181802006-6-1011000063329454502006-6-1211000086339202002006-6-1211000091036303002006-6-1311000099423252502006-6-1521001681646404002006-6-1611000063183181802006-6-161100008630918#N/A#N/A2006-6-1711000053608353502006-6-1811000099042202002006-6-1811000099127202002006-6-2011000086366202002006-6-2211000088207151502006-6-2311000060302505002006-6-2311000093745404002006-6-2311000093746404002006-6-2411000052826202002006-6-2411000088014252502006-6-2411000088435202002006-6-2711000091441202002006-6-2711000091918202002006-6-2711000086660181802006-6-2811000091732505002006-6-2911000086660181802006-6-2911000099227656502006-6-1511000086330202002006-6-161100006343918180我方漏记了11000086309这样一条记录，同样在我方账单里加入漏记的记录后，发现，对方的总金额比我方的总金额多了18元，是什么原因造成的呢，双方都加入了对方有账而自己没有记账的记录，应该是相同的，况且单个金额又都相同，这时我是用数据透视表来帮我找出相同的记录的。在Excel中点数据数据透视表或数据透视图.选定区域下一步点布局在弹出的窗口中将运单编号从右边拖到左边行那里，将费用值从右边拖到中间数据那里将鼠标指向求和项：费用值，双击它，在新窗口的汇总方式下选择计数，然后点确定上图中的求和项就变成了计数项。再点确认完成。然后就可在新插入的数据表中看到我们想要的数据表了。计数项:费用值运单编号汇总110000528261110000536081110000583311110000603021110000631831110000633291110000634391110000728291110000819611110000819721110000863091110000863181110000863301110000863391110000863601110000863661110000866602110000880141110000882071110000884351110000910361110000914411110000917321110000919181110000937451110000937461110000990421110000991271110000992271110000994231210016816461总计32看到标记黄色的2记录没有，它就是重复记录，最后两边的金额相同了，对账完成。寄件日期运单编号费用值寄件日期运单编号费用值2006-6-111000072829222202006-6-111000072829222202006-6-311000058331181802006-6-311000058331181802006-6-611000081961353502006-6-611000081961353502006-6-711000086318202002006-6-711000086318202002006-6-811000081972282802006-6-811000081972282802006-6-1011000063329454502006-6-911000086360181802006-6-1011000086360181802006-6-1011000063329454502006-6-1211000086339202002006-6-1211000086339202002006-6-1211000091036303002006-6-1211000091036303002006-6-1311000099423252502006-6-1311000099423252502006-6-1521001681646404002006-6-1521001681646404002006-6-1511000086330202002006-6-1611000063183181802006-6-1611000063439181802006-6-1611000086309181802006-6-1611000063183181802006-6-1711000053608353502006-6-1711000053608353502006-6-1811000099042202002006-6-1811000099042202002006-6-1811000099127202002006-6-1811000099127202002006-6-2011000086366202002006-6-1911000086366202002006-6-2211000088207151502006-6-2211000088207151502006-6-2311000060302505002006-6-2311000060302505002006-6-2311000093745404002006-6-2311000093746404002006-6-2311000093746404002006-6-2311000093745404002006-6-2411000052826202002006-6-2411000052826202002006-6-2411000088014252502006-6-2411000088014252502006-6-2411000088435202002006-6-2411000088435202002006-6-2711000091441202002006-6-2711000091441202002006-6-2711000091918202002006-6-2711000091918202002006-6-2711000086660181802006-6-2811000091732505002006-6-2811000091732505002006-6-2811000086660181802006-6-29110000866602006-6-2911000099227656502006-6-2911000099227656502006-6-1611000086309181802006-6-1511000086330202008532006-6-161100006343