Как посчитать одинаковые значения в excel
Как посчитать количество повторяющихся значений в Excel?
Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если вы хотите посчитать точное количество повторяющихся записей на листе Excel, используйте один из следующих способов для подсчета дубликатов.
Подсчет количества каждого из дубликатов.
Если у вас, к примеру, есть столбец с наименованиями товаров, вам часто может понадобиться узнать, сколько дубликатов имеется для каждого из них.
Мы отметили на рисунке первое, второе и третье появление Sprite.
Аналогичным образом вы можете посчитать количество повторяющихся строк. Единственное отличие состоит в том, что вам нужно будет использовать функцию СЧЁТЕСЛИМН() вместо СЧЁТЕСЛИ(). Например:
На скриншоте мы отметили одинаковые строки.
После подсчета повторяющихся значений вы можете скрыть уникальные и просматривать только одинаковые, или наоборот. Для этого примените автофильтр Excel.
Считаем общее количество дубликатов в столбце.
Пересчитываем метки, которые вы использовали для поиска дубликатов.
В этом примере наше выражение принимает следующую форму:
Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ввод.
Кроме того, имейте в виду, что она подсчитывает все повторяющиеся записи, включая первые вхождения:
Можно обойтись и без формулы маиисва:
Это работает и с текстом, и с числами, а пустые ячейки игнорируются, что также очень полезно.
Теперь давайте посчитаем количество дубликатов без учета их первого появления в таблице.
Здесь также есть два способа. Первый – с использованием вспомогательного столбца В.
В столбце B проставляем соответствующие отметки, как мы это уже не раз делали.
Далее определяем количество ячеек, содержимое которых встречается не в первый раз:
или формула массива
Ну а можно пойти от обратного. Считаем количество уникальных записей вот таким простым и элегантным способом:
Возможно,вам эта формула массива будет полезна при подсчете уникальных значений.
Ну а теперь школьная задачка: если у нас всего 16 слов (можно использовать функцию СЧЁТЗ), и из них 10 – уникальных, то сколько будет неуникальных? Правильно – 6!
Количество совпадений по части ячейки.
Предположим, у нас в ячейке записано не только название товара, но и другая дополнительная информация: товарная группа, номер счёта, единицы измерения и т.п. Как в этом случае подсчитать число упоминаний определённого товара?
Будем проверять часть содержимого, используя функцию СЧЕТЕСЛИ и знаки подстановки.
Ищем любое вхождение искомого слова при помощи знаков подстановки “*”.
Как посчитать количество дубликатов внутри ячейки.
Случается, что список находится вовсе не в таблице, а в одной ячейке ($A$2):
Рассмотренные нами выше приемы здесь точно не сработают. Но в Excel хватает других возможностей, и все можно сделать так же достаточно просто:
При помощи ДЛСТР считаем количество символов в ячейке со списком товаров ($A$2)
Затем при помощи ПОДСТАВИТЬ заменяем в указанном тексте заданное слово на пустое «». По умолчанию заменяем все повторы. А фактически – удаляем их.
Теперь наша задача – узнать, сколько слов мы удалили. При помощи ДЛСТР узнаем количество символов, оставшихся в списке после этой замены. Вычитаем из первоначального количества символов количество оставшихся, то есть узнаем, сколько символов было удалено. Делим результат на число букв в искомом слове.
Результат вы видите на скриншоте выше.
Подсчет дубликатов строк.
Чтобы найти общее количество повторяющихся строк, вставьте функцию СЧЁТЕСЛИМН() вместо СЧЕТЕСЛИ() и укажите все столбцы, которые вы хотите проверить на наличие совпадений. Например, чтобы подсчитать повторяющиеся строки на основе столбцов A и B, введите следующую формулу массива в свой лист Excel:
Как видите, выполнить подсчет повторяющихся значений в таблицах Excel можно множеством различных способов.
Как найти повторяющиеся значения в таблице Excel
Работая с большими наборами данных в Excel или объединяя несколько небольших электронных таблиц в более крупные, вы можете столкнуться с большим числом одинаковых строк.
И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в одном списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и ниже. Вот о чём мы поговорим:
Самой простой в использовании и вместе с тем эффективной в данном случае будет функция СЧЁТЕСЛИ (COUNTIF). С помощью одной только неё можно определить не только неуникальные позиции, но и их первые появления в столбце. Рассмотрим разницу на примерах.
Поиск повторяющихся значений включая первые вхождения.
Просто введите это выражение в любую ячейку и протяните вниз вдоль всей колонки, которую нужно проверить на дубликаты.
Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если имеются совпадения. А для встречающихся только 1 раз значений она показывает ЛОЖЬ.
Если же вам нужно, чтобы формула указывала только на дубли, замените «Уникальное» на пустоту («»):
В этом случае Эксель отметит только неуникальные записи, оставляя пустую ячейку напротив уникальных.
Поиск неуникальных значений без учета первых вхождений
Вы наверняка обратили внимание, что в примерах выше дубликатами обозначаются абсолютно все найденные совпадения. Но зачастую задача заключается в поиске только повторов, оставляя первые вхождения нетронутыми. То есть, когда что-то встречается в первый раз, оно однозначно еще не может быть дубликатом.
Если вам нужно указать только совпадения, давайте немного изменим:
На скриншоте ниже вы видите эту формулу в деле.
Нетрудно заметить, что она не обозначает первое появление слова, а начинает отсчет со второго.
Чувствительный к регистру поиск дубликатов
Как вы могли уже догадаться, выражения, использованные нами ранее, с такой задачей не справятся. Здесь нужно выполнить более тонкий поиск, с чем нам поможет следующая функция массива:
<=ЕСЛИ(СУММ((--СОВПАД($A$2:$A$17;A2)))
Смородина и арбуз, которые встречаются дважды, не отмечены в нашем поиске, так как регистр первых букв у них отличается.
Как извлечь дубликаты из диапазона.
Формулы, которые мы описывали выше, позволяют находить дубликаты в определенном столбце. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных.
Рассмотрим это на примере числовой матрицы. К сожалению, с символьными значениями этот метод не работает.
При помощи формулы массива
Затем протащите маркер заполнения вниз на сколько это необходимо.
Чтобы убрать сообщения об ошибке, когда дублирующиеся значения закончатся, можно использовать функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ( НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));»»);СТРОКА()-1));»»)
Также обратите внимание, что приведенное выше выражение рассчитано на то, что оно будет записано во второй строке. Соответственно выше него будет одна пустая строка.
Поэтому если вам нужно разместить его, к примеру, в ячейке K4, то выражение СТРОКА()-1 в конце замените на СТРОКА()-3.
Обнаружение повторяющихся строк
Мы рассмотрели, как обнаружить одинаковые данные в отдельных ячейках. А если нужно искать дубликаты-строки?
Есть один метод, которым можно воспользоваться, если вам нужно просто выделить одинаковые строки, но не удалять их.
Итак, имеются данные о товарах и заказчиках.
Создадим справа от наших данных формулу, объединяющую содержание всех расположенных слева от нее ячеек.
Предположим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:
Добавим следующую формулу в ячейку E2. Она отобразит, сколько раз встречается значение, полученное нами в столбце D:
Скопируем вниз для всех строк данных.
В столбце E отображается количество появлений этой строки в столбце D. Неповторяющимся строкам будет соответствовать значение 1. Повторам строкам соответствует значение больше 1, указывающее на то, сколько раз такая строка была найдена.
Если вас не интересует определенный столбец, просто не включайте его в выражение, находящееся в D. Например, если вам хочется обнаружить совпадающие строки, не учитывая при этом значение Заказчик, уберите из объединяющей формулы упоминание о ячейке С2.
Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel.
Теперь рассмотрим, как можно обойтись без формул при поиске дубликатов в таблице. Быть может, кому-то этот метод покажется более удобным, нежели написание выражений Excel.
Организовав свои данные в виде таблицы, вы можете применять к ним различные фильтры. Фильтр в таблице вы можете установить по одному либо по нескольким столбцам. Давайте рассмотрим на примере.
В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.
После этого в строке заголовка появляются значки фильтра. Если нажать один из них, откроется выпадающее меню фильтра, которое содержит всю информацию по данному столбцу. Выберите любой элемент из этого списка, и Excel отобразит данные в соответствии с этим выбором.
Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.
Но при этом вы видите дубли только по отфильтрованному. Если данных много, то искать таким способом последовательного перебора будет несколько утомительно. Ведь слишком много раз нужно будет устанавливать и менять фильтр.
Используем условное форматирование.
Выделение цветом по условию – весьма важный инструмент Excel, о котором достаточно подробно мы рассказывали.
Сейчас я покажу, как можно в Экселе найти дубли ячеек, просто их выделив цветом.
Как показано на рисунке ниже, выбираем Правила выделения ячеек – Повторяющиеся. Неуникальные данные будут подсвечены цветом.
Но здесь мы не можем исключить первые появления – подсвечивается всё.
Но эту проблему можно решить, использовав формулу условного форматирования.
Результат работы формулы выденения повторяющихся значений вы видите выше. Они выделены зелёным цветом.
Чтобы освежить память, можете руководствоваться нашим материалом «Как изменить цвет ячейки в зависимости от значения».
Поиск совпадений при помощи команды «Найти».
Еще один простой, но не слишком технологичный способ – использование встроенного поиска.
Зайдите на вкладку Главная и кликните «Найти и выделить». Откроется диалоговое окно, в котором можно ввести что угодно для поиска в таблице. Чтобы избежать опечаток, можете скопировать искомое прямо из списка данных.
Затем нажимаем «Найти все», и видим все найденные дубликаты и места их расположения, как на рисунке чуть ниже.
В случае, когда объём информации очень велик и требуется ускорить работу поиска, предварительно выделите столбец или диапазон, в котором нужно искать, и только после этого начинайте работу. Если этого не сделать, Excel будет искать по всем имеющимся данным, что, конечно, медленнее.
Этот метод еще более трудоемкий, нежели использование фильтра. Поэтому применяют его выборочно, только для отдельных значений.
Как применить сводную таблицу для поиска дубликатов.
Многие считают сводные таблицы слишком сложным инструментом, чтобы постоянно им пользоваться. На самом деле, не все так запутано, как кажется. Для новичков рекомендую к ознакомлению наше руководство по созданию и работе со сводными таблицами.
Для более опытных – сразу переходим к сути вопроса.
Создаем новый макет сводной таблицы. А затем в качестве строк и значений используем одно и то же поле. В нашем случае – «Товар». Поскольку название товара – это текст, то для подсчета таких значений Excel по умолчанию использует функцию СЧЕТ, то есть подсчитывает количество. А нам это и нужно. Если будет больше 1, значит, имеются дубликаты.
Вы наблюдаете на скриншоте выше, что несколько товаров дублируются. И что нам это дает? А далее мы просто можем щелкнуть мышкой на любой из цифр, и на новом листе Excel покажет нам, как получилась эта цифра.
К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:
Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.
Теперь, когда вы знаете, как использовать формулы для поиска повторяющихся значений в Excel, позвольте мне продемонстрировать вам еще один быстрый, эффективный и без всяких формул способ: инструмент Duplicate Remover для Excel.
Этот универсальный инструмент может искать повторяющиеся или уникальные значения в одном столбце или же сравнивать два столбца. Он может находить, выбирать и выделять повторяющиеся записи или целые повторяющиеся строки, удалять найденные дубли, копировать или перемещать их на другой лист. Я думаю, что пример практического использования может заменить очень много слов, так что давайте перейдем к нему.
Как найти повторяющиеся строки в Excel за 2 быстрых шага
Сначала посмотрим в работе наиболее простой инструмент — быстрый поиск дубликатов Quick Dedupe. Используем уже знакомую нам таблицу, в которой мы выше искали дубликаты при помощи формул:
Как видите, в таблице несколько столбцов. Чтобы найти повторяющиеся записи в этих трех столбцах, просто выполните следующие действия:
Помимо выделения цветом, вам доступен ряд других опций:
Нажмите кнопку ОК и подождите несколько секунд. Готово! И никаких формул 😊.
Как вы можете видеть на скриншоте ниже, все строки с одинаковыми значениями в первых 3 столбцах были обнаружены (первые вхождения не идентифицируются как дубликаты).
Если вам нужны дополнительные возможности для работы с дубликатами и уникальными значениями, используйте мастер удаления дубликатов Duplicate Remover, который может найти дубликаты с первыми вхождениями или без них, а также уникальные значения. Подробные инструкции приведены ниже.
В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:
Поскольку цель этого примера – продемонстрировать различные способы определения дубликатов в Excel, давайте отметим параметр «Выделить цветом» (Highlight with color) и нажмите Готово.
Мастеру Duplicate Remover требуется всего лишь несколько секунд, чтобы проверить вашу таблицу и показать результат:
Как видите, результат аналогичен предыдущему. Но здесь мы выделили дубликаты, включая и первое появление повторяющихся записей.
Итак, мы с вам научились различными способами обнаруживать повторяющиеся записи в таблице Excel. В следующих статьях разберем, что мы с этим можем полезного сделать.
Если вы хотите попробовать эти инструменты для поиска дубликатов в таблицах Excel, вы можете загрузить полнофункциональную ознакомительную версию программы. Будем очень признательны за ваши отзывы в комментариях!
Как в Excel найти повторяющиеся и одинаковые значения
При совместной работе с таблицами Excel или большом числе записей накапливаются дубли строк. Ста.
При совместной работе с таблицами Excel или большом числе записей накапливаются дубли строк. Статья посвящена тому, как выделить повторяющиеся значения в Excel, удалить лишние записи или сгруппировать, получив максимум информации.
Поиск одинаковых значений в Excel
Выберем одну из ячеек в таблице. Рассмотрим, как в Экселе найти повторяющиеся значения, равные содержимому ячейки, и выделить их цветом.
На рисунке – списки писателей. Алгоритм действий следующий:
В таблицах отмечены цветом ячейки, значение которых равно заданному.
Несложно понять, как в Экселе найти одинаковые значения в столбце. Просто выделить перед поиском нужную область – конкретный столбец.
Ищем в таблицах Excel все повторяющиеся значения
Отметим все неуникальные записи в выделенной области. Для этого нужно:
Программа ищет повторения во всех столбцах.
Если в таблице много неуникальных записей, то информативность такого поиска сомнительна.
Удаление одинаковых значений из таблицы Excel
Способ удаления неуникальных записей:
В результате получаем список, в котором каждое имя фигурирует только один раз.
Список с уникальными значениями:
Расширенный фильтр: оставляем только уникальные записи
Расширенный фильтр – это инструмент для получения упорядоченного списка с уникальными записями.
Поиск дублирующихся значений с помощью сводных таблиц
Составим список уникальных строк, не теряя данные из других столбцов и не меняя исходную таблицу. Для этого используем инструмент Сводная таблица:
Пункт «Сводная таблица».
В диалоговом окне выбрать размещение сводной таблицы на новом листе.
В открывшемся окне отмечаем столбец, в котором содержатся интересующие нас значений.
Получаем упорядоченный список уникальных строк.
Как посчитать одинаковые значения в excel
Предположим, что некоторые повторяющиеся значения заполняются в столбце вашего рабочего листа, теперь вам нужно подсчитать одинаковые или повторяющиеся значения только один раз, что означает подсчет только уникальных значений, как показано на следующем снимке экрана. Как бы вы могли решить эту задачу в Excel?
Подсчитывать одинаковые или повторяющиеся значения только один раз в столбце с формулами
Чтобы подсчитать одни и те же значения только один раз в столбце, примените следующую формулу к пустой ячейке, в которую вы хотите вывести результат:
А затем нажмите Enter ключ для получения результата, см. снимок экрана:
Чтобы подсчитать одни и те же значения только один раз с учетом регистра, примените следующую формулу:
А затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат, см. снимок экрана:
Подсчитывайте одинаковые или повторяющиеся значения только один раз в столбце с помощью простой функции
Работы С Нами Kutools for ExcelАвтора Подсчет ячеек с уникальными значениями (включая первый дубликат) Вы можете быстро подсчитать количество одинаковых значений только один раз в столбце, не запоминая никаких формул.
Примечание: Чтобы применить это Подсчет ячеек с уникальными значениями (включая первый дубликат), во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Щелкните ячейку, в которую вы хотите поместить результат.
2. Нажмите Kutools > Формула Помощник > Формула Помощник, см. снимок экрана:
3. В Помощник по формулам диалоговое окно:
4. Затем нажмите Ok Кнопка, повторяющиеся значения, которые появляются только один раз, были рассчитаны одновременно, см. снимок экрана:
Как сравнить два столбца на совпадения и различия
На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные совпадения либо уникальные значения. Ладно, обратный отсчет начался!
Мы все время от времени делаем сравнение данных в Excel. Microsoft Excel предлагает ряд опций для сравнения и сопоставления данных, но большинство из них ориентированы на поиск в одной колонке. Встроенный инструмент удаления дубликатов, доступный в Excel 2019-2010, не может справиться с этой задачей, поскольку он не умеет сравнивать данные между двумя столбиками. Кроме того, он может только удалять дубликаты. Других возможностей — таких как выделение или раскраска, увы, нет :-(.
В этом руководстве мы рассмотрим несколько методов сравнения двух столбцов в Excel и нахождения совпадений и различий между ними.
Как сравнить 2 столбца в Excel по строкам.
1. Проверяем совпадения или различия в одной строке.
Чтобы выполнить такое построчное сравнение, используйте популярную функцию ЕСЛИ, которая сравнивает первые две ячейки каждого из них. Введите её в какой-либо другой столбик той же строки, а затем скопируйте ее вниз, перетащив маркер заполнения (маленький квадрат в правом нижнем углу). При этом курсор изменится на знак плюса:
Чтобы найти в соответствующей строке позиции с одинаковым содержимым, A2 и B2 в этом примере, запишите:
Чтобы найти позиции в одной строке с разным содержимым, просто замените «=» знаком неравенства:
И, конечно же, ничто не мешает найти совпадения и различия с помощью одной формулы:
Результат может выглядеть примерно так:
Как видите, одинаково хорошо обрабатываются числа, даты, время и текст.
2. Сравниваем построчно с учетом регистра.
Как вы, наверное, заметили, формулы из предыдущего примера игнорируют регистр при сравнении текстовых значений, как в строке 10 на скриншоте выше. Если вы хотите найти совпадения с учетом регистра, используйте функцию СОВПАД (EXACT в английской версии):
Чтобы найти различия с учетом регистра в одной строке, введите соответствующий текст («Уникальный» например) в третий аргумент функции ЕСЛИ:
Сравните несколько столбцов построчно
Мы можем ставить перед собой следующие цели:
Пример 1. Найдите полное совпадение по одной строке.
Если в вашей таблице три или более колонки, и вы хотите найти строки с одинаковыми записями во всех из них, функция ЕСЛИ с оператором И подойдет для вас:
Если в вашей таблице очень много колонок, более элегантным решением будет использование функции СЧЁТЕСЛИ :
Или можно использовать —
Пример 2. Найдите хотя бы 2 совпадения в данных.
Если вы ищете способ сравнить данные на предмет наличия любых двух или более ячеек с одинаковыми значениями в одной строке, используйте функцию ЕСЛИ с оператором ИЛИ:
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0;»Все уникальные»;»Найдены одинаковые»)
Могу предложить также более компактный вариант выявления совпадений — формула массива:
Попробуйте — получите тот же результат. Также не забудьте нажать Ctrl + Shift + Enter, чтобы ввести всё правильно.
Как сравнить два столбца в Excel на совпадения и различия?
Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые записи), которые находятся в колонке A, но их нет в B. То есть, исходные данные из А мы сравниваем с В.
Для этого вы можете встроить функцию СЧЁТЕСЛИ($B:$B;$A2)=0 в логический тест ЕСЛИ и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (найдено хотя бы 1 совпадение).
Тот же результат может быть достигнут при использовании функции ЕСЛИ всесте с ЕОШИБКА и ПОИСКПОЗ:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ($A2;$B$2:$B$10;0));»Уникальное»; » Найдено в B»)
Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):
Если вы хотите, чтобы одно выражение определяло как дубликаты, так и уникальные значения, поместите текст совпадений в пустые двойные кавычки («») в любой из приведенных выше формул. Например:
Думаю, вы понимаете, что точно таким же образом можно наоборот сравнивать В с А.
Как сравнить два списка в Excel и извлечь совпадающие данные?
Кроме того, в отдельной статье мы подробно рассмотрели 4 способа, как сравнить таблицы при помощи формулы ВПР.
В качестве альтернативы вы можете использовать более мощную и универсальную комбинацию ИНДЕКС и ПОИСКПОЗ.
Например, следующее выражение сравнивает названия продуктов в колонках D и A, и если совпадение найдено, соответствующая цифра продаж извлекается из B. Если совпадения не найдено, возвращается ошибка #Н/Д.
Сообщение об ошибке в таблице выглядит не слишком красиво. Поэтому обработаем это выражение при помощи ЕОШИБКА:
Теперь мы видим либо число, либо пустое значение. Никаких ошибок.
Как выделить совпадения и различия в 2 столбцах.
Когда вы сравниваете наборы данных в Excel, вы можете захотеть «визуализировать» элементы, которые присутствуют в одном, но отсутствуют в другом. Вы можете закрасить такие позиции любым цветом по вашему выбору с помощью формул. И вот несколько примеров с подробными инструкциями.
1. Выделите совпадения и различия построчно.
Чтобы сравнить два столбца в Excel и выделить те позиции в первом, которые имеют идентичные записи во втором по той же строке, выполните следующие действия:
Чтобы выделить различия между колонками A и B, создайте правило с формулой =$B2<>$A2
Если вы новичок в условном форматировании Excel, смотрите пошаговые инструкции в статье Как закрасить строку или столбец по условию.
2. Выделите уникальные записи в каждом столбце.
Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:
О выделении дубликатов — смотрите пример выше. А сейчас рассмотрим, как выделить неповторяющиеся элементы в каждом из списков.
Выделите уникальные значения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)=0
Выделите уникальные значения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)=0
И получите следующий результат:
3. Выделите дубликаты в 2 столбцах.
Если вы внимательно следовали предыдущему примеру, у вас не возникнет трудностей с настройкой СЧЁТЕСЛИ, чтобы она находила совпадения, а не различия. Все, что вам нужно сделать, это установить счетчик больше нуля:
Вновь используем условное форматирование при помощи формулы.
Выделите совпадения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)>0
Выделите совпадения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)>0
Выделите цветом различия и совпадения в нескольких столбцах
При сравнении значений в нескольких наборах данных построчно, самый быстрый способ выделить одинаковые — создать правило условного форматирования. А самый быстрый способ скрыть различия — воспользоваться инструментом «Выделить группу ячеек», как показано в следующих примерах.
1. Как выделить совпадения.
Чтобы выделить строки, которые имеют одинаковые значения по всей длине, создайте правило условного форматирования на основе одного из следующих выражений:
Конечно, можно не ограничиваться сравнением только 3 колонок. Вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбиках.
И еще один способ выделения цветом повторяющихся значений в нескольких столбцах. Снова используем условное форматирование. Выделяем нужную область, затем на ленте в меню условного форматирования выбираем Правила выделения ячеек — Повторяющиеся значения. Определяем желаемое оформление, получаем картину подобную той, что вы видите ниже.
Кстати, на последнем этапе вы можете выбрать не повторяющиеся, а уникальные значения. Способ, конечно, незамысловатый, но, возможно, он вам будет полезен.
2. Как выделить различия.
Чтобы быстро выделить позиции с разными значениями в каждой отдельной строке, вы можете использовать функцию Excel «Выделить группу ячеек».
Чтобы изменить столбец сравнения, используйте клавишу TAB для перемещения по диапазону слева направо или клавишу Enter для перемещения сверху вниз. Если нужно перемещаться снизу вверх, то нажмите и удерживайте SHIFT, и вновь используйте ТАВ — будете двигаться не вниз, а вверх. Вы увидите, как ваше белое пятно перемещается, и соответственно изменяется активный столбец.
Примечание. Чтобы выбрать несмежные столбцы для сравнения, выберите первый диапазон, нажмите и удерживайте CTRL, а затем выбирайте дальше. Активная ячейка будет находиться в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу TAB или Enter, как описано выше.
Как сравнить два значения в отдельных столбцах.
Фактически, сравнение двух ячеек — частный случай сравнения двух колонок в Excel построчно, за исключением того, что вам не нужно копировать формулы.
Например, для сравнения ячеек A1 и C1 можно использовать:
Для совпадений: =ЕСЛИ(A1=C1; «Совпадает»; «»)
Для различий: =ЕСЛИ(A1<>C1; «Уникальные»; «»)
Для более эффективного анализа данных вам могут потребоваться более сложные формулы, и вы можете найти несколько хороших идей в следующих руководствах:
Быстрый способ сравнения двух столбцов или списков без формул.
Теперь, когда вы знаете, что предлагает Excel для сравнения и сопоставления столбцов, позвольте мне продемонстрировать вам альтернативное решение, которое может сравнить 2 списка с разным количеством столбцов на предмет дубликатов (совпадений) и уникальных значений (различий).
Надстройка Ultimate Suite умеет искать идентичные и уникальные записи в одной таблице, а также сравнивать две таблицы, находящиеся на одном листе или в двух разных листах или даже в разных книгах.
Для начала рассмотрим самый простой случай – сравним два столбца на совпадения и различия.
Предположим, у нас имеется два списка товаров. Нужно сравнить их между собой, как ранее мы делали при помощи формул.
Запускаем инструмент сравнения таблиц и выбираем первый столбец. При необходимости активируем создание резервной копии листа.
На втором шаге выбираем второй столбец для сравнения.
На третьем шаге нужно указать, что именно мы ищем – дубликаты либо уникальные значения.
Далее указываем столбцы для сравнения. Поскольку столбцов всего два, то здесь все достаточно просто:
На пятом шаге выберите, что нужно сделать с найденными значениями – удалить, выбрать, закрасить цветом, скопировать либо переместить. Можно добавить столбец статуса подобно тому, как мы это делали ранее при помощи функции ЕСЛИ. С использованием формул вы кроме того сможете разве что закрасить ячейки. Здесь же диапазон возможностей гораздо шире. Но мы выберем простой и наглядный вариант – заливку ячеек цветом.
Ячейки списка 1, дубликаты которых имеются в списке 2, будут закрашены цветом.
А теперь повторим все описанные выше шаги, только будем сравнивать список 2 с первым. И вот что мы в итоге получим:
Не закрашенные цветом ячейки содержат уникальные значения. Красиво и наглядно.
А теперь давайте попробуем сравнить сразу несколько столбцов. Допустим, у нас есть два экземпляра отчёта о продажах. Они расположены на разных листах нашей книги Excel. Список товаров совершенно одинаков, а вот сами цифры продаж отличаются кое-где.
Действуя совершенно аналогичным образом, как это было описано выше, выбираем эти две таблицы для сравнения. На третьем шаге выбираем поиск уникальных значений, чтобы можно было выбрать и выделить именно несовпадения в данных.
Устанавливаем соответствие столбцов, как это показано на рисунке ниже.
Для наглядности вновь выбираем заливку цветом для несовпадающих значений.
И вот результат. Несовпадающие строки закрашены цветом.
Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite for Excel.
Вот какими способами вы можете сравнить столбцы в Excel на наличие дубликатов и уникальных значений.
Если у вас есть вопросы или что-то осталось неясным, напишите мне комментарий, и я с радостью уточню это подробнее. Спасибо за чтение!