Microsoft Excel (в дальнейшем просто - Excel) - это программа выполнения расчетов и управления так называемыми электронными таблицами.
Excel позволяет выполнять сложные расчеты, в которых могут использоваться данные, расположенные в разных областях электронной таблицы и связанные между собой определенной зависимостью. Для выполнения таких расчетов в Excel существует возможность вводить различные формулы в ячейки таблицы. Excel выполняет вычисления и отображает результат в ячейке с формулой.
Важной особенностью использования электронной таблицы является автоматический пересчет результатов при изменении значений ячеек. Excel также может строить и обновлять графики, основанные на введенных числах.
Адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки, например, C15.
Для написания формул используют адреса ячеек и знаки арифметических операций (+, -, *, /, ^). Формула начинается знаком =.
В Excel предусмотрены стандартные функции, которые могут быть использованы в формулах. Это математические, логические, текстовые, финансовые и другие функции. Однако, на экзамене Вам могут встретиться только самые простые функции: СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение).
Диапазон ячеек обозначается следующим образом: A1:D4 (все ячейки прямоугольника от A1 до D4.
Адреса ячеек бывают относительными, абсолютными и смешанными.
Они по-разному ведут себя при копировании формулы из ячейки в ячейку.
Относительная адресация:
Если в ячейке B2 мы напишем формулу =D1+3, то таблица воспримет это как «взять значение ячейки на две правее и на одну выше текущей, и прибавить к нему 3».
Т.е. адрес D1 воспринимается таблицей, как положение относительно ячейки, куда вводится формула. Такой адрес называется относительным. При копировании такой формулы в другую ячейку, таблица автоматически пересчитает адрес относительно нового расположения формулы:
Абсолютная адресация:
Если нам не нужно, чтобы адрес пересчитывался при копировании формулы, мы можем его «закрепить» в формуле - поставить знак $ перед буквой и индексом ячейки: =$D$1+3. Такой адрес называется абсолютным. Такая формула не будет изменяться при копировании:
Смешанная адресация:
Если же мы хотим, чтобы при копировании формулы автоматически пересчитывался, к примеру, только индекс ячейки, а буква оставалась неизменной, мы можем «закрепить» в формуле только букву (или наоборот): =$D1+3. Такой адрес называется смешанным. При копировании формулы будет меняться только индекс в адресе ячейки:
Электронные таблицы. Копирование формул.
Пример 1.
В ячейке C2 записана формула =$E$3+D2 . Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?
1) =$E$3+C1 2) =$D$3+D2 3) =$E$3+E3 4) =$F$4+D2
Решение:
Место расположения формулы меняется с C2 на B1, т.е. формула сдвигается на одну ячейку влево и на одну ячейку вверх (буква «уменьшается» на единицу и индекс уменьшается на единицу). Значит, так же изменятся все относительные адреса, а абсолютные (закрепленные знаком $) останутся неизменными:
=$E$3+С1.
Ответ: 1
Пример 2.
В ячейке В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
Решение:
Проанализируем поочередно каждую формулу:
Место расположения формулы меняется с B11 на A10, т.е. буква «уменьшается» на 1 и индекс уменьшается на 1.
Тогда при копировании формулы изменятся следующим образом:
Условию задачи соответствует формула 2).
Ответ: 2
Электронные таблицы. Определение значения формулы.
Пример 3.
Дан фрагмент электронной таблицы:
В ячейку D1 введена формула =$А$1*В1+С2 , а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке D2?
1) 10 2) 14 3) 16 4) 24
Решение:
Место расположения формулы меняется с D1 на D2, т.е. буква не меняется, а индекс увеличивается на 1.
Значит, формула примет вид: =$А$1*В2+С3. Подставим в формулу числовые значения ячеек:1*5+9=14. Правильный ответ указан под номером 2.
Ответ: 2
Пример 4.
В электронной таблице значение формулы =СРЗНАЧ(A 6: C 6) равно (-2 ). Чему равно значение формулы =СУММ(A 6: D 6) , если значение ячейки D6 равно 5?
1) 1 2) -1 3) -3 4) 7
Решение:
По определению среднего значения:
СРЗНАЧ(A 6: C 6) = СУММ(A 6:С6)/3 = -2
Значит, СУММ(A 6:С6) = -6
СУММ(A 6: D 6) = СУММ(A 6:С6)+ D 6 = -6+5 = -1
Ответ: 2
Электронные таблицы и диаграммы.
Пример 5.
Дан фрагмент электронной таблицы в режиме отображения формул.
После выполнения вычислений построили диаграмму по значениям диапазона A1:D1. Укажите полученную диаграмму:
Решение:
Вычислим по формулам значения ячеек A1:D1.
Этим данным соответствует диаграмма 3.
Ответ: 3
7.1 (ege.yandex.ru-1) Дан фрагмент электронной таблицы:
Решение:
Из второго уравнения находим: С1=3. Проверим, что это значение подходит и для первого уравнения:
2*(4-3) = 2*1 =2
Ответ: 3
7.2 (ege.yandex.ru-2) Дан фрагмент электронной таблицы:
Какое целое число должно быть записано в ячейке C1, чтобы круговая диаграмма, построенная для диапазона A2:С2, соответствовала рисунку? Известно, что все значения диапазона, по которым построена диаграмма, имеют один и тот же знак.
Решение : Диаграмма построена по значениям трех ячеек: A2, B2, C2. Из круговой диаграммы видно, что эти значения соотносятся как 1:1:1. Поскольку значения ячеек A1 и B1 известны, заполним диапазон A2:C2 значениями вместо формул (там, где это возможно):
Так как значения во всех ячейках диапазона A2:C2 должны быть равны, то для величины С1 получаем два уравнения:
Из второго уравнения находим: С1=2. Проверим, что это значение подходит и для первого уравнения:
Ответ : 2
7.3 (ege.yandex.ru-3) Дан фрагмент электронной таблицы:
Какое целое число должно быть записано в ячейке B1, чтобы круговая диаграмма, построенная для диапазона A2:С2, соответствовала рисунку? Известно, что все значения диапазона, по которым построена диаграмма, имеют один и тот же знак.
Решение 1: Диаграмма построена по значениям трех ячеек: A2, B2, C2. Из круговой диаграммы видно, что эти значения соотносятся как 2:1:1, при этом неизвестно, какая ячейка соответствует какому сектору диаграммы. Упростим формулы, учитывая, что мы знаем значение для ячейки A1:
Из формулы в ячейке C2 видно, что значения B2 и C2 различны. Поэтому A2 = B2. Это дает нам уравнение для B1:
3-B1 = (3*B1+3)/3
Решаем уравнение.
Ответ: 1
Решение 2 (похожие рассуждения, немного короче): Диаграмма построена по значениям трех ячеек: A2, B2, C2. Из круговой диаграммы видно, что эти значения соотносятся как 2:1:1, при этом неизвестно, какая ячейка соответствует какому сектору диаграммы. Из формулы в ячейке C2 видно, что значения B2 и C2 различны. Поэтому A2 = B2. Учитывая, что C1 = A1+1 = 2+1 =3, получаем уравнение для B1
3-B1 = (3*B1+3)/3
Решаем уравнение.
Проверим себя – найдем значения во всех ячейках таблицы
Ответ: 1
7.4 (ege.yandex.ru-4) Дан фрагмент электронной таблицы:
Какое целое число должно быть записано в ячейке A1, чтобы круговая диаграмма, построенная для диапазона A2:С2, соответствовала рисунку? Известно, что все значения диапазона, по которым построена диаграмма, имеют один и тот же знак.
Решение : Диаграмма построена по значениям трех ячеек: A2, B2, C2. Из круговой диаграммы видно, что эти значения соотносятся как Х:1:1, где Х примерно равно 4. При этом неизвестно, какая ячейка соответствует какому сектору диаграммы. Упростим формулы в таблице, учитывая, что C1=2. Получим:
Так как B2 > C2, то должно быть выполнено A2=C2. Получаем:
откуда A1=7.
Ответ: 7
7.5 (ege.yandex.ru-5) Дан фрагмент электронной таблицы:
Какое целое число должно быть записано в ячейках B1, чтобы круговая диаграмма, построенная для диапазона A2:С2, соответствовала рисунку? Известно, что все значения диапазона, по которым построена диаграмма, имеют один и тот же знак.
Решение: Диаграмма построена по значениям трех ячеек: A2, B2, C2. Из круговой диаграммы видно, что эти значения соотносятся как 2:1:1. То есть, одно из значений (большее) отличается от других, а два меньших значения равны между собой. При этом неизвестно, какая ячейка соответствует какому сектору диаграммы. Упростим формулы в таблице, учитывая, сто A1=4. Получим:
Посмотрим на формулу =B2+4 в ячейке C2. Видно, что значение в ячейке C2 на 4 больше, чем значение в ячейке B2. Другими словами, значения в ячейках B2 и C2 различны, при этом C2 > B2. Значит С2 - большее из трех чисел, а A2 = B2 - два меньших. При этом из диаграммы видно, что C2 в два раза больше, чем A2 и B2. Поэтому выполнено:
Это дает нам для определения значений B1 и C1 систему из двух уравнений:
B1-C1+4 = 2*(B1-C1)
Из 1-го уравнения: B1 = 5*C1. Подставляем во 2-е уравнение:
5*C1 – C1 + 4 = 2*(5*C1-C1)
Следовательно, B1=5. Делаем проверку – вычисляем значения для всех ячеек:
За это задание вы можете получить 1 балл на ЕГЭ в 2020 году
Анализу диаграмм и электронных таблиц посвящено задание 7 ЕГЭ по информатике. При решении этого теста вам придется, к примеру, определять значения формул по некоторым параметрам. Типичный вопрос такого варианта: «Если среднее арифметическое четырех значений в таблице равно 5, то чему равна сумма первых трех ячеек, если в четвертой ячейке стоит число 6, и пустых ячеек в таблице не имеется».
В других вариантах задания 7 ЕГЭ по информатике учащемуся будет предложено составить диаграмму по приведенным данным. К примеру, вам даны составы двух веществ с указаниями массовых долей их компонентов. Требуется определить соотношение этих элементов в сплаве двух веществ и среди представленных диаграмм найти верную. Также в билете могут быть представлены задачи по определению суммарного за какое-то время дохода каждого члена семьи, объема собранного урожая по каждому из сортов огурцов, количеству участников школьников по предметам в разных регионах России, росту цен каких-то товаров в процентах относительно начала года.
Задачи типа А7 по информатике подразумевают знание технологии обработки информации в электронных таблицах . Еще более конкретно — абсолютную и относительную адресацию .
В качестве примера рассмотрим решение задачи А7 рассмотрим решение А7 демоверсии ЕГЭ 2013 по информатике :
Дан фрагмент электронной таблицы.
A | B | C | D | |
1 | 1 | 2 | 3 | |
2 | 5 | 4 | = $A$2 + B$3 | |
3 | 6 | 7 | = A3 + B3 |
Чему станет равным значение ячейки D1, если в неё скопировать формулу из
ячейки C2?
Примечание: знак $ обозначает абсолютную адресацию.
1)18 2)12 3)14 4)17
Решение:
Посмотрим на содержимое ячейки C2. Там содержится формула, использующая две ячейки, причем ссылки на ячейки полностью абсолютные ($A$2) или частично абсолютные (B$3). При копировании из ячейки C2 в ячейку D1 адрес ячейки $A$2 останется прежним, так как адрес ее указан абсолютно. Адрес ячейки B$3 задан частично абсолютным — при копировании номер строки изменяться не будет, а вот столбец будет изменяться. При копировании из столбца C в столбец D адрес ячейки B$3 изменится на 1 и станет C$3. В итоге в после копирования в ячейке D1 окажется формула = $A$2 + C$3. Содержимое ячейки A2 мы знаем — оно равно 5. Содержимое ячейки C3 нужно рассчитать: A3 + B3 = 6 + 7 = 13. Получим, что значение ячейки D1 будет равно 5 + 13 = 18. Правильный ответ 1 .
В качестве закрепления решим задачу А7 демоверсии ЕГЭ 2012 года по информатике :
В ячейке B4 электронной таблицы записана формула = $C3*2. Какой вид приобретет формула, после того как ячейку B4 скопируют в ячейку B6?
Примечание: знак $ используется для обозначения абсолютной адресации.
1) = $C5 *4 2) = $C5 *2 3) = $C3 *4 4) = $C1 *2
Решение:
В формуле = $С3 * 2 адресация ячейки $С3 частично абсолютная — при копировании меняется только номер строки (так как перед ним стоит знак $), а столбец останется неизменным. При копировании из ячейки В4 в ячейку В6 номер строки увеличится на 2, следовательно адрес ячейки $C3 превратится в $C5. В итоге в ячейке B6 окажется формула = $C5 * 2. Правильный ответ 2 .
Разбор 7 задания ЕГЭ 2017 года по информатике из проекта демоверсии. Это задание базового уровня сложности. Примерное время выполнения задания 3 минуты.
Проверяемые элементы содержания: знание технологии обработки информации в электронных таблицах и методов визуализации данных с помощью диаграмм и графиков. Элементы содержания, проверяемые на ЕГЭ: Математическая обработка статистических данных. Использование инструментов решения статистических и расчетно-графических задач.
Дан фрагмент электронной таблицы. Из ячейки A2 в ячейку B3 была скопирована формула. При копировании адреса ячеек в формуле автоматически изменились. Запишите в ответе числовое значение формулы в ячейке B3.
Примечание: знак $ обозначает абсолютную адресацию.
Ответ: ________
Наша формула =C$2+D$3
в ячейке А2
содержит две смешанные ссылки.
- в первой С$2
- адрес строки 2 не меняется при копировании
- во второй D$3
- не меняется адрес строки 3 при копировании
Наша формула =C$2+D$3
была скопирована из ячейки А2
в ячейку B3
.
— сместилась на один столбец вправо (увеличилась на один столбец)
— сместилась на одну строку вниз (увеличилась на одну строку)
Следовательно после копирования формула =C$2+D$3 , примет вид =D$2+E$3 .
Вычисление этого выражения дает следующий результат: 70+5=75 .