Как сравнить два столбца в Excel — методы сравнения данных Excel

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

Использование условного оператора ЕСЛИ

Метод использования условного оператора ЕСЛИ отличается тем, что для сравнения двух столбцов используется только необходимая для сравнения часть, а не весь массив целиком. Ниже описаны шаги по реализации данного метода:

Разместите оба столбца для сравнения в колонках A и B рабочего листа.

87-1-сравнение двух массивов

В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));"";A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.

87-2-сравнение двух столбцов

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

Принцип работы формулы аналогичен предыдущей методике, отличие заключается в использовании формулы ВПР, вместо ПОИСКПОЗ. Отличительной особенностью данного метода также является возможность сравнения двух горизонтальных массивов, используя формулу ГПР.

Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.

87-3-сравнение двух столбцов

Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.

Использование макроса VBA

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

Создайте новый модуль кода VBA и введите следующий код. О том, как писать макросы вы можете прочитать более подробно в статье написания VBA кода.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
' Установка переменной CompareRangeравной сравниваемому диапазону
Set CompareRange = Range("B1:B11")
' Если сравниваемый диапазон находится на другом листе или книге,
' используйте следующий синтаксис
' Set CompareRange = Workbooks("Книга2"). _
'   Worksheets("Лист2").Range("B1:B11")
'
' Сравнение каждого элемента в выделенном диапазоне с каждым элементом
' переменной CompareRange
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 2) = x
Next y
Next x
End Sub

В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.

Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_Matches и щелкните кнопку выполнить.

87-4-сравнение двух столбцов

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

87-5-сравнение двух столбцов

Использование надстройки Inquire

С запуском версии 2013, Excel обзавелась интересной надстройкой под названием Inquire, которая позволяет сравнивать и анализировать два файла Excel. Если вам необходимо сравнить две версии одного и того же файла, например, после создания книги, ваши коллеги внесли кое-какие изменения, и вам нужно определить что именно они изменили, воспользуйтесь инструментом WorkbookCompare надстройки Inquire. Подробнее о том, как устанавливать, запускать и использовать надстройку читайте в моей предыдущей статье про надстройку Inquire.

Итог

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


53 комментария

  1. Спасибо, у вас очень понятно и красиво оформлено, глаз радует
    для меня трудность- понять работу ПОИСКОЗ. Если не трудно сделайте пост с пояснениями по данной формуле.

  2. Огромное спасибо!
    Благодаря приведенной Вами формуле =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));"";A2) я смогла сравнить два списка (9 и 2 тысячи позиций в каждом).

    Но выплыла другая проблема. В списках есть одинаковые данные, отличающиеся только значком *. После выполнения формулы были отмечены, как совпадающие, и данные с * и без *. Что нужно поменять в формуле, чтобы она возвращала только точные совпадения?
    Спасибо.

  3. Добрый день, Ренат!
    Пробовала с помощью вашей формулы сравнить два столбца с датами, затем с договорами. К сожалению, не получается. Ячейки получаются пустыми, хотя большинство значений совпадают (но excel их тотально не видит). Подскажите, в чем может быть ошибка?

  4. Доброго времени суток!
    Спасибо за полезную статью!
    Сравнение прошло успешно, но при попытке сохранить результат сравнения «Export Result» выходит ошибка «Unable to save the export file. Error: Exception from HRESULT: 0x800AC472» и ничего не сохраняется. Не знаете в чём может быть дело? Office 2013 Home and Bussiness Windows 8.1 Pro

  5. День добрый, Ренат
    статья хорошая, доступно)))
    Огромная просьба, рассмотрите мою проблему.
    Чаще требуется не просто 2 столбца данных сравнить, а сравнить два прайса. Индентификатором будет код или артикул — а при совпадении значений надо сопоставить цены. НАпример А-артикулы основного массива, В-Цены основного массива, Д-Артикулы сравниваемого массива и Е-цены сравниваемого массива. При совпадении артикула в А и Д в столбик С копировать цену из соответствующего Е.
    Обычно по фирмам прайсы составлены по разному, артикулы разбросаны и чтоб сравнить цены полдня (в лучшем случае) убиваешь на рутину((((

    Файл:  -09-12-14-4.xls

  6. Ренат, добрый день!
    Может быть и мне подскажите (перевернула кучу информации, но так и не сообразила как реализовать)
    Есть номенклатура поставщика, в ее названии указан и код продукции. У меня в программе код вынесен в отдельный столбец. При помощи какой функции можно найти код в ЧАСТИ наименования
    Пример:
    Ищем «046N0511» в «Бочонок 1.1/4″ х 3/4″ Standart (046N0511) никель.»
    PS/впр пробовала — не помогло
    Буду очень-очень благодарна

  7. Ренат, здравствуйте!

    Пожалуйста, помогите решить проблему, над которой мы бьемся не первый день.
    Есть два файла, в первом наименования номенклатуры с ценами, а во втором ведется подсчет себестоимости. Как сделать так, чтобы подставляя буквенные значения (название) в расчетную таблицу, Excel автоматически подставлял цены, согласно наименованиям из первой таблицы. Заранее спасибо.

    Файл:  help.xls

  8. Здравствуйте Ренат,

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

    Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    “Set CompareRange = Workbooks(«Equipment_list_VBA»).Worksheets(«Tabelle5»).Range(«D5:D381»)
    For Each x In Workbooks(«Equipment_list_VBA»).Worksheets(«Tabelle1»).Range(«B5:B412»)
    For Each y In CompareRange
    If x = y Then Workbooks(«Equipment_list_VBA»).Worksheets(«Tabelle6»).x.Offset(0, 20) = x
    Next y
    Next x
    End Sub

    Добавляя Лист в код: Worksheets(«Лист5»).Range(«….») макрос ругается 🙂
    И вторая проблема, которая возможно требует пару других макрооператоров: когда сравниваются два столбца, и при этом те значения которые совпали, записываются в новую ячейку. При этом не переносятся все значения строки. Подскажите пожалуйста, как это можно реализовать. (То есть это простое сравнивание двух таблиц по двум колонкам при помощи макроса)

    • Sub Compare()
      For j = 1 To 1632
      b1 = val(Cells(j, 1).Value)
      For i = 1 To 10369
      a1 = Val(Cells(i, 2).Value)
      If a1 = 0 Then
      Exit For
      End If
      DoEvents
      If b1 = a1 Then
      Cells(i, 2).Value = «»
      DoEvents
      Exit For
      End If
      Next i
      Next j
      End Sub

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

  10. Здравствуйте! Подскажите, пожалуйста, как сравнить 3 одних столбца с другими 3-мя? Так как в примере «ИСПОЛЬЗОВАНИЕ УСЛОВНОГО ОПЕРАТОРА ЕСЛИ» для сравнения одного столбца с другим.

  11. Здравствуйте! есть три столбца, это «артикул» «полугодие» и «количество» сравнить надо с другой таблицей, где так же «артикул» «полугодие» и «количество». Артикула могут повторяться, получается нужно взять сумму «артикула» определенного «полугодия» и сравнить с суммой «артикула» такого же «полугодия» но из другой таблицы. важно итоговое расхождение (т.е + или — по количеству) Пожалуйста подскажите как это можно реализовать, спасибо.

  12. Здравствуйте, Ренат. У меня возникла проблема с использованием функции =ЕСЛИ(А1=D1;Е1;»»).Так как в одной из таблиц отсутствуют некоторые значения(таблица 1 содержит не все строки таблицы 2), функция выполняется только до того момента, пока условная нумерация в таблице 1 совпадает с нумерацией в таблице 2. Что в таком случае делать?

  13. Здравствуйте, Ренат!
    У меня возник вопрос: как можно упростить проверку актов сверок в эксель? Есть два столбца, с суммами реализации — наш и контрагента. Не всегда наши суммы совпадают с суммами контрагента. Надо выявить эти разногласия. Формула СЧЁТЕСЛИ не подходит, т.к одни и те же суммы в столбцах могут повторяться. Подскажите пожалуйста как это можно сделать? Спасибо!

  14. Подскажите пожалуйста.вас сделать чтобы подсчитывать .чтобы значение если оно меньше 100 то умножалось на 3, а если больше 100 то умножалось на 7. ? Спасибо.

  15. Помогите пожалуйста, мне нужно сравнить Столбец С5 с столбцом А. Если совпадает, тогда данные найденной строки с В копировать в D5. Если нет найденного значения, то ничего не проставлять, или «0» тоже сгодится. Заранее признателен.

  16. Ринат, спасибо за прекрасное изложение. Как сравнить два столбца через макросы понятно, но передо мной стоит ещё одна задача.
    Есть два столбца с товарной номенклатурой, в одном столбце номенклатура представлена без точек, в другом с ними.
    Пример:T033.410.22.011.01
    T0334102201101
    Вопрос, как написать макрос, чтобы в столбце без точек поставить точки в нужном месте, аналогично первому столбцу, а потом сравнить эти два столбца и удалить несовпадающие значения?
    Спасибо

  17. Подскажите, как сравнить числа 2 столбцов в эксель если данные одинаковы только частично. Например заявка 5678 и заявка на ремонт 5666

  18. Спасибо! Отличный макрос!
    Подскажите только, как быть, если сравниваемый массив больше основного и совпадений может быть несколько? И их все нужно отобразить в повторяющихся значениях. В идеале ещё бы подтянуть данные из нескольких других столбцов к этим повторяющимся значениям…

  19. Здравствуйте,
    Можете помочь с моей задачей? Есть таблица с данными (ID, userID, date). UserID могут повторяться в таблице (в одном столбце) много раз.
    Задача заменить значения в столбце userID на нужные мне значения, которые я прописал в отдельной таблице (две колонки «userID — name»). Простыми словами, userID хочу заменить именами.

  20. здравствуйте, нужно в столбце F найти совпадения номеров из столбца R, чтоб они выделились как например при Условном форматировании повторяющиеся значения.
    Как это можно сделать?
    Спасибо.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *