расчеты и графики Excel. 1. основные правила работы в excel
 Скачать 395 Kb.
|
Источник – блок, в котором первоначально размещена информация. Адресат – блок, в котором она размещена после выполнения команды. Техника выполнения этих команд та же, что и в программе WORD, а именно:
При перемещении блока на небольшое расстояние можно после п. 1 зацепить мышкой границу блока (курсор превращается в стрелку, направленную влево-вверх) и перетащить её на новое место. При копировании в ячейки, смежные с источником, можно после п. 1 воспользоваться протяжкой (см. п.п. 1.3, 1.7). Особенности выполнения операций.
Задание. Установите по команде Сервис Параметры… (вкладка Вычисления) флажок на параметре "Допускать названия диапазонов". Введите в ячейки А1:F1 тексты "h", "x", "y", "z", "k", "v". Ячейке А2 присвойте имя h и введите в неё число 10. В В2:В10 введите какие-нибудь числа, в С2 – формулу =В2*h, в D2 – формулу =x*h. Протяните С2:D2 вниз до строки 10. Выделите D2:D10 и протяните этот блок на столбцы E, F. Обратите внимание на то, как выглядят формулы в этих ячейках и какие результаты получены по ним. Теперь в ячейке C2 закрепите адрес В2 ($В$2) и протяните её вниз, вбок. Посмотрите, как изменится вид формулы и результат. Закрепите часть адреса В2 и повторите процедуру. 1.13. Команды форматирования Термин форматирование обозначает изменение вида информации без изменения её смысла. Форматирование применяют для того, чтобы информация выглядела удобно для глаза и дальнейшего анализа. Все команды форматирования применяются к предварительно выделенной ячейке, блоку, столбцам или строкам и собраны в диалоговом окне Формат ячеек, которое вызывается по команде ФорматЯчейки… или через контекстное меню (вызывается по (П')). Основные команды можно также заказать в панели инструментов Форматирование (если её нет на экране, она вызывается командой ВидПанели инструментов…). К основным командам форматирования относятся:
Задание. Введите в ячейку А1 заголовок: "Таблица функции", в ячейки А2, В2, С2 соответственно тексты: "х", "у", "максимальное значение". В блок А3:А15 занесите протяжкой значения аргументов, например, 2; 2,5; 3; …; 8. В блоке В3:В15 также с помощью протяжки разместите формулу какой-нибудь функции от этих аргументов, например, y=2cos(x+2)e0,5x. Выполните следующее форматирование полученной таблицы:
Графики и диаграммы делают более наглядной и понятной числовую информацию, которая введена в таблицу или получена в результате расчётов. Шаблон нужного графика или диаграммы создаётся с помощью программы Мастер диаграмм, которая заказывается командой ВставкаДиаграмма… или кнопкой "Мастер диаграмм" в стандартной панели инструментов (разноцветные вертикальные столбики). Работа Мастера разбивается на четыре шага. После окончания его работы подготовленный им шаблон корректируется пользователем для того, чтобы получить максимальную наглядность в иллюстрации числового материала. Задание. Построить диаграмму, иллюстрирующую таблицу функции, полученную в п. 1.13.
Выбор типа диаграммы зависит от характера данных, которые будут отображаться. Пояснения к каждому типу расположены справа в нижней части окна. Над ними предлагаются варианты представления диаграмм данного типа. Нужный выбирается с помощью (Л’) по нему. Вертикальная ось называется осью Y или осью значений, вдоль неё всегда откладываются числовые показатели. Горизонтальная (ось абсцисс) – называется осью Х, если на ней откладываются числовые аргументы, и осью категорий, если аргументами являются тексты (названия фирм, материалов, фамилии людей и т.п.). Основные замечания:
Выберем для нашего задания Точечную диаграмму с гладкими кривыми без маркеров. 2.2. Второй шаг Мастера диаграмм: источник данных диаграммы Окно второго шага содержит две вкладки. На вкладке "Диапазон данных" в поле "Диапазон" вводятся координаты блока, который содержит значения аргументов, функций и подписи к ним. Эти координаты можно ввести прямой набивкой или протащив мышку по нужным ячейкам. Несмежные области добавляются при протаскивании при нажатой клавише Основные элементы, на которые надо обращать внимание:
На вкладке "Ряд" представлено окно с перечнем обозначений всех функций, изображённых на диаграмме, и поля, в которых указано, из каких ячеек мастер берёт информацию для выделенной функции. В поле "Имя" можно ввести либо адрес ячейки, в которую оно занесено, либо непосредственно текст, которым удобно обозначить эту функцию. Если какая-то функция оказалась лишней, её выделяют и нажимают кнопку <Удалить>. По кнопке <Добавить> Мастер выдаёт бланк с чистыми полями, в которые следует внести координаты блоков, в которых размещены, аргументы, значения и имя новой функции. Для нашего задания на вкладке "Диапазон данных" внесём координаты блока данных А2:В15 и поставим переключатель "Ряды в столбцах". Посмотрите, как эта же информация оказалась представленной на вкладке "Ряд".
На этом шаге заказывается набор элементов, необходимых для оформления диаграммы: нужно или нет вносить в диаграмму легенду, оси, масштабную сетку, заголовки диаграммы в целом и отдельных осей и т. п. Для каждого элемента оформления выделена отдельная вкладка, на которой детализируется его вид. Просмотрите возможности всех элементов на образце, заказывая разные параметры. Введите заголовки: для диаграммы – Учебный график, для оси Х – Аргументы, для оси Y – функция. Закажите основные линии сетки по осям Х и Y. Откажитесь от легенды.
На этом шаге всего два параметра:
Закажем размещение на том же листе.
Шаблон, построенный мастером, часто приходится корректировать. Переход в режим правки можно сделать разными способами: (Л’) или (Л”) по любому элементу диаграммы приводит к тому, что элемент, на который указывал курсор, выделяется маркерами, а меню Данные превращается в меню Диаграмма. Четыре первых команды этого меню повторяют шаги Мастера диаграмм и позволяют вызвать окно того шага, на котором нужно что-то исправить. Остальные команды предлагают возможности, которые нельзя было заказать ранее. Наиболее важная из них – Добавить линию тренда… (см. п. 2.6). В меню Формат первой строкой появляется команда вызова окна форматирования выделенного элемента, которая позволяет изменить его оформление. Если переход в режим правки сделан по (Л"), то это окно открывается сразу. Перейти в режим правки можно также, сделав (П') по любому элементу диаграммы. При этом открывает контекстное меню, в котором находятся основные команды корректировки для этого элемента. Ряд элементов после выделения обводится рамкой с квадратными маркерами (область диаграммы, область построения диаграммы, легенда, заголовки диаграммы или осей). Зацепившись за неё, можно перетащить мышкой выделенный элемент на новое место, а зацепившись за маркер – изменить его размеры. Выполните следующую корректировку шаблона, сделанного Мастером:
Линия тренда – это сглаживающая кривая, проведённая по данным, представленным на графике. Для уравнения тренда обычно используют не очень сложные зависимости, параметры которых подбираются по методу наименьших квадратов. Если аргументы сглаживаемой функции имеют текстовый тип, в качестве аргументов тренда используют номер значения функции. Команду вставки этой линии в график можно вызвать из меню Диаграмма или из контекстного меню для нужного ряда данных. Окно Линия тренда, которое открывается по этой команде, содержит две вкладки. На вкладке Тип приведены графики стандартных линий тренда:
На вкладке Тип следует сделать (Л’) по тому графику, вид которого кажется наиболее подходящим для сглаживаемой функции. Если на диаграмме представлено несколько функций, то следует дополнительно выделить название нужной функции в поле "Построен на ряде", расположенном под графиками. Полиномиальный тренд удобно использовать для функций, имеющих экстремумы. Количество экстремумов, которое может воспроизвести многочлен, на 1 меньше, чем его степень (типовой график, представленный на вкладке Тип, соответствует многочлену третьей степени). На вкладке Параметры можно задать:
Для того чтобы изменить вид построенного тренда, можно воспользоваться любым из методов корректировки диаграмм. Для одной и той же функции можно построить несколько трендов разных видов, чтобы "на глаз" выбрать лучшую линию для сглаживания. Задание. Внесите на диаграмму полиномиальный тренд третьей степени и его уравнение. Представьте его цветным пунктиром. Переместите уравнение тренда из Области построения диаграммы на её поля.
Большинство основных вычислительных алгоритмов в Excel оформлены в виде стандартных функций и вызываются с помощью программы Мастер функций (см. п. 1.8). Самые популярные из них:
Стандартную библиотеку можно дополнять функциями, созданными пользователем самостоятельно (см. [3, 4]). Более сложные алгоритмы оформлены в виде команд и заказываются через меню Сервис. Наиболее важные из них:
Если нужная для вычислений команда отсутствует в меню, её можно установить с помощью команды СервисНадстройки… 3.1. Расчёт таблицы значений функции от одного аргумента При явном задании функции таблица состоит из двух главных столбцов (строк). Первый – аргументы, второй – значения функции. Если алгоритм расчёта функции сложный, может потребоваться несколько дополнительных столбцов (строк), для записи промежуточных результатов. Если аргументы меняются с постоянным шагом, то их можно ввести с помощью протяжки (1.5) или формулы. Последний способ более удобен, если может потребоваться пересчёт таблицы в разных диапазонах аргументов. Рассмотрим его на примере. Пример. Найти графически координаты корней и максимумов функции Y=2cos(x+2)e-0,5x Составим план размещения информации:
Формулы, занесённые в ячейки А5:А25, будут нагляднее, если перед их набором присвоить константам в А3:В3 имена (см. п. 1.9) и вводить их с помощью (Л’), а в формуле функции использовать название столбца аргументов: =2*COS(Х+2)*EXP(-0,5*Х) (см. п. 1.10). Задание. Отформатируйте полученную таблицу и постройте диаграмму типа Точечная по ней. Таблица и график показывают, что при х>9 функция практически равна нулю, первый корень лежит в диапазоне 2<x<3, первый экстремум – около х=4. Введём в А3:В3 новые константы: 2 и 0,1 соответственно. Excel сразу же пересчитал таблицу и график на новый диапазон аргументов и теперь можно локализовать корень и экстремум уже с точностью 0,1. Диапазон для корня 2,7<x<2,8, координата экстремума – x=3,5. При необходимости можно снова изменить константы, с помощью которых создаётся диапазон аргументов, и продолжить уточнение ответов. Задание. Найдите какой-нибудь корень и экстремум этой функции в отрицательной области аргументов. 3.2. Расчёт таблицы значений функции от двух аргументов Функция от двух аргументов обычно представляется в виде таблицы, у которой заголовками строк и столбцов являются величины аргументов, а значение функции, рассчитанное для любой пары из них, помещается на пересечение соответствующих строки и столбца. На плоской диаграмме такая функция представляется серией кривых, отражающих зависимость только от одного аргумента при фиксированном втором, причём значение второго аргумента на разных кривых различно. Рассмотрим пример. Составить таблицу и график функции z=y2x-ln(x+y) для диапазонов 2<x<6 и 1<y<5. Будем менять х с шагом 0,4, у – с шагом 0,8. Составим план размещения информации:
При вводе формулы в ячейку С4 символы "$" расставлены с таким расчётом, что при протяжке вбок разрешено менять адрес только у аргумента Y, аргумент Х для всей полученной строки берётся из одной и той же ячейки (в адресе Х закреплён столбец). При протяжке вниз – наоборот: в адресе аргумента Y закреплена строка, поэтому он не меняется, и в столбце оказываются значения функции, сосчитанные для одного и того же Y, но разных Х. Символ "$" можно вводить непосредственной набивкой в английском регистре или повторными нажатиями клавиши Задание. Отформатируйте полученную таблицу (см. п. 1.13):
Перейдём к построению диаграммы. Представим на ней зависимость z(x) при разных y. На первом шаге закажем тип Точечная с гладкими кривыми без маркеров. На втором шаге выделим диапазон данных В4:Н14 и параметр "Ряды в столбцах". На вкладке Ряд внесём для рядов данных имена: у=1, у=1,8 и т. д. На третьем шаге закажем легенду, основную сетку по осям и введём какие-нибудь заголовки. На четвёртом – размещение на том же листе. Задание. Откорректируйте диаграмму:
Постройте вторую диаграмму, на которой будет представлена зависимость z(у) при разных х. Примечание. Если перед вводом формулы в С4 присвоить диапазонам С3:Н3 и В4:В14 имена соответственно у и х, то формулы в С4 можно ввести в более понятном виде: =у^2*x-LN(x+y) (см. п. п. 1,9, 1.12). 3.3. Использование функции ЕСЛИ для анализа информации Функция ЕСЛИ позволяет предусмотреть разные способы заполнения одной и той же ячейки. То, каким из них следует воспользоваться в данный момент, Excel определяет самостоятельно по тому, выполняется или нет при введённых данных указанное в функции условие. Рассмотрим действие этой функции на конкретных примерах. Пример 1. Поставщик ввёл оптовую скидку на цену для больших партий товара. Надо составить шаблон для расчёта стоимости любой партии товара. Составим таблицу из констант, необходимых для расчёта. В ячейки А1:А4 ведём названия констант: "ОбъёмПартии", "ОптБарьер", "РознЦена", "ОптЦена". Присвоим ячейкам В1:В4 такие же имена (удобно пользоваться командой ВставкаИмяСоздать, см. п. 1.9). В ячейку С1 введём текст "СтоимПартииТовара". Выделим ячейку С2 и вызовем через Мастер функций функцию ЕСЛИ. В окне аргументов введём для них следующие значения. В аргумент "Логическое_выражение:" вводится условие, по которому Excel выбирает нужный вариант действий. Оно выглядит так (правила ввода аргументов изложены в п. 1.7): ОбъёмПартии<=ОптБарьер В строке второго аргумента ("Значение_если_истина:") указывается способ, по которому следует заполнить ячейку при правильном условии. Им может быть готовая константа, ссылка на ячейку, в которой содержится нужная информация или расчётная формула. Для нашего примера этот аргумент выглядит так: ОбъёмПартии* РознЦена И, наконец, в третьем аргументе ("Значение_если_ложь:") указывается, как заполнять ячейку, если условие не выполняется. Для нашего примера следует ввести ОбъёмПартии* ОптЦена Чтобы не набирать заново длинные названия переменных, можно ввести их с помощью (Л’) по соответствующим ячейкам в столбце "В". Расчётный шаблон готов. Чтобы проверить его, введите простые числа в ячейки В1:В4 и проверьте, правильно ли функция ЕСЛИ выбрала формулу для заполнения ячейки С2. Введите в В1 другой объём партии, при котором требуется использовать вторую формулу. Если в обоих случаях получены верные результаты, можно красиво отформатировать ячейки А1:С4 (см. п. 1.13) и пользоваться этим шаблоном, меняя только значения констант в В1:В4. В качестве вариантов, которые выбирает функция ЕСЛИ, могут быть не только расчётные формулы, но и ссылки на ячейки, где находится нужная информация, текстовые строки и т. п. Пример 2. Заполним последний столбец в Задании п. 1.13. Выделим ячейку С3 и вызовем через Мастер функций функцию ЕСЛИ. Для аргументов введём следующие значения. Условие, по которому Excel выбирает нужный вариант действий, выглядит так: В3=МАКС($B$3:$B$15). В строку второго аргумента вводим символ "*" (без кавычек), в третий – пробел и нажмём после этого <ОК>. Протянем полученную формулу по блоку С3:С15. Посмотрите, какой вид приняла функция в разных ячейках этого блока. Рассмотрим ЕСЛИ в той строке, в которой появилась "*". Значение функции yв этой строке максимально. Левая и правая части условия оказались одинаковыми, то есть первый аргумент – правильный. Поэтому для заполнения своей ячейки ЕСЛИ выбрала то, что указано во втором аргументе. Для значений функции у в других строках условие, введённое в функцию ЕСЛИ, оказывается неверным, поэтому она заполняет свои ячейки по варианту третьего аргумента. В нашем случае это пробел, который невидим на экране, поэтому ячейки кажутся пустыми. Измените аргументы, введённые в А3:А15. "*" переместилась в другую строку, хотя формулы в С3:С15 не были изменены. После изменения данных каждая функция ЕСЛИ автоматически проверила свой первый аргумент наново и приняла новое управляющее решение, каким правилом пользоваться для заполнения своей ячейки. Задание. Введите в блок D3:D15 функцию ЕСЛИ, которая поставит знак "-" в строках с отрицательными значениями "у" и знак "+" – для положительных "у". Другие варианты использования функции ЕСЛИ.
Пример 3. Объём заказа k, который принимает фирма, должен лежать в диапазоне от a1 до a2 штук изделий. Цена одного изделия составляет d рублей. Составить шаблон для расчёта стоимости заказа, который будет выдавать предупреждение, если объём не попал в допустимые границы. Введём в ячейку А1 общее название шаблона: Расчёт стоимости заказа, в А3:А6 названия переменных, а в В3:В6 их значения. Названия переменных, использованные при формулировке примера, неудобны для Excel – расчёта: а1, а2 совпадают со стандартными адресами других ячеек, поэтому можно воспользоваться длинными текстовыми именами, например, в А3 – ОбъёмЗаказа, в А4 – НеМеньше, в А5 – НеБольше, в А6 – Цена. В А2 введём текст СтоимостьЗаказа, в В2 – функцию ЕСЛИ со следующими аргументами: Логическое_выражение: И(B3>=B4;B3<=B5) Значение_если_истина: B3*B6 Значение_если_ложь: ТАКОЙ ЗАКАЗ НЕ ПРИНИМАЕМ Если ячейкам В3:В6 предварительно присвоить имена, указанные в А3:А6 (см. п. 1.9), смысл аргументов станет более понятным. Посмотрите, как действует шаблон, при разных значениях, введённых в В3. Отформатируйте его (см. п. 1.13):
Измените условие в первом аргументе: ИЛИ(В3<=B4;B3>=B5) В какой последовательности надо теперь перечислять способы заполнения ячейки В2? Итог. Если выбор одного из двух вариантов заполнения ячейки зависит от нескольких условий, все они перечисляются через ";", заключаются в общие скобки и перед ними указывается нужный тип объединения:
Пример 4. Составить шаблон для расчёта подоходного налога по прогрессивной схеме. Для простоты не будем учитывать налоговых льгот и будем считать, что существует только две границы, на которых меняется процентная ставка налога. Тогда расчётная формула выглядит следующим образом:  налог= Здесь d – доход, n1, n2 – границы налоговых ставок, р1, р2, р3 – процентные ставки налогов (очередной процент действует только на ту часть дохода, которая попала в его диапазон). Введём в ячейку А1 общий заголовок шаблона: Расчёт налога. В А2:А7 введём соответственно названия Доход, Граница1, Граница2, Проц1, Проц2, Проц3. В В2:В7 введём значения этих величин, В С2 – текст: Налог, в D2 – функцию ЕСЛИ, первый и второй аргументы которой такие: Логическое_выражение: B2<=B3 Значение_если_истина: B5*B2 В третьем аргументе надо объяснить, как заполнять ячейку, если доход больше первой границы. Так как для этого случая существует ещё две возможности: d  n2 и d > n2, в этот аргумент вставляют дополнительную функцию ЕСЛИ, которая должна выбрать нужный вариант. Правила вставки дополнительной функции в аргумент основной изложены в п. 1.8. Окончательно третий аргумент основной функции (Значение_если_ложь) выглядит так:ЕСЛИ(B2<=B4;B5*B3+B6*(B2-B3);B5*B3+B6*(B4-B3)+B7*(B2-B4)) Итог. Если для заполнения ячейки имеется от трёх до семи вариантов, все они вводятся через вложенные функции ЕСЛИ, вставленные в аргумент Значение_если_ложь предыдущей функции. Если вариантов больше семи, все они разбиваются на мелкие группы и проверяются функциями ЕСЛИ в разных ячейках. 3.4. Оценка определённого интеграла Интеграл – это площадь под графиком подынтегральной функции. Один из самых простых способов оценки этой площади – метод трапеций. По этому методу промежуток интегрирования [a;b] разбивают на несколько (n) равных частей длиной h=(b-a)/n. Подынтегральную функцию f(x) заменяют на хорды, проведённые через её значения на концах каждого из полученных отрезков. После этого реальную площадь считают приближённо равной сумме площадей прямоугольных трапеций, основания которых – ординаты функции в точках дробления промежутка интегрирования (xi, i=0, 1, 2, …, n) , а боковыми сторонами являются хорды, заменяющие функцию, и соответствующие отрезки на оси абсцисс. Математически площадь каждой трапеции выражается формулой Si= h(f(xi-1)+f(xi))/2 С учётом вышесказанного в Excel оценку интеграла можно выполнить следующим образом:
Если помимо итогового интеграла интерес представляет и то, как он изменяется с увеличением промежутка интегрирования, в таблицу можно вставить ещё один столбец (строку), в которой будут расположены суммы площадей трапеций от начала до текущего аргумента. Пример. Составить таблицу значений интеграла  как функции верхнего предела в диапазоне 0<v<5. Отразить эти данные на графике. Подобрать функцию тренда.Составим план размещения информации:
Задание. Постройте диаграмму типа Точечная для зависимости интеграла от верхнего предела. Подберите тренд для него. Выведите уравнение тренда на диаграмму. Отформатируйте таблицу и диаграмму. 3.5. Нахождение корня уравнения Помимо способа, изложенного в п. 3.1, для решения этой задачи можно воспользоваться командой СервисПодбор параметра… Перед обращением к этой команде следует ввести в таблицу алгоритм расчёта функции (он может быть представлен одной или несколькими формулами) и ввести в ячейку её аргумента ориентировочное значение, с которого следует начать поиск корня. Команда СервисПодбор параметра… вызывает на экран окно Подбор параметра, в котором следует указать:
В процессе выполнения команды начальное значение аргумента заменится на найденное, при котором функция будет равна нужному значению (не обязательно нулю). Точность подбора аргумента и максимально допустимое количество итераций при решении задачи задаются в диалоговом окне команды СервисПараметры… на вкладке Вычисления. Задание. Найдите двумя способами с точностью 0,001 корень уравнения e-0,5x-2x+4=3. 3.6. Решение систем уравнений Для решения систем нелинейных уравнений можно использовать команду СервисПоиск решения…, преобразовав задачу в оптимизационную (см. п. 3.7). Систему линейных уравнений можно решить, запрограммировав вручную метод Гаусса, но проще сделать это матричным методом, опираясь на функции работы с массивами. В матричном виде линейная система любого порядка и её решение записываются следующим образом: АХ=В; Х=А-1В Здесь А – матрица коэффициентов при неизвестных, В – столбец свободных членов системы, Х – неизвестные решения, А-1 – обратная матрица коэффициентов системы. В библиотеке Мастера функций Excel в категории Математические есть функции МУМНОЖ и МОБР, которые выполняют соответственно умножение и обращение матриц, необходимое для решения данной задачи. Так как результатом работы этих функций являются массивы чисел, их следует вводить как функции массива (см. п. п. 1.7, 1.8). Рассмотрим систему четырёх линейных уравнений с четырьмя неизвестными. Введём исходные данные задачи по представленному ниже плану.
Для удобства работы перед вводом коэффициентов системы и расчётных формул можно провести форматирование таблицы (см. п. 1.13):
Перед вводом формулы массива следует выделить ячейки, в которых надо разместить результаты. При решении системы это блок Н4:Н7, при проверке правильности найденного решения – I4:I7. Затем формула набирается обычным способом с помощью Мастера функций, но ввод заканчивается нажатием клавиши 3.7. Решение задач оптимизации Команда СервисПоиск решения… предоставляет пользователю следующие возможности:
Эти возможности реализуются с помощью параметров, собранных в основном окне Поиск решения и дополнительном Параметры поиска решения. Дополнительное окно вызывается кнопкой <Параметры> из основного. Кнопка <Справка> вызывает окно с разъяснением смысла каждого параметра и возможностей, которые предоставляются при его заказе. Методы оптимизации можно так же применять для решения систем нелинейных уравнений. Для этого из уравнений системы f1(x1,x2…,xn)=0; f2(x1,x2…,xn)=0; …; fn(x1,x2…,xn)=0; составляют вспомогательную целевую функцию S=f12+ f22+…+ fn2 S – неотрицательная функция, её минимальное значение равно нулю и достигается только тогда, когда все слагаемые одновременно равны нулю. А это и есть решения исходной задачи. Рассмотрим в качестве примера систему двух нелинейных уравнений x2+y2=3; 2x+3y=1 Введём исходные данные задачи по представленному ниже плану. Для удобства дальнейшей работы можно провести форматирование созданной таблицы, аналогичное п. 3.5. Вызовем команду СервисПоиск решения… В окне Поиск решения установим следующие параметры:
Когда команда закончит работу, на экране автоматически появляется окно Результаты поиска решения. Пояснения к параметрам, представленным в нём, вызываются кнопкой <Справка>. Закажем, к примеру, параметры "Сохранить найденное решение" и "Тип отчёта: результаты". В этом случае начальные значения переменных в ячейках А4:В4 заменятся на найденные и в таблицу будет вставлен новый лист "Отчёт по результатам 1". Просмотрите отчёт. Проверьте, какое значение приняла вспомогательная целевая функция в А9 при найденных решениях. Если она существенно отличается от нуля, то решение найдено неверно.
Успешность поиска решения во многом зависит от выбора начального приближения переменных. В случае двух уравнений с двумя переменными можно не делать аналитического исследования функций системы, а составить таблицу вспомогательной целевой функции (см. п. 3.2) и выбрать по ней те комбинации аргументов, при которых функция принимает наименьшие значения. Задание. Составьте таблицу значений целевой функции S=(x2+y2-3)2+(2x+3y-1)2в диапазоне аргументов -3<x<3, -3<y<3. Выберите 4 – 5 точек с наименьшими значениями функции, проведите поиск решения из каждой из них. В результате должно быть получено только два разных решения: х1=-1,268; у1=1,179 и х2=1, 576; у2=-0,717. Графически уравнения системы представляются окружностью и прямой линией. Система такого типа не может иметь больше двух точек пересечения. 4. ИСПОЛЬЗОВАНИЕ VISUAL BASIC В EXCEL 4.1. Работа с макросами Познакомимся с таким средством работы в Excel, как "макросы". Выберем раздел меню СервисМакросНачало записи... Появится окно Запись макроса. Вы можете ввести или изменить информацию в полях этого окна, в частности, назначить свое имя или "горячую клавишу" для запуска макроса. Оставьте содержание полей неизменным или, если хотите, измените их и нажмите кнопку <ОК>. Обратите внимание на появление небольшой панели с кнопкой <Остановить запись> на листе книги Excel. Выполните любую последовательность действий на листе, например, закажите для выделенных ячеек более крупный и цветной шрифт и введите туда формулу. После этого нажмите кнопку <Остановить запись> (надо именно нажимать кнопку, а не закрывать панель). Сотрите все результаты выполненных вами действий при записи макроса. Далее выберем раздел меню СервисМакросМакросы…. В появившемся окне выделим имя того макроса, который Вы только что записали, и нажмем кнопку <Выполнить>. Убедитесь что последовательность действий, проделанных Вами при записи макроса, оказалась выполненной заново. Вы можете опять стереть результаты этих действий или выделить другие ячейки и опять заказать выполнение макроса. Все действия, сделанные при записи макроса, повторятся автоматически. При этом, если в макрос входят формулы, которые содержат ссылки на другие ячейки , и он выполняется не на прежнем месте, то ссылки автоматически будут преобразованы в соответствии с правилами копирования. Таким образом, макрос – это средство для запоминания и последующего (многократного и автоматического) выполнения проделанной Вами один раз последовательности действий. Примечания.
Задание. Запишите макрос, выполняющий следующую последовательность действий:
После этого остановите запись макроса, перейдите на другой лист и запустите полученный макрос на выполнение. Теперь, выбрав в меню СервисМакросМакросы… имя макроса, записанного в предыдущем задании, нажмите кнопку <Изменить>. Перед Вами откроется окно редактора Visual Basic. В тексте редактора найдите ту часть текста, которая относится к записанному Вами макросу, и ознакомьтесь с его структурой и содержанием. Какие выводы Вы сделали? Если Вы знаете, например, язык Quick Basic, Вы встретите много знакомого для Вас. Незнакомого также может быть много, но это, в основном, – дополнения, к Quick Basic, хотя и очень существенные. Текст макроса начинается строкой Sub <имя макроса>() … и заканчивается словами End Sub Макрос записан как подпрограмма без параметров на языке Quick Basic. Строки, начинающиеся с апострофа "’", являются комментариями и не выполняются. Вы можете изменить в редакторе текст этой подпрограммы, то есть записанного Вами макроса. Сделайте это, например, заменив Sin на Cos в тексте, начальные значения аргументов и округлив результаты до одной цифры после запятой. Вернуться на лист Excel можно разными способами:
Вернитесь на лист Excel и выполните макрос еще раз на чистом листе. Сравните полученные таблицы. 4.2. Создание простых функций пользователя Библиотеку функций Excel можно дополнить функциями пользователя. Это делается средствами Visual Basic. Проверьте, есть ли в Мастере функций раздел "Определенные пользователем". Он может уже существовать, если до Вас кто-то уже создавал собственные функции. В противном случае, он отсутствует и появится после того, как Вы создадите свою. Теперь откройте окно редактора Visual Basic. Вы можете сделать это или, как выше, через СервисМакросМакросы…<Изменить>, или непосредственно через СервисМакросРедактор Visual Basic. После последнего из записанных макросов (слов End Sub соответствующей подпрограммы) наберите, например, следующий текст:
Последняя строчка появится сама, без Вашего участия, поэтому позаботьтесь, чтобы она не оказалась набранной дважды. Закройте окно Редактора Visual Basic и вернитесь в окно Excel. Теперь вы можете убедиться, что в Мастере функций существует раздел "Определенные пользователем", а в нем функция "МояПервая", созданная Вами. Выбрав её и нажав <ОК>, вы получите перед собой окно Вашей функции с полем аргумента «x». Введите в это поле какое-либо значение (например, 4) и Вы получите результат (16,5) в соответствии с определенной Вами функцией. Нажмите <ОК>, и результат окажется в той ячейке листа Excel, которая была выделена перед обращением к мастеру функций. Задание. Определите, как описано выше, еще несколько Ваших собственных функций, с разными именами, с разным числом аргументов и заставьте их работать на Вас. Вы вполне можете ограничиться Вашими знаниями языка Quick Basic, несмотря на то, что программируете функции в Visual Basic. Ниже мы рассмотрим несколько конкретных примеров функций, полезных для расчетов в Excel. 4.3. Вычисление определенного интеграла В Excel 2000 среди стандартных математических функций отсутствуют функции, вычисляющие определенный интеграл. Создадим такую функцию сами. Пусть нам необходимо вычислить интеграл  .Для приближенного вычисления определенного интеграла используем, как и в п. 3.4, метод трапеций. В методе трапеций приближенное значение интеграла вычисляется по итоговой формуле:  ,где h – ширина интервала значений аргумента x, после разбиения промежутка интегрирования на n равных частей, yi=f(xi) – значения функции на границах этих интервалов. Соответствующую подпрограмму-функцию создадим так же, как в предыдущем параграфе.
|