☰

Объединение двух столбцов для создания списка данных

tag-icon • tag-icon microsoft-excel vba
Объединение двух столбцов для создания списка данных

В настоящее время у меня есть два столбца, которые нужно сравнить. Столбец А, Лист 1 и Столбец А, Лист 2.

Лист 1 содержит:

A      B       C
5000   Apples  WI
6182   Oranges NY
7271   Grapes  MN
2293   Peanuts FL

Лист 2 содержит:

A
4032
5233
7271
2293

Должен получить результаты, подобные...

7271   Grapes  MN
2293   Peanuts FL

Мне нужно отобразить только те результаты, которые содержат то же число, что и на Листе 2. Есть ли лучший способ, чем загрузка Листа 2 в массив и сравнение его с каждой ячейкой на Листе 1?

    'For i = 1 to Sheet1LastRow

    Sheet2Row = Range("A" & Rows.Count).End(xlUp).Row
    Sheet2Array = Range("A2:A" & Sheet2LastRow).Value
            For i = LBound(Sheet2Array, 1) To UBound(Sheet2Array, 1)

    'if cell = Sheet2Array(i, 1)
     '....
    'End if

    Next i

   'Next Cell sheet 1

решение1

Это основная функция =VLOOKUP().

Синтаксис:

=VLOOKUP(
         compare this cell, 
         to the cells in the leftmost column of this range,
         returning the corresponding value from this column index,
         true/false for range lookup (just leave this false if you aren't sure)
 )

На листе 2 в ячейке B1 вы должны поместить =VLOOKUP(A1,Sheet1!A:B,2,False)и в ячейке C1 вы должны поместить=VLOOKUP(A1,Sheet1!A:C,3,False)

Эта функция работает между рабочими листами так же хорошо, как и между рабочими книгами (но вам придется включить ссылки и быть внимательным к изменениям в обеих рабочих книгах). VBA не требуется, это простая встроенная функция.

Расширяя это, вы, вероятно, получите #N/Aошибки для значений, которых нет в исходной таблице. Оберните формулу, =IFERROR( your vlookup() function , "" )чтобы заменить любые ошибки пустой ячейкой.

решение2

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

Если вы хотите использовать формульный подход, то это можно сделать с помощью немного сложных функций ИНДЕКС и ПОИСКПОЗ в формуле массива. Как только у вас будут соответствующие данные в смежных ячейках в столбце, просто примените VLOOKUP, чтобы извлечь оставшиеся два столбца из вашей главной таблицы в Sheet1.

Вот как. Ниже приведены два снимка экрана Лист1 и Лист2.

введите описание изображения здесь

введите описание изображения здесь

На Листе 1 находится ваша главная таблица в столбцах A, B и C, на Листе 2 находится ваш список, который нужно сопоставить со столбцом A из Листа 1.

Теперь на Листе 2 в Ячейке C1 поместите следующую Формулу массива

{=INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISERROR(MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),"",MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),ROW(1:1)))}

Вставьте эту формулу без фигурных скобок и в строке формул нажмите CTRL+SHIFT+ENTER, чтобы создать формулу массива, и перетащите ее вниз, пока не получите ошибку в ячейках или длину вашего списка для сопоставления. Теперь все строки над ячейками с ошибками имеют совпадающие ячейки из столбца A листа Sheet1.

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

Теперь в D1 введите относительно простую формулу ВПР, чтобы получить следующий совпадающий столбец из Листа1.

=VLOOKUP(C1,Sheet1!$A$1:$C$4,2,FALSE)

А в Е1 поместите формулу

=VLOOKUP(C1,Sheet1!$A$1:$C$4,3,FALSE)

и перетащите оба вниз. У вас есть желаемый список.

Вы можете использовать встроенную опцию Excel Evaluate Formula для оценки комбинации INDEX MATCH, чтобы почувствовать, как это работает. Она создает массив совпадающих номеров строк и выбирает первое, затем второе, затем третье наименьшее число по мере того, как вы перетаскиваете формулу вниз.

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

Связанный контент