Функции и формулы Excel

Введение: Роль табличных процессоров в структуре ЕГЭ

В рамках актуальных контрольно-измерительных материалов (КИМ) ЕГЭ по информатике владение табличными процессорами является фундаментальным навыком. С переходом экзамена в компьютерную форму (КЕГЭ) значимость Excel или его аналогов возросла: теперь это не просто средство для построения графиков, а полноценная вычислительная среда для решения сложных алгоритмических задач.

Знание функций и формул необходимо для успешного выполнения как минимум трех типов заданий:

1. Задание 3: Работа с реляционными таблицами и поиск данных.
2. Задание 9: Обработка числовой информации и проверка сложных логических условий в массивах данных.
3. Задание 18: Решение задач динамического программирования (движение робота в лабиринте).

Умение автоматизировать вычисления позволяет значительно сократить время на решение и исключить человеческий фактор при проведении арифметических расчетов.

Что такое электронные таблицы простыми словами

Excel и LibreOffice Calc - это программы, в которых данные хранятся в ячейках, а формулы связывают эти ячейки между собой. Главное отличие от обычного калькулятора: если ты изменил одно число, все связанные с ним результаты пересчитаются автоматически.

Чтобы успешно решать задачи ЕГЭ, нужно понять три вещи:

•  Как работают ссылки на ячейки при копировании формул
•  Как строить логические условия через ЕСЛИ/И/ИЛИ
•  Как искать и извлекать данные через ВПР

Раздел 1. Типы ссылок  - относительная, абсолютная, смешанная

Когда ты пишешь формулу, например =A1+B1, ты ссылаешься на ячейки. Но что происходит, когда ты копируешь эту формулу в другую ячейку?

1.1 Относительная ссылка - A1
Простыми словами: Excel запоминает не адрес ячейки, а расстояние до неё. Когда копируешь формулу - адрес «едет» вместе с ней.
Пример:  Ячейка B2 содержит формулу: =A1+1

Запомни: относительная ссылка сдвигается ровно на столько, на сколько ты сдвинул саму формулу.

1.2 Абсолютная ссылка - $A$1
Простыми словами: знак $ замораживает адрес. Куда бы ты ни скопировал формулу - она всегда будет смотреть на одну и ту же ячейку.
Пример:  Ячейка B2 содержит формулу: =$A$1+1

Когда использовать: если в формуле есть ячейка с общим коэффициентом (например, курс валюты, налоговая ставка) - всегда делай её абсолютной.

1.3 Смешанная ссылка - $A1 или A$1
Простыми словами: замораживаешь только одну часть адреса - либо столбец, либо строку.

Пример со смешанной ссылкой:

Формула в B2: =$A2 * B$1

При копировании в C3:  =$A3 * C$1

                                               ↑         ↑

                        строка съехала   столбец съехал

                        (A осталась)     (1 осталась)

Где это нужно: таблицы умножения, матрицы пересчёта цен — везде, где строки и столбцы играют разные роли.

Типичная ошибка на ЕГЭ

Забыл поставить $ → при «растягивании» формулы вниз адрес «уехал» не туда.
Всегда проверяй: какие ячейки должны быть фиксированными.

Раздел 2. Логические функции - ЕСЛИ, И, ИЛИ, НЕ

Эти функции используются в задании 9 ЕГЭ. Их задача - проверить условие и вернуть ИСТИНА или ЛОЖЬ (или нужный текст/число).

2.1 Функция ЕСЛИ

=ЕСЛИ(условие; что вывести если ДА; что вывести если НЕТ)

Пример:

=ЕСЛИ(A1>=60; "Зачёт"; "Незачёт")

Если в A1 число 60 или больше - выводится «Зачёт», иначе - «Незачёт».

2.2 Функция И

=И(условие1; условие2; ...)

Возвращает ИСТИНА только если все условия выполнены одновременно.

Пример:

=И(A1>0; A1<100)

ИСТИНА только если A1 больше 0 и меньше 100.

2.3 Функция ИЛИ

=ИЛИ(условие1; условие2; ...)

Возвращает ИСТИНА если хотя бы одно условие выполнено.

Пример:

=ИЛИ(A1<0; A1>100)

ИСТИНА если A1 меньше 0 или больше 100.

2.4 Функция НЕ

=НЕ(условие)

Переворачивает результат: ИСТИНА становится ЛОЖЬЮ и наоборот.

Пример:

=НЕ(A1=0)   →   ИСТИНА, если A1 не равно нулю

2.5 Вложенные логические функции

Функции можно комбинировать. Разберём пример пошагово:

=ЕСЛИ(И(A1>0; B1<100); "Всё хорошо"; "Ошибка")

Шаг 1 - проверяем A1>0 (является ли A1 положительным); 

Шаг 2 - проверяем B1<100 (является ли B1 меньше 100); 

Шаг 3 - функция И(...) проверяет что оба условия верны; 

Шаг 4 - функция ЕСЛИ(...) возвращает «Всё хорошо» если И вернула ИСТИНА, иначе возвращает «Ошибка»
Частая ошибка: написать =ЕСЛИ(A1>0 И B1<100; ...) - так не работает! Функция И пишется отдельно: =ЕСЛИ(И(A1>0; B1<100); ...)

Раздел 3. Статистические функции с условием

Используются в заданиях 3 и 9 для анализа больших таблиц.

3.1 СЧЁТЕСЛИ - считаем ячейки по условию

=СЧЁТЕСЛИ(диапазон; условие)

Пример:

=СЧЁТЕСЛИ(A1:A100; ">50")

Считает, сколько ячеек в диапазоне A1:A100 содержат число больше 50.

Частая ошибка: забыть кавычки вокруг условия. Правильно: ">50", а не >50.

3.2 СЧЁТЕСЛИМН - считаем по нескольким условиям

=СЧЁТЕСЛИМН(диапазон1; условие1; диапазон2; условие2; ...)

Пример:

=СЧЁТЕСЛИМН(A1:A100; ">50"; B1:B100; "<200")

Считает строки, где A больше 50 и одновременно B меньше 200.

3.3 СУММЕСЛИ - суммируем по условию

=СУММЕСЛИ(диапазон; условие; диапазон_суммирования)

Пример:

=СУММЕСЛИ(A1:A100; ">0"; B1:B100)

Складывает значения из столбца B только в тех строках, где в столбце A стоит положительное число.

3.4 Лайфхак: поиск повторов через СЧЁТЕСЛИ

Чтобы проверить, встречается ли число в строке больше одного раза:

=СЧЁТЕСЛИ(A1:D1; A1) > 1

Если результат ИСТИНА - значение из A1 встречается в строке ещё раз.

Раздел 4. Функция ВПР - ищем данные в другой таблице

Используется в задании 3. ВПР = Вертикальный ПРосмотр.

=ВПР(что_ищем; где_ищем; номер_столбца; 0)

Аргументы по-человечески:

«Что ищем» — ID или код из текущей таблицы, пример A2; 

«Где ищем» — таблица со справочником, пример Лист2!$A$1:$C$100; 

«Номер столбца» — из какого столбца взять результат, пример 2; 

«0» — точное совпадение, всегда 0
Пример:

=ВПР(A2; Лист2!$A$1:$C$100; 2; 0)

Разбор пошагово:

1. Берём значение из A2 (например, ID клиента = 105)
2. Ищем 105 в первом столбце диапазона Лист2!$A$1:$C$100
3. Когда нашли - берём значение из 2-го столбца (например, фамилию)
4. Параметр 0 - ищем точное совпадение (не приблизительное)

Важно: диапазон таблицы (Лист2!$A$1:$C$100) должен быть абсолютной ссылкой - иначе при копировании формулы вниз таблица «уедет».

Частая ошибка: искомое значение НЕ находится в первом столбце диапазона. ВПР всегда ищет только по первому столбцу!

Раздел 5. Порядок вычислений в сложных формулах

Excel считает формулу изнутри наружу - сначала самые вложенные функции, потом внешние.

Пример:

=ЕСЛИ(И(A1>0; СЧЁТЕСЛИ(B1:E1; 5)=0); "ОК"; "Ошибка")

ЕСЛИ(И(А1>0; СЧЁТЕСЛИ(B1:E1; 5)=0)) в Excel: 

шаг 1 - СЧЁТЕСЛИ(B1:E1; 5) считает сколько раз встречается 5 в B1:E1; 

шаг 2 - ...=0 проверяет что 5 там нет вообще; 

шаг 3 - A1>0 проверяет что A1 положительное; 

шаг 4 - И(...) проверяет что оба условия верны одновременно; 

шаг 5 - ЕСЛИ(...) возвращает «ОК» если да и «Ошибка» если нет

Практические упражнения с ответами

Упражнение 1. Адресация 

Задание: В ячейке B2 записана формула =$A$1+C2. Как она будет выглядеть после копирования в D4?

Ответ:

=$A$1+E4

Почему:

$A$1 - абсолютная ссылка, не меняется никогда 

C2 - относительная: сдвинулась на +2 вправо (C→E) и +2 вниз (2→4) 

Упражнение 2. Условие треугольника 

Задание: В ячейках A1, B1, C1 - три числа. Напиши формулу, которая выводит «ДА» если из них можно сложить треугольник, и «НЕТ» если нельзя.

Подсказка: треугольник существует если сумма любых двух сторон больше третьей.

Ответ:

=ЕСЛИ(И(A1+B1>C1; A1+C1>B1; B1+C1>A1); "ДА"; "НЕТ")

Упражнение 3. ВПР между листами

Задание: На Листе 1 в ячейке A2 стоит ID клиента. На Листе 2 в столбцах A и B - справочник (ID и Фамилия). Напиши формулу для подстановки фамилии.

Ответ:

=ВПР(A2; Лист2!$A$1:$B$1000; 2; 0)

Абсолютная ссылка здесь - Лист2!$A$1:$B$1000 (диапазон справочника).

Упражнение 4. Сумма с условием

Задание: В столбце A числа от 1 до 1000. Найди сумму чисел, которые кратны 7 и оканчиваются на 3.

Ответ:

=СУММЕСЛИМН(A1:A1000; A1:A1000; "=?*3"; A1:A1000; "<>0")

Более надёжный вариант через СУММПРОИЗВ:

=СУММПРОИЗВ((ОСТАТ(A1:A1000;7)=0) * (ОСТАТ(A1:A1000;10)=3) * A1:A1000)

Разбор:

• ОСТАТ(A1:A1000;7)=0 — число кратно 7
• ОСТАТ(A1:A1000;10)=3 — последняя цифра равна 3
• Перемножаем условия (работают как фильтр) и суммируем

Упражнение 5. Задание 18 - оптимальный путь

Задание: Робот идёт по полю с монетами. Формула максимальной суммы в ячейке B2:

=B2_монеты + МАКС(A2_накопленное; B1_накопленное)

Объяснение:

Робот может прийти в клетку B2 двумя путями:

  ← слева (из A2) — сумма монет A2_накопленное

  ↑ сверху (из B1) — сумма монет B1_накопленное

Выбираем максимум из двух вариантов и добавляем монеты текущей клетки.

Так в каждой ячейке хранится лучший возможный результат к этой точке.

Шпаргалка: частые ошибки на ЕГЭ

Итоги

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

Знание этой темы необходимо по следующим причинам:

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

Мастерство в работе с типами адресации и умение комбинировать вложенные функции позволяют превратить электронную таблицу в мощный инструмент, гарантирующий стабильный результат в заданиях 3, 9 и 18. Регулярная практика на актуальных файлах КИМ является единственным способом довести эти навыки до автоматизма, необходимого в условиях ограниченного времени экзамена.

Три кита успеха в заданиях 3, 9 и 18:

1. Ссылки: понимай разницу между A1, $A$1, $A1 и A$1
2. Логика: умей комбинировать ЕСЛИ + И + ИЛИ + НЕ
3. Поиск: знай синтаксис ВПР и СЧЁТЕСЛИ наизусть