Руководство Excel · поиск
Сопоставить два списка Excel без ВПР
ВПР находит код в одном списке и возвращает значение из той же строки, например цену по коду товара. Здесь показано, как работает формула, откуда берётся #Н/Д и когда лучше ПРОСМОТРХ; затем два списка сопоставляются в Nexell фразой вместо формулы. Полный синтаксис функции смотрите в справке по функции ВПР.
Бесплатный аккаунт · без карты · 30 кредитов в месяц · Не готовы регистрироваться? Вставьте текст и попробуйте
Как это работает
Как записывается формула ВПР
-
1
Выберите искомое значение
В ячейке результата введите =ВПР( и щёлкните ячейку со значением, которое нужно найти, здесь это код товара в A2. В русском Excel части формулы разделяются точкой с запятой.
-
2
Задайте диапазон поиска
Вторая часть — диапазон, в первом столбце которого находится искомое значение: Октябрь!$A$2:$C$7. Если список на другом листе, впереди пишут имя листа и восклицательный знак, а диапазон закрепляют клавишей F4, ведь формулу будут копировать вниз.
-
3
Посчитайте столбец
Третья часть — номер столбца, который нужно вернуть, внутри диапазона. Диапазон начинается со столбца A, поэтому цена в столбце C имеет номер 3.
-
4
Попросите точное совпадение через ЛОЖЬ
В конце напишите ЛОЖЬ. Если оставить пусто, Excel ищет приблизительное совпадение и в несортированном списке может вернуть значение другой строки. Если кода нет в списке, в ячейке появится #Н/Д. Искать можно и функцией ПРОСМОТРХ, которой не нужно считать столбцы.
Почему Nexell
Чем Nexell отличается
Сразу в обе стороны
ВПР смотрит в одну сторону. Скажите «сравни, чего не хватает» — и записи, которые есть в обоих списках и только в одном, окажутся в одной таблице.
Не нужно считать столбцы
Вы называете общий столбец; номер столбца, как в ВПР, считать не нужно. ИИ понимает запрос, поэтому писать можно на многих языках.
Значения сверяются с файлами
Каждое значение, которое ИИ записывает в таблицу, ищется в тексте ваших файлов. Значение, которое не найдено точно, не удаляется, а помечается в столбце Check.
Разница и процент
При сравнении попросите разницу цен и процент изменения; каждое добавляется отдельным столбцом.
Сначала смотрите, потом скачиваете
Таблица сначала открывается на экране. Ничто из набранного вами не меняет её, пока вы не подтвердите предпросмотр.
Ваш файл не сохраняется
Он остаётся в памяти нашего сервера только на время обработки и никогда не записывается на диск. Ваш запрос и нужные ему данные таблицы (строки небольшого файла, иначе названия столбцов и несколько значений) уходят нашему поставщику услуг ИИ с замаскированными персональными данными. Подробнее (на английском)
Ограничения
Что стоит знать заранее
- Сопоставление может не остаться формулой. Найденные значения записываются в ячейки как значения, поэтому в файле может не быть формулы ВПР. Если один из списков изменится, запустите сопоставление снова.
- Проверяйте сопоставление в предпросмотре. Столбец Check лишь сообщает, встречается ли значение в ваших файлах, а не что оно сопоставлено с нужной строкой; просмотрите несколько строк, прежде чем нажать «Подтвердить и применить».
- ИИ читает ваш запрос. Запросы на многих языках работают; непонятый запрос не применяется и не расходует кредиты.
- ИИ понимает ваши запросы. Команды можно писать на других языках; резервный движок, который включается, когда ИИ недоступен, требует английского или турецкого.
- Ограничения по размеру: бесплатный тариф: таблицы до 5 000 строк и файлы до 10 МБ; Pro — 100 000 строк и 50 МБ; Team — 200 000 строк и 100 МБ.
Вопросы и ответы
ВПР и сопоставление двух списков: частые вопросы
Как подтянуть данные с другого листа с помощью ВПР?
Поставьте перед диапазоном поиска имя листа и восклицательный знак: =ВПР(A2;Октябрь!$A$2:$C$7;3;ЛОЖЬ). Если в имени листа есть пробел, возьмите его в одинарные кавычки, например 'Цены октября'!$A$2:$C$7. Проще всего переключиться на другой лист во время ввода формулы и выделить диапазон мышью.
Почему ВПР возвращает #Н/Д?
Искомое значение не найдено в первом столбце диапазона. Три самые частые причины: кода действительно нет в списке; в конце ячейки есть невидимый пробел (уберите его функцией СЖПРОБЕЛЫ); в одном списке число, а в другом текст, поэтому текст «1001» не находит число 1001. ЕСЛИОШИБКА превращает ошибку в сообщение на ваш выбор.
Чем ВПР отличается от ПРОСМОТРХ?
ПРОСМОТРХ принимает диапазон для возврата вместо номера столбца, может вернуть значения слева от столбца поиска, по умолчанию ищет точное совпадение и позволяет указать, что показать, если ничего не найдено. Она есть в Microsoft 365 и Excel 2021 и новее; в старых версиях используйте ВПР или ИНДЕКС с ПОИСКПОЗ.
Пишет ли Nexell формулу ВПР при сопоставлении двух списков?
Обычно нет: найденные строки записываются рядом как значения, поэтому в файле нет формулы ВПР. При сравнении можно также попросить разницу и процент изменения. Если один из списков изменится, запустите сопоставление снова.
Сколько кредитов стоит сопоставление двух списков?
Чтение файлов CSV и Excel не расходует кредиты за страницы. Преобразование текста в таблицу и каждая команда стоят 1 кредит за 1 000 строк. Скачивание результата кредитов не стоит; бесплатный тариф даёт 3 скачивания и 30 кредитов в месяц, карта не нужна.
Похожее
Другие способы работать с данными
- Сводная таблица ExcelПодведите итоги длинного списка по региону, месяцу или товару шаг за шагом.
- Условное форматированиеОкрасьте строки, превысившие порог, добавьте гистограммы.
- Генератор формул ExcelОпишите вычисление и получите рабочую формулу Excel.
- НДС в ExcelРазделите сумму с НДС на сумму без НДС и НДС живыми формулами.
- Текст в ExcelРазделите вставленный список на столбцы с именем, телефоном и суммой.
- CSV в ExcelПравильный разделитель, кириллица не ломается.
Хватит сравнивать два списка построчно.
Создайте бесплатный аккаунт, загрузите два файла и скачайте сопоставленную таблицу в виде файла Excel.
Попробовать бесплатно