☰

다중 열 비교를 통해 가장 일반적인 값 반환

tag-icon • tag-icon microsoft-excel
다중 열 비교를 통해 가장 일반적인 값 반환

나는 6개의 문자열 열을 가지고 있으며 6개 열 모두 중에서 가장 일반적인 문자열을 찾고 있습니다.

이에 대한 도움을 주시면 대단히 감사하겠습니다.

열 1 열 2 열 3 칼럼4 칼럼 5 칼럼 6
사과 주황색 바나나 키위 브라우니 브로콜리
주황색 바나나 키위 브라우니 브로콜리
바나나 키위 브라우니 브로콜리
키위 브라우니 브로콜리
브라우니 브로콜리
브로콜리

결과는 브로콜리입니다. 열1, 행6 브로콜리가 없으면 결과는 브라우니/브로콜리가 됩니다.

열 1 열 2 열 3 칼럼4 칼럼 5 칼럼 6
화살뿌리 아티초크 아루굴라 아스파라거스 죽순 콩
사탕무 당근 셀러리 브라우니 브로콜리
바나나 키위 당근 마늘
초콜릿 칩 빵 치즈
주스 팝
작은 조각

결과는 당근이 될 것입니다.

답변1

다음이 작동합니다.

=LET(Source,A2:F7,
     ShortList,UNIQUE(FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN(",",TRUE,Source),",","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner")),
     Occurrences,COUNTIF(Source,ShortList),

 TEXTJOIN("/",TRUE,SORT(IF(Occurrences=MAX(Occurrences),ShortList,""))))

TEXTJOIN()공백을 없애고 목록을 모두 하나로 묶는 데 사용됩니다 . 그런 다음 FILTERXML()이를 HTML로 변환하고 Excel이 인식하는 배열로 나누는 트릭을 사용합니다. UNIQUE()그런 다음 존재하는 각 값의 단일 인스턴스 목록을 얻습니다.

그런 다음 COUNTIF()각 고유 항목에 대한 개수를 얻고, MAX()해당 개수 목록에서 가장 높은 값을 얻고, IF()각 고유 항목의 개수를 최대값과 비교하여 테스트하여 적격한 결과를 찾는 데 사용됩니다. SORT()해당 검증된 결과를 알파벳 순서로 배치합니다.

마지막으로 TEXTJOIN()검증된 결과를 가져와 원하는 출력 문자열에 맞게 형식을 지정합니다.

(정렬이 바람직하지 않거나 필요하지 않은 경우 해당 기능을 편집하면 됩니다. 나는 그것이 바람직할 것이라고 생각하고("Brownie/Broccoli" 문자열이 철갑이 아니고 원하는 결과를 빠르게 표시할 뿐이라고 생각함) 그것을 넣는 것을 생각했습니다. 그리고 그것을 편집하는 것이 넣지 않는 것보다 더 명확합니다. 그냥 "그럼 정렬하세요"라고 말하고 작업하도록 남겨두세요.)

LET()쉽고 논리적으로 구성되어 있습니다 . 작업할 범위가 즉시 시작 시 발생하고 거기에서만 편집이 쉽다는 점에서 "편리함"입니다. 현재 버전에서는 다른 것이 없으므로 상향식 프레젠테이션의 중간 계산 이름으로 이동합니다(공식에서 우선 순위를 설명하기 위해 "내부에서 외부로"라고 설명하는 것이 더 나을 수 있음). 그리고 마지막으로 결과 작업 공식입니다.

답변2

VBA로 작성된 사용자 정의 함수를 사용하여 이 작업을 수행할 수도 있습니다.

이 UDF를 입력하는 것은 쉽습니다.

이 사용자 정의 함수(UDF)를 입력하려면,

  • <alt-F11>Visual Basic Editor를 엽니다.
  • 프로젝트 탐색기 창에서 프로젝트가 강조 표시되어 있는지 확인하세요.
  • 그런 다음 상단 메뉴에서 다음을 선택하세요.Insert => Module
  • 열리는 창에 아래 코드를 붙여넣으세요.

이 사용자 정의 함수(UDF)를 사용하려면 =mostFrequent(A1:F6)일부 셀과 같은 수식을 입력하세요.

Option Explicit
Function mostFrequent(r As Range) As Variant()
    Dim arr As Variant, dict As Object
    Dim v
    Dim result(1)
    
'read range into vba array for faster processing
arr = r
Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = TextCompare
    
'read into dictionary and get the count of each item
For Each v In arr
    If Len(v) > 0 Then
        If Not dict.Exists(v) Then
            dict.Add Key:=v, Item:=1
        Else
            dict(v) = dict(v) + 1
        End If
    End If
Next v

'find max count
For Each v In dict.Keys
    If dict(v) > result(1) Then
        result(0) = v
        result(1) = dict(v)
    End If
Next v

'return most frequent string and it's count
mostFrequent = result
    
End Function

여기에 이미지 설명을 입력하세요

연산

  • 가장 빠른 처리를 위해 범위를 VBA 배열로 읽습니다.
  • 각 문자열을 사전에 입력하세요.
    • 키 = 문자열
    • 값 = 해당 문자열의 개수
  • 가장 높은 개수를 가지는 문자열을 반환
  • 아래 코드에서는 실제로 두 번째 요소가 항목 개수인 2요소 배열을 반환합니다.
    • Excel 버전에 따라 동적 배열 기능 또는 Index 함수를 사용하여 선택적으로 이를 반환할 수 있습니다.

관련 정보