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

Контрольная работа по теме: "Формулы и функции в 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

Пример 1.

В ячейке Н5 нужно записать максимальное из двух чисел, содержащихся в ячейках Н2 и Н4. Формула, введенная в ячейку Н5, =ЕСЛИ(Н2>H4;H2;H4) означает, что если значение ячейки Н2 больше значения ячейки Н4, то в ячейке Н5 будет записано значение из Н2, в противном случае – из Н4.

В качестве выражения 1 или выражения 2 можно записать вложенную функцию ЕСЛИ. Число вложенных ЕСЛИ не должно превышать семи.

ЕСЛИ(<логическое_выражение 1>;<значение_если_истина>;ЕСЛИ(<логическое_выражение 2>;<значение_если_истина>;<значение_если_ложь>)).

На месте логического выражения можно использовать одну из логических функций И или ИЛИ.

Формат функций: И(<логическое_выражение 1>;<логическое_выражение 2>;…)

ИЛИ(<логическое_выражение 1>;<логическое_выражение 2>;…).

В скобках может быть указано до пятидесяти логических выражений. Функция И принимает значение «Истина», если одновременно все логические выражения истинны. Функция ИЛИ принимает значение «Истина», если хотя бы одно из логических выражений истинно.

Определить, входит ли в заданный диапазон (5;10) число, содержащееся в ячейке Н10. Ответ 1 (если число принадлежит диапазону) и 0 (если число не принадлежит диапазону) должен быть получен в ячейке Н12. В ячейку Н12 вводится формула:

ЕСЛИ(И(Н10>5;H10<10);1;0)

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

1) Выделить ячейку Н12.

2) Щелкнуть в строке формул на кнопку Вставить функцию (см. рис.2)

Щелкнуть по вкладке Формула и из панели Библиотека функций выбрать команду Вставить функцию.

3) В открывшемся диалоговом окне (см. рис.3) в списке Категория выбрать Логические функции. В списке Выберите функцию – ЕСЛИ . Нажать ОК .

4) В следующем окне нужно задать аргументы функцииЕСЛИ (см. рис.4)

5) Аргументом данной функции является логическая функцияИ (эта функция вложена внутрь функции ЕСЛИ ). Чтобы вызвать функцияИ необходимо щелкнуть по стрелке, открывающей список функций. Список располагается в поле имен в строке формул (см. рис.5).

6) Если в списке функции нет, то выбрать пункт Другие функции. Функция И относится к логическим функциям. После ее выбора откроется диалоговое окно для задания аргументов этой функции (см. рис.6).

7) Заполнить поля Логическое_выражение логическими выражениями (см. рис.7)

8) Нажимать на кнопку ОК в этом окне не следует. Нужно вернуться к заданию аргументов функции ЕСЛИ . Для этого нужно щелкнуть мышью по этой функции в строке формул (см. рис.8).

9) Заполнить поля Значение_если_истина и Значение_если_ложь согласно заданию (см. рис.9) и нажать кнопку ОК.

Урок посвящен тому, как решать 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