Как использовать подстановочные знаки?
В этой статье мы узнаем, как и когда использовать символы подстановки или встроенные символы регулярных выражений (регулярных выражений) для функций Excel.
Excel позволяет использовать символы подстановки для захвата строк и выполнения с ними функций.
В Excel есть три подстановочных символа. Знак вопроса (?): Этот подстановочный знак используется для поиска любого отдельного символа.
-
Звездочка (): этот подстановочный знак используется для поиска любого количества символов, предшествующих или следующих за любым символом.
-
Тильда (~): этот подстановочный знак представляет собой escape-символ, используемый перед вопросительным знаком (?) Или звездочкой ().
Давайте использовать эти подстановочные знаки в примерах. Здесь нам нужны продукты, которые начинаются с C. Поэтому сначала мы применим фильтр к списку, используя Ctrl + Shift + L. Затем в поле поиска продуктов введите «C *», как показано ниже.
Как видите, весь список отфильтрован всеми продуктами, начиная с C.
Примечание: * (подстановочный знак) находит любое количество символов.
Здесь нам нужен диапазон значений Quantity от 30-39. Итак, сначала мы применим фильтр к списку с помощью Ctrl + Shift + L. Затем в поле поиска количества введите «3?» как показано ниже.
Как видите, весь список отфильтрован всеми продуктами, начиная с C.
Запись: ? (подстановочный знак) находит отдельные символы.
Подстановочный знак тильда (~) используется, когда нам нужно найти символ вопроса или звездочку (не как подстановочные знаки). Например, если вы ищете строку, которая заканчивается вопросительным знаком. Вы набираете «* ~?» Все строки будут отмечены вопросительным знаком.
Здесь нам нужно найти данные, оканчивающиеся на (astrick). Итак, мы применим два подстановочных знака ~. * Первый астрик используется как подстановочный знак, а второй используется для поиска знака астрика в данных.
Примечание: ~ (подстановочный знак) находит два символа &?
Не все функции могут выполнять подстановочные знаки с функциями Excel.
Вот список функций, которые принимают подстановочные знаки.
AVERAGEIF, AVERAGEIFS COUNTIF, COUNTIFS SUMIF, SUMIFS VLOOKUP, HLOOKUP MATCH SEARCH ===== Пример: Сумма денег, потраченных на манго. На изображении выше у нас есть таблица сумм, потраченных на разные фрукты в разные месяцы. Нам просто нужно узнать общую сумму, потраченную на манго за все эти месяцы.
В I2 формула
|
= |
Это возвращает 525 как общую сумму, потраченную на манго. Вы можете увидеть это на изображении выше.
Как это работает? Что ж, это просто. Давайте разберем формулу и разберем ее по частям.
(B2: B9 = H2): эта часть сравнивает каждое значение в диапазоне B2: B9 с H2 и возвращает массив ИСТИНА и ЛОЖЬ.
\ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}.
(B2: B9 = H2) * C2: E9: Здесь мы умножили каждое значение в приведенном выше массиве на значения в C2: E9. C2: C9 также рассматриваются как 2D-массив. Наконец, этот оператор возвращает 2D-массив из \ {0,0,0; 47,57,67; 0,0,0; 0,0,0; 0,0,0; 0,0,0; 0,0, 0; 108,118,128}.
Теперь СУММПРОИЗВ выглядит так: link: / summing-excel-sumproduct-function [SUMPRODUCT] (\ {0,0,0; 47,57,67; 0,0,0; 0,0,0; 0,0 , 0; 0,0,0; 0,0,0; 108,118,128}).
Он имеет только ценности манго. Он суммирует их и возвращает результат как 525.
Другой метод может иметь столбец итогов, а затем использовать его с функцией СУММЕСЛИ, чтобы получить сумму всех столбцов. Но мы не этого хотим.
Функция СУММЕСЛИ в Excel
Как следует из названия, формула СУММЕСЛИ в Excel суммирует значения в диапазоне при заданном условии.
Общая формула СУММЕСЛИ в Excel:
|
=SUMIF(condition_range,condition,sum range) |
Давайте рассмотрим пример. Но теория… Ах! Мы расскажем об этом позже.
Используйте СУММЕСЛИ для суммирования значений при одном условии. Для этого примера я подготовил эти данные.
На основании этих данных нам необходимо ответить на следующие вопросы:
Начнем с первого вопроса.
СУММЕСЛИ с текстовым условием. Нам нужно указать сумму выигрыша, выигранного Рави.
Таким образом, нашим диапазоном условий будет диапазон имен, то есть A2: A10.
Наше состояние — Рави, а диапазон сумм — E2: E10.
Итак, в ячейке I2 мы напишем:
|
=SUMIF(A2:A10,»ravi»,E2:E10) |
Обратите внимание, что рави заключен в двойные кавычки. Текстовые условия всегда заключаются в двойные кавычки. С числами дело обстоит иначе.
Обратите внимание, что ravi пишется мелким шрифтом. Поскольку СУММЕСЛИ не чувствительна к регистру, это не имеет значения.
Приведенная выше формула СУММЕСЛИ вернет 31907, как показано на изображении ниже.
СУММЕСЛИ с логическими операторами. Что касается второго вопроса, наш диапазон условий будет D2: D10.
Условие> 70 и диапазон сумм такой же, как и раньше.
|
=SUMIF(D2:D10,»>70″,E2:E10) |
Приведенная выше формула СУММЕСЛИ вернет 103973, как показано на рисунке ниже.
СУММЕСЛИ с операторами подстановочных знаков В третьем вопросе наше условие — Дели. Но у нас нет городской колонки. Хммм… Так что у нас? Ага! Код города. Это сработает.
Мы знаем, что все коды городов Дели начинаются с 1100. Коды городов состоят из 6 цифр. Итак, мы знаем, что это 1100 фунтов стерлингов. «?» Оператор используется, когда мы знаем количество символов, но не знаем их. Поскольку здесь мы знаем, что после 1100 есть еще два числа. Они могут быть любыми, поэтому мы использовали «?». Если бы мы не знали количество символов, мы бы использовали «*».
Помните, что операторы с подстановочными знаками работают только с текстовыми значениями. Следовательно, вам необходимо преобразовать код города в текст.
Вы можете объединять числа с помощью «», чтобы сделать их текстовыми значениями.
|
(formula to convert number into text) _ = number & “” _or =CONCATENATE(number,””) |
Теперь в ячейке I2 напишите эту формулу
|
=SUMIF(B2:B10,»1100??»,E2:E10) |
Это вернет сумму цены, код города которой начинается с 1100.
В нашем примере это 79836.
Примечания к профессионалам:
-
Если диапазон сумм и диапазон условий совпадают, вы можете опустить переменную sum_range в функции СУММЕСЛИ. = СУММЕСЛИ (E2: E10, «> 15000») и = СУММЕСЛИ (E2: E10, «> 15000», E2: E10) дадут тот же результат, 56163.
-
Текстовые значения заключены в двойные кавычки, а числа — нет.
СУММЕСЛИ (C2: C10,103, E2: E10) это будет работать нормально и вернет 28026.
Однако при работе с логическими операторами необходимо использовать двойные кавычки. Как в нашем примере = СУММЕСЛИ (D2: D10, «> 70», E2: E10)
-
Он может проверить только одно условие. Для нескольких условий мы используем функцию СУММЕСЛИМН в Excel.
Тааак… Это было полезно? Дайте мне знать в разделе комментариев. Попросите свой персональный запрос СУММЕСЛИ, если вы по-прежнему сталкиваетесь с трудностями.
Так что да, ребята, вот как вы можете узнать, как использовать подстановочные знаки? Дайте мне знать, если у вас есть какие-либо сомнения относительно этой статьи или любой другой статьи на этом сайте. Вы также можете задавать вопросы по Excel 2019, 2016, 2013 и более ранним версиям.
Похожие статьи
link: / summing-excel-sumproduct-function [Как использовать функцию СУММПРОИЗВ в Excel]: возвращает СУММ после умножения значений в нескольких массивах в Excel.
link: / summing-sum-if-date-is-between [SUM if date is between]: возвращает СУММУ значений между заданными датами или периодом в Excel.
link: / summing-sum-if-date-more-than-given-date [Сумма, если дата больше заданной даты]: * Возвращает СУММУ значений после заданной даты или периода в Excel.
link: / summing-2-way-to-sum-by-month-in-excel [2 способа суммирования по месяцам в Excel]: * Возвращает СУММУ значений за данный конкретный месяц в excel.
link: / summing-how-to-sum-multiple-columns-with-condition [Как суммировать несколько столбцов с условием]: возвращает СУММУ значений по нескольким столбцам, имеющим условие в excel `link: / tips-excel- wildcards [Как использовать подстановочные знаки в excel *] `: Подсчет ячеек, соответствующих фразам, используя подстановочные знаки в excel
Популярные статьи:
link: / tips-if-condition-in-excel [Как использовать функцию IF в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если оно ложно .
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.
link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.