Поиск в Excel по подстроке и счетчик отображений

Question

Это расширенный вопрос из этого.

Предположим, теперь у меня есть следующая таблица со строками:

    A
1   CHI111
2   SEA222
3   SEA333
4   LA444
...

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

    M    N
1   CHI  1
2   SEA  2
3   LA   1

Как мне написать формулу для N1 ~ N3, чтобы заполнить счет?

Изменить: добавление небольшого варианта

Теперь в таблице есть два ключевых слова с одинаковым количеством записей:

    A
1   CHI111
2   SEA222
3   SEA333
4   LA444
5   LA555
...

Как отобразить в ячейке с двумя ключевыми словами, разделенных разделителем (запятая, косая черта, &). Вот так:

    M    N   O
1   CHI  1   SEA, LA
2   SEA  2
3   LA   2

Кроме того, если значения для всех трех ключевых слов совпадают, его необходимо будет отобразить как "ВСЕ"

    M    N   O
1   CHI  2   ALL
2   SEA  2
3   LA   2

СПАСИБО ЗА ПОМОЩЬ!!

score 2 · Accepted Answer · 2011-03-09T19:38:42

Для подсчета в N1 положите:

=COUNTIF($A:$A,"*" & M1 & "*")

тогда вы можете использовать эту формулу, чтобы найти значение с наибольшим:

=INDEX(M:M,MATCH(MAX(N:N),N:N,0))

В одной формуле, где ваши префиксы все еще в M1:M3, используйте эту формулу массива:

=INDEX($M$1:$M$3,MATCH(MAX(COUNTIF($A$1:$A$4,"*"&$M$1:$M$3&"*")),COUNTIF($A$1:$A$4,"*"&$M$1:$M$3&"*"),0))

Будучи массивом, его нужно подтвердить с помощью Ctrl-Shift-Enter. Если все сделано правильно, тогда Excel поместит {} вокруг формулы.

С формулами массива мы хотим ссылаться только на диапазоны с данными, а не использовать полные ссылки на столбцы.

1 ответ1