Найти все номера из первой таблицы которые есть во второй

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

При работе в Excel очень часто возникает потребность найти данные в одной таблице и извлечь их в другую. Если вы ещё не умеете это делать, то, прочитав статью, вы не только научитесь этому, но и узнаете, при каких условиях вы сможете выжать из системы максимум быстродействия. Рассмотрено большинство весьма эффективных приёмов, которые стоит применять совместно с функцией ВПР.

Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

Функция ВПР имеет четыре параметра:

=ВПР( <ЧТО> ; <ГДЕ> ; <НОМЕР_СТОЛБЦА> [;<ОТСОРТИРОВАНО>] ), тут:

<ЧТО> — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

<ГДЕ> — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра <ЧТО>;

<НОМЕР_СТОЛБЦА> — номер столбца в диапазоне, из которого будет возвращено значение;

<ОТСОРТИРОВАНО> — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Как же конкретно работает формула ВПР

Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром <ЧТО>, в противном случае возвращается код ошибки #Н/Д (#N/A).

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

Формулы можно использовать для распределения значений по диапазонам.
Если первый столбец <ГДЕ> содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II

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

Производительность работы функции ВПР

Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:

мне нужен более мощный компьютер;
мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Некоторые аспекты применения формулы в реальной жизни

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

Разный формат данных

Если первый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а первый столбец массива содержит числа в правильном формате, то поиск будет неудачным. Возможна и обратная ситуация. Проблема легко решается переводом параметра 1 в необходимый формат:

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

Двойное отрицание —D7.
Умножение на единицу D7*1.
Сложение с нулём D7+0.
Возведение в первую степень D7^1.

Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.

Массив <ГДЕ>

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

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

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт, а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Спасибо. 🙂

Найти все номера из первой таблицы которые есть во второй

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

что-то не получается у меня с этим макросом.

Может делаю что-то не так?

Вот, что я делаю.

Создаю следующий макрос:
[vba]

Option Explicit
Option Compare Text

With Sheets(«Open Incidents»)
With .Range(«D1:G» & .Cells(Rows.Count, 4).End(xlUp).Row)
x = .Value
.Columns(4).Offset(1).Interior.Color = xlNone
End With
.Activate
End With

For i = 2 To UBound(x)
If .Exists(x(i, 1)) Then
If .Item(x(i, 1)) <> x(i, 4) Then Cells(i, 7).Interior.ColorIndex = 45
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

Читать:

Три тип как определить

Потом, чтоб я не выделил и когда б я не попытался выполнить его, получаю следующее сообщение:

Run-time error ‘9’:

Subscript out of range

Contnue End Debug Help

При чём кнопка «Continue» не активна.

что-то не получается у меня с этим макросом.

Может делаю что-то не так?

Вот, что я делаю.

Создаю следующий макрос:
[vba]

Option Explicit
Option Compare Text

With Sheets(«Open Incidents»)
With .Range(«D1:G» & .Cells(Rows.Count, 4).End(xlUp).Row)
x = .Value
.Columns(4).Offset(1).Interior.Color = xlNone
End With
.Activate
End With

For i = 2 To UBound(x)
If .Exists(x(i, 1)) Then
If .Item(x(i, 1)) <> x(i, 4) Then Cells(i, 7).Interior.ColorIndex = 45
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

Потом, чтоб я не выделил и когда б я не попытался выполнить его, получаю следующее сообщение:

Run-time error ‘9’:

Subscript out of range

Contnue End Debug Help

При чём кнопка «Continue» не активна. pavelasd

Сообщение что-то не получается у меня с этим макросом.

Может делаю что-то не так?

Вот, что я делаю.

Создаю следующий макрос:
[vba]

Option Explicit
Option Compare Text

With Sheets(«Open Incidents»)
With .Range(«D1:G» & .Cells(Rows.Count, 4).End(xlUp).Row)
x = .Value
.Columns(4).Offset(1).Interior.Color = xlNone
End With
.Activate
End With

For i = 2 To UBound(x)
If .Exists(x(i, 1)) Then
If .Item(x(i, 1)) <> x(i, 4) Then Cells(i, 7).Interior.ColorIndex = 45
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

Потом, чтоб я не выделил и когда б я не попытался выполнить его, получаю следующее сообщение:

Run-time error ‘9’:

Subscript out of range

Contnue End Debug Help

При чём кнопка «Continue» не активна. Автор — pavelasd
Дата добавления — 01.05.2014 в 22:57

итак, показываю лист, в нём уже обе таблицы.

Мой второй вопрос ещё. Что не так у меня с формулой ВПР? Как-то ни в какую не хочет работать. Посмотрите, пожалуйста, ячейку F2. Не смотря на то, что этот номер инвойса во второй таблице есть, всё равно пишет #Н/А.
И ещё не пойму, почему у номеров инвойсов второй таблицы зелёненький уголок в верхнем левом углу ячейки. Ведь формат ячейки и информация в них такая же, как и в первой таблице у номеров инвойсов, но там нет этой зелёной штучки. Собственно в причине этого, наверное, таится и причина неправильного отображения результата формулы, так как стоит только два раза кликнуть по номеру инвойса во второй таблице, как он тут же исчезает и результат формулы становится правильным (да). Но, как понимаете при десятках тысяч позиций вариант нажатия на каждый номер — не вариант.

итак, показываю лист, в нём уже обе таблицы.

Сообщение итак, показываю лист, в нём уже обе таблицы.

ЗЫ: А макрос, видимо, по дороге потерялся

ЗЫ: А макрос, видимо, по дороге потерялся ShAM

ЗЫ: А макрос, видимо, по дороге потерялся Автор — ShAM
Дата добавления — 02.05.2014 в 04:58

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

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

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

Точно такой же? Ну тогда ясно — нет листов
Sheets(«Incident Management»)
и
Sheets(«Open Incidents»)
Вот когда они будут — тогда и поговорим.

По поводу макросов — они «живут» и не теряются пожалуй во всех других форматах файлов Экселя. Но вот в xlsx ну никак.

По поводу макросов — они «живут» и не теряются пожалуй во всех других форматах файлов Экселя. Но вот в xlsx ну никак. Hugo

Сообщение Точно такой же? Ну тогда ясно — нет листов
Sheets(«Incident Management»)
и
Sheets(«Open Incidents»)
Вот когда они будут — тогда и поговорим.

По поводу макросов — они «живут» и не теряются пожалуй во всех других форматах файлов Экселя. Но вот в xlsx ну никак. Автор — Hugo
Дата добавления — 02.05.2014 в 10:57

эх, вновь проблема с функцией ВПР. Некоторые номера инвойсов, которые есть во второй таблице отмечаются в первой таблице как #Н/А, не знаю, быть может это потому, что в первой таблице этот номер инвойса встречается несколько раз, а во второй меньше раз и в первой отмечается этот номер инвойса только столько же раз, сколько он встречается во второй? как же этого избежать? Например, номер инвойса в ячейке B137403. Функция ВПР в столбце F.
К сожалению, файл весит больше 100 Kb, поэтому закинул его на файлообменник.

А вот уж нет уж. У Правил есть исключения, но только с разрешения админа. Урезайте файл. Ссылка удалена.
Кстати, вы что, хотите кинуть сюда 137 ТЫСЯЧ строк? Да еще с ВПРом? С вами все в порядке?

Сообщение эх, вновь проблема с функцией ВПР. Некоторые номера инвойсов, которые есть во второй таблице отмечаются в первой таблице как #Н/А, не знаю, быть может это потому, что в первой таблице этот номер инвойса встречается несколько раз, а во второй меньше раз и в первой отмечается этот номер инвойса только столько же раз, сколько он встречается во второй? как же этого избежать? Например, номер инвойса в ячейке B137403. Функция ВПР в столбце F.
К сожалению, файл весит больше 100 Kb, поэтому закинул его на файлообменник.

Как получить записи из первой таблицы которых нет во второй?

Отбор через массив: 1
Отбор через левое соединение: 1
Отбор через не В: 189
Отбор через не В (Exists): 1
Отбор через внутреннее соединение + индекс: 1
Отбор через не В + индекс: 1
Отбор через не В (Exists) + индекс: 1

дисквалифицировали В за унылость, увеличили повторения до 10:

Количество: 162 168 отбор: 8 108 повторений: 10
Отбор через массив: 18
Отбор через соединение: 16
Отбор через В (Exists): 17
Отбор через соединение + индекс: 16
Отбор через В + индекс: 17
Отбор через В (Exists) + индекс: 17

(16) Поэксприментировал немного с планами запросов.
Есть сомнение по корректности теста на exist. Конкретное на

судя по тому во что 1С транслирует такую конструкцию для скуля ( из v8: Оператор "ИЛИ", конструкция "ВЫБОР" и вероятность фуллскана при них ) там получается

AND EXISTS(SELECT
1
FROM #tt2 T2 WITH(NOLOCK)
WHERE (T1._Fld18873RRef = T2._Q_001_F_000RRef) AND (T1._Fld18875RRef = T2._Q_001_F_001RRef))

В случае если второе поле Истина, мне кажется, при трансляции его просто пропустят и останется exist по одному полю.

При этом для in и exist по одному полю строятся абсолютно одинаковые планы запроса.

Select
id
From sc72
Where id in (select id from #tempkl)

Select
id
From sc72 as sc
Where exists (select id from #tempkl where )

а вот для exsist по двум полям уже другой план c hash match

Select
id
From sc72 as sc
Where exists (select id from #tempkl where and sp81=sc.sp81)

(28) Согласен, но меня смущает именно второе поле Истина. Лучше бы может дернуть во временную таблицу реальное поле вместо Истина.

К тому же план для

Select
id
From sc72 as sc
Where exists (select id from #tempkl where and 1=1)

SQL как получить данные из первой таблицы с условием наличия данных во второй по определенному значению?

Друзья, уже всю голову сломал, подскажите, пожалуйста, как построить запрос со следующими условиями.
Есть две таблицы, допустим, article и photo.
У каждой статьи из таблицы article может быть, от 0 до 5 фотографий одного типа и от 0 до 5 фотографий другого типа. Допустим ‘before’ и ‘after’. Колонка в таблице ‘photo’ имеет название ‘made’.
Одна строка в таблице ‘photo’ это одна фотография с пометкой ‘before’ или ‘after’ в соответствующем поле.

Запрос на получение статей, для которых вообще нет фотографий вот такой:

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

Найти все номера из первой таблицы которые есть во второй

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

Синтаксис

Как же конкретно работает формула ВПР

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

Следствия для формул вида II

Производительность работы функции ВПР

Недостатки формулы

Некоторые аспекты применения формулы в реальной жизни

Диапазонный поиск

Поиск текстовых строк

Борьба с пробелами

Разный формат данных

Как подавить выдачу #Н/Д

Массив <ГДЕ>

Использование функции СТОЛБЕЦ для указания колонки извлечения

Создание составного ключа через &»|»&

Найти все номера из первой таблицы которые есть во второй

Как получить записи из первой таблицы которых нет во второй?

SQL как получить данные из первой таблицы с условием наличия данных во второй по определенному значению?

Найти все двузначные числа которые делятся на n

Найти все четырехзначные числа у которых все цифры различны с

Похожие статьи