老铁们求用VBA代码查询库存数据

lpflbc

老铁们求用VBA代码查询数据，说明：查询总的入库、销售、库存汇总情况，库存数=入库数-销售数。用VBA 如何实现，谢谢！最重要的一个要求就是查询速度快！快！

3190496160

Sub 统计库存()
Application.ScreenUpdating = False
Dim ar As Variant
Dim d As Object
Set d = CreateObject("scripting.dictionary")
ReDim arr(1 To 90000, 1 To 7)
For Each sh In Worksheets(Array("入库明细", "销售明细"))
    r = sh.Cells(Rows.Count, 1).End(xlUp).Row
    ar = sh.Range("a1:p" & r)
    For i = 2 To UBound(ar)
        If Trim(ar(i, 7)) <> "" Then
            zd = Trim(ar(i, 7)) & "|" & Trim(ar(i, 8)) & "|" & Trim(ar(i, 9)) & "|" & Trim(ar(i, 10))
            t = d(zd)
            If t = "" Then
                k = k + 1
                d(zd) = k
                t = k
                For j = 7 To 10
                    arr(k, j - 6) = ar(i, j)
                Next j
            End If
            If sh.Name = "入库明细" Then
                arr(t, 5) = arr(t, 5) + ar(i, 11)
            Else
                arr(t, 6) = arr(t, 6) + ar(i, 11)
            End If
            arr(t, 7) = arr(t, 5) - arr(t, 6)
        End If
    Next i
Next sh
With Sheets("查询2")
    .UsedRange.Offset(4) = Empty
    .Range("a5").Resize(k, 7) = arr
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub

3190496160

库存查询统计.rar (76.16 KB, 下载次数: 26)

2022-12-8 17:36 上传

点击文件名下载附件

shiruiqiang

Sub text()
Dim arr, brr, crr
Dim x, i
Dim dic, key
Set dic = CreateObject("scripting.dictionary")
arr = Sheets(1).UsedRange
ReDim crr(1 To UBound(arr), 1 To 7)
For x = 2 To UBound(arr)
key = arr(x, 7) & "|" & arr(x, 8)
If Not dic.exists(key) Then
dic(key) = dic.Count + 1
crr(dic(key), 1) = arr(x, 7)
crr(dic(key), 2) = arr(x, 8)
crr(dic(key), 3) = arr(x, 9)
crr(dic(key), 4) = arr(x, 10)
crr(dic(key), 5) = arr(x, 11)
Else
crr(dic(key), 5) = crr(dic(key), 5) + arr(x, 11)
End If
Next

brr = Sheets(2).UsedRange
For i = 2 To UBound(brr)
key = brr(i, 7) & "|" & brr(i, 8)
crr(dic(key), 6) = crr(dic(key), 6) + brr(i, 11)
crr(dic(key), 7) = crr(dic(key), 5) - crr(dic(key), 6)
Next
Sheets(3).[a5].Resize(dic.Count, 7) = crr
End Sub

qdtzq

凑个热闹  .............

lpflbc

3190496160 发表于 2022-12-8 17:36

感谢老铁分享!

风中的我脸

1. SELECT DISTINCT t3.书号,t4.书名,t4.版别,t4.单价,t3.入库数,t3.销售数,t3.库存数
   
2. FROM
   
3. (SELECT t1.书号,t1.入库数,t2.销售数,t1.入库数-t2.销售数 as 库存数 FROM
   
4. (SELECT 书号,sum(数量) as 入库数 FROM [入库明细$] GROUP BY  书号)t1
   
5. LEFT JOIN
   
6. (SELECT 书号,sum(数量) as 销售数 FROM [销售明细$] GROUP BY  书号)t2
   
7. on t1.书号 = t2.书号)t3
   
8. INNER JOIN [入库明细$]t4
   
9. on t3.书号=t4.书号

复制代码

lpflbc

qdtzq 发表于 2022-12-8 18:18
凑个热闹  .............

感谢老铁分享!