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

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

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

 

포스트 목차.

• 01. 배열 수식에서 조건이 두 개 이상인 논리식 처리하는 법
• -(1) 논리연산 특성
• -(2) 숫자의 참과 거짓
• -(3) 엑셀에서 논리식의 곱셈과 덧셈
• 02. SUMIFS 와 COUNTIFS 만들기
• 03. MAXIFS 와 MINIFS 만들기

 

본 포스트에서는 엑셀에서 배열을 수식에 활용하는 방법에 대해 다룬다. 1편에서 다루진 못한 내용들을 추가한다.

 

01. 배열 수식에서 조건이 두 개 이상인 논리식 처리하는 법

엑셀에서는 논리 연산 AND와 OR에 해당하는 연산자가 없다. 대신에 AND함수와 OR함수를 제공한다.

 - AND함수는 입력받은 모든 논리값에 대한 AND연산 결과를 반환한다.

 - OR함수는 입력받은 모든 논리값에 대한 OR연산 결과를 반환한다. 

 

위의 그림처럼 AND 함수내에 두 범위를 지정한 후에 Ctrl + Shift + Enter를 입력해보자

우리가 원하는 동작은 AND(TRUE, TURE)의 값이 TRUE여야 하지만, 해당 연산은 FALSE로 나온다. AND 함수와 OR함수는 배열에 대한 개별 연산을 지원하지 않는다. 

 

본 포스트에서는 논리식의 곱셈, 덧셈으로 AND, OR를 대체할 수 있는 방법을 소개하려고 한다. (다른 방법은 다음 포스트에서 다루도록 하겠다.)

 

(1) 논리연산 특성

인자1, 인자2에 대한 AND연산 결과와 OR연산 결과는 위의 그림과 같다.

 

엑셀에서 TRUE와 FALSE를 더하거나 곱하면 어떤 일이 발생하는지 알아보자.

TRUE를 숫자1로 인식하고, FALSE를 숫자0으로 인식하기 때문에, 논리식의 곱셈과 덧셈에 대한 결과는 위의 그림과 같다.

 

0을 FALSE로 생각하고, 0외의 값을 TRUE로 생각해보면 다음 내용이 성립한다.

   - 논리연산에 대한 곱셈은 AND연산과 동일한 결과를 갖는다.

   - 논리연산에 대한 덧셈은 OR연산과 동일한 결과를 갖는다.

 

(2) 숫자의 참과 거짓

이번에는 IF함수를 이용하여, E2:F5에 있는 숫자값을의 논리값이 어떤식으로 형성되는지 알아보자.

IF함수의 조건부분에 숫자를 넣었을 때의 논리 결과는 위의 그림에서 확인할 수 있다.

가장 왼쪽에 있는 AND 연산과  OR연산의 논리값과 인자들의 곱셈, 인자들의 덧셈 결과가 각각 같다는 것을 알 수 있다. 

 

(3) 엑셀에서 논리식의 곱셈과 덧셈

위에서 정리한 내용에 근거하여 다음과 같은 결론을 도출 할 수 있다. 

 

- 인자1 인자2의 곱셈의 논리값 = 인자1 AND 인자2
- 인자1 인자2의 덧셈의 논리값 = 인자1 OR 인자2

 

다음과 같은 예제를 통해 확인해보자.

- 논리식의 곱셈

셀 C2에 다음 수식을 입력한 후 Ctrl + Shift + Enter를 이용하여 배열수식을 적용해보자.

=A2:A5*B2:B5

셀 D2에 다음 수식을 입력한 후 Ctrl + Shift + Enter를 이용하여 배열수식을 적용해보자.

=A2:A5+B2:B5

결과는 위의 그림과 같으며, 이 값들이 IF문의 조건 부분에 들어가면, AND 연산/OR 연산을 처리할 수 있다는 것을 알 수 있다.

 

02. SUMIFS 와 COUNTIFS 만들기

이번에는 SUMIFS 함수와 COUNTIFS 역할을 수행할 수 있는 수식을 만들어 보자.

 

(1) SUMIFS

위의 데이터가 있을 때, 온도가 "핫" 이면서 가격이 3000원 이하인 제품 판매량의 합을 구해보자.

 

F14에 다음 수식을 입력한 후 Ctrl + Shift + Enter를 입력하여 배열 수식을 적용하자.

 

=if((A14:A22="핫")*(C14:C22<=3000),D14:D22,0)

두가지 조건을 괄호로 묶고 곱해준다. 두 조건의 곱이 참인경우 판매량 정보를 가져오고, 아닌경우 0을 가져오게 한다.

결과는 다음과 같다.

온도가 핫이면서 가격이 3000원 이하인 제품의 판매량만 표시되었다. 

지금까지는 이해를 돕기위한 절차였고, 실제로 SUMIFS 수식을 SUM함수를 이용하여 만들어보자.

 

셀 F23에 다음 내용을 입력 후,  Ctrl + Shift + Enter를 입력하여 배열 수식을 적용하자.

=sum(IF((A14:A22="핫")*(C14:C22<=3000),D14:D22,0))

 

SUMIFS가 정상 구현되었음을 알 수 있다.

 

(2) COUNTIFS

이번에는 판매량이 40개 이하면서 가격이 2000원인 제품의 수를 구해보자.

 

결과 비교를 위해 IF문을 위와 같이 테스트 해보자.

=IF((D2:D10>=40)*(C2:C10=2000),1,0)

 

참인 경우 1, 거짓인 경우 0을 입력한 후, 모두 더하면 COUNTIFS와 같은 효과를 낼 수 있다.

다음 수식을 입력 후에 Ctrl + Shift + Enter를 입력한다.

=sum(IF((D2:D10>=40)*(C2:C10=2000),1,0))

결과값이 일치함을 알 수 있다.

 

03. MAXIFS 와 MINIFS 만들기

이번에는 특정 조건들을 만족하는 값 중 최대값을 구하는 MAXIFS 함수와 특정 조건을 만족하는 값 중 최소값을 구하는 MINIFS 함수와 동일한 수식을 만들어보자.

 

위의 그림에서 알 수 있듯이, 엑셀 구버전에서는 MAXIFS 함수를 사용할 수 없다. 

 

수식을 통해 해결해보자.

 

MAX함수와 MIN함수는 배열 수식을 지원하기 때문에, SUM함수와 같이 사용할 수 있다.

 

MAX함수와 MIN함수는 최대최소만 다를 뿐, 인자들이 동일하기 때문에 여기서는 MAXIFS의 예만 소개하려고 한다. 

 

위의 데이터에서 가격이 2000원인 아이스 제품의 최대 판매량을 구해보자.

 

일단 이해를 위해 해당 조건에 부합하는 데이터만 추려보자.

=IF((C3:C11=2000)*(A3:A11="아이스"),D3:D11,"")

조건에 맞는 데이터인 47과 35만 값이 출력된다는 것을 확인할 수 있다.

이제 위의 IF문을 MAX함수안에 넣고 Ctrl + Shift + Enter를 입력하여, MAXIFS 함수와 동일한 기능을 하는 수식을 완성해보자.

 

=MAX(IF((C3:C11=2000)*(A3:A11="아이스"),D3:D11,""))

 

35와 47 중 최대 값인 47이 출력되는 것을 확인 할 수 있다.

 

 

같이보면 좋은 글

• 엑셀에서 배열 수식 사용하기
• SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수
• Excel [지식인 해결 시리즈] 007. 상반기/하반기 매출 집계 (SUMIF)