5 уроков ИИ в Excel из практики

Введение

В многих компаниях, особенно в сферах с жесткими правилами или ограниченными техническими возможностями, Excel вместе с дополнением XLMiner остается основным инструментом для прогнозирования и задач машинного обучения.

Доступность Excel скрывает серьезную проблему: разница между запуском моделей и созданием надежных систем анализа данных. В проекте по прогнозированию одобрения кредитов выяснилось, что машинное обучение на базе Excel подводит не из-за слабых алгоритмов, а из-за часто игнорируемых подходов.

Эта статья превращает неудачные моменты в пять практических рамок, которые улучшат работу с машинным обучением в Excel.

Урок 1: Несколько способов выявления выбросов

Обработка выбросов ближе к искусству, чем к точной науке, и поспешное удаление может стереть ценные экстремальные данные. В одном случае все значения активов жилья выше 95-го процентиля убрали по простому расчету IQR, решив, что это ошибки. Позже выяснилось, что удалили реальные дорогие объекты, важные для крупных кредитов.

Вывод: применяйте разные методы обнаружения и проверяйте вручную перед удалением. Разработайте полную схему для выявления выбросов.

В соседнем листе рядом с основными данными добавьте столбцы для обнаружения:

• Столбец A: Исходное значение (residential_assets_value)
• Столбец B: Метод IQR
  =IF(A2 > QUARTILE.INC($A$2:$A$4270,3) + 1.5*(QUARTILE.INC($A$2:$A$4270,3)-QUARTILE.INC($A$2:$A$4270,1)), "Outlier_IQR", "Normal")
• Столбец C: Метод 3-сигма
  =IF(ABS(A2-AVERAGE($A$2:$A$4270)) > 3*STDEV($A$2:$A$4270), "Outlier_3SD", "Normal")
• Столбец D: Метод процентилей
  =IF(A2 > PERCENTILE.INC($A$2:$A$4270,0.99), "Outlier_P99", "Normal")
• Столбец E: Общий флаг
  =IF(COUNTIF(B2:D2,"Outlier*")>=2, "INVESTIGATE", "OK")
• Столбец F: Ручная проверка [Заметки после расследования]
• Столбец G: Итоговое решение [Оставить/Удалить/Преобразовать]

Такой подход показал закономерности в данных по кредитам:

• Значения, отмеченные всеми тремя методами (IQR, 3-сигма и процентили): Вероятно, ошибки
• Значения, отмеченные IQR, но не 3-сигма: Реальные высокие значения в скошенных распределениях
• Значения, отмеченные только процентилями: Экстремальные, но верные случаи, которые чуть не потеряли

Столбец "Ручная проверка" обязателен. Для каждой отмеченной записи фиксируйте выводы, например: "Реальная элитная недвижимость, подтверждена по публичным источникам" или "Вероятно, ошибка ввода, значение в 10 раз превышает рыночный максимум".

Урок 2: Всегда задавайте случайные семена

Мало что раздражает сильнее, чем показать отличные результаты модели, а потом не суметь их повторить в финальном отчете. С моделью дерева классификации точность валидации однажды была 97,3%, а на следующий день — 96,8%. Разница небольшая, но она подрывает доверие. Зрители начинают сомневаться, какое число настоящее и насколько можно полагаться на анализ.

Вывод: Причина в случайном разделении данных без фиксированного семени. Большинство алгоритмов машинного обучения включают случайность на каком-то этапе.

• Разделение данных: Какие записи идут в обучение, валидацию или тест
• Нейронные сети: Случайная инициализация весов
• Некоторые ансамблевые методы: Случайный выбор признаков

XLMiner применяет случайные процессы для разделения данных. Запуск одной и той же модели дважды с одинаковыми параметрами дает чуть разные результаты из-за разного разделения на обучение и валидацию.

Решение простое, но неочевидное. При использовании функции разделения в XLMiner (в большинстве диалогов моделей):

1. Поставьте галочку "Set seed" (по умолчанию снята)
2. Введите конкретное целое число: 12345, 42, 2024 или любое запоминающееся
3. Запишите это семя в журнале модели

Теперь при каждом запуске модели с этим семенем:

• Одинаковые разделения на обучение, валидацию и тест
• Одинаковые метрики производительности модели
• Одинаковые предсказания для тех же записей
• Полная воспроизводимость

Пример из набора данных по одобрению кредитов без семени (три запуска одинаковой логистической регрессии):

• Запуск 1: Точность валидации = 92,4%, F1 = 0,917
• Запуск 2: Точность валидации = 91,8%, F1 = 0,923
• Запуск 3: Точность валидации = 92,1%, F1 = 0,919

А с семенем=12345 (три запуска одинаковой логистической регрессии):

• Запуск 1: Точность валидации = 92,1%, F1 = 0,928
• Запуск 2: Точность валидации = 92,1%, F1 = 0,928
• Запуск 3: Точность валидации = 92,1%, F1 = 0,928

Эта разница критически важна для доверия. При необходимости повторить анализ можно сделать это уверенно, зная, что цифры совпадут.

Важное замечание: Семя контролирует случайность в разделении и инициализации, но не защищает от других изменений. Если данные изменятся (добавятся записи, поменяются преобразования) или параметры модели скорректируют, результаты все равно изменятся, и это правильно.

Урок 3: Правильное разделение данных: Трехчастный подход

Связанно с воспроизводимостью — стратегия разделения. По умолчанию в XLMiner делается разделение 60/40 на обучение и валидацию. Это выглядит разумно, пока не возникнет вопрос: а где тестовый набор?

Частая ошибка — построить нейронную сеть, настроить ее по производительности на валидации, а потом представить эти метрики как финальные.

Вывод: Без отдельного тестового набора оптимизация идет прямо на данных, которые отчитывают, что завышает оценки. Правильная стратегия использует три набора.

1. Набор обучения (50% данных)

• Здесь модель изучает закономерности
• Используется для подгонки параметров, коэффициентов или весов
• Для набора по кредитам: ~2135 записей

2. Набор валидации (30% данных)

• Для выбора модели и настройки гиперпараметров
• Служит для сравнения разных моделей или конфигураций
• Помогает выбрать лучшую обрезанную ветвь дерева, оптимальный порог или подходящую архитектуру нейросети
• Для набора по кредитам: ~1280 записей

3. Тестовый набор (20% данных)

• "Финальный экзамен" — оценивать только раз
• Используется ТОЛЬКО после завершения всех решений по моделированию
• Дает беспристрастную оценку реальной производительности
• Для набора по кредитам: ~854 записи

Критическое правило: Никогда не итеративно улучшайте по производительности на тесте. Как только модель выбрана потому, что "лучше работает на тесте", этот тест становится вторым валидационным набором, и оценки становятся предвзятыми.

Теперь такой рабочий процесс:

1. Задайте семя 12345
2. Разделите 50/30/20 (обучение/валидация/тест)
3. Постройте несколько вариантов моделей, оценивая каждый только на валидации
4. Выберите лучшую модель по валидационной производительности и бизнес-требованиям
5. Оцените тестовый набор ровно один раз выбранной моделью
6. Отчитайте производительность на тесте как ожидаемую в реальности

Пример из проекта по одобрению кредитов:

После выбора Дерево классификации (глубина=7) по валидационной производительности тестовый набор оценили один раз: точность 97,4%. Эта тестовая точность отражает ожидаемую производительность в эксплуатации.

Урок 4: Разрыв между обучением и валидацией: Выявление переобучения заранее

Проблема: Первоначальный взгляд на результаты дерева классификации из отчета проекта выглядит обнадеживающе.

Производительность на обучении:

• Точность: 98,45%
• Точность: 99%
• Полнота: 96%
• F1-метрика: 98,7%

Модель казалась удачной, пока не посмотрели на валидацию.

Производительность на валидации:

• Точность: 97,27%
• Точность: 98%
• Полнота: 94%
• F1-метрика: 97,3%

Разница небольшая, всего 1,18% по точности. Но чтобы понять, проблема ли это, нужна системная схема.

Вывод: Важно различать запоминание модели от настоящего обучения.

Практическое решение: Создайте монитор переобучения. Постройте простой лист сравнения, который сразу покажет переобучение.

Шаг 1: Создайте рамку сравнения

Вот сравнение производительности модели в листе "Overfitting_Monitor":

А вот правила интерпретации:

• Разрыв < 3%: ✅ Хорошо — модель хорошо обобщает
• Разрыв 3-5%: ❓ Следить — приемлемо, но наблюдайте внимательно
• Разрыв 5-10%: ⚠️ Тревожно — возможное переобучение, упростите
• Разрыв > 10%: ❌ Проблема — явное переобучение, нужно исправить

Подробный анализ:

• Общая оценка: ХОРОШО
• Обоснование: Все ключевые метрики в пределах 2% разрыва. Разрыв по специфичности чуть больше, но все равно допустимо. Модель хорошо обобщает.
• Рекомендация: Переходите к оценке на тесте.

Шаг 2: Добавьте формулы расчетов

Ячейка: Разрыв (для точности)
=[@Training] - [@Validation]

Ячейка: Разрыв % (для точности)
=([@Training] - [@Validation]) / [@Training]

Ячейка: Статус (для точности)

=IF([@[Gap %]]<0.03, "✓ Good", IF([@[Gap %]]<0.05, "? Watch", IF([@[Gap %]]<0.10, "⚠ Concerning", "✗ Problem")))

Шаг 3: Создайте визуальный график переобучения

Постройте столбчатую диаграмму бок о бок для сравнения обучения и валидации по метрикам. Это сразу покажет закономерности:

Когда столбцы близки, модель обобщает хорошо. Когда столбцы обучения намного длиннее валидационных, есть переобучение.

Сравнение разных моделей

Настоящая ценность — в сравнении шаблонов переобучения между вариантами моделей. Вот сравнение в листе "Model_Overfitting_Comparison":

Интерпретация: Нейронная сеть с 10+ узлами явно переобучается. Несмотря на высокую точность обучения (99,3%), на валидации она падает до 85,4%. Модель запомнила шаблоны обучения, которые не работают на новых данных.

Лучший выбор: Дерево классификации

• Высокая производительность (97,3% на валидации)
• Минимальное переобучение (1,2% разрыва)
• Хороший баланс сложности и обобщения

Вот простые способы снизить переобучение, если оно выявлено:

• Для деревьев классификации: Уменьшите максимальную глубину или увеличьте минимальное число образцов на узел
• Для нейронных сетей: Уменьшите число узлов или слоев
• Для логистической регрессии: Удалите переменные или используйте шаговый отбор
• Для всех моделей: Добавьте больше данных для обучения, если возможно

Урок 5: Внедрите проверку данных для категориальных переменных

Ошибки ввода данных незаметно разрушают проекты машинного обучения. Один опечатка, вроде "gradute" вместо "graduate", создает лишнюю категорию в бинарной переменной. Модель сталкивается с неожиданным значением признака, которого не видела при обучении, что может вызвать сбои при развертывании или, хуже, неверные предсказания без предупреждения.

Профилактика: Функция проверки данных в Excel. Вот протокол внедрения для категориальных переменных:

В скрытом листе (назовите "Validation_Lists") создайте списки допустимых значений:

• Для образования: Столбец с записями "Graduate" и "Not Graduate"
• Для самозанятости: Столбец с записями "Yes" и "No"
• Для статуса кредита: Столбец с записями "Approved" и "Rejected"

В листе ввода данных:

• Выделите весь столбец для категориальной переменной (например, с данными об образовании)
• Данные → Проверка данных → Вкладка Настройки
• Разрешить: Список
• Источник: Перейдите в скрытый лист проверки и выберите нужный список
• Вкладка Оповещение об ошибке: Стиль = Стоп, с ясным сообщением: "Допустимы только 'Graduate' или 'Not Graduate'"

Теперь ввести неверные значения невозможно. Пользователи видят выпадающий список с вариантами, что полностью исключает опечатки.

Для числовых переменных с известными диапазонами примените похожую проверку, чтобы избежать невозможных значений:

• Кредитные баллы: Должны быть от 300 до 900
• Срок кредита: От 1 до 30 лет
• Годовой доход: Больше 0

Выделите столбец, примените проверку данных, установите:

• Разрешить: Целое число (или Десятичное)
• Данные: между
• Минимум: 300 (для кредитных баллов)
• Максимум: 900

Заключение

Вот краткий обзор уроков из статьи.

Пять подходов в этой статье — многостороннее выявление выбросов, задание случайных семян, трехчастное разделение данных, мониторинг разрыва между обучением и валидацией, внедрение проверки данных — объединяет одно: они легки в применении, но их отсутствие приводит к серьезным проблемам.

Ни один из этих методов не требует глубоких статистических знаний или сложного программирования. Они не нуждаются в дополнительном ПО или дорогих инструментах. XLMiner в Excel — мощный инструмент для доступного машинного обучения.