Адресация в электронных таблицах

Адресация в электронных таблицах

МБОУ «Гимназия №75» г.Казань

© Домрачева И.Н. 2012

Основные понятия

• адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки, например, C15;
• формулы в электронных таблицах начинаются знаком = («равно»);
• знаки +, –, *, / и ^ в формулах означают соответственно сложение, вычитание, умножение, деление и возведение в степень;
• запись B2:C4 означает диапазон, то есть, все ячейки внутри прямоугольника, ограниченного ячейками B2 и C4;

Функции MS Excel

• например, по формуле =СУММ(B2:C4) вычисляется сумма значений ячеек B2, B3, B4, C2, C3 и C4;
• стандартные функции СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение);
• функция СРЗНАЧ при вычислении среднего арифметического не учитывает пустые ячейки и ячейки, заполненные текстом;

Типы ссылок

• адреса ячеек (или ссылки на ячейки) бывают относительные, абсолютные и смешанные , вся разница между ними проявляется при копировании формулы в другую ячейку:
• в абсолютных адресах перед именем столбца и перед номером строки ставится знак доллара $, такие адреса не изменяются при копировании; знак $ как бы «фиксирует» значение: в абсолютных адресах и имя столбца, и номер строки зафиксированы;
• в относительных адресах знаков доллара нет, такие адреса при копировании изменяются: номер столбца (строки) изменяется на столько, на сколько отличается номер столбца (строки), где оказалась скопированная формула, от номера столбца (строки) исходной ячейки;
• в смешанных адресах часть адреса (строка или столбец) – абсолютная, она «зафиксирована» знаком $, а вторая часть – относительная; относительная часть изменится при копировании так же, как и для относительной ссылки:

Задача 1

• В ячейке B4 электронной таблицы записана формула = $C3*2. Какой вид приобретет формула, после того как ячейку B4 скопируют в ячейку B6? Примечание: знак $ используется для обозначения абсолютной адресации.

1) =$C5*4 2) =$C5*2 3) =$C3*4 4) =$C3*2

Решение:

• ссылка $C3 – это смешанная ссылка, в которой «заблокирован» столбец C, а строка 3 – это относительный адрес;
• после того, как ячейку B4 скопировали в B6, номер строки увеличился на 2, поэтому и в ссылке $C3 номер строки (относительная часть) также увеличится на 2, ссылка превратится в $C5;
• константы при копировании формул не меняются, поэтому получится =$C5*2;
• таким образом, правильный ответ – 2.

Задача 2

• Три страны: Королевство Бельгия, Королевство Нидерланды и Великое Герцогство Люксембург образуют экономико-политический союз, который носит название Бенилюкс. Ниже приведен фрагмент электронной таблицы, характеризующий каждую из стран союза и союз в целом:

1

А

2

B

Страна

3

Бельгия

C

Население (тыс. чел)

Нидерланды

Площадь (кв. км)

10 415

4

D

30 528

Плотность населения (чел / кв.км)

16 357

5

Люксембург

341

41 526

502

Бенилюкс в целом

394

2 586

27 274

194

74 640

Какое значение должно стоять в ячейке D5?

1) 365 2) 929 3) 310 4) 2,74

Задача 2. Решение

• нужно не забыть, что плотность населения вычисляется как отношение населения к площади (не наоборот!);
• население не забываем перевести из тысяч человек в единицы: 27 274 000 чел
• поэтому для всего Бенилюкса получаем 27 274 000 / 74 640 ≈ 365
• таким образом, правильный ответ – 1.

В такой простой задаче есть сильная ловушка: ответ 4 (2,74) получается при «обратном» делении, то есть 74 640 / 27 274 ≈ 2,74

Задача 3

• В электронной таблице значение формулы =СУММ(B1:B2) равно 5. Чему равно значение ячейки B3, если значение формулы =СРЗНАЧ(B1:B3) равно 3?

1) 8 2) 2 3) 3 4) 4

Решение:

• функция СУММ(B1:B2) считает сумму значений ячеек B1 и B2, поэтому B1 + B2 = 5;
• функция СРЗНАЧ(B1:B3) считает среднее арифметическое диапазона B1:B3;
• строго говоря, такие задачи некорректны, потому что

А) функция СРЗНАЧ учитывает только числовые данные (числа или формулы, при вычислении которых получается число), то есть возможны варианты:

СРЗНАЧ(B1:B3)=СУММ(B1:B3) , если есть только одна числовая ячейка;

Задача 3. Продолжение

СРЗНАЧ(B1:B3)=СУММ(B1:B3)/2 , если есть две числовых ячейки

СРЗНАЧ(B1:B3)=СУММ(B1:B3)/3 , если все три ячейки – числовые

Б) в условии не задано, сколько числовых ячеек в диапазоне B1:B3

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

5. Итак, в диапазон B1:B3 входят три ячейки; предполагаем, что все они содержат числовые данные, тогда среднее арифметическое – это сумма их значений, деленная на 3; таким образом B1 + B2 + B3 = 3 · 3 = 9.

6. Поскольку B1 + B2 = 5, сразу получаем B3 = 9 – 5 = 4.

Таким образом, правильный ответ – 4.

Задача 4

Дан фрагмент электронной таблицы.

Чему станет равным значение ячейки С2 , если в нее скопировать формулу из ячейки С1 ? Знак $ обозначает абсолютную адресацию.

1) 40 2) 50 3) 60 4) 70

А

1

В

2

10

С

20

30

= A1+B$1

40

Внимание ! При копировании все относительные адреса меняются (согласно направлению перемещения формулы), а абсолютные – нет.

Задача 4. Решение

А

1

В

10

2

С

20

30

= A1+B$1

40

• в формуле, которая находится в C1, используются два адреса: A1 и B$1
• адрес A1 – относительный, он может изменяться полностью (и строка, и столбец)
• адрес B$1 – смешанный, в нем номер строки «зафиксирован» знаком доллара, а имя столбца – нет, поэтому при копировании может измениться только имя столбца
• при копировании из C1 в C2 столбец не изменяется, а номер строки увеличивается на 1, поэтому в C2 получим формулу =A2+B$1 (здесь учтено, что у второго адреса номер строки «зафиксирован»)
• сумма ячеек A2 и B1 равна 30 + 20 = 50
• таким образом, правильный ответ – 2.

Задача 5

1

А

В

2

1

3

2

2

С

6

=СЧЁТ(A1:B2)

=СРЗНАЧ(A1:C2)

Дан фрагмент ЭТ. Как изменится значение ячейки С3 , если после ввода формул переместить содержимое ячейки В2 в В3? («+1» означает увеличение на 1, а «–1» – уменьшение на 1)

1) –2 2) –1 3) 0 4) +1

Внимание!

1) При перемещении содержимого ячейки в другое место она становится пустой.

2) Функции СЧЕТ и СРЗНАЧ не учитывают пустые ячейки .

Задача 5. Решение

А

1

1

В

2

С

2

2

3

6

=СЧЁТ(A1:B2)

=СРЗНАЧ(A1:C2)

• после ввода формул в С2 окажется количество непустых ячеек диапазона А1:В2, равное 4;
• в С3 будет выведено среднее значение диапазона А1:С2 равное (1+2+2+6+4)/5 = 3;
• после перемещения (не копирования!) содержимого ячейки В2 в В3 ячейка В2 окажется пустой, поэтому в С2 выводится число 3 – количество непустых ячеек диапазона А1:В2
• в С3 будет выведено среднее значение диапазона А1:С2 равное (1+2+2+3)/4 = 2, то есть значение С3 уменьшится на 1
• таким образом, правильный ответ – 2.

Задачи для тренировки

1. В ячейке B1 записана формула =2*$A1 . Какой вид приобретет формула, после того как ячейку B1 скопируют в ячейку C2?

1) =2*$B1 2) =2*$A2 3) =3*$A2 4) =3*$B2Н

2. В ячейке C2 записана формула =$E$3+D2 . Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?

1) =$E$3+C1 2) =$D$3+D2 3) =$E$3+E3 4) =$F$4+D2

Задачи для тренировки

A

1

2

5

B

C

2

10

D

4

1

6

3. Дан фрагмент ЭТ. В ячейку D2 введена формула =А2*В1+С1 . В результате в ячейке D2 появится значение:

1) 6 2) 14 3) 16 4) 24

4. В ячейке А1 электронной таблицы записана формула =D1-$D2 . Какой вид приобретет формула после того, как ячейку А1 скопируют в ячейку В1?

1) =E1-$E2 2) =E1-$D2 3) =E2-$D2 4) =D1-$E2

Задачи для тренировки

А

1

В

2

2

С

3

4

3

5

=СЧЁТ(A1:B2)

=СРЗНАЧ(A1:C2)

5. Дан фрагмент ЭТ. Как изменится значение ячейки С3, если после ввода формул переместить содержимое ячейки В2 в В3? («+1» означает увеличение на 1, а «–1» – уменьшение на 1):

1) –1 2) –0,6 3) 0 4) +0,6

Задачи для тренировки

12 июля

Название автохозяйства

Пробег

Автоколонна №11

Грузовое такси

9989

13 июля

Расход

490

2134

Автобаза №6

Пробег

101

9789

Расход

1076

Трансавтопарк

14 июля

147

2056

998

987

Пробег

2111

Расход

151

9234

15 июля

215

Пробег

297

2054

487

2198

112

За четыре дня

299

4021

9878

Расход

3989

2031

587

978

Пробег

601

1032

38890

203

Расход

1023

143

2942

8419

149

8240

631

8064

1174

1200

6. В динамической (электронной) таблице приведены значения пробега автомашин (в км) и общего расхода дизельного топлива (в литрах) в четырех автохозяйствах с 12 по 15 июля.

В каком из хозяйств средний расход топлива на 100 км пути за эти четыре дня наименьший?

1) Автоколонна № 11

2) Грузовое такси

3) Автобаза №6

4) Трансавтопарк

Задачи для тренировки

7. В электронной таблице значение формулы =СРЗНАЧ(B5:E5) равно 100. Чему равно значение формулы =СУММ(B5:F5) , если значение ячейки F5 равно 10?

1) 90 2) 110 3) 310 4) 410

8. В электронной таблице значение формулы =СРЗНАЧ(A1:B4) равно 3. Чему равно значение ячейки A4, если значение формулы =СУММ(A1:B3) равно 30, а значение ячейки B4 равно 5?

1) -11 2) 11 3) 4 4) -9

9. В электронной таблице значение формулы =СРЗНАЧ(A4:C4) равно 5. Чему равно значение формулы =СУММ(A4:D4) , если значение ячейки D4 равно 6?

1) 1 2) 1 1 3) 16 4) 21

Задачи для тренировки

А

1

2

В

2

3

3

7

С

6

5

4

D

13

10. В ЭТ введены данные, причем в ячейках А1:В2 данные являются первичными (основными), а в ячейках С1:С2 – производными.

Какое число появится в ячейке С2, если выделить ячейку А1 и маркер заполнения «протянуть» вниз?

Задачи для тренировки

1

А

5

2

В

6

10

С

3

D

7

9

4

12

14

8

16

11. В ЭТ в блок А1:А3 введены последовательно числа – 5, 6, 7, 8, а в блок В1:В3 – 10, 12, 14, 16. Значение ячейки С вычисляется по формуле: =А1+В1-А2.

Какое число появится в ячейке С2, если выделить ячейку С1 и маркер заполнения «протянуть» вниз»?

Задачи для тренировки

Зерновые культуры

Заря

Посевы

Пшеница

Первомайское

600

Рожь

Урожай

100

Посевы

Овёс

15600

Победа

900

100

Урожай

2200

Ячмень

2400

23400

200

Всего

500

Посевы

Рассвет

400

300

Урожай

11000

6000

1000

200

7500

Посевы

9600

50

26200

50

Урожай

6000

2000

1200

1100

100

50000

1200

31200

250

200

3100

5500

500

4800

12900

350

2000

10500

52000

12. В динамической (электронной) таблице приведены значения посевных площадей (в га) и урожай (в центнерах).

В каком из хозяйств достигнута максимальная урожайность зерновых (по валовому сбору, в центнерах с гектара)?

1) Заря 2) Первомайское 3) Победа 4) Рассвет