엑셀 세 열 이상의 여러 열에서 중복된 값 추출하는 방법

가상의 ETF 세 종목의 전략을 분석하기 위해 투자 종목을 비교 분석한다고 가정해 보겠습니다. 투자 전략을 비교하려면 공통점과 차이점을 알아야 하기 때문에 공통적으로 투자하고 있는 종목을 찾아보겠습니다.

 

이번에 활용하는 함수는 countif와 filter 함수 두 가지입니다.

 

엑셀에 A, B, C ETF가 각각 투자하고 있는 기업이 정리되어 있는 표가 있습니다. 이 표를 바탕으로 A와 B의 중복 데이터, A와 C의 중복 데이터를 먼저 찾고, 최종적으로 세 열에 모두 포함된 중복 값을 추출해 보겠습니다.

 

 

먼저 countif 함수를 활용해서 A와 B에서 중복되는 값을 찾아보겠습니다. 

=countif(B2:B11, A2:A11)라고 수식을 입력했을 때, 중복된 데이터가 있으면 1, 중복된 데이터가 없으면 0이 반환됩니다.

수식을 입력하고 엔터를 누르면 데이터가 있는 행까지 값이 자동으로 채워집니다.

 

 

이제 A와 C열에서 중복되는 값을 찾는 작업을 반복해 보겠습니다. 수식은 =countif(C2:C11, A2:A11)를 입력하면 됩니다. 마찬가지로 엔터키를 누르면 데이터가 있는 행까지 값이 자동으로 채워집니다.

 

여기에서 A열은 기준 열, C는 비교해서 중복값을 찾고 싶은 열입니다.

 

 

이제 새로운 열에 A와 B의 중복값, A와 C의 중복값을 찾은 각각의 결과를 곱해보겠습니다. 0을 1개 이상 곱하면 무조건 0이 반환되기 때문에, 모든 열에서 중복된 경우에만 1이 반환되게 됩니다.

 

만약 네 열 이상의 표에서 중복된 값을 찾고 싶다면 기준 열과 다른 열의 중복값을 찾는 과정(E열과 F열에서 수행한 과정)을 반복해 주면 됩니다.

 

 

이번에는 값이 자동으로 채워지지 않았기 때문에, H2를 클릭하고 우측 아래 초록색 점을 데이터가 있는 행까지 드래그하여 값 자동 채우기를 실행합니다.

 

 

마지막으로 필터 함수를 활용해서 중복된 값을 반환해 주겠습니다. 필터 함수는 주어진 조건에 따라 특정 값을 필터링해서 반환하는 함수입니다. 

 

=filter(반환하고 싶은 값, 필터링 조건을 지정하는 논리식)의 형태로 사용하면 됩니다.

우리가 반환하고 싶은 값은 중복된 주식 종목명이므로 A 열을 먼저 입력하고, 중복된 값은 H열에서 구했으므로 H열의 값이 1인 조건을 걸어주면 됩니다.

 

따라서 예제에서는 수식을 =filter(A2:A11, H2:H11=1)라고 입력하였습니다.

 

 

 

엔터키를 누르면 찾는 값이 모두 자동으로 채워집니다. 이렇게 a 대기업, b 대기업, c 대기업, d 대기업 네 종목이 공통적인 투자 종목임을 알게 되었습니다.

 

엑셀의 countif 함수와 filter 함수를 사용해 세 열 이상에서 공통으로 중복된 값을 찾는 방법을 알아보았습니다.