Незнайка
Вся теория
Теория· 6 мин чтения

Электронные таблицы: формулы и функции

Абсолютные и относительные ссылки, копирование формул, функции СУММ, СРЗНАЧ, СЧЁТЕСЛИ, СУММЕСЛИ в электронных таблицах для задач ЕГЭ по информатике.

В этой статье вы сможете узнать:

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

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

Относительная ссылка (например, A1) при копировании формулы в другую ячейку автоматически изменяется на такое же смещение, какое было применено к самой формуле.

Абсолютная ссылка (например, $A$1) при копировании формулы не изменяется — знак доллара «закрепляет» номер столбца и/или строки.

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

Пример. В ячейке B2 записана формула =A2+$A$1. Что получится, если скопировать эту формулу в ячейку B3?

Относительная часть A2 при сдвиге на одну строку вниз превращается в A3, а абсолютная часть $A$1 остаётся неизменной.

Ответ: в ячейке B3 окажется формула =A3+$A$1.

Копирование формулы в несколько направлений

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

Пример. В ячейке B2 формула =$A2*B$1. Что получится при копировании в ячейку C3?

Часть $A2 — столбец A закреплён, а строка меняется при сдвиге вниз на 1: 2 → 3, получаем $A3.

Часть B$1 — строка 1 закреплена, а столбец меняется при сдвиге вправо на 1: B → C, получаем C$1.

Ответ: в ячейке C3 окажется формула =$A3*C$1.

Основные встроенные функции

СУММ — суммирует значения в указанном диапазоне ячеек:

=СУММ(A1:A10)

СРЗНАЧ — вычисляет среднее арифметическое значений в диапазоне:

=СРЗНАЧ(A1:A10)

СЧЁТ — подсчитывает количество ячеек с числовыми значениями в диапазоне:

=СЧЁТ(A1:A10)

СЧЁТЕСЛИ — подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию:

=СЧЁТЕСЛИ(A1:A10, ">5")

СУММЕСЛИ — функция принимает три аргумента: диапазон, который проверяется на условие; само условие; и диапазон, значения которого суммируются. Функция проходит по первому диапазону ячейка за ячейкой, и как только очередная ячейка удовлетворяет условию, в сумму добавляется значение ячейки на той же позиции, но уже во втором (суммируемом) диапазоне:

=СУММЕСЛИ(A1:A10, ">5", B1:B10)

Здесь для каждой ячейки A1, A2, …, A10 проверяется, больше ли она 5; если условие выполняется для, скажем, A3, то к сумме прибавляется значение именно из B3 (той же по счёту ячейки в диапазоне B1:B10), а не из A3. Если диапазон условия и диапазон суммирования совпадают (например, =СУММЕСЛИ(A1:A10, ">5", A1:A10) или сокращённо =СУММЕСЛИ(A1:A10, ">5")), то суммируются сами значения, удовлетворяющие условию.

ЕСЛИ — возвращает одно значение, если условие истинно, и другое, если условие ложно:

=ЕСЛИ(A1>0, "Положительное", "Неположительное")

Разбор типовой задачи с диапазоном ячеек

Пример. В ячейках A1:A6 записаны числа: 3, 7, 2, 9, 4, 6. Какое значение вернёт формула =СЧЁТЕСЛИ(A1:A6, ">4")?

Проверяем каждое значение: 3≤43\leq4 (нет), 7>47>4 (да), 2≤42\leq4 (нет), 9>49>4 (да), 4≤44\leq4 (нет), 6>46>4 (да) — всего 3 значения больше 4.

Ответ: 3.

Пример. Для тех же данных в A1:A6 найдите значение формулы =СУММЕСЛИ(A1:A6, ">4").

Суммируем значения, большие 4: 7+9+6=227+9+6=22.

Ответ: 22.

Алгоритм разбора формул электронных таблиц

  1. Определите, какая ссылка используется в формуле — относительная, абсолютная или смешанная, и как записан знак доллара.
  2. Если формулу нужно скопировать в другую ячейку, определите направление и величину сдвига (на сколько строк и столбцов сместилась целевая ячейка относительно исходной).
  3. Измените относительные части адреса на величину сдвига, а абсолютные (и закреплённые части смешанных ссылок) оставьте без изменений.
  4. Если в формуле используется встроенная функция — определите диапазон, к которому она применяется, и условие (если функция условная, как СЧЁТЕСЛИ или СУММЕСЛИ).
  5. Вычислите результат функции, применив условие к каждому значению диапазона по очереди.

Примеры решения

Пример 1. В ячейке C1 формула =A1+B$1. Что получится при копировании в ячейку D2?

Часть A1 — обе координаты относительные, сдвиг на 1 столбец вправо и 1 строку вниз: A1 → B2.

Часть B$1 — строка закреплена, столбец меняется: B$1 → C$1.

Ответ: =B2+C$1.

Пример 2. В ячейках A1:A5 записаны числа 10, 15, 8, 20, 12. Какое значение вернёт =СРЗНАЧ(A1:A5)?

10+15+8+20+125=655=13\dfrac{10+15+8+20+12}{5} = \dfrac{65}{5} = 13

Ответ: 13.

Пример 3. В ячейке B1 формула =$A1*2, скопирована в диапазон B1:B3. Значения в A1:A3: 4, 5, 6. Какие значения получатся в B1:B3?

Столбец A закреплён, строка меняется соответственно строке формулы: B1 содержит =$A1*2=8, B2 содержит =$A2*2=10, B3 содержит =$A3*2=12.

Ответ: 8, 10, 12.

Часто задаваемые вопросы

Зачем нужны абсолютные ссылки, если можно везде использовать относительные? Абсолютные ссылки нужны, когда формула должна всегда обращаться к одной и той же ячейке (например, к общему коэффициенту или курсу валют), независимо от того, в какую ячейку эта формула скопирована, — без знака доллара такая ссылка при копировании сместилась бы вместе с остальными.

Как запомнить, какая часть смешанной ссылки закреплена? Знак доллара $ фиксирует то, что стоит сразу после него: в $A1 закреплён столбец (буква), а строка (число) остаётся относительной; в A$1 закреплена строка, а столбец — относительный.

Чем СЧЁТЕСЛИ отличается от СУММЕСЛИ? СЧЁТЕСЛИ подсчитывает количество ячеек, удовлетворяющих условию, а СУММЕСЛИ суммирует значения из одного диапазона, но проверяет условие по другому (или тому же) диапазону — то есть СЧЁТЕСЛИ отвечает на вопрос «сколько», а СУММЕСЛИ — на вопрос «сколько в сумме».

Может ли функция СЧЁТ учитывать текстовые значения в диапазоне? Нет, функция СЧЁТ учитывает только ячейки с числовыми значениями; для подсчёта всех непустых ячеек (включая текст) используется отдельная функция СЧЁТЗ.

Похожие темы для изучения

  • Массивы: обработка и типовые алгоритмы (Python)
  • Основы программирования на Python: переменные, ветвления, циклы
  • Логические операции и таблицы истинности
  • Кодирование и измерение информации

Задачи для самостоятельного решения

  1. В ячейке D5 формула =$B$2+C5. Что получится при копировании этой формулы в ячейку E6?
  2. В ячейках A1:A5 записаны числа 6, 12, 3, 9, 15. Найдите значение =СЧЁТЕСЛИ(A1:A5, "<10").
  3. В ячейках A1:A5 те же числа (6, 12, 3, 9, 15). Найдите значение =СУММЕСЛИ(A1:A5, ">=10").
  4. В ячейке B2 формула =A$2*$A2, скопирована в ячейку C3. Запишите получившуюся формулу.
  5. Найдите значение =СРЗНАЧ(A1:A6), если в ячейках записаны числа 4, 8, 2, 6, 10, 6.
  6. Объясните, почему при копировании формулы с абсолютной ссылкой $A$1 в любую другую ячейку эта часть формулы никогда не меняется.