Возврат и суммирование нескольких значений с использованием критериев подстановочных знаков

425
Junior

Я пытаюсь создать сложную формулу, которая делает следующее:

  1. Поиск данных в столбце Aдругого листа с использованием критерия «содержит» ( xxxx&"*")
  2. Возвращает все совпадающие значения из определенного столбца (столбца B)
  3. Суммирует возвращенные значения, чтобы обеспечить общее

Я приложил представление некоторых примеров данных в качестве примера набора данных, с которым я работаю:

sample data

Например, я могу показать общее количество яблок, независимо от их цвета ( 8), в одной ячейке.

Я попробовал несколько вещей ( VLOOKUP, INDEX, MATCH), но я не могу показаться, чтобы получить его 100% право.

2
В этом предыдущем вопросе приведены примеры использования символов подстановки: https://superuser.com/questions/1333733/identify-if-someone-has-paid-in-an-excel-spreadsheet-and-create-text-to- говорят, что они fixer1234 6 лет назад 0

1 ответ на вопрос

3
robinCTS

Решение очень простое. Вам нужно использовать SUMIF()функцию.

С двумя листами, настроенными так:

Worksheet Screenshot  Worksheet Screenshot

Введите следующую формулу в ячейке B2из Sheet1:

=SUMIF(Sheet2!$A$2:$A$5,A2&"*",Sheet2!$B$2:$B$5)

Обратите внимание, что эта формула на самом деле выполняет «начинается с».


Формула "содержит" нуждается в другом шаблоне:

=SUMIF(Sheet2!$A$2:$A$5,"*"&A2&"*",Sheet2!$B$2:$B$5)

Подстановочные знаки Объяснение

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

  • ?- соответствует любому одному символу
  • *- соответствует нулю или более символов
  • ~- «убегает» следующий символ, например ~?, ~*и ~~будет соответствовать буквенным символам ?, *и ~соответственно. Обратите внимание, что ~после любого другого символа будет соответствовать только этот символ, а не тильда, за которой следует этот символ

Наиболее полезные функции, позволяющие использовать подстановочные знаки:

  • SEARCH()
  • MATCH()*
  • VLOOKUP()& HLOOKUP*
  • SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS(), AVERAGEIF()&AVERAGEIFS()


* Подстановочные знаки могут использоваться только в том случае, если третий аргумент MATCH()- 0(точное совпадение) или четвертый аргумент VLOOKUP()/ HLOOKUP()- TRUE(точное совпадение)

Похожие вопросы