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

我是如何应用 Excel 的 vlookup 函数和数据透视表进行对账操作的 假设你面对如下的账单左面的是我方做出的账单 右面是对方给你的账单 我是用 vlookup 函数找出两组数据的不同 然后利用数据透视表找出重复的纪录 实际工作中 Excel 帮了我的大忙 寄件日期 运单编号 费用值 寄件日期 运单编号 费用值 2006 6 1 11000072829 22 2006 6 1 11000072829 22 2006 6 3 11000058331 18 2006 6 3 11000058331 18 2006 6 6 11000081961 35 2006 6 6 11000081961 35 2006 6 7 11000086318 20 2006 6 7 11000086318 20 2006 6 8 11000081972 28 2006 6 8 11000081972 28 2006 6 10 11000063329 45 2006 6 9 11000086360 18 2006 6 10 11000086360 18 2006 6 10 11000063329 45 2006 6 12 11000086339 20 2006 6 12 11000086339 20 2006 6 12 11000091036 30 2006 6 12 11000091036 30 2006 6 13 11000099423 25 2006 6 13 11000099423 25 2006 6 15 21001681646 40 2006 6 15 21001681646 40 2006 6 15 11000086330 20 2006 6 16 11000063183 18 2006 6 16 11000063439 18 2006 6 16 11000086309 18 2006 6 16 11000063183 18 2006 6 17 11000053608 35 2006 6 17 11000053608 35 2006 6 18 11000099042 20 2006 6 18 11000099042 20 2006 6 18 11000099127 20 2006 6 18 11000099127 20 2006 6 20 11000086366 20 2006 6 19 11000086366 20 2006 6 22 11000088207 15 2006 6 22 11000088207 15 2006 6 23 11000060302 50 2006 6 23 11000060302 50 2006 6 23 11000093745 40 2006 6 23 11000093746 40 2006 6 23 11000093746 40 2006 6 23 11000093745 40 2006 6 24 11000052826 20 2006 6 24 11000052826 20 2006 6 24 11000088014 25 2006 6 24 11000088014 25 2006 6 24 11000088435 20 2006 6 24 11000088435 20 2006 6 27 11000091441 20 2006 6 27 11000091441 20 2006 6 27 11000091918 20 2006 6 27 11000091918 20 2006 6 27 11000086660 18 2006 6 28 11000091732 50 2006 6 28 11000091732 50 2006 6 28 11000086660 18 2006 6 29 11000086660 18 2006 6 29 11000099227 65 2006 6 29 11000099227 65 835 833 一 我在我方账单的最后一行中插入 vlookup 函数 VLOOKUP B31 H 2 I 37 2 FALSE 注意 1 是绝对引用数 组 且数组多加了几行 2 第 4 个参数为 FALSE 那是因为数组没有排序 如果排序了 就可以省略 在 E 列输入 公式 C31 D31 那是用于比较数值的 往上复制 D E 两列公式 就可发现发现 寄件日期 运单编号 费用值 2006 6 1 11000072829 22 22 0 2006 6 3 11000058331 18 18 0 2006 6 6 11000081961 35 35 0 2006 6 7 11000086318 20 20 0 2006 6 8 11000081972 28 28 0 2006 6 10 11000063329 45 45 0 2006 6 10 11000086360 18 18 0 2006 6 12 11000086339 20 20 0 2006 6 12 11000091036 30 30 0 2006 6 13 11000099423 25 25 0 2006 6 15 21001681646 30 40 10 2006 6 15 11000086330 20 N A N A 2006 6 16 11000063439 18 N A N A 2006 6 16 11000063183 18 18 0 2006 6 17 11000053608 35 35 0 2006 6 18 11000099042 20 20 0 2006 6 18 11000099127 20 20 0 2006 6 19 11000086366 20 20 0 2006 6 22 11000088207 15 15 0 2006 6 23 11000060302 50 50 0 2006 6 23 11000093746 40 40 0 2006 6 23 11000093745 40 40 0 2006 6 24 11000052826 20 20 0 2006 6 24 11000088014 25 25 0 2006 6 24 11000088435 20 20 0 2006 6 27 11000091441 20 20 0 2006 6 27 11000091918 20 20 0 2006 6 28 11000091732 50 50 0 2006 6 28 11000086660 18 18 0 2006 6 29 11000099227 65 65 0 2006 6 16 11000086309 18 18 0 有两条记录是对方没有的 另外有一条费用值不同 我方记账为 30 而对方记账为 40 通过查账是我方记错 将 30 更改为 40 另外在对方数据中加入 用不同颜色表示 我方记账而对方没记账的两条数据 至此对方共有 32 条 记录 同样用 vlookup 函数也很容易就知道 寄件日期 运单编号 费用值 2006 6 1 11000072829 22 22 0 2006 6 3 11000058331 18 18 0 2006 6 6 11000081961 35 35 0 2006 6 7 11000086318 20 20 0 2006 6 8 11000081972 28 28 0 2006 6 9 11000086360 18 18 0 2006 6 10 11000063329 45 45 0 2006 6 12 11000086339 20 20 0 2006 6 12 11000091036 30 30 0 2006 6 13 11000099423 25 25 0 2006 6 15 21001681646 40 40 0 2006 6 16 11000063183 18 18 0 2006 6 16 11000086309 18 N A N A 2006 6 17 11000053608 35 35 0 2006 6 18 11000099042 20 20 0 2006 6 18 11000099127 20 20 0 2006 6 20 11000086366 20 20 0 2006 6 22 11000088207 15 15 0 2006 6 23 11000060302 50 50 0 2006 6 23 11000093745 40 40 0 2006 6 23 11000093746 40 40 0 2006 6 24 11000052826 20 20 0 2006 6 24 11000088014 25 25 0 2006 6 24 11000088435 20 20 0 2006 6 27 11000091441 20 20 0 2006 6 27 11000091918 20 20 0 2006 6 27 11000086660 18 18 0 2006 6 28 11000091732 50 50 0 2006 6 29 11000086660 18 18 0 2006 6 29 11000099227 65 65 0 2006 6 15 11000086330 20 20 0 2006 6 16 11000063439 18 18 0 我方漏记了 11000086309 这样一条记录 同样在我方账单里加入漏记的记录后 发现 对方的总金额比我方的总金 额多了 18 元 是什么原因造成的呢 双方都加入了对方有账而自己没有记账的记录 应该是相同的 况且单个金 额又都相同 这时我是用数据透视表来帮我找出相同的记录的 在 Excel 中点数据 数据透视表或数据透视图 选定 区域 下一步 点布局在弹出的窗口中将运单编号从右边拖到左边行那里 将费用值从右边拖到中间数据那里将鼠 标指向求和项 费用值 双击它 在新窗口的汇总方式下选择计数 然后点确定上图中的求和项就变成了计数项 再点确认完成 然后就可在新插入的数据表中看到我们想要的数据表了 计数项 费用值 运单编号 汇总 11000052826 1 11000053608 1 11000058331 1 11000060302 1 11000063183 1 11000063329 1 11000063439 1 11000072829 1 11000081961 1 11000081972 1 11000086309 1 11000086318 1 11000086330 1 11000086339 1 11000086360 1 11000086366 1 11000086660 2 11000088014 1 11000088207 1 11000088435 1 11000091036 1 11000091441 1 11000091732 1 11000091918 1 11000093745 1 11000093746 1 11000099042 1 11000099127 1 11000099227 1 11000099423 1 21001681646 1 总计 32 看到标记黄色的 2 记录没有 它就是重复记录 最后两边的金额相同了 对账完成 寄件日期 运单编号 费用值 寄件日期 运单编号 费用值 2006 6 1 11000072829 22 22 0 2006 6 1 11000072829 22 22 0 2006 6 3 11000058331 18 18 0 2006 6 3 11000058331 18 18 0 2006 6 6 11000081961 35 35 0 2006 6 6 11000081961 35 35 0 2006 6 7 11000086318 20 20 0 2006 6 7 11000086318 20 20 0 2006 6 8 11000081972 28 28 0 2006 6 8 11000081972 28 28 0 2006 6 10 11000063329 45 45 0 2006 6 9 11000086360 18 18 0 2006 6 10 11000086360 18 18 0 2006 6 10 11000063329 45 45 0 2006 6 12 11000086339 20 20 0 2006 6 12 11000086339 20 20 0 2006 6 12 11000091036 30 30 0 2006 6 12 11000091036 30 30 0 2006 6 13 11000099423 25 25 0 2006 6 13 11000099423 25 25 0 2006 6 15 21001681646 40 40 0 2006 6 15 21001681646 40 40 0 2006 6 15 11000086330 20 20 0 2006 6 16 11000063183 18 18 0 2006 6 16 11000063439 18 18 0 2006 6 16 11000086309 18 18 0 2006 6 16 11000063183 18 18 0 2006 6 17 11000053608 35 35 0 2006 6 17 11000053608 35 35 0 2006 6 18 11000099042 20 20 0 2006 6 18 11000099042 20 20 0 2006 6 18 11000099127 20 20 0 2006 6 18 11000099127 20 20 0 2006 6 20 11000086366 20 20 0 2006 6 19 11000086366 20 20 0 2006 6 22 11000088207 15 15 0 2006 6 22 11000088207 15 15 0 2006 6 23 11000060302 50 50 0 2006 6 23 11000060302 50 50 0 2006 6 23 11000093745 40 40 0 2006 6 23 11000093746 40 40 0 2006 6 23 11000093746 40 40 0 2006 6 23 11000093745 40 40 0 2006 6 24 11000052826 20 20 0 2006 6 24 11000052826 20 20 0 2006 6 24 11000088014 25 25 0 2006 6 24 11000088014 25 25 0 2006 6 24 11000088435 20 20 0 2006 6 24 11000088435 20 20 0 2006 6 27 11000091441 20 20 0 2006 6 27 11000091441 20 20 0 2006 6 27 11000091918 20 20 0 2006 6 27 11000091918 20 20 0 2006 6 27 11000086660 18 18 0 2006 6 28 11000091732 50 50 0 2006 6 28 11000091732 50 50 0 2006 6 28 11000086660 18 18 0 2006 6 29 11000086660 2006 6 29 11000099227 65 65 0 2006 6 29 11000099227 65 65 0 2006 6 16 11000086309 18 18 0 2006 6 15 11000086330 20 20 0 853 2006 6 16 11000063439 18 18 0 853 需要注意的是如果使用 Vlookup 函数 如果查找方跟被查找方数据类型不一样 查找就会不成功 为了排除这方面的困难 我实际操 作时将所有的其它格式软化为文本格式 插入一辅助列 输入公式 text b32 0 然后向上复制公式 复制完公式后 选择所有公式区 域 刚才复制的 点复制 再点粘贴旁的向下按钮 选择值 即可这样不管数字也好 文本也好 通通转化成文本了 如果不知道使用 vlookup 和数据透视表的朋友 在 Excel 里选择一单元格 再点击插入函数这个按钮 在查找与引用中就能够找到这 个函数 在弹出插入函数的函数参数窗口后 点击有关该函数的帮助 H 等下就会弹出有关的帮助信息