Excel countif на основе подмножеств

Я пытаюсь подсчитать количество компаний, которые мы поддерживаем, на основе их кода NAICS (код отраслевой классификации). Когда компания регистрируется, она предоставляет свой 3,4,5 или 6-значный код NAICS.

Например, допустим, у меня есть три компании, которые присоединились. Один из них предоставляет свой шестизначный код NAICS 311811 (розничные пекарни); другой также предоставляет свой шестизначный код 311812 (Commercial Bakeries); а последний выбирает только для предоставления своего трехзначного кода 311 (производство продуктов питания).

В моей электронной таблице список всех компаний, которые присоединяются, находится на листе «RawData», а код NAICS — в столбце J. Список всех кодов NAICS находится на листе «Список» моей рабочей книги, куда я хотел бы вернуть результат общего количества компаний в коде NAICS.

Простая функция суммирования =COUNTIF(RawData!J:J,List!A1) возвращает только количество присоединившихся организаций на основе точного совпадения кода. Другими словами, я получаю это:

Code   Count
311    1
3118   0
31181  0
311811 1
311812 1

То, что я действительно хочу получить, это

Code   Count
311    3
3118   2
31181  2
311811 1
311812 1

В последней таблице две компании, выбравшие 311811 и 311812, должны учитываться при подсчете кода 311 и кодов 3118 и 31181.


excel countif
person Danny    schedule 14.11.2017    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Попробуйте следующую формулу массива в ячейке B2, чтобы получить количество 311, если 311 находится в ячейке A2:

=SUM(IF(ISERROR(SEARCH(A2,J:J)),0,1))

Обратите внимание, что это формула массива, и ее необходимо вводить как таковую, используя Ctrl+Shift+Enter (вместо простого нажатия Введите). Вы увидите, что Excel распознает формулу как формулу массива, автоматически добавляя изогнутые скобки вокруг формулы следующим образом (НЕ вводите эти изогнутые скобки вручную):

{=SUM(IF(ISERROR(SEARCH(A2,J:J)),0,1))}

Конечно, сказанное выше можно расширить, перетащив формулу вниз и добавив в столбец A3 значение 3118, в ячейку A4 значение 31181 и так далее.

person Ralph    schedule 14.11.2017