发票自动凑数

发票凑数

如果你是会计，是否曾遇到过需要在一大堆发票中凑出某一金额的发票数值来，通过人脑去凑数字真的是一件痛苦的事情，而且计算过程往往要耗费很长的时间，工作效率低下，这里提供一个快速凑发票金额的方法：

1、 打开Excel表格，按下图所示填写内容，其中B列为现实中你所拥有的实际发票单张面额，需求值为你需要凑出的金额，这里用1000示例，误差为你允许的金额误差，这里用误差为零，即我们需要凑出1000元整的金额。

2、 点击开发工具，点击Visual Basic按钮，打开VBA编辑窗口，

3、 右键点击左侧工程栏点击插入，模块，新建模块1

4、 点击模块1，将以下代码复制粘贴进右侧代码编辑栏

Sub MP()

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.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

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)

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 Sub

Sub SB(DeVa As Double, Weiba As Integer, 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 & "层次=" & ci & " " & "i=" & i & " " & "上一个jar=" & jar - Sheet1.Cells(i, 2), "jar=" & jar

If jar <= DeVa + diff And 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 And jar >= DeVa - diff Then

Sheet1.Cells(2, 5) = jar

Exit For

End If

Sheet1.Cells(i, 2).Interior.ColorIndex = -4142

jar = jar - arr(i)

ci = ci - 1

DoEvents

Next

If jar = 0 Then MsgBox "现有发票无法凑出所需金额，请增加发票数或增加误差值!"

End Sub

5、 点击开发工具，点击插入，点击下拉栏中的按钮标签，在表格中点击插入按钮，并命名为科学计算，如下图所示。

6、 右键单击科学计算按钮，点击指定宏。

7、 在弹出的指定宏框中选中MP，并点击确定。

8、 保存表格，命名为发票凑数，保存类型选择下拉栏里的Excel启用宏的工作薄(*.xlsm)。

9、 再次打开发票凑数表格，点击启用内容

10、 点击科学计算按钮，蓝色填充部分为参加进凑数计算得发票，按照颜色把实际发票挑出即为你所需要的凑数发票。

建议：因为科学计算随发票张数的增多，计算量呈指数增加，如果你的电脑配置比较低，请保证参加的发票数量尽量不要多余50张，如果发票数量实在是很多，可以在表格录入单张面额时，两张甚至三张相加当作一张录入到表格里，以减轻计算量。

如果你的Excel表格没有开发工具这一栏，可以按照下图所示添加，点击文件，点击选项，在打开的选项框中，选择自定义功能区，在右侧主选项卡中勾选中开发工具，并点击确定即可。

本文来源：https://www.2haoxitong.net/k/doc/93d08cd4c67da26925c52cc58bd63186bceb92d2.html