Признание перед статьёй
Я работаю с таблицами 11 лет. Из них 5 лет — притворялся, что умею в ВПР.
В резюме у меня была строчка «продвинутый Excel». На собеседованиях я уверенно кивал. На рабочих звонках говорил: «Сейчас гляну, через час пришлю». Потом 40 минут гуглил «впр пример формула», копировал из чужого файла, менял буквы колонок наугад — и каким-то чудом оно работало в 70% случаев.
В оставшиеся 30% — я писал коллеге: «Слушай, у меня тут странная ошибка #Н/Д, не подскажешь?». Коллега смотрел и шёпотом фиксил.
Это длилось 5 лет. Стыдно? Да. Поделюсь рецептом, как я перестал.
🤔 Микроопрос. Признайтесь честно (анонимно, никто не узнает): пишете ли вы ВПР сами с нуля?
а) Да, легко
б) Списываю из старых файлов и меняю буквы
в) Прошу коллегу
г) Делаю руками без формулы (копи-паст)
>
Напишите буквой в комментариях. Соберу статистику, в Telegram расскажу распределение. Подозреваю, что (а) — меньшинство.
Что такое ВПР в одном предложении (для тех, кто гуглит каждый раз)
ВПР = «найди в этой таблице это значение и верни мне то, что стоит рядом».
Например:
· «У меня есть прайс-лист с 500 позициями.
· Я хочу: ввожу артикул товара — получаю цену.»
Без ВПР — листаешь руками.
С ВПР — формула за тебя ищет.
Звучит просто. Но если открыть справку Excel — там 4 аргумента, какие-то «ложь/истина», «диапазон поиска», и через 3 минуты ты уже не помнишь, зачем сюда зашёл.
Почему я не любил ВПР
Причина 1: 4 аргумента, которые не запоминаются.
ВПР(что_искать; где_искать; номер_колонки_откуда_взять; точное_совпадение).
Каждый раз я путался: какой по счёту аргумент про «точное».
Причина 2: оно работает только если искомое — в первой колонке таблицы.
Если артикул в колонке D, а нужно вернуть цену из B — ВПР не справляется. Нужно перестраивать таблицу или использовать ИНДЕКС+ПОИСКПОЗ. Это вторая боль уровня магистра.
Причина 3: ошибки криптографические.
#Н/Д, #ССЫЛКА, #ИМЯ. Что они значат? Где искать причину? Гугл выдаёт 50 форумов, и в каждом своё.
5 лет я обходил это стороной. Пока в один день не догадался — есть же ChatGPT, GigaChat, DeepSeek. Они знают Excel. Я могу просто сказать словами, что хочу.
Промпт, который открыл мне ВПР заново
С тех пор я ВПР пишу так. Один универсальный шаблон, в который меняю детали.
У меня есть две таблицы в Excel.
ТАБЛИЦА 1 (на листе "Заказы"):
- Колонка A: артикул товара
- Колонка B: количество заказанных штук
ТАБЛИЦА 2 (на листе "Прайс"):
- Колонка A: артикул товара
- Колонка B: цена за штуку
- Колонка C: остаток на складе
Задача: в Таблице 1, в колонке C, я хочу автоматически
подставлять цену из Таблицы 2 по артикулу.
Дай формулу. Опиши пошагово:
1. Что делает каждая часть формулы
2. Куда её вставлять
3. Что сделать, если будет ошибка #Н/Д
ИИ возвращает:
Формула для ячейки C2 (Таблица 1):
=ВПР(A2; Прайс!A:C; 2; ЛОЖЬ)
Расшифровка:
- A2 — артикул, который ищем
- Прайс!A:C — таблица, в которой ищем
- 2 — номер колонки, из которой берём (Цена)
- ЛОЖЬ — точное совпадение (не приблизительное)
Ошибка #Н/Д означает: артикул из Таблицы 1 не нашёлся
в Таблице 2. Возможные причины:
- лишний пробел в начале/конце
- разный регистр
- пустая ячейка
Как защититься:
=ЕСЛИОШИБКА(ВПР(A2; Прайс!A:C; 2; ЛОЖЬ); "Не найден")
Я просто копирую формулу. Она работает.
Если что-то не работает — скриншот ошибки в чат, и через 30 секунд знаю причину.
4 кейса из моей реальной работы (с готовыми промптами)
Кейс 1. Соединить два списка клиентов из разных систем
У меня выгрузка из CRM (3500 клиентов) и выгрузка из бухгалтерии (2800 клиентов). Нужно понять, кто есть в одной, но нет в другой.
Промпт:
Я маркетолог в B2B. У меня два листа в Excel.
Лист "CRM":
- Колонка A: ИНН клиента
- Колонка B: Название
- Колонка C: Менеджер
Лист "Бухгалтерия":
- Колонка A: ИНН
- Колонка B: Сумма оплат за апрель
Задача: в листе "CRM" в колонке D показать сумму оплат
из бухгалтерии. Если клиента нет в бухгалтерии — пишем "не платил".
Дай формулу для D2.
ИИ выдаёт =ЕСЛИОШИБКА(ВПР(A2; Бухгалтерия!A:B; 2; ЛОЖЬ); "не платил"). Готово, тяну вниз — все 3500 строк закрашены за 4 секунды.
Кейс 2. ВПР, когда искомое — НЕ в первой колонке
Беда: артикул в колонке D, а нужно вернуть название из B. ВПР тут не работает (он умеет искать только в первой колонке диапазона).
Промпт:
У меня таблица:
A — Категория
B — Название товара
C — Цена
D — Артикул
В другой таблице у меня артикулы. Хочу по артикулу
вытащить название (колонка B).
Сложность: артикул находится в колонке D, а название
в колонке B — то есть слева от того, по чему ищу.
ВПР не справляется. Дай альтернативу.
ИИ возвращает связку ИНДЕКС+ПОИСКПОЗ:
=ИНДЕКС(B:B; ПОИСКПОЗ(искомый_артикул; D:D; 0))
С пошаговым объяснением. Раньше я бы 2 часа гуглил это и не понял.
Кейс 3. ВПР возвращает #Н/Д, хотя глазами вижу — значение есть
Самая бесячая ошибка. Артикул точно есть в обеих таблицах, но формула выдаёт #Н/Д.
Промпт:
ВПР возвращает #Н/Д, хотя я глазами вижу, что значение
в таблице есть. Что проверить в первую очередь?
ИИ выдаёт чек-лист:
1. Лишний пробел (самая частая причина — "АБВ123 " ≠ "АБВ123"). Решение: =СЖПРОБЕЛЫ(A2).
2. Разный тип данных — в одной таблице число, в другой текст (число «12345» и текст «12345» — разные).
3. Регистр — для текстовых значений Excel чувствителен.
4. Невидимые символы (символ табуляции, неразрывный пробел) — проверить через ДЛСТР().
Я раньше час искал — теперь 30 секунд через чек-лист.
Кейс 4. Подставить значения сразу из НЕСКОЛЬКИХ колонок
У меня прайс-лист, по артикулу нужно вытащить и цену, и остаток, и категорию одной формулой.
Промпт:
По одному артикулу хочу вытащить сразу 3 значения:
цена, остаток, категория. Не писать ВПР три раза.
Дай вариант с одной функцией.
ИИ возвращает решение через ВПР с массивом или ПРОСМОТРX (если у вас Excel 365 / 2021). С объяснением, что в старом Excel — иначе.
Что я понял за эти полгода с ИИ-Excel
Понял 1: я не глупый. У ВПР просто плохой UX.
4 аргумента в одной строке без подсказок, ошибки на иврите, два режима совпадения — это плохой интерфейс. ИИ выступает посредником: я говорю по-человечески, он переводит в синтаксис Excel.
Понял 2: 80% задач Excel — это 5 формул.
ВПР, СУММЕСЛИМН, ИНДЕКС+ПОИСКПОЗ, ЕСЛИ, СЖПРОБЕЛЫ. Если эти 5 знает ИИ — 80% моей работы автоматизировано.
Понял 3: GigaChat и YandexGPT тоже знают Excel.
Сначала я думал, что только ChatGPT. Оказалось — GigaChat выдаёт корректные формулы на русском, YandexGPT тоже. Для русскоязычных задач (с русскими названиями колонок) GigaChat иногда даже точнее. Подробнее буду сравнивать в четверг.
Понял 4: DeepSeek и Qwen — для Excel избыточны, но прекрасны для VBA-скриптов.
Если нужно автоматизировать что-то посложнее формулы — макрос, скрипт — китайские модели справляются великолепно. Об этом в одной из статей серии.
Подвохи (предупреждаю заранее)
Подвох 1. Если в вашем Excel английские названия функций (VLOOKUP вместо ВПР) — попросите ИИ выдать формулу на английском. Иначе он напишет «=ВПР(...)», а у вас не сработает. Просто добавьте в промпт: «у меня английский Excel».
Подвох 2. Если таблица очень большая (>100 000 строк) — ВПР работает медленно. ИИ предложит ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX, оба быстрее.
Подвох 3. ИИ не видит вашу таблицу. Описывайте структуру колонок словами. Не просите «помоги с моим Excel» — это бесполезно. Опишите, что в колонке A, что в B, что в C.
Вывод за 30 секунд
ВПР — не сложная функция. Сложен её синтаксис.
ИИ убирает синтаксис из вашей жизни. Вы говорите задачу словами — получаете готовую формулу.
Это сэкономит вам в среднем 30-60 минут в день, если вы работаете с таблицами. И уберёт стыд за 5 лет «продвинутого Excel» в резюме.
Я больше не пишу формулы руками. И не извиняюсь за это.
💬 Поделитесь: какая формула в Excel вам казалась самой непостижимой? Возможно, в одной из следующих статей разберём её отдельно.
Что в TG-канале
Только что выложил продолжение — ещё 4 формулы Excel, которые я больше не пишу руками: СУММЕСЛИМН, УНИК+СЧЁТЕСЛИ, ИНДЕКС+ПОИСКПОЗ и сводные таблицы «на словах».
Подписаться: https://t.me/duyunov_znaet
В среду в Telegram — настоящие скриншоты переписки с GigaChat, как я разбирал с мамой её квитанцию ЖКХ. Финал неожиданный.
Серия «Нейросети без воды» — что дальше
· 6 мая (ср): Главный кейс недели — мама и ЖКХ через GigaChat
· 7 мая (чт): Сравнение 4 моделей на 5 задачах
· 8 мая (пт): Контент-план на месяц за 15 минут
🔗 https://t.me/duyunov_znaet — закладка + подписка = ничего не пропустите.