학습(공부)하는 블로그

오늘은 지난 시간 약속대로 참조할 영역이 다른 시트일 경우에는 어떻게 하는지를 알아보겠습니다.

아래 예제를 보겠습니다.

위 예제 역시 성적표 입니다.

조금 다른 점이 있다면 위와 같이 성적표 시트가 따로 있고,

아래와 같이 가져올 참조 데이터 영역은 중간고사, 기말고사라는 시트가 따로 있는 경우입니다.

다행인 것은 중간고사 시트와 기말고사 시트의 참조영역 범위가 똑같다는 것이죠.

만약 범위가 다른 경우라면 상당히 난감한 상황이 발생합니다.

(뭐 그래도 가장 데이터가 많은 행을 기준으로 하면 그럭저럭 위기는 모면하겠죠.)

이번에도 마찬가지로 indirect 함수를 사용할 것입니다.

우선 시험종류 아래 B4 셀에 데이터 유효성 검사를 지정하겠습니다.

제한 대상에 [목록]을 선택하시고, 원본: [중간고사,기말고사]라고 입력하시고 [확인]을 클릭합니다.

이젠 성적을 가져올 차례입니다.

여기서 잠깐 생각해봅시다.

B4 셀의 값이 중간고사일 경우와 기말고사일 경우 참조영역은 다음과 같이 표현할 수 있습니다.

• 중간고사일 경우 참조영역: 중간고사!$B$3:$I$12

• 기말고사일 경우 참조영역: 기말고사!$B$3:$I$12

참조영역을 가져오기 위해 indirect 함수를 사용한다고 예고했었죠.

그럼 저 영역중에 중간고사와 기말고사는 시트명으로 들어가기 때문에 참조영역이 가능합니다.

그래서 B4셀의 값을 그냥 가져오면 되겠죠.

문제는 $B$3:$I$12의 범위를 어떻게 삽입할 것인가 하는 것입니다.

indirect 함수에서 참조범위가 아닌 것은 모두 데이터로 입력해야 한다는 것을 지난 번에 배웠죠.

그래서 "!$B$3:$I$12" <---- 이 범위가 추가되면 되겠죠.

그런데 한가지 문제가 있습니다.

참조영역과 데이터를 연결하는 방법의 문제인데요,

indirect(B4"!$B$3:$I$12") <--- 이렇게 수식을 넣으면 어떻게 될까요?

입력이 되지 않습니다. 참조영역+데이터의 형식이 되기 때문입니다.

다시 말하자면 참조영역 형식도 아니고 데이터 형식도 아닌 잡탕이 되었기 때문에 인식이 안됩니다.

그래서 엑셀 프로그램이 인식하도록 약간의 기술이 필요합니다.

바로 & 라는 연결연산자가 등장하는거죠.

위 참조영역을 다시 맞게 정리하면 아래와 같습니다.

indirect(B4&"!$B$3:$I$12")

위 그림에서 C6셀에 들어간 수식은 다음과 같습니다.

=VLOOKUP($B$6,INDIRECT($B$4&"!$B$3:$I$12"),2,0)

시트 탭이 몇 개라도 형식만 똑같이 맞춰두면 동적 참조영역 얼마든지 쉽게 만들수 있겠죠!

도움이 되셨다면 공감 꾸~욱 눌러주시고 가세요. ^^

오늘은 indirect 함수를 배워보겠습니다.

이 함수는 텍스트로 지정한 셀 주소를 반환해주는 함수인데요,

앞서 배운 offset 함수가 동적 영역을 설정하는 반면,

indirect 함수는 동적 참조영역을 설정하는 함수입니다.

무슨 말인지 헷갈리죠?

이렇게 말하는 저도 쉽게 설명 못해 미치겠습니다.

간단히 예를 들면 아~하 라고 쉽게 이해하실 수 있을 것으로 보고

바로 예제로 들어가겠습니다.

아래는 성적표입니다.

같은 시트에 성적표가 두개 있죠.

왼쪽에 있는 것은 중간고사이고 오른쪽에 있는 것은 기말고사입니다.

하단에 보시면 시험종류에 따라 학생의 이름을 입력하면 그 학생의 성적표가 나오도록 하는 것입니다.

물론 if 함수를 사용하면 되겠죠.

B16셀에 

• 중간고사가 입력되면 참조범위를 $B$3:$I$12로

• 기말고사가 입력되면 참조범위를 $K$3:$K$12로

지정해주면 되죠.

C18에는 아래과 같은 수식이 입력이 되겠죠.

=VLOOKUP(B18, IF(B16="중간고사", $B$3:$I$12, $K$3:$K$12), 2, 0)

시험이 두 개밖에 없어 중간고사가 아니면 기말고사라면 저렇게 하면 되죠.

문제는 시험이 두 개만 있는 것이 아니고 매월 한번씩 있다면 

if 함수로 일일이 범위를 지정하는 것은 노가다가 되겠죠.

그래서 이럴 경우 사용하는 것이 바로 indirect 함수입니다.

위 그림과 같이 한 시트에 찾기참조할 범위가 두 개 이상 있는 경우에는

지난 번에 배운 이름을 정의하여 각 이름을 범위로 지정해 주는 방법이 간단하겠죠.

이름 지정하는 방법은 배웠죠. 그래서 생략합니다.

대신 오늘은 데이터 유효성검사를 활용하는 법을 알아보겠습니다.

데이터 유효성검사는 셀에 입력하는 자료가 유효한지 아닌지를 검사하는 거죠.

요즘 특정 셀에 정해진 데이터 외에는 선택하지 못하게 하는 방법으로 많이 활용합니다.

B16셀에서 [데이터] - [데이터 유효성 검사] - [데이터 유효성 검사 만들기] 를 선택합니다.

아마 아래와 같은 창이 하나 나타날 것입니다.

각 부분에 대한 설명은 생략하겠습니다. (개별적으로 아시고 싶은 분들은 인터넷 검색하면 됩니다.)

아래와 같이 입력합니다.

제한 대상에 [목록]을 선택하시고, 원본: [중간고사,기말고사]라고 입력하시고 [확인]을 클릭합니다.

제대로 되었다면 아래와 같이 셀 옆에 드롭다운 표시가 나왔죠. 

이제 이 셀에서는 중간고사와 기말고사 외에는 입력이 안됩니다.

(물론 목록 추가하면 추가한 목록도 입력할 수 있겠죠.)

자 이제 돌와와서 참조할 두 곳의 범위에 이름을 중간고사, 기말고사라고 각각 붙이겠습니다.

눈치 빠르신 분은 왜 그렇게 하는지 조금 눈치를 채셨을껍니다.

이름 지정하는 것은 앞에서 배웠으니 그냥 마우스로 범위 지정해서 정하겠습니다.

이젠 성적을 가져올 차례입니다.

여기서 잠깐, indiredt 함수는 가져올 데이터가 특정 행의 데이터일 경우에는 겹따옴표("")로 묶어줍니다.

반면에 가져올 데이터가 참조되는 값일 경우에는 그냥 셀주소만 가져옵니다.

아래 그림을 보시면

K16셀에는 =INDIRECT(K5)라고 입력되었지만 K5에는 그냥 데이터가 입력되어 있기 때문에 애러가 납니다.

K17셀에는 =INDIRECT("K5") 데이터가 입력되었기 때문에 겹따옴표("")로 막았죠. 그러면 셀값이 출력되죠.

K18셀에는 =INDIRECT(N14) N14셀에는 K5라는 셀주소가 입력되어 있죠, 참조가 되는거죠. 그래서 K5의 내용을 가져옵니다.

그러면 다시 본래대로 돌아가겠습니다.

결국 범위이름을 B16셀에 입력하는 데이터와 일치시켰으니, 찾기참조를 B16셀에서 가져오면 되겠죠.

그래서 C18셀에 위 그림과 같은 아래 식이 입력된 것입니다.

=VLOOKUP($B$18, INDIRECT($B$16), 2, 0)

INDIRECT($B$16)의 값은 우리가 B16셀에서 지정해 주는 범위로 변한다는 것 이해가세요.

이번 시간에는 같은 시트에 동적 참조영역을 만드는 방법을 알아보았습니다.

다음 시간에는 시트마다 입력된 형식은 같지만 

서로 다른 시트를 동적참조영역으로 만드는 방법을 알아보겠습니다.

도움이 되셨다면 공감 꾸~욱 눌러주시고 가세요. ^^

앞서 예고했듯이 오늘은 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 함수에서 범위를 이름으로 다시 지정해주면 끝나겠죠.

이해가 되셨나요?

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

오늘은 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

앞에서 index 함수와 match 함수를 배웠습니다.

보통 엑셀 시트에서 찾기/참조 함수에서 가장 많이 사용하는 것이 vlookup 함수라고 밝혔습니다.

하지만 vlookup 함수는 기준값이 각 데이터 레코드의 가장 왼쪽(1열)에 와 있어야 합니다.

기준값을 중간에 있는 필드(열) 값으로 할 수가 없죠.

그래서 이를 보완할 수 있는 방법이 바로 index 함수와 match 함수를 혼용하여 활용하는 방법입니다.

서두가 길었습니다. 아무래도 글로 설명하는 방법보다는 직접 예를 드는 것이 낫겠죠.

기본적인 문법은 다음과 같습니다.

INDEX(자료범위, MATCH(행 찾을값, 행 찾을범위, 찾을방법), MATCH(열 찾을값, 열 찾을범위, 찾을방법))

INDEX 함수의 기본은 INDEX(자료범위, 행 위치, 열 위치)라는 건 지난번에 배웠죠.

MATCH 함수와 결합하면서 위치를 찾는 것이 포함된 것이죠.

예제를 보겠습니다.

위 표에서 석차 1등인 학생의 이름과 총점을 묻는 문제죠.

그런데 그냥 표만 가지고 보면 누구나 쉽게 찾을 수 있습니다.

석차에서 1을 찾아서 그 열에 있는 데이터중 이름과 총점을 가져오면 되는거죠.

우선 엑셀에서 위 표의 데이터를 아래와 같이 좌표로 인지합니다.

아래 두개의 좌표가 다른점이라면 필드명이 들어갈지 말지의 차이입니다.

위 표는 필드명까지 모두 자료범위에 들어간 경우를 엑셀에서 인식하는 경우입니다.

그리고 아래 표는 필드명은 자료범위에 안 들어간 경우죠.

원리는 간단합니다.

먼저 석차에 1등이 들어간 것이 전체 데이터에서 몇 번째에 있는지를 셉니다.(match 사용)

여기서는 석차~8까지 모두 10개의 행이 있습니다. 

이중에서 1이 있는 위치는 위에서 4번째가 됩니다.(석차, 5, 7, 1 순으로 네번째)

이까지만 하면 우리가 구하고자 하는 행을 찾을 수 있습니다.

다음은 각 위치에 들어갈 성명과 총점이 되겠죠.

성명은 기준값으로 표시되어 있지만 범위에 포함시켜야겠죠.

성명은 A3:I3 행에서 첫번째에 있습니다. 마찬가지로 총점은 7번째가 되죠. (여기도 match 사용)

엑셀에서 이해하는 위치는 석차 1등이 있는 줄(행)과 성명, 총점이 들어 있는 열만 알고 좌표로 나타납니다.

그래서 성명이 들어갈 자리는 (4,1), 그리고 총점은 (4, 7)이 되는 겁니다.

위 그림에서 보면D18셀에 들어있는 수식은 아래와 같습니다.

=INDEX($A$3:$I$12,MATCH(C18,$I$3:$I$12,0),MATCH(D17,$A$3:$I$3,0))

MATCH(C18,$I$3:$I$12,0) : C18에 있는 1이라는 숫자를 $I$3:$I$12(석차, 5, 7, …… , 8) 범위에서 몇 행인지 찾습니다.

MATCH(F17,$I$3:$I$12,0) : F17에 있는 총점이라는 항목을 $A$3:$I$3(성명, 국어, 수학, …… , 석차) 범위에서 몇 열인지 찾습니다.

왜 수식이 이렇게 되는 지 이해가 가나요?

필드명이 있는 행이나 열을 포함할 지 말지는 상황에 따라 판단해야 합니다.

이 두 함수를 자주 혼용하여 파워 유저가 되세요. ^^

찾기/참조 함수 중 가장 많이 사용하는 것은 vlookup 입니다.

두번째로 많이 사용하는 것이 index 함수인데요,

index 함수는 혼자만으로 사용하는 경우는 드물죠.

보통 자료의 범위가 (행, 열) 개념으로 나오기때문입니다.

그래서 함께 사용하는 함수가 match 함수입니다.

두 함수를 조합해서 사용하기 위해서는 우선 match 함수에 대해 먼저 알아야겠죠.

match 함수의 결과는 원하는 데이터가 몇 번째에 있는 지 숫자로 나옵니다.

기본적인 문법은 다음과 같습니다.

MATCH(찾을값, 찾을범위, 찾을방법)

찾을값 : 기준이 되는 값이겠죠.

찾을범위 : 데이터가 들어있는 표 전체가 되겠죠.

찾을방법 : 0, -1, 1 세가지가 있습니다.

   0 : 정확히 일치하는 값을 찾습니다.

   1 : 작거나 같은 값 중 최대값(반드시 오름차순(아래로 내려갈수록 증가)으로 정렬되어 있어야 합니다.)

  -1 : 크거나 같은 값 중 최소값(반드시 내림차순(아래로 내려갈수록 감소)으로 정렬되어 있어야 합니다.)

자 설명만으로는 이해가 쉽지 않습니다.

예제를 보겠습니다.

위 그림에서 B14, B15 셀의 문제를 푸는 방법입니다.

원리는 간단합니다.

먼저 1호선 "괴정"을 표 중 1호선 범위에서 찾아서 몇 번째인지 셉니다.

그 다음 문제도 마찬가집니다. 5번째 역 이름이 나열된 범위에서 "연산"이 몇 번째인지 셉니다.

위 그림에서 보면 E14셀에 들어있는 수식은 아래와 같습니다.

MATCH("괴정",$B$3:$B$10,0)

왜 수식이 이렇게 되는 지 이해가 가나요?

서두에서도 밝혔듯이 match 함수는 홀로 사용되는 경우는 거의 없다고 봐도 됩니다.

주로 index 함수와 함께 사용되면 놀라운 능력을 보여줍니다.

다음 번에는 index 함수와 match 함수를 병행해서 사용하는 법을 알아보도록 하겠습니다.

오늘은 index 함수를 배워보겠습니다.

보통 index 함수는 match 함수와 함께 자주 사용되는 함수인데,

이 함수의 정확한 의미를 알아야 match 함수와 함께 응용할 수 있겠죠.

함수의 기본적인 문법은 다음과 같습니다.

INDEX(찾을범위, 행위치, 열위치)

찾을범위 : 데이터가 들어 있는 전체 범위가 되겠죠.

행위치 : 데이터가 있는 행위치(범위에서 몇 번째 행에 있느냐는 것을 말함)

열위치 : 데이터가 있는 열위치(범위에서 몇 번째 열에 있느냐는 것을 말함)

이것만 가지고는 언뜻 이해하기 힘들죠.

그럼 예제를 보면서 알아보기로 하겠습니다.

위 데이터는 부산 지하철 역명을 노선별 순서대로 만든 표입니다.

위의 데이터를 index 함수가 위치를 기억하는 방법은 아래 그림과 같은 (행, 열) 형식입니다.

따라서 우리가 구해야 하는 1호선 6번째 역은 데이터 표에서 (6,1) 위치에 있는 것을 찾는 것이 되죠.

3호선 7번째 역도 데이터 표에서 (7,3) 위치에 있는 것을 찾으면 되는 겁니다.

D47행에 왜 그런 식이 들어갔는지 이해하셨나요?

index 함수를 단독으로 사용할 경우에는 위와 같이 순수한 데이터가 있는 범위만 지정해 줍니다.

다음에 배울 match 함수와 같이 사용할 경우에는 기준이 되는 필드명이나 레코드명이 추가됩니다.

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

찾기/참조 함수 가운데 가장 많이 활용되는 함수가 vlookup 함수 입니다.

활용도가 매우 높기 때문에 꼭 배워두어야 할 함수인데요,

그 사용법을 배워봅시다.

기본적인 문법은 다음과 같습니다.

VLOOKUP(기준값, 찾을범위, 가져올데이터위치, 찾을방법)

기준값 : 표의 첫번째에서 찾을 데이터를 말합니다.

찾을범위 : 데이터가 들어있는 표 전체가 되겠죠.

가져올데이터위치 : 이건 좀 설명이 필요한데, 가져올 데이터가 기준값에서 기준값을 포함하여 몇번째 열에 있는지 말합니다.

찾을방법 : 참(1 또는 TRUE)은 정확한 값이 아니라도 찾고(기본), 거짓(0 또는 FALSE)은 정확한 값만 찾아라는 것입니다.

자 설명만으로는 이해가 쉽지 않습니다.

예제를 보겠습니다.

위 그림에서 아래쪽에 있는 이가을의 사회 점수와 조윤강의 영어 점수를 구하는 방법입니다.

원리는 간단합니다.

먼저 "이가을"을 표 전체 범위 중 첫번째 열에서 찾아 해당 행을 알아냅니다.(여기서는 8행이 되겠네요)

그 다음에 해당 행(8행)의 몇 번째 열에 있는 지를 찾아서 해당 값을 가져오는 것입니다.

위 그림에서 보면 F16셀에 들어있는 수식은 아래와 같습니다.

이 수식을 해석하면

먼저 B17에 있는 이가을 이란 이름을 표 A4:I12 의 범위 중 첫번째 열(A열)에서 찾습니다.

그러면 8행이 이가을 학생의 모든 데이터가 기록되어 있음을 먼저 알게 되죠.

그런 뒤에 A열을 포함해서 사회 과목이 있는 열이 몇번째인지를 확인합니다.

성명, 국어, 수학, 사회, 네번째가 되는군요.

그래서 가져올 데이터 위치가 4 가 됩니다.

마지막으로 정확히 일치하는 기본값과 똑같은 이름을 찾기 위해서는 거짓을 선택해야 하겠죠

그래서 0 을 마지막에 입력합니다.(생략 가능하지만 생략하면 참으로 간주합니다.)

아래 

F17 셀에 들어 있는 수식도 이와 같은 원리로 작동합니다.

여기서 한가지 중요한 포인트!

데이터에서 기본값을 검색할 때 반드시 범위의 첫째 열에서 찾는다는 것을 명심해야 합니다.

그리고 첫째열부터 하나 둘 셋 이렇게 열을 센다는 것을 꼭 기억하기 바랍니다.

시트 탭이 많은 파일로 작업하다 보면 많이 헷갈리죠.

그래서 탭 마다 서로 다른 색을 넣으면 쉽게 구별할 수 있습니다.

왼쪽 하단 시트 탭 이름 있는 곳에서 

① 마우스 오른쪽 버튼을 누르면 메뉴가 나옵니다.

② 그러면 그 메뉴 중에 탭 색(T)를 선택합니다.

③ 색상이 나오면 원하는 색을 선택합니다.

위와 같이 작업하면 아래와 같이 탭 색이 입혀집니다.

상단의 메뉴를 이용하는 방법도 있습니다.

아래와 같이 [홈] - [서식] - [탭 색]을 이용하면 됩니다.

색을 변경하는 방법도 마찬가지겠죠..

요긴하게 사용하세요. ^^

엑셀2007버전에서 열기 암호 설정하는 법입니다.

①[오피스 단추] - ②[다른이름으로 저장(A)] - ③[문서 저장할 스타일 지정]

위와 같이 순서대로 누르면 아래와 같이 저장메뉴로 바뀝니다.

왼쪽하단의 ④[도구(L)] - ⑤[일반 옵션(G)...] 을 차례로 클릭하면

아래와 같이 [일반 옵션] 탭이 또 하나 뜹니다.

열기 암호만 걸려면 열기암호칸에만 원하는 암호를 넣고

쓰기 암호만 걸려면 쓰기암호칸에만 원하는 암호를 넣어면 됩니다.

둘 다 설정할 수도 있습니다.

마지막으로 파일 이름을 정하고 저장까지 해주면 작업이 끝납니다.

자 제대로 되었는 지 파일을 한번 열어보겠습니다.

암호를 입력하라는 팝업창이 뜨죠. 다 되었습니다. ^^