0 просмотренных постов скрыто
Автоматизация создания счетов-фактур в Python: от шаблона Excel к PDF
Генерация счетов-фактур — одна из самых распространённых задач в бизнес-приложениях. Будь то разработка ERP-системы, платформы электронной коммерции или внутреннего инструмента для выставления счетов, ручное создание счетов быстро становится неэффективным по мере роста числа клиентов.
Распространённый подход — разрабатывать счета в Excel, программно заполнять их данными, а затем экспортировать в PDF-файлы для распространения. Это сочетает гибкость шаблонов Excel с переносимостью и профессиональным видом PDF-документов.
В этой статье вы узнаете, как построить автоматизированный процесс создания счетов с помощью библиотеки Free Spire.XLS для Python. Мы возьмём шаблон счёта в Excel, заполним его информацией о клиенте и товарах, а затем экспортируем готовый счёт напрямую в PDF.
Почему стоит использовать шаблоны Excel для счетов?
Многие разработчики пытаются генерировать счета непосредственно в PDF. Хотя это работает, часто требует построения макетов, форматирования таблиц, позиционирования текста и расчёта итогов в коде.
Шаблоны Excel имеют ряд преимуществ:
Просты в изменении для бизнес-пользователей
Встроенная поддержка формул и вычислений
Привычная среда проектирования
Не нужно жёстко прописывать макеты в коде
Простой экспорт в PDF
Вместо того чтобы воссоздавать макет счёта в коде, вы можете позволить Excel заниматься форматированием, в то время как ваше приложение сосредоточится на предоставлении данных.
Процесс выглядит так:
Данные счёта
↓
Шаблон Excel
↓
Заполнение ячеек
↓
Пересчёт формул
↓
Экспорт в PDF
Проектирование шаблона счёта
Наш шаблон счёта содержит три раздела:
Верхняя часть счёта
Верхняя часть включает информацию о компании и реквизиты счёта.
Эти ячейки будут заполняться программно.
Позиции счёта
Раздел позиций начинается со строки 16 и реализован как таблица Excel .
Этот выбор важен, поскольку таблицы Excel предоставляют несколько встроенных возможностей:
Автоматическое чередование цветов строк
Автоматическое распространение формул
Динамическое расширение таблицы
Упрощённое обслуживание
Например:
Шаблон уже содержит формулы для расчёта итогов по строкам.
Итоговый раздел
Нижний раздел содержит формулы для:
Промежуточного итога
Налога
Общего итога
Вот как выглядит мой шаблон:
Установка Free Spire.XLS для Python
Установите библиотеку с помощью pip:
pip install spire.xls.free
Загрузка шаблона счёта
Сначала загрузите шаблон Excel в книгу.
from spire.xls import *
from spire.xls.common import *
workbook = Workbook()
workbook.LoadFromFile("InvoiceTemplate.xlsx")
sheet = workbook.Worksheets[0]
На этом этапе всё форматирование, формулы и определения таблиц из шаблона доступны.
Определение данных счёта
В реальных приложениях данные счёта обычно поступают из базы данных, API или ERP-системы.
В этом примере мы сохраним данные в словаре Python.
invoice = {
"company_name": "ABC Corporation",
"address_1": "123 Main Street",
"address_2": "New York, NY 10001",
"phone": "+1 (555) 123-4567",
"invoice_number": "INV-2026-001",
"customer_id": "CUST-1001",
"items": [
{
"qty": 2,
"description": "Laptop Computer",
"unit_price": 899.00
},
{
"qty": 1,
"description": "Wireless Mouse",
"unit_price": 29.99
},
{
"qty": 3,
"description": "USB-C Cable",
"unit_price": 12.50
}
]
}
Эта структура очень похожа на данные, возвращаемые многими бизнес-системами.
Заполнение информации о счёте
Далее заполним верхнюю часть счёта.
sheet.Range["C7"].Text = invoice["company_name"]
sheet.Range["C8"].Text = invoice["address_1"]
sheet.Range["C9"].Text = invoice["address_2"]
sheet.Range["C10"].Text = invoice["phone"]
sheet.Range["E8"].Text = invoice["invoice_number"]
sheet.Range["E9"].Text = invoice["customer_id"]
Эти значения заменяют заполнители, определённые в шаблоне.
Заполнение позиций счёта
Таблица позиций начинается со строки 16.
Мы можем записать каждую позицию в рабочий лист с помощью простого цикла.
start_row = 16
for index, item in enumerate(invoice["items"]):
row = start_row + index
sheet.Range[f"B{row}"].NumberValue = item["qty"]
sheet.Range[f"C{row}"].Text = item["description"]
sheet.Range[f"D{row}"].NumberValue = item["unit_price"]
Поскольку в шаблоне уже есть формулы для столбца «Line Total», нет необходимости вычислять суммы в коде.
Excel выполняет вычисления автоматически.
Почему стоит использовать таблицу Excel для позиций счёта?
Одна из самых больших проблем при генерации счетов — обработка переменного количества товаров.
Традиционные шаблоны часто требуют от разработчиков:
Вручную копировать формулы
Дублировать форматирование
Пересчитывать диапазоны
Обновлять итоги
Таблицы Excel устраняют большую часть этой сложности.
При вставке новых строк:
Чередование цветов строк сохраняется
Формулы автоматически распространяются на новые строки
Ссылки на таблицу обновляются автоматически
Итоги продолжают работать корректно
В результате приложению нужно только сосредоточиться на вставке данных.
Такое разделение обязанностей делает код гораздо чище и проще в обслуживании.
Обработка более шести позиций счёта
Наш шаблон резервирует шесть строк для товаров.
Если счёт содержит более шести позиций, можно динамически добавить дополнительные строки.
base_rows = 6
if len(invoice["items"]) > base_rows:
sheet.InsertRow(
22,
len(invoice["items"]) - base_rows
)
Поскольку список товаров реализован как таблица Excel, новые вставленные строки автоматически наследуют форматирование и формулы таблицы.
Никакого дополнительного кода для стилизации не требуется.
Пересчёт формул
Перед экспортом пересчитайте все формулы в книге.
workbook.CalculateAllValue()
Это гарантирует, что итоги по строкам, промежуточные итоги, налоги и общие итоги будут отражать актуальные данные.
Управление макетом страницы и масштабированием PDF
Перед экспортом счёта в PDF часто стоит настроить параметры страницы рабочего листа.
Без правильной конфигурации в сгенерированном PDF могут быть большие поля, избыточные отступы или содержимое, неоправданно уменьшенное в масштабе.
Следующие настройки помогают максимально использовать полезную площадь страницы и обеспечить чистое отображение счёта.
sheet.PageSetup.LeftMargin = 0.0
sheet.PageSetup.RightMargin = 0.0
sheet.PageSetup.TopMargin = 0.0
sheet.PageSetup.BottomMargin = 0.0
# Вместить рабочий лист на одну страницу
workbook.ConverterSetting.SheetFitToPage = True
Эти настройки:
Убирают ненужные поля
Максимизируют доступное пространство
Улучшают читаемость
Дают более профессиональный вид PDF
Обработка длинных счетов
Если счёт может содержать много товаров и занимать несколько страниц, размещение всего листа на одной странице обычно нежелательно, потому что содержимое становится слишком мелким.
В этом случае настройте лист на соответствие ширине страницы, разрешив неограниченную высоту.
sheet.PageSetup.FitToPagesWide = 1
sheet.PageSetup.FitToPagesTall = 0
Это указывает Excel:
Уместить счёт в пределах ширины одной страницы
Автоматически создавать дополнительные страницы по вертикали при необходимости
В результате длинные счета остаются читаемыми, сохраняя исходное форматирование.
Экспорт счёта в PDF
Наконец, сохраните книгу как PDF-документ.
workbook.SaveToFile(
"Invoice.pdf",
FileFormat.PDF
)
Поскольку макет листа уже оптимизирован, экспортированный PDF сохраняет предполагаемый внешний вид счёта.
Сгенерированная PDF-счет выглядит так:
Полный пример
from spire.xls import Workbook, FileFormat
# Создание книги
workbook = Workbook()
workbook.LoadFromFile(r"C:\Users\Administrator\Desktop\invoice-template.xlsx")
# Получение рабочего листа
sheet = workbook.Worksheets[0]
# ==========================================
# Данные счёта
# ==========================================
invoice = {
"company_name": "ABC Corporation",
"address_1": "123 Main Street",
"address_2": "New York, NY 10001",
"phone": "+1 (555) 123-4567",
"invoice_number": "INV-2026-001",
"customer_id": "CUST-1001",
"items": [
{
"qty": 2,
"description": "Laptop Computer",
"unit_price": 899.00
},
{
"qty": 1,
"description": "Wireless Mouse",
"unit_price": 29.99
},
{
"qty": 3,
"description": "USB-C Cable",
"unit_price": 12.50
}
]
}
# ==========================================
# Заполнение информации о счёте
# ==========================================
# C7-C10
sheet.Range["C7"].Text = invoice["company_name"]
sheet.Range["C8"].Text = invoice["address_1"]
sheet.Range["C9"].Text = invoice["address_2"]
sheet.Range["C10"].Text = invoice["phone"]
# E8-E9
sheet.Range["E8"].Text = invoice["invoice_number"]
sheet.Range["E9"].Text = invoice["customer_id"]
# ==========================================
# Заполнение позиций счёта
# ==========================================
start_row = 16
for index, item in enumerate(invoice["items"]):
row = start_row + index
sheet.Range[f"B{row}"].NumberValue = item["qty"]
sheet.Range[f"C{row}"].Text = item["description"]
sheet.Range[f"D{row}"].NumberValue = item["unit_price"]
# ==========================================
# Пересчёт формул
# ==========================================
workbook.CalculateAllValue()
# ==========================================
# Экспорт в PDF
# ==========================================
sheet.PageSetup.LeftMargin = 0.0
sheet.PageSetup.RightMargin = 0.0
sheet.PageSetup.TopMargin = 0.0
sheet.PageSetup.BottomMargin = 0.0
# Вместить лист на одну страницу
workbook.ConverterSetting.SheetFitToPage = True
# Для длинного счёта, превышающего одну страницу, установите FitToPagesTall = 0,
# чтобы разрешить размещение на нескольких страницах по вертикали
# sheet.PageSetup.FitToPagesWide = 1
# sheet.PageSetup.FitToPagesTall = 0
workbook.SaveToFile(
"Invoice.pdf",
FileFormat.PDF
)
workbook.Dispose()
Заключение
Использование Excel в качестве движка для шаблонов счетов может значительно упростить генерацию счетов. Храня макеты, формулы и форматирование в электронной таблице, разработчики могут сосредоточиться на предоставлении данных, а не на управлении деталями представления.
Сочетание шаблона Excel с Free Spire.XLS для Python позволяет автоматизировать весь процесс — от заполнения данных счёта до создания профессиональных PDF-документов — с удивительно небольшим количеством кода.
Когда раздел позиций счёта реализован как таблица Excel, решение становится ещё более поддерживаемым. Форматирование, формулы и расширение таблицы обрабатываются автоматически, что упрощает поддержку счетов любого размера, сохраняя вашу кодовую базу чистой и простой.
Показать полностью
4
Иерархический справочник: когда данные растут как дерево
Представьте дерево. У него есть корень, от него отходят ветки, от больших веток - более мелкие, а затем листья.
Примерно так же устроены иерархические справочники в информационных системах.
И как же можно понять: что есть ветка, а что есть лист в этом иерархическом справочнике?
В канале Аналитика FM я часто разбираю такие ситуации - когда задача вроде решаема, но без нормальной структуры превращается в кашу.
Например:
📁 Транспорт
├── Легковой транспорт
│ ├── Седаны
│ └── Кроссоверы
└── Грузовой транспорт
├── Малотоннажный
└── Тягачи
Или:
📁 Товары
├── Электроника
│ ├── Телефоны
│ └── Ноутбуки
└── Бытовая техника
├── Холодильники
└── Стиральные машины
А как такое дерево хранится в базе данных?
На самом деле всё гораздо проще, чем кажется.
Обычно таблица справочника выглядит примерно так:
| id | name | parent_id |
| --- | ----------------------------------- | --------------- |
| 1 | Транспорт | NULL |
| 2 | Легковой транспорт | 1 |
| 3 | Грузовой транспорт | 1 |
| 4 | Седаны | 2 |
| 5 | Кроссоверы | 2 |
| 6 | Тягачи | 3 |
Структурно это выглядит так:
1 Транспорт
├── 2 Легковой транспорт
│ ├── 4 Седаны
│ └── 5 Кроссоверы
└── 3 Грузовой транспорт
└── 6 Тягачи
Каждая запись имеет:
id - собственный идентификатор;
parent_id - идентификатор родительского элемента.
Например:
id = 4
name = Седаны
parent_id = 2
Это означает:
Седаны → Легковой транспорт → Транспорт
Именно благодаря полю parent_id база понимает, что "Седаны" и "Кроссоверы" относятся к одной ветке дерева.
Если подниматься по родителям вверх, то рано или поздно мы придём к общему узлу - корню ветки.
Получается, что вся иерархия строится буквально на одном поле: parent_id
Для чего нужны иерархические справочники?
Они позволяют хранить данные не просто списком, а показывать связи между объектами.
Благодаря этому можно:
✅ группировать данные;
✅ строить отчёты по категориям;
✅ наследовать свойства от родительских узлов;
✅ задавать правила сразу для целой ветки;
✅ быстро находить все дочерние элементы.
Например, если правило применяется ко всей категории "Легковой транспорт", то оно автоматически действует и для седанов, и для кроссоверов, и для любых новых подкатегорий, которые появятся позже.
Где используются?
📌 MDM-системы (Master Data Management);
📌 каталоги товаров интернет-магазинов;
📌 банковские и страховые системы;
📌 ERP и CRM;
📌 классификаторы услуг и продуктов;
📌 организационная структура компании;
📌 государственные классификаторы и справочники.
Преимущества
✔ Гибкость. Можно добавлять новые ветки без изменения структуры данных.
✔ Удобная аналитика. Легко получить данные как по конкретному элементу, так и по всей категории.
✔ Наследование правил. Одно правило может применяться сразу к тысячам объектов.
✔ Масштабируемость. Структура может содержать десятки и сотни уровней вложенности.
Недостатки
❌ Сложность запросов. Иногда, чтобы найти всех потомков или родителей, приходится строить рекурсивные запросы.
❌ Производительность. Глубокие иерархии могут существенно замедлять выполнение запросов.
❌ Риск циклических ссылок. Если по ошибке сделать узел потомком самого себя, можно получить бесконечный цикл.
❌ Сложность сопровождения. Изменение структуры верхних уровней может затронуть большое количество дочерних элементов.
Как с ними работать?
При работе с иерархическими справочниками чаще всего приходится решать четыре задачи:
🔹 найти всех потомков узла;
🔹 найти всех родителей элемента;
🔹 определить, принадлежит ли элемент определённой ветке;
🔹 определить, к какому верхнему узлу относится конкретный элемент.
В Oracle для этого используются специальные иерархические запросы:
START WITH ...
CONNECT BY ...
Именно они позволяют "обходить дерево" вверх или вниз по веткам.
В канале Аналитика FM (клик :-) ) уже готов пост про конструкцию START WITH и CONNECT BY.
Подписывайся, если интересно разбираться в особенностях работы аналитика.
Иерархический справочник - это не просто список значений. Это способ описать реальные взаимосвязи между объектами и сделать систему более гибкой и управляемой.
А одна маленькая колонка parent_id превращает обычную таблицу в целое дерево данных.
Показать полностью
1
Формулы и функции в Excel: подробное руководство для начинающих
Формулы Microsoft Excel позволяют мгновенно и без ошибок производить сложнейшие расчеты. Их ценность возрастает в разы, когда приходится работать с огромными массивами данных. Стоит запустить правильный алгоритм, и Excel за считанные секунды перелопатит тонны информации, выдав готовый результат.
В этой статье мы разберем пять ключевых типов формул и функций, которые заложат прочный фундамент для вашей аналитической работы. Попутно мы покажем несколько простых способов их ввода в таблицу.
Все примеры мы будем показывать в версии Excel для Windows, входящей в подписку Microsoft 365. Если у вас установлена другая версия программы, интерфейс может слегка отличаться, однако логика и синтаксис остаются неизменными.
Для владельцев определенных подписок M365 доступна генеративная нейросеть – ИИ-помощник Copilot. К теме искусственного интеллекта мы вернемся ближе к концу статьи, а начнем с детального разбора базовых формул и функций.
О чем расскажем:
Что такое формула в Excel?
Что такое функция?
Базовые матоперации и функции
Логическая функция ЕСЛИ
Функции СУММЕСЛИ и СЧЁТЕСЛИ
Функция ССЫЛКА/СЦЕПИТЬ
Функция ВПР
Как создавать формулы с помощью Copilot
❯ Что такое формула в Excel?
По сути, формула – математическое или логическое выражение, производящее операции над содержимым ячеек. С их помощью можно делать расчеты любой сложности: от банального сложения чисел в столбце до расчета эксцесса (показателя островершинности распределения) в сложных статистических выборках. Формулы незаменимы, когда нужно превратить цифры в наглядные бизнес-показатели, на основе которых принимаются важные решения.
❯ Что такое функция?
Функция – своего рода «заготовленная» формула, встроенная в сам Excel. Это готовый шаблон, облегчающий проведение расчетов. Всего в программе насчитывается около 500 встроенных функций, и этот арсенал ежегодно пополняется. К счастью, для решения 90% повседневных задач за глаза хватит всего десятка самых популярных инструментов.
❯ 1. Базовые математические операции и функции
Запомните золотое правило: любая формула в Excel всегда начинается со знака равенства (=).
Сложение, вычитание, умножение и деление
Допустим, нам нужно сложить значения двух ячеек. Кликните по пустой ячейке, где должен появиться результат, и введите знак =, дав программе понять, что далее последует формула.
Начало ввода формулы в Excel
Теперь щелкните по ячейке с первым слагаемым. Ее адрес (например, A2) автоматически отобразится в строке ввода сразу после знака равенства.
При выборе ячейки ее адрес автоматически подставляется в формулу
Введите знак плюс (+), а затем кликните по ячейке со вторым числом – ее адрес (например, A3) встанет на свое место в формуле. В итоге выражение для сложения двух ячеек примет классический вид:
=A2+A3
Готовая формула сложения видна как в самой ячейке, так и в строке формул над листом
Обратите внимание: формула дублируется в строке формул, расположенной прямо над листом таблицы. После ввода знака = вы можете продолжать писать выражение непосредственно там. Зачастую редактировать длинные формулы в этой специальной строке удобнее, чем тесниться внутри самой ячейки.
Если нужно приплюсовать еще несколько чисел, продолжайте ставить знак плюс и кликать по нужным ячейкам. Закончив ввод, нажмите клавишу Enter – и вуаля, на месте формулы появится готовая сумма.
Нажмите Enter, чтобы увидеть итоговый результат вычислений
Вычитание, умножение и деление строятся точно так же. Достаточно сменить математический оператор: вместо плюса поставить дефис (–) для вычитания, звездочку (*) для умножения или косую черту (/) для деления.
Примеры вычитания, умножения и деления. Каждая формула отображается в строке формул, а итоговый результат – в целевой ячейке
Быстрое сложение с помощью функции СУММ (SUM)
Если чисел много, складывать их поодиночке утомительно. Куда практичнее использовать встроенную функцию СУММ (в англоязычной версии – SUM).
В русской версии Excel разделителем аргументов функций служит точка с запятой (;), а в английской – запятая (,). Это важно учитывать, чтобы избежать ошибок при вводе.
Для начала выберите ячейку для вывода результата. Наберите =СУММ (или =SUM). Excel тут же предложит подходящие функции из выпадающего списка. Дважды щелкните по строке СУММ, чтобы открыть круглую скобку.
Запуск функции СУММ
Под ячейкой появится всплывающая подсказка с синтаксисом:
=СУММ(число1; [число2]; …)
Чтобы сложить разрозненные ячейки, просто кликайте по ним, разделяя адреса точкой с запятой. Также адреса можно ввести вручную с клавиатуры.
Если нужно суммировать целый диапазон (непрерывный столбец или строку), выберите первую ячейку группы, зажмите клавишу Shift и кликните по последней. Еще вы можете вписать этот диапазон вручную через двоеточие – выражение A2:A7 охватит ячейки A2, A7 и всё, что между ними.
Выделение диапазона ячеек для суммирования
Убедившись, что все нужные ячейки выделены, нажмите Enter. Готово! На экране появится итоговая сумма. Если снова щелкнуть по этой ячейке, в строке формул отразится полное выражение. В нашем примере это:
=СУММ(A2:A7)
В строке формул видна функция СУММ, в самой ячейке – готовый результат
Важное свойство формул в Excel – динамичность (или относительность). Если изменить значение в любой из ячеек исходного диапазона, итоговая сумма автоматически пересчитается.
При изменении слагаемых результат пересчитывается автоматически
Если же вам нужно «зафиксировать» полученное число, превратив его в статичное значение (константу, которая не изменится при редактировании исходных строк), скопируйте ячейку (клик правой кнопкой мыши → «Копировать»). Затем снова щелкните по ней правой кнопкой мыши и в разделе «Параметры вставки» выберите пункт «Значения» (иконка с планшетом и цифрами 123).
Вставка в режиме «Значения» разрывает связь с исходными ячейками, фиксируя полученный результат
Теперь при выборе этой ячейки в строке формул будет отображаться обычное статичное число, а не алгоритм расчета.
Теперь в ячейке находится обычное статичное значение
Лайфхак: в некоторых случаях процесс можно ускорить благодаря функции «Автосумма». Если у вас есть непрерывный ряд чисел в строке или столбце, поставьте курсор в соседнюю пустую ячейку (справа от строки или снизу под столбцом) и нажмите кнопку Автосумма (со знаком греческой буквы сигма Σ), которая находится на вкладке «Главная» в правой части ленты. Программа сама определит диапазон данных, и вам останется лишь подтвердить расчет нажатием Enter.
Автосумма – самый быстрый способ сложить числа в строке или столбце
Расчет среднего значения с помощью функции СРЗНАЧ (AVERAGE)
Чтобы вычислить среднее арифметическое, проделайте те же шаги, но введите функцию =СРЗНАЧ (=AVERAGE), затем выделите нужные ячейки с числами.
Быстро рассчитать среднее арифметическое поможет СРЗНАЧ
Лайфхак: для вычисления среднего значения тоже есть быстрый ярлык. Поставьте курсор справа от заполненной строки или под столбцом с числами. Нажмите на стрелку рядом с «Автосуммой» и выберите в выпадающем меню пункт Среднее, после чего подтвердите выбор клавишей Enter. Excel рассчитает среднее арифметическое.
Функция расчета среднего значения также вынесена в меню кнопки «Автосумма»
❯ 2. Логическая функция ЕСЛИ (IF)
Этот инструмент позволяет автоматизировать принятие решений прямо внутри таблицы по принципу «если условие выполняется, то переходим к действию А, иначе – к действию Б». Программа проверяет заданное логическое условие и выводит результат в зависимости от исхода проверки. К примеру, вы можете настроить проверку возраста: если в ячейке указано число 18 или больше, вернуть «Да», если меньше – «Нет».
Для разнообразия опробуем еще один способ ввода формул – через вкладку «Формулы». Там все встроенные функции разложены по полочкам: автосумма, финансовые, логические, текстовые, даты и времени и т. д. Классификация очень выручит, когда не помнишь точное название инструмента или сомневаешься в правильности написания.
Чтобы воспользоваться нужной командой, выделите пустую ячейку, откройте вкладку Формулы, нажмите Логические и выберите в списке пункт ЕСЛИ (IF).
Другой вариант – нажать Вставить функцию (fx), расположенную в левом углу той же панели. В открывшемся окне отобразится список часто используемых инструментов.
Если не хотите писать формулу вручную, перейдите на вкладку «Формулы» и воспользуйтесь мастером «Вставить функцию»
Выберите в списке пункт ЕСЛИ и нажмите OK. Если искомой функции нет в перечне часто используемых, смените категорию на «Полный алфавитный перечень» – там вы гарантированно найдете всё, что скрыто в недрах программы.
На экране откроется окно «Аргументы функции», а в выбранной ячейке появится заготовка =ЕСЛИ().
Интерактивное окно «Аргументы функции» помогает безошибочно заполнить все параметры формулы
Синтаксис ЕСЛИ следующий:
=ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)
В окне аргументов увидите три поля с идентичными названиями. В нашем примере с совершеннолетием логический тест проверит, больше или равно ли число в ячейке B2 числу 18. Если да, программа выдаст «Да», если нет – «Нет». Прямо в поля формы впишите соответствующие значения:
Логическое_выражение: B2>=18
Значение_если_истина: "Да"
Значение_если_ложь: "Нет"
Можно обойтись без графической формы и просто прописать формулу в ячейке:
=ЕСЛИ(B2>=18; "Да"; "Нет")
Функция ЕСЛИ в действии
Лайфхак: вам не придется заново прописывать формулу для каждой строки вручную. Просто зажмите левой кнопкой мыши маркер автозаполнения (черный квадрат в правом нижнем углу ячейки) и потяните его вниз по столбцу. Excel автоматически скопирует алгоритм во все строки пониже, подстроив ссылки на ячейки под нужные диапазоны. То есть формула, ссылавшаяся на B2, при перетаскивании ниже станет автоматически считывать данные из B3.
Распространение формулы на остальные строки таблицы с помощью автозаполнения
❯ 3. Выборка по условию: функции СУММЕСЛИ (SUMIF) и СЧЁТЕСЛИ (COUNTIF)
СУММЕСЛИ – продвинутая версия функции сложения. Она позволяет суммировать не всё подряд, а лишь те значения из диапазона, которые соответствуют критерию. Для настройки вам нужно указать целевой диапазон ячеек для проверки, собственно критерий отбора и, опционально, диапазон суммирования (если складывать нужно значения из других ячеек, а не из тех, которые проверяли).
Формула имеет следующий синтаксис:
=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Важное правило: любое текстовое условие, а также любые математические или логические символы здесь заключаются в двойные кавычки.
Представим реальную ситуацию: перед вами таблица продаж и нужно быстро сложить сумму сделок, превышающих 100 долларов. Область проверки – диапазон от C2 до C9, а условие – «>100». Поскольку мы складываем числа из того же самого проверяемого столбца, указывать отдельный диапазон суммирования не нужно. Формула получится простой:
=СУММЕСЛИ(C2:C9; ">100")
Пример вычислений с использованием функции СУММЕСЛИ
А если задача сложнее? Допустим, нам необходима сумма заказов только по региону «East» (восточный). В данном случае диапазон проверки (столбец с регионами B2:B9) и диапазон суммирования (столбец с продажами C2:C9) различаются. Поэтому в аргументах пишем обе эти области:
=СУММЕСЛИ(B2:B9; B2; C2:C9)
Удобно то, что необязательно вбивать само слово "East". Достаточно сослаться на ячейку B2, и программа считает и применит содержащийся в ней текст.
Расчет с разделением диапазона критериев и диапазона сложения в СУММЕСЛИ
По схожему принципу работает СЧЁТЕСЛИ (COUNTIF). Она подсчитывает количество ячеек, которые удовлетворяют заданному критерию. Ее синтаксис лаконичен:
=СЧЁТЕСЛИ(диапазон; условие)
Если хотим подсчитать количество сделок, совершенных в западном регионе («West»), мы задаем область поиска (B2:B9) и критерий (значение в ячейке B3). В итоге получится выражение:
=СЧЁТЕСЛИ(B2:B9; B3)
Функция СЧЁТЕСЛИ подсчитывает количество ячеек, соответствующих вашим критериям
Если перед вами стоит еще более хитрая задача – например, подсчитать общие продажи книг именно в восточном регионе или узнать количество чеков выше 100 долларов исключительно на западе, – Excel предложит тяжелую артиллерию: функции СУММЕСЛИМН (SUMIFS) и СЧЁТЕСЛИМН (COUNTIFS).
4. Склеивание строк: функция СЦЕПИТЬ (CONCAT)
Иногда данные хранятся по кусочкам в разных ячейках, но их нужно объединить в одну строку. Популярный пример – соединить разнесенные по разным столбцам имя и фамилию. Инструмент также отлично решает задачи по сборке почтовых адресов, склейке номеров артикулов, генерации путей к файлам на диске или формированию URL-ссылок. Базовая структура выглядит так:
=СЦЕПИТЬ(текст1; текст2; текст3; …)
Попробуем собрать воедино имя и фамилию из соседних ячеек, не забыв добавить разделительный пробел между ними. Поставим курсор в пустую ячейку C2, введем обрывочное =СЦЕ и кликнем на предложенную функцию СЦЕПИТЬ (CONCAT). Сначала выберем ячейку с именем (A2), затем поставим разделитель, введем пустой пробел в кавычках (" "), снова поставим разделитель и укажем ячейку с фамилией (B2). Завершаем ввод клавишей Enter. Выражение примет следующий вид:
=СЦЕПИТЬ(A2; " "; B2)
Осталось потянуть маркер автозаполнения вниз от ячейки C2, чтобы моментально склеить имена для всех остальных сотрудников в таблице.
Функция СЦЕПИТЬ бесшовно соединила значения из столбцов А и B в словосочетания
❯ 5. Легендарная функция ВПР (VLOOKUP)
Это безоговорочный фаворит аналитиков и одна из самых популярных функций в Excel. ВПР (VLOOKUP) расшифровывается как «вертикальный просмотр». Она ищет заданное значение в крайнем левом столбце диапазона и выводит данные из соседней ячейки в той же строке. Полезно, когда вы хотите сопоставить две разные таблицы, вытащить недостающие реквизиты или сверить два независимых списка.
Чтобы запустить механизм, понадобятся три обязательных аргумента и один опциональный:
Искомое_значение (строка или число, которое пытаемся найти);
Таблица (целевой диапазон ячеек справочника, где осуществляется поиск);
Номер_столбца (порядковый номер колонки в таблице справочника, откуда нужно забрать искомое значение);
Интервальный_просмотр (необязательный аргумент выбора точности поиска: ИСТИНА или ЛОЖЬ). Вариант «ЛОЖЬ» означает, что нужен только стопроцентно точный результат. Выбор «ИСТИНА» разрешает искать близкое приближенное значение. Если опустить параметр, Excel применит поиск по умолчанию – «ИСТИНА».
В готовом виде формула выглядит так:
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Держите в уме критически важное условие: искомое значение обязано находиться исключительно в самом первом (крайнем левом) столбце выделяемой таблицы справочника. Этот столбец считается первым (индекс 1), следующий за ним вправо – вторым (индекс 2) и так далее.
Рассмотрим пример: нужно узнать, к какому региону прикреплен сотрудник.
Сначала укажем объект поиска – имя «Mike» (ячейка A2).
Далее выделим диапазон ячеек со справочником (наша целевая таблица поиска) – F2:G8.
Затем укажем, из какого столбца забирать результат. Считаем колонки слева направо в границах нашего справочника F2:G8. Название региона находится во второй колонке, значит, пишем цифру 2.
И наконец, определимся с точностью совпадения. Режим «ИСТИНА» обычно используют для числовых интервалов. Нам же необходимо железно найти конкретного человека, поэтому выбираем ЛОЖЬ (чтобы гарантировать точное совпадение). Большинство опытных пользователей по умолчанию ставят в конце формулы именно «ЛОЖЬ» (или 0).
Итоговый вид формулы:
=ВПР(A2; F2:G8; 2; ЛОЖЬ)
Функция ВПР – идеальный навигатор для стыковки связанных данных в крупных информационных массивах
Конечно, на крохотной демонстрационной таблице преимущество неочевидно. Но когда перед вами база на десятки тысяч позиций, ВПР сэкономит часы монотонного труда и застрахует от опечаток.
❯ Пишем формулы без усилий: ИИ-помощник Copilot
Если вы счастливый обладатель доступа к Copilot в Excel, можно делегировать составление формул искусственному интеллекту. Вам нужно лишь описать задачу человеческим языком, а нейросеть спроектирует нужную синтаксическую конструкцию. Сейчас разберем два наглядных примера.
Чтобы активировать ИИ, нажмите на значок Copilot на панели инструментов или в правом нижнем углу экрана.
Чтобы призвать Copilot, кликните по кнопке. Она может располагаться на ленте или парить в правом нижнем углу рабочего окна Excel
Справа откроется боковая панель ИИ-чата. Сюда мы и будем отправлять наши пожелания. Попробуем с простого: попросим ИИ просуммировать наши сделки и четко укажем ячейку для расположения формулы:
Создай в ячейке B7 формулу для подсчета суммы всех продаж
Сформулируйте задачу для Copilot простым языком
Нажмите кнопку отправки запроса. Помощник проанализирует контекст страницы, соберет нужную формулу и добавит ее в нужную ячейку.
Нейросеть сгенерировала корректный математический код, и в ячейке B7 появился верный ответ
Позовем ИИ на помощь в более запутанном кейсе, представленном на скриншоте ниже.
Разнородный массив данных для следующего теста
Отправим нейросети многоступенчатый запрос:
Создай в ячейке G2 формулу, рассчитывающую общую выручку за единицу товара с учетом скидки на каждую покупку. Затем протяни эту формулу вниз для всех остальных строк и сформируй сводную таблицу с суммой выручки по регионам (Region).
Отправляем промпт на обработку и наблюдаем за магией. Результат работы ИИ выглядит круто:
ИИ за секунду вывел все расчеты, достроил столбец и собрал сводную аналитику по регионам
Лайфхак: работать с Copilot станет комфортнее, если вы предварительно отформатируете рабочий диапазон как умную таблицу. Так нейросеть будет четко видеть границы структурированных данных и задействует нужные диапазоны без промахов.
Для дальнейшего изучения
Сегодня вы заглянули за занавес возможностей Excel, и это лишь вершина гигантского ледника. Как только освоитесь с базовыми алгоритмами, откроются сотни готовых инструментов программы и огромный набор вложенных функций разного уровня сложности. Идеальная отправная точка для углубленного изучения – справочник Microsoft «Общие сведения о формулах в Excel».
Статья является переводом. Автор оригинала: Shimon Brathwaite
Автор текста:ZheleznyChel
Написано при поддержке Timeweb Cloud↩
Больше интересных статей и новостей в нашем блоге на Хабре и телеграм-канале.
📚 Вам может быть интересно:
Реклама. ООО «ТАЙМВЭБ.КЛАУД», ИНН: 7810945525
Показать полностью
29
Помощь с Microsoft Excel
всем привет
как оставить сумму в формуле Microsoft Excel без слагаемого?
например 2+2=4
если убрать одну из двоек то пропадёт и 4
а мне надо чтобы осталась 4 в столбце
Ответ mr.ColTs в «Помогу бесплатно ПТО»31
А я с PowerBI могу помочь. Дизайн, оптимизация моделей, dax. Насколько позволит формат комментов, конечно.
Вообще если есть потребность, сигнальте - были мысли начать просветительской деятельностью в этом направлении заниматься (массово и бесплатно), если будет спрос, начну писать/снимать.
Ответ на пост «Помогу бесплатно ПТО»31
Раз пошла такая пьянка, то:
Ну может кому надо - смогу помочь, подсказать, возможно немного объяснить в гугл таблицах, эксель таблицах. Мне кажется все всё уже умеют, но если бы все умели у меня б не было заказов и доп работы)
Если что то простенькое и быстрое то смогу рассказать/объяснить за просто так.
Добра прочитавшим.
Как преобразовать данные Excel в списки, словари и объекты в Python
В серверной разработке на Python, аналитике данных и задачах автоматизации офисной работы чтение файлов Excel и преобразование их в удобные для программ обработки структуры данных является одной из самых распространённых задач.
Многие разработчики начинают работать с данными таблиц через числовые индексы, например row[2] или col[5]. Хотя такой подход позволяет быстро приступить к разработке, он приводит к серьёзной проблеме жёсткой привязки к строкам и столбцам. Код становится сложнее для чтения и поддержки, а любое изменение структуры таблицы — например, перестановка, добавление или удаление столбцов — может нарушить работу значительной части приложения.
В этой статье мы воспользуемся библиотекой Free Spire.XLS for Python, чтобы показать трёхэтапную эволюцию моделирования данных Excel:
Необработанные двумерные списки
Списки словарей
Списки пользовательских бизнес-объектов
Каждый подход подходит для определённых сценариев и уровней сложности. Отказавшись от жёстко заданных индексов, вы сможете сделать код обработки Excel более читаемым, поддерживаемым и масштабируемым.
Предварительные требования
Во всех примерах статьи используется пакет spire.xls, который позволяет читать, записывать, форматировать и пакетно обрабатывать файлы Excel без установки Microsoft Excel.
Установите библиотеку командой:
pip install spire.xls.free
Подход 1. Хранение данных Excel в виде двумерного списка
Как это работает
Это самый простой способ чтения данных Excel. Мы последовательно перебираем используемый диапазон листа по строкам и ячейкам, сохраняя все данные в двумерный список, который полностью повторяет структуру исходной таблицы.
Полный пример
from spire.xls import Workbook
# Загружаем книгу и рабочий лист
workbook = Workbook()
workbook.LoadFromFile("SalesReport.xlsx")
sheet = workbook.Worksheets[0]
# Получаем используемый диапазон
cell_range = sheet.AllocatedRange
# Сохраняем все данные в двумерный список
excel_data = []
for row_idx in range(cell_range.RowCount):
single_row = []
for col_idx in range(cell_range.ColumnCount):
# В Spire.XLS используются индексы, начинающиеся с 1
single_row.append(
cell_range[row_idx + 1, col_idx + 1].Value
)
excel_data.append(single_row)
# Освобождаем ресурсы
workbook.Dispose()
Преимущества и недостатки
Преимущества
Максимально простая реализация
Полностью сохраняет исходную структуру строк и столбцов
Не требует дополнительного преобразования данных
Недостатки
Доступ к данным осуществляется только через числовые индексы:
excel_data[row][col]
Такой код не несёт смысловой нагрузки. Если структура таблицы изменится, все ссылки на индексы придётся обновлять вручную, что усложняет поддержку и повышает риск ошибок.
Подходит для
Быстрых прототипов
Одноразовых скриптов
Матричных вычислений
Временного анализа данных
Для производственных приложений такая структура обычно не является оптимальным решением.
Подход 2. Преобразование строк в словари
Как это работает
Чтобы избавиться от жёстко заданных индексов столбцов, можно использовать первую строку как заголовки столбцов и преобразовать каждую последующую строку в словарь.
Вместо доступа к данным по позиции мы получаем доступ по имени поля. Это устраняет зависимость от порядка столбцов и значительно улучшает читаемость кода.
Полный пример
from spire.xls import Workbook
workbook = Workbook()
workbook.LoadFromFile("SalesReport.xlsx")
sheet = workbook.Worksheets[0]
cell_range = sheet.AllocatedRange
# Извлекаем заголовки из первой строки
rows = list(cell_range.Rows)
headers = [
cell_range[1, col_idx + 1].Value
for col_idx in range(cell_range.ColumnCount)
]
# Формируем список словарей
data_list = []
for row in rows[1:]: # Пропускаем строку заголовков
row_dict = {}
for idx, cell in enumerate(row.Cells):
row_dict[headers[idx]] = cell.Value
data_list.append(row_dict)
workbook.Dispose()
Преимущества и недостатки
Преимущества
Теперь данные можно получать по понятным именам полей:
data_list[0]["Sales"]
Преимущества такого подхода:
Более высокая читаемость кода
Независимость от порядка столбцов
Простая сериализация в JSON
Удобная интеграция с API и конвейерами обработки данных
Хорошая совместимость с Pandas
Недостатки
Структуры на основе словарей по-прежнему являются слабо типизированными. Перед использованием данных может потребоваться дополнительная проверка и преобразование типов.
Подходит для
Импорта и экспорта данных
Очистки и подготовки данных
Генерации полезной нагрузки для API
Бизнес-отчётности
Универсальной обработки Excel
Для большинства приложений это оптимальный баланс между простотой и удобством сопровождения.
Подход 3. Сопоставление строк с пользовательскими бизнес-объектами
Как это работает
При работе с фиксированными схемами данных и более сложной бизнес-логикой словари могут оказаться недостаточно удобными. Они не обеспечивают типобезопасность, поддержку IntelliSense и не предоставляют естественного места для размещения бизнес-правил.
Более надёжный подход заключается в создании класса бизнес-сущности и преобразовании каждой строки Excel в экземпляр этого класса.
В результате получается строго типизированная модель, поддерживающая валидацию, бизнес-методы и более удобные инструменты разработки.
Полный пример
# Определение бизнес-сущности
class Employee:
def __init__(self, name: str, age: int | None, department: str):
self.name = name
self.age = age
self.department = department
def is_adult(self) -> bool:
"""Возвращает True, если сотрудник является совершеннолетним."""
return self.age >= 18 if self.age else False
from spire.xls import Workbook
workbook = Workbook()
workbook.LoadFromFile("EmployeeData.xlsx")
sheet = workbook.Worksheets[0]
cell_range = sheet.AllocatedRange
employee_list = []
# Пропускаем строку заголовков
for row in list(cell_range.Rows)[1:]:
name = row.Cells[0].Value
age = (
int(row.Cells[1].Value)
if row.Cells[1].Value
else None
)
department = row.Cells[2].Value
employee = Employee(
name,
age,
department
)
employee_list.append(employee)
workbook.Dispose()
Преимущества и сценарии использования
Преимущества
Строгая типизация благодаря явному преобразованию типов
Более качественная проверка данных
Автодополнение и подсказки IDE
Инкапсуляция бизнес-логики
Улучшенная поддерживаемость кода
Более чистый объектно-ориентированный дизайн
Например:
employee.is_adult()
Бизнес-правила могут быть реализованы непосредственно внутри сущности, а не распределены по всему приложению.
Подходит для
Корпоративных приложений
Стабильных и хорошо определённых схем данных
Систем со сложными бизнес-правилами
Долгосрочных проектов, требующих удобной поддержки
Как выбрать подходящую структуру
Оптимальный выбор зависит от сложности приложения и способа использования данных.
Заключение
Переход от:
Числовых индексов в двумерных списках
Семантического доступа через словари
Строго типизированных бизнес-объектов
отражает более широкий переход от ориентированного на данные программирования к моделированию, ориентированному на бизнес-логику.
Простые скрипты не требуют сложных абстракций. Для большинства реальных задач обработки Excel список словарей обеспечивает отличный баланс между гибкостью и удобством сопровождения. Если же приложение содержит сложные бизнес-правила и использует стабильные схемы данных, пользовательские объекты-сущности становятся наиболее надёжным долгосрочным решением.
Правильный выбор структуры данных позволяет значительно снизить сложность кода, повысить его читаемость, уменьшить количество ошибок и упростить сопровождение процессов обработки Excel по мере роста проекта.
Показать полностью
1