Category»

Notice»

Statistics Graph
티스토리 모바일 블로그

 

'offset 함수'에 해당되는 글 2

  1. 2014.12.17 offset 함수와 counta 함수를 이용한 동적범위 지정 방법 (1)
  2. 2014.12.15 offset 함수 사용법
 



앞서 예고했듯이 오늘은 offset 함수를 이용한 동적범위를 지정 방법을 알아보겠습니다.



범위는 나누는 방법에 따라 다르겠지만 정적범위와 동적범위로 나눌 수 있습니다.


매년 국세청에서 제공하는 갑종 근로소득세 간의세율표 처럼 한번 정해놓으면 1년 내내 사용하는 표가 있는 반면,


회원명부나 고객명부 등과 같이 1년 내내 변동되는 자료가 있을 수 있습니다.


이렇듯 한번 정해 놓으면 일정기간동안 변동이 없는 자료범위를 정적범위라고 하고,

수시로 자료의 범위가 변동되는 것을 동적범위라고 합니다.


즉, 정적범위는 고정된 범위지만, 동적범위는 유동이 가능한 범위가 되겠죠.


아래 그림을 보겠습니다.




부산 사상구청 홈페이지에서 제공하고 있는 모범음식점지정현황 중 일부를 발췌한 자료입니다.

원본에는 모두 68개 업소가 지정되어 있습니다.



G4셀에 업소명을 입력하면 H4, I4셀에 주된음식과 전화번호가 출력되도록 해보겠습니다.



H4셀에 들어갈 수식은 vlookup 함수를 배웠으니 아래와 같이 정리하면 되겠죠.


VLOOKUP($G$4,$A$3:$E$13,3,0)


마찬가지로 H4셀에 들어갈 수식은 아래와 같이 됩니다.


VLOOKUP($G$4,$A$3:$E$13,4,0)


지금은 업소명에 아무것도 입력되어 있지 않기때문에 에러(#N/A)가 뜹니다.



두번째 업소명을 입력해 보겠습니다.



네 이젠 제대로 나왔네요.


이 예제에서 보면 vlookup 함수가 찾는 범위가 아래와 같이 $A$3:$E$13입니다.


그런데 아래에 두 줄을 추가해 보겠습니다.



이러면 vlookup 함수가 찾는 범위가 $A$14:$E$15까지의 범위를 추가해야 합니다.




한두 번 정도야 범위를 확장하겠지만,

자주 이런 경우가 생기면 비효율적이죠.


그래서 동적범위를 활용하여 추가 삭제가 되면 자동으로 범위가 변동되는 것을 하겠습니다.


offset 함수는 배웠으니 생략하고, 동적범위를 지정하기 위해 알아야 할 함수가 하나 더 있습니다.


바로 counta 함수입니다. 이 함수는 지정된 범위안에 내용이 있는 셀의 숫자를 세는 함수입니다.


열 전체를 지정하는 방법은 $열:$열 형식입니다. 

예를 들어 A열 전체를 지정하는 범위는 $A:$A가 되는 겁니다.



행 전체를 지정하는 방법도 마찬가지로 $행:$행 형식입니다.

이 역시 3행을 모두 지정하면 $3:$3 이 되는거죠.




업소명에서 가장 마지막 데이터까지 는 A열 전체에서 내용이 있는 셀이 몇 개인지 세면 되겠죠.


수식으로 나타내면 아래와 같습니다.


COUNTA($A:$A)


A3셀을 기준점으로 봤을 때 아래로 몇칸을 추가하면 되는 지를 COUNTA($A:$A) 값에서 찾으면 되죠.


마찬가지로 3행 전체에서 내용이 있는 셀이 몇 개인지 세는 방법 역시 아래와 같겠죠.


COUNTA($3:$3)


그런데 여기서 주의할 사항이 하나 있습니다.

우리가 포함해야 할 범위는 5칸인데, 위와 같이 세면 G3~H3까지 3개의 셀이 추가되어 버립니다.

그래서 우리가 포함해야 할 범위는 COUNTA($3:$3) 값에서 3을 빼야됩니다.


결국 우리가 동적범위로 작업할 영역은 offset 함수로 표현하면 다음과 같습니다.


OFFSET(A3,0,0,COUNTA($A:$A),COUNTA($3:$3)-3)




지난번에 포함할 영역이 없을 때에는 맨 마지막 1,1을 생략할 수 있다고 했죠.

이번에는 기준셀에서 아무런 이동 없이 영역만 포함하는 경우에는 0,0을 생략할 수 있다는 것을 알려드릴께요.

위 수식은 이동이 없기 때문에 아래와 같이 할 수도 있습니다.

(중요한 것은 0만 생략해야지 콤마(,)까지 생략하면 안된다는 점입니다.)


OFFSET(A3,,,COUNTA($A:$A),COUNTA($3:$3)-3)



자 이번에는 범위를 이름으로 지정하는 방법을 배우겠습니다.


범위를 이름으로 지정하는 방법은 두 가지가 있습니다.


마우스로 해당 범위를 지정한 뒤에 이름상자에 직접 이름을 넣는 방법입니다.


아래 그림을 보시면 이해가 되실 겁니다.




다른 방법은 [수식]탭 의 [정의된 이름]에 있는 [이름 정의]를 사용하는 방법입니다.


먼저 [수식]탭의 [정의된 이름]에 있는 [이름 정의]를 클릭합니다.




그러면 아래와 같은 새 이름 창이 하나 뜹니다.



각 부분의 활용법은 다음과 같습니다.


① 이름을 입력합니다.

② 사용할 범위를 지정합니다.(통합 문서 또는 특정 시트에서만 사용할 수도 있음)

③ 설명을 입력합니다.(생략가능)

④ 참조대상에 방금 offset 함수로 표현되는 수식을 입력합니다.



여기서는 작업영역으로 이름을 정의하고 만들어 놓았던 수식을 참조대상에 입력합니다.


OFFSET(A3,0,0,COUNTA($A:$A),COUNTA($3:$3)-3)


입력하고 난 후에 다시 열어보면 아래와 같이 나옵니다.



특이한 점은 셀주소 앞에 해당 시트 번호가 추가된다는 점이죠.


Sheet7! <--- 이 형식입니다.(이것은 자동으로 입력되는 것이니 신경 안쓰셔도 됩니다.)




자 이제 제대로 지정되었는지 확인하는 방법입니다.



아까와 마찬가지로 [수식]탭 의 [정의된 이름]에 있는 [이름관리자]를 클릭합니다.


그러면 아래와 같은 창이 새로 뜹니다.


아까 범위 지정하고 만든 데이터라는 이름영역과 방금 만든 작업영역이 보이죠.


그중 작업영역을 클릭하면




하단에 입력된 수식이 나옵니다.



이 수식 안에 마우스 커서를 넣고 클릭 한 번만 합니다.



그러면 아래와 같이 지정된 범위가 나타나게 됩니다.


나타난 영역이 제대로 된 것을 확인할 수 있습니다.




마지막으로 vlookup 함수에서 범위를 이름으로 다시 지정해주면 끝나겠죠.



이해가 되셨나요?


유용하게 활용하세요. ^^



저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

offset 함수 사용법

오피스 활용/엑셀 찾기/참조함수 | 2014.12.15 16:25 | Posted by 깨비형


오늘은 offset 함수를 배우겠습니다.


offset 함수는 주로 범위를 지정할 때 자주 사용하는 함수입니다.

특히 범위를 이름으로 지정하는 경우에 자주 쓰입니다.


보통 사원명부와 같은 자료에서 내가 원하는 자료를 찾아내기 위해서는

앞에서 배운 vlookup 함수나 index match 함수를 활용하면 찾기는 쉽죠.


그런데 이 명부가 변동이 없다면 참 좋겠습니다만

대부분이 그렇지가 않죠.

사원명부에는 신입사원이 새로 생길수도 있고, 퇴직하는 사원이 있을 수도 있습니다.

즉 추가, 삭제가 자주 이루어지면 원하는 자료를 찾기 위해 범위를 새로 지정해야되죠.


한번 두번은 그럴 수 있지만 자주 이러한 일이 반복되면 짜증이 많이 나겠죠.

이럴 때 사용하는 것이 자료의 범위를 이름으로 지정하는 방법입니다.

대신 이름으로 지정한 범위도 변동사항에 따라 자동으로 변하게 하면 되겠죠.



서설이 길어졌네요.


기본적인 문법부터 알려드리겠습니다.


OFFSET(기준셀, 행 이동 칸수, 열 이동 칸수, 행 포함 칸수, 열 포함 칸수)


기준셀 : 기준이 되는 셀입니다.(시작점으로 생각하시면 됩니다.)

행 이동 칸수 : 아래로 이동할 칸수를 말합니다.

열 이동 칸수 : 오른쪽으로 이동할 칸수를 말합니다.

행 포함 칸수 : 현재 위치에서 아래로 포함할 행수를 말합니다.(자료의 높이라고 생각하시면 됩니다.)

열 포함 칸수 : 현재 위치에서 오른쪽으로 포함할 행수를 말합니다.(자료의 폭으로 보시면 됩니다.)



예제를 보겠습니다.




위 예제는 이가을 학생의 총점을 구하는 문제입니다.


눈으로 보면 A3셀을 기준으로 아래로 5칸, 오른쪽으로 6칸을 가면 원하는 자료가 있죠.




위 그림을 보시면 D18셀에 들어있는 공식을 이해할 수 있겠죠.


D18셀에 들어있는 수식은 다음과 같습니다.


OFFSET($A$3,5,6)


그런데 처음에 배운 문법에서 두 가지가 빠졌죠.

바로 포함할 행수와 열수가 빠졌습니다.


위와 같이 한 셀만 가지고 값을 가져올 때는 포함할 행수와 열수가 각각 1, 1이 됩니다.

이럴 경우에는 생략이 가능합니다.


위의 예제를 보시면 match 함수와 같이 사용해서 5행 6열을 이동하는 것은 간단하게 이해되죠.




OFFSET($A$3,MATCH("이가을",$A$4:$A$12,0),MATCH("총점",$B$3:$I$3,0))


A열에서 이가을 이름을 찾고 3행에서 총점을 찾는 방법이죠.

문자열을 직접 입력할 때는 겹따옴표("")로 싸야되는 것 꼭 기억하세요.


이제 제대로 된 offset 함수를 알아보도록 하겠습니다.



위 그림은 이가을, 박겨울, 장여름 학생의 수학과 사회 점수를 모두 더한 값을 구하는 문제입니다.

A3 셀을 기준으로 하면 아래로 5칸, 오른쪽으로 2칸 가면 이가을 학생의 수학 점수가 나옵니다.

그 지점에서 아래로 3칸 오른쪽으로 2칸에 있는 모든 자료를 더하면 되죠.


그래서 D18셀에 들어있는 수식은 다음과 같습니다.


SUM(OFFSET($A$3,5,2,3,2))


이해가 되셨습니까?


offset 함수를 이용하여 범위를 이름으로 정의하여 

자료의 추가 삭제가 되어도 자동으로 범위를 잡아주는 방법은 다음 시간에 배우겠습니다.


아래의 파일은 유정파란 분이 만든 메크로인데, 참고하시면 되겠습니다. ^^


offset이해하기.xlsx




저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
 

티스토리 툴바