컴활 1급 실기 독학 - (공부하면서 헷갈렸던 부분) 배열수식

※ 작성한 모든 글은 제가 공부한 내용과 강의를 바탕으로 하며, 부정확한 부분이 있을수도 있습니다. 

 

 

베열수식 

여러 셀의 행과 열에 대해 계산을 한번에 수행할 수 있는 수식을 배열수식이라고 합니다. 배열 수식에는 배열상수를 사용하는 방법과 특정 범위에 배열된 값들을 이용하는 방법이 있습니다. 

 

 

1. 배열상수 : 배열된 상수들의 집합

 

여기서 ',' 는 열 구분 ';'는 행을 구분하는 기호입니다.

 

1) 배열상수 {1,2,3,4,5}는 아래와 같이 배열된 상수들을 의미합니다(열 구분)

1

2

3

4

5

 

2) 배열상수 {1;2;3;4;5}는 아래와 같이 배열된 상수들을 의미합니다(행 구분)

1

2

3

4

5

 

ex> 정해진 범위에서 1~5번째로 큰 수를 한번에 구하려고 한다면 

=large(범위,{1,2,3,4,5}) or large(범위,{1;2;3;4;5})

 

→ 더 나아가서 이렇게 한꺼번에 구해진 수들을 다른 함수에 다시 넣을 수 있습니다.

 

예를 들어 정해진 범위에서 1~5번째로 큰 수들의 평균을 구하려고 한다면

=average(large(h3:h9,{1,2,3,4,5})) 또는 average(large( h3:h9,{1;2;3;4;5}))

=average({95,90,85,85,75}) 또는 average({95;90;85;85;75})

= 86

 

 

2. 범위지정

배열수식은 보통 조건에 맞는 범위를 반환하여 계산하는 문제들이 나옵니다. 이때 주로 사용되는 함수의 종류에는 sum,average,large,small,max,min,count가 있습니다. 

 

조건을 지정할 때는 문제에서 if를 제시했냐 하지않았냐에 따르게 다른 방식을 사용하게 됩니다. 보통 and 함수를 제시하지 않으면서, 모든 조건을 만족할 때 해당하는 범위를 반환하는 문제가 나옵니다. 

 

1) if를 제시한 경우

• if(조건1,범위) → 조건1을 만족하면 범위를 반환
• if((조건1)*(조건2),범위) → 조건1과 조건2를 모두 만족하면 범위를 반환
• and 함수 대신 *를 사용할 수 있는 이유는 and함수와 or 함수 글에 있으니, 참고바랍니다.( 주소 )

 

2) if를 제시하지 않은 경우

• (조건1)*범위 → 조건1을 만족하면 범위를 반환
• (조건1)*(조건2)*범위 → 조건1과 조건2를 모두 만족하면 범위를 반환

조건1	조건2	범위	*A1,0,0,0이 반환됨
true	true	A1	1*1*A1=A1
false	ture	A2	0*1*A2=0
true	false	A3	1*0*A3=0
false	false	A4	0*0*A4=0

 

 

sum(범위) 
average(범위)
max(범위)
min(범위)
count(범위)
large(범위,k)
small(범위,k)

 

배열수식의 대부분은 범위만 입력하면 되는 함수입니다.

따라서 범위 자리에 if(조건1,범위),(조건1)*범위 -> 조건 1을 만족하면 범위를 반환하는 함수를 넣어주기만 하면 됩니다.

sum( if(조건1,범위) )	sum((조건1)*범위 )
average( if(조건1,범위) )	average( (조건1)*범위 )
count( if(조건1,범위)	count( (조건1)*범위 ) * count는 반드시 if가 필요합니다
max( if(조건1,범위) )	max( (조건1)*범위 )
min( if(조건1,범위) )	min( (조건1)*범위 )
large( if(조건1,범위) ,k)	large( (조건1)*범위 ,k)
small( if(조건1,범위) ,k)	small( (조건1)*범위 ,k)

 

배열수식이 나오면 먼저 조건의 개수와 if의 유무에 따라

=sum(()*()*범위)

=sum(if(),범위)

=large(()*범위,k)

이렇게 먼저 조건과 범위를 입력해놓은 후, 문제를 읽고 괄호 안에 알맞은 조건을 써 넣으면 됩니다. 

그렇기 때문에, 굳이 배열 수식을 따로 암기할 필요는 없다고 생각합니다. 

 

 

3) 조건에 맞는 개수를 세는 함수(sum,count)

조건에 맞는 범위의 개수를 셀 때는 sum과 count 둘 중 하나의 함수를 사용하게 됩니다. 

여기서 두 함수의 차이는 sum은 if가 있어도 되고 없어도 되는 반면 count는 반드시 if가 있어야 한다는 겁니다. 

	sum	count
if가 없는 경우	=SUM((조건1)*1)
	=SUM((조건1)*(조건2))
if 제시된 경우	=SUM(IF(조건1,1))	=count(IF(조건1,1))
	=SUM(IF((조건1)*(조건2),1))	=count(IF((조건1)*(조건2),1))

함수				4개 중 3개만 1로 반환되는 경우
SUM((조건1)*1)	조건1=true(1)	1	1*1=1	sum(1,0,1,1)=3
	조건1=false(0)	1	0*1=0	count(1,0,1,1)=4
SUM((조건1)*(조건2))	조건1=true(1)	조건2=false(0)	1*0=1	sum(1,0,1,1)=3
	조건1=true(1)	조건2=true(1)	1*1=1	count(1,0,1,1)=4
SUM(IF(조건1,1))	조건1=ture(1)	1을 반환	1	sum(1,1,1)=3
	조건1=false(0)	아무 값도 반환하지 X		count(1,1,1)=3

 

이런 식으로 범위 A1:A4에 조건을 적용한 결과 반환된 값이 (1,0,1,1)이면 sum은 3이 나옵니다. 하지만 count는 0도 카운트를 하므로 4가 나옵니다. 그렇기 때문에 count는 반드시 if를 써서 조건을 만족하지 않으면 반환되는 값이 없도록 해줘야 합니다. 

 

 

 

<요약>
★ 배열수식을 굳이 암기할 필요 없다.
범위가 반환될 수 있도록 if(조건,범위), 조건*범위를 원래 함수에 범위가 들어가던 자리에 넣어주면 된다.
sum(범위) -> sum(if(조건,범위))

★ count는 반드시 if가 있어야 한다.