Функция ЕСЛИ СОДЕРЖИТ
Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.
Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ .
Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)
У нас есть множество строк с документами Реализации и Возвратов .
Все документы имеют свое наименование за счет уникального номера .
Нам необходимо сделать признак « Только реализация » напротив документов продажи, для того, чтобы в дальнейшем включить этот признак в сводную таблицу и исключить возвраты для оценки эффективности деятельности отдела продаж.
Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .
Для того, чтобы это сделать, необходимо:
- Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).
В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.
Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ* , добавляя в начале и в конце символ * .
Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»
Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.
В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « — »
Функция ЕСЛИ
В этом курсе:
Функция ЕСЛИ — одна из самых популярных функций в Excel. Она позволяет выполнять логические сравнения значений и ожидаемых результатов.
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Например, функция =ЕСЛИ(C2=»Да»;1;2) означает следующее: ЕСЛИ(С2=»Да», то вернуть 1, в противном случае вернуть 2).
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
ЕСЛИ(лог_выражение; значение_если_истина; )
Условие, которое нужно проверить.
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Функция ЕСЛИ СОДЕРЖИТ
EXCEL«СОДЕРЖИТ»кусок словачастьнестандартном заполнении
Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ .
Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)
У нас есть множество строк с документами Реализации и Возвратов .
Все документы имеют свое наименование за счет уникального номера .
Нам необходимо сделать признак « Только реализация » напротив документов продажи, для того, чтобы в дальнейшем включить этот признак в сводную таблицу и исключить возвраты для оценки эффективности деятельности отдела продаж.
Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .
Для того, чтобы это сделать, необходимо:
- Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).
В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.
СЧЁТЕСЛИ«Критерий»*реализ**
Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»
Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.
В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « — »
Далее протягиваем формулу до конца таблицы и подключаем сводную.
Теперь мы можем работать и сводить данные только по документам реализации исключая возвраты . При дополнении таблицы новыми данными, остается только протягивать строку с нашим выражением и обновлять сводную таблицу.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже: (для перевода по карте нажмите на VISA и далее «перевести»)
Логический оператор ЕСЛИ в Excel применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.
Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.
Инструмент замены символов в табличном редакторе Excel
Как сравнить две ячейки в Excel.
Проверка в Excel, если содержит текст
Что делать, если нужно проверить, содержит ли ячейка строку текста. Используем функцию: =ЕТЕКСТ(ссылка) . Функция вернет ИСТИНА, когда ссылка указывает на ячейку с текстом, ЛОЖЬ – в противном случае. Вот несколько примеров работы:
Получили такие результаты:
То есть, функция считает текстом не только строку, а и число в текстовом формате ячейки, непечатаемый символ.
Обратный результат даст функция =ЕНЕТЕКСТ(ссылка) , которая проверяет, чтобы в ячейке было нетекстовое значение:
Таблица с итогами:
Как будет выглядеть формула в Excel: если ячейка содержит текст, то значение, в противном случае – пустая строка.
Вот так: =ЕСЛИ(ЕТЕКСТ(A1);»Это текст»;»») . Т.е. аргументы:
- Условие: ЕТЕКСТ(А1)
- Значение, если это текст: строка «Это текст»
- Значение, если это не текст: пустая строка (обозначается «»)
Как перевернуть текст в excel
Сумма, если равен х или у
Если вам необходимо суммировать числа, основанные на других ячейках равных либо одному значению или другому (х или у), вы можете использовать функцию СУММЕСЛИ.
В примере, мы подсчитываем все продажи либо от Западного или Северного региона. Формула в ячейке G6 является:
Каждый экземпляр СУММЕСЛИ обеспечивает промежуточный итог, один для продаж на Западе, один для продаж на Севере. Формула просто добавляет эти два результата вместе.
СУММЕСЛИ с аргументом массива
Более элегантное решение, чтобы дать функции СУММЕСЛИ более одного значения для критериев, использовать константу массива. Чтобы сделать это, постройте нормальный СУММЕСЛИ, но пакет критериев в синтаксисе массива — фигурные скобки, с отдельными элементами, разделенные запятыми. И, наконец, обернуть всю функцию СУММЕСЛИ в функцию СУММ. Это необходимо, потому что СУММЕСЛИ будет рассчитывать один результат для каждого элемента массива критериев. Они должны быть добавлены вместе, чтобы получить один результат.
Формула:
СУММПРОИЗВ альтернатива
Вы можете также использовать СУММПРОИЗВ для подсчета ячеек с логикой ИЛИ. Формула:
Это также может быть записано в виде:
СУММПРОИЗВ не так быстра, как СУММЕСЛИ, но разница в скорости не заметна с меньшими наборами данных.
Функция ЕСЛИ с частичным совпадением текста
Ранее мы разобрались, как сравниваются строки на предмет полного соответствия. Но в некоторых ситуациях достаточно лишь частичного совпадения строки с эталоном
Например, нам нужен город, но какой именно – не столь важно. Самый простой метод, как это сделать – использовать операторы ? и *
Но это хорошая идея далеко не во всех ситуациях.
ЕСЛИ + ПОИСК
Один из самых надежных способов добиться поставленной задачи – использование функции ПОИСК в качестве аргумента функции ЕСЛИ. С её помощью пользователь может определить порядковый номер знака, который начинает отсчёт строки. В общем виде эта функция выглядит следующим образом: =ПОИСК(что_ищем, где_ищем, начиная_с_какого_символа_ищем).
При этом третий аргумент не является обязательным. Если его не указывать, поиск будет осуществляться с самого начала строки. В качестве возвращаемого в этой функции значения используется номер знака, с которого начинается нужная строка или же код ошибки.
Эта функция должна использоваться совместно с ещё одним оператором. Называется он ЕЧИСЛО. Его задача – проверка, является ли определённая переменная числом. Если да, ячейке или функции передаётся значение ИСТИНА. Она может использоваться, чтобы проверить, является ли конкретная переменная текстовой. Вот, как ее использовать для наших целей: =ЕСЛИ(ЕЧИСЛО(ПОИСК(«город»,B2)),»Город»,»»).
Эта функция работает по следующему алгоритму: она проверяет, является ли значение, возвращенное функцией поиска, числовым. Если да, то возвращаемое значение – «Город». Логика очень простая. Дело в том, что эта функция также фильтрует любые другие типы данных, кроме текстовых. Поскольку функция «Поиск» может возвращать и ошибку в случае ненахождения соответствующего числа, то функция ЕЧИСЛО дает возможность его отфильтровать. Если оказывается, что функция ЕЧИСЛО говорит, что была выдана ошибка, то в качестве аргумента передается пустое значение.
Единственный момент, который нужно учитывать, используя эту функцию – данный способ не подходит для различения частичного совпадения с учетом регистра. То есть, этот метод по сути аналогичный использованию знака =, только для частичного совпадения.
Функция ЕСЛИ СОДЕРЖИТ
Наверное, многие задавались вопросом, как найти в EXCEL функцию «СОДЕРЖИТ» для применения любого условия, в зависимости от того, содержит ли строка текста часть слова, отрицание или часть имени аналога, особенно при заполнении регистры но нет.
Эта функциональность может быть достигнута с помощью комбинации двух общих стандартных функций: ЕСЛИ и СЧЁТЕСЛИ .
Рассмотрим пример автоматизации учета операционных показателей на основе бухгалтерских книг по продажам и возвратам (загрузка из сторонних программ автоматизации и т.д.)
У нас много линий реализации и возвратных документов .
Все документы имеют собственное название благодаря уникальному номеру .
Нам нужно создать атрибут «Только продажи» перед торговыми документами, чтобы в дальнейшем включить этот атрибут в сводную таблицу и исключить возвраты для оценки эффективности отдела продаж.
Выражение должно быть универсальным для обработки вновь добавленных данных .
Для этого вам необходимо:
- Начнем с ввода функции ЕСЛИ (введите «=», введите имя SE, выберите его из раскрывающегося списка, нажмите fx в строке формул).
В открывшемся окне аргументов в поле Log_expression введите COUNTIF (), выберите его и нажмите fx 2 раза.
Также в открытом окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» введите часть желаемого имени * инструмент *, добавив символ * в начале и в конце * .
Эта запись позволит вам не думать, с какой стороны написано слово «реализация» (до или после номера документа), а также позволит вам включить в расчет сокращенные слова «реализовано» и «разработчик.»
- Аргумент Range — это соответствующая ячейка с именем документа.
- Затем нажмите «ОК», выберите «ЕСЛИ» в строке формул, нажмите «fx» и продолжите заполнение функции «ЕСЛИ.
- В Value_if_true вставляем «Реализация», а в Value_if_false можно вставить прочерк « – »
- Далее растягиваем формулу до конца таблицы и подключаем стержень.
Теперь мы можем работать и агрегировать данные только по документам реализации, без учета возвратов. При добавлении новых данных в таблицу остается только удлинить строку с нашим выражением и обновить сводную таблицу.
Если вам понравился материал или даже он оказался полезным, вы можете поблагодарить автора, переведя определенную сумму, используя кнопку ниже:
(для перевода по карте нажмите VISA, а затем «перевести»)
Рассмотрите возможность использования функции ЕСЛИ в Excel, когда в ячейке есть текст.
Будьте особенно осторожны, если для вас важен случай написания ваших текстовых значений. Функция ЕСЛИ не проверяет регистр букв — это делается с помощью функций, которые вы в ней используете. Поясним на примере.
Использование конкатенации
Один из самых простых способов реализовать сочетание текста и значения — использовать конкатенацию (символ &).
Допустим ячейка A1 содержит итоговое значение 123,45, тогда в любой другой ячейке можно записать формулу =»Итого: «&A1
В итоге результатом будет следующее содержание ячейки Итого: 123,45.
Это простое решение, однако имеет много минусов.
- Результатом формулы будет текстовое значение, которое нельзя будет использовать при дальнейших вычислениях.
- Значение ячейки A1 будет выводится в общем формате, без возможности всякого форматирования. В следствие чего этот метод не всегда применим.
Функция ЕСЛИ СОДЕРЖИТ
Поиск ТЕКСТовых значений в Excel. Бесплатные примеры и статьи.
Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL
Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.
Есть ли слово в списке MS EXCEL
Найдем слово в диапазоне ячеек, удовлетворяющее критерию: точное совпадение с критерием, совпадение с учетом регистра, совпадение лишь части символов из слова и т.д.
Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца
Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие функции. Рассмотрим задачу в случае текстовых значений.
Поиск позиции ТЕКСТа с учетом РЕгиСТра в MS EXCEL и выводом значения из соседнего столбца
Здесь развиваются идеи статьи Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца. Для нахождения позиции значения с учетом РЕгиСТра, с последующим выводом соответствующего значения из соседнего столбца, функция ВПР() применена быть не может, т.к. она не чувствительна к регистру. Задача может быть решена с помощи функций БИЗВЛЕЧЬ() , НАЙТИ() или СОВПАД() .
Поиск в MS EXCEL текстового значения первого или последнего по алфавиту
Найдем текстовое значение, которое при сортировке диапазона по возрастанию будет выведено первым, т.е. первое по алфавиту. Также найдем последнее значение по алфавиту.
Разнесение в MS EXCEL ТЕКСТовых значений и ЧИСЕЛ по разным спискам
Если исходный список, содержит и текст и числа, то с помощью формул массива можно в один список отобрать все текстовые значения, а в другой – числовые.
Поиск в MS EXCEL третьего (n-го) повтора в списках с ТЕКСТом
Рассмотрим поиск текстовых значений в списке с повторами. Задав в качестве критерия для поиска нужное текстовое значение и номер его повтора в списке, найдем номер строки, в которой содержится этот повтор, а также выделим его и другие повторы с помощью Условного форматирования.
О классификации задач Поиска в EXCEL
В этой статье изложены попытки классификации задач по поиску значений в MS EXCEL. Статья может быть интересна для продвинутых пользователей EXCEL.
Поиск нескольких значений с максимальной длиной строки в MS EXCEL
Найдем в исходном списке текстовые значения, которые имеют максимальную длину строки, и выведем их в отдельный диапазон.
Функция ЕЧИСЛО() в EXCEL
Значение — значением может быть все что угодно: текст, число, ссылка, имя , пустая ячейка , значение ошибки, логическое выражение.
Использование функции
В файле примера приведены несколько вариантов проверок:
1. Если в качестве значения на вход подается число и формат ячейки НЕ был установлен Текстовый, то функция вернет логическое значение ИСТИНА. Если формат ячейки с числом изменить на Текстовый уже после того, как функция вернула значение ИСТИНА, то она не станет возвращать значение ЛОЖЬ.
2. Если в качестве значения на вход подается число и формат ячейки был установлен Текстовый (до ввода числа в ячейку), то функция вернет логическое значение ЛОЖЬ. Если формат ячейки с числом изменить на Числовой уже после того, как функция вернула значение ЛОЖЬ, то она не станет возвращать значение ИСТИНА.
Вывод: функция ЕЧИСЛО() не пытается конвертировать значения в числовой формат. Однако, ее легко заставить делать это. Если в ячейке А1 число сохранено как текст, то формула = ЕЧИСЛО(—A1) вернет ИСТИНА, а = ЕЧИСЛО(A1) — ЛОЖЬ.
3. Логические значения ЛОЖЬ и ИСТИНА формально в EXCEL числами не являются и это доказывает тот факт, что формулы =ЕЧИСЛО(ЛОЖЬ) и =ЕЧИСЛО(ИСТИНА) вернут ЛОЖЬ. Однако, значениям ЛОЖЬ и ИСТИНА сопоставлены значения 0 и 1 соответственно, поэтому формулы =ЕЧИСЛО(—ЛОЖЬ) и =ЕЧИСЛО(—ИСТИНА) вернут ИСТИНА.
4. Функция ЕЧИСЛО() обычно используется в паре с функцией ЕСЛИ() . Например, формула =ЕСЛИ(ЕЧИСЛО(B6);»Число»;»Не число») вернет слово Число , если в ячейке В6 находится число (в числовом формате).
Если ячейка содержит текст, отобразить в Excel
Если у вас есть список текстовых строк в столбце A и строка ключевых слов, теперь вам нужно проверить, появляются ли ключевые слова в текстовой строке. Если ключевые слова появляются в ячейке, отображая ее, если нет, отображается пустая ячейка, как показано на следующем снимке экрана.
Если ячейка содержит текст, отобразить в Excel
Для решения этой задачи следует использовать функции ПОИСК, ЕЧИСЛО и ЕСЛИ вместе. Общий синтаксис:
=IF(ISNUMBER(SEARCH(substring,text)),substring,»»)
- substring: слово, которое вы хотите найти, если оно присутствует в текстовой строке.
- text: текстовая строка, из которой вы хотите проверить.
1. Введите или скопируйте следующую формулу в ячейку C2:
=IF(ISNUMBER(SEARCH(C$1,$A2)),C$1,»»)
2. Затем перетащите маркер заполнения вправо, чтобы отобразить все тексты, которые появляются в ячейке A2 на основе данных соответствующей строки, см. Снимок экрана:
3. Продолжайте перетаскивать дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и вы получите результат, как показано на скриншоте ниже:
Пояснение к формуле:
- ПОИСК (C $ 1, $ A2): функция ПОИСК используется для проверки того, появляется ли искомое значение в C1 в A2, если строка поиска найдена, ПОИСК вернет позицию (число), если не найдено, значение ошибки # ЗНАЧ! возвращается.
- ISNUMBER (ПОИСК (C $ 1, $ A2)): функция ЕЧИСЛО вернет ИСТИНА, если поиск найден, вернет ЛОЖЬ, если не найден.
- ЕСЛИ (ЕЧИСЛО (ПОИСК (C $ 1; $ A2)); C $ 1; «»): Функция ЕСЛИ используется для возврата значения C1, если результат ЕЧИСЛО — ИСТИНА, результат ЕЧИСЛО — ЛОЖЬ, возвращается пустая строка.
Используемые относительные функции:
- SEARCH:
- Функция ПОИСК может помочь вам найти позицию определенного символа или подстроки в заданной текстовой строке.
- IF:
- Функция ЕСЛИ используется для проверки определенного условия, возвращает соответствующее значение, которое вы указали для ИСТИНА или ЛОЖЬ.
- ISNUMBER:
- Функция ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.
Другие статьи:
- Проверить, равно ли ячейка любому значению в списке
- Например, у меня есть список текстовых строк в столбце A, и теперь я хочу проверить, равно ли значение ячейки в этом столбце любому значению в другом списке столбца E. Если равно, отобразить ИСТИНА, в противном случае отображается значение ЛОЖЬ. как показано ниже.
- Проверьте, содержит ли ячейка один из многих элементов
- В этом руководстве представлена формула, позволяющая проверить, содержит ли ячейка одно из нескольких значений в Excel, а также объясняются аргументы в формуле и принцип работы формулы.
- Проверьте, содержит ли ячейка определенный текст
- Здесь в руководстве представлены формулы, позволяющие проверить, содержит ли ячейка определенный текст и вернуть значение ИСТИНА и ЛОЖЬ, как показано на скриншоте ниже, а также объясняются аргументы и принцип работы формул.
Заполняем аргументы функции
Как сравнить данные в двух таблицах
Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.
Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.
Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.
Исходные данные (таблицы, с которыми будем работать):
Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:
В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.
Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.
Выбираем формат и устанавливаем, как изменятся ячейки при соблюдении формулы. Лучше сделать заливку цветом.
Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).
Здесь вместо первой и последней ячейки диапазона мы вставили имя столбца, которое присвоили ему заранее. Можно заполнять формулу любым из способов. Но с именем проще.
Использование If ElseIf
Инструкция ElseIf позволяет вам выбирать из нескольких вариантов. В следующем примере мы печатаем баллы, которые находятся в диапазоне.
Важно понимать, что порядок важен. Условие If проверяется первым
Если это правда, то печатается «Высший балл», и оператор If заканчивается.
Если оно ложно, то код переходит к следующему ElseIf и проверяет его состояние.
Давайте поменяемся местами If и ElseIf из последнего примера. Код теперь выглядит так
В этом случае мы сначала проверяем значение более 75. Мы никогда не будем печатать «Высший балл», потому что, если значение больше 85, это вызовет первый оператор if.
Чтобы избежать подобных проблем, мы должны использовать два условия. Они помогают точно указать, что вы ищете, чтобы избежать путаницы. Пример ниже показывает, как их использовать. Мы рассмотрим более многочисленные условия в разделе ниже.
Давайте расширим оригинальный код. Вы можете использовать столько операторов ElseIf, сколько захотите. Мы добавим еще несколько, чтобы учесть все наши классификации баллов.
3 ответа 3
В дополнение к уже опубликованному ответу:
Можно использовать оператор like :
Данный оператор чувствителен к регистру, поэтому если нужно опустить чувствительность, можно использовать один из двух вариантов:
- Option Compare Text
Использовать Lcase либо Ucase , т.е. на вашем примере может выглядеть так:
if Lcase(«йЦуКе») like Lcase(«*ЙцУ*») then .
В VBA для этих целей есть функция InStr. Используется как
где start — необязательный параметр, указывающий начальную позицию для поиска (по умолчанию с первого символа) string1 — строка, в которой осуществляется поиск string2 — искомая строка compare — необязательный параметр, указывающий метод сравнения (бинарное, текстовое или сравнение информации в базах данных (только для Access)). По умолчанию используется бинарное сравнение.
Возвращаемые значения функции: NULL — если любая из строк NULL , 0 — если string1 пустая строка либо string2 не найдена, либо start больше длины строки string1 , start — если string2 пустая строка, если строка string2 найдена внутри строки string1 , то возвращает позицию, в которой найдено совпадение.
Повторюсь, но все же. InStr, Like — для строки. Если же массив строк, то используется Filter
Filter ( , ) — просматривает массив строковых значений и ищет в нем все подстроки, совпадающие с заданной строкой. Эта функция имеет четыре аргумента: — искомая строка; — параметр (boolean значение), который указывает, будут ли возвращаемые строки включать искомую подстроку или, наоборот, возвращаться будут только те строки массива, которые не содержат искомой строки в качестве подстроки; — параметр, определяющий метод сравнения строк.
Функция ПСТР и ее пример в использования условном форматировании
Чтобы легко понять, как удалось экспонировать цветом определенные значения с помощью условного форматирования разберем этапы действий в двух словах. Сначала мы извлекаем часть текста, а потом сравниваем его с требуемым значением
Но как из ячейки извлечь часть текста в Excel? Обратим внимание на функцию =ПСТР() в формуле правила. Данная функция возвращает часть строки, взятой с каждой ячейки столбца A, что указано в первом аргументе
Ссылка в первом аргументе должна быть относительной, так как формула применяется к каждой ячейке столбца A. Во втором аргументе функции указывается номер символа исходного текста, с которого должен начаться отрезок строки. В третьем аргументе указывается количество символов, которые нужно взять после определенного (во втором аргументе) символа исходного текста. В результате функция =ПСТР() возвращает только часть текста длинной 4 символа взятого начиная с 5-ой буквы в каждой ячейки из столбца А. Это «2005» или «2006». После функции стоит оператор сравнения к значению строки «2006». Если ячейка содержит такую часть текста значит ей будет присвоен новый формат.
Аналогичным способом можно использовать и другие текстовые функции в условном форматировании. Например, с помощью функции =ПРАВСИМВ() мы можем экспонировать цветом определенную группу товаров из партии C. Для этого нужно использовать формулу:
Здесь все просто функция позволяет выбрать часть текста из ячейки Excel, начиная с правой стороны исходного текста. Количество необходимых символов указано во втором аргументе функции ПРАВСИМВ. После чего все что возвращает функция сравнивается с значением строки «C».
Во всех выше описанных примерах очень легко применять текстовые функции в условном форматировании так как длина строк в исходных данных одинаковая. Но что, если у нас в исходных данных разного типа индикаторы с разной длинной символов, а нам все еще нужно выделять 2006-й год или группу «C».
В решении данной задачи нам поможет дополнительная текстовая функция в формуле =НАЙТИ(). В первом случаи формула будет выглядеть так:
Для удобного выделения идентификаторов с разной длинной текстовой строки товаров из группы «C» используем такую формулу:
Функция =НАЙТИ() выполняет поиск фрагмента текста в ячейке Excel. Потом возвращает номер символа, в котором был найдет текст, который задан в первом аргументе. Во втором аргументе указываем где искать текст. А третий аргумент – это номер позиции с какого символа вести поиск в исходном тексте. Третий аргумент позволяет нам смещаться по строке. Например, если в идентификаторе 2 раза используется символ «C». В таком случае третий аргумент пользователь задает в зависимости от ситуации.
Так как функция возвращает нужное число мы прекрасно используем ее в качестве аргументов для других функций (ПСТР и ПРАВСИМВ).
Задача 1 (поиск значений в списке, совпадающих с единственным критерием)
Выделим ячейки, содержащих значения, удовлетворяющих 1 критерию (без ). Критерий введем в ячейку B
6
. Кроме того, зададим 4 Типа поиска:
- Точно совпадает (выделение ячеек, содержащих значения в точности совпадающих с искомым текстом);
- Содержит (выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки);
- Начинается (выделение ячеек, которые содержат искомый текст в начале строки);
- Заканчивается (выделение ячеек, которые содержат искомый текст в конце строки).
В качестве примера Критерия используем текст «Дрель
», который введем в ячейку B
6
. Естественно, для критерия можно использовать любой другой текст.