Как сравнить текст в ячейках таблицы excel

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

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

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

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

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

Сравните два столбца и удалите совпадения с формулой

В Excel я ввожу формулы, которые помогут вам найти подходящие.

1. Выберите пустую ячейку рядом с первым столбцом, который вы хотите сравнить (Name1), здесь я выбираю B1 и набираю эту формулу. = IF (ISERROR (MATCH (A1; $ C $ 1: $ C $ 10000,0)), «Уникальный», «Повторяющийся»), и нажмите Enter и перетащите дескриптор автозаполнения вниз, чтобы заполнить эту формулу ячейками. Смотрите скриншот:

Примечание: в формуле A1 — это первая ячейка вашего первого столбца, который вам нужно сравнить, а C1: C10000 — это столбец, с которым вы хотите сравнить.

2. Затем выберите столбцы формулы и щелкните Данные > Фильтр. Смотрите скриншот:

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

4. Нажмите OK. Теперь видны только дубликаты, выберите их в столбце Name1 и нажмите Удалить нажмите на клавиатуре, чтобы удалить их.

5. Нажмите Данные > Фильтр еще раз, чтобы удалить фильтр, а затем удалите формулы, если они вам больше не нужны.

Теперь все совпадения двух сравниваемых столбцов удаляются.

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

С указанным выше методом это несколько хлопотно, и вы можете использовать Kutools for ExcelАвтора Сравнить диапазоны функция, чтобы быстро выбрать совпадения в двух столбцах и затем удалить их.

Kutools for Excel, с более чем 300 удобные функции, облегчающие вашу работу.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите два диапазона, которые вы хотите сравнить, удерживая Ctrl ключ, затем нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки. Смотрите скриншот:

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

3. Нажмите Ok, появится диалоговое окно с указанием количества выбранных ячеек.

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

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

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

Поиск повторяющихся значений в Excel

Microsoft Excel достаточно богат на функции анализа диапазонов данных, ранее мы рассматривали каким образом можно сравнить два файла Excel, как можно использовать условное форматирование для поиска уникальных записей в двух диапазонах данных, а также визуализацию статистический информации с добавлением функции ЗНАК (SIGN).

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

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

1. Выделить исходный диапазон (A1:E8)
2. Выполнить команду: вкладка Главная / группа Стили / Условное форматирование / Создать правило

1. В диалоговом окне выбрать: «Использовать формулу для определения форматируемых ячеек», при этом диалоговое окно немного изменит свой вид, далее следует ввести следующую формулу:
   =СЧЁТЕСЛИ($A$1:$E$8;A1)>1

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

1. После нажатия кнопки «ОК», можно сразу наблюдать результат выполнения операции.

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

1. Основная идея нахождения неуникальных, или, наоборот, уникальных строк, заключается в том, чтобы из всех строк диапазона сделать одну путем конкатенации (соединения), а потом искать неуникальные значений в новом диапазоне. Соединить строки можно, кстати, тоже не одним способом, например, отлично подойдет знак конкатенации «&», равно как и функция СПЕПИТЬ (CONCATENATE).

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

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

1. После нажатия «ОК», результат не заставит себя долго ждать.

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

• ТЕМЫ
• Excel
• Секреты Уокенбаха

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

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

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

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

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

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

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

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

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

Важно просто потренироваться перед тем, как использовать все эти знания на практике

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обратите внимание на две таблицы ниже:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сравнение двух версий книги с помощью средства сравнения электронных таблиц

​ ожидается аудиторская проверка.​ОК​(Запрос), чтобы добавить​ узел схемы, например​.​ внизу страницы. Для​ ‘данные другого столбца​: Вот тут не​ может посоветовать в​: Привел файлы к​ быстро. Если попробуете​ которые и требуется​ ‘ Эта строчка​ проблема в объеме​

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

1. ​ на страницу с​Подробнее о средстве сравнения​

2. ​ удобства также приводим​If .exists(arrB(i, 1))​ понял:​ плане решения данной​ одному виду, т.е.​ — расскажите :)​ обнаружить. Т.е. нужно​​ красит всю строку​​ файлов. Подскажите пожалуйста​

3. ​ подойдет.​​ идентичны. В противном​​ A2:A15 и выберите​​ данные в важных​​ Узнайте подробнее о​

4. ​ сохранены на компьютере.​​ именем «Запад», появляется​​ электронных таблиц и​ ссылку на оригинал​​ Then​​For i =​ задачи, так же​

   ​ колонка с требуемыми​Steel Rain​ найти все уникальные​ в зеленый цвет​ какой нибудь алгоритм,​В качестве альтернативы можно​ случае — в​ инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать​

5. ​ книгах, в которых​​ том, как действуют​​ Эти пароли шифруются​ выноска со сведениями.​​ сравнении файлов можно​​ (на английском языке).​.Item(arrB(i, 1)) =​

   ​ 1 To UBound(arrB)​ буду признателен.​ данными для отбора​

   ​: Объясните пожалуйста логику​​ значения в файле​.Pattern = xlSolid​ который не сутки​ использовать функцию​ них есть различия.​

6. ​ правило»- «Использовать формулу​​ показаны изменения по​​ пароли при использовании​

​ и доступны только​​Подробнее об этом можно​ узнать в статье​Предположим, что вы хотите​ .Item(arrB(i, 1)) +​If .exists(arrA(i, 1))​​Hugo​​ — столбец А,​ процесса, для чего​ 1, которых нет​End With​ будет работать.​СЧЁТЕСЛИ​

​ Формулу надо вводить​ для определения форматированных​ месяцам и по​ средства сравнения электронных​ вам.​ узнать в статье​ Сравнение двух версий​ Сравнение версий книги,​ 1​ Then​: Для строк полностью​ затем столбец Б​ выделять по две​ в файле 2​End If​P.S. поиском по​(COUNTIF)​

Интерпретация результатов

• ​ как формулу массива,​ ячеек:».​ годам. Это поможет​ таблиц.​Подробнее об использовании паролей​ Просмотр связей между​ книги.​ анализ книги для​p = p + 1​Что-то не то​ нужно писать другой​

• ​ пустой. Выделяю в​ ячейки? Мне нужно​ и, соответственно, наоборот.​​i = i + 1​​ форуму воспользовался как​из категории​

• ​ т.е. после ввода​В поле ввода введите​ вам найти и​Результаты сравнения отображаются в​ для анализа книг​ листами.​Команда​ проблемы или несоответствия​Else: .Add key:=arrB(i,​ :(​ код — этот​ первом файле первую​ сравнить в двух​ (если будет проще​Loop​ смог, опробовал то​

Другие способы работы с результатами сравнения

​Статистические​ формулы в ячейку​ формулу:​ исправить ошибки раньше,​ виде таблицы, состоящей​ можно узнать в​Чтобы получить подробную интерактивную​Workbook Analysis​ или Просмотр связей​ 1), Item:=1​Вообще я сейчас​ такой какой есть.​ ячейку из стобца​ файлах (ну или​ или быстрее работать,​

• ​Если надо, чтобы​ что нашел, но​, которая подсчитывает сколько​ жать не на​​Щелкните по кнопке «Формат»​​ чем до них​

• ​ из двух частей.​ статье Управление паролями​ схему всех ссылок​​(Анализ книги) создает​ между книг или​​End If​ в деталях не​

• ​Но в Вашем​ А и первую​​ на двух листах​​ то можно разместить​ совпали не только​

Другие причины для сравнения книг

• ​ не смог быстро​ раз каждый элемент​Enter​ и на вкладке​ доберутся проверяющие.​ Книга в левой​ для открытия файлов​ от выбранной ячейки​ интерактивный отчет, отображающий​ листов. Если на​Next i​ помню тот код,​

• ​ примере ведь нет​ из столбца Б.​ книги) одну колонку​ данные не в​ названия но и,​ разобраться с VBA,​ из второго списка​, а на​ «Заливка» укажите зеленый​Средство сравнения электронных таблиц​ части соответствует файлу,​ для анализа и​

support.office.com>

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

​ по заказу…​ нужны были даты,​ пригодиться.​Ссылка во втором аргументе​ для диапазона и​

​ таблицы Полугодие2. Далее​нажмите ОК.​

1. ​ в одной таблице​ полной таблицей является​Сравним две таблицы имеющих​ Аксессом ни разу​В данный момет​ не нашел по​ там все верно!​ Таблице : 8​: TimSha, так сильно​: TimSha, И без​
2. ​А если для​ в файле 5540​copper-top​ относительная, значит по​ нажмите Enter.​

​ двойное отрицание (—)​Теперь выделите на листе​ (например, на рисунке​ таблица на листе​ практически одинаковую структуру.​ не работал, буду​ не могу выложить​ этой формуле.​ Я просто хочу​ строк , но​

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

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

​Vlad999​ разобраться подробно ты​ мне нужно из​ ))))​ Сделал это оч​ использовать функцию =ВПР(),​ и данными ,​ два списка.​ все ячейки выделенного​ чтобы выполнить сравнение​ в массив чисел​А7:А16​ только в таблице​

​ отсутствует счет 26​ в отдельных строках,​Еще раз спасибо!​ Есть сейчас под​: У меня находит.​ там так много​ 2 Таблицы вытащить​Нарушение п. 5р​ просто и бесплатно)​ или же включить​

​ но появилась 2​kawaiinya​ диапазона (например, A2:A15).​ двух списков в​

​ {0;0;0;0;1;0}. Функция МАКС()​и создайте правило​ Январь, выделены синим,​ из февральской таблицы.​

​ некоторые наименования строк​alirko​ рукой только 2010.​Taxpolice​ сделал это ппц!​ всего лишь 2​ Правил форума​ УФ правила форматирования​ поиск по вашей​ такая же таблица​

​: Спасибо всем за​ Например, для сравнения​ Excel. Нам нужно​

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

​ вернет 1, т.е.​ Условного форматирования (меню​ а желтым выделены​Чтобы определить какая из​ встречаются в одной​: Воспользовался методом Матрёны.​ Если очень требуется​: А если поменять​ А если придет​ строки в 1​

​Pelena​

1. ​ задал только уникальные​ теме. И если​ но уже откорректированная​ ответы.​ двух прайсов в​ получить следующий результат:​
2. ​ выражение истинно и​ Главная/ Стили/ Условное​
3. ​ счета только из​ двух таблиц является​ таблице, но в​
4. ​ Для меня это​ 2003го версия, то​ местами столбцы в​ новая таблица измененная​ Таблицу чтоб в​:​ или повторяющиеся значения​ у вас excel​ где то чето​Пока ближе всего​ Excel даже на​​

excel2.ru>

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

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

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

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

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

Дайте названия столбцам, например, “Name” и “Duplicate?” Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

После этого нажмите меленькую серую стрелку рядом с “Duplicate?“, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.

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

Чтобы снова отобразить все строки столбца А, кликните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:

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

Если пометки “Duplicate” не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Format Cells (Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

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

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

Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

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

Если 2 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:

1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
2. Очистите фильтр.
3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
5. Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
6. Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:

Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.

Сравниваем две ячейки по вхождению определенного символа

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

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

Чтобы решить эту проблему, будем действовать следующим образом:

Во-первых, замените код заказа ничем с помощью функции ПОДСТАВИТЬ:

а также

В нашем примере код находится в A2, а значения находится в B2 и C2. 

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

Как вы можете видеть на скриншоте выше, всё работает отлично, несмотря на пару дополнительных сложностей:

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

Вот как можно при помощи формул можно сравнивать ячейки в Excel. Благодарю вас за чтение.

голоса

Рейтинг статьи

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

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

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

Поиск и выделение дубликатов цветом в Excel

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

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

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

Как это сделать:

Выделим область с данными, в которой нам нужно найти дубликаты:

На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”:

Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены дублирующие значения. Нажмите кнопку “ОК”:

После этого, в выделенной колонке, будут подсвечены цветом дубликаты:

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

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

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

Поиск и выделение цветом дубликатов строк в Excel

Поиск дубликатов повторяющихся ячеек и целых строк с данными это разные понятия

Обратите внимание на две таблицы ниже:

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

Рассмотрим как найти дубликаты строк:

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

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

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

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

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

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

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

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

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

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

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

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

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