102_엑셀 배열 수식 100프로 이해하기

102_엑셀 초급 강좌 – 함수 중급 (29)

사실 배열 수식을 이해 하려면 직접 해보는 것이 가장 빠른 방법 일 것입니다. 만약 저한테 프로그래밍의 아무 지식도 없는 사람에게 배열을 한번 정의해 보라고 한다면 엑셀의 경우 단순 연산을 영역 연산으로 가능하게 해준다고 말하겠습니다. 엄청 막연한 설명이죠. 사실 배열 Array는 프로그래밍을 한다면 정말 자주 나오는 개념입니다. 배열의 정체는 사실 우리가 수학 시간에 배우던 행 열 입니다. 매틀랩 같은 어플리케이션 에서는 메트릭스라고 부르고 C나 비쥬얼 베이직 같은 컴퓨터 언어에서는 배열이라고 부릅니다. 엑셀에서는 배열 보다는 주로 배열 연산이죠. 엑셀에서는 배열을 단순히 정의하는 것은 VBA를 사용한다면 모를까 일반 시트에선 항상 연산과 같이 사용 됩니다.

이번 포스팅 에서는 배열 수식의 예를 가장 쉬운 합계로 설명해 보고 앞으로 몇 포스팅이 될지는 모르겠지만 엑셀에서 기본으로 제공하는 배열 수식을 포함 배열 수식을 파헤쳐 보겠습니다.

102연습파일.xlsx

102연습파일_완성본.xlsx

1. 일반 적인 테이블의 총 합계 구하기

   상품 판매량과 개당 합계 테이블이 있습니다.

   

    

   위 테이블의 총 판매량을 기존의 방법으로 구한 다면 어떻게 구하시겠습니까?

    

   일단 개당 제품의 판매 합계를 다음과 같이 판매량 * 상품 가격을 해서 구하실 거에요.

   

    

    

   그런 다음 이렇게 합계를 통해 총 합계를 구하겠죠.

   

    

   물론 이 방법이 잘못된 것이 아닙니다. 만약 총 합계 만 깔끔하게 보이고 싶다면 열을 숨기 시던가 다른 시트에 데이터를 입력하고 이 시트와 연동을 해도 되고요. 그러나 엑셀의 데이터를 좀 더 깔끔하게 하고 수식을 줄이기 위해 더 좋은 방법이 있습니다. 저런 반복된 수식의 과정을 줄이고 하나의 수식으로 해결하게 해주는 것이 바로 배열 입니다.

    

2. 배열로 총 합계 구하기

   만약 수식을 하나로 통합해서 입력 해보라고 하면 어떻게 하시겠어요?

    

   보통의 방법은 합계를 먼저 떠올릴 테고 판매량과 개당 가격을 곱해서 더한다면 이런 식으로 입력을 할 것입니다.

   

    

    

   그리고 이렇게 에러가 날 것입니다.

   

    

   엑셀의 기본 수식은 범위 연산을 할 수가 없습니다. 우리가 의도한 것은 B2에서 C2 곱한 것, B3에서 C3곱한 것 …….을 보두 더 하란 것이지만 엑셀은 이 수식을 이해 하지 못합니다. 기본 연산 곱하기에 인수를 범위를 넣었기 때문입니다. 범위를 넣으시려면 엑셀에 반드시 이 연산을 범위 연산, 즉 배열 이라고 알려 주셔야 합니다.

    

3. 배열 함수로 합계 구하기

   배열 함수의 개념만 알면 배열 함수를 구하는 건 정말 쉽습니다. 이게 배열을 써야 한다는 것만 아시면 됩니다. 그리고 수식은 위와 똑같이 입력하고 엑셀 에게 이건 배열이다 라고만 알려 주시는 겁니다.

   배열 함수 입력

   시프트 + 컨트롤 + 엔터

    

   이 단축키는 추천이 아니라 무조건 외워야 하는 단축키 입니다. 제가 추천을 드리지만 이렇게 무조건 외워라 하는 단축키는 처음 일거라 생각 됩니다.

   배열 함수도 단일 함수로 쓰일때는 괄호를 닫을 필요가 없습니다. 이렇게 범위 연산을 지정 하시고 컨트롤 + 쉬프트 + 엔터를 눌러 줍니다.

   

    

    

   위의 수익 입력란을 보이죠? {} 란 기호가 이 수식은 배열로 입력 되었단 것을 의미 합니다.

   

    

   엑셀에서는 사실 이런식으로 범위 지정을 해주기 때문에 배열의 의미만 알면 에러가 날 경우는 없지만 코딩을 하면 제일 흔하게 나는 에러가 이 배열 에러 입니다. 차원(DIMENSION)을 맞춰 주지 않으면 연산 정의가 되지 않길 때문이죠. 저 연산은 행렬로 따지면 1x9 * 9x1행렬의 연산입니다. 만약 범위 지정을 잘못해서 1x9 * 8x1 행렬 연산이 된다면? 연산 자체가 정의 되질 않죠. 연산 에러를 보여 드리며 이번 포스팅을 마칩니다.

   배열 함수가 정확히 입력이 됐어도 에러가 나고 있습니다.

   

   배열을 이해 하여야 하는 이유는 엑셀의 유용한 기본 함수들 중에도 이 배열의 원리를 이용하는 것이 꽤 많습니다. 배열은 언제 배열을 써야 하는 지만 알면 아무것도 아닙니다. 일반 함수와 같죠.