Лабораторных работ

Самостоятельная работа студентов – 45 ч.

№ п/п	Наименование работы	Объем, в час.
	Подготовка к лабораторным занятиям
	Подготовка к контрольным работам
	Индивидуальные домашние задания
	Подготовка к рубежным контролям и экзамену

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ

ЛАБОРАТОРНЫХ РАБОТ

Данные методические указания разработаны с целью оказания методической помощи при выполнении студентами самостоятельных работ по дисциплтине «Программное обеспечение ЭИС». Помимо курса лабораторных занятий дополнительный материал по самостоятельным работам позволит вооружить студентов мощным вычислительным аппаратом, необходимым в профессиональной деятельнолсти и быть конкурентноспособными на рынке труда.

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

В основе рассматриваемых методов лежит математический и логический аппарат.

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

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

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

РАБОТА С КНИГАМИ И ЛИСТАМИ В MS EXCEL. ВЫЧИСЛЕНИЯ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL.

Цель: Изучить основы работы с табличным процессором в MS Excel.

ЗАДАНИЕ 1. СОЗДАНИЕ ДОКУМЕНТА В MS EXCEL СО СВЯЗАННЫМИ, С ПОМОЩЬЮ ОБЪЕМНЫХ ССЫЛОК, ЛИСТАМИ.

1.1 Виды адресов ячеек

Адрес ячейки таблицы Excel называется относительной, если при копировании изменяется (A1).

Адрес ячейки таблицы Excel называется абсолютной, если при копировании не изменяется ($A$1).

Адрес ячейки таблицы Excel называется смешанной, если при копировании изменяется частично(A$1, $A1).

Рисунок 1.1 Механизм изменения адресов ячеек при копировании формулы

1.2 Создание документа в MS Excel со связанными, с помощью объемных ссылок, листами

Связанные листы. Листы рабочей книги Excel называются связанными, если один лист (итоговый) содержит ссылку на другой лист (исходный). Ссылка с одного листа на другой в общем имеет следующий вид: =Имя листа!Адрес ячейки

Например, на листе “Ведомость” содержится формула со ссылкой на ячейку B12 листа “Данные”. Данные листы будут являться связанными, и их связанность в дальнейшем будет проявляться в том, при изменении данных в ячейке B12 листа “Данные”, данные в ячейке I21 листа “Ведомость” автоматически изменятся.

Рисунок 1.2 Связанные листы рабочей книги Excel

Объемная ссылка. В объемной ссылке производится обращение к данным, расположенным на нескольких листах рабочей книги Excel, образующих непрерывный диапазон. Формула с использованием объемной ссылки, в общем, имеет вид =Имя функции(Имя листа:Имя листа!Адрес ячейки)

Например: =СУММ(Лист1:Лист5!G15)

При выполнении данной формулы будет вычислена сумма данных, расположенных в ячейке G15 на листах, начиная с “Лист1” по ”Лист5”.

При использовании объемных ссылок в итоговых документах необходимо учесть следующие требования:

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

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

Пример применения объемных ссылок.

На нескольких, образующих непрерывный диапазон, листах рабочей книги Excel создадим документы, содержащие данные различных типов и формульные выражения, т.е. документы, в которых производятся какие-либо расчеты. Данные на этих листах должны быть расположены в одних и тех же диапазонах ячеек, иметь одинаковую структуру, но различаться исходными данными. На следующем, после последнего листа, листе рабочей книги создадим итоговый документ, в котором подытоживаются данные с предыдущих листов. Итоговые данные вычислим с помощью формул с использованием объемных ссылок.

В данном примере на листах рабочей книги Excel производится учет оплаты за обучение на различных курсах различными категориями слушателей.

Рисунок 1.3 Данные рабочего листа “январь”

Данные в столбце D (стоимость для льготников) рассчитываются на основе данных со столбца C (базовая стоимость) с учетом процентной ставки, значение которой расположено в ячейке D17.

Данные в столбце G (общая сумма денег) рассчитываются с учетом количества всех категорий слушателей и стоимостей.

На листах “февраль”, “март” расположены документы, аналогичные документу на листе “январь”, но с данными, соответствующими календарному месяцу.

Документ на листе “квартал” представляет собой свод данных с 3-х предыдущих листов.

Рисунок 1.4 Формулы рабочего листа “январь”

Рисунок 1.5 Итоговый документ на листе “Квартал”

1.3 Варианты индивидуальных заданий

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

Итоговый документ должен представлять общие данные по всем торговым точкам.

№	Наименование товара	Опт.ст-ть (тг.)	Розн.ст-ть (тг.)	Количество (шт.)	Сумма(тг)
…	…	…	…	…	…

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

№	Наимено-вание секции	Ст-ть абон-та (тг.)	Льгот. ст-ть абон-та (тг.)	Колич-во спорт-в (чел.)	Колич-во спорт-в– льготников (чел.)	Сумма(тг)
…	…	…	…	…		…

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

№	Вид путевки	Ст-ть путевки (тг.)	Льгот. ст-ть путевки (тг.)	Колич-во отдых-х (чел.)	Колич-во отдых-х– льготников (чел.)	Сумма(тг)
…	…	…	…	…		…

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

Итоговый документ должен представлять общие данные по всем пунктам реализации.

№	Наименование издания	Опт.ст-ть (тг.)	Розн.ст-ть (тг.)	Количество (шт.)	Сумма(тг)
…	…	…	…	…	…

Вариант 5: Создать документ, позволяющий учитывать прибыль от реализации автомобилей в различных автосалонах. Исходными данными являются стоимость 1 автомобиля по ценам производителя, процентная ставка для рыночной стоимости, кол-во реализованных автомобилей. Сумма должна быть вычислена с учетом курса ин. валюты. Итоговый документ должен представлять общие данные по всем автосалонам.

№	Марка автомобиля	Заводская ст-ть(у.е.)	Рыночная ст-ть(у.е.)	Количество (шт.)	Сумма(тг)
…	…	…	…	…	…

Вариант 6: Создать документ, позволяющий учитывать приход денежной суммы в кассу библиотеки за счет проката литературы в различных отделах библиотеки. Исходными данными являются стоимость проката 1 экземпляра издания в данном отделе, величина пени за удержание на руках читателя 1 экземпляра литературы больше указанного срока (тг.за 1 сутки), количество взятых на прокат экземпляров литературы, количество экземпляров, прокат которых превысил выделенный срок. Итоговый документ должен представлять общие данные по всем отделам библиотеки.

№	Наименование отдела библиотеки	Ст-ть проката 1 экз. (тг.)	Величина пени (1 экз. за 1 сутки)	Кол-во эз-в	Кол-во экз-в, удерж-х сверх срока	Сумма(тг)
…	…	…	…	…		…

Вариант 7: Создать документ, позволяющий учитывать сумму денег, выплаченную рабочим строительной организации за работу выполненную на различных строительных объектах. Исходными данными являются объем выполненной работы, вид работы, ст-ть данного вида работы на 1 ед. объема, коэффициент трудового участия, общая сумма удержаний за питание, услуги транспорта и пр.

Итоговый документ должен представлять общие данные по всем строительным объектам.

№	Фамилия рабочего	Вид работы	Объем выполненной работы	Ст-ть 1 ед. данного вида работы	КТУ	Удер-жания (тг.)	Начис-ления (тг)
…	…	…	…	…			…

Вараинт 8: Создать документ, позволяющий учитывать сумму денег, поступивших в кассу лечебного учреждения вследствие оплаты за услуги стационарного лечения различными категориями больных в различных отделениях за различные месяцы. Исходными данными являются стоимость 1 курса стационарного лечения, процентная ставка для льготной стоимости, количество пациентов, оплачивающих лечение на общих основаниях, количество пациентов-льготников. Итоговый документ должен представлять общие данные по всем отделениям стационара.

№	Отделение стационара	Ст-ть стац-го лечения (тг.)	Льгот-я ст-ть лечения (тг.)	Кол-во пац-в (чел.)	Кол-во пац-в-льгот-в (чел.)	Сумма оплаты (тг)
…	…	…	…	…		…

Вараинт9: Создать документ, позволяющий учитывать общую сумму оплаты за обучение студентами-договорниками высшего учебного заведения. Исходными данными являются стоимость 1 года обучения, процентная ставка для льготной стоимости обучения, количество студентов, оплачивающих обучение на общих основаниях, количество студентов-льготников.Итоговый документ должен представлять общие данные за весь учебный год.

№	Специаль-ность	Ст-ть 1 года обучения (тг.)	Льгот-я ст-ть обучения (тг.)	Кол-во студентов (чел.)	Кол-во студ-в-льгот-в (чел.)	Сумма оплаты (тг)
…	…	…	…	…		…

Вариант10: Создать документ, позволяющий учитывать общую сумму денег, заработанную организацией за счет выполненных услуг-грузоперевозок. Исходными данными являются объем груза, стоимость перевозки 1 ед. груза на 1 км., длительность маршрута (в км.), расход бензина на 1 км., стоимость 1 литра бензина.

Итоговый документ должен представлять общие данные за несколько периодов времени (например за несколько месяцев).

№	Номер автомобиля	Объем груза (тонна)	Ст-ть перевозки 1 т. груза	Длитель-ность маршрута (км.)	Расход бензина на 1 км.	Ст-ть 1 литра бензина	Сумма оплаты (тг)
…	…	…	…	…			…

1.4 Контрольные вопросы

1) Какие листы в рабочей книге Excel называются связанными?

2) Как проявляется связанность листов?

3) Какая формула называется объемной?

4) Какие требования предъявляются к документу при использовании объемных ссылок?

ЗАДАНИЕ 2. ДАННЫЕ ЛОГИЧЕСКОГО ТИПА. ФУНКЦИЯ ВЫБОРА ЕСЛИ

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

Выражение называется логическим, если в нем присутствуют знаки операций отношения и логические функции:

> -“больше”, < -“меньше”, >= -“больше или равно”,

<= -“меньше или равно”, = -“равно”, <> -“не равно”

=И(арг1;арг2), =ИЛИ(арг1;арг2), =НЕ(арг)

Значения логических функций определяются в зависимости от значений аргументов.

Таблица 2.1 Таблица истинности для функций НЕ, И, ИЛИ

Арг1	арг2	=НЕ(арг1)	=И(арг1;арг2)	=ИЛИ(арг1;арг2)
И	И	Л	И	И
И	Л	Л	Л	И
Л	И	И	Л	И
Л	Л	И	Л	Л

2.2 Функция выбора ЕСЛИ

Функция ЕСЛИ позволяет выбрать одно из нескольких действий в зависимости от значений условия.

В общем виде формат функции имеет следующий вид:

=если(усл1;действ1;если(усл2;действ2;…;если(усл n-1; действ n-1; действ n)…)

При выполнении данной функции проверяются условия, и будет выполнено то действие, которое соответствует истинному условию, если ни одно условие не оказалось истинным, то выполняется действие n.

2.3 Контрольное задание

Значение логического выражения.

Определим порядок действий в следующем логическом выражении

Рисунок 2.1 Логическое выражение в Excel

9 4 1 3 2 8 5 7 6

=И(ИЛИ(B2>-2;НЕ(B2+D2<>4));ИЛИ(B2>D2-4;НЕ(D2<0)))

По вышеуказанному порядку выполнив действия получим:

1. B2>-2 – ИСТИНА; 2. B2+D2<>4 – ИСТИНА;

3. НЕ(B2+D2<>4) – ЛОЖЬ 4. ИЛИ(B2>-2;НЕ(B2+D2<>4) - ИСТИНА;

5. B2>D2- 4 - ИСТИНА; 6. D2<0 – ЛОЖЬ;

7. НЕ(D2<0) – ИСТИНА; 8. ИЛИ(B2>D2-4;НЕ(D2<0)) – ИСТИНА

9. И(ИЛИ(B2>-2;НЕ(B2+D2<>4));ИЛИ(B2>D2-4;НЕ(D2<0))) – ИСТИНА

Значение функции ЕСЛИ.

Рисунок 2.2 Пример функции ЕСЛИ

Если значением ячейки A1 будет являться число 7, а ячейки B1 - 14, то значением данной функции будет число 2.

Если значением ячейки A1 будет являться число 10, а ячейки B1 - 6, то значением данной функции будет число 16.

Если значением ячеек А1 и В1 будут являться число 5, то значением данной функции будет число 25.

2.4 Варианты индивидуальных заданий

Вариант1:

а) Определить значение логического выражения

=И(НЕ(A2<3);ИЛИ(И(A2+4>5;B5<=6);НЕ(A2+B5<0)))

при a) A2: -5; B5: 6 б) A2: 3; B5: -1

б) Протабулировать функцию, построить график функции.

в) Создать документ (для принятия решения использовать функцию если)

№	Наименование товара	Дата выпуска	Срок хранения	Решение
	Макароны	07.05.03		=если(…
…	……	…	…	…

Варианты решения:

-если срок годности истек – снять с продажи;

-если до окончания срока годности осталось меньше 1 года – уценить на 30%;

-если до окончания срока годности остался 1 год и более – реализовать при нормальных условиях;

Использовать функцию =СЕГОДНЯ()

Вариант2:

а) Определить значение логического выражения:

=И(НЕ(3*(A2+C4)/2<-4);ИЛИ(A2>C4*2;НЕ(C4<7)))

при a) A2: -5; C4: 6 б) A2: 3; C4: -1

б) Протабулировать функцию, построить график функции:

в) Создать документ (для принятия решения использовать функцию если

№	Фамилия И.О.	Дата получения книги	Решение
	Иванов М.Н.	05.10.03	=если(…
…	……	…	…

Варианты решения:

-если читатель задержал книгу больше, чем на 10 дней, то –щтраф;

-если до сдачи книги остался 1 день – предупредить читателя о возврате книги;

-если до сдачи книги осталось больше 1 дня, то подсчитать количество оставшихся дней до сдачи книги;

Использовать функцию СЕГОДНЯ.

Вариант3:

а) Определить значение логического выражения:

=ИЛИ(И(C5>7;НЕ(B4<>0));НЕ(С5+B4<15))

при a) B4: -2; C5: 3 б) B4: 7; C5: -4

б) Протабулировать функцию,

построить график функции:

в) Создать документ (для принятия решения использовать функцию если)

№	Фамилия И.О.	Срок лечения (дней)	Дата поступления	Решение
	Рахимбаев А.Б.		15.03.04	=если(…
…	……	…		…

Варианты решения:

-если срок лечения истек, то ВЫПИСАТЬ;

-если до окончания срока лечения осталось 2 дня, то ПОДГОТОВИТЬ К ВЫПИСКЕ;

-если до окончания срока лечения более 2-х дней, то ПРОДОЛЖАТЬ ЛЕЧЕНИЕ.

Использовать функцию СЕГОДНЯ.

Вариант 4:

а) Определить значение логического выражения:

=И(ИЛИ(С3>8;НЕ(D2+4<>6));НЕ(C3+D2/2<12))

при a) C3: -5; D2: 4 б) C3: 6; D2: -2

б) Протабулировать функцию, построить график функции:

в) Создать документ (для принятия решения использовать функцию если)

№	Фамилия И.О. студента	Экзаменационные оценки	Решение
Математика	Физика	История
	Ахметов А.С.				=если(…
…	……	…			…

Варианты решения:

-если средний балл равен 5, то ПОВЫШЕННАЯ СТИПЕНДИЯ;

-если средний балл меньше или равен 4, то СТИПЕНДИЯ БЕЗ НАДБАВКИ;

-если средний балл меньше 4, то НЕ ВЫПЛАЧИВАТЬ СТИПЕНДИЮ;

Использовать функцию СЕГОДНЯ.

Вариант 5:

а) Определить значение логического выражения:

=И(ИЛИ(НЕ(A2+C1<>10));НЕ((A2/2)<14))