- •Окно программы
- •П Рис.2анели инструментов
- •Выделение элементов таблицы
- •Заполнение ячеек
- •Отмена операций
- •Работа с формулами Основные сведения
- •Функции
- •Массивы формул
- •Сообщения об ошибках
- •Работа с базами данных
- •Установление диапазона критериев
- •Автофильтр
- •Расширенный фильтр
- •Создание диаграмм
- •Задание 1.
- •Логические функции
- •Массивы формул
- •Алгоритм
- •Алгоритм
- •Задание 3.
- •Задание 4.
- •Лабораторная работа № 2
- •Лабораторная работа №3
- •В) Сервис/ Поиск решений
- •Поражений
- •Номер поезда
- •Фио сотрудника
- •Размер жилплощади на человека
- •Рейтинг
- •Профиль рынка
Алгоритм
Ввод x
Если x-6
y= –x +3
Вывод y
иначе
если x0
Вывод « Функция не определена»
Иначе
если x2
y= x+lnx
Вывод y
Иначе y= x+1
Вывод у
Всеесли
Всеесли
Всеесли
Реализация на Excel:
В ячейки А1 и В1 ввести текст «х» и «у» соответственно.
В диапазон А2:A10 ввести значения х из всех данных интервалов, т.е.
(-∞;-6], (-6;0], (0;2], (2,+∞) по 2-3 значения из каждого.
3.В ячейку F2 ввести текст «Функция не определена».
4. В ячейку В2 ввести формулу:
=ЕСЛИ(A2<=-6;-A2^2+3;ЕСЛИ(A2<=0;$F$2;ЕСЛИ(A2<=2;A2^2+LN(A2);A2^2+1)))
В результате получится следующая таблица значений:
Задача 2.
Вычислить функцию z=f(x,y), учитывая область существования.
Z =+lnxy
Область существования: x-y≥0 x-5≠0 xy>0
Алгоритм
Ввод x,y
Если x-5≠0
если x-y≥0
если xy>0
Z =+lnxy
Вывод y
иначе
Вывод « Логарифм отрицательного числа »
Всеесли
Иначе
Вывод « Корень из отрицательного числа»
Всеесли
Иначе
Вывод « Деление на ноль»
Всеесли
Реализация на Excel:
В ячейки А1 , В1,С1 ввести текст «х» «у» «z» соответственно.
В диапазоны А2:A10, B2:В10 ввести значения х и y из области существования .
3.В ячейку F1 ввести текст «Логарифм отрицательного числа».
4. В ячейку F2 ввести текст «Корень из отрицательного числа».
5. В ячейку F3 ввести текст «Деление на ноль».
6. В ячейку С2 ввести формулу:
=ЕСЛИ(A2-5<>0;ЕСЛИ(A2-B2>=0;ЕСЛИ(A2*B2>0;(A2-B2)^0,5/(A2-5)+LN(A2*B2);$F$1);$F$2);$F$3)
В результате получится следующая таблица значений
Задание 3.
Дана система уравненией с несколькими неизвестными. Найти корни этой системы:
А) методом Крамера (определителей)
Б) методом обратной матрицы
С) поиск решений
При выполнении этой лабораторной работы будут использоваться массивы формул (см.выше) и функции:
МОПРЕД(массив)-
Массив — числовой массив с равным количеством строк и столбцов.
Определитель матрицы — это число, вычисляемое на основе значений элементов массива.
Для массива A1:C3, состоящего из трех строк и трех столбцов, определитель вычисляется следующим образом:
МОПРЕД(A1:C3) равняется A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*С1)
МОБР(массив)
Возвращает обратную матрицу для матрицы, хранящейся в массиве. Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную матрицу — это единичная матрица, то есть квадратный массив, у которого диагональные элементы равны 1, а все остальные элементы равны 0. Если дана матрица вида , то её обратная матрица будет следующей:
Так расчёт выполняется математически, а в Excel эту задачи выполняет функция МОБР() и МОПРЕД().
МУМНОЖ(массив1; массив2)
Возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2. Количество столбцов аргумента массив1 должно быть таким же, как количество сток аргумента массив2, и оба массива должны содержать только числа.
Пример:
Дана система линейных уравнений. Найти корни этой системы
13x - 12x- 14x + 18x = 39
7x + 17x+ 3x + 6x = 60,6
12x + 16x+ 8x + 4x = 59,2
2x - x- 3x + 6x = 7,4
Метод Крамера.
, где Δ – главный определитель
Δx -
В Excel ввести коэффициенты при неизвестных, вектор свободных членов следующим образом:
2.Получить 4 новых матрицы 4х4 путём замены 1-го столбца вектором из свободных членов, далее 2-го столбца –вектором свободных членов, 3-го столбца, 4 -гостолбца.
В результате :
В ячейках Н1: Н5 записать текст:
В ячейках I1:I5 записать формулы:
В ячейках Н7: Н10 записать текст: x1=,x2=,x3=,x4=
В ячейках I7:I10 записать формулы:
Таким образом решается система уравнений методом Крамера.
Решение системы уравнений методом обратной матрицы:
, где- обратная матрица,
В- вектор свободных членов.
1.В Excel ввести коэффициенты при неизвестных, вектор свободных членов следующим образом:
2.Вычислить обратную матрицу:
В ячейке А6 записать формулу: = МОБР(A1:A4)
Выделить диапозон c A6:D9, указатель мыши в строку формул и нажать CTRL+SHIFT+ENTER одновременно.
3.В ячейках Н1: Н4 записать текст: x1=,x2=,x3=,x4=
В ячейках H5:H9 записать формулы
Надстройка «Поиск решения»
Поиск решений является частью блока задач, который иногда называют анализ «что-если». Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.
Решение системы линейных уравнений с использованием надстройки «Поиск решений» выполняется следующим образом:
Даются значения предполагаемых корней.
В целевую ячейку вводится формула какого-либо уравнения с этими корнями.
В ограничения заносятся все остальные уравнения.
1 этап выполнения:
этап выполнения:
3этап выполнения:
После выполнения:
Система уравнений решена.