Обработка групп ячеек в строке по значениям из соседней строки

[iyugov]

Есть таблица со списком школьных предметов и итоговыми оценками учеников. Некоторые предметы альтернативные - например, немецкий и французский языки - и они образуют группу: ученик может иметь итоговую оценку только по одному предмету в группе. Таких групп может быть несколько, каждая группа имеет свой номер, группы независимы друг от друга.

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

Пример прилагается.

[JohnSUN]

Ну, если честно, то строка с номерами групп не совсем "соседняя"  
Если "чисто поржать", то впиши в U4 формулу

Код Выделить Развернуть

=T(IF(SUMPRODUCT(COUNTIF(FREQUENCY(TRANSPOSE(IF(ISNUMBER($B4:$T4);$B$3:$T$3;""));ROW(INDIRECT("$A$1:$A$"&MAX($B$3:$T$3))));1))=MAX($B$3:$T$3);STYLE("Good");STYLE("Error")))и растяни на весь список учеников (ширину колонки U можно сделать маленькой, миллиметра 4 - это только индикатор)

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

Код Выделить Развернуть

Function Marks_is_valid(aGroups As Variant, aMarks As Variant) As String
Dim i As Long, aTmp As Variant, sRes1 As String, sRes2 As String 
Marks_is_valid = "*** Ошибка в вызове функции! ***"
If UBound(aGroups,1) <> 1 Then Exit Function
If UBound(aMarks,1) <> 1 Then Exit Function
If UBound(aGroups,2) <> UBound(aMarks,2) Then Exit Function
aTmp = Array()
For i = LBound(aGroups,2) To UBound(aGroups,2)
If not IsEmpty(aGroups(1,i)) Then FindOrAddVal(aGroups(1,i), aTmp, IsMark(aMarks(1,i)))
Next i
sRes1 = ""
sRes2 = ""
For i = LBound(aTmp) To UBound(aTmp)
If aTmp(i)(1) < 1 Then sRes1 = sRes1 & aTmp(i)(0) & " "
If aTmp(i)(1) > 1 Then sRes2 = sRes2 & aTmp(i)(0) & " "
Next i
Marks_is_valid = ""
If sRes1 <> "" Then sRes1 = "В " & sRes1 & " мало оценок "
If sRes2 <> "" Then sRes1 = sRes1 & "В " & sRes2 & " много оценок"
If sRes1 = "" Then
Marks_is_valid = "Всё ОК"
Else
Marks_is_valid = sRes1
EndIf
End Function

Sub FindOrAddVal(key As Variant, aData As Variant, add As Integer)
Dim l&, r&, m&, N&, i&
l=LBound(aData)
r=UBound(aData)+1
N=r
While (l<r)
m=l+Int((r-l)/2)
If aData(m)(0)<key Then
l=m+1
Else
r=m
EndIf
Wend
If r=N Then
ReDim Preserve aData(0 To N)
aData(N) = Array(key, add)
ElseIf  aData(r)(0)=key Then
aData(r)(1) = aData(r)(1) + add
Else
ReDim Preserve aData(0 To N)
For i = N-1 To r Step -1
aData(i+1)=aData(i)
Next i
aData(r) = Array(key, add)
EndIf
End Sub

Function IsMark(vTest As String) As Integer
IsMark = 0
If (Len(Trim(vTest))=1) And (Instr("12345",Trim(vTest))>0) Then IsMark = 1
End Function


Вызывать её в ячейке так:

Код Выделить Развернуть

=MARKS_IS_VALID($B$3:$T$3;$B4:$T4)
Здесь первый параметр это диапазон ячеек с номерами групп, а второй - строка для одного ученика (в данном случае из 4-ой строки).
К слову сказать, с такой функцией ты не ограничен одними только номерами групп - можешь обозначать их как хочешь, хоть буквами, хоть целыми словами

[iyugov]

Да, строка не обязательно соседняя. Трудно было сформулировать тему.

Благодарю. Вроде бы действительно работает как надо. Попробую разобраться в формуле.

По каким соображениям функция-макрос предпочтительна? Только из-за универсальности? Или она ещё и быстрее работать будет?

[JohnSUN]

Да нет, скорость что там, что там одинаково большая, глазом моргнуть не успеешь.
Просто на текст формулы глянь: его не то что понять - за один раз и прочитать-то сложно. А теперь представь, что у тебя ведомость человек на 350 учеников и на 25 предметов. Всё заполнил, формулу вписал и пересчитал - всё вроде бы нормально. И тут вспоминаешь, что нужно было еще два факультатива добавить, три отметки за поведение и ещё какую-то ерунду. И после этого формулу нужно будет изменить - колонок-то теперь другое количество. Как быстро сможешь найти все места, где нужно вносить изменения, и насколько правильно эти изменения с первого раза внесёшь?
Другое дело макрофункция - да, писать её немного дольше, чем конструировать формулу. Но у неё всего-то два параметра! И если нужно что-то изменить в адресах ячеек - то вот они, все перед глазами, ошибиться трудно

[iyugov]

Пока разбираюсь и с формулой, и с макросом.

Вот это:

Код Выделить Развернуть

ROW(INDIRECT("$A$1:$A$"&MAX($B$3:$T$3))))
выдаёт 1 независимо от значения функции MAX. Можно заменить вызов ROW() единицей?

[mikekaganski]

Вот это:

Код Выделить Развернуть

ROW(INDIRECT("$A$1:$A$"&MAX($B$3:$T$3))))
выдаёт 1 независимо от значения функции MAX. Можно заменить вызов ROW() единицей?

... выдаёт в какой строке?

[JohnSUN]

Вот это:

Код Выделить Развернуть

ROW(INDIRECT("$A$1:$A$"&MAX($B$3:$T$3))))
выдаёт 1 независимо от значения функции MAX. Можно заменить вызов ROW() единицей?

Можно, конечно можно! Только после такой замены формула перестанет работать 
Почему? А вот подсказка:

... выдаёт в какой строке?

Действительно - в ПЕРВОЙ строке всегда будет единица. А во второй - двойка, в третьей - тройка. Фокус в том, что вся эта конструкция, о которой ты спрашиваешь, возвращает не число, а массив. Длина этого массива - максимальное число в строке номеров групп. Сейчас у тебя две группы - 1 и 2 - получим массив из двух значений. Было бы, допустим, 1 и 4 - получили бы массив из четырёх последовательных значений {1|2|3|4}
Я постарался показать на картинке, как увидеть эти {1|2} для твоего примера.
Это массив индексов, второй параметр для функции FREQUENCY, которая подсчитает сколько чисел (оценок) попадает в каждую из групп.

Да, похоже, что "чисто поржать" не получилось - не все шутки одинаково смешны...
Ну, ты продолжай ковырять формулу - если разберёшь её до конца, то узнаешь о Calc'е немного нового. Типа, "всё что нас не убивает делает нас сильнее" (мне больше нравится вариант "Всё, что меня не убило, пусть прячется - теперь моя очередь"  )