Лабораторная работа по "Информатике"

 

 

 

 МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

Государственное образовательное учреждение высшего профессионального образования

 

ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ

 

 

 Кафедра прикладной информатики

 

 

 

Лабораторная  работа

 

по информатике

 

Вариант  10

 

 

 

 

 

 

 

 

 

 

 

                             Выполнил:

                                                                                      _________________________

                                                                                          

                                                                             Проверил:

___________________________

 

 

 

 

 

 

 

 

 

 

Ярославль, 2012

 

Практическая часть

Вариант 10

Задача. Одной из услуг предприятия ООО «Ландшафт» является сооружение альпийских горок на приусадебных участках клиентов. Стоимость выполняемых работ без учета стоимости посадочного и строительного материала зависит от вида альпийской горки и типа почвы. Данные для выполнения расчетов представлены на табл. 1 и табл. 2

Таблица 1

Стоимость выполняемых  работ за 1

, руб.

Вид альпийской горки	Обработка почвы	Сооружение горки	Посадка растений	Общая стоимость работ
«Горный склон»	785	956	578
«Горная долина»	754	874	693
«Альпийская лужайка»	722	821	785
«Водный каскад»	812	996	554
«Каменистая гряда»	923	911	541
«Японский садик»	758	873	589

 

Таблица 2

Повышающий  коэффициент для различных типов  почвы

Тип почвы	Повышающий коэффициент, КП
Суглинок	1,5
Супесь	1,6
Чернозем	1,0
Заболоченный  участок	2,1

 

Для решения задачи необходимо следующее:

1. Построить таблицы по данным, приведенным на табл. 1 и табл. 2.

2. Рассчитать общую стоимость  выполняемых работ по каждому  виду альпийских горок (табл. 1).

3. Организовать межтабличные связи с использованием функций ВПР или ПРОСМОТР для автоматического формирования стоимости работ по полученным заказам с учетом повышающего коэффициента.

4. Сформировать и заполнить таблицу  с данными по расчету стоимости  выполненных работ с учетом повышающего коэффициента по каждому заказу и по всем заказам в целом (табл. 3).

 

Доход предприятия  от выполненных работ на приусадебных участках

 

Таблица 3

№ заказа	Вид альпийской горки	Площадь, м2	Тип почвы	Стоимость работ, руб.	Стоимость работ с учетом КП, руб.
5С/2	«Горный склон»	10	Суглинок
9Д/1	«Горная долина»	12	Чернозем
7Л/6	«Альпийская лужайка»	15	Супесь
9В/1	«Водный каскад»	14	Суглинок
8Г/4	«Каменистая гряда»	17	Заболоченный участок
8Я/1	«Японский садик»	9	Супесь
Итого общая стоимость работ, руб.

 

5. Результаты расчетов  доходов от выполненных работ  по сооружению альпийских горок  на приусадебных участках по  заказам клиентов представить  в графическом виде.

 

Решение задачи с помощью программы Microsoft Excel

Порядок решения:

1. Запустить Excel. Открывается чистый лист Excel.

2. Строим таблицу по данным, приведенным на табл. 1

 

Переименуем «Лист 1» в «Стоимость выполняемых работ»:

• установим курсор мыши на ярлык  «Лист 1» (нижняя часть экрана) и нажмем правую кнопку мыши;

• выберем в контекстном меню команду «Переименовать» и нажмем левую кнопку мыши;

• наберем на клавиатуре «Стоимость выполняемых работ»;

• нажмем клавишу «Enter».

3. Вводим заголовок таблицы «Стоимость выполняемых работ за 1 , руб.»:

• сделаем ячейку A1 активной (установим курсор мыши на пересечение столбца A и строки 1 и нажмем левую кнопку мыши);

• наберем на клавиатуре «Стоимость выполняемых работ за 1 , руб.»;

 

 

 

 Форматируем заголовок:

• выделим ячейки A1÷E1 (сделаем активной ячейку A1, затем нажмите левую кнопку мыши и, не отпуская ее, перемесите курсор на ячейку E1)

на  панели инструментов в закладке «Главная»  выберем раздел «Выравнивание» и  нажмем кнопку .

 

4. Отформатируем ячейки A2÷E2 под ввод длинных заголовков:

• выделим ячейки A2÷E2;

• выполним команду «Выравнивание» в  разделе «Формат ячеек» меню «Главная»  на панели инструментов;

• выберите закладку «Выравнивание»;

• в группе опций «Отображение»  установите флажок опции «переносить по словам» (рис. 2);

 нажмем кнопку «OK».

 

 

 

 

 

 

5. Введите в ячейки A2÷E2 информацию, представленную на табл. 1.

Далее заполняем таблицу  в Excel  в соответствии с данными таблицы 1. В результате получаем следующее:

 

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

Для расчета общей  стоимости работ по виду альпийской горки «Горный склон» выделим  ячейку E3 нажмем на панели инструментов кнопку в результате выделится диапазон ячеек B3:D3 (рис 4), нажмем кнопку Enter

Для расчета стоимости  работ по другим видам альпийской горки необходимо выделить ячейку E3 левой кнопкой мыши потянуть за правый нижний угол ячейки до автоматического заполнения ячейки E8. В результате получаем общие стоимости работ по каждому виду альпийских горок за 1 м² (рисунок 5)

 

Построим таблицу по данным, приведенным  на табл. 2

 

Построение таблицы будем проводить на листе №2 нашей рабочей книги Excel.

2) Введем условие реализации запасов продукции в терминалах, т.е.

где - запас продукции i терминала (поставщик);

- объем заказов от i –го терминала (поставщик) к j -му салону (потребителю);

n – количество салонов магазинов (потребителей).

 

Выполняются следующие операции:

- курсор в ячейку В4,

- щелкнуть знак «  »,

-выделить ячейки C4:E4,

- нажать ENTER.

Аналогичные действия для  ячеек В5,В6.

3) Введем условие удовлетворения запросов магазинов:

,

где b –спрос j-го салона

m –количество поставщиков.

Для этого:

- курсор в С7;

-щелкнуть знак«  »,

-выделить ячейки С4:С6,

- нажать ENTER.

Аналогичные действия для  ячеек D7-E7 (Рис_2).

Рис 2

 

 

 

 

4) В таблицу Исходные данные вводим исходные данные

Вводим запасы продукции в терминалах (ячейки В12:В14), потребности салонов-магазинов в их продукции (ячейки C11-E11), а также стоимость перевозки одного заказа для каждой возможной перевозки (блок C12-E14) (Рисунок 3).

Рис. 3

5) Назначаем целевую функцию - математическая запись критерия оптимальности. Для вычисления значения целевой функции, соответствующей минимальным суммарным транспортным издержкам на доставку заказов в салоны магазины, необходимо выбрать ячейку и ввести формулу для её вычисления:

где – стоимость перевозки одного заказа от i-го терминала (поставщика) к j-му салону (потребителю);

        – объем заказов от i –го терминала (поставщик) к j -му салону (потребителю);

 

Для этого:

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

•  установить курсор на кнопку «Мастер функций», расположенную на панели инструментов. Щелчком левой кнопки мыши открыть «Мастер функций». На экране появляется диалоговое окно Мастер функций шаг 1 из 2. В окне «Категории» выбираем – Математические, в окне «Функции» выбираем – СУММПРОИЗВ  (Рис. 4). → «ОК».

                                                   Рис. 4                                               

 

•  На экране появляется диалоговое окно СУММПРОИЗВ

•  в строку «Массив 1» ввести С12:E14; адреса ячеек удобнее вводить не с клавиатуры, а протаскивая мышь по указанным ячейкам.

•  в строку «Массив 2» ввести C4:E6;

•  нажать кнопку «ОК».

Рис. 5

В поле ячейки F16 появилось числовое значение, равное произведению единичных поставок на коэффициенты затрат по доставке заказов (Рис. 6).

Рис. 6

 

6. Осуществляем ввод зависимостей из математической модели:

Для осуществления этого  этапа выполним ряд операций:

• запустим команду Поиск решения;

На панели инструментов выбрать Сервис→ команда Поиск решения → ОК. Появляется диалоговое окно Поиск решения (Рис. 7).   

•  установить курсор в строку  Установить целевую ячейку;

• ввести адрес ячейки $F$16, щелкнув по соответствующей ячейке рабочего листа;

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

•  установить курсор в строку Изменяя ячейки;

•  ввести адреса искомых  переменных $C$4:$E$6 (Рис. 7).   

Рис. 1.7

Ограничения:

• установить указатель  мыши на кнопку Добавить и щелкнуть мышью по ней. Появляется диалоговое окно Добавление ограничения (Рис. 8);

•  в строке Ссылка на ячейку ввести $B$4:$B$6;

•  ввести знак ограничения =;

•  в строке Ограничение ввести $B$12:$B$14;

• установить указатель  мыши на кнопку Добавить и щелкнуть мышью по ней. На экране вновь появляется диалоговое окно Добавление ограничения (рис. 1.8).

Рис_8

•  вводим остальные  ограничения задачи, по вышеописанному алгоритму;

•  после введения последнего ограничения нажать кнопку ОК.

На экране появится диалоговое окно Поиск решения с введенными условиями

Рис_9

7. Вводим параметры для решения задачи:

• в диалоговом окне Поиск решения установить указатель мыши на кнопку Параметры и щелкнуть мышью по ней. На экране появляется диалоговое окно Параметры поиска решения (Рис. 10);

•  установить флажки в окнах Линейная модель (это обеспечит применение симплекс-метода) и Неотрицательные значения (Рис. 10);