Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
УДК 681.3
Лабораторный практикум по информатике. Часть 1. – Ростов н/Д : Рост. гос. строит. ун-т, 2011. – 38 с.
В методических указаниях содержится материал, необходимый для проведения лабораторных занятий по информатике.
В начале каждого практикума приведён образец решения задач. В конце предлагаются задания для самостоятельной работы студентов.
Предназначены для студентов инженерно-технических специальностей.
|
Составители: |
Волосатова Т.А., Данекянц А.Г., Маринченко Е.В., Солохин Н.Н., Сайфутдинова Н.А. |
|
Рецензент: |
Корректор
Темплан 2011 г., поз.
Формат 60х84/16. Бумага писчая. Ризограф. Уч.-изд. л.
Тираж 150 экз. Заказ
__________________________________________________________________________________________
Редакционно-издательский центр
Ростовского государственного строительного университета
344022, Ростов-на-Дону, ул. Социалистическая, 162
© Ростовский государственный
строительный университет, 2011
Содержание
|
[1] Лабораторный практикум № 1 [1.1] Работа с ячейками и диапазонами ячеек [1.2] Математические формулы в Excel [1.3] Математические функции MS Excel [1.3.0.1] Ввод функций [1.4] Задания для самостоятельной работы [2] Лабораторный практикум № 2 [2.1] Ссылки в Excel [2.1.0.1] Ссылки в пределах рабочего листа [2.1.0.2] Трассировка ссылок и зависимостей [3] Лабораторный практикум № 3 [3.1] Построение графиков функций, заданных различными способами [3.2] Зависимости между полярными и прямоугольными координатами точки [3.3] Задания для самостоятельной работы
[4] [4.1] Кривые второго порядка на плоскости [4.1.1] Парабола [4.1.2] Гипербола [4.1.3] Эллипс [4.1.4] Окружность [4.2] Задания для самостоятельной работы |
Электронные таблицы Microsoft Excel представляют собой удобный инструмент для выполнения расчетов в табличной форме. Решения многих вычислительных задач, которые раньше можно было осуществить только с помощью программирования, стало возможно реализовать через математическое моделирование в электронной таблице.
Документ Microsoft Excel называется книгой. Книга состоит из листов (максимально 341 лист). Листы бывают четырех типов: стандартный лист, лист диаграмм, лист макросов и лист диалогов. При создании книга имеет 3 стандартных листа с именами: ''Лист 1'', ''Лист 2'', '’Лист 3''. Каждый стандартный лист состоит из ячеек.
Ячейка - это единичный адресуемый элемент рабочего листа, который может содержать числовое значение, текст или формулу. Каждая ячейка имеет адрес, содержимое и значение.
Адрес ячейки может быть в двух форматах. В основном формате адрес ячейки состоит из имени столбца (обозначаются буквами от А до IV, 256 столбцов максимально) и номера строки (от 1 до 65536). В формате R1C1 адрес ячейки состоит из номера строки (row) и номера столбца (column).
Например, D12 - это ячейка, которая находится в четвёртом столбце двенадцатой строки. В формате R1C1 ее адрес будет иметь вид: R12C4.
При работе с ячейками электронной таблицы обычно используют адреса в основном формате.
Диапазоном называется группа ячеек. Чтобы задать адрес диапазона, нужно указать адреса его левой верхней и правой нижней ячеек, разделив их двоеточием.
Приведём примеры адресов диапазонов.
|
А1:В1 |
Две ячейки, расположенные в одной строке и в двух соседних столбцах |
|
С24 |
Этот диапазон состоит из одной ячейки |
|
А1: А100 |
100 ячеек столбца А |
|
A1:D4 |
16 ячеек, расположенных в четырёх строках и четырёх столбцах |
|
С1:С65536 |
Все ячейки одного (третьего) столбца (этот диапазон можно также указать как С:С) |
|
A6:IV6 |
Все ячейки одной (шестой) строки (этот диапазон можно также указать как 6:6) |
|
A1:IV65536 |
Все ячейки рабочего листа |
Диапазону ячеек можно присвоить уникальное имя. Об этом будет рассказано чуть позже.
Содержимое ячейки - это то, что введено в ячейку пользователем или программой.
Значение ячейки – это то, что отображается в ячейке, после завершения ввода содержимого.
Например, пользователь ввел в ячейку формулу. Формула и будет содержимым ячейки, а результат вычислений по этой формуле, отображаемый в ячейке , будет значением.
Заполните правую колонку самостоятельно
|
В10:В20 |
|
|
7:7 |
|
|
5:10 |
|
|
D:D |
|
|
H:J |
Рассмотрим некоторые удобные способы ввода данных:
Для того, чтобы заполнить ячейки членами арифметической прогрессии надо: ввести в соседние ячейки первые два члена прогрессии, выделить обе ячейки и протянуть выделенный диапазон с помощью маркера автозаполнения до нужного элемента.
Для осуществления Автозаполнения можно использовать также команды: Правка Заполнить Прогрессия. Для этого необходимо, например, в ячейке А1 набрать первый член прогрессии, например, 0,5. Далее нажать Enter и выделить ячейку А1. В строке меню выполнить команды Правка Заполнить Прогрессия. После появления окна «Прогрессия» (рис. 2а) выбирается расположение элементов прогрессии: 1) «по строкам», если необходимо элементы расположить в какой – либо строке (в нашем примере в строке 1); 2) «по столбцам», если необходимо элементы прогрессии расположить в столбце (в нашем примере в столбце А). Далее выбирается тип прогрессии (арифметическая, геометрическая, даты, автозаполнение), шаг и предельное значение. После нажатия кнопки Ок в этом окне получим результат, изображённый на рисунке 2б.
Выделение диапазонов
Чтобы выполнить над диапазоном ячеек рабочего листа какую-либо операцию, нужно сначала выделить этот диапазон. При выборе диапазона цвет ячеек изменяется. Исключение составляет только активная ячейка, которая сохраняет свой обычный цвет.
Диапазон можно выбрать несколькими способами.
■ Щёлкните и перетащите указатель мыши по диапазону ячеек. Если перетаскивать ука затель за пределы экрана, то рабочая таблица будет автоматически прокручиваться.
■ Нажмите и удерживайте клавишу Shift, а затем выделите диапазон с помощью кла виш управления курсором.
■ Нажмите клавишу F8, а затем переместите табличный курсор с помощью клавиш управления курсором, выделяя диапазон. Чтобы вернуть клавиши управления курсо ром в нормальный режим, снова нажмите F8.
■ Введите адрес ячейки или диапазона ячеек в поле Имя, затем нажмите Enter. Excel выделит указанную ячейку или диапазон.
Выбор несмежных диапазонов
В большинстве случаев выбираемые вами диапазоны будут смежными, или непрерывными, т.е. они будут представлять собой сплошные прямоугольники ячеек. Однако Excel позволяет также работать с несмежными диапазонами. Такой диапазон состоит из двух или более диапазонов (либо одиночных ячеек), которые не обязательно находятся рядом друг с другом. Например, если требуется одинаково отформатировать ячейки в различных областях рабочей таблицы, то один из способов - выбрать несмежный диапазон. Когда вы выберете все нужные ячейки и диапазоны, указанные атрибуты форматирования будут применены ко всем выделенным ячейкам.
Выбрать несмежный диапазон можно несколькими способами.
■ Для выделения отдельных ячеек или диапазонов нажмите клавишу Ctrl и, удерживая её, щёлкните на нужных ячейках.
■ Выделите диапазон с помощью клавиатуры, как было описано выше (используя кла виши F8 или Shift). Затем нажмите клавиши Shift+F8, чтобы выбрать ещё один диапазон, не отменяя при этом выбора предыдущего.
■ Воспользуйтесь командой Правка Перейти и вручную введите адрес диапазона в диалоговое окно Переход. Различные диапазоны отделите друг от друга точкой с за пятой. После щелчка на кнопке Ok Excel выделит ячейки указанных диапазонов.
Обработка данных, хранимых в ячейках рабочих листов Excel, осу ществляется по формулам, определённым пользователем. Для перехода в режим создания формул необходимо активировать ячейку, в которую будет записана формула, и ввести знак «=».
В формулах при вычислениях могут использоваться, как различные операторы (см. табл. 1), так и встроенные функции Excel (см. ниже).
Таблица 1. Операторы MS Excel
|
Арифметические операторы: |
Операторы сравнения: |
Операторы ссылок: |
|
+ сложение |
= равно |
: двоеточие (оператор диапазона) |
|
- вычитание |
> больше |
|
|
^ возведение в степень |
< меньше |
; точка с запятой (оператор объединения ссылок) |
|
* умножение |
>= больше или равно |
|
|
/ деление |
<= меньше или равно |
|
|
% процент |
<> не равно |
При вычислении математических выражений по формуле Excel руко водствуются следующими традиционными правилами, определяющими приоритет выполнения операций:
• в первую очередь вычисляются выражения внутри круглых скобок;
• определяются значения, возвращаемые встроенными функциями;
• выполняются операции возведения в степень (^), затем умножения (*) и деления (/), а после - сложения (+) и вычитания (-).
Необходимо отметить, что операции с одинаковым приоритетом вы полняются слева направо.
В процессе выполнения вычислительных операций возможны ошибочные действия со стороны пользователя, в результате которых в активной ячейке появится запись с указанием причины ошибки.
В Excel имеется целый ряд встроенных математических функций, существенно облегчающих решение задач. Синтаксис функций:
Имя Функции (Аргумент 1;...;Аргумент N).
Здесь в качестве аргумента функции может использоваться как непосредственное значение, так и адрес ячейки или диапазона.
При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой.
Например, формула
=ПРОИЗВЕД(А1;В2;С4)
означает, что необходимо перемножить числа в ячейках A1, B2 и С4. Любой аргумент функции может быть диапазоном, содержащим произвольное число ячеек листа. Например, функция
=ПРОИЗВЕД(А1:А3;В2:В4)
имеет два аргумента, но перемножает содержимое шести ячеек.
Аргументы не обязательно должны образовывать непрерывные диапазоны ячеек:
=ПРОИЗВЕД(А1:А3;В2;В4:В7).
Некоторые функции, например ПИ(), не имеют аргументов. Комбинацию функций можно использовать для создания выражения, например
=CУMM(KOPEHЬ(16);COS(A1*ПИ())).
Перечень всех встроенных математических функций с их описанием можно посмотреть в Мастере функций. Некоторые из функций приведены в таблице.
Таблица 3. Встроенные математические функции
|
№ |
Наименование |
Обозначение |
Примечание |
|
2 |
Абсолютное значение |
=ABS(x) |
- число, ссылка на ячей ку с числом или формула, возвращающая числовое значение |
|
3 |
Сумма |
=СУММ(х1;...;xn) |
; игнорируются пустые ячейки, текстовые и логические значения |
|
4 |
Произведение |
=ПРОИЗВЕД(x1;...;хn) |
|
|
5 |
Корень квадратный |
=КОРЕНЬ(х) |
|
|
1 |
Натуральный логарифм |
=LN(х) |
х>0, при возвраща ется ошибочное значение #ЧИСЛО! |
|
2 |
Десятичный ло гарифм |
=LOG10(x) |
х>0, при х<0 возвраща ется ошибочное значение #ЧИСЛО! |
|
3 |
Логарифм по заданному ос нованию |
=LOG(х;основание) |
х>0, при возвраща ется ошибочное значение #ЧИСЛО! По умолчанию основание равно 10 |
|
4 |
Экспонента от х |
=ЕХР(х) |
|
|
1 |
=ПИ() |
Возвращает значение с 14 значащими разрядами после десятичной точки |
|
|
Тригонометрические функции |
|||
|
4 |
sin x |
=SIN(x) |
х - угол в радианах |
|
5 |
cos x |
=COS(x) |
х - угол в радианах |
|
6 |
tg x |
=TAN(x) |
х - угол в радианах |
|
7 |
arctg x |
=ATAN(x) |
Возвращаемое значение лежит на интервале между -/2 и /2 радиан |
|
8 |
arcsin х |
=ASIN(x) |
Ограничения на аргумент: . Возвращае мое значение лежит на ин тервале между -/2 и /2 радиан |
|
9 |
arccos x |
=ACOS(x) |
Ограничения на аргумент: . Возвращае мое значение лежит на ин тервале от 0 до радиан |
|
Логические функции |
|||
|
И |
=И(логическое значение1; логическое значение2;…) |
Возвращает значение ИСТИНА, если все её аргументы принимают значение ИСТИНА. Если хотя бы один из её аргументов принимает значение ЛОЖЬ, функция И возвращает значение ЛОЖЬ. |
|
|
ИЛИ |
=ИЛИ(логическое значение1; логическое значение2;…) |
Возвращает значение ИСТИНА, если хотя бы один из её аргументов принимает значение ИСТИНА. Функция ИЛИ возвращает значение ЛОЖЬ только тогда, когда все её аргументы принимают значение ЛОЖЬ |
|
|
Отрицание |
=НЕ(логическое значение) |
Изменяет логическое значение своего аргумента на противоположное. Если аргумент этой функции принимает значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА и наоборот. |
|
|
Условие |
=ЕСЛИ(логическое выражение; действие1; действие2) |
Если логическое выражение принимает значение ИСТИНА, то выполняется действие1, в противном случае выполняется действие2) |
Ввод функций
Функции могут вводиться в рабочий лист несколькими способами. После ввода знака «=» функция может либо быть введена непосредственно с клавиатуры, либо выбираться в поле имени, которое в этой ситуации становится полем функции (рис. 3).
Существует также два способа, равноценных последнему, но не требующих предварительного ввода знака равенства:
1) через пункт меню Вставка Функция;
2) с помощью кнопки Вставка функции
Функция определяется за два шага. На первом шаге в открывшемся окне диалога Мастер функций необходимо сначала выбрать категорию в списке Категория, а затем в алфавитном списке Функция выделить необходимую функцию. На втором шаге задаются аргументы функции. Второе окно диалога мастера функций содержит по одному полю для каждо го аргумента выбранной функции. Если функция имеет переменное число аргументов, то окно диалога увеличивается при вводе дополнительных аргументов. После задания аргументов необходимо нажать кнопку Оk или клавишу Enter.
Пример 1. Ввести в ячейку В4 формулу: =ЦЕЛОЕ(6,7) и затем нажать кла вишу Enter. В результате в данной ячейке отобразится число 6. Так же определяется целое отрицательного числа. Так, если в ячейку В4 записать фор мулу: =ЦЕЛОЕ(-6,7) и нажать клавишу Enter, то в этой ячейке отобразится чис ло -7 .
Вывод: данная функция выполняет операцию округления до ближайшего меньшего числа.
Пример 2. Ввести в ячейку A3 число 15, а в ячейку В3 - число 7. В ячейку В5 записать формулу: =ЦЕЛОЕ(А3/В3). Результат - число 2.
Записать в ячейку В5 формулу: =ОСТАТ(А3;В3). Результат - число 1.
Общая формула операции округления имеет вид: ОКРУГЛ(число; число раз рядов). Если в формуле число разрядов (целое число) больше нуля, то число ок ругляется до указанного количества десятичных разрядов справа после деся тичной запятой. Если число разрядов равно нулю, то число округляется до бли жайшего целого. Если число разрядов меньше нуля, то число округляется до указанного количества десятичных знаков слева от десятичной запятой.
Пример 3. В ячейку A3 записать число 123,4174, а в ячейки С5; С6; С7 соот ветственно формулы: =ОКРУГЛ(А3;2); =ОКРУГЛ(А3;0); =ОКРУГЛ(А3;-1). По сле нажатия на клавишу Enter в ячейках С5; С6; С7 отобразится окончательный результат: 123,42; 123; 120.
Представляют интерес ещё две операции округления: ОКРУГЛВНИЗ(число; число разрядов) и ОКРУГЛВВЕРХ(число; число разрядов), выполнение которых позволит округлять числа в большую или меньшую сторону.
Пример 4. В ячейку A3 записать число 123,4174, а в ячейку С5 формулу =ОКРУГЛВНИЗ(А3;2); С6 - формулу =ОКРУГЛВВЕРХ(А3;2). Конечные ре зультаты в этих ячейках будут соответственно равны 123,41 и 123,42.
Существует много сложных формул, в которых присутствуют переменные и . Вычисления путём присваивания этим переменным числовых значений представляют определённые трудности. В Excel выполнение отдельных опе раций упрощает вычисление подобных формул.
В следующем примере рассмотрим операцию присвоения имён ячейкам.
Пример 5. В ячейки А2 и A3 записать буквы: "х" и "у" (обязательно в ка вычках, для визуального контроля количества переменных). Затем присвоить имена х и у ячейкам С2 и С3. С этой целью вначале активизируется ячейка С2, в результате в окне ввода имени над столбцом А появится адрес С2, который следует выделить с помощью мыши, в это поле ввести символ х и нажать кла вишу Enter. Затем аналогичную операцию присвоения выполнить для ячейки С3, но только вводимым символом для этой ячейки будет у. Далее сле дует ввести в ячейки С2 и С3 число 2. Это будет означать, что х = 2 и у = 2.
И если теперь в ячейку В5 записать формулу: =(2+х)/(2*у) и нажать клавишу Enter, то в ней отобразится результат 1. Такой метод ближе к естест венной форме записи формулы и последующего вычисления по ней.
Аналогично можно присваивать имена диапазонам ячеек.
Пример 6. Вычислить значение выражения .
Выделить ячейку А2. Ввести знак равенства (признак формулы) или щелк нуть кнопку «=», расположенную слева от строки ввода формулы. Далее с по мощью пиктограммы панели инструментов «Стандартная» следует вызвать «Мастер функций». На экране появится диалоговое окно «Мастер функций - шаг 1 из 2», в котором щелчком левой кнопки мыши выбрать: «Категория» - Ма тематические, а справа, в появившемся списке математических функций, вы брать функцию с именем LOG. В нижней части окна отобразится краткое описа ние функции: LOG(число, основание логарифма) - нахождение логарифма числа по заданному основанию. Нажав на кнопку «ОК», перейти к новому диалого вому окну «Мастер функций - шаг 2 из 2». В поле «Число» ввести число 3, а в поле «Основание логарифма» записать значение, равное 2 (если не вводить никакого числа, то автоматически устанавливается основание лога рифма, равное 10). Ниже этих полей будет отображаться результат 1,584962501. После нажатия кнопки «ОК» этот результат отобразится в ячейке А2. Чтобы вычисление осуществлялось по основной формуле, т.е. с учетом второго сла гаемого - числа, равного 4, необходимо после сформированной и вычисленной формулы: =LOG(3;2) щелкнуть левой кнопкой мыши в поле ввода ячейки А2 и ввести недостающее слагаемое +4. Окончательная формула будет иметь вид: =LOG(3;2)+4. Нажать клавишу Enter. Результат: 5,58496.
Пример 7. Вычислить .
Левой кнопкой мыши: активизировать ячейку А2; выбрать пиктограмму ; в диалоговом окне «Мастер функций» выбрать: «Категория» - Матема тические и «Функция» - SIN; нажать на кнопку Оk. В новом диалоговом окне «Аргументы функции» в поле «Число» ввести ПИ()/6, далее нажать Оk. В ячейке А2 появится результат.
Использование встроенных функций можно осуществить и без диалогового окна «Мастер функций».
Пример 8. Вычислить значение функции при .
Переменную , например, равную , ввести в ячейку А2. Активизировать ячейку В2 и записать в неё формулу: =(TAN(COS(A2)))^(1/2). Результат: 0,85451.
Пример 9. Вычислить значение выражения .
Активизировать ячейку А2 и записать в неё формулу: =SIN(ПИ()/6)+SIN(ПИ()/4)^2+ТАN(ПИ()/4), далее нажать Enter. Ответ. 2.
Пример 10. Вычислить выражение . Активизировать ячейку А2 и записать в неё формулу =COS(ПИ()/l80*225)+ТАN(ПИ()/180*225), далее нажать Enter.
Пример 11. Вычислить значение выражения . Активизировать ячейку А2 и записать в неё формулу =3^(LOG(4;2))-5^(2-LOG(10))+5^(LOG(3;2)), далее нажать Enter.
Пример 12. Определить значение функции, заданной несколькими аналитическими выражениями, с использованием вложенной функции ЕСЛИ.
Дана функция: .
Левой кнопкой мыши активизировать ячейку А2 и ввести число 4; выделить ячейку С2; выбрать пиктограмму «»; в диалоговом окне «Мастер функций» выбрать: «Категория» - Логические, «Функция» - ЕСЛИ; нажать на кнопку Оk.
На экране монитора отобразится диалоговое окно. В текстовое поле «Логическое выражение» ввести выражение условия =ЕСЛИ(А2<2;A2^2+1;2*A2+2), которое автоматиче ски отображается в строке формул. В правом нижнем углу данного диалогового окна отобразится число 10, которое будет зафиксировано в ячейке С2 после на жатия кнопки Оk.
1. Найти ЦЕЛОЕ и OCTAT для следующих числовых отношений: 5:3; 8,4:2,7; 9,1:4; 7,2: 3,8; 26:15; 141:27; 87:19; 101:45.
2. Выполнить операции ОКРУГЛЕНИЯ:
145,4472; 247,2432; 32,1515; 1485,8767 - округлить до второго знака после запятой;
12,5454; 1423,3214; 57,98; 34,5875 - выполнить операции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.
3. Используя операцию присвоения имён (см. пример 5) и придавая различные значения х и у, рассчитать арифметические формулы: , , , .
4.Определить: . Ответ: 2.
5.Определить: .
Указание: Записать =1/TAN(405*ПИ()/180)+1/TAN(390*ПИ()/180).
Ответ: 2,73.
6.Определить: .
Указание: Записать =СOS(3*ПИ()/2+2*(ПИ()/2-АТАN(4/3)).
Ответ: 0,96.
7.Определить: . Ответ: -0,6021.
8.Определить: . Ответ: 23.
9.Вычислить, используя правила логарифмирования: , где , , . Ответ: 2,0536.
10.Вычислить, используя правила логарифмирования:
1) . Ответ: -1,2201. 2) . Ответ: 1,3219.
3) . Ответ: 0,0291. 4) . Ответ: 87,5.
11.. Ответ: 1,1547.
12.. Ответ: -0,1116.
13.. Ответ: 0,8420.
14.. Ответ: 223,4841.
Ссылки в пределах рабочего листа
Данные для вычислений по формуле могут непосредственно вводить ся в формулу: = 2+3, а также считываться из других ячеек. Для доступа к данным в других ячейках рабочего листа используются ссылки. Ссылка является идентификатором ячейки или группы ячеек в книге.
В Excel различают ссылки трёх типов: относительные, абсолют ные, смешанные. Существует два стиля оформления ссылок: стиль А1 или основной, и стиль R1C1.
Ссылки в стиле А1
Рассмотрим типы ссылок в стиле А1. Ссылка состоит из имени столбца (обозначаются буквами от А до IV, 256 столбцов максимально) и номера строки (от 1 до 65536). Например, А77. Для ссылки на диапазон ячеек указываются адреса левой верхней и правой нижней ячейки диапазона, разделённых двоеточием.
Относительная ссылка. Все ссылки в Excel по умолчанию являются относительными. Рассмотрим их на примере. Введём в ячейки А1 и В1 соответственно значения 1 и 2, а в ячейку В2 поместим формулу =А1+В1 (рис. 1а). После нажатия Enter в ячейке В2 появится значение 3. При помощи маркера автозаполнения скопируем эту формулу из ячейки В2 в нижеследующие ячейки, например, в диапазон В3:В5. Убеждаемся, что в ячейках В3, В4, В5 появились формулы =А2+В2, =А3+В3, =А4+В4 соответственно.
Таким образом, при копировании формул указанные в них адреса ячеек изменяются (убедитесь в этом, переключившись в режим отображения формул: Сервис Параметры вкладка Вид в поле Параметры окна отметить опцию Формулы). Адреса изменяются таким образом, чтобы расположение адре суемых ячеек относительно ячейки с формулой оставалось неизменным (в нашем случае в формулах всегда будет указываться адрес ячейки, расположенной над той ячейкой, которая содержит формулу). Поэтому адреса, используемые в формулах, называются относительными адресами.
Абсолютная ссылка всегда указывает на зафиксированную при создании формулы ячейку или диапазон и не изменяется при переносе или копировании формулы в другую ячейку. Механизм абсолютной адресации включается в двух случаях:
• при записи знака $ перед именем столбца и номером строки (рис. 1б) (признаком абсолютной ссылки является знак доллара перед именем строки или столбца).
• при использовании имени ячейки.
Если до момента фиксации ввода формулы нажимать на клавишу F4, можно изменить ссылку либо на абсолютную, либо на смешанную.
Например, проделаем ту же работу на листе 2. Введём в ячейки А1 и В1 соответственно значения 1 и 2, а в ячейку В2 поместим формулу =$А$1+$В$1. Для набора этой формулы поступают следующим образом. В ячейке В2 пишем знак =, затем мышью выделяем ячейку А1 и нажимаем F4, появляется запись =$A$1. Аналогично продолжаем набор формулы: +, затем выделяем мышью ячейку В1 и нажимаем F4. После нажатия Enter в ячейке В2 появится значение 3. При помощи маркера автозаполнения скопируем эту формулу из ячейки В2 в нижеследующие ячейки, например, в диапазон В3:В5. Убеждаемся, что в ячейках В3, В4, В5 появились формулы =$А$1+$В$1, =$А$1+$В$1, =$А$1+$В$1 соответственно.
Смешанные ссылки представляют собой комбинацию из относи тельных и абсолютных ссылок. Можно определить два типа смешанных ссылок.
1. Смешанная ссылка первого типа. В ссылках этого типа символ $ стоит перед буквой, поэтому координата столбца рассматривается как абсолютная, а координата строки - как относительная.
2. Смешанная ссылка второго типа. В ссылках этого типа символ $ стоит перед числом, поэтому координата столбца рассматривается как относительная, а координата строки - как абсолютная.
Примеры смешанных ссылок: =$B1+$D7, =B$1+D$7.
Ссылки в стиле R1C1
При использовании стиля ссылок R1C1 ячейки адресуются по номе рам строк (Row) и столбцов (Column). В формате R1C1, после буквы «R» указывается номер строки ячейки, после буквы «С» - номер столбца. В частности, ссылка R1C1 означа ет: строка 1, столбец 1. Абсолютная ссылка R1C1 эквивалентна абсолютной ссылке $A$1 для формата А1. Для задания относительной ссылки указывается смещение по отношению к активной ячейке. Смешение указывается в квадратных скобках. Знак указывает направление смещения.
Например,
R[-3]С (относительная ссылка на ячейку, расположенную на три строки выше в том же столбце).
R[2]С[2] (относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее).
R2С2 (абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце).
R[-1] (относительная ссылка на строку, расположенную выше текущей ячейки),
R (абсолютная ссылка на текущую строку).
Для активизации стиля необходимо выбрать ко манду Сервис Параметры и на вкладке Общие установить флажок Стиль ссылок R1C1. Для доступа к данным по умолчанию применяются абсолютные ссылки, а при заключении номеров строк и/или столбцов в квад ратные скобки включается механизм относительных ссылок.
Например, формула =R[2]C[-1]+R[2]C в ячейке R3C2 (рис. 2) читается следующим образом: сложить содержимое ячейки, расположенной на две строки ни же и на один столбец левее ячейки с формулой, с ячейкой, находящейся на две строки ниже в том же столбце. Формула =R1C1+R1C2 в ячейке R3C1 использует абсолютные ссылки на ячейки, находящиеся на пересечении первой строки с первым и вторым столбцами.
Примеры:
1. Абсолютные ссылки:
R2C6 - ячейка, расположенная во второй строке и шестом столбце;
R5C3 - ячейка, расположенная в пятой строке и третьем столбце.
2. Относительные ссылки:
RC - ячейка ввода;
RC[1] - ячейка справа от ячейки ввода;
RC[-2] - ячейка вторая слева от ячейки ввода;
R[3]C - ячейка третья снизу от ячейки ввода;
R[-1]C - ячейка сверху от ячейки ввода;
R[1]C[-1] - ячейка, расположенная на одну строку ниже и на один столбец левее ячейки ввода.
Трассировка ссылок и зависимостей
Для контроля за правильностью потоков данных и источниками оши бок в Excel используется трассировщик ячеек. При трассировке отмеча ются влияющие и зависимые ячейки.
Влияющие - это ячейки, значения которых используются формулой в выделенной ячейке. Ячейка, для которой определены влияющие ячейки, всегда содержит формулу.
Зависимые - это ячейки, которые используют значение выделенной ячейки. Ячейка, для которой определены зависимые ячейки, может со держать формулу или константу.
Для трассировки необходимо выбрать пункт Сервис Зависимости и затем либо Зависимые ячейки, либо Влияющие ячейки. Так, на рис. 3а в ячейке В2 произведена трассировка влияющих ячеек А1 и В1. А на рис. 3б видно, что ячейкой, зависимой от В2 является ячейка В3.
Неоднократный последовательный выбор пунктов Зависимые ячейки либо Влияющие ячейки позволяет проследить косвенно зависимые и кос венно влияющие ячейки.
В данной работе изучается технология построения графиков функций, заданных различными способами:
I. В прямоугольной системе координат – 1) явным способом, т.е. выражением вида (в том числе рассматриваются функции, заданные тремя ветками), 2) параметрическим способом, т.е. зависимостями вида .
II. В полярной системе координат уравнением вида , где - полярный радиус точки кривой, - полярный угол этой точки.
Остановимся подробно на полярной системе координат.
Для определения положения точки на плоскости, кроме декартовой системы координат, используется полярная система координат.
Пусть на плоскости даны некоторая точка О и луч ОР с началом в этой точке, а также указана единица масштаба ОЕ=1 (рис. 1). Точка О называется полюсом, точка Е – единичной точкой, а луч ОР – полярной осью. Таким образом, элементами полярной системы координат являются: 1) точка О – полюс, 2) луч ОР, выходящий из точки О – полярная ось, 3) единица измерения длины.
Пусть М – произвольная точка плоскости. Полярным радиусом точки М называется расстояние r=ОМ от полюса до этой точки. Полярным углом точки М называется угол, на который нужно повернуть полярную ось против вращения часовой стрелки до совпадения с лучом ОМ. Если под углом понимать угол, который получается вращением полярной оси ОР по часовой стрелке до совпадения с ОМ, то считают отрицательным. Кроме того, за полярный угол точки М можно принять угол +2n, где nZ. Полярный угол, удовлетворяющий условиям называется главным значением полярного угла.
Если точка М совпадает с полюсом, то r=0, а угол не имеет определённого значения. Однако в некоторых задачах углу придают определённое произвольное значение. Пара чисел (r,) называется полярными координатами точки М. Записывают это так: М(r,).
Пример. Построить точку по полярным координатам А.
Решение: Повернём полярную ось на угол . Затем отложим от полюса в положительном направлении построенной оси отрезок ОА, равный по длине четырём единицам (рис. 2). Заметим, что для точки А можно было указать другие координаты: А или А. Главным значением полярного угла точки А является .
Установим связь между декартовыми прямоугольными и полярными координатами одной и той же точки.
Пусть даны декартова прямоугольная система координат и полярная с полюсом в начале координат и полярной осью, совпадающей с осью абсцисс. Пусть М(х, у) – декартовы координаты точки М, М(r,) – её полярные координаты. Из прямоугольного треугольника OMN находим
(1)
Эти формулы выражают декартовы координаты точки М через её полярные координаты, т.е. зная полярные координаты точки М можно найти её декартовы координаты.
Решим обратную задачу: как найти полярные координаты точки М, зная её декартовы координаты. Для этого возведём обе части каждого из равенств (1) в квадрат и сложим их почленно. Получим , т.е. , откуда
(2)
Из равенств (1) также имеем
, (3)
Откуда
(4)
Полярный угол можно находить из формул (3) либо из формулы (4). В последнем случае мы получим два значения угла . Из этих двух значений угла нужно выбрать то, синус которого имеет тот же знак, что и y.
Построение графика функции в прямоугольной системе координат
Задание: Построить график функции , .
1. В ячейку А1 ввести заголовок х, в В1 ввести у.
2. С помощью маркера автозаполнения в столбце А, начиная с ячейки А2, получить зна чение х от 0 до 1 с шагом 0,05 (Либо с помощью команд Правка Заполнить Прогрессия).
3. В ячейку В2 ввести формулу: =SIN(ПИ() *A2)^2
4. Скопировать формулу на все ячейки диапазона В3:В22.
5. Выделить два столбца со значениями х, у.
6. Вызвать мастер диаграмм (вы полнить команду Вставка Диаграмма, либо на панели ин струментов нажать кнопку «Мастер диаграмм»: ) .
7. В появившемся диалоговом окне выбрать тип диаграммы - «точечная» и выбрать один из предложенных вариантов. Нажать на кнопку Далее (рис. 4).
8. В следующем окне проверить правильность заполнения диапазо на. Нажать кнопку Далее.
9. Ввести название диаграммы График , Название оси х: х, оси у: у. Нажать кнопку Готово.
10. Результат работы представлен на рис. 5.
Построение графиков в полярной системе координат
Задание: построить график кардиоиды .
1. Решение этой задачи сведем к предыдущей задаче. Перейдём от полярных координат к декартовым координатам , используя формулы перехода: , .
2. Запишем заголовки: в А1: fi, в ячейку В1: r, в ячейку С1: х, в ячейку D1: у.
3. В столбце А с помощью маркера автозаполнения создать ряд значений (fi) от 0 до с шагом (либо с помощью команд: Правка Заполнить Прогрессия (шаг 0,314, предельное значение 6,28)).
4. В ячейке В2 пишем формулу =3*(1+COS(A2)). Копируем формулу до значения
5. В ячейке С2 пишем формулу =B2*COS(A2) и копируем её до значения .
6. В ячейке D2 пишем формулу =В2*SIN(А2) копируем её до значения .
7. Выделив диапазон C2:D22 данных, строим точечную диаграмму.
8. Результат работы представлен на рис. 6.
Построение графиков функций, заданных параметрически
Задание: Построить график функции
1. Решение этой задачи сведём к предыдущей задаче.
2. Запишем заголовки: в А1: t, в ячейку В1: х, в ячейку С1: у.
3. В столбце А с помощью маркера автозаполнения создать ряд значений для t от 0 до с шагом (либо при помощи набора команд: Правка Заполнить Црогрессия (шаг 0,314, предельное значение 6,28)).
4. В ячейку В2 вводим формулу =COS(2*A2)*SIN(A2) и копируем её в диапазоне В3:В22.
5. В ячейку С2 вводим формулу =COS(A2)*SIN(3*A2) и копируем её в диапазоне С3:С22.
6. Выделяем диапазон данных В2:С22 со значениями х и у, строим точечную диаграмму.
7. Результат работы представлен на рис. 7.
Построение графиков кусочно-непрерывной функции
Задание: Построить график функции , заданной тремя ветками на отрезке .
.
Для построения этого графика шаг изменения желательно выбирать поменьше, например, , и т. д. Далее в мастере диаграмм выбирать точечную диаграмму (первую в первой строке).
1. В ячейке A1 записываем заголовок X.
2. В ячейке В1 записываем заголовок f1.
3. В ячейке С1 записываем заголовок f2.
4. В ячейке D1 записываем заголовок f3.
5. В ячейке E1 записываем заголовок F(x).
6. В столбце А создаём ряд значений для х от -0,2 до 2,41 с шагом 0,03. Такой диапазон изменения взят с учётом промежутков, на которых задан каждый «кусок» функции. Так, по условию, у нас , поэтому можно взять в качестве крайнего левого значения аргумента. Кроме того, из третьего участка функции видно, что . Поэтому в качестве крайнего правого участка взято .
7. В ячейке B2 записываем формулу для вычисле ния функции по 1-й ветке: =ATAN(3,1*A2) и копируем её в столбце B.
8. В ячейке С2 записываем формулу для вычисле ния функции по 2-й ветке: =SIN(A2)^2*LN(A2) и копируем её в столбце С.
9. В ячейке D2 записываем формулу для вычисле ния функции по 3-й ветке: =КОРЕНЬ(A2^2+4*A2+11) и копируем её в столбце D.
10. В Е2 запишем формулу:
=ЕСЛИ(А2<0,47;В2;ЕСЛИ(А2>=2;D2;С2))
Скопируем её в столбце Е до конца диапазона изменения аргумента функции.
11. Выделим диапазон, состоящий из данных в столбце А и данных в столбце Е (используя кла вишу CTRL) и строим точечную диаграмму.
12. Результат работы представлен на рис. 8.
1. Построить график функции в прямоугольной системе координат. Диапазон изменения и шаг выберите самостоятельно:
2. Построить график функции, заданной в полярной системе координат:
3. Построить график функции, заданной параметрическим способом:
4. Построить графики функций, используя функцию ЕСЛИ( )
а) Случай двух ветвей:
б) Случай трёх ветвей:
К кривым второго порядка относятся парабола, гипербола, эллипс (частный случай эллипса – окружность). Любая кривая второго по рядка в общем виде описывается уравнением второй степени с двумя переменными:
(1)
Коэффициенты , и не равны нулю одновременно.
Параболой называется множество всех точек, расстояния которых до данной точки, называемой фокусом, и до данной прямой, называемой директрисой, равны.
Каноническими уравнениями параболы являются:
1) , где - параметр параболы, расстояние от фокуса до директрисы, для кри вой с горизонтально расположенной осью; 2) - для параболы с вертикально расположенной осью.
Пример 1. Рассмотрим параболу . Для неё , т.е. ; осью параболы является ось (в уравнении параболы переменная в первой степени); ветви параболы направлены вправо (так как ).
Для построения кривой второго порядка в Excel в уравнении кривой выражают через : , откуда следует, что . Это определяет диапазон изменения аргумента , т.е. можно взять , например от 0 до 6.
Пример 2. Рассмотрим параболу . Имеем , т.е. ; осью параболы является ось (в уравнении параболы переменная в первой степени); ветви параболы направлены вниз (так как ).
Выражаем через : , откуда ясно, что - любое число. Поэтому в данном случае диапазон изменения берём симметрично относительно начала координат, например, от -5 до +5.
Гиперболой называется мно жество точек плоскости, разность расстояний от которых до двух данных точек и , на зываемых фокусами, есть величина постоянная, меньшая расстояния между фо кусами. Каноническое уравнение гиперболы имеет вид:
, ,
или , , .
Здесь - расстояние от начала координат до фокусов, а - расстояние от начала координат до вершин гиперболы.
В простейшем случае уравнение гиперболы имеет вид
.
Пример 3. Рассмотрим гиперболу .
1. Заметим, что в уравнении гиперболы перед подразумевается знак «+», а перед стоит знак «-», это означает, что ось является мнимой осью (гипербола не пересекает мнимую ось), а ось является действительной осью гиперболы (гипербола пересекает действительную ось в двух точках , - вершинах гиперболы). Полуоси гиперболы находим следующим образом: , откуда, так как имеем, что ; , откуда, так как имеем .
2. Выразим через в уравнении гиперболы: , , , .
3. На основании этого определим диапазон изменения , т.е. найдём область определения полученной функции: , , . Это означает, что правую ветвь гиперболы надо строить в диапазоне (т.е. брать от 5 до, например, 8), а левую - (т.е. брать от -8 до -5).
Пример 4. Рассмотрим гиперболу .
1. Заметим, что в уравнении гиперболы перед подразумевается знак «+», а перед стоит знак «-», это означает, что ось является мнимой осью (гипербола не пересекает мнимую ось), а ось является действительной осью гиперболы (данная гипербола пересекает действительную ось в двух точках , - вершинах гиперболы).
Полуоси гиперболы: , откуда , , откуда .
2. Выразим через в уравнении гиперболы: , , , .
3. Определим диапазон изменения , т.е. найдём область определения полученной функции. В данном случае . Поэтому при построении данной гиперболы можно взять в диапазоне, например, от -5 до 5.
Эллипсом называется множество точек плоскости, сумма расстояний ко торых до двух данных точек (эта сумма обозначается ), называемых фокусами, есть величина постоянная, большая расстояния между фокусами. Каноническое уравнение эллипса имеет вид: (здесь ).
Так как , то , т.е. эксцентриситет эллипса находится в пределах .
Пример 5. Рассмотрим эллипс .
1. Так как , , то его полуоси равны , . Это означает, что все точки эллипса имеют абсциссы в диапазоне , а ординаты в диапазоне .
2. Выразим через в уравнении эллипса: , , , , откуда .
3. Находим область определения полученной функции: , , , , что было получено выше.
Окружность является частным случаем эллипса, а именно это эллипс с равными полуосями.
Окружностью называется множество точек плоскости, находящихся на одинако вом расстоянии от данной точки, называемой центром.
Каноническое уравнение окружности имеет вид: - уравнение окружности с центром в начале координат и радиусом R, - уравнение окружности радиуса с центром .
Для построения кривых второго порядка в MS Excel их уравнения должны быть предварительно приведены к виду (т.е. разрешены относительно переменной у).
Пример 6. Построение параболы вида в диа пазоне с шагом .
1. Ввод данных. На Листе1 составьте таблицу данных (х и у), как показано на рисунке 1.
2. Построение диаграммы (графика). Вызвать Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы указать тип диаграммы: Точечная, вид - вторая диаграмма во втором ряду. Перейти ко второму шагу Мастера диаграмм, нажав кнопку Далее. В рабочем поле Диапазон данных установить курсор и левой кнопкой мыши выделить диапазон В2:В14 на Листе1 Excel. После чего в поле Диапазон данных появится: =Лист1!$В$2:$В$14. Переключа тель Ряды в с помощью указателя мыши следует установить в положение столбцах. Затем перейти на вкладку Ряд. В поле Значения х установить курсор и левой кнопкой мыши выделить диапазон А2:А14. Нажав кнопку Далее перейти к шагу 3. Здесь на вкладке Заголовки вводятся название диаграммы и осей. Остальные вкладки можно не трогать. Следующий шаг Мастера диаграмм выполните самостоятельно, чтобы получилось так, как показано на рисунке 2. Дважды нажав кнопкой мыши на полученном графике можно изменить толщину линий, их цвет и другие параметры (изучите самостоятельно).
Пример 7. Построение параболы вида в диа пазоне с шагом .
Для этого необходимо в уравнении параболы выразить через : . Поэтому искомый график будет состоять из двух кусков: первый лежит выше оси (значения ), второй – ниже оси (значения ).
1. Ввод данных. На Листе2 составьте таблицу данных (х и у), как показано на рисунке 1. При этом в ячейку В2 заносим формулу «=КОРЕНЬ(А2)», а в ячейку С2 – формулу «= - КОРЕНЬ(А2)» или «= - В2».
2. Построение диаграммы (графика). Вызвать Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы указать тип диаграммы: Точечная, вид - вторая диаграмма во втором ряду. Перейти ко второму шагу Мастера диаграмм, нажав кнопку Далее. В рабочем поле Диапазон данных установить курсор и левой кнопкой мыши выделить диапазон В2:В26 на Листе2 Excel. После чего в поле Диапазон данных появится: =Лист2!$В$2:$В$26. Переключа тель Ряды в с помощью указателя мыши следует установить в положение столбцах. Затем перейти на вкладку Ряд. В поле Значения х установить курсор и левой кнопкой мыши выделить диапазон А2:А26. Слева в поле Ряд нажать кнопку Добавить. Справа установить курсор в поле Значения Х: и левой кнопкой мыши выделить диапазон А2:А26. В поле Значения Y: установить курсор и выделить левой кнопкой мыши диапазон С2:С26. Нажав кнопку Далее перейти к шагу 3. Здесь на вкладке Заголовки вводятся название диаграммы и осей. Остальные вкладки можно не трогать. Закончите выполнение шагов Мастера диаграмм. Дважды нажав кнопкой мыши на каждом из полученных кусков графика, сделайте одинаковым цвет и толщину линий.
Пример 8. Построение гиперболы в диапазоне с шагом .
. 1. Ввод данных. На Листе3 составьте таблицу данных (х и у), как показано на рисунке 4.
2. Построение диаграммы (графика). Вызвать Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы указать тип диаграммы: Точечная, вид – второй во втором ряду. В рабочем поле Диапазон указать диапазон: =Лист3!$А$2:$А$21. Переключа тель Ряды в с помощью указателя мыши следует установить в положение столбцах. И затем нажать кнопку Далее в ди алоговом окне. Последующие шаги Мастера диаграмм выполните самостоятельно, чтобы получилось так, как показано на рисунке 4.
Задание. Внимательно изучив содержимое рисунка 5, выполните построение гиперболы в указанных там диапазонах.
Пример 9. Построение гиперболы (см. пример 3).
Выполните построение гиперболы, используя данные рисунка 6. Отметим, что в данном случае получается 4 ряда данных.
Пример 5. Построение гиперболы (см. пример 4).
Выполните построение гиперболы, используя данные рисунка 7. Отметим, что в данном случае получается 2 ряда данных, которым соответствует верхняя и нижняя ветви гиперболы.
Пример 6. Построение верхней полуокружности .
1. Выразим через в уравнении окружности: , . Верхней полуокружности отвечают положительные значения , т.е. берём .
2. Определим диапазон изменения , т.е. найдём область определения полученной функции: , , , .
3. Выполните построение верхней полуокружности, используя данные рисунка 8.
Задание. Постройте окружность из примера 6.
1. Постройте гиперболы (рассчитайте диапазон, шаг выберите самостоятельно):
2. Постройте эллипсы (рассчитайте диапазон, шаг выберите самостоятельно):
3. Постройте параболы (рассчитайте диапазон, шаг выберите самостоятельно):
|
|
Рис. 3
Рис. 2
Рис. 1
Рис. 8
Рис. 1. Исходные данные из примера 1
Рис. 2
а б
Рис. 1
Рис. 5
Рис. 3
Рис. 6
Рис. 4
Рис. 2а Рис. 2б
|
Рис. 3а. Трассировка влияющих ячеек |
Рис. 3б. Трассировка зависимых ячеек |
Рис. 3
Рис. 2. Ссылки в стиле R1C1
Рис. 1а. Относительные ссылки Рис. 1б. Абсолютные ссылки
Рис. 7
Рис. 6
Рис. 5
Рис. 4
Рис. 8
Рис. 7
Учебно-методическое пособие