|
|
Практическая работа Exel. Практическая работа Работа с электронными таблицами в среде ms excel Работа Настройка новой рабочей книги
Задание № 3. Подготовьте для каждой группы ведомость (рис. 3.4) назначения студентов на стипендию по результатам экзаменационной сессии. При выполнении данного задания потребуется произвести расчеты по более сложным, чем в предыдущем задании 2, формулам.
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ
Группа №________
Минимальный размер стипендии -
№ п/п
|
Фамилия, имя, отчество
|
Стипендия
|
|
|
|
|
|
|
|
|
|
Итого стипендиальный фонд по группе—
Рис. 3.4. Форма стипендиальной ведомости
Ознакомьтесь с алгоритмом действий по технологии выполнения данного задания:
1. Загрузите экзаменационную ведомость.
2. На новом листе создайте ведомость стипендии (см. рис.3.4) и скопируйте в нее список группы из экзаменационной ведомости, отображенный на рис.3.9.
3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.
4. Используя минимальное значение стипендии и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:
если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
если средний балл меньше 3, стипендия не выплачивается.
5. Подсчитайте сумму стипендиального фонда для всей группы.
6. Сверьте полученные общий вид таблицы, результаты и структуры формул с тем, что отображено на рис.3.5 и 3.6.
Рис. 3.5. Электронная таблица Ведомость назначения на стипендию в режиме отображения значений
Методика выполнения работы
1. Загрузите рабочую книгу с именем Session:
выполните команду Файл, Открыть;
в диалоговом окне установите следующие параметры:
Папка: имя вашего каталога
Имя файла: Session
Тип файла: Книга Microsoft Ехcel
|
2. Создайте в этой книге новый лист — Стипендия, на который из столбцов А и В листа Экзамен 1скопируйте фамилии и порядковые номера студентов.
3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 5.
Для этого введите название таблицы - ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕН-
ДИЮ Группа № и названия столбцов - № п/п; Фамилия, имя, отчество; Стипендия,
задайте шрифт и тип выделения - полужирный.
4. Укажите размер минимальной стипендии в ячейке D3;
5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 3.5. Скорректируйте расхождение.
6. Введите формулу вычисления среднего балла студента в ячейку С6 для первого студента, например Снегирева (см. рис. 3.6). Для этого:
установите курсор в ячейке С6;
|
A
|
B
|
C
|
D
|
E
|
1
|
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа№ _____
|
2
|
|
|
|
|
|
3
|
|
Минимум
стипендии
|
|
200
|
|
4
|
|
|
|
|
|
5
|
№ п/п
|
Фамилия, имя,
отчество
|
Средний балл
|
Кол-во сданных экзаменов
|
Стипендия
|
6
|
1
|
Снегирев А.П.
|
=СРЗНАЧ ('Экзамен 1'!D6; 'Экзамен 1 (2)'D6; Экзамен 1 (3)'! D6)
|
=СЧЕТ('Экзамен 1'!D6; 'Экзамен 1
(2)'D6; 'Экзамен 1 (3)'! D6)
|
=ECJIИ(И(C6>=4,5;D6=3);$D$3*1,5;
ЕСЛИ(И(C6>=3;D6=3);$D$3;0))
|
7
|
2
|
Орлов К.Н.
|
=СРЗНАЧ("Экзамен 1'!D7; 'Экзамен 1 (2)'!D7; 'Экзамен 1 (3)'D7)
|
=СЧЕТ("Экзамен 1'!D7; 'Экзамен 1
(2)'!D7; 'Экзамен 1 (3)'D7)
|
=ECJIИ(И(C7>=4,5;D7=3);$D$3*1,5;
ЕСЛИ(И(C7>=3;D7=3);$D$3;0))
|
8
|
3
|
Воробьева В.Л.
|
=СРЗНАЧ('Экзамен 1'!D8; Экзамен 1 (2)'!D8; 'Экзамен 1 (3)'!D8)
|
=СЧЕТ('Экзамен 1'!D8; Экзамен 1
(2)'!D8; 'Экзамен 1 (3)'!D8)
|
=ECJIИ(И(C8>=4,5;D8=3);$D$3*1,5;
ЕСЛИ(И(C8>=3;D8=3);$D$3;0))
|
9
|
4
|
Голубкина О.Л.
|
=СРЗНАЧ('Экзамен 1'!D9; 'Экзамен 1 (2)'!D9; 'Экзамен 1 (3)'!D9)
|
=СЧЕТ('Экзамен 1'!D9; 'Экзамен 1
(2)'!D9; 'Экзамен 1 (3)'!D9)
|
=ECJIИ(И(C9>=4,5;D9=3);$D$3*1,5;
ЕСЛИ(И(C9>=3;D9=3);$D$3;0))
|
10
|
5
|
Дятлов В.А.
|
=СРЗНАЧ('Экзамен 1'!D10; 'Экзамен 1 (2)'!D10; 'Экзамен 1 (3)'!D10)
|
=СЧЕТ('Экзамен 1'!D10; 'Экзамен 1
(2)'!D10; 'Экзамен 1 (3)'!D10)
|
=ECJIИ(И(C10>=4,5;D10=3);$D$3*1,5; ЕСЛИ(И(C10>=3;D10=3);$D$3;0))
|
11
|
6
|
Кукушкин М.И.
|
=СРЗНАЧ('Экзамен 1'!D11; Экзамен 1 (2)'!D11; 'Экзамен 1 (3)'D11)
|
=СЧЕТ('Экзамен 1'!D11; Экзамен 1
(2)'!D11; 'Экзамен 1 (3)'D11)
|
=ECJIИ(И(C11>=4,5;D11=3);$D$3*1,5; ЕСЛИ(И(C11>=3;D11=3);$D$3;0))
|
12
|
7
|
….
|
|
|
|
13
|
|
|
|
|
|
14
|
|
|
|
|
|
15
|
|
Итого стипендиальный фонд по группе
|
|
=СУММ(Е6:Е14)
|
Рис. 3.6. Электроннаятаблица Ведомость назначения на стипендию в режиме отображения формул
введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:
Категория: Статистические
Имя: СРЗНАЧ
|
щелкните по кнопке <ОК>, появится панель ввода аргументов функции СРЗНАЧ;
установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
щелкните по кнопке <ОК>;
в ячейке С6 появится значение, рассчитанное по формуле:
=СРЗНАЧ('Экзамен 1'!D6;'Экзамен 1 (2)'!D6;'Экзамен 1 (3)'!D6)
7. Скопируйте формулу по всем ячейкам столбца С. Для этого:
установите курсор в ячейке С6;
наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;
нажмите левую кнопку мыши и протащите ее до конца этого столбца;
просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке.
8. Введите в столбец 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. Эта формула должна иметь следующий вид: =3;D6=3);$D$3;0)'>$D$3*1,5;ЕСЛИ(И(C6>=3;_D_6=3);$D$3;0))__Внимание!'>=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(C6>=3;D6=3);$D$3;0))
Внимание!
1. В структуре формулы имеются вложенные функции И(...), ЕСЛИ(...). Для ввода этих функций надо воспользоваться
кнопкой вызова функции, находящейся в строке
ввода под панелями.
2. При наборе формулы автоматически расставляются круглые скобки и разделительный символ - точка с запятой.
3. В процессе набора формулы постоянно сравнивайте ее с выражением, которое приведено в этом пункте выше.
4. В числах для отделения целой части от дробной используется либо точка, либо запятая, что зависит от установок Excel.
5. Если после ввода формулы появится синтаксическая ошибка,
то следует проверить количество скобок, наличие разделите-
ля (точки с запятой), заменить в числе точку на запятую или
наоборот.
Технология ввода формулы будет аналогична описанной в п. 6 и 8:
установите курсор в ячейке Е6;
введите команду Формулы, Вставить функцию и выберите в диалоговом окне параметры:
Категория: Логические
Имя: ЕСЛИ
|
щелкните по кнопке , появится панель ввода аргументов функции ЕСЛИ;
курсор будет находиться в 1-й строке (имя — Логическое выражение) панели ввода аргументов функции;
нажмите кнопку вызова функции в строке ввода, выберите категорию Другие функции и функцию И, нажмите кнопку <ОК>;
появится второе окно ввода аргументов функции И, курсор автоматически будет установлен в строке Логическое значение1;
щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение С6>=4,5
установите курсор на второй строке Логическое значение 2 и аналогично сформируйте выражение, которое указывает необходимое количество сданных экзаменов (в данном примере - это число 3) D6=3
щелкните по кнопке <ОК>. В результате в строке ввода должно появиться выражение =ЕСЛИ(И(С6>=4,5; D6=3)
щелкните мышью на строке ввода, появится первое окно ввода аргументов для функции ЕСЛИ;
установите курсор во 2-й строке (Значение_ если_ истина), щелкните в ячейке D3 и нажмите клавишу . Появится символ $ перед именем столбца и номером строки. Введите выражение *1,5. В результате в этой строке будет выражение $D$3*1,5
установите курсор в 3-й строке ( Значение_ если_ ложь) и по аналогичной технологии введите оставшуюся часть формулы ЕСЛИ(И(С6>=3;D6=3);$D$3;0)
после окончания формирования формулы нажмите кнопку <ОК>.
6. Скопируйте эту формулу в другие ячейки столбца Е так же, как вы делали в п. 7 и 9.
вводите другие оценки в экзаменационные ведомости;
измените минимальный размер стипендии.
7. Сохраните рабочую книгу командой Файл, Сохранить.
8. Закройте рабочую книгу командой Файл, Закрыть.
Работа 3. Построение, редактирование и форматирование диаграмм
Задание № 1. Для таблицы на рис.3.7 постройте два вида диаграмм - внедренную на лист с исходными данными и на отдельном листе.
Для этого вам необходимо выполнить следующие действия:
1. Провести подготовительную работу, которая состоит в следующем:
создать рабочую книгу;
сохранить рабочую книгу;
переименовать Лист1 на Успеваемость.
Создать таблицу в соответствии с рис.3.7 и вычислить средний балл по факультету по каждому предмету.
Рис. 3.7. Таблица успеваемости к заданию 1
3. Построить внедренную диаграмму, оформив согласно рис.3.8.
4. Построить диаграмму другого типа и разместить ее на отдельном листе.
Рис. 3.8. Диаграмма типа Гистограмма для задания 1
Методика выполнения работы
1. Проделайте подготовительную работу:
создайте новую рабочую книгу командой Файл, Создать;
сохраните в выбранной папке созданную книгу под именем Diagкомандой Файл, Сохранить как;
переименуйте Лист 1:
установите курсор на Лист 1 и вызовите правой кнопкой мыши контекстное меню;
выполните команду Переименовать и введите новое имя -Успеваемость.
2. Создайте таблицу согласно рис. 7, например начиная с ячейки А1. Впустые ячейки с названием Факультет введите формулу вычисления среднего балла по факультету, например в ячейку В6 столбца Информатика:
вызовите Мастер функций, щелкнув по его кнопке на панели инструментов;
выберите категорию функций Статистическая, имя функции -СРЗНАЧ, щелкните по кнопке <ОК>;
введите в первую строку диалогового окна адреса первой и последней ячеек столбца с оценками, используя для этого мышь, например В2:В5;
скопируйте формулу в ячейку С6 столбца Высшая математика,
3. Постройте внедренную диаграмму, выполнив следующие операции:
• выполните команду Вставка, Диаграммы.
|
|
|
Скачать 244.49 Kb.