컴퓨터활용능력 1급 기출 엑셀 문제풀이 - 8 (배열수식)

오늘은 "계산작업"문제 중 "배열수식"에대해 알아보도록 하겠습니다.

 

"배열수식"문제는 "계산작업"문제 중 한문제 혹은 두문제 이상 반드시 출제되는 문제면서도 다소 낯선 접근이 필요한 함수문제입니다.

 

포기하고 가기에는 두문제 이상 출제되는 경우가 많아 부담스러운 문제로 꼭 한번씩 짚고 넘어가야할 필요가 있습니다.

 

이 또한 원리만 알면 적용이 쉬우니 꾸준한 연습을 통해 "배열수식"을 마스터하는 것이 중요합니다.

그럼 문제를 함께 보기전에 배열수식에대해 알아보도록 하겠습니다.

 

"배열수식"은 기존 함수가 기준셀을 한개로 설정하는 것과 달리 범위로 설정하는 차이가 있습니다. 또한, 조건과 범위를 "*"로 이어 입력하는데요. 여기서 "*"는 곱하기의 의미가 아닌 항목을 구분하는 역할로 사용합니다.

 

쉽게 설명하면 배열수식은 대응되는 셀끼리의 각각 계산한 결과값을 셀 범위를 이용해서 나타내는 수식이라 할 수 있겠습니다. 

 

사용방법은 기존 함수와 차이가 있어 숙지가 반드시 필요합니다.

 

- 조건 1개일 때의 합

= sum(if(조건,1))

=sum((조건)*1)

 

- 조건 1개일 때의 개수

=count(if(조건),1))

 

- 범위에서 조건에 만족하는 데이터들의 합

=sum((조건)*범위)

=sum(if(조건,범위))

=sum((조건1)*(조건2)*범위)

=sum(if((조건1)*(조건2),범위))

 

if와 함께 사용하거나 함수 혼자 단독으로 사용하는 것 두개 다 가능하지만, 문제에서 if의 사용여부를 알려주므로 이를 이용해 둘 중 한 방법을 선택하여 사용하면 되겠습니다.

 

이러한 형태 중 문제에 맞게 선택하여 입력하신 후에 반드시 ctrl + shift + enter 로 입력하셔야 작동이 됩니다.

평균을 구하라할때는 average, 최대값은 max 등 문제에서 주어진 조건과 상황, 함수에 맞게 함수를 바꿔주셔서 설정해주시면 되겠습니다.

 

자 그럼 문제를 함께 풀면서 적용을 해보도록 하겠습니다. 

 

문제를 살펴보면 [표1]의 과목ID와 학과를 이용해, [표3]에 인원수를 계산하여 표시하는 문제입니다.

인원수를 카운팅하는 것이다보니 문제에서는 COUNT함수가 주어졌고, IF함수도 주어졌으므로 우리는 COUNT와 IF를 합친 배열수식을 입력하면 되겠습니다.

 

또한, 강의코드는 과목ID의 첫번째 글자, 그리고 LEFT함수를 이용하라 하였으므로 LEFT(셀,1)의 형태로 강의코드를 추출하면 되겠습니다.

 

그럼 [표1]과 [표3]을 보면서 문제를 설명하겠습니다.

 

먼저 [표3]의 강의코드와 [표1]의 강의코드 맨 첫번째자리가 같은 것의 개수 + 학과가 같은 것의 개수를 세어야 하기때문에 COUNT함수를 사용합니다.

 

조건이 2개이고 IF를 사용하는 배열수식이므로 

 

=COUNT(IF((조건1)*(조건2),1)을 입력하고 ctrl + shift + enter을 입력해주면 되겠습니다.

 

먼저 조건1은 [표1]의 강의코드 앞자리가 [표3]의 강의코드와 일치하는 것입니다. 

 

(LEFT($A$4:$A$24,1)=$A32) 로 설정하는데요.

 

A4:A24는 강의코드 데이터들이고 절대참조를 시켜줘야합니다. 그리고 A32는 [표3]의 강의코드로 A에만 절대참조를 해주는 이유는 채우기 핸들을 이용할시 A33, A34의 영역의 데이터로 적용해줘야 하기 때문입니다.

 

그 다음으로 두번째 조건은 학과가 [표3]의 학과와 같아야 하므로

 

($C$4:$C$24=B$31)로 설정하면 되겠습니다. 여기서 B31에서 31에만 절대참조를 한 이유는 위의 이유와 비슷하게 채우기 핸들을 이용할시 옆에 데이터들로 적용해줘야 하기 때문에 B에는 절대참조를 해주지 않습니다.

 

그리고 조건들을 입력한 후 쉼표와 1을 입력해주고 괄호를 닫으면 되겠습니다.

 

정리하면

 

=COUNT(IF((LEFT($A$4:$A$24,1)=$A32)*($C$4:$C$24=B$31),1))

 

형태로 입력 후, ctrl + shift + enter를 실행해주면 되겠습니다.

 

채우기 핸들을 이용해 남은 셀에도 값을 채워주시면 완성입니다.

 

오늘은 이렇게 배열수식에 대해 알아봤는데요. count함수를 이용했지만 다른 문제에서는 sum, average, max 등등 여러 함수와 함께 사용되며 무궁무진하게 응용될 수 있으므로 배열수식과 관해서는 여러문제를 풀어보면서 익숙해져야할 필요가 있습니다.