월별로 중복값 제외, 고유값 개수 구하기(SUM, COUNTIF, ADDRESS, INDIRECT)

1월부터 12월까지 일별로 강사료를 지급한 엑셀 시트가 있습니다. 이 시트를 활용해 매달 몇명의 강사에게 얼마의 강의료를 지급했는지 확인하고 소득세를 국세청에 신고해야 합니다. 월별로 중복되는 강사의 이름은 제외하고 교유한 강사이름만 카운트해 보겠습니다. 

 

[SUM, COUNTIF] 중복없이 강사 인원수 구하기

COUNTIF함수 활용

COUNTIF함수는 지정한 범위 내에세 조건에 맞는 셀의 개수를 구합니다.

B2셀에 COUNTIF함수를 이용해

강사의 이름이 있는 열(B2:B15)을 범위로 지정하고

해당 열을 조건(B2:B15)으로 입력하면

=COUNTIF(B2:B15,B2:B15)

강사의 이름 옆에 지정된 범위에서

몇번 중복되는지 확인 할 수 있습니다.

1/COUNTIF()함수 설명

D2에 1/COUNTIF(강사범위, 강사범위)를 입력하면

한번만 강의료를 받은 강사는 "1",

여러번 강의료를 받은 강사는 "0.XXX"형태로

값을 받을 수 있습니다. 

고유한 값 개수 구하기

SUM함수로 1/COUNTIF(강사범위, 강사범위)를 모두 더하면

고유한 강사의 인원수를 구할 수 있습니다. 

 

 

[ADDRESS, INDIRECT]월별로 중복없이 강사인원 구하기

엑셀시트에는 1월부터 12월까지 매일 매일 강사료가 입력되어 있습니다. 월별로 고유한 강사 인원수를 구해 보겠습니다. 

SUM, COUNTIF함수로 지정된 셀의 범위에서 중복값을 제외한 고유값 찾을 수 있습니다.

그렇다면 월별로 셀 범위를 지정해 줄수 있다면 월별로 고유한 강사인원을 구할 수 있습니다.

 

 

월별 강사이름 셀범위 찾기

ADDRESS함수는 지정된 행/열 번호를 가지고 셀 주소를 나타내는 텍스트를 만들어 주는 함수입니다.

시트에서 각 월이 시작하는 행번호와 월이 끝나는 행번호를 찾아 셀주소를 표시해 보겠습니다.

 

1월의 시작 셀주소 : ADDRESS(MATCH(E2,B:B,0),3) = $C$2,

-ADDRESS(MATCH(E2,B:B,0),3) : B열에서 1월에 해당하는 첫번째 행은 2행이며

-ADDRESS(MATCH(E2,B:B,0),3) :  열번호 3은 C열을 나타내므로 셀주소는 C2가 됩니다.  

 

1월의 끝 셀주소 : ADDRESS(MATCH(E2,B:B,0)+COUNTIF(B:B,E2)-1,3) = $C$15, 

- ADDRESS(MATCH(E2,B:B,0)+COUNTIF(B:B,E2)-1,3) : B열에서 1월에 해당하는 첫번째 행은 2행이며

- ADDRESS(MATCH(E2,B:B,0)+COUNTIF(B:B,E2)-1,3) : B열에서 1월에 해당하는 셀개수는 14개이며 월을 시작하는 1를 빼주면 해당월의 마지막날의 행번호를 찾을수 있습니다. 

 

월의 시작과 끝을 나타내는 셀주소 합치기

= ADDRESS(MATCH(E2,B:B,0),3)&":"&ADDRESS(MATCH(E2,B:B,0)+COUNTIF(B:B,E2)-1,3)

=$C$2:$C$15(1월에 해당하는 강사이름의 셀범위)

 

 

SUM, COUNTIF함수에 적용하기

 

 

ADDRESS와 MATCH함수로 찾은 셀범위를

SUM(1/COUNTIF(셀범위,셀범위) 함수에 적용합니다. 

그러나 ADDRESS로 구한 셀범위는 텍스트이므로

INDIRECT함수로 셀 주소로 바꿔주면 됩니다.

 

=SUM(1/COUNTIF(INDIRECT(1월범위),INDIRECT(1월범위))

1월에 중복없는 강사의 이름이 몇개인지 찾음.!