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

Question

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

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

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

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

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

Answer 1

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

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

  

Введите следующую формулу в ячейку 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 (точное совпадение)