Частичное совпадение текста в excel

Обновлено: 07.07.2024

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы "n" в слове "printer", можно использовать следующую функцию:

Эта функция возвращает 4, так как "н" является четвертым символом в слове "принтер".

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово "base" начинается с пятого символа слова "database". Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

Эти функции могут быть доступны не на всех языках.

Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

Искомый_текст Обязательный. Текст, который требуется найти.

Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.

Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Замечание

Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Если аргумент начальная_позиция опущен, то он полагается равным 1.

Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой "МДС0093.МужскаяОдежда". Чтобы найти первое вхождение "М" в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — "МДС0093"). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Есть две текстовые ячейки Экселя. В одной, скажем, "абырвалг", в другой -- "абырвалг; главсельхоз; спасиботов" или "главсельхоз; спасиботов" (или ещё что угодно, кроме абырвалга). Какой формулой посчитать, что во второй ячейке есть "абырвалг" или процент совпадения текстов на предмет элемента "абырвалг"?

Пробовал надстройку "Fuzzy Lookup", но при попытке включения графы similarity она либо уходит в бесконечность, либо Эксель зависает.

Анализировать десятки (сотни) записей руками -- глаза сломаешь, да и времени потребуется огромное количество.

Win 7/8/10, Office - настройка

125 постов 2.2K подписчиков

Правила сообщества

- В свободной форме комментировать и обсуждать посты и другие комментарии.

- Оскорблять кого-либо из пользователей пикабу.

- Размещать контент не по теме сообщества (последнее решение за модератором).

- Размещать рекламу (контент и/или ссылки на платные программы).

Бан можно получить:

- За регулярное нарушение правил

Будьте культурными няшами и все будет хорошо :)

народ совсем оленился, в гугле первый же ответ: Функция ПОИСК() в MS EXCEL

Дык сортировка по маске. А формула маски весьма гибкая.
А если и её недостаточно- визуалбейсик есть дефолтный.

СУММЕСЛИ или СУММЕСЛИМН практически одинаковые, разбив предварительно на слова по разным ячейка по ;

Образец - =ПСТР взять из текста нужную построку

Поиск- =НАЙТИ или ПОИСК - искать образцовую строку в тексте

Фильтр по маске

Из простого только fuzzy. Я обычно им свожу.

Выложите файл.аж посмотреть интересно.попробуем разобратьс.и посмотрите канал на ютубе и книги Николая Павлова.он очень круто рассказывает такие моменты

Наберите в интернете “формула XL правда = ложь , я так проверяла , выверку по ней обычно делают

Ответы.мэйл.ру на другом сайте.

Тормозит Excel? Файлы из 1С. Есть решение

Чтобы решить эту проблему миллионов пользователей Excel и 1С, решение которого нет даже в Майкрософт.

Надо просто отодвинуть скрытый список листов, чтобы они стали видны.

Как бы это странно не выглядело, это реально многим помогает.

Причина и пользовательское решение стало известно ещё 2017-07-26, но майкрософт ещё не исправили этот баг и его можно наблюдать даже в новых версиях MS Office в том числе 2019 и 365.

Исключение неверных текстовых значений в Excel

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

Перенос строк в Microsoft Excel

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

Рассмотрим каждый из вариантов переноса подробнее и разберём обратную операцию –удаление переносов.

Автоматический перенос текста

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

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

Если текст отображается не полностью, то задана точная высота строки (столбца) или текст находится в объединенных ячейках.

Ручной ввод разрыва строки

Новую строку текста можно начать в любом месте ячейки.

1. Дважды щелкните ячейку или выделите ячейку, а затем нажмите F2, в которую требуется ввести разрыв строки.

2. Дважды щелкните в ячейке на место, в которое нужно вставить разрыв строки, и нажмите сочетание клавиш Alt+Enter:

Автоматический перенос строк подходит далеко не всегда. Если нам нужны четкие отступы, используйте ручной перенос строк.

Удаление переносов строк

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

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

1. Выделите область в которой нужно удалить переносы.

2. Нажмите сочетание клавиш Ctrl+H, в открывшемся окне выделите курсором поле Найти и нажмите сочетание клавиш Ctrl+J.

Вроде ничего не произошло, но для Excel запрос для поиска понятен:

Нажмите Заменить. Готово!

Если в строке, до переноса, нет пробела замена его не добавит!

Не забудьте удалить невидимый символ в поле Найти, т.к. он остаётся в поле и может помешать в дальнейшем.

Решить задачу, можно встроенной функцией =ПЕЧСИМВ(), которая очищает текст от всех непечатаемых символов, включая переносы строк:

Если в строке, до переноса, нет пробела формула его не добавит!

Более продвинутый вариант, использование макросов:

Sub RemoveCarriageReturnsSelection() 'Удаление переноса каретки в выделенном диапазоне
Selection.Replace What:=Chr(10), Replacement:=" " ' заменяем перенос на пробел
Selection.Replace What:=Chr(160), Replacement:="" ' удаляем символ "похожий" на пробел
Selection.Replace What:=" ", Replacement:=" " ' удаляем двойной пробел
End Sub

Sub RemoveCarriageReturnsSheet() 'Удаление переноса каретки на листе
Cells.Replace What:=Chr(10), Replacement:=" " ' заменяем перенос на пробел
Cells.Replace What:=Chr(160), Replacement:="" ' удаляем символ "похожий" на пробел
Cells.Replace What:=" ", Replacement:=" " ' удаляем двойной пробел
End Sub

Спасибо, что дочитали до конца!

Было полезно? Ставьте лайк, пишите, что думаете в комментариях и подписывайтесь на канал, если еще не сделали этого.

Вывод списка файлов в папке на лист Excel

Перед вами стоит задача вывести списком содержимое папки? Этот пост для вас.

Вывести cписок файлов в папке можно макросом:

Sub FileListInFolder() 'Вывод содержимого папки на лист Excel
Dim V As String
Dim BrowseFolder As String
'открывает диалоговое окно выбора папки
With Application.FileDialog(msoFileDialogFolderPicker)
.title = "Выберите папку или диск"
.Show
On Error Resume Next
Err.Clear
V = .SelectedItems(1)
If Err.Number <> 0 Then
MsgBox "Вы ничего не выбрали!"
Exit Sub
End If
End With
BrowseFolder = CStr(V)
'добавляет лист и выводит на него шапку таблицы
ActiveWorkbook.Sheets.Add
With Range("A1:E1")
.Font.Bold = True
.Font.Size = 12
End With
Range("A1").Value = "Имя файла"
Range("B1").Value = "Путь"
Range("C1").Value = "Размер"
Range("D1").Value = "Дата создания"
Range("E1").Value = "Дата изменения"
'вызывает процедуру вывода списка файлов
'измените True на False, если не нужно выводить файлы из вложенных папок
ListFilesInFolder BrowseFolder, True
End Sub
Private Sub ListFilesInFolder(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
Dim FSO As Object
Dim SourceFolder As Object
Dim SubFolder As Object
Dim FileItem As Object
Dim r As Long
Dim x
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.getfolder(SourceFolderName)
r = Range("A65536").End(xlUp).Row + 1 'находит первую пустую строку
'выводит данные по файлу
For Each FileItem In SourceFolder.Files
Cells(r, 1).Formula = FileItem.Name
Cells(r, 2).Formula = FileItem.Path
Cells(r, 3).Formula = FileItem.Size
Cells(r, 4).Formula = FileItem.DateCreated
Cells(r, 5).Formula = FileItem.DateLastModified
r = r + 1
x = SourceFolder.Path
Next FileItem
'вызывает процедуру повторно для каждой вложенной папки
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
End If
Columns("A:E").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
End Sub

Копируйте код и сохраните его в Личной книге макросов.

Открыть редактор VBA Alt +F11, вызвать меню для выполнения или изменения макросов Alt + F8.

Word, excel - добавление часто используемых функций в панель быстрого доступа

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

Выглядит это вот так:

Чтобы это сделать, наведите курсор на нужную вам функцию и нажмите правую кнопку мыши. Во всплывающем окне выберите первую команду.

Также, в эту панель можно добавлять и макросы. Для этого нужно перейти в параметры Word (Excel, PowerPoint) и выбрать там пункт "Панель быстрого доступа", либо нажать крайнюю правую кнопку на панели и там выбрать пункт "Другие команды" :

Появится окно параметров. Тут нужно будет сделать последовательность действий:

1. выбрать из всплывающего списка пункт "Макросы"

2. выбрать макрос, который вы хотите добавить на панель быстрого доступа

3. нажать "Добавить"

Может быть, кому-то будет полезно)

Наша победа! Сообществу быть!

Сообщество будет посвящено MS Office, Libreoffice и Google docs.

Я хочу чтобы сообщество приносило пользу многим, дабы облегчить работу офисному брату))

Тех кто владеет Libreoffice и Google docs призываю вас быть активней, сообществу понадобятся модераторы, чтобы следить за порядком и публиковать полезные посты.

Лига Microsoft Office

Уважаемая администрация сайта и подписчики, я хочу создать Лигу Microsoft Office, в которой будут размещаться посты про Word, Excel, Access, Power Point, Outlook, Publisher и др., где пользователи смогут узнать что-то новое или поделиться своим опытом.

В настоящее время на сайте имеется много постов про Word и Excel, но они не систематизированы, однако тема интересна и заслуживает отдельного внимания. В этом сообществе я планирую публиковать обучающие посты про Excel. Кто-то также может выкладывать полезные статьи, которые пригодятся пользователям Пикабу.

Тем кому будет не интересна данная тематика смогут поставить сообщество в игнор.

Ссылки на мои посты:

Остальные опубликованы в сообществе "Лайфхак"
Ссылка на сообщество MS, Libreoffice & Google docs

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

Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.

1 Сравнение с помощью простого поиска

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

Перейти на главную вкладку табличного процессора.
В группе «Редактирование» выбрать пункт поиска.
Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.

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

2 Операторы ЕСЛИ и СЧЕТЕСЛИ

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

Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
Протянуть формулу до конца столбца.

Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.

Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.

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

Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.

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

3 Формула подстановки ВПР

Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.

4 Функция СОВПАД

Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:

В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

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

Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.

5 Сравнение с выделением совпадений цветом

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

Например, есть диапазон данных, а столбец E - это список имен учащихся, столбцы F и G - класс и оценка учащихся, а столбец H - рейтинг оценок учащихся, теперь у вас есть заданная имя «Джек», которое является частичной строкой «Джеки», как вы можете быстро найти частичное совпадение строки и вернуть рейтинг «Джеки», как показано ниже.

Частичное совпадение строк ВПР в Excel

Здесь у меня есть несколько формул, которые помогут вам найти частичное совпадение строк в Excel.

1. Выберите пустую ячейку, чтобы ввести частичную строку, которую вы хотите найти. Смотрите скриншот:

2. Выберите другую ячейку, в которую вы поместите искомое значение, и введите эту формулу. = ВПР ($ K $ 1 & "*", $ E $ 1: $ H $ 14,4, FALSE) , Нажмите Enter ключ, чтобы получить значение. Смотрите скриншот:

Наконечник:

1. В формуле K1 - это ячейка, содержащая частичную строку, E1: H14 - диапазон данных, 4 - значение подстановки в столбце Forth диапазона. Вы можете изменить их по своему усмотрению.

2. Если вы просто хотите найти, какое имя частично совпадает с заданным именем, вы также можете использовать эту формулу =INDEX($E$2:$E$14,MATCH($K$1&"*",E2:E14,0)) . (E2: E14 - это список столбцов, из которого вы хотите выполнить поиск, k1 - это заданное имя, вы можете изменить его по своему усмотрению.)

Читайте также: