Как сравнить текстовые поля в excel

Корпорация "Центр"

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

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

Сравнение двух ячеек в табличном редакторе

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

Применение специальной формулы без учета регистра

Самый простейший вариант сравнения текста в 2-х ячейках без учета регистра – это применение символа «=» между координатами ячеек. Пример формулы: =А1=В1.

Важно! А1 и В1 являются элементами сравнения. В результате, в ячейке с формулой выведется либо значение ЛОЖЬ, либо значение ИСТИНА.

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

На нижеприведенной картинке прекрасно видно, как две рассмотренные формулы справляются с процедурой сравнения текстовой информации в ячейках.

kak-sravnit-tekst-v-yachejkah-tablicy-excel

1

Применение специальной формулы с учетом регистра

Часто при работе с документами случаются такие моменты, когда необходимо не просто произвести сравнение текстовой информации, но и учесть регистр. Для реализации этой несложной процедуры применятся специальный оператор «СОВПАД». В английской вариации он имеет наименование «EXACT». Общий вид формулы выглядит так: =СОВПАД(текст1; текст2). Аргументы текст1 и текст2 – это координаты ячеек, в которых будет производиться сравнение текстовой информации.

К примеру, данные для сравнения располагаются в полях, имеющих координаты А1 и В1. В этом случае, применяемая формула будет выглядеть так: =СОВПАД(А1; В1). Значение ИСТИНА будет отображено в том случае, если регистр каждого знака будет совпадать. В иных случаях отобразится значение ЛОЖЬ.

Внимание! Если пользователь желает в виде результата отобразить уникальные сообщения, говорящие о наличие соответствий в ячейках, то дополнительно в формулу необходимо добавить оператор «ЕСЛИ».

Простой пример такой формулы: =ЕСЛИ(СОВПАД(A1; B1); “Совпадает”; “НЕ совпадает”). На нижеприведенной картинке прекрасно видно, как две рассмотренные формулы справляются с процедурой сравнения текстовой информации в ячейках с учетом регистра.

kak-sravnit-tekst-v-yachejkah-tablicy-excel

2

Сравнение нескольких ячеек в табличном редакторе

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

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

Здесь, как и в ранее рассмотренных методах, применяется две вариации формулы:

  1. Первый вариант формулы выглядит так: =И(А1=В1; А1=С1).
  2. Второй вариант формулы выглядит так: =ЕСЛИ(И(А1=В1; А1=С1); “Совпадает”; “НЕ совпадает”).

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

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

kak-sravnit-tekst-v-yachejkah-tablicy-excel

3

Применение специальной формулы с учетом регистра для нескольких ячеек

Здесь, как и в ранее рассмотренных методах, применяется две вариации формулы:

  1. Первый вариант формулы выглядит так: =И(СОВПАД(А1;В1); СОВПАД(А1; С2)).
  2. Второй вариант формулы выглядит так: =ЕСЛИ(И(СОВПАД(А1;В1);СОВПАД(А1; С1));”Точное совпадение”; “НЕ совпадает”).

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

kak-sravnit-tekst-v-yachejkah-tablicy-excel

4

Если пользователю необходимо, чтобы формула вывела наличие соответствий хотя бы 2-х показателей, то ему необходимо использовать следующую формулу: =ЕСЛИ(ИЛИ(СОВПАД(А1;В1);СОВПАД(А1; С1);СОВПАД(В1;С1)); “Не менее двух точных соответствий”; “Соответствий нет”). Иными словами, в этом случае наличие хотя бы 2-х соответствий в 3-х случаях, выведет положительное значение. Результат преобразований выглядит так:

kak-sravnit-tekst-v-yachejkah-tablicy-excel

5

Если же пользователю необходимо произвести сравнение в табличке, в которой присутствует огромное количество ячеек, то вышеописанная формула будет не удобной в использовании, так как она станет слишком длинной. Здесь целесообразнее использовать математический оператор «СЧЕТЕСЛИ». Подсчитать количество соответствий в большом диапазоне ячеек можно при помощи следующей формулы: <=СЧЕТЕСЛИ(А2:D2;А2:D2)>. Результат преобразований выглядит так:

kak-sravnit-tekst-v-yachejkah-tablicy-excel

6

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

Заключение

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

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

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

  1. Как сравнить два столбца в Excel по строкам
  2. Пример 1. Как сравнить два столбца на совпадения и различия в одной строке
  3. Как сравнить несколько столбцов на совпадения в одной строке Excel
  4. Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы
  5. Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы
  6. Как сравнить два столбца в Excel на совпадения
  7. Как сравнить два столбца в Excel на совпадения и выделить цветом
  8. Поиск и выделение совпадений цветом в нескольких столбцах в Эксель
  9. Поиск и выделение цветом совпадающих строк в Excel

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

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

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

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

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

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

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

Корпорация "Центр"

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

Поиск различий в двух столбцах Excel

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

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

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

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

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

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

Поиск различий в двух столбцах Excel

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

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

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

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

Поиск различий в двух столбцах Excel

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

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

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

Поиск различий в двух столбцах Excel

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

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

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

Поиск различий в двух столбцах Excel

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

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

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

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

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

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

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

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

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

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

поиск и подсветка дублирующихся строк в Эксель - 1 поиск и подсветка дублирующихся строк в Эксель

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

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

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

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

вспомогательная колонка для поиска дублирующихся строк в Excel

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

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

поиск дубликатов строк в Эксель

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

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

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

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

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

вспомогательная колонка для поиска дублирующихся строк в Excel

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

создать правило условия форматирования в excel

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

новая формула в условном форматировании

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

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

дубликаты строк в excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Корпорация "Центр"

Ссылка на основную публикацию