Как сложить строку, пока не будет выполнено условие

Question

У меня есть набор данных, в котором строки - это результаты теста учащегося, а столбцы - отдельные вопросы теста. Я хотел бы создать формулу, которая суммирует столбцы для учащегося (результат теста; 0 = неверный, 1 = правильный), пока в строке не появятся четыре нуля. Например, если студент набрал:

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20
1  1  1  1  1  1  0  1  1  0   0   0   1   0   0   0   0   1   1   0

Формула должна возвращать 9, потому что она суммируется до Q17, где выполняется условие (четыре неправильных ответа).

Предположим, что задание по первому вопросу для первого ученика - это ячейка B2 (первая строка - заголовки, первая колонка - имя ученика).

Любая помощь будет высоко ценится, потому что это превосходит мои знания Excel.

Answer 1

Используйте эту формулу, которая находит первую строку 4 0 и устанавливает ее как конец диапазона:

=SUM(B2:INDEX(B2:U2,MATCH(1,INDEX((B2:R2=0)*(C2:S2=0)*(D2:T2=0)*(E2:U2=0),),0)))

Answer 2

Рассмотрим следующую пользовательскую функцию:

Public Function SpecialSum(rng As Range) As Long
    Dim r As Range, crit As Long, v As Long
    SpecialSum = 0
    crit = 0
    For Each r In rng
        v = r.Value
        SpecialSum = SpecialSum + v
        If v = 0 Then
            crit = crit + 1
        Else
            crit = 0
        End If
        If crit = 4 Then Exit Function
    Next r
End Function

Он добавляет значения в диапазон, пока не встретит четыре последовательных нуля:

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

=SpecialSum(B2:Z2)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!