Excel и LibreOffice Calc для ЕГЭ по информатике — какие функции нужны и где применять

Электронные таблицы на КЕГЭ — это не только задание 9. Excel и LibreOffice Calc закрывают задание 3, ускоряют 18, помогают в 19–21, 22 и 26 и заменяют калькулятор в 7, 8 и 14. Ниже — справочник функций Excel для ЕГЭ по информатике с примерами и привязкой к заданиям, отличия Calc от Excel, типичные ошибки и план тренировки на две недели. Для тех, кто таблицы уже открывал, но пользуется ими «на ощупь».

Навыки, без которых формулы не помогут

Формулу можно подсмотреть в мастере функций. А эти пять приёмов должны быть в руках, иначе задание 9 займёт 15 минут вместо пяти.

Ссылки со знаком $. A1 — относительная, при протягивании двигается. $A$1 — абсолютная, не двигается никогда. $A1 фиксирует столбец, A$1 — строку. В Excel клавиша F4 при курсоре внутри ссылки перебирает все четыре варианта по кругу. В LibreOffice Calc для этого документирована Shift+F4, в свежих версиях работает и F4 — проверь на своей сборке заранее.

Автозаполнение. Тянуть за маркер (квадратик в правом нижнем углу ячейки) на 5 000 строк — плохая идея. Двойной клик по маркеру заполняет формулу вниз до конца соседнего столбца (в Calc проверь, что это работает в твоей версии). Универсальный способ для обеих программ: выделить диапазон и нажать Ctrl+D.

Навигация по большим данным. Ctrl+↓ прыгает к последней заполненной ячейке столбца, Ctrl+Shift+↓ — выделяет до неё. Ctrl+End — в правый нижний угол данных. Так сразу видно, где заканчивается таблица.

Сортировка. Меню «Данные → Сортировка». Обе программы сами расширяют выделение на всю таблицу — проверь галочку «диапазон содержит заголовки», иначе шапка уедет в середину.

Автофильтр. Стрелочки в шапке, отбор по значению или условию. Фильтр только прячет строки — формулы продолжают считать всё.

Действие	Excel	LibreOffice Calc
Переключить тип ссылки	F4	Shift+F4 (в свежих версиях и F4)
Заполнить формулу вниз	двойной клик по маркеру, Ctrl+D	двойной клик по маркеру, Ctrl+D
Выделить до конца данных	Ctrl+Shift+↓	Ctrl+Shift+↓
Последняя ячейка таблицы	Ctrl+End	Ctrl+End
Автофильтр	Ctrl+Shift+L	«Данные → Автофильтр»
Найти и заменить (в формулах тоже)	Ctrl+H	Ctrl+H
Мастер функций	Shift+F3	Ctrl+F2

Справочная таблица функций Excel для ЕГЭ по информатике

Имена — для русской локали, они одинаковы в Excel и Calc. Разделитель аргументов — точка с запятой. Условия в текстовом виде — в кавычках: ">100", "<>0". Если порог лежит в ячейке — склеивай: ">"&$H$1.

Функция	Англ.	Пример	Что делает	Где нужна
СУММ	SUM	=СУММ(B2:B1001)	Сумма диапазона	9, 26
СРЗНАЧ	AVERAGE	=СРЗНАЧ(C2:C1001)	Среднее	9
МАКС / МИН	MAX / MIN	=МАКС(B2:D2)	Наибольшее / наименьшее	9, 18
СЧЁТ	COUNT	=СЧЁТ(A:A)	Сколько числовых ячеек	9, 3
СЧЁТЕСЛИ	COUNTIF	=СЧЁТЕСЛИ(B2:B1001;">50")	Количество по условию	9, 3
СЧЁТЕСЛИМН	COUNTIFS	=СЧЁТЕСЛИМН(B:B;"Москва";C:C;">10")	Количество по нескольким условиям (все сразу)	3, 9
СУММЕСЛИ	SUMIF	=СУММЕСЛИ(A:A;"Чай";C:C)	Сумма по условию	9, 3
СУММЕСЛИМН	SUMIFS	=СУММЕСЛИМН(D:D;A:A;"Чай";B:B;"Тверь")	Сумма по нескольким условиям; диапазон суммы — первым	3, 9
СРЗНАЧЕСЛИ	AVERAGEIF	=СРЗНАЧЕСЛИ(A:A;">0";B:B)	Среднее по условию	9
ЕСЛИ	IF	=ЕСЛИ(B2>$H$1;1;0)	Ветвление, вспомогательный столбец	9, 18, 19–21
И / ИЛИ	AND / OR	=ЕСЛИ(И(B2>0;C2<5);1;0)	Составные условия	9, 19–21
ОСТАТ	MOD	=ОСТАТ(A1;3)	Остаток от деления	14, 25
ЦЕЛОЕ	INT	=ЦЕЛОЕ(A1/3)	Целая часть, для положительных — деление нацело	14
СТЕПЕНЬ	POWER	=СТЕПЕНЬ(2;10) или =2^10	Возведение в степень	7, 8
LOG / LN	LOG / LN	=LOG(1024;2)	Логарифм по основанию / натуральный	7
ОКРУГЛВВЕРХ	ROUNDUP	=ОКРУГЛВВЕРХ(LOG(200;2);0)	Округление вверх до N знаков	7
ДЕС.В.ДВ	DEC2BIN	=ДЕС.В.ДВ(37;8) → 00100101	В двоичную, до 10 разрядов, число от −512 до 511	14
ДВ.В.ДЕС	BIN2DEC	=ДВ.В.ДЕС("100101") → 37	Из двоичной, до 10 знаков	14
ВПР	VLOOKUP	=ВПР(A2;Товар.$A:$C;2;0)	Подтянуть значение по ключу из другой таблицы; в Excel лист пишется Товар!$A:$C	3
ПРОМЕЖУТОЧНЫЕ.ИТОГИ	SUBTOTAL	=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C1001)	Сумма/среднее только по видимым после фильтра строкам	3, 9

Наизусть нужны около десяти: агрегаты и условия из верхней части таблицы плюс ОСТАТ, ЦЕЛОЕ и ВПР. Остальные достаточно находить в мастере функций.

Числа и системы счисления в таблице

Таблица — быстрый калькулятор для заданий 7, 8 и 14, если помнить про подводные камни.

Перевод в любую систему протягиванием. В A1 число, в B1 формула =ОСТАТ(A1;3), в A2 формула =ЦЕЛОЕ(A1/3), в B2 — снова =ОСТАТ(A2;3). Выделяешь A2:B2 и тянешь вниз, пока в столбце A не появится 0 — эта строка уже лишняя. Столбец B — цифры числа в троичной системе, читать снизу вверх. Для 50 в столбце B получится сверху вниз 2, 1, 2, 1, а ответ читается с конца: 1212₃. Меняешь тройку на нужное основание — метод универсальный, а разбор самих задач — в статье про задание 14.

Ограничение ДЕС.В.ДВ. Функция принимает числа только от −512 до 511 и выдаёт не больше 10 двоичных разрядов; ДВ.В.ДЕС тоже читает максимум 10 знаков. Второй аргумент ДЕС.В.ДВ(37;8) — сколько разрядов показать, недостающие добьёт нулями слева. Для больших чисел — способ выше или функции BASE/DECIMAL (в русском Excel — ОСНОВАНИЕ и ДЕС), они работают с основанием от 2 до 36 без ограничения в 10 разрядов.

LOG и плавающая точка. Число бит для кодирования N вариантов — =ОКРУГЛВВЕРХ(LOG(N;2);0). Но логарифм считается приближённо: для больших степеней двойки вместо ровного числа выходит, например, 29,000000000000004 — и ОКРУГЛВВЕРХ даст 30 вместо 29. Проверяй ответ обратной операцией: =СТЕПЕНЬ(2;k) должно быть не меньше N, а =СТЕПЕНЬ(2;k-1) — меньше.

Задание 9 и задание 3: агрегаты и связки таблиц

Здесь электронные таблицы на ЕГЭ по информатике — основной инструмент, а не запасной.

В задании 9 схема одна: вспомогательный столбец с ЕСЛИ (или сразу СЧЁТЕСЛИМН/СУММЕСЛИМН), затем агрегат. Порог, зависящий от другого столбца («больше среднего по всей таблице»), — в отдельной ячейке с абсолютной ссылкой. Пошаговый пример с формулами и типовые формулировки уже разобраны в статье как решать задание 9 — здесь повторяться не буду.

В задании 3 файл, как правило, содержит несколько связанных таблиц: движение товаров, справочник товаров, справочник магазинов. Рабочая связка: автофильтр — посмотреть строки, СЧЁТЕСЛИМН/СУММЕСЛИМН — посчитать без фильтра, ВПР — подтянуть цену или категорию по артикулу. Схема из четырёх шагов, работа со связанными таблицами и разбор задачи — в статье про задание 3.

Общий совет: фильтр — чтобы посмотреть данные, формула — чтобы получить ответ. Смешивать их — самый частый способ получить неверное число.

Задание 18: динамика протягиванием

Задание 18 — робот на сетке, собирающий максимум или минимум монет. Таблица подходит идеально: формулу пишешь один раз, дальше работает протягивание.

Пусть исходная сетка 4×4 лежит в A1:D4, а таблицу динамики строишь правее — в F1:I4. Формулы для движения вправо и вниз, старт в левом верхнем углу:

Ячейка	Формула	Смысл
F1	=A1	Стартовая клетка
G1 (тянуть вправо)	=F1+B1	Первая строка: только слева
F2 (тянуть вниз)	=F1+A2	Первый столбец: только сверху
G2 (растянуть на весь блок)	=B2+МАКС(F2;G1)	Максимум: слева или сверху
То же для минимума	=B2+МИН(F2;G1)	Заменить МАКС на МИН

Ответ — в правом нижнем углу блока. Если спрашивают и максимум, и минимум, скопируй готовый блок ниже и через Ctrl+H замени в нём МАКС на МИН — обе программы ищут и заменяют внутри формул.

Стены и запрещённые клетки. В части вариантов между клетками стоят стены, через которые робот не проходит, либо отдельные клетки закрыты. Стена правится руками: если у клетки стена слева, оставляешь только приход сверху (=B3+G2), если стена сверху — только слева (=B3+F3). Запрещённую клетку проще пометить заведомо плохим числом: для максимума — очень маленьким (скажем, -1000000), для минимума — очень большим, тогда МАКС/МИН её не выберут. Пока таких мест единицы, правка занимает минуту. Если их много или сетка крупная — разумнее написать десяток строк на Python. Разбор обоих подходов с полным примером — в статье про задание 18. Если робот ходит вверх и вправо — разверни направления ссылок.

Задания 19–21, 22 и 26: где таблица тоже уместна

Задания 19–21. Для 19 достаточно столбца позиций и проверки «есть ли выигрыш за один ход». Для типовой кучи с ходами «+1» и «×2»: в A2:A100 значения S, в B2 формула =ЕСЛИ(ИЛИ(A2+1>=$D$1;A2*2>=$D$1);1;0), где D1 — порог выигрыша. Ходы в конкретном варианте бывают другими — подставь свои. Протянул, посмотрел на границу между нулями и единицами. Для 20 и 21 нужен обратный ход по таблице позиций — на Python код короче и понятнее. Сам обратный ход разобран в статье про задание 19.

Задание 22 в формате таблицы процессов. Если попался файл, где у каждого процесса указаны длительность и номера предшественников, время завершения считается формулой «длительность + максимум времён завершения предшественников»: ВПР подтягивает время по номеру, МАКС берёт наибольшее, ответ — МАКС по всему столбцу. Работает, пока предшественников у процесса немного и в файле они стоят выше него: иначе формула сошлётся на ещё не посчитанную ячейку, и надёжнее уйти в Python.

Задание 26. Оно стоит два балла, и там часто нужно отсортировать данные и пройти по ним с накоплением. Сортировка — три клика; накопительная сумма — в C2 пишешь =B2, в C3 — =C2+B3 и тянешь вниз; количество влезших в бюджет — =СЧЁТЕСЛИ(C:C;"<="&$H$1), где в H1 лежит сам бюджет. Пока задача сводится к «отсортируй и накопи», таблица выигрывает; жадный выбор с проверками — уже Python. Оба варианта — в статье про задание 26.

Excel против LibreOffice Calc: что совпадает, что нет

Язык формул общий, различия — в интерфейсе и паре привычек.

Что сравниваем	Excel	LibreOffice Calc
Разделитель аргументов	; в русской локали	;
Имена функций	русские в русской версии	русские в русской локали; есть переключатель «Использовать английские имена функций»
Абсолютные ссылки	$, клавиша F4	$, клавиша Shift+F4 (в свежих версиях и F4)
Протягивание одиночного числа	копирует; ряд 1, 2, 3 — с Ctrl	даёт ряд 1, 2, 3; копирование — с Ctrl
Открытие CSV	иногда портит кодировку и разделитель, лучше «Данные → Из текста»	диалог импорта при открытии: кодировка, разделитель, всё видно
Ссылка на другой лист	Товар!A:C	Товар.A:C
Автофильтр	Ctrl+Shift+L	«Данные → Автофильтр»
Что чаще на ППЭ	иногда	как правило

Переключатель в Calc: «Сервис → Параметры → LibreOffice Calc → Формулы → Использовать английские имена функций». Привык к COUNTIF — включи его сразу. Но русские имена ключевых функций знай наизусть: вдруг в аудитории окажется Excel.

Главный вывод: тренируйся в LibreOffice Calc — он бесплатный и чаще всего стоит на станции КЕГЭ. Что ещё проверить про рабочее место в день экзамена — в чек-листе для КЕГЭ.

Таблица или Python — как выбирать за десять секунд

Спор «Excel или Python» решается не вкусом, а формой задачи. Видишь ответ как одну-две формулы — таблица. Начинаешь думать словами «для каждой строки, если…, то…» и условий больше двух — Python.

Задание	Быстрее в таблице, когда	Быстрее на Python, когда
3	два-три листа, условия простые: фильтр + СЧЁТЕСЛИМН	нужен цикл по нескольким связям, сложная логика
9	агрегат по условию, порог из другого столбца	условие про порядок или уникальность чисел внутри строки
18	сетка до 15×15, стен мало	большая сетка, много стен, особые правила движения
19–21	только 19 (выигрыш за один ход)	20 и 21 — рекурсия по позициям
26	«отсортируй и накопи», данных до нескольких тысяч строк	жадный выбор с проверками, десятки тысяч строк
7, 8, 14	одна формула вместо калькулятора	перебор вариантов

Для сравнения — задание 9 с условием «в скольких строках максимум встречается один раз» на Python — две строки:

rows = [list(map(int, s.split())) for s in open('9.txt')]
print(sum(1 for r in rows if r.count(max(r)) == 1))

В таблице то же условие требует двух вспомогательных столбцов — решаемо, но дольше и с риском ошибиться в диапазоне. Как язык влияет на скорость в остальных заданиях — в статье Python или C++ для ЕГЭ.

Типичные ошибки в электронных таблицах

Ошибки в таблицах коварны: они не дают сообщения, а просто выдают другое число.

• Протянул не тот диапазон. Формула дотянута до строки 1 000, а данных 5 001. Признак: ответ «почти правильный». Лечение — двойной клик по маркеру и проверка Ctrl+End.
• Забыл $. Порог H1 при протягивании превратился в H2, H3, и каждая строка сравнивается с пустой ячейкой. Перед протягиванием спроси про каждую ссылку: «должна ли она ехать?»
• Отфильтровал и написал СУММ. Скрытые строки в фильтре при СУММ и СЧЁТЕСЛИ всё равно считаются. Либо ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; …), либо без фильтра через СУММЕСЛИМН.
• Текстовые числа. После импорта CSV или копирования из условия числа могут стать текстом: выровнены по левому краю, СУММ их игнорирует, СЧЁТЕСЛИ(…;">50") их не видит. Лечение — «Данные → Текст по столбцам» или столбец с =ЗНАЧЕН(A2). Точка вместо запятой в дробной части — та же болезнь.
• Поменял тип данных сам. =ЕСЛИ(B2>10;"1";"0") возвращает текст, и СУММ по этому столбцу даст 0. Возвращай числа без кавычек.
• Захватил заголовок. СРЗНАЧ(A1:A1001) вместо A2:A1001 — среднее «плывёт», СЧЁТ врёт на единицу.
• Экспоненциальная запись. Большое число в узком столбце общего формата показывается как 1,2E+08, и переписать ответ в бланк уже нельзя. Расширь столбец или поставь числовой формат без дробной части.
• Формула из интернета с запятыми. =COUNTIF(A:A,">5") в русской локали не сработает — нужна точка с запятой.

Мини-план тренировки за две недели

План на 30–40 минут в день. Файлы бери из открытого банка ФИПИ и с сайта Полякова — там есть архивы к заданиям 3, 9 и 18. Работай в Calc, даже если дома стоит Excel.

Дни	Что делать	Критерий, что получилось
1–2	Ссылки с $, F4, двойной клик по маркеру, Ctrl+Shift+↓, сортировка, автофильтр — на любом файле	Каждое действие делаешь не глядя на меню
3–4	Задание 9: 10 файлов с секундомером, только СЧЁТЕСЛИ/СУММЕСЛИ/СРЗНАЧЕСЛИ и вспомогательный столбец	8 из 10 верно, каждое до 6 минут
5–6	Задание 3: файлы с несколькими листами, ВПР, СЧЁТЕСЛИМН	Связываешь два листа без подглядывания в справку
7	Контроль: три задания 3 и три задания 9 подряд на время	Укладываешься в 30 минут суммарно
8–9	Задание 18: пять сеток, максимум и минимум, две сетки со стенами	Блок динамики строишь за 5 минут
10	Задания 7, 8, 14 через формулы: LOG, СТЕПЕНЬ, ОСТАТ/ЦЕЛОЕ, ДЕС.В.ДВ	Проверяешь ответ обратной операцией
11–12	Смешанная тренировка: те же задания вперемешку, плюс одна задача 26 через сортировку	Ни разу не открыл справку по функциям
13	Пробник целиком (СтатГрад, ФИПИ или тренажёр с таймером вроде TuteMe), засечь время на 3, 9, 18	Три задания — не больше 20 минут вместе
14	Разбор ошибок пробника, личная шпаргалка из 10–12 формул	Шпаргалка помещается на половину листа

Как проводить пробник в день 13 — в статье как сдавать пробники.

Что взять с собой в голову на экзамен

Короткая версия статьи:

• Наизусть: СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН, СРЗНАЧЕСЛИ, ЕСЛИ, И/ИЛИ, МАКС/МИН, ОСТАТ, ЦЕЛОЕ, ВПР. Остальное — узнавать в мастере функций.
• В руках: $ и F4, двойной клик по маркеру, Ctrl+Shift+↓, Ctrl+End, сортировка, автофильтр, Ctrl+H.
• Задания 9 и 3 — сначала таблица. 18 — таблица при малой сетке. 19–21, 22, 26 — только если формула очевидна. Всё сложное — Python.
• Три проверки перед ответом: диапазон до последней строки, ссылки с $, число не превратилось в текст.
• Тренируйся в LibreOffice Calc: на ППЭ чаще всего он.

Ошибки в таблицах бесшумные, поэтому нужна обратная связь на каждую попытку. В TuteMe задания 3, 9 и 18 идут в формате экзамена, после ответа показывается ход решения, а адаптивный подбор возвращает те типы, где ты ошибаешься; а пробники с таймером на 3 ч 55 мин покажут тайминг.

Попробовать бесплатно →