배열 수식 지침 및 예제

[호영]

 

배열 수식 지침 및 예제

 

Excel 고급 사용자가 되기 위해서는 배열 수식을 사용하는 방법을 알아야 합니다. 배열 수식을 사용하면 다른 수식으로 할 수 없는 계산을 수행할 수 있습니다. 다음 문서는 Colin Wilcox의 Excel Power User 칼럼에 연재된 내용을 바탕으로 하며 Excel MVP인 John Walkenbach가 저술한 Excel 2002 Formulas(영어)의 14장과 15장에서 발췌한 내용을 포함하고 있습니다. John의 다른 저서에 대한 자세한 내용은 그의 저서 페이지(영어)를 참고하십시오.

이 문서의 내용

---

배열 수식에 대한 자세한 정보

이 섹션에서는 배열 수식에 대해 소개하고 수식의 입력, 편집 및 문제 해결 방법을 설명합니다.

배열 수식을 사용하는 이유

Excel에서 수식을 사용해 본 적이 있다면 꽤 정교한 연산을 수행할 수 있다는 점을 알 것입니다. 예를 들어 지정한 햇수의 총 대출 비용을 계산할 수 있습니다. 그러나 Excel의 수식을 완벽하게 익히려면 배열 수식을 사용하는 방법을 알아야 합니다. 배열 수식을 사용하면 다음과 같은 복잡한 작업을 수행할 수 있습니다.

• 셀 범위에 포함된 문자 수 계산
• 특정 범위 내의 최소값 또는 상한과 하한 사이에 있는 숫자와 같이 특정 조건을 만족하는 숫자만의 합계
• 값 범위에서 매 n번째 값의 합계

 참고   배열 수식은 Ctrl+Shift+Enter를 눌러 입력하기 때문에 "CSE 수식"이라고도 합니다.

배열 및 배열 수식에 대한 간단한 소개

프로그래밍 작업을 해본 적이 있다면 '배열'이라는 용어를 접할 기회가 있었을 것입니다. 이 문서에서 배열은 항목의 모음으로 정의됩니다. Excel에서 이러한 항목은 단일 행(1차원 가로 배열), 열(1차원 세로 배열) 또는 여러 행과 열(2차원 배열)에 있을 수 있습니다. 3차원 배열이나 배열 수식은 Excel에서 만들 수 없습니다.

배열 수식은 배열에 있는 하나 이상의 항목에서 여러 계산을 수행할 수 있는 수식으로, 여러 결과를 반환할 수도 있고 하나의 결과만 반환할 수도 있습니다. 예를 들어 셀 범위에서 배열 수식을 사용하여 열 또는 행의 부분합을 계산하거나 단일 셀에 배열 수식을 적용하여 하나의 양을 계산할 수 있습니다. 여러 셀에 있는 배열 수식을 다중 셀 수식이라고 하고, 하나의 셀에 있는 배열 수식을 단일 셀 수식이라고 합니다.

다음 섹션의 예제에서는 다중 셀 및 단일 셀 배열 수식을 만드는 방법을 보여 줍니다.

방법

이 실습에서는 다중 셀 및 단일 셀 배열 수식을 사용하여 판매액을 계산하는 방법을 보여 줍니다. 첫 번째 단계에서는 다중 셀 수식을 사용하여 일련의 부분합을 계산하고, 두 번째 단계에서는 단일 셀 수식을 사용하여 총합계를 계산합니다.

다중 셀 배열 수식 만들기

1. 새 통합 문서를 엽니다.
2. 예제 워크시트 데이터를 복사한 다음 A1 셀부터 새 워크시트에 붙여넣습니다.

예제 워크시트 데이터를 복사하는 방법

• 빈 통합 문서 또는 워크시트를 만듭니다.
• 도움말 항목에서 예제를 선택합니다.

 참고   행 또는 열 머리글은 선택하지 마십시오.

도움말에서 예제 선택

• Ctrl+C를 누릅니다.
• 워크시트에서 A1 셀을 선택하고 Ctrl+V를 누릅니다.

---

직원	자동차 종류	판매 대수	단가	총 판매량
김종설	세단	5	2200
	쿠페	4	1800
이한일	세단	6	2300
	쿠페	8	1700
문익한	세단	3	2000
	쿠페	1	1600
김윤중	세단	9	2150
	쿠페	5	1950
김소미	세단	6	2250
	쿠페	8	2000

---

3. 옆에 표시되는 붙여넣기 옵션 단추 를 사용하여 대상 서식을 일치시킵니다.
4. 배열(셀 범위 C2~D11)의 값을 곱하려면 E2 셀부터 E11 셀까지 선택하고 수식 입력줄에 다음 수식을 입력합니다.

=C2:C11*D2:D11

5. Ctrl+Shift+Enter를 누릅니다.

Excel에서는 중괄호({ })로 수식을 묶고 선택한 범위의 각 셀에 수식 인스턴스를 배치합니다. 이 작업은 매우 빠르게 실행되므로 E열에는 각 판매 직원의 자동차 종류별 총 판매량만 표시됩니다.

---

---

단일 셀 배열 수식 만들기

1. 통합 문서의 A13 셀에 총 판매량을 입력합니다.
2. B13 셀에 다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누릅니다.

=SUM(C2:C11*D2:D11)

이 경우 Excel에서는 배열(셀 범위 C2~D11)의 값을 곱한 다음 SUM 함수를 사용하여 합계를 모두 더합니다. 결과에는 판매량 총합계 \111,800,000이 표시됩니다. 이 예제에서는 배열 수식의 기능이 얼마나 강력한지를 잘 보여 줍니다. 예를 들어 15,000개의 데이터 행이 있다고 가정해 봅니다. 이 경우 단일 셀에 배열 수식을 만들어 이 데이터의 전부 또는 일부에 대한 합계를 계산할 수 있습니다.

단일 셀 수식(B13 셀)은 다중 셀 수식(셀 E2~E11의 수식)과 완전히 독립적입니다. 이는 배열 수식을 사용하여 얻을 수 있는 또 다른 이점, 유연성을 제공합니다. 즉, E열의 수식을 변경하거나 해당 열을 모두 삭제하는 등 단일 셀 수식에 영향을 주지 않고 원하는 모든 작업을 수행할 수 있습니다. 

또한 배열 수식은 다음과 같은 이점을 제공합니다.

• 일관성    E2 셀에서 아래쪽으로 임의의 셀을 클릭하면 동일한 수식이 표시됩니다. 이러한 일관성은 정확성을 더욱 높여 줄 수 있습니다.
• 안전    다중 셀 배열 수식의 구성 요소는 덮어쓸 수 없습니다. 예를 들어 E3 셀을 클릭하고 Delete 키를 누르는 경우 전체 셀 범위(E2~E11)를 선택하고 수식을 변경해야 합니다. 그러지 않으면 배열을 현재 상태 그대로 두어야 합니다. 안전을 위한 또 하나의 조치로, 수식을 변경하려면 Ctrl+Shift+Enter를 눌러 변경을 확인해야 합니다.
• 작은 파일 크기    여러 개의 중간 수식 대신 단일 배열 수식을 사용할 수 있는 경우가 많습니다. 예를 들어 이 실습용으로 만든 통합 문서에서는 배열 수식 하나만 사용하여 E열의 결과를 계산할 수 있지만, 표준 수식(예: =C2*D2)을 사용한 경우에는 동일한 결과를 계산하는 데 11개의 수식이 사용될 수 있습니다.

배열 수식 구문

대부분의 경우 배열 수식에서는 표준 수식 구문을 사용합니다. 따라서 모두 등호(=)로 시작하며 모든 기본 제공 Excel 함수를 사용할 수 있습니다. 중요한 차이점은 배열 수식을 사용할 경우 Ctrl+Shift+Enter를 눌러 수식을 입력해야 한다는 점입니다. 이렇게 하면 Excel에서 배열 수식을 중괄호로 묶습니다. 중괄호를 직접 입력하려고 하면 수식이 텍스트 문자열로 변환되어 더 이상 수식으로 작동하지 않습니다. 

다음으로, 배열 함수는 줄임 형식임을 이해해야 합니다. 예를 들어 앞에서 사용한 다중 셀 함수는 다음과 같습니다.

=C2*D2
=C3*D3...

B13 셀의 단일 셀 수식은 모든 곱하기 연산과 =E2+E3+E4... 부분합을 더하는 데 필요한 산술을 압축합니다.

배열 수식 입력 및 변경 규칙

배열 수식을 만드는 기본 규칙은 반복하는 것입니다. 배열 수식을 입력하거나 편집할 때마다 Ctrl+Shift+Enter를 눌러야 하며, 이 규칙은 단일 셀 수식과 다중 셀 수식에 모두 적용됩니다.

또한 다중 셀 수식을 사용할 때는 다음 규칙을 따라야 합니다.

• 수식을 입력하기 전에 결과를 유지할 셀 범위를 선택해야 합니다. 이는 다중 셀 배열 수식 실습의 3단계에서 E2~E11 셀을 선택할 때 수행한 작업입니다.
• 배열 수식의 개별 셀 내용을 변경할 수 없습니다. 이렇게 하려면 예제 통합 문서에서 E3 셀을 선택하고 Delete 키를 눌러야 합니다.
• 전체 배열 수식을 이동하거나 삭제할 수 있지만 배열 수식의 일부만을 이동하거나 삭제할 수는 없습니다. 즉, 배열 수식을 축소하려면 먼저 기존 수식을 삭제한 다음 다시 시작해야 합니다.

 팁   배열 수식을 삭제하려면 전체 수식(예: =C2:C11*D2:D11)을 선택하고 Delete 키를 누른 다음 Ctrl+Shift+Enter를 누릅니다.

• 다중 셀에서 셀을 삭제하거나 빈 셀을 삽입할 수 없습니다.

배열 수식 확장

경우에 따라 배열 수식을 확장해야 할 수 있습니다. 이 경우 다시 축소할 수 없다는 점에 주의해야 합니다. 배열 수식을 확장하는 과정은 복잡하지 않지만 이전 섹션에서 설명한 규칙을 기억해야 합니다.

1. 예제 통합 문서에서 기본 테이블 아래에 있는 모든 텍스트와 단일 셀 수식을 지웁니다.
2. A12 셀부터 추가 데이터 행을 통합 문서에 붙여넣습니다. 옆에 표시되는 붙여넣기 옵션 단추 를 사용하여 대상 서식을 일치시킵니다.

---

서태진	세단	6	2500
	쿠페	7	1900
임홍삼	세단	4	2200
	쿠페	3	2000
천용만	세단	8	2300
	쿠페	8	2100

---

3. 현재 배열 수식(E2:E11)이 포함된 셀 범위와 새 데이터 옆에 있는 빈 셀(E12:E17)을 선택합니다. 즉, E2:E17 셀을 선택합니다.
4. F2 키를 눌러 편집 모드로 전환합니다.
5. 수식 입력줄에서 C11을 C17로, D11을 D17로 변경한 다음 Ctrl+Shift+Enter를 누릅니다. E2~E11 셀의 수식이 변경되고 E12~E17의 새로운 셀에 수식 인스턴스가 배치됩니다.

---

---

배열 수식의 단점

배열 수식이 마술처럼 보일 수 있겠지만 여기에도 몇 가지 단점이 있습니다.

• Ctrl+Shift+Enter를 누르는 것을 가끔 잊어버릴 수 있습니다. 배열 수식을 입력하거나 편집할 때마다 이 키 조합을 눌러야 한다는 것을 꼭 기억해야 합니다.
• 다른 사용자가 수식을 이해하지 못할 수 있습니다. 배열 수식은 비교적 문서에 잘 나타나지 않기 때문에 다른 사용자가 통합 문서를 수정해야 하는 경우에는 가급적 배열 수식의 사용을 자제하거나 해당 사용자에게 배열 수식을 변경하는 방법을 알려 주어야 합니다.
• 배열 수식이 큰 경우 컴퓨터의 처리 속도 및 메모리에 따라 계산하는 데 오래 걸릴 수 있습니다.

맨 위로 이동

배열 상수에 대한 자세한 정보

이 섹션에서는 배열 상수에 대해 소개하고 수식의 입력, 편집 및 문제 해결 방법을 설명합니다.

배열 상수에 대한 간단한 소개

배열 상수는 배열 수식의 구성 요소로, 다음과 같이 중괄호({ })와 함께 그 안에 항목 목록을 입력하여 만듭니다.

={1,2,3,4,5}

이 문서의 앞부분에서 배열 수식을 만들 때는 Ctrl+Shift+Enter를 눌러야 한다는 점을 강조했습니다. 배열 상수는 배열 수식의 구성 요소이기 때문에 중괄호를 직접 입력하여 상수를 묶어야 합니다. 그런 다음 Ctrl+Shift+Enter를 사용하여 전체 수식을 입력합니다.

가로 배열(행)을 만들려면 쉼표를 사용하여 항목을 구분하고, 세로 배열(열)을 만들려면 세미콜론을 사용하여 항목으로 구분합니다. 또한 2차원 배열을 만들려면 각 행의 항목을 쉼표를 사용하여 구분하고 각 행을 세미콜론으로 구분합니다.

배열 수식과 마찬가지로 Excel의 기본 제공 함수와 함께 배열 상수를 사용할 수 있습니다. 다음 섹션에서는 각종 상수를 만드는 방법과 이러한 상수를 Excel 함수와 함께 사용하는 방법을 설명합니다.

1차원 및 2차원 상수 만들기

다음 절차에 따라 가로, 세로 및 2차원 상수 만드는 방법을 연습해 봅니다.

가로 상수 만들기

1. 이전 통합 문서를 사용하거나 새 통합 문서를 시작합니다.
2. A1~E1 셀을 선택합니다.
3. 수식 입력줄에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

={1,2,3,4,5}

 참고   이 경우 여는 괄호와 닫는 괄호({ })를 입력해야 합니다.

다음과 같은 결과가 나타납니다.

---

---

괄호 없이 숫자만 직접 입력할 수 없는 이유가 궁금할 것입니다. 계속 진행하다 보면 이 문서의 뒤에 나오는 수식에서 상수 사용 섹션에서 배열 상수의 장점에 대한 설명을 통해 그 이유를 알게 됩니다.

세로 상수 만들기

1. 통합 문서에서 한 열의 5개 셀을 선택합니다.
2. 수식 입력줄에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

={1;2;3;4;5}

다음과 같은 결과가 나타납니다.

---

---

2차원 상수 만들기

1. 통합 문서에서 열 4개와 행 3개로 구성된 셀 블록을 선택합니다.
2. 수식 입력줄에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

={1,2,3,4;5,6,7,8;9,10,11,12}

다음과 같은 결과가 나타납니다.

---

---

수식에서 상수 사용

이제 배열 상수를 입력하는 작업이 익숙해졌을 것이므로 여기에서는 간단한 예제를 통해 지금까지 설명한 내용을 확인해 봅니다.

1. 빈 워크시트를 엽니다.
2. A1 셀부터 시작하여 다음 테이블을 복사합니다. 옆에 표시되는 붙여넣기 옵션 단추 를 사용하여 대상 서식을 일치시킵니다.

---

3

4

5

6

7

---

3. A3 셀에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

=SUM(A1:E1*{1,2,3,4,5})

배열 수식으로 입력했기 때문에 Excel에서 다른 괄호 집합으로 상수를 묶습니다.

---

---

값 85가 A3 셀에 나타납니다. 다음 섹션에서는 수식 작동 방법에 대해 설명합니다.

배열 상수 구문

방금 사용한 수식은 여러 부분으로 구성되어 있습니다.

---

 함수

 저장된 배열

 연산자

 배열 상수

---

괄호 안의 마지막 요소는 배열 상수({1,2,3,4,5})입니다. 배열 상수에는 괄호가 자동으로 적용되지 않으므로 직접 입력하여 배열 상수를 묶어야 합니다. 또한 배열 수식에 상수를 추가한 후에는 Ctrl+Shift+Enter를 눌러 수식을 입력해야 한다는 점도 유의해야 합니다.

Excel에서는 괄호 안에 포함된 식에 대한 연산을 먼저 수행하므로 다음으로 고려해야 할 두 요소는 통합 문서에 저장된 값(A1:E1)과 연산자입니다. 이 시점에서는 수식에서 저장된 배열의 값을 상수의 해당 값과 곱하며 다음과 같이 표현됩니다.

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

끝으로, SUM 함수는 값을 더하므로 합계 85가 A3 셀에 나타납니다.

저장된 배열을 사용하지 않고 전체 연산만 메모리에 유지하려면 저장된 배열을 다른 배열 상수로 바꿉니다.

=SUM({3,4,5,6,7}*{1,2,3,4,5})

이렇게 하려면 함수를 복사하고 통합 문서의 빈 셀을 선택한 다음 수식 입력줄에 수식을 붙여넣고 Ctrl+Shift+Enter를 누릅니다. 이전 실습에서 배열 수식 =SUM(A1:E1*{1,2,3,4,5})를 사용했을 때와 동일한 결과가 나타납니다.

상수에서 사용할 수 있는 요소

배열 상수는 숫자, 텍스트, 논리 값(예: TRUE 및 FALSE), 오류 값(예: #N/A) 등을 포함할 수 있습니다. 숫자의 경우 정수, 10진수 및 지수 형식의 숫자를 사용할 수 있으며, 텍스트를 포함할 경우에는 큰따옴표(")로 해당 텍스트를 묶어야 합니다.

추가 배열, 수식 또는 함수는 배열 상수에 사용할 수 없습니다. 즉, 배열 상수에는 쉼표나 세미콜론으로 구분된 텍스트 또는 숫자만 사용할 수 있으며, {1,2,A1:D4} 또는 {1,2,SUM(Q2:Z8)}과 같은 수식을 입력할 경우 오류 메시지가 표시됩니다. 또한 숫자 값에는 백분율 기호, 달러 기호, 쉼표 또는 괄호를 사용할 수 없습니다.

배열 상수 이름 지정

배열 상수는 이름을 지정하여 사용하는 것이 가장 좋습니다. 이름이 지정된 상수는 사용하기 쉽고 초보자에게 일부 복잡한 배열 수식을 숨길 수 있습니다. 배열 상수의 이름을 지정하여 수식에서 사용하려면 다음을 실행하십시오.

1. 수식 탭의 정의된 이름 그룹에서 이름 정의를 클릭합니다.

이름 정의 대화 상자가 나타납니다.

2. 이름 상자에 1분기를 입력합니다.
3. 참조 대상 상자에 괄호와 함께 다음 상수를 입력합니다.

={"1월","2월","3월"}

대화 상자의 내용은 다음과 같은 모양으로 표시됩니다.

4. 확인을 클릭합니다.
5. 워크시트의 한 행에서 빈 셀 세 개를 선택합니다.
6. 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

=1분기

다음과 같은 결과가 나타납니다.

---

---

이름이 지정된 상수를 배열 수식으로 사용할 경우에는 등호를 입력해야 합니다. 그러지 않으면 Excel에서 배열을 텍스트 문자열로 인식합니다. 배열 상수에는 텍스트와 숫자의 조합을 사용할 수 있습니다.

배열 상수 문제 해결

배열 상수가 작동하지 않는 경우 다음과 같은 문제가 발생했을 수 있습니다.

• 일부 요소가 잘못된 문자로 구분되었을 수 있습니다. 쉼표나 세미콜론을 생략하거나 잘못된 위치에 둔 경우 배열 상수가 올바르게 생성되지 않거나 경고 메시지가 표시될 수 있습니다.
• 상수에 포함된 요소 수와 일치하지 않는 셀 범위를 선택했을 수 있습니다. 예를 들어 5개 셀 상수에 사용하기 위해 한 열에서 6개 셀을 선택한 경우에는 빈 셀에 #N/A 오류 값이 표시됩니다. 반대로, 선택한 셀 수가 너무 적은 경우에는 해당하는 셀이 없는 값이 표시되지 않습니다.

배열 상수 작업

다음 예제에서는 배열 수식에서 배열 상수를 사용할 수 있는 몇 가지 방법을 보여 줍니다. 이 중 일부 예제에서는 TRANSPOSE 함수를 사용하여 행을 열로 변환하거나 열을 행으로 변환합니다.

배열의 각 항목 곱하기

1. 열 4개와 행 3개로 구성된 빈 셀 블록을 선택합니다.
2. 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

={1,2,3,4;5,6,7,8;9,10,11,12}*2

배열의 항목 제곱

• 열 4개와 행 3개로 구성된 빈 셀 블록을 선택합니다.
• 다음 배열 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

또는 캐럿 연산자(^)를 사용하는 다음 배열 수식을 입력합니다.

={1,2,3,4;5,6,7,8;9,10,11,12}^2

1차원 행 바꾸기

1. 한 열에서 빈 셀 5개를 선택합니다.
2. 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

=TRANSPOSE({1,2,3,4,5})

가로 배열 상수를 입력한 경우에도 TRANSPOSE 함수는 배열 상수를 열로 변환합니다.

1차원 열 바꾸기

1. 한 행에서 빈 셀 5개를 선택합니다.
2. 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

=TRANSPOSE({1;2;3;4;5})

세로 배열 상수를 입력한 경우에도 TRANSPOSE 함수는 상수를 행으로 변환합니다.

2차원 상수 행/열 바꿈

1. 열 3개와 행 4개로 구성된 셀 블록을 선택합니다.
2. 다음 상수를 입력하고 Ctrl+Shift+Enter를 누릅니다.

=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

TRANSPOSE 함수는 각 행을 일련의 열로 변환합니다.

맨 위로 이동

기본 배열 수식 사용

이 섹션에서는 기본 배열 수식에 대한 예제를 제공합니다.

시작

이 섹션의 데이터를 사용하여 예제 워크시트를 두 개 만듭니다.

1. 기존 통합 문서를 열거나 새 통합 문서를 만들고 빈 워크시트 두 개가 포함되어 있는지 확인합니다.
2. 다음 테이블의 데이터를 복사하여 A1 셀부터 워크시트에 붙여넣습니다.

400		퀵		1	2	3	4
1200		브라운 폭스		5	6	7	8
3200		점프 오버		9	10	11	12
475		레이지		13	14	10	16
500		파워 유저
2000
600
1700
800
2700

3. 완성된 워크시트의 모양은 다음과 같습니다.

---

4. 

---

5. 첫 번째 워크시트의 이름을 데이터로 지정하고 두 번째 빈 워크시트의 이름을 배열로 지정합니다.

기존 값에서 배열 및 배열 상수 만들기

다음 예제에서는 배열 수식을 사용하여 여러 워크시트에 있는 셀 범위 간의 연결을 만드는 방법을 설명하고, 동일한 값 집합에서 배열 상수를 만드는 방법을 보여 줍니다.

기존 값에서 배열 만들기

1. 예제 통합 문서에서 배열 워크시트를 선택합니다.
2. C1~E3 셀 범위를 선택합니다.
3. 수식 입력줄에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

=데이터!E1:G3

다음과 같은 결과가 나타납니다.

수식은 데이터 워크시트의 E1~G3 셀에 저장된 값에 연결됩니다. 이러한 다중 셀 배열 수식 대신 다음과 같이 배열 워크시트의 각 셀에 고유한 수식을 사용할 수도 있습니다.

---

=데이터!E1	=데이터!F1	=데이터!G1
=데이터!E2	=데이터!F2	=데이터!G2
=데이터!E3	=데이터!F3	=데이터!G3

---

데이터 워크시트의 일부 값이 변경되면 해당 변경 내용이 배열 워크시트에 나타납니다. 데이터 워크시트에서 값을 변경하려면 배열 수식 편집 규칙을 따라야 합니다. 이러한 규칙에 대한 자세한 내용은 배열 수식에 대한 자세한 정보 섹션을 참고하십시오.

기존 값에서 배열 상수 만들기

1. 배열 워크시트에서 C1~E3 셀을 선택합니다.
2. F2 키를 눌러 편집 모드로 전환합니다.
3. F9 키를 눌러 셀 참조를 값으로 변환합니다. 이 값은 자동으로 배열 상수로 변환됩니다.
4. Ctrl+Shift+Enter를 눌러 배열 상수를 배열 수식으로 입력합니다.

=데이터!E1:G3 배열 수식은 다음과 같은 배열 상수로 바뀝니다.

={1,2,3;5,6,7;9,10,11}

데이터 워크시트와 배열 워크시트 간의 연결이 끊어지고 배열 수식이 배열 상수로 대체되었습니다.

셀 범위의 문자 수 계산

다음 예제에서는 공백을 포함하여 셀 범위의 문자 수를 세는 방법을 보여 줍니다.

• 데이터 워크시트의 C7 셀에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

=SUM(LEN(C1:C5))

값 20이 C7 셀에 나타납니다.

이 경우 LEN 함수는 범위의 각 셀에 포함된 각 텍스트 문자열의 길이를 반환하며, SUM 함수는 해당 값을 모두 더한 다음 수식이 있는 셀(C7)에 결과를 표시합니다.

범위에서 n개의 가장 작은 값 찾기

이 예제에서는 셀 범위의 가장 작은 값 세 개를 찾는 방법을 보여 줍니다.

1. 데이터 워크시트에서 A12~A14 셀을 선택합니다.

이 셀 집합에 배열 수식에서 반환되는 결과가 유지됩니다.

2. 수식 입력줄에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

=SMALL(A1:A10,{1;2;3})

값 400, 475 및 500이 A12~A14 셀에 각각 나타납니다.

이 수식에서는 배열 상수를 사용하여 SMALL 함수를 세 번 계산하고 A1:A10 셀에 포함된 배열에서 가장 작은 멤버(1), 두 번째 가장 작은 멤버(2) 및 세 번째 가장 작은 멤버(3)를 반환합니다. 더 많은 값을 찾으려면 상수에 인수를 추가하고 A12:A14 범위에 해당하는 결과 셀 수를 추가합니다. 이 수식에 SUM 또는 AVERAGE와 같은 추가 함수를 사용할 수도 있습니다. 예를 들면 다음과 같습니다.

=SUM(SMALL(A1:A10,{1;2;3}))

=AVERAGE(SMALL(A1:A10,{1;2;3}))

범위에서 n개의 가장 큰 값 찾기

범위에서 가장 큰 값을 찾으려면 SMALL 함수를 LARGE 함수로 바꿉니다. 다음 예제에서는 ROW 및 INDIRECT 함수도 사용합니다.

1. 데이터 워크시트에서 A12~A14 셀을 선택합니다.
2. Delete 키를 눌러 기존 수식을 지우고 셀을 선택한 채로 둡니다.
3. 수식 입력줄에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누릅니다.

=LARGE(A1:A10,ROW(INDIRECT("1:3")))

값 3200, 2700 및 2000이 A12~A14 셀에 각각 나타납니다.

이 단계에서는 ROW 및 INDIRECT 함수에 대해 조금 알아두는 것이 좋습니다. ROW 함수를 사용하면 연속된 정수 배열을 만들 수 있습니다. 예를 들어 예제 통합 문서의 빈 열에서 셀 10개를 선택하고 A1:A10 셀에 다음 배열 수식을 입력한 다음 Ctrl+Shift+Enter를 누릅니다.

=ROW(1:10)

10개의 연속된 정수로 구성된 열이 생성됩니다. 잠재적인 문제를 알아보려면 배열 수식이 있는 범위, 즉 1행 위에 행을 삽입합니다. 행 참조가 조정되고 수식에 의해 2~11의 정수가 생성됩니다. 이 문제를 해결하려면 수식에 INDIRECT 함수를 추가합니다.

=ROW(INDIRECT("1:10"))

INDIRECT 함수에서는 범위 1:10이 큰따옴표로 묶여 있기 때문에 텍스트 문자열을 해당 인수로 사용합니다. 이 함수를 사용하면 행을 삽입하거나 배열 수식을 이동할 때 텍스트 값이 자동으로 조정되지 않습니다. 따라서 ROW 함수에서 항상 원하는 정수 배열을 생성합니다.

이전에 사용한 수식(=LARGE(A1:A10,ROW(INDIRECT("1:3"))))을 안쪽에 있는 괄호부터 차례로 살펴봅니다. INDIRECT 함수는 텍스트 값 집합(이 예제의 경우 1~3)을 반환하고, ROW 함수는 셀 3개로 구성된 열 배열을 생성합니다. LARGE 함수는 A1:A10 셀 범위의 값을 사용하는데, ROW 함수에서 반환되는 각 참조마다 한 번씩 모두 세 번 계산됩니다. 셀 3개로 구성된 열 배열에는 값 3200, 2700 및 2000이 반환됩니다. 더 많은 값을 찾으려면 INDIRECT 함수에 더 큰 셀 범위를 추가합니다.  

끝으로 SUM 및 AVERAGE와 같은 다른 함수와 함께 이 수식을 사용할 수 있습니다.

셀 범위에서 가장 긴 텍스트 문자열 찾기

이 예제에서는 셀 범위의 가장 긴 문자열 텍스트를 찾습니다. 이 수식은 데이터 범위에 단일 열의 셀이 포함된 경우에만 작동합니다.

• 데이터 워크시트의 C7 셀에서 기존 수식을 지우고 대신 다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누릅니다.

=INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

값 브라운 폭스가 C7 셀에 나타납니다.

안쪽에 있는 요소부터 차례로 수식을 살펴봅니다. LEN 함수는 C1:C5 셀 범위에 있는 각 항목의 길이를 반환하고, MAX 함수는 이러한 항목 중에서 가장 큰 값, 즉 C3 셀에 있는 가장 긴 텍스트 문자열에 해당하는 값을 계산합니다.

지금부터는 계산이 조금 복잡해집니다. MATCH 함수는 가장 긴 텍스트 문자열이 있는 셀의 오프셋(상대 위치)을 계산합니다. 이 계산에는 조회 값, 조회 배열 및 일치 형식의 세 가지 인수가 필요합니다. MATCH 함수는 조회 배열에서 지정된 조회 값을 검색합니다. 이 예제의 경우 조회 값은 가장 긴 텍스트 문자열입니다.

(MAX(LEN(C1:C5))

또한 해당 문자열은 다음 배열에 있습니다.

LEN(C1:C5)

일치 형식은 0입니다. 일치 형식은 1, 0 또는 -1 값으로 구성될 수 있습니다. 1을 지정하면 MATCH에서 조회 값보다 작거나 같은 값 중에서 가장 큰 값을 반환하고, 0을 지정하면 MATCH에서 조회 값과 동일한 첫 번째 값을 반환하며, -1을 지정하면 MATCH에서 지정된 조회 값보다 크거나 같은 값 중에서 가장 작은 값을 찾습니다. 일치 형식을 생략한 경우에는 Excel에서 1로 가정합니다.

끝으로, INDEX 함수에서는 배열과 해당 배열 내의 행 및 열 번호를 인수를 사용합니다. C1:C5 셀 범위는 배열을 나타내고 MATCH 함수는 셀 주소를 제공하며 마지막 인수(1)는 값을 배열의 첫 번째 열에서 가져오도록 지정합니다.

이러한 함수에 대한 자세한 내용은 Excel의 도움말을 참고하십시오.

맨 위로 이동

고급 배열 수식 사용

이 섹션에서는 고급 배열 수식에 대한 예제를 제공합니다.

오류 값이 포함된 범위 더하기

오류 값(예: #N/A)이 포함된 범위를 더할 경우에는 Excel의 SUM 함수가 작동하지 않습니다. 이 예제에서는 오류가 있는 데이터 범위의 값을 더하는 방법을 보여 줍니다.

=SUM(IF(ISERROR(데이터),"",데이터))

이 수식은 원래 값에서 오류 값을 제외한 값이 포함된 새 배열을 만듭니다. 안쪽에 있는 함수부터 차례로 살펴보면 ISERROR 함수는 셀 범위(데이터)에서 오류를 검색하고, IF 함수는 지정한 조건이 TRUE인 경우와 FALSE인 경우 각각 다른 값을 반환합니다. 이 예제에서는 TRUE로 계산된 경우 모든 오류 값에 대한 빈 문자열("")이 반환되고, FALSE로 계산된 경우 범위(데이터)의 나머지 값이 반환됩니다. 따라서 오류 값이 포함되지 않습니다. 그런 다음 SUM 함수는 필터링된 배열의 합계를 계산합니다.

범위의 오류 값 개수 계산

이 예제는 이전 수식과 유사하지만 오류 값을 필터링하는 대신 데이터 범위의 오류 값 개수를 반환합니다.

=SUM(IF(ISERROR(데이터),1,0))

이 수식은 오류가 있는 셀은 값이 1로 지정되고, 오류가 없는 셀은 값이 0으로 지정된 배열을 만듭니다. 다음과 같이 IF 함수에 대한 세 번째 인수를 제거하여 수식을 간단하게 고치고 동일한 결과를 얻을 수 있습니다.

=SUM(IF(ISERROR(데이터),1))

인수를 지정하지 않으면 셀에 오류 값이 없는 경우 IF 함수에서 FALSE를 반환합니다. 이 수식을 다음과 같이 더 간단하게 고칠 수 있습니다.

=SUM(IF(ISERROR(데이터)*1))

이 버전은 TRUE*1=1이고, FALSE*1=0인 조건으로 작동합니다.

조건에 따라 값 더하기

조건에 따라 값을 더해야 하는 경우가 있을 수 있습니다. 예를 들어 다음 배열 수식에서는 판매 범위에서 양수만 더합니다.

=SUM(IF(판매>0,판매))

IF 함수는 양의 값과 False 값을 만듭니다. SUM 함수는 0+0=0이기 때문에 기본적으로 False 값을 무시합니다. 이 수식에서 사용하는 셀 범위를 구성할 수 있는 행/열의 개수에는 제한이 없습니다.

또한 여러 조건을 만족하는 값을 더할 수 있습니다. 예를 들어 다음 배열 수식은 0보다 크고 5보다 작거나 같은 값을 계산합니다.

=SUM((판매>0)*(판매<=5)*(판매))

숫자가 아닌 셀이 범위에 하나 이상 포함된 경우 이 수식은 오류를 반환합니다.

OR 조건을 사용하는 배열 수식을 만들 수도 있습니다. 예를 들어 5보다 작고 15보다 큰 값을 더할 수 있습니다.

=SUM(IF((판매<5)+(판매>15),판매))

IF 함수는 5보다 작고 15보다 큰 값을 모두 찾은 다음 이 값을 SUM 함수로 전달합니다.

 중요   AND 및 OR 함수는 배열 수식에 직접 사용할 수 없습니다. 배열 함수에는 결과의 배열이 필요한데, 이러한 함수는 TRUE 또는 FALSE의 단일 결과만을 반환하기 때문입니다. 그러나 이전 수식에 표시된 논리를 사용하여 이 문제를 해결할 수 있습니다. 즉, OR 또는 AND 조건을 만족하는 값에 대해 덧셈이나 곱셈 같은 수학 연산을 수행할 수 있습니다.

0을 제외한 평균 계산

이 예제에서는 해당 범위에 포함된 값의 평균을 구해야 하는 경우 범위에서 0을 제외하는 방법을 보여 줍니다. 다음 수식에서는 판매라는 데이터 범위를 사용합니다.

=AVERAGE(IF(판매<>0,판매))

IF 함수는 0이 아닌 값의 배열을 만든 다음 이 값을 AVERAGE 함수로 전달합니다.

두 셀 범위 간의 차이 계산

다음 배열 수식에서는 내 데이터라는 셀 범위와 기타 데이터라는 셀 범위 간의 값을 비교하여 그 차이를 반환합니다. 두 셀 범위의 내용이 동일한 경우에는 0이 반환됩니다. 이 수식을 사용하려면 셀 범위의 크기와 차원이 같아야 합니다.

=SUM(IF(내 데이터=기타 데이터,0,1))

이 수식에서는 비교할 범위와 크기가 같은 새 배열을 만듭니다. IF 함수는 값 0(일치하지 않는 셀)과 값 1(동일한 셀)로 배열을 채웁니다. 그런 다음 SUM 함수는 배열 값의 합계를 반환합니다.

이 수식을 다음과 같이 간단하게 고칠 수 있습니다.

=SUM(1*(내 데이터<>기타 데이터))

범위의 오류 값을 계산하는 수식과 마찬가지로 이 수식은 TRUE*1=1 및 FALSE*1=0을 조건으로 작동합니다.

범위에서 최대값 위치 찾기

다음 배열 수식은 데이터라는 단일 열 배열에서 최대값이 있는 행의 번호를 반환합니다.

=MIN(IF(데이터=MAX(데이터),ROW(데이터),""))

IF 함수는 데이터 범위에 해당하는 새 배열을 만듭니다. 해당 셀에 범위의 최대값이 있는 경우에는 배열에 행 번호가 포함되고, 그렇지 않으면 빈 문자열("")이 포함됩니다. MIN 함수는 새 배열을 두 번째 인수로 사용하여 데이터 범위의 최대값이 있는 행 번호에 해당하는 가장 작은 값을 반환합니다. 따라서 데이터 범위에 동일한 최대값이 있는 경우 첫 번째 값의 행이 반환됩니다.

최대값의 실제 셀 주소를 반환하려면 다음 수식을 사용합니다.

=ADDRESS(MIN(IF(데이터=MAX(데이터),ROW(데이터),"")),COLUMN(데이터))