LR_informatika / Excel(lab) №1

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Задание 12. Начисление стипендии. В сессию студентами сдавались два экзамена (вводится оценка) и зачета (вводится символ “+”). Неудовлетворительные оценки не выставляются (клетка остается пустой). Вычислить средний балл и стипендию, которая назначается студентам таким образом:

отличники – 3 минимальные зарплаты (МЗ)

сдавшие без троек (хорошисты) – 2 минимальные зарплаты

остальные сдавшие (троечники) – 1 минимальная зарплата

несдавшие – 0

Средний балл (F4) – среднее арифметическое оценок экзаменов, но только если сессия сдана, в противном случае он принимается равным нулю:

средний_балл=ЕСЛИ(сессия_сдана; то (логика+этика)/2; иначе 0) или

средний_балл=ЕСЛИ(логика_сдана И этика_сдана И химия_сдана

И право_сдано; (логика+этика)/2;0) или

средний_балл=ЕСЛИ(логика>2 И этика>2 И химия=”+” И право=”+”; (логика+этика)/2; 0)

Отсюда в виде формулы для первого студента (Петра):

F4 =ЕСЛИ(И(B4>2;С4>2;D4=”+”;E4=”+”); (B4+С4)/2;0).

Средний балл можно вычислить и иначе, исходя от обратного – если не сдана какая-либо из дисциплин, он принимается равным 0, иначе – среднее арифметическое:

средний_балл=ЕСЛИ(сессия_не_сдана; то 0; иначе (логика+этика)/2) или

=ЕСЛИ(логика_не_сдана ИЛИ этика_не_сдана ИЛИ химия_не_сдана ИЛИ право_не_сдано; 0; (логика+этика)/2) или

=ЕСЛИ(логика=0 ИЛИ этика=0 ИЛИ химия=0 ИЛИ право=0; 0; (логика+этика)/2)

F4 =ЕСЛИ(ИЛИ(B4=0;С4=0;D4=0;E4=0); 0; (B4+С4)/2). – пустая клетка считается равной нулю

а можно, используя функцию подсчета занятых клеток СЧЁТЗ, записать и так

F4 =ЕСЛИ(СЧЁТЗ(B4:E4)<4; 0; (B4+С4)/2).

Найдем стипендию:

стипендия=ЕСЛИ(отличник; 3; ЕСЛИ(хорошист; 2; ЕСЛИ(троечник; 1; иначе 0)))•МЗ или

стипендия=ЕСЛИ(ср._балл=5;3;ЕСЛИ(И(логика>3;этика>3;ср._балл>0);2;ЕСЛИ(ср._балл>0;1;иначе 0)))•МЗ

Окончательно для Петра (МЗ вынесем за ЕСЛИ):

G4 =ЕСЛИ(F4=5; 3; ЕСЛИ(И(B4>3;C4>3;F4>0); 2; ЕСЛИ(F4>0;1; 0)))*G$1.

Самостоятельно напишите выражение для стипендии при обратном порядке анализа успеваемости:

стипендия=ЕСЛИ(двоечник; 0; ЕСЛИ(троечник; 1; ЕСЛИ(хорошист; 2; иначе 3)))•МЗ

G4 =ЕСЛИ(. . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

ВСПОМОГАТЕЛЬНЫЕ (ИТОГОВЫЕ) ФУНКЦИИ

=СЧЁТ(область_подсчёта) – посчитывает количество непустых числовых ячеек в области.

=СЧЁТЗ(область_подсчёта) – посчитывает количество всех непустых ячеек в области.

=СЧИТАТЬПУСТОТЫ(область_подсчёта) – посчитывает количество всех пустых ячеек в области.

=СРЗНАЧ(область_подсчёта) – находит среднее арифметическое всех ячеек в области.

=ЦЕЛОЕ(число) – отбрасывает дробную часть числа (например =ЦЕЛОЕ(В4/В2)).

=СЧЁТЕСЛИ(область_подсчёта; критерий_отбора) – посчитывает количество непустых числовых ячеек в области, удовлетворяющих критерию (например =СЧЁТЕСЛИ(C2:C6;”>10”) – в блоке C2:C6 подсчитывается число ячеек, значение которых больше 10. Замечание: если в критерии используется отношение =, знак “=” и кавычки не обязательны.

=СУММЕСЛИ(область_критерия; критерий_отбора; область_суммирования) – суммируется содержимое ячеек из области суммирования, для которых наблюдается истинность критерия отбора в области критерия (например =СУММЕСЛИ(C2:C6;”>10”;D2:D6) – суммируется содержимое клеток из области D2:D6, для которых значения соответствующих клеток из C2:C6 больше 10.

=МАКС(число;число;…) – максимальное значение из чисел/блока чисел.

=МИН(число;число;…) – минимальное значение из чисел/блока.

Подведем общие итоги сдачи сессии студентами (к заданию 12).

Число студентов, сдавших логику и этику: B8=СЧЁТ(B4:B7) и C8=СЧЁТ(C4:C7)

Число студентов, сдавших химию и право: D8=СЧЁТЗ(D4:D7) и E8=СЧЁТЗ(E4:E7)

Число студентов, не сдавших дисциплины: B9=СЧИТАТЬПУСТОТЫ(B4:B7) и аналогично для C9, D9, E9

Число студентов, успешно сдавших сессию: F8=СЧЁТЕСЛИ(F4:F7;">0")

Число студентов, не сдавших сессию: F9=СЧЁТЕСЛИ(F4:F7;"=0")

Средние балл и стипендия всей группы: F10=СРЗНАЧ(F4:F7) и G10=СРЗНАЧ(G4:G7)

Средний балл и средняя стипендия только F11=СУММЕСЛИ(F4:F7;">0")/СЧЁТЕСЛИ(F4:F7;">0")

студентов, успешно сдавших сессию: G11=СУММЕСЛИ(G4:G7;">0")/СЧЁТЕСЛИ(G4:G7;">0")

Общее число отличников и хорошистов: F12=СЧЁТЕСЛИ(G4:G7;G$1*3)+СЧЁТЕСЛИ(G4:G7;G$1*2)