배열 수식은 배열의 하나 이상의 항목에 대해 여러 계산을 수행할 수 있는 수식입니다. 배열을 값의 행 또는 열 또는 행과 값 열의 조합으로 생각할 수 있습니다. 배열 수식은 여러 결과 또는 단일 결과를 반환할 수 있습니다.
2018년 9월 업데이트부터 Microsoft 365 결과를 반환할 수 있는 모든 수식은 자동으로 아래로 또는 인접한 셀로 유출됩니다. 이 동작의 변경에는 몇 가지 새로운 동적 배열 함수도 함께 수행됩니다. 동적 배열 수식은 기존 함수 또는 동적 배열 함수를 사용하는지 여부에 따라 단일 셀에 입력한 다음 Enter 를 눌러 확인하면 됩니다. 이전의 레거시 배열 수식은 먼저 전체 출력 범위를 선택한 다음 Ctrl+Shift+Enter를사용하여 수식을 확인해야 합니다. 일반적으로 CSE 수식이라고 합니다.
배열 수식을 사용하여 다음과 같은 복잡한 작업을 수행할 수 있습니다.
-
샘플 데이터 세트를 빠르게 만들 수 있습니다.
-
셀 범위에 포함된 문자 수를 계산합니다.
-
범위에서 가장 낮은 값과 같은 특정 조건을 충족하는 숫자 또는 상한 경계와 아래쪽 경계 사이에 있는 숫자만 합계합니다.
-
값 범위에서 모든 N번째 값을 합산합니다.
다음 예제에서는 다중 셀 및 단일 셀 배열 수식을 만드는 방법을 보여 줄 수 있습니다. 가능한 경우 동적 배열 함수 중 일부와 동적 및 레거시 배열로 입력된 기존 배열 수식의 예제를 포함했습니다.
예제 다운로드
이 문서의 모든 배열 수식 예제를 사용하여 예제 통합 문서를 다운로드합니다.
이 연습에서는 다중 셀 및 단일 셀 배열 수식을 사용하여 판매 수치 집합을 계산하는 방법을 보여줍니다. 첫 번째 단계 집합은 다중 셀 수식을 사용하여 소계 집합을 계산합니다. 두 번째 집합은 단일 셀 수식을 사용하여 총합을 계산합니다.
-
다중 셀 배열 수식
-
셀 H10에서 =F10:F19*G10:G19를 입력하여 각 판매원의 쿠페 및 세단 총 판매액을 계산합니다.
Enter를 누르면결과가 H10:H19 셀로 유출됩니다. 유출 범위 내의 셀을 선택할 때 유출 범위가 테두리로 강조 표시됩니다. H10:H19 셀의 수식이 회색으로 표시될 수도 있습니다. 이 수식은 참조용이기 때문에 수식을 조정하려면 마스터 수식이 있는 H10 셀을 선택해야 합니다.
-
단일 셀 배열 수식
예제 통합 문서의 H20 셀에서 =SUM(F10:F19*G10:G19)을입력하거나 복사하고 붙여 넣은 다음 Enter 를 누를 수 있습니다.
이 경우 Excel 값(셀 범위 F10~G19)을 곱한 다음 SUM 함수를 사용하여 합계를 함께 추가합니다. 결과에는 판매량 총합계 1,590,000,000이 표시됩니다.
이 예제에서는 배열 수식의 기능이 얼마나 강력한지를 잘 보여 줍니다. 예를 들어 1,000개의 데이터 행이 있다고 가정해 봅니다. 이 경우 수식을 1,000개의 행 아래로 끌어다 놓는 대신 단일 셀에 배열 수식을 만들어 이 데이터의 전부 또는 일부에 대한 합계를 계산할 수 있습니다. 또한 H20 셀의 단일 셀 수식은 다중 셀 수식(H10~H19 셀의 수식)에 완전히 독립적입니다. 이는 배열 수식을 사용하여 얻을 수 있는 또 다른 이점인 유연성을 나타냅니다. H20의 수식에 영향을 주지 않고 H 열의 다른 수식을 변경할 수 있습니다. 결과의 정확도의 유효성을 검사하는 데 도움이 좋기 때문에 이렇게 독립적인 합계를 설정하는 것이 좋습니다.
-
동적 배열 수식은 다음 이점도 제공합니다.
-
일관성 H10에서 아래에서 셀을 클릭하면 동일한 수식이 표시됩니다. 이러한 일관성은 정확성을 더욱 높여 줄 수 있습니다.
-
안전 다중 셀 배열 수식의 구성 요소를 덮어 덮을 수 없습니다. 예를 들어 셀 H11을 클릭하고 Delete를 클릭합니다. Excel 출력은 변경되지 않습니다. 변경하려면 배열에서 왼쪽 위 셀 또는 H10 셀을 선택해야 합니다.
-
작은 파일 크기 여러 개의 중간 수식 대신 단일 배열 수식을 사용할 수 있는 경우가 많습니다. 예를 들어 자동차 판매 예제에서는 하나의 배열 수식을 사용하여 E 열의 결과를 계산합니다. =F10*G10, F11*G11, F12*G12 등의 표준 수식을 사용한 경우 11개 수식을 사용하여 동일한 결과를 계산했습니다. 큰 문제가 되지는 않지만 수천 개의 행이 있는 경우 어떻게 하나요? 그런 다음 큰 차이를 만들 수 있습니다.
-
효율성 배열 함수는 복잡한 수식을 빌드하는 효율적인 방법이 될 수 있습니다. 배열 수식 =SUM(F10:F19*G10:G19)은 =SUM(F10*G10,F11*G11,F12*G12,F)과 동일합니다. 13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
-
유출 동적 배열 수식은 출력 범위에 자동으로 유출됩니다. 원본 데이터가 테이블에 Excel 경우 동적 배열 수식은 데이터를 추가하거나 제거할 때 자동으로 조정됩니다.
-
#SPILL! 오류 동적 배열은 #SPILL! 오류를 도입했습니다.는 의도한 유출 범위가 어떤 이유로 차단되는지 나타냅니다. 차단을 해결하면 수식이 자동으로 유출됩니다.
-
배열 상수는 배열 수식의 구성 요소입니다. 항목 목록을 입력한 다음, 다음과 같이 중괄호({ })를 사용하여 목록을 수동으로 둘러싸서 배열 상수를 생성합니다.
={1,2,3,4,5} 또는 ={"January","February","March"}
콤마를 사용하여 항목을 구분하는 경우 가로 배열(행)을 생성합니다. 세미코론을 사용하여 항목을 구분하는 경우 세로 배열(열)을 생성합니다. 2차원 배열을 만들 때 각 행의 항목을 콤마로 나타 내고, 세미코론으로 각 행을 나타 내는 것입니다.
다음 절차에 따라 가로, 세로 및 2차원 상수 만드는 방법을 연습해 봅니다. 시퀀스 함수를 사용하여 수동으로 입력한 배열 상수뿐만 아니라 배열 상수도 자동으로 생성하는 예제를 보여 보겠습니다.
-
가로 상수 만들기
이전 예제의; 통합 문서를 사용하거나 새 통합 문서를 만듭니다. 빈 셀을 선택하고 =SEQUENCE(1,5)를 입력합니다. SEQUENCE 함수는 ={1,2,3,4,5}처럼 1행을 5열 배열로 빌드합니다. 다음 결과가 표시됩니다.
-
세로 상수 만들기
아래에 있는 빈 셀을 선택하고 =SEQUENCE(5)또는 ={1;2;3;4;5}를 입력합니다. 다음 결과가 표시됩니다.
-
2차원 상수 만들기
오른쪽과 아래쪽에 있는 빈 셀을 선택하고 =SEQUENCE(3,4)를 입력합니다. 다음과 같은 결과가 나타납니다.
또는 ={1,2,3,4;5,6,7,8;9,10,11,12}를 입력할 수도 있지만 세미 콜론과 콤마를 놓는 위치를 주의해야 합니다.
이처럼 시퀀스 옵션은 배열 상수 값을 수동으로 입력하는 데 상당한 이점을 제공합니다. 주로 시간을 절약하지만 수동 항목의 오류를 줄이는 데 도움이 될 수 있습니다. 특히 세미 콜론은 콤마 구분선과 구분하기가 어려워 읽을 수도 있습니다.
다음은 더 큰 수식의 일부로 배열 상수를 사용하는 예제입니다. 샘플 통합 문서에서 수식 워크시트의 상수로 이동하거나 새 워크시트 만들기
셀 D9에서 =SEQUENCE(1,5,3,1)를입력했지만 셀 A9:H9에서 3, 4, 5, 6, 7을 입력할 수도 있습니다. 특정 숫자 선택에 대해 특별한 것은 없습니다. 차별화를 위해 1-5가 아니라 다른 것을 선택했습니다.
셀 E11에서 =SUM(D9:H9*SEQUENCE(1,5))또는 =SUM(D9:H9*{1,2,3,4,5})을 입력합니다. 수식은 85를 반환합니다.
SEQUENCE 함수는 배열 상수 {1,2,3,4,5}에 해당합니다. Excel 괄호에 묶인 식에 대한 작업을 먼저 수행하기 때문에 다음 두 요소가 D9:H9의 셀 값과 곱하기 연산자(*)입니다. 이 시점에서 수식은 저장된 배열의 값을 상수의 해당 값에 곱합니다. 이는 다음과 동일합니다.
=SUM(D9*1,E9*2,F9*3,G9*4,H9*5)또는 =SUM(3*1,4*2,5*3,6*4,7*5)
마지막으로 SUM 함수는 값을 추가하고 85를 반환합니다.
저장된 배열을 사용하지 않도록하고 작업을 메모리에 완전히 유지하기 위해 다른 배열 상수로 바꿀 수 있습니다.
=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5))또는 =SUM({3,4,5,6,7}*{1,2,3,4,5})
배열 상수에서 사용할 수 있는 요소
-
배열 상수는 숫자, 텍스트, 논리 값(예: TRUE 및 FALSE) 및 오류 값(예: #N/A)을 포함할 수 있습니다. 정수, 소수점 및 과학적 형식으로 숫자를 사용할 수 있습니다. 텍스트를 포함하면 인용 부호("text")로 둘러싸야 합니다.
-
배열 상수는 추가 배열, 수식 또는 함수를 포함할 수 없습니다. 즉, 콤마 또는 세미코론으로 구분된 텍스트 또는 숫자만 포함할 수 있습니다. Excel {1,2,A1:D4} 또는 {1,2,SUM(Q2:Z8)}과 같은 수식을 입력하면 경고 메시지가 표시됩니다. 또한 숫자 값은 백분율 기호, 달러 기호, 콤마 또는 괄호를 포함할 수 없습니다.
배열 상수를 사용하는 가장 좋은 방법 중 하나는 이름을 지정하는 것입니다. 이름이 지정된 상수는 사용하기 쉽고 다름 사용자에게 일부 복잡한 배열 수식을 숨길 수 있습니다. 배열 상수의 이름을 지정하여 수식에서 사용하려면 다음을 실행합니다.
이름 정의 > 수식으로 > 로 이동합니다. 이름 상자에 Quarter1을 입력합니다. 참조 대상 상자에 괄호와 함께 다음 상수를 입력합니다.
={"1월","2월","3월"}
이제 대화 상자는 다음과 같이 표시됩니다.
확인을클릭한 다음 빈 셀 세 개가 있는 행을 선택하고 =Quarter1 을 입력합니다.
다음 결과가 표시됩니다.
결과가 가로 대신 세로로 유출하려는 경우 =TRANSPOSE(분기1)를사용할 수 있습니다.
재무제표를 구축할 때 사용할 수 있는 12개월 목록을 표시하려는 경우 SEQUENCE 함수를 사용하여 현재 연도 중 하나를 기본으로 할 수 있습니다. 이 함수의 깔끔한 점은 월만 표시되어도 다른 계산에서 사용할 수 있는 유효한 날짜가 뒤에 있습니다. 예제 통합 문서에서 명명된 배열 상수 및 빠른 샘플 데이터 세트 워크시트에서 이러한 예제를 찾을 수 있습니다.
=TEXT(DATE(TODAY(TODAY)), SEQUENCE(1,12),1),"mmm")
이 함수는 DATE 함수를 사용하여 현재 연도에 따라 날짜를 만들고, SEQUENCE는 1월부터 12월까지의 배열 상수(1~12)를 만들고 TEXT 함수는 표시 형식을 "mmm"(1월, 2월, 3월 등)으로 변환합니다. 1월과 같은 전체 월 이름을 표시하고 싶을 경우 "mmmm"을 사용하게 됩니다.
명명된 상수를 배열 수식으로 사용하는 경우 분기 1 뿐만 아니라 =Quarter1에서와 같은 기호를 입력해야 합니다. 이렇게 하지 않으면 Excel에서 배열을 텍스트 문자열로 해석하고 수식이 예상대로 작동하지 않습니다. 마지막으로 함수, 텍스트 및 숫자 조합을 사용할 수 있습니다. 이 모든 것은 원하는 창의성에 따라 달라 지는 것입니다.
다음 예제에서는 배열 수식에서 배열 상수를 사용할 수 있는 몇 가지 방법을 보여 줍니다. 일부 예제에서는 TRANSPOSE 함수를 사용하여 행을 열로 변환하고 그 반대의 경우도 마찬가지입니다.
-
배열의 각 항목 여러 개
=SEQUENCE(1,12)*2,또는 ={1,2,3,4;5,6,7,8;9,10,11,12}*2를 입력합니다.
(/)를사용하여 나누고 (+) 를 추가하고 를 뺄 수 있습니다.-
-
배열의 항목 제곱
=SEQUENCE(1,12)^2,또는 ={1,2,3,4;5,6,7,8;9,10,11,12}^2를 입력합니다.
-
배열에서 제곱된 항목의 제곱근 찾기
Enter =SQRT(SEQUENCE(1,12)^2) 또는 =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)
-
1차원 행 바꾸기
=TRANSPOSE(SEQUENCE(1,5))또는 =TRANSPOSE({1,2,3,4,5})를 입력합니다.
가로 배열 상수를 입력한 경우에도 TRANSPOSE 함수는 배열 상수를 열로 변환합니다.
-
1차원 열 바꾸기
=TRANSPOSE(SEQUENCE(5,1))또는 =TRANSPOSE({1;2;3;4;5})를 입력합니다.
세로 배열 상수를 입력한 경우에도 TRANSPOSE 함수는 상수를 행으로 변환합니다.
-
2차원 상수 행/열 바꿈
=TRANSPOSE(SEQUENCE(3,4))또는 =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})
TRANSPOSE 함수는 각 행을 일련의 열로 변환합니다.
이 섹션에서는 기본 배열 수식에 대한 예제를 제공합니다.
-
기존 값에서 배열 만들기
다음 예제에서는 배열 수식을 사용하여 기존 배열에서 새 배열을 만드는 방법을 설명합니다.
=SEQUENCE(3,6,10,10,10)또는 ={10,20,30,40,50,60,90,100,110,120;130,140,150,160,170,180}을 입력합니다.
숫자 배열을 만들었기 때문에 10을 입력하기 전에 {(중괄호 열기)와 180을 입력한 후 }(닫기 중괄호)를 입력해야 합니다.
다음으로 빈 셀에 =D9#또는 =D9:I11을 입력합니다. 3 x 6개 셀 배열은 D9:D11에서 보는 값과 동일합니다. # 기호를 유출된 범위 연산자라고 Excel 대신 전체 배열 범위를 참조하는 방법을 사용하는 것이 좋습니다.
-
기존 값에서 배열 상수 만들기
유출된 배열 수식의 결과를 해당 구성 요소로 변환할 수 있습니다. 셀 D9를 선택한 다음 F2를 눌러 편집 모드로 전환합니다. 다음으로 F9를 눌러 셀 참조를 값으로 변환하고, Excel 상수로 변환합니다. Enter, 수식인 =D9#을 누르면 ={10,20,30;40,50,60;70,80,90}이 됩니다.
-
셀 범위의 문자 수 계산
다음 예제에서는 셀 범위의 문자 수를 계산하는 방법을 보여 줍니다. 여기에는 공백이 포함됩니다.
=SUM(LEN(C9:C13))
이 경우 LEN 함수는 범위의 각 셀에 있는 각 텍스트 문자열의 길이를 반환합니다. 그런 다음 SUM 함수는 이러한 값을 함께 추가하고 결과를 표시합니다(66). 평균 문자 수를 얻게 원하는 경우 다음을 사용할 수 있습니다.
=AVERAGE(LEN(C9:C13))
-
범위 C9:C13에서 가장 긴 셀의 내용
=INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)), LEN(C9:C13,0),1)
이 수식은 데이터 범위에 단일 열의 셀이 포함된 경우에만 작동합니다.
내부 요소에서 시작하여 외부로 작업하는 수식을 자세히 살펴보자. LEN 함수는 셀 범위 D2:D6의 각 항목의 길이를 반환합니다. MAX 함수는 해당 항목 중 가장 큰 값을 계산합니다. 이 값은 D3 셀에 있는 가장 긴 텍스트 문자열에 해당합니다.
지금부터는 계산이 조금 복잡해집니다. MATCH 함수는 가장 긴 텍스트 문자열을 포함하는 셀의 오프셋(상대 위치)을 계산합니다. 이 계산에는 조회 값, 조회 배열, 일치 형식의 세 가지 인수가 필요합니다. MATCH 함수는 조회 배열에서 지정된 조회 값을 검색합니다. 이 예제의 경우 조회 값은 가장 긴 텍스트 문자열입니다.
MAX(LEN(C9:C13)
또한 해당 문자열은 다음 배열에 있습니다.
LEN(C9:C13)
이 경우 일치 형식 인수는 0입니다. 일치 유형은 1, 0 또는 -1 값일 수 있습니다.
-
1 - 가장 큰 값을 룩업 val보다 작거나 같음으로 반환합니다.
-
0 - 첫 번째 값이 룩업 값과 정확히 동일한 값을 반환합니다.
-
-1 - 지정된 룩업 값보다 크거나 같은 가장 작은 값을 반환합니다.
-
일치 유형을 생략하면 Excel 가정합니다.
마지막으로 INDEX 함수는 배열 및 해당 배열 내의 행 및 열 번호와 같은 인수를 제공합니다. 셀 범위 C9:C13은 배열을 제공하며 MATCH 함수는 셀 주소를 제공하며, 최종 인수(1)는 값이 배열의 첫 번째 열에서 온 것으로 지정합니다.
가장 작은 텍스트 문자열의 내용을 얻게 원하는 경우 위의 예제의 MAX를 MIN으로 바 대체합니다.
-
-
범위에서 n개의 가장 작은 값 찾기
이 예제에서는 셀 범위에서 세 가지 가장 작은 값을 찾는 방법을 보여 줍니다. 여기서 셀 B9:B18has의 샘플 데이터 배열이 =INT(RANDARRAY(10,1)*100)로 만들어집니다. RANDARRAY는 휘발성 함수이기 때문에 계산할 때마다 새 난수 집합을 Excel 있습니다.
Enter =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})
이 수식은 배열 상수를 사용하여 SMALL 함수를 세 번 평가하고 B9:B18 셀에 포함된 배열에서 가장 작은 3개 멤버를 반환합니다. 여기서 3은 D9 셀의 변수 값입니다. 더 많은 값을 찾으면 SEQUENCE 함수의 값을 늘리거나 상수에 더 많은 인수를 추가할 수 있습니다. 이 수식에 SUM 또는 AVERAGE와 같은 추가 함수를 사용할 수도 있습니다. 예를 들면 다음과 같습니다.
=SUM(SMALL(B9#,SEQUENCE(D9))
=AVERAGE(SMALL(B9#,SEQUENCE(D9))
-
범위에서 n개의 가장 큰 값 찾기
범위에서 가장 큰 값을 찾으면 SMALL 함수를 LARGE 함수로 바꿀 수 있습니다. 다음 예제에서는 ROW 및 INDIRECT 함수도 사용합니다.
=LARGE(B9#,ROW(INDIRECT("1:3"))또는 =LARGE(B9:B18,ROW(INDIRECT("1:3")))
이 단계에서는 ROW 및 INDIRECT 함수에 대해 조금 알아두는 것이 좋습니다. ROW 함수를 사용하면 연속된 정수 배열을 만들 수 있습니다. 예를 들어 빈을 선택하고 다음을 입력합니다.
=ROW(1:10)
10개의 연속된 정수로 구성된 열이 생성됩니다. 잠재적인 문제를 알아보려면 배열 수식이 있는 범위, 즉 1행 위에 행을 삽입합니다. Excel 참조를 조정하고 수식은 이제 2에서 11까지 정수를 생성합니다. 이 문제를 해결하려면 수식에 INDIRECT 함수를 추가합니다.
=ROW(INDIRECT("1:10"))
INDIRECT 함수는 텍스트 문자열을 인수로 사용합니다(범위 1:10이 인용 부호로 둘러싸여 있는 이유). 이 함수를 사용하면 행을 삽입하거나 배열 수식을 이동할 때 텍스트 값이 자동으로 조정되지 않습니다. 따라서 ROW 함수에서 항상 원하는 정수 배열을 생성합니다. 시퀀스를 쉽게 사용할 수 있습니다.
=SEQUENCE(10)
앞에서 사용한 수식 (=LARGE(B9#,ROW(INDIRECT("1:3")) - 내부 괄호에서 시작하여 외부로 작업하는 수식을 살펴보고, 이 경우 INDIRECT 함수는 값 1에서 3까지의 텍스트 값 집합을 반환합니다. ROW 함수는 3셀 열 배열을 차례로 생성합니다. LARGE 함수는 셀 범위 B9:B18의 값을 사용하며 ROW 함수에서 반환되는 각 참조에 대해 3번 평가됩니다. 더 많은 값을 찾으면 INDIRECT 함수에 더 큰 셀 범위를 추가합니다. 마지막으로 SMALL 예제와 같이 SUM 및 AVERAGE와 같은 다른 함수와 함께 이 수식을 사용할 수 있습니다.
-
오류 값이 포함된 범위 더하기
오류 값이 Excel 범위를 합산하려고 할 때 이 함수의 SUM 함수는 작동하지 #VALUE! 또는 #N/A를 사용할 수 있습니다. 이 예제에서는 오류를 포함하는 데이터라는 범위의 값을 합산하는 방법을 보여 주며,
-
=SUM(IF(ISERROR(데이터),"",데이터))
이 수식은 원래 값에서 오류 값을 제외한 값이 포함된 새 배열을 만듭니다. 내부 함수에서 시작하여 외부로 작업하는 ISERROR 함수는 셀 범위(데이터)를 검색하여 오류를 검색합니다. IF 함수는 지정한 조건이 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인 조건으로 작동합니다.
조건에 따라 값을 더해야 하는 경우가 있을 수 있습니다.
예를 들어 이 배열 수식은 위의 예제에서 E9:E24 셀을 나타내는 Sales라는 범위의 양수 정수만 합산합니다.
=SUM(IF(판매액>0,판매액))
IF 함수는 긍정 값 및 거짓 값의 배열을 만듭니다. SUM 함수는 0+0=0이기 때문에 기본적으로 False 값을 무시합니다. 이 수식에서 사용하는 셀 범위를 구성할 수 있는 행/열의 개수에는 제한이 없습니다.
또한 여러 조건을 만족하는 값을 더할 수 있습니다. 예를 들어 이 배열 수식은 0보다 크고 2500보다 작은 값을 계산합니다.
=SUM((sales>0)**(판매<2500)*(Sales))
숫자가 아닌 셀이 범위에 하나 이상 포함된 경우 이 수식은 오류를 반환합니다.
OR 조건을 사용하는 배열 수식을 만들 수도 있습니다. 예를 들어 0보다 크거나 2500보다 작은 값을 합산할 수 있습니다.
=SUM(IF((판매>0)+(판매<2500),Sales))
이러한 함수는 TRUE 또는 FALSE 중 하나의 결과를 반환하고 배열 함수에 결과 배열이 필요하기 때문에 배열 수식에서 AND 및 OR 함수를 직접 사용할 수 없습니다. 이전 수식에 표시된 논리를 사용하여 문제를 해결할 수 있습니다. 즉, OR 또는 AND 조건을 충족하는 값에 대한 추가 또는 곱셈과 같은 수학 작업을 수행할 수 있습니다.
이 예제에서는 해당 범위에 포함된 값의 평균을 구해야 하는 경우 범위에서 0을 제외하는 방법을 보여 줍니다. 다음 수식에서는 판매라는 데이터 범위를 사용합니다.
=AVERAGE(IF(판매액<>0,판매액))
IF 함수는 0이 아닌 값의 배열을 만든 다음 이 값을 AVERAGE 함수로 전달합니다.
이 배열 수식은 MyData와 YourData라는 두 셀 범위의 값을 비교하고 둘 사이의 차이점 수를 반환합니다. 두 범위의 내용이 동일하면 수식은 0을 반환합니다. 이 수식을 사용하려면 셀 범위가 크기가 같고 차원이 같아야 합니다. 예를 들어 MyData가 5개 열에 3개 행의 범위인 경우 YourData는 5개 열에 3개 행이 되어야 합니다.
=SUM(IF(내 데이터=기타 데이터,0,1))
이 수식에서는 비교할 범위와 크기가 같은 새 배열을 만듭니다. IF 함수는 값 0(일치하지 않는 셀)과 값 1(동일한 셀)로 배열을 채웁니다. 그런 다음 SUM 함수는 배열 값의 합계를 반환합니다.
이 수식을 다음과 같이 간단하게 고칠 수 있습니다.
=SUM(1*(MyData<>YourData))
범위의 오류 값을 계산하는 수식과 마찬가지로 이 수식은 TRUE*1=1 및 FALSE*1=0을 조건으로 작동합니다.
다음 배열 수식은 데이터라는 단일 열 배열에서 최대값이 있는 행의 번호를 반환합니다.
=MIN(IF(데이터=MAX(데이터),ROW(데이터),""))
IF 함수는 데이터라는 범위에 해당하는 새 배열을 만듭니다. 해당 셀에 범위의 최대 값이 포함된 경우 배열에 행 번호가 포함되어 있습니다. 그렇지 않으면 배열에 빈 문자열("")이 포함되어 있습니다. MIN 함수는 새 배열을 두 번째 인수로 사용하여 데이터에서 최대 값의 행 수에 해당하는 가장 작은 값을 반환합니다. 데이터라는 범위에 동일한 최대 값이 포함된 경우 수식은 첫 번째 값의 행을 반환합니다.
최대값의 실제 셀 주소를 반환하려면 다음 수식을 사용합니다.
=ADDRESS(MIN(IF(데이터=MAX(데이터),ROW(데이터),"")),COLUMN(데이터))
데이터 세트 워크시트의 차이점에 대한 샘플 통합 문서에서 비슷한 예제를 찾을 수 있습니다.
이 연습에서는 다중 셀 및 단일 셀 배열 수식을 사용하여 판매 수치 집합을 계산하는 방법을 보여줍니다. 첫 번째 단계 집합은 다중 셀 수식을 사용하여 소계 집합을 계산합니다. 두 번째 집합은 단일 셀 수식을 사용하여 총합을 계산합니다.
-
다중 셀 배열 수식
아래 표 전체를 복사하여 빈 워크시트의 셀 A1에 붙여넣습니다.
영업사원 |
자동차 유형 |
판매된 번호 |
단가 |
총 판매량 |
---|---|---|---|---|
김광준 |
세단 |
5 |
33000 |
|
쿠페 |
4 |
37000 |
||
오흥미 |
세단 |
6 |
24000 |
|
쿠페 |
8 |
21000 |
||
조미순 |
세단 |
3 |
29000 |
|
쿠페 |
1 |
31000 |
||
황태준 |
세단 |
9 |
24000 |
|
쿠페 |
5 |
37000 |
||
강용만 |
세단 |
6 |
33000 |
|
쿠페 |
8 |
31000 |
||
수식(총합계) |
총합계 |
|||
‘=SUM(C2:C11*D2:D11) |
=SUM(C2:C11*D2:D11) |
-
각 판매원에 대한 쿠페 및 세단의 총 판매를 표시하려면 셀 E2:E11을 선택하고 수식 =C2:C11*D2:D11을입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
-
모든 매출의 총합을 표시하려면 F11 셀을 선택하고 =SUM(C2:C11*D2:D11)을입력한 다음 Ctrl+Shift+Enter 를누를 수 있습니다.
Ctrl+Shift+Enter를누르면 Excel 중괄호({})로수식을 둘러싸고 선택한 범위의 각 셀에 수식 인스턴스를 삽입합니다. 이 작업은 매우 빠르게 실행되므로 E열에는 각 판매 직원의 자동차 종류별 총 판매량만 표시됩니다. E2를 선택한 다음 E3, E4 등을 차례로 선택하면 동일한 수식({=C2:C11*D2:D11})이 표시되는 것을 확인할 수 있습니다.
-
단일 셀 배열 수식 만들기
통합 문서의 D13 셀에서 다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=SUM(C2:C11*D2:D11)
이 경우 Excel 값(셀 범위 C2~D11)을 곱한 다음 SUM함수를 사용하여 합계를 함께 추가합니다. 결과에는 판매량 총합계 1,590,000,000이 표시됩니다. 이 예제에서는 배열 수식의 기능이 얼마나 강력한지를 잘 보여 줍니다. 예를 들어 1,000개의 데이터 행이 있다고 가정해 봅니다. 이 경우 수식을 1,000개의 행 아래로 끌어다 놓는 대신 단일 셀에 배열 수식을 만들어 이 데이터의 전부 또는 일부에 대한 합계를 계산할 수 있습니다.
또한 D13 셀의 단일 셀 수식은 다중 셀 수식(E2에서 E11까지 셀의 수식)에 완전히 독립적입니다. 이는 배열 수식을 사용하여 얻을 수 있는 또 다른 이점인 유연성을 나타냅니다. D13의 수식에 영향을 주지 않고 E 열의 수식을 변경하거나 해당 열을 모두 삭제할 수 있습니다.
또한 배열 수식은 다음과 같은 이점을 제공합니다.
-
일관성 E2 셀에서 아래쪽으로 임의의 셀을 클릭하면 동일한 수식이 표시됩니다. 이러한 일관성은 정확성을 더욱 높여 줄 수 있습니다.
-
안전 다중 셀 배열 수식의 구성 요소는 덮어쓸 수 없습니다. 예를 들어 E3 셀을 클릭하고 삭제 를 클릭합니다. 그러지 않으면 배열을 현재 상태 그대로 두어야 합니다. 추가 안전 조치로 Ctrl+Shift+Enter를 눌러 수식의 변경을 확인해야 합니다.
-
작은 파일 크기 여러 개의 중간 수식 대신 단일 배열 수식을 사용할 수 있는 경우가 많습니다. 예를 들어 이 통합 문서에서는 배열 수식을 하나만 사용하여 E열의 결과를 계산할 수 있지만, 표준 수식(예: =C2*D2, C3*D3, C4*D4)을 사용한 경우에는 동일한 결과를 계산하는 데 11개의 수식이 사용될 수 있습니다.
일반적으로 배열 수식은 표준 수식 구문을 사용 합니다. 모두 동일한(=) 기호로 시작하며 배열 수식에서 기본 제공 Excel 함수를 사용할 수 있습니다. 주요 차이점은 배열 수식을 사용할 때 Ctrl+Shift+Enter 키를 눌러 수식을 입력하는 것입니다. 이렇게 하면 Excel 중괄호를 수동으로 입력하면 수식이 텍스트 문자열로 변환되어 작동하지 않습니다.
배열 함수는 복잡한 수식을 빌드하는 효율적인 방법이 될 수 있습니다. 배열 수식 =SUM(C2:C11*D2:D2:D11)은=SUM(C2*D2,C3*D3,C4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D10,C11*D11)과동일합니다.
중요: 배열 수식을 입력해야 할 때마다 Ctrl+Shift+Enter를 누를 수 있습니다. 이는 단일 셀 및 다중 셀 수식 모두에 적용됩니다.
또한 다중 셀 수식을 사용할 때는 다음을 염두에 두어야 합니다.
-
수식을 입력하기 전에 결과를 유지할 셀 범위를 선택해야 합니다. 이는 다중 셀 배열 수식을 만들 때 E2~E11 셀을 선택하여 수행한 작업입니다.
-
배열 수식의 개별 셀 내용을 변경할 수 없습니다. 이렇게 하려면 통합 문서에서 E3 셀을 선택하고 Delete 키를 눌러야 합니다. Excel에서는 배열의 일부분을 변경할 수 없음을 알려 주는 메시지가 표시됩니다.
-
전체 배열 수식을 이동하거나 삭제할 수 있지만 배열 수식의 일부만을 이동하거나 삭제할 수는 없습니다. 즉, 배열 수식을 축소하려면 먼저 기존 수식을 삭제한 다음 다시 시작해야 합니다.
-
배열 수식을 삭제하려면 전체 수식 범위(예: E2:E11)를 선택한다음 삭제 를 누를 수 있습니다.
-
빈 셀을 삽입하거나 다중 셀 배열 수식에서 셀을 삭제할 수 없습니다.
경우에 따라 배열 수식을 확장해야 할 수 있습니다. 기존 배열 범위에서 첫 번째 셀을 선택하고 수식을 확장할 전체 범위를 선택할 때까지 계속합니다. F2를 눌러 수식을 편집한 다음 CTRL+SHIFT+ENTER를 눌러 수식 범위를 조정한 후 수식을 확인합니다. 키는 배열의 왼쪽 위 셀부터 시작하여 전체 범위를 선택하는 것입니다. 왼쪽 위 셀은 편집됩니다.
배열 수식이 유용하기는 하지만 다음과 같은 몇 가지 단점이 있을 수 있습니다.
-
가끔 Ctrl+Shift+Enter를 누르는 것을 잊을 수 있습니다. 이러한 상황은 경험이 많은 Excel 사용자에게도 발생할 수 있습니다. 배열 수식을 입력하거나 편집할 때마다 이 키 조합을 눌러야 한다는 것을 꼭 기억해야 합니다.
-
통합 문서의 다른 사용자는 수식을 이해하지 못할 수 있습니다. 실제로 배열 수식은 일반적으로 워크시트에서 설명되지 않습니다. 따라서 다른 사용자가 통합 문서를 수정해야 하는 경우 배열 수식을 피하거나 해당 사용자가 배열 수식에 대해 알고 필요한 경우 변경 방법을 이해해야 합니다.
-
배열 수식이 큰 경우 컴퓨터의 처리 속도 및 메모리에 따라 계산하는 데 오래 걸릴 수 있습니다.
배열 상수는 배열 수식의 구성 요소입니다. 항목 목록을 입력한 다음 중괄호({ })를 사용하여 목록을수동으로둘러싸서 배열 상수를 만들 수 있습니다.
={1,2,3,4,5}
이제 배열 수식을 만들 때 Ctrl+Shift+Enter를 눌러야 합니다. 배열 상수는 배열 수식의 구성 요소이기 때문에 중괄호를 직접 입력하여 상수를 묶어야 합니다. 그런 다음 Ctrl+Shift+Enter를 사용하여 전체 수식을 입력합니다.
콤마를 사용하여 항목을 구분하는 경우 가로 배열(행)을 생성합니다. 세미코론을 사용하여 항목을 구분하는 경우 세로 배열(열)을 생성합니다. 2차원 배열을 만들 때 콤마를 사용하여 각 행의 항목을 나타 내고 세미코론을 사용하여 각 행을 나타 내는 것이 됩니다.
다음은 {1,2,3,4}의 배열입니다. 다음은 {1;2;3;4}의 배열입니다. 다음은 {1,2,3,4;5,6,7,8}의 행과 4개의 열 배열입니다. 두 행 배열에서 첫 번째 행은 1, 2, 3, 4, 두 번째 행은 5, 6, 7 및 8입니다. 단일 세미코론은 두 행을 4에서 5로 구분합니다.
배열 수식과 마찬가지로 Excel에서 사용 가능한 대부분의 기본 제공 함수와 함께 배열 상수를 사용할 수 있습니다. 다음 섹션에서는 각종 상수를 만드는 방법과 이러한 상수를 Excel 함수와 함께 사용하는 방법을 설명합니다.
다음 절차에 따라 가로, 세로 및 2차원 상수 만드는 방법을 연습해 봅니다.
가로 상수 만들기
-
빈 워크시트에서 A1에서 E1까지 셀을 선택합니다.
-
수식 표시줄에서 다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
={1,2,3,4,5}
이 경우 열기 및 닫기 중괄호({})를 입력해야 Excel 두 번째 집합이 추가됩니다.
다음과 같은 결과가 표시됩니다.
세로 상수 만들기
-
통합 문서에서 한 열의 5개 셀을 선택합니다.
-
수식 표시줄에서 다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
={1;2;3;4;5}
다음과 같은 결과가 표시됩니다.
2차원 상수 만들기
-
통합 문서에서 열 4개와 행 3개로 구성된 셀 블록을 선택합니다.
-
수식 표시줄에서 다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
={1,2,3,4;5,6,7,8;9,10,11,12}
다음과 같은 결과가 나타납니다.
수식에서 상수 사용
다음은 상수를 사용하는 간단한 예제입니다.
-
예제 통합 문서에서 새 워크시트를 만듭니다.
-
셀 A1에 3을 입력한 다음 B1에는 4, C1에는 5, D1에는 6, E1에는 7을 각각 입력합니다.
-
셀 A3에서 다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=SUM(A1:E1*{1,2,3,4,5})
배열 수식으로 입력했기 때문에 Excel에서 다른 괄호 집합으로 상수를 묶습니다.
값 85가 A3 셀에 나타납니다.
다음 섹션에서는 수식 작동 방법에 대해 설명합니다.
방금 사용한 수식은 여러 부분으로 구성되어 있습니다.
1. 함수
2. 저장된 배열
3. 적용 방법
4. 배열 상수
괄호 안의 마지막 요소는 배열 상수({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)을 포함할 수 있습니다. 정수, 소수점 및 과학적 형식으로 숫자를 사용할 수 있습니다. 텍스트를 포함하면 텍스트를 인용 부호(")로 둘러싸야합니다.
배열 상수는 추가 배열, 수식 또는 함수를 포함할 수 없습니다. 즉, 콤마 또는 세미코론으로 구분된 텍스트 또는 숫자만 포함할 수 있습니다. Excel {1,2,A1:D4} 또는 {1,2,SUM(Q2:Z8)}과 같은 수식을 입력하면 경고 메시지가 표시됩니다. 또한 숫자 값은 백분율 기호, 달러 기호, 콤마 또는 괄호를 포함할 수 없습니다.
배열 상수를 사용하는 가장 좋은 방법 중 하나는 이름을 지정하는 것입니다. 이름이 지정된 상수는 사용하기 쉽고 다름 사용자에게 일부 복잡한 배열 수식을 숨길 수 있습니다. 배열 상수의 이름을 지정하여 수식에서 사용하려면 다음을 실행합니다.
-
수식 탭의 정의된 이름 그룹에서 이름 정의를 클릭합니다.
이름 정의 대화 상자가 나타납니다. -
이름 상자에 1분기를 입력합니다.
-
참조 대상 상자에 괄호와 함께 다음 상수를 입력합니다.
={"1월","2월","3월"}
대화 상자의 내용은 다음과 같은 모양으로 표시됩니다.
-
확인을 클릭한 다음 한 행에서 빈 셀 세 개를 선택합니다.
-
다음 수식을 입력한 다음 Ctrl+Shift+Enter 를 누를 수 있습니다.
1분기
다음과 같은 결과가 표시됩니다.
이름이 지정된 상수를 배열 수식으로 사용할 경우에는 등호를 입력해야 합니다. 이렇게 하지 않으면 Excel에서 배열을 텍스트 문자열로 해석하고 수식이 예상대로 작동하지 않습니다. 배열 상수에는 텍스트와 숫자의 조합을 사용할 수 있습니다.
배열 상수가 작동하지 않는 경우 다음과 같은 문제가 발생했을 수 있습니다.
-
일부 요소가 잘못된 문자로 구분되었을 수 있습니다. 콤마 또는 세미코론을 생략하거나 잘못된 장소에 놓은 경우 배열 상수가 올바르게 만들어지지 않을 수 있습니다. 또는 경고 메시지가 표시될 수 있습니다.
-
상수에 포함된 요소 수와 일치하지 않는 셀 범위를 선택했을 수 있습니다. 예를 들어 5개 셀 상수에 사용하기 위해 한 열에서 6개 셀을 선택한 경우에는 빈 셀에 #N/A 오류 값이 표시됩니다. 반대로, 선택한 셀 수가 너무 적은 경우에는 해당하는 셀이 없는 값이 표시되지 않습니다.
다음 예제에서는 배열 수식에서 배열 상수를 사용할 수 있는 몇 가지 방법을 보여 줍니다. 일부 예제에서는 TRANSPOSE 함수를 사용하여 행을 열로 변환하고 그 반대의 경우도 마찬가지입니다.
배열의 각 항목 곱하기
-
새 워크시트를 만든 다음 열 4개와 행 3개로 구성된 빈 셀 블록을 선택합니다.
-
다음 수식을 입력한 다음 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차원 행 바꾸기
-
한 열에서 빈 셀 5개를 선택합니다.
-
다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=TRANSPOSE({1,2,3,4,5})
가로 배열 상수를 입력한 경우에도 TRANSPOSE 함수는 배열 상수를 열로 변환합니다.
1차원 열 바꾸기
-
한 행에서 빈 셀 5개를 선택합니다.
-
다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=TRANSPOSE({1;2;3;4;5})
세로 배열 상수를 입력한 경우에도 TRANSPOSE 함수는 상수를 행으로 변환합니다.
2차원 상수 행/열 바꿈
-
열 3개와 행 4개로 구성된 셀 블록을 선택합니다.
-
다음 상수를 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})
TRANSPOSE 함수는 각 행을 일련의 열로 변환합니다.
이 섹션에서는 기본 배열 수식에 대한 예제를 제공합니다.
기존 값에서 배열 및 배열 상수 만들기
다음 예제에서는 배열 수식을 사용하여 서로 다른 워크시트의 셀 범위 간에 링크를 만드는 방법을 설명합니다. 또한 동일한 값 집합에서 배열 상수를 만드는 방법을 보여줍니다.
기존 값에서 배열 만들기
-
Excel 워크시트에서 C8:E10 셀을 선택하고 다음 수식을 입력합니다.
={10,20,30;40,50,60;70,80,90}
숫자 배열을 만드는 중이므로 10을 입력하기 전에 {(여는 중괄호)를 입력하고 90을 입력한 후에 }(닫는 중괄호)를 입력해야 합니다.
-
배열 수식을 사용하여 셀 범위 C8:E10에서 이 숫자 배열을 입력하는 Ctrl+Shift+Enter를누를 수 있습니다. 워크시트에서 C8~E10의 모양은 다음과 같습니다.
10
20
30
40
50
60
70
80
90
-
C1~E3 셀 범위를 선택합니다.
-
수식 표시줄에 다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=C8:E10
셀의 3x3 배열은 C8에서 E10까지의 셀과 동일한 값을 사용하여 C1~E3 셀에 나타납니다.
기존 값에서 배열 상수 만들기
-
셀 C1:C3을 선택한 경우 F2를 눌러 편집 모드로 전환합니다.
-
F9를 눌러 셀 참조를 값으로 변환합니다. 이 값은 자동으로 배열 상수로 변환됩니다. 이제 수식은 ={10,20,30;40,50,60;70,80,90}입니다.
-
Ctrl+Shift+Enter를 눌러 배열 상수를 배열 수식으로 입력합니다.
셀 범위의 문자 수 계산
다음 예제에서는 공백을 포함하여 셀 범위의 문자 수를 세는 방법을 보여 줍니다.
-
이 전체 표를 복사해 워크시트의 A1 셀에 붙여넣습니다.
데이터
This is a
bunch of cells that
come together
to form a
single sentence.
A2:A6의 총 문자 수
=SUM(LEN(A2:A6))
가장 긴 셀의 내용(A3)
=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)
-
셀 A8을 선택한 다음 Ctrl+Shift+Enter를 눌러 셀 A2:A6(66)의 총 문자 수를 볼 수 있습니다.
-
A10 셀을 선택한 다음 Ctrl+Shift+Enter를 눌러 가장 긴 셀 A2:A6(셀 A3)의 내용을 볼 수 있습니다.
다음 수식은 셀 A8에서 A2~A6 셀의 총 문자 수(66)를 계산합니다.
=SUM(LEN(A2:A6))
이 경우 LEN 함수는 범위의 각 셀에 있는 각 텍스트 문자열의 길이를 반환합니다. 그런 다음 SUM 함수는 이러한 값을 함께 추가하고 결과를 표시합니다(66).
범위에서 n개의 가장 작은 값 찾기
이 예제에서는 셀 범위의 가장 작은 값 세 개를 찾는 방법을 보여 줍니다.
-
셀 A1:A11에 임의의 숫자를 입력합니다.
-
C1~C3 셀을 선택합니다. 이 셀 집합에 배열 수식에서 반환되는 결과가 유지됩니다.
-
다음 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=SMALL(A1:A11,{1;2;3})
이 수식은 배열 상수를 사용하여 SMALL 함수를 세 번 평가하고 셀 A1:A10 셀에 포함된 배열에서 가장 작은(1), 두 번째 작은(2), 세 번째 작은(3) 멤버를 반환하여 더 많은 값을 찾으면 상수에 더 많은 인수를 추가합니다. 이 수식에 SUM 또는 AVERAGE와 같은 추가 함수를 사용할 수도 있습니다. 예를 들면 다음과 같습니다.
=SUM(SMALL(A1:A10,{1,2,3})
=AVERAGE(SMALL(A1:A10,{1,2,3})
범위에서 n개의 가장 큰 값 찾기
범위에서 가장 큰 값을 찾으려면 SMALL 함수를 LARGE 함수로 바꿉니다. 다음 예제에서는 ROW 및 INDIRECT 함수도 사용합니다.
-
D1~D3 셀을 선택합니다.
-
수식 표시줄에서 이 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=LARGE(A1:A10,ROW(INDIRECT("1:3"))
이 단계에서는 ROW 및 INDIRECT 함수에 대해 조금 알아두는 것이 좋습니다. ROW 함수를 사용하면 연속된 정수 배열을 만들 수 있습니다. 예를 들어 연습 통합 문서에서 10개 셀의 빈 열을 선택하고 이 배열 수식을 입력한 다음 Ctrl+Shift+Enter를 누를 수 있습니다.
=ROW(1:10)
10개의 연속된 정수로 구성된 열이 생성됩니다. 잠재적인 문제를 알아보려면 배열 수식이 있는 범위, 즉 1행 위에 행을 삽입합니다. 행 참조가 조정되고 수식에 의해 2~11의 정수가 생성됩니다. 이 문제를 해결하려면 수식에 INDIRECT 함수를 추가합니다.
=ROW(INDIRECT("1:10"))
INDIRECT 함수에서는 범위 1:10이 큰따옴표로 묶여 있기 때문에 텍스트 문자열을 해당 인수로 사용합니다. 이 함수를 사용하면 행을 삽입하거나 배열 수식을 이동할 때 텍스트 값이 자동으로 조정되지 않습니다. 따라서 ROW 함수에서 항상 원하는 정수 배열을 생성합니다.
앞에서 사용한 수식 (=LARGE(A5:A14,ROW(INDIRECT("1:3")) - 내부 괄호에서 시작하여 외부로 작업하는 경우 INDIRECT 함수는 값 1에서 3까지의 텍스트 값 집합을 반환합니다. 행 함수는 차례로 3셀 열 배열을 생성합니다. LARGE 함수는 셀 범위 A5:A14의 값을 사용하며 ROW 함수에서 반환되는 각 참조에 대해 3번 평가됩니다. 값 3200, 2700 및 2000은 3셀 열 배열로 반환됩니다. 더 많은 값을 찾으면 INDIRECT 함수에 더 큰 셀 범위를 추가합니다.
이전 예제와 같이 SUM 및 AVERAGE와 같은 다른 함수와 함께 이 수식을 사용할 수 있습니다.
셀 범위에서 가장 긴 텍스트 문자열 찾기
이전 텍스트 문자열 예제로 돌아가 빈 셀에 다음 수식을 입력하고 Ctrl+Shift+Enter를 누를 수 있습니다.
=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)
텍스트 "셀의 무리"가 나타납니다.
내부 요소에서 시작하여 외부로 작업하는 수식을 자세히 살펴보자. LEN 함수는 셀 범위 A2:A6의 각 항목의 길이를 반환합니다. MAX 함수는 해당 항목 중 가장 큰 값을 계산합니다. 이 값은 A3 셀에 있는 가장 긴 텍스트 문자열에 해당합니다.
지금부터는 계산이 조금 복잡해집니다. MATCH 함수는 가장 긴 텍스트 문자열이 있는 셀의 오프셋(상대 위치)을 계산합니다. 이 계산에는 조회 값, 조회 배열, 일치 형식의 세 가지 인수가 필요합니다. MATCH 함수는 조회 배열에서 지정된 조회 값을 검색합니다. 이 예제의 경우 조회 값은 가장 긴 텍스트 문자열입니다.
(MAX(LEN(A2:A6))
또한 해당 문자열은 다음 배열에 있습니다.
LEN(A2:A6)
일치 형식 인수는 0 입니다. 일치 유형은 1, 0 또는 -1 값으로 구성될 수 있습니다. 1을 지정하면 MATCH는 검색 값보다 작거나 같은 가장 큰 값을 반환합니다. 0을 지정하면 MATCH는 첫 번째 값을 검색 값과 정확히 동일하게 반환합니다. -1을 지정하는 경우 MATCH는 지정된 검색 값보다 크거나 같은 가장 작은 값을 찾습니다. 일치 유형을 생략하면 Excel 가정합니다.
끝으로, INDEX 함수에서는 배열과 해당 배열 내의 행 및 열 번호를 인수를 사용합니다. 셀 범위 A2:A6은 배열을 제공하며 MATCH 함수는 셀 주소를 제공하며, 최종인수(1)는값이 배열의 첫 번째 열에서 온 것으로 지정합니다.
이 섹션에서는 고급 배열 수식에 대한 예제를 제공합니다.
오류 값이 포함된 범위 더하기
오류 값(예: #N/A)이 포함된 범위를 더할 경우에는 Excel의 SUM 함수가 작동하지 않습니다. 이 예제에서는 오류가 있는 데이터 범위의 값을 더하는 방법을 보여 줍니다.
=SUM(IF(ISERROR(데이터),"",데이터))
이 수식은 원래 값에서 오류 값을 제외한 값이 포함된 새 배열을 만듭니다. 내부 함수에서 시작하여 외부로 작업하는 ISERROR 함수는 셀 범위(데이터)를 검색하여 오류를 검색합니다. IF 함수는 지정한 조건이 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 함수로 전달합니다.
이러한 함수는 TRUE 또는 FALSE 중 하나의 결과를 반환하고 배열 함수에 결과 배열이 필요하기 때문에 배열 수식에서 AND 및 OR 함수를 직접 사용할 수 없습니다. 이전 수식에 표시된 논리를 사용하여 문제를 해결할 수 있습니다. 즉, OR 또는 AND 조건을 충족하는 값에 대한 추가 또는 곱셈과 같은 수학 작업을 수행할 수 있습니다.
0을 제외한 평균 계산
이 예제에서는 해당 범위에 포함된 값의 평균을 구해야 하는 경우 범위에서 0을 제외하는 방법을 보여 줍니다. 다음 수식에서는 판매라는 데이터 범위를 사용합니다.
=AVERAGE(IF(판매액<>0,판매액))
IF 함수는 0이 아닌 값의 배열을 만든 다음 이 값을 AVERAGE 함수로 전달합니다.
두 셀 범위 간의 차이 계산
이 배열 수식은 MyData와 YourData라는 두 셀 범위의 값을 비교하고 둘 사이의 차이점 수를 반환합니다. 두 범위의 내용이 동일하면 수식은 0을 반환합니다. 이 수식을 사용하려면 셀 범위가 같은 크기와 차원이 같아야 합니다(예: MyData가 5열에 3개 행의 범위인 경우 YourData도 5개 열의 행 3개가 되어야 합니다.
=SUM(IF(내 데이터=기타 데이터,0,1))
이 수식에서는 비교할 범위와 크기가 같은 새 배열을 만듭니다. IF 함수는 값 0(일치하지 않는 셀)과 값 1(동일한 셀)로 배열을 채웁니다. 그런 다음 SUM 함수는 배열 값의 합계를 반환합니다.
이 수식을 다음과 같이 간단하게 고칠 수 있습니다.
=SUM(1*(MyData<>YourData))
범위의 오류 값을 계산하는 수식과 마찬가지로 이 수식은 TRUE*1=1 및 FALSE*1=0을 조건으로 작동합니다.
범위에서 최대값 위치 찾기
다음 배열 수식은 데이터라는 단일 열 배열에서 최대값이 있는 행의 번호를 반환합니다.
=MIN(IF(데이터=MAX(데이터),ROW(데이터),""))
IF 함수는 데이터라는 범위에 해당하는 새 배열을 만듭니다. 해당 셀에 범위의 최대 값이 포함된 경우 배열에 행 번호가 포함되어 있습니다. 그렇지 않으면 배열에 빈문자열("" )이 포함되어 있습니다.MIN 함수는 새 배열을 두 번째 인수로 사용하여 데이터에서 최대 값의 행 수에 해당하는 가장 작은 값을 반환합니다. 데이터라는 범위에 동일한 최대 값이 포함된 경우 수식은 첫 번째 값의 행을 반환합니다.
최대값의 실제 셀 주소를 반환하려면 다음 수식을 사용합니다.
=ADDRESS(MIN(IF(데이터=MAX(데이터),ROW(데이터),"")),COLUMN(데이터))
인정
이 문서의 일부분은 Colin Wilcox가 Excel Power User 열 시리즈를 기반으로 하여 2002년 14장과 15장에서 Excel MVP인 John Walkenbach가 Excel 작성했습니다.
추가 도움이 필요하세요?
언제든지 Excel 기술 커뮤니티에서 전문가에게 문의하거나 커뮤니티에서 지원을 받을 수 있습니다.