EXCEL的函数使用

Exce表格网 2023-02-02 22:17 编辑:admin 279阅读

这个是很难解决的,既然求助我,我给你个方法,供你参考:
假设有6个数字(越多越复杂),把他们放在A1到F1里,同时按Alt和F11进入宏界面,点菜单的插入,模块,粘贴如下代码:

Sub xx()
Dim i, j, k, l, m, n As Byte
Dim xRow As Long
xRow = 2
Application.ScreenUpdating = False
For i = 1 To 6
Cells(xRow, 1) = Cells(1, i)
Cells(xRow, 2) = Cells(1, i)
xRow = xRow + 1
Next i


For i = 1 To 5
For j = i + 1 To 6
Cells(xRow, 1) = Cells(1, i) + Cells(1, j)
Cells(xRow, 2) = Cells(1, i) & , & Cells(1, j)
xRow = xRow + 1
Next
Next


For i = 1 To 4
For j = i + 1 To 5
For k = j + 1 To 6
Cells(xRow, 1) = Cells(1, i) + Cells(1, j) + Cells(1, k)
Cells(xRow, 2) = Cells(1, i) & , & Cells(1, j) & , & Cells(1, k)
xRow = xRow + 1
Next
Next
Next


For i = 1 To 3
For j = i + 1 To 4
For k = j + 1 To 5
For l = k + 1 To 6
Cells(xRow, 1) = Cells(1, i) + Cells(1, j) + Cells(1, k) + Cells(1, l)
Cells(xRow, 2) = Cells(1, i) & , & Cells(1, j) & , & Cells(1, k) & , & Cells(1, l)
xRow = xRow + 1
Next
Next
Next
Next


For i = 1 To 2
For j = i + 1 To 3
For k = j + 1 To 4
For l = k + 1 To 5
For m = l + 1 To 6
Cells(xRow, 1) = Cells(1, i) + Cells(1, j) + Cells(1, k) + Cells(1, l) + Cells(1, m)
Cells(xRow, 2) = Cells(1, i) & , & Cells(1, j) & , & Cells(1, k) & , & Cells(1, l) & , & Cells(1, m)
xRow = xRow + 1
Next
Next
Next
Next
Next


Cells(xRow, 1) = Cells(1, 1) + Cells(1, 2) + Cells(1, 3) + Cells(1, 4) + Cells(1, 5) + Cells(1, 6)
Cells(xRow, 2) = Cells(1, 1) & , & Cells(1, 2) & , & Cells(1, 3) & , & Cells(1, 4) & , & Cells(1, 5) & , & Cells(1, 6)


Application.ScreenUpdating = True
End Sub


在此界面直接按F5运行此宏,回到Excel,B列是63种组合(2^6-1),A列是组合的和。
在C2输入合计值,在D2输入
=VLOOKUP(C2,A:B,2,0)
就是结果。希望你能理解。

0
0
相关评论
我要评论
点击我更换图片
283