[엑셀함수#49] 기준 위치에서 지정 값만큼 이동된 셀 범위 지정(OFFSET)

이번 글에서는 OFFSET 함수를 사용해서 기준 위치에서 지정 값만큼 이동된 셀 범위를 지정하는 방법에 대하여 다루겠습니다.

 

1.OFFSET 함수

• OFFSET 함수는 특정 셀을 기준으로 셀 범위를 동적으로 지정하고자 할 때 사용합니다.
• 즉 고정된 데이터 범위가 아닌 새로운 데이터를 추가하면 데이터 범위가 자동으로 증가되고, 데이터가 삭제되면 데이터 범위가 자동으로 감소되도록 지정할 때 주로 사용하는 함수입니다.

 

2.OFFSET 함수 사용 방법

• 먼저, 아래 사진을 보시면 2021년 상반기 매출 분석을 품목 별로 원가와 매출액을 기록한 엑셀 사진을 보실 수 있습니다.

엑셀 1

• 여기서, 2버 박스에는 총 원가와 총매출액이 들어갈 것입니다.

• 하지만, 범위 지정을 그냥 SUM이 아닌 OFFSET 함수를 사용하여할 것입니다. 그렇게 되면 새로운 데이터가 들어오고 나갈 때에 범위를 새로 지정해야 할 불편함이 줄어들기 때문입니다.

 

엑셀 2

• F4 셀에 셀포인터를 위치하여 총원가를 먼저 작성하겠습니다.

• =SUM(OFFSET(B3,1,1,COUNT(C:C),1)) 을 입력한 뒤에 ENTER를 눌러줍니다.
• 이 수식은 데이터 목록의 첫번째 기준인 B3 셀을 기준으로 1행 1열을 이동해야 데이터가 있음을 나타내는 수식입니다. 그리고, SUM은 OFFSET으로 지정한 값들을 모두 더해주는 것이죠.

엑셀 3

• 총 매출액 또한 동일합니다.

• =SUM(OFFSET(C3,1,1,COUNT(D:D),1)) 을 입력한 뒤에 ENTER를 눌러줍니다.

엑셀 4

• 여기까지 따라하셨다면 위에 사진의 2번 박스에 보이는 것과 같이 총 원가와 총매출액이 출력되어질 것입니다.

 

3. OFFSET 함수 상세 설명

• OFFSET 함수는 지정된 셀을 기준으로 지정된 행, 열을 구성되는 셀 범위를 반환하는 함수입니다.

(1) 형식

• =OFFSET(기준 셀, 행, 열, 행 높이, 열 높이)

(2) 인수

• 기준 셀 : 기준 셀을 지정합니다
• 행 : 기준 셀을 기준으로 움직일 행의 수를 의미하며 기준 셀이 대상이 되면 0을 지정합니다
• 열 : 기준 셀을 기준으로 움직일 열의 수를 의미하며, 기준 셀이 대상이 되면 0을 지정합니다
• 행 높이 : 참조할 행 높이를 지정합니다. 현재 기준 셀을 가리키고자 하려면 1을 입력합니다
• 열 높이 : 참조할 열 높이를 지정합니다. 현재 기준 셀을 가리키고자 하려면 1을 입력합니다.

 

여기까지 OFFSET 함수에 대하여 다루었습니다.