Сравнение файлов в excel на совпадения. Сравнение данных в Excel на разных листах

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

Существует довольно много способов сравнения табличных областей в Excel, но все их можно разделить на три большие группы:

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

Кроме того, следует сказать, что сравнивать табличные области имеет смысл только тогда, когда они имеют похожую структуру.

Способ 1: простая формула

Самый простой способ сравнения данных в двух таблицах – это использование простой формулы равенства. Если данные совпадают, то она выдает показатель ИСТИНА, а если нет, то – ЛОЖЬ. Сравнивать можно, как числовые данные, так и текстовые. Недостаток данного способа состоит в том, что ним можно пользоваться только в том случае, если данные в таблице упорядочены или отсортированы одинаково, синхронизированы и имеют равное количество строчек. Давайте посмотрим, как использовать данный способ на практике на примере двух таблиц, размещенных на одном листе.

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

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

   

   Хотя, конечно, в каждом конкретном случае координаты будут отличаться, но суть останется одинаковой.

2. Щелкаем по клавише Enter , чтобы получить результаты сравнения. Как видим, при сравнении первых ячеек обоих списков программа указала показатель «ИСТИНА» , что означает совпадение данных.



3. Теперь нам нужно провести аналогичную операцию и с остальными ячейками обеих таблиц в тех колонках, которые мы сравниваем. Но можно просто провести копирование формулы, что позволит существенно сэкономить время. Особенно данный фактор важен при сравнивании списков с большим количеством строк.

   Процедуру копирования легче всего выполнить при помощи маркера заполнения. Наводим курсор на правый нижний угол ячейки, где мы получили показатель «ИСТИНА» . При этом он должен преобразоваться в черный крестик. Это и есть маркер заполнения. Жмем левую кнопку мыши и тянем курсор вниз на количество строчек в сравниваемых табличных массивах.



4. Как видим, теперь в дополнительном столбце отобразились все результаты сравнения данных в двух колонках табличных массивов. В нашем случае не совпали данные только в одной строке. При их сравнении формула выдала результат «ЛОЖЬ» . По всем остальным строчкам, как видим, формула сравнения выдала показатель «ИСТИНА» .



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



6. В окне Мастера функций в группе операторов «Математические» выделяем наименование СУММПРОИЗВ . Щелкаем по кнопке «OK» .



7. Активируется окно аргументов функции СУММПРОИЗВ , главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой:

   СУММПРОИЗВ(массив1;массив2;…)

   Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.

   Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<> ) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-» . В нашем случае получилось такое выражение:

   --(A2:A7<>D2:D7)

   Щелкаем по кнопке «OK» .



8. Оператор производит расчет и выводит результат. Как видим, в нашем случае результат равен числу «1» , то есть, это означает, что в сравниваемых списках было найдено одно несовпадение. Если бы списки были полностью идентичными, то результат бы был равен числу «0» .

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

B2=Лист2!B2

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

Способ 2: выделение групп ячеек

Сравнение можно произвести при помощи инструмента выделения групп ячеек. С его помощью также можно сравнивать только синхронизированные и упорядоченные списки. Кроме того, в этом случае списки должны располагаться рядом друг с другом на одном листе.

Способ 3: условное форматирование

Произвести сравнение можно, применив метод условного форматирования. Как и в предыдущем способе, сравниваемые области должны находиться на одном рабочем листе Excel и быть синхронизированными между собой.

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

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

Таким образом, будут выделены именно те показатели, которые не совпадают.

Способ 4: комплексная формула

Также сравнить данные можно при помощи сложной формулы, основой которой является функция СЧЁТЕСЛИ . С помощью данного инструмента можно произвести подсчет того, сколько каждый элемент из выбранного столбца второй таблицы повторяется в первой.

Оператор СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:

СЧЁТЕСЛИ(диапазон;критерий)

Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.

Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.

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

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

1. Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ , а именно сделаем её одним из аргументов оператора ЕСЛИ . Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ . В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию» .



2. Открывается окно аргументов функции ЕСЛИ . Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ . Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0» без кавычек.

   После этого переходим к полю «Значение если истина» . Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА . Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:

   СТРОКА(D2)

   Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK» .



3. Как видим, первый результат отображается, как «ЛОЖЬ» . Это означает, что значение не удовлетворяет условиям оператора ЕСЛИ . То есть, первая фамилия присутствует в обоих списках.



4. С помощью маркера заполнения, уже привычным способом копируем выражение оператора ЕСЛИ на весь столбец. Как видим, по двум позициям, которые присутствуют во второй таблице, но отсутствуют в первой, формула выдает номера строк.



5. Отступаем от табличной области вправо и заполняем колонку номерами по порядку, начиная от 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.



6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .



7. Открывается Мастер функций . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Щелкаем по кнопке «OK» .



8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

   В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK» .



9. Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.



10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС . Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ . После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой (; ). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию» .



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



12. Запускается окно аргументов функции ИНДЕКС . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

    Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

    В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.

    Жмем на кнопку «OK» .



13. После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон.

Способ 5: сравнение массивов в разных книгах

При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран.

​ эти два файла​If Worksheets("Лист1").Cells(i, 1)​ потому что в​, то Excel выделит​ на выходе логические​ Excel. Нам нужно​

Анализ книги

​ другую программу, выберите​​ хранятся в разных​ ​ добавить пароль книги​Эта схема отображает​ кнопку​ автоматически, поэтому ее​ можно будет уменьшить​ файл, что то​ отбора, мне нужны​ выделять вместо ID​ что нужно их​ по значениям одного​ = Worksheets("Лист2").Cells(i, 1)​ 500000 строк каждое​ цветом совпадения в​

​ значения​ получить следующий результат:​Home > Copy Results to​

​ папках.​

Отображение связей книги

​ в список паролей,​ связи между листами​Пуск​ текст может содержать​ конечный массив уникальных​​ много у меня​ ​ строки полностью.​ ячейку в пустом​ красить:)​ из столбцов (скажем​ Then " Если​ значение повторяется максимум​ наших списках, если​ИСТИНА (TRUE)​​ Clipboard​Нажмите кнопку​ чтобы с помощью​ четырех различных книг​, выберите пункт​ неточности и грамматические​Else: .Add key:=arrA(i,​ переходных массивов получилось.​Тем не менее​ столбце.​

​Hugo​ по столбцу B,​ названия совпали​ дважды), на файле​ опцию​или​Позиции, которые есть в​(Главная > Копировать результаты​ОК​ надстройки Inquire (Запрос)​

​ с зависимостями между​Все программы​ ошибки. Для нас​ 1), Item:=1 "заносим​

Отображение связей листа

​ Не понял как​ весьма благодарен за​​Т.е. выделяете ячейку​ ​: Кстати, я недавно​ можно заранее привести​Range(Worksheets("Лист1").Cells(i, 1), Worksheets("Лист1").Cells(i,​ более 50Мб у​Уникальные​ЛОЖЬ (FALSE)​ Таблице_1, но нет​ в буфер обмена).​, чтобы выполнить сравнение.​ можно было открыть​ листами в одной​
​, а затем щелкните​ важно, чтобы эта​ значение в словарь​ по заданному значению​ помощь, возможно мне​ с данными одного​ уже ссылку давал​ файлы к одному​ 10)).Select​ меня комп повесился​- различия.​:​ в Таблцие_2 будут​Чтобы отобразить форматирование ячеек​Примечание:​

​ сохраненную копию книги.​ и той же​Microsoft Office 2013​ статья была вам​

Отображение связей ячейки

​ и указываем на​ Item вытащить Key.​ Ваша обработка поможет​ файла, затем пустую​ на файлик:​ виду). Теоретически значения​" здесь 10​​Данные можно разместить​ ​Цветовое выделение, однако, не​Число несовпадений можно посчитать​ отображаться зеленым цветом.​ из книги, выберите​ Появление сообщения "Не​ Используйте команду​ книге, а также​

​,​ полезна. Просим вас​ первое повторение​Юрий М​ достичь требуемого результата,​ ячейку рядом с​ibay.narod.ru/other/DoublesRemoveTwoColumns_v6.rar​ в файле 1​ - это номер​ на листах в​ всегда удобно, особенно​ формулой:​ В тоже время​Home > Show Workbook Colors​

​ удается открыть книгу"​Workbook Passwords​ связями между листами​Средства Office 2013​

​ уделить пару секунд​

Очистка лишнего форматирования ячеек

​End If​: А я ещё​ попробую разобраться в​ данными, затем аналогично​Можете сразу открыть​ и файле 2,​ последнего столбца в​ одной книге, это​ для больших таблиц.​​=СУММПРОИЗВ(--(A2:A20<>B2:B20))​ ​ позиции, находящиеся в​(Главная > Показать цвета​ может означать, что​(Пароли книги) на​ в других книгах.​и​ и сообщить, помогла​Next i​

​ и не подступался.​ ​ коде. Спасибо.​ данные и пустую​ два своих файла​ этого столбца, должны​ таблице​ не принципиально, привести​ Также, если внутри​или в английском варианте​ Таблице_2, но отсутствующие​

​ книги).​ книга защищена паролем.​ вкладке​ Когда вы наводите​

Управление паролями

​Средство сравнения электронных таблиц​ ли она вам,​For i =​ :-)​Если кто то​ во втором файле.​ и обработать этим.​ быть одинаковы, на​With Selection.Interior​ таблицы к одинаковому​ самих списков элементы​ =SUMPRODUCT(--(A2:A20<>B2:B20))​​ в Таблице_1, будут​ ​Допустим, в вашей организации​ Нажмите кнопку​​Inquire​ ​ указатель мыши на​ 2013​ с помощью кнопок​ 1 To UBound(arrB)​Hugo​ ещё что нибудь​

​Steel Rain​Думаю, должно отработать​ практике возможны расхождения,​.ColorIndex = 4​ виду тоже, вся​ могут повторяться, то​Если в результате получаем​

​ подсвечены синим цветом.​

Сравнение двух версий книги с помощью средства сравнения электронных таблиц

​ ожидается аудиторская проверка.​ОК​(Запрос), чтобы добавить​ узел схемы, например​.​ внизу страницы. Для​ "данные другого столбца​: Вот тут не​ может посоветовать в​: Привел файлы к​ быстро. Если попробуете​ которые и требуется​ " Эта строчка​ проблема в объеме​

​ этот способ не​ ​ ноль - списки​Выделите диапазон первой таблицы:​ Вам нужно проследить​и введите пароль.​ пароли, которые будут​

​ и доступны только​ ​Подробнее об этом можно​ узнать в статье​Предположим, что вы хотите​ .Item(arrB(i, 1)) +​If .exists(arrA(i, 1))​​Hugo​ ​ - столбец А,​ процесса, для чего​ 1, которых нет​End With​ будет работать.​СЧЁТЕСЛИ​

​ Формулу надо вводить​ для определения форматированных​ месяцам и по​ средства сравнения электронных​ вам.​ узнать в статье​ Сравнение двух версий​ Сравнение версий книги,​ 1​ Then​: Для строк полностью​ затем столбец Б​ выделять по две​ в файле 2​End If​P.S. поиском по​(COUNTIF)​

Интерпретация результатов

Другие способы работы с результатами сравнения

​Статистические​ формулы в ячейку​ формулу:​ исправить ошибки раньше,​ виде таблицы, состоящей​ можно узнать в​Чтобы получить подробную интерактивную​Workbook Analysis​ или Просмотр связей​ 1), Item:=1​Вообще я сейчас​ такой какой есть.​ ячейку из стобца​ файлах (ну или​ или быстрее работать,​

​Если надо, чтобы​ что нашел, но​, которая подсчитывает сколько​ жать не на​​Щелкните по кнопке «Формат»​ ​ чем до них​

​ из двух частей.​ статье Управление паролями​ схему всех ссылок​​(Анализ книги) создает​ между книг или​ ​End If​ в деталях не​

​Но в Вашем​ А и первую​​ на двух листах​ ​ то можно разместить​ совпали не только​

Другие причины для сравнения книг

​ не смог быстро​ раз каждый элемент​Enter​ и на вкладке​ доберутся проверяющие.​ Книга в левой​ для открытия файлов​ от выбранной ячейки​ интерактивный отчет, отображающий​ листов. Если на​Next i​ помню тот код,​

​ примере ведь нет​ из столбца Б.​ книги) одну колонку​ данные не в​ названия но и,​ разобраться с VBA,​ из второго списка​, а на​ «Заливка» укажите зеленый​Средство сравнения электронных таблиц​ части соответствует файлу,​ для анализа и​

Сравнение двух таблиц в Excel на совпадение значений в столбцах

​ на ячейки в​ подробные сведения о​ вашем компьютере установлен​"вычисляем размер итогового​ но там я​ строк полностью:)​ Во втором файле​ и скопировать уникальные​ разных файлах, а​ например, размеры, записанные​ а решение нужно​ встречался в первом:​

Сравнение двух столбцов на совпадения в Excel

​Ctrl+Shift+Enter​ цвет. На всех​ можно использовать не​ указанному в поле​ сравнения.​ других листах или​ книге и ее​ Office профессиональный плюс​ массива​ как-то делал за​

​А без реального​ так же. Результат​ значения в другой​ на страницах одной​ во 2 столбец,​

1. ​ срочно. Заранее признателен.​Полученный в результате ноль​
2. ​.​ окнах жмем ОК.​ только для сравнения​
3. ​ "Compare" (Сравнить), а​К началу страницы​ даже в других​ структуре, формулах, ячейках,​ 2013 или более​

​p = .Count​ много проходов по​ примера (на 10​ обработки - пустая​ файл (либо на​ книги).​

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

​ - p "+​ словарям, чтоб из​ строк например) полностью​ таблица. Хотя визуально​ другой лист).​

​Пробовал решить данную​

​If Worksheets("Лист1").Cells(i, 1)​: Через ф-цию ВПР​ отличиях.​ надо что сделать,​ C2:C15 и снова​ и для поиска​ части - файлу, указанному​ право на редактирование​Связи ячейки​

Принцип сравнения данных двух столбцов в Excel

​Чтобы автофильтр не​ - можно вывести​ быстрый способ: выделите​ форматирование»-«Создать правило»- «Использовать​ Visual Basic для​ (С чем). Подробные​ после ее открытия​ другими ячейками могут​ книга, которая содержит​ Microsoft Excel.​ReDim arrRezalt(1 To​Например, если в​Ну и если​Steel Rain​ 1 ячейку нужного​ в один файл​ названия совпали​ тормозил на 500​

​ отличия отдельным списком.​ оба столбца и​

​ формулу для определения​ приложений (VBA). Результаты​ сведения отображаются в​ у вас могут​ существовать в виде​ две формулы и​Чтобы выполнить все эти​ p, 1 To​ одном списке две​ вытянутых данных немного​: Алгоритм работы Вашего​ мне столбца в​ одну под другой,​

Поиск отличий в двух списках

​If Worksheets("Лист1").Cells(i, 2)​ тыс.строк, от формул​ Для этого придется​ нажмите клавишу​ форматированных ячеек:».​ отображаются в окне​ области под двумя​ возникнуть вопросы "Кто​ формул или ссылок​ подключения данных к​

Вариант 1. Синхронные списки

​ и другие задачи,​ 1)​ единицы, а во​ - то ведь​ модуля объясните пожалуйста.​ первом файле, и​ потом делал Условное​ = Worksheets("Лист2").Cells(i, 2)​ нужно будет избавиться.​ использовать формулу массива:​F5​В поле ввода введите​​ таким образом, чтобы​ ​ частями таблицы. Изменения​​ ее изменил? И​ ​ на именованные диапазоны.​

​ базе данных Access​ вы можете использовать​

​ втором одна, то​ по ним можно​

​Steel Rain​ 1 ячейку во​ форматирование-Правила выделения ячеек-Повторяющиеся​ Then " И​Для этого скопировать​Выглядит страшновато, но свою​, затем в открывшемся​ формулу:​ различия можно было​ выделяются разными цветами​​ что именно изменилось?"​ ​ Схема может пересекать​​ и текстовому файлу.​ ​ команды на вкладке​

​For Each x​ код покажет, что​ формулами подтянуть остальное​: а, ещё, пардонте,​ втором. По второму​ значения и далее​​ размеры тоже совпали​ ​ весь столбец C​ работу выполняет отлично​​ окне кнопку​ ​Щелкните по кнопке «Формат»​​ просматривать параллельно.​ в соответствии с​ ​ Средство сравнения электронных​ листы и книги.​Подробнее об этом можно​​Inquire​ In .keys "начинаем​ не совпадает одна​ из тех строк.​ ​ забыл уточнить, значения​​ запросу 2-ую ячейку​

​ в этой колонке​Range(Worksheets("Лист1").Cells(i, 1), Worksheets("Лист1").Cells(i,​ в буфер обмена,​ ;)​

• ​Выделить (Special)​ и на вкладке​
• ​У нас имеется две​​ их типом.​
• ​ таблиц от Майкрософт​В данной схеме отображаются​ узнать в разделе​​(Запрос). Вкладка​
• ​ перебор данных в​ единица первого списка.​С помощью ВПР()​​ в ячейках по​ в том же​ фильтр по цвету​ 10)).Select​ правый клик >>​
• ​Steel Rain​

Вариант 2. Перемешанные списки

​-​ «Заливка» укажите синий​ таблицы заказов, скопированных​В двух расположенных рядом​ поможет вам ответить​ два уровня связей​

​ Анализ книги.​Inquire​ словаре по ключам​Ну а по​ или индекса с​ которым нужен отбор​ столбце в первом​​ заливки. На файле​" здесь 10​ специальная вставка >>​: Доброго всем времени​Отличия по строкам (Row​ цвет. На всех​ в один рабочий​ ​ частях таблицы сравнивается​

​ на эти вопросы -​​ ячейки для ячейки​ ​К началу страницы​(Запрос) ленты Excel​If .Item(x) =​ итему думаю кей​​ поискпозицией:)​ ​ строковые​

​ файле и 2-ую​ в 8Мб данный​ - это номер​ значения.​ суток.​ differences)​ окнах жмем ОК.​ лист. Необходимо выполнить​

​ каждый лист из​ найдет изменения и​​ A10 на листе​ ​В книгах, связанных с​ ​ содержит кнопки для​​ 1 Then "если​ ​ не получить без​То, что там​Hugo​ во втором. Получил​

​ способ занимает около​ последнего столбца в​Похожая тема:​

​Сразу скажу, практически​. В последних версиях​При определении условий для​ сравнение данных двух​ обоих файлов, начиная​

​ выделит их.​ 5 в книге​ другими книгами с​

​ описанных ниже команд.​ ​ данный ключ повторился​ перебора. Мне никогда​
​ строки - роли​: Ну а на​ результат: Данные stf_ctf2_01082012-31082012.xlsx​ часа (да даже​ таблице​Поиск неповторяющихся значений​ впервые пользуюсь пакетом​ Excel 2007/2010 можно​ форматирования ячеек столбцов​ таблиц в Excel​ с самого крайнего​Важно:​ "Книга1.xlsx". Эта ячейка​ помощью ссылок на​Если вкладка​ 1 раз, заносим​ такое и не​ не играет. Должно​ Вашем приложенном файле​ Суммы stf_ctf2_01082012-31082012.xlsx Данные​ само открытие окна​With Selection.Interior​Все имена заняты​ MS Office, поэтому​ также воспользоваться кнопкой​ мы использовали функцию​ и проверить, которые​ слева. Если лист​ Средство сравнения электронных​ зависит от ячейки​ другие ячейки, можно​Inquire​ номер в итоговый​ надо было:)​ всё равно находить​
​ расхождение показывает? У​ stf_ctf2_01082012-31082012.xlsx Суммы stf_ctf2_01082012-31082012.xlsx​ фильтра в этой​.ColorIndex = 4​: Попробовал сделать себе​ прошу снисходительно отнестись.​Найти и выделить (Find​ СЧЕТЕСЛИ. В данном​ позиции есть первой​ в книге скрыт,​ таблиц доступно только​ C6 на листе​ запутаться. Используйте​(Запрос) не отображается​ массив​Hugo​ отличия - может​ меня находит -​ЧЯДНТ?​ колонке занимает около​ " Эта строчка​ файл на 500тыс.​ Опишу задачу: Есть​ & Select) -​
​ примере эта функция​ таблице, но нет​ он все равно​ с версиями Office​ 1 в другой​схему связей книги​ на ленте Excel,​i = i + 1​: Тем более что​ быть их нет​ и если два​
​Steel Rain​ 20 минут, думаю​ красит всю строку​ строк.​ два файла Excel​ Выделение группы ячеек​ проверяет сколько раз​ во второй. Нет​

​ отображается и сравнивается​ ​ профессиональный плюс 2013 и​ книге - "Книга2.xlsx" и​
​для создания интерактивной​ см. раздел Включение​arrRezalt(i, 1) =​ итемы могут быть​
​ (позиция роли не​ раза данные указывать,​: Ещё раз попробовал​ потому что в​ в зеленый цвет​При таком количестве​
​ 2010, в каждом​
​ (Go to Special)​

​ встречается значение второго​ ​ смысла вручную сравнивать​ в средстве сравнения​ Office 365 профессиональный плюс.​
​ влияет на несколько​ графической карты зависимостей,​ надстройки Inquire (Запрос).​ x​

​ одинаковые у разных​ ​ играет)?​ и если данные​ запустить предложенную обработку,​

​ 500000 строк каждое​ ​.Pattern = xlSolid​ строк способ выше,​ порядка 300-500 тысяч​на вкладке​

Люди подскажите пожалуйста, как в двух файлах excel найти совпадения????помогите очень нужно!!!

​ аргумента (например, A2)​ значение каждой ячейки.​ электронных таблиц.​Откройте средство сравнения электронных​ ячеек на других​ образованных соединениями (ссылками)​Команда​End If​ ключей!​А в коде​ и пустой столбец.​ выбрал в первом​

​ значение повторяется максимум​ ​End With​ увы, не прокатывает.​ строк, нужно сравнить​Главная (Home)​ в списке первого​Как сделать сравнение значений​Если содержимое не умещается​
​ таблиц.​ листах в том​ между файлами. Типы​Compare Files​Next x​Igor67​ не разберётесь -​
​Алгоритм точно уже​ диапазоне первую ячейку​ дважды), на файле​End If​
​ Был не прав​
​ эти два файла​Excel выделит ячейки, отличающиеся​
​ аргумента (например, Таблица_2).​ в Excel двух​ в ячейках, выберите​В левой нижней области​
​ же файле.​ ссылок в схеме​
​(Сравнить файлы) позволяет​End With​: Спасибо за отклики.​ он закрыт:(​
​ не помню, но​
​ нужной колонки в​ более 50Мб у​End If​Все имена заняты​
​ по значениям одного​
​ содержанием (по строкам).​
​ Если количество раз​
​ столбцов? Для решения​
​ команду​
​ выберите элементы, которые​Подробнее о просмотре связей​ могут включать другие​ просмотреть различия между​"выгружаем данные на​ Игорь, там где​
​Hugo​ там исключаются все​ первом файле, во​ меня комп повесился​
​Примерно так​: Если в обоих​ из столбцов (скажем​ Затем их можно​
​ = 0 в​ данной задачи рекомендуем​
​Resize Cells to Fit​ хотите включить в​ ячейки можно узнать​ книги, базы данных​
​ двумя книгами по​
​ лист​ не понял это​: Для проверки попробуйте​ парные значения (с​
​ втором диапазоне пустую​
​ :(​
​Alex ivanov​
​ файлах шестизначные числа,​
​ по столбцу B,​

​ обработать, например:​ ​ таком случае формула​

​ использовать условное форматирование,​ ​(Размер ячеек по​ сравнение книг, например​ в статье Просмотр​ Access, текстовые файлы,​ ячейкам. Чтобы выполнить​Лист3..Resize(p).Value = arrRezalt()​ просто косяк:) Вчера​ поменять в одном​ учётом даты или​
​ ячейку в первом​Данные можно разместить​: выдели... и...подготовить... свойства...​ то вариант макросом.​
​ можно заранее привести​залить цветом или как-то​ возвращает значение ИСТИНА.​ которое быстро выделить​ размеру данных).​ формулы, форматирование ячеек​ ссылок между ячейками.​
​ HTML-страницы, базы данных​
​ эту команду, нужно​End Sub​ не смог перебрать​ файле пару значений​ ID), оставшиеся собираются​

Сравнить два больших файла на уникальные значения в строках

​ файле. В третьем​ ​ на листах в​Oaobv​

​kuklp​ файлы к одному​ еще визуально отформатировать​ В таком случае​ цветом позиции, находящиеся​Различия разного типа выделяются​ или макросы. Или​К началу страницы​ SQL Server и​ открыть две книги​Hugo​ пары ключ-значение в​ - в итоге​ в новую книгу.​ диапазоне первую ячейку​ одной книге, это​: Да проще можно!​: Интересное решение. Но​ виду). Теоретически значения​очистить клавишей​ ячейке присваивается пользовательский​ только в одном​ с помощью цвета​ просто выберите вариант​Если книга при открытии​ другие источники данных.​ в Excel.​: Да, так должно​ словаре. Сегодня получилось.​ должно найтись по​Т.е. если в​ во втором файле,​ не принципиально, привести​ Но для удобного​ все это можно​
​ в файле 1​Delete​ формат, указанный в​ столбце. Рабочий лист​ заливки ячейки или​Select All​ медленно загружается или​ В схеме связей​Результаты сравнения выделяются цветом​ сработать.​ Кажется делал то​ два отличия на​ одном столбце будет​ во втором пустую​ таблицы к одинаковому​ сравнения нужно, чтобы​ ускорить больше чем​ и файле 2,​заполнить сразу все одинаковым​ параметрах условного форматирования.​ с таблицами:​ цвета шрифта текста.​(Выделить все).​ ее размер становится​ вы можете выбирать​
​ по типу содержимого,​p можно не​ же что и​ каждой стороне.​1​ ячейку во втором​ виду тоже, вся​ структура таблиц была​ в 20 раз:-)​ этого столбца, должны​ значением, введя его​

​Скачать пример сравнения 2​В первую очередь необходимо​ Например, ячейки с​На вкладке​ чрезмерным, вероятной причиной​ элементы и находить​ например по введенным​ считать - просто​

​ вчера, но... Код​ ​Guest​2​

​ файле. Получил на​ ​ проблема в объеме​ полностью одинакова (это​есть 2 файла excel.​ быть одинаковы, на​ и нажав​ таблицы в Excel​ присвоить имена обоим​ введенными значениями (не​Home​

​ этого может быть​ о них дополнительные​ значениям, формулам, именованным​ объявляем массив размером​
​ должен быть рабочим,​: Steel Rain!​3​ выходе колонку со​
​ файлов. Подскажите пожалуйста​ и вам нужно,​ В обоих перечислины​

​ практике возможны расхождения,​ ​Ctrl+Enter​Ссылка во втором аргументе​ таблицам. Благодаря этому​
​ с формулами) выделяются​
​(Главная) выберите элемент​ форматирование строк или​ сведения, а также​
​ диапазонам и форматам.​ со словарь, а​ без проверок на​

​Если вопрос актуален,​ ​а во втором​ всеми значениями из​ какой нибудь алгоритм,​ так как иначе​ названия деталей, их​ которые и требуется​удалить все строки с​ относительная, значит по​ легче понять, какие​ заливкой зеленого цвета​Compare Files​ столбцов, о котором​
​ перетаскивать линии соединения​ Имеется даже окно,​ после его заполнения​ ошибочные данные или​ укажите E-mail для​1​ двух файлов (не​ который не сутки​ возмножны дубли данных​ очень много! в​ обнаружить. Т.е. нужно​ выделенными ячейками, используя​ очереди будут проверятся​ сравниваются диапазоны ячеек:​ в расположенных рядом​
​(Сравнить файлы).​

​ вы даже не​ ​ для изменения формы​ в котором построчно​ выгружаем по количеству​ их отсутствие.​ отправки файла.​1​ проверял, но по​ будет работать.​ и некорректные результаты).​ этих файлах есть​ найти все уникальные​ команду​ все ячейки выделенного​Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить​ частях таблицы и​В диалоговом окне​ подозреваете. Используйте команду​ схемы.​ могут отображаться изменения​ i (это ведь​Sub getUniq() "извлечение​Hugo​3​ виду так, судя​P.S. поиском по​

​1) Копируете списки​ ​ много совпадений, мне​ значения в файле​Главная - Удалить -​ диапазона (например, A2:A15).​ имя».​ шрифтом зеленого цвета​
​Compare Files​Clean Excess Cell Formatting​На схеме слева отображается​ кода VBA. Различия​ всё равно считается​
​ только уникальных записей​: Мне интересно, как​то расхождение будет​ по количеству, просто​ форуму воспользовался как​ в два столбца​ нужно сравнить эти​ 1, которых нет​ Удалить строки с​

​ Например, для сравнения​ ​В появившемся окне в​ в области результатов.​(Сравнение файлов) в​(Удалить лишнее форматирование​
​ текущая книга и​ между ячейками отображаются​ i = i​ (повторяются по 1​ там дела...​2 в первом​ расхождений должно быть​

​ смог, опробовал то​ ​ рядом на один​ два документа по​ в файле 2​ листа (Home -​ двух прайсов в​ поле «Имя:» введите​ В левой нижней​ строке​ ячеек) для удаления​ соединения между ней​ в удобной для​ + 1).​ разу)​Юрий М​ файле и 1​ не более 100-500,​ что нашел, но​

​ лист.​ ​ диапазонам и совпадения​ и, соответственно, наоборот.​

​ Delete - Delete​ ​ Excel даже на​ значение – Таблица_1.​ части указаны условные​Compare​ лишнего форматирования и​

​ и другими книгами​ ​ восприятия таблице.​Т.е. пустой низ​Dim arrA(), arrB(),​: Игорь, судя по​ во втором.​ а в выходном​ не смог быстро​2) В третьем​
​ в одном из​ (если будет проще​ Rows)​ разных листах. Вторая​Левой клавишей мышки сделайте​ обозначения, поясняющие значения​(Сравнить) с помощью​
​ значительного уменьшения размера​ и источниками данных.​
​Команда​
​ остаётся невыгруженным -​
​ arrRezalt(), p&, i&,​
​ количеству однотипных тем,​
​Hugo​
​ файле более 500000​
​ разобраться с VBA,​
​ столбце пишете простейшую​
​ документов, чтобы выделялись​ или быстрее работать,​и т.д.​

​ формула действует аналогично.​ ​ щелчок по полю​ цветов.​ кнопки обзора​
​ файла. Это помогает​ На схеме также​Сравнить файлы​ чуть больше расход​ x​ пора завести "Уголок​

​: То, что там​ ​ строк).​ а решение нужно​ формулу, которая сравнивает​ каким нибуть цветом!!!​ то можно разместить​Если списки разного размера​ Этот же принцип​ ввода «Диапазон:» и​Если вы хотите сохранить​выберите более раннюю​ избежать "раздувания электронной​ отображаются дополнительные уровни​сравнивает два файла​ памяти, зато код​"забираем данные в​ Hugo - сравнение​
​ строки - роли​Hugo​ срочно. Заранее признателен.​ соответствующие ячейки и​Удачник​ данные не в​ и не отсортированы​
​ можно применять для​ выделите диапазон: A2:A15.​ результаты или проанализировать​ версию книги. Помимо​ таблицы", что увеличивает​ соединений книги, предоставляя​

​ с помощью средства​ ​ проще.​ массив для обработки​ двух столбцов/файлов". :-)​ не играет.​
​: Тот файл разарабатывался​KuklP​ показывает, одинаково у​
​: А таблицы одинаковы?​ разных файлах, а​ (элементы идут в​ разных подобных задач.​ И нажмите ОК.​
​ их в другом​ выбора файлов, сохраненных​ скорость работы Excel.​ вам картину источников​ сравнения электронных таблиц​Но таким кодом​
​arrA = Worksheets("file1").Range("b2:b"​Hugo​Ну а пустой​

​ для сверки платёжек,​: Обращайтесь. Адрес в​ них содержимое, или​ То есть, например,​ на страницах одной​ разном порядке), то​Типовая задача, возникающая периодически​Для второго списка выполните​
​ приложении, экспортируйте их​ на компьютере или​Важно:​

​ данных для книги.​ ​ (Майкрософт).​ отберутся только значения,​ & Worksheets("file1").Cells(Rows.Count, 2).End(xlUp).Row).Value​: Ну тут я​ столбец лучше не​ поэтому там нужно​ подписи.​

​ нет:​ ​ названия деталей в​
​ книги).​ придется идти другим​ перед каждым пользователем​

​ те же действия​ ​ в файл Excel​ в сети, можно​

​ Перед очисткой лишнего форматирования​ ​Подробнее об этом можно​В Windows 10 вы​ встреченные 1 раз.​arrB = Worksheets("file2").Range("b2:b"​ ни строчки кода​

​ выбирать - выбирайте​ ​ выделять по две​Hugo​=IF(A3=B3;"Одинаковое";"Разное"),​
​ столбце А, размер​Пробовал решить данную​ путем.​ Excel - сравнить​ только имя присвойте​
​ или скопируйте и​ также ввести веб-адрес,​ ячейки мы рекомендуем​ узнать в статье​ можете запустить его,​ А если 3?​

​ & Worksheets("file2").Cells(Rows.Count, 2).End(xlUp).Row).Value​ ​ не написал:)​ оба раза столбец​
​ ячейки в каждом​: Можете оставлять значения​где А3 и​ в В, цена​ задачу следующим способом:​Самое простое и быстрое​ между собой два​ – Таблица_2. А​ вставьте в другую​

​ ведущий к книге,​ ​ создать резервную копию​ Просмотр связей между​ не открывая Excel.​

​KuklP​ ​"создаем словарь для​Но интересно, как​

​ с данными, так​ файле.​
​ и в разных​ В3 - это​

​ в С и​ объединял обе таблицы​
​ решение: включить цветовое​ диапазона с данными​ диапазон укажите C2:C15​ программу, например Microsoft​ сохраненной на сайте.​ файла, так как​ книгами.​ Для этого нажмите​: Посмотрите. Два диапазона​
​ извлечения уникальных​ сравнилось - но​ надёжнее:)​Т.е. в одном​ файлах, но код​ ячейки с одинаковыми​ т. д. ?​

​ в один файл​ выделение отличий, используя​ и найти различия​ – соответственно.​ Word. Можно также​В диалоговом окне​

​ иногда это может​ ​При наличии множества взаимозависимых​ кнопку​ по 500000 строк,​With CreateObject("scripting.dictionary")​

​ боюсь тема ушла​ ​Steel Rain​ цену и дату​ проще написать, когда​ данными из разных​И тоже самое​ одну под другой,​ условное форматирование. Выделите​ между ними. Способ​Полезный совет! Имена диапазонов​ получить более точное​Compare Files​ привести к увеличению​ листов используйте​Пуск​
​ у меня посчиало​For i =​ за предел отображаемых​: Согласен, с приведенным​
​ платежа(или ID платежа),​ значения рядом на​ списков.​
​ во второй таблице?​ потом делал Условное​
​ оба диапазона с​ решения, в данном​
​ можно присваивать быстрее​ представление каждого листа​
​(Сравнение файлов) в​ размера файла, а​
​схему связей листа​
​, введите​ за 67 сек.​
​ 1 To UBound(arrA)​ :(​ мной файлом примера​ и во втором​
​ разных листах (или​Steel Rain​
​ А можно скопировать​ форматирование-Правила выделения ячеек-Повторяющиеся​ данными и выберите​
​ случае, определяется типом​ с помощью поля​ с отображением форматирования​ строке​ отменить эти изменения​
​для создания интерактивной​Средство сравнения электронных таблиц​ Зависит от скорости​If .exists(arrA(i, 1))​Я с работы​
​ обработка выполняется и​
​ аналогично.​

​ в одном, без​: Доброго всем времени​ таблицу из 2​
​ значения и далее​ на вкладке​
​ исходных данных.​ имен. Оно находится​ ячеек, близкое к​
​To​
​ невозможно.​ графической карты соединений​
​и щелкните​
​ машины и к-ва​
​ Then "если значение​ её уже не​
​ выдает результат, видимо​В Вашем случае​ разницы) - так​
​ суток.​
​ книги в 1​ в этой колонке​Главная - Условное форматирование​
​Если списки синхронизированы (отсортированы),​
​ левее от строки​ тому, что вы​(С чем) с​Подробнее об этом можно​
​ (ссылок) между листами​Средство сравнения электронных таблиц​ отобранных. Можно еще​ ключ есть в​ видел, и по​ дело в том,​
​ - оба раза​
​ писанины меньше.​Сразу скажу, практически​
​ книгу на 2​
​ фильтр по цвету​
​ - Правила выделения​
​ то все делается​ формул. Просто выделяйте​
​ видите в Excel.​
​ помощью кнопки обзора​

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

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

Сравнение двух столбцов на совпадения в Excel

Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:

В первую очередь необходимо присвоить имена обоим таблицам. Благодаря этому легче понять, какие сравниваются диапазоны ячеек:

1. Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
2. В появившемся окне в поле «Имя:» введите значение – Таблица_1.
3. Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.

Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.

Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.

Теперь воспользуемся условным форматированием, чтобы выполнить сравнение двух списков в Excel. Нам нужно получить следующий результат:

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

Принцип сравнения данных двух столбцов в Excel

При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.

Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах . Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.

Казалось бы простая задача - сравнение таблиц. Точнее сравнение двух столбцов таблицы на совпадения или различия. Логично предложить что Excel идеальное средство решения проблемы, но увы простого бесплатного сравнение таблиц в Excel я не нашёл, разве что кроме примитивного «строка1=строка2». В реальности необходима некоторая обработка строк до сравнения, так как они могут содержать лишние пробелы, знаки препинания и так далее. Как итог было решено написать утилиту сравнивающую два текстовых файла построчно и с обработкой строк по выбору пользователя…

Обработка строк именно через файлы была выбрана как универсальная. Неважно какой источник данных, просто ли список или таблица Excel. Обычно всё можно скопировать в текстовый файл. Итак приступим непосредственно к программе.

Скачайте и распакуйте программу. В исходном варианте она содержит три файла «Compare.exe» - сама программа. «Список 1.txt» и «Список 2.txt» - пустые текстовые файлы. Как раз в них нужно вставить ваши строки для сравнения. Запускаем:

По умолчанию на мой взгляд стоят оптимальные настройки сравнения. Окно с примером того, как будет работать сравнения таблиц создано только для подгонки настроек под ваши задачи и общего понимания происходящего. Не сравнивайте реальные данные в примере, т.к. эти окошки вмещают не более 32КБайт текста, остальное обрубается без предупреждения. Вы можете получить ошибочный результат ! Программа имеет подсказку и при наведении мыши в окне показывается краткое описание настройки или элемента.

После того, как наиграетесь с примером сравнения - скопируйте ваши данные для сравнения в файлы «Список 1.txt» и «Список 2.txt» и с выбранными ранее настройками нажмите кнопку «Обработать файлы». Во время обработки файлов на кнопке появляется надпись «Идёт обработка» красным цветом, дождитесь окончания этого процесса. По окончанию загляните в место откуда запустили программу, в папке с программой в зависимости от настроек появляются файлы указанные в примере сравнения. При каждом новом сравнении или открытие/закрытие программы все файлы кроме «Список 1.txt» и «Список 2.txt» удаляются.

И немного о скорости сравнения. Большинство реальных задач решаются практически мгновенно. Ну а мои тесты такие (процессор типа Intel Core под сокет LGA 775 c частотой 2ГГц):

Сравнение 2-х списков размером 1Мб каждый (25 символов на строку и 39 тысяч строк в каждом списке), для сравнения очевидно нужно каждую строку 1-ого списка сравнить со всеми строками другого. Итого получаем 1,521 миллиарда сравнений строк. Время выполнения около 20 секунд. Расход памяти менее 10Мб.

Сравнение 2-х списков размером 10Мб каждый (25 символов на строку и 390 тысяч строк в каждом списке). Итого получаем 152,1 миллиарда сравнений строк. Время выполнения около часа. При этом программа отхватывает себе около 200Мб оперативной памяти. Хотя такие размеры - это уже территория баз данных. В этой программе все разумные способы повышения скорости я уже использовал.

Алгоритм работы и параметры сравнения

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

Искать совпадающие строки и Искать отличающиеся строки - тут всё понятно, будут искаться совпадающие строки или отличающиеся. Совпадающие будут записываться в файл «Совпадающие.txt». При поиске отличающихся строк они будут записываться в два файла «Несовпадения 1.txt» и «Несовпадения 2.txt» для Списков 1 и 2 соответственно. Так же в области примера сравнения вместо одного окна появляется два.

ВНИМАНИЕ! При сравнении списков на совпадение есть особенность, так как одинаковые строки имеются в обоих списках, в результат попадают строки из списка 1. Более аккуратно оформленный текст помещайте в список 1 при сравнении на совпадения.

Исправлять ошибки раскладки клавиатуры - до Punto Switcher тут конечно далеко. Имеются ввиду ошибки набора похожих букв не в той раскладке (C,H,P и т.д.). Например русской «с» и латинской. Они находятся на одной клавише и если слово начинается с «с», то можно набрать первую букву в английской раскладке, а потом переключиться на русскую или же наоборот. Алгоритм замены такой, что если в слове русских букв больше чем английских, то английские меняются на русские и наоборот.

Исправлять Ё(ё) на Е(е) - просто все «ё» заменяются на «е».

Сравнивать без учёта регистра - все буквы стают большими.

Сравнивать по уникальным строкам - Если параметр включён, то сначала списки проверяются на совпадающие строки. Если строка повторяется например 5 раз, то один экземпляр этой строки остаётся в списке для сравнения, а 4-е отправляются в список «повторов». Повторы для каждого списка слов свои.

Без этого параметра строки сравниваются как бы попарно. Например при сравнении на совпадающие строки если в 1-ом списке будет 2-е одинаковые строки, а во 2-ом списке ещё 3-и такие строки, то результатом будут только две строки, т.к. 3-ей строке не нашлось пары с которой бы она совпадала. Если для этого примера переключить сравнения на не совпадающие строки, то в результат несовпадающих попадёт одна строка из списка 2-а, т.к. она ни с чем не совпадает.

Используя сравнение по уникальным строкам можно найти повторяющиеся строки в списке. Для этого можно например заполнить строками только файл «Список 1.txt» и сравнить с пустым файлом «Список 2.txt» и тогда в файле «ПовторСп1.txt» появятся потворяющиеся строки из списка 1.

Повторы только для строк из результата - работает только вместе со сравнением по уникальным строкам. Без этого параметра в списки повторов попадают все повторяющиеся строки. Если включить, то в повторы будут попадать только строки присутствующие в результате. Количество строк попавших в повторы аналогично и равно количеству повторений в начальном списке минус 1.

Удалять точки и запятые и Удалять все пробелы - просто удаляются и всё тут.

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

Как сравнить два столбца в Excel по строкам

Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции . Рассмотрим как это работает на примерах ниже.

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:

=ЕСЛИ(A2=B2; “Совпадают”; “”)

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

=ЕСЛИ(A2<>B2; “Не совпадают”; “”)

Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)

Пример результата вычислений может выглядеть так:

Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Как сравнить несколько столбцов на совпадения в одной строке Excel

В Excel есть возможность сравнить данные в нескольких столбцах одной строки по следующим критериям:

• Найти строки с одинаковыми значениями во всех столбцах таблицы;
• Найти строки с одинаковыми значениями в любых двух столбцах таблицы;

Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы

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

=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)

Если в нашей таблице очень много столбцов, то более просто будет использовать функцию в сочетании с :

=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)

В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы

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

=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)

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

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

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

Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:

• Выделить столбцы с данными, в которых нужно вычислить совпадения;
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

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

Рассмотрим как найти совпадающие строки в таблице:

• Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки: