Подстановочные знаки в excel

Подстановочный символ – это вводимый с клавиатуры символ, ( например звездочка * или вопросительный знак ? ) который можно использовать для представления одного или нескольких других символов при поиске файлов или папок. Подстановочные символы часто используются вместо одного или нескольких символов, когда нужный символ неизвестен либо для того, чтобы не вводить имя полностью.

Звездочка (*)

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

В диалоговом окне Результаты поиска будут найдены все файлы любого типа, имена которых начинаются на «gloss», включая файлы Glossary.txt, Glossary.doc и Glossy.doc. Чтобы ограничить поиск конкретным типом файлов, введите:

В этом случае в диалоговом окне Результаты поиска будут найдены все файлы с именами, начинающимися на «gloss», имеющие расширение .doc, такие как Glossary.doc и Glossy.doc.

Вопросительный знак (?)

Вопросительный знак можно использовать для замены одного символа в имени. Например, если ввести gloss?.doc , в диалоговом окне Результаты поиска будет найден файл Glossy.doc или Gloss1.doc, но не Glossary.doc.

Справка © Microsoft Windows 2000

Microsoft Excel

Собственно, цитата из справки приведена потому, что в Excel’е также возможно использование символов подстановки, и далее приведены функции Excel’я которые их поддерживают. От себя лишь добавлю, что если нужно найти именно знак вопроса ? или звездочку * , то имеет смысл ознакомиться с данным советом [FAQ]

Поиск (меню Правка команда Найти или CTRL + F)

Замена (меню Правка команда Заменить или CTRL + H)

Автофильтр (меню Данные пункт Фильтр команда Автофильтр )

Расширенный фильтр (меню Данные пункт Фильтр команда Расширенный фильтр )

Visual Basic for Application

В VBA, кроме всего вышеперечисленного, применение символов подстановки, также поддерживают :

Об’ект Application.FileSearch (только MS Excel 97, 2000, XP, 2003)

Об’ект File System Object (только некоторые методы)

Подстановочные знаки (* и ?) можно использовать в Условном форматировании , условиях отбора Расширенного фильтра , в ряде функций, а также при поиске и замене ТЕКСТовых значений штатными средствами EXCEL.

Если имеется диапазон с текстовыми значениями и необходимо произвести поиск или подсчет этих значений на основании какого-нибудь критерия, то использование подстановочных знаков (* и ?) существенно расширяет возможности поиска.

Подстановочный знак ? (знак вопроса) означает один любой символ, а знак * (звездочка) – последовательность символов любой длины.

Используйте

Чтобы найти

Один из символов Пример: условию «бар?н» соответствуют результаты «барин», «барон» и «баран»

Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»

(тильда), за которой следует ?, * или

Вопросительный знак, звездочку или тильду. Пример: по условию поиска «ан06

?» будет найдено «ан06?»

Использование в функциях

Предположим, что стоит задача подсчета ячеек, содержащих все склонения слова молоток (например, молотку, молотком, молотка и пр.). Для решения этой задачи проще всего использовать критерии отбора с подстановочным знаком * (звездочка). Идея заключается в следующем: для условий отбора текстовых значений можно задать в качестве критерия лишь часть текстового значения (для нашего случая, это – молот) , а другую часть заменить подстановочными знаками. В нашем случае, критерий будет выглядеть так – молот*.

Предполагая, что текстовые значения (склонения слова молот ) находятся в диапазоне А2:А10 , запишем формулу =СЧЁТЕСЛИ(A2:A10;"молот*")) . В результате будут подсчитаны все склонения слова молоток в диапазоне A2:A10 .

Нижеследующие функции позволяют использовать подстановочные знаки:

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

Описание применения подстановочных знаков в вышеуказанных функциях описано соответствующих статьях .

Использование в инструменте Найти…

Также подстановочные знаки можно использовать в стандартном инструменте Найти… ( Главная/ Редактирование/ Найти и выделить/ Найти… или нажать CTRL+F )

На рисунке выше показано, как найти ячейки, содержащие слово из пяти букв, первые 4 из которых фраза "дрел".

Использование в Расширенном фильтре

Использование подстановочных знаков в Расширенном фильтре описано в этой статье .

Использование в Условном форматировании

Использование подстановочных знаков в правилах Условного форматирования описано в этой статье .

Функция СУММЕСЛИ, пожалуй, одна из самых полезных и часто используемых в Excel. У этой функции есть недостаток. Для суммирования значений с помощью этой функции критерий должен совпадать символ в символ.

Например, представим, что вас зовут Иван Петров и у нас есть таблица с данными продаж, а также фамилиями и именами продавцов. Если мы будем знать только ваше имя, то мы не сможем суммировать данные из этой таблицы с помощью функции СУММЕСЛИ только по имени. Нам потребуется знать вашу фамилию.

Это проблема. Но у нас есть решение в виде подстановочных знаков.

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

Какие подстановочные знаки наиболее часто встречаются?

Итак, давайте теперь рассмотрим как подстановочные знаки помогут нам с суммированием данных при использовании функции СУММЕСЛИ.

Подстановочный знак “звездочка” [*] и функция СУММЕСЛИ

“Звездочка” это самый популярный подстановочный знак. Она используется для поиска любой строки знаков.

Например, если в запросе будет указано “г*д”, то поиску будут соответствовать результаты “год”, “город”, “грейд” и т.д.

Если “звездочка” будет стоять в конце запроса, например “Иван*”, то результату поиска будут соответствовать значения “Иван Маслов”, “Иван Мельников”, “Иван Петров”, “Иванвадпловапло” и т.д.

Рассмотрим работу подстановочного знака и функции СУММЕСЛИ на примере. Представим, что у нас есть таблица со счетами по трем товарам с суммами выплат. Наша задача посчитать сумму поступлений по каждому продукту, на основе оплаченных счетов.

Даже если мы составим сводную таблицу на основе этих данных, мы не сможем суммировать данные по отдельным продуктам.

Для решения этой задачи мы выпишем в отдельную таблицу перечень продуктов, а в соседнем столбце пропишем формулу с функцией СУММЕСЛИ и подстановочным знаком “звездочка”:

В этой формуле мы для аргумента “критерий” указали ссылку на ячейку с названием товара и добавили с помощью знака “&” подстановочный знак “звездочка”. Excel, при таком запросе будет считать, что любое значение в таблице, начинающееся с названия продукта соответствует результату поиска. При этом не важно, какие символы будут указаны в таблице после названия продукта.

Вопросительный знак и функция СУММЕСЛИ

Вопросительный знак также может эффективно использоваться с функцией СУММЕСЛИ для поиска данных.

Вопросительный знак используется для подстановки любого одного символа. Например, если поисковый запрос будет “г?д”, то результатом могут быть слова “гид”, “гад”, “год”.

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

Формула с функцией СУММЕСЛИ будет выглядеть так:

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

Итак, подстановочные знаки “звездочка” и вопросительный знак эффективно использовать вместе с функциями СУММЕСЛИ и СУММЕСЛИМН:

Когда вы понимаете что в таблице, данные которой вы хотите суммировать, могут содержаться значения не полностью совпадающие с критерием поиска. В таких случаях используйте подстановочный знак “звездочка” [*].
Когда вы понимаете, что в таблице есть несколько знаков, которые могут отличаться от критерия поиска. В таких случаях используйте подстановочный знак [?]