Как узнать количество наиболее часто встречающихся столбцов?

Question

Нужна помощь в выяснении, как это сделать в Excel.

У меня есть длинный список имен, разделенных на 2 столбца - имя и фамилия. Я хотел бы выяснить, какие 5 самых популярных полных имен, но - некоторые фамилии и имена поменялись местами, и поэтому я бы хотел, чтобы «Джон | Смит» также считался с «Смит | Джон». Как я могу сделать это в Excel?

Я попытался поместить оба столбца в сводную таблицу и поместить Имя и Фамилию в метки строк и счетчик Имя в значениях, но это дает мне длинный список каждого имени, смешанного с любым другим именем, и единственное Чтобы выяснить самое популярное имя, прокрутите всю таблицу и посчитайте наибольшее число, которое я вижу. Когда я добавляю фильтр значений "10 лучших" к результатам, верхние значения возвращаются по имени, имеющему наибольшую частоту, независимо от того, какая фамилия.

Например. Я могу иметь 5 имен:

John Smith
Smith John
Frank White
Frank Green
Frank Black

И если бы я применил фильтр «топ-10», это показало бы, что Фрэнк был самым распространенным именем, за которым следовал Джон Смит, хотя я действительно хочу видеть Джона Смита, поскольку он был там больше, чем любое другое имя.

Надеюсь, это имеет смысл. Если бы кто-нибудь мог дать некоторое представление о том, как я могу это сделать, я был бы очень признателен. Пожалуйста, дайте мне знать, если у вас есть какие-либо вопросы, спасибо!

РЕДАКТИРОВАТЬ: Относительно того, как определить, что такое имя и фамилия, порядок имен не имеет значения. Единственная проблема, которую я предвижу в этом, заключается в том, что будут дубликаты наиболее часто встречающихся имен, поэтому в приведенном мной примере "Смит Джон" и "Джон Смит" являются наиболее популярными именами, по 2 на каждое. (Если это имеет смысл.) Я согласен с этим, потому что очень легко посмотреть список топ-10 и обесценить любой другой результат.

Answer 1

edit: после 3-х правок вопроса это может работать лучше:

Предположим, что столбцы A и B содержат названия частей. Поместите это в столбец C:

=A1&" "&B1

Затем введите эту формулу в свободный столбец:

=(COUNTIF(C$1:C$7;$A1 & " " & $B1) + COUNTIF(C$1:C$7;$B1 & " " & $A1)) * NOT((COUNTIF(C$1:C1;$B1 & " " & $A1) > 0))

Это подсчитывает пары имен независимо от порядка и определяет, была ли обращенная пара видна раньше (обратите внимание на индекс конца выполнения в 3-м COUNTIF). Если да, счет будет установлен на ноль. Таким образом, будут учитываться только уникальные пары.

Используйте фильтр Top10 в этом столбце, чтобы найти наиболее частые.

Answer 2

Вы можете запустить этот код, чтобы найти все совпадения и сделать так, чтобы они выглядели одинаково, будь то first,last или last,first

Sub test()

Dim LastRow As Integer
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Dim fname As String
Dim lname As String

For i = 1 To LastRow
    fname = LCase(Cells(i, 1))
    lname = LCase(Cells(i, 2))
        For j = 1 To LastRow
            If LCase(Cells(j, 1)) = lname And LCase(Cells(j, 2)) = fname Then
                Cells(j, 1) = fname
                Cells(j, 2) = lname
            End If
        Next
Next

End Sub

Затем используйте эти данные для вашей сводной таблицы.