오늘은 엑셀의 배열수식을 정리해보려 합니다. 먼저 배열수식이라는 단어를 분해해서 배열과 수식으로 나누어 생각해볼 것입니다. 말 그대로 우리는 배열이 무엇인지를 먼저 알 필요가 있습니다. 엑셀에서는 데이터가 셀 단위로 저장되고 표현됩니다. 배열 역시 데이터를 저장하기 위한 구조인데 엑셀에서는 배열을 셀들을 나열한 것이라 생각하는 것이 배열이 무엇인지 이해하는데 편할겁니다. 따라서 배열수식은 나열된 셀들에 대한 수식, 즉 범위를 가진 셀들 간에 계산을 하는 수식 정도로 이해될 수도 있겠네요. 그렇다면 배열이 무엇인지 알아보죠.
배열
앞에서 배열은 셀들을 나열한 것이라고 했습니다. 사실 이 둘이 정확하게 같다는 것이 아니라 같은 의미로 사용할 수 있다는 것입니다. 다음은 1부터 5까지 값들을 셀에 나열한 것입니다. 그리고 B2:F5라는 셀범위를 갖습니다.
아마 이 값들의 합을 구하고 싶다면
=SUM(B2:F5)
이렇게 수식을 만들면 되겠죠.
그렇다면 배열을 사용해서 위와 동일한 계산을 보겠습니다.
=SUM({1,2,3,4,5})
셀 주소가 없을 뿐이지 동일한 데이터를 더합니다.
배열을 표현하는 방법
앞에서 본 것처럼 엑셀에서 배열을 사용하면 셀을 사용하지 않고도 데이터를 행과 열을 가진 테이블에 표현할 수 있습니다. 배열에서 열을 구분하는 방법은 ,(콤마)를 사용하고 행을 구분하려면 ;(세미콜론)을 사용하여 데이터를 나열하면 됩니다. 위에서 표현한 {1,2,3,4,5}는 1행 5열의 테이블을 나타냅니다.
{1,2,3,4,5;6,7,8,9,10} 은 2행 5열의 테이블을 나타내겠죠.
배열과 범위를 가진 셀이 같은 의미로 이해될 수 있다면 배열을 엑셀의 셀에 표시할 수 있을 겁니다.
{={1,2,3,4,5;6,7,8,9,10}}
배열을 셀에 표시하는 방법은
1. 배열의 범위(위 배열은 2행 5열) 에 맞게 셀의 범위를 선택합니다.
2. 배열을 수식에 입력합니다. (={1,2,3,4,5;6,7,8,9,10})
3. ctrl+shift+Enter을 눌러 배열수식을 만듭니다. (배열수식 만드는 방법)
배열을 셀에 표현하려면 배열 수식을 만들어 줘야합니다. 배열수식을 만드는 단축키를 꼭 알아두세요!
수식을 배열수식으로 만들면 다음과 같은 특징이 생깁니다.
수식 내에 있는 셀범위 또는 배열의 요소에 대해서 하나씩 연산을 해줍니다.
위 수식은 배열 수식에 단순히 배열이 있으므로 2행 5열의 배열이 셀에 표현됩니다.
1행1열의 요소는 1. 이것은 선택된 범위의 1행 1열의 셀에 위치합니다. 마찬가지로 1행 2열의 요소는 2이므로 선택된 셀 범위의 1행 2열에 표시됩니다.
배열간의 덧셈도 가능합니다. 배열과 배열을 더하면 배열이 됩니다.
실제로 수학의 행렬간의 덧셈 뺄셈도 위와 같이 합니다.
또한 배열 말고 셀범위와 셀범위간에 덧셈 뺄셈도 이런 방식으로 하죠.
2행 2열의 행렬이므로 결과를 표시할 셀 범위를 2행 2열로 한 후, 수식을 입력하고 ctrl+shift+Enter를 눌러주면 배열수식이 완성됩니다.
요소들 간에 덧셈을 한 후의 결과 역시 배열이 되고 선택된 셀 범위에 결과가 표시됩니다.
배열 수식을 이용한 합계(SUM)
합계를 구할 때 배열 수식을 사용할 수 있습니다. 배열수식을 사용하지 않고 하는 방법과 비교해 보겠습니다.
- 배열수식을 사용하지 않는 합계
각 물품의 매출을 따로 따로 구합니다.
총 매출은 앞에서 구한 각 물품의 매출을 더한 값입니다.
- 배열수식을 이용한 합계
1. 매출 아래 녹섹 테두리 범위(D3:D7)을 선택합니다.
2. 판매수량 전체 범위와 가격 전체 범위의 곱을 수식입력 창에 위와 같이 입력합니다.
3. 이 상태에서 ctrl+shift+Enter를 눌러줍니다.(배열수식을 만듬)
총 매출을 구할 때는 앞에서 구한 매출을 그냥 합해도 되고 위 그림과 같이 판매수량 범위와 가격 범위를 곱한 뒤 결과를 모두 합해주면 됩니다.
엑셀 배열수식은 응용범위가 넓습니다. 반드시 알아두세요.