Контрольная работа по "Информатика". 28

Содержание 

1. Задание 1 

      Построить графики функций в разных системах координат (значения а и b задать самостоятельно).

                   e^x-a arctgb  x<lg a

      У =       ln Sin (x-b) x>=lg a 

      при х [0.1, 1.8] 

      g = e^{cos x}

      при х [0.1, 0.8] 

     1.1 Описание построения  электронного документа 

     В ТП Microsoft Excel построим таблицу для функций y=f(x) и g=f(x).

     Диапазон  ячеек А1:E1 представляет собой шапку будущей таблицы с названиями значений. В диапазон ячеек А2:A36 занесем значения х (с шагом 0,05). В ячейку А2 введем значение, равное 0,1, в ячейку А3 - значение 0,015. Затем выделим  эти ячейки и, ухватившись за маркер автозаполнения, заполним остальные ячейки до тех пор, пока значение не будет 1,8.

     В ячейку С2 внесем значение а, равное 5, а в ячейку D2 внесем значение b, равное 6.

     Диапазоны ячеек B2:B36 и E2:E16 рабочего листа являются расчетными. В ячейку В2 вводим формулу, по которой определяется значение функции Y для аргумента из ячейки А2 и значений a (ячейка C2) и b (ячейка D2). В ячейку E2 вводим формулу для определения значения функции g при аргументе в ячейке А2. При построении формулы следует использовать абсолютные ссылки на номера строк ячеек, содержащих значения.

     Содержимое  ячеек В2 и E2 копируем в диапазоны В2:B36 и E2:E16 соответственно, в результате чего получаем таблицы значений функций y и g. Копирование осуществляется при помощи маркера автозаполнения. При наведении мышки на черный квадратик (маркер автозаполнения) в нижнем правом углу ячейки, содержащей формулу, мышка принимает вид черного крестика, ухватившись за который, можно скопировать формулу для определенного диапазона ячеек, который содержит идентичную формулу.

     Для отображения формул в таблице выбираем Сервис – Параметры, во вкладке Вид ставим галочку в меню Параметры окна напротив формулы.

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

     1.2. Описание формул, используемых для  вычисления 

     Функция y задается различными формулами на разных участках интервала табуляции:

                    e^x-a arctgb  x<lg a

      У =

                  ln Sin (x-b) x>=lg a

     В подобных случаях расчетная формула  в Excel строится с использованием логической функции ЕСЛИ (Логическое_выражение; Значение_если_истина; Значение_если_ложь). Логическое выражение может принимать значения ИСТИНА либо ЛОЖЬ в зависимости от конкретных значений зависимых ячеек. В первом случае функция ЕСЛИ возвращает значение в соответствии со своим первым аргументом, а во втором случае используется второй аргумент.

     В задании  в качестве логического выражения используем выражение x<lg a (для х из ячейки А2).

     В ячейку B2 вводим формулу, которая будет вычислять значение функции y для произвольного значения x в ячейке A2 и заданных значений а и b в ячейках C2 и D2 соответственно:

     =ЕСЛИ(A2<LOG($C$2);EXP(A2-$C$2)*ATAN($D$2);LOG10(SIN(A2-$D$2)))

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

     = EXP(A2-$C$2)*ATAN($D$2)

     Формула строится с использованием функции  EXP, возводящей константу е в степень своего аргумента, и функции ATAN – аналога тригонометрической функции арктангенс.

     Если  же логическое выражение ложно, то для  вычислений используется формула:

     = LOG10(SIN(A2-$D$2)

     В ячейке E2 будет рассчитываться значение функции g. Для этого вводим формулу:

     = EXP(COS(A2))

     Для копирования формул воспользуемся  автозаполнением. При этом обратим  внимание, что при копировании  формулы для вычисления функции y некоторые ячейки не меняют своего значения, т.е. являются константами (ячейка C2 – значение а и ячейка D2 – значение b). Ячейки С2 и D2 являются абсолютными ссылками, в формулах записываются в виде $C$2 и $D$2. При копировании формулы для вычисления функции g используются относительные ссылки.

     Обычно  ссылки на ячейки описываются и используются как относительные (например, А1). Когда формула, содержащая эти ссылки, копируется, происходит изменение формулы для поддержания относительности ссылок.

     Абсолютная  ссылка на ячейку или область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (например, $А$1).

     Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая  осуществляет преобразование относительной ссылки в абсолютную и наоборот.

      В результате вычислений функций y=f(x) и g=f(x) электронная таблица примет следующий вид:

      1.3 Описание построения  диаграммы 

     Для построения графиков для функций  y и g в ТП Microsoft Excel на панели Стандартная выберем Мастер диаграмм или Вставка - Диаграмма. На экране появится окно Мастер диаграмм (шаг 1 из 4): тип диаграммы. Выберем Тип Точечная (точечная диаграмма со значениями, соединенными сглаживающими линиями).

     Нажмем  кнопку Далее. Появится следующее окно Мастер диаграмм (шаг 2 из 4): источник данных диаграммы.

     В строке Диапазон отображаются исходные данные, на основании которых будет строится график (мышкой выделяем необходимый диапазон. Если нужно выделить диапазон ячеек, стоящих поодаль друг от друга, удерживаем клавишу Ctrl). Во вкладке Вид в строке Имя задаем название ряда (у для первого графика и g для второго графика).

     Нажмем  кнопку Далее.

     Появится  следующее окно Мастер диаграмм (шаг 3 из 4): параметры диаграммы.

     Во  вкладке Заголовки даем название диаграммы и координатных осей (Ось Х (категорий) и Ось У (значений)).

     Нажимаем  кнопку Далее.

     Появится  следующее окно Мастер диаграмм (шаг 4 из 4): размещение диаграммы. Выбираем на имеющемся. Нажимаем кнопку Готово.

      После окончания работы с Мастером обычно проводится форматирование диаграммы  по элементам. Диаграмма состоит  из отдельных частей, таких как  оси, название, область построения диаграммы, линии сетки, ряды данных и др. Каждая из этих частей может редактироваться отдельно, для чего достаточно дважды щелкнуть на нужной части. Например, чтобы изменить отдельные параметры оси категорий, достаточно дважды щелкнуть на ней, после чего на экране появляется специальное окно, которое позволяет изменять параметры оси, такие как вид, шрифт и формат подписей и др.

      График  функции у=f(x) имеет вид:

     График  функции g=f(x) имеет вид:

2. Задание 2 

      Решить  систему линейных уравнений:

       х₁ - 5х₂ + 19х₃ = 56

      х₁ - 2х₂ + 5х₃ = 24

      4х₁ - 8х₂ + 31х₃ = 107 

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

     Системой  линейных алгебраических уравнений  называется система вида

  a₁₁x₁+a₁₂x₂+...+a_1nx_n = b₁

            a₂₁x₁+a₂₂x₂+...+a_2nx_n = b₂

                   ..........................................

            a_m1x₁+a_m2x₂+...+a_mnx_n = b_m 

которая в общем случае имеет m уравнений и n неизвестных.

     Упорядоченный набор с₁, с₂, ..., с_n называется решением системы уравнений, если при подстановке чисел с₁, ..., с_n в систему каждое из ее уравнений превращается в верное равенство. Система, для которой такой набор существует, называется совместной. Если система совместна, то она может иметь либо единственное решение, либо бесконечно много решений. Если система решений не имеет, то она называется несовместной. Система является совместной в том и только в том случае, если ранг ее основной матрицы равен рангу расширенной матрицы, которая получается из основной присоединением к ней столбца свободных членов.

      Выбор метода решения системы уравнений  определяется, в частности, свойствами системы. В частности, основные точные методы решения систем (обеспечивающие ее точное решение за конечное число шагов, если вычисления проводятся без погрешности) – это метод Гаусса, Крамера и матричный способ. Первый метод является универсальным; он позволяет решать системы с произвольным количеством уравнений и неизвестных, находить множество решений в общем виде либо обосновывать несовместность системы. В то же время, этот метод сравнительно трудоемок при реализации в Excel уже при небольшом числе уравнений и неизвестных.

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

     Можно также отметить, что помимо точных способов решения существует достаточно много итерационных методов (простой  итерации, Зейделя и т.д.), которые позволяют получить решение системы, как правило, с заданной точностью. В ряде случаев использование этих методов является предпочтительным, так как, с одной стороны, в реальных задачах коэффициенты линейных систем сами по себе могут быть получены с погрешностями, и, с другой стороны, при машинных вычислениях с использованием точных методов может происходить накопление погрешности.

     Итак, метод Гаусса пригоден для решения  произвольных систем линейных уравнений. Данный метод заключается в последовательном преобразовании расширенной матрицы системы с целью приведения ее к ступенчатому виду. Так, на первом шаге неизвестное х₁ исключается из всех уравнений, кроме первого. Для этого достаточно:

     - при необходимости выполнить перестановку уравнений так, чтобы первым из них было уравнение с ненулевым коэффициентом при х₁;

     - разделить первое уравнение на коэффициент при х₁;

     - их прочих уравнений вычесть полученное первое, умноженное на коэффициент при х₁ в соответствующем уравнении.

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

     При этом, если в процессе преобразований возникает уравнение, у которого все коэффициенты нулевые, оно исключается  из системы. Если же получается уравнение, у которого нулевые коэффициенты при неизвестных при ненулевом свободном члене, то система, очевидно, не совместна.

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

     Для применения метода Крамера необходимо вычислить определители n матриц (помимо определителя основной матрицы). Каждая из таких матриц получается из основной матрицы заменой ее некоторого столбца на столбец свободных членов. Формула Крамера выглядит следующим образом:

      где det A = |A| - главный определитель матрицы А,

                 det A_i = |A_i| (i=1, 2, …, n) – определители матриц А_i (вспомогательные определители), которые получаются из А заменой i-го столбца на столбец свободных членов.

     Итак, метод Крамера может быть использован  для решения систем с квадратной и не вырожденной основной матрицей, которые имеют единственное решение.

      В матричной форме система записывается в виде:

A * X = Y,

      где A - матрица коэффициентов при  неизвестных (матрица системы).

          X - вектор-столбец неизвестных X = (x1, x2, …, xn).

          Y - вектор-столбец свободных членов.

В данном задании матрица коэффициентов А = , вектор-столбец свободных членов Y=

      Матричный способ решения системы уравнения  заключается в следующем: умножив  обе части матричного равенства  слева на обратную матрицу А^-1, получим А^-1 * А * Х = А^-1 * У. тогда решение системы запишется в следующем виде:

Х = А^-1*У

      Т.е. для решения системы (вычисления вектора-столбца Х) необходимо найти  для матрицы А обратную А^-1 и умножить ее на вектор-столбец У свободных членов.

     Данный  способ идеально подходит для решения  системы с квадратной не вырожденной основной матрицей, так как в Excel имеются встроенные функции матричного умножения и нахождения обратной матрицы. Поэтому если основная матрица системы квадратная и не вырожденная, то лучше всего применять для ее решения матричный способ.

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

      Табличные формулы или формулы массива - очень мощное вычислительное средство Excel, позволяющее работать с блоками  рабочего листа как с отдельными ячейками. Табличные формулы в  качестве результата возвращают массив значений. Поэтому перед вводом такой формулы необходимо выделить диапазон ячеек, куда будут помещены результаты. Потом набирается сама формула. Ввод ее в выделенный диапазон ячеек осуществляется нажатием комбинации клавиш Ctrl+Shift+Enter. Это принципиально. Формула вводится во все ячейки выделенного интервала. При активизации любой ячейки из интервала, содержащего формулу массива, в строке формул отображается введенная формула, заключенная в фигурные скобки. Именно фигурные скобки являются признаком табличной формулы. 

      2.2. Описание построения  электронного документа 

     Решим систему двумя способами: методом  Крамера и при помощи обратной матрицы.

     Для решения системы уравнений методом Крамера найдем главный определитель системы уравнений, определители вспомогательных матриц, а затем вычислим корни системы уравнений.

      В диапазон ячеек B1:D3 занесем элементы матрицы A, а в диапазон ячеек E1:E3 - элементы вектора B.

      В диапазонах ячеек B6:D8, B11:D13, B16:D18, B21:D23 разместим основную (А) и вспомогательные (А1, А2, А3) матрицы. Вставка скопированных диапазонов и рядов свободных членов осуществляется при помощи Специальной вставки – Вставить связь.

      В ячейке E7 вычислим значение главного определителя по формуле:

      =МОПРЕД(B6:D8)

      В ячейках E12, E17, E22 найдем значения вспомогательных определителей для вспомогательных матриц по формулам =МОПРЕД(B11:D13) для матрицы А1, =МОПРЕД(B16:D18) для матрицы А2, =МОПРЕД(B21:D23) для матрицы А3.

 В ячейках С26, С27 и С28 вычислим корни системы уравнений (Х1, Х2 и Х3). Воспользуемся формулами отношения определителя вспомогательной матрицы к главному определителю. Для этого запишем формулы =E12/E7, =E17/E7, =E22/E7 в соответствующие ячейки.

      Выполним  проверку правильности решения данной системы уравнений в диапазоне ячеек С31:С33. Для этого воспользуемся табличной формулой. Выделим диапазон ячеек С31:С33 и в строке формул запишем формулу {=МУМНОЖ(B1:D3;C26:C28)}. Затем одновременно нажимаем комбинацию клавиш Ctrl+Shift+Enter. Формула вводится во все ячейки выделенного интервала. В строке формул отображается введенная формула, заключенная в фигурные скобки. Именно фигурные скобки являются признаком табличной формулы.

      В результате выполненной проверки столбец из полученных чисел должен быть равен столбцу свободных членов, а именно 56, 24, 107.

     Для решения системы уравнений при  помощи обратной матрицы (матричным  способом) найдем обратную матрицу  А^-1, затем выполним умножение обратной матрицы на столбец свободных членов.

      В диапазоне ячеек B1:D3 разместим элементы матрицы A, а в диапазоне ячеек E1:E3 - элементы вектора B.

     Для нахождения обратной матрицы выделим  диапазон ячеек B6:D8, воспользуемся табличной формулой. Для этого в строку формул введем формулу {=МОБР(B1:D3)}. Затем одновременно нажимаем комбинацию клавиш Ctrl+Shift+Enter. Формула вводится во все ячейки выделенного интервала.

     Диапазон  ячеек B11:B13 зарезервируем под искомое  решение - вектор X (Х1, Х2, Х3). В данном расчете также воспользуемся табличной формулой. Выделим диапазон ячеек B11:B13, в строку формул введем формулу {=МУМНОЖ(B6:D8;E1:E3)}. Затем одновременно нажимаем комбинацию клавиш Ctrl+Shift+Enter. Формула вводится во все ячейки выделенного интервала.

     Выполним  проверку правильности решения данной системы уравнений. Для этого вновь используем табличную формулу. В выделенный диапазон ячеек B17:B19 введем формулу {=МУМНОЖ(B1:D3;B11:B13)}. Затем одновременно нажимаем комбинацию клавиш Ctrl+Shift+Enter. Формула вводится во все ячейки выделенного интервала.

      2.3. Анализ полученных  результатов 

      Во  втором задании была решена система  линейных уравнений двумя способами: методом Крамера и при помощи обратной матрицы. В обоих случаях  были использованы табличные формулы, которые в качестве результата возвращают массив значений.

      Также были использованы следующие формулы:

      =МОБР(матрица)  – нахождение обратной матрицы;

      =МОПРЕД(матрица)  – нахождение определителя матрицы;

      =МУМНОЖ(матрица1, матрица2) – возвращает произведение  матриц.

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

3. Задание 3 

     Найти оптимальное решение для модели линейного программирования. Решить задачу с использованием пакета «Поиск решения» ЭТ MS Excel.

L(X) = 4x₁ + 6x₂ - 14x₃ + 49x₅         min;

   21x₁ + 9x₂ - 2x₄ - 12x₅ >=58,

   110x₂ - 60x₃ + 80x₄ - 45x₅ =290,

   5x₂ + 27x₃ - 14x₄ + x₅ <=72,

   87x₁ – 6,4x₂ + 130x₄ =140,

   X_j >= 0 (j = 1,5). 

      3.1. Построение математической  модели для решения  оптимизационной  задачи 

      Широкий класс экономических задач составляют задачи оптимизации. Задачи оптимизации  предполагают поиск значений аргументов, доставляющих функции, которую называют целевой, минимальное или максимальное значение при наличии каких-либо дополнительных ограничений. MS Excel располагает мощным средством для решения оптимизационных задач. Это инструмент-надстройка, который называется «Поиск решения» и доступен через меню Сервис - Поиск решения.

       «Поиск решения» предоставляет  возможность:

1. использования планов большой размерности (т.е. с большим количеством варьируемых переменных);
2. задания ограничений сложного вида;
3. отыскания оптимального из допустимых решений;
4. генерирования множества различных решений, сохраняемых в дальнейшем в виде сценариев;
5. автоматического создания отчета по решению задачи.

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

      - целевую функцию;

      - максимум или минимум (далее - оптимальный план) целевой функции;

      - ограничения на решение в виде системы линейных уравнений (либо неравенств);

      - требование неотрицательности переменных.