Перемещение формул и аргументов

Лабораторная работа 2

Тема: Ввод, редактирование, копирование и перемещение формул. Относительные и абсолютные адреса

Задание №1

В книге с лабораторными работами создать лист с именем Лабораторная работа №2

Дальнейшая работа выполняется на этом листе.

Ввод формул

Задание №2

1. Скопировать название и тему лабораторной работы на свой лист

2. Вести в указанные ячейки следующую информацию. Чтобы не вводить адрес ячейки вручную, достаточно просто щелкнуть мышкой на саму ячейку. Текстовый процессор сам вставит адрес указанной ему ячейки.

· А5= 1

· B6=2

· B5: =A5+B6

ВНИМАНИЕ!!! Все формулы в ячейки вводятся начиная со знака =

· D5= A5+B6*B5

· E5= D5-B5

3. Изменить числа в ячейках A5 и B6. Обратите внимание, как изменяются значения в ячейках с формулами.

Относительные адреса. Копирование формул

Если в ячейке B5 находится формула =A5+B6, Excel воспринимает ее следующим образом:

"Сложить числа, находящиеся ниже и левее ячейки с формулой."

Задание №3

1. Для того, чтобы проследить зависимости, выделите ячейку B5 и в меню Сервис выберите операцию Зависимости, в выпадающем меню выберите Влияющие ячейки.

2. Любым способом скопируйте формулу из ячейки B5 в ячейки D7, F6.

3. Откройте панель " Зависимости " (как в пункте 1.) и проследите расположение влияющих ячеек.

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

Использование относительной адресации при вычислениях

Задание №4

1. Создать таблицу значений функции у=x³ для значений x, изменяющихся от 0 до 10 с шагом 2.

2. В строке 10 разместить заголовки X и Y, а под ними вычисления.

3. В эту же таблицу добавить вычисление y1=x²+1 и y2=y+y1

ПОРЯДОК ВЫПОЛНЕНИЯ:

Чтобы получилась таблица вида:

x	y=x3	y1=x2+1	y2=y+y1

Необходимо выполнить следующие действия:

1. В ячейку А10 ввести значение Х и протянуть его до 10 с шагом 2 (лаб. раб №1)

2. Возведение в степень осуществляется двумя способами:

2.1. Можно просто ввести в ячейку В10 формулу "=А10*А10*А10";

2.2. Или же можно ввести в ячейку В10 формулу = А10^3, что значит возвести значение ячейки А10 в куб (символ ^ вставляется в табличный процессор сочетанием клавиш SHIFT+6);

3. В ячейке С10 ввести формулу =А10^2+1

4. В ячейку D10 ввести формулу =B10+C10

Абсолютные адреса

Excel позволяет сделать адрес абсолютным, т.е. не меняющимся при копировании.

Фиксировать адрес можно полностью или частично. Для фиксации используется знак " $ ".

Например:

· $A$1 — абсолютным является весь адрес ячейки. Это значит, что при протягивании адрес ячейки ($A$1) не будет меняться ни при каких обстоятельствах. Т.е. все время в формулу будет подставляться значение только ячейки А1.

· $A1 — абсолютным является только имя колонки, а номер строки является относительным. Это означает, что при протягивании значение ячейки будет пересчитываться ТОЛЬКО по столбцу, но не будет передвигаться по строкам.

· A$1 — абсолютным является только номер строки, а имя колонки является относительным. Это означает, что при протягивании значение ячейки будет пересчитываться ТОЛЬКО по строке, но не будет передвигаться по столбцам.

Знак "$" подставляется в ячейку нажатием сочетания клавиш SHIFT+F4. При повторном нажатии этого сочетания клавиш адресация изменяется на абсолютную по столбцу, затем на абсолютную по строке, и. наконец, на относительную.

Изменение адресов при копировании формул с различными способами фиксации адресов иллюстрируют следующие результаты копирования формул из ячейки B1 в ячейку D2:

Формула ячейкиB1	Формула ячейкиD2
=A1 + B2	=C2 + D3
=$ A$1 + B2	=$ A$1 + D3
= $ A1 + B2	=$A2 + D3
=A$1 + B2	=C$1 + D3

 

Перемещение формул и аргументов

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

Т. о. если в ячейке B1 находится формула $A$1+B2 и данные из ячейки A1 переместить в ячейку D3,

Формула ячейке B1 примет вид $D$3+B2.

Нельзя перемещать данные в ячейку, на которую ссылается формула!

Задание №5

1. В ячейку B18 введите формулу =$A$18+B$19+$C20+B20. Скопируйте ее в ячейку D20. Проанализируйте изменение адресов. Расставьте стрелки зависимости.

2. Переместите формулу из ячейки B18 в другую ячейку. Посмотрите на формулу в новом положении.

3. Переместите данные из ячейки B19 в другую ячейку. Посмотрите на формулу в B18.

4. Начиная со строки 23 разместить таблицу 10 значений функций:

Где параметр x изменяется с некоторым шагом h и существует параметр y, который не меняется, а значения фунции: Z1=2*X+3*Y; Z2=Y-5*X; Z3=X/Z1.

Значения Xo, H, Y введите сами.

Должна получиться таблица вида (примерно, значения могут не совпадать):

x	h	y	Z1	Z2	Z3
				-7	-0,28
				-17	-0,23
				-27	-0,222
				-37	-0,216
				-47	-0,213
				-57	-0,2105
				-67	-0,209

 

5. Создать таблицу разностей, начиная с ячейки А35:

	X1	X2	X3	X4
Y1	Y1-X1	Y1-X2	Y1-X3	Y1-X4
Y2	Y2-X1	Y2-X2	Y2-X3	Y2-X4
Y3	Y3-X1	Y3-X2	Y3-X3	Y3-X4
Y4	Y4-X1	Y4-X2	Y4-X3	Y4-X4

Значения X — 10; 20; 30; 40.

Значения Y — 100; 110; 120; 130.

Здесь вместо y1..y4 ставятся значения 100; 110; 120; 130, вместо x1..х4 10; 20; 30; 40 соответственно. В ячейку В36 вводится одна формула, а результат во всей таблице получается протягиванием ЭТОЙ формулы на строки и столбцы. Адресация должна быть в одном случае абсолютной по столбцу, в другом по строкам.