На рисунке 1 приведен пример табеля успеваемости по информатике.

Рис. 1. Табель успеваемости по информатике

    Ключ к заданию

• При вводе номеров и дат удобно пользоваться автозаполнением. Вписав первую дату, протяните курсорную рамку за нижний угол, удерживая нажатой правую кнопку мыши, выберите из контекстного меню вариант Прогрессия. Укажите, что прогрессия нужна арифметическая, с шагом отличным от 1 (выберите самостоятельно).
• Внесите фамилии и, используя сортировку, расположите их по алфавиту, выделив нужные ячейки. Запомните список фамилий, для этого выделите необходимые ячейки, затем в меню Сервис щелкните пункт Параметры, в нем раскрыть закладку Списки и в дополнение к уже существующим спискам добавьте новый. Это позволит применять к фамилиям учеников операцию автозаполнения.
• Введем в таблицу дополнительную строку, где укажем вес каждого занятия (обычное занятие имеет вес, равный 1, контрольное занятие - 2). Расположим эти данные над датами занятий.
• Итоговый результат определяется так: сначала оценки, полученные на разных уроках, перемножаем на коэффициенты этих уроков, и складываем вместе все произведения. Это будет сумма баллов с учетом их веса. Затем подсчитываем количество оценок, на которое надо будет разделить сумму, чтобы выйти на средневзвешенное значение. Для каждого действия – своя функция. Для баллов это СУММПРОИЗВ, в нашем случае с двумя аргументами:
  1. Область оценок ученика;
  2. Область весов каждого занятия (ссылки должны быть абсолютными, т.е. символ "$" надо подставить перед цифрами области). Числа из обеих областей будут браться попарно, перемножаться первое с первым, второе со вторым и т.д. и после окончания умножения – суммироваться.
  Поставить знак деления и ввести вторую функцию. В знаменателе установим функцию СЧЕТ, первым значением в которой будет являться диапазон оценок ученика, а следующими значениями будут ячейки, которые имеют двойной вес.
• Подведите итоги по каждой контрольной: определите количество "5", "4", "3" и "2" (используя функцию СЧЕТЕСЛИ), а также количество не сданных контрольных (функция СЧИТАТЬПУСТОТЫ).
• Введем некоторый дополнительный критерий оценки. Пусть, например, обязательным условием для аттестации за полугодие будет, будет, во-первых, сдача всех контрольных, и, во-вторых, - наличие еще трех обычных оценок. Добавим перед столбцом 1-е полугодие:
  1. Столько столбцов, сколько в полугодии было контрольных с наименованиями Первая контрольная пусто? и т.д. Поместим функцию ЕПУСТО, аргументом которой будет являться адрес ячейки с результатами контрольной работы. Результат будет ЛОЖЬ, если ячейка не пуста, в противном случае результат ИСТИНА.
  2. Распространите формулу на все ячейки таблицы в данном столбце. В данном столбце за таблицей в ячейку поместите функцию И, аргументами которой будет диапазон данного столбца от первой до последней фамилии включительно. Эта формула даст ответ на вопрос А может эту работу класс вообще не выполнял?. Если хотя бы у одного ученика есть оценки за эту контрольную, то ответом будет ЛОЖЬ.
  3. Столбец 1-ое условие выполнено?, поместим функцию И. Эта формула будет иметь столько аргументов, сколько было проведено контрольных. Аргументы будут представлены в виде равенств значений столбца Первая контрольная пусто? и последней строки (которая находится за пределами таблицы, и должна передаваться по абсолютной ссылке) этого же столбца Первая контрольная пусто? и т.д. Смысл формулы таков: Данный ученик идет в ногу со всем классом, т.е. если он не имеет оценки за контрольную, то не потому ли, что ее еще не проводили в классе?. Если ответ ИСТИНА, то первое условие аттестации соблюдено, если ЛОЖЬ, то за учеником долг. Эту формулу копируем вниз на весь список.
  4. Столбец 2-ое условие выполнено?, поместим формулу ЕСЛИ(СЧЕТ(аргументы)>=3;ИСТИНА;ЛОЖЬ). Аргументами функции СЧЕТ будут являться диапазоны оценок за исключением оценок за контрольные работы.
  5. Эти добавленные столбцы надо сделать скрытыми.
• В столбце 1-е полугодие измените формулу следующим образом: ЕСЛИ(И(аргументы);СУММПРОИЗВ(аргументы)/СЧЕТ(аргументы);"н/а"), где аргументами функции И являются столбцы 1-е условие выполнено? и 2-е условие выполнено?, а аргументы функций СУММПРОИЗВ и СЧЕТ остаются без изменений.
• Постройте круговые диаграммы по результатам всех контрольных.
• Выполните аналогичные действия как минимум еще для двух предметов.

    Файл с данным заданием можно взять здесь.