Упражнения. Используя созданную матрицу как источник данных, вычислить:

Используя созданную матрицу как источник данных, вычислить:

13.1. Сумму элементов первых трех столбцов.

13.2. Сумму элементов 1, 2 и 4-й строк.

13.3. Наибольшее, наименьшее и среднее значения для блока ячеек, состоящего из 3, 4 и 5-й строк.

13.4.Общее количество чисел, превышающих 3.

Работа 14. Матричные вычисления

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

- ТРАНСП – транспонирование матрицы;

- МОБР – вычисление обратной матрицы;

- МОПРЕД – вычисление определителя матрицы;

- МУМНОЖ – умножение матриц.

Функция ТРАНСП находится в категории Ссылки и массивы, остальные – в категории Математические.

Формулы, создаваемые с помощью этих функций, используются как формулы массива.

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

Ввод формулы массива заканчивается нажатием комбинации клавиш [Ctrl/Shift+Enter] либо [Ctrl/Shift]+ [ОК] при использовании Мастера функций. При этом формула массива автоматически заключается в фигурные скобки {}.

Задание 14.1. В книге «Матрицы» добавить лист. Дать ему имя «МатрВычисл».

Пример 14.1. В горизонтальном диапазоне ячеек имеется текст. Поменять ориентацию массива с текстом с горизонтальной на вертикальную.

Пример будем выполнять на листе «МатрВычисл».

Пусть, например, в диапазоне В3:Е3 находится массив текстовых значений: «первый», «второй», «третий», «четвертый». Новый массив разместим в ячейках Н3:Н6.

Такая задача может быть решена с помощью функции ТРАНСП, которая возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. Функция ТРАНСП должна быть введена как формула массива в интервал, который имеет столько же строк и столбцов, сколько столбцов и строк имеет массив-аргумент.

4 В ячейки В3:Е3 поместить: «первый», «второй», «третий», «четвертый» Þ выделить диапазон для вставки Н3:Н6 Þ Вставка функции Þ ТРАНСП (из категории Ссылки и массивы) Þ в окне Аргументы функции Массив: В3:Е3 Þ [Ctrl/Shift+OK] (или [Ctrl/Shift+Enter]).

Пример 14.1а. Другой вариант транспонирования матрицы.

Результат поместим в ячейки К3:К6.

4 Выделить диапазон В3:В6 Þ команда меню Правка/Копировать Þ выделить диапазон для вставки К3:К6 Þ команда меню Правка/Специальная вставка Þ в окне Специальная вставка установить флажок Транспонировать Þ [ОК].

Внешне результат один и тот же, но есть существенная разница. Если мы в исходной матрице изменим какое-нибудь значение (например, «первый» на «десятый»), то при использовании функции ТРАНСП в результирующей матрице также произойдет изменение, а при использовании варианта копирования – нет.

Пример 14.2. Вычислить определитель матрицы.

4 Задать квадратную матрицу размером 2´2 в ячейках В12:С13 Þ выделить ячейку для вставки значения определителя (пусть это будет ячейка Е12) Þ вставить формулу «=МОПРЕД(B12:C13)», используя Мастер функций. Функция МОПРЕД находится в категории Математические. Не забыть закончить ввод формулы нажатием [Ctrl/Shift+OK] или [Ctrl/Shift+Enter].

4Подсчитать вручную определитель матрицы и убедиться в совпадении результатов.

Пример 14.3. Выполнить умножение матрицы размером 3´5 на матрицу размером 5´2.

4 Задать исходные матрицы в ячейках B16:F18 и H16:I20 Þ выделить для размещения результата диапазон K16:L18 размером 3´2 (3 – это количество строк первой матрицы, 2 – количество столбцов второй матрицы) Þ Вставка функции Þ МУМНОЖ (в категории Математические).

4 В окне Аргументы функции в строке Массив1 указать диапазон первой матрицы, в строке Массив2 – диапазон второй матрицы Þ [Ctrl/Shift+OK].

Пример 14.4. Имеется квадратная матрица. Построить обратную матрицу.

Пусть исходная матрица размером 3´3 находится в ячейках С24:Е26. Обратную матрицу поместим в ячейки G24:I26.

4 В ячейки С24:Е26 записать произвольные числа (это исходная матрица).

4 Выделить диапазон G24:I26 для вставки обратной матрицы Þ Вставка функции Þ функция МОБР в категории Математические Þ в окне Аргументы функции в строке Массив указать диапазон исходной матрицы Þ [Ctrl/Shift+OK].

В ячейках G24:I26 должна появиться матрица, обратная исходной.

Если в ячейках результата будет сообщение об ошибке «#ЧИСЛО!», это будет означать, что для исходной матрицы обратная не может быть вычислена. Измените некоторые числа исходной матрицы, и ошибка исчезнет.

4 Для контроля выполнить умножение исходной матрицы на полученную обратную и результат поместить в ячейки К24:М26. Должна получиться единичная матрица.

Внимание!!! Единичная матрица – это квадратная матрица, все элементы главной диагонали которой равны 1, а остальные элементы – 0. В некоторых позициях матрицы вместо нулей могут быть записаны числа порядка 10^-15 – 10^-18. Это точность, с которой производятся вычисления, так что по сравнению с единицей такие числа можно приближенно считать равными нулю.

Пример 14.5. Решить систему линейных алгебраических уравнений:

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

A´X=B,

где А – матрица коэффициентов;

Х – матрица-столбец неизвестных;

В – матрица-столбец свободных членов.

Решение можно записать в виде

Х=А^-1 ´ В,

где А^-1 – матрица, обратная матрице А.

4 В диапазоне В31:С32 разместим матрицу коэффициентов

,

Þ в диапазоне Е31:Е32 – матрицу-столбец свободных членов

.

4 Вычислим и разместим в диапазоне G31:H32 матрицу А^-1 (выделим диапазон G31:H32 Þ Вставка функции Þ функция МОБР в категории Математические Þ в окне Аргументы функции в строке Массив указать диапазон исходной матрицы В31:С32 Þ [Ctrl/Shift]+[OK]).

4 Вычислим и разместим в диапазоне J31:J32 произведение А^-1 ´ В ( выделим диапазонJ31:J32 Þ Вставка функции Þ функция МУМНОЖ Þ Массив1 G31:H32 Þ Массив2 Е31:Е32 Þ [Ctrl/Shift]+[OK]). Получим матрицу-столбец Х – решение системы уравнений.

4 А теперь для проверки перемножим матрицы А и Х и убедимся, что получилась матрица В.

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

Для решения поставленной задачи в функции МУМНОЖ в качестве первого аргумента используем функцию МОБР:

4 Выделить диапазон М31:М32 для размещения результата Þ Вставка функции Þ функция МУМНОЖ Þ Массив1: МОБР(B31:C32) Þ Массив2: E31:E32 Þ [Ctrl/Shift]+[OK].

4 Сравнить результаты обоих вариантов. Они должны совпадать.

Упражнения

14.1. Создать 2 матрицы: 4´6 и 6´4. Выполнить их умножение.

14.2. Вычислить определитель полученной в упражнении 14.1 матрицы.

14.3. Построить матрицу, обратную полученной в упражнении 14.1. Убедиться, что она действительно обратная.

14.4. Решить систему линейных алгебраических уравнений:

Выполнить проверку полученного решения.

14.5. Выполнить двумя способами транспонирование квадратной матрицы, полученной в упр. 14.1.

Работа 15. Диаграммы

Диаграмма – это общее понятие для группы графических объектов, используемых в Excel для наглядного представления данных.

Задание 15.1. Создать новую книгу с именем «Диаграммы».

Задание 15.2. Листу 1 книги «Диаграммы» дать имя «Продукция». На этом листе построить таблицу, отображающую выпуск сельхозпродукции по месяцам. Таблицу разместить начиная с ячейки В4 (слово «месяц» должно быть в ячейке В5). Отформатировать таблицу. Данные для таблицы приведены ниже.

Производство животноводческой продукции
месяц	молоко, т	мясо, т	шерсть, кг	яйцо, тыс
янв
фев
мар
апр
май
июн
июл
авг
сен
окт
ноя
дек
Итоги

Задание 15.3. Вычислить результаты для строки «Итоги» (произвести суммирование по столбцам).

Пример 15.1. Построить круговую диаграмму, отображающую производство молока с января по май.

4 Инструмент Мастер диаграмм (или команда меню Вставка/Диаграмма…).

4 В окне Мастер диаграмм (шаг 1 из 4) выбрать вкладку Стандартные, тип: Круговая, вид: пусть будет первый вариант Þ [Далее].

4 В окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Диапазон данных Þ установить курсор мыши в строку Диапазон Þ выделить диапазон С6:С10 Þ установить переключатель Ряды в: в положение в столбцах Þ вкладка Ряд Þ установить курсор мыши в строку Подписи категорий Þ выделить диапазон В6:В10 Þ [Далее].

4 В окне Мастер диаграмм (шаг 3 из 4) вкладка Заголовки Þ Название диаграммы: «Производство молока» Þ [Далее].

4 В окне Мастер диаграмм (шаг 4 из 4) на панели Поместить диаграмму на листе: выбрать имеющемся Þ в списке листов выбрать «Продукция» Þ [Готово].

Диаграмму можно перемещать, изменять ее размеры (используя маркеры).

4 Поэкспериментировать с изменением положения и размеров диаграммы.

4 Увеличить размер диаграммы.

Получившаяся диаграмма не очень информативна. Кроме названия диаграммы практически ничего больше нет. Устраним некоторые упущения, допущенные при работе с Мастером диаграмм.

Пример 15.2. Произвести оформление легенды диаграммы примера 15.1.

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

Оформим внешний вид легенды:

4 Выделить (активизировать) область легенды Þ контекстное меню/Формат легенды Þ в окне Формат легенды, используя вкладки Вид, Шрифт, Размещение, произвести оформление Þ [ОК].

Пример 15.3. Произвести оформление рядов данных диаграммы примера 15.1.

4 Выделить (активизировать) область рядов данных (щелкнуть мышью внутри круга – области рядов данных) Þ контекстное меню/Формат рядов данных Þ в окне Формат ряда данных, используя вкладки Вид, Подписи данных, Параметры, произвести оформление, обратив особое внимание на вкладку Подписи данных Þ [ОК].

Пример 15.4. Изменить оформление одного из секторов диаграммы.

Изменим оформление, например, сектора февраль.

4 Щелкнуть мышью в любом секторе диаграммы (логичнее, конечно, щелкнуть в секторе февраль) Þ еще раз щелкнуть в секторе февраль (чтобы выделить именно этот сектор) Þ контекстное меню/Формат точки данных Þ и далее использовать средства окна Формат элемента данных.

4 Изменить вид и других секторов.

4 Сдвинуть диаграмму, например, вправо, чтобы она не отвлекала нас при выполнении последующих примеров.

Пример 15.5. Построить диаграмму-гистограмму, отображающую производство молока и мяса по месяцам в течение года.

Построим диаграмму в два шага, а потом будем видоизменять ее.

4 Инструмент Мастер диаграмм Þ в окне Мастер диаграмм (шаг 1 из 4) вкладка Стандартные Þ тип: Гистограмма, вид: первый вариант Þ [Далее].

4 В окне Мастер диаграмм (шаг 2 из 4) вкладка Диапазон данных Þ установить курсор мыши в строку Диапазон (очистить, если надо, содержимое строки Диапазон) Þ в таблице с исходными данными выделить диапазон C6:D17 Þ вкладка Ряд Þ на панели Ряд ряду1 дать имя «Молоко», ряду2 – имя «Мясо» Þ установить курсор мыши в строку Подписи по оси Х Þ выделить в таблице диапазон В6:В17 с названиями месяцев Þ [Готово].

4 Добавить на диаграмму ее название: курсор мыши на область диаграммы Þ контекстное меню/Параметры диаграммы Þ вкладка Заголовки Þ Название диаграммы: «Производство молока и мяса» Þ [ОК].

4 Подкорректировать надписи по оси Х: курсор мыши установить на разметку оси Х (появится всплывающая подсказка «Ось категорий») Þ контекстное меню/Формат оси Þ в окне Формат оси поработать с вкладками Шрифт, Выравнивание Þ [ОК].

4 Изменить цвет столбика «Мясо»: установить курсор мыши на любой столбик «Мясо» Þ контекстное меню/Формат рядов данных Þ в окне Формат ряда данных вкладка Вид Þ и т. д.

4 Выполнить цветовое оформление области диаграммы: установить курсор мыши на область диаграммы (всплывающая подсказка Область диаграммы) Þ контекстное меню/Формат области диаграммы Þ в окне Формат области диаграммы вкладка Вид Þ и т. д.

4 Выполнить цветовое оформление области построения диаграммы: установить курсор мыши на область построения диаграммы (всплывающая подсказка Область построения диаграммы) Þ контекстное меню/Формат области построения Þ в окне Формат области построения вкладка Вид Þ и т. д.

Пример 15.6. Построить графики функций sinx и cosx для х от 0 до 360 градусов.

Сначала построим таблицу со значениями функций sinx и cosx, которая будет являться источником данных для построения графиков.

4 В книге «Диаграммы» листу 2 дать имя «Синус».

4 В ячейках В4:Е4 поместим названия столбцов: «х, град», «х, рад», «sinx», «cosx».

4 В столбец «х, град» записать значения х от 0 до 360 (автозаполнение, арифметическая прогрессия, шаг 30).

4 В столбец «х, рад» записать значения х в радианной мере: в ячейку С5 поместить формулу «=РАДИАНЫ(B5)» Þ скопировать эту формулу в ячейки С6:С17.

4 В столбцы «sinx» и «cosx» поместить формулы для расчета этих функций.

А теперь построим графики.

4 Выделить ячейки со значениями функций sinx и cosx (D5:E17) Þ Мастер диаграмм Þ в окне Мастер диаграмм (шаг 1…) вкладка Стандартные Þ тип: график.

4 Просмотреть разные варианты построения графика: выбирать вид графика, нажимать и удерживать кнопку [Просмотр результата].

4 Выберем вариант 4: график с маркерами Þ [Готово].

На рабочем листе должна появиться диаграмма с графиками функций.

Пример 15.7. Изменение внешнего вида графика.

4 Увеличить размеры графика: выделить весь график Þ растянуть область диаграммы путем перемещения маркеров.

4 Изменить цвет фона области построения диаграммы: выделить область построения диаграммы Þ контекстное меню/Формат области построения Þ выбрать вид заливки и рамки области построения диаграммы.

4 Изменить вид области диаграммы: выделить область диаграммы Þ контекстное меню/Формат области диаграммы Þ и т. д.

4 Изменить внешний вид кривой графика: установить курсор мыши в любую точку кривой Þ контекстное меню/Формат рядов данных Þ в окне Формат рядов данных выбрать вкладку Вид Þ поэкспериментировать с внешним видом маркеров и линии графика (попробуйте вариант отсутствия маркера). В ходе эксперимента установите флажок Сглаженная линия и обратите внимание на некоторое изменение внешнего вида кривой графика (она стала более плавной).

Пример 15.8. Изменить внешний вид и обозначение осей графика.

Напомним, что ось Х называется осью категорий, а ось Y – осью значений.

4Поместить курсор мыши на ось Х (должна появиться всплывающая подсказка Ось категорий) Þ контекстное меню/Формат оси.

4 В окне Формат оси выбирая вкладки Вид, Шрифт, Выравнивание, поэкспериментировать с параметрами оси Þ остановиться на наиболее привлекательном варианте.

4 Аналогично установить параметры Вид, Шрифт, Выравнивание для оси Y.

Пример 15.9. Установить разметку шкалы оси Х в соответствии с исходными данными в таблице.

Для разметки оси Х используем данные в ячейках В5:В17.

4 Щелкнуть в области диаграммы Þ контекстное меню/Исходные данные Þ в окне Исходные данные вкладка Ряд Þ щелкнуть в строке ввода Подписи по оси Х Þ задать диапазон В5:В17 значений Х (в исходной таблице выделить столбец со значениями Х (В5:В17) Þ в строке ввода Подписи по оси Х должно появиться выражение «=Синус!$B$5:$B$17» Þ [ОК].

4 Подкорректировать, если надо, внешний вид и обозначение осей графика.

Пример 15.10. Установить пересечение оси Y с осью Х в точке (0,0).

4 Поместить курсор мыши на ось категорий Þ контекстное меню/Формат оси Þ вкладка Шкала Þ сбросить флажок пересечение с осью Y (значений) между категориями Þ [ОК].

Пример 15.11. Добавить на график линии сетки.

4 Установить курсор мыши в любом месте области диаграммы Þ контекстное меню/Параметры диаграммы Þ закладка Линии сетки Þ поэкспериментировать с линиями сетки (устанавливая и сбрасывая флажки Ось Х и Ось У.

4 Поэкспериментировать с внешним видом линий сетки: установить курсор на линию сетки (горизонтальную или вертикальную) Þ контекстное меню/Формат линий сетки Þ вкладка Вид Þ и т.д.

Пример 15.12. Задать границы графика по оси Y.

Предположим, что нам необходимо наблюдать значения кривых по оси Y лишь в определенных пределах, например, от –0,5 до 0,7.

4 Установить курсор на ось Y (появится всплывающая подсказка Ось значений) Þ контекстное меню/Формат оси Þ вкладка Шкала Þ установить минимальное значение (–0,5), максимальное (0,7) Þ сбросить флажки Авто для минимального и максимального значений (если они установлены) Þ [ОК].

4 А теперь установить границы значений по оси Y в пределах от –1 до 1.

Пример 15.13. Дать название диаграмме и осям графика.

4 В области диаграммы контекстное меню/Параметры диаграммы Þ вкладка Заголовки Þ Название диаграммы: «График функции» Þ Ось Х: «градусы» Þ Ось Y: «Синус, косинус» Þ [ОК].

4 Если мало места для размещения названий, можно растянуть всю диаграмму и уменьшить область построения диаграммы (выделить эту область и переместить маркеры).

4 Не нравится шрифт названия диаграммы и названия осей? Установить курсор на нужное название Þ контекстное меню и т.д.

Пример 15.14. Изменить оформление легенды.

4 Область диаграммы Þ контекстное меню/Исходные данные Þ вкладка Ряд Þ на панели Ряд выбрать Ряд1 Þ дать имя «Синус» Þ Выбрать Ряд2 Þ дать имя «Косинус» Þ [ОК].

4 Для изменения вида легенды: курсор мыши на область легенды Þ контекстное меню/Формат легенды и т. д.

Упражнения

15.1. Построить круговую диаграмму,отображающую производство шерсти с июня по декабрь.

15.2. Построить диаграмму-гистограмму, отображающую производство молока, мяса и яиц в течение года.

15.3. Построить график функции для х от 0 до 40 с шагом 1.

Выполнить оформление графика.

15.4. Построить график функции для х от –6 до 6 с шагом 0,5. Считается, что аргумент х для функции cos задан в радианах.

Выполнить оформление графика.

15.5. Построить график функции для х от –7 до 7 с шагом 0,5.

Считается, что аргумент х для функции sin задан в радианах.

Для решения проблемы вычисления функции y при х=0 следует вспомнить первый замечательный предел.

Работа 16. Построение линии тренда

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

Задание 16.1. Создать книгу с именем «Задачи». Листу 1 дать имя «Тренд».

Пример 16.1. Имеется ряд из 20 значений (104; 100; 83; 123; 114; 109; 118; 152; 124; 161; 140; 145; 154; 155; 122; 134; 113; 131; 126; 108), характеризующих ежедневное изменение курса доллара к некоторой валюте с названием «денежка». Спрогнозировать поведение денежки по отношению к доллару в ближайшее время.

4В ячейки В3, С3 поместить текст-разметку: «день», «курс, днж» Þ в ячейки В4:В23 внести порядковые номера дней (1..20) Þ в ячейки С4:С23 внести 20 значений курса доллара из предлагаемого ряда.

4Построить график изменения курса доллара: выделить диапазон С4:С23 Þ Мастер диаграмм Þ вкладка Стандартные Þ Тип: График Þ Вид: первый вариант Þ [Готово].

4Задать на графике нужные названия: вобласти диаграммы контекстное меню/Параметры диаграммы Þ в окне Параметры диаграммы вкладка Заголовки Þ Название диаграммы: «Стоимость $1» Þ Ось Х: «дни» Þ Ось Y: «денежки» Þ [ОК].

4Добавить на график линию тренда: установить курсор мыши на линию графика Þ щелкнуть правой кнопкой мыши Þ Добавить линию тренда Þ в окне Линия тренда вкладка Тип Þ вариант Линейная Þ [ОК].

Видим, что линия тренда направлена вверх. Значит ли это, что доллар по отношению к денежке будет все время дорожать? Увы, не все так просто.

Продолжим исследование имеющихся данных.

4Задать параметры линии тренда: установить курсор мыши на линию тренда Þ щелкнуть правой кнопкой мыши Þ Формат линии тренда Þ в окне Формат линии тренда вкладка Параметры Þ установить флажок показывать уравнение на диаграмме Þ [ОК].

На диаграмме появится уравнение линии тренда: y = 1,3203х+111,94. Если уравнение наложилось на график, его можно сместить: выделить (щелкнуть мышью) и переместить рамку. Если нужно, можно растянуть и область диаграммы.

Качество аппроксимации линией тренда фактических данных оценивается величиной R², значение которой лежит в пределах от 0 до 1. Чем ближе линия тренда к фактическим данным, тем ближе значение R² к 1.

4Поместить на диаграмму значение R²: установить курсор мыши на линию тренда Þ щелкнуть правой кнопкой мыши Þ Формат линии тренда Þ в окне Формат линии тренда вкладка Параметры Þ установить флажок поместить на диаграмму величину достоверности аппроксимации (R^2) Þ [ОК]. Получим R² = 0,1414.

4Выполнить аппроксимацию данных полиномом второй степени: установить курсор мыши на линию тренда Þ щелкнуть правой кнопкой мыши Þ Формат линии тренда Þ в окне Формат линии тренда вкладка Тип: Полиномиальная, Степень: 2 Þ вкладка Параметры Þ установит флажки показывать уравнение на диаграмме, поместить на диаграмму величину достоверности аппроксимации (R^2) Þ [ОК]. Получим R² = 0,5938.

4Поэкспериментировать с разными типами аппроксимации.

Пример 16.2. Попробуем спрогнозировать курс доллара на 5 дней вперед.

4 Активировать диаграмму предыдущего примера Þ установить курсор мыши на линию тренда Þ щелкнуть правой кнопкой мыши Þ Формат линии тренда Þ в окне Формат линии тренда вкладка Параметры Þ на панели Прогноз значение вперед на: задать равным 5 единиц Þ [ОК].

4Поэкспериментировать с разными типами аппроксимации.

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

Упражнения

16.1. Ежегодная популяция зайцев в течение 12 лет в некотором регионе характеризуется рядом значений (2820; 4400; 5390; 5930; 6150; 6440; 6890; 7460; 7490; 7180; 7620; 8010). Подобрать аппроксимацию экспериментальных данных и сделать прогноз на ближайшие 5 лет.

16.2. В некотором не очень чистом месте еженедельный подсчет количества мух дал следующий ряд: 2; 58; 1700; 57000. Определить, через сколько недель количество мух может достичь 1 млрд, если не будет никаких препятствий для их размножения.

Работа 17. Решение уравнений

Для решения оптимизационных задач и уравнений в Excel имеется мощный инструмент – надстройка «Поиск решения». Этот инструмент можно использовать и для численного решения уравнений.

Задание 17.1. Убедиться, что на компьютере установлена надстройка «Поиск решения».

4 Выполнить команду меню Сервис.

Если в раскрывшемся подменю есть команда Поиск решения, значит, надстройка установлена. Если нет, то выполнить установку:

4Команда меню Сервис/Надстройки Þ в окне Надстройки в качестве доступной надстройки установить Поиск решения Þ [ОК].

Задание 17.2. Открыть книгу «Задачи». Листу 2 дать имя «Уравнения».

Пример 17.1. Решить уравнение .

Начнем с некоторых уточнений и предварительного анализа задачи.

Прежде всего, следует определить границы переменной х, в пределах которых ведется поиск. Пусть это будет вся числовая ось (-¥; +¥).

Поскольку у нас уравнение третьего порядка, то количество вещественных корней (а эта надстройка позволяет найти только вещественные корни) может достигать 3-х.

Для начала очень полезно (хотя и не обязательно) построить график функции. Выберем для построения графика отрезок [0;3] с шагом 0,2. Если он окажется неудачным, выберем другой. Итак:

4На листе «Уравнения» в ячейки А3:А18 записать значения х от 0 до 3 с шагом 0,2 (арифметическая прогрессия) Þ в ячейку В3 поместить формулу

«=A3^3-4,1*A3^2+4,55*A3-1,375»

Þ скопировать эту формулу в ячейки В4:В18 Þ выделить диапазон В3:В18 Þ Мастер диаграмм Þ вкладка Нестандартные Þ Тип: Гладкие графики Þ [Далее] Þ в окне Мастер диаграмм (шаг 2…) вкладка Ряд Þ Подписи по оси Х: выделить диапазон А3:А18 Þ [Готово].

На графике хорошо видно, что наша кривая трижды пересекает ось Х, следовательно, все 3 корня уравнения вещественные. Можно даже приближено оценить их значения.

Одна попытка применения инструмента «Поиск решения» позволяет найти лишь один корень. Для нахождения трех корней придется сделать, по крайней мере, 3 попытки. При каждой попытке придется задавать некоторое начальное значение х, для выбора которого график функции оказывается очень полезным.

Итак, начинаем.

4В ячейки А21, В21 поместим текст «х», «f(x)» соответственно (это разметка ячеек.

4В ячейку В22 поместим формулу для вычисления функции: «=A22^3-4,1*A22^2+4,55*A22-1,375».

4В ячейку А22 поместим начальное значение х – точку, от которой будет идти поиск корня (пусть это будет 0,2).

4Команда меню Сервис/Поиск решения.

4В окне Поиск решения Установить целевую ячейку: $B$22 (набрать с клавиатуры, а лучше установить курсор мыши в эту строку ввода и щелкнуть мышью в ячейку В22) Þ установить переключатель Равной значению: Þ задать значение 0 Þ Изменяя ячейки: $A$22 Þ [Выполнить].

В окне Результаты поиска решения появится сообщение о том, что решение найдено либо нет.

Если решение найдено, оно находится в ячейке А22. Для наших данных оно найдено и равно 0,5 (сравните со значением на графике).

Обратим внимание на полученное при этом значение функции f(x) – величину порядка 10^-7. Теоретически должен получиться 0, но поскольку в надстройке реализован приближенный метод, то все расчеты выполняются с некоторой погрешностью. Впрочем, точностью расчетов можно управлять.

4Аналогичным образом найти оба оставшихся корня, в качестве начального задавая значения х =1 и х =2,2. Должны получиться корни х =1,1 и х =2,5.

Пример 17.2. Найти корни функции на отрезке [2;3].

Задача практически та же, что и в предыдущем примере, но здесь есть ограничения на значения переменной х. Эти ограничения нужно указать в окне Поиск решения. Для решения будем использовать ячейки А22, В22 с формулами предыдущего примера.

4В ячейку А22 поместим начальное значение х =1 Þ Сервис/Поиск решения Þ Установить целевую ячейку: $B$22 Þ Равной значению: 0 Þ Изменяя ячейки: $A$22 Þ [Добавить] (нажатие этой кнопки инициирует добавление ограничений) Þ в окне Добавление ограничений Ссылка на ячейку: $A$22 Þ выбрать >= Þ Ограничение: 2 Þ [Добавить] Þ Ссылка на ячейку: $A$22 Þ выбрать <= Þ Ограничение: 3 Þ [ОК] Þ [Выполнить].

Получим х =2,5. Коль скоро мы искали решение на отрезке [2;3], не вполне корректно было задавать начальное значение вне этого отрезка, но решение найти удалось.

Упражнения

17.1. Решить уравнение lnx=1/x. Построить графики функций lnx и 1/х для х от 0,2 до 3 с шагом 0,2. Сравнить результаты графического решения и решения методом подбора параметра.

17.2. Решить уравнение .

Указание. Предварительно построить график функции для х от –2 до 2 с шагом 0,2. Из графика определить приближенные значения корней (точки перехода функции через ноль). Их должно быть два. Затем методом подбора параметра уточнить значения корней, задавая в качестве начального значения величины, близкие к найденным из графика.

17.3. Решить уравнение .

Величина х задается в радианах. Использовать рекомендации предыдущего упражнения. График функции строить с шагом 0,2.

17.4. Решить уравнение .

Работа 18. Решение систем уравнений

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

Задание 18.1. Открыть книгу «Задачи». Вставить новый лист. Дать ему имя «СистемыУравнений».

Пример 21.1. Решить систему линейных алгебраических уравнений:

Это задача упражнения 14.4.

Переформулируем задачу следующим образом: найти значения переменных (х₁, х₂, х₃, х₄), при которых функция

принимает значение, равное 10, при выполнении системы ограничений

4На листе «Системы Уравнений» размечаем ячейки B3:G3: «х1», «х2», «х3», «х4», «правая часть», «вычисл. правая».

4В ячейках В6:Е9 разместим матрицу коэффициентов исходной системы:

.

4В ячейках F6:F9 разместим столбец правых частей системы уравнений: (10, 14, 2, 24).

4Полезно убедится, что определитель системы уравнений не равен нулю, что гарантирует единственность полученного решения. Вычислим определитель, например, в ячейке А3 по формуле «=МОПРЕД(B6:E9)». Для наших данных должно получится -158.

4В ячейку G6 поместим формулу вычисления левой части первого уравнения системы «={СУММ($B$4:$E$4*B6:E6)}», используя методику формулы массива: курсор мыши в ячейку G6 Þ вводим формулу =СУММ($B$4:$E$4*B6:E6) Þ [Ctrl/Shift+Enter].

4Формулу из ячейки G6 копируем в ячейки G7:G9.

4В ячейках В4:Е4 задать произвольные начальные значения переменных, например, все равные 1.

4Команда меню Сервис/Поиск решения Þ Установить целевую ячейку $G$6 Þ Равной: значению 10 Þ Изменяя ячейки: выделить диапазон B4:E4 Þ очистить панель Ограничения (если там были записи) Þ Ограничения: [Добавить] Þ Ссылка на ячейку: выделить диапазон G7:G9 Þ выбрать = (знак равенства) Þ Ограничение: выделить диапазон F7:F9 Þ [ОК] Þ [Выполнить].

В ячейках В4:Е4 должен получиться результат решения системы: (1; 3; 2; 4).

Пример 18.2. Решить систему уравнений с двумя неизвестными

Поступим следующим образом. Первое уравнение (точнее, выражение

зададим в качестве целевой функции, т.е. потребуем, чтобы оно приняло значение, равное нулю, а второе уравнение зададим как ограничение. Итак:

4 В ячейки В14:Е14 поместим обозначения ячеек: «x», «y», «f1», «f2».

4 В ячейку D15 поместим формулу «=B15^2+C15*EXP(B15)-EXP(C15)-1» Þ в ячейку Е15 поместим формулу «=2*B15^3+3*B15*C15-EXP(2*B15)+4».

4В ячейки В15, С15 поместим начальные значения переменных: х =1; у =1.

4Команда меню Сервис/Поиск решения Þ Установить целевую ячейку $D$15 Þ Равной: значению 0 Þ Изменяя ячейки: $B$15:$C$15 Þ очистить панель Ограничения (если там были записи) Þ Ограничения: [Добавить] Þ Ссылка на ячейку: $E$15 Þ выбрать = (знак равенства) Þ Ограничение: 0 Þ [ОК] Þ [Выполнить].

Получаем решение: х @1,052788; у @0,594639.

Попробуем поискать другие решения системы.

4Выполнить поиск для начальных значений х=10; у=0 (решение х @-1,35597; у @-0,2588).

4Выполнить поиск для начальных значений х=20; у=20. После запуска программы на исполнение при этих начальных значениях скорее всего появится окно Текущее состояние поиска решения с сообщением «Достигнуто максимальное число итераций. Продолжить?». Это значит, что предельное количество шагов поиска решения, установленное в программе (по умолчанию это обычно 100), исчерпано. Принимаем решение на продолжение поиска: нажать [Продолжить].

Получаем решение: х @1,634111; у @2,761423.

4Попробуйте поискать решения при других начальных значениях. Вдруг удастся найти еще.

Упражнения

18.1. Решить систему уравнений:

18.2. Решить систему линейных алгебраических уравнений:

Выполнить проверку полученного решения.

Литература

1. Безручко В. Т. Практикум по курсу «Информатика». Работа в Windows, Word, Excel: Учеб. Пособие. М.: Финансы и статистика, 2003.
2. Основы информатики: Учеб. Пособие / Ф. Н. Морозевич, Н. Н. Говядинова, В. Г. Левашенко и др.; Под ред. А.Н. Морозевича. Мн.: Новое знание, 2001.
3. Символоков Л. В. Решение бизнес-задач в Microsoft Office – М.: Бином, 2001.

Дата добавления: 2014-12-27