Формула Excel: расчет статистики опроса

У меня есть опрос от 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,

Мне нужно рассчитать процент мужчин, которые заинтересованы в «Беге». Текст всегда будет отображаться в строке точно так же, как «Выполняется», но может отображаться в другом порядке.

Вот что у меня есть до сих пор:

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

Заметьте, я еще не учитывал критерии «мужской/женский». Это выражение в настоящее время возвращает 0.

Любая помощь будет принята с благодарностью.


excel formula statistics survey
person pws5068    schedule 22.06.2010    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Простым способом приблизиться к этому было бы немного разбить ваши вычисления.

В другом столбце используйте следующую формулу:

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

Это даст вам 1 для всех, кто является мужчиной и имеет «Бег», указанный как интерес, и 0 для всех остальных. Скопируйте это на весь лист, а затем легко вычислите процент, например, если столбец был E:

=SUM(E1:E100)/100
person brendan    schedule 22.06.2010

arrow_upward
1
arrow_downward

Вы также должны проверить, является ли эта пропорция (% мужчин, которые любят бег) случайностью или нет.

Вы можете выполнить тест хи-квадрат (Пирсона или отношения правдоподобия), чтобы увидеть, отличаются ли пропорции от ожидаемых.

Вы также можете выполнить точный тест Фишера, чтобы увидеть, например, различаются ли пропорции между полами.

person Neil McGuigan    schedule 23.06.2010

arrow_upward
0
arrow_downward

Вы можете многое сделать с помощью 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
person Fionnuala    schedule 22.06.2010
comment
это кажется немного сложнее, чем принятый ответ :) - person Neil McGuigan; 23.06.2010
comment
Он не полагается на дополнительные столбцы и вычисляет результат для мужчин и женщин для каждого введенного интереса, при этом пользователю не нужно выбирать один. Это сложнее, потому что выходит за пределы вопроса на шаг. - person Fionnuala; 23.06.2010
comment
Оба ответа полезны, но в этом конкретном случае я не возражаю против дополнительных столбцов, поэтому для простоты я выбрал el Chief. Спасибо, парни! - person pws5068; 23.06.2010