ЛАБОРАТОРНАЯ РАБОТА. Создание ведомости на начисление стипендии.

ЛАБОРАТОРНАЯ РАБОТА

СОЗДАНИЕ ВЕДОМОСТИ НА НАЧИСЛЕНИЕ СТИПЕНДИИ.

ЗАДАНИЕ 1.

Сформируйте структуру таблицы и заполните ее постоянными значениями — числами, символами, текстом.

Рис. 1.

ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ

Группа__________ Дисциплина_________________

№ п/п	Фамилия, имя, отчество	№ зачетной книжки	Оценка	Подпись экзаменатора

            «отлично» ____________________________________

            «хорошо» _____________________________________

            «удовлетворительно»____________________________

            «неудовлетворительно»__________________________

            «неявки»_______________________________________

            ИТОГО ________________________________________

Рис.1. Форма экзаменационной ведомости

После окончания работы по заполнению ведомости постоянными данными запомните ее как рабочую книгу.

ТЕХНОЛОГИЯ РАБОТЫ

В любой таблице всегда можно выделить минимум две структурные части - название и ее шапку.

Содержимое ячеек в которых располагаются название таблицы и ее шапки.

Адрес ячейки            Вводимый текст

В1                   ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ

A3                   Группа №

СЗ                   Дисциплина

А5                   № п/п

В5                   Фамилия» имя, отчество

С5                   № зачетной книжки

D5                   Оценка

Е5                   Подпись экзаменатора

1.                  Отформатируйте ячейки А1:Е1:

-           выделите блок ячеек, нажмите правую кнопку мыши для вызова контекстного меню;      

-           введите команду контекстного меню Формат ячеек;

-           на вкладке Выравнивание выберите опции:

По горизонтали: по центру выделения

По вертикали: по верхнему краю

-           нажав кнопку <Размер>, выберите размер шрифта, например 14 пт;

-           выделите текст жирным шрифтом, нажав на панели инструментов кнопку

2.                  Проделайте подготовительную работу для формирования шапки таблицы:

-           выделите блок ячеек A3:J5, где располагается шапка таблицы;

-           вызовите контекстное меню и выберите команду Формат ячеек;

-           на вкладке Выравнивание задайте параметры:

По горизонтали: по значению

По вертикали: по верхнему краю

Переносить по словам: поставить флажок

Ориентация: горизонтальный текст (по умолчанию)

-           нажмите кнопку <ОК>.

3.                  Установите ширину столбцов таблицы.

Примечание. Можно изменить ширину столбца или строки иначе, если уже введен текст. Двойной щелчок левой кнопкой мыши на границе клетки с именем столбца (строки), в результате которого ширина столбца установится равной количеству позиций в самом длинном слове этого столбца.

-           аналогичные действия проделайте со столбцами  А, С, D, Е, F-J.   

4.       Заполните ячейки столбца В данными о студентах учебной группы, приблизительно 10—15 строк. Отформатируйте данные.

5.        Присвойте каждому студенту порядковый номер:

-           введите в ячейку А6 число 1;

-           установите курсор в нижний правый угол ячейки А6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер; выполните команду локального меню Заполнить.

6.                  После списка студентов в нижней части таблицы  введите в ячейки , столбца А текст итоговых строк; Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, ИТОГО.

7.                  Объедините две соседние ячейки для более удобного представления текста итоговых строк.

8.                  Сохраните рабочую книгу под именем Session.

ЗАДАНИЕ 2.

В созданной в предыдущем задании 1 рабочей книге с экзаменационной ведомостью  рис. 1, хранящейся в файле с именем Session, рассчитайте;

-         количество оценок  (отлично, хорошо, удовлетворительно, неудовлетворительно), неявок, полученных в данной группе;

-         общее количество полученных оценок.

A		B		C		D	E	F	G	H	I	J
ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ
Группа №			Дисциплина
№ п/п	Ф.И.О.		№ зачетной книжки		Оценка		Подпись экзаменатора	5	4	3	2	неявки
1	Ванин А.А.				4			0	1	0	0	0
2	Воробьева В.Л.				5			1	0	0	0	0
3	Голубкина О.Л.				4			0	1	0	0	0
4	Дятлов В.А.				3			0	0	1	0	0
5	Иванов Н.А.				5			1	0	0	0	0
6	Иванова  В.А.				4			0	1	0	0	0
7	Кукушкин М.И.				2			0	0	0	1	0
8	Орлов К.Н.				2			0	0	0	1	0
9	Петров К.Н.				5			1	0	0	0	0
10	Снегирев А.П.				2			0	0	0	1	0
Отлично
Хорошо
Удовлетворительно
Неудовлетворительно
Неявки
ИТОГО

Рис.2 Электронная таблица Экзаменационная ведомость  в режиме отображения значений.

 

ТЕХНОЛОГИЯ РАБОТЫ

1. Проделайте подготовительную работу, вводя названия (5, 4, 3, 2, неявки) соответственно в ячейки F5, G5, Н5,15, J5 вспомогательных столбцов (см. рис. 2).      

2. В эти столбцы F - J введите вспомогательные формулы. Суть формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего вспомогательного столбца как 1.           

Пример. Студент Снегирев получил оценку 5, тогда в ячейке F6 должна стоять 1, а в остальных вспомогательных столбцах G-J в данной строке—0.

Для ввода исходных формул воспользуйтесь Мастером функций. Рассмотрим эту технологию на примере ввода формулы в ячейкуF6:

-           установите курсор в ячейку F6 и выберите мышью на панели инструментов кнопку Мастера функций;

-           в 1-м диалоговом окне выберите вид функции

Категория — логические

Имя функции — ЕСЛИ

-           щелкните по кнопке<ОК>;

-           во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите соответствующие операнды логической функции:

Логическое выражение — D6

Значение, если истина, — 1

 Значение, если ложно, — 0

-           щелкните по кнопке <ОК>                                    

3.С помощью Мастера функции введите формулы аналогичным способом в остальные ячейки данной строки. В результате в ячейках F6- J6 должно быть:

Адрес ячейки                        Формула

F6                               ЕСЛИ(D6=5;0;1)

G6                               ЕСЛИ(D6=4;0;1)

H6                               ЕСЛИ(D6=3;0;1)

I6                                ЕСЛИ(D6=2;0;1)

J6                                ЕСЛИ(D6=”н/я”;0;1)

4.Скопируйте эти формулы во все остальные ячейки дополнительных столбцов:

-           выделите блок ячеек F6 ;J6;

-           установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, протащите ее до конца таблицы Экзаменационная ведомость;

-           выберите в контекстном меню команду Заполнить значения.

5.Определите имена блоков ячеек по каждому дополнительному столбцу. Рассмотрите это на примере дополнительного столбца F:                          .

-           выделите все значения дополнительного столбца , например F6.: адрес ячейки в столбце, в которой находится последнее значение  

-           введите команду Формулы, Присвоить имя;

-           в диалоговом окне в строке Имя введите слово ОТЛИЧНО:

-           щелкните по кнопке <Добавить>;

-           проводя аналогичные действия с остальными столбцами, вы создадите еще не­сколько имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.

6.Введите формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций. Покажем это на примере подсчета количества отличных оценок:

-           установите указатель мыши в ячейку С13 подсчета количества отличных оце­нок;

-           щелкните по кнопке Мастер функций,           

-           в диалоговом окне Мастер функций выберите: Категория — Математические, функция — СУММ; щелкните по кнопке <ОК>;

-           в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка, Имя, Вставить;

-           в появившемся диалоговом окне выделите имя блока ячеек Отлично, щелкните по кнопке <ОК>;

-           повторите аналогичные действия для подсчета количества других оценок в ячейках С16-С21.                  

7.Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом (см. рис.2);                           

-           установите курсор в пустой ячейке С21 (рядом с ИТОГО). Эта ячейка должна обязательно находиться под ячейками, где подсчитывались суммы по всем видам оценок;                                   

-           щелкните по кнопке Автосумма;                

-           выделите блок ячеек, где подсчитывались суммы по всем видам оценок, и нажмите клавишу <Enter>.

8.Переименуйте текущий лист:

-          установите курсор на имени текущего листами вызовите контекстное меню;

-          выберите параметр Переименовать и введите новое имя, например Экзамен 1(история).

9.Скопируйте несколько раз текущий лист Экзамен1:            

-           установите курсор на имени текущего листа и вызовите контекстное меню;

-           выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец, нажмите <ОК>. Обратите внимание на автоматическое наименование ярлыков новых листов.

-           Переименуйте листы в Экзамен 1(история), Экзамен 2 (география), Экзамен 3(физика).
Измените  оценки на 2  и  3 листах ведомости.

10.  Выполните команду Файл, Сохранить как.

 

ЗАДАНИЕ 3.

Подготовьте для каждой группы ведомость (рис. 3) назначения студентов на стипендию по результатам экзаменационной сессии. При выполнении данного задания потребуется произвести расчеты по более сложным, чем в предыдущем задании 2, формулам.

A	B	C		D	E
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа№___
	Минимум стипендии		200
№п/п	Ф.И.О.	Средний бал	Кол-во с данных экзаменов		Стипендия
1	Ванин А.А.
2	Воробьева В.Л.
3	Голубкина О.Л.
4	Дятлов В.А.
5	Иванов Н.А.
6	Иванова  В.А.
7	Кукушкин М.И.
8	Орлов К.Н.
9	Петров К.Н.
10	Снегирев А.П.
	Итого стипендиальный фонд по группе

Рис. 3. Форма стипендиальной ведомости

 Ознакомьтесь с алгоритмом действий по технологии выполнения данного задания:

1.Загрузите экзаменационную ведомость.           

2.На новом листе создайте ведомость стипендии (см рис. 3) и скопируйте в нее список группы из экзаменационной ведомости, отображенный на рис. 2.

3.Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.

4.Используя минимальное значение стипендии и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:

-      если средний балл не менее 4,5, выплачивается 50%-нaя надбавка к минимальной стипендии;         

-      если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;

-      если средний балл меньше 3, стипендия не выплачивается.

5.Подсчитайте сумму стипендиального фонда для всей группы.

ТЕХНОЛОГИЯ РАБОТЫ

1.    Создайте в этой книге новый лист— Стипендия, на который из столбцов А и В листа Экзамен 1 скопируйте фамилии и порядковые номера студентов.

2.    Оформите название и шапку ведомости назначения на стипендию согласно рис. 3. Для этого введите название таблицы — ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕН­ДИЮ Группа № и названия столбцов — № п/п; Фамилия, имя, отчество; Стипендия, задайте шрифт и тип выделения—полужирный.

3.    Укажите размер минимальной стипендии в ячейке D3;

4.    Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов.

5.    Введите формулу вычисления среднего балла студента в ячейку С6 для первого студента, например Снегирева .

 Для этого:

6.    установите курсор в ячейке С6;                        

7.    щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:

Категория: Статистические

 Имя: СРЗНАЧ

-     щелкните по кнопке <ОК>, появится панель ввода аргументов функции СРЗНАЧ;        

-     установите курсор в 1-й строке (имя Число /) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;

-     установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экза­мену;

-     установите курсор в 3-й строке (имя Число 3 щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;

-     щелкните по кнопке <ОК>;

-     в ячейке С6 появится значение, рассчитанное по формуле

-     =СРЗНАЧ('Экзамен 1’!D6;'Экзамен 1(2)'!D6;'Экзамен 1(3)'!D6).

-     Скопируйте формулу по всем ячейкам столбца С. Для этого:

-     установите курсор в ячейке С6;

-     наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;

-     нажмите левую кнопку мыши и протащите ее до конца этого столбца;

-     просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке.

-     Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. При этом технология ввода будет аналогична описанной в п.6:

-     установите курсор в ячейке D6;

-     щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры;

Категория: Статистические

Имя: СЧЕТ

-      щелкните по кнопке <ОК>, появится панель ввода аргументов функции СЧЕТ;

-      установите курсор в 1-Й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;

-      установите курсор во 2-й строке (имя Значение 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;        

-      установите курсор в 3-Й строке (имя Значение 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;             

-      щелкните по кнопке <ОК>;

-      в ячейке D6 появится значение, рассчитанное по формуле

=СЧЕТ('Экзамен 1'!D6;'Экзамен 1(2)'!D6;'Экзамен 1(3)'!D6).

9. Скопируйте формулу по всем ячейкам столбца D так же, как вы делали в п.7.
10.  Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта фор­мула должна иметь следующий вид:

=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0))

A	B	C		D	E
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа№___
	Минимум стипендии		200
№п/п	Ф.И.О.	Средний бал	Кол-во с данных экзаменов		Стипендия
1	Ванин А.А.	4,33	3		200
2	Воробьева В.Л.	4,8	3		300
3	Голубкина О.Л.	3	3		200
4	Дятлов В.А.	5	3		300
5	Иванов Н.А.	5	3		300
6	Иванова  В.А.	3	3		200
7	Кукушкин М.И.	4	3		200
8	Орлов К.Н.	4	3		200
9	Петров К.Н.	4	3		200
10	Снегирев А.П.	3	3		200
	Итого стипендиальный фонд по группе				2300

Рис. 4. Итоговая ведомость

Литература:

Информатика: Практикум по технологии работы на компьютере /Под ред. Н.В. Макаровой. - 3-е изд., перераб. - М.: Финансы и статистика, 2005 - 256 с: ил. ISBN 5-279-02280-2