发票自动凑数

1、发票凑数如果你是会计，是否曾遇到过需要在一大堆发票中凑出某一金额的发票数值来，通过人脑去凑数字真的是一件痛苦的事情，而且计算过程往往要耗费很长的时间，工作效率低下，这里提供一个快速凑发票金额的方法：1、 打开Excel表格，按下图所示填写内容，其中B列为现实中你所拥有的实际发票单张面额，需求值为你需要凑出的金额，这里用1000示例，误差为你允许的金额误差，这里用误差为零，即我们需要凑出1000元整的金额。2、 点击开发工具，点击Visual Basic按钮，打开VBA编辑窗口，3、 右键点击左侧工程栏点击插入，模块，新建模块1 4、 点击模块1，将以下代码复制粘贴进右侧代码编辑栏Sub MP(

2、) Sheet1.Range(E2) = Dim csh As Double Dim brr() As Double Dim SS As Integer Dim MM As Integer Dim HH As Integer SS = Second(Time) MM = Minute(Time) HH = Hour(Time) Dim diff As Double diff = Sheet1.Cells(2, 4) Dim zoci As Double Dim ci As Integer Dim DeVa As Double Dim Weiba As Integer DeVa = Sheet1

3、.Cells(2, 3) Dim jar As Double Dim arr() As Double Dim tot As Double zoci = 0 ci = 0 jar = 0 Weiba = Sheet1.Cells(3000, 2).End(xlUp).Row ReDim arr(2 To Weiba) ReDim brr(1 To Weiba) Dim MaVa As Double Dim SeLaVa As Double brr(1) = 0 For i = 2 To Weiba arr(i) = Sheet1.Cells(i, 2) brr(i) = arr(i) Next

4、For i = 2 To Weiba - 1 For p = i + 1 To Weiba If brr(i) brr(p) Then csh = brr(i) brr(i) = brr(p) brr(p) = csh End If Next Next For i = 2 To Weiba Sheet1.Cells(i, 2) = brr(Weiba + 2 - i) Next For i = 1 To Weiba tot = tot + brr(i) Next For i = 2 To Weiba MaVa = MaVa + brr(i) Next SeLaVa = MaVa - brr(2

5、) If (DeVa = brr(2) And DeVa = SeLaVa) Or DeVa = MaVa Then Call SB(DeVa, Weiba, 2, jar, ci, zoci, diff, arr(), brr(), tot) Else MsgBox 金额超限啦！请更改需求值或添加发票！ End If Debug.Print 耗时： & Second(Time) - SS + (Minute(Time) - MM) * 60 + (Hour(Time) - HH) * 3600 & 秒End SubSub SB(DeVa As Double, Weiba As Integer

6、, x As Integer, jar As Double, ci As Integer, zoci As Double, diff As Double, arr() As Double, brr() As Double, tot As Double) Dim caob As Double Static caomm As Integer For i = x To Weiba ci = ci + 1 zoci = zoci + 1 jar = jar + arr(i) Sheet1.Cells(i, 2).Interior.ColorIndex = 42 Debug.Print zoci & 层

7、次= & ci & & i= & i & & 上一个jar= & jar - Sheet1.Cells(i, 2), jar= & jar If jar = DeVa - diff Then Sheet1.Cells(2, 5) = jar Exit Sub End If If jar DeVa + diff Then Call SB(DeVa, Weiba, i + 1, jar, ci, zoci, diff, arr(), brr(), tot) If jar = DeVa - diff Then Sheet1.Cells(2, 5) = jar Exit For End If Shee

8、t1.Cells(i, 2).Interior.ColorIndex = -4142 jar = jar - arr(i) ci = ci - 1 DoEvents Next If jar = 0 Then MsgBox 现有发票无法凑出所需金额，请增加发票数或增加误差值!End Sub5、 点击开发工具，点击插入，点击下拉栏中的按钮标签，在表格中点击插入按钮，并命名为科学计算，如下图所示。6、 右键单击科学计算按钮，点击指定宏。7、 在弹出的指定宏框中选中MP，并点击确定。8、 保存表格，命名为发票凑数，保存类型选择下拉栏里的Excel启用宏的工作薄(*.xlsm)。9、 再次打开发票凑数表格，点击启用内容10、 点击科学计算按钮，蓝色填充部分为参加进凑数计算得发票，按照颜色把实际发票挑出即为你所需要的凑数发票。建议：因为科学计算随发票张数的增多，计算量呈指数增加，如果你的电脑配置比较低，请保证参加的发票数量尽量不要多余50张，如果发