Практическая работа Exel. Практическая работа Работа с электронными таблицами в среде ms excel Работа Настройка новой рабочей книги
Задание № 3. Подготовьте для каждой группы ведомость (рис. 3.4) назначения студентов на стипендию по результатам экзаменационной сессии. При выполнении данного задания потребуется произвести расчеты по более сложным, чем в предыдущем задании 2, формулам.
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ
Группа №________
Рис. 3.4. Форма стипендиальной ведомости
Ознакомьтесь с алгоритмом действий по технологии выполнения данного задания:
1. Загрузите экзаменационную ведомость.
2. На новом листе создайте ведомость стипендии (см. рис.3.4) и скопируйте в нее список группы из экзаменационной ведомости, отображенный на рис.3.9.
3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.
- если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
- если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
- если средний балл меньше 3, стипендия не выплачивается.
5. Подсчитайте сумму стипендиального фонда для всей группы.
6. Сверьте полученные общий вид таблицы, результаты и структуры формул с тем, что отображено на рис.3.5 и 3.6.
Рис. 3.5. Электронная таблица Ведомость назначения на стипендию в режиме отображения значений
Методика выполнения работы
- выполните команду Файл, Открыть;
- в диалоговом окне установите следующие параметры:
3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 5.
Для этого введите название таблицы — ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕН—
ДИЮ Группа № и названия столбцов — № п/п; Фамилия, имя, отчество; Стипендия,
задайте шрифт и тип выделения — полужирный.
4. Укажите размер минимальной стипендии в ячейке D3;
5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 3.5. Скорректируйте расхождение.
- установите курсор в ячейке С6;
стипендии
- введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:
Категория: Статистические
Имя: СРЗНАЧ - щелкните по кнопке , появится панель ввода аргументов функции СРЗНАЧ;
- установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
- установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
- установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
- щелкните по кнопке ;
- в ячейке С6 появится значение, рассчитанное по формуле:
- установите курсор в ячейке С6;
- наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;
- нажмите левую кнопку мыши и протащите ее до конца этого столбца;
- просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке.
- установите курсор в ячейке D6;
- введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:
- щелкните по кнопке , появится панель ввода аргументов функции СЧЕТ;
- установите курсор в 1-й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
- установите курсор во 2-й строке (имя Значение 2), щелкните на названии листаЭкзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экза-
мену; - установите курсор в 3-й строке (имя Значение 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
- щелкните по кнопке ;
- в ячейке D6 появится значение, рассчитанное по формуле
9. Скопируйте формулу по всем ячейкам столбца D аналогично п.7.
10. Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула должна иметь следующий вид: =4,5;D6=3); =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;
- введите команду Формулы, Вставить функцию и выберите в диалоговом окне параметры:
- щелкните по кнопке , появится панель ввода аргументов функции ЕСЛИ;
- курсор будет находиться в 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)
- после окончания формирования формулы нажмите кнопку .
- вводите другие оценки в экзаменационные ведомости;
- измените минимальный размер стипендии.
8. Закройте рабочую книгу командой Файл, Закрыть.
Работа 3. Построение, редактирование и форматирование диаграмм
Задание № 1. Для таблицы на рис.3.7 постройте два вида диаграмм — внедренную на лист с исходными данными и на отдельном листе.
Для этого вам необходимо выполнить следующие действия:
- создать рабочую книгу;
- сохранить рабочую книгу;
- переименовать Лист1 на Успеваемость.
- Создать таблицу в соответствии с рис.3.7 и вычислить средний балл по факультету по каждому предмету.
Рис. 3.7. Таблица успеваемости к заданию 1
3. Построить внедренную диаграмму, оформив согласно рис.3.8.
4. Построить диаграмму другого типа и разместить ее на отдельном листе.
Excel. Функции СЧЕТЕСЛИ и СУММЕСЛИ
Статья представляет собой подборку заданий для проведения итоговых уроков по изучению Excel. Задания снабжены пояснениями, которые с каждым упражнением становятся все менее подробными. Такой подход заставляет учащихся не просто выполнять предложенные упражнения, но и запоминать приемы работы, ведь иначе придется возвращаться к уже выполненной работе и разбираться с заданием заново; а также способствует простому и понятному для учащихся оцениванию их работы.
Упражнение 1. «Магазин»
В магазин «Молоко» каждый день завозят молочные продукты несколько поставщиков. Составить таблицу учета поставок за день для этого магазина. В отдельной таблице определить количество поставок молочных продуктов одного вида за день, их суммарную стоимость и среднюю цену.
Комментарии:
I. Создайте две таблицы: с исходными данными и результатами.
1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:
| A | B | C | D | E | F | |
| 1 | № | Наименование | Поставщик | Количество, л | Цена | Стоимость |
| 2 | 1 | Молоко | Поставщик № 1 | 76 | 43,00р. | |
| 3 | 2 | Кефир | Поставщик № 2 | 40 | 26,00р. | |
| 4 | 3 | Варенец | Поставщик № 3 | 86 | 34,00р. | |
| 5 | 4 | Молоко | Поставщик № 3 | 45 | 26,00р. | |
| 6 | 5 | Молоко | Поставщик № 4 | 87 | 24,00р. | |
| 7 | 6 | Варенец | Поставщик № 4 | 34 | 41,00р. | |
| 8 | 7 | Кефир | Поставщик № 4 | 67 | 32,00р. | |
| 9 | 8 | Кефир | Поставщик № 3 | 45 | 39,00р. | |
| 10 | 9 | Молоко | Поставщик № 2 | 43 | 35,00р. | |
| 11 | 10 | Молоко | Поставщик № 5 | 32 | 33,00р. |
3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».
4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:
II. Введите формулы в таблицы.
1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке
| A | B | C | D | E | F | |
| 1 | № | Наименование | Поставщик | Количество, л | Цена | Стоимость |
| 2 | 1 | Молоко | Поставщик № 1 | 76 | 43,00р. | 3 268,00р. |
| 3 | 2 | Кефир | Поставщик № 2 | 40 | 26,00р. | 1 040,00р. |
| 4 | 3 | Варенец | Поставщик № 3 | 86 | 34,00р. | 2 924,00р. |
| 5 | 4 | Молоко | Поставщик № 3 | 45 | 26,00р. | 1 170,00р. |
| 6 | 5 | Молоко | Поставщик № 4 | 87 | 24,00р. | 2 088,00р. |
| 7 | 6 | Варенец | Поставщик № 4 | 34 | 41,00р. | 1 394,00р. |
| 8 | 7 | Кефир | Поставщик № 4 | 67 | 32,00р. | 2 144,00р. |
| 9 | 8 | Кефир | Поставщик № 3 | 45 | 39,00р. | 1 755,00р. |
| 10 | 9 | Молоко | Поставщик № 2 | 43 | 35,00р. | 1 505,00р. |
| 11 | 10 | Молоко | Поставщик № 5 | 32 | 33,00р. | 1 056,00р. |
2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:
а) Для определения количества поставок нужно определить, сколько раз за день в магазин завозили, например, молоко. Для этого нужно использовать функцию СЧЁТЕСЛИ, которая определяет количество данных в диапазоне, равных критерию. Примерный вид формулы в ячейке С15:
где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).
| A | B | C | D | E | |
| 14 | № | Наименование | Кол-во поставок | Суммарная стоимость | Средняя цена |
| 15 | 1 | Молоко | 5 | ||
| 16 | 2 | Кефир | 3 | ||
| 17 | 3 | Варенец | 2 |
b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:
где F2:F11 — диапазон стоимости продукта из первой таблицы.
| A | B | C | D | E | |
| 14 | № | Наименование | Кол-во поставок | Суммарная стоимость | Средняя цена |
| 15 | 1 | Молоко | 5 | 9 087,00р. | |
| 16 | 2 | Кефир | 3 | 4 939,00р. | |
| 17 | 3 | Варенец | 2 | 4 318,00р. |
c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:
где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.
| A | B | C | D | E | |
| 14 | № | Наименование | Кол-во поставок | Суммарная стоимость | Средняя цена |
| 15 | 1 | Молоко | 5 | 9 087,00р. | 32,20р. |
| 16 | 2 | Кефир | 3 | 4 939,00р. | 32,33р. |
| 17 | 3 | Варенец | 2 | 4 318,00р. | 37,50р. |
Замечания:
Данное упражнение может быть дополнено следующими заданиями (и не только ими):
1. Определить количество (в литрах) каждого продукта, завезенного в магазин.
2. Составить таблицу «Поставщики», в которой определить, на какую сумму каждый поставщик завез в магазин продукции, общий вес привезенной каждым поставщиком продукции, и сколько видов продуктов привез каждый из поставщиков.
Упражнение 2. «Студенческие стипендии» [1]
- стипендия не назначается, если среди оценок есть хотя бы одна двойка;
- 3,0 < средний балл < 3,5 — 1000р.
- 3,5 < средний балл < 4,0 — 1200р.
- 4,0 < средний балл < 4,5 — 1500р.
- 4,5 < средний балл < 5,0 — 1800р.
- средний балл = 5,0 — 2000р.
Определить общую сумму назначенных стипендий.
Замечания:
Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.
- Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:
| № | Фамилия | Предметы | Средний балл | Стипендия | ||||
| Матем. | Физика | Информ. | Химия | История | ||||
| 1 | Алексеев | 5 | 3 | 2 | 3 | 3 | ||
| 2 | Гаврилов | 4 | 4 | 3 | 3 | 3 | ||
| 3 | Зайцев | 3 | 3 | 3 | 3 | 3 | ||
| 4 | Иванов | 4 | 3 | 5 | 3 | 3 | ||
| 5 | Краснов | 5 | 4 | 4 | 4 | 4 | ||
| 6 | Кузнецов | 4 | 5 | 5 | 5 | 5 | ||
| 7 | Петров | 5 | 5 | 5 | 5 | 5 | ||
| 8 | Сидоров | 4 | 3 | 3 | 3 | 5 | ||
| 9 | Смирнов | 4 | 4 | 4 | 4 | 4 | ||
| 10 | Солдатов | 5 | 4 | 5 | 2 | 3 | ||
- Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:
| № | Фамилия | Предметы | Средний балл | Стипендия | ||||
| Матем. | Физика | Информ. | Химия | История | ||||
| 1 | Алексеев | 5 | 3 | 2 | 3 | 3 | 3,2 | 0р. |
| 2 | Гаврилов | 4 | 4 | 3 | 3 | 3 | 3,4 | 1 000р. |
| 3 | Зайцев | 3 | 3 | 3 | 3 | 3 | 3 | 1 000р. |
| 4 | Иванов | 4 | 3 | 5 | 3 | 3 | 3,6 | 1 200р. |
| 5 | Краснов | 5 | 4 | 4 | 4 | 4 | 4,2 | 1 500р. |
| 6 | Кузнецов | 4 | 5 | 5 | 5 | 5 | 4,8 | 1 800р. |
| 7 | Петров | 5 | 5 | 5 | 5 | 5 | 5 | 2 000р. |
| 8 | Сидоров | 4 | 3 | 3 | 3 | 5 | 3,6 | 1 200р. |
| 9 | Смирнов | 4 | 4 | 4 | 4 | 4 | 4 | 1 500р. |
| 10 | Солдатов | 5 | 4 | 5 | 2 | 3 | 3,8 | 0р. |
| Итого: | 11 200р. | |||||||
Упражнение 3. «Завод железобетонных изделий»
Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.
I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.
1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
b) Столбец «Количество блоков» заполните произвольными данными.
1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.
Упражнение 4. «Прайс-лист»
Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.
Как посчитать стипендию в excel
Расчет стипендии с помощью приложения MS Excel с использованием функций
Страницы работы
Содержание работы
Задание № 7 (Excel)
Использование функций ЕСЛИ(), ИЛИ(), ВПР(), СРЗНАЧ(), СЧЕТ(), СЧЕТЗ().
Расчет стипендии с помощью приложения MS Excel.
По представленному образцу рассчитать стипендию в зависимости от результатов сессии (среднего балла).
1. Сначала необходимо повторить оформление таблицы.
§ Результаты экзаменов и зачетов (диапазон ячеек B4:F7).
§ Мин. зарплату, предварительно выбрав собственный формат числа 0,00р в текстовом окошке Тип после выполнения команды Формат ► Ячейки ► Вкл.: Число.
§ Справа данные Для расчета стипендии – Средний балл и Коэффициент.
3. В строке Ср. балл для расчета использовать функцию СРЗНАЧ() (в Категории Статистические).
4. Для подсчета студентов, сдавших экзамены, в строке Сдало использовать функцию СЧЕТ() (Категория: Статистические).
5. Для подсчета студентов, сдавших зачеты, в строке Сдало использовать функцию СЧЕТЗ() (Категория Статистические).
6. В столбце Средн. балл для студента Петрова А. расчет среднего балла подсчитывается по формуле (B4+C4+D4)/3, но только в том случае, когда сданы все экзамены и все зачеты. Для реализации этих условий в ячейке G4 можно использовать, например, такую формулу:
=ЕСЛИ(ИЛИ(B4*C4*В4=0;E4&F4<>”++”);0;(B4+C4+D4)/3)
Функции ЕСЛИ() и ИЛИ() следует выбирать из Категории Логические. Эту формулу распространить для остальных студентов.
7. Величина начисляемой стипендии (ячейка Н4) определяется с помощью функции вертикального поиска значения G4 в области J4:K8. Найденный коэффициент умножается на величину минимальной зарплаты К1. В расчетной таблице (область J4:K8) выбирается средний балл, значение которого ближе всего к значению среднего балла ячейки G4, а затем и соответствующий этому выбранному значению коэффициент. Если же средний бал в столбе G равен 0, то коэффициент сразу должен быть принят нулю. Такой алгоритм может быть реализован формулой: =ЕСЛИ(G4=0;0;ВПР(G4;J$4:K$8;2;1)*K$1). Функция ВПР() выбирается из Категории Ссылки и массивы.
8. Сумму начисленной стипендии (Н9) вычислить с помощью функции СУММ().
Пояснения к п. 7. Значения аргументов функции ВПР() означают следующее:
ВПР( ; ; ; ) или
Если =1, то поиск – приблизительный, если =0, поиск точный.
Расчет стипендии в зависимости от оценок
Здравствуйте. Помогите, пожалуйста, гуманитарию решить задачку.
Смысл в чем: посчитать размер стипендии исходя из условий.
если за все экзамены получены оценки 5, то размер стипендии 2550 р.,
если за экзамены получены оценки 4 или 5, то стипендия составляет 1700 р.,
если среди оценок за экзамены есть хотя бы одна 3, то стипендия не начисляется.
Не понимаю какую формулу нужно использовать, чтобы вот это все впихнуть в одну ячейку.
Буду очень благодарна за помощь, если хотя бы просто формулу пропишете..остальное сама сделаю.
Спасите)
Помощь в написании контрольных, курсовых и дипломных работ здесь.
| Excel_lab7.xlsx (17.3 Кб, 71 просмотров) |
Исходя из массива оценок по предметам найти студентов, имеющих право на получение стипендии
Есть задача — Написать программу, используя массив. Описание задачи: Имеем оценки m студентов по n.
Расчет стипендии
Здравствуйте такая проблема возникла! Дипломку пишу, вот такая задача появилась рассчитать.
Расчет стипендии в Excel
Помогите решить! Извините если не правильно оформил тему. Расчет стипендии. Создать таблицу.
Расчет стипендии в таблице
Ребят помогите рассчитать стипендию,последовательность значений не знаю,допускаю ошибки в.