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