엑셀에서 배열 수식 사용하기 (1)

전체 목차
- 001. SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수
- 002. MATCH 함수, INDEX 함수
- 003. 엑셀 이동 단축키, 채우기 핸들, 수식 채우기 팁
- 004. DAVERAGE함수, DMAX함수, DMIN 함수
- 005. DCOUNT, DCOUNTA, DSUM, DGET, DPRODUCT, DVAR, DSTDEV 함수
- 006. 엑셀 절대주소 vs 상대주소 (절대주소 활용법)

 

포스트 목차.

• 01. 엑셀에서 배열이란
• 02. 수식에서 배열 입력하기
• 03. 배열 논리연산과 IF함수
• 04. COUNTIF 와 SUMIF 만들어 보기

 

본 포스트에서 엑셀에서 배열을 수식에 활용하는 방법에 대해 다룬다.

 

01. 엑셀에서 배열이란

배열은 여러 값이 모여있는 데이터의 덩어리이다. 

엑셀에서는 중괄호{}를 이용하여 배열을 표기한다. 

예를 들어 위의 배열은 값1, 값2, 값3,  값4로 구성된 배열이다. 

 

수식에 입력되는 범위주소 또한 배열로 인식될 수 있다. 

A1:A3 은 배열로 인식될 경우, {셀A1의 값, 셀A2의 값, 셀A3의 값}과 같다고 볼 수 있다.

 

02. 수식에서 배열 입력하기

간단한 예제를 통해 배열을 사용해보자.

위의 배열은 {5, 4, 3, 2, 1}을 의미한다. 엔터를 입력하여 값을 입력할 경우, 첫 번째 값인 5가 출력된다.

이 값을 SUM 함수에 입력해보자.

배열의 모든 요소의 합인 15가 출력된다.  SUM 함수의 경우 배열을 인자로 가질 수 있기 때문에, 위의 입력이 허용된다.

 

이번에는 범위주소를 수식에서 활용해보자.

 

예제에서 사용할 데이터는 다음과 같다.

 

가격과 판매량을 곱한 값을 E열에 표기해보자.

(1) 배열 연산 결과가 입력될 셀의 범위를 선택한다.

F2를 눌러서 수식을 입력해도 되고, 그냥 내용을 입력해도 된다. 선택한 영역의 첫 셀에 수식이 입력될 것이다.

가격 데이터는 C2:C10에 있고, 판매량 데이터는 D2:D10에 있다. 이 값을 곱하는 수식을 써보자.

 

(2) 배열 수식을 입력한다.

= C2:C10*D2:D10

위의 수식처럼 범위주소 형태로 입력하면 된다.

 

(3) Shift + Ctrl + Enter 로 수식입력을 마무리 한다.

주의할 점은 선택되어 있는 셀들에 배열 연산 결과를 입력하기 위해서는 Shift + Ctrl + Enter로 수식을 마무리해야 한다는 점이다. Enter를 입력하면 일반 수식으로 적용된다.

 

상대적으로 같은 위치에 있는 가격값과 판매량값이 곱해진 결과가 상대적으로 같은 위치에 있는 셀에 저장된다.

 

03. 배열 논리 연산과 IF함수

위의 데이터에서 상품명이 아이스티인 경우 True를 반환하는 비교연산을 수행해보자.

=B2:B10="아이스티"

F2:F10을 선택한 상태에서 F2에 위의 수식을 입력 한 후,  Ctrl + Shift + Enter를 입력하여 수식 입력을 마무리한다.

비교 연산도 배열 연산이 가능하다는 것을 알 수 있다.

 

IF함수도 배열을 인자로 사용할 수 있다.

위의 그림 처럼 입력한 후  Ctrl + Shift + Enter를 입력하면 다음과 같은 결과를 얻을 수 있다.

 

04. COUNTIF 와 SUMIF 만들어 보기

수식에 배열을 입력하여 COUNTIF와 SUMIF 같은 동작을 하는 수식을 만들어보자

SUMIF, COUNTIF에 대해 더 알고 싶다면 다음 링크를 참고하자

https://eggdrop.tistory.com/42

SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수

 

 

(1) SUMIF

판매량이 30 이상인 상품의 가격의 총합을 구하는 수식을 만들어 보자.

이해를 돕기 위해 IF문을 이용하여, 판매량이 30이상일 경우, 가격을 표기하고, 30미만인 경우 0을 표기하는 수식을 만들어보자.

=IF(D16:D24 >= 30, C16:C24, 0)

수식을 입력 후에 Ctrl + Shift + Enter를 입력하면 다음과 같은 결과를 얻을 수 있다.

 

판매량이 30 이상인 경우 가격이 표기된다.

이런 결과를 이용하여 SUMIF와 같은 효과를 내는 수식을 만들어보자.

=SUM(IF(D16:D24 >= 30, C16:C24, 0))

SUM함수로 위의 IF문을 감싸준 후에, Ctrl + Shift + Enter를 입력하면 SUMIF와 같은 효과를 낼 수 있다.

 

(2) COUNTIF

판매량이 30 이상인 상품의 수를 구하는 수식을 만들어 보자.

 

이해를 돕기 위해 IF문을 이용하여, 판매량이 30이상일 경우, 1을 표기하고, 30미만인 경우 0을 표기하는 수식을 만들어보자.

=IF( D16:D24 >= 30, 1,  0)

수식을 입력 후에 Ctrl + Shift + Enter를 입력하면 다음과 같은 결과를 얻을 수 있다.

판매량이 30 이상인 경우 가격이 1이 표기된다.

이런 결과를 이용하여 COUNTIF와 같은 효과를 내는 수식을 만들어보자.

 

 

=SUM(IF(D16:D24>=30,1,0))

SUM함수로 위의 IF문을 감싸준 후에, Ctrl + Shift + Enter를 입력하면 COUNTIF와 같은 효과를 낼 수 있다.