這算數(shù)據(jù)分析？！

數(shù)據(jù)1：銷售明細(xì)

銷售明細(xì)

數(shù)據(jù)2：商品組合清單

商品組合清單

要求：在【銷售明細(xì)表】中查詢符合【商品組合清單】的銷售單，即銷售明細(xì)表中有一個（門店&流水號）中包含商品組合清單中任何一種組合的兩種品種以上，則把這個門店的這個流水號的銷售明細(xì)粘貼到組合銷售明細(xì)表中?。。?/h2>

---

思路：

• 1、先統(tǒng)計部門和流水號（分組）有多少個藥品ID，然后去除重復(fù)。
• 2、對1中的數(shù)據(jù)進(jìn)行計數(shù)，提取大于等于2的，即有2種組合的部門和流水號（分組）.
• 3、根據(jù)2中的部門和流水號鏈接明細(xì)數(shù)據(jù)。

SQL代碼：

Sub cdsr()
    Dim cnn As Object, rs As Object, i&, SQL$
    Set cnn = CreateObject("ADODB.Connection")
  cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.FullName
    SQL = "select distinct * from (select a.部門,a.流水號,a.藥品ID from [銷售明細(xì)$] a ,[商品組合清單$] b where a.藥品ID=b.商品ID)"
    SQL = "Select 部門,流水號 from (" & SQL & " ) group by 部門,流水號 having count(藥品ID)>1"
    SQL = "select t1.* from [銷售明細(xì)$] t1,(" & SQL & ") t2 where t1.部門=t2.部門 and t1.流水號=t2.流水號"
    Set rs = cnn.Execute(SQL)
    Sheets("組合銷售的明細(xì)").Range("a2:ac66666").ClearContents
    Sheets("組合銷售的明細(xì)").Range("a2").CopyFromRecordset rs
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

VBA代碼:

Sub cdsr1()
    Dim arr(), brr(), i&, d As Object, d1 As Object
    arr = Sheet1.[a1].CurrentRegion.Value
    brr = Sheet3.[a1].CurrentRegion.Value
    Set d = CreateObject("scripting.dictionary")
    Set d1 = CreateObject("scripting.dictionary")
    For i = 2 To UBound(arr)
        d(arr(i, 2)) = arr(i, 1)
    Next
    For i = 2 To UBound(brr)
        If d.exists(brr(i, 4)) Then
            s = brr(i, 1) & brr(i, 3) & brr(i, 4)
            ss = brr(i, 1) & brr(i, 3)
            If Not d1.exists(s) Then
                d1(s) = ""
                d(ss) = d(ss) + 1
            End If
        End If
    Next

    For i = 2 To UBound(brr)
        ss = brr(i, 1) & brr(i, 3)
        If d(ss) > 1 Then
            k = k + 1
            For j = 1 To UBound(brr, 2)
                brr(k, j) = brr(i, j)
            Next
        End If
    Next
    Sheet2.[a2:ab66666] = ""
    Sheet2.[a2].Resize(k, UBound(brr, 2)) = brr
End Sub

結(jié)果

結(jié)果

示例文件

鏈接: http://pan.baidu.com/s/1qXRae0O 密碼: tv56