☰

Подсчитайте количество вхождений в список Excel со списком из 400+ различных элементов

tag-icon • tag-icon microsoft-excel microsoft-excel-365
Подсчитайте количество вхождений в список Excel со списком из 400+ различных элементов

У меня есть список примерно из 5 тыс. строк, в котором я хочу подсчитать количество вхождений каждого элемента.

В этом списке около 300–400 различных наименований.

Вместо того, чтобы набирать более 300 уникальных формул, выглядящих как

=COUNTIF(A1:A5000,"A")
=COUNTIF(A1:A5000,"B")
=COUNTIF(A1:A5000,"C")
=COUNTIF(A1:A5000,"D")
=COUNTIF(A1:A5000,"E")
ETC to 300...

, есть ли способ достичь этой цели с помощью какой-то улучшенной формулы?

Ниже приведен пример списка, который я позаимствовал из предыдущего опубликованного вопроса, поскольку он демонстрирует, чего я пытаюсь добиться. Однако в моем случае у меня есть от 300 до 400 различных элементов, а не только A, B, C, D и E:

«У меня есть список вещей, который выглядит примерно так:»

A
B
C
A
A
B
D
E
A

«Теперь я хочу подсчитать количество появлений каждого элемента. Результат должен выглядеть так:»

A 4
B 2
C 1
D 1
E 1

Благодарим RoflcoptrException за публикацию оригинального вопроса.

решение1

Самый простой способ сделать это — экспортировать значения в текстовый файл, например f.txt, и запустить следующий .batскрипт в той же папке:

@echo off
setlocal

rem Accumulate each occurrence in its corresponding array element
for /F %%a in (f.txt) do set /A "[%%a]+=1"

rem Show the result
for /F %%a in ('set [') do echo %%a

Выполнение этого на вашем образце данных возвращает:

enter image description here

решение2

Вместо формулы я хотел бы предложить самый быстрый метод — подсчет VBA Macroвстречаемости различных элементов.

enter image description here

Как это работает:

  • Чтобы создать уникальный список подсчитываемых элементов, в ячейке используется формула массива (CSE) C68:

    {=IFERROR(INDEX($A$68:$A$78, MATCH(0,COUNTIF($C$67:C67, $A$68:$A$78), 0)),"")}

  • Закончите формулу сCtrl+Shift+Enterи залейте.

  • Либо нажмитеАльт+Ф11илирщелкните TAB листа и нажмитеВКодекс iew.

  • В окне редактора VB изявставить команду щелчокМодуль.

  • Скопия &псохраните этот код.

    Public Function CountString(SearchFor As String, InRange As Range) As Long

  Dim wbcs As Long, rng As Range, addr As String
   For Each s In Worksheets

addr = InRange.Address
Set rng = s.Range(addr)

wbcs = wbcs + Application.WorksheetFunction.CountIf(rng, "*" & SearchFor & "*")
 Next s
 CountString = wbcs

 End Function

  • Сохранить книгу как файл с поддержкой макросов (.xlsm).

  • Введите эту формулу в ячейку D68, заполните ее:

=CountString($C68,A$68:A$78)

  • Чтобы подсчитать количество случаев в нескольких столбцах, можно использовать следующую формулу.

=CountString($C68,A:C)

При необходимости измените ссылки на ячейки в формуле.

Связанный контент