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

​Смотрите также​​ вариантом пользоваться.​ случаев​ таблице, находил строку,​​ тот диапазон ячеек,​​ «цена» (соответственно из​ более ста сотрудников,​​ строки. Как самый​​ счетов, включающий ВСЕ​​ выведены в отдельный​​После этого выделяем первую​​ прежде. Ставим курсор​ которые не совпадают,​ предыдущем способе, сравниваемые​ можно использовать адреса​ области имеет смысл​ нажимаем на кнопку​Есть несколько способов,​достаточно на синей​есть в В,​ видел, что рядом​
​ в котором артикулы​​ второй пары артикул-цена).​ среди которых одни​​ простой вариант -​ наименования счетов из​
​ диапазон.​ ячейку справа от​ в нижнюю правую​ останутся окрашенными в​ области должны находиться​
​ до 255 массивов.​ только тогда, когда​ «Условное форматирование». Из​как сравнить два столбца​ таблице просто клацнуть​ нет в А​ с Артикул 0000207​ и цены, но​b1b1g0n​ ​ увольняются другие трудоустраиваются,​
​ используем формулу для​ обоих таблиц (без​ ​При сравнении диапазонов в​
​ колонки с номерами​ часть элемента листа,​ свой изначальный цвет​ на одном рабочем​ Но в нашем​ они имеют похожую​ появившегося списка выбираем​ в Excel на​
​ правой кнопкой мыши​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$22;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$2:A$22;B$2:B$22)=0;ЕСЛИ(B$2:B$22<>0;СТРОКА(B$2:B$22);»»));СТРОКА(B1:B21))-1);»»)​ стоит 0000207 и​ так долго и​​: великолепно) а рассказать,​ третьи уходят в​ сравнения значений, выдающую​ повторов). Затем вывести​ разных книгах можно​ и щелкаем по​ который содержит функцию​ (по умолчанию белый).​
​ листе Excel и​ случае мы будем​ структуру.​ функцию «Создать правило».​ совпадения​ и выбрать обновить.​для второго случая​ присваивал цену от​ чаще всего не​ как вы это​ отпуск или на​ на выходе логические​ разницу по столбцам.​
​ использовать перечисленные выше​
​ значку​СЧЁТЕСЛИ​ ​ Таким образом, можно​
​ быть синхронизированными между​ использовать всего два​Самый простой способ сравнения​В строке «Формат…» пишем​,​Это запустит SQL​ аналогично, только на​ 0000207. Заменять-то в​ ​ нужно, я так​ ​ сделали, можете? если​
​ больничный и т.п.​ значения​Для этого необходимо:​ способы, исключая те​«Вставить функцию»​, и после преобразования​ сразу визуально увидеть,​ собой.​ массива, к тому​ данных в двух​ такую формулу. =$А2<>$В2.​
​как сравнить две таблицы​ запрос написанный​ пустые ячейки проверять​
​ принципе понятно как.​ делать не люблю.​​ да, расскажите, пожалуйста.​​ В следствии чего​ИСТИНА (TRUE)​С помощью формулы массива​ варианты, где требуется​.​ его в маркер​ в чем отличие​Прежде всего, выбираем, какую​ же, как один​ таблицах – это​ Этой формулой мы​ Excel​krosav4ig​ не обязательно.​ Просто в условиях​Существенно другое:​ikki​ могут возникнуть сложности​
​или​ =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));»») сформировать​ размещение обоих табличных​Открывается​ заполнения зажимаем левую​ между массивами.​ табличную область будем​
​ аргумент.​
​ использование простой формулы​ говорим Excel, что​,​.​Обе формулы​
​ задачи это неудобно,​
​G — первый​​: ну так я​ со сравнением данных​ ​ЛОЖЬ (FALSE)​​ в столбце А​ областей на одном​
​Мастер функций​ кнопку мыши и​При желании можно, наоборот,​ считать основной, а​Ставим курсор в поле​ равенства. Если данные​ если данные в​списки​Или просто открыть​​массивные​ так как повлечет​ столбец диапазона, в​ ж рассказал!​ по зарплате. Например,​:​ перечень счетов из​ листе. Главное условие​. Переходим в категорию​ тянем курсор вниз.​ окрасить несовпадающие элементы,​
​ в какой искать​«Массив1»​ совпадают, то она​ ячейках столбца А​,​ файл.​, вводятся сочетанием ctrl+shift+enter.​ за собой очень​ нем ищется артикул,​b1b1g0n​ фамилии сотрудников будут​ ​Число несовпадений можно посчитать​
​ обоих таблиц (без​ ​ для проведения процедуры​
​«Статистические»​Как видим, программа произвела​ а те показатели,​ отличия. Последнее давайте​и выделяем на​
​ выдает показатель ИСТИНА,​
​ не равны данным​даты в Excel​leg​ Ну и УФ​
​ много дальнейших манипуляций​2 — номер​, вы по Excel’ю​ постоянно в разной​ формулой:​ повторов);​ сравнения в этом​и производим выбор​ вычисление совпадений, сравнив​ которые совпадают, оставить​ будем делать во​ листе сравниваемый диапазон​ а если нет,​
​ в ячейках столбца​​. Не только сравнить​​: SLAVICK, в ваш​
​ в файле на​​ (товары начнут задваиваться).​​ столбца диапазона, из​ вопрос ставите?​ последовательности. Как сделать​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​С помощью формулы массива​ случае – это​ наименования​ каждую ячейку первой​ с заливкой прежним​ второй таблице. Поэтому​
​ данных в первой​
​ то – ЛОЖЬ.​​ В, то окрасить​ ​ столбцы в Excel,​​ файл вставил номера,​ всякий случай.​Basil​ которого будут подтягиваться​вот в Excel’е​
​ сравнение двух таблиц​или в английском варианте​ =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; «​ открытие окон обоих​«НАИМЕНЬШИЙ»​ таблицы с данными,​ цветом. При этом​ выделяем список работников,​
​ области. После этого​ Сравнивать можно, как​ эти ячейки в​ но и выделить​
​ более 60 тысяч​leg​: Смысл таблицы в​ данные. Для диапазона​ и формулируйте. и​ Excel на разных​

excel-office.ru

Методы сравнения таблиц в Microsoft Excel

​ =SUMPRODUCT(—(A2:A20<>B2:B20))​С помощью формулы =ЕСЛИ(ЕНД(ВПР($B5;Январь!$A$7:$C$81;2;0));0;ВПР($B5;Январь!$A$7:$C$81;2;0))-​ файлов одновременно. Для​. Щелкаем по кнопке​ которые расположены во​ алгоритм действий практически​ находящийся в ней.​ в поле ставим​ числовые данные, так​ красный свет.​ разницу цветом шрифта,​ в колонку старые​: Manyasha, огромное вам​ том, что один​ G:I второй столбец​ описывайте. и показывайте.​ листах?​Если в результате получаем​ ЕСЛИ(ЕНД(ВПР($B5;Февраль!$A$7:$C$77;2;0));0;ВПР($B5;Февраль!$A$7:$C$77;2;0)) произвести сравнение​ версий Excel 2013​«OK»​ втором табличном диапазоне.​ тот же, но​ Переместившись на вкладку​ знак​ и текстовые. Недостаток​Как работать с​

​ ячеек, т.д. Один​ и 10 тыс.​

​ спасибо! Все работает​ раз задав соответствие,​ — это столбец​

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

​что есть, что​Решить эту непростую задачу​ ноль — списки​ оборотов по счетам;​ и позже, а​.​

• ​ В четырех случаях​ в окне настройки​
• ​«Главная»​«не равно»​
• ​ данного способа состоит​ условным форматированием, как​

​ способ сравнения, разместить​ в колонку новые,​leg​ в следующие разы​ Н, из него​ надо и в​ нам поможет условное​ идентичны. В противном​С помощью Условного форматирования​ также для версий​Функция​

​ результат вышел​ выделения повторяющихся значений​, щелкаем по кнопке​(​ в том, что​ настроить цвет заливки,​

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

​ две таблицы на​ нажимаю обновить выскакивает​: Manyasha, только я​ вручную менять Артикул​ и подтягиваются цены.​ каком виде.​ форматирование. Для примера,​ случае — в​ выделить расхождения цветом,​ до Excel 2007​НАИМЕНЬШИЙ​«1»​ в первом поле​«Условное форматирование»​<>​ ним можно пользоваться​ шрифта в условном​ одном мониторе одновременно,​ сообщение — [Драйвер​ вот не понял​ 0000207 на 0000207​Если загнать ВПР​пример того, пример​ возьмем данные за​ них есть различия.​ а также выделить​ с выполнением этого​

​, окно аргументов которой​, а в двух​ вместо параметра​, которая имеет месторасположение​) и выделяем сравниваемый​ только в том​ форматировании, как написать​ описан в статье​

1. ​ ODBC Excel] Несоответствие​ как переделать формулу​ не придется. Ну,​ в ЕСЛИ, то​​ этого.​​ февраль и март,​ Формулу надо вводить​ счета встречающиеся только​ условия нет никаких​ было раскрыто, предназначена​​ случаях –​​«Повторяющиеся»​ на ленте в​ диапазон второй области.​ случае, если данные​ другие условия для​ «Сравнение таблиц Excel».​

   ​ типа выражений​

   

   ​ под задачу 2,​ я тоже вашей​ да. Если нашли​и про версию​

2. ​ как показано на​​ как формулу массива,​​ в одной таблице​ проблем. Но в​ для вывода указанного​«0»​следует выбрать параметр​​ блоке​​ Далее обворачиваем полученное​ в таблице упорядочены​

   

3. ​ выделения ячеек, строк,​Здесь рассмотрим,​Макрописец​ т.е. как в​ специфики не знаю.​ новую цену, то​ Excel не забудьте​ рисунке:​ т.е. после ввода​ (например, на рисунке​ Excel 2007 и​ по счету наименьшего​. То есть, программа​

   ​«Уникальные»​«Стили»​ выражение скобками, перед​ или отсортированы одинаково,​ т.д., читайте в​как сравнить столбцы в​:​​ столбец D вывести​​komerz_by​ берем оттуда, где​ упомянуть.​Чтобы найти изменения на​ формулы в ячейку​ выше счета, содержащиеся​ Excel 2010 для​ значения.​ не смогла отыскать​. После этого нажать​

   

4. ​. Из выпадающего списка​ которыми ставим два​ синхронизированы и имеют​ статье «Условное форматирование​ Excel​leg​ ячейки с номерами​: Здравствуйте. Пожалуйста. помогите​ нашли, иначе берем​и про примерный​ зарплатных листах:​​ жать не на​​ только в таблице​ того, чтобы открыть​В поле​ во второй таблице​​ на кнопку​​ переходим по пункту​

   

5. ​ знака​ равное количество строчек.​ в Excel». Получилось​, выделить разницу цветом,​,​ из столбца B,​ разобраться с похожей​ старую цену.​​ объем данных.​​Перейдите на лист с​

   

6. ​Enter​​ Январь, выделены синим,​​ оба окна одновременно,​​«Массив»​​ два значения, которые​​«OK»​​«Управление правилами»​​«-»​​ Давайте посмотрим, как​

   

7. ​ так.​​ символами, т.д.​​Файл такой же,​ которые есть в​ задачей.​b1b1g0n​и про интересующий​ именем «Март» и​, а на​ а желтым выделены​

   ​ требуется провести дополнительные​

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

   ​ столбце А. С​​Допустим есть список​​:​ способ решения -​ выберите инструмент: «ФОРМУЛЫ»-«Определенные​Ctrl+Shift+Enter​ счета только из​ манипуляции. Как это​​ диапазона дополнительного столбца​​ табличном массиве.​​Таким образом, будут выделены​​Активируется окошко диспетчера правил.​ получилось такое выражение:​ на практике на​Сравнить значения столбцов в​ сдали отчет по​ стартовом посте этой​​ массивами я вообще​​ с остатками (в​Basil​

   ​ формулами или макросами.​

   ​ имена»-«Присвоить имя».​​.​​ февральской таблицы).​

   

8. ​ сделать рассказывается в​«Количество совпадений»​Конечно, данное выражение для​ именно те показатели,​ Жмем в нем​​—(A2:A7<>D2:D7)​​ примере двух таблиц,​Excel формулой.​ продажам. Нам нужно​ темы? Если нет​ не дружу просто​ данном случае с​, благодарю за разъяснения!​форум позволяет прикрепить​​В окне «Создание имени»​​Если с отличающимися ячейками​

​Допустим вы работаете с​ отдельном уроке.​, который мы ранее​ того, чтобы сравнить​ которые не совпадают.​ на кнопку​Щелкаем по кнопке​ размещенных на одном​Можно сделать в​ сравнить эти отчеты​ — сделайте пример​Manyasha​ автошинами) от нескольких​ все потихоньку становится​ к сообщению​ для поля «Имя:»​ надо что сделать,​ таблицей созданной сотрудником,​

​Урок: Как открыть Эксель​

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

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

​ со СТРУКТУРОЙ данных​:​ поставщиков товара. В​ на свои места.​xls​ введите значение –​ то подойдет другой​ который в неупорядоченный​ в разных окнах​ функции​ применять и в​ Экселе​

1. ​.​.​​Итак, имеем две простые​​ и установить в​У нас такая​​ ИДЕНТИЧНОЙ структуре данных​​leg​ нем продавец устанавливает​ Сам пришел к​​-файлы.​​ Фамилия.​ быстрый способ: выделите​ способ заполняет информацию,​​Как видим, существует целый​​ЕСЛИ​

   

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

2. ​ выбор позиции​ выводит результат. Как​​ работников предприятия и​​ формулу. =А2=В2​ из магазинов. Как​

   

3. ​ и данными вида​ «=» на знак​ ли прописать формулу​ Н/Д, только вместо​ слишком много​ «Диапазон:» введите следующую​ нажмите клавишу​​ по определенным товарам.​​ таблицы между собой.​​ абсолютными.​​ усовершенствовать.​

   

4. ​ формулы, основой которой​«Использовать формулу»​ видим, в нашем​ их окладами. Нужно​Получится так.​ перенести данные из​ идентичного данным в​ «>» в условии​ чтообы вносить остатки​ ЕОШИБКА использовал ЕНД.​от Вас​

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

​ ссылку:​F5​ Одной из ваших​ Какой именно вариант​В поле​Сделаем так, чтобы те​ является функция​. В поле​ случае результат равен​

1. ​ сравнить списки сотрудников​Можно условным форматированием окрасить​ разных таблиц в​ Вашем файле и​ для СЧЁТЕСЛИ, и​ поставщиков на другие​Теперь пойдет этап​хочу для помощи​Выберите инструмент «ФОРМУЛЫ»-«Присвоить имя»​, затем в открывшемся​​ задач будет –​​ использовать зависит от​​«K»​​ значения, которые имеются​СЧЁТЕСЛИ​«Форматировать ячейки»​​ числу​​ и выявить несоответствия​ слова «Ложь» другим​​ одну, читайте в​​ приложите в своём​

   

2. ​ можно проверку на​ листы, а в​ 2. Ну, в​​ по​​ и в поле​

   

3. ​ окне кнопку​ сравнение. Следует проверить​​ того, где именно​​указывается, какое по​​ во второй таблице,​​. С помощью данного​записываем формулу, содержащую​«1»​ между столбцами, в​ цветом или окрасить​​ статье «Как связать​​ сообщении.​ пустые ячейки убрать:​ общем списке обновлялось​ принципе, смысл пока​​Вашей​​ «Имя:» введите значение​Выделить (Special)​ содержит ли столбец​ расположены табличные данные​ счету наименьшее значение​ но отсутствуют в​ инструмента можно произвести​ адреса первых ячеек​​, то есть, это​​ которых размещены фамилии.​ эти ячейки.​ таблицы в Excel»​SLAVICK​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(B$2:B$22;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$2:A$22;B$2:B$22)>0;СТРОКА(B$2:B$22));СТРОКА(B1:B21))-1);»»)​ количество и цена​ тот же. Теперь​задаче?​

   ​ - Зарплата.​

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

   

4. ​ диапазонов сравниваемых столбцов,​​ означает, что в​​Для этого нам понадобится​​Можно написать такую​​ тут.​: Спасибо конечно, но​leg​ соответственно товару и​ надо искать неточное​b1b1g0n​В поле «Диапазон:» введите​​Отличия по строкам (Row​​ или нет. Конечно​

   

5. ​ (на одном листе,​ указываем координаты первой​ списком.​​ каждый элемент из​​ разделенные знаком «не​

   

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

   

7. ​ поставщику. Заранее огромное​ соответствие. Просто может​: excel 2003. сделать​ ссылку:​ differences)​ можно воспользоваться инструментом:​

​ в разных книгах,​ ячейки столбца с​Прежде всего, немного переработаем​ выбранного столбца второй​ равно» (​ найдено одно несовпадение.​ листе. Вписываем туда​ С2. =СУММ(ЕСЛИ(A2:A6<>B2:B6;1;0)) Нажимаем​Как сравнить два столбца​krosav4ig​ огромно вам спасибо​ спасибо. Версия EXCELL​ быть так, что​ желательно формулами. сравнить​Теперь перейдите на лист​. В последних версиях​

1. ​ «ГЛАВНАЯ»-«Редактирование»-«Найти» (комбинация горячих​ на разных листах),​

   

2. ​ нумерацией, который мы​ нашу формулу​​ таблицы повторяется в​​<>​ Если бы списки​​ знак​​ «Enter». Копируем формулу​ в​​(10-й пост), я​​leg​ 2010. Пример файла​​ в первой таблице​​ столбец D со​

   

3. ​ с именем «Февраль»​ Excel 2007/2010 можно​ клавиш CTRL+F). Однако​ а также от​ недавно добавили. Адрес​СЧЁТЕСЛИ​ первой.​​). Только перед данным​​ были полностью идентичными,​«=»​ по столбцу. Тогда​Excel на совпадения.​ только добавил одну​

   

4. ​: Manyasha, а есть​ во вложении.​ артикул записан как​ столбцом B. при​ и выделите диапазон​ также воспользоваться кнопкой​ при регулярной необходимости​ того, как именно​ оставляем относительным. Щелкаем​, а именно сделаем​Оператор​ выражением на этот​ то результат бы​

​. Затем кликаем по​ в столбце с​Выделяем столбцы (у​ строку​ ли вариант формулы​kalbasiatka​ Арт.0000205 а в​ совпадении значений отсортировать​ ячеек B2:C12.​Найти и выделить (Find​ выполнения поиска по​ пользователь желает, чтобы​​ по кнопке​​ её одним из​​СЧЁТЕСЛИ​​ раз будет стоять​ был равен числу​​ первому наименованию, которое​​ разницей будут стоять​

​ нас столбцы А​:​ попроще? С не​

​: А сколько поставщиков?​ той, откуда нужно​

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

​ так, чтобы, к​А на панели «ГЛАВНАЯ»​ & Select) -​ таблице данный способ​​ это сравнение выводилось​​«OK»​ аргументов оператора​относится к статистической​ знак​«0»​ нужно сравнить в​ цифры. Единица будет​

​ и В). На​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Me.Connections(«запрос»).Refresh​​ большими объемами это​komerz_by​ брать значения -​ примеру, если B150=D100,​ выберите «Условное форматирование»-«Создать​ Выделение группы ячеек​ оказывается весьма неудобным.​ на экран.​

​.​

​ЕСЛИ​​ группе функций. Его​​«=»​.​ первом списке. Опять​ стоять, если есть​

​ закладке «Главная» нажимаем​​Попробуйте приложить больше​​ все работает отлично,​: Пока задача стоит​ 0205. Я так​ то ячейки A150​ правило»-«Использовать формулу для​ (Go to Special)​

1. ​ Кроме этого данный​Автор: Максим Тютюшев​Оператор выводит результат –​. Для этого выделяем​ задачей является подсчет​​. Кроме того, ко​​Таким же образом можно​

   

2. ​ ставим символ​​ различия, а «нуль»​​ на кнопку функции​​ своих данных для​​ а вот если​ по двум…​​ понимаю, в ВПР​​ стала рядом с​ определения форматированных ячеек:».​​на вкладке​​ инструмент не позволяет​

   

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

   ​ — данные в​​ «Найти и выделить»,​​ проверки, возможно действительно​ в одном столбце​kalbasiatka​ можно же это​ B150, как и​В поле ввода формул​Главная (Home)​ выполнять вычисления с​ практически одинаковую структуру.​3​ которой расположен оператор​ в которых удовлетворяют​ столбцов в данной​ в таблицах, которые​с клавиатуры. Далее​​ ячейках одинаковые. Получится​​ выбираем функцию «Выделение​

   ​ другая структура, или​ около 100 тысяч​: Если правильно понял​ сделать? просто последняя​

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

   

4. ​ и в другом​a87u​ будет стоять единица​​ D100 (после сортировки,​​Щелкните по кнопке «Формат»​ содержанием (по строкам).​ пользователю следует научиться​​ в отдельных строках,​​ из нумерации несовпадающих​. В строке формул​ данного оператора имеет​ абсолютную адресацию. Для​

   

5. ​ листах. Но в​ ячейке колонки, которую​Четвертый​В появившемся окне ставим​onzek​ примерно столько же,​: Доброго времени суток!​ в формуле, а​ к примеру, B150​ и на вкладке​ Затем их можно​ автоматически решать задачи​​ некоторые наименования строк​​ строк табличных массивов.​ перед ней дописываем​ такой вид:​ этого выделяем формулу​ этом случае желательно,​

   

6. ​ мы сравниваем, во​с​ галочку у слов​: Друзья, выручайте! Есть​ эксель просто с​ Проблема вот в​ не ноль. я​ и A150 станут​​ «Заливка» укажите зеленый​​ обработать, например:​ в Excel.​​ встречаются в одной​​ С помощью маркера​ выражение​=СЧЁТЕСЛИ(диапазон;критерий)​ курсором и трижды​ чтобы строки в​ второй таблице. Получилось​

​пособ.​ «Отличия по строкам».​ 3 столбца артикулов:​ ума сходит. А​ чем-имеется список товаров,​ правильно понимаю?​ A1 B1, как​

​ цвет.​залить цветом или как-то​Чтобы автоматизировать данный процесс​ таблице, но в​ заполнения копируем формулу​«ЕСЛИ»​

1. ​Аргумент​ жмем на клавишу​​ них были пронумерованы.​​ выражение следующего типа:​Можно​ Нажимаем «ОК».​​1 (битрикс) -​​ мне как раз​ которые продает магазин,​Basil​​ и D100 E100​​На всех окнах жмем​ еще визуально отформатировать​ стоит воспользоваться формулой​​ другой могут отсутствовать.​​ до самого низа.​без кавычек и​«Диапазон»​F4​ В остальном процедура​=A2=D2​​объединить таблицы Excel​​В таблице выделились все​ то, что есть​​ необходимо обрабатывать большие​​ и список товаров,​

   

2. ​: Увы, нет. Под​​ станут D1 и​​ ОК.​очистить клавишей​ с использованием функций​​Пусть на листах Январь​​Теперь, зная номера строк​ открываем скобку. Далее,​представляет собой адрес​. Как видим, около​ сравнения практически точно​Хотя, конечно, в каждом​​с отчетами. Тогда​​ ячейки с разными​

   ​ на сайте,​ объемы информации. Или​​ что продают на​​ неточным соответствием понимается​ E1 соответственно. примерный​После ввода всех условий​​Delete​​ =ИЛИ() и =СОВПАД().​​ и Февраль имеется​​ несовпадающих элементов, мы​ чтобы нам легче​ массива, в котором​ всех адресов столбцов​ такая, как была​ конкретном случае координаты​ все одинаковые данные​ данными так. Excel​2 (навижн) -​​ я слишком много​​ оптовой базе. Нужно​

   ​ ситуация, когда число​

   ​ объем 1000-2000 строк.​​ для форматирования Excel​​заполнить сразу все одинаковым​​Чтобы легко проверить наличие​​ две таблицы с​ можем вставить в​ было работать, выделяем​ производится подсчет совпадающих​ появился знак доллара,​ описана выше, кроме​ будут отличаться, но​​ соберутся в одну​​ сравнила данные в​ то, что есть​ требую от эксель​​ сравнить эти два​​ 205 не найдено,​

   

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

   

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

   

5. ​ по соответствующим счетам.​ значения с помощью​ значение​Аргумент​​ превращение ссылок в​​ при внесении формулы​Щелкаем по клавише​ будет посмотреть и​ — данные ячейки​3 (совпадения) -​ такие нужды не​ совпадении товара из​

   

6. ​ за ним, т.е.​ отсортированных так, чтобы​ которых изменились по​Ctrl+Enter​В ячейку B1 вводим​​Как видно из рисунков,​​ функции​

   

7. ​«ЕСЛИ»​​«Критерий»​​ абсолютные. Для нашего​​ придется переключаться между​​Enter​ отдельно данные по​​ А2 и данными​​ то, что есть​​ подходит?​​ списка магазина проставить​

   

8. ​ 206, например. И​​ в начале шли​​ сравнению с предыдущим​удалить все строки с​ названия товара например​ таблицы различаются:​ИНДЕКС​

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

   ​ строки, артикулы которых​​ месяцем.​​ выделенными ячейками, используя​ – Монитор.​Наличием (отсутствием) строк (наименований​. Выделяем первый элемент​ иконке​ В нашем случае​ примет следующий вид:​ случае выражение будет​ сравнения. Как видим,​​ сделать, смотрите в​​Если мы сейчас нажмём​

   

9. ​ — не уверен​:​​ из списка оптовой​​ упорядочены. Так что​ совпадают (то есть​​ команду​В ячейке B2 вводим​ счетов). Например, в​

   

10. ​ листа, содержащий формулу​«Вставить функцию»​ он будет представлять​=$A2<>$D2​ иметь следующий вид:​ при сравнении первых​​ статье «Как объединить​​ на мышь, то​ в правильности, делал​​leg​​ базы. В примере-сравнить​ ВПР здесь не​ ячейки D и​​В определенном условии существенное​​Главная — Удалить -​​ следующую формулу:​​ таблице на листе​НАИМЕНЬШИЙ​.​ собой координаты конкретных​​Данное выражение мы и​​=B2=Лист2!B2​ ячеек обоих списков​​ таблицы в Excel».​​ выделения ячеек исчезнут.​ сам​​, массивные формулы на​​ столбцы А и​

    

11. ​ поможет.​ B), а все​ значение имеет функция​ Удалить строки с​Обязательно после ввода формулы​​ Январь отсутствует счет​​. После этого переходим​Открывается окно аргументов функции​ ячеек первой табличной​ записываем в вышеуказанное​То есть, как видим,​ программа указала показатель​Пятый способ.​ Чтобы эти выделенные​​Нужно сделать 4​​ таких объемах всегда​

    

12. ​ С, и в​​Два варианта. Если​​ остальные — потом.​ ПОИСКПОЗ. В ее​ листа (Home -​ для подтверждения нажмите​ 26 (см. файл​

    ​ в строку формул​​ЕСЛИ​​ области.​ поле. После этого​​ перед координатами данных,​​«ИСТИНА»​Используем​ ячеек оставить, мы​ столбец (разница), в​ тормозят.​ столбце В получить​ артикулы не совпадают,​p.s. извините, не​ первом аргументе находится​ Delete — Delete​ комбинацию горячих клавиш​ примера), а в​ и перед наименованием​​. Как видим, первое​​Выделяем первый элемент дополнительного​​ щёлкаем по кнопке​​ которые расположены на​

    ​, что означает совпадение​​функцию «СЧЕТЕСЛИ» в​​ можем закрасить эти​ котором будут данные​Можно написать макрос,​ значения 35,30 и​ но хотя бы​ прикрепил файл, сейчас​ пара значений, которая​ Rows)​

    ​ CTRL+SHIFT+Enter. Ведь данная​​ таблице на листе​​«НАИМЕНЬШИЙ»​

    

13. ​ поле окна уже​ столбца, в котором​«Формат…»​ других листах, отличных​ данных.​Excel​ ячейки или изменить​ из второго столбца,​ будет быстрее. Или​ 60 соответственно.​ подход к написанию​

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

​ исправлюсь)​ должна быть найдена​и т.д.​ формула должна выполняться​ Февраль отсутствуют счет​дописываем название​ заполнено значением оператора​ будет производиться подсчет​.​ от того, где​Теперь нам нужно провести​. Эта функция посчитает​ цвет шрифта в​ если их не​ можно формулой​jakim​ артикулов всегда одинаковый,​b1b1g0n​ на исходном листе​Если списки разного размера​ в массиве. Если​ 10 и его​«ИНДЕКС»​СЧЁТЕСЛИ​ количества совпадений. Далее​Активируется окно​ выводится результат сравнения,​ аналогичную операцию и​

​ количество повторов данных​ этих ячейках функциями​

​ будет в первом​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$22;A2)>0;1;0)​:​ т.е., если в​: Вот, собственно, сам​ следующего месяца, то​ и не отсортированы​ все сделано правильно​ субсчета.​без кавычек, тут​. Но нам нужно​ щелкаем по пиктограмме​«Формат ячеек»​ указывается номер листа​ с остальными ячейками​ их первого столбца,​

​ раздела «Шрифт» на​

lumpics.ru

Сравнение 2-х таблиц в MS EXCEL

​Помогите пожалуйста решить​отметить ячейки, удовлетворяющие​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=INDEX($D$1:$D$11;MATCH(A1;$C$1:$C$11;0))​ первом списке​ файл.​ есть «Март». Просматриваемый​ (элементы идут в​ в строке формул​

​Разными значениями в строках.​ же открываем скобку​ дописать кое-что ещё​«Вставить функцию»​. Идем во вкладку​

​ и восклицательный знак.​ обеих таблиц в​

1. ​ сравнив их с​ закладке «Главная», пока​ данную задачку!​ условиям (есть в​buchlotnik​Арт.0000205​Basil​ диапазон определяется как​ разном порядке), то​ вы найдете фигурные​
2. ​ Например, по счету​ и ставим точку​ в это поле.​.​«Заливка»​

​Сравнение можно произвести при​ тех колонках, которые​ данными второго столбца.​ эти ячейки выделены.​Таблица с данными​ А, нет в​: как-то так​Арт.0000206​:​ соединение значений диапазонов,​

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

​ придется идти другим​ скобки.​ 57 обороты за​ с запятой (​ Устанавливаем туда курсор​Происходит запуск​. Тут в перечне​ помощи инструмента выделения​ мы сравниваем. Но​В дополнительном столбце​ Например, так.​ https://drive.google.com/open?id=0B8D43Ww0tpVyQ1RkcXNNQUxveTg​ В), а потом​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР(A1;$C$1:$D$11;2;0)​

​Арт.0000207,​b1b1g0n​ определенных именами, в​ путем.​В результате формула будет​ январь и февраль​;​ и к уже​Мастера функций​ цветов останавливаем выбор​ групп ячеек. С​ можно просто провести​ устанавливаем формулы, они​

​Или так.​заранее спасибо!​ отсортировать по убыванию.​a87u​а во втором​, уверены, что нужно​ пары. Таким образом​Самое простое и быстрое​ возвращать логическое значение​ не совпадают.​

​). Затем выделяем в​ существующему выражению дописываем​. Переходим в категорию​ на цвете, которым​

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

​Pelena​ Отсортировать, кстати, еще​:​0205,​ именно сортировать? Имхо,​ выполняется сравнение строк​ решение: включить цветовое​ ИСТИНА или ЛОЖЬ.​Если структуры таблиц примерно​ строке формул наименование​

​«=0»​«Статистические»​ хотим окрашивать те​

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

​ можно сравнивать только​ позволит существенно сэкономить​ условиями. Или в​ столбцах​:​ можно и по​jakim​0206,​ это дурацкий способ.​ по двум признакам​

​ выделение отличий, используя​

1. ​ В зависимости от​ одинаковы (большинство наименований​«ИНДЕКС»​без кавычек.​. Находим в перечне​ элементы, где данные​
2. ​ синхронизированные и упорядоченные​ время. Особенно данный​
3. ​ формуле указываем конкретные​Excel.​onzek​
4. ​ цвету (УФ в​,​0207,​Basil​ – фамилия и​ условное форматирование. Выделите​ того содержит ли​ счетов (строк) совпадают,​и кликаем по​После этого переходим к​ наименование​ не будут совпадать.​

excel2.ru

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

​ списки. Кроме того,​ фактор важен при​ данные, слова, которые​Здесь мы сравнили​, Прочитайте Правила форума​ предыдущем файле есть)​buchlotnik​то можно, я​: Я бы сделал​ зарплата. Для найденных​ оба диапазона с​ таблица исходное значение​ количество и наименования​ пиктограмме​ полю​«СЧЁТЕСЛИ»​ Жмем на кнопку​ в этом случае​ сравнивании списков с​ нужно посчитать в​ три столбца в​ и создайте свою​leg​, спасибо большое!!! Очень​ думаю, привести их​ как-нибудь так.​

Функция СОВПАД позволяет сравнить два столбца таблицы

​ совпадений возвращается число,​ данными и выберите​ или нет.​ столбцов совпадают), то​

​«Вставить функцию»​«Значение если истина»​. После его выделения​

1. ​«OK»​ списки должны располагаться​ большим количеством строк.​
2. ​ столбце.​ таблице, предварительно выделив​
3. ​ тему. Для Google​: Manyasha, все теперь​ помогли!!!​ к одинаковому виду.​b1b1g0n​ что по сути​ на вкладке​​ можно сравнить две​.​

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

​ Docs есть свой​

​ все отлично и​leg​ См. ЛЕВСИМВ, ПРАВСИМВ,​

​:​ для Excel является​Главная — Условное форматирование​Разбор принципа действия формулы​ таблицы. Проведем сравнение​После этого открывается небольшое​ ещё одной вложенной​«OK»​Вернувшись в окно создания​ другом на одном​ выполнить при помощи​ формулы в статье​ таблицы. Сравниваются данные​ раздел.​ быстро работает. Я​: Здравствуйте! Помогите пожалуйста​ ПСТР, ну или​Basil​ истиной. Поэтому следует​ — Правила выделения​ для сравнения двух​ двумя способами: один​ окошко, в котором​

​ функцией –​.​ правила форматирования, жмем​ листе.​ маркера заполнения. Наводим​ «Функция «СЧЕТЕСЛИ» в​

​ в ячейках построчно​Эта тема закрыта​ вам очень благодарен​ найти решение.​ СЦЕПИТЬ.​, да, вот то,​ использовать функцию =НЕ(),​ ячеек — Повторяющиеся​ столбцов разных таблиц:​ проще в реализации,​ нужно определить, ссылочный​

​СТРОКА​Происходит запуск окна аргументов​ на кнопку​

​Выделяем сравниваемые массивы. Переходим​ курсор на правый​ Excel».​ (А2, В2, С2,т.д.).​Зибин​

exceltable.com

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

​krosav4ig​Задача 1​А если артикулы​ что для начала​ которая позволяет заменить​ значения (Home -​Функция =СОВПАД() сравнивает (с​ другой нагляднее.​ вид должна иметь​. Вписываем слово​

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

​ оператора​«OK»​ во вкладку​ нижний угол ячейки,​Этот способ сравнения​ Получилось так.​: 1) Самая простая​:​Есть таблица с​ записываются​ нужно. С ВПР​ значение ИСТИНА на​​ Conditional formatting -​​ учетом верхнего регистра),​​Сначала определим какие строки​​ функция​

​«СТРОКА»​СЧЁТЕСЛИ​

​.​

​«Главная»​ где мы получили​

​ можно применить при​Как сравнить даты в​ формула — =А1=В1​leg​ номерами: в столбце​как угодно​ разберусь, подскажите, пожалуйста,​ ЛОЖЬ. Иначе будет​ Highlight cell rules​ являются ли два​​ (наименования счетов) присутствуют​​ИНДЕКС​​без кавычек, далее​​. Как видим, наименования​

​После автоматического перемещения в​. Далее щелкаем по​ показатель​ сравнении двух прайсов.​Excel.​ результатом будет или​​, а такой вариант​​ А — старые​по-разному, то это​​ как вы сделали​​ применено форматирование для​​ — Duplicate Values)​ значения идентичными или​​ в одной таблице,​или предназначенный для​ открываем скобки и​​ полей в этом​ окно​ значку​«ИСТИНА»​​ Смотрите статью «Как​​Можно сравнить даты.​

​ ИСТИНА или ЛОЖЬ​ не подойдет?​ номера, в столбце​ полная жесть, откровенно​

• ​ связь между столбцами​ ячеек значение которых​
• ​:​​ нет. Если да,​
• ​ но отсутствуют в​ работы с массивами.​ указываем координаты первой​​ окне соответствуют названиям​
• ​«Диспетчера правил»​«Найти и выделить»​. При этом он​​ сделать прайс-лист в​ Принцип сравнения дат​2) Выделение различий​leg​ B — новые​
• ​ говоря. Потому что​

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

​ A,B,C, а также​ совпали. Для каждой​Если выбрать опцию​ возвращается логическое значение​ другой. Затем, в​ Нам нужен второй​

​ ячейки с фамилией​ аргументов.​щелкаем по кнопке​, который располагается на​ должен преобразоваться в​ Excel».​ тот же –​​Выделите оба столбца​: krosav4ig, к сожалению​ номера, номера в​ на каком-то этапе,​ интересует как вы​ не найденной пары​Повторяющиеся​​ ИСТИНА. Учитывая тот​

​ таблице, в которой​​ вариант. Он установлен​​ во второй таблице,​Устанавливаем курсор в поле​«OK»​ ленте в блоке​​ черный крестик. Это​​Довольно часто перед пользователями​

​ выделяем столбцы, нажимаем​ и нажмите клавишу​ я не разобрался,​ обоих столбцах могут​ так или иначе,​ добавили условия сортировки?​ значений (то есть​, то Excel выделит​

​ факт что формула​ меньше строк отсутствует​​ по умолчанию, так​ ​ после чего закрываем​​«Диапазон»​​и в нем.​​ инструментов​ и есть маркер​ Excel стоит задача​ на кнопку «Найти​

​ F5, затем в​ как вашим вариантом​ повторятся. Необходимо сравнить​

​ оба списка придется​ Очень хорошо получилось,​ – несоответствие) &B2&$C2​ цветом совпадения в​ выполняется в массиве​

​ (в наиболее полной​ что в данном​ скобки. Конкретно в​

planetaexcel.ru

Сравнение данных в Excel на разных листах

​. После этого, зажав​Теперь во второй таблице​«Редактирование»​ заполнения. Жмем левую​ сравнения двух таблиц​ и выделить». Выбираем​ открывшемся окне кнопку​ пользоваться. И у​ на повторы столбец​ просматривать глазами. Тут​ именно то, что​ в диапазоне Фамилия&Зарплата,​ наших списках, если​ функция СОВПАД сравнивает​ таблице), выведем отчет​ окошке просто щелкаем​ нашем случае в​ левую кнопку мыши,​ элементы, которые имеют​. Открывается список, в​ кнопку мыши и​

Сравнение двух листов в Excel

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

​ выявления в них​ ячеек», ставим галочку​ по строкам. В​ на иконке вашего​ и в столбец​ способа, ну или​ к сожалению, Excel​

​ ошибку. Ошибочное значение​Уникальные​

1. ​ B1 с каждым​ собой разницу по​«OK»​«Значение если истина»​
2. ​ столбца с фамилиями​ соответствующими значениями первой​ позицию​ на количество строчек​
3. ​ отличий или недостающих​ у слов «Отличия​ последних версиях Excel​
4. ​ файла восклицательный знак​ С вывести только​ я его не​ знаю слишком слабо,​
5. ​ не является логическим​- различия.​
6. ​ значением во всех​ столбцам (разница оборотов​.​получилось следующее выражение:​
7. ​ второй таблицы. Как​ табличной области, будут​«Выделение группы ячеек…»​ в сравниваемых табличных​
8. ​ элементов. Каждый юзер​ по строкам». Нажимаем​
9. ​ 2007/2010 можно также​ стоит.​ те номера из​ знаю.​
10. ​ чтобы понять, как​ значением. Поэтому исползаем​

​Цветовое выделение, однако, не​ ячейках диапазона A5:A10.​ за январь и​Запускается окно аргументов функции​СТРОКА(D2)​ видим, координаты тут​ выделены выбранным цветом.​

​.​

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

​ массивах.​ справляется с этой​ «ОК».​ воспользоваться кнопкой Найти​vikttur​ столбца В, которых​Единственно, что тут​ вы это сделали.​ функцию ЕСЛИОШИБКА, которая​ всегда удобно, особенно​ А благодаря функции​ февраль).​ИНДЕКС​Теперь оператор​ же попадают в​Существует ещё один способ​Кроме того, в нужное​Как видим, теперь в​ задачей по своему,​Здесь расхождение дат в​ и выделить -​: Достаточно​ нет в столбце​ можно еще сделать,​И еще вопрос:​ присвоит логическое значение​ для больших таблиц.​ =ИЛИ() формула возвращает​Основным недостатком этого подхода​. Данный оператор предназначен​СТРОКА​ указанное поле. Но​ применения условного форматирования​ нам окно выделения​ дополнительном столбце отобразились​ но чаще всего​ ячейках строк второй​ Выделение группы ячеек​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—(СЧЁТЕСЛИ($B$2:$B$22;A2)>0)​ А. Т.е. если​ это просмотрев оба​ Когда в условиях​ для каждой ошибки​ Также, если внутри​ по отдельности результат​ является, то, что​ для вывода значения,​

exceltable.com

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

​будет сообщать функции​​ для наших целей​ для выполнения поставленной​ группы ячеек можно​ все результаты сравнения​ на решение указанного​ и третьей.​ на вкладке Главная​
​Manyasha​ в столбце В​ списка и задав​ функции ВПР вы​ – ИСТИНА. Это​ самих списков элементы​ вычислений функции =СОВПАД().​ отчет о сравнении​ которое расположено в​ЕСЛИ​ следует сделать данный​ задачи. Как и​ попасть и другим​ данных в двух​ вопроса тратится довольно​Есть еще один​

​3) Подсветка различий​​:​
​ нет номера, который​ соответствие одного артикула​ пишете =ВПР(A2;G:I;2;0), здесь​ способствует присвоению нового​ могут повторяться, то​ Если не использовать​ таблиц не включает​ определенном массиве в​номер строки, в​ адрес абсолютным. Для​ предыдущие варианты, он​ способом. Данный вариант​ колонках табличных массивов.​ большое количество времени,​ способ​

​ условным форматированием​​vikttur​ есть в столбце​ другому, сохранить это​ вот G:I, I​

​ формата только для​​ этот способ не​ функцию ИЛИ, тогда​
​ строки отсутствующие в​​ указанной строке.​ которой расположена конкретная​
​ этого выделяем данные​ требует расположения обоих​ особенно будет полезен​
​ В нашем случае​ так как далеко​сравнить даты в Excel​
​Алексей матевосов (alexm)​, верно, частенько забываю​
​ А, то его​ все на отдельном​ — весь столбец?​
​ ячеек без совпадений​ подойдет.​
​ формула будет возвращать​ наиболее полной таблице.​Как видим, поле​
​ фамилия, и в​ координаты в поле​​ сравниваемых областей на​​ тем пользователям, у​
​ не совпали данные​ не все подходы​​- сравнить периоды​​: Еще вариант считать​ про него (в​​ выводим в столбец​​ листе, чтобы в​

​теперь такой вопрос.​​ значений по зарплате​В качестве альтернативы можно​ только результат первого​ Например, в рассматриваемом​«Номер строки»​ случае, когда условие,​ и жмем на​ одном листе, но​ которых установлена версия​ только в одной​ к данной проблеме​ дат,есть ли в​ сколько раз А1​ смысле про двойное​ С, если же​ следующий раз можно​ если при замене​ в отношении к​ использовать функцию​
​ сравнения.​ нами случае, наиболее​уже заполнено значениями​ заданное в первом​ клавишу​ в отличие от​ программы ранее Excel​ строке. При их​ являются рациональными. В​
​ указанных периодах одинаковые​ встречается в В1:В10​ отрицание)​

​ в обоих столбцах​​ было использовать ВПР.​ я получаю Н/Д,​

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

​F4​​ ранее описанных способов,​ 2007, так как​

​ сравнении формула выдала​​ то же время,​​ даты и сколько​​Если количество равно​leg​ есть одинаковые номера,​b1b1g0n​ могу ли я,​ марту.​(COUNTIF)​ сразу несколько таких​ таблица на листе​НАИМЕНЬШИЙ​ функция​.​ условие синхронизации или​ метод через кнопку​ результат​ существует несколько проверенных​
​ дней в периодах​ единице, то ИСТИНА,​, восклицательный знак -​ то в столбец​: Вот это, как​ протягивая формулу вниз,​
​b1b1g0n​из категории​ формул на практике​ Январь, в которой​. От уже существующего​ЕСЛИ​Как видим, ссылка приняла​ сортировки данных не​

​«Найти и выделить»​​«ЛОЖЬ»​ алгоритмов действий, которые​ совпадают. Этот способ​ в противном случае​ это признак того,​ С их выводить​ мне кажется, существенно​ пропустить эту ячейку,​: В общем задача.​Статистические​ при сравнении двух​ отсутствует счет 26​ там значения следует​
​будет выводить этот​ абсолютную форму, что​ будет являться обязательным,​
​эти приложения не​. По всем остальным​ позволят сравнить списки​ может пригодиться, чтобы​ ЛОЖЬ​ что файл содержит​ не надо, так​ облегчило бы задачу​ оставив предыдущее значение​ Представьте, есть четыре​, которая подсчитывает сколько​ столбцов в разных​ из февральской таблицы.​ отнять разность между​ номер в ячейку.​ характеризуется наличием знаков​ что выгодно отличает​ поддерживают. Выделяем массивы,​ строчкам, как видим,​ или табличные массивы​ выявить пересечение дат​Формула для ячейки​ макросы.​ же если в​ в будущем. Не​
​ этой ячейки?​
​ столбца — два​ раз каждый элемент​ таблицах одновременно:​
​Чтобы определить какая из​ нумерацией листа Excel​ Жмем на кнопку​ доллара.​ данный вариант от​ которые желаем сравнить,​ формула сравнения выдала​ в довольно сжатые​
​ в периодах. Например,​ С1​Откройте файл, нажмите​ столбце А есть​ могли бы вы​Basil​ с ценами, два​

​ из второго списка​​Достаточно ввести массив формул​​ двух таблиц является​​ и внутренней нумерацией​«OK»​Затем переходим к полю​ ранее описанных.​ и жмем на​ показатель​ сроки с минимальной​
​ чтобы в отпуске​=СЧЁТЕСЛИ (B$1:B$10;A1)​ alt+f11 и в​ номера, которых нет​ подсказать, как это​: Связь есть между​ с артикулами. Соответственно,​ встречался в первом:​ в одну ячейку​ наиболее полной нужно​ табличной области. Как​.​«Критерий»​Производим выделение областей, которые​ клавишу​«ИСТИНА»​ затратой усилий. Давайте​ не было два​После ввода формулу​ модуле «Эта книга»​

​ в столбце В,​​ делается?​ столбцами А и​ сочетаются попарно: артикул-цена,​Полученный в результате ноль​ (E2), потом скопировать​ ответить на 2​ видим, над табличными​Как видим, первый результат​, установив туда курсор.​ нужно сравнить.​F5​
​.​ подробно рассмотрим данные​ сотрудника сразу или​ протянуть.​ увидите код (лично​ их тоже не​Basil​
​ С, столбец В​
​ артикул-цена.​
​ и говорит об​
​ его во все​
​ вопроса: Какие счета​
​ значениями у нас​
​ отображается, как​
​ Щелкаем по первому​Выполняем переход во вкладку​.​Кроме того, существует возможность​ варианты.​ даты учебы, оплаты​
​Если в столбце​ для меня пока​​ надо выводить в​​: Допустим получили вы​ здесь никак не​Задача: сравнить два​ отличиях.​ остальные ячейки диапазона​ в февральской таблице​ только шапка. Это​«ЛОЖЬ»​ элементу с фамилиями​ под названием​Активируется небольшое окошко перехода.​
​ с помощью специальной​Скачать последнюю версию​ счетов, т.д. не​ С все значения​ загадочный:))​ столбец С.​ первый список, где​ участвует. Связь между​ столбца с артикулами,​И, наконец, «высший пилотаж»​

​ E3:E8. Обратите внимание,​​ отсутствуют в январской?​ значит, что разница​. Это означает, что​ в первом табличном​«Главная»​ Щелкаем по кнопке​ формулы подсчитать количество​

​ Excel​​ пересекались. Об этом​ ИСТИНА, то таблицы​SLAVICK​Задача 2​ артикулы записаны абы​ столбцами в том,​ при нахождении совпадения​ — можно вывести​
​ что теперь мы​ и Какие счета в​ составляет одну строку.​ значение не удовлетворяет​ диапазоне. В данном​. Делаем щелчок по​
​«Выделить…»​ несовпадений. Для этого​Читайте также: Сравнение двух​ способе читайте в​ совпадают по данным.​:​В этой же​

​ как. Нашли, чему​​ что в столбец​ желательно окрасить эти​ отличия отдельным списком.​ используем абсолютные адреса​ январской таблице отсутствуют​ Поэтому дописываем в​ условиям оператора​ случае оставляем ссылку​ кнопке​в его нижнем​ выделяем тот элемент​ документов в MS​ статье «Как сравнить​Чтобы не просматривать​Manyasha​ таблице в столбец​ эти артикулы соответствуют​ С подтягивается не​ ячейки, отсортировать чтобы​ Для этого придется​ ссылок на диапазон​ в январской?​

​ поле​​ЕСЛИ​ относительной. После того,​«Условное форматирование»​ левом углу.​ листа, куда оно​ Word​ даты в Excel».​ весь столбец, все​, посмотрите в подключениях​

​ D вывести номера​​ во втором списке.​ любая цена, а​ одинаковые ячейки находились​
​ использовать формулу массива:​ $A$2:$A$12 во втором​Это можно сделать с​«Номер строки»​. То есть, первая​ как она отобразилась​. В активировавшемся списке​После этого, какой бы​ будет выводиться. Затем​Существует довольно много способов​Как посчитать разницу​ ли там ИСТИНА,​ и в именах​ из столбца B,​ Составили какую-нибудь такую​ цена согласно артикулу​ друг напротив друга,​Выглядит страшновато, но свою​

​ аргументе функции СОВПАД.​​ помощью формул (см.​

​значение​​ фамилия присутствует в​ в поле, можно​

​ выбираем позицию​​ из двух вышеперечисленных​

CyberForum.ru

Требуется сравнить два столбца, и проставить значения (Формулы/Formulas)

​ щелкаем по значку​​ сравнения табличных областей​ дат, стаж, возраст,​ можно в С11​ — там самое​ которые есть в​ таблицу:​ в столбце А.​ и главное: если​ работу выполняет отлично​Скачать формулу для сравнения​ столбец Е): =ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));»Нет»;»Есть») и​«-1»​ обоих списках.​ щелкать по кнопке​«Правила выделения ячеек»​ вариантов вы не​«Вставить функцию»​ в Excel, но​ как прибавить к​

​ вставить формулу​​ интересное, а код​​ столбце А. Т.е.​

​Артикул.00002050000205Арт.00002060000206Артикул 00002070000207После чего​​ Собственно, см. справку​​ артикулы совпадают, то​

​ ;)​​ двух столбцов таблиц​​ =ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));»Нет»;»Есть»)​​без кавычек.​​С помощью маркера заполнения,​​«OK»​. В следующем меню​

excelworld.ru

Сравнить два столбца и вывести уникальные значения (Формулы/Formulas)

​ избрали, запускается окно​​.​ все их можно​
​ дате число, т.д.,​
​=И (C1:C10)​ — это часть​ если в столбце​ вместо «кривых» артикулов​ по ВПР.​ из первого столбца​Каждый месяц работник отдела​ на совпадение​Сравнение оборотов по счетам​В поле​ уже привычным способом​.​ делаем выбор позиции​ выделения групп ячеек.​В окне​ разделить на три​ смотрите в статье​Таким образом сравнивают​ решения.​ B есть номер​ подставляете нормальные, а​Для 2003 фильтр​ с ценами (из​ кадров получает список​В первом аргументе должны​ произведем с помощью​«Массив»​ копируем выражение оператора​В элемент листа выводится​«Повторяющиеся значения»​ Устанавливаем переключатель в​Мастера функций​ большие группы:​ «Дата в Excel.​
​ таблицы, в которых​
​правда этот код​ из столбца А,​ потом уже подставляете​ по условию, далее​ первой пары артикул-цена),​ сотрудников вместе с​ быть относительные адреса​ формул: =ЕСЛИ(ЕНД(ВПР($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​ значений второй таблицы.​на весь столбец.​
​ числу​Запускается окно настройки выделения​
​«Выделить по строкам»​«Математические»​​ одном листе;​​Можно сравнить числа.​ не обязательно в​ обновлять подключение.​ столбец D.​
​ списка.​​Ну, да весь​​ другой столбец «цена»​ копирует список на​ (как и в​В случае отсутствия соответствующей​

​ При этом все​​ Как видим, по​«1»​

​ повторяющихся значений. Если​​. Жмем по кнопке​выделяем наименование​сравнение таблиц, расположенных на​Функцию выделения ячеек можно​ тех же строках.​по моему нужно​Файл с номерами​Такая таблица с​ столбец I. G:I​ (соответственно из второй​ новый лист рабочей​ предыдущем примере).​

​ строки функция ВПР()​​ координаты делаем абсолютными,​​ двум позициям, которые​​. Это означает, что​ вы все сделали​«OK»​СУММПРОИЗВ​ разных листах;​ вызвать клавишей F5.​
​Abram pupkin​

​ тогда еще добавить​​ в приложении. Буду​ каждым разом будет​

​ — три столбца​​ пары артикул-цена).​ книги Excel. Задача​Типовая задача, возникающая периодически​ возвращает ошибку #Н/Д,​ то есть, ставим​ присутствуют во второй​ в перечне имен​ правильно, то в​.​. Щелкаем по кнопке​сравнение табличных диапазонов в​ В появившемся окне​: аналогичные вопросы​ обновление.​ очень благодарен за​ расширяться, но от​ G, H, I​ikki​ следующая: сравнить зарплату​ перед каждым пользователем​

​ которая обрабатывается связкой​​ перед ними знак​​ таблице, но отсутствуют​​ второй таблицы фамилия​ данном окне остается​Как видим, после этого​
​«OK»​ разных файлах.​ «Переход» нажимаем кнопку​
​http://otvet.mail.ru/question/87383103​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Sub Workbook_Open()​ помощь​ просмотра списков глазами​ полностью. Было бы​: Представьте: сделал.​ сотрудников, которая изменилась​ Excel — сравнить​ функций ЕНД() и​ доллара уже ранее​

​ в первой, формула​​«Гринев В. П.»​ только нажать на​ несовпадающие значения строк​.​

​Именно исходя из этой​​ «Выделить…».​​http://otvet.mail.ru/question/87175757​​Me.Connections(«запрос»).ODBCConnection.Connection = «ODBC;DBQ=»​Manyasha​

​ все равно на​​ достаточно G:H, просто​сравнил два столбца​ по отношению к​ между собой два​ ЕСЛИ(), заменяя ошибку​ описанным нами способом.​ выдает номера строк.​, которая является первой​

​ кнопку​​ будут подсвечены отличающимся​
​Активируется окно аргументов функции​

​ классификации, прежде всего,​​Второй способ.​​http://otvet.mail.ru/question/97716954​​ & Me.FullName &​:​ мой взгляд не​ там у вас​
​ с артикулами, при​​ предыдущему месяцу. Для​ диапазона с данными​ на 0 (в​Жмем на кнопку​
​Отступаем от табличной области​ в списке первого​«OK»​ оттенком. Кроме того,​СУММПРОИЗВ​ подбираются методы сравнения,​

​Можно в​​Дима слепаков​​ «;Driver={Microsoft Excel Driver​​leg​ уйти.​ объединение ячеек, поэтому​ нахождении совпадения окрасил​ этого необходимо выполнить​ и найти различия​
​ случае отсутствия строки)​«OK»​ вправо и заполняем​
​ табличного массива, встречается​. Хотя при желании​ как можно судить​
​, главной задачей которой​
​ а также определяются​Excel сравнить и выделить​: 1) Посмотрите эту​ (*.xls, *.xlsx, *.xlsm,​, я запуталась, так​
​b1b1g0n​
​ вместо G:H в​
​ эти ячейки, отсортировал​​ сравнение данных в​ между ними. Способ​ или на значение​.​ колонку номерами по​ один раз.​
​ в соответствующем поле​ из содержимого строки​ является вычисление суммы​ конкретные действия и​
​ цветом​ программу http://infostart.ru/public/186905/​​ *.xlsb)}»​​ как нужно: или​
​: я, видимо, немного​ формулу попало G:I,​

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

​ячейки с разными данными​​2) Можете попробовать​​Me.Connections(«запрос»).Refresh​​Цитата​
​ неправильно вас понял.​ я не стал​ находились друг напротив​ листах. Воспользуемся условным​ случае, определяется типом​С помощью Условного форматирования​ экран протягиваем функцию​1​ подобное выражение и​ выбрать другой цвет​ активной одну из​ Но данную функцию​ задачи. Например, при​

​с помощью условного​​ автоматически сравнить excel​End Sub​​leg, 17.06.2015 в​​ я думал, можно​ менять. Но это​ друга, и главное:​​ форматированием. Таким образом​
​ исходных данных.​
​ можно выделить расхождения​ с помощью маркера​. Количество номеров должно​ для всех других​ выделения.​

​ ячеек, находящуюся в​​ можно использовать и​ проведении сравнения в​
​ форматирования.​ таблицы тут http://сравнитьэксель.рф​Цитата​
​ 23:11, в сообщении​ как-нибудь их связать.​ все несущественно, можно​
​ если артикулы совпадают,​ мы не только​Если списки синхронизированы (отсортированы),​ (например, красным цветом).​ заполнения до конца​ совпадать с количеством​
​ элементов первой таблицы.​После того, как мы​ указанных не совпавших​ для наших целей.​ разных книгах требуется​Итак, мы выделяем​
​3) https://3dnews.ru/585650 прочитайте​leg, 26.06.2015 в​
​ № 1200?’200px’:»+(this.scrollHeight+5)+’px’);»> в​ чтобы потом через​
​ было сделать G:Z,​

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

excelworld.ru

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

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

​ http://office-guru.ru/excel/kak-v-excel-sravnit-dva-stolbca-i-udalit-dublikaty-vydelit-raskrasit-peremestit-288.html и хорошая​​ № 11200?’200px’:»+(this.scrollHeight+5)+’px’);»>krosav4ig, к​ номера, который есть​ Артикул 0000207 как​
​ бы на работу.​ (из первой пары​ ячеек, но и​ надо, по сути,​
​ 2-х списков в​ которые присутствуют во​
​ ускорить процедуру нумерации,​
​ заполнения, как это​ будут выделены выбранным​
​ метод условного форматирования.​=СУММПРОИЗВ(массив1;массив2;…)​Кроме того, следует сказать,​ На закладке «Главная»​
​ статья на the​ сожалению я не​ в столбце А​ 0000207, то есть​ Или совсем правильно​
​ артикул-цена), вставил цену​
​ выделим их цветом.​ сравнить значения в​ MS EXCEL можно​ второй таблице, но​ можно также воспользоваться​

​ мы уже делали​​ цветом. Те элементы,​
​ Как и в​
​Всего в качестве аргументов​
​ что сравнивать табличные​

​ в разделе «Стили»​​ village http://www.the-village.ru/village/business/cloud/148301-vlastelin-tablits-10-fishek-excel-o-kotoryh-vy-ne-znali​ разобрался, как вашим​
​Сделала для обоих​ обращался к третей​ $G$11:$H$1468 — именно​
​ в другой столбец​В фирме может быть​ соседних ячейках каждой​ сформировать список наименований​ отсутствуют в первой,​

​ маркером заполнения.​

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) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

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

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

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

Функция СОВПАД в Excel предназначена для проверки двух текстовых срок на их полное соответствие друг другу и возвращает одно из двух возможных логических значений: ИСТИНА – если строки полностью совпадают, ЛОЖЬ – если обнаружено хотя бы одно несоответствие в сравниваемых данных. При работе данной функции учитывается регистр букв. Это значит, что результат сравнения двух строк «текст» и «ТеКсТ» будет равен значению ЛОЖЬ. Распространенный вариант применения рассматриваемой функции – определение вхождения некоторого текста в документ.

Сравнение двух таблиц по функции СОВПАД в Excel

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

Вид таблицы данных:

Для сравнения двух строк используем следующую формулу массива (CTRL+SHIFT+Enter):

Описание параметров функции СОВПАД:

• D3 – текущая ячейка с текстом из второй таблицы;
• $B$3:$B$13 – соответствующая ячейка с текстом из второй таблицы для проверки на совпадение со значением D3.

Функция ИЛИ возвращает логическое значение ИСТИНА из массива если хотя бы одно из них совпадает с исходным значением.

Протянем данную формулу вниз до конца таблицы, чтобы Excel автоматически рассчитал значения для остальных строк:

Как видно, в сравниваемых строках были найдены несоответствия.

Выборка значений из таблицы по условию в Excel без ВПР

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

Вид таблицы данных:

Поскольку товар имеет фиксированную стоимость, для определения самого продаваемого смартфона можно использовать встроенную функцию МОДА. Чтобы найти наименование наиболее продаваемого товара используем следующую запись:

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

В результате расчетов получим:

Для определения общей прибыли от продаж iPhone 5s используем следующую запись:

Функция СУММПРИЗВ используется для расчета произведений каждого из элементов массивов, переданных в качестве первого и второго аргументов соответственно. Каждый раз, когда функция СОВПАД находит точное совпадение, значение ИСТИНА будет прямо преобразовано в число 1 (благодаря двойному отрицанию «—») с последующим умножением на значение из смежного столбца (стоимость).

Результат расчетов формулы:

Всего было куплено 4 модели iPhone 5s по цене 239 у.е., что в целом составило 956 у.е.

Правила синтаксиса и параметры функции СОВПАД в Excel

Функция СОВПАД имеет следующий вариант синтаксической записи:

• текст1 – обязательный для заполнения, принимает ссылку на ячейку с текстом или текстовую строку для сравнения с данными, принимаемые вторым аргументом.
• текст2 – обязательный для заполнения, принимает ссылку на ячейку или текст, с которым сравниваются данные, переданные в виде первого аргумента.

1. Результат выполнения функции СОВПАД, принимающей на вход два имени, является код ошибки #ИМЯ? (например, СОВПАД(имя;имя)). Для корректной работы функции указываемые текстовые данные необходимо помещать в кавычки (например, («имя»;«имя»)).
2. Функция выполняет промежуточное преобразование числовых данных в текст. Например, результат выполнения =СОВПАД(111;111) будет логическое значение ИСТИНА. Однако, преобразование логических данных в числа текстового формата не выполняется. Например, результат выполнения =СОВПАД(ИСТИНА;1) будет логическое ЛОЖЬ.
3. Результат сравнения двух пустых ячеек или пустых текстовых строк с использованием функции СОВПАД — логическое ИСТИНА.

Добрый день!

Эта статья посвящена решению такого вопроса, как сравнить две таблицы в 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 по строкам

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2 , вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2 . Если результат вычисления равен “0” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого сделаем следующее:

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

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

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

• В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

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

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

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

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

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

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

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

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

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

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

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

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

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

или

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2, вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2. Если результат вычисления равен «0» — это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат «Уникальная строка», если нет, то «Не уникальная строка».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=A2&B2&C2&D2

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

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

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

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого сделаем следующее:

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

=A2&B2&C2&D2

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

• Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15;
• Затем, на вкладке «Главная» на Панели инструментов нажмем на пункт «Условное форматирование» -> «Создать правило»:

• В диалоговом окне «Создание правила форматирования» кликните на пункт «Использовать формулу для определения форматируемых ячеек» и в поле «Форматировать значения, для которых следующая формула является истинной» вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

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

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