엑셀(Excel) offset 함수로 참조 범위 영역을 동적 범위로 수식 작성을 해보자.

안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.

저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.

기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.

---

목차

 

 

참조 범위 영역을 조건에 따라 자동으로 변경하는 offset함수 수식 작성 방법 썸네일 이미지.

엑셀 작업을 하면서 데이터가 입력된 범위 영역을 참조하여 여러 함수들을 사용해서 계산 수식을 작성하여 편하게 이용할 수 있는데 종종 미리 작성해 놓은 수식의 참조 영역의 시작행, 또는 시작하는 열의 데이터를 삭제하거나 일부 삭제, 편집등의 수정을 하게 되면 미리 작성해 놓은 수식의 참조 영역이 사라지게 되어 "#REF!" 오류값을 결과값으로 반환하게 되는 현상이 발생하게 됩니다.

 

또한 미리 작성해 놓은 수식의 참조 영역이 조건에 따라 참조할 영역이 변경되어야 할 경우가 있게 되면 수식이 참조 하는 범위를 수시로 변경해줘야 하는 불편함을 겪게 될 수 있는것이죠.

 

이럴때 offset함수를 사용하여 참조할 영역을 조건에 따라 변경되어 참조 영역을 동적 범위로 활용할 수 있는 수식을 작성 할 수 있게 되어 범위를 참조하여 계산하는 수식보다 조건에 따라 참조할 범위 영역을 자동으로 변경할 수 있으니 훨씬 수월한 엑셀 작업을 할 수 있을것입니다.

 

실제로 앞서 기록한 글에서 offset함수를 참조 영역 범위 함수로 적용한 수식을 함께 확인해 보면 좋겠습니다.

엑셀(Excel) 중복값 제거 1개만 나열 하는 수식을 작성해 보자.

 

---

1. offset함수 기본 수식 작성 방법.

offset함수는 셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환하고 반환되는 참조는 단일 셀 또는 범위일 수 있으며, 반환할 행, 열 수를 지정 할 수 있습니다.

offset함수로 수식을 작성하는 인수 요소는 reference, rows, cols, height, widht로 reference, rows, cols는 필수 요소이고 height, widht는 선택 요소입니다.

 

offset함수로 수식을 작성해 보면 =offset(reference, rows, cols, height, widht)로 작성 할 수 있는데 각각의 인수에 무엇을 작성해야 하는지는 다음과 같이 작성하면 됩니다.

① reference

→ 참조 범위 영역을 지정하기 위한 기준셀 (기준점).

 

② rows

→ 기준셀(reference)로부터 행 방향으로 이동할 수.

 

③ cols

→ 기준셀(reference)로부터 열 방향으로 이동할 수.

 

④ height

→ 기준셀(reference), 기준셀로부터 행 방향으로 이동하고, 기준셀로부터 열 방향으로 이동한 셀을 기준으로 참조할 범위의 높이.

 

⑤ widht

→ 기준셀(reference), 기준셀로부터 행 방향으로 이동하고, 기준셀로부터 열 방향으로 이동한 셀을 기준으로 참조할 범위의 너비.

 

offset함수의 수식을 작성하다 보면 필자 역시 자주 사용하는 함수는 아니다 보니 인수를 작성하는데 헷갈려서 잘 안쓰게 되는 함수이지만, 생각보다 많이 활용할 수 있고 많이 활용할수록 범위를 참조하는 수식을 작성할때 간단하고 편리하게 사용할 수 있는 함수입니다.

 

offset함수의 선택요소 높이(height)와 너비(widht)를 지정하지 않으면 범위 영역을 지정하는 함수로 사용할수는 없고 기준 셀로부터 행 방향과 열 방향으로 얼만큼 이동했을때 해당하는 셀값을 결과값으로 반환하는 수식으로 단일 함수보다는 범위를 참조 영역으로 하는 함수와 함께 쓰일때 활용도가 높은 함수라고 볼 수 있습니다.

 

따라서 기준점으로부터 행 방향, 열 방향으로 얼만큼 이동해야 할지의 조건과 행과 열 방향으로 이동한 셀로부터 높이를 얼마로 지정할지, 너비는 얼마로 지정할지의 조건들을 수식으로 작성할 수 있다면 조건이 변할때마다 범위를 따로 지정해 주지 않아도 조건에 따라 참조 영역이 변경되는 수식을 작성 할 수 있습니다.

 

이미지에서 보는것처럼 1번부터 48번까지의 번호를 A2셀부터 F9셀까지 표 형태로 작성 되어 있는 데이터를 참조해서 offset함수 수식을 작성했을때의 결과값을 확인해 보면 다음과 같습니다.

 

=OFFSET($A$1,3,5) / 결과값 18

=OFFSET($A$1,2,4) / 결과값 11

=OFFSET($A$1,6,4) / 결과값 35

 

선택요소 인수인 높이(height)와 너비(widht)는 작성하지 않고 기준셀과 행방향, 열방향의 인수만 작성하게 되면 단일 셀로 이동하여 이동한 셀의 값을 결과값으로 반환 시키게 됩니다.

간단하게 작성되었지만 =OFFSET($A$1,3,5) 수식을 확인해 보면 A1셀을 기준으로 행방향으로 3행을 이동하면 A4셀에 위치하게 되고 A4셀에서 다시 열방향으로 5열을 이동하게 되면 F4셀의 값 18번을 결과값으로 반환하게 되는것입니다.

 

=OFFSET($A$1,2,4) 수식은 A1셀을 기준으로 2행으로 이동하면 A3셀에 위치하고 A3셀에서 열방향으로 4열을 이동하면 E3셀에 위치하게 되어 E3셀의 값 11번을 결과값으로 반환합니다.

 

=OFFSET($A$1,6,4) 수식은 A1셀을 기준으로 6행만큼 이동하면 A7셀에 위치하게 되고 A7셀에서 열방향으로 4열 이동하면 E7셀에 위치하게 되어 E7셀의 결과값을 반환하게 되어 35번이라는 결과값을 반환하게 되는것입니다.

 

결국 선택 요소 높이(height)와 너비(widht)의 인수를 작성하게 되면 기준셀로부터 행과 열 방향으로 이동한 셀이 기준점이 되고 기준점에서 높이와 너비 만큼의 범위를 지정하여 참조할 범위를 지정할 수 있게 되는것입니다.

 

따라서 행 방향, 열 방향으로의 이동을 row함수, Column함수등을 활용하여 수식을 작성한다면 더욱 활용범위를 넓게 적용할 수 있는 수식을 작성할 수 있습니다.

 

2. 참조 영역 범위 조건 형식으로의 offset함수 활용.

1번에서는 높이(height)와 너비(widht)의 선택 요소 인수를 작성하지 않았는데 offset함수의 활용을 어떻게 적용시켜야 할지 감이 오시나요?

 

높이(height)와 너비(widht)를 인수로 작성하지 않게 되면 사실 offset함수의 활용도는 크지 않을것이고 index,match함수로 수식을 작성하여 결과값을 반환시키는것이 더 좋을 수 있습니다.

 

따라서 offset함수는 참조 영역의 범위를 조건에 맞게 지정하는 수식으로 작성되었을때 그 활용 범위를 참조 영역으로 하는 함수들과 함께 쓰일때 활용도가 높다고 할 수 있겠죠.

 

참조 범위 영역으로 offset함수를 작성하면 =OFFSET($A$1,2,4,5,2)라는 수식을 작성하게 되면 E3셀부터 F7셀까지 범위를 지정한것으로 결과값은 #VALUE!라는 오류값을 반환하게 되는데 작성된 수식이 논리 함수가 아니기 때문에 true 또는 false의 값을 반환하지 않고, 범위를 지정하는 수식이기 때문에 계산을 할 수 없는 #VALUE!값을 반환하게 되는것입니다.

 

3. 동적 범위로 활용할 수 있는 offset함수 수식 작성.

1번과 2번 내용을 확인하면 offset함수의 기본적인 수식 작성 방법을 확인 할 수 있고, 특히 2번의 내용에서 높이(height)와 너비(widht)를 인수로 작성했을때 참조 범위 영역의 수식을 작성 할 수 있다는것을 확인해 봤습니다.

 

이제 offset함수를 이용해서 참조 범위 영역을 만들고 결과값을 반환 할 수 있는 수식을 예제를 통해서 확인해 보겠습니다.

조건에 따라 참조 영역이 동적으로 바뀌는 offset함수의 수식을 작성해 보고 결과값을 확인해 보기 위한 샘플 예제 이미지.

조건에 따라 참조 영역 범위가 동적 범위로 변경되어 결과값을 반환 시킬 수 있도록 샘플 예제를 작성해 보았는데 1행에는 1번째부터 6번째까지 몇번째 열에 작성된 번호인지 카테고리를 작성한것이고, A열에는 회차를 작성한 카테고리로 B2셀부터 G9셀의 범위가 실제 데이터값이 입력된 표 형태의 데이터 입력값들입니다.

 

여기서 J1셀과 J2셀에 각각 조건1과 조건2를 입력하면 B2셀부터 G9셀까지의 범위 중에서 조건에 맞는 참조 영역 범위에서의 결과값을 반환 시키는 수식을 offset함수로 작성해 볼텐데 예제에서 조건1은 2번, 조건2는 1030을 작성 했습니다.

 

조건1의 2는 참조 영역 범위에서 2번이 입력된 개수가 몇번 입력되었는지 조건2의 1030회차 미만의 참조 영역의 범위에서 결과값을 반환하라는 의미로 J3열에 수식을 작성해 보면 다음과 같이 작성 할 수 있습니다.

=COUNTIFS($B$3:$G$9,$J$1) 또는  =COUNTIFS(OFFSET($A$1,MATCH($J$2,$A$2:$A$9,0)+1,1,8,6),$J$1)

위와 같이 두가지의 수식으로 결과값을 반환 시킬 수 있는데 두가지 수식 모두 결과값은 2회의 결과값을 반환 하게 됩니다.

조건을 해석하면 1030회차 미만의 회차의 범위를 참조해서 2번이 몇회가 입력 되었는가?에 대한 결과값을 반환하는것으로 1030회차 미만의 참조 영역 범위는 1029회차부터 1023회차까지의 범위로 B3셀부터 G9셀까지의 범위가 참조 영역 범위가 될것이고, 이 범위 안에서 2번이 몇회나 입력되었는지 확인해 보면 C5셀, E6셀에 각각 입력되어 있는 데이터로 2회가 입력된 것을 확인 할 수 있습니다.

 

따라서 개수 또는 회수의 결과값을 반환 시킬 수 있는 countifs함수를 메인 함수로 작성을 하고 countifs함수 자체의 수식으로 =COUNTIFS($B$3:$G$9,$J$1) 작성하여 참조 영역 범위는 B3:G9, 개수를 파악할 조건 2번이 입력된 J1셀을 참조하여 참조 영역 범위에서 2번이 입력된 개수를 결과값으로 반환하게 되는것입니다.

 

offset함수를 참조 영역 범위 특히 동적 범위로 활용하기 위해 작성 한 수식 =COUNTIFS(OFFSET($A$1,MATCH($J$2,$A$2:$A$9,0)+1,1,8,6),$J$1)을 보게 되면 참조 영역 범위에 입력되어야 할 B3:G9의 참조 영역 범위를 OFFSET($A$1,MATCH($J$2,$A$2:$A$9,0)+1,1,8,6)의 수식이 대신하는것을 볼 수 있는데 작성된 인수를 확인해 보면 $A$1 = A1셀이 기준점으로부터 MATCH($J$2,$A$2:$A$9,0)+1 = J2셀에 입력된 값과 정확하게 일치 하는 값을 A2셀부터 A9셀에서 찾고 해당셀의 행 방향으로 +1셀 이동하고, 1 = 열 방향으로 1열 이동, 8 = 높이는 8행, 6 = 너비는 6열의 참조 영역 범위를 지정하라는 의미가 됩니다.

 

따라서 countifs함수 자체의 기본 수식으로 작성을 하게 되면 조건이 변경될때마다 참조 영역 범위를 직접 수정해 줘야 하는 번거로움이 발생되지만 offset함수로 참조 영역 범위를 작성 하게 되면 J1셀과 J2셀의 조건만을 변경해주게 되면 참조 영역 범위는 조건에 따라 변경하게 되어 결과값을 반환 시킬 수 있게 되는겁니다.

---

오늘 참조 범위를 인수로 하는 함수에서 offset함수가 참조 영역 범위의 인수로 어떻게 활용될 수 있고 특히 동적 범위와 조건에 따라 참조 영역 범위가 변경 될 수 있는 수식 작성 방법으로 참조 범위를 인수로 하는 함수가 굉장히 많고 수식을 계산 하는 과정에서 조건에 따라 결과값을 반환시켜야 하는 수식을 작성할 경우가 굉장히 많다는점에서 offset함수의 활용도가 굉장히 많을것으로 생각해 볼 수 있습니다.

 

샘플 예제에서는 참조 범위를 인수로 하는 countifs함수를 예제로 활용했지만 참조 범위를 인수로 하는 함수는 많이 사용되는 함수 sumifs함수등이 있을 수 있고, offset함수의 인수 또한 row함수, column함수, index, match함수등과 같이 사용하게 되면 복작한 조건에 따른 참조 영역 범위를 동적 범위로 활용 할 수 있는 중요한 내용이 될 수 있을것 같습니다.

 

오늘 내용도 엑셀 작업을 하시는 모든 분들에게 도움이 될 수 있는 활용 자료가 되기를 바라겠습니다.

---

이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"동적 범위를 만드는 offset함수 수식 작성 방법." 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.

긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.

 

※ "동적 범위를 만드는 offset함수 수식 작성 방법" 샘플 예제 다운로드.

글쓰는이작가 샘플 예제 - 동적 범위를 만드는 offset함수 수식 작성 방법..xlsx

0.01MB