# Подготовка данных для Copilot в Excel (ТРЕБОВАНИЯ):
# 1. Данные должны быть в формате таблицы (Ctrl+T)
# 2. Каждый столбец — с заголовком (без пустых ячеек в шапке)
# 3. Нет объединённых ячеек
# 4. Один тип данных на столбец (числа, даты, текст — раздельно)

# Примеры промптов для Copilot в Excel:
# Эти команды работают в панели Copilot (правая боковая панель):

"Покажи месячную динамику продаж за 2025 год в виде линейного графика"
→ Copilot строит график и добавляет его на новый лист

"Выдели все строки, где сумма заказа > 100 000 ₽ и статус = 'Отменён'"
→ Условное форматирование + фильтр применены автоматически

"Добавь столбец с категорией: если сумма > 50 000 — 'Крупный',
 иначе — 'Мелкий'. Используй формулу ЕСЛИ."
→ Copilot генерирует и вставляет формулу:
=ЕСЛИ([@Сумма]>50000; "Крупный"; "Мелкий")

"Сделай сводную таблицу: регионы по строкам, месяцы по столбцам,
 сумма продаж — значения"
→ Автоматическое создание PivotTable с настройкой полей

"Найди выбросы в столбце 'Время доставки' и объясни возможные причины"
→ Статистический анализ + текстовое объяснение от AI

# Python в Excel — функция PY()
# Доступно в Excel 365 (Beta Channel, включено в настройках)

# Пример 1: Загрузка данных из таблицы Excel в pandas DataFrame
df = xl("SalesData[#All]", headers=True)
df.head(10)

# Пример 2: Группировка и агрегация (аналог сводной таблицы на pandas)
report = df.groupby("Регион").agg(
    Выручка=("Сумма", "sum"),
    Средний_чек=("Сумма", "mean"),
    Кол_заказов=("ID", "count")
).round(0)
report  # Вывод в ячейку Excel как таблица

# Пример 3: Визуализация — график продаж по месяцам
import matplotlib.pyplot as plt
monthly = df.groupby(df["Дата"].dt.to_period("M"))["Сумма"].sum()
plt.figure(figsize=(10, 5))
plt.plot(monthly.index.astype(str), monthly.values, "b-o", linewidth=2)
plt.title("Динамика продаж по месяцам")
plt.xticks(rotation=45)
plt.grid(True, alpha=0.3)
plt.show()  # График отображается прямо в Excel

# Пример 4: Прогноз продаж с помощью линейной регрессии
from sklearn.linear_model import LinearRegression
import numpy as np

X = np.arange(len(monthly)).reshape(-1, 1)
y = monthly.values
model = LinearRegression().fit(X, y)
next_month = model.predict([[len(monthly)]])[0]
print(f"Прогноз на следующий месяц: {next_month:,.0f} ₽")

// Google Sheets + Apps Script + Gemini API
// Extensions → Apps Script → вставить код → Deploy

// Кастомная функция: AI-анализ столбца данных
function AI_ANALYZE(range) {
  const GEMINI_KEY = "YOUR_GEMINI_API_KEY";
  const url = "https://generativelanguage.googleapis.com/v1beta/models/
                 gemini-2.0-flash:generateContent?key=" + GEMINI_KEY;

  // Подготавливаем данные для отправки
  const dataStr = JSON.stringify(range.flat());

  const payload = {
    contents: [{
      parts: [{
        text: "Проанализируй массив чисел и верни КРАТКО (3-5 предложений):
                 среднее, медиану, тренд, есть ли аномалии. Данные: " + dataStr
      }]
    }]
  };

  const response = UrlFetchApp.fetch(url, {
    method: "POST",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  });

  const result = JSON.parse(response);
  return result.candidates[0].content.parts[0].text;
}

// Использование в Google Sheets:
// =AI_ANALYZE(B2:B50) → возвращает текстовый анализ столбца B

// Кастомная функция: AI-категоризация транзакций
function AI_CATEGORIZE(description) {
  const GEMINI_KEY = "YOUR_GEMINI_API_KEY";
  const url = "https://generativelanguage.googleapis.com/v1beta/models/
                 gemini-2.0-flash:generateContent?key=" + GEMINI_KEY;

  const response = UrlFetchApp.fetch(url, {
    method: "POST",
    contentType: "application/json",
    payload: JSON.stringify({
      contents: [{parts: [{text: "Определи категорию одним словом
        (Маркетинг/Логистика/IT/HR/Продажи/Финансы/Прочее): " + description}]}]
    })
  });
  const result = JSON.parse(response);
  return result.candidates[0].content.parts[0].text.trim();
}

// Использование:
// =AI_CATEGORIZE(A2) → Маркетинг
// =AI_CATEGORIZE(A3) → Логистика

# Полный пайплайн: CSV → Дашборд за 5 минут

# Шаг 1: Загрузка CSV через Power Query (Excel)
# Данные → Из текстового/CSV-файла → Выбрать файл
# Power Query автоматически определяет разделители и типы данных
# При необходимости — очистка данных в редакторе Power Query:

# M-код для Power Query (очистка данных):
let
    Источник = Csv.Document(File.Contents("C:\Data\sales_2025.csv"),
                     [Delimiter=",", Encoding=65001]),
    Заголовки = Table.PromoteHeaders(Источник),
    Типы = Table.TransformColumnTypes(Заголовки, {
      {"Дата", type date},
      {"Сумма", Currency.Type},
      {"Количество", Int64.Type}
    }),
    Чистые = Table.RemoveRowsWithErrors(Типы)
in
    Чистые

# Шаг 2: Запрос к Copilot для создания дашборда
"Создай дашборд на новом листе: сверху — 4 карточки KPI (выручка,
 заказы, средний чек, конверсия), под ними — график продаж по
 месяцам, справа — круговая диаграмма по регионам, снизу —
 топ-10 товаров столбчатой диаграммой. Добавь спарклайны."

# Шаг 3: Python-код для автоформатирования дашборда
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side

wb = openpyxl.load_workbook("dashboard.xlsx")
ws = wb.active

# Стилизация KPI-карточек
header_fill = PatternFill(start_color="1E40AF", fill_type="solid")
header_font = Font(color="FFFFFF", bold=True, size=14)
for cell in ws["B2:E2"][0]:
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center")

wb.save("dashboard_final.xlsx")
print("✅ Дашборд готов: dashboard_final.xlsx")

# AI-формулы: Copilot генерирует сложные формулы по описанию

# Промпт: "Найди клиентов, купивших 3+ раза за последние 30 дней"
# Copilot генерирует:
=ФИЛЬТР(клиенты; СЧЁТЕСЛИМН(даты;">="&СЕГОДНЯ()-30;
         клиенты;клиенты)>=3)

# Промпт: "Прогноз продаж на завтра на основе среднего за 7 дней"
# Copilot генерирует:
=СРЗНАЧ(СМЕЩ(продажи;СЧЁТ(продажи)-7;0;7;1))

# Python LAMBDA для Excel — кастомная AI-функция
# =LAMBDA(text, PY("""
# from textblob import TextBlob
# blob = TextBlob(text)
# return blob.sentiment.polarity
# """, text))(A2)
# Возвращает тональность текста от -1 (негатив) до +1 (позитив)

# Python в Excel: кастомная функция для продвинутого анализа
data = xl("A1:B100")

def analyze_sales(df):
    import pandas as pd
    import numpy as np

    q1 = df["Сумма"].quantile(0.25)
    q3 = df["Сумма"].quantile(0.75)
    iqr = q3 - q1
    anomalies = df[(df["Сумма"] < q1 - 1.5 * iqr) |
                     (df["Сумма"] > q3 + 1.5 * iqr)]

    return {
        "Среднее": df["Сумма"].mean(),
        "Медиана": df["Сумма"].median(),
        "Аномалий": len(anomalies),
        "StdDev": df["Сумма"].std()
    }

analyze_sales(data)  # Вывод в Excel как таблица с результатами