本帖最后由 ys19840718 于 2015-4-20 11:38 编辑
SQL不会,先上无脑公式- =LOOKUP(ROW(1:35),SUBTOTAL(9,OFFSET(C1,,,ROW(1:6)))+1,A2:A7)*{1,1,0}+(ROW(1:35)-LOOKUP(ROW(1:35),SUBTOTAL(9,OFFSET(C1,,,ROW(1:6)))+1,SUBTOTAL(9,OFFSET(C1,,,ROW(1:6)))+1))*{0,1,0}+LOOKUP(ROW(1:35),SUBTOTAL(9,OFFSET(C1,,,ROW(1:6)))+1,B2:B7)/LOOKUP(ROW(1:35),SUBTOTAL(9,OFFSET($C$1,,,ROW(1:6)))+1,C2:C7)*{0,0,1}
复制代码 VBA- Sub test()
- Dim arr
- Dim dic As Object
- Dim i As Long, j As Long
- Dim t As Double
- t = Timer
- Set dic = CreateObject("Scripting.Dictionary")
- With Worksheets("题目")
- arr = .Range("a1").CurrentRegion
- For i = 2 To UBound(arr)
- For j = 1 To arr(i, 3)
- dic(arr(i, 1) & "|" & arr(i, 1) + j - 1 & "|" & arr(i, 2) / arr(i, 3)) = ""
- Next
- Next
- Erase arr
- arr = dic.keys
- .Range(.Cells(2, 6), .Cells(.Cells(.Rows.Count, 8).End(xlUp).Row, 8)).ClearContents
- Application.ScreenUpdating = False
- For i = 0 To UBound(arr)
- .Range("F" & i + 2).Resize(, 3) = VBA.Split(arr(i), "|")
- Next
- Application.ScreenUpdating = True
- End With
- Debug.Print Timer - t
- End Sub
复制代码 |