###
  • Android
  • Asus
  • Bluetooth
  • D-link
  • iPhone/iPad
  • Tenda
  • TP-Link
  • Беспроводные
  • Настройка
  • Настройка 
  • Ошибки
  • Полезное
  • Проблемы
  • Советы
  • Телевизоры LG

    • Как сравнить два столбца в Excel на совпадения. Сравнить два файла Excel на совпадения Как сравнить таблицы эксель

    02.08.2023 Tenda

    Казалось бы простая задача - сравнение таблиц. Точнее сравнение двух столбцов таблицы на совпадения или различия. Логично предложить что 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

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

    Решить эту непростую задачу нам поможет условное форматирование. Для примера, возьмем данные за февраль и март, как показано на рисунке:

    Чтобы найти изменения на зарплатных листах:


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

    

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

    В определенном условии существенное значение имеет функция ПОИСКПОЗ. В ее первом аргументе находится пара значений, которая должна быть найдена на исходном листе следующего месяца, то есть «Март». Просматриваемый диапазон определяется как соединение значений диапазонов, определенных именами, в пары. Таким образом выполняется сравнение строк по двум признакам – фамилия и зарплата. Для найденных совпадений возвращается число, что по сути для Excel является истиной. Поэтому следует использовать функцию =НЕ(), которая позволяет заменить значение ИСТИНА на ЛОЖЬ. Иначе будет применено форматирование для ячеек значение которых совпали. Для каждой не найденной пары значений (то есть – несоответствие) &B2&$C2 в диапазоне Фамилия&Зарплата, функция ПОИСКПОЗ возвращает ошибку. Ошибочное значение не является логическим значением. Поэтому исползаем функцию ЕСЛИОШИБКА, которая присвоит логическое значение для каждой ошибки – ИСТИНА. Это способствует присвоению нового формата только для ячеек без совпадений значений по зарплате в отношении к следующему месяцу – марту.

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

    Пусть на листах Январь и Февраль имеется две таблицы с оборотами за период по соответствующим счетам.

    Как видно из рисунков, таблицы различаются:

    1. Наличием (отсутствием) строк (наименований счетов). Например, в таблице на листе Январь отсутствует счет 26 (см. файл примера ), а в таблице на листе Февраль отсутствуют счет 10 и его субсчета.
    2. Разными значениями в строках. Например, по счету 57 обороты за январь и февраль не совпадают.

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

    Простой вариант сравнения 2-х таблиц

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

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

    Чтобы определить какая из двух таблиц является наиболее полной нужно ответить на 2 вопроса: Какие счета в февральской таблице отсутствуют в январской? и Какие счета в январской таблице отсутствуют в январской?

    Это можно сделать с помощью формул (см. столбец Е): =ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));"Нет";"Есть") и =ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));"Нет";"Есть")

    Сравнение оборотов по счетам произведем с помощью формул: =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7

    В случае отсутствия соответствующей строки функция ВПР() возвращает ошибку #Н/Д, которая обрабатывается связкой функций ЕНД() и ЕСЛИ() , заменяя ошибку на 0 (в случае отсутствия строки) или на значение из соответствующего столбца.

    С помощью можно выделить расхождения (например, красным цветом).

    Более наглядный вариант сравнения 2-х таблиц (но более сложный)

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

    Для этого необходимо:

    1. С помощью =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));"") сформировать в столбце А перечень счетов из обоих таблиц (без повторов);
    2. С помощью =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , где Список -

    Вопрос от пользователя

    Здравствуйте!

    У меня есть одна задачка, и уже третий день ломаю голову - не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут - скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете (прим. : фотка вырезана цензурой, все-таки личная информация) .

    Заранее благодарю. Андрей, Москва.

    Доброго дня всем!

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

    Пример работы с функцией ВПР

    В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B - товар и цена) нет данных по столбцу B ; во второй - заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая...

    Как это сделать...

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

    =ВПР(A2 ;$E$1:$F$7 ;2 ;ЛОЖЬ )

    A2 - значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

    $E$1:$F$7 - полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок "$" - он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

    2 - номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца - то значение можно было бы копировать из 2-го или 3-го столбца);

    ЛОЖЬ - ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

    Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

    Чтобы цена была проставлена и для других наименований товара - просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

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

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

    На этом всё, удачи!

    ​ эти два файла​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.​
    ​ помощью кнопки обзора​

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