Использование логических функций

[Клубничка]

Зачем мы от номера строки отнимаем 1??

[IgorGO]

Обьясню кусочками (хотя, viter.alex практически сделал это).
Итак формула:
{=ЕСЛИ(СТРОКА()-R45C1>R45C[1];"";СМЕЩ(R1C1;НАИМЕНЬШИЙ(ЕСЛИ((СМЕЩ (R1C1;4;16;R44C 1)=13);СТРОКА(СМЕЩ(R1C1;4;16;R44C1) )-1;99);СТРОКА()-R45C1);1))}
Эта формула формирует список круглых отличников (отличник в классе один (отличница), поэтому список получился небольшой).

итак, приступим

СТРОКА()-R45C1дает порядковый номер (ПН) отличника. От номера текущей строки отнимается значение ячейки R45C1 всегда равное 45. Таким образом в 46 строку будет вписан 1 отличник, в 47 - второй, и т.д...

ЕСЛИ(СТРОКА()-R45C1>R45C[1];"";...ЕСЛИ проверяет не больше ли ПН общего числа отличников, хранящегося в ячейке R45C[1]. Для всех строк начиная с 47 ЕСЛИ выберет пусто "", что будет в 46 строке - разберем дальше

перескакиваем немного внутрь:
СМЕЩ(R1C1;4;16;R44C 1) - эта фрагмент возвращает диапазон, в котором находятся результаты подсчетов количества пятерок для каждого ученика. Диапазон количества пятерок (ДК5) постоянно равен R5C17:R31C17.

следующая фрагмент:
ЕСЛИ((СМЕЩ(R1C1;4;16;R44C 1)=13);СТРОКА(СМЕЩ(R1C1;4;16;R44C1) )-1;99) с учетом введенного ранее сокращения может быть записана так
ЕСЛИ((ДК5=13);СТРОКА(ДК5)-1;99)проверяет равна ли каждая ячейку диапазона ДК5 тринадцати. если не равна - будет возвращено число 99, если равно - номер строки, в которой это случилось минус 1. Результатом работы этого ЕСЛИ будет массив {99,99,99,99,99,99,99,99,99,13,99,. .. и еще 16 раз 99} назовем его М.

следующий фрагмент
НАИМЕНЬШИЙ(ЕСЛИ((СМЕЩ(R1C1;4;16;R44 C 1)=13);СТРОКА(СМЕЩ(R1C1;4;16;R44C1) )-1;99);СТРОКА()-R45C1) с учетом сокращений -
НАИМЕНЬШИЙ(М; ПН)
тут вроде и обьяснять нечего - выбирает из массива М соответсвенный наименьший.

и последнее
СМЕЩ(R1C1;НАИМЕНЬШИЙ(ЕСЛИ((СМЕЩ(R1C 1;4;16;R44C 1)=13);СТРОКА(СМЕЩ(R1C1;4;16;R44C1) )-1;99);СТРОКА()-R45C1);1) переписываем как
СМЕЩ(R1C1;НАИМЕНЬШИЙ(М;ПН);1)смещаемся от ячейки R1C1 на 13 строк вниз, и одну вправо. отсчитайте и посмотрите кого вы там найдете, конечно Линник Елену.

Снова спасибо всем, кто дочитал до сюда.
Отдельная благодарность Клубничке за интерес к экселю.
И лично Лене Линник - не училась бы она на 5, трудно было бы все обьяснять без конкретного примера. Успехов ей в учебе и огромного счастья в личной жизни!

[viter.alex]

Сообщение от IgorGO

…
НАИМЕНЬШИЙ(М; ПН)
тут вроде и обьяснять нечего - выбирает из массива М соответсвенный наименьший…

Как раз наоборот. Зачитал справку до дыр, но так и не понял какой элемент эта функция возвращает. Понял, что если вторым аргументом поставить 1, то получим минимальный, а остальное никак.

[IgorGO]

НАИМЕНЬШИЙ({10;12;3;5;7;21}; 3) - посортирует элементы по порядку и вернет 3-й в списке, т.е. 7.

[Клубничка]

Пойду переваривать

[Клубничка]

ДА! ДА! ДА! Я это сделала! Перерешала кучу задач, с разными данными, диапазонами и условиями. И у меня все получилось! IGORGO, Вы ГЕНИЙ! Моему счастью нет предела!
А теперь господа, перехожу к макросам

[Клубничка]

EducatedFool, Вы советовали не смотреть макрос, но мне очень интересно как он работает.
Если не трудно, просвятите

[viter.alex]

Сообщение от Клубничка

EducatedFool, Вы советовали не смотреть макрос, но мне очень интересно как он работает.
Если не трудно, просвятите

Макрос не сложный.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Me.[c5:o31]) Is Nothing Then
        res5 = 0: res4 = 0: res3 = 0: res2 = 0 'переменные для хранения количества соотв. оценок
        f5 = "": f4 = "": f3 = "": f2 = "" 'переменные для имен учеников, имеющих соотв. оценки

        Dim cell As Range: Set cell = Me.[b5] 'записываем первого ученика в переменную cell
        While cell.Value <> "" 'пока  в переменной не пуст
            sum5 = 0: sum4 = 0: sum3 = 0: sum2 = 0: sum1 = 0:

            КоличествоПредметов = 13 'задаем количество предметов
            arr = cell.Next.Resize(, КоличествоПредметов).Value 'записываем в переменную arr все оценки, относящиеся к данному ученику
            For i = LBound(arr, 2) To UBound(arr, 2) 'перебираем предметы
                Select Case Val(Trim$(arr(1, i))) 'смотрим оценку по каждому предмету и считаем ее
                Case 5: sum5 = sum5 + 1 'если «пятерка»
                Case 4: sum4 = sum4 + 1 'если «четверка»
                Case 3: sum3 = sum3 + 1 'если «тройка»
                Case 2: sum2 = sum2 + 1 'если «двойка»
                Case 1: sum1 = sum1 + 1 'если «единица»
                'если что-то другое
                Case Else: MsgBox "Ошибка в оценке ученика " & cell.Value & " по предмету " & cell.Offset(, i).EntireColumn.Cells(4), vbCritical
                End Select
            Next i
            Debug.Print cell.Value, sum5, sum4, sum3, sum2, sum1 'отладочная иннформация. Можно убрать
            cv = cell.Value 'переменной cv передаем фамилию ученика
            Select Case True 'определяем количество учащихся, которые окончили с теми или иными оценками, и записываем их имена в переменные на букву f
            Case sum5 = КоличествоПредметов 'все «пятерки»
                res5 = res5 + 1: f5 = f5 & Фамилия(CStr(cv)) & ", "
            Case sum5 = КоличествоПредметов - 1 And sum4 = 1 'все «пятерки», одна «четверка»
                res4 = res4 + 1: f4 = f4 & Фамилия(CStr(cv)) & ", "
            Case sum5 + sum4 = КоличествоПредметов - 1 And sum3 = 1 'одна «тройка»
                res3 = res3 + 1: f3 = f3 & Фамилия(CStr(cv)) & ", "
            Case sum2 > 0 Or sum1 > 0 'если есть «единицы» или «двойки»
                res2 = res2 + 1: f2 = f2 & Фамилия(CStr(cv)) & ", "
            End Select
            Set cell = cell.Offset(1) 'переходим к следующей ячейке, которая содержит фамилию 
        Wend 'и все заново
'этот блок нужен для удаления запятой и пробела в конце переменных f2-f5
'от него можно избавиться, если добавлять пробел и запятую перед добавлением фамилии, в функцию Фамилия
        If Right(f5, 2) = ", " Then f5 = Left(f5, Len(f5) - 2)
        If Right(f4, 2) = ", " Then f4 = Left(f4, Len(f4) - 2)
        If Right(f3, 2) = ", " Then f3 = Left(f3, Len(f3) - 2)
        If Right(f2, 2) = ", " Then f2 = Left(f2, Len(f2) - 2)
        'записываем количество оценок
        cell.Offset(8, 7) = res5: cell.Offset(9, 7) = res4: cell.Offset(10, 7) = res3: cell.Offset(11, 7) = res2
        'записываем фамилии
        cell.Offset(8, 13) = f5: cell.Offset(9, 13) = f4: cell.Offset(10, 13) = f3: cell.Offset(11, 13) = f2
    End If
End Sub
'функция добавляет фамилию к списку других фамилий.
Function Фамилия(ByVal txt) As String
    Фамилия = Left$(txt & "  ", InStr(1, txt, " ") + 1) & "."
End Function

[EducatedFool]

Ещё более подробные комментарии Вы можете посмотреть во вложенном файле.

Для отображения текста макроса щелкните правой кнопкой на ярлычке листа, и в контекстном меню выберите пункт "Исходный текст"

Весь текст комментария в сообщение вместить не удалось - оказалось, присутствует ограничение на количество введённых символов.

[viter.alex]

Сообщение от EducatedFool

Ещё более подробные комментарии Вы можете посмотреть во вложенном файле.

Для отображения текста макроса щелкните правой кнопкой на ярлычке листа, и в контекстном меню выберите пункт "Исходный текст"

Весь текст комментария в сообщение вместить не удалось - оказалось, присутствует ограничение на количество введённых символов.

Прошу прощения, что поспешил. Очень ценный макрос для меня в плане приемов работы с ячейками. Респект.