Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как назначить значение или категорию на основе диапазона чисел в Excel?

Подстановочные знаки Excel — что это такое и как лучше всего их использовать

Подстановочные знаки Excel - почему вы их не используете?

В Excel есть только три символа подстановки (звездочка, вопросительный знак и тильда), но с ними можно сделать очень многое.

Эти подстановочные знаки Excel выручают вас в четырех примерах.

Подстановочные знаки Excel — Введение

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

В Excel есть три символа подстановки:

  1. * (звездочка) — представляет собой любое количество символов. Например, Ex * может означать Excel, Exemplar, Expert и т.д. EX. EXCEL * (ЗВЕЗДОЧКА) — ПРЕДСТАВЛЯЕТ СОБОЙ ЛЮБОЕ КОЛИЧЕСТВО СИМВОЛОВ. (вопросительный знак) — представляет собой один символ. Например: Tr? Мп может означать Трамп или Трамп.

(тильда) — Он используется для обозначения подстановочного знака (

, *, ?) в тексте. Например, допустим, вы хотите найти точную фразу Excel * в списке. Если вы используете Excel * в качестве строки поиска, вы получите любое слово, начинающееся с Excel, за которым следует любое количество символов (например, Excel, Excels, Excels, Excellent). Чтобы искать конкретно по excel *, нам нужно использовать

. Таким образом, наша поисковая строка будет выглядеть следующим образом: excel

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

. Тем не менее, это полезная функция.

Давайте рассмотрим четыре хороших примера, в которых двусмысленные символы выполняют тяжелую работу.

Подстановочные знаки Excel — примеры

Давайте рассмотрим четыре практических примера, в которых подстановочные знаки Excel могут быть очень полезными:

  1. Фильтрация данных с помощью подстановочного знака.
  2. Частичный поиск с использованием подстановочных знаков и VPR.
  3. Поиск и замена частичных совпадений.
  4. Подсчет непустых ячеек, содержащих текст.

# 1 Фильтрация данных с использованием подстановочных знаков Excel

Если у вас большие наборы данных и вы хотите отфильтровать данные на основе условия, подстановочные знаки Excel могут пригодиться вам.

Допустим, у нас есть набор данных, показанный ниже:

Вы можете использовать подстановочный знак звездочки (*) в фильтре данных, чтобы получить список компаний, начинающихся с алфавита А.

Это делается следующим образом:

  • Выделите ячейки, которые хотите отфильтровать.
  • Перейдите в Данные -> Сортировка и фильтр -> Фильтр. (Сочетание клавиш — Control + Shift + L).
  • Щелкните значок фильтра в ячейке заголовка.
  • В поле (под параметром «Текстовый фильтр») введите А *
  • Щелкните ОК.

При этом вы получите три результата — ABC Ltd, Amazon.com и Apple Store.

Как это работает? Excel отфильтрует все, что начинается с буквы А, если после буквы А добавить звездочку (*). Поскольку звездочки (это подстановочный знак Excel) могут представлять любое количество символов, они могут быть произвольными.

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

Например, если вы хотите отфильтровать компании, название которых начинается с буквы A и содержит букву C, используйте строку A * C. Это покажет только два результата — ABC Ltd. и Amazon.com.

Если вы используете A ? C, вы получите в результате только ABC Ltd (так как между «a» и «c» допускается только один символ).

# 2 Частичный поиск с использованием подстановочных знаков и ВПР

Частичный поиск необходим, когда нужно найти значение в списке, а точного совпадения нет.

Например, предположим, у вас есть набор данных, показанный ниже, и вы хотите найти в списке компанию ABC, но в списке вместо ABC указано ABC Ltd.

В этом случае обычная функция БПФ не может быть использована, так как искомое значение не является точным совпадением.

Это даст вам неправильные результаты, если вы используете VLOOKUP с грубым соответствием.

Однако можно использовать подстановочный знак в функции UPP для получения правильных результатов:

В ячейку D2 необходимо ввести следующую формулу, а также перетащить ее в остальные ячейки:

Как действует эта формула?

В приведенной выше формуле вместо того, чтобы использовать значение в том виде, в котором оно появляется, оно окружено с обеих сторон подстановочным знаком Excel со звездочкой (*) — «*» и C2 & «*».

Теперь Excel будет искать текст, содержащий слово в C2. Символ(ы) до или после текста в C2 произвольный.

Поэтому формула ищет совпадение и, как только находит, возвращает это значение.

3. Найдите и замените частичные совпадения

Использовать подстановочные знаки в Excel довольно просто.

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

Предположим, у вас имеются следующие данные:

В приведенных выше данных регион указывается по-разному (например, северо-запад, северо-запад, северо-запад).

Так часто бывает с данными о продажах.

Чтобы очистить эти данные и обеспечить согласованность, можно использовать подстановочные знаки Excel в функции «Поиск и замена».

А вот как:

  • Выберите данные, в которых вы хотите найти и заменить текст.
  • На главную -> Найти и выбрать -> Перейти. Откроется диалоговое окно «Найти и заменить». (Вы также можете использовать сочетание клавиш — Control + H).
  • Введите следующий текст в диалоговом окне поиска и замены:
    • Найти то, что: Север * З *
    • Заменить: Северо-Запад

    Это мгновенно изменит все различные форматы и сделает их совместимыми с Северо-Западом.

    Как он работает?

    В результате нашего Sever * Z * мы найдем любой текст, в котором слово North стоит после буквы «W».

    Поэтому он охватывает все сценарии (Северо-Западный, Северо-Западный и Северо-Западный).

    Функция Find and Replace находит все эти экземпляры, изменяет их на Northwestern и обеспечивает согласованность.

    4. Подсчитайте непустые ячейки, содержащие текст.

    Очевидно, что вы умны, и Excel уже имеет встроенную функцию.

    Ты абсолютно прав! !

    Это можно сделать с помощью функции СКРИНИНГ.

    НО… С этим есть небольшая проблема.

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

    Кажется, что эти ячейки пусты, но они содержат знак = «». Проблема заключается в том, что

    Существует проблема с COUNTA. Он игнорирует эту ячейку как пустую (считает ее текстом).

    Используя функцию COUNTA для поиска непустых ячеек, я получаю 11 результатов вместо 10 (хотя ясно, что только 10 ячеек содержат текст).

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

    Но так уж устроен Excel.

    Исправление заключается в использовании в формуле Excel подстановочного знака.

    Ниже приведена формула с использованием функции READ, которая подсчитывает только ячейки, содержащие текст:

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

    В ? * комбо:

    • ? (вопросительный знак) указывает на наличие хотя бы одного символа.
    • * (звездочка) позволяет ввести любое количество дополнительных символов.

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

    Кроме того, вы можете использовать подстановочные знаки во многих функциях Excel, включая IF (), SUMIF (), AVERAGEIF () и MATCH ().

    Стоит также отметить, что хотя подстановочные знаки могут использоваться в функции ПОИСК, они не могут использоваться в функции ПОИСК.

    На этих примерах видно, что подстановочные знаки Excel обладают большой гибкостью и мощью.

    Вы также можете поделиться другими инновационными способами его использования в разделе комментариев.

    Функция СУММЕСЛИ и подстановочные знаки в Excel

    Функция СУММЕСЛИ, вероятно, является одной из самых полезных и широко используемых функций в Excel. У этой функции есть один недостаток. Чтобы суммировать значения с помощью этой функции, критерии должны соответствовать каждому символу.

    Например, предположим, что его зовут Иван Петров, и у нас есть таблица, содержащая данные о продажах и имена и фамилии продавцов. Если мы знаем только его имя, мы не сможем обобщить данные в этой таблице с помощью функции СУММЕСЛИ только по имени. Нам необходимо знать их фамилию.

    Это проблема. Но у нас есть решение в виде джокеров.

    Подстановочные знаки позволяют выполнять частичный поиск данных. Для этого добавьте подстановочный знак в критерии поиска.

    В каких случаях используется символ подстановки?

    • * — звездочка
    • ? — вопросительный знак

    Далее рассмотрим, как подстановочные знаки могут помочь нам суммировать данные с помощью функции SUMMESLI.

    Подстановочный знак «звездочка» [*] и функция СУММЕСЛИ

    «Звездочка» это самый популярный подстановочный знак. Она используется для поиска любой строки знаков.

    Например, если в запросе будет указано «г*д», то поиску будут соответствовать результаты «год», «город», «грейд» и т.д.

    Если «звездочка» будет стоять в конце запроса, например «Иван*», то результату поиска будут соответствовать значения «Иван Маслов», «Иван Мельников», «Иван Петров», «Иванвадпловапло» и т.д.

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

    Функция СУММЕСЛИ и подстановочные знаки в Excel

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

    Для решения этой задачи мы выпишем в отдельную таблицу перечень продуктов, а в соседнем столбце пропишем формулу с функцией СУММЕСЛИ и подстановочным знаком «звездочка»:

    Функция СУММЕСЛИ и подстановочные знаки в Excel

    В этой формуле мы для аргумента «критерий» указали ссылку на ячейку с названием товара и добавили с помощью знака «&» подстановочный знак «звездочка». Excel, при таком запросе будет считать, что любое значение в таблице, начинающееся с названия продукта соответствует результату поиска. При этом не важно, какие символы будут указаны в таблице после названия продукта.

    Вопросительный знак и функция СУММЕСЛИ

    Знак вопроса также может эффективно использоваться с функцией SUMMESLI при поиске данных.

    Вопросительный знак используется для подстановки любого одного символа. Например, если поисковый запрос будет «г?д», то результатом могут быть слова «гид», «гад», «год».

    Давайте рассмотрим, как знак вопроса работает с функцией SUMMESLY. Предположим, у нас есть таблица счетов-фактур по трем продуктам с суммами оплаты. Названия продуктов компании A отличаются на один символ. Используя вопросительный знак, мы можем создать поиск, учитывающий тот факт, что персонаж может быть чем угодно.

    Формула с функцией SUMMESLY будет выглядеть так:

    Функция СУММЕСЛИ и подстановочные знаки в Excel

    Если указано более одного вопросительного знака, то система будет искать более одного вопросительного знака каждого символа.

    Итак, подстановочные знаки «звездочка» и вопросительный знак эффективно использовать вместе с функциями СУММЕСЛИ и СУММЕСЛИМН:

    1. Когда вы понимаете что в таблице, данные которой вы хотите суммировать, могут содержаться значения не полностью совпадающие с критерием поиска. В таких случаях используйте подстановочный знак «звездочка» [*].
    2. Когда вы понимаете, что в таблице есть несколько знаков, которые могут отличаться от критерия поиска. В таких случаях используйте подстановочный знак [?]

    Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

    Как назначить значение или категорию на основе диапазона чисел в Excel?

    Настоящий вопрос заключается в том, можно ли присвоить буквенному символу значение 1 для дальнейшего суммирования. Я ничего не смог найти в справке или не понял ее. Заранее благодарю вас за ответ.

    Ответы

    Ни один из этих методов не работает. Давайте избавимся от них. Считайте, не двигая руками.

    Лит тиль Горелова

    Знаете ли вы, что мешает вам решить проблему? — Вы делаете это сами 😉

    1) Вы не можете изложить проблему так, чтобы другие ее поняли (но это не так страшно)
    2) Вы делаете выводы и отметаете решения, толком не объяснив задачи и не поняв предложенных решений
    3) Вы не задаете вопросов, чтобы понять суть того, что вам предложили
    4) А самое главное — вы не объясняете что лично для вас означает "не получается", в чем оно выражается, и каков критерий "получается"

    И согласитесь между "присвоить букве цифровое значение" и "считать непустые ячейки" — пропасть. Судя по вашим последним пояснениям, ваша сумма — не сумма а счет 😉

    Только на всякий случай:

    если надо считать ячейки только с "я" ИЛИ только с "н", то

    если надо считать ячейки только с "я" И "н", то

    =СУММА(СЧЁТЕСЛИ(А1:J1;<"я";"н">)) Si la respuesta te es de utilidad por favor marca la pregunta como respondida.

    • Отмечено как ответ пользователем Gorelovo 20 июля 2009 8:22

    Все ответы

    Вы можете. Меню Вставка-> Имя-> Назначить.

    Если ответ полезен, пометьте вопрос как отвеченный.

    Вы можете сделать это. Меню Вставка-> Имя-> Назначить.

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

    Допустим, в строке состоящей из 15 ячеек заполняются только одной буквой, допустим "н" 5 ячеек, буквой "я" 3 ячейки, остальные остаются пустыми. А вот в 16 ячейке у меня должна появляться сумма всех заполненных ячеек. Попробую изобразить то что мне нужно наглядно.

    | | |n|n|ya|||| sum=>>11 | Я согласен, что немного сложно подсчитывать заполненные ячейки.

    • Помечено как ответ Gorelovo 17 июля 2009 г. 13:15
    • Не отмечено как ответ Gorelovo 20 июля 2009 8:22

    То, что вы описали в примере, сделать очень легко.

    Например, функция countif подсчитывает все ячейки, содержащие хотя бы один текстовый символ

    сила в справедливости

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

    Эскухен в Горелово,

    Знаете ли вы, что мешает вам решить проблему — вы сами 😉

    1) Вы не можете изложить проблему так, чтобы другие ее поняли (но это не так страшно)
    2) Вы делаете выводы и отметаете решения, толком не объяснив задачи и не поняв предложенных решений
    3) Вы не задаете вопросов, чтобы понять суть того, что вам предложили
    4) А самое главное — вы не объясняете что лично для вас означает "не получается", в чем оно выражается, и каков критерий "получается"

    И согласитесь между "присвоить букве цифровое значение" и "считать непустые ячейки" — пропасть. Судя по вашим последним пояснениям, ваша сумма — не сумма а счет 😉

    Только в этом случае:

    если надо считать ячейки только с "я" ИЛИ только с "н", то

    если надо считать ячейки только с "я" И "н", то

    =СУММА(СЧЁТЕСЛИ(А1:J1;<"я";"н">)) Si la respuesta te es de utilidad por favor marca la pregunta como respondida.

    • Tagged as ответ Горелово 20 июля 2009 8:22

    Ни один из этих методов не работает. Давайте избавимся от них. Считайте, не двигая руками.

    Слушайте Gorelovo,

    Знаете ли вы, что мешает вам решить проблему? — Вы делаете это сами 😉

    1) Вы не можете изложить проблему так, чтобы другие ее поняли (но это не так страшно)
    2) Вы делаете выводы и отметаете решения, толком не объяснив задачи и не поняв предложенных решений
    3) Вы не задаете вопросов, чтобы понять суть того, что вам предложили
    4) А самое главное — вы не объясняете что лично для вас означает "не получается", в чем оно выражается, и каков критерий "получается"

    И согласитесь между "присвоить букве цифровое значение" и "считать непустые ячейки" — пропасть. Судя по вашим последним пояснениям, ваша сумма — не сумма а счет 😉

    На всякий случай:

    если надо считать ячейки только с "я" ИЛИ только с "н", то

    если надо считать ячейки только с "я" И "н", то

    =СУММА(СЧЁТЕСЛИ(А1:J1;<"я";"н">)) Si la respuesta te es de utilidad por favor marca la pregunta como respondida. Ура . Заработало. Спасибо большое.Я всякую надежду потерял. А тут Такое.
    А вопрос как мог так и сформулировал, даже пытался изобразить ячейки. Не обессудьте. Еще раз спасибо.

    голоса
    Рейтинг статьи
    Читайте так же:
    Как изменить размер вкладок рабочего листа в Excel?
Ссылка на основную публикацию
Adblock
detector