2015-06-28
398
Необходимо составить расчетную ведомость на выдачу стипендии группе студентов, условно состоящей из 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.
