Умение сравнивать два массива данных в Excel часто пригождается для людей, обрабатывающих большие объемы данных и работающих с огромными таблицами. Например, сравнение может быть использовано в выявлении повторяющихся значений, корректности занесения данных или внесение данных в таблицу в срок. В статье ниже описаны несколько приемов сравнения двух столбцов с данными в Excel.
Использование условного оператора ЕСЛИ
Метод использования условного оператора ЕСЛИ отличается тем, что для сравнения двух столбцов используется только необходимая для сравнения часть, а не весь массив целиком. Ниже описаны шаги по реализации данного метода:
Разместите оба столбца для сравнения в колонках A и B рабочего листа.
В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));"";A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.
Использование формулы подстановки ВПР
Принцип работы формулы аналогичен предыдущей методике, отличие заключается в использовании формулы ВПР, вместо ПОИСКПОЗ. Отличительной особенностью данного метода также является возможность сравнения двух горизонтальных массивов, используя формулу ГПР.
Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.
Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце 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 и щелкните кнопку выполнить.
После выполнения макроса, результат должен быть следующим:
Использование надстройки Inquire
С запуском версии 2013, Excel обзавелась интересной надстройкой под названием Inquire, которая позволяет сравнивать и анализировать два файла Excel. Если вам необходимо сравнить две версии одного и того же файла, например, после создания книги, ваши коллеги внесли кое-какие изменения, и вам нужно определить что именно они изменили, воспользуйтесь инструментом WorkbookCompare надстройки Inquire. Подробнее о том, как устанавливать, запускать и использовать надстройку читайте в моей предыдущей статье про надстройку Inquire.
Итог
Итак, мы рассмотрели несколько способов сравнения данных в Excel, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.
Спасибо, у вас очень понятно и красиво оформлено, глаз радует
для меня трудность- понять работу ПОИСКОЗ. Если не трудно сделайте пост с пояснениями по данной формуле.
Добрый день, Сергей. Про формулы подстановки можете прочитать в одной из моих предыдущих статей.
Молодца. Читаю Ваши статьи, наглядно и доходчиво, Спасибо.
Огромное спасибо!
Благодаря приведенной Вами формуле =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));"";A2) я смогла сравнить два списка (9 и 2 тысячи позиций в каждом).
Но выплыла другая проблема. В списках есть одинаковые данные, отличающиеся только значком *. После выполнения формулы были отмечены, как совпадающие, и данные с * и без *. Что нужно поменять в формуле, чтобы она возвращала только точные совпадения?
Спасибо.
Пришлите пример, пожалуйста, не совсем понял ситуацию. Видимо сравнение идет по формулам, а с ними уже посложнее будет
Добрый день, Ренат!
Пробовала с помощью вашей формулы сравнить два столбца с датами, затем с договорами. К сожалению, не получается. Ячейки получаются пустыми, хотя большинство значений совпадают (но excel их тотально не видит). Подскажите, в чем может быть ошибка?
Пришлите, пожалуйста, файл с примером, посмотрим
Доброго времени суток!
Спасибо за полезную статью!
Сравнение прошло успешно, но при попытке сохранить результат сравнения «Export Result» выходит ошибка «Unable to save the export file. Error: Exception from HRESULT: 0x800AC472» и ничего не сохраняется. Не знаете в чём может быть дело? Office 2013 Home and Bussiness Windows 8.1 Pro
Забыл добавить! Для сравнения использовал Inquire.
Добрый день, Антон. Честно говоря, не сталкивался с подобной проблемой, поэтому чем-то конкретным помочь не могу. Но официальном сайте данная ошибка описана, если это вам поможет, скидываю ссылку на страницу
Ренат, спасибо большое за статью! Очень пригодилась в сравнении формула! 🙂
День добрый, Ренат
статья хорошая, доступно)))
Огромная просьба, рассмотрите мою проблему.
Чаще требуется не просто 2 столбца данных сравнить, а сравнить два прайса. Индентификатором будет код или артикул — а при совпадении значений надо сопоставить цены. НАпример А-артикулы основного массива, В-Цены основного массива, Д-Артикулы сравниваемого массива и Е-цены сравниваемого массива. При совпадении артикула в А и Д в столбик С копировать цену из соответствующего Е.
Обычно по фирмам прайсы составлены по разному, артикулы разбросаны и чтоб сравнить цены полдня (в лучшем случае) убиваешь на рутину((((
Файл: -09-12-14-4.xls
Использую сводные таблицы для этих целей.
Второй файл вдогонку
Файл: Makita-21.01-tarjous.xlsx
Александр, судя по описанной задаче, в ячейке С1 должна находится формула =ЕСЛИ(A1=D1;E1;""). Ее необходимо будет протянуть до конца таблицы.
Ренат, добрый день!
Может быть и мне подскажите (перевернула кучу информации, но так и не сообразила как реализовать)
Есть номенклатура поставщика, в ее названии указан и код продукции. У меня в программе код вынесен в отдельный столбец. При помощи какой функции можно найти код в ЧАСТИ наименования
Пример:
Ищем «046N0511» в «Бочонок 1.1/4″ х 3/4″ Standart (046N0511) никель.»
PS/впр пробовала — не помогло
Буду очень-очень благодарна
Юлия, если код всегда заключен в скобки и число знаков кода одинаковое, то наиболее оптимально использовать формулу =ПСТР(A1;НАЙТИ(«(«;A1)+1;8), где в ячейке A1 будет просматриваемый текст
Спасибо! Толково написано. Очень помогло. Буквально пару минут и я сделал то, что грозило испортить весь день 🙂
Ренат, здравствуйте!
Пожалуйста, помогите решить проблему, над которой мы бьемся не первый день.
Есть два файла, в первом наименования номенклатуры с ценами, а во втором ведется подсчет себестоимости. Как сделать так, чтобы подставляя буквенные значения (название) в расчетную таблицу, Excel автоматически подставлял цены, согласно наименованиям из первой таблицы. Заранее спасибо.
Файл: help.xls
Вам необходимо использовать формулы подстановки, например, ВПР. Почитайте мою статью на эту тему про формулы подстановки в Excel, думаю, вам многое станет понятным. Примерная формула, которой вам нужно воспользоваться будет выглядеть следующим образом: =ВПР(A3;'[Файл с ценами.xlsx]Лист1′!$C$1:$F$22;4;0)
Спасибо большое! Формула с ВПР сработала на отлично!
Здравствуйте Ренат,
ваш 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
Загружаю образец файла.
Файл: Table_compare.xlsx
Ренат, добрый день, подскажите пжл., возможно ли сопоставить столбцы с наименованиями совпадающими частично, например, «картофель весовой» и «Картофель, кг»
Нет, нельзя
Здравствуйте! Подскажите, пожалуйста, как сравнить 3 одних столбца с другими 3-мя? Так как в примере «ИСПОЛЬЗОВАНИЕ УСЛОВНОГО ОПЕРАТОРА ЕСЛИ» для сравнения одного столбца с другим.
Здравствуйте! есть три столбца, это «артикул» «полугодие» и «количество» сравнить надо с другой таблицей, где так же «артикул» «полугодие» и «количество». Артикула могут повторяться, получается нужно взять сумму «артикула» определенного «полугодия» и сравнить с суммой «артикула» такого же «полугодия» но из другой таблицы. важно итоговое расхождение (т.е + или — по количеству) Пожалуйста подскажите как это можно реализовать, спасибо.
Здравствуйте, Ренат. У меня возникла проблема с использованием функции =ЕСЛИ(А1=D1;Е1;»»).Так как в одной из таблиц отсутствуют некоторые значения(таблица 1 содержит не все строки таблицы 2), функция выполняется только до того момента, пока условная нумерация в таблице 1 совпадает с нумерацией в таблице 2. Что в таком случае делать?
Здравствуйте, Ренат!
У меня возник вопрос: как можно упростить проверку актов сверок в эксель? Есть два столбца, с суммами реализации — наш и контрагента. Не всегда наши суммы совпадают с суммами контрагента. Надо выявить эти разногласия. Формула СЧЁТЕСЛИ не подходит, т.к одни и те же суммы в столбцах могут повторяться. Подскажите пожалуйста как это можно сделать? Спасибо!
Подскажите пожалуйста.вас сделать чтобы подсчитывать .чтобы значение если оно меньше 100 то умножалось на 3, а если больше 100 то умножалось на 7. ? Спасибо.
Помогите пожалуйста, мне нужно сравнить Столбец С5 с столбцом А. Если совпадает, тогда данные найденной строки с В копировать в D5. Если нет найденного значения, то ничего не проставлять, или «0» тоже сгодится. Заранее признателен.
Файл: -фомула.xlsx
как мне из одного столбца вычислить второй столбец и в третий вставить ответ? надоело каждую строку вычислять
А возможно сравнить два листа в таблице?
Браво, вас посетила замечательная мысль
—
пися мамы а также кончил в тетю рассказ
А стандартными метода можно сравнить?
Можно.. Вот статья https://www.planetaexcel.ru/techniques/14/99/#6653
Молодец автор ! Не все Foreach умеют пользоваться в нынешнее время.. Я по старинке сравниваю простым FOR https://hammga.ru/blogs?id=2 🙂
Здравствуйте! Молоток! VBA — это вещь, что захотел все написал, поддерживаю! Но начиная с версии 2007 Excel поддерживает сравнение стандартными методами без формул и макросов: https://hammga.ru/blogs?id=40
я не жалею что посмотрела
—
gps ошейник магазин а также купить фонарик шокер
Ринат, спасибо за прекрасное изложение. Как сравнить два столбца через макросы понятно, но передо мной стоит ещё одна задача.
Есть два столбца с товарной номенклатурой, в одном столбце номенклатура представлена без точек, в другом с ними.
Пример:T033.410.22.011.01
T0334102201101
Вопрос, как написать макрос, чтобы в столбце без точек поставить точки в нужном месте, аналогично первому столбцу, а потом сравнить эти два столбца и удалить несовпадающие значения?
Спасибо
Программа для удобного и быстрого сравнения списков https://www.aapsoftware.ru/product.php?id=57
Я обычно сравниваю тут https://сравнитьэксель.рф
Подскажите, как сравнить числа 2 столбцов в эксель если данные одинаковы только частично. Например заявка 5678 и заявка на ремонт 5666
Спасибо! Отличный макрос!
Подскажите только, как быть, если сравниваемый массив больше основного и совпадений может быть несколько? И их все нужно отобразить в повторяющихся значениях. В идеале ещё бы подтянуть данные из нескольких других столбцов к этим повторяющимся значениям…
Здравствуйте,
Можете помочь с моей задачей? Есть таблица с данными (ID, userID, date). UserID могут повторяться в таблице (в одном столбце) много раз.
Задача заменить значения в столбце userID на нужные мне значения, которые я прописал в отдельной таблице (две колонки «userID — name»). Простыми словами, userID хочу заменить именами.
суперррр спс
здравствуйте, нужно в столбце F найти совпадения номеров из столбца R, чтоб они выделились как например при Условном форматировании повторяющиеся значения.
Как это можно сделать?
Спасибо.
Спасибо!
Отличный сайт, а составитель его — умничка! :3
Здравствуй, автор.
Спасибо тебе большое.
Счастья тебе и всего-всего)
Спасибо автору из 2021 года!! Выручил
Безграничная благодарность автору!!!
Еще один привет из 2021 года )))