Посмотреть видео о подстановочных знаках в Excel
В Excel всего 3 подстановочных символа (звездочка, вопросительный знак и тильда), и с их помощью можно многое сделать.
В этом уроке я покажу вам четыре примера, в которых эти подстановочные знаки Excel - настоящие спасители.
Подстановочные знаки Excel - Введение
Подстановочные знаки - это специальные символы, которые могут занимать любое место любого символа (отсюда и название - подстановочный знак).
В Excel есть три подстановочных символа:
- * (звездочка) - Он представляет собой любое количество символов. Например, Ex * может означать Excel, Excel, Пример, Эксперт и т. Д.
- ? (вопросительный знак) - Он представляет собой один-единственный персонаж. Например, Tr? Mp может означать Трампа или Бродяги.
- ~ (тильда) - Он используется для обозначения подстановочного знака (~, *,?) В тексте. Например, допустим, вы хотите найти точную фразу Excel * в списке. Если вы используете Excel * в качестве строки поиска, он даст вам любое слово, в начале которого есть Excel, за которым следует любое количество символов (например, Excel, Excels, Excellent). Чтобы специально искать excel *, нам нужно использовать ~. Таким образом, наша строка поиска будет выглядеть следующим образом: excel ~ *. Здесь наличие ~ гарантирует, что excel прочитает следующий символ как есть, а не как подстановочный знак.
Примечание: я не встречал много ситуаций, когда вам нужно было бы использовать ~. Тем не менее, это полезная функция.
Теперь давайте рассмотрим четыре замечательных примера, в которых подстановочные знаки выполняют всю тяжелую работу.
Подстановочные знаки Excel - примеры
Теперь давайте рассмотрим четыре практических примера, в которых подстановочные знаки Excel могут оказаться очень полезными:
- Фильтрация данных с использованием подстановочного знака.
- Частичный поиск с использованием подстановочного знака и ВПР.
- Найти и заменить частичные совпадения.
- Подсчитайте непустые ячейки, содержащие текст.
# 1 Фильтрация данных с использованием подстановочных знаков Excel
Подстановочные знаки Excel пригодятся, когда у вас есть огромные наборы данных и вы хотите отфильтровать данные на основе условия.
Предположим, у вас есть набор данных, как показано ниже:
Вы можете использовать подстановочный знак звездочки (*) в фильтре данных, чтобы получить список компаний, начинающихся с алфавита A.
Вот как это сделать:
- Выделите ячейки, которые хотите отфильтровать.
- Перейдите в Данные -> Сортировка и фильтр -> Фильтр. (Сочетание клавиш - Control + Shift + L).
- Щелкните значок фильтра в ячейке заголовка.
- В поле (под параметром «Текстовый фильтр») введите А *
- Щелкните ОК.
Это мгновенно отфильтрует результаты и даст вам 3 названия - ABC Ltd., Amazon.com и Apple Stores.
Как это работает? - Когда вы добавляете звездочку (*) после A, Excel будет фильтровать все, что начинается с A. Это связано с тем, что звездочка (являющаяся подстановочным знаком Excel) может представлять любое количество символов.
Теперь, используя ту же методологию, вы можете использовать различные критерии для фильтрации результатов.
Например, если вы хотите отфильтровать компании, которые начинаются с алфавита A и содержат в себе алфавит C, используйте строку А * С. Это даст вам только 2 результата - ABC Ltd. и Amazon.com.
Если вы используете А? С вместо этого в результате вы получите только ABC Ltd (поскольку между «a» и «c» допускается только один символ)
Примечание: Ту же концепцию можно применить и при использовании расширенных фильтров Excel.
# 2 Частичный поиск с использованием подстановочных знаков и ВПР
Частичный поиск необходим, когда вам нужно найти значение в списке, а точное совпадение отсутствует.
Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите найти компанию ABC в списке, но в списке есть ABC Ltd вместо ABC.
В этом случае нельзя использовать обычную функцию ВПР, так как значение поиска не имеет точного соответствия.
Если вы используете VLOOKUP с приблизительным соответствием, это даст вам неправильные результаты.
Однако вы можете использовать подстановочный знак в функции ВПР для получения правильных результатов:
Введите следующую формулу в ячейку D2 и перетащите ее для других ячеек:
= ВПР ("*" & C2 & "*"; $ A $ 2: $ A $ 8,1; FALSE)
Как работает эта формула?
В приведенной выше формуле вместо использования искомого значения как есть, оно окружено с обеих сторон символом подстановки Excel звездочкой (*) - «*» & C2 & »*»
Это говорит Excel, что ему нужно искать любой текст, содержащий слово в C2. Он может содержать любое количество символов до или после текста в C2.
Следовательно, формула ищет совпадение, и как только оно находит совпадение, она возвращает это значение.
3. Найдите и замените частичные совпадения
Подстановочные знаки Excel довольно универсальны.
Вы можете использовать его в сложных формулах, а также в основных функциях, таких как «Найти и заменить».
Предположим, у вас есть данные, как показано ниже:
В приведенных выше данных регион введен разными способами (например, Северо-Запад, Северо-Запад, Северо-Запад).
Это часто бывает с данными о продажах.
Чтобы очистить эти данные и сделать их согласованными, мы можем использовать «Найти и заменить» с помощью подстановочных знаков Excel.
Вот как это сделать:
- Выберите данные, в которых вы хотите найти и заменить текст.
- На главную -> Найти и выбрать -> Перейти. Откроется диалоговое окно «Найти и заменить». (Вы также можете использовать сочетание клавиш - Control + H).
- Введите следующий текст в диалоговом окне поиска и замены:
- Найти то, что: Север * З *
- Заменить: Северо-Запад
- Нажмите «Заменить все».
Это мгновенно изменит все различные форматы и сделает их совместимыми с Северо-Западом.
Как это работает?
В поле «Найти» мы использовали Север * З * который найдет любой текст, в котором есть слово North и где после него стоит буква «W».
Следовательно, он охватывает все сценарии (Северо-Запад, Северо-Запад и Северо-Запад).
Функция «Найти и заменить» находит все эти экземпляры, меняет их на северо-запад и делает согласованными.
4. Подсчитайте непустые ячейки, содержащие текст.
Я знаю, что вы умны и думаете, что в Excel уже есть встроенная функция для этого.
Ты абсолютно прав!!
Это можно сделать с помощью функции СЧЁТ.
НО… С этим есть одна небольшая проблема.
Часто, когда вы импортируете данные или используете лист других людей, вы замечаете, что есть пустые ячейки, хотя это может быть не так.
Эти ячейки выглядят пустыми, но в них стоит знак = ””. Проблема в том, что
Проблема в том, что функция COUNTA не считает это пустой ячейкой (она считает ее текстом).
См. Пример ниже:
В приведенном выше примере я использую функцию COUNTA для поиска непустых ячеек, и она возвращает 11, а не 10 (но вы можете четко видеть, что только 10 ячеек содержат текст).
Причина, как я уже упоминал, в том, что он не считает A11 пустым (хотя должен).
Но так работает Excel.
Исправление - использовать в формуле подстановочный знак Excel.
Ниже приведена формула, использующая функцию СЧЁТЕСЛИ, которая подсчитывает только те ячейки, в которых есть текст:
= СЧЁТЕСЛИ (A1: A11; "?*")
Эта формула сообщает Excel, что нужно считать, только если в ячейке есть хотя бы один символ.
в ?* комбо:
- ? (знак вопроса) означает наличие хотя бы одного символа.
- * (звездочка) освобождает место для любого количества дополнительных символов.
Примечание: Приведенная выше формула работает, когда в ячейках есть только текстовые значения. Если у вас есть список, содержащий как текст, так и числа, используйте следующую формулу:
= СЧЕТЧИК (A1: A11) -СЧЕТЧИК ПУСТО (A1: A11)
Точно так же вы можете использовать подстановочные знаки во многих других функциях Excel, таких как IF (), SUMIF (), AVERAGEIF () и MATCH ().
Также интересно отметить, что, хотя вы можете использовать подстановочные знаки в функции ПОИСК, вы не можете использовать их в функции НАЙТИ.
Надеюсь, эти примеры дадут вам представление об универсальности и силе подстановочных знаков Excel.
Если у вас есть какой-либо другой инновационный способ его использования, поделитесь им со мной в разделе комментариев.
Вам могут быть полезны следующие руководства по Excel:
- Использование COUNTIF и COUNTIFS с несколькими критериями.
- Создание выпадающего списка в Excel.
- Оператор пересечения в Excel
