Необходимо составить расчетную ведомость на выдачу стипендии группе студентов, условно состоящей из 5 человек. Исходными данными являются:
1. Общий стипендиальный фонд, выделенный на группу (рубли);
2. Минимальная стипендия M (рубли),
3. Студенческий коэффициент K, на который умножается минимальная стипендия для вычисления значения стипендии каждого студента;
4. Успеваемость каждого студента, выраженная в виде его среднего рейтинга R за семестр;
5. Количество двоек Д каждого студента за сессию.
Стипендия конкретного студента складывается из стипендии, одинаковой для всех успевающих студентов с рейтингом не ниже 30 (общая), и дополнительной стипендии, размер которой зависит от успеваемости студента и общего стипендиального фонда (добавочная).
Для решения задачи используются следующие формулы:
Стипендия = Общая стипендия + Добавочная стипендия | Ф(1) | ||||||||||||||||
Общая стипендия = | Ф(2) | ||||||||||||||||
Добавочная стипендии = | Баллы, превышающие R=35 | ´ | Добавка за 1 балл | Ф(3) | |||||||||||||
Баллы, превышающие R=35 | = | Ф(4) | |||||||||||||||
Найдем добавку за 1 балл: | |||||||||||||||||
Общий фонд = Сумма общих стипендий | Ф(5) | ||||||||||||||||
Добавочный фонд = Стипендиальный фонд - Общий фонд | Ф(6) | ||||||||||||||||
Добавка за 1 балл | = | Добавочный фонд Сумма баллов, превышающих R=35 | Ф(7) | ||||||||||||||
Создание таблицы начинается с подготовки ее проекта (Рис. 8.).
|
|
Рис. 8. Проект таблицы
Теперь будем заполнять таблицу. Сначала занесем текстовые значения, потом внесем исходные данные (серые ячейки в таблице), которые приведены на рисунке 9.
Рис. 9. Результирующая таблица
Теперь запишем наши формулы, используя в них ссылки на соответствующие ячейки таблицы.
Формула | Ячейка | Вид формулы в Excel |
H7 | =E7+G7 | |
E7 | =если(или(C7<30;D7>0);0;$G$3*$G$2) или =если(и(C7>=30;D7<=0);$G$3*$G$2;0) | |
G7 | =F7*$G$4 | |
F7 | =если(C7<=35;0;C7-35) или =если(C7>35;C7-35;0) | |
E3 | =E12 | |
E4 | =E2-E3 | |
G4 | =E4/F12 |
После этого в ячейках E3 и G4 появится результат – сообщение об ошибке, т.к. в ячейках E12 и F12 пока нет данных. Чтобы получить данные в этих ячейках и записать необходимые формулы, надо сначала скопировать формулы из ячеек E7, F7, G7, H7 в соответствии с нашим проектом (на строки 8, 9, 10 и 11), а затем в ячейках E12, F12, G12, H12 записать формулы суммирования.
Ячейка | Вид формулы в Excel |
E12 | =E7+E8+E9+E10+E11 или =СУММ(E7:E11) |
F12 | =F7+F8+F9+F10+F11 или =СУММ(F7:F11) |
G12 | =G7+G8+G9+G10+G11 или =СУММ(G7:G11) |
H12 | =H7+H8+H9+H10+H11 или =СУММ(H7:H11) |
Теперь заполним нижнюю часть таблицы с подведенными итогами:
Ячейка | Вид формулы в Excel |
E15 | =МАКС(C7:C11) |
E16 | =МИН(C7:C11) |
H15 | =ПОИСКПОЗ(E15;C7:C11;0) |
F15 | =ИНДЕКС(B7:B11;H15) |
G15 | =ИНДЕКС(H7:H11;H15) |
H16 | =ПОИСКПОЗ(E16;C7:C11;0) |
F16 | =ИНДЕКС(B7:B11;H16) |
G16 | =ИНДЕКС(H7:H11;H16) |
В результате получим таблицу, представленную на Рис. 9.
|
|