엑셀 OFFSET 함수 사용법에 관해 알아보는 영상입니다. SUM(OFFSET 함수) 사용법 및 OFFSET MATCH 함수, 엑셀 OFFSET ROW 함수 사용법까지 모두 알아보겠습니다. 지금부터 엑셀 OFFSET 사용법에 관해 총정리합니다!
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 OFFSET 함수 기본 사용법
- 엑셀 OFFSET 함수: 내가 지정한 행, 열 숫자로 이동한 값을 반환하는 함수
- = OFFSET ( 시작 위치, 행 이동, 열 이동, [행 선택 셀 개수], [열 선택 셀 개수] )
엑셀 OFFSET 함수는 내가 지정한 행과 열의 숫자만큼 이동을 한 후의 값을 반환 해 주는 함수 입니다. OFFSET 함수는 다양한 곳에서 유용하게 활용할 수 있는 함수이지만 난이도가 조금 높은 편에 속합니다.
엑셀 OFFSET 함수 사용법을 바로 알아보겠습니다.
1.엑셀 OFFSET 함수 기본
아래 사진은 학생별, 과목별 점수를 나타낸 자료입니다. 여기서 홍길동의 수학점수를 찾아온다고 해보겠습니다.
그러기 위해서 아래와 같이 함수식을 입력합니다.
= OFFSET(A1,2,2)
= OFFSET ( 시작 위치, 행 이동, 열 이동, [행 선택 셀 개수], [열 선택 셀 개수] )
OFFSET 함수 내 첫 번째 인수로 시작 위치를 입력해야 하는데요. 위 예시의 경우 범위의 제일 왼쪽 상단에 위치한 A1 셀이 시작위치가 됩니다. 따라서 A1 이라고 입력을 합니다.
콤마를 입력하고 ‘행 이동’ 을 입력 하는데요. A1 셀에서 ‘홍길동’ 이 있는 셀로 가려면 아래로(행으로) 2만큼 이동해야 합니다. 따라서 ‘행 이동’에는 2를 입력합니다.
콤마를 입력하고 ‘열 이동’을 입력 하는데요. ‘홍길동’ 이 있는 셀에서 ‘수학’이 있는 열로 가려면 오른쪽으로 2만큼 이동을 해야 합니다. 따라서 ‘열 이동’ 에도 2를 입력합니다.
그 후 괄호를 닫고 엔터키를 누르면 홍길동의 수학 점수인 80점이 바로 반환이 됩니다.
이번에는 위 예시에서 ‘박길동’의 ‘영어’ 점수를 찾아와 본다고 해보겠습니다. 그러기 위해서 아래와 같이 함수식을 입력합니다.
=OFFSET (A1, 4, 3)
가장 먼저 시작 위치인 A1 셀을 선택합니다. 그 후 ‘박길동’ 셀로 이동하기 위해서 ‘행 이동’에 4를 입력합니다.
그리고 ‘영어’가 있는 열로 이동하기 위해 ‘열 이동’에 3을 입력합니다. 엔터키를 누르면 ‘박길동’의 ‘영어’ 점수인 50점이 반환됩니다.
정리해보자면 아래로 이동하는 경우 행이 이동하는 것이고, 오른쪽로 이동하는 경우 열이 이동하는 것인데요.
반대로 위 또는 왼쪽으로 이동하는 경우라면 마이너스를 입력해줘야 합니다.
2.엑셀 OFFSET 함수 중첩
이번에는 아까 위에서 봤던 사진에서 ‘홍길동’의 ‘점수 합계’를 구한다고 해보겠습니다. 합계를 구해야 하므로 SUM 함수, OFFSET 함수를 중첩해서 사용합니다.
=SUM(OFFSET(A1,2,1,1,4))
=OFFSET ( 시작 위치, 행 이동, 열 이동, [행 선택 셀 개수], [열 선택 셀 개수] )
시작위치인 A1 셀에서 아래로 2 이동하고, 오른쪽으로 1 이동합니다. 그럼 홍길동의 국어 점수 칸에 위치하게 됩니다.
=SUM(OFFSET(A1,2,1,1,4))
=OFFSET ( 시작 위치, 행 이동, 열 이동, [행 선택 셀 개수], [열 선택 셀 개수] )
여기서 홍길동의 국어, 수학, 영어, 사회 점수 합계를 모두 구해야합니다.
OFFSET 함수의 4번째, 5번째 인수를 입력하여 아래 사진처럼 범위가 지정되도록 해야 합니다.
‘행 선택 셀 개수’를 1로 입력하면 행 범위가 1개로 지정이 됩니다. 그리고 ‘열 선택 셀 개수’를 4로 입력하면 열 범위가 4개로 지정이 됩니다. 따라서 위 사진과 같은 범위가 지정이 되게 됩니다.
그리고 엔터키를 누르면 위 사진에 해당되는 범위의 합계 값이 320이라고 나오게 됩니다.
이번에는 맨 위 사진의 예시에서 ‘박길동’의 평균점수를 찾아와 본다고 해볼게요. 아래와 같이 함수식을 입력합니다.
=AVERAGE(OFFSET(A1,4,1,1,4))
AVERAGE 함수는 입력한 범위의 평균을 구하는 함수입니다.
우선 시작위치를 A1로 입력합니다. 우리는 박길동의 평균점수를 찾아야 합니다. 따라서 박길동의 점수 셀(B5)로 이동해야 합니다.
따라서 A1 셀에서 아래로 4만큼 이동하고, 오른쪽으로 1만큼 이동합니다.
=AVERAGE(OFFSET(A1,4,1,1,4))
그 후 박길동의 전체 점수 범위를 지정하기 위해서 ‘행 선택 셀 개수’를 1로 입력하고, ‘열 선택 셀 개수’를 4로 입력합니다.
그 후 엔터키를 누르면 박길동의 평균 점수 값이 바로 구해집니다.
자동으로 입력되도록 지정하기
이번에는 함수식을 딱 1번만 입력해서 나머지 셀에 자동으로 값이 나오게 해보겠습니다.
즉, 첫 번째 셀에만 함수식을 입력하고 아래 셀에는 자동채우기를 하는 것입니다.
아래 사진에서 ‘홍길동’의 ‘국어’ 점수만 구해보겠습니다. 그리고 아래 셀에 자동채우기를 해보겠습니다. 아래와 같이 함수식을 입력합니다.
=OFFSET($A$1,MATCH(G2,$A$2:$A$10,0),MATCH(H2,$B$1:$E$1,0))
함수식 풀이를 해보겠습니다.
=OFFSET($A$1,MATCH(G2,$A$2:$A$10,0),MATCH(H2,$B$1:$E$1,0))
=OFFSET ( 시작 위치, 행 이동, 열 이동, [행 선택 셀 개수], [열 선택 셀 개수] )
OFFSET 함수 내 첫 번째 인수로 시작위치를 입력합니다. 위 예시의 시작위치는 데이터의 제일 왼쪽 상단에 위치한 A1 셀입니다.
아래 셀에 자동채우기를 할 때 시작위치가 딱 고정이 되어 있어야 합니다. 따라서 F4 키를 눌러서 절대참조를 해주세요.
=OFFSET($A$1,MATCH(G2,$A$2:$A$10,0),MATCH(H2,$B$1:$E$1,0))
=OFFSET ( 시작 위치, 행 이동, 열 이동, [행 선택 셀 개수], [열 선택 셀 개수] )
이번에는 OFFSET 함수 내 2번째 인수로 ‘행 이동’ 을 입력 해야 하는데요. 위 예시의 경우 아래 셀에 이동을 할 때 행 번호가 바뀌어야 합니다. 따라서 단순히 행 이동 숫자를 입력하면 안 됩니다.
숫자 대신 행 번호를 반환해주는 함수인 MATCH 함수를 사용해 볼 수 있습니다.
=MATCH (찾을 값, 범위, 정확도)
MATCH 함수를 입력한 후 첫 번째 인수로 찾을 값을 입력합니다. 행에서 ‘홍길동’ 을 찾아야 합니다. 따라서 ‘홍길동’ 셀(G2)을 클릭합니다.
콤마를 입력하고 ‘홍길동’ 셀이 포함되어 있는 전체 범위인 A2:A10을 선택합니다. 그리고 F4 키를 눌러서 셀을 고정시킵니다.
우리는 정확한 값을 찾아야 하므로 0을 입력하고 괄호를 닫습니다.
=OFFSET($A$1,MATCH(G2,$A$2:$A$10,0),MATCH(H2,$B$1:$E$1,0))
= OFFSET ( 시작 위치, 행 이동, 열 이동, [행 선택 셀 개수], [열 선택 셀 개수] )
이번에도 MATCH 함수를 이용해서 ‘열 이동’ 인수를 입력해줘야 하는데요. 열에 해당되는 부분은 ‘국어’ 가 포함되어 있는 ‘과목’ 부분입니다.
MATCH 함수 첫 번째 인수로 찾을 값인 ‘국어’ 셀(H2)를 선택합니다. 콤마를 입력하고 ‘국어’ 셀이 포함되어 있는 부분인 과목 범위(B1:E1)를 선택합니다. 그리고 F4 키를 눌러서 절대참조를 합니다.
그 후 정확한 값을 찾기 위해 0을 입력하고 엔터키를 누릅니다. 그럼 ‘홍길동’의 ‘국어’ 점수가 바로 찾아와집니다.
그리고 아래 셀에도 자동채우기를 하면 바로 자동완성이 됩니다.
최근 값 반환하기
이번에는 OFFSET 함수를 이용해서 최근 값을 반환하는 방법을 알아보겠습니다.
아래 사진은 날짜 별 과일명 및 단가 데이터를 나타낸 것입니다. 위쪽에 있는 셀일수록 오래된 날짜이고, 아래쪽에 있는 셀일수록 최근 날짜입니다.
여기서 최근단가, 전 단가, 전전 단가를 구해보겠습니다. 우선 최근단가를 구하기 위해 아래와 같이 함수식을 입력합니다.
=OFFSET($C$1,COUNTA(C:C)-ROW(A1),0)
함수식이 위와 같이 입력된 이유를 알아보겠습니다.
=OFFSET($C$1,COUNTA(C:C)-ROW(A1),0)
우선 OFFSET 함수 내 첫 번째 인수로 시작위치를 입력합니다. 전 단가, 전전 단가의 경우 자동채우기를 통해 값을 구할 것입니다. 따라서 F4 키를 눌러서 절대참조를 합니다.
=OFFSET($C$1,COUNTA(C:C)-ROW(A1),0)
이번에는 OFFSET 함수 내 두 번째 인수로 ‘행 이동’ 을 입력해야 하는데요. 아래 셀에 자동채우기를 할 것이므로 숫자로 입력을 하면 안됩니다.
따라서 COUNTA 함수를 입력한 후 C열 전체를 선택합니다. COUNTA 함수는 범위 내에서 값이 입력되어 있는 셀을 모두 세는 함수입니다.
위 예시의 경우 COUNTA 함수를 이용해서 값을 구하면 8이 나옵니다.
=OFFSET($C$1,COUNTA(C:C)-ROW(A1),0)
그런데 우리가 지정 했던 C열을 보면 C1셀이 숫자가 아니라 ‘단가’ 라는 텍스트입니다. 따라서 이 셀은 빼 줘야 합니다.
그러기 위해서 빼기 1을 해주면 되는데요. 단, ‘전 단가’의 경우 빼기2를 해야합니다. 그리고 ‘전전 단가’의 경우 빼기 3을 해야 합니다.
그래서 단순히 숫자를 입력하면 안 됩니다. 숫자 대신 ROW 함수를 입력하는데요. ROW 함수는 행 번호를 반환하는 함수입니다.
ROW 함수를 입력하고 인수로 A1 을 입력합니다. A1 행번호는 1입니다. 따라서 1이라는 값이 반환이 됩니다. 만약 A2를 입력한다면 2라는 값이 반환이 되고요. A3을 입력한다면 3이라는 값이 반환이 됩니다.
따라서 아래 셀에도 각각 빼기 2, 빼기 3가 자동으로 지정되도록 할 수 있습니다.
따라서 ROW(A1) 이라고 입력을 해줍니다.
우리는 ‘행 이동’ 만 하면 되며, ‘열 이동’은 하지 않아도 됩니다. 따라서 ‘열 이동’ 부분에는 0이라고 입력을 해주시면 됩니다.
그 후 엔터키를 누르면 ‘최근단가’ 인 7,000원이 결과 값으로 나옵니다.
그리고 ‘단가’ 데이터를 수정하거나 추가할 경우 자동으로 최근단가, 전 단가, 전전 단가도 바로 수정이 됩니다.
최근 3개 단가 평균 구하기
최근 3개 단가 평균 구하기 방법을 알아보겠습니다. 아래 사진에서 최근 3개 단가인 키위, 딸기, 오렌지의 단가 평균을 구하는 방법입니다.
평균을 구하는 함수로 AVERAGE 함수를 사용할 수 있습니다. 값을 구하기 위해서 아래와 같이 함수식을 입력합니다.
=AVERAGE(OFFSET(C1,COUNTA(C:C),0,-3))
위와 같이 함수식을 입력하는 이유를 알아보겠습니다.
=AVERAGE(OFFSET(C1,COUNTA(C:C),0,-3))
우리는 단가와 관련된 값을 찾을 것입니다. 따라서 단가가 시작되는 부분인 C1 셀을 ‘시작위치’로 선택합니다.
=AVERAGE(OFFSET(C1,COUNTA(C:C),0,-3))
그 후 ‘행 이동’ 값을 COUNTA 함수로 입력하는데요. COUNTA 함수를 사용하는 이유는 최근 데이터가 입력되는 경우 해당 최신 데이터를 반영하기 위함입니다.
COUNTA 함수 내에서 단가에 해당되는 C열을 선택 해줍니다. 콤마를 입력하고 ‘열 이동’으로 0을 입력합니다. 그 후 ‘행 선택 셀 개수’ 를 -3으로 입력합니다.
만약 3으로 입력 했다면 아래로 3번째 범위까지 지정이 될 것입니다. 하지만 반대로 -3을 입력한다면 위로 3번째 범위까지 지정이 됩니다.
우리는 최근 3개 단가 평균을 구할 것입니다. 따라서 아래에서부터 위로 3번째 셀까지 범위가 지정되도록 하는 것입니다.
괄호를 2번 입력하고 엔터키를 누르면 최근 3개 단가 평균이 자동으로 구해집니다.
<결론>
- 엑셀 OFFSET 함수 사용법
- 엑셀 OFFSET 함수: 내가 지정한 행, 열 숫자로 이동한 값을 반환하는 함수
- = OFFSET ( 시작 위치, 행 이동, 열 이동, [행 선택 셀 개수], [열 선택 셀 개수] )
- SUM, AVERAGE, MATCH, ROW 함수 등과 함께 사용됨
이상 엑셀 OFFSET 함수 사용법에 관한 글이었습니다.