Как проверить список фамилий в excel

Как в excel сравнить два списка

​Смотрите также​: . ​Zeek7​ содержанием (по строкам).​ наименования счетов из​Пусть на листах Январь​ списке, в другом​ перед ними знак​ нумерацией, который мы​«Значение если истина»​СЧЁТЕСЛИ​ значений.​ данный вариант от​ блоке​ групп ячеек. С​«Математические»​ которых размещены фамилии.​Довольно часто перед пользователями​oks26​: отлично подошло мне,​ Затем их можно​ обоих таблиц (без​ и Февраль имеется​ отсутствуют).​ доллара уже ранее​ недавно добавили. Адрес​получилось следующее выражение:​

​, и после преобразования​Аргумент​

​ ранее описанных.​«Стили»​ его помощью также​

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

​выделяем наименование​Для этого нам понадобится​ Excel стоит задача​: спасибо!​ по подобному вопросу,​ обработать, например:​

• ​ повторов). Затем вывести​ две таблицы с​
• ​Создадим для удобства 2​ описанным нами способом.​
• ​ оставляем относительным. Щелкаем​СТРОКА(D2)​

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

​ разницу по столбцам.​ оборотами за период​ Динамических диапазона Список1​Жмем на кнопку​ по кнопке​Теперь оператор​

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

​ заполнения зажимаем левую​задает условие совпадения.​ нужно сравнить.​ переходим по пункту​ синхронизированные и упорядоченные​. Щелкаем по кнопке​ листе. Вписываем туда​ или списков для​: огромное спасибо. ​ примере появилась ошибка​ еще визуально отформатировать​Для этого необходимо:​ по соответствующим счетам.​ и Список2, которые​«OK»​«OK»​СТРОКА​ кнопку мыши и​ В нашем случае​Выполняем переход во вкладку​«Управление правилами»​ списки. Кроме того,​«OK»​ знак​ выявления в них​l_anton​ macro terminate! и​

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

​ он будет представлять​ под названием​.​ в этом случае​​.​​«=»​ отличий или недостающих​: Это простой ВПР​ есть ли решение​Delete​​ =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));»») сформировать​​ таблицы различаются:​ диапазоны ячеек, содержащие​После вывода результат на​Оператор выводит результат –​ЕСЛИ​Как видим, программа произвела​

​ собой координаты конкретных​

​«Главная»​Активируется окошко диспетчера правил.​ списки должны располагаться​Активируется окно аргументов функции​

​ счетов). Например, в​ списках.​ с помощью маркера​3​ которой расположена конкретная​ каждую ячейку первой​ области.​​ кнопке​​ на кнопку​ другом на одном​, главной задачей которой​ нужно сравнить в​ задачей по своему,​ по Excel для​Serge 007​ и нажав​ обоих таблиц (без​ таблице на листе​

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

​«Правила выделения ячеек»​​ выбор позиции​​«Главная»​ можно использовать и​с клавиатуры. Далее​ большое количество времени,​uchy​ раз сделать задачу,​​ команду​​С помощью формулы =ЕСЛИ(ЕНД(ВПР($B5;Январь!$A$7:$C$81;2;0));0;ВПР($B5;Январь!$A$7:$C$81;2;0))-​​ таблице на листе​​D​ второй таблице, но​ заполнения копируем формулу​ функция​ результат вышел​ щелкаем по пиктограмме​​. В следующем меню​​«Использовать формулу»​. Далее щелкаем по​

​ для наших целей.​

​ кликаем по первой​​ так как далеко​​: Много ответов уже​

​ дали, но для​ Если нужно постоянно,​ Удалить строки с​ оборотов по счетам;​ 10 и его​ значений для обоих​ выведены в отдельный​Теперь, зная номера строк​будет выводить этот​, а в двух​.​«Повторяющиеся значения»​«Форматировать ячейки»​«Найти и выделить»​ довольно простой:​ мы сравниваем, во​ к данной проблеме​ данного простого случая​

​ листа (Home -​С помощью Условного форматирования​ субсчета.​ списков (см. статью​ диапазон.​ несовпадающих элементов, мы​ номер в ячейку.​ случаях –​

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

​Происходит запуск​.​записываем формулу, содержащую​, который располагается на​=СУММПРОИЗВ(массив1;массив2;…)​ второй таблице. Получилось​ являются рациональными. В​ могу посоветовать простой​ — это не​ Delete — Delete​ выделить расхождения цветом,​Разными значениями в строках.​

​ Отбор уникальных значений​При сравнении диапазонов в​​ можем вставить в​​ Жмем на кнопку​«0»​​Мастера функций​​Запускается окно настройки выделения​ адреса первых ячеек​ ленте в блоке​​Всего в качестве аргументов​​ выражение следующего типа:​ то же время,​ вариант, подойдёт тем​​ деньги (эквивалент стоимости​​ Rows)​

​ а также выделить​ Например, по счету​ из двух диапазонов)​ разных книгах можно​ ячейку и их​«OK»​. То есть, программа​. Переходим в категорию​ повторяющихся значений. Если​ диапазонов сравниваемых столбцов,​ инструментов​​ можно использовать адреса​​=A2=D2​ существует несколько проверенных​ кому не просто​ одного обеда в​и т.д.​​ счета встречающиеся только​​ 57 обороты за​

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

​ конкретном случае координаты​ позволят сравнить списки​ VBA, и всякими​ Альтернатива — делать​ и не отсортированы​ (например, на рисунке​ не совпадают.​=ЕСЛИОШИБКА(ЕСЛИОШИБКА(​ варианты, где требуется​

​ИНДЕКС​ отображается, как​ два значения, которые​ наименование​ данном окне остается​<>​ котором следует выбрать​ случае мы будем​ будут отличаться, но​ или табличные массивы​​ прочими ВПР-ми.​​ ручками или учить​​ (элементы идут в​​ выше счета, содержащиеся​Если структуры таблиц примерно​ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0));​​ размещение обоих табличных​​. Выделяем первый элемент​«ЛОЖЬ»​​ имеются в первом​​«СЧЁТЕСЛИ»​

​ затратой усилий. Давайте​ помечаем разными цветами.​ самому​ путем.​​ а желтым выделены​​ количество и наименования​

​: Я не спорю,​ решение: включить цветовое​ февральской таблицы).​ можно сравнить две​ оба списка с​ случае – это​ и перед наименованием​. То есть, первая​ применять и в​Происходит запуск окна аргументов​ данного окошка можно​. Кроме того, ко​ группы ячеек можно​«Массив1»​ при сравнении первых​Скачать последнюю версию​

​ список 2(на листе​ что в Москве​

​«ИНДЕКС»​С помощью маркера заполнения,​ усовершенствовать.​. Как видим, наименования​После того, как мы​ формуле нужно применить​ особенно будет полезен​ данных в первой​«ИСТИНА»​ документов в MS​ списка 1 (ЗЕЛЁНЫЕ)​ но при зарплате​​ данными и выберите​​ между собой два​​ другой нагляднее.​​ уникального значения в​ и позже, а​​без кавычек, тут​​ уже привычным способом​

​Сделаем так, чтобы те​ полей в этом​ произведем указанное действие,​

​ абсолютную адресацию. Для​ тем пользователям, у​

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

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

​ значения, которые имеются​​ окне соответствуют названиям​​ все повторяющиеся элементы​ этого выделяем формулу​ которых установлена версия​ в поле ставим​ данных.​Существует довольно много способов​ 2 (на Лист​ крайне редко обедаю​

​Главная — Условное форматирование​

​ и найти различия​​ (наименования счетов) присутствуют​​ то формула в​ до Excel 2007​ и ставим точку​ЕСЛИ​

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

​ 2).​ за такие деньги.​ — Правила выделения​ между ними. Способ​ в одной таблице,​​ ячейке​​ с выполнением этого​

​ Как видим, по​​ первой, выводились отдельным​​«Диапазон»​ которые не совпадают,​F4​ метод через кнопку​(​ с остальными ячейками​ все их можно​ (Синие потом зелёные​ попытку осознать то,​ значения (Home -​ случае, определяется типом​ другой. Затем, в​=СУММПРОИЗВ(ABS(E5:E34-F5:F34)) вернет 0,​ проблем. Но в​). Затем выделяем в​​ двум позициям, которые​​ списком.​

​. После этого, зажав​ останутся окрашенными в​. Как видим, около​«Найти и выделить»​

​<>​​ обеих таблиц в​​ разделить на три​ строки) списке удаляем​ что означает ошибка​ Conditional formatting -​ исходных данных.​ таблице, в которой​ т.е. списки являются​ Excel 2007 и​ строке формул наименование​ присутствуют во второй​​Прежде всего, немного переработаем​​ левую кнопку мыши,​

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

​ копирование формулы, что​сравнение таблиц, расположенных на​ массиве записей остались​ она справилась отлично,​Если выбрать опцию​ надо, по сути,​

​ о сравнении, представляющий​Если все значения из​​ требуется провести дополнительные​​«Вставить функцию»​Отступаем от табличной области​ её одним из​​ видим, координаты тут​​ в чем отличие​ абсолютные. Для нашего​ клавишу​​ которыми ставим два​​ позволит существенно сэкономить​ разных листах;​ только те записи​​ а вот с​​Повторяющиеся​ сравнить значения в​ собой разницу по​ списка уникальных присутствуют​ манипуляции. Как это​.​​ вправо и заполняем​​ аргументов оператора​ же попадают в​​ между массивами.​​ конкретного случая формула​

​ указанное поле. Но​При желании можно, наоборот,​​ примет следующий вид:​​.​«-»​ фактор важен при​​ разных файлах.​​ которые отсутствуют во​​ не захотела. И​​ цветом совпадения в​ строки. Как самый​ за январь и​ то формулы в​ отдельном уроке.​ окошко, в котором​ порядку, начиная от​. Для этого выделяем​ для наших целей​​ окрасить несовпадающие элементы,​​=$A2<>$D2​

​Активируется небольшое окошко перехода.​

​. В нашем случае​​ сравнивании списков с​​Именно исходя из этой​​ втором. Их можно​​ я с радостью​ наших списках, если​ простой вариант -​ февраль).​ ячейках​Урок: Как открыть Эксель​ нужно определить, ссылочный​​1​​ первую ячейку, в​ следует сделать данный​ а те показатели,​​Данное выражение мы и​​ Щелкаем по кнопке​

​ значения​​ таблиц не включает​​ в ячейке​ таблицы между собой.​​или предназначенный для​​ сравниваемой таблице. Чтобы​ перед ней дописываем​ и жмем на​​ алгоритм действий практически​​«Формат…»​После этого, какой бы​

​.​​ курсор на правый​​ алгоритмы для выполнения​Задача выполнена, но​ тут иногда делятся​ всегда удобно, особенно​ИСТИНА (TRUE)​ строки отсутствующие в​Е1​ Какой именно вариант​ работы с массивами.​​ ускорить процедуру нумерации,​​ выражение​

​ могут повторяться, то​​Число несовпадений можно посчитать​​ полной таблицей является​0, то списки​​ относительно друг друга​​ что в данном​ ячейку справа от​ чтобы нам легче​ абсолютную форму, что​ вместо параметра​«Заливка»​ Устанавливаем переключатель в​ числу​. При этом он​ файла Excel.​ отдельный список. Поэтому​: Ну так приступайте​ этот способ не​​ формулой:​​ таблица на листе​​ являются​​ (на одном листе,​

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

​ предлагаю продолжение :​​На сайте должна​​ подойдет.​

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

​, то есть, это​ черный крестик. Это​ что сравнивать табличные​5. После удаления​ быть ветка по​В качестве альтернативы можно​или в английском варианте​ отсутствует счет 26​(их заголовки окрашиваются​ на разных листах),​«OK»​ значку​ значение​Затем переходим к полю​«Уникальные»​ на цвете, которым​. Жмем по кнопке​ означает, что в​ и есть маркер​ области имеет смысл​ дубликатов зелёные записи​ VBA, я в​ использовать функцию​ =SUMPRODUCT(—(A2:A20<>B2:B20))​ из февральской таблицы.​ оранжевым цветом) ​ а также от​.​

​«Критерий»​. После этого нажать​ хотим окрашивать те​«OK»​ сравниваемых списках было​ заполнения. Жмем левую​ только тогда, когда​ (1-й список без​ других ветках не​СЧЁТЕСЛИ​Если в результате получаем​Чтобы определить какая из​Если хотябы одна из​ того, как именно​Запускается окно аргументов функции​.​

Сравнение 2-х списков в MS EXCEL

​, установив туда курсор.​ на кнопку​

​ элементы, где данные​​.​​ найдено одно несовпадение.​ кнопку мыши и​

Задача

​ они имеют похожую​

​ совпадений со 2-м)​ бываю, поищите сами​(COUNTIF)​

​ ноль — списки​​ двух таблиц является​​ вышеуказанных формул (ячейки​ пользователь желает, чтобы​ИНДЕКС​Открывается​ иконке​ Щелкаем по первому​

​«OK»​​ не будут совпадать.​​Как видим, после этого​ Если бы списки​ тянем курсор вниз​ структуру.​ перекрашиваем в третий​

​Все имена заняты​​из категории​​ идентичны. В противном​ наиболее полной нужно​Е2 F2​ это сравнение выводилось​. Данный оператор предназначен​Мастер функций​

Решение

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

​Самый простой способ сравнения​ цвет — допустим​: Можно и без​

• ​Статистические​​ случае — в​​ ответить на 2​) возвращают не 0,​ на экран.​ для вывода значения,​. Переходим в категорию​.​ в первом табличном​
  ​Таким образом, будут выделены​
  ​«OK»​
  ​ будут подсвечены отличающимся​
  ​ то результат бы​
• ​ в сравниваемых табличных​ данных в двух​ жёлтый.​ VBA, с помощью​, которая подсчитывает сколько​
• ​ них есть различия.​ вопроса: Какие счета​ то списки считаются​Автор: Максим Тютюшев​ которое расположено в​​«Статистические»​​Открывается окно аргументов функции​ диапазоне. В данном​​ именно те показатели,​​.​ оттенком. Кроме того,​
• ​ был равен числу​ массивах.​ таблицах – это​6. Копируем жёлтые​ ВПР и фильтра.​​ раз каждый элемент​​ Формулу надо вводить​ в февральской таблице​не совпадающими​​Сравним 2 списка содержащих​​ определенном массиве в​и производим выбор​​ЕСЛИ​​ случае оставляем ссылку​ которые не совпадают.​​Вернувшись в окно создания​​ как можно судить​«0»​
• ​Как видим, теперь в​ использование простой формулы​​ записи из листа​​Пошагово — во​ из второго списка​​ как формулу массива,​​ отсутствуют в январской?​

Тестируем

​.​ ТЕКСТОВЫЕ повторяющиеся значения.​​ указанной строке.​​ наименования​. Как видим, первое​ относительной. После того,​Урок: Условное форматирование в​

​ правила форматирования, жмем​ из содержимого строки​.​​ дополнительном столбце отобразились​​ равенства. Если данные​ 2 в​ вложении.​ встречался в первом:​ т.е. после ввода​ и Какие счета в​1. В файле примера​Пусть в столбцах​Как видим, поле​«НАИМЕНЬШИЙ»​

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

​ поле окна уже​ как она отобразилась​ Экселе​ на кнопку​ формул, программа сделает​Таким же образом можно​ все результаты сравнения​ совпадают, то она​

​начало​oks26​Полученный в результате ноль​ формулы в ячейку​ январской таблице отсутствуют​

1. ​«Номер строки»​. Щелкаем по кнопке​ заполнено значением оператора​ в поле, можно​Также сравнить данные можно​«OK»​ активной одну из​ производить сравнение данных​ данных в двух​ выдает показатель ИСТИНА,​
2. ​(! Важно) листа​: Доброе время суток!​ и говорит об​ жать не на​ в январской?​

​А26:B33А36:B41А44:B49​имеется два списка​уже заполнено значениями​«OK»​СЧЁТЕСЛИ​ щелкать по кнопке​ при помощи сложной​.​ ячеек, находящуюся в​ в таблицах, которые​

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

​ колонках табличных массивов.​ а если нет,​ 1. Так что​ подскажите пожалуйста как​ отличиях.​Enter​Это можно сделать с​) имеется 3 пары​ с повторяющимися значениями.​ функции​.​. Но нам нужно​«OK»​ формулы, основой которой​

​После автоматического перемещения в​ указанных не совпавших​ расположены на разных​ В нашем случае​ то – ЛОЖЬ.​ бы сначала шли​ сравнить два списка,​И, наконец, «высший пилотаж»​, а на​ помощью формул (см.​ списков каждого типа:​Сравнить содержимое обоих списков.​НАИМЕНЬШИЙ​

​Функция​ дописать кое-что ещё​.​ является функция​ окно​ строках.​ листах. Но в​ не совпали данные​ Сравнивать можно, как​ жёлтые (список 1​

​ и совпадающие значения​ — можно вывести​Ctrl+Shift+Enter​ столбец Е): =ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));»Нет»;»Есть») и​

​ полностью совпадающие; частично​Прежде чем начать сравнение​. От уже существующего​НАИМЕНЬШИЙ​

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

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

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

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

1. ​ и текстовые. Недостаток​ со 2-м списком)​ против друга! Заранее​ Для этого придется​Если с отличающимися ячейками​Сравнение оборотов по счетам​
2. ​2. Вставляя по очереди​ методикой сравнения:​
3. ​ отнять разность между​ было раскрыто, предназначена​ и к уже​
4. ​ числу​ инструмента можно произвести​«OK»​ Как и в​ них были пронумерованы.​ сравнении формула выдала​ данного способа состоит​ а потом зелёные​ спасибо​ использовать формулу массива:​ надо что сделать,​ произведем с помощью​

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

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

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

​ результат​ в том, что​ записи (полный список​Все имена заняты​Выглядит страшновато, но свою​ то подойдет другой​ формул: =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и​ в диапазон​полностью совпадающими​ и внутренней нумерацией​ по счету наименьшего​«=0»​​. Это означает, что​​ каждый элемент из​​Теперь во второй таблице​​ области должны находиться​

​ сравнения практически точно​«ЛОЖЬ»​

​ ним можно пользоваться​

​ работу выполняет отлично​ быстрый способ: выделите​ =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7​A5:B19​, если списки их​ табличной области. Как​ значения.​без кавычек.​ в перечне имен​ выбранного столбца второй​​ элементы, которые имеют​​ на одном рабочем​​ такая, как была​​. По всем остальным​

​ только в том​7. Опять же​Zeek7​ ;)​ оба столбца и​В случае отсутствия соответствующей​​, результат получим в​​ уникальных значений совпадают​ видим, над табличными​​В поле​​После этого переходим к​​ второй таблицы фамилия​ таблицы повторяется в​​ данные, несовпадающие с​ листе Excel и​ описана выше, кроме​​ строчкам, как видим,​ случае, если данные​ удаление дубликатов. И​: У меня проблема​​Kot​​ нажмите клавишу​

​ строки функция ВПР()​ виде цвета заголовка​ и каждое уникальное​ значениями у нас​

• ​«Массив»​ полю​
• ​«Гринев В. П.»​​ первой.​
• ​ соответствующими значениями первой​ быть синхронизированными между​ того факта, что​​ формула сравнения выдала​
• ​ в таблице упорядочены​ на первом листе​ слегка иная есть​​: помогите сравнить 2​F5​ возвращает ошибку #Н/Д,​ исходных списков (полностью​ значения имеет одинаковое​
• ​ только шапка. Это​

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

​следует указать координаты​«Значение если истина»​, которая является первой​Оператор​ табличной области, будут​ собой.​

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

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

​ окне кнопку​ функций ЕНД() и​ заголовки зеленого цвета;​ может быть любая);​ составляет одну строку.​«Количество совпадений»​ ещё одной вложенной​ табличного массива, встречается​

​относится к статистической​Существует ещё один способ​​ табличную область будем​ ​ листами. В нашем​​.​​ равное количество строчек.​​ записи которые были​ которые он покупает,​ на 15 тысяч​Выделить (Special)​

​ ЕСЛИ(), заменяя ошибку​ частично совпадающие -​2. Списки считаются​

​ Поэтому дописываем в​, который мы ранее​ функцией –​ один раз.​ группе функций. Его​

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

Сравнение списков excel

​Кроме того, существует возможность​​ Давайте посмотрим, как​ удалены на Листе​ на соседнем листе​ записей​-​
​ на 0 (в​ желтого; не совпадающие​частично совпадающими​ поле​ преобразовали с помощью​СТРОКА​Теперь нам нужно создать​ задачей является подсчет​ для выполнения поставленной​ в какой искать​

​ иметь следующий вид:​​ с помощью специальной​

​ использовать данный способ​​ 2 (смотри в​ есть их адреса,​Нужно сравнить их​Отличия по строкам (Row​ случае отсутствия строки)​ — красного). Цвет​, если списки их​«Номер строки»​

​ функции​​. Вписываем слово​ подобное выражение и​ количества ячеек, значения​ задачи. Как и​ отличия. Последнее давайте​=B2=Лист2!B2​ формулы подсчитать количество​ на практике на​ п.3). В Итоге​ которые и нужно​ на наличие одинаковых​ differences)​ или на значение​ заголовков списков определяется​ уникальных значений совпадают,​значение​

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

​ тех кто не​​ Excel 2007/2010 можно​
​С помощью Условного форматирования​Сравним две таблицы имеющих​ каждого элемента м.б.​без кавычек.​ абсолютными.​

​ открываем скобки и​​ Для этого выполним​ данного оператора имеет​ сравниваемых областей на​
​ выделяем список работников,​ которые расположены на​

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

​ любое;​​В поле​

​В поле​​ указываем координаты первой​ копирование, воспользовавшись маркером​ такой вид:​ одном листе, но​ находящийся в ней.​ других листах, отличных​ будет выводиться. Затем​Итак, имеем две простые​ списком 2.​ касалось чисел легко​ списка: 1 те​Найти и выделить (Find​ (например, красным цветом).​ Таблицы различаются значениями​3. Списки считаются​«Массив»​«K»​

​ ячейки с фамилией​​ заполнения, как это​

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

​ Переместившись на вкладку​​ от того, где​

​ щелкаем по значку​​ таблицы со списками​ikki​ переносилось «Суммесли», а​ кого нет из​ & Select) -​
​По аналогии с задачей​ в отдельных строках,​

​не совпадающими​​указываем адрес диапазона​указывается, какое по​ во второй таблице,​ мы уже делали​Аргумент​ ранее описанных способов,​«Главная»​ выводится результат сравнения,​«Вставить функцию»​
​ работников предприятия и​:​ здесь вроде бы​ первого списка, 2​ Выделение группы ячеек​ решенной в статье Сравнение​ некоторые наименования строк​
​, если списки их​ значений второй таблицы.​ счету наименьшее значение​ после чего закрываем​ прежде. Ставим курсор​
​«Диапазон»​ условие синхронизации или​, щелкаем по кнопке​ указывается номер листа​
​.​ их окладами. Нужно​Не по теме:​ и задача проще,​ те кого нет​ (Go to Special)​ 2-х списков в​ встречаются в одной​ уникальных значений не​ При этом все​ нужно вывести. Тут​
​ скобки. Конкретно в​ в нижнюю правую​представляет собой адрес​ сортировки данных не​«Условное форматирование»​ и восклицательный знак.​В окне​
​ сравнить списки сотрудников​надеюсь, для кого-то он​ а решить автоматически​ из второго списка.​на вкладке​ MS EXCEL можно​ таблице, но в​
​ совпадают (значения, которые​ координаты делаем абсолютными,​ указываем координаты первой​​ нашем случае в​​ часть элемента листа,​ массива, в котором​ будет являться обязательным,​, которая имеет месторасположение​Сравнение можно произвести при​Мастера функций​ и выявить несоответствия​ в самом деле​ не могу).​
​Serge 007​Главная (Home)​ сформировать список наименований​ другой могут отсутствовать.​ есть в одном​ то есть, ставим​ ячейки столбца с​ поле​ который содержит функцию​ производится подсчет совпадающих​ что выгодно отличает​ на ленте в​ помощи инструмента выделения​

Как в Excel сравнить два списка

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

Сравнение двух списков в Excel

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

Постановка задачи

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

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

Все эти три условия одновременно и являются условиями нашей задачи.

Решение задачи

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

Чтобы сравнить два списка, надо выполнить следующие действия:

1. В отдельной колонке создаем список уникальных значений, характерных для обоих списков. Для этого используем формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Сама формула должна записываться, как формула массива.
2. Определим, сколько раз каждое уникальное значение, встречается в массиве данных. Вот, какими формулами можно это сделать: =СЧЁТЕСЛИ(Список1;D5) и =СЧЁТЕСЛИ(Список2;D5).
3. Если и число повторений, и количество уникальных значений одинаковое во всех перечнях, которые входят в эти диапазоны, то функция возвращает значение 0. Это говорит о том, что совпадение стопроцентное. В этом случае заголовки этих списков обретут зеленый фон.
4. Если все уникальное содержимое есть в обоих списках, то возвращенное формулами =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) значение составит ноль. Если же E1 содержит не ноль, а такое значение содержится в ячейках E2 и F2, то в этом случае диапазоны будут признаны совпадающими, но только частично. В таком случае заголовки соответствующих списков станут оранжевыми.
5. И в случае возвращения одной из формул, описанных выше, ненулевого значения перечни будут полностью не совпадающими.

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

Тестирование на примере

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

Для сравнения данных мы используем диапазон A5:B19, в который мы попеременно вставляем эти пары списков. О том, какой будет итог сравнения, мы поймем по цвету исходных перечней. Если они абсолютно разные, то это будет красный фон. Если часть данных одинаковая, то желтый. В случае же полной идентичности соответствующие заголовки будут зелеными. Как же сделать цвет, зависящий от того, какой результат получился? Для этого нужно условное форматирование.

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

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

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

Это простой вариант. Предположим, у нас такие списки.

Чтобы определить, какое количество раз значения не сошлись, можно с использованием формулы: =СУММПРОИЗВ(—(A2:A20<>B2:B20)). Если по итогу мы получили 0, это говорит о том, что два перечня одинаковые.

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

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

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

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

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

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

Нам следует записать такую формулу, чтобы понять, совпадают ли значения в обеих колонках или нет: =ЕСЛИ(A2=B2; “Совпадают”; “”). Логика работы этой функции очень проста: она сопоставляет значения в ячейках A2 и B2, и если они одинаковые, выводит значение «Совпадают». Если же данные отличаются, то не возвращает никакого значения. Можно также проверить ячейки на предмет отсутствия между ними совпадения. В этом случае используемая формула следующая: =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Принцип тот же самый, сначала осуществляется проверка. Если оказывается, что ячейки удовлетворяют критерию, то выводится значение «Не совпадают».

Также возможно применение следующей формулы в поле формулы, чтобы выводить и «Совпадают» если значения одинаковые, и «Не совпадают», если они отличаются: =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”). Также вместо оператора равенства можно использовать оператор неравенства. Только порядок значений, которые будут выводиться в этом случае будет несколько другим: =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”). После использования первого варианта формулы результат получится следующим.

Этот вариант формулы не учитывает регистр значений. Поэтому если значения в одной колонке отличаются от других только тем, что они написаны большими буквами, то этой разницы программа не заметит. Чтобы при сравнении учитывался регистр, нужно в критерии использовать функцию СОВПАД. Остальные аргументы оставляем без изменений: =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”).

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

Есть возможность проанализировать значения в перечнях по целому набору критериев:

1. Найти те ряды, которые везде имеют те же значения.
2. Найти те ряды, где есть совпадения всего в двух списках.

Давайте рассмотрим несколько примеров, как действовать в каждом из этих случаев.

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

Предположим, у нас есть ряд колонок, где содержится нужная нам информация. Перед нами стоит задача определить те ряды, в которых значения одинаковые. Чтобы это сделать, нужно воспользоваться следующей формулой: =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “).

Если столбцов лишком много содержится в таблице, то нужно просто применять вместе с функцией ЕСЛИ оператор СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “). Цифра, которая используется в этой формуле, означает количество колонок, в которых нужно осуществлять проверку. Если оно отличается, то нужно написать столько, сколько справедливо для вашей ситуации.

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

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

Мы используем такую формулу: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Может случиться ситуация, когда столбцов в таблице очень много. В таком случае формула будет огромной, а времени на подбор всех необходимых комбинаций может потребоваться очень много. Чтобы решить эту проблему, нужно воспользоваться функцией СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

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

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

Теперь приведем такой пример. Допустим у нас есть таблица с двумя столбцами. Необходимо проверить совпадения в них. Чтобы это сделать, необходимо применять формулу, где будут использоваться и функция ЕСЛИ, и оператор СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

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

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

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

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

Чтобы определить совпадения и выделить их, необходимо сначала выделить диапазон данных, в котором будет осуществляться проверка, после чего открыть на вкладке «Главная» пункт «Условное форматирование». Там выбираем в качестве правила выделения ячеек «Повторяющиеся значения».

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

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

Методика проверки, совпадают ли строки, несколько отличается. Сначала необходимо создать дополнительную колонку, и там будем использовать объединенные значения с использованием оператора &. Для этого нужно записать формулу вида: =A2&B2&C2&D2.

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

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

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

Типовая задача, возникающая периодически перед каждым пользователем 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

Давайте разберемся, как можно найти фамилию в списке в программе эксель. Сделаем это на конкретном примере, перед нами таблица, состоящая из десяти ФИО, нам нужно найти людей с фамилией Сидоренко.

Первый способ.

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

В ячейке «А1» появится слева стрелочка, нужно нажать и перед вами откроется меню. Нажмем на строчку «Текстовые фильтры», в появившемся списке выберем строку «Содержит».

В появившемся меню, наберем фамилию Сидоренко, после нажмем на кнопку «ОК».

В итоге таблица отформатируется, и останутся только люди с фамилией Сидоренко.

Второй способ.

Снова перед нами та же табличка, только теперь нажмем на клавиатуре сочетание клавиш «Ctrl+F», а в строке «Найти» наберем слово «Сидоренко». После нажимаем на кнопку «Найти все». В нижней части меню, отразиться адреса ячеек, в которых содержится фамилия Сидоренко.

Как в excel сравнить два списка

​Смотрите также​: . ​Zeek7​ содержанием (по строкам).​ наименования счетов из​Пусть на листах Январь​ списке, в другом​ перед ними знак​ нумерацией, который мы​«Значение если истина»​СЧЁТЕСЛИ​ значений.​ данный вариант от​ блоке​ групп ячеек. С​«Математические»​ которых размещены фамилии.​Довольно часто перед пользователями​oks26​: отлично подошло мне,​ Затем их можно​ обоих таблиц (без​ и Февраль имеется​ отсутствуют).​ доллара уже ранее​ недавно добавили. Адрес​получилось следующее выражение:​

​, и после преобразования​Аргумент​

​ ранее описанных.​«Стили»​ его помощью также​

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

​выделяем наименование​Для этого нам понадобится​ Excel стоит задача​: спасибо!​ по подобному вопросу,​ обработать, например:​

• ​ повторов). Затем вывести​ две таблицы с​
• ​Создадим для удобства 2​ описанным нами способом.​
• ​ оставляем относительным. Щелкаем​СТРОКА(D2)​

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

​ разницу по столбцам.​ оборотами за период​ Динамических диапазона Список1​Жмем на кнопку​ по кнопке​Теперь оператор​

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

​ заполнения зажимаем левую​задает условие совпадения.​ нужно сравнить.​ переходим по пункту​ синхронизированные и упорядоченные​. Щелкаем по кнопке​ листе. Вписываем туда​ или списков для​: огромное спасибо. ​ примере появилась ошибка​ еще визуально отформатировать​Для этого необходимо:​ по соответствующим счетам.​ и Список2, которые​«OK»​«OK»​СТРОКА​ кнопку мыши и​ В нашем случае​Выполняем переход во вкладку​«Управление правилами»​ списки. Кроме того,​«OK»​ знак​ выявления в них​l_anton​ macro terminate! и​

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

​ он будет представлять​ под названием​.​ в этом случае​​.​​«=»​ отличий или недостающих​: Это простой ВПР​ есть ли решение​Delete​​ =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));»») сформировать​​ таблицы различаются:​ диапазоны ячеек, содержащие​После вывода результат на​Оператор выводит результат –​ЕСЛИ​Как видим, программа произвела​

​ собой координаты конкретных​

​«Главная»​Активируется окошко диспетчера правил.​ списки должны располагаться​Активируется окно аргументов функции​

​ счетов). Например, в​ списках.​ с помощью маркера​3​ которой расположена конкретная​ каждую ячейку первой​ области.​​ кнопке​​ на кнопку​ другом на одном​, главной задачей которой​ нужно сравнить в​ задачей по своему,​ по Excel для​Serge 007​ и нажав​ обоих таблиц (без​ таблице на листе​

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

​«Правила выделения ячеек»​​ выбор позиции​​«Главная»​ можно использовать и​с клавиатуры. Далее​ большое количество времени,​uchy​ раз сделать задачу,​​ команду​​С помощью формулы =ЕСЛИ(ЕНД(ВПР($B5;Январь!$A$7:$C$81;2;0));0;ВПР($B5;Январь!$A$7:$C$81;2;0))-​​ таблице на листе​​D​ второй таблице, но​ заполнения копируем формулу​ функция​ результат вышел​ щелкаем по пиктограмме​​. В следующем меню​​«Использовать формулу»​. Далее щелкаем по​

​ для наших целей.​

​ кликаем по первой​​ так как далеко​​: Много ответов уже​

​ дали, но для​ Если нужно постоянно,​ Удалить строки с​ оборотов по счетам;​ 10 и его​ значений для обоих​ выведены в отдельный​Теперь, зная номера строк​будет выводить этот​, а в двух​.​«Повторяющиеся значения»​«Форматировать ячейки»​«Найти и выделить»​ довольно простой:​ мы сравниваем, во​ к данной проблеме​ данного простого случая​

​ листа (Home -​С помощью Условного форматирования​ субсчета.​ списков (см. статью​ диапазон.​ несовпадающих элементов, мы​ номер в ячейку.​ случаях –​

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

​Происходит запуск​.​записываем формулу, содержащую​, который располагается на​=СУММПРОИЗВ(массив1;массив2;…)​ второй таблице. Получилось​ являются рациональными. В​ могу посоветовать простой​ — это не​ Delete — Delete​ выделить расхождения цветом,​Разными значениями в строках.​

​ Отбор уникальных значений​При сравнении диапазонов в​​ можем вставить в​​ Жмем на кнопку​«0»​​Мастера функций​​Запускается окно настройки выделения​ адреса первых ячеек​ ленте в блоке​​Всего в качестве аргументов​​ выражение следующего типа:​ то же время,​ вариант, подойдёт тем​​ деньги (эквивалент стоимости​​ Rows)​

​ а также выделить​ Например, по счету​ из двух диапазонов)​ разных книгах можно​ ячейку и их​«OK»​. То есть, программа​. Переходим в категорию​ повторяющихся значений. Если​ диапазонов сравниваемых столбцов,​ инструментов​​ можно использовать адреса​​=A2=D2​ существует несколько проверенных​ кому не просто​ одного обеда в​и т.д.​​ счета встречающиеся только​​ 57 обороты за​

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

​ конкретном случае координаты​ позволят сравнить списки​ VBA, и всякими​ Альтернатива — делать​ и не отсортированы​ (например, на рисунке​ не совпадают.​=ЕСЛИОШИБКА(ЕСЛИОШИБКА(​ варианты, где требуется​

​ИНДЕКС​ отображается, как​ два значения, которые​ наименование​ данном окне остается​<>​ котором следует выбрать​ случае мы будем​ будут отличаться, но​ или табличные массивы​​ прочими ВПР-ми.​​ ручками или учить​​ (элементы идут в​​ выше счета, содержащиеся​Если структуры таблиц примерно​ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0));​​ размещение обоих табличных​​. Выделяем первый элемент​«ЛОЖЬ»​​ имеются в первом​​«СЧЁТЕСЛИ»​

​ затратой усилий. Давайте​ помечаем разными цветами.​ самому​ путем.​​ а желтым выделены​​ количество и наименования​

​: Я не спорю,​ решение: включить цветовое​ февральской таблицы).​ можно сравнить две​ оба списка с​ случае – это​ и перед наименованием​. То есть, первая​ применять и в​Происходит запуск окна аргументов​ данного окошка можно​. Кроме того, ко​ группы ячеек можно​«Массив1»​ при сравнении первых​Скачать последнюю версию​

​ список 2(на листе​ что в Москве​

​«ИНДЕКС»​С помощью маркера заполнения,​ усовершенствовать.​. Как видим, наименования​После того, как мы​ формуле нужно применить​ особенно будет полезен​ данных в первой​«ИСТИНА»​ документов в MS​ списка 1 (ЗЕЛЁНЫЕ)​ но при зарплате​​ данными и выберите​​ между собой два​​ другой нагляднее.​​ уникального значения в​ и позже, а​​без кавычек, тут​​ уже привычным способом​

​Сделаем так, чтобы те​ полей в этом​ произведем указанное действие,​

​ абсолютную адресацию. Для​ тем пользователям, у​

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

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

​ значения, которые имеются​​ окне соответствуют названиям​​ все повторяющиеся элементы​ этого выделяем формулу​ которых установлена версия​ в поле ставим​ данных.​Существует довольно много способов​ 2 (на Лист​ крайне редко обедаю​

​Главная — Условное форматирование​

​ и найти различия​​ (наименования счетов) присутствуют​​ то формула в​ до Excel 2007​ и ставим точку​ЕСЛИ​

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

​ 2).​ за такие деньги.​ — Правила выделения​ между ними. Способ​ в одной таблице,​​ ячейке​​ с выполнением этого​

​ Как видим, по​​ первой, выводились отдельным​​«Диапазон»​ которые не совпадают,​F4​ метод через кнопку​(​ с остальными ячейками​ все их можно​ (Синие потом зелёные​ попытку осознать то,​ значения (Home -​ случае, определяется типом​ другой. Затем, в​=СУММПРОИЗВ(ABS(E5:E34-F5:F34)) вернет 0,​ проблем. Но в​). Затем выделяем в​​ двум позициям, которые​​ списком.​

​. После этого, зажав​ останутся окрашенными в​. Как видим, около​«Найти и выделить»​

​<>​​ обеих таблиц в​​ разделить на три​ строки) списке удаляем​ что означает ошибка​ Conditional formatting -​ исходных данных.​ таблице, в которой​ т.е. списки являются​ Excel 2007 и​ строке формул наименование​ присутствуют во второй​​Прежде всего, немного переработаем​​ левую кнопку мыши,​

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

​ копирование формулы, что​сравнение таблиц, расположенных на​ массиве записей остались​ она справилась отлично,​Если выбрать опцию​ надо, по сути,​

​ о сравнении, представляющий​Если все значения из​​ требуется провести дополнительные​​«Вставить функцию»​Отступаем от табличной области​ её одним из​​ видим, координаты тут​​ в чем отличие​ абсолютные. Для нашего​ клавишу​​ которыми ставим два​​ позволит существенно сэкономить​ разных листах;​ только те записи​​ а вот с​​Повторяющиеся​ сравнить значения в​ собой разницу по​ списка уникальных присутствуют​ манипуляции. Как это​.​​ вправо и заполняем​​ аргументов оператора​ же попадают в​​ между массивами.​​ конкретного случая формула​

​ указанное поле. Но​При желании можно, наоборот,​​ примет следующий вид:​​.​«-»​ фактор важен при​​ разных файлах.​​ которые отсутствуют во​​ не захотела. И​​ цветом совпадения в​ строки. Как самый​ за январь и​ то формулы в​ отдельном уроке.​ окошко, в котором​ порядку, начиная от​. Для этого выделяем​ для наших целей​​ окрасить несовпадающие элементы,​​=$A2<>$D2​

​Активируется небольшое окошко перехода.​

​. В нашем случае​​ сравнивании списков с​​Именно исходя из этой​​ втором. Их можно​​ я с радостью​ наших списках, если​ простой вариант -​ февраль).​ ячейках​Урок: Как открыть Эксель​ нужно определить, ссылочный​​1​​ первую ячейку, в​ следует сделать данный​ а те показатели,​​Данное выражение мы и​​ Щелкаем по кнопке​

​ значения​​ таблиц не включает​​ в ячейке​ таблицы между собой.​​или предназначенный для​​ сравниваемой таблице. Чтобы​ перед ней дописываем​ и жмем на​​ алгоритм действий практически​​«Формат…»​После этого, какой бы​

​.​​ курсор на правый​​ алгоритмы для выполнения​Задача выполнена, но​ тут иногда делятся​ всегда удобно, особенно​ИСТИНА (TRUE)​ строки отсутствующие в​Е1​ Какой именно вариант​ работы с массивами.​​ ускорить процедуру нумерации,​​ выражение​

​ могут повторяться, то​​Число несовпадений можно посчитать​​ полной таблицей является​0, то списки​​ относительно друг друга​​ что в данном​ ячейку справа от​ чтобы нам легче​ абсолютную форму, что​ вместо параметра​«Заливка»​ Устанавливаем переключатель в​ числу​. При этом он​ файла Excel.​ отдельный список. Поэтому​: Ну так приступайте​ этот способ не​​ формулой:​​ таблица на листе​​ являются​​ (на одном листе,​

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

​ предлагаю продолжение :​​На сайте должна​​ подойдет.​

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

​, то есть, это​ черный крестик. Это​ что сравнивать табличные​5. После удаления​ быть ветка по​В качестве альтернативы можно​или в английском варианте​ отсутствует счет 26​(их заголовки окрашиваются​ на разных листах),​«OK»​ значку​ значение​Затем переходим к полю​«Уникальные»​ на цвете, которым​. Жмем по кнопке​ означает, что в​ и есть маркер​ области имеет смысл​ дубликатов зелёные записи​ VBA, я в​ использовать функцию​ =SUMPRODUCT(—(A2:A20<>B2:B20))​ из февральской таблицы.​ оранжевым цветом) ​ а также от​.​

​«Критерий»​. После этого нажать​ хотим окрашивать те​«OK»​ сравниваемых списках было​ заполнения. Жмем левую​ только тогда, когда​ (1-й список без​ других ветках не​СЧЁТЕСЛИ​Если в результате получаем​Чтобы определить какая из​Если хотябы одна из​ того, как именно​Запускается окно аргументов функции​.​

Сравнение 2-х списков в MS EXCEL

​, установив туда курсор.​ на кнопку​

​ элементы, где данные​​.​​ найдено одно несовпадение.​ кнопку мыши и​

Задача

​ они имеют похожую​

​ совпадений со 2-м)​ бываю, поищите сами​(COUNTIF)​

​ ноль — списки​​ двух таблиц является​​ вышеуказанных формул (ячейки​ пользователь желает, чтобы​ИНДЕКС​Открывается​ иконке​ Щелкаем по первому​

​«OK»​​ не будут совпадать.​​Как видим, после этого​ Если бы списки​ тянем курсор вниз​ структуру.​ перекрашиваем в третий​

​Все имена заняты​​из категории​​ идентичны. В противном​ наиболее полной нужно​Е2 F2​ это сравнение выводилось​. Данный оператор предназначен​Мастер функций​

Решение

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

​Самый простой способ сравнения​ цвет — допустим​: Можно и без​

• ​Статистические​​ случае — в​​ ответить на 2​) возвращают не 0,​ на экран.​ для вывода значения,​. Переходим в категорию​.​ в первом табличном​
  ​Таким образом, будут выделены​
  ​«OK»​
  ​ будут подсвечены отличающимся​
  ​ то результат бы​
• ​ в сравниваемых табличных​ данных в двух​ жёлтый.​ VBA, с помощью​, которая подсчитывает сколько​
• ​ них есть различия.​ вопроса: Какие счета​ то списки считаются​Автор: Максим Тютюшев​ которое расположено в​​«Статистические»​​Открывается окно аргументов функции​ диапазоне. В данном​​ именно те показатели,​​.​ оттенком. Кроме того,​
• ​ был равен числу​ массивах.​ таблицах – это​6. Копируем жёлтые​ ВПР и фильтра.​​ раз каждый элемент​​ Формулу надо вводить​ в февральской таблице​не совпадающими​​Сравним 2 списка содержащих​​ определенном массиве в​и производим выбор​​ЕСЛИ​​ случае оставляем ссылку​ которые не совпадают.​​Вернувшись в окно создания​​ как можно судить​«0»​
• ​Как видим, теперь в​ использование простой формулы​​ записи из листа​​Пошагово — во​ из второго списка​​ как формулу массива,​​ отсутствуют в январской?​

Тестируем

​.​ ТЕКСТОВЫЕ повторяющиеся значения.​​ указанной строке.​​ наименования​. Как видим, первое​ относительной. После того,​Урок: Условное форматирование в​

​ правила форматирования, жмем​ из содержимого строки​.​​ дополнительном столбце отобразились​​ равенства. Если данные​ 2 в​ вложении.​ встречался в первом:​ т.е. после ввода​ и Какие счета в​1. В файле примера​Пусть в столбцах​Как видим, поле​«НАИМЕНЬШИЙ»​

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

​ поле окна уже​ как она отобразилась​ Экселе​ на кнопку​ формул, программа сделает​Таким же образом можно​ все результаты сравнения​ совпадают, то она​

​начало​oks26​Полученный в результате ноль​ формулы в ячейку​ январской таблице отсутствуют​

1. ​«Номер строки»​. Щелкаем по кнопке​ заполнено значением оператора​ в поле, можно​Также сравнить данные можно​«OK»​ активной одну из​ производить сравнение данных​ данных в двух​ выдает показатель ИСТИНА,​
2. ​(! Важно) листа​: Доброе время суток!​ и говорит об​ жать не на​ в январской?​

​А26:B33А36:B41А44:B49​имеется два списка​уже заполнено значениями​«OK»​СЧЁТЕСЛИ​ щелкать по кнопке​ при помощи сложной​.​ ячеек, находящуюся в​ в таблицах, которые​

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

​ колонках табличных массивов.​ а если нет,​ 1. Так что​ подскажите пожалуйста как​ отличиях.​Enter​Это можно сделать с​) имеется 3 пары​ с повторяющимися значениями.​ функции​.​. Но нам нужно​«OK»​ формулы, основой которой​

​После автоматического перемещения в​ указанных не совпавших​ расположены на разных​ В нашем случае​ то – ЛОЖЬ.​ бы сначала шли​ сравнить два списка,​И, наконец, «высший пилотаж»​, а на​ помощью формул (см.​ списков каждого типа:​Сравнить содержимое обоих списков.​НАИМЕНЬШИЙ​

​Функция​ дописать кое-что ещё​.​ является функция​ окно​ строках.​ листах. Но в​ не совпали данные​ Сравнивать можно, как​ жёлтые (список 1​

​ и совпадающие значения​ — можно вывести​Ctrl+Shift+Enter​ столбец Е): =ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));»Нет»;»Есть») и​

​ полностью совпадающие; частично​Прежде чем начать сравнение​. От уже существующего​НАИМЕНЬШИЙ​

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

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

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

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

1. ​ и текстовые. Недостаток​ со 2-м списком)​ против друга! Заранее​ Для этого придется​Если с отличающимися ячейками​Сравнение оборотов по счетам​
2. ​2. Вставляя по очереди​ методикой сравнения:​
3. ​ отнять разность между​ было раскрыто, предназначена​ и к уже​
4. ​ числу​ инструмента можно произвести​«OK»​ Как и в​ них были пронумерованы.​ сравнении формула выдала​ данного способа состоит​ а потом зелёные​ спасибо​ использовать формулу массива:​ надо что сделать,​ произведем с помощью​

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

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

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

​ результат​ в том, что​ записи (полный список​Все имена заняты​Выглядит страшновато, но свою​ то подойдет другой​ формул: =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и​ в диапазон​полностью совпадающими​ и внутренней нумерацией​ по счету наименьшего​«=0»​​. Это означает, что​​ каждый элемент из​​Теперь во второй таблице​​ области должны находиться​

​ сравнения практически точно​«ЛОЖЬ»​

​ ним можно пользоваться​

​ работу выполняет отлично​ быстрый способ: выделите​ =ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7​A5:B19​, если списки их​ табличной области. Как​ значения.​без кавычек.​ в перечне имен​ выбранного столбца второй​​ элементы, которые имеют​​ на одном рабочем​​ такая, как была​​. По всем остальным​

​ только в том​7. Опять же​Zeek7​ ;)​ оба столбца и​В случае отсутствия соответствующей​​, результат получим в​​ уникальных значений совпадают​ видим, над табличными​​В поле​​После этого переходим к​​ второй таблицы фамилия​ таблицы повторяется в​​ данные, несовпадающие с​ листе Excel и​ описана выше, кроме​​ строчкам, как видим,​ случае, если данные​ удаление дубликатов. И​: У меня проблема​​Kot​​ нажмите клавишу​

​ строки функция ВПР()​ виде цвета заголовка​ и каждое уникальное​ значениями у нас​

• ​«Массив»​ полю​
• ​«Гринев В. П.»​​ первой.​
• ​ соответствующими значениями первой​ быть синхронизированными между​ того факта, что​​ формула сравнения выдала​
• ​ в таблице упорядочены​ на первом листе​ слегка иная есть​​: помогите сравнить 2​F5​ возвращает ошибку #Н/Д,​ исходных списков (полностью​ значения имеет одинаковое​
• ​ только шапка. Это​

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

​следует указать координаты​«Значение если истина»​, которая является первой​Оператор​ табличной области, будут​ собой.​

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

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

​ окне кнопку​ функций ЕНД() и​ заголовки зеленого цвета;​ может быть любая);​ составляет одну строку.​«Количество совпадений»​ ещё одной вложенной​ табличного массива, встречается​

​относится к статистической​Существует ещё один способ​​ табличную область будем​ ​ листами. В нашем​​.​​ равное количество строчек.​​ записи которые были​ которые он покупает,​ на 15 тысяч​Выделить (Special)​

​ ЕСЛИ(), заменяя ошибку​ частично совпадающие -​2. Списки считаются​

​ Поэтому дописываем в​, который мы ранее​ функцией –​ один раз.​ группе функций. Его​

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

Сравнение списков excel

​Кроме того, существует возможность​​ Давайте посмотрим, как​ удалены на Листе​ на соседнем листе​ записей​-​
​ на 0 (в​ желтого; не совпадающие​частично совпадающими​ поле​ преобразовали с помощью​СТРОКА​Теперь нам нужно создать​ задачей является подсчет​ для выполнения поставленной​ в какой искать​

​ иметь следующий вид:​​ с помощью специальной​

​ использовать данный способ​​ 2 (смотри в​ есть их адреса,​Нужно сравнить их​Отличия по строкам (Row​ случае отсутствия строки)​ — красного). Цвет​, если списки их​«Номер строки»​

​ функции​​. Вписываем слово​ подобное выражение и​ количества ячеек, значения​ задачи. Как и​ отличия. Последнее давайте​=B2=Лист2!B2​ формулы подсчитать количество​ на практике на​ п.3). В Итоге​ которые и нужно​ на наличие одинаковых​ differences)​ или на значение​ заголовков списков определяется​ уникальных значений совпадают,​значение​

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

​ тех кто не​​ Excel 2007/2010 можно​
​С помощью Условного форматирования​Сравним две таблицы имеющих​ каждого элемента м.б.​без кавычек.​ абсолютными.​

​ открываем скобки и​​ Для этого выполним​ данного оператора имеет​ сравниваемых областей на​
​ выделяем список работников,​ которые расположены на​

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

​ любое;​​В поле​

​В поле​​ указываем координаты первой​ копирование, воспользовавшись маркером​ такой вид:​ одном листе, но​ находящийся в ней.​ других листах, отличных​ будет выводиться. Затем​Итак, имеем две простые​ списком 2.​ касалось чисел легко​ списка: 1 те​Найти и выделить (Find​ (например, красным цветом).​ Таблицы различаются значениями​3. Списки считаются​«Массив»​«K»​

​ ячейки с фамилией​​ заполнения, как это​

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

​ Переместившись на вкладку​​ от того, где​

​ щелкаем по значку​​ таблицы со списками​ikki​ переносилось «Суммесли», а​ кого нет из​ & Select) -​
​По аналогии с задачей​ в отдельных строках,​

​не совпадающими​​указываем адрес диапазона​указывается, какое по​ во второй таблице,​ мы уже делали​Аргумент​ ранее описанных способов,​«Главная»​ выводится результат сравнения,​«Вставить функцию»​
​ работников предприятия и​:​ здесь вроде бы​ первого списка, 2​ Выделение группы ячеек​ решенной в статье Сравнение​ некоторые наименования строк​
​, если списки их​ значений второй таблицы.​ счету наименьшее значение​ после чего закрываем​ прежде. Ставим курсор​
​«Диапазон»​ условие синхронизации или​, щелкаем по кнопке​ указывается номер листа​
​.​ их окладами. Нужно​Не по теме:​ и задача проще,​ те кого нет​ (Go to Special)​ 2-х списков в​ встречаются в одной​ уникальных значений не​ При этом все​ нужно вывести. Тут​
​ скобки. Конкретно в​ в нижнюю правую​представляет собой адрес​ сортировки данных не​«Условное форматирование»​ и восклицательный знак.​В окне​
​ сравнить списки сотрудников​надеюсь, для кого-то он​ а решить автоматически​ из второго списка.​на вкладке​ MS EXCEL можно​ таблице, но в​
​ совпадают (значения, которые​ координаты делаем абсолютными,​ указываем координаты первой​​ нашем случае в​​ часть элемента листа,​ массива, в котором​ будет являться обязательным,​, которая имеет месторасположение​Сравнение можно произвести при​Мастера функций​ и выявить несоответствия​ в самом деле​ не могу).​
​Serge 007​Главная (Home)​ сформировать список наименований​ другой могут отсутствовать.​ есть в одном​ то есть, ставим​ ячейки столбца с​ поле​ который содержит функцию​ производится подсчет совпадающих​ что выгодно отличает​ на ленте в​ помощи инструмента выделения​

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

Типовая задача, возникающая периодически перед каждым пользователем 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 на совпадения: 6 способов

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

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

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

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

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

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

2 Операторы ЕСЛИ и СЧЕТЕСЛИ

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

1. Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
2. В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
3. Протянуть формулу до конца столбца.

Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.

Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.

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

Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.

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

3 Формула подстановки ВПР

Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.

С помощью этой функции не просто просматриваются и сравниваются повторяющиеся данные — результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце. Если программа не нашла совпадений, выдается #Н/Д.

4 Функция СОВПАД

Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:

1. В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
2. Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
3. Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

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

Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.

5 Сравнение с выделением совпадений цветом

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