여기서 OFFSET이란 말은 기준으로부터 얼마나 떨어져 있는지를 나타내는 의미로 쓰입니다. 공학쪽에서는 편차라고도 하죠. 아마 표준 편차라는 말은 많이 들어보셨을 겁니다.
아무튼 엑셀에서도 이 의미대로 이해해 주면 될 겁니다.
이 함수에 전달되는 인수가 좀 많은데 크게 염려하실 필요 없습니다. 그냥 이해하면 됩니다.
OFFSET(Reference, Rows, Cols, Height, Width)
우선 이 함수의 하는 일은 어떤 기준이 되는 셀로부터 전달된 Rows값과 Cols값 만큼 떨어진 셀을 반환해 줍니다. Rows는 행이고 Cols는 열이죠. 이 행과 열의 값은 절대값이 아닌 상대적인 편차(OFFSET)을 의미합니다. 즉 얼만큼 떨어졌냐를 지정하는 것이죠.
그럼 예를 들어보겠습니다.
OFFSET 기본 사용법
OFFSET(C3, 3, 1)
해석 : 전달된 인자 순서대로 해석하면 기준 셀은 C3, 3행 이동, 1열이동. 그리고 이동된 곳의 셀의 값을 반환합니다. 여기선 9를 반환했죠.
OFFSET으로 범위 지정하기
앞에서는 하나의 셀만 선택하여 값을 돌려주었습니다. 하지만 엑셀에서는 여러개의 셀을 선택할 수도 있죠. 그리고 OFFSET의 나머지 인자들을 사용하면 범위를 지정할 수 있습니다. 이렇게 범위가 지정될 때는 이름정의를 사용해 주면 됩니다.
잘 모르겠다면 위 링크 참조해주세요.
여기서는 OFFSET함수로 지정된 범위의 셀에 있는 내용들을 목록으로 하는 드롭다운 메뉴를 만들어 보겠습니다. 결과는 다음과 같은 모습입니다. 드롭다운 메뉴가 들어갈 곳은 물품 아래쪽 셀들입니다.
- 먼저 이름정의를 해보겠습니다.
▲ 셀 주소는 절대주소로 $를 붙여주세요.($C$3)
- 이번에는 드롭다운을 만들 셀(물품 아래셀선택)을 선택한 후에 데이터 ->데이터도구의 데이터 유효성 검사를 선택해 주세요.
▲ 제한 대상을 목록으로 선택하고 원본은 =OFFSET연습 으로 해주세요. (OFFSET연습은 앞에서 이름정의한 이름입니다.)
이제 드롭다운 메뉴가 생겼을 겁니다.
아래쪽 셀들도 드롭다운 메뉴를 적용해야 하므로 셀 오른쪽 끝을 마우스로 잡아서 아래쪽까지 끌어서 적용해 주세요.
- 수식->이름관리자로 들어가서 OFFSET함수의 마지막 인자(height)값 대신 COUNTA함수를 사용합니다.
COUNTA함수는 인수로 전달된 범위 안에 비어있지 않는 셀의 개수를 반환해주는 함수입니다. 대략 감이 잡히셨나요? 범위로 부품목록의 처음부터 넉넉하게 16칸을 잡았습니다. 만약 더 추가될 것이 있을 것 같다면 더 범위를 넒히셔도 되고 아니면 열 전체로 만드셔도 될 겁니다. $D:$D라고 하면 D열 전체가 됩니다. 이때는 필드이름 "부품"이 포함되니 1을 빼주셔야 합니다. 그리고 그 행 전체에는 다른 내용이 있으면 안되니 주의도 해주셔야하구요.
추가물품 2개를 추가했는데 드롭다운 목록에도 자동으로 추가된 그림입니다. 적절하게 사용하시면 될 것 같습니다.