🧃 Вступление
Мы все знаем, что Excel — это швейцарский нож офисной жизни. Но давай честно: вручную вставлять =IF(...), =VLOOKUP(...) и прочие заклинания — уже не модно.
Модно — делать это через Python, нажимая одну кнопку, а не 73.
Сегодня покажу:
- Как вставлять формулы IF, VLOOKUP, SUMIF, СЧЁТЕСЛИ через Python
- Как всё это автоматизировать
- С примерами из реальной жизни, с юмором и подробными комментариями
🔧 Что нам нужно?
pip install pandas openpyxl
Импортируем всё нужное:
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule
🎯 Сценарий из жизни
Ты работаешь в отделе продаж. У тебя есть:
- Список продаж по менеджерам
- Таблица с планами по продажам
- Твоя задача: автоматом посчитать, кто молодец, а кто "в пролёте"
- Подсветить неудачников
- Использовать IF, VLOOKUP, SUMIF, СЧЁТЕСЛИ и не сойти с ума
📄 Шаг 1: Подготовим данные
Создадим два DataFrame:
- sales — фактические продажи
- plans — плановые значения
# Фактические продажи
sales_data = {
'Менеджер': ['Анна', 'Борис', 'Анна', 'Борис', 'Виктор'],
'Сделка': [1, 2, 3, 4, 5],
'Сумма': [50000, 30000, 20000, 15000, 70000]
}
sales_df = pd.DataFrame(sales_data)
# План по менеджерам
plans_data = {
'Менеджер': ['Анна', 'Борис', 'Виктор'],
'План': [60000, 50000, 80000]
}
plans_df = pd.DataFrame(plans_data)
Сохраняем оба в Excel на отдельные листы:
file_name = 'отчет_продажи.xlsx'
with pd.ExcelWriter(file_name, engine='openpyxl') as writer:
sales_df.to_excel(writer, sheet_name='Продажи', index=False)
plans_df.to_excel(writer, sheet_name='Планы', index=False)
🧮 Шаг 2: Добавим формулы: SUMIF, VLOOKUP, IF
Открываем файл и начинаем магию:
wb = load_workbook(file_name)
ws_sales = wb['Продажи']
ws_plans = wb['Планы']
🧮 Добавим SUMIF — сколько менеджер продал всего?
# Добавим новый столбец "Итог продаж"
ws_sales.cell(row=1, column=5).value = 'Итог продаж (SUMIF)'
for row in range(2, ws_sales.max_row + 1):
manager_cell = f"A{row}"
formula = f'=SUMIF(A$2:A${ws_sales.max_row},{manager_cell},C$2:C${ws_sales.max_row})'
ws_sales.cell(row=row, column=5).value = formula
📝 Комментарий:
Для каждой строки мы считаем общую сумму продаж менеджера. Используем SUMIF, как в Excel: =SUMIF(A:A,"Анна",C:C).
🔍 Добавим VLOOKUP — подтянем план по каждому менеджеру
# Новый столбец — "План (VLOOKUP)"
ws_sales.cell(row=1, column=6).value = 'План (VLOOKUP)'
# Предположим, что таблица "Планы" находится на листе "Планы" и в колонках A:B
for row in range(2, ws_sales.max_row + 1):
manager = f"A{row}"
# Формула: ищем менеджера в листе "Планы", берем 2-ю колонку (план)
formula = f'=VLOOKUP({manager},Планы!A:B,2,FALSE)'
ws_sales.cell(row=row, column=6).value = formula
📝 Комментарий:
Поиск плана по имени менеджера через VLOOKUP. Всё как в Excel, только Python делает это сам.
🧠 Добавим IF — выполнил ли менеджер план?
# Новый столбец — "Статус (IF)"
ws_sales.cell(row=1, column=7).value = 'Статус (IF)'
for row in range(2, ws_sales.max_row + 1):
total_sales = f"E{row}" # Итог продаж
plan = f"F{row}" # План
formula = f'=IF({total_sales}>={plan},"Молодец","Не дотянул")'
ws_sales.cell(row=row, column=7).value = formula
📝 Комментарий:
Простая формула: если продажи >= план, пишем "Молодец", иначе "Не дотянул". Пусть Excel сам решает, кто герой.
📊 Добавим СЧЁТЕСЛИ — сколько раз менеджер фигурирует
# Новый столбец — "Сделок всего (СЧЁТЕСЛИ)"
ws_sales.cell(row=1, column=8).value = 'Сделок всего (СЧЁТЕСЛИ)'
for row in range(2, ws_sales.max_row + 1):
manager_cell = f"A{row}"
formula = f'=СЧЁТЕСЛИ(A$2:A${ws_sales.max_row},{manager_cell})'
ws_sales.cell(row=row, column=8).value = formula
📝 Комментарий:
Считаем количество сделок у каждого менеджера — полезно для анализа активности.
🎨 Добавим условное форматирование
Пусть строки, где "Не дотянул", подсвечиваются розовым.
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
ws_sales.conditional_formatting.add(
f"G2:G{ws_sales.max_row}",
CellIsRule(operator='equal', formula=['"Не дотянул"'], fill=red_fill)
)
💾 Финальный шаг — сохраняем
wb.save(file_name)
print(f"Файл сохранён: {file_name}")
📊 Что получилось в Excel?
✅ Что ты теперь умеешь?
- ✅ Вставлять в Excel формулы прямо из Python
- ✅ Использовать IF, VLOOKUP, SUMIF, СЧЁТЕСЛИ
- ✅ Делать отчеты, которые не стыдно показать начальству
- ✅ Экономить часы рутинной работы