SUM 및 OFFSET을 사용하여 동적 데이터 범위에 대한 합계를 찾습니다.
Excel 워크 시트 에 셀 범위 변경을 기반으로 한 계산이 포함 된 경우 SUM OFFSET 수식에서 SUM 및 OFFSET 함수를 함께 사용하면 계산을 최신 상태로 유지하는 작업이 간단 해집니다.
SUM 및 OFFSET 함수를 사용하여 동적 범위 만들기
해당 월의 총 판매와 같이 지속적으로 변경되는 기간 동안 계산을 사용하는 경우 OFFSET 기능을 사용하면 매일 판매량이 추가 될 때마다 계속 변화하는 동적 범위를 설정할 수 있습니다.
그 자체로, SUM 함수 는 대개 합계 범위에 삽입되는 새로운 데이터 셀을 수용 할 수 있습니다.
함수가 현재 위치한 셀에 데이터가 삽입 될 때 한 가지 예외가 발생합니다.
이 기사와 함께 제공되는 예제 이미지에서 매일 새로운 판매 수치가 목록 하단에 추가되어 새 데이터가 추가 될 때마다 합계가 한 셀씩 계속 이동합니다.
SUM 함수가 자체적으로 데이터를 합산하는 데 사용되면 새 데이터가 추가 될 때마다 함수의 인수 로 사용되는 셀 범위를 수정해야합니다.
그러나 SUM 및 OFFSET 함수를 함께 사용하면 합계 범위가 동적이됩니다. 즉, 새로운 데이터 셀을 수용하도록 변경됩니다. 새로운 셀이 추가 될 때마다 범위가 계속 조정되기 때문에 새로운 데이터 셀을 추가해도 문제가 발생하지 않습니다.
구문과 인수
이 자습서와 함께이 기사와 함께 제공되는 이미지를 참조하십시오.
이 수식에서 SUM 함수는 인수로 제공된 데이터 범위를 합계하는 데 사용됩니다. 이 범위의 시작점은 정적이며 수식에서 계산할 첫 번째 숫자에 대한 셀 참조 로 식별됩니다.
OFFSET 함수는 SUM 함수 내에 중첩되어 있으며 수식에서 계산 된 데이터 범위에 대한 동적 끝점을 만드는 데 사용됩니다. 이는 범위의 끝점을 수식의 위치 위에 하나의 셀로 설정하여 수행됩니다.
수식의 구문 :
= SUM (범위 시작 : OFFSET (참조, 행, 열))Range Start - (필수) SUM 함수에 의해 합계 될 셀 범위의 시작점입니다. 예제 이미지에서 이것은 B2 셀입니다.
참조 - 여러 행과 열에 위치한 범위 끝점을 계산하는 데 사용되는 셀 참조가 필요합니다 (필수). 예제 이미지에서 참조 인수는 수식 자체에 대한 셀 참조입니다. 왜냐하면 항상 수식 위에 셀 하나가 끝나기를 원하기 때문입니다.
행 수 - (필수) 오프셋 계산에 사용 된 참조 인수 위 또는 아래 행 수입니다. 이 값은 양수, 음수 또는 0으로 설정할 수 있습니다.
오프셋의 위치가 Reference 인수보다 큰 경우이 값은 음수입니다. 아래에있는 경우 Rows 인수는 양수입니다. 오프셋이 같은 행에 있으면이 인수는 0입니다. 이 예제에서 오프셋은 Reference 인수보다 한 행 먼저 시작 하므로이 인수의 값은 음수 1 (-1)입니다.
Cols - (필수) 오프셋 계산에 사용 된 Reference 인수의 왼쪽 또는 오른쪽 열 수입니다. 이 값은 양수, 음수 또는 0으로 설정할 수 있습니다.
오프셋 (offset)의 위치가 Reference 인수의 왼쪽에있는 경우,이 값은 음수입니다. 오른쪽의 경우, Cols 인수는 양수입니다. 이 예에서 합계중인 데이터는 수식과 동일한 열에 있으므로이 인수의 값은 0입니다.
SUM OFFSET 공식을 사용하여 판매 데이터 합계
이 예에서는 SUM OFFSET 수식을 사용하여 워크 시트의 B 열에 나열된 일일 판매 수치의 합계를 반환합니다.
처음에는 셀 B6에 수식을 입력하고 4 일 동안 판매 데이터를 합산했습니다.
다음 단계는 SUM OFFSET 수식을 행 아래로 이동하여 다섯 번째 날의 판매 합계를위한 공간을 확보하는 것입니다.
이것은 수식을 7 행으로 이동 시키는 새로운 행 6을 삽입하여 수행됩니다.
이동 결과로 Excel은 참조 인수를 셀 B7에 자동으로 업데이트하고 셀 B6 을 수식에서 합한 범위에 추가합니다.
SUM OFFSET 수식 입력하기
- 수식의 결과가 처음 표시 될 위치 인 B6 셀을 클릭하십시오.
- 리본 메뉴의 수식 탭을 클릭하십시오.
- 리본에서 Math & Trig 를 선택하여 함수 드롭 다운 목록을 엽니 다.
- 함수 대화 상자 를 불러 오려면 목록에서 SUM 을 클릭하십시오.
- 대화 상자에서 Number1 행을 클릭하십시오.
- 이 셀 참조를 대화 상자에 입력하려면 B2 셀을 클릭하십시오. 이 위치는 수식의 정적 끝점입니다.
- 대화 상자에서 Number2 행을 클릭하십시오.
- OFFSET (B6, -1,0) 함수를 입력하여 수식의 동적 끝점을 만듭니다.
- 확인 을 클릭하여 기능을 완료하고 대화 상자를 닫습니다.
총 $ 5679.15 셀 B7에 나타납니다.
B3 셀을 클릭하면 전체 함수 = SUM (B2 : OFFSET (B6, -1,0)) 이 워크 시트 위의 수식 입력 줄에 나타납니다.
다음날 판매 데이터 추가
다음날 판매 데이터를 추가하려면 다음을 수행하십시오.
- 행 6의 행 머리글 을 마우스 오른쪽 버튼으로 클릭하여 컨텍스트 메뉴를 엽니 다.
- 메뉴에서 삽입 을 클릭하여 워크 시트에 새 행을 삽입 하십시오 .
- 결과적으로 SUM OFFSET 수식이 B7 셀로 이동하고 6 행이 비어있게됩니다.
- A6 셀을 클릭하십시오.
- 다섯 번째 날의 판매 합계가 입력되었음을 나타 내기 위해 숫자 5 를 입력하십시오.
- B6 셀을 클릭하십시오.
- 숫자 $ 1458.25 를 입력 하고 키보드의 Enter 키를 누릅니다.
셀 B7이 $ 7137.40 의 새 총계로 업데이트됩니다 .
B7 셀을 클릭하면 업데이트 된 수식 = SUM (B2 : OFFSET (B7, -1,0)) 이 수식 입력 줄에 나타납니다.
참고 : OFFSET 함수에는 높이 와 너비 라는 두 개의 선택적 인수가 있습니다.이 인수는이 예제에서 생략되었습니다.
이 인수는 OFFSET 함수에 출력의 모양을 알려주는 데 사용할 수 있습니다.
이러한 인수를 생략함으로써 함수는 기본적으로 참조 인수의 높이와 너비를 대신 사용합니다.이 예제에서는 높이가 1 행, 너비가 1 열입니다.