Формулы в электронных таблицах: интенсив для ОГЭ и база для программирования

Зачем учить формулы, а не кликать мышкой

** изображение создано или обработано с помощью ИИ.

Представьте, что мы сидим на кухне с ноутбуками. Мне 27, я сам когда-то путался в долларах, диапазонах и скобках. Сейчас объясняю школьникам и вижу: формулы пугают ровно до первой нормальной тренировки.

Логика формул, абсолютные ссылки и условные функции — это база для алгоритмического мышления, программирования и работы с данными. Эти навыки напрямую проверяются в ОГЭ.

В задачах, где используются таблицы, проверяют не любовь к Excel. Проверяют логику, внимательность и умение читать условие. Таблица — это просто ускоритель счёта. Если вы поняли идею, программа превращается в калькулятор на стероидах.

Главная ошибка новичка — щёлкать по ячейкам без плана. Что-то посчиталось, ответ не сошёлся, а где промах — непонятно. Я называю это режимом «авось прокатит». На экзамене он живёт недолго.

Лучше действовать так:

1. Читаете условие.

1. Решаете, какие строки и столбцы нужны.

1. Выбираете функцию.

1. Вводите формулу.

Звучит скучно. Зато нервы остаются при вас.

— Я просто протяну формулу вниз?

— Протянешь. Но сначала проверь, какие ссылки должны двигаться.

— А если не проверю?

— Тогда Excel станет генератором сюрпризов. Весёлым, но злым.

Формулы хороши тем, что повторяют действия без усталости. Человек на двадцатой строке начинает зевать и ошибаться. Таблица — нет. Поэтому наша задача простая: научиться давать ей точные команды с первого раза.

Базовый набор функций для экзамена

** изображение создано или обработано с помощью ИИ.

На интенсиве я начинаю не с функций. Сначала разбираем язык таблицы. Ячейка A1, диапазон A1:C10, строка, столбец, лист — это алфавит. Без него любая функция выглядит как заклинание из дешёвого фэнтези.

Дальше — короткая схема. Каждая формула начинается со знака равенства. Аргументы — в скобках. Диапазоны — через двоеточие.

В русских версиях табличных процессоров (Excel, LibreOffice Calc) функции называются СУММ, СРЗНАЧ. На экзамене можно использовать любой редактор, но проверьте синтаксис (разделитель аргументов — точка с запятой или запятая) заранее.

Я не советую заучивать все функции подряд. Это как учить словарь, чтобы заказать чай. Для экзамена важнее уверенно владеть базовым набором. Потом, если останется время, можно добавить редкие приёмы. Пять вопросов, которые я задаю к любой задаче:

1. Что именно нужно найти?

1. Где лежат исходные данные?

1. Какие строки надо отобрать?

1. Какую функцию удобно применить?

1. Как проверить результат на здравый смысл?

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

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

Когда рядом есть преподаватель, который видит вашу ошибку до того, как вы успели в ней увязнуть. Курс не заменит вашу голову, но сэкономит время, которое можно потратить на сон или повторение других тем.

Относительные и абсолютные ссылки: как работают доллары

** изображение создано или обработано с помощью ИИ.

Относительная ссылка изменяется при копировании. Формула в B2 ссылается на A2. Тянете вниз — получаете A3, A4 и так дальше. Всё едет.

Абсолютная ссылка фиксирует адрес. В Excel перед буквой и цифрой ставят знак доллара: $A$1. Такая ячейка не двигается при копировании. Удобно для коэффициента, порога или общей настройки.

Есть ещё смешанные: $A1 — заморожен только столбец, строка едет. A$1 — заморожена только строка, столбец едет. Мелочь, но именно на ней ученики теряют баллы. Один неверный доллар — и вся колонка считает мимо. Как я объясняю через наклейки:

1. Относительная ссылка — стикер на рюкзаке. Рюкзак поехал, стикер поехал.

1. Абсолютная ссылка — табличка на двери кабинета. Вы ходите вокруг, она висит на месте.

Мини-проверка для себя. Сделайте маленькую таблицу на три строки. В одной ячейке напишите 10. В соседнем столбце умножьте каждую строку на эту десятку. Протяните формулу вниз. Если ссылка на 10 тоже поехала вниз, значит, там не хватает доллара.

Про диапазоны:

1. A1:A10 — один столбец из десяти строк.

1. A1:C1 — одна строка из трёх ячеек.

1. A1:C10 — прямоугольник 10×3.

Если функция выдаёт странный результат, первым делом проверьте, ту ли область вы выделили. Ошибка часто сидит именно там.

Мой приём: уменьшите задачу. Возьмите три строки вместо ста. Проверьте формулу глазами. Если работает на маленьком примере — масштабировать уже не страшно.

Функции, которые реально пригодятся

** изображение создано или обработано с помощью ИИ.

СУММ складывает числа в диапазоне. База, но база не значит «слишком просто». В задачах сумма часто появляется после отбора строк. Сначала поймите, что именно нужно складывать, потом подставляйте диапазон.

СРЗНАЧ считает среднее арифметическое. Следите за пустыми ячейками и текстом — в разных редакторах они обрабатываются по-разному. Проверьте поведение в своей версии до экзамена, а не во время.

МИН и МАКС находят крайние значения. Отличные функции для быстрой проверки. Если ваш ответ не помещается между минимумом и максимумом — ищите ошибку. Она есть.

ЕСЛИ строит условия. Формула читается так: если условие верно — верни одно значение, если нет — другое. Пример: =ЕСЛИ(A1>10; 1; 0). Синтаксис зависит от редактора и настроек (точка с запятой или запятая), проверьте заранее.

СЧЁТЕСЛИ и СУММЕСЛИ — для отбора по условию. Первая считает подходящие ячейки. Вторая суммирует значения, которые подходят под критерий. Экономят время, когда в таблице сотни строк.

— Можно через фильтр?

— Можно, если задача позволяет. Но формулу легче проверить постороннему глазу.

Фильтр удобен для глаз. Формула удобна для повторения и контроля. В экзаменационной суете лучше иметь оба варианта. Но формулы дают больше уверенности — вы видите логику, а не просто результат.

Не гонитесь за сложностью. Если задачу можно решить двумя простыми столбцами, то решайте двумя. Красивые монстры из вложенных функций хороши для самооценки. Для баллов важнее надёжность.

Ловушки, из-за которых ответ уезжает

** изображение создано или обработано с помощью ИИ.

Первая ловушка — неверный тип данных. Число выглядит как число, но хранится как текст. Тогда СУММ или СРЗНАЧ ведут себя странно. Проверьте выравнивание (числа обычно прижаты вправо, текст — влево) и исходный формат ячеек.

Вторая ловушка — лишняя строка в диапазоне. Захватили заголовок с текстом. Или забыли последнюю строку с данными. На маленьких таблицах ошибку видно сразу. На больших — перепроверяйте адреса вручную, не полагайтесь на глаз.

Третья ловушка — округление. Если в ответе просят целое число, не округляйте «на глаз». Используйте штатные функции редактора или следуйте тому, что написано в условии. В методических материалах ОГЭ формулировки обычно подсказывают нужный способ.

Четвёртая ловушка — копирование без проверки. Формула в первой строке идеальна. В десятой она уже ссылается в пустоту. Протянули — проверьте хотя бы две-три строки в разных местах таблицы.

Пятая ловушка — невнимательное чтение условия. «Больше» и «не меньше» — разные вещи. «Не превышает» — не равно «меньше». Я однажды сам прочитал условие неверно на разборе. Группа была счастлива. Учитель — тоже человек.

Мой приём. Перепишите условие обычными словами. Пример: «нужно посчитать строки, где балл не меньше 70». Потом эту фразу превращаете в формулу. Мозг спотыкается меньше.

Ещё совет. Держите черновик рядом с таблицей. Запишите, что означает каждый вспомогательный столбец. Через десять минут вы скажете себе спасибо.

Мини-практикум: тренировка за 40 минут

** изображение создано или обработано с помощью ИИ.

Соберём тренировку. Не нужно сидеть пять часов подряд. Лучше 40 минут с полной концентрацией, чем вечер в режиме «я герой, но всё смешалось». Поставьте таймер и откройте пустую таблицу.

Сделайте маленький набор данных на десять строк: имена, баллы, город, статус. Данные можно придумать самим для тренировки. Экзаменационные варианты для этой темы берите на сайте ФИПИ: https://fipi.ru → раздел «ОГЭ» → открытые задания.

Задание первое. Найдите сумму баллов. Посчитайте среднее. Найдите максимум и минимум. Проверьте результаты глазами на вашей маленькой таблице — они должны быть похожи на правду.

Задание второе. Добавьте столбец «зачёт». Пусть он выдаёт 1, если балл не ниже выбранного порога, и 0 — если ниже. Порог вынесите в отдельную ячейку. Ссылку на него зафиксируйте знаком доллара, чтобы при копировании она не уехала.

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

Задание четвёртое. Отфильтруйте один город вручную. Сравните результат фильтра с тем, что дала формула подсчёта по условию. Это лучший способ заметить расхождение.

После практики честно ответьте себе:

1. Я понимаю, какие ссылки двигаются, а какие стоят на месте?

1. Я умею фиксировать важную ячейку одним долларом или двумя?

1. Я проверяю диапазон перед тем, как сказать «готово»?

1. Я могу объяснить свою формулу вслух простыми словами?

Если на последний вопрос ответ «нет» — формулу рано считать освоенной. Объяснение вслух мгновенно выявляет туман. Я сам так проверяю свои разборы. Иногда разговариваю с монитором. Монитор пока терпит.

Главный совет. Тренируйте не память, а порядок: условие → диапазон → функция → проверка. Маршрут скучный только на вид. На экзамене он работает спокойно, без фокусов и без лишних сюрпризов.