Построение таблицы значений заданной функции

Цель:

• закрепить навыки по заполнению и редактированию таблицы;

• познакомиться со способами адресации;

• освоить некоторые приемы оформления таблиц.

 

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

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

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

№	Х	Y1=С os2x	Y2=2 ln(х2+1)	Y=Y1+Y2

ЗАДАНИЕ 1. Прежде чем перейти к выполнению задачи, позна­комьтесь с понятием формула и со способами адресации в 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).

В ячейках A 1, B 1 и C 1 введены значения коэффициентов соответственно a, b, c.

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

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

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

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

Относительная ссылка (адресация) указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула. Пусть в ячейку В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.