Подстрока из строки в excel

Примеры

Поиск и замена в документах и презентациях

1. Откройте файл в Google Документах или Презентациях на компьютере.
2. Выберите Правка Найти и заменить.
3. В поле «Найти» укажите нужное слово. Чтобы заменить его, введите новый текст в поле «Заменить на».
4. Чтобы перемещаться между результатами поиска, используйте кнопки Следующее и Предыдущее.
5. Чтобы уточнить поисковый запрос, используйте следующие параметры:
   • Учитывать регистр. Поиск с учетом прописных и строчных букв.
   • Учитывать регулярные выражения. Поиск текста в Google Документах с помощью регулярных выражений.
6. Чтобы выделенный текст поменялся на тот, который вам нужен, нажмите Заменить. Чтобы исправить все результаты поиска, нажмите Заменить все.

Как вставить пустое значение в excel

Регистрация на форуме тут, о проблемах пишите сюда — [email protected], проверяйте папку спам! Обязательно пройдите восстановить пароль

Поиск по форуму

Расширенный поиск

К странице.

Страница 1 из 2

1

2

Следующая >

А что, «пусто» от «пустой строки» сильно отличается ?

это те значения, которые имеют ячейки при открытии новой книги, а не ноль и не пустой текст

__ Полезные надстройки для Excel. Парсинг сайтов и файлов. Макросы любой сложности на заказ. Мониторинг цен конкурентов

EducatedFool

Посмотреть профиль

Найти ещё сообщения от EducatedFool

А что, «пусто» от «пустой строки» сильно отличается ?

Позвольте узнать, каким способом Вы определили, что ячейки вновь создаваемой книги какие-то «особо пустые»?

позвольте, в ячейке =»» — записана формула, естесственно она неПУСТО. Если в ячейке есть формула, то она уже никогда не будет «ЕПУСТО», какой бы результат не возвращала

как вычислить посредством формулы, чтобы значение ячейки равнялось «пусто»

Определяется применением функции ЕПУСТО(): к девственной ячейке — получается значение ИСТИНА и к ячейке =»» — получается значение ЛОЖЬ

А Вы проверять это пробовали?

Ввожу в ячейку формулу =ЕПУСТО(A4) В ячейку А4 ввожу данные — формула выдаёт ЛОЖЬ Удаляю данные (клавишей Delete) — формула выдаёт опять ИСТИНА.

Что, «девственность ячейки» восстановилась?

__ Полезные надстройки для Excel. Парсинг сайтов и файлов. Макросы любой сложности на заказ. Мониторинг цен конкурентов

EducatedFool

Посмотреть профиль

Найти ещё сообщения от EducatedFool

позвольте, в ячейке =»» — записана формула, естесственно она неПУСТО. Если в ячейке есть формула, то она уже никогда не будет «ЕПУСТО», какой бы результат не возвращала

Вы несколько неправильно понимаете и вот почему.

Применением функции (в частности ЕПУСТО() ) к ячейке в качестве её аргумента используется именно значение ячейки, а не её содержание (формула).

Пример: в ячейке А1 запишем форумулу =»» Значение ячейки будет пустая строка (текст). Проверка — запишите в ячейке B1 формулу =ЕТЕКСТ(А1). Значение получится ИСТИНА.

А Вы проверять это пробовали?

Ввожу в ячейку формулу =ЕПУСТО(A4) В ячейку А4 ввожу данные — формула выдаёт ЛОЖЬ Удаляю данные (клавишей Delete) — формула выдаёт опять ИСТИНА.

Что, «девственность ячейки» восстановилась?

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

Вы совершенно правильно описали пример. Удаляя содержимое ячейки клавишей Delete, ячейка принимает значение ПУСТО. Я это понимаю именно так — «девственность ячейки» восстановилась. Т.е. значение не есть число или текст (частный случай текста пустая строка «»).

Я же спрашивал как, например, формулой ЕСЛИ( лог_выражение; ячейка_принимает_значение_пусто ; ) определить значение ячейки как «ПУСТО».

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

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

Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

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

Пример 3: Продолжение поиска с использованием Find с параметром After.

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом

Свойство FindFormat можно задавать разными способами, например, так:

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

Пример 5: Найти последнюю колонку и столбец, заполненные данными

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

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

Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы «т», при этом после этого слова может следовать любой текст.

Для поиска функцией Find по маске (шаблону) можно применять символы: * — для обозначения любого количества любых символов; ? — для обозначения одного любого символа;

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

• Тип данных Date в VBA представляется в виде #//#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
• В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

Пример 8: Найти 1 марта 2018 г.

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

Как выполнить точный поиск текстового значения в столбце Excel

Ниже на рисунке представлен список идентификаторов MAC-адресов сетевых карт компьютеров и даты последнего входа пользователя в панель администрирования. Идентификаторы на строках листа Excel 3 и 10 выглядят идентичными за исключением двух символов по средине (Ef и eF). Допустим нам необходимо найти идентификатор MAC-адреса сетевой карты с учетом регистра символов (большая или маленькая буква). Формула:

На выше приведенном рисунке отображены результаты вычислений сразу двух формул. В первой формуле использована функция ВПР в ячейке D4: =ВПР(D3;A2:B11;2;ЛОЖЬ), а во второй СОВПАД. Хоть в ячейке D3 введен идентификатор MAC-адреса с 10-ой строки листа, функция возвращает в результате вычислений значения с 3-й строки.

Вторая формула, приведенная ниже с функцией СОВПАД возвращает правильный результат:

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

Поиск позиции подстроки

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; )

• искомый_текст – строка, которую необходимо найти;
• текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
• нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.

Пример использования:

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

Постоянно напоминайте себе о правильных и хороших, и старайтесь, чтобы вокруг вас их было больше.

Когда вы держите в своем разуме как можно больше позитивных мыслей, это помогает вам преодолевать окружающий вас негатив.

В конце каждого дня вспомните все ваши победы за день, неважно, насколько крохотные, и обо всем, что пошло правильно, и так, как нужно. А еще вспомните о трех небольших событиях, случившихся сегодня, и за которые вы благодарны. К примеру:

К примеру:

• Я и вся моя семья безопасно добрались домой с работы и школы.
• Мы с моей женой хорошо посмеялись сегодня над одним забавным случаем.
• Сегодня у нас был очень вкусный и приятный семейный обед.
• И так далее.

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

INSTR Function

The VBA Instr Function checks if a string of text is found in another string of text.  It returns 0 if the text is not found. Otherwise it returns the character position where the text is found.

The Instr Function performs exact matches. The VBA Like Operator can be used instead to perform inexact matches / pattern matching by using Wildcards.

Instr Example

The following code snippet searches the string “Look in this string” for the word “Look”. The Instr Function returns 1 because the text is found in the first position.

This second example returns 7 because the text is found starting in the 7th position:

Important! The Instr Function is case-sensitive by default. This means “look” will not match with “Look”. To make the test case-insensitive read below.

Instr Syntax

The syntax for the Instr function is as follows:

(optional) – This optional argument is the starting position of the search. Enter 1 to start searching from position 1 (or leave blank). Enter 5 to start searching from position 5. Important! The INSTR function calculates the character position by counting from 1 NOT from the position.

string – The string of text to search in.

substring – The string of text to find in the primary string.

(optional) – By default, Instr is case-sensitive. By setting this argument you can make Instr Case insensitive:

Argument vb Value	Argument Integer	Description
vbBinaryCompare	(Default) Case-sensitive
vbTextCompare	1	Not Case-sensitive
vbDatabaseCompare	2	MS Access Only. Uses information in the database to perform comparison.

Instr Start Position

The Instr start position allows you to indicate the character position where you will begin your search.  Keep in mind however, the Instr output will always count from 1.

Here we set the start position to 3 to skip the first B:

The result is 6 because the second B is the 6th character in the string.

Case-Insensitive INSTR Test

By default, VBA treats “L” different from “l”. In other words, VBA is case-sensitive. This is true of all text functions.  To make VBA case-insensitive, set the argument to 1 or vbTextCompare.

Alternatively, you can add Option Compare Text to the top of your code module:

Option Compare Text will impact all of the code in that module. I personally place this at the top of any module that deals with text because I never care about case differences.

Условное форматирование по части текста в ячейке Excel

Базовая информация об ИНДЕКС и ПОИСКПОЗ

Функция 23: FIND (НАЙТИ)

Функция FIND (НАЙТИ) находит текстовую строку внутри другой текстовой строки с учётом регистра.

Как можно использовать функцию FIND (НАЙТИ)?

Функция FIND (НАЙТИ) может найти текст внутри текстовой строки, учитывая регистр символов. Например:

• Найти начальную позицию текста в текстовой строке.
• Найти точные данные на листе.
• Найти название улицы в адресе.

Синтаксис FIND (НАЙТИ)

Функция FIND (НАЙТИ) имеет вот такой синтаксис:

FIND(find_text,within_text,) НАЙТИ(искомый_текст;просматриваемый_текст;)

• find_text (искомый_текст) – текст, который Вы ищете.
• within_text (просматриваемый_текст) – текстовая строка, внутри которой происходит поиск.
• start_num (нач_позиция) – если не указан, то поиск начнётся с первого символа.

Ловушки FIND (НАЙТИ)

• Функция FIND (НАЙТИ) возвратит позицию первой совпадающей строки с учётом регистра. Для того, чтобы произвести поиск без учёта регистра, используйте функцию SEARCH (ПОИСК), которую мы уже рассматривали ранее в рамках марафона 30 функций Excel за 30 дней.
• В аргументе find_text (искомый_текст) функции FIND (НАЙТИ) нельзя использовать символы подстановки. Если Вы все-таки хотите их использовать, то применяйте функцию SEARCH (ПОИСК).

Функции И и ИЛИ

Поиск значения в строке Excel

ИНДЕКС+ПОИСКПОЗ – поиск с учётом регистра для любых типов данных

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

Этот пример идёт последним не потому, что лучшее оставлено на десерт, а потому, что знания, полученные из предыдущих примеров, помогут лучше и быстрее понять чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).

Как Вы, наверное, догадались, комбинация функций ПОИСКПОЗ и ИНДЕКС используется в Excel как более гибкая и мощная альтернатива для ВПР. Статья Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР прекрасно объяснит Вам, как эти функции работают в паре.

Я лишь напомню ключевые моменты:

• Функция ПОИСКПОЗ (MATCH) ищет значение в заданном диапазоне и возвращает его относительную позицию, то есть номер строки и/или столбца;
• Далее, функция ИНДЕКС (INDEX) возвращает значение из определённого столбца и/или строки.

Чтобы формула ИНДЕКС+ПОИСКПОЗ могла искать с учётом регистра, к ней нужно добавить лишь одну функцию. Не трудно догадаться, что это снова СОВПАД (EXACT):

В этой формуле СОВПАД работает так же, как и в , и даёт такой же результат:

Заметьте, что формула ИНДЕКС+ПОИСКПОЗ заключена в фигурные скобки – это формула массива, и Вы должны завершить её ввод нажатием Ctrl+Shift+Enter.

Почему ИНДЕКС+ПОИСКПОЗ – это лучшее решение для поиска с учётом регистра?

Главные преимущества связки ИНДЕКС и ПОИСКПОЗ:

1. Не требует добавления вспомогательного столбца, в отличие от ВПР.
2. Не требует сортировки столбца поиска, в отличие от ПРОСМОТР.
3. Работает со всеми типами данных – с числами, текстом и датами.

Эта формула кажется идеальной, не правда ли? На самом деле, это не так. И вот почему.

Предположим, что ячейка в столбце возвращаемых значений, связанных с искомым значением, пуста. Какой результат возвратит формула? Никакой? Давайте посмотрим, что возвратит формула на самом деле:

Упс, формула возвращает ноль! Это может быть не велика беда, если Вы работаете с чисто текстовыми значениями. Однако, если таблица содержит числа, в том числе «настоящие» нули – это становится проблемой.

На самом деле, все остальные формулы поиска (ВПР, ПРОСМОТР и СУММПРОИЗВ), которые мы обсуждали ранее, ведут себя так же. Но Вы же хотите безупречную формулу, так ведь?

Чтобы сделать чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ идеальной, поместите её в функцию ЕСЛИ (IF), которая будет проверять ячейку с возвращаемым значением и возвращать пустой результат, если она пуста:

В этой формуле:

• B – это столбец с возвращаемыми значениями
• 1+ – это число, которое превращает относительную позицию ячейки, возвращаемую функцией ПОИСКПОЗ, в реальный адрес ячейки. Например, в нашей функции ПОИСКПОЗ задан массив поиска A2:A7, то есть относительная позиция ячейки A2 будет 1, потому что она первая в массиве. Но реальная позиция ячейки A2 в столбце – это 2, поэтому мы добавляем 1, чтобы компенсировать разницу и чтобы функция ДВССЫЛ (INDIRECT) извлекла значение из нужной ячейки.

Рисунки ниже демонстрируют исправленную чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ в действии. Она возвращает пустой результат, если возвращаемая ячейка пуста.

Я переписал формулу в столбцы B:D, чтобы строка формул поместилась на скриншоте.

Формула возвращает , если возвращаемая ячейка содержит ноль.

Если Вы хотите, чтобы связка ИНДЕКС и ПОИСКПОЗ отображала какое-то сообщение, когда возвращаемое значение пусто, можете написать его в последних кавычках («») формулы, например, так:

Проверка вводимых данных в ячейки Excel

Допустим, что в таблице прайс-листа с розничными ценами на товары, в одном из столбцов указана процентная ставка НДС. Как часто бывает в рутинной работе срабатывает человеческий фактор и по ошибке для одной из категорий товаров, вместо ставки НДС 20% была введена старая ставка 19%. Эта небольшая разница в данных – 1% может создать большие проблемы для фирмы с всевозможными последствиями. Чтобы исключить ошибки созданных по причине человеческого фактора, воспользуемся встроенным инструментом Excel для проверки данных, который позволяет контролировать все что вводиться на рабочий лист.

Как в Excel сделать проверку данных в ячейках

Пример прайс-листа с введенными ошибками в процентных ставках НДС:

Чтобы в Excel сделать проверку вводимых данных в ячейки следует выполнить ряд последовательных действий:

Закладка «Сообщение об ошибке» предоставляет пользователю возможность оформить стиль сообщения об ошибочных вводах значений. Если пользователь вводить в ячейку неправильное значение тогда будет выполнен один из 3-х параметров:

1. Останов – данный параметр разрешает вводить только правильное значение, выбранного из выпадающего списка или отменить ввод оставив пустую ячейку.
2. Предупреждение – этот параметр предупреждает об вводе ошибочного значения и предоставляет попытку отредактировать или выбрать из выпадающего списка.
3. Сообщение – параметр сообщает о неправильном значении и позволяет проигнорировать ошибку.

Закладка «Сообщение для ввода» содержит поля ввода для заголовка и текстовое поле для сообщения в примечании, которое будет появляться перед вводом данных в ячейку. А точнее как только ячейка будет активной сразу высветлиться соответствующее примечание. Данное примечание повышает контроль над ошибками связанных с вводом значений. В нем можно указать правильное значение для данных ячеек.

После заполнения всех параметров в окне «Проверка вводимых значений» нажмите на кнопку ОК.

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

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

Проверка ячейки на наличие в ней текста (без учета регистра)

​ текст или число​: _Boroda_, Да спасибо​Функция =НАЙТИ() выполняет поиск​ Ссылка в первом​ячейка C6 не​но они почему-то​ массива.​)​ перечня​ совпадает (1), то​ перечнем инструментов (см.​ поиска. Например, можно найти​Мы стараемся как​

​ значительно проще получается:​If InStr(r.Text, «ИНН»)​ из способов. Но​ ИНАЧЕ значение с.​ НЕ отвечают заданному​

Поиск ячеек, содержащих текст

​ большое! СУММПРОИЗВ тоже​ фрагмента текста в​ аргументе должна быть​

1. ​ имеет отношения к​ некорректно работают, не​Владислав 1​

   ​Рассмотрим списки, элементами которых​заканчивается​ красным фоном выделится​

2. ​ файл примера). Список​​ все ячейки, содержащие​​ можно оперативнее обеспечивать​​=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК(«КПП»;B1:B6))/ЕЧИСЛО(ПОИСК(«ИНН»;B1:B6));B1:B6)​​ > 0 And​​ с именем проще.​​Функция ИЛИ проверяет условие​​ условию (лживы).​​ подошла​

3. ​ ячейке Excel. Потом​​ относительной, так как​​ диапазону B148:C180​ тянут то что​: Помогите новичку…​ являются отдельные слова​на текст, указанный​​ только ячейка​​ содержит как ячейки​

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

4. ​ InStr(r.Text, «КПП») >​Himtree​​ 1 или условие​​Пример:​Если ячейка содержит «текст1»,​ возвращает номер символа,​​ формула применяется к​​Qualcomm​

5. ​ мне требуется. Буду​​Какая функция нужна​​ (не фразы). Выделять​ в Критерии.​A14​ с одним словом,​ такого как формулы.​ материалами на вашем​

   ​ требуется​ 0 Then inn​: Добрый вечер! Битые​ 2. Как только​​Оператор проверяет ячейку А1​​ значение «1», если​​ в котором был​​ каждой ячейке столбца​​:​​ очень благодарен за​

6. ​ чтобы проверить условие:​​ будем только ту​​Примечание​​, содержащая значение​​ так и текстовые​

   ​Для поиска на текущем​​ языке. Эта страница​Himtree​ = r.Text: Exit​ сутки не могу​ хотя бы одно​ и сравнивает ее​​ содержит «текст2», значение​​ найдет текст, который​

​ A. Во втором​​Pelena​ помощь.​

​Если ячейка содержит​ ячейку, которая содержит​​: Обратите внимание, что​​в точности совпадающее​

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

​ строки (несколько строк,​ листе или во​ переведена автоматически, поэтому​: Да, спасибо, как​​ Function​​ решить казалось бы​

Проверка соответствия части ячейки определенному тексту

​ условие истинно, то​ с 20. Это​​ «2» и так​​ задан в первом​​ аргументе функции указывается​​, спасибо. Это намного​​sorcerer​​ текст «Кол:» (т.е.​

​ любое из слов-критериев​​ будет выделена ячейка​​с искомым текстом.​​ разделенных пробелами).​

support.office.com>

Применение в функциях

Как уже говорилось выше, подстановочные знаки в Excel могут использоваться в качестве критерия при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, ГПР, ВПР и другие).

Повторим задачу из предыдущего примера и подсчитаем количество сотрудников компании, фамилии которых начинаются на букву «п». Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет посчитать количество ячеек соответствующих указанному критерию. В качестве диапазона данных укажем диапазон с сотрудниками (A2:A20), а в качестве критерия укажем запись «п*» (т.е. любая фраза начинающаяся на букву «п»):

Однако не все функции поддерживают применение подстановочных знаков. Некоторые из них (к примеру, функция НАЙТИ) любой символ воспринимают как текст, даже несмотря на то, что он может быть служебным. С помощью функции НАЙТИ найдем в тексте позицию вхождения вопросительного знака и звездочки:

ПОИСК

Сравните диапазон ячеек с образцом.

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

Совпадает ли ячейка с образцом?

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

ЧСТРОК( диапазон ) * ЧИСЛСТОЛБ( диапазон ) = СЧЁТЕСЛИ( диапазон ; ячейка образца )

В логической проверке функции ЕСЛИ вы проверяете равенство двух чисел:

1. Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов), и
2. Количество ячеек, содержащих то же значение, что и в образце (возвращенное функцией СЧЁТЕСЛИ ).

Предполагая, что образец текста находится в C2, а данные находятся в диапазоне A2: B6, формула выглядит следующим образом:

(ЧСТРОК(A2:B6)*ЧИСЛСТОЛБ(A2:B6))=СЧЁТЕСЛИ(A2:B6;C2)

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

Как показано на скриншоте выше, формула отлично справляется с диапазоном текстовых значений, но ее также можно использовать для сопоставления чисел и дат.

Проверяем совпадение с образцом с учётом регистра букв.

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

Поскольку исходный диапазон находится в области A2: B6, а образец текста — в C2, получаем следующее:

В отличие от обычных формул Excel, формулы массива заполняются нажатием Ctrl + Shift + Enter. При правильном вводе Excel заключает формулу массива в {фигурные скобки}, как показано ниже:

Функция ПОИСК() в EXCEL

history 12 апреля 2013 г.

• Группы статей
• Подстановочные знаки (*, ?)
• Функции и Средства EXCEL учитывающие подстановочные знаки (*, ?)

Синтаксис функции

ПОИСК ( искомый_текст ; просматриваемая_строка

Искомый_текст — текст, который требуется найти.

Просматриваемая_строка — текст, в которой ищется Искомый_текст .

Нач_позиция — позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент нач_позиция опущен, то предполагается значение 1.

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

Если искомый_текст не найден, возвращается значение ошибки #ЗНАЧ!

Функция ПОИСК() не учитывает РЕгиСТР букв. Для поиска с учетом регистра следует воспользоваться функцией НАЙТИ() .

Примеры

Формула =ПОИСК(«к»;»Первый канал») вернет 8, т.к. буква к находится на 8-й позиции слева.

Пусть в ячейке А2 введена строка Первый канал — лучший . Формула =ПОИСК(СИМВОЛ(32);A2) вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Формула =ПОИСК(«#. #»;»Артикул #123# ID») будет искать в строке » Артикул #123# ID » последовательность из 5 символов, которая начинается и заканчивается на знак #.

Чтобы найти позицию второго вхождения буквы «а» в строке «мама мыла раму» используйте формулу =ПОИСК(«а»;»мама мыла раму»;ПОИСК(«а»;»мама мыла раму»)+1). Чтобы определить есть ли третье вхождение буквы «м» в строке «мама мыла раму» используйте формулу =ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(«мама мыла раму»;»м»;»»;3))=ДЛСТР(«мама мыла раму»);»Нет третьего вхождения м»;»Есть третье вхождение м»)

Формула =ПОИСК(«клад?»;»докладная») вернет 3, т.е. в слове «докладная» содержится слово из 5 букв, первые 4 из которых клад (начиная с третьей буквы слова докладная ).

Функция НАЙТИ() vs ПОИСК()

Функция НАЙТИ() учитывает РЕгиСТР букв и не допускает использование подстановочных знаков. Для поиска без учета регистра, а также для поиска с использованием подстановочных знаков пользуйтесь функцией ПОИСК() .

Связь с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР()

Функция ПОИСК() может быть использована совместно с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР() .

Например, в ячейке А2 содержится фамилия и имя «Иванов Иван», то формула =ЛЕВСИМВ(A2;ПОИСК(СИМВОЛ(32);A2)-1) извлечет фамилию, а =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(32);A2)) — имя. Если между именем и фамилией содержится более одного пробела, то для работоспособности вышеупомянутых формул используйте функцию СЖПРОБЕЛЫ() .

Поиск и замена в таблицах

1. Откройте файл в Google Таблицах на компьютере.
2. Выберите Правка Найти и заменить.
3. В поле «Найти» укажите нужное слово. Чтобы изменить его, введите новый текст в поле «Заменить на».
4. Нажимайте Найти, чтобы перемещаться между результатами поиска.
5. Чтобы уточнить поисковый запрос, используйте следующие параметры:
   • Учитывать регистр. Поиск с учетом прописных и строчных букв.
   • Совпадение с полным содержанием ячейки. Поиск ячеек с полным совпадением текста.
   • Поиск с использованием регулярных выражений. Поиск ячеек по определенному шаблону.
   • Поиск по формулам. Поиск с учетом формул.
6. Чтобы заменить выделенный текст, нажмите Заменить. Чтобы исправить все результаты поиска, нажмите Заменить все.

Проверка ячейки на наличие в ней текста (без учета регистра)

​вот такая, блин​ часть слова в​ B1:B6, и формула​Dim r As​ данном случае?​H5455​ очень надо.​Во всех выше описанных​ ОК на всех​ Функция СОВПАД учитывает регистр,​ищутся значения с учетом​ такого как формулы.​Мы стараемся как​

​ после копирования скобки​Наташа Демчук​ (ПОИСК (“суббота”;A1);-1)))>0;A1;””)​ листе 1 в​ задача :((((​

Поиск ячеек, содержащих текст

​ определенном столбце и​ значительно проще получается:​ Range​

1. ​Z​: А если эти​Большое спасибо!!!​

   ​ примерах очень легко​ открытых окнах.​ но не учитывает​

2. ​ РЕгиСТра.​​Для поиска на текущем​​ можно оперативнее обеспечивать​​ исчезают и формула​​: Добрый день, очень​​Успехов!​​ столбце D ячейку​​Серега​​ подставить сокращенное имя.​

3. ​=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК(“КПП”;B1:B6))/ЕЧИСЛО(ПОИСК(“ИНН”;B1:B6));B1:B6)​​For Each r​​: Подсказки живут тута​ формулы в LibO​Михаил С.​ применять текстовые функции​Экспонированные цветом изделия 2006-го​​ различия в форматировании.​​Это простейший случай. Здесь​

   ​ листе или во​​ вас актуальными справочными​ не работает, извините,​

4. ​ похожая тема у​Алексей матевосов (alexm)​​ с соответствущим числом​​: Сначала надо составить​Полное значение ячейки​массивный ввод не​​ In rng​​ – .​

5. ​ не работают в​​: ВПР с подставочными​​ в условном форматировании​ года выпуска:​Для выполнения этой задачи​ можно использовать формулу​ всей книге можно​ материалами на вашем​

   ​ но не могли​ меня, помогите прописать​: Вариант, работающий в​ из столбца С​​ таблицу соответствия​​ всегда разное, надо​​ требуется​​If InStr(r.Text, “ИНН”)​​Ваша – здеся​​ основном из-за того​

6. ​ знаками работает при​​ так как длина​​Далее разберем принцип действия​​ используются функции СОВПАД​​ наподобие нижеуказанной​

   ​ выбрать в поле​​ языке. Эта страница​ бы Вы пояснить,​ формулу, пожалуйста, что-то​ любой версии Excel​ на лист 2.​Сокращенное название -​​ найти часть слова​​Himtree​

​ > 0 And​​ – , см.​ что я использую​

​ последнем параметре ИСТИНА​ строк в исходных​​ формулы и ее​​ и или .​

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

​=СЧЁТЕСЛИ($A$5:$A$11;”яблоки”)​Искать​ переведена автоматически, поэтому​ как правильно скопировать,​​ не выходит по​​=ЕСЛИ (ЕЧИСЛО (ПОИСК​

Проверка соответствия части ячейки определенному тексту

​ Надеюсь задача понятна.​ Полное название​​ и, если таковое​​: Да, спасибо, как​​ InStr(r.Text, “КПП”) >​​ – Ошибки #Н/Д​​ знак &, если​​ (1) и диапазон​

​ данных одинаковая. Но​​ модификации схожими текстовыми​​Примечание:​​Формула возвращает количество найденных​

support.office.com>

Проверка соответствия части ячейки определенному тексту

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

Примечание: Функция Поиск не учитывает регистр.

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

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

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; )

• искомый_текст – строка, которую необходимо найти;
• текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
• нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.

Пример использования:

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