Подстановочные знаки (символы *, ? и ~) в Excel

Рассмотрим применение подстановочных знаков в Excel (символы звездочки «*», тильды «~» и вопросительного знака «?») и их использование при поиске и замене текстовых значений.


Приветствую всех, дорогие читатели блога TutorExcel.Ru.

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

  • * (звездочка); Обозначает любое произвольное количество символов.
    Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
  • ? (вопросительный знак); Обозначает один произвольный символ.
    К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.
  • ~ (тильда) с последующими знаками *, ? или ~. Обозначает конкретный символ *, ? или ~.
    Например, поиск по фразе «хор*» найдет все фразы начинающиеся на «хор» («хоровод», «хорошо» и т.д.). Поэтому для точного поиска «хор*» нужно использовать символ «~» и искать по фразе «хор~*». Наличие «~» гарантирует, что Excel прочитает следующий символ как текст, а не как подстановочный знак.

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

Фильтрация данных

Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

Список сотрудников
Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Применение фильтра
Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

Фильтрация данных
В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.).
К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст).
Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).

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

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

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

Сравнение текста
Как и в первом примере, в результате мы получили ровно 3 фамилии.

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

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

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

Инструмент «Найти и заменить»

Подстановочные знаки в Excel также можно использовать для поиска и замены текстовых значений в инструменте «Найти и заменить» (комбинация клавиш Ctrl + F для поиска и Ctrl + H для замены).

Рассмотрим пример. Имеется список продукции магазина, в котором нам нужно найти продукт «молоко».
Предположим, что при вводе данных сделали ошибки из-за чего в списке появились продукты «малоко».

Чтобы несколько раз не искать данные по словам «молоко» или «малоко», при поиске воспользуемся критерием «м?локо» (т.е. вторая буква — произвольная):

Инструмент "Найти и заменить"
При этом не стоит забывать, что с помощью данного инструмента можно не только искать текст, но и заменять его (к примеру, заменить «м?локо» на «молоко»).

Как заменить звездочку «*» в Excel?

Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст.
Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст.
Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст «~*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:

Удаление звездочки
Аналогичная ситуация и при замене или удалении вопросительного знака и тильды.
Производя замену «~?» (для тильды — «~~») мы также без проблем сможем заменить или удалить спецсимвол.

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru

Поделиться с друзьями:
Поиск по сайту:

Комментарии (11)

  1. Валерий

    Пробую в Excel найти номер телефона: 1234567890. Сам номер неизвестен, но известно что он состоит из 10-ти знаков и находится один в ячейке без дополнительных слов и знаков. Даю маску поиска: ?????????? — т.е. покажи десять любых знаков в отдельной ячейке. НЕ НАХОДИТ! ПОЧЕМУ?

    1. tutorexcel Автор поста

      С такой маской Excel будет искать не ровно 10 знаков, а все ячейки где есть хотя бы 10 знаков (т.е. 10, 11, 12, … знаков).

      1. Алексей

        Какую формулу и маску использовать если в ячейке есть текст и номер, но мы знаем что номер состоит из 4 цыфр?

        1. tutorexcel Автор поста

          Формально подстановочные знаки в Excel не различают какой именно текст перед ними(буквы или цифры), поэтому маской такую комбинацию не задать.

  2. Вадим

    Подстановочные знаки (символы *, ? и ~) хорошо работают для поля «Найти:», а можно ли их использовать в поле «Заменить:»

    Например: диапазон из 3 ячеек имеет текст:
    2019
    2017
    2018

    Как с получить результат:
    2019 г.
    2017 г.
    2018 г.

    Спасибо.

  3. Дмитрий Марков

    Число из текста в ячейке A1:

    =ЛЕВСИМВ(A1;4)
    =ПОДСТАВИТЬ(A1;» г.»;»»)
    {=МАКС(ЕСЛИОШИБКА(—ПСТР(A1;СТРОКА($1:$6);СТОЛБЕЦ(A:L));))}

    …, что Вам больше нравится

  4. Анастасия

    =ЕСЛИ(D3/B3>СРЗНАЧ($D$3/$B$3;$D$4/$B$4;$D$5/$B$5;$D$6/$B$6;$D$7/$B$7;$D$8/$B$8;$D$9/$B$9);A3;» «)

    хочу сжать то, что написано в скобках СРЗНАЧ, чтобы Эксель автоматически выполнял это действие,.

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

    =ЕСЛИ(D3/B3>СРЗНАЧ($D$3/$B$3;;$D$9/$B$9);A3;» «) но не знаю какой

  5. Михаил

    Добрый день , задача простая, но не смог найти решения. Есть большое кол-во значений в столбце , необходимо найти значение только чисел с значением до сотых. Т.е не округлить, а именно найти (пример 2,02 подходит 2,022 не подходит) . В идеале вывод в сводную таблицу или поиск значения наиболее близкого к заданному.

    1. tutorexcel Автор поста

      Подстановочные знаки не различают что перед ними (буква или цифра), поэтому найти именно число с их помощью не получится.
      Но к Вашей задаче можно попробовать подойти с другой стороны.
      Если нужно найти все числа с сотыми долями, то у всех таких чисел 3 знак с конца обязательно запятая (или точка, в зависимости от того, что используется для разделения).
      Поэтому с помощью формулы =ЕСЛИ(ЕЧИСЛО(A1)=ИСТИНА;ЕСЛИ(ПСТР(A1;ДЛСТР(A1)-2;1)=»,»;1;0);0) мы сначала проверяем число ли в ячейке (A1), а потом проверяем какой знак стоит третьим с конца, если запятая, то число подходит, в остальных случаях нет.

  6. Антон

    Добрый день! Подскажите, пожалуйста, как в в столбце с номерами телефонов найти и удалить те номера, которые короче, чем 11 знаков?
    Спасибо!

    1. tutorexcel Автор поста

      Антон, добрый день!
      Можно с помощью функции ДЛСТР (определяет длину строки) сделать проверку на длину номера, если значение меньше 11, то удаляем, если больше то оставляем.
      Если именно через подстановочные знаки решать эту задачу, то попробуйте в поиске ввести 11 знаков «?», Excel найдет все номера где 11 и более символов, все остальное тогда можно удалять.

Добавить комментарий

Ваш адрес email не будет опубликован.

Читайте ранее:
Как преобразовать формулу в текст в Excel?

Познакомимся с вариантами преобразования формулы Excel в текст (в результате получается не значение ячейки, а формульное выражение в текстовом виде,...

Закрыть