Лабораторная работа 1 Основы работы в Excel Цель работы научить студента открывать существующие книги, сохранять книги на новом месте и под новыми именами, работать с листами книги,
 Скачать 1.3 Mb.
|
|
ZOY, выполняя действия a) меню Диаграмма – Объемный вид, b) задать параметры возвышение – 0, поворот 90, перспектива – 45, c) подписать диаграмму Вид сбоку в направлении оси OX. -5 -4 -2 0 2 4 -2 -1 0 1 2 3 4 5 6 7 Z Х Y Вид сбоку в направлении оси ОХ 5-6 4-5 3-4 2-3 1-2 0-1 -1-0 -2--1 7.4. Аналогично отформатировать вторую диаграмму так, чтобы она отображалась со стороны плоскости ZOX. Подписать диаграмму Вид сбоку в направлении оси OY. 7.5. На третьей диаграмме получить вид сверху. Подписать диаграмму Вид сверху. -5 -2 ,5 0 2 ,5 5 -4 -2 0 2 4 6 8 Z Х Y Вид сбоку в направлении оси ОУ 6-8 4-6 2-4 0-2 -2-0 -5 -3 -1 1 3 5 -4 -2 0 2 4 -50 5 10 Z Х Y Вид сверху 0-5 -5-0 51 Задание 8 Нанесение на диаграмму графических изображений 8.1. Скопировать в текущую книгу таблицу Осадки из л/р 9. 8.2. Построить обычную гистограмму, отображающую распределение осадков по годам. 8.3. Используя панель Рисование нанести на диаграмму графические изображения a) объект WordArt – заголовок Осадки (мм b) - выноска "Облако, c) - рамка текста, вставить текст "- Влажные месяцы, d) выполнить группировку выноски и рамки текста Действия – Группировать e) - основная автофигура "Солнце, f) - рамка текста, вставить текст "- Засушливые месяцы, g) выполнить группировку автофигуры и рамки текста Действия – Группировать h) вставить облако над месяцами с максимальным количеством осадков, солнце – над месяцами с минимальным количеством осадков, i) облако залить голубым цветом, солнце – желтым. 8.4. Отформатировать диаграмму a) удалить линии сетки, b) фон области построения диаграммы – прозрачный, рамка – невидимая, c) фон области диаграммы – заливка градиентная два цвета (голубой, темно-голубой), тип штриховки – от центра, d) шкала оси значений – от 0 до 155, шаг 15. 0 15 30 45 60 75 90 105 120 135 150 1992 1993 январь февраль март апрель май июнь июль август сентябрь октябрь ноябрь декабрь- Влажные месяцы- Засушливые месяцы 2002 2003 52 Контрольные вопросы 1. Основные понятия диаграмм ряды данных, категории, тип, элементы диаграмм. 2. Основные правила построения диаграмм в системе Excel. 3. Создание диаграмм с использованием Мастера диаграмм. 4. Создание графиков функций. Какой тип диаграммы необходимо использовать для построения графиков 5. Построение поверхностей. 6. Построение сечений поверхностей. 7. Редактирование диаграмм и их элементов. 8. Форматирование диаграмм и их элементов. 9. Форматирование трехмерных диаграмм. Лабораторная работа № 10 Аппроксимация таблицы данных с использованием линии тренда Цель работы научить студента находить функциональную зависимость по табличным данным, выполнять прогнозирование с использованием линии тренда. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения Задание 1 Построение графика по табличным данным Имеется зависимость между двумя величинами X и Y, заданная таблично. Вариант 1 X 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 6 6.5 Y 2.1 2.5 2.9 3.2 3.5 3.8 3.95 4.2 4.5 4.7 4.9 5.1 Вариант 2 X 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 6 6.5 Y 3.7 4.1 4.55 5 5.4 6 6.6 7.4 8.1 9 10 11 1.1. Построить график по табличным значениям. 1.2. Отформатировать график a) отметить точки символами "" красного цвета размером 7 ед. без соединения отрезками, b) подписать графики оси. 1.3. Скопировать график ниже два раза. Задание 2 Линейная аппроксимация 2.1. На первую диаграмму добавить линейный тренд. Показать на диаграмме уравнение линии тренда и коэффициент достоверности аппроксимации R2. 2.2. Отформатировать линию тренда: отобразить ее сплошной линией зеленого цвета. 53 Задание 3 Квадратичная аппроксимация 3.1. На вторую диаграмму добавить полиномиальный тренд степени 2. Показать на диаграмме уравнение линии тренда и коэффициент достоверности аппроксимации R2. 3.2. Отформатировать линию тренда: отобразить ее сплошной линией голубого цвета. Задание 4 Нелинейная аппроксимация 4.1. На третью диаграмму добавить для варианта 1 для варианта 2 степенной тренд экспоненциальный тренд Показать уравнение линии тренда и коэффициент достоверности аппроксимации R 2 4.2. Отформатировать линию тренда: отобразить ее сплошной линией синего цвета. Задание 5 Анализ результатов аппроксимации 5.1. Определить, какая из приближающих функций, полученных в заданиях 2-4, наилучшим образом описывает исходную зависимость. 5.2. Используя выбранную приближающую функцию найти значения Y для следующих значений X: для варианта 1 для варианта 2 a) x1=3.5 a) x1=4.5 b) x2=5.25 b) x2=1.75 c) x3=7.5 c) x3=0.5 5.3. Отобразить на диаграмме с лучшим трендом эти точки символом 'х' размером 11 ед. красного, зеленого и желтого цвета соответственно. Задание 6 Прогнозирование доходов фирмы с использованием линии тренда За 9 месяцев текущего года доход фирмы составил (в тыс.руб.): январь февраль март апрель май июнь июль август сентябрь 150 225 300 400 450 600 700 775 800 Определить, какой доход можно ожидать к концу года (в декабре, если сохранится такая же тенденция увеличения дохода. Указания к выполнению 6.1. Занести исходные данные. Построить график зависимости. 6.2. Скопировать график a) на первый график нанести степенной тренд, b) на второй график нанести линейный тренд, c) на третий график нанести полиномиальный тренд степени 2. 6.3. Добавить на каждый график уравнение и коэффициент достоверности аппроксимации R2. 6.4. Выбрать наилучший тренд. На выбранный тренд добавить прогноз на 3 периода вперед 54 a) выделить линию тренда правой кнопкой мыши, b) выбрать вариант Формат линии тренда, вкладка Параметры, в группе счетчиков Прогноз установить значение вперед на 3. 6.5. Вычислить, используя наилучший тренд, чему будет равен спрос в декабре месяце. Ответ 1083,062 Задание 7 Прогнозирование продажи компьютеров В некоторой компьютерной фирме продажа компьютеров составила (в штуках март май июль сентябрь 200 250 310 430 Определить тенденцию увеличения продаж функциональную зависимость. Определить, сколько компьютеров было продано в июне. Определить, сколько компьютеров будет продано в ноябре, в декабре. Ответ, 573, 658 Контрольные вопросы 1. Понятие линии тренда, основные параметры линии тренда, типы линий тренда 2. Построение линии тренда. 3. Аппроксимация табличной зависимости с использованием линии тренда. 4. Прогнозирование с использованием линии тренда. 5. Форматирование и редактирование линии тренда. Образец контрольной работы по графикам функций Цель работы проверить навыки работы с диаграммами и линиями тренда. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения Задание 1 Построение и форматирование диаграмм 1.1. Создать таблицу значений функции одной переменной на отрезке от -4 до 4 с шагом 0.5. 1.2. Построить график функции, задать заголовок График функции и подписать оси. 1.3. Скопировать полученный график ниже. 0 x если 2 2 [-2,0] x если x если 3 2 2 , x , x ), x ( y 1.4. Отформатировать скопированный график a) для оси Х изменить максимальное значение на 4, минимальное на –4, цена основных делений 1, высота текста 8 пунктов b) для оси Y изменить максимальное значение на 2.5, минимальное на – 1.5, цена основных делений 0.5, высота текста 8 пунктов 55 c) отобразить линии осей Хи утолщенными синими линиями, линии меток пересекают ось d) график функции отобразить утолщенной линией красного цвета с маркерами в виде треугольников e) отобразить основные линии сетки по горизонтали и вертикали f) исправить подпись в легенде на Кусочная функция. Задание 2 Использование линии тренда 2.1. Имеется полученная экспериментально зависимость количества посетителей видеокафе от входной платы Входная плата 0 1 1.5 2 2.5 3 3.5 4 5 Кол-во посетителей 24 20 18 16 14 12 11 9 7 2.2. Определить, используя линию тренда, какая из предложенных функций наиболее точно описывает эту зависимость - линейная, - экспоненциальная, - полиномиальная степени 2. 2.3. Вычислить для наилучшей функции количество посетителей при входной плате 4.5. Задание 3 Построение поверхностей Построить поверхность, заданную функцией 2 2 y x 2 5 . 0 y) f(x, для x [-5,5], y [-4,4] c шагом 1. Подписать как Поверхность. Подписать названия осей. Высота меток осей – 8 пунктов. В таблице значения должны содержать две цифры после десятичной точки Задание 4 Построение сечений поверхностей Построить два сечения поверхности при хи при у как две точечные диаграммы. Подписать каждую диаграмму, указав в качестве заголовка Сечение поверхности. Правильно подписать оси (х, у или “z”).. Легенда должна содержать значение х или у, при котором построена поверхность. Обеспечить вертикальные и горизонтальные основные линии сетки сценой деления 1. Высота меток осей – 8 пунктов 56 Лабораторная № 11 Подбор параметра. Решение нелинейных уравнений Цель работы научить студента решать обратные задачи с одним неизвестным, нелинейные уравнения с использованием Подбора параметра. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения Задание 1 Решение нелинейного уравнения 1.1. Найти в Excel корни уравнения x 4 +0.5x 3 -4x 2 =3x+0.5 на интервале [-2.4, 2.4] с точностью =0.001. 1.2. Изменить точность =10 -6 . Повторно решить уравнение. Записать решение в другие ячейки. 1.3. Решить это же уравнение в системе MathCAD. Сравнить с решениями, полученными в Excel. Задание 2 Задача о покупке автомобиля Для покупки автомобиля семья вкладывает в банк по 2430 руб. ежегодно. Определить, сколько денег будет на счете через 8 лет при постоянной годовой ставке 3%. Замечание Для решения задачи использовать функцию ПС(ПРОЦЕНТНАЯ СТАВКА, КВО ПЕРИОДОВ, ВЫПЛАТА. Функция вычисляет объем вклада на основе постоянных периодических платежей. 2.1. Занести в ячейки исходные данные. A B C 1 Срок лет Ставка Выплаты р Сумма на счете. В ячейку B4 занести формулу для вычисления вклада. Учесть а) что ежегодные выплаты вкладываются (те. отнимаются у семьи, поэтому соответствующий аргумент задать отрицательным, б) что процентная ставка добавляется к выплатам (те. отнимается у банка, поэтому соответствующий аргумент задать отрицательным. Ответ 22350.16 р 2.3. В ячейку С занести, используя функцию ЕСЛИ, одно из слов "год, "года, "лет" в зависимости от срока платежа для срока 1 – "год, для срока от 2 до 4 – слово "года, для срока от 5 до 20 – слово "лет. Задание 3 Определение срока накопления Используя предыдущую задачу и подбор параметра, определить сколько лет понадобится семье для покупки автомобиля, если стоимость автомобиля 70 000 руб. Ответ 20 лет 57 Задание 4 Вычисление выплат Используя задачу из задания 2 и подбор параметра, определить, какие выплаты необходимо делать ежегодно, чтобы залет купить автомобиль стоимостью 70 000 руб. Ответ р Задание 5 Покупка дома Для покупки дома предлагают два варианта выплатить сразу 205 тыс.руб. или выплачивать постепенно – через год – 40 тыс. руб, через 2 года 80 тыс.руб. и через 3 года – 120 тыс. руб. при процентной ставке 5%. Определить, какая сумма будет выплачена за 3 года. Сравнить, что выгоднее – выплатить 205 тыс. сразу или платить частями в течение 3 лет Замечание Для решения задачи использовать функцию ЧПС(ПРОЦЕНТНАЯ СТАВКА, ДИАПАЗОН ПОСТУПЛЕНИЙ. Функция вычисляет объем вклада на основе ряда последовательных поступлений. 5.1. Занести в ячейки исходные данные. Ежегодные выплаты поступления год р . 3 2 год р . 4 3 год р Процентная ставка Общие выплаты через года bb7bbП ер вона чал ь на я выплата р . 5.2. В ячейку B6 занести формулу для вычисления размера общих выплат (используя ЧПС). 5.3. Сравнить значения единовременной выплаты и выплаты по частями, используя функцию ЕСЛИ, выдать в ячейке С одно из сообщений Выгоднее платить сразу или Выгоднее выплачивать по частям. Ответ Выгоднее платить сразу Задание 6 Вычисление процентной ставки Определить, при какой процентной ставке выгоднее выплачивать по частям Какое сообщение выдается Ответ Контрольные вопросы 1. Для каких задач используется подбор параметра 2. Порядок выполнения подбора параметра. Заполнение элементов диалогового окна Подбор параметра 3. Задание погрешности вычислений. 4. Решение нелинейных уравнений. 58 Лабораторная работа № 12 Поиск решения. Решение систем нелинейных уравнений с использованием поиска решения Цель работы научить студента решать системы линейных уравнений с использованием матричных функций, системы нелинейных уравнений с использованием Поиска решения. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения Задание 1 Линейная оптимизация Решить задачи линейной оптимизации. Указания к выполнению. Написать математическую постановку задачи – целевую функцию и ограничения (сколько ограничений) 1.2. На новом листе создать форму и заполнить ее исходными данными и формулами 1.3. Найти решение задачи с использованием поиска решения Вариант - 1 Задача 1. Фирма производит две модели Аи В книжных полок. Для каждого изделия модели А требуется м досок, а для изделия модели В – м, фирма же может получать от своих поставщиков дом досок в неделю. Для каждого изделия модели А требуется 12 минут работы оборудования, а для изделия модели В –30 минут, всего неделю можно использовать до 160 часов работы оборудования. Изделие А приносит 2 доллара прибыли, а изделие В- 4 доллара. Сколько изделий каждой модели следует фирме выпускать в неделю, чтобы максимизировать прибыль. Ответ 300 шт. изделия Аи шт. изделия В. Задача 2*. Фирма производит 3 типа столов для кабинетов, выполняя 3 операции изготовление частей, сборка, полировка. Кроме того, имеется дополнительное условие возможность хранения – не более 170 столов в неделю. Прибыль от продажи составляет за стол го типа - 15 долларов, за стол го типа – 22 доллара, за стол го типа – 19 долларов. Сколько столов различного типа следует выпускать фирме в неделю, чтобы максимизировать прибыль 59 Работы по производству столов Объем работ в часах для столов Максимум объема работ в неделю го типа го типа го типа Изготовление частей 2 3 2 360 Сборка 1 2 3 240 Полировка 1 1 2 180 Ответ 100 шт. го типа, шт. го типа, шт. го типа Вариант - 2 Задача 1. Фирма имеет возможность рекламировать свою продукцию, используя местные радио и телевизионную сеть. Затраты на рекламу в бюджете фирмы ограничены суммой 1000 долларов в месяц. Каждая минута радиорекламы обходится 5 долларов, а каждая минута телерекламы – 100 долларов. Фирма хотела бы использовать радиосеть, по крайней мере, в два раза чаще, чем телевидение. Удельный объем сбыта, обеспеченный минутой телерекламы 25 товаров/мин., а удельный объем сбыта, обеспеченный минутой радиорекламы - 1 товар/мин. Определить оптимальное распределение времени на рекламу, обеспечивающее максимальный объем сбыта. Ответ 18 мин. на радиорекламу и 9 мин. на телерекламу. Задача 2*. Процесс изготовления двух видов промышленных изделий состоит в последовательной обработке каждого из них на трех станках. Время использования этих станков для производства каждого изделия ограничено значением 8 часов в сутки. Время обработки и прибыль от продажи одного изделия каждого вида приведены в таблице Изделие Время обработки одного изделия, мин. Удельная прибыль Станок 1 Станок 2 Станок 3 I 10 6 8 2 II 5 20 15 3 Найти оптимальный объем производства изделий каждого вида, обеспечивающий максимальную прибыль. Ответ 20 шт. изделия I и 12 шт. изделия Задание 2 Нелинейная оптимизация Решить задачи нелинейной оптимизации. Указания к выполнению 2.1. Написать математическую постановку задачи – целевую функцию и ограничения (сколько ограничений) 2.2. На новом листе создать форму и заполнить ее исходными данными и формулами. Предварительно значения переменных положить равными 1. 2.3. Найти решение задачи с помощью модуля поиска решения Задача 1. Контейнер, в котором пересылаются материалы, имеет форму параллелепипеда. При отправлении на него накладываются ограничения длина а, ширина b, высота h не более 42, а также a+2b+2h<=72. Найти размеры контейнера, при которых его объем будет максимальным. Ответ 24, 12, 12 60 Задача 2. Контейнер, в котором пересылаются материалы, имеет форму параллелепипеда. При отправлении на него накладываются ограничения длина а не более 20, ширина b не более 11, высота h не более 42, а также a+2b+2h<=72. Найти размеры контейнера, при которых его объем будет максимальным. Ответ 20, 11,15 Задание 3 Создание сценариев при поиске решения Заведующий хозрасчетной больницей должен составить штатное расписание, те. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Известно, что общий месячный фонд зарплаты составляет 100 000 руб. Для нормальной работы больницы нужно 5-7 санитарок, 8-10 медсестер, 10-12 врачей, 3 заведующихотделениями |