엑셀(excel)함수 tip 42 - (OFFSET함수를 사용하여 특정기간 값 구하기)
엑셀(EXCEL)에서 OFFSET함수는 셀 범위에 대한 참조를 반환하는 함수로서 특정한 범위안의 셀 값을 반환하여 다른함수(예:SUM 함수)와 중첩을 통하여 해당 범위의 값의 합계 등을 계산 할수 있는 유용하면서도 많이 사용하는 함수입니다.
하단과 같이 일자별, 품목별, 판매값과 반품된 값이 있을 경우에 특정 범위의 일자(예 5일~10일)만의 자료만 필요한 경우가 있습니다. 이때 사용하는 함수가 바로 OFFSET함수와 SUM함수의 중첩함수입니다.
OFFSET함수를 이용한 특정값 반환 2번째.xlsx
▶ 원본값과 구하고자 하는 값
구하고자 하는 값(판매값과 반품값)은 G3와 I3셀에 해당날짜만 입력할 경우 하단의 값들이 자동으로 구해집니다. 이때 사용하는 함수가 바로 OFFSET함수 입니다.
▶ 데이터 유효성검사(설정)하기
하단의 자료에서 상단의 [판매기간]의 5일부터 9일까지에서 해당 셀에 5와 9를 입력하면 하단부위의 [기간]에서 5-9라는 숫자가 입력이 되도록 지정을 합니다 이렇게 지정하는 것은 엑셀 탭의 데이터유효성검사에서 할 수 있습니다. [I3셀 선택 - 데이터탭 - 데이터도구 그룹 - 데이터유효성검사]를 클릭
▶ 데이터유효성 입력창
[제한대상 / 정수, 제한방법 / 해당범위, 최소값 / G3, 최대값 /10] 과 같이 데이터가 유효하다는 설정을 해 줍니다. 이렇게 데이터유효성 검사를 해 두면 제한대상, 최소값, 최대값의 범위를 벗어나게 되면 결과값이 유효하지 않기 때문에 값이 나타나지 않습니다. 구하고자 하는 값과 범위가 위의 엑셀 예보다 많거나 적다면 거기에 맞게 데이터유효성 설정을 하시면 됩니다.
▶ OFFSET 함수인수 창
먼저 판매값은 구해보도록 하겠습니다. 판매값에 입력된 함수식은 =SUM(OFFSET(D2,G3,0,I3-G3+1,1)) 입니다. 이를 함수인수창에서 입력을 할 경우에 [I7셀 클릭 / = / SUM함수 클릭 ]
[SUM함수인수창에서 Number 1 클릭 / OFFSET함수 클릭]
[OFFSET함수인수창에서 각각의 인수 입력 / Reference(D2) / ROWS(G3) / COLS(0) / HEIGHT(I3-G1+1) / WIDTH(1) / 확인]
다음은 반품값을 구해보도록 하겠습니다. 위의 판매값을 구하는 절차와 동일하게 진행하시면 됩니다.
[OFFSET함수인수창에서 각각의 인수 입력 / Reference(E2) / ROWS(G3) / COLS(0) / HEIGHT(I3-G1+1) / WIDTH(1) / 확인]
▶ 판매값
여기에 사용된 함수식을 해석하여 보면 =SUM(OFFSET(D2,G3,0,I3-G3+1,1))
OFFSET함수에서 Reference(D2)는 참조의 범위를 D2로 즉 판매값을 참조하고 / ROWS(G3)는 참조행은 G3(5일)이며 / COLS(0) 참조행은 0(없고) / HEIGHT(I3-G1+1)는 I3-G1은 4에다 1을 더한 값, 즉 5개의 값을 구하라 / WIDTH(1)은 참조폭은 1열만 참조하라는 뜻입니다. 앞에 SUM함수가 있기 때문에 그 결과를 더하면 됩니다.
▶ 반품값
반품값에 사용된 함수식을 해석하여 보면 =SUM(OFFSET(E2,G3,0,I3-G3+1,1))여기에 사용된 함수식도 판매값과 마찬가지로 해석을 하시면 됩니다.
