엑셀 시트 복사할때 다른 시트나 다른 파일 참조 방지하는 방법

안녕하세요! 

 

엑셀을 하나 잘 만들어 놓으면 계속 복사해서 쓰게 되죠. 그러다 보면 복사할때 원래 있던 곳의 데이터를 참조하는 경우가 자주 발생해서 새로 만드는 시간이나 참조를 바꾸는 시간이나 비슷하게 걸릴때도 있습니다. 이것을 방지하고 복사해서도 간단하게 바꿀 수 있는 방법이 있는데요.  지금부터 그 방법에 대해 알아볼께요.

 

offset과 indirect함수를 이용합니다.

1. 아래 그림처럼 같은파일 내에서 다른 시트에 값을 참조하여 vlookup을 했다면 아래와 같은 화면과 함수를 볼 수 있습니다.

 

[같은 파일 참조]

2. 하지만 다른 파일을 참조할경우 파일 연결이 되어버리는데요. 아래 그림처럼 파일을 참조하게 됩니다.

 

[다른 파일 참조]

 

3. 이런경우를 대비하여 우리는 offset함수와 indirect 함수를 사용하여 다른 파일 참조를 방지할 수 있습니다.

 

 - 우선 OFFSET함수에 대해 알아볼 필요가 있는데요. 오프셋함수는 

    OFFSET(시작셀주소,아래로 이동, 오른쪽 이동, 아래로 범위, 오른쪽으로 범위)입니다. 

    그림으로 표현하자면 아래와 같습니다.

 

 - 이것을 바로 대입해봅시다. 여기서 참조시트명을 적어줌으로서 범용적으로 사용이 가능합니다.

 

[다른 파일 참조 방지 함수]

① 이것은 어떤 시트를 참조할 것인가를 나타내는 것이며, INDIRECT로 해당 셀을 바로 참조할 수 있습니다. 풀이하자면 

    INDIRECT(INDIRECT("'"&$C$1&"'!B1") 함수는 INDIRECT('Sheet1'!B1)과 같은 이야기입니다.

    따라서 범위를 지정하는 함수인 OFFSET의 시작점은 'Sheet1'!B1 이라는 것으로 풀이됩니다.

② 아래로 0칸 이동이므로 제자리입니다.

③ 오른쪽 0칸 이동이므로 제자리입니다.

④ 현재 셀 포함 아래 1000줄이 범위입니다.

⑤ 현재 셀 포함 오른쪽 2줄이 범위입니다.  

 

- 그림으로 보면 아래와 같이 범위가 설정되었습니다.

[OFFSET/INDIRECT 함수 적용 범위]

 

4. 바로 다른 파일에 복사하여 적용해보겠습니다.

 

[다른 파일에 복사 적용]

 ① 그대로 복사하였기 때문에 처음에는 Sheet1로 되어 있고, 

 ② Sheet1은 해당시트기 때문에 당연히 오류가 나는것입니다.

 ③ 과일가격시트를 생성하고, 데이터를 넣은 후 참조시트명을 똑같이 참조해야할 시트로 바꿔줍니다.

 ④ 즉시 답이 나오게 됩니다.

 

이렇게 작업을 한다면 한개의 시트로 여러개의 데이터시트를 참조해서 볼 수도 있답니다. 

여기까지 여러분도 성공하시길 바랄께요! 화이팅!