엑셀 입력된 값을 비교하여 같은 값이 개수 찾기 (SUMPRODUCT, ISNUMBER, MATCH 함수)

기준이 되는 6개의 숫자가 있고 다른 6개의 숫자를 입력하고 몇개가 같은지 알 수있는 수식을 작성해보겠습니다. 

예시)

오른쪽에 1,2,3,4,5,6 이 작성되어 있습니다. 왼쪽에 1,5,6,8,3,4 가 작성 되어 있습니다. 같은 숫자가 있는 것은 1, 3, 4, 5, 6로 5개 입니다. 이렇게 값을 찾고 싶어요.라는 질문 이었습니다. 

 

 

---

두 목록에서 중복된 값 찾기: 엑셀 수식 활용법

두 열에서 중복된 숫자의 개수를 계산하는 방법을 소개하겠습니다.

 

1. 문제 상황

예를 들어, 다음과 같은 두 열의 데이터가 있다고 가정해봅시다:

A열 (A1:A6)B열 (B1:B6)

1	1
5	2
6	3
8	4
3	5
4	6

여기서 **A열과 B열에 모두 존재하는 값(중복된 값)**의 개수를 계산하고 싶습니다. 위 데이터를 보면 1, 3, 4, 5, 6이 중복되므로 총 5개가 중복됩니다.

---

2. 해결 방법: SUMPRODUCT와 MATCH 수식

엑셀에서는 다음과 같은 수식을 사용하여 두 목록에서 중복된 값을 계산할 수 있습니다.

사용할 수식

 

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A6, B1:B6, 0)))

수식 입력 위치

• 결과를 표시할 셀(예: C1)에 위 수식을 입력합니다.

---

3. 수식의 작동 원리

이 수식은 여러 함수를 조합하여 중복된 값의 개수를 계산합니다. 각 함수의 역할을 아래에서 하나씩 설명하겠습니다.

(1) MATCH(A1:A6, B1:B6, 0)

• MATCH 함수는 A열의 각 값이 B열에서 어느 위치에 있는지 찾습니다.
• 정확히 일치하는 값만 확인하기 위해 옵션 0을 사용합니다.
• 결과:
  • 값이 존재하면 위치(숫자)를 반환합니다.
  • 값이 없으면 #N/A를 반환합니다.

(2) ISNUMBER(MATCH(...))

• ISNUMBER 함수는 MATCH의 결과가 숫자인 경우 TRUE를, #N/A인 경우 FALSE를 반환합니다.
• 결과:
  • 중복된 값 → TRUE
  • 중복되지 않은 값 → FALSE

(3) --ISNUMBER(...)

• TRUE와 FALSE 값을 각각 숫자 1과 0으로 변환합니다.
• 이 과정을 통해 숫자로 계산이 가능해집니다.

(4) SUMPRODUCT(...)

• 변환된 숫자 값을 모두 더합니다.
• 결과적으로, 중복된 값의 개수를 계산합니다.

---

4. 예제 풀이

위 데이터를 기준으로 수식을 계산하면 다음과 같은 결과가 나옵니다.

1. MATCH 결과:
   • A1=1 → B열에서 위치 1
   • A2=5 → B열에서 위치 5
   • A3=6 → B열에서 위치 6
   • A4=8 → #N/A (없음)
   • A5=3 → B열에서 위치 3
   • A6=4 → B열에서 위치 4
2. ISNUMBER 결과:
   • TRUE, TRUE, TRUE, FALSE, TRUE, TRUE
3. --ISNUMBER 결과:
   • 1, 1, 1, 0, 1, 1
4. SUMPRODUCT 결과:
   • 1 + 1 + 1 + 0 + 1 + 1 = 5

결과적으로, 중복된 숫자는 5개입니다.

---

5. 실전 활용 팁

이 수식은 아래와 같은 다양한 상황에서 유용하게 사용할 수 있습니다:

• 고객 ID 중복 확인: 여러 목록에서 동일 고객의 개수를 파악
• 시험 정답 개수 계산: 제출한 답안과 정답지를 비교
• 상품 코드 비교: 다른 두 시스템의 데이터 정합성 검토

---

6. 정리

두 목록에서 중복된 값을 찾는 것은 데이터 분석에서 자주 필요한 작업입니다. 엑셀의 SUMPRODUCT와 MATCH 함수를 활용하면 쉽고 빠르게 이 작업을 수행할 수 있습니다.

시간을 절약하고 데이터를 효과적으로 분석하고 싶다면 이 방법을 꼭 활용해 보세요! 😊