Поиск отличий в двух списках
Типовая задача, возникающая периодически перед каждым пользователем Excel - сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE):
Число несовпадений можно посчитать формулой:
=СУММПРОИЗВ(--(A2:A20<>B2:B20))
или в английском варианте =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) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:
Полученный в результате ноль и говорит об отличиях.
И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:
Выглядит страшновато, но свою работу выполняет отлично ;)
Ссылки по теме
- Выделение дубликатов в списке цветом
- Сравнение двух диапазонов с помощью надстройки PLEX
- Запрет ввода повторяющихся значений
Большое спасибо за способ!!!!
Ато целый день сидел никак не мог сделать.
Что только не устанавливал, оказывается всё так просто))).
У меня возник вопрос. Есть данные в трех столбцах. Необходимо их сравнить построчно и выделить ячейку с наименьшим значением цветом. При использовании формулы(=$A1=МИН($A1:$C1)) в условном форматировании для всего диапазона, выделяется вся строка. Данная формула работает, но только если выделять колонки отдельно и каждой прописывать это правило. Собственно вопрос в том, что можно ли описать данную операцию одним условием при выделении всего диапазона(A1:C10 - условно) сразу?
Заранее спасибо.
=СЧЁТЕСЛИ(B7:B97;B7=МИН($B7:$J7))
Считаю количество минимальных цен у нескольких поставщиков.
Спасибо.
Кликаю по неправильной ячейке "ИСТИНА" - значение меняется на верное - "ЛОЖЬ".
Как сделать так, чтобы сразу было видно где значение "ИСТИНА", а где "ЛОЖЬ"?
=СЧЁТЕСЛИ($E$1:$G$20;A1)=0
А если хочется, чтобы все само происходило и в отдельный список разница выгружалась, то посмотрите из моей .
Три столбца A,В и С. В столбце "А" список слов и выражений. В столбце "В" список слов и выражений больший, чем в столбце "А". Нужно в столбец "С" вписать формулу, которая будет сравнивать значени ячейки столбца "В" со всеми ячейками столбца "А". При совпадении значений в ячейках столбцов "А" и "В", в ячейке справа (столбец "С" появляется знак "*", при несовпадении ячейка справа остается пустой.
Еще один момент. Нужно чтобы формула работала даже при не точном совпадении текстов ячеек в столбцах "А" и "В", т.к. слова могут иметь разные окончания, разный регистр, может быть разный порядок слов.
С этой задачей отлично справляется формула вида:
=ЕСЛИ(ЕОШИБКА(ПОИСК(A$1;B17));"";"*") Но она сравнивает ячейки столбца "В" только с одной ячейкой столбца "А", а вот как сделать, чтобы она проверяла все ячейки столбца "А" я не знаю, но решение этого вопроса решило бы всю задачу.
Подскажите пожалуйста как можно это осуществить. Спасибо!
Можно это сделать с помощью формул? Спасибо!
(некропостинг, но вдруг кому поможет)
Сравниваю два списка,прайс,ассортимент с помощью варианта 2, выделил уникальные,редактировал нужный список,необходимо скопировать.но он вставляется в условном форматировании в цвете а как это все отменить чтобы нормально копировалось? спасибо!
Нельзя ли создать сводную табл. из такого большого кол. книг и потом отфильтровать дубликаты ?
Или есть другие способы сверивания данных ?
За каждый совет большое спасибо ! ЮРА.
Считаю ваш сайт очень полезным и информативным, он не раз помогал мне в решении трудных задач.
Но сейчас столкнулся с проблемой, решение которой не смог найти. Суть проблемы в том, что когда-то мной была создана таблица и к ней был применен расширенный фильтр как было описано вами в теме "Расширенный фильтр и немного магии", в результате поиска по этой таблице получается список "A". Вопрос, можно ли этот отфильтрованный список "A" сравнить с еще тремя списками "B", "C" и "D", чтобы в отдельной ячейке появилось название списка в котором максимальное количество совпадений со списком "A"?
Заранее спасибо!
Немного смущает по столбцу G. Если стоит 1 в столбце G, то первое уникальное слово из столбца С выводит, а дальге если поставить в столбец G цифру 2 выводит слово, которое есть в столбце А. Ничего не пойму. Спасибо!
Подскажите пожалуйста:
1. Существует 2 таблицы:
Таблица №1 имеет: 1 столбец с данными и 3 последующих столбца пустые
Таблица №2 имеет: 4 столбца с данными.
2. Стоит такая задача: Нужно сравнить столбец №1 обеих таблиц на наличие повторяющихся значений, если значения повторяются, то данные столбцов №2, 3, 4 таблицы №2, нужно записать в соответствующие пустые столбцы таблицы №1.
Заранее большое спасибо.
Имеются проблемы с использованием способа 5 (он же способ 6 в файле-примере), никак не могу разобраться сам. Надеюсь на помощь коллективного разума!
Диапазон сравнения список 1 - 192 наименования, список 2 - 191 наименование. При изменении в формуле массива диапазонов А2:А10 и С2:С10 (формула корректно выдает три наименования из списка 2, отличающихся от списка 1) на А2:А193 и С2:С192 (формула выдает только 1 наименование)... и при изменения диапазона пропадают {} скобки, при попытке добавить скобки {} вновь - отображается только сама формула...
при этом кнопка "Показать формулы" не нажата...
MS стандартный 2010
ссылочка на excel
Имеется список с полными адресными данными (исходный адрес-столбец А)в одном столбце и разбитыми на составляющие (Город. Улица)- в следующих столбцах. Надо выделить цветом совпадения- хотелось бы при самом красивом раскладе выделить цветом ячейку с совпадающим Городом ( столбец С) и изменить цвет шрифта при совпадении Улицы( столбец В). Например в строке 7 не прописан Зеленоград-значит она не будет выделена, в строке 19 не прописана улица Авиаторная- так же выделение. Требуется найти максимальные совпадения столбцов В И С со столбцом А
Файл прилагаю
У меня есть такая таблица
Столбцы содержат наименования и соответствующие им значения. Я отметил цветами совпадающие имена в столбцах А и D
Какую использовать формулу, чтобы она сама искала совпадающие имена в столбцах А и D и при нахождении подсчитывала разницы между значениями, соответствующими этим столбцам, например как у меня сделано вручную в столбце - G "В ячейке А2 совпадение с ячейкой D3 и найдена разница между значениями эти ячеек и вписана в G2"
=ЕСЛИОШИБКА(ВПР(A1;$D$1:$E$10;2)-B1;"")
Подскажите, пожалуйста, в Вашей формуле:
=СУММПРОИЗВ(--(A2:A20<>B2:B20))
какую роль играют, для чего нужны и что обозначают символы --
вместо G2 - СТРОКА(A1)
{=ЕСЛИОШИБКА(ИНДЕКС($C$1:$C$21;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$1:$A$21;$C$1:$C$21)<>0;
СТРОКА($C$1:$C$21));СТРОКА(A1)));"")}
формула массива сравнивает с первой по 21 строки и выводит одинаковые
Пожалуйста, помогите решить задачу:
дан всего один столбец из 1000 строк с буквами : A, B, C, D, E, F, G, H в хаотичном порядке
как рассчитать количество возникновения таких ситуация, где В следует за А, Е следует за А, С следует за А и так каждую Уникальную букву.
есть вариант немного проще, столбец из множества строк с числами от 0 до 3. также, как рассчитать количество возникновения таких ситуаций, где 1 следуют за 0, 1 следуют за 2, 1 следуют за 3 и т. д.
Очень прошу, помогите, кто знает какое-либо решение!
Примитив условия работает =И(A2=C2;F2=$Z$2), надо перевести в универсальную формулу с учетом одновременного совпадения ячейки с ячейкой из первого диапазона и в строке совпадения ячейки из другого диапазона с конкретной ячейкой или значением (Например "ТО";)
Очень нужно - помогите товарищи форумчане!
подскажите, пожалуйста, решение.
Вариант 1 не работает уже на трех списках (=A1=B1=C1). А если списков двадцать?