Относительная и абсолютная адресация ячеек

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

 

Примеры записи различных видов адресации ячеек:

 

• Относительные адреса A1 F6 E17
• Абсолютные адреса $A$1 $F$6 $E$17
• Комбинированные адреса A$1 $F6 $E17

 

Примеры записей формул, содержащих различные виды адресаций ячеек:

 

=A1+B2 =(F6+D8)*5 + (G6-E9)

=$A$1+$B$2 =($F$6+$D$8)*5 + ($G$6-$E$9)

=A$1+$B2, =(F$6+D8)*5 + ($G$6-$E9)

 

В чем особенность различных видов адресации ячеек?

 

Если пользователь не предусматривает копирование формулы из одного места в другое, то разницы, между абсолютной и относительной адресацией ячеек, нет никакой т.е. если например, необходимо получить сумму двух чисел, находящихся по адресам А1 и В5, а результат постоянно будет в ячейке С8, то совершенно не важно, какую из формул поместить в ячейку С8

 

=A1+B5 или =$A$1+$B$5 или =$A1+$B5 ….. =A1+$B$5

 

Особенности относительной и абсолютной адресации ячеек, входящих в формулу,

проявляются при копировании этой формулы из одной ячейки в другую.

 

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

 

Примеры особенности использования относительной и абсолютной адресации при необходимости копирования формулы:

 

Относительная адресация Абсолютная адресация

A

B

C

D

E

F

A

B

C

D

E

F

5

5

7

7

12

 

 

Пример:

Дано: фамилии сотрудников, количество отработанных часов, почасовая зарплата в рублях.

Определить зарплату каждого сотрудника, за все время работы, в долларах.

 

	A	B	C	D	Е	F	G	H
			Курс $
			28,52
		№	Фамилия	Колич. часов	Руб/час	Зарплата ($)
			Абрамов			196,35
			Володин			126,23
			Романов			236,68
			Смирнов			88,36
			Харченко			227,21
			Яковенко			296,98

 

После того как таблица заполнена (кроме столбца "Зарплата $"), необходимо в ячейку F6 вписать формулу для расчета зарплаты Абрамова, (=$D6*$E6/$C$2), а затем копировать эту формулу вниз до ячейки F11. (Объяснить, что происходит с формулой).

 

Пример:

Определить стоимость драгоценного метала в евро, если известен курс евро, вес метала и стоимость одного грамма в рублях.

Вариант таблицы

	A	B	C	D	Е	F	G	H
				золото	серебро	платина
			Вес (гр)
			Гр./руб
			Стоимость?

 

Вариант решения

	A	B	C	D	Е	F	G	H
				золото	серебро	платина
			Вес (гр)
			Гр./руб
			Стоимость?	2345,16	1926,43	620,29
			Курс?
			34,5

 

Понятие диапазона. Имя диапазона.

Определение: Диапазоном называется последовательность смежных ячеек.

 

• Диапазон может быть расположен в строке, в столбце, или занимать несколько смежных строк и столбцов.
• Диапазон имеет свое начало и конец.
• Начало диапазона - адрес верхней левой ячейки,
• Конец диапазона - адрес нижней правой ячейки.

 

Примеры расположения диапазонов:

A

B

C

D

Е

F

G

H

I

J

K

L

M

N

O

P

 

$B$3:$I$3 $E$6:$E$13 $J$6:$O$10

 

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

Примечание2: Однако бывают задачи, требующие относительной или комбинированной адресации ("плавающие" диапазоны) т.е. возможны следующие адреса диапазонов:

B3:I3 $E$6:E13 J6:$O$10 и.т.д.

 

Диапазонам могут быть присвоены персональные имена. Для присвоения диапазону имени необходимо:

Выделить диапазон ►В поле адресов ячеек, вписать имя диапазона ►ENTER

 

Диапазоны применяются в функциях

Функции СУММ(), МИН(), МАКС()

Определение: Функция - готовая к использованию формула.

 

Функция состоит из своего имени, определяющим что надо делать, и параметров, перечисляемых в скобках. Параметры отделяются друг от друга символом (;). У разных функций свой набор параметров.

Имя функции (параметр 1; параметр 2; параметр 3; …; параметр N)

Функция СУММ()

Выдает сумму чисел, перечисленных как параметры этой функции.

 

Пусть необходимо сложить числа: 4, 7, 1, 5, 3, которые находятся по адресам A1, A2, A3, A4, A5, соответственно.

Можно воспользоваться обычной формулой =A1+A2+A3+A4+A5.

 

А можно применить функцию СУММ()

 

=СУММ(4; 7; 1; 5; 3) - суммировать числа

=СУММ(A1; A2; A3; A4; A5) - суммировать числа в отдельных ячейках

=СУММ(A1:A5) - суммировать числа в диапазоне ячеек

=СУММ($A$1:$A$5) - суммировать числа в диапазоне ячеек

=СУММ(таблица) - суммировать числа в диапазоне "таблица"

Вопрос:

Что произойдет с результатами, если эти функции копировать в другие ячейки?

Функция МИН (число1; число2;...)

Возвращает наименьшее значение в списке аргументов.

Число1, число2,... — от 1 до 30 чисел, среди которых требуется найти наименьшее.

· Можно задавать аргументы, которые являются числами, пустыми ячейками, или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстом, не преобразуемым в числа, вызывают значения ошибок.

· Если аргумент является массивом, то учитываются только числа. Пустые ячейки, или текст в массиве игнорируются.

· Если аргументы не содержат чисел, то функция МИН возвращает 0.

 

 

Функция МАКС (число1; число2;...)

Возвращает наибольшее значение в списке аргументов.

Число1, число2,... — от 1 до 30 чисел, среди которых требуется найти наименьшее.

· Можно задавать аргументы, которые являются числами, пустыми ячейками, или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстом, не преобразуемым в числа, вызывают значения ошибок.

· Если аргумент является массивом, то учитываются только числа. Пустые ячейки, или текст в массиве игнорируются.

· Если аргументы не содержат чисел, то функция МИН возвращает 0.

 

Пример использования функции СУММ(), МИН(), МАКС()

 

=МИН($B$3:$I$3) =МАКС($E$6:$E$13)

 

A

B

C

D

Е

F

G

H

I

J

K

L

M

N

O

P

 

=СУММ($B$3:$I$3)

=СУММ($E$6:$E$13)

=СУММ($J$6:$O$10)

 

=СУММ($B$14; $G$14; $L$14)

или

=СУММ($B$3:$I$3; $E$6:E13; $J$6:$O$10)

 

Что произойдет с результатами, если эти функции копировать в другие ячейки?

Самостоятельная

В Экзаменационной ведомости определить

• средний балл каждого студента по всем предметам обучения
• средний балл всех студентов по каждому предмету обучения
• общий средний балл группы

 

Экзаменационная ведомость

	A	B	C	D	E	F	G
	N	Ф.И.О	Предмет 1	Предмет 2	Предмет 3	Предмет 4	Ср. балл студента
		Протас					4,25
		Евгеньев					4,25
		Санников					4,00
		Синютин					4,00
		Ивановский					4,00
		Дырда					3,50
		Петров					4,25
	Ср. балл по предм.	3,86	4,71	3,43	4,14	4,04

 

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

1. Как адресуются ячейки таблицы?
2. Что такое диапазон ячеек?
3. Как выделить диапазон ячеек мышью?
4. Как адресовать фрагмент строки?
5. Как адресовать фрагмент столбца?
6. Как осуществляется ввод чисел?
7. Как отделяется дробная часть от целой?
8. Как осуществляется ввод строк символов (текста)?
9. Как осуществляется ввод формул Каким символом начинается формула?
10. Как используется функция суммирования. Ее конструкция?
11. Как редактируется содержимое ячейки?
12. Как копируются ячейки. Что происходит с адресами ячеек?
13. Как пересчитывается таблица?

Самостоятельная

Определить среднее значение чисел в соответствии с требованиями.

 

	A	B	C	D	Е
		№	Число	Ср. знач.	Требование (комментарий)
				33,00	Ср.знач. первого числа
				42,00	Ср.знач. первых двух чисел (33+51)/2
				42,00	Ср.знач. первых трех чисел (33+51+42)/3
				48,25	и.т.д.
				54,40	и.т.д.
				56,00	и.т.д.
				56,43	и.т.д.
				54,50	и.т.д.
				52,22	Ср.знач. первых девяти чисел (33+51+…+34)/9
				51,80	Ср.знач. всех чисел (33+51+…+34+48)/10

 

 

ОТВЕТ: В ячейку D3 пишется формула =СУММ($С$3:$С3)/$B3, затем копируется вниз по столбцу.