Сравнение текста в excel

Как сравнить текст в 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)

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

Сравнение текста с шаблоном в Excel

Описание функции

Функция =ТЕКСТКАК(ТЕКСТ, ШАБЛОН) имеет два обязательных аргумента:

  • ТЕКСТ — строка в которой необходимо произвести сравнение по заданному шаблону.
  • ШАБЛОН, задающий сравнение текста. В шаблоне можно применять специальные обозначения:
  1. ? — Любой отдельный знак.
  2. * — Ноль или больше знаков.
  3. # — Любая отдельная цифра.
  4. [ ] — Любой отдельный знак из скобок.
  5. [! ] — Любой отдельный знак отсутствующий в скобках
Читать еще:  Как в excel перенести текст в одной ячейке на следующую строку

Теперь к практике. Далее привожу множество примеров использования данной функции.

Как найти фамилии, заканчивающиеся на букву «а»?

Решением является формула =ТЕКСТКАК(ТЕКСТ;»*а»), где символ * указывает на неограниченное количество букв и символов перед последней буквой а.

Как привести телефонный справочник в порядок.

Используем формулу =ТЕКСТКАК(ТЕКСТ;»+# ### ###-##-##»), где знак # обозначает, что на этом месте может быть расположена любая цифра.

Найти слова которые содержат сочетание «сто» в любой части слова

Найти слова которые содержат сочетание «раст» или «рост» в любой части слова

Найти слова которые содержат любое сочетание кроме «раст»

Найти текст, который начинается с цифры

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

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 без ВПР

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

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

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

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

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

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

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

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

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

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

Выборка значений из таблицы по условию в Excel без ВПР

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

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

Поскольку товар имеет фиксированную стоимость, для определения самого продаваемого смартфона можно использовать встроенную функцию МОДА. Чтобы найти наименование наиболее продаваемого товара используем следующую запись:

Функция мода определяет наиболее часто повторяющиеся числовые данные в диапазоне цен. Функция ПОИСКПОЗ находит позицию первой ячейки из диапазона, в которой содержится цена самого популярного товара. Полученное значение выступает в качестве первого аргумента функции адрес, возвращающей ссылку на искомую ячейку (к значению прибавлено число 2, поскольку отсчет начинается с третьей строки сверху). Функция ДВССЫЛ возвращает значение, хранящееся в ячейке по ее адресу.

В результате расчетов получим:

Для определения общей прибыли от продаж iPhone 5s используем следующую запись:

Функция СУММПРИЗВ используется для расчета произведений каждого из элементов массивов, переданных в качестве первого и второго аргументов соответственно. Каждый раз, когда функция СОВПАД находит точное совпадение, значение ИСТИНА будет прямо преобразовано в число 1 (благодаря двойному отрицанию «—») с последующим умножением на значение из смежного столбца (стоимость).

Результат расчетов формулы:

Всего было куплено 4 модели iPhone 5s по цене 239 у.е., что в целом составило 956 у.е.

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

Функция СОВПАД имеет следующий вариант синтаксической записи:

  • текст1 – обязательный для заполнения, принимает ссылку на ячейку с текстом или текстовую строку для сравнения с данными, принимаемые вторым аргументом.
  • текст2 – обязательный для заполнения, принимает ссылку на ячейку или текст, с которым сравниваются данные, переданные в виде первого аргумента.
  1. Результат выполнения функции СОВПАД, принимающей на вход два имени, является код ошибки #ИМЯ? (например, СОВПАД(имя;имя)). Для корректной работы функции указываемые текстовые данные необходимо помещать в кавычки (например, («имя»;«имя»)).
  2. Функция выполняет промежуточное преобразование числовых данных в текст. Например, результат выполнения =СОВПАД(111;111) будет логическое значение ИСТИНА. Однако, преобразование логических данных в числа текстового формата не выполняется. Например, результат выполнения =СОВПАД(ИСТИНА;1) будет логическое ЛОЖЬ.
  3. Результат сравнения двух пустых ячеек или пустых текстовых строк с использованием функции СОВПАД — логическое ИСТИНА.

Сравнение текста по части предложения

Довольно часто возникает проблема сравнения двух строк(ячеек) по части текста. Если точнее — по совпадению слов. Чем больше слов в двух строках совпадает — тем больше они считаются похожими. Так, к примеру текст «Защитная пленка 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 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector