Заполнить ведомость поступления в институт excel
Задание №1 Создание и заполнение таблицы Excel данными и формулами.
1. Создать в среде EXCEL электронную таблицу Экзаменационная Ведомость и заполнить ее данными. Отформатировать согласно образцу.
ввести дополнительное количество столбцов, по одному на каждый вид оценки
(5 столбцов);
в первые ячейки столбцов ввести вспомогательные формулы с помощью Мастера функций;
скопировать эти формулы во все остальные ячейки дополнительных столбцов;
под таблицей (в выделенные ячейки) ввести формулы подсчета суммарного количества полученных оценок определенного вида с помощью функции СУММ;
подсчитать общее количество (ИТОГО) всех полученных оценок другим способом – Автосумма;
сделать дополнительные столбцы скрытыми;
провести коррекцию оценок и оценить полученные результаты;
переименовать текущий лист на Экзамен1, сохранить рабочую книгу под именем Задание №1.
4. На новом листе Экзамен3 создать третий вариант Экзаменационной ведомости. Провести коррекцию оценок. Сохранить документ.
1. На новом листе Стипендия создать Ведомость назначения на стипендию и скопировать на нее список группы из Экзаменационной ведомости.
2. В отдельной ячейке задать размер минимальной стипендии – 1100 руб.
3. Вычислить средний балл на основе трех Экзаменационных ведомостей по каждому студенту:
например: =СРЗНАЧ('Экзамен1'!D6;'Экзамен2'!D6;'Экзамен3'!D6)
4. Подсчитать количество сданных каждым студентом экзаменов с учетом неявок:
например: =СЧЕТ('Экзамен1'!D6;'Экзамен2'!D6;'Экзамен3'!D6)
если средний балл равен 5 – выплачивается 50% надбавка к минимальной стипендии;
если средний балл от 4 до 5 – выплачивается минимальная стипендия;
если средний балл меньше 4 – стипендия не выплачивается.
6. Подсчитать сумму стипендиального фонда для всей группы.
7. Применить условное форматирование Ведомости (заливка ячеек и начертание шрифта)
для трех столбцов: средний балл, количество сданных экзаменов, стипендия.
8. Скрыть формулы, установить защиту на неизменяемые ячейки (снять защиту с ячеек с фамилиями студентов и минимальным размером стипендии), лист, книгу.
9. Сохранить и защитить документ (установить пароль на открытие документа).
10. На новом листе рассчитать начисление стипендии по следующему условию:
Стипендиальная ведомость факультета представляет собой ЭТ Excel , содержащую 5 рабочих листов. Соответственно Лист 1 – курс 1, Лист 2 – курс 2 и т. д.
На каждом рабочем листе составлены списки двух групп по 25 человек. Каждая таблица содержит следующие поля: №п\п, ФИО, оценки по пяти предметам, средний балл, успеваемость, стипендия, стипендия с надбавкой хорошистам и отличникам, стипендия с надбавкой из дополнительного фонда.
Поля: №п\п, ФИО, оценки по пяти предметам заполняются, остальные поля расчетные. (пример на рисунке)
Успеваемость
Успеваемость студентов определяется по следующей схеме: если средний балл 4,75 и выше, присваивается категория «отличник», если в промежутке от 3,75 до 4,75 – «хорошист», в промежутке от 2,5 до 3,75 – «троечник», если средний балл меньше 2,5 – «неуспевающий».
Для расчета успеваемости используется логическая функция ЕСЛИ. Данная функция включает в себя три Условия, три Истины и Ложь. Выглядит следующим образом:
Условие1 – средний балл >=4,75; ему соответствует Истина1 «отличник»;
Условие2 – средний балл >=3,75; ему соответствует Истина2 «хорошист»;
Условие3 – средний балл >=2,5; ему соответствует Истина3 «троечник»;
Ложью является значение «неуспевающий».
Стипендия
В условии задачи заявлено, что стипендия студентам, чей балл меньше 3,5 не начисляется. Стипендия остальным студентам составляет 460 руб.
Для назначения стипендии используется логическая функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – средний балл <3,5;
Стипендия с надбавкой хорошистам и отличникам
Студентам, имеющим категорию успеваемости «хорошист» или «отличник», назначается надбавка в размере 10% от стипендии.
Для расчета стипендии с надбавкой используется логическая функция ЕСЛИ. Данная функция включает в себя два Условия, одну Истину и Ложь. Выглядит следующим образом:
Условие1 – категория «отличник»;
Условие2 – категория «хорошист»;
Истина – стипендия с надбавкой 10%;
Стипендия с доп.надбавкой
Всему факультету дополнительно выделили 50% стипендиального фонда. Необходимо распределить его между отличниками. Для выполнения данных расчетов необходимо:
Вставить дополнительный лист в рабочую книгу, назвать его «общий фонд».
Рассчитать величину стипендиального фонда каждой группы. Для этого внизу каждой таблицы, в поле Стипендия с надбавкой хор и отл, вставить функцию СУММ.
Рассчитать первоначальный стипендиальный фонд. Для этого используется Консолидация данных, расположенная на ленте Данные. Откроется окно, в котором необходимо выбрать действие – Сумма, далее необходимо по очереди Добавить ссылки на ячейки, содержащие итоговые значения фондов по каждой группе.
После нажатия ОК в ячейке появится сумма стипендиальных фондов групп. Щелкнув слева от рабочего поля на символе +, можно увидеть список чисел, являющихся фондами этих групп.
Рассчитать дополнительный фонд, умножив общий фонд на 50%
Рассчитать количество отличников на факультете. Для этого необходимо воспользоваться функцией СЧЁТЕСЛИ, выбрав ее в категории статистические. На втором шаге Мастера функций указать диапазон ячеек первой таблицы, содержащей информацию о категории успеваемости. Критерий для отбора указать «отличник».
Т.к. у нас на рабочем листе две таблицы, для расчета общего количества отличников на курсе необходимо суммировать две функции СЧЁТЕСЛИ. Далее необходимо выполнить вычисления по каждому курсу отдельно.
Таблица в режиме отображения формул выглядит следующим образом
Рассчитать общее количество отличников. Для этого вставить функцию СУММ внизу таблицы.
Рассчитать величину надбавки каждому отличнику. Для этого необходимо дополнительный фонд разделить на количество отличников.
Рассчитать Стипендию с доп.надбавкой.
Для этого используется условная функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:
Условие – категория успеваемости студента - «отличник»;
Истина – стипендия с дополнительной надбавкой (ссылке на ячейку, содержащую доп.надбавку, присваивается абсолютное значение – клавишей F 4);
Ложь – стипендия без изменений.
Для построения диаграммы, отображающей стипендиальные фонды групп факультета, необходимо воспользоваться данными на листе «общий фонд».
Сведения о зачислении в университет
ФИО абитуриента | Мате-матика | Рус-ский язык | Инфор-матика | Общий балл | Ср. балл | Зачисле-ние |
Иванов И.И. | ||||||
Петров П.П. | ||||||
Буль В.В. | ||||||
Соркин Е.П. | ||||||
Мороз А.Л. | ||||||
Гусев Р.И. | ||||||
Мороз Д.Л. | ||||||
Гвоздев С.А. | ||||||
Козлов Н.Н. | ||||||
Средний балл за экзамен |
Далее необходимо поменять название рабочего листа: щелкнем правой кнопкой мыши внизу по названию Лист1, выберем пункт Переименовать и введем с клавиатуры новое имя Пример1.
Введем в таблицу исходные данные:
1. Щелкнем по ячейке А1 и введем название таблицы: Сведения о зачислении в университет
2. В ячейки А3 : G3 введем шапку таблицы 1.
3. В ячейки А4 : А12 введем ФИО студентов.
4. В ячейки В4 : D12 введем числа, которые представляют собой полученные на экзаменах индивидуальные баллы абитуриентов.
5. В ячейке Е4 необходимо посчитать общий балл абитуриента Иванова, который равен сумме баллов по всем дисциплинам, т.е. B4+C4+D4. Для этого выделим ячейку Е4 (один раз щелкнув по ней левой кнопкой мыши) и введем по-английски с клавиатуры следующую формулу: =B4+C4+D4 и нажмем Enter для расчета. Это ручной способ ввода формул.
6. Освоим автоматический способ ввода формулы. Выделим ячейку Е5, щелкнув на панели инструментов на значке Автосумма , получим следующую формулу =СУММ(B5:D5). Для ввода формулы нажмем Enter.
Далее таким же способом необходимо посчитать весь столбец общих баллов (Е6:Е12);
Для расчета среднего балла воспользуемся стандартной функцией MS Excel СРЗНАЧ (число1; число2; . ), которая возвращает среднее (арифметическое) своих аргументов. Число1, число2, . – это от 1 до 30 аргументов, для которых вычисляется среднее. Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
2. В поле Число1 введем с клавиатуры или выделим мышью диапазон ячеек (B4:D4). В поле Аргументы функциисразу появится результат, нажмем Enter или Ок.
3. Аналогично посчитаем весь столбец Ср. балл.(см. рис. 1) для всех абитуриентов и средний балл по предмету.
Рис. 1. Результаты выполнения примера 1
Воспользуемся логической функцией ЕСЛИ,которая возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае – ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.
Значение_если_истина – это значение, которое возвращается, если лог_выражение равно ИСТИНА. Значение_если_ложь – это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Значение_если_ложь может быть формулой.
Microsoft Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для вычисления числа появлений текстовой строки или числа в диапазоне ячеек используйте функцию СЧЁТЕСЛИ. Для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, используйте функцию СУММЕСЛИ.
Для расчета количества абитуриентов, принятых в ВУЗ воспользуемся функцией СЧЁТЕСЛИ,котораяподсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
СЧЁТЕСЛИ(диапазон;критерий),
Для расчета рейтинга(ранга) каждого абитуриента по общему баллу необходимо применить функцию РАНГ, которая возвращает ранг (рейтинг) каждого абитуриента в списке чисел. Ранг числа – это его величина относительно других значений в списке.
В MS Excel для вычисления ранга используется функция
РАНГ (число; ссылка; порядок), где
число – адрес на ячейку, для которой определяется ранг (E4);
ссылка – ссылка на массив общих баллов (Е4:Е12);
порядок – число, определяющее способ упорядочения. Если порядок равен 0 (нулю), или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если порядок – любое ненулевое число, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.
На следующем этапе необходимо оформить таблицу так, чтобы при распечатке она красиво выглядела на бумаге. В программе MS Excel предусмотрены разнообразные средства, с помощью которых вы можете по своему вкусу оформить таблицу – изменить шрифт, цвет, заливку и границы.
Для выполнения задания используйте в качестве образца таблицу (рис. 1).
Ввести в ячейку А1 текст «Ведомость»
Ввести в ячейку А2 текст «учета посещений в поликлинике (амбулатории), диспансере, консультации на дому»
Ввести в ячейку А3 текст «Фамилия и специальность врача»
Ввести в ячейку А4 текст «за»
Ввести в ячейку А5 текст «Участок: территориальный №»
Ввести в ячейку Е5 текст «цеховой №»
Создать шапку таблицы:
ввести в ячейку А7 текст «Числа месяца»
ввести в ячейку В7 текст «В поликлинике принято осмотрено- всего»
ввести в ячейку С7 текст «В том числе по поводу заболеваний»
ввести в ячейку Е7 текст «Сделано посещений на дому»
ввести в ячейку F7 текст «В том числе к детям в возрасте до 14 лет включительно»
ввести в ячейку C8 текст «взрослых и подростков»
ввести в ячейку D8 текст «детей в возрасте до 14 лет включительно»
ввести в ячейку F8 текст «по поводу заболеваний»
ввести в ячейку G8 текст «профилактических и патронажных»
ввести в ячейку А9 текст «А»
пронумеровать остальные столбцы таблицы
Отформатировать шапку таблицы по образцу
В таблице, построенной в предыдущем задании, заполнить произвольными данными столбцы
В строке 15 сформировать строку ИТОГО: (в ячейках В15, С15, D15, Е15, F15 и G15) использовать Автосумму .
Введите в ячейку J6 формулу для подсчета Суммы к выдаче без учета налога : =G6+H6
Скопируйте формулу в ячейки диапазона J7:J14, обратите внимание на автоматические изменения в формулах, происходящие при копировании
Введите формулу для расчета Налога (столбец 9) : =$E$3*(G6+H6)
Скопируйте формулу в ячейки диапазона I7:I14, обратите внимание на автоматические изменения в формулах, происходящие при копировании
Измените формулу в ячейке J6: = G6+H6-I6
Скопируйте формулу в ячейки диапазона J7:J14, обратите внимание на автоматические изменения в формулах, происходящие при копировании
Подсчитайте итоговые значения в ячейках G16, I16, J16, используя Автосумму
Подсчитайте среднее значение по столбцу Оклад в ячейке G18, используя Мастер функций и функцию СРЗНАЧ (категория Статистические). Формула: = СРЗНАЧ (G6:G14)
Скопируйте формулу в ячейки I18 и J18, обратите внимание на автоматические изменения в формулах, происходящие при копировании
По данным таблицы постройте диаграммы:
круговую диаграмму первичной заболеваемости социально значимыми болезнями в г. Санкт- Петербурге в 2010 году;
гистограмму динамики изменения первичной заболеваемости населения социально значимыми болезнями в г. Санкт- Петербурге в период 2006- 2010 гг.
график динамики изменения первичной заболеваемости населения дизентерией в г. Санкт- Петербурге в период 2006- 2010 гг.
Вычисления в таблицах. Формулы.
Преобразуйте таблицу из задания №3 к виду на рис.4, создав и заполнив столбец «Процент выполнения плана», а также задайте размер премии 15% в ячейке Н3.
Выполните расчет значений в столбце «Премия», используя встроенную логическую функцию ЕСЛИ, исходя из следующего условия:
Пересчитайте в соответствии с изменениями в таблице столбцы «Налог», «Сумма к выдаче», итоговые и средние значения.
Сравните полученные результаты с таблицей на рис. 5.
Рисунок 4- Изменения таблицы задания №3
Рисунок 5- Результат выполнения задания 5
ПРАКТИЧЕСКОЕ ЗАДАНИЕ №6
Вычисления в таблицах. Формулы.
Используя логические функции, составьте формулу для автоматизированного определения оценок студентов в соответствии с набранными баллами, исходя из следующих условий:
Перед выполнением расчетов составьте алгоритм решения задачи в графической форме.
3. Рассчитайте средний балл, установив вывод его значения в виде целого числа.
4. Упорядочьте данные, содержащиеся в таблице, по убыванию набранных баллов.
Читайте также: