Excel SUM 및 INDIRECT 동적 범위 수식

Microsoft Excel에는 멋진 트릭이 있으며 SUM 및 INDIRECT 동적 범위 수식을 사용하면 보유한 데이터를 쉽게 조작 할 수 있습니다.

SUM - 간접 수식 개요

Excel 수식에서 INDIRECT 함수 를 사용하면 수식 자체에서 편집 할 필요없이 수식에 사용 된 셀 참조 범위를 쉽게 변경할 수 있습니다.

INDIRECT는 OFFSET 및 SUM 함수와 같은 인수로 셀 참조를 허용하는 여러 함수와 함께 사용할 수 있습니다.

후자의 경우 SUM 함수의 인수 로 INDIRECT를 사용하면 SUM 함수가 더하는 셀 참조 의 동적 범위를 만들 수 있습니다.

INDIRECT는 중간 위치를 통해 간접적으로 셀의 데이터를 참조하여이를 수행합니다.

예 : 동적 범위 값을 합산하는 데 사용되는 SUM - INDIRECT 수식

이 예제는 위의 그림에 표시된 데이터를 기반으로합니다.

아래의 자습서 단계를 사용하여 만든 SUM - INDIRECT 수식 은 다음과 같습니다.

= SUM (간접 ( "D"& E1 & ": D"& E2))

이 수식에서 중첩 된 INDIRECT 함수의 인수에는 셀 E1 및 E2에 대한 참조가 포함됩니다. 해당 셀의 숫자 인 1과 4는 나머지 INDIRECT의 인수와 결합되어 셀 참조 D1과 D4를 형성합니다.

결과적으로 SUM 함수에 의해 합계 된 숫자의 범위 는 셀 D1에서 D4 까지 의 범위 에 포함 된 데이터이며 50입니다.

셀 E1과 E2에있는 숫자를 변경함으로써; 그러나 합계 범위를 쉽게 변경할 수 있습니다.

이 예제에서는 먼저 위의 수식을 사용하여 셀 D1 : D4의 데이터를 합계 한 다음 셀 F1의 수식을 편집하지 않고 합계 범위를 D3 : D6으로 변경합니다.

01 / 03

수식 입력 - 옵션

수식 입력 옵션은 다음과 같습니다.

• 위의 수식을 셀 F1에 직접 입력하고 키보드의 Enter 키를 누릅니다.
• SUM 함수의 대화 상자를 사용하여 INDIRECT 함수를 인수로 입력

Excel의 대부분의 함수에는 대화 상자가있어 구문 에 대해 걱정할 필요없이 각 함수의 인수를 별도의 줄에 입력 할 수 있습니다.

이 경우 SUM 함수의 대화 상자를 사용하여 수식을 어느 정도 단순화 할 수 있습니다. INDIRECT 함수가 SUM 내에 중첩되어 있으므로 INDIRECT 함수와 해당 인수는 수동으로 입력해야합니다.

아래 단계에서는 SUM 대화 상자를 사용하여 수식을 입력합니다.

자습서 데이터 입력

셀 데이터 D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. 셀 D1 ~ E2에 다음 데이터 를 입력하십시오.

SUM - INDIRECT 수식 시작 - SUM 함수 대화 상자 열기

1. F1을 클릭하십시오 -이 예제의 결과가 표시됩니다
2. 리본 메뉴의 수식 탭을 클릭하십시오.
3. 리본에서 Math & Trig 를 선택하여 함수 드롭 다운 목록을 엽니 다.
4. 함수 대화 상자를 열려면 목록에서 SUM 을 클릭하십시오.

02 of 03

간접 기능 입력 - 클릭하여 큰 이미지보기

INDIRECT 수식을 SUM 함수의 인수로 입력해야합니다.

중첩 된 함수의 경우 Excel에서는 두 번째 함수의 대화 상자를 열어 인수를 입력 할 수 없습니다.

따라서 INDIRECT 함수는 SUM 함수 대화 상자의 Number1 행에 수동으로 입력해야합니다.

1. 대화 상자에서 Number1 행을 클릭하십시오.
2. 다음 INDIRECT 함수를 입력하십시오. INDIRECT ( "D"& E1 & ": D"& E2)
3. 확인을 클릭하여 기능을 완료하고 대화 상자를 닫습니다.
4. 숫자 D1은 셀 D1에서 D4에있는 데이터의 합계이기 때문에 셀 F1에 숫자 50 이 표시되어야합니다.
5. 셀 F1을 클릭하면 전체 수식 = SUM (INDIRECT ( "D"& E1 & ": D"& E2)) 이 워크 시트 위의 수식 입력 줄에 나타납니다

간접 기능 중단

INDIRECT를 사용하여 열 D에 동적 범위를 만들려면 INDIRECT 함수의 인수에서 문자 D와 셀 E1 및 E2에 포함 된 숫자를 결합해야합니다.

이것은 다음에 의해 수행됩니다.

• 앰퍼샌드 ( & )는 텍스트 데이터 (이 경우 문자 D)를 셀 참조 (E1 및 E2)와 함께 연결 하거나 결합하는 데 사용됩니다.
• 또한 셀 참조와 연결된 텍스트 데이터는 큰 따옴표 ( "" )로 묶어야합니다.
• 마지막으로 범위의 끝점은 콜론 (:)으로 구분됩니다 .

따라서 범위의 시작점은 "D"& E1 문자로 정의됩니다.

두 번째 문자 세트 : ": D"& E2 는 콜론과 끝점을 결합합니다. 이것은 콜론이 텍스트 문자이므로 따옴표 안에 포함되어야하기 때문에 수행됩니다.

중간의 세 번째 앰퍼샌드는 두 부분을 하나의 인수 로 연결하는 데 사용됩니다.

"D"& E1 & ": D"& E2

03 of 03

SUM 함수의 범위를 동적으로 변경하기

이 수식의 요점은 함수의 인수를 편집 할 필요없이 SUM 함수로 합계 된 범위를 쉽게 변경하는 것입니다.

수식에 INDIRECT 함수를 포함하면 E1 및 E2 셀의 수를 변경하면 SUM 함수에서 읽는 셀 범위가 변경됩니다.

위의 이미지에서 볼 수 있듯이 새로운 셀 범위를 합산함에 따라 셀 F1에있는 수식의 대답이 변경됩니다.

1. E1 셀을 클릭하십시오.
2. 숫자 3을 입력하십시오.
3. 키보드의 Enter 키를 누릅니다.
4. E2 셀을 클릭하십시오.
5. 숫자 6을 입력하십시오.
6. 키보드의 Enter 키를 누릅니다.
7. 셀 F1의 대답은 90으로 변경되어야합니다. 이는 셀 D3에서 D6에 포함 된 숫자의 합계입니다
8. 셀 B1과 B2의 내용을 1과 6 사이의 숫자로 변경하여 공식을 더 테스트하십시오.

간접 및 #REF! 오류 값

#REF! INDIRECT 함수의 인수 인 경우 오류 값이 셀 F1에 나타납니다.

• 유효한 셀 참조가 아닙니다.
• 다른 통합 문서에 대한 외부 참조가 들어 있으며 통합 문서가 열려 있지 않습니다.
• 워크 시트의 한도 (1,048,576 행 또는 XFD 행) 외부의 셀 범위를 나타냅니다.