В ячейке в2. Введение формул в ячейки Excel для расчетов данных

Наша задача - выбрать и ввести в ячейку В2 оптимальную формулу, которая задает умножение первых сомножителей. На первый взгляд кажется, что таковой является следующая формула:

А2*В1

Операцию ввода формулы нужно проделать 100 раз, чтобы заполнить все 100 ячеек области вычислений. Самым простым методом снижения трудоемкости данной операции является копирование. Результаты копирования содержимого ячейки В2 в область B2:D4 показаны на рис. 3.2.

Рис. 3.2.

Проанализировав этот рисунок, можно сделать следующий вывод: для остальных 99 ячеек метод копирования формулы умножения из ячейки В2 не подходит, поскольку ссылки на ячейки в формуле изменились таким образом, что вычисления производятся неправильно. Как выйти из этого положения, описано ниже.

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

Ссылка на ячейку может быть относительной, абсолютной и смешанной. До сих пор мы пользовались относительными ссылками. Фактически они задают смещение ячейки, на которую производится ссылка, относительно ячейки, в которой эта ссылка указывается. По этой причине при копировании адрес ячейки, на которую производится ссылка, изменяется таким образом, чтобы смещение осталось прежним. Это очень полезное свойство, и именно благодаря ему мы имеем возможность при копировании формул избежать ручной работы. Чтобы вы лучше поняли, как работают относительные ссылки в электронных таблицах, приведем пример. Предположим, что в ячейку В2 введена такая формула:

Она указывает на ячейку, находящуюся на одну ячейку выше и левее ячейки В2 (то есть на ячейку А1). После копирования содержимого ячейки В2 в ячейку С 4 формула в ячейке С4 уже будет указывать не на ячейку А1, а на ячейку ВЗ (то есть на ячейку, расположенную на одну ячейку выше и левее С4). Таким образом, при копировании содержимого ячейки в любое место рабочего листа расположенная в ней формула будет ссылаться не на конкретную ячейку, а на ячейку, находящуюся на некотором расстоянии от ячейки с формулой. Пунктирные линии на рис. 3.3 указывают, куда переадресуется ссылка при копировании формулы из ячейки В2 в ячейки С4 и D6.

Рис. 3.3. Изменение ссылки при копировании формулы с относительной ссылкой на ячейку А1 из ячейки B2 в ячейки С4 и D6

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

ПРИМЕЧАНИЕ.

На рис. 3.3 в нижней части окна Excel находится панель Рисование, с помощью которой были нанесены стрелки.

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

Это продемонстрировано на рис. 3.4, где формула с абсолютной ссылкой на ячейку А1 копируется из ячейки В2 в ячейки С4 и D6.

Рис. 3.4. При копировании формулы с абсолютной ссылкой ссылка не изменяется

Широкие возможности предоставляют смешанные ссылки. Это ссылки с одним знаком доллара - перед именем столбца или перед номером строки. Например, если в ячейке В2 имеется формула:

то после копирования ее в любое место рабочего листа изменится лишь название столбца, а строка 1 будет присутствовать в формуле всегда. Таким образом, в данном случае мы имеем абсолютную ссылку на строку и относительную - на столбец.

Аналогично, если скопировать в другое место ячейку В2 с формулой:

то изменится только номер строки, а имя столбца останется прежним. Следовательно, здесь речь идет об абсолютной ссылке на столбец и относительной - на строку.

Приведенные ниже рисунки иллюстрируют применение в ячейке В2 абсолютной ссылки на строку (рис. 3.6) и абсолютной ссылки на столбец (рис. 3.5). При копировании данной формулы в ячейки С4 и D6 получаются разные формулы.

Рис. 3.5. Результат копирования в ячейки С4 и D6 формулы из ячейки В2, которая включает относительную ссылку на строку и абсолютную ссылку на столбец ячейки А1

Рис. 3.6. Результат копировании в ячейки С4 и D6 формулы из ячейки В2, которая включает абсолютную ссылку на строку и относительную ссылку на столбец ячейки А1

Изменение типа ссылки производится циклически, в результате последовательных нажатий функциональной клавиши в то время, когда курсор ввода находится в тексте ссылки. Если, например, в ячейке В2 имеется ссылка на ячейку А1. то при каждом нажатии клавиши ее вид в строке формул будет изменяться: А1 - $A$1 - A$1 - $А1 - А1 - $A$1 и т. д.

Ввод формулы со смешанной ссылкой в область вычислений

Для того чтобы быстро заполнить формулами область вычислений, в ячейку В2 необходимо ввести формулу со смешанной ссылкой:

=$А2*В$1

Эту формулу можно смело копировать в другие ячейки - вычисления в таблице будут производиться правильно. При копировании в первом компоненте формулы изменяется только номер строки (=$A2, $АЗ, $А4 и т. д.), а ссылка на столбец, в котором находятся элементы сомножителя 1, остается постоянной. Во втором компоненте, наоборот, изменяется имя столбца (=С$1, D$1, Е$1 и т. д.), а ссылка на строку, где находятся элементы сомножителя 2, остается постоянной. Для ввода формулы умножения в область вычислений таблицы воспользуемся методом заполнения ячеек одинаковыми данными. Оптимальной в этом случае является такая последовательность действий:

Выделите диапазон ячеек В2:К11.
Введите с клавиатуры знак "=" и нажмите клавишу , чтобы создать ссылку на ячейку А2.
Три раза нажмите функциональную клавишу (создание абсолютной ссылки на столбец А).
Введите знак "*" (умножить) и нажмите клавишу для создания ссылки на ячейку В1.
Два раза нажмите функциональную клавишу (создание абсолютной ссылки на строку 1).
Нажмите комбинацию клавиш .

Таблица умножения с формулами и результатами расчетов представлена на рис. 3.7 и 3.8.

Выполните задания:

Активизируйте ячейку В2 и введите текст «Первая таблица». Обратите внимание! Если вводимый текст не помещается по ширине столбца, он будет накладываться на соседние ячейки, если они пусты.

Активизируйте ячейку F 2 и введите текущую дату в виде 27.11.02 .

Активизируйте ячейку G 2 и введите текущее время в виде 10:32 .

Активизируйте ячейку С3 введите число 25 . Рекомендация : числа и десятичную точку набирайте на цифровой клавиатуре (индикатор Num Lock должен быть включен).

Активизируйте ячейку С4 введите число 13,6 .

Задание 4. Ввод формул

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

1. Ввод формулы начинается со знака равенства = .

2. В формулу могут входить числа, адреса ячеек и стандартные функции , соединенные знаками арифметических операций:

% процент,

^ операция возведения в степень (символ ^ набирается в латинском регистре при нажатии клавиш Shift+6 на основной клавиатуре),

умножение,

/ деление,

+ сложение,

 вычитание.

При записи выражения могут использоваться круглые скобки . Например, = 2^3-(3 4+10).

3. Адреса ячеек заносятся в формулу или набором с клавиатуры в латинском регистре или щелчком мыши на нужной ячейке (второй вариант удобнее).

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

Выполните задания:

Задание 4. Редактирование данных в ячейке

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

Для полной замены данных достаточно активизировать ячейку и ввести новые данные, при этом старые данные автоматически удаляются.

Для частичной замены данных существуют два способа:

1-ый способ : сделать двойной щелчок на ячейке (появится текстовый курсор) и отредактировать данные.

2-ый способ: активизировать ячейку, сделать щелчок в строке формул (появится текстовый курсор) и отредактировать данные.

Выполните задания:

В ячейке В2 измените текст на «Таблица № 1 » и нажмите клавишу Enter .

В ячейке Е5 частично измените формулу: = 25,5-С3 и нажмите клавишу Enter .

Задание 5. Ввод примечаний

П римечание - это поясняющий текст к ячейке .

Выполните задания:

Добавьте к ячейке В2 примечание (свою фамилию и имя), для этого:

активизируйте ячейку В2;

выберите пункт меню Вставка ® Примечание (в результате около ячейки появится рамка с текстовым курсором внутри);

введите нужный текст;

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

Для просмотра примечания подведите указатель мыши к ячейке (в результате появится окошко с текстом примечания).

Отредактируйте примечание, для этого:

активизируйте ячейку В2;
выберите пункт меню Вставка ® Изменить примечание;
измените текст примечания (добавьте номер группы);
сделайте щелчок вне рамки.

Дан фрагмент электронной таблицы. Из ячейки A2 в ячейку B3 была скопирована формула. При копировании адреса ячеек в формуле автоматически изменились. Запишите в ответе числовое значение формулы
в ячейке B3.

Демонстрационный вариант Единый государственный экзамен ЕГЭ 2017 г. – задание №7

Решение:

Если знак “$” стоит перед цифрой или буквой координатов клетки, то эта цифра или буква при перемещении не меняются. При его отсутствии буква и/или цифра изменяются в соответствии с тем, куда было скопировано значение. Как мы видим, знак “$” стоит перед “2” и перед “3”, следовательно эти цифры не изменятся. Однако перед буквами знака “$” нет, следовательно буква “C” изменится на “D”, а буква “D” изменится на “E”, так как, если смотреть на изменения по горизонтали(по буквам), то мы переместили значение клетки А2 в клетку B3, т.е. сдвинули на один столбик – теперь клетка B3 принимает следующий вид: “=D2+E3”. Складываем значения этих клеток:

B3 = D2 + E3 = 70 + 5 = 75

Ответ: 75

Демонстрационный вариант Единый государственный экзамен ЕГЭ 2016 г. – задание №7 а

Дан фрагмент электронной таблицы. Из ячейки E4 в ячейку D3 была скопирована формула. При копировании адреса ячеек в формуле автоматически изменились. Каким стало числовое значение формулы в ячейке D3?

Примечание: знак $ обозначает абсолютную адресацию.

Решение:

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

Формула, которая записана в ячейке Е4 имеет смешанный вид, то есть при ее перемещении останутся неизменными- столбец В в первом множители и строка 3 во втором.

Нам требуется переместить формулу из ячейки Е4 в ячейку D3 (на один столбец влево и на одну строку вверх). Например, при перемещении ячейки В2 на один столбец влево и на одну строку вверх мы переходим в ячейку А1, но в нашем случае В- абсолютная величина, следовательно первый множитель нашей формулы- В1.

Аналогично поступаем со вторым множителем: перемещаем С3 на один столбец влево и на одну строку вверх, переходим в ячейку В2, 3- абсолютная величина. Следовательно, второй множитель равен В3.

Формула в ячейке D3 принимает вид: В1 * В3 = 4 * 2 = 8.

Ответ: 8

Демонстрационный вариант Единый государственный экзамен ЕГЭ 2016 г. – задание №7 б

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

	A	B
1		6	10
2	= (A1 – 3)/(B1 – 1)	= (A1 – 3)/(С1 – 5)	= C1/(A1 – 3)

Какое целое число должно быть записано
в ячейке A1, чтобы диаграмма, построенная
по значениям ячеек диапазона A2:С2,
соответствовала рисунку? Известно, что все
значения ячеек из рассматриваемого
диапазона неотрицательны.

Решение:

по диаграмме определяем, что секторы А2 и В2 равны, то есть С2 = А2 + В2.
Обозначаем значение А1 как пременную х, записываем значения ячеек второй строки:

А2= (х – 3)/(6 – 1)= (х – 3)/5

В2= (х – 3)/(10 – 5)=(х – 3)/5

С2= 10 /(х – 3)

Получаем уравнение:

Ответ: 8

При работе с электронной таблицей в ячейке ЕЗ записана формула =В2+$СЗ . Какой вид приобретет формула после того, как ячейку ЕЗ скопируют в ячейку D2?

1) =А1+$СЗ 2) =А1+$С2 3) =E2+$D2 4) =D2+$E2

Решение:

=В2+$СЗ

Столбец меняется с “E” на “D”; она уменьшается на 1, поэтому “B” меняется на “A”. “C” не меняется, потому что знак “$” не допускает изменений.
=A?+$С?

Строка меняется с “3” на “2”; она уменьшается на 1, поэтому “2” становится “1”, а “3” становится “2”.
=A1+$С2

Ответ: 2

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

1) -1 2) -3 3) 1 4) 7

Решение:

Значение формулы =СРЗНАЧ(A 6: C 6) равно (-2 ).

СУММ(A 6: D 6) = СРЗНАЧ(A6:C6) . 3 = -2 . 3 = -6

СУММ(A 6: D 6) = =СУММ(A6:C6) + D6 = -6 + 5 = -1

Ответ: 1

На рисунке приведен фрагмент электронной таблицы. Определите, чему будет равно значение, вычисленное по следующей формуле =СУММ(B1: C 4)+F2* E 4– A 3

	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

Решение:

=СУММ(B1: C 4)+F2* E 4– A 3

=(3+4-5-2+5+5+3+1)+5*4-5 = 14 + 20 – 5 = 29

Ответ: 2

	B	C
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

Решение:

	B	C
69	5	10
70	6	9	=СЧЁТ(B69:C70)=4
71			=СРЗНАЧ(B69:D70)=(5+10+6+9+4)/5=6,8

	B	C
69		10	5
70	6	9	=СЧЁТ(B69:C70)=3
71			=СРЗНАЧ(B69:D70)=(5+10+6+9+3)/5=6,6

6,6 – 6,8 = -0,2

Ответ: 1

На рисунке приведен фрагмент электронной таблицы. В ячейку B2 записали формулу =($A2*10+B$1)^2 и скопировали ее вниз на 2 строчки, в ячейки B3 и B4. Какое число появится в ячейке B4?

1) 144 2) 300 3) 900 4) 90

Решение:

B2 =($A2*10+B$1)^2

B3 =($A3*10+B$1)^2

B4 =($A4*10+B$1)^2 = (3*10+0)^2 = 30^2 = 900

Ответ: 3

На рисунке приведен фрагмент электронной таблицы. Какое число появится в ячейке D1, если скопировать в нее формулу из ячейки C2?

	A	B	C	D
1	1	2
2	5	4	=A$2+$B$3
3	6	7	=A3+B3

1) 11 2) 9 3) 8 4) 6

Решение:

C2 =A$2+$B$3

D1 = B$2+$B$3 = 4+7 = 11

Ответ: 1

В ячейке 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

Решение:

Зх+у = 3*C22+D22

Столбец уменьшается на 1, строка уменьшается на 1.

1) =3*C22+D22 => =3*B21+C21

2) =3*$C22+$D22 => =3*C21+D21

3) =3*C$22+D$22 => =3*B22+C22

4) =3*D$22+$D23 => =3*C22+D22 (верно)

Ответ: 4

В ячейке E16 электронной таблицы записана формула. Эту формулу скопировали в ячейки D17 и C В соответствии с формулой, полученной в ячейке D17, значение в этой ячейке равно сумме значений в ячейках D32 и C31; в соответствии с формулой, полученной в ячейке C18, значение в этой ячейке равно сумме значений в ячейках D32 и B32. Укажите, какая формула могла быть написана в ячейке E16.

1) =$D30+D$30 2) =$D$32+D30 3) =$D$32+$D30 4) =D$32+$D30

Решение:

D32 меняется, поэтому $D$32 .

2) =$D$32+D30 3) =$D$32+$D30

D меняется на С и В, поэтому pнак $ не должен быть перед D.

2) =$D$32+D30

Ответ: 2

Дан фрагмент электронной таблицы. Из ячейки D2 в одну из ячеек диапазона E1:E4 была скопирована формула. При копировании адреса ячеек в формуле автоматически изменились, и значение формулы стало равным 24. В какую ячейку была скопирована формула? В ответе укажите только одно число – номер строки, в которой расположена ячейка.

	А	В	С	D	Е
1	10	9	8	7
2	9	8	7
3	8	7	6	5
4	7	6	5	4

Решение:

=C$2+$A3

Из ячейки D2 в одну из ячеек диапазона E1:E4 была скопирована формула.

=D$2+$A? = 24

D2 = C$2+$A3 = 7+8 = 15

15+$A? = 24

$A? = 24-15 = 9

“9” находится во второй строке. Это означает, что “3” ($A3 ) изменился на “2”, формула была скопирована в какую ячейку E1 .

Ответ: 1

В ячейки диапазонов C2:F6 и B3:B6 электронной таблицы записаны числа, как показано на рисунке. В ячейке A1 записали формулу =$E5 – D$3. После этого ячейку A1 скопировали в ячейку B2. Какое число будет показано в ячейке B2?

	А	В	С	D	Е	F
1
2			1	2	3	4
3		1	1	2	3	4
4		2	2	4	6	8
5		3	3	6	9	12
6		4	4	8	12	16

Решение:

B2 = $E6 – E$3 = 12 – 3 = 9

Ответ: 9

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

После выполнения вычислений построили диаграмму по значениям диапазона A1:D1. Укажите полученную диаграмму:

Решение:

	A	B	C	D
1	=C2-1=3-1=2	=A1*2=2.2=4	=C2+B1-2=3+4-2=5	=(B1+D2)/2=(4+2)/2=3
2			3	2

Ответ: 3

Все ученики старших классов (с 9-го по 11-й) участвовали в школьной спартакиаде. По результатам соревнований каждый из них получил от 0 до 3 баллов. На диаграмме 1 показано количество по классам, а на диаграмме 2 – количество учеников, набравших баллы от 0 до 3.

Какое из этих утверждений следует из анализа обеих диаграмм?

1) Среди учеников 9 класса есть хотя бы один, набравший 2 или 3 балла.

3) Все 10-классники могли набрать ровно по 2 балла.

4) Среди набравших 3 балла нет ни одного 10-классника.

Решение:

2) Все ученики, набравшие 0 баллов, могут быть 9-классниками.

Ответ: 2

На предприятии работают 100 человек. Каждый из них владеет по крайней мере одним иностранным языком. Диаграмма 1 показывает, сколько человек владеют каждым из языков (А – английский, Н – немецкий, Ф – французский). Диаграмма 2 отражает количество человек, знающих только один язык, два языка или три языка.

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

1) 10 2) 20 3) 30 4) 40

Решение:

10 человек, знающих только французский (Диаграмма 1)

10 человек, знающих три языка (Диаграмма 2)

20 человек, знающих два языка – владеют английским и немецким, но не говорят по-французски

Ответ: 2

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

Какое целое число должно быть записано в ячейке C1, чтобы построенная после выполнения вычислений диаграмма по значениям диапазона ячеек A2:С2 соответствовала рисунку? Известно, что все значения диапазона, по которым построена диаграмма, имеют один и тот же знак.

Решение:

	A	B	C
1	2	4
2	=B1+1=4+1=5	=A1+2C1=2+2C1**	=C1+A1/2=C1+1

Из диаграммы мы понимаем, что есть 2 равные части, а третья часть в 2 раза больше.

C2=4+1=5

B2=2+2*4=10

Ответ: 4

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

Какое целое число должно быть записано в ячейке B1, чтобы построенная после выполнения вычислений диаграмма по значениям диапазона ячеек A2:С2 соответствовала рисунку? Известно, что все значения диапазона, по которым построена диаграмма, имеют один и тот же знак.

Решение:

	A	B	C
1	20	x	35
2	=C1-2B1B1	(B1B1B1-4)/A1	=C1-8*B1

Из диаграммы мы понимаем, что все части являются одинаковыми.

Ответ: 4

При использовании материалов этого сайта - и размещение баннера -ОБЯЗАТЕЛЬНО!!!

Контрольная работа по теме: "Формулы и функции в Excel"

Практическую работу подготовила: Ермолаева Наталья Николаевна, учитель информатики, email: [email protected]

Контрольная работа.

Вариант 1.

1.Определить вид ссылок: А$10; $D$8; F5

2.Найти значение ячейки С2:

3.В ячейку В2 занесена формула =$A1+$B$2-B1 Какая формула получится после копирования данной в ячейку D3?

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

5.При каких значениях А2 в ячейке В6, где записана формула =ЕСЛИ (И(А2<10;А2>5);1;0), отобразится число 1?

Контрольная работа.

Вариант 2.

1.Определить вид ссылок: А3; $С7; $Е$12.

2.Найти значение ячейки С2:

3.В ячейку А1 занесена формула =A$1-$B$1-B3 Какая формула получится после копирования данной в ячейку С4?

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

5.При каких значениях В1 в ячейке С4, где записана формула =ЕСЛИ(И(B1<7;B1>=12);1;0), отобразится число 0?

Контрольная работа.

Вариант 3.

1.Определить вид ссылок: $В$5; А12; $Е4.

2.Найти значение ячейки С2:

3.В ячейку С1занесена формула =$А$1+В1-С$1 Какая формула получится после копирования данной в ячейку D5?

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

5.При каких значениях А1 в ячейке В5, где записана формула =ЕСЛИ(И(А1<=5;А1>2);1;0), отобразится число 1?

Контрольная работа.

Вариант 4.

1.Определить вид ссылок: D11; F$5; $A$1.

2.Найти значение ячейки С2:

3.В ячейку A2 занесена формула =$A2+$A$1-B1. Какая формула получится после копирования данной в ячейку F3?

4.В электронной таблице значение формулы =СУММ(D1:D3) равно 7. Чему равно значение ячейки D3, если значение формулы =СРЗНАЧ(D1:D2) равно 3?

5.При каких значениях B2 в ячейке C4, где записана формула =ЕСЛИ(И(B2<7;B2>=11);1;0), отобразится число 0?

ОТВЕТЫ:

А$10- смешанная $D$8 - абсолютная F5- относительная
А1=1 А2=10 В1=2 В2=12 С1=17 С2=22
=$A2+$B$2-D2
C1+C2+C3=12

(C1+C2+C3+C4)/4=5

А3 - относительная; $С7- смешанная; $Е$12- абсолютная
А1=10 А2=6 В1=4 В2=5 С1=1 С2=4
=С$1-$B$1-D6
A1+A2 =7

$В$5- абсолютная; А12- относительная; $Е4- смешанная
А1=8 А2=2 В1=4 В2=2 С1=6 С2=15
=$А$1+C5-D$1
В1+В2+В3+В4 =13

(В1+В2+В3)/3=3

D11- относительная; F$5- смешанная; $A$1- абсолютная
А1=3 А2=3 В1=1 В2=7 С1=8 С2=13
=$A3+$A$1-G2
D1+D2+D3 =7

Урок посвящен тому, как решать 7 задание ЕГЭ по информатике

7-я тема — «Электронные таблицы Excel»- характеризуется, как задания базового уровня сложности, время выполнения – примерно 3 минуты, максимальный балл — 1

* Некоторые изображения страницы взяты из материалов презентации К. Полякова

Типы ссылок в ячейках

Формулы, записанные в ячейках таблицы, бывают относительными , абсолютными и смешанными .

Стандартные функции Excel

В ЕГЭ встречаются в формулах следующие стандартные функции:

СЧЕТ — количество непустых ячеек,
СУММ — сумма,
СРЗНАЧ — среднее значение,
МИН — минимальное значение,
МАКС — максимальное значение

В качестве параметра функции везде указывается диапазон ячеек: МИН(А2:А240)

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

Построение диаграмм

Решение заданий ЕГЭ по информатике

Рассмотрим, как решается задание 7 ЕГЭ по информатике.

Анализ диаграмм

7_1:

Какая из диаграмм правильно отражает соотношение общего количества участников (из всех трех регионов) по каждому из предметов тестирования?

✍ Решение:

столбчатая диаграмма позволяет определить числовые значения. Так, например, в Татарстане по биологии количество участников 400 и т.п. Найдем с помощью нее общее количество участников со всех регионов по каждому предмету. Для этого посчитаем значения абсолютно всех столбцов в диаграмме:

400 + 100 + 200 + 400 + 200 + 200 + 400 + 300 + 200 = 2400

по круговой диаграмме можно определить только доли отдельных составляющих в общей сумме: в нашем случае это доли участников по различным предметам тестирования;

для того чтобы разобраться, какая круговая диаграмма подходит, сначала посчитаем самостоятельно долю участников, тестирующихся по отдельным предметам; для этого из столбчатой диаграммы вычислим сумму участников по каждому предмету и разделим на уже полученное в первом пункте общее количество участников:

Биология: 1200/2400 = 0,5 = 50% История: 600/2400 = 0,25 = 25% Химия: 600/2400 = 0,25 = 25%

Теперь сравним полученные данные с круговыми диаграммами. Данные соответствуют диаграмме под номером 1 .

Результат: 1

Предлагаем посмотреть подробный разбор данного 7 задания на видео:

7_2:

На диаграмме отображено количество участников тестирования по предметам в разных регионах России.

Какая из диаграмм правильно отражает соотношение количества участников тестирования по истории в регионах?

✍ Решение:

Результат: 2

Подробный разбор задания смотрите на видео:

Копирование формул

7_3: ЕГЭ по информатике 2016, «Типовые тестовые задания по информатике», Крылова С.С., Чуркиной Т.Е. Вариант 2.:

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

Из ячейки A3 в ячейку С2
С2 ?

✍ Решение:

Результат: 180

Разбор данного 7 задания смотрите на видео:

7_4: ЕГЭ по информатике 2017, «Типовые тестовые задания по информатике», Крылова С.С., Чуркиной Т.Е. Вариант 5:

A3 в ячейку E2 была скопирована формула. При копировании адреса ячеек автоматически изменились.
Каким стало числовое значение формулы в ячейке E2 ?

✍ Решение:

Рассмотрим формулу в ячейке A3 : = $E$1*A2 . Знак доллара означает абсолютную адресацию: при копировании формулы буква или цифра, стоящая рядом с долларом, не изменится. То есть в нашем случае сомножитель $E$1 так и останется в формуле при копировании.
Поскольку копирование осуществляется в ячейку E2 , необходимо посчитать на сколько столбцов вправо переместится формула: на 5 столбцов (от A до E ). Соответственно, и в сомножителе A2 буква A заменится на E .
Теперь посчитаем на сколько строк вверх сместится при копировании формула: на одну (c A3 на E2 ). Соответственно и в сомножителе A2 цифра 2 заменится на 1 .
Получим формулу и посчитаем результат: =$E$1*E1 = 1

Результат: 1

7_5: 7 задание. Демоверсия ЕГЭ 2018 информатика:

Дан фрагмент электронной таблицы. Из ячейки B3 в ячейку A4 была скопирована формула. При копировании адреса ячеек в формуле автоматически изменились.
Каким стало числовое значение формулы в ячейке A4 ?

Примечание: знак $ обозначает абсолютную адресацию.

✍ Решение задания 7:

Знак доллара $ означает абсолютную адресацию:
$ перед буквой означает фиксацию столбца: т.е. при копировании формулы название столбца меняться не будет;
$ перед цифрой означает фиксацию строки: при копировании формулы название строки меняться не будет.
В нашем случае меняться не будут выделенные буквы и цифры: = $C 2 + D$3
Копирование же формулы на один столбец влево, означает, что буква D (в D$3) должна поменяться на предшествующую ей C . При копировании формулы вниз на одну строку, значение 2 (в $C2) меняется на 3 .
Получаем формулу:

= $C3 + С$3

В итоге имеем результат: 300 + 300 = 600

Результат: 600

Подробное решение данного 7 задания из демоверсии ЕГЭ 2018 года смотрите на видео:

Какая формула была записана

7_6: 7 задание ЕГЭ. Задание 6 ГВЭ 11 класс 2018 год (ФИПИ)

Коле нужно с помощью электронных таблиц построить таблицу значений формулы 5х–3у для значений х и у от 2 до 5 . Для этого сначала в диапазонах В1:Е1 и А2:А5 он записал числа от 2 до 5 . Затем в ячейку В2 записал формулу (А2 – значение х; В1 – значение у), после чего скопировал её во все ячейки диапазона B2:E5 . В итоге получил таблицу, представленную ниже.

Какая формула была записана в ячейке В2 ?

Примечание: знак $ используется для обозначения абсолютной адресации.

Варианты:
1)=5*$A$2–3*$B$1
2)=5*$A2–3*B$1
3)=5*A$2–3*$B1
4)=5*A2–3*$B$1

✍ Решение:

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

По горизонтали:

В формуле ссылка на столбец А не должна менять букву при копировании, значит, перед ней необходимо поставить знак $ :

= 5 * $A

Тогда как имя столбца B должно меняться (на C, D, E), чтобы цифры в вычитаемом менялись (3, 4, 5):

= 3 * B

По вертикали:

Номер строки в уменьшаемом должен меняться, чтобы цифры в нем увеличивались (3, 4, 5). Тогда как строки в вычитаемом меняться не должны: $A2 . Таким образом, необходимо поставить знак $ перед номером строки в уменьшаемом: B$1

В результате получаем формулу: = 5 * $A2 – 3 * B$1 , что соответствует номеру 2 .

Результат: 2

Значение формулы СУММ или СРЗНАЧ

7_7: ЕГЭ по информатике задание 7 (пример задания P-00, Поляков К.)

За

Как изменится значение ячейки C3 , если после ввода формул переместить содержимое ячейки B2 в B3 ?
(«+1» означает увеличение на 1 , «-1» означает уменьшение на 1 ):

Варианты:
1) -2
2) -1
3) 0
4) +1

✍ Решение:

В ячейке C2 будет находиться число 4 , так как функция СЧЁТ подсчитывает количество непустых ячеек указанного диапазона.
В ячейке С3 будет находиться число 3 :

(1 + 2 + 2 + 6 + 4) / 5 = 3

Теперь посмотрим, что произойдет после перемещения:

Перемещение содержимого ячейки означает, что ячейка B2 окажется пустой, а в ячейке B3 появится число 6 .

Тогда расчёт формулы в ячейке C2 поменяется: количество непустых ячеек диапазона A1:B2 станет равным 3 .

Соответственно изменится и значение после расчёта формулы ячейки C3 : среднее значение содержимого диапазона ячеек A1:C2 станет равным:

(1 + 2 + 2 + 3) / 4 = 2

(нужно не забывать, что функция СРЗНАЧ не учитывает пустые ячейки, поэтому ячейка B2 не учтена).

Таким образом, значение после перемещения формулы изменилось, уменьшившись на 1 . Верный ответ 2

Результат: 2

Подробное решение задания на видео:

7_8:

В электронной таблице значение формулы =СРЗНАЧ(С2:С5) равно 3 .

Чему равно значение формулы =СУММ(С2:С4) , если значение ячейки С5 равно 5 ?

✍ Решение:

Функция СРЗНАЧ предназначена для вычисления среднего арифметического значения указанного диапазона ячеек. Т.е. в нашем случае среднее значение ячеек C2, C3, C4, C5.
Результат функции =СРЗНАЧ(С2:С5) задан по условию, подставим его в формулу:

(C2 + C3 + C4 + C5)/4 = 3

Примем неизвестную сумму в за x и получим по формуле вычисления среднего значения:

x / 4 = 3

Найдем x :

x = 3 * 4 = 12 -> C2 + C3 + C4 + C5 = 12

По заданию необходимо найти =СУММ(С2:С4) . Зная значение в ячейке С5 , вычтем его из полученной суммы и найдем ответ:

C2 + C3 + C4 = C2 + C3 + C4 + C5 - C5 = = 12 - 5 = 7

Результат: 7

Подробное решение смотрите на видео:

Какое число должно быть записано в ячейке

7_9: ЕГЭ по информатике 2017 задание ФИПИ вариант 7 (Крылов С.С., Чуркина Т.Е.):

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

А1 , чтобы диаграмма, построенная по значениям ячеек А2:С2 , соответствовала рисунку? Известно, что все значения ячеек из рассматриваемого диапазона неотрицательны.

✍ Решение:

Имеем круговую диаграмму, которая отображает доли отдельных составляющих в общей сумме. По изображению диаграммы можно судить о том, что, скорее всего, значения во всех ячейках формулы должны быть равны (секторы диаграммы визуально равны).
A1 -> x :

А2: х + 4 - 3 = х + 1 В2: (5 * х + 5) / 5 С2: (х + 1)*(х - 4) = х 2 - 3 * х - 4

Так как секторы диаграммы равны, то приравняем любые два из полученных выражений (например, С2 = А2 ):

х²-3 * х - 4 = х + 1 х²-4 * х - 5 = 0 х1,2 = (4±√16 - 4 * 1 * (-5)) / 2 = (4±6) / 2 x1 = 5, x2 = -1

По условию задания число не должно быть отрицательным, поэтому нам подходит 5

Результат: 5

Для более детального разбора предлагаем посмотреть видео решения данного 7 задания ЕГЭ по информатике:

Рассмотрим еще один пример решения 7 задания ЕГЭ по информатике:

7_10: ЕГЭ по информатике 2017 задание 7 ФИПИ вариант 15 (Крылов С.С., Чуркина Т.Е.):

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

Какое целое число должно быть записано в ячейке C1 , чтобы построенная после выполнения вычислений диаграмма по значениям диапазона ячеек А2:С2 соответствовала рисунку?
Известно, что все значения диапазона, по которым построена диаграмма, имеют один и тот же знак.

✍ Решение:

Круговая диаграмма отображает доли отдельных частей в общей сумме. В нашем случае в диаграмме отражаются результаты вычисления формул в ячейках А2:С2
По диаграмме можно судить о том, что, скорее всего, полученные значения в формулах во всех ячейках должны быть равны (секторы диаграммы визуально равны).
Получим выражения из формул ячеек, подставив вместо С1 -> x :

А2: х + 2 В2: 8/2 = 4 С2: х * 2

Так как секторы диаграммы равны, то приравняем два из полученных выражений (например, С2 = В2 ):

2 * х = 4 => x = 2