Данные и таблицы

Добавить формулы в Excel

Дописывает в xlsx строки с суммами, средним, количеством, минимумом и максимумом по колонке.

Python Windows macOS Linux Средний
Скачать add_formulas.py
Описание

Что делает скрипт

Скрипт открывает книгу через openpyxl, находит последнюю строку данных и добавляет ниже блок формул. Колонка задаётся буквой Excel, диапазон начинается со строки 2 по умолчанию. Формулы сохраняются как настоящие формулы Excel, а не как готовые числа. Исходная книга не изменяется.

Перед запуском
  • Формулы вычисляются при открытии в Excel или LibreOffice. openpyxl не считает их сам.
Запуск

Быстрый старт

python add_formulas.py --input report.xlsx --column D --output report_total.xlsx

Скачайте файл, откройте терминал в папке со скриптом и выполните команду. В Linux и macOS интерпретатор может называться python3, в Windows - python или py.

Примеры

Другие варианты

  • python add_formulas.py --input sales.xlsx --column C --sheet Данные
  • python add_formulas.py --input report.xlsx --column B --formulas sum,average --force
Зависимости

Что понадобится

Python 3.10+ Только стандартная библиотека
Windows winget install Python.Python.3.12
macOS brew install python
Linux sudo apt install python3
openpyxl Чтение и запись xlsx
Windows py -m pip install openpyxl
macOS python3 -m pip install openpyxl
Linux python3 -m pip install openpyxl
Исходник

add_formulas.py

#!/usr/bin/env python3
"""Добавление итоговых формул в xlsx."""
from __future__ import annotations

import argparse
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.utils import column_index_from_string
from openpyxl.utils.exceptions import InvalidFileException

LABELS = {
    "sum": "Итого",
    "average": "Среднее",
    "count": "Количество",
    "min": "Минимум",
    "max": "Максимум",
}
FORMULAS = {
    "sum": "SUM",
    "average": "AVERAGE",
    "count": "COUNT",
    "min": "MIN",
    "max": "MAX",
}


def main() -> int:
    parser = argparse.ArgumentParser(description="Добавить формулы в Excel")
    parser.add_argument("--input", type=Path, required=True, help="Исходный xlsx")
    parser.add_argument("--output", type=Path, help="Результат, по умолчанию <имя>_formulas.xlsx")
    parser.add_argument("--sheet", default="", help="Имя листа, по умолчанию активный")
    parser.add_argument("--column", required=True, help="Буква колонки, например D")
    parser.add_argument("--start-row", type=int, default=2, help="Первая строка данных")
    parser.add_argument("--formulas", default="sum,average,count,min,max", help="Список формул через запятую")
    parser.add_argument("--force", action="store_true", help="Перезаписать выходной файл")
    args = parser.parse_args()

    source = args.input.expanduser().resolve()
    if not source.is_file():
        parser.error(f"Файл не найден: {source}")
    target = (args.output or source.with_name(f"{source.stem}_formulas.xlsx")).expanduser().resolve()
    if source == target:
        parser.error("Выходной файл не должен совпадать с исходным")
    if target.exists() and not args.force:
        parser.error(f"Файл уже существует: {target}. Добавьте --force")
    if args.start_row < 1:
        parser.error("--start-row должен быть положительным")
    try:
        column_index = column_index_from_string(args.column.strip().upper())
    except ValueError:
        parser.error(f"Неверная буква колонки: {args.column}")

    requested = [item.strip().lower() for item in args.formulas.split(",") if item.strip()]
    unknown = [item for item in requested if item not in FORMULAS]
    if unknown:
        parser.error("Неизвестные формулы: " + ", ".join(unknown))

    try:
        workbook = load_workbook(source)
    except (OSError, InvalidFileException) as error:
        parser.error(f"Не удалось открыть книгу: {error}")

    if args.sheet:
        if args.sheet not in workbook.sheetnames:
            parser.error(f"Нет листа: {args.sheet}")
        worksheet = workbook[args.sheet]
    else:
        worksheet = workbook.active

    last_row = worksheet.max_row
    if last_row < args.start_row:
        parser.error("В листе нет строк данных")
    letter = args.column.strip().upper()
    data_range = f"{letter}{args.start_row}:{letter}{last_row}"
    first_output = last_row + 2

    for offset, name in enumerate(requested):
        row_number = first_output + offset
        label_cell = worksheet.cell(row=row_number, column=1, value=LABELS[name])
        label_cell.font = Font(bold=True)
        formula = f"={FORMULAS[name]}({data_range})"
        value_cell = worksheet.cell(row=row_number, column=column_index, value=formula)
        value_cell.number_format = "0" if name == "count" else "#,##0.00"

    target.parent.mkdir(parents=True, exist_ok=True)
    workbook.save(target)
    print(f"Лист: {worksheet.title}")
    print(f"Диапазон: {data_range}")
    print(f"Формул добавлено: {len(requested)}")
    print(f"Результат: {target}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

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

CLI 1.0.0

Скачать без браузера

Команда скачает этот файл в текущую папку. CLI работает на Windows, macOS и Linux.

scriptdock get data-excel-formulas
  • Поиск по всему каталогу
  • Python 3.10+
  • Без внешних пакетов
Скачать CLI

GUI-версия готовится. Подробнее

Категория

Данные и таблицы

CSV, JSON, SQLite, Excel, очистка, объединение и отчёты.