讨论一个有趣的统计问题

lovelfg

为了说清楚，我分步说：
1.有一百个人参加一个宴会，这一百个人都有唯一的编码，分别是从100001到直到100100；
2.宴会现场有三张桌子，这三张桌子都有一个特殊的仪器，可以记录坐到这桌子的人的唯一编码；
3.这一百个人身份并不相同，有一类宾客，二类宾客，三类宾客三种，但每个人只有一种身份，不存在跨身份类别的情况；
4.每个人可以随时随地的换一张桌子吃饭，每个人只要坐到桌子边上，编码就会被桌子上的仪器记录下来，而且如果一个人来到这桌子两次，那么就记录两次编码，但在统计的时候，一个人无论来了多少次，都只能算一个人

现在问题来了，如何统计以下这几项：
1.一号、二号、三号桌分别来了多少一类宾客，二类宾客，三类宾客？
2.只参加一号桌和二号桌、只参加一号桌三号桌，只参加二号桌三号桌以及一二三号桌都参加的三类宾客分别又是多少人？

更详细的情况请参见附件

求教如何用VBA解决这个问题？

zopey

vba字典 去掉重复的编码，sql  分类统计。

sheeboard

python写的，先去重复drop_duplicates,然后合并merge，再分组统计.

1. one=df2['一号桌']   
   
2. two=df2['二号桌']
   
3. three=df2['三号桌']
   
4. 
   
5. one=one.dropna().drop_duplicates()
   
6. two=two.dropna().drop_duplicates()
   
7. three=three.dropna().drop_duplicates()
   
8. 
   
9. df1.coulumns=['id','type']
   
10. one=one.to_frame(name='id')
    
11. two=two.to_frame(name='id')
    
12. three=three.to_frame(name='id')
    
13. 
    
14. one_table=pd.merge(one,df1,on='id')
    
15. two_table=pd.merge(two,df1,on='id')
    
16. three_table=pd.merge(three,df1,on='id')
    
17. 
    
18. one_table.groupby('type').count()
    
19. two_table.groupby('type').count()
    
20. three_table.groupby('type').count()
    
21. 结果分别为
    
22. type   
    
23. 一类宾客   6
    
24. 三类宾客   9
    
25. 二类宾客   4
    
26. 
    
27.       id
    
28. type   
    
29. 一类宾客   7
    
30. 三类宾客  17
    
31. 二类宾客   5
    
32. 
    
33. type   
    
34. 一类宾客   8
    
35. 三类宾客  23
    
36. 二类宾客  10
    
37. 
    
38. pd.merge(one_table,two_table,on='id').groupby('type').count()
    
39. ......
    
40. 
    
41. 
    

复制代码

魂断蓝桥

sql 用 UNION 去重连接，再分组就可以了。

返回结果

一类宾客	一号桌	6
三类宾客	一号桌	9
二类宾客	一号桌	4
一类宾客	三号桌	8
三类宾客	三号桌	23
二类宾客	三号桌	10
一类宾客	二号桌	7
三类宾客	二号桌	17
二类宾客	二号桌	5

代码

Sub 查询()
Dim cnn As Object
Dim Sql As String
Set cnn = CreateObject("ADODB.CONNECTION")
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
Sql = "select A.类型,一号桌 AS 编号," & """一号桌"" AS 桌号" & " from [Sheet1$a1:b101] A,[Sheet1$g1:g28]  B  where A.总编号=B.一号桌" _
            & " UNION  " _
            & "select A.类型,二号桌," & """二号桌""" & " from [Sheet1$a1:b101] A,[Sheet1$i1:i48]  B  where A.总编号=B.二号桌" _
            & " UNION  " _
            & "select A.类型,三号桌," & """三号桌""" & " from [Sheet1$a1:b101] A,[Sheet1$K1:K56]  B  where A.总编号=B.三号桌"
Sheet2.Activate
Cells.ClearContents
Sql = "SELECT 类型,桌号,COUNT(*) FROM (" & Sql & ") GROUP BY 类型,桌号 order by 2,1"
[A2].CopyFromRecordset cnn.Execute(Sql)
cnn.Close: Set cnn = Nothing
End Sub


你说的 参加一号桌和二号桌
可以用
Sql = "SELECT * FROM (" & Sql & ") WHERE 桌号='一号桌'OR 桌号='二号桌'"
[e2].CopyFromRecordset cnn.Execute(Sql)

一把小刀闯天下

'估计差不多

Option Explicit

Sub test()
  Dim arr, i, j, k, t, key, n, mark
  ReDim dic(1 To 4)
  For i = 1 To UBound(dic)
    Set dic(i) = CreateObject("scripting.dictionary")
  Next
  arr = ActiveSheet.UsedRange
  For i = 2 To UBound(arr, 1)
    If Len(arr(i, 1)) = 0 Then Exit For
    dic(4)(arr(i, 1)) = arr(i, 2)
  Next
  For i = 1 To 3
    For j = 2 To UBound(arr, 1)
      If Len(arr(j, 2 * i + 5)) = 0 Then Exit For
      If Not dic(i).exists(arr(j, 2 * i + 5)) Then
        dic(i)(arr(j, 2 * i + 5)) = dic(4)(arr(j, 2 * i + 5))
      End If
  Next j, i
  For i = 1 To 3
    ReDim cnt(1 To 3)
    For Each key In dic(i).keys
      If dic(i)(key) = "一类宾客" Then
        cnt(1) = cnt(1) + 1
      ElseIf dic(i)(key) = "二类宾客" Then
        cnt(2) = cnt(2) + 1
      Else
        cnt(3) = cnt(3) + 1
      End If
    Next
    Debug.Print i & "号桌3类客人分别为：" & Join(cnt, ",")
  Next
  Debug.Print String(50, "-")
  ReDim arr(1 To dic(4).Count + 1, 1 To 2) As String
  For Each key In dic(4).keys
    mark = vbNullString: n = n + 1
    For i = 1 To 3
      mark = mark & IIf(dic(i).exists(key), "1", "0")
    Next
    arr(n, 1) = mark: arr(n, 2) = dic(4)(key)
  Next
  For i = 1 To UBound(arr, 1) - 2
    For j = i + 1 To UBound(arr, 1) - 1
      If arr(i, 1) > arr(j, 1) Then
        t = arr(i, 1): arr(i, 1) = arr(j, 1): arr(j, 1) = t
        t = arr(i, 2): arr(i, 2) = arr(j, 2): arr(j, 2) = t
      End If
  Next j, i
  mark = Split("110 一二号 101 一三号 011 二三号 111 一二三号")
  For i = 0 To UBound(mark) Step 2
    ReDim cnt(1 To 3)
    For j = 1 To UBound(arr, 1)
      If arr(j, 1) = mark(i) Then
        For k = j To UBound(arr, 1)
          If arr(k, 1) <> mark(i) Then
            Debug.Print mark(i + 1) & "桌3类客人分别为：" & Join(cnt, ",")
            j = UBound(arr, 1): Exit For
          End If
          If arr(k, 2) = "一类宾客" Then
            cnt(1) = cnt(1) + 1
          ElseIf arr(k, 2) = "二类宾客" Then
            cnt(2) = cnt(2) + 1
          Else
            cnt(3) = cnt(3) + 1
          End If
        Next
      End If
  Next j, i
End Sub

一把小刀闯天下

代码审核中，估计差不多

1号桌3类客人分别为：6,4,9
2号桌3类客人分别为：7,5,17
3号桌3类客人分别为：8,10,23
---------------------------
一二号桌3类客人分别为：1,,1
一三号桌3类客人分别为：2,1,2
二三号桌3类客人分别为：2,2,4
一二三号桌3类客人分别为：1,1,

lovelfg

一把小刀闯天下 发表于 2018-8-9 11:01
'估计差不多

Option Explicit

太厉害了！

香川群子

问一个问题：
桌号组合，如是3个桌子就有 7种组合(2^n-1)

那么排除重复的类型统计，该向下覆盖统计吗？

比如，编号001 去了 1号桌、3号桌，类型1
那么，按桌子组合统计时，
是1号桌、3号桌、1&3号桌 各算一次类型1统计进去，实际被统计为3次（向下覆盖）
还是，仅仅按照1&3号桌统计1次？（向下不覆盖）

这里面的做法，很不一样，结果也差异很大的。

请楼主确认一下。

一把小刀闯天下

'应该有7+1种组合，因为有都不参加的。所有组合全部输出

Option Explicit

Sub test()
  Dim arr, i, j, k, kk, t, key, n, mark
  ReDim dic(1 To 5)
  For i = 1 To UBound(dic)
    Set dic(i) = CreateObject("scripting.dictionary")
  Next
  mark = Split("一 二 三")
  For i = 0 To UBound(mark): dic(5)(mark(i) & "类宾客") = i + 1: Next
  arr = ActiveSheet.UsedRange
  For i = 2 To UBound(arr, 1)
    If Len(arr(i, 1)) = 0 Then Exit For
    dic(4)(arr(i, 1)) = arr(i, 2)
  Next
  For i = 1 To 3
    For j = 2 To UBound(arr, 1)
      If Len(arr(j, 2 * i + 5)) = 0 Then Exit For
      If Not dic(i).exists(arr(j, 2 * i + 5)) Then
        dic(i)(arr(j, 2 * i + 5)) = dic(4)(arr(j, 2 * i + 5))
      End If
  Next j, i
  For i = 1 To 3
    ReDim cnt(1 To 3)
    For Each key In dic(i).keys
      cnt(dic(5)(dic(i)(key))) = cnt(dic(5)(dic(i)(key))) + 1
    Next
    Debug.Print i & "号桌3类客人分别为：" & Join(cnt, ",")
  Next
  Debug.Print String(30, "-")
  ReDim arr(1 To dic(4).Count + 1, 1 To 2) As String
  For Each key In dic(4).keys
    mark = vbNullString: n = n + 1
    For i = 1 To 3
      mark = mark & IIf(dic(i).exists(key), "1", "0")
    Next
    arr(n, 1) = mark: arr(n, 2) = dic(4)(key)
  Next
  For i = 1 To UBound(arr, 1) - 2
    For j = i + 1 To UBound(arr, 1) - 1
      If arr(i, 1) > arr(j, 1) Then
        t = arr(i, 1): arr(i, 1) = arr(j, 1): arr(j, 1) = t
        t = arr(i, 2): arr(i, 2) = arr(j, 2): arr(j, 2) = t
      End If
  Next j, i
  mark = Split("000 不参加 100 一号 010 二号 001 三号 110 一二号 101 一三号 011 二三号 111 一二三号")
  For i = 0 To UBound(mark) Step 2
    ReDim cnt(1 To 3)
    For j = 1 To UBound(arr, 1)
      If arr(j, 1) = mark(i) Then
        For k = j To UBound(arr, 1)
          If arr(k, 1) <> mark(i) Then
            For kk = 1 To 3
              If Len(cnt(kk)) = 0 Then cnt(kk) = 0
            Next
            Debug.Print mark(i + 1) & "桌3类客人分别为：" & Join(cnt, ",")
            j = UBound(arr, 1): Exit For
          End If
          cnt(dic(5)(arr(k, 2))) = cnt(dic(5)(arr(k, 2))) + 1
        Next
      End If
  Next j, i
End Sub

一把小刀闯天下

'应该有7+1种组合，因为有都不参加的。代码审核中,,,

1号桌3类客人分别为：6,4,9
2号桌3类客人分别为：7,5,17
3号桌3类客人分别为：8,10,23
------------------------------
不参加桌3类客人分别为：6,8,16
一号桌3类客人分别为：2,2,6
二号桌3类客人分别为：3,2,12
三号桌3类客人分别为：3,6,17
一二号桌3类客人分别为：1,0,1
一三号桌3类客人分别为：2,1,2
二三号桌3类客人分别为：2,2,4
一二三号桌3类客人分别为：1,1,0