Поиск части текста в ячейке excel
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функций НАЙТИ и НАЙТИБ в Microsoft Excel.
Описание
Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую и возвращают начальную позицию искомой строки относительно первого знака второй строки.
Эти функции могут быть доступны не на всех языках.
Функция НАЙТИ предназначена для языков с однобайтовой кодировкой, а функция НАЙТИБ — для языков с двухбайтовой кодировкой. Заданный на компьютере язык по умолчанию влияет на возвращаемое значение указанным ниже образом.
Функция НАЙТИ при подсчете всегда рассматривает каждый знак, как однобайтовый, так и двухбайтовый, как один знак, независимо от выбранного по умолчанию языка.
Функция НАЙТИБ при подсчете рассматривает каждый двухбайтовый знак как два знака, если включена поддержка языка с БДЦС и такой язык установлен по умолчанию. В противном случае функция НАЙТИБ рассматривает каждый знак как один знак.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций НАЙТИ и НАЙТИБ описаны ниже.
Искомый_текст — обязательный аргумент. Текст, который необходимо найти.
Просматриваемый_текст — обязательный аргумент. Текст, в котором нужно найти искомый текст.
Начальная_позиция — необязательный аргумент. Знак, с которого нужно начать поиск. Первый знак в тексте "просматриваемый_текст" имеет номер 1. Если номер опущен, он полагается равным 1.
Замечания
Функции НАЙТИ и НАЙТИБ работают с учетом регистра и не позволяют использовать подстановочные знаки. Если необходимо выполнить поиск без учета регистра или использовать подстановочные знаки, воспользуйтесь функцией ПОИСК или ПОИСКБ.
Если в качестве аргумента "искомый_текст" задана пустая строка (""), функция НАЙТИ выводит значение, равное первому знаку в строке поиска (знак с номером, соответствующим аргументу "нач_позиция" или 1).
Искомый_текст не может содержать подстановочные знаки.
Аргумент "нач_позиция" можно использовать, чтобы пропустить нужное количество знаков. Предположим, например, что для поиска строки "МДС0093.МесячныеПродажи" используется функция НАЙТИ. Чтобы найти номер первого вхождения "М" в описательную часть текстовой строки, задайте значение аргумента "нач_позиция" равным 8, чтобы поиск в той части текста, которая является серийным номером, не производился. Функция НАЙТИ начинает со знака 8, находит искомый_текст в следующем знаке и возвращает число 9. Функция НАЙТИ всегда возвращает номер знака, считая от левого края текста "просматриваемый_текст", а не от значения аргумента "нач_позиция".
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.
Описание
Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы "n" в слове "printer", можно использовать следующую функцию:
Эта функция возвращает 4, так как "н" является четвертым символом в слове "принтер".
Можно также находить слова в других словах. Например, функция
возвращает 5, так как слово "base" начинается с пятого символа слова "database". Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.
Эти функции могут быть доступны не на всех языках.
Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ПОИСК и ПОИСКБ описаны ниже.
Искомый_текст Обязательный. Текст, который требуется найти.
Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.
Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Замечание
Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (~).
Если аргумент начальная_позиция опущен, то он полагается равным 1.
Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой "МДС0093.МужскаяОдежда". Чтобы найти первое вхождение "М" в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — "МДС0093"). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Описание
В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание образца листа
В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
Определение
Вся таблица подстановки
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».
Просматриваемый_массив
-или-
Лукуп_вектор
Диапазон ячеек, которые содержат возможные значения подстановки.
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.
3 (третий столбец в инфо_таблица)
Ресулт_аррай
-или-
Ресулт_вектор
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение "5" в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе "образец".
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как "Мария" находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе "образец".
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется "3" в качестве Колумн_индекс (столбец C). Так как "Мария" находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе "образец".
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как "Мария" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе "образец".
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как "Мария" находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:
Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).
Способ 1. Power Query
Само-собой, сначала превращаем наши таблицы в динамические ("умные") с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) , даём им имена (например Марки и Запчасти ) и загружаем по очереди в редактор Power Query, выбрав на вкладке Данные - Из таблицы/диапазона (Data - From Table/Range) . Если у вас старые версии Excel 2010-2013, где Power Query установлена как отдельная надстройка, то нужная кнопка будет на вкладке Power Query. Если у вас совсем новая версия Excel 365, то кнопка Из таблицы/диапазона называется там теперь С листа (From Sheet) .
После загрузки каждой таблицы в Power Query возвращаемся обратно в Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в. - Только создать подключение (Home - Close & Load - Close & Load to. - Only create connection) .
Теперь создадим дубликат запроса Запчасти, щёлкнув по нему правой кнопкой мыши и выбрав команду Дублировать запрос (Duplicate query) , затем переименуем получившийся запрос-копию в Результаты и дальше будем работать уже с ним.
Логика действий следующая:
-
На вкладке Добавление столбца выбираем команду Настраиваемый столбец (Add column - Custom column) и вводим формулу =Марки . После нажатия на ОК получим новый столбец, где в каждой ячейке будет вложенная таблица со списком наших ключевых слов - марок автопроизводителей:
Способ 2. Формулы
Если у вас версия Excel 2016 или новее, то нашу проблему можно весьма компактно и изящно решить с помощью новой функции ОБЪЕДИНИТЬ (TEXTJOIN) :
Логика работы этой формулы проста:
Сравнение быстродействия и буферизация запроса Power Query для ускорения
Для тестирования быстродействия возьмем в качестве исходных данных таблицу из 100 000 описаний запчастей. На ней получаем следующие результаты:
- Время пересчета формулами (Способ 2) - 9 сек. при первом копировании формулы на весь столбец и 2 сек. при повторном (сказывается буферизация, видимо).
- Время обновления запроса Power Query (Способ 1) гораздо хуже - 110 сек.
Само-собой, многое зависит от "железа" отдельно взятого ПК и установленной версии Office и обновлений, но общая картина, думаю, понятна.
Для ускорения запроса Power Query давайте буферизуем таблицу-справочник Марки, т.к. она у нас не меняется в процессе выполнения запроса и постоянно пересчитывать её (как это де-факто делает Power Query) не нужно. Для этого используем функцию Table.Buffer из встроенного в Power Query языка М.
Для этого откроем запрос Результаты и на вкладке Просмотр нажмём на кнопку Расширенный редактор (View - Advanced Editor) . В открывшемся окне добавим строку с новой переменной Марки2, которая будет буферизованной версией нашего справочника автопроизводителей и используем эту новую переменную далее в следующей команде запроса:
После такой доработки скорость обновления нашего запроса возрастает почти в 7 раз - до 15 сек. Совсем другое дело :)
Быстрый способ разделить слипшиеся данные на несколько ячеек с помощью функции ФИЛЬТР.XML, превратив исходный текст в XML-код.
Как быстро найти в исходном тексте все ключевые слова из справочника и вывести их рядом с каждой ячейкой данных через заданный символ-разделитель. Попутно разбираемся с буферизацией запросов в Power Query с помощью функции Table.Buffer для ускорения обработки.
Как заменить множество одних фрагментов текста на другие по справочнику в Power Query с помощью хитрой функции List.Accumulate.
Как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.
Подробный разбор нового инструмента в Power Query - объединения таблиц с помощью нечёткого текстового поиска.
Как бороться с повторами в тексте внутри ячейки: обнаруживать их, выделять цветом или удалять. С помощью формул, макросов или запросов Power Query.
Подробный разбор вариантов применения одного из самых удивительных, но малоизвестных инструментов - Мгновенного заполнения (Flash Fill). Поможет для обработки текста: нарезки, склейки, исправления регистра, добавления или удаления слов, извлечения чисел из текста и многого другого.
Как работать с переносами строк (Alt+Enter) в Excel: удалять их, заменять на пробелы и другие символы, делить по ним данные на столбцы или строки.
Как быстро сгенерировать все возможные фразы, состоящие из заданных наборов слов в любом порядке, используя Декартово произведение множеств с помощью формул или Power Query.
Как использовать бесплатную надстройку Fuzzy Lookup для нечеткого поиска ближайших текстовых соответствий в двух таблицах на примере поиска совпадающих адресов.
Как перенести данные из таблицы PDF-документа на лист Microsoft Excel, используя только Word и Power Query.
Как извлечь из текста последнее слово или фрагмент по заданному символу-разделителю. Разбор нескольких способов (формулы, макросы, Power Query).
Как с помощью регулярных выражений (Regular Expression = RegExp) находить и извлекать из текста фрагменты по заданным сложным шаблонам. Например, вытащить номер счета из описания платежа, город из адреса, телефон из контактов клиента и т.п.
Как добавить невидимый текстовый комментарий к формуле в ячейке с помощью функции преобразованиия Ч(N).
Подробный разбор функции замены текста ПОДСТАВИТЬ (SUBSTITUTE) и примеров ее использования для удаления неразрывных пробелов, подсчета количества слов, извлечения первых двух слов из текста.
Как удалить из текста лишние пробелы в начале, в конце или между словами с помощью функции СЖПРОБЕЛЫ (TRIM) или формул.
Большинство функций Excel не различают строчные и прописные буквы. Если же это необходимо, то сможет помочь функция СОВПАД (EXACT), умеющая сравнивать данные с учетом регистра.
Как при помощи формул, макро-функций или надстройки Power Query склеить (сцепить) текст из нескольких ячеек, но только при выполнении заданного условия. Что-то похожее на функцию СУММЕСЛИ (SUMIF), но - для текста. Причем условия могут быть как точными, так и приблизительными или их может быть несколько.
Как подсветить ячейки с лишними пробелами в полях ввода формы, намекнув пользователю, что он некорректно ввел данные.
Несколько способов очистить текстовые данные в Excel от ненужных символов, мешающих последующей обработке: лишних пробелов, непечатаемых символов, апострофов, латиницы и т.д.
Несколько способов разделить текст из одной ячейки (столбца) - на несколько. Например, поделить столбец с адресом на три отдельных столбца с индексом, городом, улицей в каждом по-отдельности. Или разделить слипшееся ФИО без пробелов на отдельные слова.
Как быстро найти в символы латиницы, ошибочно набранные вместо русских букв (английская "С" вместо русской "С") в большом списке?
Как при помощи функции ВПР (VLOOKUP) и простого макроса быстро заполнять любой бланк (счет-фактура, платежка, приходно-кассовый ордер и т.д.) данными из таблицы.
Как собрать текст из нескольких ячеек в одну с помощью функций СЦЕП, СЦЕПИТЬ, ОБЪЕДИНИТЬ и объединять ячейки без потери текста всех ячеек кроме верхней левой специальным макросом.
Пользовательская функция на VBA для поиска наиболее похожего текста в списке (по максимальному количеству совпадений символов).
Если Вам приходится делать многоязычные отчеты, то Вам пригодится этот макрос, умеющий переводить таблицы Excel с одного языка на другой. Всем работающим в иностранных компаниях посвящается.
Готовая пользовательская функция на VBA для перевода чисел в текст, т.е. в сумму прописью (123 = сто двадцать три).
Небольшой макрос для быстрого преобразования русских символов в английские, т.е. кириллицы в транслит (Пупкин в Pupkin и т.п.)
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071
Читайте также: