B5 셀에는 다음과 같은 수식이 있습니다.
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
A 열에 아래와 같은 데이터가 있는 경우 두 번 이상 발생하는 값이 3개(14, 16, 17) 있으므로 수식은 정답 3을 반환합니다. 이것이 바로 내가 알고 싶은 것입니다. 두 번 이상 나타나는 세 가지 값이 있습니다. 값이 무엇인지, 어디에 있는지 알 필요는 없습니다(비록 마지막 부분에 대한 좋은 정보는 있지만).
Column A:
12
13
14
14
14
15
16
16
17
17
그러나 A2:A10 범위의 아무 곳이나 빈 셀인 경우 1씩 감소하면 중복 값의 개수가 계산됩니다(위 예에서 수식은 3이어야 하는데 2를 반환합니다). 아래 샘플에서는 비어 있습니다. 셀은 "B"로 표시됩니다.
Column A:
12
13
14
B
14
15
16
16
17
17
이 예에서는 14, 16, 17이 각각 두 번 이상 발생합니다. 따라서 수식은 3을 반환해야 하지만 2를 반환합니다.
=SUMPRODUCT((A2:A10<>"")나는 이것이 본질적으로 비어 있지 않은 셀을 계산하는 첫 번째 부분 때문이라고 확신합니다 . 물론 두 개 이상의 빈 셀이 있는 경우 다시 증가하지만 이 역시 실제로는 비어 있지 않은 복제본을 생략하고 있기 때문에 옳지 않습니다(전혀 의미가 있는 경우).
답변1
참고: 귀하의 질문은9셀 범위이지만 표시되고 있습니다10예제의 값이 너무 많아서 합산되지 않습니다. 문제를 이해하지만.
수식의 첫 번째 부분은 공백이 아닌 다른 값의 수를 계산하는 표준 방법이므로 괜찮습니다. 하지만 두 번째 부분을 뺄 때 COUNTIF공백도 제외해야 하므로 &""를 제거해야 합니다. 두 번째 부분 COUNTIF, 즉 이 버전
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10)=1))
....하지만 이 버전이 더 좋습니다
=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))
두 수식 모두 A2:A10(또는 두 가지의 혼합)의 텍스트 또는 숫자 데이터에 대해 작동하지만 숫자 값에 대해서만(예제에 따라) FREQUENCY다음과 같은 함수를 사용할 수도 있습니다.
=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)