Тема: Электронные таблицы.
Что нужно знать:
· адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки, например, 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 в смешанных адресах часть адреса (строка или столбец) – абсолютная, она «зафиксирована» знаком $, а вторая часть – относительная; относительная часть изменится при копировании так же, как и для относительной ссылки:
Пример задания:
Дан фрагмент электронной таблицы.
A | B | C | D | |
=$A$2+B$3 | ||||
=A3+B3 |
Чему станет равным значение ячейки D1, если в неё скопировать формулу из ячейки С2?
Примечание: знак $ обозначает абсолютную адресацию.
1) 18 2) 12 3) 14 4) 17
Решение:
1) при копировании формулы в другую ячейку все абсолютные ссылки на строки и столбцы (перед которыми стоит знак $) сохраняются, а все относительные – изменяются в соответствии со сдвигом формулы: если, например, формулу скопировали на 3 столбца вправо и на одну строку вверх, все «незаблокированные» адреса столбцов увеличиваются на 3, а все номера строк, перед которыми нет знака $, уменьшаются на 1
2) формула в ячейке С3 (=$A$2+B$3) содержит одну абсолютную ссылку ($A$2), которая при копировании не меняется (и строка, и столбец заблокированы) и одну смешанную (B$3), в которой столбец B будет изменяться, а строка 3 – нет
3) при копировании из C2 в D1 столбец увеличивается на 1, поэтому вместо B будет C, так что окончательный вид формулы в ячейке D1 после копирования – «=$A$2+C$3»
4) вычисление этого выражения дает 5 + (6 + 7) = 18, это вариант 1
5) Ответ: 1.
Ещё пример задания:
В ячейке 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 | |
Страна | Население (тыс. чел) | Площадь (кв. км) | Плотность населения (чел / кв.км) | |
Бельгия | 10 415 | 30 528 | ||
Нидерланды | 16 357 | 41 526 | ||
Люксембург | 2 586 | |||
Бенилюкс в целом | 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), это неверные ответы |
Еще пример задания:
Дан фрагмент электронной таблицы:
А | В | С | |
= A1+B$1 | |||
Чему станет равным значение ячейки С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) |
Еще пример задания:
Дан фрагмент электронной таблицы:
А | В | С | |
=СЧЁТ(A1:B2) | |||
=СРЗНАЧ(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) Дан фрагмент электронной таблицы:
A | B | C | D | |
В ячейку 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 | |
В ячейку 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) Дан фрагмент электронной таблицы:
А | В | С | |
=СЧЁТ(A1:B2) | |||
=СРЗНАЧ(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 | ||||||||||
Грузовое такси | ||||||||||
Автобаза №6 | ||||||||||
Трансавтопарк |
В каком из хозяйств средний расход топлива на 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 | |
–5 | –2 | |||||
1) 19 2) 29 3) 31 4) 71
22) На рисунке приведен фрагмент электронной таблицы. Определите, чему будет равно значение, вычисленное по следующей формуле =СУММ(A1:C2)*F4*E2-D3
A | B | C | D | E | F | |
–5 | –2 | |||||
1) –15 2) 0 3) 45 4) 55
23) В электронной таблице значение формулы =СРЗНАЧ(A4:C4) равно 5. Чему равно значение формулы =СУММ(A4:D4), если значение ячейки D4 равно 6?
1) 1 2) 1 1 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) В динамической (электронной) таблице приведены значения посевных площадей (в га) и урожай (в центнерах).
Зерновые культуры | Заря | Первомайское | Победа | Рассвет | ||||
Посевы | Урожай | Посевы | Урожай | Посевы | Урожай | Посевы | Урожай | |
Пшеница | ||||||||
Рожь | ||||||||
Овёс | ||||||||
Ячмень | ||||||||
Всего |
В каком из хозяйств достигнута максимальная урожайность зерновых (по валовому сбору, в центнерах с гектара)?
1) Заря 2) Первомайское 3) Победа 4) Рассвет
27) Дан фрагмент электронной таблицы:
B | C | D | |
=СЧЁТ(B69:C70) | |||
=СРЗНАЧ(B69:D70) |
После перемещения содержимого ячейки C70 в ячейку C71 значение в ячейке D71 изменится по абсолютной величине на:
1) 2,2 2) 2,0 3) 1,05 4) 0,8
28) Дан фрагмент электронной таблицы:
B | C | D | |
=СЧЁТ(B69:C70) | |||
=СРЗНАЧ(B69:D70) |
После перемещения содержимого ячейки B69 в ячейку D69 значение в ячейке D71 изменится по сравнению с предыдущим значением на:
1) –0,2 2) 0 3) 1,03 4) –1,3
29) В динамической (электронной) таблице приведены данные о продаже путевок турфирмой «Все на отдых» за 4 месяца. Для каждого месяца вычислено общее количество проданных путевок и средняя цена одной путевки.
Страна | май | июнь | июль | август | ||||
Продано, шт. | Цена, тыс. руб. | Продано, шт. | Цена, тыс. руб. | Продано, шт. | Цена, тыс. руб. | Продано, шт. | Цена, тыс. руб. | |
Египет | ||||||||
Турция | ||||||||
ОАЭ | ||||||||
Хорватия | ||||||||
Продано, шт. | ||||||||
Средняя цена, тыс.руб. |
Известно, что доход фирмы от продажи каждой путевки не зависит от места отдыха и равен 10% от средней цены путевки в текущем месяце. В каком месяце доход турфирмы был максимальный?
1) май
2) июнь
3) июль
4) август
30) В электронной таблице значение формулы =СРЗНАЧ(D1:D4) равно 8. Чему равно значение формулы =СРЗНАЧ(D2:D4), если значение ячейки D1 равно 11?
1) 19 2) 2 1 3) 7 4) 32
31) На рисунке приведен фрагмент электронной таблицы. В ячейку B2 записали формулу =($A2*10+B$1)^2 и скопировали ее вниз на 2 строчки, в ячейки B3 и B4. Какое число появится в ячейке B4?
A | B | C | D | |
1) 144 2) 300 3) 900 4) 90
32) На рисунке приведен фрагмент электронной таблицы. Чему будет равно значение ячейки B4, в которую записали формулу =СУММ(A1:B2;C3)?
A | B | C | D | |
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 | |
=B2+$B3-$A$1 | ||||
1) 8 2) 18 3) 21 4) 26
35) На рисунке приведен фрагмент электронной таблицы. Какое число появится в ячейке D1, если скопировать в нее формулу из ячейки C2?
A | B | C | D | |
=$A2+$B$3 | ||||
=A3+B3 |
1) 9 2) 8 3) 6 4) 5
36) На рисунке приведен фрагмент электронной таблицы. Какое число появится в ячейке D1, если скопировать в нее формулу из ячейки C2?
A | B | C | D | |
=A$2+$B$3 | ||||
=A3+B3 |
1) 11 2) 9 3) 8 4) 6
37) На рисунке приведен фрагмент электронной таблицы. Какое число появится в ячейке D1, если скопировать в нее формулу из ячейки C2?
A | B | C | D | |
=$A$2+$B$3 | ||||
=A3+B3 |
1) 12 2) 16 3) 13 4) 14
38) В ячейки диапазона C3:F6 электронной таблицы записаны числа, как показано на рисунке.
A | B | C | D | E | F | |
В ячейке A1 записали формулу =E$5-$D4. После этого ячейку A1 скопировали в ячейку B2. Какое число будет показано в ячейке B2? Примечание: знак $ используется для обозначения абсолютной адресации.
1) 6 2) 14 3) 16 4) 24
39) В ячейки диапазона C3:F6 электронной таблицы записаны числа, как показано на рисунке.
A | B | C | D | E | F | |
В ячейке A1 записали формулу =$D4+E$5. После этого ячейку A1 скопировали в ячейку B2. Какое число будет показано в ячейке B2? Примечание: знак $ используется для обозначения абсолютной адресации.
1) 40 2) 54 3) 56 4) 70
40) В ячейки диапазона C3:F6 электронной таблицы записаны числа, как показано на рисунке.
A | B | C | D | E | F | |
В ячейке В2 записали формулу =E$5-$D4. После этого ячейку В2 скопировали в ячейку А1. Какое число будет показано в ячейке А1? Примечание: знак $ используется для обозначения абсолютной адресации.
1) 22 2) 14 3) 12 4) 4
41) В ячейки диапазона C3:F6 электронной таблицы записаны числа, как показано на рисунке.
A | B | C | D | E | F | |
В ячейке В2 записали формулу =E$5+$D4. После этого ячейку В2 скопировали в ячейку А1. Какое число будет показано в ячейке А1? Примечание: знак $ используется для обозначения абсолютной адресации.
1) 66 2) 44 3) 23 4) 13
42) В ячейке D5 электронной таблицы записана формула. Эту формулу скопировали в ячейку С4. В результате значение в ячейке С4 вычисляется по формуле Зх+у, где х — значение в ячейке С22, а у — значение в ячейке D22. Укажите, какая формула могла быть написана в ячейке D5.
1) =3*C22+D22 2) =3*$C22+$D22 3) =3*C$22+D$22 4) =3*D$22+$D23
43) В ячейке В11 электронной таблицы записана формула. Эту формулу скопировали в ячейку А10. В результате значение в ячейке А10 вычисляется по формуле х—Зу, где х — значение в ячейке С22, а у — значение в ячейке D22. Укажите, какая формула могла быть написана в ячейке В11.
1) =C22-3*D22 2) =D$22-3*$D23 3) =C$22-3*D$22 4) =$C22-3*$D22
[1] Источники заданий:
1. Демонстрационные варианты ЕГЭ 2004-2013 гг.
2. Тренировочные и диагностические работы МИОО.
3. Гусева И.Ю. ЕГЭ. Информатика: раздаточный материал тренировочных тестов. — СПб: Тригон, 2009.
4. Крылов С.С., Ушаков Д.М. ЕГЭ 2010. Информатика. Тематическая рабочая тетрадь. — М.: Экзамен, 2010.
5. Якушкин П.А., Ушаков Д.М. Самое полное издание типовых вариантов реальных заданий ЕГЭ 2010. Информатика. — М.: Астрель, 2009.
6. М.Э. Абрамян, С.С. Михалкович, Я.М. Русанова, М.И. Чердынцева. Информатика. ЕГЭ шаг за шагом. – М.: НИИ школьных технологий, 2010.
7. Чуркина Т.Е. ЕГЭ 2011. Информатика. Тематические тренировочные задания. — М.: Эксмо, 2010.
8. Якушкин П.А., Лещинер В.Р., Кириенко Д.П. ЕГЭ 2011. Информатика. Типовые тестовые задания. — М.: Экзамен, 2011.
9. Самылкина Н.Н., Островская Е.М. ЕГЭ 2011. Информатика. Тематические тренировочные задания. — М.: Эксмо, 2010.