不会SQL,写了VBA的哈^:L
SQL不会,憋了个笨办法~~~ 本帖最后由 轩辕轼轲 于 2015-4-22 10:22 编辑
SQL语句支持支持最多48天的
先交了有时间再改吧! =TEXT(INDEX(A:A,SMALL(IF(C$2:C$7>=COLUMN(A:Z),ROW($2:$7)),ROW(A1))),"e-m-d") Sub Lianxi2()
Dim x As Long, y As Long, m As Long, k As Long
Dim max_x As Long
Dim arr, brr()
max_x = ActiveSheet.Range("a65536").End(xlUp).Row
arr = ActiveSheet.Range("a1").Resize(max_x, 3)
For x = 2 To max_x
If arr(x, 3) > 1 Then
For y = 1 To arr(x, 3)
k = k + 1
ReDim Preserve brr(1 To 3, 1 To m + k)
brr(1, m + k) = arr(x, 1)
brr(2, m + k) = brr(1, m + k) + k - 1
brr(3, m + k) = arr(x, 2) / arr(x, 3)
Next
m = m + k
k = 0
Else
m = m + 1
brr(1, m) = arr(x, 1)
brr(2, m) = arr(x, 1)
brr(3, m) = arr(x, 3)
End If
Next
With ActiveSheet.
.Offset(0, 0) = "预定日期"
.Offset(0, 1) = "使用日期"
.Offset(0, 2) = "金额"
.Offset(1).Resize(m, 3) = Application.Transpose(brr)
End With
End Sub 学习
页:
1
[2]