배열 수식 지침 및 예제

배열 수식 지침 및 예제

참고: 사용자 언어로 가능한 한 빨리 가장 최신의 도움말 콘텐츠를 제공하고자 합니다. 이 페이지는 자동화를 통해 번역되었으며 문법 오류나 부정확한 설명을 포함할 수 있습니다. 이 목적은 콘텐츠가 사용자에게 유용하다는 것입니다. 이 페이지 하단의 정보가 도움이 되었다면 알려주세요. 쉽게 참조할 수 있는 영어 문서가 여기 있습니다.

배열 수식은 배열에서 하나 이상의 항목에 대해 여러 개의 계산을 수행할 수 있는 수식입니다. 배열을 값의 행 또는 열 또는 값의 행과 열의 조합으로 생각할 수 있습니다. 배열 수식은 여러 결과를 반환 하거나 단일 결과를 반환할 수 있습니다.

Office 365의 9 월 2018 업데이트로 시작 하 여 여러 결과를 반환할 수 있는 수식은 자동으로 아래로 또는 인접 한 셀로 분할 됩니다. 이러한 동작 변경은 몇 가지 새로운 동적 배열 함수를 동반 하기도 합니다. 기존 함수 또는 동적 배열 함수를 사용 하 고 있는지에 관계 없이 동적 배열 수식은 단일 셀에만 입력 하면 되며 enter키를 눌러 확인 해야 합니다. 이전, 레거시 배열 수식에서는 먼저 전체 출력 범위를 선택한 다음 Ctrl + Shift + Enter를 사용 하 여 수식을 확인 해야 합니다. 일반적으로 CSE 수식 이라고 합니다.

다음과 같은 복잡 한 작업을 수행 하는 데 배열 수식을 사용할 수 있습니다.

  • 예제 데이터 집합을 빠르게 만듭니다.

  • 셀 범위에 포함 된 문자 수를 계산 합니다.

  • 범위에서 가장 작은 값 이나 상한과 하 한 사이에 있는 숫자와 같이 특정 조건을 만족 하는 숫자만 합산 합니다.

  • 값 범위에서 매 n 번째 값의 합계를 계산 합니다.

다음 예제에서는 다중 셀 및 단일 셀 배열 수식을 만드는 방법을 보여 줍니다. 가능 하면 몇 가지 동적 배열 함수를 비롯 하 여 예를 포함 하 고 동적 배열과 레거시 배열로 모두 입력 한 기존 배열 수식을 사용 합니다.

예제 다운로드

이 문서의 모든 배열 수식 예제를 포함 하는 예제 통합 문서를 다운로드합니다.

이 연습에서는 여러 셀 및 단일 셀 배열 수식을 사용 하 여 판매 수치를 계산 하는 방법을 보여 줍니다. 첫 번째 단계 집합에서는 여러 셀 수식을 사용 하 여 부분합 집합을 계산 합니다. 두 번째 집합에서는 단일 셀 수식을 사용 하 여 총계를 계산 합니다.

  • 다중 셀 배열 수식

    H 셀의 여러 셀 배열 함수 = F10: F19 * G10: G19 단가로 판매 된 자동차 수 계산

  • 여기에서는 h 셀에 = F10: F19 * G10: G19 를 입력 하 여 각 판매원의 총 매출을 계산 하 고 있습니다.

    Enter 키를 누르면 결과가 h: H19 셀로 분산 되어 표시 됩니다. 분할 범위 내에서 셀을 선택 하면 분할 범위가 테두리로 강조 표시 됩니다. H: H19 셀의 수식은 회색으로 표시 될 수도 있습니다. 단지 참조를 위한 것 이므로 수식을 조정 하려면 마스터 수식이 있는 셀 h를 선택 해야 합니다.

  • 단일 셀 배열 수식

    = SUM (F10: F19 * G10: G19)을 사용 하 여 총합계를 계산 하는 단일 셀 배열 수식

    예제 통합 문서의 셀 H20에 = SUM (F10: F19 * G10: G19)을 입력 하거나 복사 하 여 붙여 넣은 다음 enter 키 를 누릅니다.

    이 경우 Excel은 배열의 값 (셀 범위 F10 ~ G19을 곱한 다음 SUM 함수를 사용 하 여 합계를 함께 추가 합니다. 결과에는 판매량 총합계 1,590,000,000이 표시됩니다.

    이 예제에서는 배열 수식의 기능이 얼마나 강력한지를 잘 보여 줍니다. 예를 들어 1,000개의 데이터 행이 있다고 가정해 봅니다. 이 경우 수식을 1,000개의 행 아래로 끌어다 놓는 대신 단일 셀에 배열 수식을 만들어 이 데이터의 전부 또는 일부에 대한 합계를 계산할 수 있습니다. 또한 H20 셀의 단일 셀 수식은 여러 셀 수식 (h 셀의 수식)에 완전히 독립적입니다. 이는 배열 수식을 사용하여 얻을 수 있는 또 다른 이점인 유연성을 나타냅니다. H20의 수식에 영향을 주지 않고 H 열에서 다른 수식을 변경할 수 있습니다. 또한 결과의 정확성을 확인 하는 데 도움이 되므로 이와 같이 독립적으로 합계를 유지 하는 것이 좋습니다.

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

    • 일관성    H 아래쪽에 있는 셀을 클릭 하면 동일한 수식이 표시 됩니다. 이러한 일관성은 정확성을 더욱 높여 줄 수 있습니다.

    • 안전    여러 셀 배열 수식의 구성 요소는 덮어쓸 수 없습니다. 예를 들어 H11 셀을 클릭 하 고 Delete 키를 누릅니다. Excel에서는 배열의 출력이 변경 되지 않습니다. 이를 변경 하려면 배열 또는 셀 h에서 왼쪽 위 셀을 선택 해야 합니다.

    • 작은 파일 크기    여러 개의 중간 수식 대신 단일 배열 수식을 사용할 수 있는 경우가 많습니다. 예를 들어 차량 판매 예제에서는 배열 수식을 하나 사용 하 여 열 E의 결과를 계산 합니다. = F10 * G10, F11 * G11, F12 * G12 등의 표준 수식을 사용한 경우에는 11 개의 다른 수식을 사용 하 여 동일한 결과를 계산할 수 있습니다. 이는 큰 문제가 되지 않지만 총 행 수가 많은 경우에는 어떨까요? 이렇게 하면 큰 차이를 만들 수 있습니다.

    • 효율성    배열 함수를 통해 복잡 한 수식을 효율적으로 만들 수 있습니다. Array 수식 = SUM (F10: F19 * G10: G19)은 = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * Techniques for, F19 * G19.

    • Spilling    동적 배열 수식이 자동으로 출력 범위로 분할 됩니다. 원본 데이터가 Excel 표에 있는 경우 데이터를 추가 하거나 제거할 때 동적 배열 수식의 크기가 자동으로 조정 됩니다.

    • #SPILL! 오류    동적 배열에 #SPILL! 오류가 발생 했습니다.특정 이유 때문에 의도 된 분할 범위가 차단 됨을 나타냅니다. Blockage를 해결 하면 수식이 자동으로 분산 됩니다.

배열 상수는 배열 수식의 구성 요소입니다. 다음과 같이 항목 목록을 입력 한 다음 중괄호 ({})를 사용 하 여 수동으로 목록을 둘러싸는 배열 상수를 만들 수 있습니다.

= {1, 2, 3, 4, 5} 또는 = {"1 월", "2 월", "3 월"}

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

다음 절차에 따라 가로, 세로 및 2차원 상수 만드는 방법을 연습해 봅니다. SEQUENCE 함수 를 사용 하 여 배열 상수를 자동으로 생성 하 고 배열 상수를 수동으로 입력 하는 예제를 보여 드리겠습니다.

  • 가로 상수 만들기

    이전 예제의; 통합 문서를 사용하거나 새 통합 문서를 만듭니다. 빈 셀을 선택 하 고 = SEQUENCE (1, 5)를 입력 합니다. SEQUENCE 함수는 = {1, 2, 3, 4, 5}와 동일한 1 개의 행을 5 개 열 배열로 작성 합니다. 다음과 같은 결과가 표시 됩니다.

    = SEQUENCE (1, 5) 또는 = {1, 2, 3, 4, 5}를 사용 하 여 가로 배열 상수를 만듭니다.

  • 세로 상수 만들기

    아래쪽에 공간이 있는 빈 셀을 선택 하 고 = SEQUENCE (5)또는 = {1; 2; 3; 4; 5}를 입력 합니다. 다음과 같은 결과가 표시 됩니다.

    = SEQUENCE (5) 또는 = {1; 2; 3; 4; 5}를 사용 하 여 세로 배열 상수를 만듭니다.

  • 2차원 상수 만들기

    오른쪽 아래에 공간이 있는 빈 셀을 선택 하 고 = SEQUENCE (3, 4)를 입력 합니다. 다음과 같은 결과가 나타납니다.

    = SEQUENCE (3, 4)를 사용 하 여 3 행 4 열 배열 상수를 만듭니다.

    또는 = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}를 입력할 수도 있지만, 세미 콜론 및 쉼표를 입력 하는 위치에 주의를 기울여야 합니다.

    여기에서 알 수 있듯이, SEQUENCE 옵션은 배열 상수 값을 수동으로 입력 하는 방법 보다 상당한 장점을 제공 합니다. 이 방법을 사용 하면 시간을 절약할 수 있지만 수동 입력의 오류를 줄이는 데도 도움이 됩니다. 또한, 특히 세미 콜론을 쉼표 구분 기호와 구분 하는 데 어려움을 더 쉽게 읽을 수 있습니다.

다음은 배열 상수를 큰 수식의 일부로 사용 하는 예제입니다. 예제 통합 문서에서 수식 워크시트의 상수로 이동 하거나 새 워크시트를 만듭니다.

셀 D9에 = SEQUENCE (1, 5, 3, 1)를 입력 했지만 A9: h 4 셀에 3, 4, 5, 6, 7을 입력할 수도 있습니다. 특정 숫자 선택에 대 한 특별 한 것이 없기 때문에 1-5이 아닌 다른 항목을 선택한 것입니다.

E11 셀에 = sum (d9: h9 * SEQUENCE (1, 5))또는 = sum (d9: H9 * {1, 2, 3, 4, 5})을 입력 합니다. 수식은 85를 반환 합니다.

수식에 배열 상수를 사용 합니다. 이 예제에서는 = SUM (D9: H (* SEQUENCE (1, 5))를 사용 했습니다.

SEQUENCE 함수는 배열 상수 {1, 2, 3, 4, 5}에 해당 하는 값을 작성 합니다. Excel에서는 괄호로 묶인 식에 대해 연산을 수행 하기 때문에 다음 두 요소는 D9: h 2의 셀 값과 곱하기 연산자 (*)로 시작 됩니다. 이 시점에서 수식은 저장 된 배열의 값을 상수의 해당 값과 곱합니다. 이는 다음과 같은 기능입니다.

= 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) * 순서 (1, 5))또는 = sum ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

배열 상수에 사용할 수 있는 요소

  • 배열 상수에는 숫자, 텍스트, 논리 값 (예: TRUE, FALSE), 오류 값 (예: #N/A)이 포함 될 수 있습니다. 정수, 소수, 과학적 형식으로 숫자를 사용할 수 있습니다. 텍스트를 포함 하는 경우 따옴표로 묶어야 합니다 ("text").

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

배열 상수를 사용 하는 가장 좋은 방법 중 하나는 이름을 설명 하는 것입니다. 이름이 지정된 상수는 사용하기 쉽고 다름 사용자에게 일부 복잡한 배열 수식을 숨길 수 있습니다. 배열 상수의 이름을 지정하여 수식에서 사용하려면 다음을 실행합니다.

수식 _GT_ 정의 된 이름 > 이름 정의를 참조 하세요. 이름 상자에 1 분기를 입력 합니다. 참조 대상 상자에 괄호와 함께 다음 상수를 입력합니다.

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

대화 상자가 이제 다음과 같이 표시 됩니다.

수식 > 정의 된 이름 > Name Manager > New를 통해 명명 된 배열 상수를 추가 합니다.

확인을 클릭 한 다음 빈 셀이 세 개 있는 행을 선택 하 고 = 1 분기을 입력 합니다.

다음과 같은 결과가 표시 됩니다.

= {"1 월", "2 월", "1 분기", "3 월"으로 정의 된 = 1 분기와 같은 수식에 명명 된 배열 상수를 사용 합니다.

결과를 가로로 배치 하지 않고 세로로 분할 하려면 =전치(1 분기)를 사용 하면 됩니다.

재무 문을 작성할 때와 같이 12 개월 목록을 표시 하려는 경우에는 SEQUENCE 함수를 사용 하 여 현재 연도를 기준으로 할 수 있습니다. 이 함수는 월만 표시 되지만 다른 계산에서 사용할 수 있는 유효한 날짜가 있다는 장점이 있습니다. 예제 통합 문서의 명명 된 배열 상수 빠른 샘플 데이터 집합 워크시트에 이러한 예가 나와 있습니다.

= TEXT (날짜 (연도 (TODAY ()), SEQUENCE (1, 12), 1), "mmm")

텍스트, 날짜, 연도, 오늘 및 시퀀스 함수의 조합을 사용 하 여 12 개월의 동적 목록을 작성 합니다.

Date 함수 를 사용 하 여 현재 연도를 기준으로 날짜를 만들고, SEQUENCE는 1 년 1 월 ~ 12 월에 대 한 배열 상수를 만들고, TEXT 함수 는 표시 형식을 "mmm" (Jan, 2 월, 3 월 등)으로 변환 합니다. 1 월과 같이 정식 월 이름을 표시 하려면 "mmmm"을 사용 합니다.

명명 된 상수를 배열 수식으로 사용 하는 경우에는 1 분기 아니라 = 1 분기와 같이 등호를 입력 해야 합니다. 이렇게 하지 않으면 Excel에서 배열을 텍스트 문자열로 해석하고 수식이 예상대로 작동하지 않습니다. 마지막으로, 함수, 텍스트 및 숫자 조합을 사용할 수 있다는 점에 유의 하세요. 이는 모든 소재를 얻는 방법에 따라 달라 집니다.

다음 예제에서는 배열 수식에서 배열 상수를 사용할 수 있는 몇 가지 방법을 보여 줍니다. 일부 예제에서는 행을 열로 변환 하 고 그 반대의 값으로 바꾸는 함수 를 사용 합니다.

  • 배열의 각 항목을 여러 개

    = SEQUENCE (1, 12) * 2, = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12} * 2 를 입력 합니다.

    (/)와 함께 (/), 더하기 (+), 빼기 (-)로 나눌 수도 있습니다.

  • 배열의 항목 제곱

    = SEQUENCE (1, 12) ^ 2, or = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12} ^ 2 입력

  • 배열에 있는 제곱 항목의 제곱근 찾기

    = Sqrt (순서 (1, 12) ^ 2), = sqrt ({1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12} ^ 2) 을 입력 합니다.

  • 1차원 행 바꾸기

    Enter = 바꾸기 (순서 (1, 5))또는 = 바꿈 ({1, 2, 3, 4, 5})

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

  • 1차원 열 바꾸기

    Enter = 바꾸기 (순서 (5, 1))또는 = 바꿈 ({1; 2; 3; 4, 5})

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

  • 2차원 상수 행/열 바꿈

    Enter = 바꾸기 (순서 (3, 4))또는 = 바꿈 ({1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12})

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

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

  • 기존 값에서 배열 만들기

    다음 예제에서는 배열 수식을 사용 하 여 기존 배열에서 새 배열을 만드는 방법을 설명 합니다.

    Enter = SEQUENCE (3, 6, 10, 10), = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    숫자 배열을 만들기 때문에 10을 입력 하기 전에 {(여는 중괄호)를 입력 하 고 180을 입력 한 후에는} (닫는 중괄호)를 입력 해야 합니다.

    다음으로, 빈 셀에 = d9 #또는 = d9: I11 를 입력 합니다. 셀의 3 x 6 배열은 D9: e 4에 표시 되는 것과 동일한 값으로 표시 됩니다. # Sign을 분산 범위 연산자라고 하며,이를 입력 하는 대신 전체 배열 범위를 참조 하는 것은 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)

    이 경우 match 형식 인수는 0입니다. Match 형식은 1, 0 또는-1 값이 될 수 있습니다.

    • 1-조회 val 보다 작거나 같은 값의 최대값을 반환 합니다.

    • 0-첫 번째 값을 조회 값과 정확 하 게 반환 합니다.

    • -1-지정 된 조회 값 보다 크거나 같은 가장 작은 값을 반환 합니다.

    • 일치 유형을 생략 하면 Excel에서 1로 간주 합니다.

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

    가장 작은 텍스트 문자열의 내용을 가져오려면 위의 예제에서 MAX를 MIN으로 바꿉니다.

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

    이 예제에서는 B9: B18has 셀의 샘플 데이터 배열이: = INT (RANDARRAY(10, 1) * 100)으로 생성 되는 셀 범위에서 가장 작은 세 개의 값을 찾는 방법을 보여 줍니다. RANDARRAY는 volatile 함수 이므로 Excel에서 계산할 때마다 새로운 난수 집합을 받게 됩니다.

    N 번째 가장 작은 값을 찾는 Excel 배열 수식: = 작음 (B9 #, SEQUENCE (D9))

    Enter = small (B9 #, SEQUENCE (D9), = SMALL (B9: B18, {1; 2; 3})

    이 수식은 array 상수를 사용 하 여 SMALL 함수 를 세 번 평가 하 고 B9: B18 셀에 포함 된 배열의 가장 작은 구성원 (3은 셀 D9의 변수 값)을 반환 합니다. 값을 더 찾으려면 SEQUENCE 함수의 값을 늘리거나 상수에 인수를 더 추가할 수 있습니다. 이 수식에 SUM 또는 AVERAGE와 같은 추가 함수를 사용할 수도 있습니다. 예를 들면 다음과 같습니다.

    = SUM (는 작음 (B9 #, SEQUENCE (D9))

    = AVERAGE (작음 (B9 #, SEQUENCE (D9))

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

    범위에서 가장 큰 값을 찾기 위해 SMALL 함수를 큰 함수로바꿀 수 있습니다. 다음 예제에서는 ROWINDIRECT 함수도 사용합니다.

    Enter = large (B9 #, ROW (간접 ("1:3")))또는 = 큼 (b9: B18, ROW (("1:3"))))))))))))))

    이 단계에서는 ROW 및 INDIRECT 함수에 대해 조금 알아두는 것이 좋습니다. ROW 함수를 사용하면 연속된 정수 배열을 만들 수 있습니다. 예를 들어 빈를 선택 하 고 다음을 입력 합니다.

    =ROW(1:10)

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

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

    INDIRECT 함수는 텍스트 문자열을 인수로 사용 합니다 (범위 1:10이 인용 부호로 둘러싸인 이유). 이 함수를 사용하면 행을 삽입하거나 배열 수식을 이동할 때 텍스트 값이 자동으로 조정되지 않습니다. 따라서 ROW 함수에서 항상 원하는 정수 배열을 생성합니다. 다음과 같이 시퀀스를 사용 하는 것 처럼 간편 합니다.

    = SEQUENCE (10)

    이전에 사용 하 던 수식 (B9 #, ROW (간접 ("1:3"))-안쪽 괄호에서 시작 하 여 바깥쪽으로 작업 합니다. INDIRECT 함수는 텍스트 값 집합 (이 예제의 경우 값 1 ~ 3)을 반환 합니다. 그런 다음 ROW 함수는 3 셀 열 배열을 생성 합니다. LARGE 함수는 B9: B18 셀 범위의 값을 사용 하며, ROW 함수에서 반환 되는 각 참조에 대해 한 번씩 3 번 계산 됩니다. 값을 더 찾으려면 INDIRECT 함수에 큰 셀 범위를 추가 합니다. 마지막으로, 작은 예제에서는 SUM 및 AVERAGE와 같은 다른 함수에이 수식을 사용할 수 있습니다.

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

    오류 값이 포함 된 범위 (예: #VALUE)의 합계를 계산 하려고 하면 Excel의 SUM 함수가 작동 하지 않습니다. 또는 #N/A 이 예제에서는 오류가 있는 데이터 라는 범위의 값 합계를 계산 하는 방법을 보여 줍니다.

    배열을 사용 하 여 오류를 처리 합니다. 예를 들어 = SUM (IF (ISERROR (데이터), "", 데이터)는 #VALUE! 등의 오류가 포함 된 경우에도 데이터 범위를 합산 합니다. 또는 #NA!.

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

    이 수식은 원래 값에서 오류 값을 제외한 값이 포함된 새 배열을 만듭니다. 이 함수는 내부 함수부터 시작 하 여 바깥쪽으로 작업 하 여 셀 범위 (데이터)에서 오류를 검색 합니다. IF 함수는 지정한 조건이 TRUE로 평가 되는 경우 특정 값을 반환 하 고 FALSE로 평가 되는 경우 다른 값을 반환 합니다. 따라서 오류 값이 포함되지 않습니다. 그런 다음 SUM 함수는 필터링된 배열의 합계를 계산합니다.

  • 범위의 오류 값 개수 계산

    이 예제는 이전 수식과 비슷하지만 필터링 하는 대신 Data 라는 범위의 오류 값 개수를 반환 합니다.

    =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 (Sales>0, Sales)) 이면 Sales 라는 범위에서 0 보다 큰 모든 값의 합계를 계산 합니다.

예를 들어 다음 배열 수식은 Sales 라는 범위의 양수 정수만 합산 하 고 위의 예제에서 E9: E24 셀을 나타냅니다.

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

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

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

= SUM ((Sales>0) * (Sales<2500) * (Sales))

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

OR 조건을 사용하는 배열 수식을 만들 수도 있습니다. 예를 들어 0 보다 크거나 2500 보다 작은 값의 합계 를 만들 수 있습니다.

= SUM (IF (((Sales>0) + (Sales<2500), Sales))

배열 수식에는 AND 및 OR 함수를 직접 사용할 수 없으며, 이러한 함수는 TRUE 또는 FALSE를 반환 하 고, 배열 함수에는 결과 배열이 필요 합니다. 이전 수식에 나와 있는 논리를 사용 하 여 문제를 해결할 수 있습니다. 즉, OR 또는 AND 조건을 충족 하는 값에 더하기 또는 곱하기와 같은 수학 연산을 수행 합니다.

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

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

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

이 배열 수식은 MyData 및 해당 데이터 라는 두 셀 범위에 있는 값을 비교 하 고 둘 사이의 차이 수를 반환 합니다. 두 범위의 내용이 같으면 수식이 0을 반환 합니다. 이 수식을 사용 하려면 셀 범위는 같은 크기와 차원을 동일 하 게 지정 해야 합니다. 예를 들어, MyData가 열 개의 행이 5 개에 해당 하는 범위인 경우 데이터는 각각 3 행에 5 열 이어야 합니다.

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

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

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

= SUM (1 * (MyData<>YourData))

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

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

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

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

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

=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)

  1. 각 판매원에 대 한 E11의 총 판매량을 보려면 E2: 셀을 선택 하 고 수식 = C2: C11 * D2:c 2를 입력 한 다음 Ctrl + Shift + enter를 누릅니다.

  2. 모든 판매액의 총합계를 보려면 F11 셀을 선택 하 고 수식 = SUM (C2: C11 * D2:c 2)을 입력 한 다음 Ctrl + Shift + enter를 누릅니다.

Ctrl + Shift + enter를 누르면 수식이 중괄호 ({})로 둘러싸인 다음 선택한 범위의 각 셀에 수식의 인스턴스가 삽입 됩니다. 이 작업은 매우 빠르게 실행되므로 E열에는 각 판매 직원의 자동차 종류별 총 판매량만 표시됩니다. E2를 선택한 다음 E3, E4 등을 차례로 선택하면 동일한 수식({=C2:C11*D2:D11})이 표시되는 것을 확인할 수 있습니다. 

배열 수식을 통해 계산된 E열의 합계

  • 단일 셀 배열 수식 만들기

통합 문서의 셀 D13에 다음 수식을 입력 하 고 Ctrl + Shift + enter를 누릅니다.

=SUM(C2:C11*D2:D11)

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

또한 D13 셀의 단일 셀 수식은 여러 셀 수식 (E2 ~ E11 셀의 수식)에 완전히 독립적입니다. 이는 배열 수식을 사용하여 얻을 수 있는 또 다른 이점인 유연성을 나타냅니다. D13의 수식에 영향을 주지 않고 E 열의 수식을 변경 하거나 해당 열을 모두 삭제할 수 있습니다.

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

  • 일관성    E2 셀에서 아래쪽으로 임의의 셀을 클릭하면 동일한 수식이 표시됩니다. 이러한 일관성은 정확성을 더욱 높여 줄 수 있습니다.

  • 안전    다중 셀 배열 수식의 구성 요소는 덮어쓸 수 없습니다. 예를 들어 E3 셀을 클릭 하 고 delete키를 누릅니다. 그러지 않으면 배열을 현재 상태 그대로 두어야 합니다. 보안을 강화 하기 위해 Ctrl + Shift + Enter 를 눌러 수식의 변경 내용을 확인 해야 합니다.

  • 작은 파일 크기    여러 개의 중간 수식 대신 단일 배열 수식을 사용할 수 있는 경우가 많습니다. 예를 들어 이 통합 문서에서는 배열 수식을 하나만 사용하여 E열의 결과를 계산할 수 있지만, 표준 수식(예: =C2*D2, C3*D3, C4*D4)을 사용한 경우에는 동일한 결과를 계산하는 데 11개의 수식이 사용될 수 있습니다.

일반적으로 배열 수식에는 표준 수식 구문이 사용 됩니다. 모두 등호 (=)로 시작 하 고, 배열 수식에 대부분의 기본 제공 Excel 함수를 사용할 수 있습니다. 주요 차이점은 배열 수식을 사용 하는 경우 Ctrl + Shift + Enter 를 눌러 수식을 입력 하는 것입니다. 이렇게 하면 Excel에서 배열 수식을 중괄호로 둘러쌉니다. 중괄호를 수동으로 입력 하면 수식이 텍스트 문자열로 변환 되 고 제대로 작동 하지 않습니다.

배열 함수를 통해 복잡 한 수식을 효율적으로 만들 수 있습니다. Array 수식 = sum (c2: C11 * D2: c 1)은 = sum (c2 * d2, C3 * D3, C4 * D4, C5 * D5, C6 * D6, C7 * D7, C8 * D8, C9 * D9, C10 * D10, C11 *c 2)와 같습니다.

중요: 배열 수식을 입력 해야 할 때마다 ctrl + Shift + enter 를 누릅니다. 이는 단일 셀 및 여러 셀 수식에 모두 적용 됩니다.

또한 다중 셀 수식을 사용할 때는 다음을 염두에 두어야 합니다.

  • 수식을 입력하기 에 결과를 유지할 셀 범위를 선택해야 합니다. 이는 다중 셀 배열 수식을 만들 때 E2~E11 셀을 선택하여 수행한 작업입니다.

  • 배열 수식의 개별 셀 내용을 변경할 수 없습니다. 이렇게 하려면 통합 문서에서 E3 셀을 선택하고 Delete 키를 눌러야 합니다. Excel에서는 배열의 일부분을 변경할 수 없음을 알려 주는 메시지가 표시됩니다.

  • 전체 배열 수식을 이동하거나 삭제할 수 있지만 배열 수식의 일부만을 이동하거나 삭제할 수는 없습니다. 즉, 배열 수식을 축소하려면 먼저 기존 수식을 삭제한 다음 다시 시작해야 합니다.

  • 배열 수식을 삭제 하려면 전체 수식 범위 (예 : E2: E11)를 선택한 다음 delete키를 누릅니다.

  • 여러 셀 배열 수식에서 빈 셀을 삽입 하거나 셀을 삭제할 수는 없습니다.

경우에 따라 배열 수식을 확장해야 할 수 있습니다. 기존 배열 범위에서 첫 번째 셀을 선택 하 고 수식을 확장 하려는 전체 범위를 선택할 때까지 계속 합니다. 수식을 편집 하려면 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} 개의 배열이 있습니다. 여기에는 두 개의 행과 4 개의 열로 이루어진 배열 {1, 2, 3, 4, 5, 6, 7, 8}이 있습니다. 두 행 배열의 첫 번째 행은 1, 2, 3, 4이 고 두 번째 행은 5, 6, 7 및 8입니다. 한 개의 세미콜론으로 4와 5 사이의 두 행을 구분 합니다.

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

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

가로 상수 만들기

  1. 빈 워크시트에서 A1 ~ E1 셀을 선택 합니다.

  2. 수식 입력줄에 다음 수식을 입력 한 다음 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}

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

    배열 수식의 2차원 배열 상수

수식에서 상수 사용

다음은 상수를 사용하는 간단한 예제입니다.

  1. 예제 통합 문서에서 새 워크시트를 만듭니다.

  2. 셀 A1에 3을 입력한 다음 B1에는 4, C1에는 5, D1에는 6, E1에는 7을 각각 입력합니다.

  3. 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)이 포함 될 수 있습니다. 정수, 소수, 과학적 형식으로 숫자를 사용할 수 있습니다. 텍스트를 포함 하는 경우 텍스트 앞뒤에 따옴표 (")를 사용 해야 합니다.

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

배열 상수를 사용 하는 가장 좋은 방법 중 하나는 이름을 이름으로 하는 것입니다. 이름이 지정된 상수는 사용하기 쉽고 다름 사용자에게 일부 복잡한 배열 수식을 숨길 수 있습니다. 배열 상수의 이름을 지정하여 수식에서 사용하려면 다음을 실행합니다.

  1. 수식 탭의 정의된 이름 그룹에서 이름 정의를 클릭합니다.
    이름 정의 대화 상자가 나타납니다.

  2. 이름 상자에 1분기를 입력합니다.

  3. 참조 대상 상자에 괄호와 함께 다음 상수를 입력합니다.

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

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

    수식을 사용하여 이름 대화 상자 편집

  4. 확인을 클릭한 다음 한 행에서 빈 셀 세 개를 선택합니다.

  5. 다음 수식을 입력 하 고 ctrl + Shift + Enter를 누릅니다.

    1분기

    다음과 같은 결과가 표시됩니다.

    수식으로 입력된 명명된 배열

이름이 지정된 상수를 배열 수식으로 사용할 경우에는 등호를 입력해야 합니다. 이렇게 하지 않으면 Excel에서 배열을 텍스트 문자열로 해석하고 수식이 예상대로 작동하지 않습니다. 배열 상수에는 텍스트와 숫자의 조합을 사용할 수 있습니다.

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

  • 일부 요소가 잘못된 문자로 구분되었을 수 있습니다. 쉼표나 세미콜론을 생략 하거나 잘못 된 위치에 배치 하는 경우 배열 상수가 제대로 만들어지지 않거나 경고 메시지가 표시 될 수 있습니다.

  • 상수에 포함된 요소 수와 일치하지 않는 셀 범위를 선택했을 수 있습니다. 예를 들어 5개 셀 상수에 사용하기 위해 한 열에서 6개 셀을 선택한 경우에는 빈 셀에 #N/A 오류 값이 표시됩니다. 반대로, 선택한 셀 수가 너무 적은 경우에는 해당하는 셀이 없는 값이 표시되지 않습니다.

다음 예제에서는 배열 수식에서 배열 상수를 사용할 수 있는 몇 가지 방법을 보여 줍니다. 일부 예제에서는 행을 열로 변환 하 고 그 반대의 값으로 바꾸는 함수 를 사용 합니다.

배열의 각 항목 곱하기

  1. 새 워크시트를 만든 다음 열 4개와 행 3개로 구성된 빈 셀 블록을 선택합니다.

  2. 다음 수식을 입력 하 고 ctrl + Shift + enter를 누릅니다.

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

배열의 항목 제곱

  1. 열 4개와 행 3개로 구성된 빈 셀 블록을 선택합니다.

  2. 다음 배열 수식을 입력 하 고 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. Excel 워크시트에서 C8:E10 셀을 선택하고 다음 수식을 입력합니다.

    ={10,20,30;40,50,60;70,80,90}

    숫자 배열을 만드는 중이므로 10을 입력하기 전에 {(여는 중괄호)를 입력하고 90을 입력한 후에 }(닫는 중괄호)를 입력해야 합니다.

  2. 배열 수식을 사용 하 여 셀 범위 C8: E10에 입력 하는 다음 숫자 배열이 있는 Ctrl + Shift + enter를 누릅니다. 워크시트에서 C8~E10의 모양은 다음과 같습니다.

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. C1~E3 셀 범위를 선택합니다.

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

    =C8:E10

    셀의 3x3 배열이 C1 ~ E3 셀에 C8 ~ E10에 표시 되는 것과 동일한 값이 표시 됩니다.

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

  1. 셀 C1: C3을 선택한 상태에서 F2 키를 눌러 편집 모드로 전환 합니다. 

  2. 셀 참조를 값으로 변환 하려면 F9 키를 누릅니다. 이 값은 자동으로 배열 상수로 변환됩니다. 수식은 = {10, 20, 30; 40, 50, 60; 70, 80, 90}이어야 합니다.

  3. 배열 상수를 배열 수식으로 입력 하려면 ctrl + Shift + Enter 를 누릅니다.

셀 범위의 문자 수 계산

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

  1. 이 전체 표를 복사해 워크시트의 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)

  2. A8 셀을 선택한 다음 Ctrl + Shift + Enter 를 눌러 A2: A6 셀의 총 문자 수 (66)를 확인 합니다.

  3. A10 셀을 선택 하 고 ctrl + Shift + Enter 를 눌러 A2: A6 셀 중 가장 긴 항목의 내용을 확인 합니다 (A3 셀).

다음 수식은 A8 셀에서 A2 ~ A6 셀의 총 문자 수 (66)를 계산 하는 데 사용 됩니다.

=SUM(LEN(A2:A6))

이 경우 LEN 함수는 범위의 각 셀에 있는 각 텍스트 문자열의 길이를 반환 합니다. SUM 함수는 해당 값을 더하여 결과를 표시 합니다 (66).

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

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

  1. A1: A11 셀에 임의의 숫자를 입력 합니다.

  2. C1 ~ C3 셀을 선택 합니다. 이 셀 집합에 배열 수식에서 반환되는 결과가 유지됩니다.

  3. 다음 수식을 입력 하 고 ctrl + Shift + enter를 누릅니다.

    = 작음 (A1: A11, {1; 2; 3})

이 수식은 array 상수를 사용 하 여 SMALL 함수를 세 번 계산 하 고 A1: A10 셀에 포함 된 배열의 가장 작은 (1), 두 번째 작은 (3) 구성원을 반환 합니다. 추가 값을 찾으려면 다음에 인수를 추가 합니다. 지속적. 이 수식에 SUM 또는 AVERAGE와 같은 추가 함수를 사용할 수도 있습니다. 예를 들면 다음과 같습니다.

= SUM (작음 (A1: A10, {1, 2, 3})

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

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

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

  1. D1부터 D3 까지의 셀을 선택 합니다.

  2. 수식 입력줄에 다음 수식을 입력 한 다음 Ctrl + Shift + enter를 누릅니다.

    = 큼 (A1: A10, ROW (간접 ("1:3")))))))))))))

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

=ROW(1:10)

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

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

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

앞에서 사용한 수식을 살펴보고, 안쪽 괄호에서 시작 하 여 바깥쪽 으로 작업을 수행 합니다. INDIRECT 함수는 텍스트 값 집합을 반환 합니다 (이 경우 값 1 ~ 3에 대 한 자세한 내용은 0을 참조). 그런 다음 ROW 함수는 3 셀 컬럼 형식의 배열을 생성 합니다. LARGE 함수는 A5: A14 셀 범위의 값을 사용 하며, ROW 함수에서 반환 되는 각 참조에 대해 한 번씩 3 번 계산 됩니다. 값 3200, 2700 및 2000이 3 셀 컬럼 형식 배열로 반환 됩니다. 값을 더 찾으려면 INDIRECT 함수에 큰 셀 범위를 추가 합니다.

앞의 예제에서와 마찬가지로이 수식을 SUMAVERAGE와 같은 다른 함수와 함께 사용할 수 있습니다.

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

이전 텍스트 문자열 예제로 돌아가서 빈 셀에 다음 수식을 입력 하 고 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)

Match 형식 인수는 0입니다. Match 유형은 1, 0 또는-1 값으로 구성 될 수 있습니다. 1을 지정 하는 경우 MATCH 는 조회 값 보다 작거나 같은 값의 최대값을 반환 합니다. 0을 지정 하면 MATCH 는 첫 번째 값을 조회 값과 정확 하 게 반환 합니다. -1을 지정 하면 MATCH 는 지정 된 조회 값 보다 크거나 같은 가장 작은 값을 찾습니다. 일치 유형을 생략 하면 Excel에서 1로 간주 합니다.

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

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

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

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

=SUM(IF(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 함수로 전달합니다.

배열 수식에는 andOR 함수를 직접 사용할 수 없으며, 이러한 함수는 TRUE 또는 FALSE를 반환 하 고, 배열 함수에는 결과 배열이 필요 합니다. 이전 수식에 나와 있는 논리를 사용 하 여 문제를 해결할 수 있습니다. 즉, 더하기 또는 곱하기와 같은 수학 연산을 OR 또는 조건과 일치 하는 값에 대해 수행 합니다.

0을 제외한 평균 계산

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

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

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

두 셀 범위 간의 차이 계산

이 배열 수식은 MyData 및 해당 데이터 라는 두 셀 범위에 있는 값을 비교 하 고 둘 사이의 차이 수를 반환 합니다. 두 범위의 내용이 같으면 수식이 0을 반환 합니다. 이 수식을 사용 하려면 셀 범위가 같은 크기와 같은 차원 이어야 합니다 (예: MyData가 3 행의 범위를 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 함수는 Data 이라는 범위에 해당 하는 새 배열을 만듭니다. 해당 셀에 범위의 최대값이 포함 된 경우이 배열은 행 번호를 포함 합니다. 그렇지 않으면 배열에 빈 문자열 ("")이 포함 됩니다. MIN 함수는 두 번째 인수로 새 배열을 사용 하 고 데이터의 최대값에 대 한 행 번호에 해당 하는 가장 작은 값을 반환 합니다. Range 데이터 범위의 값이 동일한 최대값을 포함 하는 경우 수식은 첫 번째 값의 행을 반환 합니다.

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

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

부정

이 문서의 일부는 Wilcox에서 Colin 한 일련의 Excel Power User 열과 Excel 2002 수식 중 14 번째, 15 장, John Walkenbach, 이전의 Excel MVP가 작성 한 책을 기준으로 적용 되었습니다.

추가 지원

Excel 기술 커뮤니티의 전문가에게 질문하고, Answers 커뮤니티에서 지원을 받고, Excel 사용자 의견에서 새로운 기능이나 개선 사항을 제안해 보세요.

참고 항목

동적 배열 및 분산된 배열 동작

동적 배열 수식 vs 레거시 CSE 배열 수식

FILTER 함수

RANDARRAY 함수

SEQUENCE 함수

SINGLE 함수

SORT 함수

SORTBY 함수

UNIQUE 함수

Excel의 #SPILL! 오류

수식 개요

Office 기술 확장
교육 살펴보기
새로운 기능 우선 가져오기
Office Insider 참여

이 정보가 유용한가요?

의견 주셔서 감사합니다!

피드백을 주셔서 감사합니다. Office 지원 에이전트와 연락하는 것이 도움이 될 것 같습니다.

×