Как использовать суммесли с подстановочными знаками?

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

Допустим, вас зовут Джон Мартин.

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

Да, это проблема. Но и для этого у меня есть решение. Помните подстановочные знаки Excel ? Да, ты прав.

Если мы используем подстановочные знаки с SUMIF , мы можем суммировать значения, используя частичные критерии. Хорошей новостью является то, что мы можем использовать все три символа (звездочку [*], вопросительный знак [?], тильду [~]) с SUMIF/SUMIFS.

Сегодня в этой статье я хотел бы поделиться с вами простым способом использования СУММЕСЛИ с подстановочными знаками, объясняя использование всех трех символов на примерах.

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

А теперь начнём…

1. Звездочка [*] с СУММЕСЛИ

Звездочка — самый популярный подстановочный знак. И это тот, который прекрасно можно использовать с SUMIF.

Asterisk: краткое введение

Вы можете использовать звездочку , чтобы указать один или несколько пропущенных символов в конце или начале критериев.

Как я уже сказал, если у нас есть Джон в качестве критерия и нам нужно суммировать значения имени Джон Мартин, мы можем объединить текст «Джон» со звездочкой, чтобы представить остальную часть текста. со звездочкой для обозначения остального текста.

Пример: СУММЕСЛИ + звездочка.

Здесь у меня есть лучший пример, чтобы вы могли понять, как asterisk работает с SUMIF.

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

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

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

 =SUMIF(invoice_column,product_name&"*",amount_column)

В приведенной выше формуле мы объединили название продукта со значениями суммы звездочек для каждого продукта.

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

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

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

2. Вопросительный знак [?] с СУММЕСЛИ

Знак вопроса — следующий важный символ после звездочки. SUMIF/SUMIFS может помочь вам создать критерии частичного текста.

Вопросительный знак: краткое введение

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

Меня зовут «ПУНИТ». Но что произойдет, если кто-то напишет это как PUNNET и PUNIIT? Я не могу СУММИРОВАТЬ значение, связанное с этими двумя или любыми другими.

Если я использую PUN??T (два вопросительных знака), это помогает мне СУММИНИРОВАТЬ значения обоих критериев.

Пример: СУММЕСЛИ + знак вопроса.

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

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

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

И если вы хотите суммировать там все бонусы, вы не можете просто сослаться на мое имя.

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

 =SUMIF(name_column,"Puneet?Gogia",amount_column)

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

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

Итак, решающий момент заключается в следующем: знак вопроса представляет собой один символ в текстовой строке.

3. Тильда [~] с SUMIFS

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

Тильда: краткое знакомство

Проще говоря, тильда переопределяет эффект подстановочных знаков (звездочки и вопросительного знака).

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

Пример: СУММЕСЛИ + Тильда.

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

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

Назначение: Если вы посмотрите на результат формулы, то он отображает сумму всех значений бонуса (для имени и полного имени).

SUMIF рассматривает этот вопросительный знак в критериях как подстановочный знак и возвращает сумму бонусных значений, где текст в критериях — «Пунит». Как я уже сказал, нам нужно использовать тильду со звездочкой, чтобы получить сумму значений. Таким образом, формула будет такой:

 =SUMIF(name_column,"Puneet*~",amount_column)

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

Задача решена. Вы получили правильную сумму значений.

Заключение

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

Просто помни:

1. Если у вас есть данные, в которых вы не уверены в полной текстовой строке, вы можете использовать звездочку.
2. А если вы хотите заменить определенное количество символов, можно использовать вопросительный знак.
3. Но, как я уже сказал, ваша работа включает в себя обработку большей части грязных данных, поэтому тильда может пригодиться.

Я надеюсь, что этот совет поможет вам лучше разбираться в формулах. Здесь вы можете узнать некоторые из самых удивительных формул Excel.

Теперь скажи мне одну вещь.

Вы когда-нибудь использовали SUMIF с подстановочными знаками?

Поделитесь со мной своими мыслями в комментариях, мне будет интересно услышать ваше мнение. И не забудьте поделиться этим советом с друзьями.