ИИ для работы с таблицами Excel: формулы, очистка данных и отчеты

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

1. Формула по описанию. Запрос «подставь цену из листа Прайс по артикулу» дает =ПРОСМОТРX(A2;Прайс!A:A;Прайс!B:B;"нет в прайсе"), это ПРОСМОТРX (XLOOKUP). В версиях без нее ИИ предложит =ЕСЛИОШИБКА(ВПР(A2;Прайс!A:B;2;ЛОЖЬ);"нет в прайсе"), то есть ВПР (VLOOKUP) внутри ЕСЛИОШИБКА (IFERROR).

2. Исправление ошибки в формуле. Если ВПР возвращает #Н/Д, хотя артикул в прайсе есть, ИИ назовет частую причину: лишние пробелы в ячейке. Исправленная формула =ВПР(СЖПРОБЕЛЫ(A2);Прайс!A:B;2;ЛОЖЬ), где СЖПРОБЕЛЫ (TRIM) убирает пробелы по краям.

3. Очистка и разбор текста. Из адреса «Казань, ул. Баумана, 5» в ячейке B2 город достает формула =ЛЕВСИМВ(B2;НАЙТИ(",";B2)-1): ЛЕВСИМВ (LEFT) берет символы слева до первой запятой, а запятую находит НАЙТИ (FIND).

4. Сводные и итоги. Запрос «сумма продаж по Москве» дает =СУММЕСЛИ(B:B;"Москва";D:D): СУММЕСЛИ (SUMIF) складывает суммы из столбца D в строках, где в столбце B стоит «Москва». Для отчета по менеджерам и месяцам ИИ опишет сводную таблицу и диаграмму к ней.

5. Выводы по данным. Формула =ПОИСКПОЗ(A2;A:A;0)<СТРОКА() с функциями ПОИСКПОЗ (MATCH) и СТРОКА (ROW) возвращает ИСТИНА для каждого повторного номера заказа: тот же номер уже стоит выше. Дальше ИИ пишет вывод словами, но проверять его нужно на исходных строках.

17 сентября 2024 года Copilot в Excel стал общедоступным. По запросу обычным языком он пишет формулы, настраивает условное форматирование, строит диаграммы и сводные таблицы.

Анализ на Python внутри Excel на момент запуска работал в режиме предварительного доступа. Для Copilot нужна отдельная лицензия Microsoft 365 Copilot.

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

Если таблица не должна покидать компанию, модель запускают на своем компьютере или сервере. Для этого есть Ollama: программа с лицензией MIT, которая запускает открытые моделимодель с открытыми весами Модель с открытыми весами (open-weights model) это нейросеть, файлы параметров которой можно скачать и запустить на своем оборудовании. Подробнее в словаре локально.

Локальная модельлокальная модель Локальная модель это нейросеть, которая работает на вашем компьютере или сервере, а не в облаке поставщика, поэтому данные никуда не уходят. Подробнее в словаре пишет те же формулы и объяснения, но файл никуда не уходит. Для больших таблиц удобен другой прием: попросить модель написать скрипт на Python с библиотекой pandas и запустить его у себя. Модель видит только названия столбцов, а считает ваш компьютер.

Какую модель и какое оборудование выбрать, разобрано в разделе о суверенном ИИ. Сравнение по цене и рискам на странице «локальный или облачный ИИ».

1. Формула подстановки. Запрос: «На листе Заказы в столбце A артикулы, на листе Прайс в столбце A артикулы, в столбце B цены. Напиши формулу для русской версии Excel с разделителем „;“, которая выведет цену в столбец C, а если артикула нет, напишет „нет в прайсе“. Объясни каждый аргумент». Проверка: протяните формулу вниз, выберите три артикула, найдите их цены в Прайсе фильтром и сравните.

2. Очистка выгрузки. Запрос: «В столбце B телефоны в разном виде: со скобками, пробелами, через 8 и через +7. Напиши формулу, которая приведет их к виду +7 и десять цифр, и перечисли форматы, которые она не обработает». Сами номера в запрос не вставляйте. Проверка: СЧЕТЗ (COUNTA) до и после очистки дает одинаковое число строк, а строки из списка исключений просмотрите глазами.

3. Сводная таблица. Запрос: «В таблице столбцы Дата, Менеджер, Клиент, Сумма. Опиши по шагам, как построить сводную таблицу с выручкой по менеджерам и месяцам: какие поля поставить в строки, столбцы и значения и как сгруппировать даты по месяцам». Проверка: общий итог сводной совпадает с =СУММ(D:D) по исходной таблице, это СУММ (SUM).

4. Сравнение двух таблиц. Запрос: «На листе 1С счета из учета, на листе Банк оплаты из выписки, номер счета в столбце A на обоих листах. Напиши формулу для листа 1С, которая отметит счета без оплаты в банке». Ожидаемый ответ: =ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A2;Банк!A:A;0));"оплачен";"нет в банке"), функции ЕЧИСЛО (ISNUMBER) и ПОИСКПОЗ (MATCH). Проверка: найдите в выписке три счета с отметкой «нет в банке», их там быть не должно.

5. Поиск выбросов. Запрос: «В столбце D суммы заказов. Предложи правило, по которому сумма считается выбросом, и формулу для отметки таких строк». Частый ответ: =ABS(D2-СРЗНАЧ(D:D))>3*СТАНДОТКЛОН.В(D:D), где СРЗНАЧ (AVERAGE) дает среднее, а СТАНДОТКЛОН.В (STDEV.S) разброс. Проверка: откройте документы по отмеченным строкам, выброс бывает ошибкой ввода, а бывает крупным клиентом.

Таблицы с ФИО, телефонами, почтами и паспортными данными клиентов и сотрудников содержат персональные данные. Если такую таблицу загрузить в зарубежный чат-сервис, данные уходят за границу.

По 152-ФЗ с 1 марта 2023 года до трансграничной передачи персональных данных оператор подает уведомление в Роскомнадзор (ч. 3 ст. 12). Сбор и хранение данных граждан России ведутся в базах на территории России (ч. 5 ст. 18).

Обезличить таблицу перед ИИ можно за четыре шага.

1. Замените ФИО на код клиента из CRM.

2. Удалите столбцы с телефонами, почтами, адресами и паспортами.

3. Таблицу соответствия кодов и ФИО храните отдельно, внутри компании.

4. Если ИИ нужен только для формулы, отправляйте не данные, а названия столбцов и пару выдуманных строк.

Как устроена защита данных в наших проектах, описано на странице о безопасности данных.

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

1. Каждое утро кто-то собирает сводную из выгрузок 1С, CRM и банка, и к обеду она уже устарела.

2. Сверку оплат из 1С с банковской выпиской делают вручную, построчно.

3. По почте ходят несколько версий одного файла, и цифры в них расходятся.

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

Как ИИ отвечает на вопросы по цифрам компании без ручных таблиц, разобрано на странице про ИИ для аналитики данных. Начать можно с пилота: один агент на утреннем отчете по одному источнику за 14 дней.