블로그 이미지
주로 인재개발원 등의 사이버학습을 정리, 요약하는 상시학습 블로그입니다. 깨비형
« 2017/10 »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

Archive»

체험 블로그 마케팅 서비스 OLPOST

Category»

Notice»

Statistics Graph

 

'Offset'에 해당되는 글 2

  1. 2014.12.15 offset 함수 사용법
  2. 2012.10.12 9. 데이터 유형 및 참조 영역 변환하기
 

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




저작자 표시 동일 조건 변경 허락
신고

1. 데이터 유형 검사하기


  1) IS 함수의 개요

○ 정의 : IS로 시작되는 정보 함수. 값의 유형을 검사하고 그 결과에 따라 TRUE · FALSE를 반환하는 함수

○ 구문 =함수명(Value) 

○ 인수

▷ Value : 필수 요소, 테스트 할 값, 빈 셀, 오류, 논리값, 텍스트, 숫자, 참조 값 또는 이러한 항목을 가리키는 이름


  2) IS 함수의 종류


함수

TRUE를 반환하는 경우 

ISBLANK

  값이 빈 셀을 참조하는 경우

ISERR

  값이 #N/A(사용할 수 없는 값)를 제외한 오류 값을 참조하는 경우

ISERROR

  값이 임의의 오류 값을 참조하는 경우

ISLOGICAL

  값이 논리값을 참조하는 경우

ISNA

  값이 #N/A(사용할 수 없는 값) 오류 값을 참조하는 경우

ISNONTEXT

  값이 텍스트가 아닌 항목을 참조하는 경우

  빈 셀을 참조하는 경우 → TRUE를 반환

ISNUMBER

  값이 숫자를 참조하는 경우

ISREF

  값이 참조를 참조하는 경우

ISTEXT

  값이 텍스트를 참조하는 경우


<사용 예시>

[G4]셀의 경우 : [F4]셀 데이터 유형이 문자인지 비교 → False → D4/F4 → 259.1

※ G4셀의 함수 구문은 F4가 텍스트면 그대로, 텍스트가 아니면 D4/F4를 가져오라는 수식

[I4] 셀의 경우 : [H4]셀 데이터가 있는 지 확인 → True → "A" 또는 "B"인지 확인 → True

"통과"

※ I4셀은 친절도열 조사(비어있으면 "재조사", A나 B는 "통과", 나머지는 "미달"로 표시하라는 수식)




2. 파생된 참조 영역 사용하기


  1) OFFSET 함수

○ 정의 : 셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환하는 함수 

- 반환되는 참조는 단일 셀 또는 셀 범위 

- 반환할 행 및 열 수 지정 가능

○ 구문 =OFFSET(Reference, Rows, Cols, [Height], [Width])

○ 인수

▷ Reference : 필수 요소, 기본 참조 영역으로 셀 또는 인접한 셀 범위

▷ Rows : 필수 요소, 기본 참조 영역의 첫 행과 출력할 영역의 첫 행 사이의 간격

양수 → 아래로 지정, 음수 → 위로 지정 가능

예) Rows 인수로 5 사용 : 기본 참조 영역보다 5행 아래로 지정됨 

▷ Cols : 필수 요소, 기본 참조 영역의 첫 열과 출력할 영역의 첫 열 사이의 간격

양수 → 오른쪽으로 지정, 음수 →왼쪽으로 지정

예) Cols 인수로 5 사용 : 기본 참조 영역보다 5열 오른쪽으로 지정됨

▷ Height : 선택 요소, 반환되는 참조의 높이(행 수), 양수만 지정 가능

▷ Width : 선택 요소, 반환되는 참조의 너비(열 수), 양수만 지정 가능

○ 특징

▷ Rows 및 Cols 오프셋이 워크시트 가장자리 위를 참조하는 경우 #REF! 오류 값을 반환

▷ Height 또는 Width를 생략하면 높이나 너비가 Reference와 같은 것으로 간주

▷ OFFSET은 셀을 실제로 이동하거나 선택을 변경하지 않으며 단지 참조를 반환


<사용 예시>

[B19]셀의 경우 : 기준이 되는 A2셀에서 행으로(아래로) 1칸, 열로(오른쪽으로) 1칸 이동한 B3셀부터 높이(아래) 6칸, 폭이(오른쪽) 5칸이 되는 범위를 모두 더하라는 식이다.



3. 행과 열을 바꾸어 표시하기


  1) TRANSPOSE 함수

○ 개념 : 세로 셀 범위 → 가로 범위, 가로 셀 범위→ 세로 범위로 바꾸어 반환하는 함수 

배열 수식으로 입력해야함.

이를 입력하는 범위의 행 및 열 수는 각각 범위의 열 및 행 수와 동일해야 함

워크시트에서 배열의 가로와 세로 방향 변환 가능

○ 구문 =TRANSPOSE(Array)

○ 인수

▷ Array : 필수 요소, 행과 열을 바꿀 워크시트의 셀 범위 또는 배열 

▷ 배열의 첫 번째 행 → 새 배열의 첫 번째 열

▷ 배열의 두 번째 행 → 새 배열의 두 번째 열


<사용 예시>

[B14:H15]셀의 경우 : [F4:G10] 셀 범위 → 행/열 변환

  


4. 응용하기


 ○ 동적 범위 이름 지정하기

  이름 정의에서 OFFSET 함수를 사용하면 됩니다. 

  이 때, 포인트가 되는 부분은 셀 범위의 전체 행 수를 지정하는 Height 인수입니다. 

  여기서는 A열에 제목이 입력되어 있으므로 B열에 데이터가 입력된 셀의 개수를 COUNTA 함수를 사용해서 구한 후 인수로 사용하면 됩니다. 

  따라서 Reference 인수는 데이터 목록이 시작되는 '$A$3', 시작할 행과 열 위치는 [A3]에서 변경이 없으므로 Rows와 Cols 인수값은 둘 다 '0'이 됩니다. 데이터의 행 수인 Height는 COUNTA($B:$B)로 구하고, Width는 데이터 목록의 열이 모두 8개이므로 '8'이 됩니다. 

  이 수식은 이름 정의에서 사용할 것이므로 셀 참조는 모두 절대 번지로 지정해야 합니다. 따라서 이름 정의에서 참조 대상으로 사용할 수식은 '=OFFSET($A$3,0,0,COUNTA($B:$B),8)'이 됩니다. 



① [수식] 탭을 선택한 후, [정의된 이름] 그룹에서 [이름 정의] 아이콘을 클릭합니다.



② [새 이름] 대화상자에서 '이름'에 '데이터'를 입력하고 '참조 대상'에 '=OFFSET($A$3,0,0,COUNTA($B:$B),8)'을 입력한 후, <확인> 단추를 클릭합니다.



③ 데이터베이스 함수를 사용한 [J9]셀에서 데이터베이스 범위로 입력된 'A3:H16' 부분을 정의된 이름인 '데이터'로 변경합니다.



④ 다음과 같이 15번 데이터를 추가하면 이름으로 정의된 범위가 자동으로 변경되어 추가된 데이터가 수식에 적용되는 것을 확인할 수 있습니다.



 




저작자 표시 동일 조건 변경 허락
신고