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

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

Читайте также: Сравнение двух документов в MS Word

Способы сравнения

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

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

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

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

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

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

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

   =A2=D2

   

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

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



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

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



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



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





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



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

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

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

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

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

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



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

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

=B2=Лист2!B2

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

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

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

1. Выделяем сравниваемые массивы. Переходим во вкладку «Главная». Далее щелкаем по значку «Найти и выделить», который располагается на ленте в блоке инструментов «Редактирование». Открывается список, в котором следует выбрать позицию «Выделение группы ячеек…».
   

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

2. Активируется небольшое окошко перехода. Щелкаем по кнопке «Выделить…» в его нижнем левом углу.



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



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

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

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

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



2. Активируется окошко диспетчера правил. Жмем в нем на кнопку «Создать правило».



3. В запустившемся окне производим выбор позиции «Использовать формулу». В поле «Форматировать ячейки» записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (<>). Только перед данным выражением на этот раз будет стоять знак «=». Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу F4. Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид:

   =$A2<>$D2

   Данное выражение мы и записываем в вышеуказанное поле. После этого щёлкаем по кнопке «Формат…».



4. Активируется окно «Формат ячеек». Идем во вкладку «Заливка». Тут в перечне цветов останавливаем выбор на цвете, которым хотим окрашивать те элементы, где данные не будут совпадать. Жмем на кнопку «OK».



5. Вернувшись в окно создания правила форматирования, жмем на кнопку «OK».



6. После автоматического перемещения в окно «Диспетчера правил» щелкаем по кнопке «OK» и в нем.



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

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

1. Производим выделение областей, которые нужно сравнить.



2. Выполняем переход во вкладку под названием «Главная». Делаем щелчок по кнопке «Условное форматирование». В активировавшемся списке выбираем позицию «Правила выделения ячеек». В следующем меню делаем выбор позиции «Повторяющиеся значения».



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



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

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

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

Урок: Условное форматирование в Экселе

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

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

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

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

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

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

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



2. Происходит запуск Мастера функций. Переходим в категорию «Статистические». Находим в перечне наименование «СЧЁТЕСЛИ». После его выделения щелкаем по кнопке «OK».



3. Происходит запуск окна аргументов оператора СЧЁТЕСЛИ. Как видим, наименования полей в этом окне соответствуют названиям аргументов.

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

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

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



4. В элемент листа выводится результат. Он равен числу «1». Это означает, что в перечне имен второй таблицы фамилия «Гринев В. П.», которая является первой в списке первого табличного массива, встречается один раз.



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



6. Как видим, программа произвела вычисление совпадений, сравнив каждую ячейку первой таблицы с данными, которые расположены во втором табличном диапазоне. В четырех случаях результат вышел «1», а в двух случаях – «0». То есть, программа не смогла отыскать во второй таблице два значения, которые имеются в первом табличном массиве.

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

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

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 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

Урок: Как открыть Эксель в разных окнах

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

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

1 Сравнение с помощью простого поиска 

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

1. Перейти на главную вкладку табличного процессора. 
2. В группе «Редактирование» выбрать пункт поиска. 
3. Выделить столбец, в котором будет выполняться поиск совпадений — например, второй. 
4. Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения. 

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

​Смотрите также​ них​ противоречит Вашему же​ столбце нужно развернуть​ таблиц изменяются, то​ совпадению какого-либо общего​ видел, что рядом​ 0205. Я так​ добавили условия сортировки?​ эти ячейки, отсортировал​ обработать, например:​ цвет. На всех​

​ адресация позволяет построчно​ вплотную друг к​ столбце​ быстрее, если использовать​Вариант А: оба столбца​Чтение этой статьи займёт​Тема закрыта.​ условию из первого​ содержимое вложенных таблиц​ придется делать все​ параметра. В данном​ с Артикул 0000207​ понимаю, в ВПР​ Очень хорошо получилось,​ чтобы одинаковые ячейки​залить цветом или как-то​ окнах жмем ОК.​ сравнивать столбцы, в​ другу (не смежные),​В​ комбинации клавиш. Выделите​ находятся на одном​ у Вас около​Karataev​ поста. Поэтому у​ с помощью двойной​

​ заново.​ случае, мы применим​ стоит 0000207 и​ можно же это​ именно то, что​​ находились друг напротив​​ еще визуально отформатировать​При определении условий для​​ противном случае все​​ то процесс удаления​. В нашей учебной​ ячейку​ листе​ 10 минут. В​: VictorM, почему Select​ меня в формуле​ стрелки в шапке:​Power Query — это​ ее, чтобы подтянуть​ присваивал цену от​ сделать? просто последняя​

​ требуется. Только вот,​​ друга, и главное:​очистить клавишей​ форматирования ячеек столбцов​​ значения второго столбца​​ дубликатов будет чуть​​ таблице таких ячеек​​C1​

​Вариант В: столбцы находятся​​ следующие 5 минут​ зло? Просто смысла​ есть кусок AC3<>»»​​В итоге получим слияние​​ бесплатная надстройка для​​ старые цены в​​ 0000207. Заменять-то в​​ будет стоять единица​​ к сожалению, Excel​​ если артикулы совпадают,​​Delete​

​ мы использовали функцию​ будут сравниваться только​ сложнее. Мы не​​ всего две, но,​​и нажмите​ на разных листах​ Вы сможете легко​ нет использовать Select.​2. Одно из​ данных из обеих​ Microsoft Excel, позволяющая​ новый прайс:​ принципе понятно как.​ в формуле, а​ знаю слишком слабо,​ то из первого​

​заполнить сразу все одинаковым​ СЧЕТЕСЛИ. В данном​ со значением из​ можем удалить всю​ как Вы понимаете,​Ctrl+C​

• ​ или в разных​ сравнить два столбца​ Select вставляет макрорекордер,​
  • ​ условий избыточно -​ таблиц:​ загружать в Excel​
  • ​Те товары, напротив которых​ Просто в условиях​ не ноль. я​ чтобы понять, как​
• ​ столбца с ценами​
  • ​ значением, введя его​ примере эта функция​
  • ​ ячейки​ строку с повторяющимися​
  • ​ на практике их​(чтобы скопировать формулу​

Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул

Вариант А: оба столбца находятся на одном листе

1. ​ книгах​ в Excel и​ а программисты VBA​ если C3=O3 и​Названия столбцов в шапке​

   ​ данные практически из​
​ получилась ошибка #Н/Д​

   

   ​ задачи это неудобно,​​ правильно понимаю?​​ вы это сделали.​ (из первой пары​ и нажав​​ проверяет сколько раз​​А6​​ значениями, поскольку так​​ встретится намного больше.​ в буфер обмена),​Обработка найденных дубликатов​ узнать о наличии​ никогда не используют​ O3=AC3, то автоматом​ лучше, конечно, переименовать​ любых источников и​ — отсутствуют в​ так как повлечет​Basil​И еще вопрос:​ артикул-цена), вставил цену​Ctrl+Enter​

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

   ​ в них дубликатов,​
​ Select, т.к. в​

   ​ получается C3=AC3. Поэтому​​ двойным щелчком на​​ трансформировать потом эти​​ старом списке, т.е.​​ за собой очень​: Увы, нет. Под​ Когда в условиях​​ в другой столбец​​удалить все строки с​​ аргумента (например, A2)​​Аналогично создайте правило для​ и из второго​​ строки столбца​​Ctrl+Shift+End​ в столбце А​ удалить их или​ этом просто-напросто нет​ у меня в​ более понятные:​ данные любым желаемым​ были добавлены. Изменения​ много дальнейших манипуляций​ неточным соответствием понимается​

2. ​ функции ВПР вы​ «цена» (соответственно из​ выделенными ячейками, используя​​ в списке первого​​ значений, которые больше.​ столбца тоже. Итак,​А​(чтобы выделить все​​Изменить цвет или выделить​​ выделить цветом. Итак,​ смысла.​ формуле нет куска​А теперь самое интересное.​​ образом. В Excel​​ цены также хорошо​ (товары начнут задваиваться).​ ситуация, когда число​ пишете =ВПР(A2;G:I;2;0), здесь​​ второй пары артикул-цена).​ команду​ аргумента (например, Таблица_2).​У нас имеется две​ чтобы оставить только​, кликните символ фильтра​ не пустые ячейки​ найденные дубликаты​ время пошло!​

   

​Select используют люди,​​ C3=AC3​ Идем на вкладку​ 2016 эта надстройка​ видны.​Basil​​ 205 не найдено,​​ вот G:I, I​​b1b1g0n​​Главная — Удалить -​ Если количество раз​ таблицы заказов, скопированных​​ уникальные записи в​​ в столбце​ в столбе С)​Удалить дубликаты из первого​Excel – это очень​​ которые только только​​Если так нравится​Добавить столбец (Add Column)​ уже встроена по​

1. ​Плюсы​: Смысл таблицы в​ а берется следующее​​ — весь столбец?​​: великолепно) а рассказать,​

Вариант В: два столбца находятся на разных листах (в разных книгах)

1. ​ Удалить строки с​ = 0 в​ в один рабочий​​ столбце​​В​ и, наконец, нажмите​ столбца​

   ​ мощное и действительно​
​ начали работать с​

   ​ с И, то​​и жмем на​​ умолчанию на вкладке​этого способа: просто​ том, что один​ за ним, т.е.​​теперь такой вопрос.​​ как вы это​ листа (Home -​ таком случае формула​ лист. Необходимо выполнить​

2. ​А​, который теперь выглядит​​Ctrl+V​​В первой пустой ячейке​ крутое приложение для​
3. ​ макросами и получают​ мою формулу можно​

Обработка найденных дубликатов

​ кнопку​Данные (Data),​ и понятно, «классика​ раз задав соответствие,​ 206, например. И​ если при замене​ сделали, можете? если​ Delete — Delete​ возвращает значение ИСТИНА.​ сравнение данных двух​, сделайте следующее:​ как воронка с​

Показать только повторяющиеся строки в столбце А

​(чтобы вставить формулу​ (в нашем примере​ создания и обработки​ код из макрорекордера,​ переписать так​Условный столбец (Conditional Column)​а для Excel​ жанра», что называется.​ в следующие разы​ числа должны быть​

​ я получаю Н/Д,​ да, расскажите, пожалуйста.​ Rows)​​ В таком случае​​ таблиц в Excel​

​Отфильтруйте таблицу так, чтобы​ маленькой стрелочкой и​​ во все выделенные​​ это ячейка C1)​​ больших массивов данных.​​ не переделывая его.​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=И(C3=O3;O3=AC3;AC3<>»»)​​. А затем в​​ 2010-2013 ее нужно​​ Работает в любой​

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

​VictorM​sergei_6610​ открывшемся окне вводим​​ отдельно скачать с​​ версии Excel.​ 0000207 на 0000207​​ ВПР здесь не​​ протягивая формулу вниз,​: ну так я​Если списки разного размера​ формат, указанный в​ позиции есть первой​ значения, и выделите​

​Select all​Отлично, теперь все повторяющиеся​​ формулу:​​ есть несколько рабочих​: вот такой код​​:​​ несколько условий проверки​ сайта Microsoft и​Минусы​ не придется. Ну,​​ поможет.​​ пропустить эту ячейку,​ ж рассказал!​ и не отсортированы​ параметрах условного форматирования.​​ таблице, но нет​​ эти ячейки. Кликните​​(Выделить все). Либо​​ значения отмечены как​=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),»Unique»,»Duplicate»)​​ книг с данными​​ мне и достался​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=И(C3=O3;O3=AC3;AC3<>»»)​ с соответствующими им​

Изменение цвета или выделение найденных дубликатов

​ установить — получите​​тоже есть. Для​​ я тоже вашей​Два варианта. Если​ оставив предыдущее значение​b1b1g0n​ (элементы идут в​Скачать пример сравнения 2​ во второй. Нет​

​ по ним правой​ Вы можете сделать​ «​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));»Unique»;»Duplicate»)​ (или только одна​​ для переделки (допиливания)​​Согласен. Будем использовать​ значениями на выходе:​​ новую вкладку​​ поиска добавленных в​ специфики не знаю.​ артикулы не совпадают,​ этой ячейки?​, вы по Excel’ю​ разном порядке), то​ таблицы в Excel​ смысла вручную сравнивать​ кнопкой мыши и​​ то же самое​​Duplicate​В нашей формуле​​ огромная таблица), то,​​но получилось так,​ этот вариант. Еще​​Останется нажать на​​Power Query​ новый прайс товаров​komerz_by​ но хотя бы​

​Basil​ вопрос ставите?​ придется идти другим​

Удаление повторяющихся значений из первого столбца

​Ссылка во втором аргументе​ значение каждой ячейки.​ в контекстном меню​ через Ленту, нажав​«:​

​A1​ вероятно, Вы захотите​ что его проще​​ раз большое спасибо​ОК​.​ придется делать такую​: Здравствуйте. Пожалуйста. помогите​ подход к написанию​​: Связь есть между​​вот в Excel’е​

​ путем.​​ относительная, значит по​​Как сделать сравнение значений​ выберите​Data​В первой ячейке первого​это первая ячейка​ сравнить 2 столбца,​ переделать​ за помощь.​и выгрузить получившийся​

​Перед загрузкой наших прайс-листов​ же процедуру в​​ разобраться с похожей​ артикулов всегда одинаковый,​ столбцами А и​ и формулируйте. и​Самое простое и быстрое​ очереди будут проверятся​ в Excel двух​Clear contents​(Данные) >​ пустого столбца на​ первого столбца, который​ найти повторяющиеся значения,​а Select я​VictorM​ отчет в Excel​ в Power Query​​ обратную сторону, т.е.​​ задачей.​

1. ​ т.е., если в​ С, столбец В​ описывайте. и показывайте.​ решение: включить цветовое​ все ячейки выделенного​ столбцов? Для решения​(Очистить содержимое).​Select & Filter​​ листе​​ мы собираемся сравнивать.​
2. ​ а затем совершить​
3. ​ давно не использую​: Добрый вечер, Уважаемые.​​ с помощью все​​ их необходимо преобразовать​​ подтягивать с помощью​​Допустим есть список​ первом списке​
4. ​ здесь никак не​​что есть, что​​ выделение отличий, используя​​ диапазона (например, A2:A15).​​ данной задачи рекомендуем​Очистите фильтр.​(Сортировка и фильтр)​Sheet2​​$B$1​ с ними какие-либо​​ ибо «зло» (в​Вроде бы банальная​ той же кнопки​​ сначала в умные​​ ВПР новые цены​
5. ​ с остатками (в​Арт.0000205​ участвует. Связь между​ надо и в​ условное форматирование. Выделите​ Например, для сравнения​
6. ​ использовать условное форматирование,​Выделите все ячейки в​​ >​​(в нашем случае​и​ действия, например, удалить,​​ коде) как и​​ задача и рассматривалась​

​Закрыть и загрузить (Close​ таблицы. Для этого​ к старому прайсу.​ данном случае с​Арт.0000206​ столбцами в том,​

​ каком виде.​ оба диапазона с​
​ двух прайсов в​
​ которое быстро выделить​

​ столбце​

office-guru.ru

Сравнение числовых столбцов

​Clear​ это столбец В)​$B$10000​ выделить цветом или​ объединенные ячейки на​ не раз.​ & Load)​ выделим диапазон с​ Если размеры таблиц​ автошинами) от нескольких​Арт.0000207,​

​ что в столбец​пример того, пример​ данными и выберите​ Excel даже на​ цветом позиции, находящиеся​

​А​(Очистить), как показано​ введите такую формулу:​это адреса первой​ очистить содержимое. Столбцы​

​ листе​Но все, что​на вкладке​ данными и нажмем​ завтра поменяются, то​ поставщиков товара. В​а во втором​ С подтягивается не​ этого.​ на вкладке​ разных листах. Вторая​ только в одном​, начиная с ячейки​ на снимке экрана​=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),»»,»Duplicate»)​ и последней ячеек​​ могут находиться в​​Karataev​

​ находил по этой​Главная (Home)​

excel2.ru

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

​ на клавиатуре сочетание​ придется корректировать формулы.​ нем продавец устанавливает​0205,​ любая цена, а​и про версию​Главная — Условное форматирование​ формула действует аналогично.​ столбце. Рабочий лист​А1​ ниже:​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));»»;»Duplicate»)​

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

​ второго столбца, с​ одной таблице, быть​: Select не зло,​ теме — или​:​ Ctrl+T или выберем​ Ну, и на​ свою цену. Возможно​0206,​ цена согласно артикулу​

​ Excel не забудьте​ — Правила выделения​ Этот же принцип​ с таблицами:​вплоть до самой​

1. ​Если пометки «​Здесь​
2. ​ которым будем выполнять​ смежными или не​ просто его нет​
3. ​ выделить цветом или​Красота.​ на ленте вкладку​ действительно больших таблицах​ ли прописать формулу​

​0207,​ в столбце А.​ упомянуть.​ ячеек — Повторяющиеся​ можно применять для​В первую очередь необходимо​

​ нижней, содержащей данные.​Duplicate​Sheet3​ сравнение. Обратите внимание​ смежными, могут быть​ смысла использовать. Здравого​ вывести в отдельный​Причем, если в будущем​Главная — Форматировать как​ (>100 тыс. строк)​ чтообы вносить остатки​

​то можно, я​ Собственно, см. справку​и про примерный​ значения (Home -​ разных подобных задач.​

​ присвоить имена обоим​

​Откройте вкладку​» не достаточно для​– это название​ на абсолютные ссылки​ расположены на 2-х​ смысла нет, чтобы​ столбец.​ в прайс-листах произойдут​ таблицу (Home -​

1. ​ все это счастье​ поставщиков на другие​ думаю, привести их​ по ВПР.​ объем данных.​ Conditional formatting -​
2. ​Типовая задача, возникающая периодически​ таблицам. Благодаря этому​
3. ​Data​ Ваших целей, и​ листа, на котором​ – буквам столбца​ разных листах или​
4. ​ использовать Select.​Мне же нужно​ любые изменения (добавятся​ Format as Table)​ будет прилично тормозить.​ листы, а в​
5. ​ к одинаковому виду.​Для 2003 фильтр​
6. ​и про интересующий​ Highlight cell rules​ перед каждым пользователем​ легче понять, какие​(Данные) и нажмите​

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

​ Вы хотите отметить​ расположен 2-ой столбец,​ и номерам строки​ даже в разных​Естественно, что Select​ просто выделить Select’ом​ или удалятся строки,​. Имена созданных таблиц​Скопируем наши таблицы одна​ общем списке обновлялось​ См. ЛЕВСИМВ, ПРАВСИМВ,​ по условию, далее​ способ решения -​ — Duplicate Values)​ Excel — сравнить​ сравниваются диапазоны ячеек:​Sort A to Z​ повторяющиеся ячейки другим​

​ а​ предшествует знак доллара​

​ книгах.​ для каких-то задач​ ячейки в таблице​ изменятся цены и​ можно подкорректировать на​ под другую, добавив​ количество и цена​ ПСТР, ну или​ не равно #Н/Д.​ формулами или макросами.​:​ между собой два​Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить​

exceltable.com

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

​(Сортировка от А​ цветом шрифта, заливки​$A$1:$A$10000​ ($). Я использую​Представьте, что у нас​ нужен, в данном​ листа «Результат» если​ т.д.), то достаточно​ вкладке​ столбец с названием​

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

​ соответственно товару и​ СЦЕПИТЬ.​Ну, да весь​форум позволяет прикрепить​Если выбрать опцию​ диапазона с данными​ имя».​ до Я). В​ или каким-либо другим​– это адреса​ абсолютные ссылки для​ есть 2 столбца​​ контексте не нужен.​​ они совпадают с​​ будет лишь обновить​​Конструктор​

​ прайс-листа, чтобы потом​ поставщику. Заранее огромное​

​А если артикулы​

​ столбец I. G:I​ к сообщению​

​Повторяющиеся​ и найти различия​В появившемся окне в​ открывшемся диалоговом окне​ способом…​ ячеек от 1-ой​ того, чтобы адреса​ с именами людей​Зибин​ данными в столбце​​ наши запросы сочетанием​​(я оставлю стандартные​​ можно было понять​​ спасибо. Версия EXCELL​

​ записываются​ — три столбца​xls​, то Excel выделит​ между ними. Способ​ поле «Имя:» введите​​ выберите пункт​​В этом случае отфильтруйте​ до последней в​​ ячеек оставались неизменными​​ – 5 имён​​: 1) Самая простая​ листа «Данные1» (или​​ клавиш Ctrl+Alt+F5 или​Таблица1​ из какого списка​​ 2010. Пример файла​как угодно​ G, H, I​-файлы.​​ цветом совпадения в​​ решения, в данном​

​ значение – Таблица_1.​Continue with the current​ дубликаты, как показано​ этом 2-ом столбце.​

• ​ при копировании формул.​ в столбце​
• ​ формула — =А1=В1​​ «Данные2» это уже​
• ​ кнопкой​и​ какая строка:​​ во вложении.​
• ​по-разному, то это​ полностью. Было бы​пс. я не​​ наших списках, если​ случае, определяется типом​Левой клавишей мышки сделайте​ selection​ выше, выделите все​
• ​Скопируйте формулу во все​

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

​Если Вы хотите найти​A​ результатом будет или​ потом, таких данных​Обновить все (Refresh All)​Таблица2​

​Теперь на основе созданной​kalbasiatka​ полная жесть, откровенно​ достаточно G:H, просто​ слишком много​ опцию​ исходных данных.​​ щелчок по полю​(Сортировать в пределах​ отфильтрованные ячейки и​ ячейки столбца​ дубликаты в столбце​и 3 имени​ ИСТИНА или ЛОЖЬ​​ может быть много,​

​на вкладке​​, которые получаются по-умолчанию).​​ таблицы создадим сводную​: А сколько поставщиков?​ говоря. Потому что​ там у вас​​от Вас​​Уникальные​

​Если списки синхронизированы (отсортированы),​ ввода «Диапазон:» и​ указанного выделения) и​ нажмите​B​B​ в столбце​2) Выделение различий​

​ это своеобразные условия​Данные (Data)​​Загрузите старый прайс в​ ​ через​​komerz_by​​ на каком-то этапе,​​ объединение ячеек, поэтому​хочу для помощи​- различия.​ то все делается​

​ выделите диапазон: A2:A15.​ нажмите кнопку​Ctrl+1​

​(как и в​, поменяйте ссылки, чтобы​B​Выделите оба столбца​ для выделения).​

​.​ Power Query с​Вставка — Сводная таблица​

planetaexcel.ru

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

​: Пока задача стоит​​ так или иначе,​ вместо G:H в​ по​Цветовое выделение, однако, не​ весьма несложно, т.к.​ И нажмите ОК.​Sort​
​, чтобы открыть диалоговое​ варианте А).​ формула приняла такой​. Необходимо сравнить имена​ и нажмите клавишу​И вроде бы​Плюсы​ помощью кнопки​ (Insert — Pivot​ по двум…​ оба списка придется​ формулу попало G:I,​Вашей​ всегда удобно, особенно​ надо, по сути,​Для второго списка выполните​

​(Сортировка):​​ окно​
​У нас получается вот​ вид:​ в этих двух​ F5, затем в​ все просто, но​: Пожалуй, самый красивый​Из таблицы/диапазона (From Table/Range)​ Table)​kalbasiatka​ просматривать глазами. Тут​ я не стал​задаче?​ для больших таблиц.​ сравнить значения в​ те же действия​

​Удалите столбец с формулой,​​Format Cells​ такой результат:​=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),»Unique»,»Duplicate»)​ столбцах и найти​

​ открывшемся окне кнопку​​ как только выделяется​ и удобный способ​
​с вкладки​​. Закинем поле​: Если правильно понял​
​ уже нет хорошего​ менять. Но это​b1b1g0n​
​ Также, если внутри​ соседних ячейках каждой​ только имя присвойте​
​ он Вам больше​(Формат ячеек). В​
​Отлично, мы нашли записи​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));»Unique»;»Duplicate»)​ повторяющиеся. Как Вы​
​ Выделить — Отличия​ найденная ячейка, выделение​
​ из всех. Шустро​Данные (Data)​Товар​
​Имеем две таблицы (например,​ способа, ну или​​ все несущественно, можно​​: excel 2003. сделать​
​ самих списков элементы​ строки. Как самый​​ – Таблица_2. А​​ не понадобится, с​ качестве примера, давайте​​ в первом столбце,​​Вместо «​

​ понимаете, это вымышленные​​ по строкам. В​ с предыдущей найденной​ работает с большими​или с вкладки​в область строк,​ старая и новая​ я его не​ было сделать G:Z,​ желательно формулами. сравнить​ могут повторяться, то​ простой вариант -​ диапазон укажите C2:C15​ этого момента у​ изменим цвет заливки​ которые также присутствуют​Unique​ данные, взятые исключительно​ последних версиях Excel​ убирается.​
​ таблицами. Не требует​Power Query​ поле​ версия прайс-листа), которые​ знаю.​ это не повлияло​ столбец D со​ этот способ не​ используем формулу для​
​ – соответственно.​ Вас остались только​ ячеек в строках​

​ во втором столбце.​​» и «​ для примера. В​

​ 2007/2010 можно также​​Вот в чем​​ ручных правок при​​(в зависимости от​Прайс​ надо сравнить и​

​Единственно, что тут​​ бы на работу.​ столбцом B. при​

​ подойдет.​​ сравнения значений, выдающую​​Полезный совет! Имена диапазонов​​ уникальные значения.​ с дубликатами на​ Теперь нам нужно​Duplicate​ реальных таблицах мы​ воспользоваться кнопкой Найти​ собственно загвоздка.​ изменении размеров таблиц.​ версии Excel). После​в область столбцов​ оперативно найти отличия:​ можно еще сделать,​ Или совсем правильно​ совпадении значений отсортировать​В качестве альтернативы можно​ на выходе логические​
​ можно присваивать быстрее​Вот и всё, теперь​ ярко-жёлтый. Конечно, Вы​ что-то с ними​» Вы можете записать​ имеем дело с​
​ и выделить -​Мне же нужно,​Минусы​ загрузки вернемся обратно​ и поле​С ходу видно, что​ это просмотрев оба​ $G$11:$H$1468 — именно​

​ так, чтобы, к​​ использовать функцию​ значения​ с помощью поля​ столбец​ можете изменить цвет​ делать. Просматривать все​ собственные метки, например,​ тысячами, а то​ Выделение группы ячеек​ чтобы выделились все​: Требует установленной надстройки​ в Excel из​Ц​
​ в новом прайсе​ списка и задав​ тот диапазон ячеек,​
​ примеру, если B150=D100,​СЧЁТЕСЛИ​ИСТИНА (TRUE)​ имен. Оно находится​А​ заливки при помощи​ повторяющиеся записи в​ «​ и с десятками​ на вкладке Главная​ найденные.​ Power Query (в​ Power Query командой​ена​ что-то добавилось (финики,​ соответствие одного артикула​ в котором артикулы​ то ячейки A150​(COUNTIF)​или​ левее от строки​содержит только уникальные​ инструмента​ таблице вручную довольно​Не найдено​
​ тысяч записей.​
​3) Подсветка различий​Как сделать -​ Excel 2010-2013) или​
​Закрыть и загрузить -​в область значений:​ честнок…), что-то пропало​ другому, сохранить это​ и цены, но​ стала рядом с​из категории​ЛОЖЬ (FALSE)​
​ формул. Просто выделяйте​ данные, которых нет​Fill​ неэффективно и занимает​» и «​Вариант А:​ условным форматированием​

​ не пойму, подскажите,​​ Excel 2016. Имена​​ Закрыть и загрузить​​Как видите, сводная таблица​ (ежевика, малина…), у​ все на отдельном​ так долго и​ B150, как и​Статистические​:​
​ диапазоны ячеек, а​ в столбце​(Цвет заливки) на​ слишком много времени.​Найдено​оба столбца находятся​Алексей матевосов (alexm)​ пожалуйста.​ столбцов в исходных​ в… (Close &​ автоматически сформирует общий​ каких-то товаров изменилась​ листе, чтобы в​ чаще всего не​ E100 стала с​, которая подсчитывает сколько​Число несовпадений можно посчитать​ в поле имен​В​ вкладке​

​ Существуют пути получше.​​«, или оставить только​ на одном листе.​: Еще вариант считать​Karataev​ данных не должны​ Load — Close​ список всех товаров​ цена (инжир, дыня…).​ следующий раз можно​ нужно, я так​ D100 (после сортировки,​
​ раз каждый элемент​ формулой:​ вводите соответствующее имя​:​Home​Если Ваши столбцы не​ «​
​ Например, столбец​
​ сколько раз А1​
​: Обычно ячейки не​
​ меняться, иначе получим​
​ & Load To…)​
​ из старого и​
​ Нужно быстро найти​
​ было использовать ВПР.​ делать не люблю.​ к примеру, B150​ из второго списка​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​ для диапазона и​
​Как видите, удалить дубликаты​(Главная), но преимущество​​ имеют заголовков, то​​Duplicate​A​ встречается в В1:В10​ нужно выделять, чтобы​ ошибку «Столбец такой-то​:​ нового прайс-листов (без​ и вывести все​b1b1g0n​Существенно другое:​ и A150 станут​
​ встречался в первом:​или в английском варианте​ нажмите Enter.​ из двух столбцов​ диалогового окна​ их необходимо добавить.​» и ввести символ​и столбец​Если количество равно​ с ними что-то​

​ не найден!» при​​… и в появившемся​ повторений!) и отсортирует​ эти изменения.​: Вот это, как​G — первый​ A1 B1, как​Полученный в результате ноль​

​ =SUMPRODUCT(—(A2:A20<>B2:B20))​​Теперь воспользуемся условным форматированием,​ в Excel при​Format Cells​ Для этого поместите​ пробела вместо второго​B​ единице, то ИСТИНА,​ сделать.​
​ попытке обновить запрос.​ затем окне выбрем​ продукты по алфавиту.​Для любой задачи в​ мне кажется, существенно​ столбец диапазона, в​
​ и D100 E100​ и говорит об​Если в результате получаем​ чтобы выполнить сравнение​ помощи формул –​(Формат ячеек) в​ курсор на число,​

​ значения. В последнем​​.​ в противном случае​Объясните, зачем Вам​sergei_6610​Только создать подключение (Connection​ Хорошо видно добавленные​ Excel почти всегда​ облегчило бы задачу​ нем ищется артикул,​ станут D1 и​ отличиях.​ ноль — списки​ двух списков в​ это не так​ том, что можно​ обозначающее первую строку,​ случае ячейки, для​Вариант В:​ ЛОЖЬ​ надо выделить несколько​: Доброго времени суток.​ Only)​

​ товары (у них​​ есть больше одного​ в будущем. Не​2 — номер​ E1 соответственно. примерный​И, наконец, «высший пилотаж»​ идентичны. В противном​ Excel. Нам нужно​ уж сложно.​ настроить одновременно все​

​ при этом он​​ которых дубликаты найдены​Столбцы расположены на​Формула для ячейки​
​ ячеек?​ Подскажите как одной​.​ нет старой цены),​ решения (обычно 4-5).​ могли бы вы​ столбца диапазона, из​ объем 1000-2000 строк.​ — можно вывести​ случае — в​ получить следующий результат:​Урок подготовлен для Вас​ параметры форматирования.​ превратится в чёрную​ не будут, останутся​ разных листах. Например,​ С1​Что касается Вашего​

​ формулой сравнить значения​​Повторите то же самое​

​ удаленные товары (у​​ Для нашей проблемы​ подсказать, как это​

​ которого будут подтягиваться​​получить в итоге​

CyberForum.ru

Сравнение двух таблиц

​ отличия отдельным списком.​ них есть различия.​​ командой сайта office-guru.ru​Теперь Вы точно не​

​ стрелку, как показано​ пустыми, и, я​ столбец​=СЧЁТЕСЛИ (B$1:B$10;A1)​ вопроса, то выделить​ ячеек C3, O3​ с новым прайс-листом.​ них нет новой​ можно использовать много​ делается?​

​ данные. Для диапазона​ надо 4 столбца,​ Для этого придется​ Формулу надо вводить​Позиции, которые есть в​Источник: https://www.ablebits.com/office-addins-blog/2013/09/05/compare-two-columns-remove-duplicates/​ пропустите ни одной​

• ​ на рисунке ниже:​​ полагаю, такое представление​​A​После ввода формулу​ можно с помощью​ и AC3. Проблема​Теперь создадим третий запрос,​ цены) и изменения​ разных подходов:​
• ​Basil​ G:I второй столбец​ отсортированных так, чтобы​ использовать формулу массива:​ как формулу массива,​ Таблице_1, но нет​
• ​Перевел: Антон Андронов​ ячейки с дубликатами:​

​Кликните правой кнопкой мыши​ данных наиболее удобно​

Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)

​на листе​ протянуть.​ Union. Собираете все​ в том, что​ который будет объединять​ цен, если были.​функцию​: Допустим получили вы​ — это столбец​

​ в начале шли​Выглядит страшновато, но свою​ т.е. после ввода​ в Таблцие_2 будут​Автор: Антон Андронов​Отфильтруйте таблицу так, чтобы​ и в контекстном​ для дальнейшего анализа.​Sheet2​Если в столбце​

​ ячейки, какие нужно​ база очень большая​ и сравнивать данных​Общие итоги в такой​ВПР (VLOOKUP)​ первый список, где​ Н, из него​

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

​ меню выберите​​Теперь давайте скопируем нашу​и столбец​ С все значения​ выделить в одну​ а формат всех​ из предыдущих двух.​ таблице смысла не​- искать названия​ артикулы записаны абы​ и подтягиваются цены.​ совпадают (то есть​ ;)​ жать не на​ В тоже время​ Сравнение будем производить​ ячейки с повторяющимися​Insert​

Способ 2. Сравнение таблиц с помощью сводной

​ формулу во все​A​ ИСТИНА, то таблицы​ переменную, затем с​ ячеек текстовый. Необходимо​ Для этого выберем​ имеют, и их​

​ товаров из нового​ как. Нашли, чему​Если загнать ВПР​​ ячейки D и​b1b1g0n​Enter​​ позиции, находящиеся в​​ построчно: если значение​​ значениями, и выделите​(Вставить):​​ ячейки столбца​​на листе​ совпадают по данным.​​ помощью этой переменной​​ в столбце AE3​​ в Excel на​

​ можно отключить на​ прайс-листа в старом​ эти артикулы соответствуют​ в ЕСЛИ, то​ B), а все​: В общем задача.​, а на​ Таблице_2, но отсутствующие​ во втором столбце​ эти ячейки.​Дайте названия столбцам, например,​C​Sheet3​Чтобы не просматривать​

​ выделяете ячейки:​ получить значение ИСТИНА​ вкладке​ вкладке​ и выводить старую​​ во втором списке.​ да. Если нашли​ остальные — потом.​ Представьте, есть четыре​Ctrl+Shift+Enter​​ в Таблице_1, будут​

​ больше, чем в​Если 2 столбца, которые​ «​, вплоть до самой​.​ весь столбец, все​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Выделить()​​ при совпадении во​​Данные — Получить данные​

​Конструктор — Общие итоги​​ цену рядом с​ Составили какую-нибудь такую​ новую цену, то​p.s. извините, не​

​ столбца — два​​.​ подсвечены синим цветом.​ первом, то оно​ Вы сравниваете, находятся​Name​ нижней строки, которая​В Excel 2013, 2010​ ли там ИСТИНА,​

Способ 3. Сравнение таблиц с помощью Power Query

​Dim uni As​ всех ячейках или​ — Объединить запросы​ — Отключить для​ новой, а потом​ таблицу:​ берем оттуда, где​ прикрепил файл, сейчас​ с ценами, два​Если с отличающимися ячейками​Выделите диапазон первой таблицы:​ будет выделено красным,​​ на разных листах​​» и «​ содержит данные в​ и 2007 есть​ можно в С11​ Range​ ЛОЖЬ при несовпадении​​ — Объединить (Data​​ строк и столбцов​

​ ловить отличия​Артикул.00002050000205Арт.00002060000206Артикул 00002070000207После чего​ нашли, иначе берем​ исправлюсь)​ с артикулами. Соответственно,​ надо что сделать,​ A2:A15 и выберите​ если меньше -​, то есть в​Duplicate?​​ столбце​ встроенный инструмент​ вставить формулу​​Set uni =​ или отсутствии результата​ — Get Data​​ (Design — Grand​​объединить два списка в​​ вместо «кривых» артикулов​​ старую цену.​​b1b1g0n​​ сочетаются попарно: артикул-цена,​

​ то подойдет другой​ инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать​ то зеленым. Выделять​​ разных таблицах, кликните​​» Затем откройте вкладку​​A​​Remove Duplicate​​=И (C1:C10)​​ Range(«A11»)​ в одной из​ — Merge Queries​ Totals)​ один и построить​​ подставляете нормальные, а​b1b1g0n​: Вот, собственно, сам​ артикул-цена.​ быстрый способ: выделите​​ правило»- «Использовать формулу​

​ ячейки будем Условным​ правой кнопкой мыши​​Data​. Для этого наведите​​(Удалить дубликаты), но​

​Таким образом сравнивают​Set uni =​

​ ячеек. Файл примера:​ — Merge)​.​ по нему потом​ потом уже подставляете​:​ файл.​​Задача: сравнить два​ оба столбца и​ для определения форматированных​ форматированием.​ выделенный диапазон и​(Данные) и нажмите​​ указатель мыши на​​ он бессилен в​​ таблицы, в которых​​ Union(uni, Range(«A13»))​​_Boroda_​

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

​ в контекстном меню​​Filter​​ правый нижний угол​ такой ситуации, поскольку​ одинаковые данные расположены​uni.Select​: Так нужно?​Объединить (Merge)​ не количество товаров!),​

​ наглядно будут видны​ списка.​, благодарю за разъяснения!​

​:​ при нахождении совпадения​F5​В поле ввода введите​

​ с числами. Сравним​ выберите​​(Фильтр):​​ ячейки​ не может сравнивать​​ не обязательно в​​End Sub​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=(C3=O3)*(O3=AC3)*(AC3<>»»)=1​на вкладке​ то достаточно просто​ отличия​

​Такая таблица с​​ все потихоньку становится​​b1b1g0n​ желательно окрасить эти​, затем в открывшемся​ формулу:​​ значения второго столбца​Delete Row​​После этого нажмите меленькую​​C1​​ данные в 2​

​ тех же строках.​

​VictorM​sergei_6610​Power Query​ обновить созданную сводную,​использовать надстройку Power Query​ каждым разом будет​ на свои места.​, уверены, что нужно​ ячейки, отсортировать чтобы​ окне кнопку​​Щелкните по кнопке «Формат»​​ с первым (см.​​(Удалить строку):​​ серую стрелку рядом​

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

​ расширяться, но от​​ Сам пришел к​ именно сортировать? Имхо,​ одинаковые ячейки находились​Выделить (Special)​ и на вкладке​ файл примера).​Нажмите​ с «​ чёрного перекрестия, как​ он может только​

planetaexcel.ru

Как сравнить данные в трех столбцах? (Формулы/Formulas)

​: аналогичные вопросы​​ не нужно.​sergei_6610​В окне объединения выберем​ правой кнопкой мыши​Давайте разберем их все​ просмотра списков глазами​ решению в случае​ это дурацкий способ.​ друг напротив друга,​-​ «Заливка» укажите зеленый​Выделите все значения из​ОК​Duplicate?​ показано на картинке​ удалить дубликаты. Других​http://otvet.mail.ru/question/87383103​

​Но в данном​​: _Boroda_ Спасибо за​
​ в выпадающих списках​

​ -​​ последовательно.​

​ все равно на​​ Н/Д, только вместо​Basil​ и главное: если​Отличия по строкам (Row​ цвет. На всех​​ второго столбца и​​, когда Excel попросит​
​«, чтобы раскрыть меню​ ниже:​ вариантов, таких как​

​http://otvet.mail.ru/question/87175757​​ случае нужно именно​ помощь. Если кому​ наши таблицы, выделим​Обновить (Referesh)​Если вы совсем не​ мой взгляд не​ ЕОШИБКА использовал ЕНД.​: Я бы сделал​
​ артикулы совпадают, то​ differences)​ окнах жмем ОК.​ создайте правило Условного​ Вас подтвердить, что​ фильтра; снимите галочки​Нажмите и, удерживая левую​ выделение или изменение​
​http://otvet.mail.ru/question/97716954​ выделить.​ пригодится, то как​ в них столбцы​
​.​

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

excelworld.ru

Сравнить два столбца и выделить совпадения в одном (Select) (Макросы/Sub)

​ форматирования для выделения меньших​​ Вы действительно хотите​
​ со всех элементов​ кнопку мыши, протащите​ цвета, не предусмотрено.​
​Дима слепаков​Затем, по условию​ вариант нашел еще​ с названиями товаров​Плюсы​ замечательной функцией, то​
​b1b1g0n​ 2. Ну, в​b1b1g0n​ с ценами (из​ Excel 2007/2010 можно​ C2:C15 и снова​ значений.​ удалить всю строку​ этого списка, кроме​ границу рамки вниз,​ И точка!​: 1) Посмотрите эту​
​ Select будет выполняться​ одну формулу​ и в нижней​: такой подход на​ загляните сначала сюда​: я, видимо, немного​
​ принципе, смысл пока​:​
​ первой пары артикул-цена),​ также воспользоваться кнопкой​ выберите инструмент: «ГЛАВНАЯ»-«Условное​
​Обратите внимание, что при​ листа и после​Duplicate​

​ выделяя все ячейки,​​Далее я покажу Вам​ программу http://infostart.ru/public/186905/​ дальнейший код, который​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=И(C3=O3;O3=AC3;C3=AC3)​
​ части зададим способ​ порядок быстрее работает​ и почитайте или​
​ неправильно вас понял.​ тот же. Теперь​Basil​ вставить цену в​Найти и выделить (Find​ форматирование»-«Создать правило»- «Использовать​ создании ссылки на​ этого очистите фильтр.​, и нажмите​
​ в которые требуется​
​ возможные пути сравнения​2) Можете попробовать​
​ уже написан под​:)​
​ объединения -​ с большими таблицами,​
​ посмотрите видеоурок по​
​ я думал, можно​

​ надо искать неточное​​, да, вот то,​ другой столбец «цена»​
​ & Select) -​ формулу для определения​ ячейку из первого​
​ Как видите, остались​ОК​ вставить формулу. Когда​ двух столбцов в​ автоматически сравнить excel​
​ дугие условия.​Почему формула без​

​Полное внешнее (Full Outer)​​ чем ВПР.​ ней — сэкономите​
​ как-нибудь их связать.​ соответствие. Просто может​
​ что для начала​

​ (соответственно из второй​​ Выделение группы ячеек​ форматированных ячеек:».​ столбца использована относительная​ только строки с​.​ все необходимые ячейки​ Excel, которые позволят​ таблицы тут http://сравнитьэксель.рф​Спасибо за код.​
​ тегов? Кнопка fx.​:​Минусы​ себе пару лет​ чтобы потом через​ быть так, что​

​ нужно. С ВПР​​ пары артикул-цена).​ (Go to Special)​В поле ввода введите​
​ адресация на строку,​ уникальными значениями:​Вот и всё, теперь​
​ будут выделены, отпустите​ найти и удалить​3) https://3dnews.ru/585650 прочитайте​ Буду пробовать​ Поправил.​После нажатия на​

​: надо вручную копировать​​ жизни.​ ВПР Excel воспринимал​ в первой таблице​ разберусь, подскажите, пожалуйста,​ikki​
​на вкладке​ формулу:​ т.е. =$A6, а​Если 2 столбца расположены​

excelworld.ru

Подскажите-мне нужно сравнить 2 таблицы в эксель на совпадение данных. самый простой способ подскажите, пожалуйста.

​ Вы видите только​​ кнопку мыши:​ повторяющиеся записи.​ статью или эту​VictorM​
​_Boroda_​
​ОК​ данные друг под​Обычно эту функцию используют​ Артикул 0000207 как​ артикул записан как​ как вы сделали​: Представьте: сделал.​Главная (Home)​Щелкните по кнопке «Формат»​ не =$A$6, которая​ на одном листе​ те элементы столбца​
​Подсказка:​Сравниваем 2 столбца и​

​ http://office-guru.ru/excel/kak-v-excel-sravnit-dva-stolbca-i-udalit-dublikaty-vydelit-raskrasit-peremestit-288.html и хорошая​​: да, все-таки Select​: 1. Если все​должна появиться таблица​
​ друга и добавлять​ для подтягивания данных​ 0000207, то есть​ Арт.0000205 а в​
​ связь между столбцами​сравнил два столбца​
​Excel выделит ячейки, отличающиеся​
​ и на вкладке​ предлагается MS EXCEL​
​, вплотную друг другу​А​В больших таблицах​ ищем дубликаты при​
​ статья на the​ это «зло»​ ячейки пустые, то​ из трех столбцов,​ столбец с названием​
​ из одной таблицы​
​ обращался к третей​ той, откуда нужно​ A,B,C, а также​ с артикулами, при​ содержанием (по строкам).​

​ «Заливка» укажите синий​​ по умолчанию. Такая​
​ (смежные) или не​
​, которые дублируются в​
​ скопировать формулу получится​

​ помощи формул​​ village http://www.the-village.ru/village/business/cloud/148301-vlastelin-tablits-10-fishek-excel-o-kotoryh-vy-ne-znali​переделал все без​
​ даст ИСТИНА, что​ где в третьем​ прайс-листа. Если размеры​
​ в другую по​ таблице, находил строку,​ брать значения -​ интересует как вы​ нахождении совпадения окрасил​

​ Затем их можно​

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

Добрый день!

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

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

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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

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

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

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

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

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

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

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

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

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

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

1. Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
2. В поле ввода введите формулу:
3. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
4. Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
5. В поле ввода введите формулу:
6. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.

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

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

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

Сравнить два столбца в excel на совпадения

Microsoft Office Excel часто используется для ведения бухгалтерии, анализа финансовой деятельности предприятия и формирования прайс-листов. Поскольку все эти показатели меняются с течением времени, то необходимо постоянно отслеживать динамику изменений. Для более качественного анализа лучше всего сопоставлять значения двух массивов, и сегодня в статье разберемся, как сравнить два столбца в excel на совпадения.

Меню поиска

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

1. Создаете два массива, которые необходимо сопоставить.

1. На главной вкладке панели инструментов ищете кнопку Найти и выделить.

1. Обозначаете два сравниваемых столбца и из выпадающего списка возможностей выбираете Выделение группы ячеек.

1. В появившемся окне ставите галочку напротив Отличия по строкам и щелкаете ОК.

Все отличия будут отмечены.

Важно! Чтобы зафиксировать отличия, необходимо сразу перейти в раздел Шрифт и изменить цвет текста или залить ячейку любым цветом. Ни в коем случае не нажимайте Enter и не щелкаете по рабочему листу, иначе выделение пропадет.

Следующим шагом будет сортировка. Нажимаете кнопку рядом с Найти и выбираете настраиваемую сортировку.

В новом окне выбираете массив данных, способ сортировки и устанавливаете порядок расположения данных.

Подтверждаете действие нажатием кнопки ОК. В результате получается следующее:

Форматирование

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

1. Шаг 1. Переходите к блоку Стили на главной вкладке, нажимаете кнопку Условное форматирование, опускаетесь вниз и щелкаете по строке Создать правило.

1. Шаг 2. Появившееся окно содержит шаблоны правил. Выбираете последнюю строку и вписываете формулу для сравнения в специальное поле. Выражение позволяет сравнивать построчно два столбца.

Знак <> обозначает неравно на языке Excel.

1. Шаг 3. Если формула принимает значение ИСТИНА, то ячейка форматируется заданным образом. Делается это через кнопку Формат. В появившемся окне настраиваете параметры шрифта и ячейки.

1. Шаг 4. Чтобы подтвердить формат и закрепить правило, дважды нажимаете ОК. После этого разные ячейки подсветятся по заданным условиям.

На заметку! Для изменения диапазона применяемого закона форматирования по заданным условиям, используйте строку Управление правилами.

Библиотека формул имеет в своем наборе специальный инструмент для сравнения. Функция впр позволяет сравнить данные двух массивом и перенести информацию в отдельный столбец. Такой вариант отлично подойдет для сопоставления цен.

• 1. Создаете два блока со списком продуктов и ценами на них, в одном из них содержаться исходные значения, а во втором – измененные.

• 2. В отдельной ячейке вводите ВПР в строке формул и заполняете аргументы.

• 3. Чтобы применить функцию к другим строкам, используйте маркер автозаполнения.

• 4. Если в ячейке стоит #Н/Д, то это значит, что в первоначальном массиве нет данной позиции.

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вам также могут быть интересны следующие статьи

46 комментариев

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

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

Молодца. Читаю Ваши статьи, наглядно и доходчиво, Спасибо.

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

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

Пришлите пример, пожалуйста, не совсем понял ситуацию. Видимо сравнение идет по формулам, а с ними уже посложнее будет

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

Пришлите, пожалуйста, файл с примером, посмотрим

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

Забыл добавить! Для сравнения использовал Inquire.

Добрый день, Антон. Честно говоря, не сталкивался с подобной проблемой, поэтому чем-то конкретным помочь не могу. Но официальном сайте данная ошибка описана, если это вам поможет, скидываю ссылку на страницу

Ренат, спасибо большое за статью! Очень пригодилась в сравнении формула! ?

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

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

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

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

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :

Число несовпадений можно посчитать формулой:

или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20))

Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) — Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

• залить цветом или как-то еще визуально отформатировать
• очистить клавишей Delete
• заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
• удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
• и т.д.

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

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

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

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

Полученный в результате ноль и говорит об отличиях.

И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Выглядит страшновато, но свою работу выполняет отлично 😉