Подстановочные знаки в Excel (эксель): *, ? и ~ в формулах

В Excel есть три подстановочных знака для условий и поиска: * соответствует любому числу символов (в том числе нулю), ? ровно одному символу, а ~ превращает следующий * или ? обратно в обычный символ. =СЧЁТЕСЛИ(A2:A7;"*apple*") считает ячейки, в которых apple встречается в любом месте. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой, как здесь.

ФормулыСбросить

D2fx

=COUNTIF($A$2:$A$7,C2)

	A	B	C	D
1	Product		Pattern	Count
2	Apple juice		*apple*	4
3	Green apple		apple*	2
4	Pineapple		*juice	2
5	Orange juice		?????	0
6	Pear		*e	4
7	Apples

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЧЁТЕСЛИ($A$2:$A$7;C2)

• *apple* содержит apple: 4 совпадения, потому что Pineapple тоже подходит.
• apple* начинается с apple: только Apple juice и Apples. СЧЁТЕСЛИ не учитывает регистр.
• *juice заканчивается на juice.
• ????? ровно пять символов. Ни в одном из этих товаров нет пяти символов, поэтому 0; введите Peach в A6, и получится 1.
• *e заканчивается на e.

Введите свой шаблон в столбце C, например *an* или P*, и количество обновится.

Частичное совпадение в ВПР и ПРОСМОТРX

ВПР принимает подстановочные знаки в режиме точного совпадения (ЛОЖЬ в последнем аргументе). Присоедините знак к значению прямо в формуле, тогда в D2 достаточно ввести только первые буквы:

ФормулыСбросить

E2fx

=VLOOKUP(D2&"*",A2:B6,2,FALSE)

	A	B	C	D	E
1	Product	Price		Starts with	Price
2	Apple juice	3.5		Pin	4
3	Green apple	1.2			4
4	Pineapple	4
5	Orange juice	3.2
6	Pear	0.9

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ВПР(D2&"*";A2:B6;2;ЛОЖЬ)

Обе формулы находят Pineapple. Замените D2 на juice: шаблону ВПР juice* нужен текст, который начинается с juice, поэтому она возвращает #Н/Д (по-английски #N/A; таблицы на этой странице показывают ошибки под английскими именами), а шаблон ПРОСМОТРX *juice* находит первый товар, где это слово есть, Apple juice. Как и любое точное совпадение, поиск с подстановочным знаком возвращает первую подходящую строку, поэтому делайте шаблон достаточно точным.

ПРОСМОТРX считает * и ? подстановочными знаками, только когда её пятый аргумент, режим сопоставления, равен 2. Без него она ищет звёздочку буквально. ПОИСКПОЗ принимает подстановочные знаки с типом сопоставления 0, а ПОИСКПОЗX с режимом 2, как ПРОСМОТРX. Остальные аргументы описаны на странице ВПР.

ФормулыСбросить

D2fx

	A	B	C	D
1	Product			Phone products
2	Smartphone
3	Headphones
4	Phone stand
5	Laptop bag
6	Charger

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

Ваша очередь: В D2 посчитайте товары, в названии которых в любом месте есть phone.

ПроверитьПоказать ответ

Сумма и среднее с подстановочным знаком

Все функции с условиями читают их одинаково, поэтому те же шаблоны работают в СУММЕСЛИ, СУММЕСЛИМН, СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН, МАКСЕСЛИ и МИНЕСЛИ:

ФормулыСбросить

E2fx

=SUMIF(A2:A7,D2,B2:B7)

	A	B	C	D	E
1	Region	Sales		Pattern	Total
2	North-East	120		North*	285
3	North-West	95		*West	155
4	South	80		?????	150
5	South-West	60
6	East	110
7	North	70

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММЕСЛИ(A2:A7;D2;B2:B7)

North* складывает все регионы, начинающиеся с North, включая сам North, потому что * соответствует и пустоте. ????? складывает регионы ровно из пяти символов: South и North.

ФормулыСбросить

E2fx

	A	B	C	D	E
1	Region	Sales			West total
2	North-East	120
3	North-West	95
4	South	80
5	South-West	60
6	East	110
7	North	70

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

Ваша очередь: В E2 посчитайте сумму продаж всех регионов, название которых заканчивается на West.

ПроверитьПоказать ответ

Найти настоящую звёздочку или вопросительный знак через ~

Чтобы посчитать текст, в котором есть настоящий * или ?, поставьте перед ним тильду. Сама тильда записывается как ~~.

ФормулыСбросить

C2fx

=COUNTIF(A2:A5,"*~**")

	A	B	C
1	Note		Count
2	Rated 5*		1
3	Why?		1
4	Done		4
5	5 stars

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЧЁТЕСЛИ(A2:A5;"*~**")

C2 считает ячейки с буквальной звёздочкой (только A2), C3 ячейки с вопросительным знаком. C4 показывает другую сторону: "*" сам по себе соответствует любому тексту, поэтому считает все текстовые ячейки, здесь 4. Числа и пустые ячейки он пропускает, поэтому СЧЁТЕСЛИ(range;"*") обычно и используют для подсчёта текстовых ячеек.

Какие функции принимают подстановочные знаки

ПОИСК принимает подстановочные знаки внутри формулы: =ПОИСК("b?d";"a bad day") возвращает 3 в Excel (подробности на странице НАЙТИ и ПОИСК). Для ФИЛЬТР используйте вместо шаблона условие ЕЧИСЛО(ПОИСК(...)).

Частая ошибка: подстановочный знак после =

Оператор = никогда не читает подстановочные знаки: =A2="*apple*" спрашивает, содержит ли A2 семь символов *apple*. В Excel, если в A2 стоит Green apple:

=A2="*apple*"                    FALSE
=IF(A2="*apple*","yes","no")     no

Перенесите проверку в СЧЁТЕСЛИ, которая для одной ячейки возвращает 1 или 0, или используйте ПОИСК:

ФормулыСбросить

B2fx

=IF(COUNTIF(A2,"*apple*"),"contains apple","no")

	A	B	C
1	Product	COUNTIF test	SEARCH test
2	Green apple	contains apple	contains apple
3	Pear	no	no

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ЕСЛИ(СЧЁТЕСЛИ(A2;"*apple*");"contains apple";"no")

ЕСЛИ считает 1 от СЧЁТЕСЛИ значением ИСТИНА, а 0 значением ЛОЖЬ. Варианту с ПОИСК подстановочные знаки вообще не нужны, потому что ПОИСК и так ищет текст в любом месте ячейки.