[컴퓨터활용능력 1급] 엑셀 배열수식

 

배열수식은 스프레드시트 영역에서 어려움 상위 3개 안에 든다.

괄호가 정말 헷갈리고 수식도 다 비슷비슷해서 헷갈린다. 정말 헷갈린다.

사실 아직도 헷갈려서 시험장에서 오류가 나올까봐 걱정이다.

 

배열(Array)이란 표 형태로 표현될 수 있는 값의 집합을 의미한다. 이러한 배열의 성질을 이용하여 작성하는 수식을 배열수식이라고 한다.

 

<배열수식에서 주의할 포인트>

1. 배열수식을 입력할 때에는 ctrl + shift + enter 를 눌러 수식입력을 종료한다.

2. 배열수식을 입력하면 자동으로 수식의 앞 뒤에 중괄호 ( { } )가 입력되어 배열수식임을 표시한다.

3. 조건식에서 대표문자의 사용이 불가능하다.

4. and 조건은 *, or 조건은 +를 사용한다.

( 시험에서는 거의 and조건만 나온다.)

5. true는 1, false는 0으로 변환되어 계산에 사용할 수 있다.

 

 

<시험에 출제되는 배열 수식 유형>

1. 합계를 구하는 배열수식

 

1) sum

-조건이 1개일 때

=sum((조건)*값을 구할 범위)

-조건이 2개일 때

=sum((조건1)*(조건2)*값을 구할 범위)

 

2) sum, if

-조건1개

=sum(if(조건,값을 구할 범위))

-조건2개

=sum(if((조건1)*(조건2),값을 구할 범위))

 

if가 있으면 반점(,)으로, if가 없으면 and(*)로 연결된다고 생각하면 쉽다.(아마)

 

 

 

2. 개수를 구하는 배열수식

값을 구하는 것이 아니기때문에 값을 구할 범위의 지정이 불필요하다.

 

1) sum

-조건1개

=sum((조건)*1)

*1은 true와 같다.

-조건2개

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

 

 

2) sum, if

-조건1개

=sum(if(조건,1))

-조건2개

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

if로 연 괄호와 sum으로 연 괄호 둘 다 닫아줘야함에 주의!

 

3) count, if

-조건1개

=count(if(조건,1))

-조건2개

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

sum, if를 사용해서 갯수를 구하는 바로 위의 수식과 같다.

 

 

 

3. 평균을 구하는 배열수식

average, if

-조건1개

=average(if(조건, 값을 구할 범위))

-조건2개

=average(if((조건1)*(조건2),값을 구할 범위))

 

 

 

4. 최대값을 구하는 배열수식

 

1) max

-조건1개

=max((조건1)*값을 구할 범위)

-조건2개

=max((조건1)*(조건2)*값을 구할 범위)

 

 

2) max, if

-조건1개

=max(if((조건), 값을 구할 범위))

-조건2개

=max(if((조건1)*(조건2),값을 구할 범위))

 

 

 

5. N번째 큰 값을 구하는 배열서식

 

1)large

-조건1개

=large((조건)*값을 구할 범위, N)

-조건2개

=large((조건1)*(조건2)*값을 구할 범위, N)

 

2) large, if

-조건1개

=large(if((조건),값을 구할 범위), N)

-조건2개

=large(if((조건1)*(조건2), 값을 구할 범위), N)

 

---

p. 143

출처 입력

출제유형1

배열수식(예제)시트

sum을 이용한 배열수식을 통해 성별이 남자인 항목의 금액 합계를 구하는 문제이다.

=SUM((B2:B6=B3)*C2:C6)

이렇게 식을 입력하고 꼭 ctrl + shift + enter 를 눌러줘야 한다.

만약 까먹고 그냥 엔터를 누르면 이렇게 오류가 난다.

ctrl + shift + enter를 치면 양쪽에 중괄호가 생기면서 배열수식이 적용된다.

인원수를 구하는 배열수식이다. 값을 구할범위가 아닌 *1을 써준다.

=SUM((B2:B6=B3)*1)

꼭 ctrl + shift + enter를 쳐준다.

 

---

출제유형 2

배열1(예제) 시트

average와 if를 이용한 배열수식으로 직무수행 평균을 구한다.

=AVERAGE(IF(($A$11:$A$33=$A4),$C$11:$C$33))

조건문의 범위와 값을 구해야 할 범위에 꼭 절대참조를 해주어야한다. 조건부분의 a4는 상대참조(또는 열고정 혼합참조)여도 상관없다.

마지막에는 꼭 ctrl + shift + enter

sum과 if를 이용하여 인원수를 구하는 배열수식이다.

=SUM(IF(($A$11:$A$33=A4),1))

조건문에서 절대참조를 잊지 않아야한다. 개수를 구하는 배열수식이기때문에 if문에서 값을 구할 범위가 아니라 1을 넣어서 수식을 완성한다.

마지막에는 꼭 ctrl + shift + enter

완성된 배열수식은 양쪽에 중괄호가 나온다.

배열수식에서는 괄호가 가장가장 중요하다. 열어준 괄호는 모두 닫아주어야 오류가 발생하지 않는다.

 

---

출제유형3

배열2(예제) 시트

 

sum을 이용해서 학과별 과목수를 구하는 배열수식이다.

=SUM(($A$3:$A$23=$I4)*($C$3:$C$23=J$3))

조건마다 꼭 괄호를 통해서 구해준다.

과목명은 열고정, 학과명은 행고정를 해주어야하고 조건 범위는 절대참조를 해주어야한다.

마지막에는 꼭 ctrl + shift + enter

자동채우기를 해도 나머지 셀들에 똑같이 배열서식이 적용된다.

average와 if를 통해서 학과별 점수의 평균을 구하는 것이다.

=AVERAGE(IF(($C$3:$C$23=$I12),D$3:D$23))

조건문에서는 절대참조와 열고정, 값을 구할 범위에는 행고정을 해주어야한다.

마지막에는 꼭 ctrl + shift + enter

자동채우기를 통해서 완성

---

출제유형3

배열2(예제) 시트

MAX와 IF 함수를 이용해서 분류코드별 최고 월평균임금을 구한다.

=MAX(IF(($A$8:$A$23=A3),$D$8:$D$23))

절대참조와 괄호에 주의한다. 값을 구할 범위에는 괄호를 하지 않아도 된다.

마지막에는 꼭 ctrl + shift + enter

자동채우기

SUM과 RIGHT함수를 이용해서 분류코드 SA의 업무코드별 종사자수의 합계를 구하는 배열수식이다.

식이 길고 복잡해서 순서대로 하나씩 입력하는 것이 좋다.

조건 1 : ($A$8:$A$23="SA")

조건2 : (RIGHT($B$8:$B$23,3)=E3)

값을 구할 범위 : $C$8:$C$23

이렇게 세 가지를 합쳐서 SUM 안에 넣어주고 괄호를 꼭 닫아준다.

=SUM(($A$8:$A$23="SA")*(RIGHT($B$8:$B$23,3)=E3)*$C$8:$C$23)

마지막에는 꼭 ctrl + shift + enter