엑셀 배열함수를 이용하여 여러 조건에 맞는 데이터를 찾아 값 구하기

여러 개의 조건에 맞는 데이터를 찾을 때 사용하는 것이 배열 함수입니다. 일반적으로 함수를 사용할 때는 데이터를 찾는 조건이 하나 또는 두 개 정도입니다. 하지만 그 이상의 조건을 비교하여 데이터를 찾으려면 배열 함수를 이용하여 찾는 것이 효율적입니다.

 

1. 주어진 조건에 맞는 데이터 수량 파악하기

셀의 개수를 구하는 배열 함수는 아래와 같이 사용합니다.

  

   {=SUM(IF((조건1)*(조건2)*...*(조건n),1,0}

 

   {=SUM(IF((조건1)+(조건2)+...+(조건n),1,0}

 

배열 함수는 모두 작성한 후 반드시 Ctrl + Shift + ENTER를 동시 클릭하여 입력을 마쳐야 하며, 중괄호({ })가 자동으로 입력됩니다. 첫 번째 식의 조건 사이의 ' * '는 논리 함수의 AND와 동일하고, 두 번째 식의 ' + '는 OR와 동일합니다. 즉, 조건 1 ~ 조건 n 까지 모두 만족하는 데이터를 찾게 됩니다.

 

 

 

[그림 1] 학생명부

[그림 1]과 같은 학생명부가 있습니다. 이 학생명부에서 성별과 탄생 월을 조건으로 해당하는 학생이 몇 명인지 알려고 합니다. 찾는 조건이 두가지입니다. 따라서 배열함수를 이용하여 찾아보겠습니다.

 

다음과 같이 N5셀에 아래와 같이 수식을 입력하고 Ctrl + Shift + ENTER를 동시에 누릅니다.

 

     =SUM(IF(($D$5:$D$15=L5)*(MONTH($E$5:$E$15)=M5),1,0))

 

[그림 2] 배열함수_*결과_인원수

 

L5 셀과 M5 셀은 찾으려는 조건인 성별과 탄생월을 기입하는 셀 주소입니다.

N5셀에 입력한 식을 살펴보면, D열의 값이 L5 셀과 동일하고 E열의 값이 M5 셀과 동일한 데이터를 찾으라는 의미입니다. 

 

식 중간에 있는 MONTH( ) 함수는 E열의 날자 데이터에서 월만 출력해주는 함수입니다.

[그림 2]를 보시면 성별이 남이고 탄생 월이 11월인 사람을 찾는 모습이고 조건에 맞는 사람 수는 N5셀에 0명으로 표시됩니다.

 

 이번에는 N5셀에 아래와 같이 수식을 입력합니다.

 

     =SUM(IF(($D$5:$D$15=L5)+(MONTH($E$5:$E$15)=M5),1,0))

 

조건 사이의 기호를 ' * '에서 ' + '로 변경하였습니다. [그림 3]은 변경된 식을 적용하여 [그림 2]와 동일한 조건으로 실행해 보았습니다.

 

[그림 3] 배열함수_+결과_인원수

[그림 3]의 결과를 보시면 성별이 남이거나 탄생월이 11월인 인원은 9명이라는 결과가 나옵니다.

 

 

 

2. 주어진 조건에 맞는 데이터 합계 파악하기

조건에 맞는 데이터의 지정된 값의 합계를 구하는 배열 함수는 다음과 같이 사용합니다.

 

   {=SUM(IF((조건1)*(조건2)*...*(조건n),합계를 구할 범위,0}

 

   {=SUM(IF((조건1)+(조건2)+...+(조건n),합계를 구할 범위,0}

 

[그림 4] 금전출납부

[그림 4]와 같은 금전출납부가 있을 때 알고자 하는 해당 월의 원하는 내역을 찾아서 지출 합계를 계산하고자 합니다. 이를 배열 함수를 사용하여 구해보겠습니다.

 

[그림 5] 배열함수_합계

[그림 5]를 보시면 첫 행에는 1월 교통비 합계를 다음 행에는 2월 외식비 합계를 구하였습니다.

[그림 5]의 금액 합계 항목에서 첫 번째 셀에 다음과 같이 수식을 입력합니다.

 

     =SUM(IF((MONTH($B$3:$B$17)=[@월])*($C$3:$C$17=[@내역]),$E$3:$E$17,0))

 

수식을 입력하고 Ctrl + Shift + ENTER를 동시에 클릭합니다. 그럼 수식에 중괄호({ })가 자동으로 입력됩니다. 수식 채우기를 이용하여 아래 행에도 수식을 입력합니다.