Средние числа, если другой столбец содержит определенный текст подстроки в Excel

Question

Я ищу способ усреднения числовых чисел в одном столбце, скажем, B, только если строка в столбце A содержит определенный текст.

Например:

(In column A) (In column B)
Hello table . . . 1
Hello desk  . . . 2 
Bye table   . . . 3
Bye desk  . . . . 4

Таким образом, если критерий для усреднения чисел является "рабочим", ответом будет (2+4)/2 = 3.

То, что я придумал, так это:

=AVERAGEIF(A1:A5, SEARCH("desk",B1:B5),B1:B5) 

но он возвращает ошибку, потому что ПОИСК принимает только один вход.

Какие-либо предложения?

Answer 1

Добавьте подстановочный знак звездочка * перед и / или после слова «стол», как:

=AVERAGEIF(A1:A5,"*desk",B1:B5)

или же

=AVERAGEIF(A1:A5,"*desk*",B1:B5)

Это говорит Excel, чтобы игнорировать любые символы до / после слова «стол».

Answer 2

Ну, как несколько общий ответ, который будет работать для вас, другой способ выполнить условие внутри функции (в вашем случае, если IF внутри AVERAGE), вы всегда можете разбить его на части. Оператор IF также может возвращать массив и не должен быть единственным значением.

Если вы печатаете

=AVERAGE(IF(ISERROR(SEARCH("desk",A1:A5))=FALSE,A1:A5))

и нажмите Ctrl + Shift + Enter после того, как вы наберете его, тогда он будет обрабатывать ввод как массив, а не как значение, давая ваши результаты.

Надеюсь это поможет!

Answer 3

AVERAGEIF - лучший ответ для Excel 2013+. Для более старых версий вам нужно использовать третий столбец C.

Все ячейки в столбце C (со значением слева) имеют следующую формулу: =IF(ISNUMBER(SEARCH(C$7,A1)), B1, "")

В ячейке C7 просто набрано слово « desk , поэтому нет необходимости фиксировать эту формулу при каждом изменении поиска.

После этого вы просто делаете среднее значение в C5 =AVERAGE(C1:C4) . Это все, что вам нужно. На самом деле не много потерянного места, и вы также можете избавиться от окна поиска, если хотите просто настроить функции при необходимости.

Answer 4

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

В ячейке C1 введите следующую формулу: = IF(ISNUMBER(SEARCH("desk", A1)), B1, "")

Скопируйте / вставьте эту формулу вниз для каждой строки в столбце C

Среднее количество, которое отображается в столбце C.

Если столбец C уже используется, просто вставьте другой столбец на его место