Лабораторная работа 1 Основы работы в Excel Цель работы научить студента открывать существующие книги, сохранять книги на новом месте и под новыми именами, работать с листами книги,
 Скачать 1.3 Mb.
|
|
, 1 заведующийаптекой, 1 главныйврач, 1 завхоз, 1 заведующийбольницей. При расчете заработной платы за основу берется минимальный оклад. Оклады вычисляются последующей схеме санитарка получает минимальный оклад, медсестра должна получать в 1.5 раза больше санитарки, врач – в 3 раза больше санитарки, заведующий отделением – на 300 руб. больше, чем врач, заведующий аптекой – в 2 раза больше санитарки, завхоз – на 400 руб. больше медсестры, главврач – в 4 раза больше санитарки, заведующий больницей – на 200 руб. больше главврача. Используя поиск решения, подсчитать количество санитарок, медсестер, врачей, если минимальный оклад составляет 1 000 руб. 3.1. Задать исходные данные. Количество санитарок, медсестер и врачей задать равным 0. В столбец Оклад занести формулы для вычисления окладов персонала, исходя из оклада санитарки, значение которого хранится в ячейке С. Ячейкам В2:В4 присвоить соответствующие имена. Ячейке С присвоить имя Оклад. 3.2. В ячейку В занести формулу для вычисления фонда заработной платы. Присвоить ячейке имя Зарплата. 3.3. Используя Поиск решения, вычислить количество санитарок, медсестер и врачей. В диалоговом окне a) в качестве изменяемых ячеек задать В2:В4 и С, b) учесть ограничения на количество сотрудников и минимальный оклад, c) обеспечить максимальный фонд зарплаты (нос ограничением размера максимального фонда. d) Сохранить решение в сценарии с именем Оклад_1000р. 3.4. Изменить минимальный оклад на 1300. Повторить поиск решения и сохранить в сценарии с именем Оклад_1300р. 61 3.5. Изменить минимальный оклад на 1350. Выполнить поиск решения и сохранить в сценарии с именем Оклад_1350р. 3.6. Создать отчет Задание 4 Решение системы нелинейных уравнений Найти координаты точек пересечения параболы с окружностью, решая систему нелинейных уравнений с использованием поиска решения, выполняя действия. 9 ) 1 ( 4) - y ( 2 4x - y 2 2 2 x 4.1. Построить графики параболы, верхней и нижней полуокружностей на интервале [-2,4]: a) выразить из каждого уравнения y, учитывая, что во втором случае будет два выражения для y, b) построить таблицу трех функций на интервале [-2, 4], c) построить три точечные диаграммы. 4.2. Найти на диаграммах приближенно координаты точек пересечения. 4.3. Привести исходную систему уравнений к виду 0 9 ) 1 x ( ( 0 2 2 2 2 4) - y 4x - y 4.4. Занести координату х первой точки в ячейку А, а координату y – в ячейку А. Дать ячейкам именах, у. 4.5. В ячейку В занести левую часть первого уравнения, ссылаясь на ячейки А8:А9, а в ячейку В занести левую часть второго уравнения. 4.6. В ячейку В занести целевую функцию – сумму квадратов левых частей уравнения. Присвоить ячейке имя Критерий. Замечание 1: Система уравнений, приведенных к виду f(x)=0, имеет решение, если сумма квадратов левых частей этих уравнений равна 0. Замечание 2: Для вычисления суммы квадратов использовать функцию СУММКВ. 62 4.7. Выполнить поиск решения для каждой точки пересечения, изменяя начальные приближения в ячейках А8:А9. Результаты сохранять в сценариях с именами Точка, Точка, Точка, Точка. 4.8. Вывести итоговый отчет в виде Структура сценария Точка1 Точка2 Точка3 Точка4 Изменяемые: x -0.96621314 -1.396799742 0.866456277 1.496533426 y 1.734372366 5.804258287 1.002973977 6.958534585 Результат: Критерий 8.88768E-07 3.63477E-09 1.43835E-10 Контрольные вопросы 1. Назначение поиска решения. 2. Выполнение поиска решения. 3. Использование сценариев для многовариантных расчетов. 4. Общая постановка задач оптимизации. Виды задач оптимизации линейная, нелинейная. 5. Решение задач оптимизации с использованием поиска решения. Лабораторная работа № 13 Решение систем линейных алгебраических уравнений Цель работы научить студента решать системы линейных уравнений с использованием матричных функций. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения Задание 1 Решение СЛАУ в матричном виде 1.1. Решить систему линейных уравнений в матричном виде. 1.2. Выполнить проверку решения. 1 x 2 x x 5 x 2 x 4 x 5 x 2 x x 5 x 4 6 x x x 3 x 4 2 1 4 3 2 4 3 2 1 4 3 2 Задание 2 Решение СЛАУ с несколькими вариантами правых частей 2.1. Решить систему линейных уравнений стремя вариантами правых частей матричным способом. 2.2. Выполнить проверку решения подстановкой неизвестных в первое уравнение. 9 18 3 2 5 5 17 4 14 x 10 x 2 x 3 x 13 x 2 x 11 x 2 x 2 8 x 2 x 2 x 9 x 6 x x x x 5 4 3 2 1 4 3 2 1 4 3 2 1 4 3 2 1 63 Задание 3 Определение точки пересечения двух прямых 3.1. Найти точку пересечения двух прямых 5x-y=5 и x+9y=47, решая систему линейных уравнений матричным способом. 3.2. Проверить найденное решение подстановкой в уравнения. 3.3. Построить графики прямых на интервале [0,3]. 3.4. Отобразить на диаграмме точку пересечения. Решение оформить в виде Матрица А В е кто р правой части bbТ . пересечения . 5 3 5 x - 5 - 5 - 2 . 5 0 2 . 5 5 7 . 5 1 0 ( 4 7 - x ) / 9 5 . 2 2 2 2 2 2 5 . 1 6 6 6 6 7 5 . 1 1 1 1 1 1 5 . 0 5 5 5 5 6 5 4 . 9 4 4 4 4 4 4 . 8 8 8 8 8 Определение точки пересечения. 5 2 2 . 5 3 X Y 5 x - 5 ( 4 7 - x ) / Т . пересечения bЗадание 4 Поворот треугольника При повороте точки с координатами (x0, y0, z0) вокруг осина угол , пересчет координат этой точки выполняется по формулам cos sin sin cos 0 y 0 x y 0 y 0 x или в матричном виде 0 y 0 x y x cos sin sin cos 4.1. Пусть в пространстве задан треугольник координатами своих вершин Т, Т, Т. После поворота треугольника вокруг осина угол координаты его вершин стали равны 536 3 121 2 3 , 243 4 828 2 2 , 414 1 1 1 T T T 4.2. Найти исходные координаты вершин треугольника, решая систему уравнений. 64 4.3. Построить исходный треугольники треугольник после поворота. 4.4. Результаты решения оформить в виде 4.5. Изменить угол поворота на 30 . Проследить за изменением диаграммы. Контрольные вопросы 1. Запись СЛАУ в алгебраическом виде. 2. Запись СЛАУ в матричном виде. 3. Решение СЛАУ в матричном виде. В каком случае СЛАУ имеет решение. 4. Функции для работы с матрицами. Как завершается ввод матричной функции 5. Понятие СЛАУ с несколькими вариантами правых частей. Решение СЛАУ с несколькими вариантами правых частей. Образец контрольной работы по подбору параметра, поиску решения и оптимизации Задание 1 Подбор параметра Решить трансцендентное уравнение x+ln(x+0,5)-0,2=0 с точностью =0.0001, отделяя графически корень на интервале [0;2]. Задание 2 Поиск решения Имеются открытки по 1 руб, поруби руб. за штуку. Сколько всего открыток можно купить на 100 рублей, если рублевых требуется купить враз больше, чем двухрублевых Ответ 50 шт. по 1 руб, 5 шт. по 2 руб, 8 шт. по 5 руб. Задание 3 Линейная оптимизация Намечается выпуск двух видов костюмов – мужских и женских. На женский костюм требуется 1 м шерсти, 2 м лавсана и 1 человеко-день трудозатрат, на мужской костюм – 3.5 м шерсти, 0.5 м лавсана и 1 человеко-день трудозатрат. Всего имеется 350 м шерсти, 240 м лавсана и 150 человеко-дней трудозатрат. По плану предусматривается выпуск не менее 110 костюмов, причем необходимо обеспечить прибыль не менее 1400 руб. Определить оптимальное число костюмов каждого вида, обеспечивающее максимальную прибыль, если прибыль от реализации женского костюма составляет 10 руб, а от мужского – 20 руб. Ответ 80 мужских, 70 женских 65 Задание 4 Решение систем линейных алгебраических уравнений Решить в Excel систему линейных алгебраических уравнений с двумя вариантами правых частей. 4 4 2 6 2 3 3 2 2 3 2 1 3 2 1 Найти определитель матрицы коэффициентов системы. Литература 1) Долженков В.А. Microsoft Excel 2000./ Долженков В.А., Колесников Ю.В. – СПб.: БХВ – Петербург, 2002. – 1088 сил) Лавренов СМ. Excel: Сб. примеров и задач СМ. Лавренов.- М Финансы и статистика, с. 3) Златопольский ДМ. 1700 заданий по Microsoft Excel. – СПб.: БХВ- Петербург, 2003. – 544 сил) Бусыгина ГМ. Выполнение инженерно-экономических расчетов в MS Excel: Учебное пособие по курсам "Электронные таблицы, "Информационно-графические системы" для студентов строительных специальностей ГМ. Бусыгина, МН. Корницкая, В.В. Соколова; Алт. гос. техн. унт им. И.И. Ползунова. – Барнаул Изд-во АлтГТУ, 2005. – с. с прил. 5) Гарнаев А.Ю. Использование MS Excel ив экономике и финансах- СПб.: БХВ – Санкт-Петербург, сил) Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. – СПб: BHV – Санкт – Петербург, 1997. – 384 с. 66 Приложение А Содержимое файла БЮДЖЕТ A B C D E 1 фирма ЗАПАД 2 Торговый бюджет 3 1999 финансовый год 4 Составил 5 Дата 6 7 Исходные данные Темпы роста 8 Рост объема продаж 1% 9 Удорожание товаров 2% 10 11 Отчете полугодие полугодие Всего 12 Приход 32550 33600 13 Затраты на товар 19250 19500 14 Полная выручка 15 Стать расходов 16 Реклама 4000 4500 17 Аренда помещений 500 500 18 Налоги и выплаты 250 250 19 Проценты по кредитам 800 800 20 Расходы всего 21 22 Прибыль 67 Приложение Б Содержимое файла TEPLO.XLS 68 Приложение В Содержимое файла ОСАДКИ |