Разбираемся, зачем нужны и как применять символы подстановки в аналитике и не только
Можно долго работать с таблицами, но не использовать их возможности на максимум (вручную искать по ключевым словам, стандартно использовать фильтр или суммировать значения через простые формулы). Все эти задачи можно упростить подстановочными знаками.
В статье разберемся, что такое подстановочные знаки и как применять их на практике. А также поделимся конкретными примерами использования инструмента.
Подстановочные знаки (символы подстановки, 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»)
Пояснение к формуле:
На примере ниже зеленым цветом выделено частичное выражение, которое является критерием поиска.
А теперь представим, что нам нужно запланировать сумму денег на обзвон участников будущего мероприятия. Но сначала — выяснить точное число звонков.
Задача: посчитать количество участников конференции, которым можно позвонить и пригласить на афтепати.
Для этого мы заранее подготовили таблицу. В столбце 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; ЛОЖЬ)
Каждый элемент формулы означает:
Предположим, вы хотите найти определенного клиента в базе данных. Вы не помните фамилию, но уверены, что она начинается с «Анд».
Чтобы вернуть фамилию из столбца 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 на всех листах, чьи названия начинаются с «Отдел» — независимо от того, что идёт дальше: Отдел_Маркетинг, Отдел_Продажи, Отдел_Аналитика.
Бывают списки с данными, где внутри ячейки указаны порядковые номера и, например, ФИО или любые другие значения. Чаще всего приходится очищать такие списки вручную.
Чтобы быстро очистить диапазон ячеек от порядковых номеров и точек, нужно:
Готовый список будет содержать только имена без цифр.
Здесь разберем как меняется поведение подстановочных знаков, когда их используют в формулах. И в Excel, и в Google Таблицах подстановочные знаки работают во всех функциях:
Например, одинаково сработает формула СЧЁТЕСЛИ:
=СЧЁТЕСЛИ(A1:A10;»*@gmail.com»)
Обе системы поймут, что нужно посчитать ячейки, заканчивающиеся на @gmail.com.
Но не сработает формула, где нужно использовать несколько листов. Например, в Google Таблицах не подействует формула:
=СУММ(Лист1:Лист5!A2)
Также Excel может обрабатывать большие массивы данных, а Google Таблицы — нет.
Символы подстановки (*, ?, ~) помогают работать с неструктурированными или разнородными данными. Ниже — основные преимущества и ограничения, которые важно учитывать при использовании подстановочных знаков.
Позволяют искать значения по шаблону. Символ «*» заменяет любое количество символов. Это удобно для поиска строк, которые начинаются, заканчиваются или содержат определённую часть.
Пример: SKU-A* найдёт всё, что начинается с SKU-A.
Помогают в работе с «неидеальными» данными. Если данные в таблице записаны с разными вариантами формулировок, подстановочные знаки позволяют их объединить.
Пример: фраза *ябл* найдёт и «сок яблочный», и «яблоко», и «ябл. 1л».
Работают в разных инструментах. Поддерживаются во многих функция в Excel и Google Таблицах: СЧЁТЕСЛИ, ПОИСК, ВПР, правилах условного форматирования, фильтрах.
Ускоряют проверку и анализ данных. Позволяют находить и обрабатывать группы строк без вспомогательных столбцов и макросов. Это особенно полезно при подготовке отчётов или сводных данных.
Не работают, если данные записаны в разном формате. Если часть значений в текстовом виде, а часть — в числовом, поиск по шаблону может не сработать. Перед применением подстановочных знаков нужно привести столбец к единому формату.
Не работают во всех функциях. Некоторые функции, например, НАЙТИ или СОВПАД, не распознают подстановочные знаки. В них «*» и «?» считаются обычными символами. А функция Excel «СУММПРОИЗВ» в принципе не поддерживают подстановочные знаки.
Чувствительность к пробелам и невидимым символам. Подстановочные знаки не игнорируют лишние пробелы или невидимые символы в строках.
Читайте только в Конверте
Отправляйте красивые письма, делитесь классным контентом, привлекайте больше платящих клиентов. До 1 500 писем бесплатно.
Искренние письма о работе и жизни, эксклюзивные кейсы и интервью с экспертами диджитала.
Проверяйте почту — письмо придет в течение 5 минут (обычно мгновенно)
