블로그

Excel 동적 범위 만들기: OFFSET과 표를 쓰는 경우

행이 늘어날 때 합계와 드롭다운 목록을 함께 갱신한다. OFFSET과 COUNTA로 범위를 구하는 법, 빈 ID의 영향, Excel 표 설정을 살펴본다.

표에 행을 추가하면 강조된 참조 범위가 아래로 늘어나는 그림.

Excel 상품 목록에 항목을 추가했는데 합계와 드롭다운 목록은 그대로일 수 있다. 참조가 A2:A10이면 11행은 범위 밖이다. 동적 범위로 Excel이 목록의 끝을 판단하게 하면 목록을 수정할 때 수식까지 고칠 필요가 없다.

OFFSET으로 높이와 너비 구하기

데이터가 연속되면 머리글과 상품 ID의 개수를 세어 OFFSET에 높이와 너비로 전달한다. Sheet1에 아래 표를 만들고 합계 수식은 다른 시트에 입력한다. 뒤에서 쓰는 구조적 참조가 동작하도록 일본어 머리글은 그대로 입력한다.

셀 A열 B열 C열
1행 商品ID (상품 ID) 商品名 (상품명) 数量 (수량)
2행 A01 펜 4
3행 A02 노트 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

A1에서 이동 없이 시작하고, A열의 데이터 수를 높이로, 1행의 머리글 수를 너비로 사용한다. 예제의 범위는 3행 3열인 A1:C3이고 합계는 10이다. 4행에 A03, 자, 2를 추가하면 A1:C4가 되고 합계는 12다. 상품 ID는 텍스트이며, 개수를 세는 범위에 다른 데이터가 없다고 가정한다. Microsoft OFFSET 함수 설명

이제 A3의 ID만 지우면 A열에서 머리글 A1과 ID가 있는 A2, A4 세 셀이 집계된다. 따라서 OFFSET은 왼쪽 위부터 세 행인 A1:C3을 반환한다. 3행의 수량 6은 포함되지만 4행의 수량 2는 빠져서 합계는 다시 10이다.

A3이 비어 있고 OFFSET 범위가 A1:C3이라 합계는 10이다. 3행의 6이 포함되고 4행의 2는 제외된다.
녹색 테두리는 Windows용 Excel에서 OFFSET이 반환한 A1:C3 범위다.

열도 마찬가지로 중간 머리글이 비면 뒤쪽 열이 범위를 벗어난다. COUNTA는 빈 문자열("")을 반환하는 수식이 든 셀도 센다. 기존 통합 문서에서는 머리글과 ID를 연속으로 입력하고 범위 상한과 빈칸을 확인한다. 수량이 비어도 ID가 있으면 높이는 바뀌지 않는다. Microsoft COUNTA 함수 설명

머리글이 2행에서 시작할 때

1행을 보고서 제목으로 쓴다면 표를 A2에서 시작하고, 높이와 너비도 2행부터 세어 제목을 뺀다. 예제 표를 한 행 아래로 내리면 머리글과 상품 두 건의 범위는 A2:C4가 되고 합계는 10이다.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

행 추가 시 자동으로 확장되는 표 만들기

새 목록은 머리글을 포함해 범위를 선택한 뒤 삽입 → 표로 변환한다. 표 이름을 Items로 지정하면 다음 수식으로 표 바깥에서 수량 열을 합산할 수 있다. 표에 행을 추가하면 집계 범위도 늘어난다. Microsoft 구조적 참조 가이드

=SUM(Items[数量])

상품 ID가 있는 행만 합산하기

A1:C4를 Items 표로 만들면 A3이 비어 있어도 수량 4 + 6 + 2를 합쳐 12가 된다. ID가 있는 행만 합산하려면 SUMIF에서 ID 열이 비어 있지 않다는 조건을 지정한다. 이 예에서는 2행과 4행의 4 + 2를 더해 6이 나온다. Microsoft SUMIF 함수 설명

=SUMIF(Items[商品ID],"<>",Items[数量])

드롭다운 목록을 표와 연동하기

다른 시트에서 상품명 드롭다운을 만들려면 데스크톱 Excel의 수식 → 이름 관리자에서 통합 문서 범위 이름 ItemChoices를 만들고 참조 대상을 =Items[商品名]으로 지정한다. 입력 셀의 데이터 유효성 검사를 목록으로 지정하고 원본을 =ItemChoices로 설정한다. 머리글은 제외되며 표 끝에 행을 추가하면 선택지가 늘어난다. 빈 선택지를 방지하려면 행을 추가할 때 상품명을 채운다. Microsoft 드롭다운 목록 만들기