Excel 公式：计算调查统计数据

Question

我对 100 位用户进行了调查，并尝试计算一些统计数据。我的调查中的相关字段如下所示：

    Gender           Interests
B1: Male         D1: Running, Snowboarding, Mountain Bikes
B2: Male         D2: Programming, Running, Paintball
B3: Female       D3: Bowling, Gymnastics
B4: Male         D4: Rock Climbing, Running,

我需要计算对“跑步”感兴趣的男性的百分比。文本将始终以“Running”的形式出现在字符串中，但可能以不同的顺序出现。

到目前为止，我得到的是：

=SUM(
     COUNTIF(
             D1:D100,ISNUMBER(
                               SEARCH(D1:D100,"Running")
            )
      )
 )

请注意，我还没有考虑男性/女性标准。该表达式当前返回 0。

任何帮助将不胜感激。

Answer 1

解决这个问题的一个简单方法是稍微分解一下你的计算。

在另一列中，使用如下公式：

=IF(AND(B1="Male",ISNUMBER(SEARCH("Running",D1))),1,0)

对于所有将“跑步”列为兴趣的男性，这将为您提供 1，而对于所有其他人，则为 0。将其一直复制到工作表中，然后很容易计算百分比，例如，如果列是 E：

=SUM(E1:E100)/100

Answer 2

您还应该测试一下这个比例（喜欢跑步的男性的百分比）是否是侥幸。

您可以进行（皮尔逊或似然比）卡方检验来查看比例是否与预期不同。

例如，您还可以进行费舍尔精确检验来查看性别之间的比例是否不同。

Answer 3

您可以使用 Excel 和 ADO 做很多事情。

Dim cn As Object
Dim rs As Object
Dim sFile As String
Dim sCn As String
Dim sSQL As String
Dim s As String, f As String
Dim sa As Variant
Dim i As Integer, c As Integer
Dim r As Range

''This is not the best way to refer to the workbook
''you want, but it is very conveient for notes
''It is probably best to use the name of the workbook.

sFile = ActiveWorkbook.FullName

sCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open sCn

sSQL = "SELECT interests " _
       & "FROM [Sheet1$] "

rs.Open sSQL, cn, 3, 3

With Worksheets("Sheet2")
    s = rs.GetString(, , , ",")
    sa = Split(s, ",")

    c = 1
    For i = 0 To UBound(sa)
        Set r = .Range("a1:a" & c)
        f = Trim(sa(i))
        If r.Find(f) Is Nothing Then
            c = c + 1
            .Cells(c, 1) = f
        End If
    Next

    .Cells(1, 1) = "Interests"
    .Cells(1, 2) = "Male"
    .Cells(1, 3) = "Female"

    For i = 2 To c
        rs.Close

        sSQL = "SELECT Gender, Count(Gender) As GNo " _
             & "FROM [Sheet1$] " _
             & "WHERE Interests Like '%" & .Cells(i, 1) & "%' " _
             & "GROUP BY Gender"

         rs.Open sSQL, cn

         Do While Not rs.EOF
            If rs.Fields("Gender") = "Male" Then
                .Cells(i, 2) = rs.Fields("GNo")
            ElseIf rs.Fields("Gender") = "Female" Then
                .Cells(i, 3) = rs.Fields("GNo")
            End If
            rs.MoveNext
        Loop

        Next

End With

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub