Электронные таблицы: формулы, ссылки и анализ данных

Инструмент, на котором держится половина мировой аналитики, — и логика, которая работает в Excel, Google Таблицах и любом аналоге.

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

Зачем это нужно

Электронные таблицы — самый распространённый инструмент обработки данных в мире: бюджеты, отчёты, расписания, расчёты. Их сила — в автоматическом пересчёте: меняешь одно число, и связанные формулы обновляются сами. На ЕГЭ задания на таблицы (с относительными и абсолютными ссылками, функциями, сортировкой) — стабильный блок. Мы разберём логику формул глубоко, а чтобы она была не абстрактной, промоделируем поведение таблицы на Python — так станет видно, что именно происходит «под капотом» каждой формулы.

Стоит оценить масштаб явления: электронными таблицами пользуются сотни миллионов людей, многие из которых не считают себя программистами. Бухгалтер, сводящий баланс, учитель, ведущий журнал оценок, владелец маленького магазина, планирующий закупки, — все они, по сути, программируют, даже не подозревая об этом. Каждая формула в ячейке — это маленькая программа, а вся таблица — это сеть автоматически пересчитываемых вычислений. В этом и заключается гениальность изобретения: оно дало мощь вычислений людям без навыков кодинга. Для вас же, изучающих информатику всерьёз, важно увидеть таблицу именно как программу — тогда логика ссылок, функций и условий перестаёт быть набором «кнопок Excel» и становится понятной системой. Именно поэтому мы будем не просто кликать по ячейкам, а моделировать их поведение кодом: это вскрывает суть, которая одинакова в Excel, Google Таблицах, LibreOffice и любом аналоге.

Ячейки, адреса и формулы

Каждая ячейка имеет адрес: буква столбца и номер строки, например B3. В ячейку можно записать число, текст или формулу, начинающуюся со знака «равно»: =A1+B1. Формула ссылается на другие ячейки, и при изменении их значений результат пересчитывается. Промоделируем простую ведомость: количество, цена, и формула стоимости. В нашей модели таблица — это словарь «адрес → значение»:

# модель таблицы: адрес -> значение
sheet = {
    "A1": "Товар", "B1": "Кол-во", "C1": "Цена", "D1": "Сумма",
    "A2": "Тетрадь", "B2": 10, "C2": 40,
    "A3": "Ручка",   "B3": 25, "C3": 25,
    "A4": "Пенал",   "B4": 3,  "C4": 350,
}

# формула D = B * C для строк 2..4
for row in (2, 3, 4):
    sheet[f"D{row}"] = sheet[f"B{row}"] * sheet[f"C{row}"]

print(f"{'Товар':<10}{'Кол-во':>8}{'Цена':>8}{'Сумма':>8}")
for row in (2, 3, 4):
    print(f"{sheet[f'A{row}']:<10}{sheet[f'B{row}']:>8}{sheet[f'C{row}']:>8}{sheet[f'D{row}']:>8}")

Вывод:

Товар       Кол-во    Цена   Сумма
Тетрадь         10      40     400
Ручка           25      25     625
Пенал            3     350    1050

Относительные и абсолютные ссылки — главное различие

Это та тема, на которой спотыкаются чаще всего. Когда вы копируете формулу в другую ячейку, относительная ссылка (B2) сдвигается вместе с ней: скопировали из D2 в D3 — и B2 стала B3. А абсолютная ссылка ($B$2, со знаками доллара) при копировании не меняется. Это нужно, когда формула должна всегда ссылаться на одну и ту же ячейку — например, на курс валюты или ставку налога.

Промоделируем пересчёт цен по общему курсу, который лежит в одной «закреплённой» ячейке. Курс — абсолютная ссылка, цена — относительная:

rate = 90          # курс в «ячейке» B1 — абсолютная ссылка $B$1
prices_usd = [2, 5, 12, 0.5]   # цены в долларах, столбец A

print("цена $  ->  цена в рублях (=A * $B$1)")
for usd in prices_usd:
    rub = usd * rate           # абсолютная ссылка на курс не сдвигается
    print(f"  {usd:>5}  ->  {rub:>8.2f}")

print("\nизменим курс на 100 — все формулы пересчитаются:")
rate = 100
for usd in prices_usd:
    print(f"  {usd:>5}  ->  {usd * rate:>8.2f}")

Вывод:

цена $  ->  цена в рублях (=A * $B$1)
      2  ->    180.00
      5  ->    450.00
     12  ->   1080.00
    0.5  ->     45.00

изменим курс на 100 — все формулы пересчитаются:
      2  ->    200.00
      5  ->    500.00
     12  ->   1200.00
    0.5  ->     50.00

Встроенные функции: SUM, AVERAGE, IF, COUNTIF

Таблицы предлагают сотни функций. Самые ходовые: SUM (сумма диапазона), AVERAGE (среднее), MAX/MIN, COUNT, условная IF и подсчёт по условию COUNTIF. Промоделируем итоги по ведомости оценок — это типовой школьный расчёт:

marks = [5, 4, 3, 5, 2, 5, 4]   # «диапазон» B2:B8

total = sum(marks)                              # =SUM(B2:B8)
average = round(sum(marks) / len(marks), 2)     # =AVERAGE(B2:B8)
top = max(marks)                                # =MAX(B2:B8)
fives = sum(1 for m in marks if m == 5)         # =COUNTIF(B2:B8, 5)
passed = sum(1 for m in marks if m >= 3)        # =COUNTIF(B2:B8, ">=3")

print("сумма (SUM):", total)
print("среднее (AVERAGE):", average)
print("максимум (MAX):", top)
print("пятёрок (COUNTIF =5):", fives)
print("сдали, ≥3 (COUNTIF >=3):", passed)

Вывод:

сумма (SUM): 28
среднее (AVERAGE): 4.0
максимум (MAX): 5
пятёрок (COUNTIF =5): 3
сдали, ≥3 (COUNTIF >=3): 6

Условные вычисления: функция IF

Функция IF(условие; значение_если_да; значение_если_нет) — это ветвление прямо в ячейке. Она автоматизирует решения: «зачёт/незачёт», «скидка для оптовиков», «отметка о просрочке». Промоделируем выставление зачёта по баллу:

scores = [("Анна", 82), ("Борис", 55), ("Вера", 91), ("Глеб", 40)]
threshold = 60   # порог в абсолютной ячейке

print("Имя   | Балл | Результат (=IF(B>=$E$1;\"зачёт\";\"незачёт\"))")
for name, score in scores:
    result = "зачёт" if score >= threshold else "незачёт"
    print(f"{name:<5} | {score:>4} | {result}")

Вывод:

Имя   | Балл | Результат (=IF(B>=$E$1;"зачёт";"незачёт"))
Анна  |   82 | зачёт
Борис |   55 | незачёт
Вера  |   91 | зачёт
Глеб  |   40 | незачёт

Попробуй сам

Соберём мини-отчёт по продажам: для каждого менеджера посчитаем выручку и пометим «план выполнен», если она не ниже цели. Это сразу формулы, условие IF и абсолютная ссылка на цель.

sales = {"Иванов": [120, 80, 200], "Петров": [50, 60, 70], "Сидоров": [300, 150, 90]}
goal = 300   # абсолютная ссылка $B$1

print(f"{'Менеджер':<10}{'Выручка':>9}{'  Статус'}")
for manager, deals in sales.items():
    revenue = sum(deals)                 # =SUM(диапазон сделок)
    status = "план выполнен" if revenue >= goal else "ниже плана"
    print(f"{manager:<10}{revenue:>9}  {status}")

Вывод:

Менеджер   Выручка  Статус
Иванов         400  план выполнен
Петров         180  ниже плана
Сидоров        540  план выполнен

Частые ошибки

Забывают знак доллара в абсолютной ссылке. При копировании формулы ссылка на «общую» ячейку (курс, ставка) сдвинется и сломает расчёт.
Путают порядок аргументов IF. Сначала условие, затем «если истина», затем «если ложь».
Считают среднее как сумму, делённую вручную. Используйте функцию AVERAGE — она сама учтёт число ячеек.
Кавычки у текста в формулах. Текстовые значения и условия типа ">=3" в COUNTIF берут в кавычки.

Итоги

Ячейка хранит значение или формулу (начинается с «=»), которая ссылается на другие ячейки и пересчитывается автоматически.
Относительная ссылка (B2) сдвигается при копировании, абсолютная ($B$2) — нет.
Ходовые функции: SUM, AVERAGE, MAX/MIN, COUNTIF, условная IF.
IF(условие; да; нет) переносит ветвление прямо в ячейку, автоматизируя решения.