(1) 고정된 범위를 설정하는 경우에는 기본적으로 : 기호를 이용한다.
예) SUM(B2:B9), MAX(A4:B6) 등
(2) 함수로 범위를 정하는 경우에는 OFFSET함수를 이용할 수 있다.
▶ OFFSET함수 사용법
OFFSET(기준위치, 행변위, 열변위, 행수, 열수)
기준위치로부터 행변위, 열변위만큼 떨어진 셀로부터 행수와 열수 만큼의 범위가 정해진다.
(cf. OFFSET함수의 행수,열수가 생략되거나 1일 경우에는 해당셀을 표시하게 되지만, 2이상의 셀이 포함되는 범위를 반환하는 경우에는 OFFSET함수 단독으로 사용하면 에러가 발행한다. 2셀 이상의 범위를 나타낼 경우에는 다른 함수의 인수로써 주로 사용된다.)
예) OFFSET($A$1, 5, 0, 4, 3) 의 경우 위 그림에서 $A$1 셀에서 (5, 0)변위만큼 떨어진 $A$6셀을 기준으로 4행 3열의 범위가 설정된다.
(3) 이름관리자
참조대상이 되는 범위를 일일이 함수 안에서 지정하면 복잡하므로 별도로 이름을 정하여 저장하고, 반복적으로 사용하거나 수정할 수 있도록 해주는 것이 바로 수식메뉴의 '이름관리자'이다.
떨어져 있는 범위의 경우에도 이름으로 지정해두면 다른 셀에서 쉽게 활용할 수 있다.
2. 유동범위를 이용한 토익성적 관리
(1) 유동범위를 이용한 간단한 예를 살펴본다.
다달이 보는 토익시험점수를 누적한 데이터에서 최고점을 뽑아보도록 한다.
토익점수가 있는 열의 데이터를 유동적으로 정의하기 위해 수식메뉴의 이름관리자를 클릭한다.
새로만들기 탭을 누르고 이름을 "토익점수"로 하고 참조대상은 아래와 같이 입력하고 확인을 누른다.
참조대상 = OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)
COUNTA함수는 데이터가 채워진 셀의 갯수를 반환하여 주는 함수이므로 데이터의 증감에 따른 유동적인 갯수를 지정할 수 있다.
최고득점을 뽑아내기 위해 MAX함수를 이용하면
D2 = MAX(토익점수)
가 된다.
이제, 데이터가 추가될 때마다 새로 함수를 작성할 필요없이 바로 최고득점이 갱신된다.
* 주의사항 : COUNTA함수는 비어있지 않은 행의 갯수를 세는 함수이다. 따라서, 시험을 보지 않은 경우 행을 비워 놓으면 계산에 논리적인 오류가 생긴다. 따라서, 시험을 보지 않은 경우에는 ×기호 등을 입력해 놓아서 이러한 오류를 방지하여야 한다.
* 수식 - 이름관리자 메뉴에서 OFFSET,COUNTA함수를 이용하여 이름을 정의하여 두고 이 이름을 범위로 하여 함수에 적용하면 자료의 증감에 따라 동적으로 변하는 범위에 대하여 새로 함수를 만들 필요없이 자동으로 결과가 갱신된다.
(2) 동적범위의 시작위치가 변하는 경우
보통의 경우에는 위(1)처럼 OFFSET함수의 행수만 설정하면 끝나지만, 토익시험처럼 유효기간이 설정되어 있는 경우에는 시작위치(OFFSET함수의 행변위값)를 설정해주어야 한다.
아래의 그림처럼 토익성적의 유효기간내 최고득점을 뽑아보자.
토익성적의 유효기간은 2년이다. 따라서, 시험일자가 유효기간 내의 날짜인지를 비교하여 유효기간에 포함되는 첫시험일자의 행위치를 OFFSET함수의 행변위 값으로 이용하면 될 것이다.
우선, 시험일자도 동적범위를 활용하여 이름을 정의하여 둔다.
이름관리자 메뉴를 열고 새로만들기 탭을 눌러 이름을 "시험일자"로 하고 참조대상은 아래와 같이 입력하고 확인을 누른다.
참조대상 = OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)
추가하면 이름관리자에서 아래와 같이 나타난다.
행위치를 찾을 때 이용하는 함수는 MATCH함수이다.
▶ MATCH함수 사용법
MATCH(찾을값 혹은 기준값, 찾을범위, 옵션)
기준값 : EDATE(TODAY(),-24) (2년 전 오늘은 오늘에서 24개월을 빼면 되므로 EDATE함수 사용)
찾을범위 : 시험일자 (유동범위로 이름 정의하여 둔 '시험일자')
옵션 : 1 (오름차순으로 나열된 범위에서 기준값보다 작은값들 중에서 최대값을 찾는다.)
그림에서 2011-12-06일에 실행시 MATCH(EDATE(TODAY(),-24),시험일자,1) = 2 이다. 즉, 시험일자 범위(A2:A29)의 데이터 중에서 A3이 기준값(2009-12-06)보다 작은 값 중에서 최대값이므로 행의 상대 위치 2를 반환하는 것이다. 이 값을 토익점수 범위의 OFFSET함수 행변위값으로 이용하면 될 것이다.
이번에는 유효기간 내의 토익점수만을 추려내기 위하여 동적범위 '유효득점'을 정의한다.
수식 - 이름관리자 - 새로만들기
이름 : 유효득점
참조대상 : OFFSET($B$2,MATCH(EDATE(TODAY(),-24),시험일자,1),0,COUNTA($B:$B)-3,1)
확인을 누르면 아래 그림처럼 '유효득점'으로 정의된 이름이 생긴다.
OFFSET함수의 행수는 행변위를 고려하여 추가로 빼주었다.
유효기간 내의 토익성적을 추려냈으니 최고점만 뽑으면 된다.
D6 = MAX(유효득점)
* 어떤 값을 찾을 때는 VLOOKUP함수를 사용하지만 값 자체가 아니라 값이 있는 위치를 알아내고자 할때 사용하는 함수가 MATCH함수이다. MATCH함수와 유동범위를 활용하면 데이터의 증감이 있는 자료에서 유효범위 내의 자료만을 대상으로 작업을 할 수 있다.
3. 유동범위를 이용한 주가그래프 그리기
유동범위를 이용하여 주가그래프를 그려본다.
아래는 현대자동차 종목의 주가 현황이다.
최종거래일을 기준으로 역으로 30일 간의 주식의 시가와 종가 현황을 그래프로 나타내고자 한다.
일단, 거래일자로부터 시가까지의 범위를 임의로 설정(예: B1:D7)하여 그래프를 삽입한다.
가로축이 보기좋게 나오지 않으므로 가로축에서 마우스 오른쪽을 클릭하여 축서식 메뉴에서 표시형식을 간단한 날짜 형식으로 바꾼다.
이제 유동범위를 생각해보도록 한다.
최종거래일자의 행은 B열의 끝행이다.
= COUNTA(B:B)
시작위치가 변하므로 OFFSET함수의 행변위 인수를 유동적으로 설정한다.
최종거래일로부터 역으로 30일 간의 거래일자 범위는 아래와 같다.
= OFFSET($B$1,COUNTA($B:$B)-30,,30,1)
이름관리자 메뉴의 새로만들기 탭을 눌러 이름을 "일자범위"로 하고 참조대상은 위와 같이 입력하고 확인을 누른다.
마찬가지로 이름관리자에서 "시가범위"와 "종가범위"를 설정한다.
시가범위 = OFFSET($D$1,COUNTA($D:$D)-30,,30,1)
종가범위 = OFFSET($C$1,COUNTA($C:$C)-30,,30,1)
차트의 데이터 위에서 마우스 오른쪽 클릭하여 데이터 선택 메뉴를 누른다.
데이터 원본 선택 메뉴의 범례항목(계열)에서 종가를 선택하여 '편집'탭을 누르고 유동범위를 입력하도록 한다.
계열 값 = (시트명)!종가범위
마찬가지로 계열에서 시가도 선택하여 편집탭을 눌러 시가범위를 입력하고, 가로축 레이블의 편집탭을 눌러서 일자범위도 입력한다.
최종적으로 확인을 누르면 아래 그림과 같은 그래프가 나타난다.
가로축 항목의 갯수가 많아 보기 불편하므로 가로축에서 마우스 오른쪽 버튼을 눌러,
축서식에서 축옵션을 텍스트 축으로 선택하고 간격 단위를 2로 설정한다.
거래일자의 데이터가 추가되는대로 그래프 내용도 자동적으로 바뀐다.
* 유동범위를 이용하면 데이터의 증감에 따라 새로 함수를 작성할 필요가 없어 편리하게 활용할 수 있다.
