Тема: Электронные
таблицы.
Что нужно знать:
·
адрес ячейки в электронных таблицах состоит из
имени столбца и следующего за ним номера строки, например, C15
·
формулы в электронных таблицах начинаются знаком =
(«равно»)
·
знаки +, –, *, / и ^ в формулах означают
соответственно сложение, вычитание, умножение, деление и возведение в степень
·
запись B2:C4 означает диапазон, то есть, все ячейки
внутри прямоугольника, ограниченного ячейками B2 и C4:
·
например, по формуле =СУММ(B2:C4) вычисляется сумма
значений ячеек B2, B3, B4, C2, C3 и C4
·
в заданиях ЕГЭ могут использоваться стандартные
функции СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее
значение), МИН (минимальное значение), МАКС (максимальное значение)
·
функция СРЗНАЧ при вычислении среднего
арифметического не учитывает пустые ячейки и ячейки, заполненные текстом;
например, после ввода формулы в C2 появится значение 2 (ячейка А2 – пустая):
функция СЧЕТ(A1:B2) в этом случае выдаст значение 3 (а не 4).
·
адреса ячеек (или ссылки на ячейки) бывают
относительные, абсолютные и смешанные, вся разница между ними проявляется при
копировании формулы в другую ячейку:
o
в абсолютных адресах перед именем столбца и
перед номером строки ставится знак доллара $, такие адреса не изменяются при
копировании; вот что будет, если формулу =$B$2+$C$3 скопировать из D5 во все соседние ячейки
знак $ как бы «фиксирует» значение: в
абсолютных адресах и имя столбца, и номер строки зафиксированы
o
в относительных адресах знаков доллара нет,
такие адреса при копировании изменяются: номер столбца (строки) изменяется на
столько, на сколько отличается номер столбца (строки), где оказалась
скопированная формула, от номера столбца (строки) исходной ячейки; вот что
будет, если формулу =B2+C3 (в ней оба адреса – относительные) скопировать из D5 во все соседние
ячейки:
o
в смешанных адресах часть адреса (строка или
столбец) – абсолютная, она «зафиксирована» знаком $, а вторая часть –
относительная; относительная часть изменится при копировании так же, как и для
относительной ссылки:
Пример задания:
В ячейке B4 электронной таблицы записана
формула = $C3*2. Какой вид приобретет формула, после того как ячейку B4
скопируют в ячейку B6? Примечание: знак $ используется для обозначения
абсолютной адресации.
1) =$C5*4 2) =$C5*2 3)
=$C3*4 4) =$C3*2
Решение:
1)
ссылка $C3 – это смешанная ссылка, в которой
«заблокирован» столбец C, а строка 3 – это относительный адрес;
2)
после того, как ячейку B4 скопировали в B6, номер
строки увеличился на 2, поэтому и в ссылке $C3 номер строки (относительная
часть) также увеличится на 2, ссылка превратится в $C5
3)
константы при копировании формул не меняются,
поэтому получится =$C5*2
4)
таким образом, правильный ответ – 2.
Возможные ловушки и проблемы:
·
если ошибочно посчитать, что
знак $ защищает от изменений всю ссылку, получим неверный ответ 4
|
Ещё пример задания:
Три страны: Королевство Бельгия,
Королевство Нидерланды и Великое Герцогство Люксембург образуют
экономико-политический союз, который носит название Бенилюкс. Ниже приведен
фрагмент электронной таблицы, характеризующий каждую из стран союза и союз в
целом:
|
А
|
B
|
C
|
D
|
1
|
Страна
|
Население
(тыс. чел)
|
Площадь
(кв. км)
|
Плотность населения (чел / кв.км)
|
2
|
Бельгия
|
10 415
|
30 528
|
341
|
3
|
Нидерланды
|
16 357
|
41 526
|
394
|
4
|
Люксембург
|
502
|
2 586
|
194
|
5
|
Бенилюкс в целом
|
27 274
|
74 640
|
|
Какое значение должно стоять в ячейке D5?
1) 365 2) 929 3) 310 4)
2,74
Решение:
5)
нужно не забыть, что плотность населения
вычисляется как отношение населения к площади (не наоборот!);
6)
население не забываем перевести из тысяч человек в
единицы: 27 274 000 чел
7)
поэтому для всего Бенилюкса получаем 27 274
000 / 74 640 ≈ 365
8)
таким образом, правильный ответ – 1.
Возможные ловушки и проблемы:
·
в такой простой задаче есть
сильная ловушка: ответ 4 (2,74) получается при «обратном» делении, то есть
74 640 / 27 274 ≈ 2,74
|
Еще пример задания:
В электронной таблице значение формулы =СУММ(B1:B2) равно 5. Чему равно значение
ячейки B3, если значение формулы =СРЗНАЧ(B1:B3) равно 3?
1) 8 2) 2 3)
3 4) 4
Решение:
1)
функция СУММ(B1:B2) считает сумму значений
ячеек B1 и B2, поэтому B1 + B2 = 5
2)
функция СРЗНАЧ(B1:B3) считает среднее
арифметическое диапазона B1:B3
3)
строго говоря, такие задачи некорректны, потому что
а)
функция СРЗНАЧ учитывает только числовые данные
(числа или формулы, при вычислении которых получается число), то есть возможны
варианты:
СРЗНАЧ(B1:B3)=СУММ(B1:B3), если есть только одна числовая ячейка
СРЗНАЧ(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
7)
таким образом, правильный ответ – 4.
Возможные ловушки и проблемы:
·
чтоб сбить угадывание, среди
ответов приведены сумма исходных данных (8) и их разность (2) , это неверные
ответы
|
Еще пример задания:
Дан фрагмент электронной таблицы:
|
А
|
В
|
С
|
1
|
10
|
20
|
= A1+B$1
|
2
|
30
|
40
|
|
Чему станет равным значение ячейки С2, если в нее скопировать формулу из ячейки С1? Знак $ обозначает
абсолютную адресацию.
1) 40 2) 50 3)60 4) 70
Решение:
1)
это задача на использование абсолютных и
относительных адресов в электронных таблицах
2)
вспомним, что при копировании все относительные
адреса меняются (согласно направлению перемещения формулы), а абсолютные – нет
3)
в формуле, которая находится в C1, используются два
адреса: A1 и B$1
4)
адрес A1 – относительный, он может изменяться
полностью (и строка, и столбец)
5)
адрес B$1 – смешанный, в нем
номер строки «зафиксирован» знаком доллара, а имя столбца – нет, поэтому при
копировании может измениться только имя столбца
6)
при копировании из C1 в C2 столбец не изменяется, а
номер строки увеличивается на 1, поэтому в C2 получим формулу =A2+B$1 (здесь
учтено, что у второго адреса номер строки «зафиксирован»)
7)
сумма ячеек A2 и B1 равна 30 + 20 = 50
8)
таким образом, правильный ответ – 2.
Возможные ловушки и проблемы:
·
расчет на то, что ученик
забудет, что абсолютная ссылка не меняется (тогда получится формула =A2+B$2, на этот случай дан неверный ответ 70)
|
Еще пример задания:
Дан фрагмент электронной таблицы:
|
А
|
В
|
С
|
1
|
1
|
2
|
|
2
|
2
|
6
|
=СЧЁТ(A1:B2)
|
3
|
|
|
=СРЗНАЧ(A1:C2)
|
Как изменится значение ячейки С3, если после ввода формул переместить содержимое ячейки В2 в
В3? («+1» означает увеличение на 1, а «–1» – уменьшение на 1)
1) –2 2) –1 3) 0 4) +1
Решение:
1)
это задача на знание особенностей функций СЧЕТ и
СРЗНАЧ, которые не учитывают пустые ячейки
2)
после ввода формул в С2 окажется количество
непустых ячеек диапазона А1:В2, равное 4
3)
в С3 будет выведено среднее значение диапазона
А1:С2 равное
(1+2+2+6+4)/5 = 3
4)
после перемещения (не копирования!)
содержимого ячейки В2 в В3 ячейка В2 окажется пустой, поэтому в С2 выводится
число 3 – количество непустых ячеек диапазона А1:В2
5)
в С3 будет выведено среднее значение диапазона
А1:С2 равное
(1+2+2+3)/4 = 2,
то есть значение С3 уменьшится на 1
6)
таким образом, правильный ответ – 2.
Возможные ловушки и проблемы:
·
нужно помнить, что при
перемещении содержимого ячейки в другое место она становится пустой
·
нужно помнить, что функции
СЧЕТ и СРЗНАЧ не учитывают пустые ячейки
|
Задачи для тренировки[1]:
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
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
5)
Дан фрагмент электронной таблицы:
|
А
|
В
|
С
|
D
|
1
|
1
|
2
|
3
|
|
2
|
4
|
5
|
6
|
|
3
|
7
|
8
|
9
|
|
В ячейку D1 введена формула =$А$1*В1+С2,
а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке
D2?
1) 10 2) 14 3) 16 4) 24
6)
В ячейке В2 записана формула =$D$2+Е2. Какой
вид будет иметь формула, если ячейку В2 скопировать в ячейку А1?
1) =$D$2+E1 2) =$D$2+C2 3) =$D$2+D2
4) =$D$2+D1
7)
В ячейке СЗ электронной таблицы записана формуле =$А$1+В1.
Какой вид будет иметь формула, если ячейку СЗ скопировать в ячейку ВЗ?
1) =$A$1+А1 2) =$В$1+ВЗ 3)
=$А$1+ВЗ 4) =$B$1+C1
8)
При работе с электронной таблицей в ячейке ЕЗ
записана формула =В2+$СЗ. Какой вид приобретет формула после того, как
ячейку ЕЗ скопируют в ячейку D2?
1) =А1+$СЗ 2) =А1+$С2
3) =E2+$D2 4) =D2+$E2
9)
В ячейке электронной таблицы В4 записана формула =С2+$A$2.
Какой вид приобретет формула, если ячейку В4 скопировать в ячейку С5?
1) =D2+$В$3 2) =С5+$A$2 3)
=D3+$A$2 4) =СЗ+$А$3
10) В ячейке электронной таблицы А1 записана формула =$D1+D$2. Какой
вид приобретет формула, если ячейку А1 скопировать в ячейку ВЗ?
1) =D1+$E2 2) =D3+$F2 3)
=E2+D$2 4) =$D3+Е$2
11) Дан фрагмент электронной таблицы:
|
А
|
В
|
С
|
1
|
2
|
3
|
|
2
|
4
|
5
|
=СЧЁТ(A1:B2)
|
3
|
|
|
=СРЗНАЧ(A1:C2)
|
Как изменится значение ячейки С3, если после
ввода формул переместить содержимое ячейки В2 в В3? («+1» означает увеличение
на 1, а «–1» – уменьшение на 1):
1) –1 2) –0,6 3) 0 4) +0,6
12) В электронной таблице значение формулы =СРЗНАЧ(A6:C6) равно (-2).
Чему равно значение формулы =СУММ(A6:D6), если значение ячейки D6 равно 5?
1) 1 2) -1 3) -3 4)
7
13) В электронной таблице значение формулы =СРЗНАЧ(A6:C6) равно 0,1.
Чему равно значение формулы =СУММ(A6:D6), если значение ячейки D6 равно (–1)?
1) – 0,7 2)
-0,4 3) 0,9 4) 1,1
14) В электронной таблице значение формулы =СРЗНАЧ(B5:E5) равно 100.
Чему равно значение формулы =СУММ(B5:F5), если значение ячейки F5 равно 10?
1) 90 2) 110 3) 310 4) 410
15) В электронной таблице значение формулы =СРЗНАЧ(A6:C6) равно 2.
Чему равно значение формулы =СУММ(A6:D6), если значение ячейки D6 равно -5?
1) 1 2) -1 3) -3 4)
7
16) В электронной таблице значение формулы =СУММ(C3:E3) равно 15.
Чему равно значение формулы =СРЗНАЧ(C3:F3), если значение ячейки F3 равно 5?
1) 20 2) 10 3) 5 4)
4
17) В динамической (электронной) таблице приведены значения пробега
автомашин (в км) и общего расхода дизельного топлива (в литрах) в четырех
автохозяйствах с 12 по 15 июля.
|
12 июля
|
13 июля
|
14 июля
|
15 июля
|
За четыре дня
|
Название автохозяйства
|
Пробег
|
Расход
|
Пробег
|
Расход
|
Пробег
|
Расход
|
Пробег
|
Расход
|
Пробег
|
Расход
|
Автоколонна №11
|
9989
|
2134
|
9789
|
2056
|
9234
|
2198
|
9878
|
2031
|
38890
|
8419
|
Грузовое такси
|
490
|
101
|
987
|
215
|
487
|
112
|
978
|
203
|
2942
|
631
|
Автобаза №6
|
1076
|
147
|
2111
|
297
|
4021
|
587
|
1032
|
143
|
8240
|
1174
|
Трансавтопарк
|
998
|
151
|
2054
|
299
|
3989
|
601
|
1023
|
149
|
8064
|
1200
|
В каком из хозяйств
средний расход топлива на 100 км пути за эти четыре дня наименьший?
1) Автоколонна № 11
2) Грузовое такси
3) Автобаза №6
4) Трансавтопарк
18) В электронной таблице значение формулы =СРЗНАЧ(A1:C1) равно 5.
Чему равно значение ячейки D1, если значение формулы =СУММ(A1:D1)равно 7?
1) 2 2) -8 3)
8 4) -3
19) В электронной таблице значение формулы =СРЗНАЧ(B1:D1) равно 4. Чему
равно значение ячейки A1, если значение формулы =СУММ(A1:D1)равно 9?
1) -3 2) 5 3) 1 4) 3
20) В электронной таблице значение формулы =СРЗНАЧ(A1:B4) равно 3.
Чему равно значение ячейки A4, если значение формулы =СУММ(A1:B3)равно 30, а
значение ячейки B4 равно 5?
1) -11 2) 11 3) 4 4)
-9
21) На рисунке приведен фрагмент электронной таблицы. Определите, чему
будет равно значение, вычисленное по следующей формуле =СУММ(B1:C4)+F2*E4–A3
|
A
|
B
|
C
|
D
|
E
|
F
|
1
|
1
|
3
|
4
|
8
|
2
|
0
|
2
|
4
|
–5
|
–2
|
1
|
5
|
5
|
3
|
5
|
5
|
5
|
5
|
5
|
5
|
4
|
2
|
3
|
1
|
4
|
4
|
2
|
1) 19 2) 29 3) 31
4) 71
22) На рисунке приведен фрагмент электронной таблицы. Определите, чему
будет равно значение, вычисленное по следующей формуле =СУММ(A1:C2)*F4*E2-D3
|
A
|
B
|
C
|
D
|
E
|
F
|
1
|
1
|
3
|
4
|
8
|
2
|
0
|
2
|
4
|
–5
|
–2
|
1
|
5
|
5
|
3
|
5
|
5
|
5
|
5
|
5
|
5
|
4
|
2
|
3
|
1
|
4
|
4
|
2
|
1) –15 2) 0 3) 45 4) 55
23) В электронной таблице значение формулы =СРЗНАЧ(A4:C4) равно 5.
Чему равно значение формулы =СУММ(A4:D4), если значение ячейки D4 равно 6?
1) 1 2) 11 3) 16 4) 21
24) В электронной таблице значение формулы =СРЗНАЧ(A3:D4) равно 5.
Чему равно значение формулы =СРЗНАЧ(A3:C4), если значение формулы =СУММ(D3:D4)равно 4?
1) 1 2) 3 3)
4 4) 6
25) В электронной таблице значение формулы =СРЗНАЧ(C2:D5) равно 3.
Чему равно значение формулы =СУММ(C5:D5), если значение формулы =СРЗНАЧ(C2:D4)равно 5?
1) –6 2) –4 3) 2 4) 4
26) В динамической (электронной) таблице приведены значения посевных
площадей (в га) и урожай (в центнерах).
Зерновые культуры
|
Заря
|
Первомайское
|
Победа
|
Рассвет
|
Посевы
|
Урожай
|
Посевы
|
Урожай
|
Посевы
|
Урожай
|
Посевы
|
Урожай
|
Пшеница
|
600
|
15600
|
900
|
23400
|
300
|
7500
|
1200
|
31200
|
Рожь
|
100
|
2200
|
500
|
11000
|
50
|
1100
|
250
|
5500
|
Овёс
|
100
|
2400
|
400
|
9600
|
50
|
1200
|
200
|
4800
|
Ячмень
|
200
|
6000
|
200
|
6000
|
100
|
3100
|
350
|
10500
|
Всего
|
1000
|
26200
|
2000
|
50000
|
500
|
12900
|
2000
|
52000
|
В каком из хозяйств достигнута максимальная
урожайность зерновых (по валовому сбору, в центнерах с гектара)?
1) Заря 2) Первомайское 3)
Победа 4) Рассвет
27) Дан фрагмент электронной таблицы:
|
B
|
C
|
D
|
69
|
5
|
10
|
|
70
|
6
|
9
|
=СЧЁТ(B69:C70)
|
71
|
|
|
=СРЗНАЧ(B69:D70)
|
После перемещения содержимого ячейки C70 в
ячейку C71 значение в ячейке D71 изменится по абсолютной величине на:
1) 2,2 2) 2,0 3) 1,05 4) 0,8
28) Дан фрагмент электронной таблицы:
|
B
|
C
|
D
|
69
|
5
|
10
|
|
70
|
6
|
9
|
=СЧЁТ(B69:C70)
|
71
|
|
|
=СРЗНАЧ(B69:D70)
|
После перемещения содержимого ячейки B69 в ячейку D69 значение в ячейке D71 изменится по
сравнению с предыдущим значением на:
1) –0,2 2) 0 3) 1,03 4) –1,3
29) В динамической (электронной) таблице приведены данные о продаже путевок
турфирмой «Все на отдых» за 4 месяца. Для каждого месяца вычислено общее
количество проданных путевок и средняя цена одной путевки.
Страна
|
май
|
июнь
|
июль
|
август
|
Продано, шт.
|
Цена, тыс. руб.
|
Продано, шт.
|
Цена, тыс. руб.
|
Продано, шт.
|
Цена, тыс. руб.
|
Продано, шт.
|
Цена, тыс. руб.
|
Египет
|
12
|
24
|
15
|
25
|
10
|
22
|
10
|
25
|
Турция
|
13
|
27
|
16
|
27
|
12
|
26
|
11
|
28
|
ОАЭ
|
12
|
19
|
12
|
22
|
10
|
21
|
9
|
22
|
Хорватия
|
5
|
30
|
7
|
34
|
13
|
35
|
10
|
33
|
Продано, шт.
|
42
|
|
50
|
|
45
|
|
40
|
|
Средняя цена, тыс.руб.
|
25
|
|
27
|
|
26
|
|
27
|
|
Известно, что доход фирмы от продажи каждой
путевки не зависит от места отдыха и равен 10% от средней цены путевки в
текущем месяце. В каком месяце доход турфирмы был максимальный?
1) май
2) июнь
3) июль
4) август
30) В электронной таблице значение формулы =СРЗНАЧ(D1:D4) равно 8. Чему равно значение формулы =СРЗНАЧ(D2:D4), если
значение ячейки D1 равно 11?
1) 19 2) 21 3) 7 4) 32
31) На рисунке приведен фрагмент электронной таблицы. В ячейку B2 записали
формулу =($A2*10+B$1)^2 и скопировали ее вниз на 2 строчки, в ячейки B3
и B4. Какое число появится в ячейке B4?
|
A
|
B
|
C
|
D
|
1
|
|
0
|
1
|
1
|
2
|
1
|
|
|
|
3
|
2
|
|
|
|
4
|
3
|
|
|
|
5
|
|
|
|
|
1) 144 2) 300 3) 900 4) 90
32) На рисунке приведен фрагмент электронной таблицы. Чему будет равно
значение ячейки B4, в которую записали формулу =СУММ(A1:B2;C3)?
|
A
|
B
|
C
|
D
|
1
|
1
|
2
|
3
|
|
2
|
4
|
5
|
6
|
|
3
|
7
|
8
|
8
|
|
4
|
|
|
|
|
1) 14
2) 15 3) 17 4) 20
33) В ячейке электронной таблицы С3 записана формула =B2+$D$3-E$2. Какой вид приобретет формула, если ячейку C3
скопировать в ячейку С4?
1) =B3+$G$3-E$2 2) =B3+$D$3-E$3
3) =B3+$D$3-E$2 4) =B3+$D$3-F$2
34) На рисунке приведен фрагмент электронной таблицы. Какое число появится
в ячейке C4, если скопировать в нее формулу из ячейки D3?
|
A
|
B
|
C
|
D
|
1
|
5
|
10
|
|
|
2
|
6
|
12
|
|
|
3
|
7
|
14
|
|
=B2+$B3-$A$1
|
4
|
8
|
16
|
|
|
1) 8 2)
18 3) 21 4) 26
35) На рисунке приведен фрагмент электронной таблицы. Какое число появится
в ячейке D1, если скопировать в нее формулу из ячейки C2?
|
A
|
B
|
C
|
D
|
1
|
1
|
2
|
3
|
|
2
|
5
|
4
|
=$A2+$B$3
|
|
3
|
6
|
7
|
=A3+B3
|
|
1) 9 2)
8 3) 6 4) 5
36) На рисунке приведен фрагмент электронной таблицы. Какое число появится
в ячейке D1, если скопировать в нее формулу из ячейки C2?
|
A
|
B
|
C
|
D
|
1
|
1
|
2
|
3
|
|
2
|
5
|
4
|
=A$2+$B$3
|
|
3
|
6
|
7
|
=A3+B3
|
|
1) 11
2) 9 3) 8 4) 6
37) На рисунке приведен фрагмент электронной таблицы. Какое число появится
в ячейке D1, если скопировать в нее формулу из ячейки C2?
|
A
|
B
|
C
|
D
|
1
|
1
|
2
|
3
|
|
2
|
5
|
4
|
=$A$2+$B$3
|
|
3
|
6
|
7
|
=A3+B3
|
|
1) 12
2) 16 3) 13 4) 14
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.