我今天要解决的一个具体问题是在 Excel 表中匹配借方和贷方金额,其中我们没有共同的标准或查找值。我们拥有的只是数字。
我尝试了很多方法,但都失败了。如果我有 a10
和 a-5,-3,-2
作为信用,那么我必须手动匹配它。有没有办法让变为零的单元格自动突出显示?有没有更好的方法来做到这一点?
一种方法是使用 Solver
• Put your data in A1:A12
• In B13, put a formula =SUMPRODUCT(A1:A12,B1:B12)
• Set up solver so that B1:12 must be binary (ie 1 or 0)
• In B14 put a "target" score, 100 in your example
• in B15 put =ABS(B13-B14)
• Set solver to look for the minimum value in B15 (to either give you an exact solution with no difference, or closest solution with smallest possible difference)
In this case the simplest solution is setting 100 to "on" (ie 1), all other values es "off" (0)
Screenshot for xl2003 for solving for 367 below (as this is more complex than 100)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句