엑셀 배열 수식, 배열 함수, 배열에서 값 찾기, 컴퓨터활용능력 1급 배열함수 정리

 

엑셀 배열 수식, 배열 함수, 배열에서 값 찾기, 컴퓨터활용능력 1급 배열함수 정리

 

 

[링크]엑셀 함수 총정리 및 사용 예제

[링크]메모리스트 엑셀 함수 정리 목록 및 엑셀 팁 정리 목록

[링크]메모리스트 엑셀 카테고리 목록

 

엑셀은 실무에서 매우 유용하게 사용이 됩니다.

그렇다보니 실무에서 사용할 수 있는 실력이 되는지를 확인하기 위해 컴퓨터활용능력 자격증 소지자를 회사에서도 선호하고 있지요.

컴퓨터활용능력 시험은 엑셀의 다양한 기능들에 대해 테스트하지만 그 중에서도 계산작업에 해당하는 함수 사용법은 많이들 어려워하지요.

컴퓨터활용능력 2급이나 3급의 경우 함수의 사용법에 대해 이해하면 큰 어려움없이 해결이 가능합니다.

함수 각각에 대한 이해가 어렵다면 시험에 자주 출제되는 함수별로 1번째 인수에는 어떤 값을 넣고, 2번째 인수에는 어떤 값을 넣는지 암기해서라도 문제를 풀 수가 있습니다.

 

하지만 컴퓨터활용능력 1급의 경우 함수 각각에 대한 이해도 필요하며, 특히 배열함수의 경우 배열함수에 대한 이해가 없다면 풀기가 어려운 문제입니다.

대부분의 회사에서는 컴퓨터 활용능력 2급 시험에 나오는 함수 정도만 알아도 업무처리에 어려움이 없지만, 배열함수를 사용할 경우 원하는 자료를 쉽게 추출할 수 있어서 반드시 컴퓨터활용능력 1급 자격증 소지자만 뽑는 곳도 있습니다.

 

배열함수를 이용해서 값을 추출하기 위해서는 함수의 사용법에 대한 이해도 필요하지만 배열함수를 어떻게 사용하는지에 대해서도 이해를 할 필요가 있습니다.

 

컴퓨터활용능력 1급 대비책이 많이 나와있지만 책을 보더라도 배열함수를 쉽게 이해하기가 어려워서 배열함수를 쉽게 해결할 수 있는 방법에 대해 설명하도록 하겠습니다.

 

단, 배열함수를 배우기 위해서는 기본적인 함수의 사용법은 사전에 이해하고서 접근하셔야만 합니다.

 

우선 배열이라는 개념에 대해 설명하겠습니다.

배열이란 1개의 값이 아니라, 여러 개의 값을 표 형태로 저장한 데이터를 뜻합니다.

예를 들어 지역이라는 배열에 { 서울, 부산, 대구, 인천, 대전 } 과 같이 여러 개의 데이터가 들어있는 형태입니다.

 

배열함수는 수식을 작성 후 CTRL + SHIFT + ENTER 를 눌러서 작성한 수식을 배열함수로 작성하게 됩니다.

일반적으로 함수를 사용한 경우 수식은 =함수(계산식) 의 형태이지만,

배열함수를 사용한 경우 수식은 {=함수(계산식)} 의 형태를 갖게 됩니다.

수식 형태를 좀 더 세부적으로 나누면 {=함수((조건)*(영역))} 형태를 갖게 됩니다.

 

조건이 2개인 경우 {=함수((조건)*(조건)*(영역))} 형태를 가지게 되며,

조건이 3개인 경우 {=함수((조건)*(조건)*(조건)*(영역))} 형태를 가지게 되고,

조건이 4개인 경우 {=함수((조건)*(조건)*(조건)*(조건)*(영역))} 형태를 가지게 됩니다.

이처럼 조건이 늘어나게 되면 조건만 추가해서 수식을 완성해 주면 됩니다.

 

또는 아래의 형태처럼 조건을 주기도 합니다.

조건에 따라  {=함수(((조건)*(영역))*((조건)*(영역)))} 형태를 가지기도 합니다.

 

배열함수에서 자주 사용하는 형태는 대략 다음과 같습니다.

 

ㅇ SUM 함수를 사용하는 경우

ㅇ COUNT + IF 함수를 사용하는 경우

ㅇ AVERAGE + IF 함수를 사용하는 경우

ㅇ MAX + IF 함수를 사용하는 경우

ㅇ MIN + IF 함수를 사용하는 경우

ㅇ MEDIAN + IF 함수를 사용하는 경우

ㅇ LARGE + IF 함수를 사용하는 경우

ㅇ SMALL + IF 함수를 사용하는 경우

등등

 

SUM 함수를 사용한 배열함수 예제

 

아래 그림은 SUM 함수를 사용한 배열함수 예제를 설명하기 위한 화면입니다.

 

 

아래 동영상은 SUM 함수를 사용해 지역이 서울인 곳의 판매량 합계를 구하는 과정으로, 바로 아래의 동영상은 SHIFT + F3 키를 눌러서 함수마법사를 띄운 화면이고, 그 아래쪽 동영상은 모든 수식을 직접 입력한 예제입니다.

수식 입력이 모두 완성된 후에는 수식을 배열함수로 만들기 위해 CTRL + SHIFT + ENTER 키를 눌러서 수식의 좌우에 중괄호 {} 가 표시되도록 해 주어야 합니다. 중괄호 {}는 직접 입력해서는 안되고, 반드시 CTRL + SHIFT + ENTER 키를 눌러서 배열함수가 되도록 해 주어야 합니다.

 

 

 

 

 

위 예제의 E4 셀 내용은 다음과 같습니다.

{=SUM((B4:B13="서울")*(C4:C13))}

 

위의 수식은 위에서 언급했던 것처럼 {=함수(계산식)} 형식이며, 좀 더 세부적으로 보면 {=함수((조건)*(영역))} 형태입니다.

 

위 식에서 조건은 B4:B13="서울" 이고, 괄호로 둘러싸여 있습니다.

영역은 C4:C13 이고, 괄호로 둘러싸여 있습니다.

 

괄호를 생략할 경우 연산 순서에 의해 원하는 값이 나오지 않으므로 조건과 영역은 반드시 괄호로 둘러싸는 것 잊지 마시기 바랍니다.

 

조건은 함수로 IF 함수와 동일한 형태입니다.

위의 조건인 지역이 서울인 경우 참(TRUE)을 반환하며, 그렇지 않을 경우 거짓(FALSE)를 반환합니다.

참(TRUE)은 1의 값을 가지며, 거짓(FALSE)은 0의 값을 가지게 됩니다.

 

위의 배열 수식을 이해하기 쉽도록 설명하기 위해 어떻게 배열로 구성되어 있는지를 풀어보면 다음과 같습니다.

 

=(B4="서울") * C4

=(B5="서울") * C5

=(B6="서울") * C6

=(B7="서울") * C7

=(B8="서울") * C8

=(B9="서울") * C9

=(B10="서울") * C10

=(B11="서울") * C11

=(B12="서울") * C12

=(B13="서울") * C13

 

=(B4="서울") * C4 의 경우 B4 셀이 "서울"이므로 참이며, 1의 값을 갖게 됩니다.

=(B5="서울") * C5 의 경우 B5 셀이 "서울"이 아니므로 거짓이며, 0의 값을 갖게 됩니다.

이처럼 참인 경우와 거짓인 경우의 판매량을 대입하면 다음과 같은 형태가 됩니다.

 

=(B4="서울") * C4 → B4 셀이 "서울"이므로 15

=(B5="서울") * C5 → B5 셀이 "서울"이 아니므로 0

=(B6="서울") * C6 → B6 셀이 "서울"이 아니므로 0

=(B7="서울") * C7 → B7 셀이 "서울"이 아니므로 0

=(B8="서울") * C8 → B8 셀이 "서울"이므로 32

=(B9="서울") * C9 → B9 셀이 "서울"이 아니므로 0

=(B10="서울") * C10 → B10 셀이 "서울"이 아니므로 0

=(B11="서울") * C11 → B11 셀이 "서울"이 아니므로 0

=(B12="서울") * C12 → B12 셀이 "서울"이므로 26

=(B13="서울") * C13 → B13 셀이 "서울"이 아니므로 0

 

지역이 서울인 C4 셀의 15와 C8 셀의 32, C12 셀의 26 을 더한 값인 73을 구하게 되는 것입니다.

 

SUM + IF 함수를 사용한 배열함수 예제

 

SUM 함수와 IF 함수를 함께 사용해서 지역이 서울인 곳의 개수를 구해보도록 하겠습니다.

아래 그림은 지역이 서울인 곳의 개수를 구하는 화면입니다.

 

 

SUM 함수와 IF 함수를 함께 사용하는 경우는 SUM 함수를 사용하는 형태와 약간의 차이가 있습니다.

 

수식은 {=함수(계산식)} 형식이며, 좀 더 세부적으로 보면 {=함수(IF(조건,1,0))} 형태입니다.

IF 조건이 맞으면 1을 더하고, 맞지 않으면 0을 더하는 방식으로 개수를 구하는 것입니다.

 

위의 그림에서 사용된 수식은 다음과 같습니다.

 

{=SUM(IF(B4:B13="서울",1,0))}

 

IF 조건만 따로 떼어서 보면 다음과 같습니다.

 

IF(B4:B13="서울",1,0)

 

위의 수식을 풀어서 설명하면 B4 셀부터 B13 셀까지의 셀이 "서울이면 1, 그렇지 않으면 0 이라는 의미입니다.

이 값들을 SUM 함수를 이용해 합계를 낼 경우 개수를 구하게 되는 것입니다.

 

위의 배열수식인 {=SUM(IF(B4:B13="서울",1,0))} 를 풀어서 보면 다음과 같습니다.

 

=SUM(

IF(B4="서울",1,0)

IF(B5="서울",1,0)

IF(B6="서울",1,0)

IF(B7="서울",1,0)

IF(B8="서울",1,0)

IF(B9="서울",1,0)

IF(B10="서울",1,0)

IF(B11="서울",1,0)

IF(B12="서울",1,0)

IF(B13="서울",1,0)

)

 

실제 대입되는 값을 정리해 보면 다음과 같습니다.

 

=SUM(

IF(B4="서울",1,0) → B4 셀이 "서울"이므로 1

IF(B5="서울",1,0) → B5 셀이 "서울"이 아니므로 0

IF(B6="서울",1,0) → B6 셀이 "서울"이 아니므로 0

IF(B7="서울",1,0) → B7 셀이 "서울"이 아니므로 0

IF(B8="서울",1,0) → B8 셀이 "서울"이므로 1

IF(B9="서울",1,0) → B9 셀이 "서울"이 아니므로 0

IF(B10="서울",1,0) → B10 셀이 "서울"이 아니므로 0

IF(B11="서울",1,0) → B11 셀이 "서울"이 아니므로 0

IF(B12="서울",1,0) → B12 셀이 "서울"이므로 1

IF(B13="서울",1,0) → B13 셀이 "서울"이 아니므로 0

)

 

B4 셀과 B8 셀, B12 셀의 값인 1을 모두 더하면 3개가 되는 것입니다.

 

COUNT + IF 함수를 사용한 배열함수 예제

 

위의 SUM 함수와 IF 함수를 사용해 지역이 서울인 곳의 개수를 구하는 예제를 COUNT 함수와 IF 함수를 사용해 풀어보도록 하겠습니다.

아래 그림은 COUNT 함수와 IF 함수를 사용해 지역이 서울인 곳의 개수를 구하는 예제입니다.

 

 

수식은 {=함수(계산식)} 형식이며, 좀 더 세부적으로 보면 {=함수(IF(조건,1))} 형태입니다.

 

E10 셀의 수식은 다음과 같습니다.

 

{=COUNT(IF(B4:B13="서울",1))}

 

위의 수식을 풀어서 설명하면 B4 셀부터 B13 셀까지의 셀이 "서울이면 1 이라는 의미입니다.

이 값들을 COUNT 함수를 이용해 개수를 구하게 되는 것입니다.

위의 수식에서 IF 함수의 3번째 인수는 생략된 형태입니다.

3번째 인수를 넣어서 수식을 만든다면

{=COUNT(IF(B4:B13="서울",1,""))}  형태가 됩니다.

하지만 이렇게 사용하는 것보다는 {=COUNT(IF(B4:B13="서울",1))} 형태로 사용하는 것이 가독성도 뛰어나므로 굳이 3번째 인수를 넣을 필요는 없을 것 같네요.

위의 배열수식인 {=COUNT(IF(B4:B13="서울",1))} 를 풀어서 보면 다음과 같습니다.

 

=COUNT(

IF(B4="서울",1)

IF(B5="서울",1)

IF(B6="서울",1)

IF(B7="서울",1)

IF(B8="서울",1)

IF(B9="서울",1)

IF(B10="서울",1)

IF(B11="서울",1)

IF(B12="서울",1)

IF(B13="서울",1)

)

 

실제 대입되는 값을 정리해 보면 다음과 같습니다.

 

=COUNT(

IF(B4="서울",1) → B4 셀이 "서울"이므로 1

IF(B5="서울",1) → B5 셀이 "서울"이 아니므로 ""

IF(B6="서울",1) → B6 셀이 "서울"이 아니므로 ""

IF(B7="서울",1) → B7 셀이 "서울"이 아니므로 ""

IF(B8="서울",1) → B8 셀이 "서울"이므로 1

IF(B9="서울",1) → B9 셀이 "서울"이 아니므로 ""

IF(B10="서울",1) → B10 셀이 "서울"이 아니므로 ""

IF(B11="서울",1) → B11 셀이 "서울"이 아니므로 ""

IF(B12="서울",1) → B12 셀이 "서울"이므로 1

IF(B13="서울",1) → B13 셀이 "서울"이 아니므로 ""

)

 

B4 셀과 B8 셀, B12 셀의 값인 1을 모두 더하면 3개가 되는 것입니다.

 

이처럼 개수를 구할 때 사용할 수 있는 배열함수 형태는 2가지가 있으며 매우 흡사합니다.

SUM 함수와 IF 함수를 사용할 경우에는 {=SUM(IF(B4:B13="서울",1,0))}

COUNT 함수와 IF 함수를 사용할 경우에는 {=COUNT(IF(B4:B13="서울",1))} 로 사용이 가능합니다.

 

AVERAGE + IF 함수를 사용한 배열함수 예제

 

AVERAGE 함수와 IF 함수를 사용해 지역이 서울인 곳의 판매량 평균을 구해보도록 하겠습니다.

AVERAGE 함수와 IF 함수를 사용하는 형태를 비롯해서 대부분의 함수를 사용하는 경우 SUM 함수와 IF 함수를 사용하는 형태를 응용해서 사용하면 쉽게 해결이 가능합니다.

 

아래 그림은 AVERAGE 함수와 IF 함수를 이용해 지역이 서울인 곳의 판매량 평균을 구하는 화면입니다.

 

 

E13 셀에 사용된 수식은 다음과 같습니다.

 

{=AVERAGE(IF(B4:B13="서울",(C4:C13)))}

 

위의 수식은 {=함수(IF(조건,영역))} 형태입니다.

 

좀 더 풀어서 설명드리면

지역(B4:B13)이 "서울"인 경우 판매량(C4:C13)의 평균을 구한다는 수식입니다.

 

AVERAGE 함수 외에도 MAX, MIN, LARGE, SMALL 함수 등 대부분의 함수에 대해 위의 수식 형태를 사용하면 됩니다.

 

{=MAX(IF(B4:B13="서울",(C4:C13)))} → 최대값

{=MIN(IF(B4:B13="서울",(C4:C13)))} → 최소값

{=LARGE(IF(B4:B13="서울",(C4:C13)),2)} → 2번째 큰 값

{=SMALL(IF(B4:B13="서울",(C4:C13)),2)} → 2번째 작은 값

 

조건이 2개 이상인 경우 배열함수 사용 예제

 

이번에는 조건이 2개인 경우 배열함수를 어떻게 사용하는지 살펴보도록 하겠습니다.

조건이 3개 이상인 경우도 조건이 2개인 경우에 조건만 추가하는 방식으로 응용해서 사용하시면 쉽게 해결이 가능합니다.

 

아래 그림은 조건이 2개인 예제 화면으로, 지역이 서울이면서 부서가 영업2팀인 곳의 판매량 합계를 구하는 예제입니다.

 

 

조건이 2개인 경우이므로 {=함수((조건)*(조건)*(영역))} 형태를 이용하면 됩니다.

 

위의 예제에서 F4 셀의 수식은 다음과 같습니다.

 

{=SUM((B4:B13="서울")*(C4:C13="영업2팀")*(D4:D13))}

 

위의 수식을 구분하면 다음과 같습니다.