Excel сравнить текст в ячейках
Как сравнить два столбца в Excel на совпадения
Пожалуй, каждый, кто работает с данными в Excel сталкивается с вопросом как сравнить два столбца в Excel на совпадения и различия. Существует несколько способов как это сделать. Давайте рассмотрим подробней каждый из них.
Как сравнить два столбца в Excel по строкам
Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции ЕСЛИ . Рассмотрим как это работает на примерах ниже.
Пример 1. Как сравнить два столбца на совпадения и различия в одной строке
Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу ЕСЛИ . Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.
Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:
=ЕСЛИ(A2=B2; “Совпадают”; “”)
Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:
=ЕСЛИ(A2<>B2; “Не совпадают”; “”)
Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:
=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)
=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)
Пример результата вычислений может выглядеть так:
Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:
=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)
Как сравнить несколько столбцов на совпадения в одной строке Excel
В Excel есть возможность сравнить данные в нескольких столбцах одной строки по следующим критериям:
- Найти строки с одинаковыми значениями во всех столбцах таблицы;
- Найти строки с одинаковыми значениями в любых двух столбцах таблицы;
Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы
Представим, что наша таблица состоит из нескольких столбцов с данными. Наша задача найти строки в которых значения совпадают во всех столбцах. В этом нам помогут функции Excel ЕСЛИ и И . Формула для определения совпадений будет следующей:
=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)
Если в нашей таблице очень много столбцов, то более просто будет использовать функцию СЧЁТЕСЛИ в сочетании с ЕСЛИ :
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)
В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.
Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы
Представим, что наша задача выявить из таблицы с данными в несколько столбцов те строки, в которых данные совпадают или повторяются как минимум в двух столбцах. В этом нам помогут функции ЕСЛИ и ИЛИ . Напишем формулу для таблицы, состоящей из трех столбцов с данными:
=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)
В тех случаях, когда в нашей таблице слишком много столбцов – наша формула с функцией ИЛИ будет очень большой, так как в ее параметрах нам нужно указать критерии совпадения между каждым столбцом таблицы. Более простой способ, в этом случае, использовать функцию СЧЁТЕСЛИ .
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)
Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2 , вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2 . Если результат вычисления равен “0” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.
Как сравнить два столбца в Excel на совпадения
Представим, что наша таблица состоит из двух столбцов с данными. Нам нужно определить повторяющиеся значения в первом и втором столбцах. Для решения задачи нам помогут функции ЕСЛИ и СЧЁТЕСЛИ .
=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)
Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.
Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.
Как сравнить два столбца в Excel на совпадения и выделить цветом
Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.
Поиск и выделение совпадений цветом в нескольких столбцах в Эксель
В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:
- Выделить столбцы с данными, в которых нужно вычислить совпадения;
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены цветом совпадения:
Поиск и выделение цветом совпадающих строк в Excel
Поиск совпадающих ячеек с данными в двух, нескольких столбцах и поиск совпадений целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:
В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.
Рассмотрим как найти совпадающие строки в таблице:
- Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:
=A2&B2&C2&D2
Во вспомогательной колонке вы увидите объединенные данные таблицы:
Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:
- Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены дублирующиеся строки:
На примере выше, мы выделили строки в созданной вспомогательной колонке.
Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?
Для этого сделаем следующее:
- Так же как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:
=A2&B2&C2&D2
Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:
- Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
- Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:
- В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:
=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1
- Не забудьте задать формат найденных дублированных строк.
Эта формула проверяет диапазон данных во вспомогательной колонке и при наличии повторяющихся строк выделяет их цветом в таблице:
Как сравнить текст в Excel
Когда вы используете табличный процессор Excel (от Microsoft Office), кроме задач по сравнению числовых значений, часто необходимо бывает сравнить текстовые (или «строковые») данные в ячейках таблицы. Сделать это можно так: в Excel есть встроенныефункции сравнения, и если результат операции необходимо получить в виде цифрового или же логического значения нужно воспользоваться этим сравнением. Так же можно использовать и опции условного форматирования, это делается в том случае, когда результатом должно стать визуальное выделение всех совпадающих (или же несовпадающих) ячеек данной таблицы.
Воспользуемся встроенной функцией для сравнения ячеек СЧЁТЕСЛИ, когда нужно сравнить текстовые показатели в ячейках колонки таблицы непосредственно с образцом текста и потом пересчитать все возможные совпадающие значения. Начинаем с заполнения конкретной колонки текстовыми значениями, потом делаем это в другой колонке и щелкаем ячейку, где вы хотите увидеть результат вашего подсчета, и там вводим соответствующую формулу. К примеру, если проверяемые показатели находятся в колонке типа «A», а результат нужно поместить в 1-ю ячейку колонки типа «C», то всё ее содержимое должно выглядеть так: =СЧЁТЕСЛИ($A:$A;»Виноград»).«Виноград» здесь, это строковое значение, непосредственно с которым и сравниваются показатели всех ячеек данной колонки «A». Можно так же не указывать это в формуле, но поместить в свободную отдельную ячейку (в «B1», например) и вставить в формулу нужную ссылку: =СЧЁТЕСЛИ($A:$A;B1).
Дальше мы будем использовать опции условного форматирования, особенно, если нужно визуально выделить в конкретной таблице результаты сравнения строковых переменных. Так, если необходимо выделить в данной колонке ячейки «A», текст совпадающий с образцом находящимся в ячейке «B1», старайтесь начинать с выделения данной колонки, а для этогощелкните по её заголовку. Потом щелкните по кнопке «Условное форматирование», находящееся в группе команд типа «Стили», закладке «Главная» в программе Excel. Переходим в раздел под названием «Правила выделения ячеек» с выбором строки «Равно». Там указываем ячейку-образец (щелкаем по клетке «B1») и выбираем в выпадающем списке вариант для оформления совпадающих строк. Нажимаем кнопку «OK».
Тут нужно использовать комбинацию встроенных функций типа «ЕСЛИ» и «СЦЕПИТЬ» ипри необходимости сравниватьс данным образцом не одну, а несколько текстовых ячеек. Функция «СЦЕПИТЬ» будет соединять указанные ей значения непосредственно в одну строковую переменную. К примеру, команда типа «СЦЕПИТЬ» (шаг «A1» или «B1») в строке из ячейки типа «A1» добавит нужный текст » и «, ну а после него он поместит строку из другой ячейки, а именно из „ B1“. Сделанную таким способом строку можно затем сравнивать с образцом при помощи функции „ЕСЛИ“. Если сравнивать необходимо не одну строку, то удобнее дать имя (своё собственно) ячейке-образцу. Что бы это сделать щелкните ее, а потом влево от строки формулы вместо обозначения ячейки („C1“ например) наберите новое ее имя („образец“, к примеру). Затем нужно кликнуть ту ячейку, в которой и должен быть результат вашего сравнения, и вводим формулу такого типа:
ЕСЛИ(СЦЕПИТЬ(A1;» и «;B1)=образец;1;0)
Тут, единица -значение, которое станет содержать ячейка с формулой, когда сравнение даст вам положительный результат, а цифра ноль — то же самое, но для отрицательного результата. Размножать эту формулу ко всем строкам таблицы, которые необходимо сравнить с данным образцом очень легко — наведите курсор к правому нижнему углу ячейки и, в тот момент, когдакурсор изменится (он станет черным крестиком), нужно нажать на левую кнопку мышки и растянуть эту ячейку далеко вниз до последней из сравниваемых строк.
Сравнение текста по части предложения
Довольно часто возникает проблема сравнения двух строк(ячеек) по части текста. Если точнее — по совпадению слов. Чем больше слов в двух строках совпадает — тем больше они считаются похожими. Так, к примеру текст «Защитная пленка iPhone» и текст «Защитная пленка для Samsung GalaxyII» совпадут только на 40%, а «шла маша по шоссе» и «маша по шоссе шла» — на 100%.
Я не имею ввиду сейчас случаи вроде двух строк: «пр и вет» и «пр е вет». Для подобного сравнения можно написать решения различные, но скорость их выполнения как правило оставляет желать лучшего, да и точность такого сравнения тоже не на высоте, если не использовать всевозможные справочники
На деле подобная задача встречается достаточно часто и предположу, что данная статья может быть полезна очень многим. Итак, как ни жаль, но подобную задачу невозможно решить без применения Visual Basic for Applications(VBA). Решение, которое я предложу — функция пользователя. Поэтому прежде чем его использовать настоятельно рекомендую прочесть следующие статьи:
Option Explicit Option Compare Text ‘————————————————————————————— ‘ Procedure : CompareTxt ‘ DateTime : 10.03.2015 22:46 ‘ Author : The_Prist(Щербаков Дмитрий) ‘ WebMoney — R298726502453; Яндекс.Деньги — 41001332272872 ‘ http://www.excel-vba.ru ‘ Purpose : Сравнивает две строки по совпадению отдельных слов. Выводит процент, саму строку и номер строки ‘ s1 — исходный текст(ссылка на ячейку или текст) ‘ mass — диапазон значений для сравнения с исходным текстом(ссылка на ячейку или текст) ‘ sDelim — разделитель слов в тексте. По умолчанию пробел ‘ lFstLast — указатель, выводить первое или последнее подходящее совпадение. ‘ По умолчанию 0(последнее максимально совпадающее). ‘ Если указать 1 — будет выбрано первое подходящее(в котором совпадают все слова) ‘ lShowAllInfo — указатель на результат. Допускается четыре значения: ‘ -1 — показывается вся информация: Процент совпадения строк, Найденное значение, ‘ Номер строки в указанном диапазоне в которой найдено значение ‘ 1 — Выводится только процент совпадения строк ‘ 2 — выводится только значение ‘ 3 — выводится только номер строки с найденным значением ‘ По умолчанию применяется -1(вся информация) ‘ Синтаксис: ‘ =CompareTxt(A1;B1:B100) — с разделителем по умолчанию ‘ =CompareTxt(A1;B1:B100;»-«) — с разделителем короткое тире(-) ‘ =CompareTxt(A1;B1:B100;»-«;;2) — с разделителем короткое тире(-) и выводом только значения ‘————————————————————————————— Function CompareTxt(s1 As String, mass As Range, Optional sDelim As String = » «, Optional lFstLast As Long = 0, Optional lShowAllInfo As Long = -1) Dim as1, as2, l1 As Long, l2 As Long, lr As Long Dim asStr2 Dim s As String, s2 As String, lp, lTmpCom As Long, lResCom As Long Dim lResR As Long, sResS As String, v as1 = Split(s1, sDelim) asStr2 = mass.Value If Not IsArray(asStr2) Then ReDim asStr2(1 To 1, 1 To 1): asStr2(1, 1) = mass.Value For lr = 1 To UBound(asStr2, 1) as2 = Split(asStr2(lr, 1), sDelim) lResCom = 0 For l1 = LBound(as1) To UBound(as1) s = as1(l1) For l2 = LBound(as2) To UBound(as2) If as2(l2) = s Then lResCom = lResCom + 1 Exit For End If Next l2 Next l1 If lTmpCom = (UBound(as1) + 1) Then Exit For End If End If Next lr v = (lTmpCom / (UBound(as1) + 1)) * 100 Select Case lShowAllInfo Case -1 CompareTxt = «Процент совпадения: » & v & «; Значение: » & sResS & «; Строка в массиве mass: » & lResR Case 1 ‘только процент CompareTxt = v Case 2 ‘только значение строки CompareTxt = sResS Case 3 ‘только номер строки CompareTxt = lResR End Select End Function
Данный код необходимо вставить в стандартный модуль книги(выше я привел ссылки на статьи, чтобы более точно понять куда и как вставить). Функция ищет указанное значение( s1 ) в массиве значений( mass ) и выводит максимально подходящее значение. Максимально подходящее, естественно, полное совпадение — то, которое совпадает на 100%. Если же полного совпадения среди значений массива( mass ) не будет найдено, то будет выведено значение с максимальным процентом совпадения. В таких случаях всегда можно указать последним аргументом( lShowAllInfo ) -1 или 3, чтобы посмотреть номер строки в указанном диапазоне( mass ) и сверить уже глазами подходит это значение или нет.
Синтаксис:
=CompareTxt(A1;B1:B100) — с разделителем по умолчанию
=CompareTxt(A1;B1:B100;»-«) — с разделителем короткое тире(-)
=CompareTxt(A1;B1:B100;»-«;;2) — с разделителем короткое тире(-) и выводом только значения
Аргументы:
s1 — исходный текст(ссылка на ячейку или текст)
mass — диапазон значений для сравнения с исходным текстом(ссылка на ячейку или текст)
sDelim — разделитель слов в тексте. По умолчанию пробел.
lFstLast — указатель, выводить первое или последнее подходящее совпадение. По умолчанию 0(последнее максимально совпадающее). Если указать 1 — будет выбрано первое подходящее(в котором совпадают все слова)
lShowAllInfo — указатель на результат. Допускается четыре значения:
- -1 — показывается вся информация: Процент совпадения строк, Найденное значение, номер строки в указанном диапазоне в которой найдено значение
- 1 — Выводится только процент совпадения строк
- 2 — выводится только значение
- 3 — выводится только номер строки с найденным значением. По умолчанию применяется -1(вся информация)
Ниже функция в файле с примерами использования:
Tips_Macro_ComparePart.xls (50,5 KiB, 2 136 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
Excel works!
Excel работает за вас
Excel works!
Thanks for Visiting
Сравнение текста в Excel и поиск символов
Чтобы сравнить две ячейки, в которых содержится текст необходимо просто приравнять их в соседней ячейки. Но что делать если, вам нужно сравнить текст с учетом больших и маленьких букв? А если с учетом английских букв? Появилась такая потребность как сравнение текста в Excel? Читайте далее и уверен статья вам поможет:
1) С простым сравнением разобрались? Отлично 🙂
2) Сравнение текста в Excel по регистрам
Сравнение текстов происходит простой формулой в примере номер 1, на картинке. Если необходимо сравнить прописные и заглавные буквы воспользуйтесь функцией =СОВПАД(). Пример 2.
3) Поиск чисел в ячейки с текстом
Самый простой способ найти числа в строке, воспользоваться формулой
Самая распространенная ошибка — вместо буквы О пишут число 0 и наоборот
Но обязательно воспользуйтесь формулой массива — нажмите ctrl+shift+enter вместо enter после ввода формулы.
4) Поиск определенных символов в тексте
В примере показывается, как можно найти все заглавные буквы русского алфавита. Но можно использовать эту группу функций для поиска любых символов.
Опять же не забываем про формулы массивов!
5) Удалить лишние пробелы в начале и конце строки
В версии excel 2007 появилась замечательная функция =СЖПРОБЕЛЫ() — она удаляет все лишние пробелы в начале и конце текста, а так же все задвоенные пробелы. Настоятельно рекомендую запомнить — использую ее чуть ли не каждый день.
6) Поиск символов
Для поиска символов можно использовать функцию =ПОИСК() она возвращает (считает) на какой позиции находиться заданный вами символ. Подробнее здесь .
7) Поиск символов с помощью условного форматирования или фильтра. Возможность «Содержит»
Можно окрасить все ячейки цветом, которые содержат заданные символы, или надписи в них. Для этого воспользуйтесь условным форматированием . Как показано на рисунке
Чтобы оставить только те ячейки, которые содержат нужные символы воспользуйтесь фильтрами. В фильтрах есть возможность отобрать строки по заданным условиям
Сравнение текста с шаблоном в Excel
Описание функции
Функция =ТЕКСТКАК(ТЕКСТ, ШАБЛОН) имеет два обязательных аргумента:
- ТЕКСТ — строка в которой необходимо произвести сравнение по заданному шаблону.
- ШАБЛОН, задающий сравнение текста. В шаблоне можно применять специальные обозначения:
- ? — Любой отдельный знак.
- * — Ноль или больше знаков.
- # — Любая отдельная цифра.
- [ ] — Любой отдельный знак из скобок.
- [! ] — Любой отдельный знак отсутствующий в скобках
Теперь к практике. Далее привожу множество примеров использования данной функции.
Как найти фамилии, заканчивающиеся на букву «а»?
Решением является формула =ТЕКСТКАК(ТЕКСТ;»*а»), где символ * указывает на неограниченное количество букв и символов перед последней буквой а.
Как привести телефонный справочник в порядок.
Используем формулу =ТЕКСТКАК(ТЕКСТ;»+# ### ###-##-##»), где знак # обозначает, что на этом месте может быть расположена любая цифра.
Найти слова которые содержат сочетание «сто» в любой части слова
Найти слова которые содержат сочетание «раст» или «рост» в любой части слова
Найти слова которые содержат любое сочетание кроме «раст»
Найти текст, который начинается с цифры
Проверка правильности введенного email. Сразу оговорюсь, проверка email в данном случае не полноценная, написана для в качестве примера, объясняющего принцип работы.