сделать по заданиям в ексель практическая самостаятельная отдельно каждое задание с нового листа все подробно описано пользователю ексель легко дастся каждое задание с нового листа

ПРАКТИЧЕСКАЯ РАБОТА

Загрузить MS Excel.

Сохранить Рабочую книгу под именем Занятие 4-2 на Рабочий стол.

Переименовать листы:

лист - Прогрессия;

лист - Поощрение;

лист - Довольствие;

лист – Отбор в секцию;

лист – Итоги зачисления;

На Листе Прогрессия создать таблицу по представленному образцу:

1 лист - Прогрессии

Надписи в заголовке таблицы развернуть под углом 450 (Главная Диалоговое окно группы Выравнивание Ориентация – 45º Выравнивание – по центру, перенос по словам).

Ячейки таблицы заполнить, используя команды: Главная Редактирование Заполнить Прогрессия выбрать Тип и Шаг в соответствии с заголовком столбца таблицы.

Готовую таблицу обвести двойной рамкой синего цвета по внешнему контуру и красного цвета по внутреннему (Главная→ Шрифт Граница Другие границы → Тип линии Цвет <Внешние> или <Внутренние> ОК)

2 лист - Поощрение

Рассмотрим использование логических функций на практических примерах.

Известны итоги сдачи экзаменационной сессии студентами 1 курса. Поощрения за успехи в учебе получают студенты, у которых средний балл по итогам экзаменов выше 4,50. Составить ведомость на поощрение курсантов.

Решение:

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

На рабочем листе создайте таблицу по образцу, представленному на рисунке ниже.

Вычисление среднего балла будем вести с точностью до сотых долей. Для реализации этого условия необходимо использовать вложенную функцию, которая будет состоят из двух функций ОКРУГЛ – округление значения и СРЗНАЧ – вычисление среднего арифметического значения чисел в диапазоне.

Ввод формулы:

Установить курсор в ячейку F4;

Выполнить команду Вставить функцию →Математические →ОКРУГЛ;

В окне Аргументы функции ОКРУГЛ установить курсор в поле Число, затем выбрать в поле Функция, расположенном слева от строки формул, с помощью выпадающего списка функцию СРЗНАЧ (если в выпадающем списке данная функция отсутствует, выбрать Другие функции…, в открывшемся окне Вставка функции в Категории Статистические найти функцию СРЗНАЧ);

В окне Аргументы функции СРЗНАЧ, установить курсор в поле Число1, в таблице выделить диапазон С4:Е4, затем в строке формул установить курсор в названии функции ОКРУГЛ, так будет осуществлен переход в окно аргументов функции ОКРУГ, в этом окне в поле Число разрядов указать 2, нажать ОК.

С помощью маркера автозаполнения копировать формулу для расчета среднего балла в диапазоне F4:F13;

Теперь с помощью логической функции ЕСЛИ заполним столбец Получение поощрения. Установите курсор в ячейку G4. Выполните команду

Вставить функцию →Логические →ЕСЛИ;

В окне Аргументы функции ЕСЛИ в поле Лог_выражение введите F4>=4,50, в поле Значение_если_истина – ДА, в поле Значение_если_ложь – НЕТ (cм. Рисунок ниже), нажмите ОК.

С помощью маркера автозаплнения копировать формулу для расчета среднего балла в диапазоне G4:G13.

Подсчет количества поощренных выполним при помощи статистической функции СЧЕТЕСЛИ, которая осуществляет подсчет количества ячеек в заданном диапазоне, значения в которых соответствуют определенному условию.
Установить курсор в ячейку G14, выполнить команду Вставить функцию →Статистические →СЧЕТЕСЛИ;

В окне Аргументы функции СЧЕТЕСЛИ указать в полях аргументов их значения, как показано на рисунке ниже, нажать ОК.

3 Лист - Довольствие

На Рабочем листе самостоятельно сформировать таблицу, содержащую сведения о назначении курсантов на усиленное питание. Усиленное питание назначается для курсантов, рост которых превышает 190 см. Образец таблицы представлен ниже.

Для автоматического заполнения столбца Усиленное питание необходимо использовать логическую функцию ЕСЛИ. Для заполнения ячейки Всего на усиленном питании использовать функцию СЧЕТЕСЛИ().

4 лист – Отбор в секцию

На Рабочем листе сформировать таблицу, содержащую сведения по результатам отбора учеников в спортивную секцию (см. образец). Таблица содержит следующие данные об учениках: фамилия, возраст, рост.

Сколько учеников могут заниматься в баскетбольной секции, если туда принимают с ростом не менее 160 см, возраст не должен превышать 13 лет.

Создать таблицу, представленную на образце.

Для заполнения столбца Зачислен используем логическую функцию ЕСЛИ. Поскольку зачисление в секцию предполагает выполнение двух условий: рост >= 160 см. И возраст <=13 лет, то первый аргумент функции ЕСЛИ должен включать логическое выражение, описывающие одновременное выполнение этих условий. Для записи такого логического выражения будем использовать логическую функцию И, содержащую два аргумента: И(рост >= 160 см.; возраст <=13 лет). Итоговая формула примет вид:

=ЕСЛИ(И(В4>=$B$1;C4<=13);«ДА»;«НЕТ»).

Для ввода формулы:

Установить курсор в ячейку D4;

Нажать кнопку Вставить функцию→Логические→ЕСЛИ;

В окне Аргументы функции ЕСЛИ установить курсор в поле Лог_выражение, затем в поле Функция, расположенном слева от строки формул, с помощью выпадающего списка выбрать функцию И;

Теперь вы перешли в окно Аргументы функции И. В данном окне необходимо ввести аргументы функции И: в поле Логическое_значение1 вводим выражение В4>=$B$1, в поле Логическое_значение2 – выражение C4<=13. Первый аргумент функции ЕСЛИ записан;

Для продолжения записи формулы щелкните в слове ЕСЛИ в строке формул, вы должны вернуться в окно Аргументы функции ЕСЛИ. Если все действия выполнены правильно, в поле Лог_вырадение будет записана формула - 

И(В4>=$B$1;C4<=13);

В поле Значение_если_истина введите «ДА», в поле Значение_если_ложь – «НЕТ». Нажмите ОК.

С помощью маркера автозаполнения скопируйте, полученную формулу на весь диапазон.

5 лист – Итоги зачисления

Самостоятельно разработать и заполнить таблицу, содержащую сведения об абитуриентах: фамилия, оценки за экзамены по математике, русскому языку и обществознанию, сумму баллов за три экзамена и итог зачисления. Образец таблицы представлен ниже. Столбцы Фамилия и Оценки по дисциплинам заполнить самостоятельно на 10 человек (для заполнение столбца Фамилия можно воспользоваться данными с Листа 2). Столбец Сумма баллов заполняется при помощи функции СУММ(). Для заполнения столбца Итог зачисления использовать функцию ЕСЛИ(). Условия зачисления: если сумма баллов больше или равна проходному баллу и оценка за экзамен по математике – 4 или 5, то абитуриент ЗАЧИСЛЕН в учебное заведение, в противном случае – НЕТ.

Дополнительно:

В задании №2 Поощрения создать дополнительный столбец с именем Категория успеваемости, в котором указать к какой категории относится каждый студент по принципу:

Если средний балл успеваемости >=4.5 – это «отличник»;

Если средний балл успеваемости >=3.5 – это «хорошист»;

Если средний балл успеваемости >=2.5 – это «успевающий»,

иначе – «неуспевающий».

Для реализации задания используется вложенная функция ЕСЛИ. В результирующей таблицы с помощью инструмента Условное форматирование создать правила для заливки категорий студентов по успеваемости (заливка применяется ко всей строке таблицы):

Отличники – заливка ячеек строки – оттенками красного, оранжевого, розового (на ваш выбор);

Хорошисты – заливка ячеек строки – оттенки зеленого;

Успевающие - заливка ячеек строки – оттенки синего, голубого, фиолетового;

Неуспевающие – заливка ячеек строки- оттенки серого цвета.

Создать таблицу итогов, в которой выполнить подсчет количества студентов в каждой категории успеваемости образец таблицы представлен ниже. Для подсчета использовать функцию СЧЕТЕСЛИ.

ЗАДАНИЕ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ

ЗАДАНИЕ 1

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

в каких месяцах был начислен штраф;

какой штраф был уплачен предприятием в каждом месяце;

месяцы, когда был начислен максимальный штраф;

сколько месяцев выплачивался штраф и суммарный штраф за год.

Исходными данными являются месяцы работы предприятия, количество отходов за каждый месяц, а также наличие арендованных помещений на предприятии.

РЕКОМЕНДАЦИИ ДЛЯ ВЫПОЛНЕНИЯ ЗАДАНИЯ:

В столбец Начислен штраф с помощью логической функции размещается обозначение, например 1- если штраф начислен, ноль – в противном случае (можно использовать обозначения «да» или «нет»);

Столбец Месяцы, когда выплачивался штраф должен содержать название месяцев, в которые выплачивался штраф и пустые ячейки, для тех месяцев, в которые у предприятия штрафа не было.

Для выполнения задания использовать функции: ЕСЛИ(), СУММ(), СЧЕТЕСЛИ().

ЗАДАНИЕ 2

Разработать ведомость начисления стипендии студентам 3 курса по результатам сдачи

экзаменационной сессии. Условия начисления стипендии:

При начислении стипендии учитывается результаты сдачи экзаменов и зачетов с оценкой;

Минимальный размер стипендии 2300 руб.;

Студенты, сдавшие все экзамены и зачеты с оценкой и имеющие средний балл по результатам сдачи выше 4,6 получают надбавку в размере 30% от минимальной стипендии;

Студенты, сдавшие все экзамены и зачеты с оценкой и имеющие средний балл по результатам сдачи от 4,0 до 4,6 получают минимальную стипендию;

Студенты, сдавшие все экзамены и зачеты с оценкой и имеющие хотя бы одну оценку 3 стипендию не получают.

РЕКОМЕНДАЦИИ ДЛЯ ВЫПОЛНЕНИЯ ЗАДАНИЯ:

Для выполнения задания создать новую Рабочую книгу, присвоить ей имя «Начисление стипендии»;

В Рабочей книге «Начисление стипендии» создать четыре рабочих листа и именами:

1 Лист - Экзамены;

2 Лист – Зачеты с оценкой;

3 Лист – Итоги сдачи сессии;

4 Лист – Ведомость начисления стипендии.

На листе разработать таблицу «Ведомость сдачи экзаменов» по образцу, представленному ниже.

При выставлении итоговой оценки на экзамене учитывается работа на семинарах и самостоятельных работах в течение семестра, а также результаты компьютерного экзамена по лекционному курсу. За работу в течение семестра на семинарах студент может набрать максимально 170 баллов. За компьютерный тест на экзамене студент может набрать максимально 80 баллов. Итоговая оценка выставляется по сумме баллов, причем: если студент набрал не менее 85% от максимальной суммы баллов (в данном случае максимальная сумма баллов равна 250), он получает оценку 5 (отлично).

Если сумма больше либо равна 65% от 250 баллов, но менее 85% от 250, то студент

получает 4 (хорошо).

Если сумма больше либо равна 40% от 250, но менее 65% от 250, то студент получает 3 (удовлетворительно), и если сумма баллов меньше 40% от 250, то студент получает оценку 2 (неудовлетворительно).

На Рабочем листе экзамены разместить ведомости сдачи экзаменов по дисциплинам: «Чрезвычайные ситуации и методы защиты», «Промышленная санитария и гигиена труда», «Организация системы гражданской обороны». Баллы за работу в течении семестра и экзаменационное тестирование для неуказанных дисциплин проставить произвольно.

1 Лист – Зачеты с оценкой

На листе разработать таблицу «Ведомость сдачи зачета с оценкой» по аналогии с экзаменационными ведомостями (Лист Экзамены) для дисциплин «Информационные технологии в управлении БЖД» и «Производственная безопасность». Для быстроты работы воспользоваться копированием.

При выставлении итоговой оценки на зачете с оценкой учитывается работа в течение семестра и результат выполнения зачетного задания (в баллах). За работу в течение семестра на семинарах студент может набрать максимально 120 баллов. За выполнение задания на зачете студент может набрать максимально 30 баллов. Итоговая оценка выставляется по сумме баллов, причем:

если студент набрал не менее 90% от максимальной суммы баллов (в данном случае максимальная сумма баллов равна 150), он получает оценку 5 (отлично).

Если сумма больше либо равна 70% от 150 баллов, но менее 90% от 150, то студент получает 4 (хорошо).

Если сумма больше либо равна 50% от 150, но менее 70% от 150, то студент получает 3 (удовлетворительно), и если сумма баллов меньше 50% от 150, то студент получает оценку 2 (неудовлетворительно).

3 Лист – Итоги сдачи сессии

На листе разработать таблицу «Итоги сдачи сессии» по образцу, представленному ниже.

Для подсчета количества оценок использовать функцию СЧЕТЕСЛИ(). Поскольку итоговая таблица с оценками находится на отдельном листе, при указании диапазона подсчета оценок необходимо переключаться между разными рабочими листами. Это делается щелчком левой кнопки мышки на ярлыке рабочего листа. Адрес диапазона подсчета будет начинаться с названия листа, на котором находится диапазон и непосредственно самого адреса диапазона.

4 Лист – Ведомость начисления стипендии

На листе разработать таблицу начисления стипендии по образцу, представленному ниже.

Для начисления стипендии за основу принять рекомендации, сформулированные в пункте 1-5 начала задания.