Как представить любое число в Excel
Обновлено: 21.11.2024
В Excel есть только 3 подстановочных знака (звездочка, вопросительный знак и тильда), и с их помощью можно многое сделать.
В этом руководстве я покажу вам четыре примера, в которых эти подстановочные знаки Excel являются абсолютным спасением.
Это руководство охватывает:
Подстановочные знаки Excel — введение
Подстановочные знаки — это специальные символы, которые могут стоять на любом месте любого символа (отсюда и название — подстановочный знак).
В Excel есть три подстановочных знака:
- * (звездочка) — обозначает любое количество символов . Например, Ex* может означать Excel, Excels, Example, Expert и т. д. <ли>? (вопросительный знак) — представляет собой один символ. Например, «Tr?mp» может означать «Трамп» или «Бродяга».
- ~ (тильда) — используется для обозначения подстановочного знака (~, *, ?) в тексте. Например, допустим, вы хотите найти точную фразу Excel* в списке. Если вы используете Excel* в качестве строки поиска, это даст вам любое слово, в начале которого есть Excel, за которым следует любое количество символов (например, Excel, Excels, Excellent). Чтобы специально искать excel*, нам нужно использовать ~. Таким образом, наша строка поиска будет excel~*. Здесь наличие ~ гарантирует, что Excel читает следующий символ как есть, а не как подстановочный знак.
Примечание: я не сталкивался со многими ситуациями, когда вам нужно использовать ~. Тем не менее, это полезная функция.
Теперь давайте рассмотрим четыре замечательных примера, в которых всю тяжелую работу выполняют подстановочные знаки.
Подстановочные знаки Excel — примеры
Теперь давайте рассмотрим четыре практических примера, где подстановочные знаки Excel могут быть очень полезны:
- Фильтрация данных с использованием подстановочного знака.
- Частичный поиск с использованием подстановочного знака и функции ВПР.
- Найти и заменить частичные совпадения.
- Подсчет непустых ячеек, содержащих текст.
Подстановочные знаки Excel удобны, когда у вас есть огромные наборы данных и вы хотите отфильтровать данные по условию.
Предположим, у вас есть набор данных, как показано ниже:
Вы можете использовать подстановочный знак звездочки (*) в фильтре данных, чтобы получить список компаний, имена которых начинаются с буквы А.
Вот как это сделать:
Как это работает? – Когда вы добавляете звездочку (*) после A, Excel будет фильтровать все, что начинается с A. Это связано с тем, что звездочка (являющаяся подстановочным знаком Excel) может представлять любое количество символов.
Теперь с той же методологией вы можете использовать различные критерии для фильтрации результатов.
Если вместо этого вы используете A?C, в результате вы получите только ABC Ltd (поскольку между «a» и «c» допускается только один символ)
Частичный поиск необходим, когда нужно найти значение в списке, а точное совпадение отсутствует.
Например, предположим, что у вас есть набор данных, как показано ниже, и вы хотите найти компанию ABC в списке, но в списке есть ABC Ltd вместо ABC.
В этом случае нельзя использовать обычную функцию ВПР, так как искомое значение не имеет точного совпадения.
Если вы используете функцию ВПР с приблизительным совпадением, это даст неправильные результаты.
Однако вы можете использовать подстановочный знак в функции ВПР, чтобы получить правильные результаты:
Введите следующую формулу в ячейку D2 и перетащите ее в другие ячейки:
Как работает эта формула?
В приведенной выше формуле вместо того, чтобы использовать искомое значение как есть, оно обрамлено с обеих сторон подстановочным знаком Excel со звездочкой (*) — «*»&C2&»*»
Это говорит Excel, что ему нужно искать любой текст, который содержит слово в C2. Он может иметь любое количество символов до или после текста в C2.
Следовательно, формула ищет совпадение и, как только находит совпадение, возвращает это значение.
3. Найти и заменить частичные совпадения
Подстановочные знаки Excel весьма универсальны.
Его можно использовать в сложных формулах, а также в основных функциях, таких как поиск и замена.
Предположим, у вас есть данные, как показано ниже:
В приведенных выше данных регион указан по-разному (например, Северо-Запад, Северо-Запад, Северо-Запад).
Так часто бывает с данными о продажах.
Чтобы очистить эти данные и сделать их согласованными, мы можем использовать функцию "Найти и заменить" подстановочными знаками Excel.
Вот как это сделать:
Это мгновенно изменит все различные форматы и сделает их совместимыми с Северо-Западным.
Как это работает?
В поле «Найти» мы использовали параметр «Север*W*», который найдет любой текст, содержащий слово «Север» и букву «W» после него.
Следовательно, он охватывает все сценарии (северо-запад, северо-запад и северо-запад).
Функция «Найти и заменить» находит все эти экземпляры, изменяет их на «Северо-запад» и делает их согласованными.
4. Подсчет непустых ячеек, содержащих текст
Я знаю, что вы умны и думаете, что в Excel уже есть встроенная функция для этого.
Вы абсолютно правы!!
Это можно сделать с помощью функции COUNTA.
НО... есть одна небольшая проблема.
Часто, когда вы импортируете данные или используете чужой рабочий лист, вы замечаете пустые ячейки, хотя это может быть не так.
Эти ячейки выглядят пустыми, но в них есть ="". Проблема в том, что
Проблема в том, что функция СЧЕТЧИК не считает это пустой ячейкой (она считает ее текстом).
См. пример ниже:
В приведенном выше примере я использую функцию COUNTA для поиска непустых ячеек, и она возвращает 11, а не 10 (но вы ясно видите, что только в 10 ячейках есть текст).
Причина, как я уже упоминал, заключается в том, что он не считает A11 пустым (хотя должен бы).
Но именно так работает Excel.
Исправление заключается в использовании подстановочного знака Excel в формуле.
Ниже приведена формула, использующая функцию СЧЁТЕСЛИ, которая подсчитывает только те ячейки, в которых есть текст:
Эта формула указывает Excel считать, только если в ячейке есть хотя бы один символ.
В комбинации ?*:
-
<ли>? (знак вопроса) гарантирует наличие хотя бы одного символа.
- * (звездочка) освобождает место для любого количества дополнительных символов.
Примечание. Приведенная выше формула работает, если в ячейках есть только текстовые значения. Если у вас есть список, содержащий как текст, так и числа, используйте следующую формулу:
Аналогичным образом вы можете использовать подстановочные знаки во многих других функциях Excel, таких как ЕСЛИ(), СУММЕСЛИ(), СРЗНАЧЕСЛИ() и ПОИСКПОЗ().
Также интересно отметить, что хотя вы можете использовать подстановочные знаки в функции ПОИСК, вы не можете использовать их в функции НАЙТИ.
Надеемся, что эти примеры дадут вам представление об универсальности и возможностях подстановочных знаков Excel.
Если у вас есть какой-либо другой инновационный способ его использования, поделитесь им со мной в разделе комментариев.
Подстановочные знаки в Excel — это специальные символы в Excel, которые заменяют символы в нем, в Excel есть три подстановочных знака: звездочка, вопросительный знак и тильда, звездочка используется для нескольких символов в excel, в то время как знак вопроса используется для представления только одного символа, тогда как тильда относится к идентификации, если символ подстановки.
Подстановочные знаки специальные символы, которые используются для поиска результата, который является менее точным или точным.
Например, если у вас есть слово "Простой чат", а в базе данных у вас есть "Простой чат", то общей буквой в этих двух словах будет "Чат", поэтому, используя подстановочные знаки Excel, мы можем сопоставить эти.
Типы
В Excel есть три типа подстановочных знаков.
Это соответствует нулю или количеству символов. Например, «Fi*» может соответствовать «Final, Fitting, Fill, Finch и Fiasco» и т. д.…
Это используется для соответствия любому одиночному символу. Например, «Фа? e» может соответствовать «Face» и «Fade», «?ore» может соответствовать «Bore» и «Core», «a?ide» может соответствовать «Abide» и «Aside».
Это используется для сопоставления подстановочных знаков в слове. Например, если у вас есть слово «Привет*», чтобы найти это слово, нам нужно обрамить предложение как «Привет~*», поэтому здесь символ тильды (~) указывает на слово «Привет», поскольку за ним не следует подстановочным знаком.
Примеры
Как мы уже говорили, звездочка используется для сопоставления любого количества символов в предложении.
-
Например, посмотрите на данные ниже.
ВПР требует совпадения точного значения поиска для извлечения данных. Это традиционный слоган, но мы все еще можем получить данные, используя значение поиска частиц. Например, если искомое значение — «VIVO», а в основной таблице — «VIVO Mobile», мы по-прежнему можем сопоставлять с использованием подстановочных знаков. Сейчас мы увидим один из примеров. Ниже приведены данные для примера.
У нас есть справочная таблица в столбце A. В столбце C у нас есть справочные значения. Эти значения поиска не совсем совпадают со значениями таблицы поиска. Итак, мы увидим, как применять функцию ВПР с использованием подстановочных знаков.
Первый аргумент — это значение поиска. Одна из проблем с искомым значением здесь заключается в том, что у нас нет точного совпадения, поэтому нам нужно заключить это искомое значение со звездочкой до и после искомого значения.
Здесь мы применили две звездочки «*»&C2&»*». Здесь звездочка указывает, что все, что находится между подстановочными знаками, должно сопоставляться и возвращать соответствующий результат.
Несмотря на то, что у нас был только символ звездочки «Infosys», он соответствовал значению в таблице поиска и возвращал точный результат как «Infosys Ltd».
Как мы уже говорили, вопросительный знак может соответствовать одному символу в указанном слоте. Например, посмотрите на данные ниже.
В приведенных выше данных нашим идеальным значением должно быть «Wallstreet Mojo», но между ними есть несколько специальных символов. Поэтому мы будем использовать вопросительный знак, чтобы заменить все это.
Выберите данные и нажмите Ctrl + H.
В поле НАЙТИ Что введите «Wallstreet?Mojo», а в поле «Заменить на» введите «Wallstreet Mojo».
Нажмите «Заменить все». Мы получим следующий результат.
Вау. Красиво, правда??
Здесь весь трюк делается с помощью вопросительного знака Excel (?). Давайте посмотрим на то, что мы упомянули.
Что найти: «Уоллстрит? Моджо»
Заменить на: «Волшебство Уолл-Стрит»
Итак, после слова Wallstreet любой символ должен быть заменен пробелом. Таким образом, все специальные символы заменяются пробелом, и у нас будет правильное значение «Wallstreet Mojo».
Таким образом, используя подстановочные знаки, мы можем сопоставить частичные данные и выполнить работу.
Рекомендуемые статьи
Это руководство по подстановочным знакам в Excel. Здесь мы обсуждаем 3 различных типа подстановочных знаков — звездочка (*), вопросительный знак (?) и тильда (~) в Excel вместе с примерами и загружаемым шаблоном Excel. Вы также можете посмотреть на эти полезные функции в Excel –
Чтобы выполнить частичное (подстановочное) сопоставление чисел, вы можете использовать формулу массива, основанную на функциях ПОИСКПОЗ и ТЕКСТ. В показанном примере формула в E6 выглядит следующим образом:
где данные — это именованный диапазон B5:B15. Результат равен 7, так как число в ячейке B11 (седьмая строка данных) содержит 99.
Это формула массива, которую необходимо вводить с помощью клавиш Control + Shift + Enter, за исключением Excel 365.
Excel поддерживает подстановочные знаки "*" и "?", и эти подстановочные знаки можно использовать для частичного совпадения (подстроки) в различных формулах поиска. Однако, если вы используете подстановочные знаки с числом, вы преобразуете числовое значение в текстовое значение. Другими словами, "*"&99&"*" = "*99*" (текстовая строка), и если вы попытаетесь найти текстовое значение в диапазоне чисел, совпадение не удастся.
Одним из решений является преобразование числовых значений в текст с помощью функции ТЕКСТ следующим образом:
Это формула массива, которую необходимо вводить с помощью клавиш Control + Shift + Enter, за исключением Excel 365.
Эта формула использует функцию ТЕКСТ для преобразования чисел в формате B5:B10 в текст с числовым форматом "0". Поскольку мы передаем ТЕКСТ весь диапазон, мы возвращаем все значения, преобразованные в текст, в виде массива, который возвращается непосредственно в функцию ПОИСКПОЗ в качестве аргумента массив. Когда числа преобразованы в текст, функция ПОИСКПОЗ может найти частичное совпадение, как обычно.
Обратите внимание, что ПОИСКПОЗ должен быть настроен на точное соответствие, чтобы использовать подстановочные знаки, установив для третьего аргумента ноль (0) или ЛОЖЬ.
Другой вариант
Еще один способ преобразовать число в текст — объединить числа в пустую строку ("") с помощью оператора амперсанда (&) следующим образом:
Числа в данных становятся текстом без форматирования, и результат такой же, как и выше, 7.
Подстановочный знак – это специальный символ, который позволяет выполнять "нечеткое" сопоставление текста в формулах Excel. Например, эта формула:
подсчитываются все ячейки в диапазоне B5:B11, заканчивающиеся текстом "combo". И эта формула:
Подсчитывает все ячейки в A1:A100, которые содержат ровно 3 символа.
Доступные подстановочные знаки
В Excel есть 3 подстановочных знака, которые можно использовать в формулах:
- Звездочка (*) – ноль или более символов.
- Вопросительный знак (?) – один любой символ
- Тильда (~) — экранирование буквенного символа (~*), буквального вопросительного знака (~?) или буквальной тильды (~~).
Пример использования подстановочных знаков
| Использование | Поведение | Соответствует |
|---|---|---|
| ?< /td> | Любой один символ | "A", "B", "c", "z" и т. д. |
| ?? | Любые два символа | "AA", "AZ", "zz" и т. д. |
| . | Любые три символа | "Jet", "AAA", "ccc" и т. д. |
| * | Любые символы | "apple", "APPLE", "A100" и т. д. |
| *th | Заканчивается на "th" | "баня", "четвертый" и т.д. |
| с* | Начинается с "с" | < td>"Кот", "CAB", "синди", "конфета" и т.д.|
| ?* | По крайней мере один символ | "а", "б", "аб", "ABCD" и т.д. |
| . -?? | 5 символов с дефисом | "АВС-99","100-ЗТ" и т.д. |
| * ~? | Оканчивается знаком вопроса | "Привет?", "Кто-нибудь дома?" и т. д. |
| *xyz* | Содержит "xyz" | "код XYZ", "100-XYZ", "XyZ90" и т. д. |