나눔터  
  HOME > 나눔터 > 묻고답하기 > 엑셀
엑셀
엑셀에 대한 질문과 답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
 "000 님, 도와주세요", "부탁 드립니다.", "급합니다!" 등과 같이 막연한 제목을 달지 말아주세요.
[필독] 빠르고 정확한 답변을 얻는 16가지 Tip !
[필독] 저작권법 개정에 따른 이용안내
작성자:  

 조삿갓 (choga21)

추천:  2
파일:     테이블참조4인방.xlsx (41.7KB) 조회:  4778
제목:   [강좌] 테이블 참조 4인방: 끝판왕!
     
  - 엑셀 버전(95,97,2000,xp,2003,2007):2016

* 아래줄에 질문을 작성하세요 >>

1. 채워넣어야 하는 셀에 데이터를 채워넣으면 됩니다. 
2. 참조하는 셀에 보면 데이터가 있습니다. 
3. 조건은 이메일을 기준으로 참조하는 셀의 W01~ 부터의 값과 배치되는 숫자를 넣는 것인데요. 어떻게 하면 될까요. 오프셋을 쓰면 되는 것으로 들었는데 예시가 피요합니다...ㅠ
==============[야생토끼님 글에 대한 답변입니다]==============

1. VLOOKUP 함수

=VLOOKUP(조회하려는 값, 값을 조회하려는 범위, 반환 값이 들어 있는 열 번호, 정확히 일치 또는 유사 일치(0/FALSE 또는 1/TRUE로 표시됨)). 

예1) =VLOOKUP($C$3,$O$3:$R$6,3,0)
      $O$3:$R$6에 있는 참조표의 맨 왼쪽 열('인덱스 키'라고 함)에서
     $C$3에 있는 값과 일치하는 값(행)을 찾아
     그 줄의 3번째 열에 있는 값을 가져옴

예2) =VLOOKUP($C$3,$O:$R,3,0)
      참조표의 주소를 지정하는 방식에서 이처럼 행번호를 생략하면
     O열부터 R열까지 열 전체를 참조표로 인식
     즉, $O:$R 은 $O$1:$R$1048576 으로 해석됨
     이렇게 함으로써 O열~R열의 원본 참조표에
     선수가 몇명이든 자료만 얼마든지 입력하면 되겠습니다.

예3) =VLOOKUP($C$3,$O$3:$R$6,2)  또는
     =VLOOKUP($C$3,$O$3:$R$6,2,1)
      $O$3:$R$6에 있는 참조표의 맨 왼쪽 열에서
     $C$3에 있는 값보다 작거나 같은 값 중 제일 큰 값을 찾아
     그 줄의 2번째 열에 있는 값을 가져옴
     단, 이렇게 네 번째 인수를 생략하거나 1, TRUE 등으로 지정하여
     유사한 값을 참조할 목적이라면
     데이터 참조표는 반드시
     첫째 열(인덱스 키)를 기준으로 오름차순 정렬되어 있어야 합니다.
      특히 $O:$R과 같이 열 전체를 참조표 범위로 지정할 경우
     표 헤드(이 파일의 경우 '성명') 데이터까지도
     인덱스키로 인식해 버리기 때문에 주의해야 합니다.

예4) =VLOOKUP($A3,'참조 대상'!$A:$AC,MATCH(C$2,'참조 대상'!$1:$1,0),0)
     현재 시트의 A 열에 있는 값과 정확히 일치하는 값을
     참조대상 시트의 $A열에서 찾고
     현재 시트의 2행에 있는 필드명을
     참조대상 시트의 1행에서 찾아서
     서로 만나는 지점에 있는 값을 가져옴
     (참조대상 테이블의 필드명과 찾아오려는 테이블의 필드명이 서로 같게 하면
      이와 같이 MATCH 함수와 결합하여 열번호를 찾게 설계하고
      이 수식을 찾아오려는 테이블 전체에 그냥 복사해서 쓸 수 있음)
     - 조회할 값과 열번호에 혼합참조 주소를 사용한 것에 주목 -
    
* 장점
  1) 문법이 쉽고 간결하다
  2) 조회키를 직접 지칭하여 조회가 가능하다
     (MATCH 함수와의 결합은 열번호 계산할 때만 쓰면 됨 - 예4 참고)
  3) 유사일치 기능을 적절히 활용하면 A, B, C, ... 학점 평어 등을
     중복 IF 함수를 복잡하게 쓰지 않고도 구현이 가능하다.

* 제한점
  1) 인덱스 키는 참조표의 맨 왼쪽에 위치해야 한다.

============================================================

2. INDEX 함수

=INDEX(참조표의 범위, 행번호, 열번호, 영역번호)

  - 참조표(배열)가 한 행 또는 한 열로만 되어 있을 경우에는 열번호나 행번호만 지정하면 됨
  - 영역번호는 보통 생략하고 사용하는데
    만일 참조표가 서로 떨어져 있는 두 직사각형 영역의 합집합인 경우에
    참조표를 괄호로 묶어 지정해 주고 영역번호를 지정함

예1) =INDEX($B$11:$E$20, 5, 3)
     $B$11:$E$20 범위의 배열에서 5째 줄, 3째 열에 있는 셀
     즉, D15 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

예2) =INDEX(($B$1:$E$8,$A$11:$G$15), 3, 2, 2)
     $B$1:$E$8, $A$11:$G$15 두 개의 배열 영역 중 2번째 영역
     즉, $A$11:$G$15에서 3째 줄, 2째 열에 있는 셀
     즉, B13 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

예3) =INDEX($A$5:$Z$5, 7)
     $A$5:$Z$5 범위의 배열에서 7번째 셀(한 줄짜리이므로 열번호만 지정)
     즉, G5 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

예4) INDEX('참조대상'!$A:$AC,MATCH($A3,'참조대상'!$A:$A,0),MATCH(B$2,'참조대상'!$1:$1,0))
     참조대상 시트의 $A:$AC에 있는 참조표에 대하여
     A열에서는 현재 시트의 A열에 있는 참조키와 일치하는 위치(행번호)를 찾고
     1행에서는 현재 시트의 2행에 있는 필드명와 일치하는 위치(열번호)를 찾아
     그 교차점의 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

* 장점
  - 참조대상 테이블에서 참조키가 맨 왼쪽이 아닌 중간 위치에 있어도 활용 가능

* 단점
  - 예 4의 경우처럼 실제 활용하기에는 좀 복잡함
    (MATCH 함수를 두 번 결합해 주어야 함)

* 제한점
  - 행번호와 열번호는 양수만 가능함
    (0이나 음수가 지정되면 오류 발생하며
    소숫점 이하는 반올림하지 않고 잘라내림: 3.9 -> 3)

==================================================================

3. INDIRECT 함수

=INDIRECT(주소값, 참조방식)

  - 주소값이란 참조할 주소를 나타내는 문자열임
    (예: A1, $B$7, R3C2, R[-1]C, '시트명'!Z8 등)
  - 참조방식은 주소가 A1 방식이면 TRUE(0 아닌 정수 또는 생략해도 됨)
    R1C1 방식이면 0(FALSE)를 지정함
  - 지정된 주소에 해당하는 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

  - INDIRECT 함수의 본래 기능은 지정한 주소에 해당하는 셀을
    간접 참조하는 것이지만,
    MATCH 함수와 적절히 결합하여 행번호와 열번호를 지정함으로써
    테이블 참조를 할 수도 있음

예1) =INDIRECT("A1", 1) 또는
     =INDIRECT("A1")
     현재 시트의 A1 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

예2) =INDIRECT("R3C2", 0)
     현재 시트의 3번째 행 2번째 열(B)
     즉, B3 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

예3) =INDIRECT("'DB'!R2C5", 0)
     'DB' 시트의 2번째 행 5번째 열(E)
     즉, E2 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

예4) =INDIRECT(A1, 1) 또는
     =INDIRECT(A1)
     현재 시트의 A1 셀에 있는 값(수식일 경우 계산 결과)이 나타내는
     주소에 해당하는 셀에 있는 값(수식일 경우 계산 결과)을 가져옴
     예를 들어 A1 셀의 값이 "C7"이라면 C7 셀의 값을 가져옴

예5) =INDIRECT("'참조대상'!R"&MATCH($A3,'참조대상'!$A:$A,0)&"C"&MATCH(B$2,'참조대상'!$1:$1,0),0)
     참조대상 시트의 A열에서는 현재 시트의 A열에 있는 참조키와 일치하는 위치(행번호)를 찾고
     참조대상 시트의 1행에서는 현재 시트의 2행에 있는 필드명와 일치하는 위치(열번호)를 찾아
     참조대상 시트에서 그 행/열에 위치한 셀에 있는 값(수식일 경우 계산 결과)을 가져옴

* 장단점은 INDEX 함수의 경우와 동일함

* 유의할 사항
  - INDEX 함수는 첫번째 인수로 지정한 배열 영역에서 행/열 위치를 계산하여 가져오지만
    INDIRECT 함수는 시트 전체를 대상으로 행/열 위치를 계산해야 하므로 주의할 것
    즉, INDIRECT 함수는 시트 전체를 참조대상 배열영역으로 하는 INDEX 함수와 같다고 볼 수 있음
  - 통상 테이블 참조의 목적으로 INDIRECT 함수를 쓰려면
    주소를 R1C1 방식으로 계산하여 지정하는 것이 더 간편하므로
    R1C1 참조 방식에 대한 이해가 있어야 함
  - 상대참조 방식을 사용할 경우에, 행번호나 열번호에 0이나 음수 사용 가능

================================================================

4. OFFSET 함수

=OFFSET(기준 셀, 행번호, 열번호, [길이], [폭])

기준 셀로 지정한 셀을 기준으로
행번호만큼 아래로, 열번호만큼 오른쪽으로 이동한 셀로부터
(0이면 기준셀과 같은 자리, 음수로 지정하면 반대 방향)
길이만큼 아래쪽(반드시 양수여야 함)
폭만큼 오른쪽(역시 반드시 양수)으로 간 만큼의 범위를 반환

만일 길이나 폭을 생략하면 1로 간주되어
둘 다 생략한 경우에는 딱 그 셀만 참조하게 됨
(즉, 이 경우에 그 셀에 있는 값(수식의 계산결과)을 가져옴)

예1) OFFSET($P$3,2,0,3,2)
     $P$3을 기준으로 아래로 2칸, 오른쪽 0칸을 가면 $P$5
     거기서부터 다시 아래로 3줄, 오른쪽 2칸의 직사각형을 그리면
    $P$5:$Q$8 범위를 반환
     이렇게 범위를 반환하는 경우는
    데이터유효성검사 목록이나 또는 다른 함수의 인수로 사용할 수 있음
     즉, =SUM(OFFSET($P$3,2,0,3,2))과 같이 사용하면
    $P$5:$Q$8 범위의 합계를 구할 수 있음

예2) =OFFSET($O$3,1,2)
     $O$3을 기준으로 아래로 1칸, 오른쪽 2칸을 가면 $Q$4
     길이와 폭을 모두 생략했기 때문에
    이 경우에는 $Q$4 셀에 있는 수식의 계산결과 값이 바로 반환됨

예3) =OFFSET('참조대상'!$A$1,MATCH($A9,'참조대상'!$A:$A,0)-1,MATCH(B$2,'참조대상'!$1:$1,0)-1)
     참조대상 시트의 A열에서는 현재 시트의 A열에 있는 참조키와 일치하는 위치(행번호)를 찾고
     참조대상 시트의 1행에서는 현재 시트의 2행에 있는 필드명와 일치하는 위치(열번호)를 찾아
     참조대상 시트의 A1 셀을 기준으로 (행번호-1)만큼 아래쪽으로
     (열번호-1)만큼 오른쪽으로 이동한 위치에 있는
     셀에 있는 값(수식일 경우 계산 결과)을 가져옴

* 장단점은 INDEX 함수의 경우와 동일함

* 유의할 사항
  - OFFSET 함수의 행번호, 열번호는 기준 셀로부터의 상대 위치 값이므로
    0이나 음수로도 지정할 수 있음
  - 동일한 이유에서,
    만일 참조대상 표가 1행 또는 A열부터 시작될 경우에는
    MATCH 함수로 계산한 행/열 번호에서 1씩 빼 주어야 함
    (예3 참고 - INDIRECT 함수의 예5와 비교해 보삼)

================================================================

5. 전체적인 유의사항

1) 테이블 참조를 목적으로 위 4인방 중 어느 것을 쓰든지
   VLOOKUP 또는 MATCH 함수가 참조키 값을 찾지 못하면
   #N/A 오류가 발생하여
   관련된 계산(합계 등)이 모두 오류가 되므로
   IFERROR 함수와 결합해서 사용하는 것이 안전함

   예) =IFERROR(VLOOKUP($A3,'DB'!$A:$G,3,0),0)
       A3의 참조키 값을 DB에서 찾지 못할 경우에는 0을 반환

   예) =IFERROR(INDEX('참조대상'!$A:$AC,MATCH($A18,'참조대상'!$A:$A,0),MATCH(C$2,'참조대상'!$1:$1,0)),"")
       찾지 못하면 ""을 반환

2) 만일 참조 대상 테이블에 참조키가 중복된 것이 있을 경우
   VLOOKUP 이나 MATCH 함수는 최초로 만나는 것만 찾아주고
   그 이후의 것은 무시되어 버립니다.
   중복된 키 값에 대해 구분하여 찾는 기법은
   이전 제가 작성한 답변 내용을 참고하기 바람니다

   - COUNTIF 함수와 결합하기
     http://www.officetutor.co.kr/board/Dtype/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=123066&page=
   - 가장 아래(최근 데이터)에 있는 키 값 찾아오기
     http://www.officetutor.co.kr/board/Dtype/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=123579&page=
   - 문자열 결합으로 중복 키 구분하기
     http://www.officetutor.co.kr//board/DType/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=123702&page=&fchk=&fval=

3) 참조키는 물론 필드명(열번호를 찾는 기준) 역시
   MATCH 함수와 결합하기 위해서는
   각각의 필드명이 서로 구분되어야 하고(중복 금지)
   찾을 대상 테이블과 찾아오려는 테이블에서
   필드명이 서로 일치하게 일관성을 가져야 합니다.
   이렇게 하고 혼합참조 주소지정 방식을 적절히 활용하면
   잘 설계한 하나의 수식을
   찾아오려는 테이블 전체에 복사해서 활용할 수 있습니다.
   첨부파일에 있는 모든 수식이
   각 시트의 B3 셀에 똑똑한 수식 하나만 잘 설계해서 입력하고
   요걸 시트 전체에 복사해서 완성한 것입니다.
   즉, 노가다가 아닌 머리로 해결한 것이지요.
   이것이 컴퓨터 활용의 묘미입니다.

4) 결론적으로
  - 참조 테이블의 맨 왼쪽에 참조키가 있는 경우에는 VLOOKUP을 강추
  - 참조키가 테이블의 중간 열에 있는 경우
    . 참조 테이블이 시트 전체를 차지하는 경우 또는
      참조 테이블이 A1 셀부터 시작하는 경우에는 INDIRECT 함수
    . 참조 테이블이 시트의 중간 어느 부분에 있는 경우에는 INDEX 함수가 유리
  - 참조할 행번호나 열번호가 기준위치의 위쪽, 왼쪽을 참조해야 하는 경우에는
    위치 값을 음수로 지정할 수 있는 INDIRECT나 OFFSET 함수가 유리함
    예제) http://www.officetutor.co.kr//board/DType/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=123515&page=
 
[불량 게시물 신고]  
조삿갓강좌 내용을 잘 읽어보시면 알 수 있듯이
질문자께서 올리신 파일의 경우
위 4인방 중에서 가장 똑똑하고 경제적인
VLOOKUP을 안 쓸 이유가 전혀 없습니다.

그런데 왜 그걸 마다하는 건지
제가 이해가 안됩니다만...
01-26 (02:13)
삭제 ■신고
조삿갓혹시 '백번 안쓰고 해결'이라는 의미가
 위 강좌에서 표현한 '노가다'를 하지 않고 해결해 달라는 뜻이었나요?
그렇다면, 잘 읽어 보시면 해결이 될 겁니다.
똑똑한 수식 하나만 잘 설계해서 입력한 후
 걍 복사해서 쓰시면 되니까요.
머리를 잘 쓰시면....

단, 주소지정의 혼합참조 방식에 대해서
공부를 하셔야 합니다.
언제 시간 여유가 나면 이것(주소지정 방식)에 대한 강좌도
포스팅할까 생각 중입니다만...
01-26 (10:58)
삭제 ■신고
야생토끼와! 감사합니다!!!01-31 (17:37)
삭제 ■신고
조삿갓한가지 유의사항
앞에 설명했듯이 VLOOKUP 함수의 경우 중복키가 존재하면
그 중에 제일 처음 만나는 것만 찾아옵니다.
다만, 네 번째 인수를 TRUE로 지정하여 '유사값 찾기'로 한 경우에는, 제일 나중에(즉, 색인키로 지정한 값보다 큰 값을 만나기 직전의 위치) 있는 값을 찾아오는 특징을 갖고 있습니다.
04-04 (12:15)
삭제 ■신고
        
  

작성일 : 2018-01-26(01:00)
최종수정일 : 2018-01-26(11:16)
 


 ◎ 관련글

  제 목   작성자   날짜
VLOOKUP을 백번 안쓰고해결 하는 방법을 알려주세요... 야생토끼 2018-01-25
[강좌] 테이블 참조 4인방: 끝판왕! 조삿갓 2018-01-26
[외전] MATCH, HLOOKUP, LOOKUP 조삿갓 2018-01-26
[RE]VLOOKUP을 백번 안쓰고해결 하는 방법을 알려주세요... 이성 2018-01-25