Формулы в openpyxl: записываем, но не считаем

У прайса появилась строка «Итого», и первый порыв — записать туда формулу, как в Excel: =SUM(B2:B6). Так и надо сделать. Но сразу договоримся о главном: openpyxl формулы записывает, а не считает. Это одна библиотека, у которой нет калькулятора внутри, и сегодня мы разберём, что это значит на практике — и почему это всё равно ровно то, что нужно.

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

Почему так устроено? Формат xlsx хранит формулу в ячейке без обязательного результата: файл описывает таблицу, а не снимок вычислений. Это экономит место и гарантирует, что после правки любой ячейки всё пересчитается заново — при следующем открытии. Решение считать самому openpyxl означало бы тащить в библиотеку целый вычислитель Excel со всеми функциями, приоритетами операций и особенностями — поэтому границу провели честно: запись и чтение — openpyxl, вычисление — табличный движок.

• скрипт записал формулу — строка легла в ячейку файла;
• wb.save упаковал книгу в xlsx — формула внутри, результата рядом нет;
• человек открывает файл — Excel находит формулу и вычисляет её;
• пользователь меняет цену — Excel пересчитывает всё, что от неё зависит;
• пересохранение записывает в файл свежие вычисленные значения — они пригодятся в следующем уроке.

Формула — это строка со знаком равенства

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

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "Товар"
ws["B1"] = "Цена"
price = [("Эспрессо", 120), ("Капучино", 250), ("Латте", 280)]
for i, (name, p) in enumerate(price, start=2):
    ws.cell(row=i, column=1, value=name)
    ws.cell(row=i, column=2, value=p)

ws["B5"] = "=SUM(B2:B4)"      # пишем формулу как строку

cell = ws["B5"]
print(cell.value)
print(type(cell.value).__name__)

=SUM(B2:B4)
str

Вот оно, поведение урока: cell.value вернул =SUM(B2:B4) — саму формулу, а не 650. Для Python это просто строка: сложи cell.value с числом, и получишь TypeError. Именно так ячейка выглядит для любого скрипта на openpyxl — до тех пор, пока файл не открыл Excel.

Проверим, что формула честно доехала до файла и осталась формулой:

from openpyxl import Workbook, load_workbook
import os

NAME = "price_formula.xlsx"

wb = Workbook()
ws = wb.active
ws["A1"] = 100
ws["A2"] = 200
ws["A3"] = "=SUM(A1:A2)"

wb.save(NAME)

wb2 = load_workbook(NAME)
print(wb2.active["A3"].value)

os.remove(NAME)

=SUM(A1:A2)

В файле лежит формула — читается она формулой же. Как из файла достать уже посчитанное число, разберём в следующем уроке про data_only.

Ту же проверку полезно делать после каждого изменения: записал формулу — прочитай файл обратно и убедись, что строка доехала без искажений. Ошибки тут коварны тем, что openpyxl ничего не валидирует: опечатка в имени функции, забытая скобка или запятая вместо точки с запятой уедут в файл молча и выстрелят уже в Excel — у пользователя, который не сможет понять, что сломалось и почему. Скрипт, который сам себе приёмщик, экономит час переписки.

Формулы прайса: SUM, AVERAGE и IF

Синтаксис формул — обычный Excel, но с одним правилом: имена функций в файле всегда английские. Excel показывает =СУММ(...) только в русском интерфейсе — внутри xlsx хранится SUM. openpyxl пишет в файл ровно то, что ты дал, поэтому пиши SUM, AVERAGE, IF — даже если в твоём Excel они по-русски.

• SUM — сумма диапазона; главный гость итоговых строк;
• AVERAGE — среднее арифметическое; для средних цен и чеков;
• IF — условие с двумя ветками; пометки «дорогой/дешёвый», «хватает/заказать»;
• COUNT — количество чисел в диапазоне; пустые и текстовые ячейки не считаются;
• MIN и MAX — крайние значения; самая дешёвая и самая дорогая позиция прайса.

from openpyxl import Workbook
import os

NAME = "price_totals.xlsx"

wb = Workbook()
ws = wb.active
ws.append(["Товар", "Цена", "Кол-во", "Сумма"])
rows = [("Эспрессо", 120, 40), ("Капучино", 250, 25), ("Латте", 280, 30)]
for r in rows:
    ws.append(r)

last = ws.max_row
for i in range(2, last + 1):
    ws.cell(row=i, column=4, value=f"=B{i}*C{i}")

total_row = last + 1
ws.cell(row=total_row, column=3, value="Итого:")
ws.cell(row=total_row, column=4, value=f"=SUM(D2:D{last})")

wb.save(NAME)

for i in range(2, total_row + 1):
    print(ws.cell(row=i, column=4).value)

os.remove(NAME)

=B2*C2
=B3*C3
=B4*C4
=SUM(D2:D4)

Обрати внимание на f-строки: f"=B{i}*C{i}" подставляет номер строки в формулу. Это главный приём генерации формул из кода — диапазоны собираются динамически, как только данные разрастаются. Excel при открытии посчитает: 4800, 6250, 8400 и итог 19450.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "=AVERAGE(B2:B6)"
ws["A2"] = '=IF(B2>200,"дорогой","дешёвый")'

print(ws["A1"].value)
print(ws["A2"].value)

=AVERAGE(B2:B6)
=IF(B2>200,"дорогой","дешёвый")

IF с текстовыми ветками — обычное дело в шаблонах: Excel посчитает и покажет «дорогой» или «дешёвый». Обрати внимание на кавычки: внутри формулы двойные, поэтому Python-строку взяли в одиночные. Склеивать русские имена функций не пробуй — сейчас объясню, чем это кончается.

MIN и MAX дополняют набор: крайние цены прайса — частый гость в отчётах. Обрати внимание, как диапазон собрался f-строкой из ws.max_row — формулы подстраиваются под объём данных сами:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
prices = [120, 250, 280, 320, 260]
for i, p in enumerate(prices, start=2):
    ws.cell(row=i, column=1, value=p)
last = ws.max_row
ws.cell(row=last + 1, column=1, value=f"=MIN(A2:A{last})")
ws.cell(row=last + 2, column=1, value=f"=MAX(A2:A{last})")
print(ws.cell(row=last + 1, column=1).value)
print(ws.cell(row=last + 2, column=1).value)

=MIN(A2:A6)
=MAX(A2:A6)

Формула может ссылаться и на одну ячейку — так в прайс добавляют производные цены: скидка, наценка, пересчёт валюты. Excel при открытии возьмёт значение B1 и умножит:

Ссылки в формулах бывают относительные и абсолютные. Запись B1 при протягивании формулы вниз превращается в B2, B3 — Excel двигает ссылку вместе с формулой. Знаки доллара это запрещают: $B$1 всегда указывает на одну и ту же ячейку, что бы ни делали с остальными. Если в шаблоне курс доллара или коэффициент наценки лежит в одной ячейке — пиши её с долларами: =B2*$B$1. openpyxl передаёт ссылку в файл буквально, никаких преобразований не происходит.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws["A1"] = "Цена"
ws["B1"] = 250
ws["A2"] = "Цена со скидкой 10%"
ws["B2"] = "=B1*0.9"

print(ws["B2"].value)

=B1*0.9

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "SUM(B2:B6)"     # забыли "=" - это текст
ws["A2"] = "=SUM(B2:B6)"    # а это формула

print("A1 ->", ws["A1"].value)
print("A2 ->", ws["A2"].value)

A1 -> SUM(B2:B6)
A2 -> =SUM(B2:B6)

Диапазон формулы может лежать и на другом листе — синтаксис требует имя листа с восклицательным знаком: =SUM(Sales!B2:B10) посчитает продажи со листа Sales, находясь на любом другом. Так собираются книги-отчёты: листы с сырыми данными, а сверху — сводный лист с формулами на соседей. openpyxl без разницы, куда указывает формула, — строка уедет в файл в первозданном виде.

И не путай проверку синтаксиса с проверкой смысла: openpyxl спокойно сохранит =SUM(B2:B4 с незакрытой скобкой, а Excel при открытии попытается починить или предложит выбрать файл — диалог, которого пользователь не заслуживал. Скобки, кавычки внутри IF и английские имена функций — три места, где генерация формул ошибается чаще всего. Локальная проверка строки перед записью стоит строки кода и снимает все три.

Зачем формулы, если Python умеет считать сам?

Честный вопрос: sum([120, 250, 280]) в Python даст 650 быстрее, чем любая формула. Но прайс — не статичный отчёт, а живой документ: менеджер поменяет цену, бухгалтер допишет позицию — и формулы пересчитаются сами. Python-скрипт собирает каркас: данные, форматы, готовые формулы в итогах. Люди дальше работают мышкой — и каркас отвечает им числами.

from openpyxl import Workbook
import os

NAME = "shift_template.xlsx"

wb = Workbook()
ws = wb.active
ws.append(["Час", "Чашек"])

for hour in range(8, 12):          # строки для заполнения в Excel
    ws.append([f"{hour}:00", None])

last = ws.max_row
ws.cell(row=last + 1, column=1, value="Итого:")
ws.cell(row=last + 1, column=2, value=f"=SUM(B2:B{last})")

wb.save(NAME)

for row in ws.iter_rows(min_row=2, max_row=last + 1):
    print(row[0].value, "|", row[1].value)

os.remove(NAME)

8:00 | None
9:00 | None
10:00 | None
11:00 | None
Итого: | =SUM(B2:B5)

Шаблон раздал пустые ячейки и уже вставил итог — менеджер заполняет цифры по часам, сумма появляется сама. Это классическое разделение труда: код готовит структуру, Excel считает. Сценарии, где логика сложнее пары формул (API, проверки, отчёты), живут уже на стороне Python — как Pydantic-модели в FastAPI описывают данные вместо формул.

И ещё одно соображение в пользу формул в шаблонах — доверие. Бухгалтер, открывший твой файл, может кликнуть на итог и увидеть формулу: число не из воздуха, а честная сумма видимых строк. Жёстко вбитое из Python значение такой прозрачности не даёт — оно просто верно, пока никто не поменял данные выше. Для живых документов прозрачность расчёта стоит не меньше правильности.

Что дальше

Ты записал настоящие формулы и понял контракт openpyxl: запись — да, вычисление — нет. Но файлы Excel, сохранённые людьми, хранят не только формулы, но и последние посчитанные значения — и их можно прочитать. В следующем уроке включаем data_only=True и вытаскиваем числа; а про то, почему пустые даты-строки ломают сортировку, мы предупреждали ещё в уроке про даты.

openpyxl готовит каркас, Excel его оживляет: код пишет формулы, а табличный движок считает.