МЕТОДИКА ПОСТРОЕНИя ТАБЛИЦЫ ЗНАЧЕНИЙ ЗАДАННОЙ ФУНКЦИИ

Лабораторная работа № 3

Составление таблиц значений функции и построение ее графика

Цель работы:

o Выработать навыки применения различных типов ссылок. Табулирование выражений. Применение относительных ссылок. Применение абсолютных ссылок.

o Научиться строить графики функций .

Задание

Произвести табулирование выражения при начальном значении х = 1, 20 значений с шагом 0,1 и построить график зависимости y от x.

Вариант	Выражение		Вариант	Выражение

В формулах записывать:

ln − LN( ) –натуральный логарифм;

− КОРЕНЬ( );

х² − Х*Х или X^2

МЕТОДИКА ПОСТРОЕНИя ТАБЛИЦЫ ЗНАЧЕНИЙ ЗАДАННОЙ ФУНКЦИИ

Постановка задачи:

Вычислить значения функции у=cos²х –2ln(x²+1) для всех х на интервале [0,4] с шагом 0,2.

Решение должно быть получено в виде таблицы:

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

Понятие формула

Формулой называется в MS Excel последовательность символов, начинающихся со знака равенства «=». В эту последовательность могут входить константы, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является значение, рассчитанное по имеющимся данным. Если изменять значения в ячейках, на которые есть ссылка в формулах, то результат изменяется автоматически.

В качестве примера приведем формулы определения корней уравнения ax² +bx+c=0, которые находятся в ячейках А2 и А3:

=(B1-корень(B1*B1-4*A1*C1))/2/A1) и =(B1+корень(B1*B1-4*A1*C1))/2/A1).

В ячейках A1, B1 и C1 введены значения коэффициентов соответственно a, b, c.

1.1. В ячейку А1 введите 1, в ячейку В1– -5, в ячейку С1 – 6.

В ячейках А2 и А3 должно быть соответственно 2 и 3. Если поменять значения коэффициента а в ячейке А1 с 1 на –1, то ответ будет –6 и 1.

Функция КОРЕНЬ() в приведенных формулах вычисляет квадратный корень из числа, в данном случае B1*B1-4*A1*C1.

Абсолютная, относительная и смешанная адресация ячеек и блоков

Относительная ссылка (адресация) указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула. Пусть в ячейку В4 введена формула =В1+В2, Excel интерпретирует ее как "прибавить содержи­мое ячейки, расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше". При копировании формулы =В1+В2 из ячейки В4 в С4, Exсеl также интерпретирует формулу как "прибавить содер­жимое ячейки, расположенной тремя рядами выше, к содержи­мому ячейки двумя рядами выше". Таким образом, формула в ячей­ке С4 изменит свой вид на =С1 +С2.

Абсолютная адресация. Иногда при копировании формул необходимо сохранить ссылку на КОНКРЕТНУЮ ячейку (область), т.е. на основе фиксированного положения ее на листе, например, “ячейка находится в столбце В и в строке 12”. В этом случае необходимо воспользоваться абсолютной ад­ресацией. Для ее задания необходимо перед буквой колонки и перед номером ряда напечатать символ $. Например, $В$4 или $С$2:$F$48 и т.д.

Смешанная адресация. Символ $ ставится только там, где он необходим, например В$4 или $С2. Тогда при копировании один параметр адреса изменяется, а другой нет.

С помощью клавиши F4 можно быстро изменить тип ссылки, например, в ячейке А1 введена формула =В1+В2, и точка вставки находится перед В2. Если нажать F4, то ссылка изменяется на абсолютную: =В1+$B$2. Еще одно нажатие F4 приведет к тому, что ссылка станет смешанной: =B1+B$2. Еще одно нажатие F4 реверсирует ссылку: =B1+$B2.Если снова нажать F4, то произойдет возврат к исходной относительной ссылке.

Имена

Использование имен ячеек и блоков в формулах вместо ссылок повышает читабельность и упрощает анализ. Присвоить имя ячейке или блоку можно двумя способами:

1. Выделить ячейку или блок, перейти в поле имени строки формул и набрать в нем нужное имя. Окончание ввода – нажатие клавиши Enter.

2. После выделения ячейки или блока выполнить команду Вставка, Имя, Присвоить. В появившемся диалоговом окне Присвоение имени в нижней части будет выведен абсолютный адрес ячейки или блока, который содержит в себе имя рабочего листа, например: =Лист3!$A$1:$A$21 (выделен блок ячеек). В текстовое поле Имя необходимо ввести требуемое имя и нажать кнопку Ok.

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

Для предыдущего примера зададим ячейкам А1, В1, С1 имена a, b, c соответственно. Тогда формулы в ячейках А2 и А3 будут иметь привычный для нас вид

=(b-корень(b^2-4*a*c))/(2*a)и=(b+корень(b^2-4*a*c))/(2*a).

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

ЗАДАНИЕ 2. Откройте файл Лабораторные работы.xls. Перейдите на Лист2. Назначьте ему имя Табулирование. Создайте основную и вспомогательную таблицы.

2.1. Заполните шапку основной таблицы, начиная с ячейки А1.

Шапка – это верхняя строка таблицы с названием столбцов (см. постановку задачи).

• В ячейку А1 занесите № (название первого столбца таб­лицы).

• В ячейку В1 занесите Х (название второго столбца таб­лицы).

• В ячейку С1 занесите Y₁=cos²x (название третьего столбца таб­лицы) и т.д. Индекс установите в разделе Видоизменение вкладки Шрифт диалогового окна Формат ячеек путем выбора соответствующего флажка. Окно Формат ячеек активизируется командой Формат, Ячейки после выделения символа, используемого в качестве индекса, в строке формул.

• Установите ширину столбцов такой, чтобы надписи были видны полностью.

2.2. Заполните вспомогательную таблицу начальными исход­ными данными, начиная с ячейки Н1: Х₀ - начальное значение X. Присвойте ячейкам H2 и I2 имена, расположенные в строке 1. Для этого надо:

· Выделить блок H1:I2. В выделяемый блок входят ячейки с текстовыми данными (предполагаемыми именами) и ячейки, которым присваиваются имена.

· Выполнить команду Вставка, Имя, Создать.

· В появившемся диалоге (рис. 2.1) надо указать, где расположены ячейки с назначаемыми менами. Excel предлагает свой вариант и, как правило, правильный.

Рис. 2.1. Диалоговое окно Создание имен

2.3. Заполните столбец А числами от 1 до 21, начиная с ячейки А2 и заканчивая ячейкой А22. Эту задачу можно решить двумя способами. Согласно первому способу:

· ввести в А2 цифру 1. Затем щелкнуть по кнопке Ввод в строке формул, если не была нажата клавиша Enter;

· выполнить команду Правка, Заполнить, Прогрессия. В диалоговом окне Прогрессия в разделе Расположение выбрать переключатель по столбцам. Значение шага оставить без изменения, в поле Предельное значение ввести 21 и нажать Ok (рис.2.2).

Рис. 2.2. Диалоговое окно Прогрессия

Второй способ:

· ввести в ячейку А1 число 1, в ячейку А2 число 2, выделить эти две ячейки;

· указатель в правый нижний угол выделенного блока – на экране указатель приобретает вид черного перекрестия и называется маркером заполнения;

· нажать кнопку мыши и, не отпуская ее, переместить указатель до ячейки А22;

· отпустить кнопку мыши – блок А1:А22 будет заполнен цифрами от 1 до 21.

2.4. Заполните столбец В значениями X.

• В ячейку В2 занесите =$Н$2 или =Х0. Это означает, что в ячейку В2 заносится значение из ячейки Н2 (начальное значение X), знак $ указывает на абсолютную ад­ресацию.

• В ячейку ВЗ занесите =В2+$I$2 или В2+Шаг.

Это означает, что начальное значение Х будет увеличено на величину шага, которая берется из ячейки I2.

• Заполните этой формулой ячейки В4: В22.

Столбец заполнится значениями Х от 0 до 4 с шагом 0,2.

2.5. Заполните столбец С значениями функции Y₁=Сos²x, рассчитанными по значениям аргумента в ячейках В2:В22.

• В ячейку С2 занесите =Сos(B2)^2.

• Заполните этой формулой ячейки С3:С22. Для этого надо выделить эту ячейку, нажать на маркер заполнения и, не отпуская левую кнопку мыши, переместить указатель до ячейки С22. Весь столбец заполнится значениями функции Y₁=cos²x.

2.6. Заполните столбец D значениями функции Y₂=2ln(х²+1).

• В ячейку D2 занесите =2*ln(В2*В2-1), скопируйте эту формулу на ячейки D3:D22.

2.7. Заполните столбец Е значениями функции Y=Y₁-Y₂ путем копирования формулы =$C2-$D2.

ЗАДАНИЕ 3. Понаблюдайте за изменениями в основной таблице при смене данных во вспомогательной. Для этого:

3.1. Введите в ячейку Н2 число -3,14159, а в ячейку 12 занесите 0,1.

При всех изменениях данных во вспомогатель­ной таблице в основной таблице пересчет производится автоматически

Контрольные вопросы

1. Какие функции выполняют программы, относящиеся к электронным таблицам?

2. Из чего состоит книга Excel и сколько ячеек содержится на каждом листе книги?

3. Какие данные может содержать ячейка?

4. Что такое A1 и R1C1?

5. Как происходит копирование и перемещение данных?

6. Что такое относительный и абсолютный адрес ячейки?

7. Как вводится в формулу абсолютный адрес ячейки?

8. Из каких элементов состоит строка формул и их назначение?

9. Как осуществляется редактирование содержимого ячейки?

10. Как вводится формула, и из каких допустимых элементов может состоять формула?

11. Что отображается на экране монитора в ячейке, содержащей формулу?

12. Какие ошибки в формулах выражают следующие обозначения: #ДЕЛ/0!, #ИМЯ?, #Н/Д, #ЧИСЛО!, #ЗНАЧ!, # # # # # # #?

13. Может ли функция быть без аргументов, не иметь после имени круглых скобок?