Электронные таблицы (Excel): подготовка к ЕГЭ по информатике
Электронные таблицы (Excel) — одна из ключевых тем кодификатора ФИПИ для ЕГЭ по информатике. Она охватывает работу с ячейками, ссылками, встроенными функциями, построение диаграмм, а также сортировку и фильтрацию данных. Владение этими навыками необходимо не только для успешной сдачи экзамена, но и для дальнейшего обучения и профессиональной деятельности.
На ЕГЭ задания по электронным таблицам встречаются как в базовой, так и в повышенной части. Типичные задачи требуют умения применять функции SUM, AVERAGE, COUNT, IF, COUNTIF, SUMIF, использовать абсолютные и относительные ссылки, а также интерпретировать данные с помощью диаграмм. Ниже мы подробно разберём каждый подраздел, приведём примеры реальных задач и покажем пошаговые решения.
Материал рассчитан на учеников 10-11 классов, которые готовятся к ЕГЭ. Мы не будем использовать упрощённые условия — только задачи уровня экзамена. Для закрепления теории в конце статьи предложим FAQ с ответами на частые вопросы.
Без карты, без кредитки. Выбери персонажа — учи голосом, побеждай в баттлах.
Ячейки, диапазоны, абсолютные и относительные ссылки
В электронных таблицах каждая ячейка имеет адрес, состоящий из буквы столбца и номера строки (например, A1, B2). Диапазон задаётся через двоеточие: A1:B10. При копировании формул ссылки могут изменяться (относительные) или оставаться фиксированными (абсолютные).
Относительная ссылка (например, A1) при копировании в другую ячейку изменяется в зависимости от смещения. Абсолютная ссылка (например, $A$1) при копировании остаётся неизменной. Смешанные ссылки фиксируют либо столбец, либо строку ($A1 или A$1).
Понимание этих механизмов критически важно для решения задач ЕГЭ, где часто требуется определить результат копирования формулы или значение в ячейке после автозаполнения.
В ячейке B3 записана формула =$A$1*C2. Эту формулу скопировали в ячейку D5. Какая формула будет в ячейке D5?
Шаг 1: Определяем смещение при копировании из B3 в D5. По столбцам: от B к D — смещение на 2 столбца вправо. По строкам: от 3 к 5 — смещение на 2 строки вниз.
Шаг 2: Анализируем ссылки в исходной формуле. $A$1 — абсолютная ссылка (и столбец, и строка зафиксированы), при копировании она не изменится. C2 — относительная ссылка, она изменится на величину смещения: столбец C сместится на 2 вправо → E, строка 2 сместится на 2 вниз → 4. Получаем E4.
Шаг 3: Итоговая формула в D5: =$A$1*E4.
В ячейке A1 записано число 10, в B1 — 20, в C1 — 30. В ячейке D1 записана формула =A$1+$B1. Как изменится результат, если скопировать формулу из D1 в D2?
Шаг 1: Исходная формула в D1: =A$1+$B1. A$1 — смешанная ссылка: столбец относительный (A), строка абсолютная (1). $B1 — смешанная: столбец абсолютный (B), строка относительная (1).
Шаг 2: Копируем из D1 в D2. Смещение по строкам: на 1 вниз. По столбцам смещения нет.
Шаг 3: Изменяем ссылки: A$1 — столбец A останется (относительный, но смещения по столбцам нет), строка 1 не изменится (абсолютная). $B1 — столбец B абсолютный, строка 1 изменится на 2 (относительная). Получаем формулу =A$1+$B2.
Шаг 4: Вычисляем: A$1 = 10, $B2 = 20 (значение из B2, но B2 пусто, считаем 0? В задаче обычно подразумевается, что B2 пусто, поэтому результат 10. Если в B2 есть число, то оно и будет). В контексте ЕГЭ важно, что формула изменилась.
Функции: SUM, AVERAGE, COUNT, IF, COUNTIF, SUMIF
Встроенные функции Excel — основа для обработки данных. На ЕГЭ часто встречаются задачи на вычисление суммы, среднего, количества значений, а также условные функции. Рассмотрим каждую:
- SUM(диапазон) — сумма чисел в диапазоне.
- AVERAGE(диапазон) — среднее арифметическое.
- COUNT(диапазон) — количество ячеек, содержащих числа.
- IF(логическое_выражение; значение_если_истина; значение_если_ложь) — условное вычисление.
- COUNTIF(диапазон; критерий) — количество ячеек, удовлетворяющих условию.
- SUMIF(диапазон; критерий; [диапазон_суммирования]) — сумма ячеек, удовлетворяющих условию.
Важно уметь комбинировать эти функции и правильно задавать критерии (числа, тексты, операторы сравнения).
Дан фрагмент электронной таблицы: A1=5, A2=10, A3=15, B1=20, B2=25, B3=30. В ячейке C1 записана формула =SUM(A1:B2). В ячейке C2 записана формула =AVERAGE(A2:B3). Найдите значение в ячейке C3, если в ней записана формула =IF(C1>C2; C1; C2).
Шаг 1: Вычисляем C1. Диапазон A1:B2 включает ячейки A1, A2, B1, B2. Сумма: 5+10+20+25 = 60.
Шаг 2: Вычисляем C2. Диапазон A2:B3 включает A2, A3, B2, B3. Среднее: (10+15+25+30)/4 = 80/4 = 20.
Шаг 3: Вычисляем C3. Логическое условие: C1>C2? 60>20 — истина. Значит, C3 = C1 = 60.
Ответ: 60.
В таблице указаны продажи товаров: столбец A — товар, столбец B — количество. Товары: 'Яблоки' (5), 'Груши' (8), 'Яблоки' (3), 'Бананы' (10). Необходимо с помощью COUNTIF и SUMIF найти количество продаж товара 'Яблоки' и общее количество проданных яблок. Запишите формулы.
Шаг 1: Для подсчёта количества продаж (число записей) товара 'Яблоки' используем COUNTIF. Диапазон критерия — столбец A (A1:A4), критерий — 'Яблоки'. Формула: =COUNTIF(A1:A4; 'Яблоки'). Результат: 2.
Шаг 2: Для суммы количества проданных яблок используем SUMIF. Диапазон проверки — A1:A4, критерий — 'Яблоки', диапазон суммирования — B1:B4. Формула: =SUMIF(A1:A4; 'Яблоки'; B1:B4). Результат: 5+3=8.
Примечание: в ЕГЭ обычно требуется записать формулы или вычислить значения.
Построение диаграмм
Диаграммы визуализируют данные. На ЕГЭ часто дают готовую таблицу и просят определить тип диаграммы, подобрать диапазон или вычислить значения по диаграмме. Основные типы: столбчатая (гистограмма), круговая, линейная. Важно уметь соотносить данные с элементами диаграммы (категории, ряды, подписи).
При решении задач обращайте внимание на заголовки строк и столбцов, а также на легенду. Часто требуется вычислить сумму или долю, используя данные с диаграммы.
На диаграмме (столбчатой) показаны средние баллы учеников по трём предметам: Математика (80), Русский язык (75), Физика (90). Какая формула в ячейке D1, если в A1='Математика', B1=80, A2='Русский', B2=75, A3='Физика', B3=90, и диаграмма построена по диапазону A1:B3?
Шаг 1: Диаграмма построена по диапазону A1:B3, значит, категории — столбец A (названия предметов), значения — столбец B (баллы).
Шаг 2: Для вычисления среднего балла по всем предметам можно использовать формулу =AVERAGE(B1:B3). Результат: (80+75+90)/3 ≈ 81.67.
Шаг 3: Если требуется формула, которая выводит название предмета с максимальным баллом, можно использовать =INDEX(A1:A3; MATCH(MAX(B1:B3); B1:B3; 0)). Но в данном случае, вероятно, достаточно простой функции.
Круговая диаграмма отображает доли расходов семьи: продукты (40%), коммунальные услуги (25%), транспорт (20%), прочее (15%). Общий бюджет 50 000 руб. Сколько рублей составляют расходы на транспорт?
Шаг 1: Доля транспорта — 20%. Общий бюджет — 50 000 руб.
Шаг 2: Расходы на транспорт = 50 000 * 20% = 50 000 * 0,2 = 10 000 руб.
Ответ: 10 000 руб.
Сортировка и фильтрация
Сортировка упорядочивает данные по возрастанию или убыванию (числа, текст, даты). Фильтрация отображает только строки, удовлетворяющие заданному условию. На ЕГЭ проверяется умение применять эти инструменты для анализа данных.
Типичные задания: отсортировать таблицу по одному или нескольким полям, отфильтровать записи по условию, определить количество записей после фильтрации. Важно понимать, что фильтрация не удаляет строки, а скрывает их.
Дана таблица: Столбец A — Фамилия, B — Город, C — Возраст. Записи: Иванов, Москва, 25; Петров, СПб, 30; Сидоров, Москва, 22; Кузнецов, СПб, 35. После сортировки по возрастанию возраста (столбец C) и фильтрации по городу 'Москва', какие фамилии останутся видимыми?
Шаг 1: Сортируем таблицу по столбцу C (возраст) по возрастанию. Порядок строк: Сидоров (22), Иванов (25), Петров (30), Кузнецов (35).
Шаг 2: Применяем фильтр по столбцу B: отобрать только 'Москва'. Останутся строки, где город Москва: Сидоров (Москва, 22) и Иванов (Москва, 25).
Ответ: Сидоров, Иванов.
В таблице 100 записей. После фильтрации по условию 'Цена > 1000' осталось 30 записей. Если дополнительно отфильтровать по условию 'Количество < 5', осталось 15 записей. Сколько записей было после первой фильтрации, но до второй?
Шаг 1: После первой фильтрации осталось 30 записей. Вторая фильтрация применяется к уже отфильтрованным записям, поэтому после второй фильтрации осталось 15 записей. Количество записей после первой фильтрации не изменилось — 30.
Ответ: 30.
Часто задаваемые вопросы (FAQ)
Частые вопросы
Без карты, без кредитки. Выбери персонажа — учи голосом, побеждай в баттлах.