Сделать задание в Exel

Задание 47

В деканате имеются сведения о студентах для начисления стипендии. На факультете имеется две специальности, по каждой - 2 группы студентов, в каждой группе не менее 4 человек.

3) Составить Таблицу 1 «Условия начисления стипендии», в которой указать порядок начисления стипендии, проценты и коэффициенты. Стипендия начисляется следующим образом:

- если есть хотя бы одна тройка, то стипендия не начисляется;

- если средний балл меньше 4,5 , то размер стипендии 1100 рублей – базовая стипендия;

- если средний балл больше 4,5 , то размер стипендии увеличивается на 20% от базовой стипендии;

- если все пятерки, то к базовой стипендии прибавляется 50%.

При составлении таблиц ячейки не объединять! Все постоянные значения и коэффициенты помещаются в первых строках (перед Таблицей 1) и для них устанавливается абсолютная адресация при использовании в формулах.

2) На новом Листе создать Таблицу 2 «Сведения о студентах»: ФИО студента, № зачетки, дата рождения, пол, дата поступления, специальность, № группы, оценка по физике, оценка по математике, оценка по иностранному языку. Добавить вычисляемые поля (столбцы): средний балл за сессию (используйте функцию ЕСЛИ(), которая может иметь до 7 вложений), сумма стипендии.

3) В Таблице 2 установить условное форматирование столбца «Сумма стипендии». Для этого в меню ГЛАВНАЯ \ СТИЛИ \ УСЛОВНОЕ ФОРМАТИРОВАНИЕ\ ПРАВИЛА ВЫДЕЛЕНИЯ – выбрать условие: «равно» - 1100 (базовая стипендия) – выбрать цвет для выделения (стипендия 1100 руб. выделится этим цветом), выбрать другой цвет для выделения стипендии более 1100 руб.

4) На отдельном Листе создать копию Таблицы 2 - для этого нажать правой кнопкой мыши на закладке Лист и выбрать в контекстном меню Переместить\Копировать =>Создать копию, переименовать Лист с копией => «Фильтр» - для этого нажать правой кнопкой мыши на закладке Лист(2) и выбрать в контекстном меню Переименовать. Установить ФИЛЬТР для столбца «Средний балл за сессию» - выбрать в меню ДАННЫЕ \ СОРТИРОВКА И ФИЛЬТР \ ФИЛЬТР и, нажав левой кнопкой мыши на появившуюся в заголовке столбца кнопку (справа внизу ячейки), выбрать «Числовой фильтр» - задать условие: «более» - 4,5 для вывода данных о студентах, средний балл за сессию у которых более 4,5 баллов.

Можно установить фильтр через меню ГЛАВНАЯ \ РЕДАКТИРОВАНИЕ \ СОРТИРОВКА И ФИЛЬТР \ ФИЛЬТР.

5) Создать копию Листа Таблицы 2, переименовать копию Листа(2) => «Сортировка». Выполнить сортировку по убыванию Сумма стипендии. Сортировка осуществляется через меню ДАННЫЕ \ СОРТИРОВКА И ФИЛЬТР выбором типа сортировки для заданной области, либо через меню ГЛАВНАЯ \ РЕДАКТИРОВАНИЕ \ СОРТИРОВКА И ФИЛЬТР \ НАСТРАИВАЕМАЯ СОРТИРОВКА.

6) По данным Таблицы 2 составить Таблицу 3 «Сводную ведомость стипендии студентов по факультету»: ФИО, размер стипендии, группа. Отсортировать данные в алфавитном порядке.

7) По данным Таблицы 2 составить Таблицу 4 «Данные о суммарной начисленной стипендии по специальностям»: № специальности, сумма стипендии (с использованием функции (СУММЕСЛИ(), количество студентов (СЧЕТЕСЛИ()), обучающихся на данной специальности, средняя стипендия по специальности, итоговой строкой рассчитать общую сумму стипендии по факультету. Построить Гистограмму по долям средней суммы стипендии по специальностям.

8) Составить Таблицу 5 «Ведомость уполномоченных (старост) на получение стипендии»: ФИО старосты, номер группы, общая суммарная стипендии по группе (с использованием функции (СУММЕСЛИ(), роспись в получении.

Построить Круговую диаграмму по суммарной стипендии по каждой группе.

9) Составить «Сводную таблицу» по Таблице 2 - «Расчетная ведомость …» (курсор внутри Таблицы 2)

- для EXCEL 2007 – Меню ВСТАВКА \ СВОДНАЯ ТАБЛИЦА \ выбрать таблицу или диапазон, поместить отчет на новый Лист.

Макет Сводной таблицы задается перетаскиванием полей из списка полей Исходной Таблицы: в области Строк выводить ФИО студента, а в области Значений – Сумма стипендии, в области Фильтра отчета – Группа. Задать вывод максимального значения в поле Сумма стипендии, для этого щелкнуть два раза левой кнопкой мыши на заголовке поля, появится окно – Параметры поля значений - задать значение «максимум».

Расположить данные в поле Сумма стипендии в порядке возрастания = от А до Я: щелкнуть один раз левой кнопкой мыши на кнопке справа в заголовке поля ФИО студента - выбрать в контекстном меню от А до Я и Дополнительные параметры сортировки, и в окне «Сортировка» выбрать имя поля - Сумма стипендии.

10) Построить на отдельном Листе Сводную диаграмму.

В случае изменения данных исходной Таблицы воспользуйтесь кнопкой - ! Обновить.