Файл: В.В. Бочков Информатика. Рабочая программа дисциплины, методические указания и контрольные задания для студентов заочного факультета.pdf
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 31.05.2024
Просмотров: 78
Скачиваний: 0
15
Выполнив команду меню Вставка/Объект и выбрав Microsoft Equation 3.0 (или 2.0), с помощью редактора (Мастера) формул создадим объект –
ln(1 + x) |
|
формулу, задающую вид табулируемой функции y = sin |
esin( πx ) . |
|
x |
Для оформления таблицы значений функции введём заголовки колонок:
1)в ячейку A3 введём текст «Номер»;
2)в ячейку B3 – текст «X»;
3)в ячейку C3 – текст «Y».
В результате выполненных действий рабочий лист Excel примет вид, приведённый на рис. 5. Вводить дан-
Рис. 5. Фрагмент рабочего листа ные и редактировать их на рабочем листе можно различными способами.
3.2.4. Конструирование и ввод формул
Для вычисления величины шага табулирования h=(b-a)/10 в ячейку F2 введём формулу =(D2-B2)/10. С правилами использования и ввода формул, управления вычислениями, применения ссылок и способами адресации можно познакомиться во время лекционных, практических занятий, либо из литературы.
В качестве номеров точек в столбце A можно использовать константы 1,2,3,…, а можно и воспользоваться формулой: номер_текущей_строки-3 (столько строк уже занято), которая в Excel будет выглядеть так: =СТРОКА()-3. Её мы и введём в ячейку A4. Затем, воспользовавшись автозаполнением, протащим ячейку A4 за маркер заполнения (знак + в правом нижнем углу) до ячейки A14 включительно. В результате одна и та же формула, необходимая нам, будет содержаться во всех ячейках интервала
A4:A14.
16
Рис. 6. Фрагмент рабочего листа
тате содержимым ячейки B5 станет формула
=$B$2+(A6-1)*$F$2 и т.д.
Для вычисления текущего значения аргумента можно воспользоваться формулой (далеко не единственной) xi=a+(i-1)h, где i – номер текущей точки. Эту формулу
=$B$2+(A4-1)*$F$2 мы и введём в ячейку B4, затем протащим её за маркер заполнения до ячейки B14 включительно. В резуль-
=$B$2+(A5-1)*$F2, B6 –
При конструировании формулы для вычисления значений функции необходимо учесть её область определения. В нашей ситуации это довольно просто (см. п. 2.2). Должно быть x>–1, а, кроме того, y(0)=sin 1. Воспользовавшись логическими функциями рабочего листа, в ячейку C4 введём формулу, в которой учтены все возможные ситуации:
=ЕСЛИ(B4>-1;ЕСЛИ(ABS(B4)<0,001;SIN(1); SIN(LN(1+B4)/B4)*EXP(SIN(ПИ()*B4)));"Неопред.")
Протащив C4 за маркер заполнения до ячейки C14 включительно, получим в качестве содержимого ячеек интервала C4:C14 необходимые нам формулы, а рабочий лист Excel примет вид, приведённый на рис. 6.
3.2.5. Поиск наибольшего и наименьшего значений функции
Найти наибольшее и наименьшее значения функции среди вычисленных достаточно просто. Для этого можно в ячейку D4 ввести формулу:
=ЕСЛИ(C4=МАКС($C$4:$C$14);"<=макс.";
ЕСЛИ(C4=МИН($C$4:$C$14);"<=мин.";""))
17
После чего скопировать ее перетаскиванием в интервал D5:D14. Тогда рабочий лист Excel примет вид, приведенный на рис. 7.
Однако для решения подобных и гораздо более сложных оптимизационных задач в инструментарии Excel имеется мощное средство – Решатель (Solver), доступ к которому реализо-
ван через пункт меню Сервис/Поиск решения.
После открытия диалога Поиск решения (рис. 8) необходимо выполнить следующие дейст-
Рис. 8. Окно диалога Поиск решения
формула в ней имеет вид:
Рис. 7. Фрагмент рабочего листа
вия:
1) в поле Установить целевую ячейку
ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка – это D15, а
=ЕСЛИ(ABS(F15)<0,001;SIN(1);SIN(LN(1+F15)/F15)*EXP(SIN(ПИ()*F15)));
2)для максимизации значения целевой ячейки установить переключатель максимальному значению в положение , для минимизации используется переключатель минимальному значению;
3)в поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (F15), разделяя их знаком «;» (или щелкая мышью при нажатой клавише Сtrl на соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить;
18
4)в поле Ограничения с помощью кнопки Добавить ввести все ограничения, которым должен отвечать результат поиска: в нашем примере изменяемое значение не должно быть меньше, чем начало интервала ($B$2), а также не должно быть больше конца заданного интервала
($D$2), т.е. $F$15>=$B$2 и $F$15<=$D$2;
5)для запуска процесса поиска решения нажать кнопку Выполнить. Для сохранения получен-
ного решения необходимо ис- |
|
|
пользовать переключатель |
Со- |
|
хранить найденное решение в |
|
|
открывшемся окне диалога Ре- |
|
|
зультаты поиска решения. |
По- |
Рис. 9. Фрагмент рабочего листа |
сле чего рабочий лист примет вид, представленный на рис. 9.
Если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей опти-
мизации |
(например |
найти максимум и |
|
минимум |
одной |
функции, |
или |
наибольшие |
значения |
Рис. 10. Диапазон ячеек для сохраняемой модели нескольких |
функций), |
то удобнее |
сохранить |
эти модели, используя кнопку Параметры / Сохранить модель окна Поиск решения.
Диапазон для сохраняемой модели (рис. 10) содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения диалога Параметры. Выбор модели для решения конкретной оптимизационной задачи осуществляется с помощью кнопки Параметры/ За-
грузить модель диалога Поиск решения.
19 3.2.6. Построение графика
Для построения необходимого нам графика воспользуемся услугами
Мастера диаграмм (кнопка на панели инструментов Стандартная).
Следуя указаниям Мастера диаграмм, необходимо в диалоге выполнить следующие действия:
1)выбрать тип диаграммы (точечная) и вид (№3 – со значениями, соединенными сглаживающими линиями без маркеров);
2)определить источник данных диаграммы ($B$3:$C$14) – диапазон ячеек, содержащих числовую информацию и подписи, и расположение рядов данных (у нас
– в столбцах);
3)задать параметры диаграммы (заголовки, оси, линии сетки, легенда, подписи данных);
4)указать место размещения
диаграммы (на имеющемся
листе). |
Рис. 11. График функции |
|
В результате выполнения этих действий и после удаления заливки области построения диаграммы на рабочем листе Excel к расчётам добавится график табулируемой функции (рис. 11).
3.2.7. Форматирование и защита рабочего листа
Основные действия по форматированию можно произвести с помощью кнопок панели инструментов Форматирование. Придадим нашей таблице более привлекательный вид, отформатировав объекты (формулу и диаграмму) и содержимое некоторых ячеек:
1) сделав активной ячейку A1, увеличим размер символов содержащегося в ней текста, выбрав его в списке Размер шрифта (список
20
раскрывается щелчком левой кнопки мыши на стрелке), и изменим начертание символов, щёлкнув по кнопке Курсив ;
2)уберём рамку по периметру объекта-формулы, щёлкнув правой кнопкой мыши на нём, выбрав затем во всплывающем контекстнозависимом меню пункт Формат объекта/вкладка Вид (таким же способом уберём рамку вокруг диаграммы);
3)с помощью кнопки По правому краю сдвигаем содержимое ячеек A2, C2 и E2 вправо (несмежные ячейки выделяются щелчком левой кнопки мыши при нажатой клавише Ctrl), с помощью соответствующих кнопок числа в ячейках B2, D2 и F2 выровняем влево
(), а содержимое интервала A3:C14 – по центру ();
4) командой меню Формат/Ячейки представим числа в ячейках B2, D2, F2, B4:C14 в формате с двумя (0,00) значащими цифрами в дробной части (формат и разрядность можно изменить и кнопками
);
5)используя кнопку Границы , обрамим таблицу, содержащую значения аргумента, функции и моделей оптимизации (это можно сделать и с помощью команды меню Формат/Ячейки/ вкладка Граница или с помощью контекстного меню);
6)скроем линии сетки, воспользовавшись командой меню Сервис/ Па-
раметры/ вкладка Вид и убрав флажок в окне Сетка .
Врезультате этих манипуляций, даже без злоупотребления кнопками
Цвет заливки и Цвет шрифта , вид рабочего листа изменился в лучшую сторону (рис. 12). Осталось поработать с диаграммой.
Для редактирования диаграмма выделяется одинарным щелчком, после чего вокруг неё появляются маркеры границы в виде черных квадратиков. Для окончания редактирования достаточно щёлкнуть левой кнопкой мыши в любом месте рабочего листа вне диаграммы или нажать Esc. Изменять (форматировать) можно отдельные части диаграммы, которые называются элементами. В Excel определены следующие классы элементов диаграммы:
21
1)область диаграммы;
2)область построения диаграммы;
3)основание (только для объёмных диаграмм);
4)стены (только для объёмных диаграмм);
5)легенда;
6)оси Х и Y;
7)текст;
8)стрелки;
9)название осей Х и Y;
10)линии сетки осей Х и Y;
11)первый ряд данных;
12)второй и последующие ряды данных;
13)линии проекции;
14)линии мини-макс уровней;
15)плюс-маркеры;
16)минус-маркеры;
17)линии рядов.
Каждый |
из этих |
|
||
классов можно выделить |
|
|||
на диаграмме |
щелчком |
|
||
левой |
кнопки |
мыши и |
|
|
затем |
отредактировать. |
|
||
Редактирование |
можно |
|
||
производить с помощью |
|
|||
контекстно-зависимого |
|
|||
меню |
(индивидуального |
|
||
для каждого |
элемента), |
|
||
которое |
вызывается |
|
||
щелчком правой кнопки |
Рис. 12. Фрагмент рабочего листа |
|||
мыши на выделенном элементе (двойной щелчок левой кнопки мыши вызывает сразу же окно диалога форматирования соответствующего элемента).
22
Результаты редактирования графика, на наш (может быть и не самый изысканный) вкус, представлены на рис. 12.
С помощью спроектированной нами таблицы можно исследовать поведение функции на любом интервале, меняя содержимое ячеек, в которых хранятся значения левой (B2) и правой (D2) границ отрезка.
Кроме того, с помощью примечаний к ячейкам можно добавлять различные комментарии. Чтобы добавить примечание, необходимо выбрать пункт меню Вставка/ Примечание. Примечания можно просматривать по отдельности, если подвести указатель мыши к ячейке с примечанием (рис.13). Такой режим просмотра выбирается в меню Сер-
вис/Параметры/Вид установкой переключателя Только индикатор в поло-
Рис. 13. Рабочий лист Excel
жение . Также примечания можно отображать и все одновременно (Сер-
вис/Параметры/Вид/Примечания и индикатор).
Было бы разумно защитить от несанкционированного (иногда случайного) изменения содержимое и вид всех остальных ячеек. Excel позволяет выполнить это для всей рабочей книги, листа или отдельных ячеек. Для установки (снятия) защиты необходимо выполнить два действия:
23
1)отключить блокировку с ячеек, содержимое которых подлежит изменению, с помощью команды меню Формат/ Ячейки/вкладка Защита. Затем убрать флажок в окне Заблокировать;
2)включить защиту листа или книги с помощью команды меню
Сервис/ Защита/ Защитить лист (книгу).
Выполним эти операции для ячеек B2 и D2.
Теперь мы можем исследовать поведение функции на любом отрезке (например [0,6]), не боясь чего-либо испортить (рис. 13).
Для того чтобы максимизировать (минимизировать) функцию с новыми граничными значениями, необходимо вновь активизировать диалоговое окно Поиск решения. При этом необходимо предварительно снять защиту с рабочего листа.
3.2.8. Задание для выполнения контрольной работы №2
По номеру зачётной книжки выберите из прил. 2 вариант задания для выполнения контрольной работы, воспользовавшись следующей формулой: №варианта=1+(№зач. mod 50) (A mod B – остаток от деления A на B, например, (99 mod 50)=49), где №зач. – число, составленное из двух последних цифр номера зачётной книжки. Например, пусть номер зачётной книжки 237883, тогда №зач.=83, №варианта=1+(83 mod 50)=1+33=34. Разработайте таблицу для табулирования этой функции на произвольном отрезке [a,b], разбив его на 20 частей (п. 3.2.2 – 3.2.4). На заданном отрезке найдите наибольшее и наименьшее значения этой функции (п. 3.2.5). На основе полученной таблицы постройте график функции, отформатируйте и защитите рабочий лист, выполнив действия, аналогичные описанным в п. 3.2.6÷3.2.7. Отчёт по контрольной работе должен состоять из следующих разделов:
1)постановка задачи (см. п.3.2.1);
2)анализ области определения выбранной функции (см. п.3.2.2);
3)вид формулы для вычисления значений функции в Excel (см.
п.3.2.3);