Информатика. Методические указания для выполнения контрольных работ (стр. 11 )

Ø  опция «другая» группы «Линия», для которой выбраны: сплошная линия (список «тип линии:»), серый цвет (список «цвет:»), средняя толщина (список «толщина:») ;

Ø  опция «отсутствует» группы «Маркер».

3.7 Использование логических функций

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

ü  ЛОЖЬ - Возвращает логическое значение ЛОЖЬ.

ü  ИСТИНА – Возвращает логическое значение ИСТИНА.

ü  И - Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА. Иначе, если хотя бы один аргумент имеет значение ЛОЖЬ, возвращается значение ЛОЖЬ.

ü  ИЛИ - Возвращает ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.

ü  НЕ - Меняет на противоположное логическое значение своего аргумента. Например, =НЕ(ИСТИНА ()) равняется ЛОЖЬ.

Рассмотрим более подробно функцию ЕСЛИ, которая позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое - если оно ложно.

ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)

Лог_выражение - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина - это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть любой формулой.

Значение_если_ложь - это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть любой формулой.

Например, в ячейках B2:B4 содержатся фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно, а в ячейках C2:C4 приведены данные по предполагаемым расходам за те же периоды: 900, 900 и 925. Тогда формула

=ЕСЛИ(B2>C2;"Превышение бюджета"; "OK")

возвратит значение "Превышение бюджета",

3.8 Работа с массивами

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

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

Функции первой группы возвращают ответ в виде числа, это:

Ø  СУММ(число1;число2;...)

Ø  МИН (число1;число2; ...) ...) или МАКС(число1;число2; ...).

Число1, число2, .. - это от 1 до 30 аргументов, для которых требуется определить сумму, минимум или максимум. Например, если ячейки A2:E2 содержат числа 5, 15, 30, 40 и 50, то СУММ(A2:C2) равняется 50, СУММ(B2:E2; 15) равняется 150, МАКС(A21:Е2) равняется 50.

Ø  СУММПРОИЗВ(массив1;массив2;массив3; ...)

Массив1, массив2, массив3, ... - это от 2 до 30 массивов, чьи компоненты нужно перемножить, а затем сложить. Массивы, используемые в качестве аргументов, должны иметь одинаковые размерности. Например при вызове функции СУММПРОИЗВ({3;4:8;6:1;9};{2;7:6;7:5;3}) вычисления производятся по формуле: 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3, а результат равняется 156

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

ü  выделить область такого размера, какого размера должен быть массив, выдаваемый в качестве ответа, например, А5:С7, если ответом будет матрица из трёх строк и трёх столбцов;

ü  задать аргументы, необходимые для правильной работы выбранной функции;

ü  при завершении создания функции вместо клавиши «Enter» или кнопки «ОК» одновременно нажать на три клавиши: «Ctrl» + «Shift» + «Enter» . Лучше всего сделать это так: пальцами одной руки нажать на две клавиши «Ctrl» + «Shift» и, не отпуская их, пальцем другой руки нажать на клавишу «Enter».

Наиболее распространёнными функциями этой группы являются функции, позволяющие работать с матрицами, например:

Ø  МОБР(массив). Возвращает обратную матрицу для матрицы, хранящейся в массиве. Массив - это числовой массив с равным количеством строк и столбцов. Массив может быть задан: как диапазон ячеек, например, A1:C3; или как массив констант.

Ø  МУМНОЖ(массив1;массив2). Возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1, и с таким же числом столбцов, как массив2.

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

Решаем систему линейных уравнений методом обратной матрицы. Для этого необходимо:

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

Ø  вычислить обратную матрицу при помощи функции МОБР;

Ø  вычислить значения неизвестных, умножив обратную матрицу на матрицу правых частей при помощи функции МУМНОЖ.

В приложении 1 приведена система трёх линейных уравнений с тремя неизвестными и её решение методом обратной матрицы.

3.8.2 Задача аппроксимации

Постановка задачи: Дана дискретная (табличная) зависимость Yi от Xi. Требуется найти ее аналитическую формулу (аппроксимирующую зависимость) в виде параболы Yp = a*X2 + b*X + c. Таким образом, задача сводится к нахождению числовых значений коэффициентов a, b, и c. При использовании метода наименьших квадратов эти значения находят как решение системы трех линейных уравнений (1) с тремя неизвестными a, b, c. Для этого необходимо выполнить следующие действия:

ü  На рабочем листе создать таблицу для расчета коэффициентов и правых частей системы линейных уравнений (1). В каждом столбце таблицы вычисляется один из коэффициентов или правых частей системы (1). При этом Хi и Yi – значения, данные в исходной таблице. В последней строке таблицы вычислить суммы по столбцам.

ü  Записать матрицы коэффициентов и правых частей системы (1). Коэффициенты при неизвестных a, b, c и правые части - это суммы из последней строки расчетной таблицы, а коэффициент «n» равен количеству точек в исходной таблице.

ü  Решить систему линейных уравнений методом «Обратной матрицы» как описано в разделе 3.8.1.

ü  Выписать искомое выражение для Ур и рассчитать значения полученной функции для исходных значений Xi.

ü  Проверьте себя: значения Yp и Yi в соответствующих точках должны быть достаточно близки.

3.9 Использование специальных средств для решения
оптимизационных задач

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

Простейшим случаем такой задачи является определение корня нелинейного уравнения. Уравнение f (x) = 0 будет нелинейным, если в его правой части присутствует неизвестная в степени, отличной от 1, или имеются трансцендентные функции sin x, ln x и т. д. Для некоторых уравнений существуют методы, позволяющие получить точное решение (например, квадратное уравнение). Однако большинство из них не имеет точного аналитического решения. Чаще всего нелинейное уравнение имеет несколько корней, поэтому его приближённое решение состоит из двух этапов, на первом производится грубый подбор или отделение корня, то есть находятся все или хотя бы один отрезок, на котором есть корень, а на втором отделённый корень определяется с заданной точностью. Проиллюстрируем решение на примере уравнения x 3+ 2x + 5 = 0. Это уравнение может иметь 3 действительных корня. Напомним, что корень - это значение х, при котором правая часть уравнения равна 0. В приложении 1 показан первый этап решения – корни отделены графическим методом, т. е. рассчитана таблица значений функции Y=x3+2x+5 для х, изменяющегося от –3 до 3 с шагом 0,5, и построен её график.

Анализируя график, можно сделать следующие выводы:

Ø  Уравнение имеет один действительный корень, поскольку график только один раз пересекает ось Х между значениями –2 и -1.

Ø  Вместо первоначально выбранного отрезка от –3 до 3 для уточнения корня можно взять меньший отрезок от –2 до –1.

Для уточнения решения используем два специальных средства – это «Подбор параметра» и «Поиск решения».

Чтобы использовать средство «Подбор параметра», были произведены следующие действия:

1)  В ячейку С5 было записано граничное значение х – 2 , а в ячейки D5 -формула для расчета функции ( =C5^2+2*C5+5).

2)  Выполнена команда «Подбор параметра» меню «Сервис». В появившемся окне (Приложение1) указаны: адрес функции (поле « Установить в ячейке»); значение, которому должна стать равна функция (поле «Значение»); адрес аргумента (поле «Изменяя значение ячейки»). После щелчка по кнопке «ОК» производится подбор подходящего значения аргумента (-1,328), которое записывается в ячейку С5 , при этом в ячейке D5 появится значение функции 0,000. Это говорит о том, что подбор прошёл успешно и корень равен -1,328.