Методичка по СИТу (лабораторные работы для 1 курса). Методичка. Информационные технологии Лабораторный практикум
 Скачать 2.57 Mb.
|
|
Лабораторная работа № 8 MS Excel. Математические расчеты Цель работы: - ознакомиться с возможностями Excel для решения математических задач; - освоить методы построения графиков математических функций; - освоить встроенные функции для работы с матрицами; - освоить приемы работы с командами Подбор параметра и Поиск решения. Общие сведения Математические функции Excel Общие правила записи функций для Excel представлены в лабораторной работе № 6. Наиболее часто используемые математические функции приведены в таблице: Математические функции Ecxel Обозначение Описание ABS (число) Модуль аргумента ACOS (число) Арккосинус аргумента ASIN (число) Арксинус аргумента ATAN (число) Арктангенс аргумента COS (число) Косинус аргумента EXP (число) Экспонента аргумента LN (число) Натуральный логарифм аргумента LOG (число) Логарифм аргумента по данному основанию. Если осно- вание не указано, оно полагается равным 10 LOG10 (число) Десятичный логарифм аргумента SIN (число) Синус аргумента TAN (число) Тангенс аргумента ЗНАК (число) Определяет знак числа КОРЕНЬ (число) Вычисляет положительный квадратный корень аргумента ОСТАТ (число;делитель) Возвращает остаток от деления числа на делитель ПИ () Возвращает значение числа π с точностью до 15 знаков. Функция не имеет аргумента ПРОИЗВЕД (число1; число2;…) Перемножает все числа, заданные ее аргументами СЛЧИС (число) Возвращает случайное число между 0 и 1. Для получение случайного числа между a и b можно использовать фор- мулу: СЛЧИС()*(b-a)+a СТЕПЕНЬ (число; степень) Возводит аргумент в заданную степень ФАКТР (число) Факториал аргумента Построение графиков функций График функции строится по созданной таблице значений функции. Значения аргумента задаются методом Автозаполнения (в виде арифметической прогрессии). Шаг выбирают небольшим, чтобы таблица значений функции отражала её поведение на интервале табу- ляции. Затем вызывается Мастер диаграмм. Для построения графиков функций используются два типа диаграмм: График и Точечная. 41 При выборе диаграммы типа График следует выделить только диапазон, содержащий значения функции. Подпись значений аргументов по оси x добавляется на вкладке Ряд в поле Подпись по оси X диалогового окна Исходные данные (Мастер диаграмм, шаг 2). При выборе диаграммы типа Точечнаядостаточно выделить ячейку, содержащую первое значение аргумента, на графике отобразится весь диапазон таблицы значений функции; подписи по оси аргументов x будут соответствовать табличным значениям аргумента. Следует отметить, что при таком способе создания графика значения аргументов и функ- ций должны обязательно располагаться в соседних ячейках. При необходимости построе- ния графика по значениям аргумента и функции, расположенным в несмежных диапазо- нах, следует выделить оба диапазона. Можно совместить несколько графиков в одной системе координат. Для этого следует до- бавлять ряды, соответствующие таблицам функций в окне Ряд вкладки Ряд диалогового окна Исходные данные. Готовый график можно редактировать, выбрав любой элемент на диаграмме или с помо- щью контекстного меню области диаграмм или области построения графика. При построении поверхностей следует построить таблицу значений функции по обоим ее аргументам, задавая, например, для функции Z(X,Y) значения аргумента X в виде столбца, а значения аргумента Y в виде строки. Затем выделить весь диапазон значений функции и в Мастере диаграмм выбрать тип графика Поверхность. На рисунке ниже представлен график поверхности z=sin(x 5 +y 5 ). Для задания функции используются абсолютные ссылки на столбец аргументов X и строку аргументов Y. Поэтому формула в ячейке B2 для приведенной функции будет следующей: =SIN($A10^5+I$1^5). Эта формула методом Автозаполнения введена в диапа- зон B2:V32. Для изменения ориентации поверхности в пространстве следует выделить диаграмму и в контекстном меню выбрать команду Объемный вид . На экране отобразится диалоговое ок- но Формат трехмерной проекции. Матричные вычисления Для работы с матрицами в Excel имеются встроенные функции, основные из них приведе- ны в таблице: Функции обработки матриц Функция Описание МОБР Возвращает обратную матрицу. Для результата следует выделить диапа- зон ячеек 42 МОПРЕД Вычисляет определитель матрицы МУМНОЖ Производит матричное произведение двух матриц. Для результата сле- дует выделить диапазон ячеек ТРАНСП Возвращает транспонированную матрицу. Для результата следует выде- лить диапазон ячеек Работа с матрицами выполняется как работа с массивами. Если результатом выполнения функции является матрица, следует для ввода функции выделить диапазон, соответству- ющий по размерам получаемой матрице. Ввод функций для работы с массивами заканчи- вается нажатием трех клавиш [Ctrl+Shift+Enter]. Анализ данных с помощью команд Подбор параметра и Поиск решения Команда Данные→ Анализ «что если»→ Общие → Подбор параметра используется для полу- чения такого значения аргумента некоторой функции, при котором функция принимает заданное значение. Так как реализацию этой команды Excel выполняет численным мето- дом (методом последовательных приближений Ньютона), необходимо задать начальное значение аргумента (первое приближение к искомому значению). Для вызова команды следует: выбрать ячейку, содержащую формулу (функцию искомого аргумента), вызвать функцию Сервис → Подбор параметра и в диалоговом окне Подбор пара- метра задать o начальное значение аргумента, o искомое значение функции. Метод подбора параметров используется, например, для нахождения корней уравнения. Команда Данные→ Поиск решения ( Файл→ Параметры→ Надстройки→ Перейти → Поиск решения) используется для решения системы уравнений с несколькими неизвестными или уравнения с несколькими переменными и заданными ограничениями на решения. Чаще всего эта команда используется для решения линейной и нелинейной задачи оптимизации. Команда Поиск решения является надстройкой и должна быть предварительно установле- на. Для установки предельного числа итераций и относительной погрешности вычислений следует использовать вкладку Вычисления диалогового окна команды Параметры→ Фор- мулы. По умолчанию Excel предлагает предельное число итераций – 1000 и относитель- ную погрешность – 0,001. Содержание работы Задания выполняйте на отдельных листах созданной Вами книги. Задание 1. Табулирование функций и построение графиков функций Назовите новый рабочий лист График. Постройте график функции 1 в соответствии с заданием. Тип диаграммы выберите Точеч- ная или График. При редактировании графика задайте заголовки, убедитесь, что подписи по оси x соответствуют табличным значениям. На том же графике отобразите функцию 2 (выберите график функции 1, в контекстном меню активизируйте Исходные данные и на вкладке Ряд добавьте график, указав диапазон ячеек со значениями функции 2 в поле Зна- чения. Задание 2. Построение трехмерных графиков Создайте новый лист с именем Поверхность. Постройте график поверхности в соответ- ствии с заданием. Выберите ориентацию графика, соответствующую наиболее наглядному представлению поверхности. 43 Задание 3. Работа с массивами. Решение системы линейных алгебраических уравнений (СЛАУ) СЛАУ в матричной форме имеет вид Ax = b, где A – матрица коэффициентов, x – вектор неизвестных, b - вектор свободных членов: nn n n n n a a a a a a a a a A 2 1 2 22 21 1 12 11 , n b b b b 2 1 , n x x x x 2 1 Решение системы уравнений в матричном виде: x = A -1 b, Где A -1 – матрица,обратная к матрице А. Для проведения вычислений следует: ввести исходные матрицу и векторы, построить обратную матрицу (выделить диапазон ячеек для хранения обратной матрицы (размер диапазона должен совпадать с размером массива А) и вызвать функ- цию МОБР. Для запуска функции следует воспользоваться комбинацией клавиш [Ctrl+Shift+Enter]; выделить массив для результата и вызвать функцию МУМНОЖ; в диалоговом окне задать исходные массивы - диапазоны ячеек с матрицей A -1 и вектором b. Для запуска функции также следует нажать [Ctrl+Shift+Enter]. Создайте новый лист с именем СЛАУ и выполните на нем вычисление заданной системы матричным методом и методом Крамера. Задание 4. Подбор параметра при выполнении финансовых расчетов Методом подбора параметра рассчитать, при какой ежемесячной процентной ставке С можно за год накопить S рублей, внося каждый месяц платеж на n % больше предыдуще- го, начав с первого платежа P рублей. Слева представлено заполнение таблицы для расчета накопления за год при процентной став- ке C=12% годовых, n=10% и P=100 руб. Применяемые формулы: в ячейке C6: =C5+С5*0,1; в ячейке E5: =БЗ($D$2;D5;;-C5); в ячейке E17: =СУММ(E5:E16). Финансовая функция БЗ возвращает будущее значение вклада на основе периодических по- стоянных платежей и постоянной процентной ставки Синтаксис: БЗ(ставка; кпер; плата; нз; тип), где ставка – процентная ставка или скидка по вложению или ссуде за период выплат, кпер – общее количество платежей или периодов выплат (это значение в примере уменьша- ется на 1 каждый месяц), плата– объем периодической выплаты по вложению или ссуде (в примере =0), нз – общая сумма, которую составят будущие платежи, начиная с текущего момента (в при- мере это сумма, которую следует вернуть (=0), минус сумма начального вложения); тип – режим выплат, с которым осуществляются выплаты (значение 0 соответствует выпла- там в конце месяца, значение 1 - в начале месяца). Для расчета накопления, например 7000 рублей, вызывается команда Подбор параметра для формулы в ячейке Е17 (общая сумма выплаты) и задается изменяемая ячейка D2 (ежемесячная процентная ставка). Выполните расчеты для заданных преподавателем значений S, n, P и C. Задание 5. Решение оптимизационной задачи Для решения оптимизационных задач предназначено средство Поиск решения. 44 Пусть необходимо найти максимум функции Z(x), где n i i i x c Z 1 ) ( ) ( x c, x , с – заданный вектор, x – искомый вектор при ограничениях A x ≤ b, где А – матрица размером m×n; b=(b 1 ,b 2 ,…,b m ). Функция Z называется целевой функцией. Так как целевая функция и ограничения линей- но зависят от переменных, оптимизационная задача в такой постановке называется зада- чей линейного программирования. Пример поиска максимума функции Z=3000x 1 +2000x 2 при ограничениях: x 2 +2x 1 ≤6, 2x 1 +x 2 ≤8, x 2 -x 1 ≤1, x 2 ≤2, x 1 ,x 2 ≥0. После ввода формул выделяется ячейка, содержащая целевую функцию (С4) и вызывается команда Поиск решения Пример решения оптимизационной задачи Выполните решение оптимизационной задачи в соответствии с заданием преподавателя. Выделите на листе результаты расчетов. Убедитесь, что найденное решение не противо- речит заданным ограничениям. Контрольные вопросы и задания 1. Какие типы диаграмм Excel можно применять для построения графиков функций? 2. Чем различаются диаграммы типа График и Точечная? 3. Как метод работы с массивами применяется для решения системы линейных урав- нений? 4. Следует ли учитывать работу с массивами при решении СЛАУ методом Крамера? 5. Какой численный метод лежит в основе выполнения команды Подбор параметра? 6. Что представляют собой оптимизационные задачи? 7. В каких случаях решается задача линейного программирования? 8. Как применить команду Поиск решения для решения задачи оптимизации? 9. Какая из команд – Поиск решения или Подбор параметра – может быть применена для решения нелинейного уравнения? 45 Лабораторная работа № 9 Автоматизация работ в офисных приложениях Цель работы: освоить работу по созданию бланков в виде шаблонов в Word; научиться создавать макросы на примере Excel; освоить работу по созданию форм и бланков с возможностью автоматизации вво- да данных и выполнения расчетов в Excel. Общие сведения Создание форм и бланков в Word Программа Word позволяет создавать формы и бланки, которые можно распространять в электронном виде и заполнять на компьютере. В виде бланков оформляются анкеты, опросные листы и другие документы. Электронный бланк используется многократно, поэтому он создается не как обычный до- кумент, а как шаблон. Шаблон является основой для других документов, а сам остается неизменным. Для этой цели в шаблоне делаются д0оступными только поля ввода данных (поля формы), к остальному тексту доступ пользователя закрыт. Создание шаблона Создать шаблон можно сохранив готовый документ как шаблон ( Файл→Сохранить как ) , установив в диалоговом окне сохранения тип файла Шаблон документа. Файл, сохраненный как шаблон, имеет расширение .dot. Сохранить шаблон можно в лю- бом месте, но по умолчанию Word сохраняет шаблоны в специальной папке Шаблоны. Создание бланка Для создания полей формы бланка используют панель инструментов Формы. В Word воз- можны три вида полей формы: текстовые поля - служат для ввода текста; флажки – позволяют установить или снять отметку в поле; раскрывающиеся списки - позволяют выбрать один из нескольких вариантов. Настройку полей выполняют в диалоговом окне Параметры , которое вызывается коман- дой Файл контекстного меню для выбранного поля. Размещение полей формы удобнее всего выполнять с помощью таблицы, чтобы удобнее было выравнивать расположение полей. Созданную форму перед использованием следует защитить. Защита полей Для ограничения доступа пользователя при заполнении шаблона следует активизировать кнопку Защита формы. Заполнение готовой формы Если шаблон был сохранен в стандартной папке Шаблоны, то вызвать его можно по ко- манде Файл→Создать , при этом в диалоговом окне Создание документа выбирается имя требуемого бланка. Поля формы выбираются клавишами [Tab] и [Shift+Tab] (движение в прямом и обратном направлении, соответственно) или с помощью мыши. Заполнив форму, следует сохранить ее в виде обычного документа или распечатать. Пример заполнения фрагмента формы, содержащей три типа полей (текстовый для ввода фамилии, флаг для ввода пола, поля со списком для ввода курса и номера группы): 46 Создание макросов в Excel Макрос – это программа, записанная на языке VBA (Visual Basic for Applications – специ- альная версия языка Visual Basic), и предназначенная для автоматизации вычислений и действий в офисных приложениях фирмы Microsoft (Word, Excel, PowerPoint и других). Макросы часто используются: для ускорения часто выполняемых операций редактирования или форматирова- ния; для объединения нескольких команд, например, для вставки таблицы с указанны- ми размерами и границами и определенным числом строк и столбцов; для упрощения доступа к параметрам в диалоговых окнах; для автоматизации обработки сложных последовательных действий в задачах. Создать макрос можно двумя способами: с помощью средства для записи макросов или с помощью редактора Visual Basic. В данном описании рассматривается только способ работы с макросами для приложения Excel с помощью средства для записи макросов. Средство записи макросов ( Вид→ Макросы→ Запись макроса ) позволяет быстро созда- вать макросы с минимальными усилиями (нет необходимости знать VBA). При этом все действия, производимые пользователем, автоматически записываются в виде последова- тельности инструкций на языке VBA. Запись макроса можно временно приостанавливать и затем возобновлять с того места, где запись была остановлена. Записываемому макросу в диалоговом окне Запись макроса назначается имя (или принимается предлагаемое имя). Назначаемое имя должно обяза- тельно начинаться с буквы и содержать только буквы, цифры и знаки подчеркивания. При записи можно назначить для запуска макроса сочетание клавиш. Изменить, просмотреть или удалить макрос можно с помощью диалогового окна Макрос ( |