110_엑셀 INDIRECT 함수로 다른 시트 값 불러오기 – 함수 중급 (37)

INDIRECT함수를 처음 봤을 땐 논리는 쉬운데 도무지 활용도를 찾지 못했습니다. 다른 함수로 간단히 대체 가능하기 때문이죠. 근데 이 INDRECT함수는 여러 시트의 데이터를 한곳에 모을 때 기똥차게 활용 됩니다. 단 제가 오늘 소개해 드리는 방법은 시트의 이름을 한국어로 했을 땐 바로 사용이 불가능하고 문자열 함수와 조합해서 사용해야 합니다. 그러나 시트의 이름이 영어라면 바로 적용하실 수 있습니다. (정정 하겠습니다. 오피스 버전 마다 다른 것 같습니다. 예전에는 Trim 함수로 공백을 제거해 줘야 하는데 지금은 그냥 됩니다. 한글시트로 바꾼 추가 연습파일을 올립니다.) 

함수 사용법

INDIRECT (셀주소) 는 그 셀에 해당하는 데이터 값을 반환합니다. 예를 들어 셀주소가 B2라면 B2에 있는 데이터 값을 반환합니다.

110연습파일.xlsx

110연습파일_완성본.xlsx

110연습파일_완성본_한글시트.xlsx

  1. INDIRECT 함수 이해하기

    INDIRECT는 직역하면 "간접적인" 이란 뜻 정도가 되겠습니다. 직접 값을 구하지 않고 주소를 받아와서 해당 주소를 찾아가 값을 구한다는 것이죠. 한번만 보면 이해 합니다.

     

    함수의 인수로 문자열 즉 B2가 문자열로 들어갑니다.

     

     

    19410을 얻게 되는 과정입니다. INDIRECT함수가 D4셀을 찾아가니 D4가 B2셀을 찾아가라 합니다. B2에 갔더니 19410이 있네요.

     

    가장 널리 쓰이는 INDIRECT의 사용용도, 즉 문자열을 받고 또는 문자열과 조합해서 원하는 곳의 데이터를 가져 옵니다.

     

  2. INDIRECT 함수의 활용, 데이터 한군데 모으기

    엑셀 데이터 시트 간의 연동하는 법은 모르시면 제 이전 데이터를 참고 하셔도 되고 이번에 기본 개념은 설명 드리겠습니다.

     

    지금 WEST시트에 총합계는 F6셀에 있습니다. 그리고 모든 시트에 같은 위치에 합계가 있죠. 보통 월별로 시트를 구분해서 엑셀을 쓰는 분들한테는 흔한 구조 일 거에요.

     

     

    다른 시트에서 WEST시트 데이터 F6를 가져오는 방법입니다. WEST가 시트이름 !찍고 셀이름 입니다.

     

     

    이렇게 가져와서 채우기 핸들을 적용하면 안됩니다.

     

     

    채우기 핸들은 계속 WEST시트만 참조하지 NORTH시트를 참조하지 않습니다.

     

     

    지금은 4개의 시트지만 12개의 시트라면요? 일일이 시트를 찾아가면서 연동하긴 귀찮습니다. INDIRECT함수를 이용해서 한 시트 안에서 끝낼 수 있습니다.

    INDIRECT함수를 이용해서 텍스트를 조합 했습니다. 즉 ""안에 있는 F6값은 절대 바뀌지 않고 A열에 있는 텍스트는 채우기 핸들을 이용하면 바뀝니다.

     

     

    자 이렇게 간단하게 구했습니다. 시트의 수가 많을수록, 또는 다른 파일과 연동되어도 활용이 가능합니다.

    이상으로 제이킴이 활용하던 INDIRECT함수의 사용법 이었습니다. 만약 한글로 시트 이름이 되어서 에러가 날 경우 확인을 해 보시면 한글 시트를 불러 올 때 공백이 있습니다. 이때는 TRIM함수로 공백을 제거해 주면 됩니다. 오피스 2016 현 버전에서는 한글 시트도 똑 같이 작동하고 있습니다. TRIM함수에 대한 내용은 함수편을 참조하세요. TRIM은 잘라내다 라는 의미이며 엑셀 함수에서는 공백 제거에 사용 됩니다. 비디오를 줄이는 것도 TRIMMING이라고 하고 사진 사이즈 줄이는 것도 TRIM입니다. 


+ Recent posts