☰

다른 두 열의 기준에 따라 한 열의 고유 값을 계산하려면 어떻게 해야 합니까?

tag-icon • tag-icon microsoft-excel-2010
다른 두 열의 기준에 따라 한 열의 고유 값을 계산하려면 어떻게 해야 합니까?

C열은 NO이고 D열은 YES인 A열의 고유 항목 수를 계산하려고 합니다. 그러나 두 가지 다른 기준에 따라 이를 수행하는 공식을 고안할 수는 없습니다. 어떻게 이런 일을 할 수 있습니까?

예를 들어 주문형 이벤트를 시청했지만 실시간으로 시청하지 않은 고유 국가 수를 알고 싶습니다. 다음 예에서는 4입니다.

country  |  Preview  |  Live  |  On Demand
GB       |  NO       |  NO    |  YES
GB       |  NO       |  YES   |  YES
ES       |  NO       |  YES   |  YES
DE       |  NO       |  NO    |  YES
FR       |  NO       |  NO    |  YES
US       |  NO       |  NO    |  YES

아래 답변에 제안된 수식에서 Excel이 다음 수식을 허용하도록 관리했지만 값을 반환하지 않습니다. 이것의 목적은 E가 1인 경우 F열에 1을 입력하는 것입니다. 어떤 도움이라도 주시면 감사하겠습니다.

=IF(E=1, IF(FREQUENCY(MATCH(A2:A214, A2:A214, 0), MATCH(A2:A214, A2:A214, 0)) > 0, 1, 0))

답변1

단순히 두 열의 값을 결합하는 수식과 새/고유 값을 계산하는 수식을 추가해 보시겠습니까?

E의 공식=$C:$C&$D:$D

F에 대한 공식 =IF(COUNTIF(E$1:E2,E2)=1,1,0)(이것은 fopr 셀(F2)의 예입니다.

country  |  Preview  |  Live  |  On Demand | Combined | New/unique
GB       |  NO       |  NO    |  YES       | NOYES    | 1
GB       |  NO       |  YES   |  YES       | YESYES   | 1
ES       |  NO       |  YES   |  YES       | YESYES   | 0
DE       |  NO       |  NO    |  YES       | NOYES    | 0
FR       |  NO       |  NO    |  YES       | NOYES    | 0
US       |  NO       |  NO    |  YES       | NOYES    | 0

답변2

다음 배열 수식을 사용하여 모든 기준을 충족하는 고유 레코드 수를 얻을 수 있습니다. 수식 입력줄에 다음을 붙여넣고 Ctrl+ Shift+ 를 누릅니다 Enter.

=SUMPRODUCT((C2:C8="NO")*(D2:D8="YES")/IF(COUNTIFS(A2:A8,A2:A8,C2:C8,"NO",D2:D8,"YES")=0,1,COUNTIFS(A2:A8,A2:A8&"",C2:C8,"NO",D2:D8,"YES")))

길고 약간 반복적이지만 효과가 있을 것입니다. 이 공식은 1/COUNTIF(...)표시된 트릭을 사용합니다.많은 타임스고유한 기록을 계산하기 위해 과거에 이 사이트에 있었습니다. 오류가 IF발생하지 않도록 반복 조건을 도입해야 했습니다 #DIV/0!.

공식의 역할은 다음과 같습니다.

SUMPRODUCT하나의 배열 인수를 사용하면 배열의 요소만 추가됩니다. 곱셈 조건은 둘 중 하나라도 만족하지 않으면 0이 되고, 둘 다 만족하면 1이 됩니다. 로 나누는 것은 COUNTIFS이 값의 크기를 조정하여 고유 개수를 얻는 방법입니다. 예를 들어 GB주문형에는 있지만 라이브에는 없는 두 개의 레코드가 있는 경우 해당 레코드 각각은 1/2총 합계에서 하나의 레코드로 계산되도록 축소됩니다. 그러한 레코드가 3개 있는 경우 각각은 1/3. 이렇게 하면 합계에 이러한 레코드가 추가될 때 GB라이브가 아닌 On Demand의 총 합계는 1이 됩니다(예: 1/2+ 1/2= ) 1. 이것이 독특함이 포착되는 방식입니다.

답변3

Makro를 만들고 이름을 "CountMyUniqueEntries"로 지정합니다. VBA 편집기를 열고 다음 코드 조각을 Module1에 복사합니다.

Sub CountMyUniqueEntries()
    Dim cells As Range: Set cells = Excel.Selection
    'the selection does only contain data, no column-headers
    Dim i
    Dim uniqueCountries As New Collection
    For i = 1 To cells.Rows.Count
        Dim Live As Boolean: Live = (UCase(cells(i, 3)) = "YES")
        Dim OnDemand As Boolean: OnDemand = (UCase(cells(i, 4)) = "YES")
        Dim country As String: country = UCase(cells(i, 1))
        If Not Live And OnDemand Then
            If Not Contains(uniqueCountries, country) Then
                uniqueCountries.Add country, country
            End If
        End If
    Next
    MsgBox "The number of unique countries is: " & uniqueCountries.Count 
End Sub
Private Function Contains(col As Collection, ByVal entry As String) As Boolean
    On Error Resume Next
    If IsEmpty(col(entry)) Then: 'DoNothing
    Contains = (Err.Number = 0)
    On Error GoTo 0
End Function

어쩌면 "옵션"에서 "개발자 도구" 메뉴를 먼저 활성화해야 할 수도 있습니다.

답변4

이는 다소 복잡한 공식이 되는 경향이 있습니다. 나는 그것을 여러 조각으로 나누어 이것이 어떻게 작동하는지 더 명확하고 분명해질 것입니다.
이미 4개의 열 A, B, C, D가 있습니다. 4개의 열 E, F, G, H가 더 필요합니다.

E열에서 이 수식을 모든 셀에 복사합니다(첫 번째 셀의 오른쪽 하단에 +부호를 사용하여 복사).

=IF(C2="NO", IF(D2="YES", 1, 0), 0)

F열에서 이 수식을 첫 번째 셀에 복사합니다.

=IF(FREQUENCY(MATCH(A2:A8, A2:A8, 0), MATCH(A2:A8, A2:A8, 0)) > 0, 1, 0)

F열의 모든 셀을 선택하고 "F2"를 누른 다음 "Ctrl"+"Shift"+"Enter"를 누릅니다.

G열에서 이 수식을 모든 셀에 복사합니다.

=IF(E2=1, IF(F2=1, 1, 0), 0)

H열에 추가 셀을 사용하고 G의 1을 다음과 같이 합산합니다.

=SUM(G:G)

재미있게 보내세요

관련 정보