excel如何求固定值

今日遇到的问题:“excel如何求固定值”

一列数字 ,十几个,如果用EXCEL的功能选取 其中的数值总和等于指定的数



“excel如何求固定值” 解决方法:



Sub sumNum()

Dim endR%, r$

Dim shuzu() As String, x@, y@, A&

endR = Application.WorksheetFunction.Count(Range("A:A"))

r = "a1:a" & endR

'A列降序排列

With ActiveSheet.Sort

.SortFields.Clear

.SortFields.Add Key:=Range("A1"), Order:=xlDescending

.SetRange Range(r)

.Apply

 End With

y = [B2]

If y > Application.WorksheetFunction.Sum(Range(r)) Then End

Columns("C:D").ClearContents

ReDim shuzu(endR)

'寻找和=y的数据

For i = 1 To endR

r = "a" & i & ":a" & endR

If y > Application.WorksheetFunction.Sum(Range(r)) Then Exit For

ReDim shuzu(endR)

x = 0: A = 0

For j = i To endR

50 x = x + Cells(j, 1)

If x > y Then

x = x - Cells(j, 1)

Else

shuzu(A) = Cells(j, 1): A = A + 1: j2 = j

End If

If x = y Then GoTo 100

Next j

If j2 >= endR - 1 Then GoTo 80

A = A - 1: shuzu(A) = ""

If j2 < endR - 1 Then shuzu(A) = "" Else GoTo 80

x = x - Cells(j2, 1): j = j2 + 1: k = k + 1

If k + j2 > endR Then GoTo 80

GoTo 50

80 If Abs(x - y) < 100 Then Exit For

Next i

'输入数据、设置单元格格式

100 [C1] = "找到数之和"

[C1].Interior.Color = 5296274: [C1].HorizontalAlignment = xlCenter

[C2] = x: [C2].NumberFormatLocal = "G/通用格式"

[C4] = "误差"

[C4].Interior.Color = 5296274: [C4].HorizontalAlignment = xlCenter

[C5] = x - y

[C5].NumberFormatLocal = "G/通用格式"

[D1] = "找到以下" & A & "个数"

[D1].HorizontalAlignment = xlCenter

[D1].Interior.Color = 5296274

[D2].Resize(A) = Application.Transpose(shuzu)

End Sub


相关文章