Как использовать подстановочные знаки в таблицах (Google и Excel)

Можно долго работать с таблицами, но не использовать их возможности на максимум (вручную искать по ключевым словам, стандартно использовать фильтр или суммировать значения через простые формулы). Все эти задачи можно упростить подстановочными знаками. 

В статье разберемся, что такое подстановочные знаки и как применять их на практике. А также поделимся конкретными примерами использования инструмента.

Что такое подстановочные знаки

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

Подробнее о каждом:

Звёздочка (*) заменяет любое количество символов. Ее также называют астерикс. Можно ставить в конце, в начале и с 2 сторон запроса. Допустим, нам нужно найти все молочные продукты в таблице товаров. Сочетание  «мол*» со звездочкой найдет и «молоко», и «молочный», а также слово «молния».

А по запросу *рекламы, система сама решит: «продажа рекламы» это, «покупка рекламы» или «проданной рекламы». 

Вопрос (?) заменяет ровно один символ. 

«ст?л» поймает «стол» и «стул», но не «стулья» — потому что букв больше.

Тильда (~) помогает искать звездочку, вопрос или тильду не как подстановочный знак. Работает только в сочетании с другими символами подстановки. 

Например, нам надо найти фразу, в которой используется «*» или «?» на конце. Если в поиск вбить фразу «*ко?», получится «ко» + один символ в конце («кол», «ком» и т.д.), А чтобы таблица прочитала следующий символ вопроса как текст, а не как подстановочный знак, нужно использовать выражение «*ко~?» Так вы найдете фразы «сколько?», «количество?» и так далее.

Тильду обычно используют только как вспомогательный инструмент, поэтому в статье рассмотрим основные символы подстановки: звездочку и вопрос.

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

Фильтровать товары с одним префиксом. Представьте таблицу из 10 000 строк. Каждому товару присвоен ID по типу 2598, 2474, 2735, а нужно посмотреть только те, что начинаются на 24. 

Без подстановочных знаков фильтрация покажет все значения, содержащие «24» — нужное сочетание может стоять в конце, в начале и в середине. А вот с символами подстановки в фильтре можно прописать 24* и получить нужный результат. Все, что начинается на 24, окажется в выдаче. 

Сделать подсчет по определенным условиям. Например, чтобы узнать, сколько сотрудников пользуются Gmail, Yandex или корпоративной почтой, используйте звездочку как символ подстановки. Формула =СЧЁТЕСЛИ(A1:A100; "*@yandex.ru") поможет найти нужное число (диапазон и столбец указывайте в зависимости от вашей таблицы).

Найти ошибки и опечатки. К примеру, база состоит из фамилий. Сотрудник обычно записывает себя как Климов, но потом допускает ошибку — теперь его фамилия Клтмов. Если вам надо найти Климова, но он не ищется, поставьте в фильтр Кл?мов. Таблицы покажут все вариации с одной пропущенной буквой. 

Символы подстановки пригодятся и в других ситуациях. Например, при фильтрации данных, условном форматировании, поиске и замене текстовых значений и для сравнения текста.

Как использовать подстановочные знаки

Фильтрация и поиск с символами подстановки

Начнём с самого простого — функций без формул. В Excel фильтр, а также функция «Найти и заменить» понимает звёздочку (*) и вопрос (?). Это значит, что отфильтровать строки по шаблону можно за пару секунд.

Допустим, вы хотите найти все фразы со словом «апельсин», но не уверены, в каком виде оно записано или на каком месте в словосочетании находится. Пропишите фильтр *апельсин* — таблица покажет всё: от «апельсин сочи» до «апельсиновый».

А если работаете с номерами, где важны последние цифры, используйте знак вопроса. Например, с комбинацией «????45» получится найти всё, что заканчивается на «45» и перед этим содержит ровно 4 символа. Поиск чисел сработает как через функцию «найти и выделить», так и через «фильтр». 

Символы подстановки в функции «Найти и заменить» и фильтрации ведут себя по-разному в Excel и Google Таблицах.

Во-первых, через «Найти и заменить» в Google Таблицах символы подстановки не работают. Если вы используете знак вопроса или астериск, поиск c подстановочными знаками не даст результата. 

Во-вторых, подстановочные знаки не распознаются в режиме фильтрации. Google Таблицы позволяют фильтровать данные по условию и значению. Например, можно выбрать «Фильтровать по условию» → «Текст начинается с». Это будет равно фильтрации с астериском на конце в Excel, где мы бы ввели «2*».

Невозможность использовать символы подстановки в некоторых функциях Google Таблиц связана с тем, что «звездочка» и «вопрос» распознаются как регулярные выражения. Например, «звездочка» используется при поиске, когда нужно найти значения с отсутствием символа или с его повторением. Поясним на примере.

Задача: найти имена, начинающиеся со строчной буквы, чтобы исправить ошибки и подготовить ФИО для печати на бейджах.

Чтобы это сделать, используем символы «*» и «^». В поле «Найти» введем «^[а-я].*». В квадратных скобках укажем, что под критерий поиска подходят все строчные буквы от «а» до «я».

В этом случае знак ^ в начале регулярного выражения означает, что результат начинается с указанных в скобках символов. [а-я] говорит о поиске в заданном диапазоне, а * — что символ перед звездочкой может присутствовать в слове, отсутствовать или повторяться несколько раз.

В результате нашего поиска по фамилиям мы найдем ячейки, содержащие слова с маленькой буквы. Например, имена вроде андрей, алексей, мАКС, алеНа.

Если при поиске не нажать галочку «Поиск с использованием регулярных выражений», астериск и другие символы не сработают. (Подробнее о том, как применять регулярные выражения, можно почитать в документации Google.)

Теперь к формулам. Рассмотрим конкретные команды и как внутри них можно применять символы подстановки.

СЧЁТЕСЛИ

Эта функция позволяет посчитать количество ячеек, соответствующих указанному критерию. Вернемся к задаче с поиском email-адресов для понимания, сколько сотрудников пользуются Gmail, Yandex или корпоративной почтой. Чтобы посчитать email-адреса сотрудников, которые пользуются Gmail, мы используем суммирование с условием. В данном случае условие — это домен «gmail.com» и диапазон, в котором необходимо произвести поиск. Так как названия почтовых адресов разные, перед доменом добавим звездочку:

=СЧЁТЕСЛИ(I1:I110; "*@gmail.com")

Пояснение к формуле:

• «I1:I110» — диапазон, где указаны все почты сотрудников;
• «”*@gmail.com”» — критерий для поиска: ищем все Gmail почты.

На примере ниже зеленым цветом выделено частичное выражение, которое является критерием поиска.

А теперь представим, что нам нужно запланировать сумму денег на обзвон участников будущего мероприятия. Но сначала —  выяснить точное число звонков.

Задача: посчитать количество участников конференции, которым можно позвонить и пригласить на афтепати. 

Для этого мы заранее подготовили таблицу. В столбце B — номера телефонов участников, которые оплатили билет, а в столбце C — ответ человека на вопрос, пойдет ли он на афтепати. 

Чтобы посчитать количество людей, которые оставили номер телефона и отметили «да» в столбце C, поможет формула СЧЁТЕСЛИМН. Она применяет критерии к ячейкам в нескольких диапазонах и вычисляет количество соответствий всем критериям. 

Критерий поиска с символом подстановки у нас будет 8* (на восьмерку начинаются все телефонные номера), а условие в этой формуле — согласие на афтепати (участник ответил «да»). Для обоих критериев задаем диапазон от 2 до 196 в соответствующем столбце. Формула будет выглядеть вот так:

=СЧЁТЕСЛИМН(B2:B196;"8*";C2:C196;"да")

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

СУММЕСЛИ

Эта функция суммирует значения ячеек, которые соответствуют заданному условию. 

Представим, что в таблице есть коды товаров (SKU), а также сумма затрат по каждому из кодов. Чтобы узнать, сколько потрачено на несколько товаров с определенным префиксом, используем формулу: 

=CУММЕСЛИ(B2:B13; "SKU-1*"; C2:C13)

Формула просуммирует все значения в заданном диапазоне из столбца C, если в этом же диапазоне в столбце B встречается «SKU-1…».

СРЗНАЧЕСЛИ

Функция «СРЗНАЧЕСЛИ» возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.

Например, чтобы подсчитать среднюю цену по всем товарам с «яблоком» в названии, можно использовать формулу: 

=СРЗНАЧЕСЛИ(A3:A15;"*ябло*";B3:B15)

В итоге если в одном столбце заданного диапазона будут ячейки «яблочный сок», «яблоко 1шт», «яблочный нектар», а в соседней колонке цены, то со звездочкой внутри формулы мы легко подсчитаем среднюю цену по всем «яблочным» позициям.

ПОИСКПОЗ

Представим, что в таблице есть коды товаров и нам нужно найти ячейку со значением, которое подходит под шаблон «SKU-» и 5 любых символов. Функция «ПОИСКПОЗ» предназначена для поиска позиции элемента в диапазоне ячеек и возврата этой позиции. 

Готовая формула будет выглядеть так:

=ПОИСКПОЗ(“SKU-?????";A2:A13;0)

Подстановочный знак «?» отыщет первую строку, где встретится пятизначный код товара. Обратите внимание, после диапазона A2:A13 нужно дополнительно указать метод поиска: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1). 

Так как мы искали точное значение, формула показала, что в третьей строке есть SKU с пятизначным кодом на конце.

ВПР

ВПР — объемная функция, поэтому для начала рекомендуем изучить основы работы с ней в другой статье. Здесь мы не будем углубляться в тонкости и остановимся на ВПР только в контексте подстановочных знаков.

Подстановочные знаки можно вставлять в формулы ВПР, чтобы искать не точные совпадения, а значения, начинающиеся или заканчивающиеся определённым образом.

Для примера возьмем таблицу, где артикулы товаров начинаются с кодов вроде AB1001, AB1002, AC1003. Наша задача — найти в таблице название товара, зная, что его артикул начинается на AB.

Можно использовать подстановочный знак «*» — он заменит любое количество символов. Формула будет такая:

=ВПР("AB*"; A2:C100; 2; ЛОЖЬ)

Каждый элемент формулы означает:

• «AB» — шаблон для поиска: ищем всё, что начинается с AB;
• «A2:C100» — диапазон, где происходит поиск;
• «2» — номер столбца, из которого вернуть значение (в данном случае второй столбец);
• «ЛОЖЬ» — указывает точное совпадение, иначе подстановочные знаки не сработают.

Как использовать символы подстановки на реальных задачах

Поиск текста, начинающегося или заканчивающегося определенными символами

Предположим, вы хотите найти определенного клиента в базе данных. Вы не помните фамилию, но уверены, что она начинается с «Анд».

Чтобы вернуть фамилию из столбца A, используйте следующую формулу подстановочных знаков ВПР:

=ВПР("Анд*";$A$2:$B$10;1;ЛОЖЬ)

Чтобы извлечь лицензионный ключ из столбца B, используйте эту (разница только в индексном номере столбца):

=ВПР("Анд*";$A$2:$B$10;2;ЛОЖЬ)

Подсчет сумм с разных листов

Допустим, вы делаете отчёт по отделам: «Отдел_Маркетинг», «Отдел_Продажи», «Отдел_PR» — и хотите подсчитать общую выручку. 

Например, данные по каждому отделу лежат в одинаковых ячейках «A2».

Как посчитать выручку с разных листов:

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

Формула будет такая: 

='Отдел_Маркетинг'!A2 + 'Отдел_Продаж'!A2 + 'Отдел_Аналитики'!A2

Указать диапазон листов вручную. Если листы идут подряд в книге Excel, можно сократить формулу:

=СУММ(Лист1:Лист5!A2)

Это сложит ячейку A2 на всех листах от Лист1 до Лист5, включая 1 и 5 листы. Главное, чтобы порядок листов в книге соответствовал диапазону.

Использовать подстановочный знак «*». Этот способ подойдет, если все названия листов начинаются одинаково, например, с «Отдел». Тогда можно использовать формулу:

=СУММ('Отдел*'!A2)

Excel просуммирует ячейку A2 на всех листах, чьи названия начинаются с «Отдел» — независимо от того, что идёт дальше: Отдел_Маркетинг, Отдел_Продажи, Отдел_Аналитика. 

Удаление порядковых номеров из диапазона ячеек

Бывают списки с данными, где внутри ячейки указаны порядковые номера и, например, ФИО или любые другие значения. Чаще всего приходится очищать такие списки вручную. 

Чтобы быстро очистить диапазон ячеек от порядковых номеров и точек, нужно: 

• выделить левой клавишей мыши диапазон ячеек с именами;
• нажать сочетание клавиш CTRL + F для вызова меню «Найти и заменить»;
• перейти во вкладку «Заменить»;
• в поле «Найти» ввести значение «*»;
• поле «Заменить на» оставить пустым;
• нажать на кнопку «Заменить все».

Готовый список будет содержать только имена без цифр.

Отличия в формулах

Здесь разберем как меняется поведение подстановочных знаков, когда их используют в формулах. И в Excel, и в Google Таблицах подстановочные знаки работают во всех функциях:

• СЧЁТЕСЛИ, СУММЕСЛИ, СРЗНАЧЕСЛИ;
• ВПР;
• ПОИСК, ПОИСКПОЗ, ЕСЛИ.

Например, одинаково сработает формула СЧЁТЕСЛИ:

=СЧЁТЕСЛИ(A1:A10;"*@gmail.com") 

Обе системы поймут, что нужно посчитать ячейки, заканчивающиеся на @gmail.com. 

Но не сработает формула, где нужно использовать несколько листов. Например, в Google Таблицах не подействует формула: 

=СУММ(Лист1:Лист5!A2)

Также Excel может обрабатывать большие массивы данных, а Google Таблицы — нет.

Подстановочные знаки: минусы и плюсы

Символы подстановки (*, ?, ~) помогают работать с неструктурированными или разнородными данными. Ниже — основные преимущества и ограничения, которые важно учитывать при использовании подстановочных знаков.

Плюсы

Позволяют искать значения по шаблону. Символ «*» заменяет любое количество символов. Это удобно для поиска строк, которые начинаются, заканчиваются или содержат определённую часть.

Пример: SKU-A* найдёт всё, что начинается с SKU-A.

Помогают в работе с «неидеальными» данными. Если данные в таблице записаны с разными вариантами формулировок, подстановочные знаки позволяют их объединить.

Пример: фраза *ябл* найдёт и «сок яблочный», и «яблоко», и «ябл. 1л».

Работают в разных инструментах. Поддерживаются во многих функция в Excel и Google Таблицах: СЧЁТЕСЛИ, ПОИСК, ВПР, правилах условного форматирования, фильтрах.

Ускоряют проверку и анализ данных. Позволяют находить и обрабатывать группы строк без вспомогательных столбцов и макросов. Это особенно полезно при подготовке отчётов или сводных данных.

Минусы

Не работают, если данные записаны в разном формате. Если часть значений в текстовом виде, а часть — в числовом, поиск по шаблону может не сработать. Перед применением подстановочных знаков нужно привести столбец к единому формату.

Не работают во всех функциях. Некоторые функции, например, НАЙТИ или СОВПАД, не распознают подстановочные знаки. В них «*» и «?» считаются обычными символами. А функция Excel «СУММПРОИЗВ» в принципе не поддерживают подстановочные знаки. 

Чувствительность к пробелам и невидимым символам. Подстановочные знаки не игнорируют лишние пробелы или невидимые символы в строках.