엑셀 Offset 함수 사용법 (특정 셀 범위 값 찾기)

엑셀에서 많은 양의 데이터가 있는 경우 원하는 위치의 값 또는 일정 구간씩 떨어진 위치에 있는 값들을 찾는 경우가 필요합니다. 이럴 때 유용한 함수가 바로 Offset 함수입니다. 

 

 

1. Offset 함수 구문 소개

어떤 함수라도 함수를 사용하기 전에 해당 함수에 대해 정확히 이해를 해야지만 다른 함수와 조합하여 수식을 구성 할 때 올바르게 구성을 할 수 있습니다. Offset 함수의 구문의 구성에 대해 먼저 이해를 하고 넘어가는 것이 중요한 이유입니다.

 

OFFSET(reference, rows, cols, [height], [width])

 

Offset 함수는 총 5개의 인수로 구성되어 있습니다. 앞 쪽의 3개가 기본 인수이며, 뒤쪽의 대괄호로 묶여진 인수는 생략이 가능합니다. 어떨 때에 생략이 가능하고, 왜 이런 인수가 있는지도 설명을 해 드리겠습니다.

 

• reference : Offset 함수 사용시에 기준이 되는 위치의 셀을 선택하는 인수입니다.
• rows : 기준 위치의 셀에서 찾는 값이 있는 행 까지의 행의 수입니다.
• cols : 기준 위치의 셀에서 찾는 값이 있는 열 까지의 열의 수입니다.
• [height] : 찾아진 위치를 다시 기준으로 아래의 열까지 추가로 범위를 지정하는 인수입니다.
• [width] : 찾아진 위치를 다시 기준으로 옆의 행까지 추가로 범위를 지정하는 인수입니다.

매번 드는 생각이지만 함수의 기능을 텍스트로 설명하는 것은 설명하는 사람도 힘들고 이해를 하는 사람은 더더욱 이해가 어렵다는 생각이 듭니다. 예시를 통해서 Offset 함수의 기능을 익히는 것이 중요한 이유입니다.

 

2. Offset 함수 사용법 1

Offset 함수의 기본적인 사용법부터 간단한 예제를 통해 익혀보겠습니다. 다음과 같이 3곳의 가게에서 5종류의 과일의 가격이 있다고 가정합니다. Offset 함수를 이용하여 원하는 가게의 과일 값을 찾는 과정을 설명해 드립니다.

 

1. 먼저 Offset 함수를 입력하고 기준이 되는 셀을 선택합니다. 

 

2. 두 번째는 기준위치에서 찾고자 하는 값이 있는 행까지의 거리를 입력합니다. 수식안에서 텍스트로 입력할 수도 있고, 값이 있는 셀을 선택할 수도 있습니다.

 

3. 세 번재는 기준위치에서 찾을 값이 있는 열까지의 수를 입력합니다. 마찬가지로 텍스트 또는 셀을 선택할 수 있습니다.

 

4. 이제 결과를 확인해 보겠습니다. 아래와 같이 가게1의 귤 가격은 60이라는 값이 찾아진 것을 볼 수 있습니다. 옆의 다른 두 예제도 원하는 결과가 나왔습니다.

 

3. Offset 함수 사용법 2

위에서 함수 구문을 설명할 때 대괄호로 묶여진 두 개의 인수는 생략이 가능하다고 했습니다. 하지만 이 인수를 사용이 필요한 경우는 어떤 경우인지 설명을 해 드리겠습니다. 이번에는 가게1의 전체 과일 가격의 합계를 예로 들어봅니다.

 

우리가 가장 잘 아는 합계를 구하는 함수는 sum 함수 입니다. 이 함수를 이용하여 합계를 구하고자 하는 모든 셀들을 선택하면 간단하게 합계를 구할 수가 있습니다.

 

하지만 만약 수식을 만들고 난 다음에 추가로 과일 항목이 생겨서 합계를 구해야 할 때는 수식을 변경해야 합니다. 값들이 계속 늘어나는 상황이 되면 수식을 변경하면서 실수로 빠뜨릴 수도 있는 경우도 발생합니다. 이럴 때는 Offset 함수를 이용하여 수식을 만들어 두면 유용합니다.

 

아래의 수식처럼 sum 함수 안의 인수로 셀을 선택하는 것이 아니라 offset 함수와 counta 함수를 조합하여 수식을 만들면 이후에 어떤 값이 추가되더라도 수식의 변경없이 합계를 구할 수 있습니다.

 

먼저 offset 함수로 구하고자 하는 셀의 기준 열을 선택하고, 뒤의 행과 열의 두 인수는 모두 0을 입력합니다. 그리고 대괄호로 들어가는 참조인수에 행의 값을 더할 경우에는 1을 입력하고, 열의 범위를 지정하는 인수는 counta 함수를 사용하여 셀의 값이 있는 갯수를 지정해 주면 간단하게 수식을 구성할 수 있습니다.