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

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

Однако, часто возникает ситуация, когда адрес некоторой ячейки не должен изменяться при копировании. Адрес ячейки, не изменяющейся при копировании, называется абсолютным. Для обозначения абсолютного адреса используется знак $ (доллар).

Например,

$B1 – при копировании нельзя менять имя столбца В;

В$1 - при копировании нельзя менять номер первой строки;

$В$1 - при копировании нельзя менять ни имя столбца, ни номер строки.

Адрес $В$1 называется полным абсолютным, адреса $В1 и В$1 – неполными абсолютными. Рассмотрим использование абсолютных адресов на примере. Требуется создать ведомость начисления премии работникам предприятия. Премия рассчитывается в размере 20 % от оклада (следует предусмотреть возможность изменения этого коэффициента). Сведем вычисления в таблицу и рассмотрим ее в режиме показа формул. Сначала рассчитаем премию для сотрудника Алексеева И.И. Теперь скопируем эту формулу для остальных сотрудников.

 

 

 

 

 

 

Поскольку коэффициент премии всегда находится в одной и той же ячейке - С2. - следует запретить изменение этого адреса при копировании. Используем абсолютную адресацию С$2.

При копировании получаем таблицу

 

 

 

 

Результат вычислений:

 

Пример 16. Копируем формулу из ячейки D3 таблицы в D4. Какими будут формула в D4 и числа в ячейках D3 и D4?

 

 

	A	B	C	D	E
				-3
		-2		-1
				=B$1+D1

Решение

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

=B$1+D2.

2. По формуле в ячейке D3 вычисляем результат - число -3, а в ячейке D4 число -1.

Пример 17. Копируем формулу из ячейки D3 той же таблицы в Е3. Какими будут формула в Е3 и числа в ячейках D3 и Е3?

 

Решение

1. Абсолютный адрес первого слагаемого относится только к номеру строки, поэтому при сдвиге формулы влево имя столбца изменяется (а номер строки – нет, так как движемся по той же строке). Имя столбца второго слагаемого также меняется, получаем формулу:

=С$1+Е1.

2. По формуле в D3 вычисляем результат - значение -3, а в Е3 число 3.

 

Пример 18. Копируем формулу из ячейки D3 той же таблицы в Е4. Какими будут формула в Е3 и числа в ячейках D3 и Е4?

 

Решение

1. Смещение формулы происходит и по строке, и по столбцу. Поэтому у второго слагаемого образуется адрес Е2. У первого слагаемого запрещено изменять номер строки, его адрес С$1. Получаем формулу:

=С$1+Е2.

2. По формуле в ячейке D3 вычисляем результат - число -3, а в ячейке Е4 число 4.

 

Задание 5.3. Копируем формулу из ячейки В4 таблицы в С4.

	A	B	C	D
		-1		-3
				-2
			-1	-1
		=$A2+С1

Какими будут формула в С4 и числа в ячейках В4 и С4?

 

__________________________________________________________________

Задание 5.4. Копируем формулу из ячейки В4 таблицы задания 5.3. в ячейку В5.

Какими будут формула в В5 и числа в ячейках В4 и В5?

 

__________________________________________________________________

Задание 5.5. Копируем формулу из ячейки В4 в таблице задания 5.3. в ячейку С5.

Какими будут формула и число в ячейке С5?

__________________________________________________________________

 

Пример 19. Копируем формулу из ячейки D4 таблицы в Е5. Какая формула и какое число будут в Е5?

	A	B	C	D
			-5
			-1	-1
				=$С$2+В2

 

 

Решение

1. Смещение формулы происходит и по строке, и по столбцу. Поэтому у второго слагаемого образуется адрес С3. У первого слагаемого запрещено изменять и имя столбца, и номер строки, его адрес остается $С$2. Получаем формулу:

=$С$2+С3.

2. По формуле в ячейке Е5 вычисляем результат - число -6.

Пример 20. В таблице из примера 19 копируем формулу из ячейки D4 в ячейку D3. Какая формула и какое число будут в D3?

 

Решение

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

=$С$2+В1.

2. По формуле в ячейке D3 вычисляем результат - число 3.

 

 

Задание 5.6. В таблице из примера 19 копируем формулу из ячейки D4в ячейку С4.

Какая формула и какое число в ячейке С4?

 

_________________________________________________________________

Пример 21. В ячейках А1:А4 таблицы помещены значения 1, 2, 3, 4. В ячейку С1 ввели формулу =3*А1 и скопировали в ячейки С2:С4. Какие числа будут в ячейках С1:С4?

Решение

1. Создаем ЭТ, заполняем ячейки А1:А4 исходными данными.

2. Вводим в ячейку С1 формулу =3*А1.

3. Копируем формулу в ячейки С2:С4.

		А В С       =3*А1       =3*А2       =3*А3       =3*А4
	Теперь на базе этой таблицы вычислим значения: 3*А1=3*1=3 3*А2=3*2=6 3*А3=3*3=9 3*А4=3*4=12

 

 

4.

 

 

Пример 22. В ячейках А1:А4 помещены значения 4, 3, 2, 1, а в ячейку С1 введена формула =3*A$1. Эта формула скопирована в ячейки С2:С4. Какие числа в ячейках С1:С4?

.

Решение

1. Создаем ЭТ, заполняем ячейки А1:А4 исходными данными.

2. Вводим в ячейку С1 формулу =3*А$1.

3. Копируем формулу в ячейки С2:С4. Поскольку в ячейке С1 адрес

абсолютный, номер строки при копировании не меняется.

	А В С       =3*A$1       =3*A$1       =3*A$1       =3*A$1
		Значит в каждой из ячеек С1:С4 одно и то же число: 3*A$1=3*4=12.

 

 

Задание 5.7. В таблице из примера 22 в ячейку С1 введена формула =2*$А1 и скопирована в ячейки С1:С4.

Какие числа в ячейках С1:С4?

 

_________________________________________________________________

5.6. Проверка условий в электронных таблицах

Для проверки условий в Excel используется функция ЕСЛИ, которая в общем виде записывается так:

=ЕСЛИ(Условие;Выражение1;Выражение2).

Работает эта функция следующим образом: проверяется записанное условие. Если оно выполняется, вычисляется Выражение1, значение которого записывается в ячейку вместо функции ЕСЛИ. Если условие не выполняется, происходит вычисление Выражения2. В программе OpenOffice.org Calc эта функция называется IF и работает аналогично.

Пример 23. Вычислить

Здесь х – значение ячейки А1. Результат вычислений поместить в ячейку В2.

. Решение

Поскольку вычисления требуют проверки условий, очевидно, что в В2 нужно ввести функцию ЕСЛИ. Будем проверять условие х £0 (то есть А1<=0). Если это условие выполняется, нужно вычислить у =7 х (значит, Выражение1 примет вид =7*А1) Если же условие не выполняется, очевидно, что будет выполняться условие x >0 (то есть А1>0). В таком случае следует вычислить у = х +5 (следовательно, Выражением2 будет =А1+5). Вводим в В2 формулу:

=ЕСЛИ(А1<=0;7*А1;А1+5).

В программе OpenOffice.org Calc формула запишется в таком виде:

=IF(А1<=0;7*А1;А1+5).

Обратите внимание! Ввод функций для проверки условий существенно облегчается при использовании мастера функций. Выполнив команды Вставка – Функция – Логические – ЕСЛИ, пользователь получает возможность ввести все аргументы функции в окно Мастера.

Пример 24. Вычислить

Здесь х – значение ячейки D2. Результат вычислений поместить в ячейку С3.

. Решение

Вводим в ячейку С3 формулу: =ЕСЛИ(D2>=4;D2*D2;COS(D2)).

В программе Calc формула примет вид: =IF(D2>=4;D2*D2;COS(D2)).

 

Пример 25. В ячейку F7 введена формула =ЕСЛИ(В5>10;100;-20). Какое значение будет вычислено в F7, если в ячейку В5 ввести число 3?

Решение

Поскольку проверяемое условие В5>10 не выполняется, в ячейку F7 будет помещено Выражение2, то есть число -20.

 

Задание 5.8. В ячейку G9 введена формула

=ЕСЛИ(C4<6;2*C4;50).

Какое значение будет вычислено в G9, если в C4 ввести число 5?

_________________________________________________________________

 

В качестве аргументов функции ЕСЛИ могут использоваться другие функции. Это дает возможность организовать проверку не двух, а большего числа условий.

 

Пример 26. Вычислить .

 

Решение

Значение х находится в ячейке С8. Результат поместить в ячейку D3.

Очевидно, что при решении данной задачи придется проверить не одно, а два условия. Например, проверим выполнение условия х >10. В случае его выполнения путь решения очевиден – следует вычислить z = e ^x. Если же условие х >10 не выполняется (что равнозначно выполнению условия х £10), возникает неопределенность: возможно, х £5, а может быть, значение х находится в интервале (5;10]. Чтобы разрешить эту неопределенность, придется проверить еще одно условие, то есть использовать в качестве Выражения2 новую функцию если.

Следовательно, в ячейку D3 будем вводить такую функцию:

 

=ЕСЛИ(С8>10;EXP(C8);если(c8>5;C8^4;9*C8)).

В программе Calc формула примет вид:

=IF(С8>10;EXP(C8);IF(c8>5;C8^4;9*C8)).

 

Замечание. Знак равенства ставится только один раз - перед основной функцией ЕСЛИ.

 

Пример 27. Вычислить .

Здесь y – значение ячейки А4. Вычисления поместить в ячейку В4.

 

Решение

Вводим в В4 функцию:

=ЕСЛИ(A4<=-15;SIN(A4);ЕСЛИ(A4<=0;COS(A4);LOG(A4))).

 

 

Пример 28. В ячейку Е6 введена формула =ЕСЛИ(D7<2;COS(D7);ЕСЛИ(D7<8;EXP(D7);3*D7)).

Какое значение будет вычислено в Е6, если в ячейку D7 ввести число 15?

Решение

Поскольку первое проверяемое условие D7<2 не выполняется, происходит переход к проверке второго условия. Условие D7<8 также не выполнено, следовательно, вычисляется выражение 3*D7, равное 3*15=45.

Пример 29. Какую формулу следует поместить в ячейку А1, чтобы вычислить в ячейке В5 значение переменной у по формуле

Варианты ответов:

А. =ЕСЛИ(А1>0;A1*A1;-2*A1).

B. =ЕСЛИ(А1>0;A1*A1;ЕСЛИ(А1<=0;-2*A1)).

C. =ЕСЛИ(А1<=0;A1*A1;=ЕСЛИ(А1<=0;-2*A1;0)).

D. =ЕСЛИ(А1>0;B1*B1;ЕСЛИ(А1<=0;-2*B1)).

 

Решение

Верный ответ – А. В вариантах B и D у функции ЕСЛИ всего два аргумента вместо трех. В варианте С перед второй функцией ЕСЛИ стоит знак равенства.

Пример 30. Какую формулу следует поместить в ячейку А3, чтобы вычислить в ячейке С9 значение переменной у по формуле

 

 

Варианты ответов:

 

А. =ЕСЛИ(А3<=-5;A3^3;ЕСЛИ(А3<=3;3*A3;ЕСЛИ(A3>3;A3/2))).

B. = ЕСЛИ(А3<=-5;A3^3;=ЕСЛИ(А3<=3;3*A3;A3/2)).

C. = ЕСЛИ(А3<=-5;A3^3;ЕСЛИ(А3<=3;3*A3;A3/2)).

D. = ЕСЛИ(А3<=-5;A3^3;=ЕСЛИ(А3<=3;3*A3;=ЕСЛИ(A3>3;A3/2))).

 

Решение

Верный ответ – С. В вариантах B и D перед вложенными функциями ЕСЛИ стоит знак равенства. В варианте А присутствует лишняя функция ЕСЛИ, у которой всего два аргумента вместо трех.

 

Задание 5.9. Написать формулу для вычисления

 

 

если х находится в ячейке В3.

 

_________________________________________________________________

 

 

Задание 5.10. Написать формулу для вычисления

 

 

если х находится в ячейке F2.

 

_________________________________________________________________

Задание 5.11. В ячейку F5 введена формула

=ЕСЛИ(В11>=10;SIN(B11);ЕСЛИ(B11<3;В11/2;5*В11).

Какое значение будет вычислено в F5, если в В11 ввести число 8?

 

_____________________________________________________________

 

 

Задание 5.12. Какую формулу следует поместить в ячейку С10, чтобы вычислить значение переменной у по формуле

, где х – значение в ячейке D4.

 

Варианты ответов:

А. =ЕСЛИ(D4>=10;8 D4;sin(D4)).

В. =ЕСЛИ(Х>=10;8*X;sin(x)).

С. =ЕСЛИ(D4>=10;8* D4;sin(D4).

D. =ЕСЛИ(D4>=10;8*D4;sin(D4)).

 

_________________________________________________________________

 

 

Задание 5.13. Какую формулу следует поместить в ячейку F10,

чтобы вычислить значение переменной a по формуле:

 

 

 

где b – значение в ячейке C2.

 

Варианты ответов:

 

A. =ЕСЛИ(C2<-10;ABS(C2);ЕСЛИ(C2<1;15+C2;2*C2))

B. =ЕСЛИ(C2<-10;ABS(C2);ЕСЛИ(C2<1; 2*C2;15 +C2))

C. =ЕСЛИ(C2<-10;ABS (B);=ЕСЛИ(C2<1;15+C2;2*C2))

D. =ЕСЛИ(C2<-10;ABS (B);=ЕСЛИ(C2<1;ЕСЛИ(С2>=1;5+C2)))

 

__________________________________________________________________________

 

КОНТРОЛЬНЫЙ ЛИСТ № 3

Тема 5. Электронные таблицы

Фамилия И.О.___________________________ Шифр____________________

 

1. Запишите формулу для проведения вычислений в Excel согласно индивидуальному заданию.

 

 

Ответ:___________________________________________________________

 

2. Формула из одной ячейки копируется в другую. Как запишется формула в новой ячейке?

 

№ варианта (последняя цифра шифра)	Ячейка, в которой находится формула	Формула	Ячейка, куда копируется формула
1, 6	F2	=$D2 + C1	F3
2, 7	A5	=B$1 – D1	A6
3, 8	C3	=$A4 + B1	C4
4, 9	D1	= B$2 – A4	D2
5, 0	B4	=$D3 + C5	C4

 

 

Ответ:___________________________________________________________

3. Формула из одной ячейки копируется в другую. Как запишется формула в новой ячейке?

 

№ варианта (последняя цифра шифра)	Ячейка, в которой находится формула	Формула	Ячейка, куда копируется формула
1, 6	F2	=$D$2 + C1	F3
2, 7	A5	=$B$1 – D1	A6
3, 8	C3	=$A$4 + B1	C4
4, 9	D1	= $B$2 – A4	D2
5, 0	B4	=$D$3 + C5	C4

 

 

Ответ:___________________________________________________________

 

 

4. Написать формулу для вычисления

 

Номер варианта (последняя цифра шифра)	Ячейка, где находится Х	Формула
1, 5, 7	В3
2, 4, 8, 10	С2
3, 6, 9	D5

Ответ:___________________________________________________________

 

СОДЕРЖАНИЕ

1. ИНФОРМАЦИЯ И ИНФОРМАТИКА …………………………………...3

1.1. Понятие об информации и информатике………………………......3

1.2. Кодирование информации………………………………………......3

1.2.1. Кодирование целых чисел……………………………….....3

1.2.2. Двоичное кодирование действительных чисел.

Нормализованная форма чисел………………………….....5

1.2.4. Кодирование текстовой информации………………….…..7

1.2.5. Кодирование графической информации………………......7

1.3. Файлы и файловая структура………………………………………..7

1.3.1. Единицы представления данных…………………………...7

1.3.2. Единицы измерения данных………………………………..8

1.3.3. Единицы хранения данных. Понятие

о файловой структуре……………………………………….9

КОНТРОЛЬНЫЙ ЛИСТ № 1 ………………………………………………….11

2. ВЫЧИСЛИТЕЛЬНАЯ ТЕХНИКА ………………………………………..13

2.1. Понятие базовой конфигурации

персонального компьютера (ПК)……………………………………13

2.2. Внутренние устройства системного блока.………………………...13

2.3. Внешние устройства компьютера…………………………………...14

3. ПРОГРАММНОЕ ОБЕСПЕЧЕНИЕ КОМПЬЮТЕРОВ ……………….15

3.1. Программная конфигурация…………………………………………15

3.1.1. Системные программы……………………………………...15

3.1.2. Прикладные программы…………………………………….16

3.2. Языки программирования – основные понятия……………………17

3.2.1. Уровни языка программирования………………………….17

3.2.2. Компиляторы и интерпретаторы…………………………...17

3.3. Вирусы и антивирусные программы………………………………..18

3.4. Архиваторы…………………………………………………………...18

4. СЕТЕВЫЕ ТЕХНОЛОГИИ ОБРАБОТКИ ИНФОРМАЦИИ …………19

КОНТРОЛЬНЫЙ ЛИСТ № 2 ………………………………………………….21

5. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ ……………………………………………..25

5.1. Основные понятия……………………………………………………25

5.2. Типы данных в ЭТ…………………………………………………...26

5.3. Режимы работы электронной таблицы……………………………..27

5.4. Копирование формул в ЭТ………………………………………….28

5.5. Абсолютные и относительные адреса ячеек…..………………….28

5.6. Проверка условий в электронных таблицах……………………….34

КОНТРОЛЬНЫЙ ЛИСТ № 3 …………………………………………………39

 

Боброва Л.В.

Информатика

УЧЕБНО-МЕТОДИЧЕСКИЙ КОМПЛЕКС Рабочая тетрадь № 1

Институты: все

Редактор А. М. Никитина

Сводный темплан 2008 г.

Лицензия ЛР №020308 от 14.02.97

Санитарно-эпидемиологическое заключение №78.01.07.953.П.005641.11.03

от 21.11.2003 г.

Подписано в печать Формат 60х84 1/16

Б. кн.-журн. П.л. Б.л. Изд-во СЗТУ

Тираж Заказ

 

Северо-Западный государственный заочный технический университет

Издательство СЗТУ, член Издательско-полиграфической ассоциации университетов России

191186, Санкт-Петербург, ул. Миллионная, д.5