От ручного Excel к ИИ-генерации кода: опыт автоматизации обработки справочников для тех, кто далек от разработки

Часы копирования, формулы, которые ломаются от случайного пробела или запятой, и бесконечные сверки справочников. Знакомо? Делимся опытом, как мы превратили ручную Excel-рутину в автоматизированный процесс с помощью ИИ-генерации кода и почему для этого не нужно быть гуру программирования. Расскажем в блоге ЛАНИТ, как перестать быть  «оператором таблиц» и начать экономить рабочее время.

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

Для ведения методологии системы используются справочники в формате Excel-файлов, сформированные по закодированным аналитикам системы (это бизнес-единицы, показатели, виды деятельности и др.). Справочники ведут методологи, так как, например, выделить виды деятельности и соотнести их с показателями – работа специалиста, который разбирается в экономике отрасли. Справочники разбиты на несколько блоков, занимающих более 30 Excel-файлов. Объем одного из справочников формул – массив из 16 столбцов, 50 000 строк и еще на пяти листах, так как формулы формируются по версиям. И вот теперь представьте, что вам необходимо внести изменения в одной строке, одной ячейке на одном или нескольких листах. 

Типичные ошибки “человеческой” обработки таких справочников:

• внесены изменения не по всем необходимым аналитикам;

• дубли аналитик;

• несохраненные изменения;

• синтаксические ошибки в кодировке справочников.

Как результат – многократное переработка справочника. На примере собственного опыта: четыре раза переделывала справочник, потому что пропустила знак апострофа. И удивление «Почему это система не считывает мои изменения?» сменилось на «Ну как я это могу проконтролировать? Это просто невозможно». Но оказалось, что-то все таким можно сделать, а ИИ здесь нам в помощь. Причем помощь не только в генерации кода по промту, но и комментариями к коду и даже подсказками, как скопировать и как разместить код. Вот, например, как подсказывает DeepSeek использование сгенерированного кода макроса в Excel.

 Как использовать

1. Откройте редактор VBA: Alt + F11

2. Вставьте модуль: Insert → Module

3. Скопируйте код в появившееся окно

4. Закройте редактор: Alt + Q

5. Запустите макрос: Alt + F8, выберите GetIndentLevel, нажмите Run

А вот так Qwen3.6.

📥 Как запустить

1. Alt + F11 → Insert → Module

2. Вставьте код, закройте редактор

3. Выделите диапазон ячеек → Alt + F8 → ПодкраситьНесовпадающиеСкобки → Выполнить

Хочу сразу оговориться, что команда нашего проекта — это не профессиональные программисты. Мы методологи, поэтому, возможно, для гуру программирования наши идеи покажутся примитивными, но ведь это работает и реально помогает. 

Так как справочники ведутся в формате Excel, для автоматизации их обработки и валидации внесенных изменений мы использовали VBA и немного Python. Строгих предпочтений по использованию конкретных ИИ, у нас нет, но как-то так сложилось, что мы в основном используем большую языковую модель Qwen3.6 (разработчик: Alibaba Cloud, Tongyi Lab) или DeepSeek (модель DeepSeek-V3 / DeepSeek-R1) от компании DeepSeek AI. Модели достаточно быстро исправляют ошибки, предлагают несколько альтернативных вариантов кода, дают хорошие комментарии и подсказки, поэтому даже неопытному программисту (или, скажем откровенно, совсем не программисту) не составит большого труда использовать таких помощников.

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

Пример 1. Изменилась кодировка нескольких аналитик. Задача - заменить старые коды на новые во всем справочнике. Привычная функция Ctr+F убьет энтузиазм уже на третьей-четвертой замене. Вот такой промт для ИИ позволит сгенерить код для автоматической замены по всему справочнику.

Напиши макрос. На листе «Для макроса» в первом столбце значения (назовем его переменной a), во втором столбце замена (назовем его переменной b). Макрос проходится по данным столбца 11 каждого листа книги, кроме листа «Для макроса» и если встречает a, заменяет его на b.

И вот как это будет выглядеть при общении с ИИ:

Думаю, здесь понятно, что мейпинг замены сделан на листе «Для макроса». Не буду приводить пример кода, так как промт проверен с точки зрения корректности сгенерированного кода и его отработки. Можно поэкспериментировать с ИИ самостоятельно. 

И вуаля! У вас есть многокритериальный фильтр замены. Осталось только добавить его в разработчик, а затем бери и пользуйся, модифицируй под свои задачи. Например, замену можно делать не по всей книге, а только на активном листе и не в одном, а нескольких (или во всех) столбцах и т. д. И да, мы помним, что ИИ также подскажет, как добавлять макросы в книгу и как их запускать.

Пример 2. Есть несколько файлов-справочников с одинаковой структурой. Задача - объединить все файлы в папке в один файл, при этом сохранив данные по листам. Вот такой промт для ИИ позволит сгенерить код для автоматической работы с файлами.

Напиши макрос. В папке несколько файлов Excel, имеющих одинаковую структуру по листам. Надо создать новую книгу (назови ее «Общий справочник») с листами A, B, C, D, E. На каждом листе строки 6 и 7 - это шапка, которая есть во всех файлах на всех листах, поэтому ее надо скопировать с первого рассматриваемого файла на все листы. Макрос проходится по всем файлам и, если есть данные на листе с таким же названием, копирует данные, начиная с 8 строки, и сливает в новую книгу.

Для работы макроса предварительно помещаем все необходимые файлы в одну папку. Макрос делает запрос расположения папки, но можно задать его и статично.

Для работы макроса предварительно помещаем все необходимые файлы в одну папку. Макрос делает запрос расположения папки, но можно задать его и статично.

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

Напиши макрос. При сохранении или закрытии книги: если в столбце 5 последняя заполненная строка содержит любое значение из столбца 1 листа «Р1» выдай сообщение «Об изменениях в методологии отчета необходимо сообщить разработчикам Р1!».

Для работы макроса на листе «Р1» размещены фильтры для выведения оповещения – коды отчетов, а в столбце 5 активного листа отражаются коды последних изменений.

А вот и решение моей проблемы с апострофами.

Пример 4. При написании формул используется знак апострофа (двойное выделение). Если прописать только один, то формула в системе не отобразится. Задача - проверить количество апострофов до загрузки справочника в систему.

Напиши макрос. В выделенных ячейках проверяет количество символов «’». Кроме апострофа в ячейках есть и другой текст. Если количество нечетное, подкрашивает ячейку зеленым цветом.

И здесь все-таки приведу пример кода, так как при использовании обычный Replace некорректно работает с Unicode-символами в смешанном тексте, и первые итерации кода не давали нужного результата. А вот этот будет работать:

Sub ПодкраситьНечетныеАпострофы()
	Dim cell As Range
	Dim txt As String
	Dim count As Long
	Dim i As Long
	Dim chCode As Long
	    Application.ScreenUpdating = False
	Application.Calculation = xlCalculationManual
	    For Each cell In Selection
    	' Пропускаем пустые ячейки и ячейки с ошибками
    	If cell.Value <> "" And Not IsError(cell.Value) Then
        	txt = CStr(cell.Value2) ' Value2 быстрее и надежнее обрабатывает текст
        	count = 0
        	            ' Посимвольный перебор. Остальной текст просто пропускается.
        	For i = 1 To Len(txt)
            	chCode = AscW(Mid(txt, i, 1))
            	' Проверяем коды основных апострофов и кавычек:
            	' 39  =  '  (обычный ASCII)
            	' 8217=  ’  (правая типографская)
            	' 8216=  ‘  (левая типографская)
            	' 96  =  `  (обратный апостроф)
            	If chCode = 39 Or chCode = 8217 Or chCode = 8216 Or chCode = 96 Then
                	count = count + 1
            	End If
        	Next i
        	
        	' Если нечётное количество → зелёный, иначе → убираем заливку
        	If count Mod 2 <> 0 Then
   	         cell.Interior.Color = RGB(144, 238, 144)
        	Else
            	cell.Interior.ColorIndex = xlNone
        	End If
    	End If
	Next cell
	
	Application.Calculation = xlCalculationAutomatic
	Application.ScreenUpdating = True
	MsgBox "Готово! Ячейки с нечётным количеством апострофов выделены.", vbInformation
End Sub

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

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

Напиши макрос. В выделенных ячейках проверяет количество открывающихся и закрывающихся скобок. Если количество открытых не равно закрытым, подкрашивает ячейку зеленым цветом.

Приведены четыре простых примера, а дальше больше. Мы создали универсальный файл обработки справочников. На отдельном листе вводятся необходимые корректировки по аналитикам, подгружается файл справочника, обрабатывается и на выходе обновленный справочник. Красота и мечта человека с плохим зрением (это я про себя, потому как мои “любимые” ошибки – это что-то не дописать, просто потому, что не увидела).

В таблице представлен укрупненный расчет времени на обработку справочников в зависимости от операций.

Да, расчет укрупненный, но даже эти цифры впечатляют. И дело даже не в экономии времени, хотя это тоже важно, а в том, что количество ошибок сократилось в среднем на 15-20%, а по отдельным справочникам вообще свелось к нулю.

Как в любой другой сфере использования ИИ, автоматизация обработки справочников требует валидации кода, поэтому совсем без человека мы не обойдемся. Именно человек будет проверять адекватность работы обработчика. Отмечу проблемы, с которыми столкнулись.

• На простые задачи типа заменить, совместить, сравнить ИИ генерирует хороший код. Остается только его просмотреть на предмет наименования листов, столбцов и строк. На многоэтапных задачах (сделай сначала это, потом вот это и затем еще и вот это) ИИ начинает спотыкаться, часто уже на втором шаге и, если совсем не разбираетесь в программировании, найти ошибку бывает очень проблематично. Поэтому желательно сложные задачи разбить на более мелкие, создать для них отдельный промт;

• Трудности с формулировкой самого промта. Далеко не каждый методолог может сформулировать, что он хочет получить, используя код. Промт надо писать, исходя из алгоритма ваших действий. Т. е. если вы выполняете некую последовательность действий, чтобы получить нужный результат, то достаточно его просто описать, ИИ это понимает. Вот, например, если вы хотите удалить все строки, в которых по второму столбцу в ячейках только “Y”. Если делать это вручную, то мы ставим фильтр на второй столбец, находим строки с “Y”, удаляем их. Вот так и пишем в промте:

Напиши макрос. На активном листе книги во втором столбце, если значение ячейки “Y”, удали всю строку.

И здесь еще один совет. Если код что-то делает не так, по вашему мнению, или выпадает в ошибку, нужно тоже прямо так и писать:

удаляет, но в конце все равно выпадает в ошибку на сроке: 
 If UCase(Trim(ws.Cells(i, 2).Value)) = "Y" Then

• Сбой при работе самого макроса. К сожалению, такое тоже случается. Макрос обработчика просто зависает или отрабатывает некорректно. Для оценки корректности работы обработчика мы придумали дополнительные    маячки-подсказчики. Например, автоматическое заполнение реестра изменений, подсчет количества строк или столбцов и т.п. После того, как отработал обработчик, есть возможность посмотреть, что изменилось и оценить корректность его работы. Например, для контроля корректности работы макроса по обновлению справочника формул, после его отработки, формируется вот такой отчет на отдельном листе:

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

Надеюсь, опыт нашей команды методологов воодушевит вас на более активное использование ИИ в обработке файлов Excel. Поделитесь своим мнением и опытом “красных линий”: что можно доверять ИИ, а что вы предпочитаете делать сами и никогда не доверите ИИ. Как вы проверяете корректность сгенерированного кода? Может быть, есть какой-то более универсальный подход?

Статья написана в рамках ХабраЧелленджа 6.0, который прошел в ЛАНИТ весной 2026 года. О том, что такое ХабраЧеллендж, читайте здесь.