В прошлой статье мы начали изучать функцию ЕСЛИ, посмотрели как она работает с числовыми, а также с текстовыми значениями. Сегодня разовьём тему работы с текстами. Приступим к следующим примерам.
Пример 3: Точное совпадение текста
В прошлый раз мы говорили, что функция ЕСЛИ не чувствительна к регистру текста, то есть ей всё равно используете вы заглавные или строчные буквы. В большинстве случаев этого будет достаточно для наших задач. Но что, если нам всё-таки понадобится учитывать регистр?
Вернёмся к нашим покупателям из прошлых примеров. Вы также можете скачать файл с готовыми новыми примерами по ссылке. Или пробуйте всё делать своими руками.
Допустим, покупатели заходили на наш сайт и вводили разные промокоды (например, XY, XX, xx). Нам нужно выбрать тех покупателей, которые ввели промокод XX, чтобы, например, одобрить им членство в закрытом клубе покупателей с дополнительными привилегиями.
Как же нам это сделать, если функция ЕСЛИ игнорирует регистр и когда мы с помощью неё будем искать XX, она также будет принимать и xx? На помощь нам придёт ещё одна функция - СОВПАД.
Функция СОВПАД сравнивает две текстовые строки и если они полностью совпадают, включая регистр, то возвращает ИСТИНА, а если нет, то ЛОЖЬ.
Это очень простая функция, вам нужно только указать её для сравнения две текстовые строки:
=СОВПАД(текст1; текст2)
Попробуйте сами используя в связке функции ЕСЛИ и СОВПАД сделать формулу, которая бы выбирала только код XX и в столбце "Одобрение" возвращала бы "Да" в случае одобрения и "Нет" в случае отказа. Давайте проверим, правильно ли у вас получилось.
В ячейку C2 введём следующую формулу:
=ЕСЛИ(СОВПАД(B2;"XX");"ДА";"Нет")
Расшифруем: если текст в ячейке B2 полностью совпадает с текстом "XX", то пишем "Да", иначе пишем "Нет".
Вот что у нас получилось:
Теперь давайте усложним задачу.
Пример 4: Частичное совпадение текста
А что если нам надо сопоставить только часть текста из ячейки?
У нас есть список кодов заказов, который состоит из наборов цифр и символов. Буквенные символы указывают, кто сделал заказ (XX - участник нашего клуба покупателей, XY - внешний покупатель). В столбце B нам нужно расшифровать тип заказчика: "Участник" или "Внешний".
На первый взгляд задача нетривиальная. Как вы думаете, что нам мешает использовать ранее изученные функции для её решения?
Во-первых, это циферно-буквенная смесь. Во-вторых, длина строк разная, то есть количество символов в каждой ячейке отличается. И, в-третьих, нужный нам буквенный код в каждом случае "бегает" по строке. Обычный пользователь Excel махнул бы рукой и начал бы вручную смотреть какой код в строке и заполнять столбец B.
Но мы ведь с вами собираемся стать продвинутыми пользователями, правда? Поэтому ручной метод - это не наш путь. Что, если таких кодов тысячи и десятки тысяч? Благо Excel очень мощная программа, которая позволяет с лёгкостью автоматизировать даже такие непростые на первый взгляд задачи.
Многие функции в Excel, такие как ВПР или СЧЁТЕСЛИМН, позволяют использовать символы подстановки (* звездочку, ? вопросительный знак и ~ тильду) для фильтрации данных в строке. Но функция ЕСЛИ не позволяет использовать эти символы. Поэтому о них мы поговорим позднее. а пока нам надо придумать альтернативный способ.
Для поиска буквенного кода в строке мы будем использовать, как ни странно, функцию ПОИСК. Задача функции ПОИСК - найти текст внутри другой текстовой строки у казать его местоположение в этой строке.
У функции ПОИСК три аргумента:
=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])
- Искомый текст: Какой текст вы ищете.
- Текст для поиска: Где искать данный текст.
- [Начальная позиция]: С какой позиции (заданной номером индекса) в строке текст_для_поиска начинать поиск. Если этот аргумент опущен, то поиск начнётся с первого символа.
Введём в ячейку B2 и протянем формулу, которая будет искать местоположение кода "XX":
=ПОИСК("XX";A2)
Вот что у нас получилось:
Смотрите, там где в коде заказа было "XX", формула вернула номер позиции, с которой начинается в строке искомый код. Например, в "XX2962" с первого знака, а в "485XX" с четвёртого. А там, где "XX" не встречается, формула возвращает ошибку #ЗНАЧ! Кстати, обо всех ошибках формул Excel вы можете прочитать здесь.
Небольшая дополнительная информация. Можно также использовать функцию НАЙТИ. Разница между НАЙТИ и ПОИСК заключается в том, что НАЙТИ чувствительна к регистру текста, а ПОИСК - нет. Но более подробно о всех функциях работы с текстом мы поговорим позднее.
Итак, с помощью функции ПОИСК мы нашли индексный номер первого символа кода "XX" там где он есть.
Затем мы используем ещё одну новую функцию ЕЧИСЛО, для того, чтобы преобразовать полученную информацию в ИСТИНУ или ЛОЖЬ. ЕЧИСЛО проверяет, является ли значение число, и если да, то возвращает ИСТИНА, если нет - ЛОЖЬ.
Доработаем нашу формулу:
=ЕЧИСЛО(ПОИСК("XX";A2))
Расшифровка: если в ячейке A2 находится строка "XX" и возвращается в виде числа номер позиции с которой она начинается, то ИСТИНА, иначе ЛОЖЬ.
Но для простого восприятия нам проще работать с обычными словами, как, например, "Участник" или "Внешний", а не с логическими переменными ИСТИНА и ЛОЖЬ. Поэтому мы добавим в нашу формулу функцию ЕСЛИ:
=ЕСЛИ(ЕЧИСЛО(ПОИСК("XX";A2));"Участник";"Внешний")
Расшифровка: если в ячейке A2 находится строка "XX" и возвращается в виде числа номер позиции с которой она начинается, то ИСТИНА, иначе ЛОЖЬ. Если ИСТИНА, то пишем "Участник", если ЛОЖЬ, то пишем "Внешний".
Уффф, какая сложная многоуровневая формула. Давайте проверим, работает ли она?
Всё работает идеально!
Предлагаю сделать паузу. Сегодня мы продолжили изучать тонкости функции ЕСЛИ, а также познакомились с несколькими новыми: СОВПАД, ПОИСК, НАЙТИ и ЕЧИСЛО. Скажу без иронии, уже с таким арсеналом знаний вы стали намного более продвинутым пользователем Excel, чем многие ваши коллеги. Что же будет, если вы подпишетесь и продолжите изучение дальше? ;)
В следующий раз мы продолжим изучать функцию ЕСЛИ и посмотрим, как с помощью неё проверять значения даты, а также пустые и незаполненные ячейки.
Не переключайтесь;)
