首页 ›存档› 技术 › 查看内容

每个追求效率的会计都应该学会Excel中"多表核对"

2018-3-30 13:00 |来自: 互联网 314 0

摘要: 每到月底LW就要对系统的数据跟手工输入的数据进行核对，以前Excel没重视，需要一项项核对，效率非常低下，搞得自己很累。这天跟我提起这事，我考虑了一下提出了一些对策。 1、借助合并计算 IF函数轻松实现 =IF(H2= ...

每到月底LW就要对系统的数据跟手工输入的数据进行核对，以前Excel没重视，需要一项项核对，效率非常低下，搞得自己很累。这天跟我提起这事，我考虑了一下提出了一些对策。

1、借助合并计算 IF函数轻松实现

=IF(H2=I2,"相同","不同")

合并计算有一个小细节需要注意，就是金额这里名字不能一样，否则就变成多表合并了。

2、删除重复项 SUMIF IF函数

001将手工跟系统2个表格的编号复制到G列，删除重复项。

002依次输入公式。

=SUMIF(A:A,G2,B:B)

=SUMIF(D:D,G2,E:E)

=IF(H2=I2,"","不同")

SUMIF函数视频教程

声明：文章版权归原作者所有部分文章转自互联网如有侵权请联系 <span id="email-placeholder">[邮箱地址]</span> 删除 </div> <script> (function() { // 邮箱地址使用 Base64 编码伪装，防止爬虫直接抓取 var encodedEmail = "YWRtaW5AaGFja2Jhc2UubmV0"; // admin@hackbase.net 的 Base64 编码 var email = atob(encodedEmail); // 解码得到真实邮箱 var container = document.getElementById("email-placeholder"); if (container) { container.textContent = email; } })(); </script> </td></tr> </table> <div id="diycontentbottom" class="area"></div> <script src="data/cache/home.js?zj6" type="text/javascript"></script> <div id="click_div"><table cellpadding="0" cellspacing="0" class="atd"> <tr><td> <a href="https://www.hackbase.net/home.php?mod=spacecp&ac=click&op=add&clickid=1&idtype=aid&id=230370&hash=c27c2a64735c4c0930d5f4c30043d867&handlekey=clickhandle" id="click_aid_230370_1" onclick="showWindow(this.id, this.href);doane(event);"> <img src="static/image/click/luguo.gif" alt="" /><br />路过</a> </td> <td> <a href="https://www.hackbase.net/home.php?mod=spacecp&ac=click&op=add&clickid=2&idtype=aid&id=230370&hash=c27c2a64735c4c0930d5f4c30043d867&handlekey=clickhandle" id="click_aid_230370_2" onclick="showWindow(this.id, this.href);doane(event);"> <img src="static/image/click/leiren.gif" alt="" /><br />雷人</a> </td> <td> <a href="https://www.hackbase.net/home.php?mod=spacecp&ac=click&op=add&clickid=3&idtype=aid&id=230370&hash=c27c2a64735c4c0930d5f4c30043d867&handlekey=clickhandle" id="click_aid_230370_3" onclick="showWindow(this.id, this.href);doane(event);"> <img src="static/image/click/woshou.gif" alt="" /><br />握手</a> </td> <td> <a href="https://www.hackbase.net/home.php?mod=spacecp&ac=click&op=add&clickid=4&idtype=aid&id=230370&hash=c27c2a64735c4c0930d5f4c30043d867&handlekey=clickhandle" id="click_aid_230370_4" onclick="showWindow(this.id, this.href);doane(event);"> <img src="static/image/click/xianhua.gif" alt="" /><br />鲜花</a> </td> <td> <a href="https://www.hackbase.net/home.php?mod=spacecp&ac=click&op=add&clickid=5&idtype=aid&id=230370&hash=c27c2a64735c4c0930d5f4c30043d867&handlekey=clickhandle" id="click_aid_230370_5" onclick="showWindow(this.id, this.href);doane(event);"> <img src="static/image/click/jidan.gif" alt="" /><br />鸡蛋</a> </td> </tr> </table> <script type="text/javascript"> function errorhandle_clickhandle(message, values) { if(values['id']) { showCreditPrompt(); show_click(values['idtype'], values['id'], values['clickid']); } } </script> </div> <div id="diycontentclickbottom" class="area"></div> </div> <div class="o cl ptm pbm"> <a href="https://www.hackbase.net/home.php?mod=spacecp&ac=favorite&type=article&id=230370&handlekey=favoritearticlehk_230370" id="a_favorite" onclick="showWindow(this.id, this.href, 'get', 0);" class="oshr ofav">收藏</a> <a href="https://www.hackbase.net/home.php?mod=spacecp&ac=share&type=article&id=230370&handlekey=sharearticlehk_230370" id="a_share" onclick="showWindow(this.id, this.href, 'get', 0);" class="oshr">分享</a> <a href="misc.php?mod=invite&action=article&id=230370" id="a_invite" onclick="showWindow('invite', this.href, 'get', 0);" class="oshr oivt">邀请</a> </div> <div class="pren pbm cl"> <em>上一篇：<a href="https://www.hackbase.net/techdoc/tech/230369.html">浅析MySQL网络协议(2)--query查询</a></em><em>下一篇：<a href="https://www.hackbase.net/techdoc/tech/230371.html">Swat.io如何在两年内从MySQL迁移到PostgreSQL</a></em></div> </div> <div id="diycontentrelatetop" class="area"></div> <div id="diycontentrelate" class="area"></div> <div id="diycontentcomment" class="area"></div> </div> <div class="sd pph"> <div class="drag"> <div id="diyrighttop" class="area"></div> </div> <div class="bm"> <div class="bm_h cl"> <h2>相关分类</h2> </div> <div class="bm_c"> <ul class="xl xl2 cl"><li><a href="https://www.hackbase.net/techdoc/info/">资讯</a></li> <li><a href="https://www.hackbase.net/techdoc/tech/">技术</a></li> </ul> </div> </div> <div class="drag"> <div id="diy2" class="area"></div> </div> </div> </div> <div class="wp mtn"> <div id="diy3" class="area"></div> </div> <input type="hidden" id="portalview" value="1"></div> <div id="ft" class="footer cl"> <div class="wp"> <div id="flk" class="y"> <p> <a href="https://www.hackbase.net/forum.php?mod=misc&action=showdarkroom" >黑名单</a><span class="pipe">|</span><a href="https://www.hackbase.net/archiver/" >存档</a><span class="pipe">|</span><a href="https://www.hackbase.net/forum.php?showmobile=yes" >手机版</a><span class="pipe">|</span><a href="sitemap.html" target="_blank" >网站地图</a><span class="pipe">|</span><a href="exemption.html" target="_blank" >免责条款</a><span class="pipe">|</span><a href="legal.html" target="_blank" >法律声明</a><span class="pipe">|</span><a href="privacy.html" target="_blank" >隐私保护</a><span class="pipe">|</span><strong><a href="https://www.hackbase.net" target="_blank">HACKBASE</a></strong> </p> <p class="xs0"> GMT+8, 2026-6-5 08:18<span id="debuginfo"> </span> </p> </div> <div id="frt"> <p>Powered by <strong><a href="https://www.discuz.vip/" target="_blank">Discuz!</a></strong> </p> </div></div> </div> <script src="home.php?mod=misc&ac=sendmail&rand=1780618709" type="text/javascript"></script> <div id="scrolltop"> <span hidefocus="true"><a title="返回顶部" onclick="window.scrollTo('0','0')" class="scrolltopa" ><b>返回顶部</b></a></span> </div> <script type="text/javascript">_attachEvent(window, 'scroll', function () { showTopLink(); });checkBlind();</script> </body> </html>