Как найти второе по частоте встречающееся число или текст в Excel?

Это решение использует формулу массива для определения второго по частоте встречающегося числа в ограниченном диапазоне. Формулы массива идеально подходят, когда результат нужно получить одной формулой напрямую, однако они надёжно работают только с числовыми значениями. Для текстовых данных потребуется другой подход. Обратите внимание: в зависимости от версии Excel (до Office 365 и Excel 2019) для ввода формулы массива может потребоваться нажать Ctrl + Shift + Enter вместо обычного Enter.

Выберите пустую ячейку для отображения результата и введите следующую формулу:

=MODE(IF(A2:A31<,>,MODE(A2:A31),A2:A31,""))

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

Второе по частоте встречающееся число из вашего диапазона отобразится в ячейке. Этот метод отлично подходит для списков чисел, однако не работает с текстовыми значениями и не предусматривает обработку случаев, когда несколько значений имеют одинаковую частоту.

Советы и примечания: Убедитесь, что ваш диапазон (в примере — A2:A31) соответствует фактическим данным. Если в списке содержится менее двух уникальных чисел, формула может вернуть ошибку или неожиданный результат. Кроме того, если вы используете Office 365, Excel 2019 или более новую версию, вы можете применять динамические формулы массива — просто нажмите Enter.

Этот метод позволяет массово подсчитать количество вхождений каждого элемента с помощью функции «Расширенное объединение строк» из Kutools для Excel, после чего вы сможете определить второй по частоте встречающийся элемент и отсортировать значения по количеству вхождений, чтобы найти второе по частоте текстовое значение.

1. Выделите столбец с текстом или числами, который нужно проанализировать, а также соседний пустой столбец для указания количества вхождений. Затем перейдите в Kutools > Содержимое > Расширенное объединение строк.
Примечание: Для наглядности дайте вспомогательному столбцу заголовок, например «Количество».

2. В диалоговом окне «Расширенное объединение строк» установите выбранный столбец в качестве первичного ключа, а во вспомогательном столбце настройте подсчёт количества вхождений. Нажмите ОК, чтобы подтвердить.

3. Выделите столбец с количеством вхождений, затем перейдите в Данные → Сортировка от наибольшего к наименьшему, чтобы расположить значения по убыванию частоты.

4. Если появится предупреждение о сортировке, обязательно выберите Расширить выделенный диапазон и нажмите Сортировать. Это упорядочит строки так, что второй по частоте встречающийся элемент окажется в третьей строке.

Преимущества: Этот метод отлично подходит как для разделения по тексту и числам, так и для определения не только второго, но и третьего, четвёртого и последующих по частоте встречающихся значений.

Сортировка значений по частоте с помощью встроенных средств Excel или надстроек, таких как Kutools для Excel, — один из самых простых и наглядных способов быстро найти второе по частоте встречающееся значение, особенно при работе с большими наборами данных.

1. Выделите целевой столбец данных, затем откройте Enterprise > Расширенная сортировка.

2. Настройте диалоговое окно «Расширенная сортировка»: выберите нужный столбец из выпадающего списка «Столбец», установите Частота в качестве критерия сортировки и задайте порядок от Я до А.

3. Нажмите ОК. Значения автоматически отсортируются от самых частых к наименее частым — так вы сразу увидите второе по популярности значение в списке. Этот метод одинаково хорошо работает и с числами, и с текстом: он упрощает сравнение и не требует никаких формул.

Для пользователей, которые предпочитают автоматизировать повторяющиеся задачи или хотят избежать ручной настройки, собственный макрос VBA — эффективное решение для поиска второго по частоте встречающегося значения в любом диапазоне, будь то числа, текст или их комбинация. Решения на основе VBA особенно практичны, если вам регулярно требуется выполнять такой анализ на разных листах или вы предпочитаете обходиться без вспомогательных столбцов.

1. На ленте Excel нажмите Инструменты разработчика > Visual Basic. В открывшемся окне Microsoft Visual Basic для приложений выберите Вставка > Модуль, затем скопируйте и вставьте приведённый ниже код в модуль:

Sub SecondMostFrequentValue()
    Dim dict As Object
    Dim rng As Range
    Dim cell As Range
    Dim freqArr As Variant
    Dim i As Integer
    Dim j As Integer
    Dim keyArr As Variant
    Dim tempF As Variant
    Dim tempK As Variant
    
    Set dict = CreateObject("Scripting.Dictionary")
    xTitleId = "KutoolsforExcel"
    
    On Error Resume Next
    Set rng = Application.Selection
    Set rng = Application.InputBox("Range", xTitleId, rng.Address, Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            dict(cell.Value) = dict(cell.Value) + 1
        End If
    Next
    
    If dict.Count < 2 Then
        MsgBox "Not enough unique values.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    freqArr = dict.Items
    keyArr = dict.Keys
    
    For i = 0 To dict.Count - 2
        For j = i + 1 To dict.Count - 1
            If freqArr(j) > freqArr(i) Then
                tempF = freqArr(i)
                tempK = keyArr(i)
                freqArr(i) = freqArr(j)
                keyArr(i) = keyArr(j)
                freqArr(j) = tempF
                keyArr(j) = tempK
            End If
        Next j
    Next i
    
    MsgBox "Second most frequent value: " & keyArr(1) & vbCrLf & "Times: " & freqArr(1), vbInformation, xTitleId
End Sub

2. Нажмите кнопку (зелёный треугольник), чтобы выполнить код. После этого появится диалоговое окно с запросом на выбор диапазона. Выделите нужные данные и подтвердите выбор — второе по частоте встречающееся значение и количество его вхождений отобразятся автоматически.

Сводная таблица — это невероятно гибкий и удобный инструмент для сводного анализа больших объёмов данных в Excel. Она позволяет подсчитывать, сколько раз встречается каждое значение, сортировать их и мгновенно находить самое частое, второе или даже третье по популярности значение — независимо от того, содержат ли ваши данные числа или текст. Этот метод идеально подходит тем, кто предпочитает использовать встроенные возможности Excel без формул и надстроек.

1. Выделите любую ячейку в вашем наборе данных, затем нажмите Вставка > Сводная таблица. В диалоговом окне укажите нужный диапазон и выберите место размещения сводной таблицы (например, новый лист).

2 Перетащите столбец с данными (например, «Название товара» или «Значения») одновременно в области Строки и Значения на панели полей сводной таблицы. Затем измените поле «Значения» на «Количество [Имя столбца]».

3. Нажмите стрелку раскрывающегося списка в столбце «Метки строк» внутри сводной таблицы и отсортируйте данные от наибольшего к наименьшему. Самое частое значение появится вверху, а следующее по частоте — во второй строке.

Советы: Этот метод упрощает визуализацию распределения частот. Вы можете фильтровать или обновлять таблицу по мере изменения данных. Учтите, что пустые ячейки также учитываются, поэтому для повышения точности рекомендуем исключить их из анализа.

Сравнение: В отличие от решений на основе формул, метод со сводной таблицейНе требует сложного синтаксиса или продвинутых навыков работы в Excel и идеально подходит для динамических и постоянно обновляемых наборов данных.

Удаление дубликатов из списка в Excel

Найдите моду для текстовых значений из списка/столбца в Excel