엑셀 시트 이름을 변경 하면 사용된 함수는 시트 이름을 찾지못해 오류로 수식이 깨져서 불편한 적이 많았습니다.
저 같은 경우는 특히 vlookup 함수나 sumif 함수등을 아주 자주 사용하는 이들 함수는 다른 sheet의 참조가 대부분 이죠.
그런데 작성을 한후 나중에 sheet명을 순서대로 다시 네이밍을 했더니 수식이 다 깨져서 난감한 적이 많았습니다.
그래서, 함수적용시 참조된 시트명을 변경하면 자동으로 변경 시트명이 적용되게 하는 방법을 찾아 보게 되었는데요.
그결과 indirect 함수를 사용하면 해결 됨을 찾게 되었지요.
그래서 오늘은 indirect 함수를 이용한 vlookup, sumif, countif 함수등에 sheet명을 연동해서 적용 되게끔 하는 법을 포스팅 하겠습니다.
1. 시트이름 변경시 발생 했었던 문제점
1-1. 제가 작성시 문제인식 하였던 부분
- 현재 시트는 총 4개 있고 정리, 1월, 2월, 3월 이렇게 시트 이름을 설정한 상태 입니다.
- 현재 적용된 함수 : VLOOKUP($B4,’1월’!$B:$H,4,FALSE)
- 정리 시트에 해당일자에 맞는 1월, 2월, 3월의 각 일자에 해당하는 판매량을 찾아 오는 수식이죠.
- 하지만 월을 갑자기 영문으로 변경해야 했는데 그냥 변경하면 수식은 다 깨지죠.
- 그래서, 하는수 없이 일일이 수작업으로 각 월의 수식내 시트 이름을 영문으로 변경후 드래그 하여 적용 하고 있었어요.
- 이게 수식적용 항목이 몇개 없을때는 그냥 해도 되는데 점점 많아지니 감당이 안됩니다.
- 나중에는 어디를 수정해야 하는지 찾기도 어려울 때도 있어요.
1-2. 시트 이름 변경 하면 자동으로 연결되는 방법
- 기존 VLOOKUP 함수는 해당 월의 시트에서 대응하는 일자 데이터를 가져오게 되어 있죠
- 이때, 월에 대응하는 시트이름이 변경되면 오류가 생기는데 이를 자동으로 하려면
- C3에 시트이름과 같게 적어 주면 됩니다. 즉, 1월은 시트이름 1월의 데이터를 가져오는 형태 이죠
- 그리고 나서, VLOOKUP($B4,”1월” … 여기서 “1월” 대신에 INDIRECT 함수를 이용 하면 C3 값을 시트이름으로 적용 됩니다.
- 나중에 시트이름을 변경 하고 싶으면 C3에 변경된 시트이름과 같게 수정해 주면 나머지 수식은 자동 연동 됩니다.
- 즉, 오류가 생기지 않고 각 수식의 시트 이름이 바로 반영되는 것이죠
이제 INDIRECT를 제가 어떻게 적용 했는지 좀 더 구체적으로 정리해 보겠습니다.
2. 시트 이름 변경이 수월한 INDIRECT 함수 설명
- 일단은 VLOOKUP 함수를 작성 합니다. =VLOOKUP($B4,’1월’!$B:$H,4,FALSE).
- 그 다음에 작성된 기존함수에서 ‘1월’!$B:$H 이것만 INDIRECT 함수로 변경 하면 됩니다.
- 변경 방법은 INDIRECT(“‘”&C$3&”‘!$B:$H”) 이렇게 하면 되는데.
- 설명을 하면 일단 맨앞에 INDIRECT를 치고 괄호를 연 다음 기존 1월에 해당하는 부분을.
- 양 따옴표로 ‘를 감싸줍니다. 왜냐하면 기존함수의 1월 앞에 ‘가 있으니까요.
- “‘” 이렇게 된것 옆에 &를 붙여 줍니다. 그러면 앞의 문자와 뒤의 문자가 이어 집니다.
- “‘”&C$3 이어서 시트 이름이 있는 C3 셀주소를 붙여 줍니다. 이때 수직 절대주소를 $로 적용 해야죠.
- “‘”&C$3& 다시 &를 붙여어 문자가 이어지게 하고.
- “‘”&C$3&“‘” 기존 1월의 뒤에 있는 ‘를 붙이기 위해 ‘”‘를 붙이면 됩니다.
- “‘”&C$3&“‘” 이렇게 작성된 것은 기존의 ‘1월’ 과 같은 효과를 냅니다.
- 마지막으로 참조시트 즉, 1월 시트의 지정된 범위를 그대로 붙여 줍니다.
- 그러면 최종 INDIRECT 함수 부분은 다음과 같이 되는 것이죠.
- INDIRECT(“‘”&C$3&”‘!$B:$H”).
- 이제 끝입니다. 저 변경 함수를 기존 함수의 ‘1월’!$B:$H 를 대신해서 넣어주면 끝.
3. 다른 시트 참조하는 함수들과 INDIRECT 적용법
그러면 주로 제가 유용하게 사용하는 INDIRECT 적용하는 함수별 사례를 정리해 보겠습니다.
3-1. 앞에서 다룬 vlookup 함수
이 vlookup 함수는 제품코드를 기준으로 해서 다른시트에 있는 기준정보의 값을 가져올때 사용 하고 있습니다.
다음을 보세요.. 제가 사용하는 엑셀 표는 내용측면에서 공개할수 없어 임의로 유사하게 작성해 보았습니다.
아래를 보시면 품명은 품명시트에서 정보(품명)을 가져 옵니다. 규격은 규격 시트에서 가져 와야 하는데
수식을 드래그하여 붙여 넣으려니 시트명을 일일히 수정해 주어야 하는 불편이 생깁니다.
이렇게 위와 같은 상황에서 indirect를 쓰면 한번에 해결 가능 합니다. 즉, 다음과 같이.
3-2. sumif 함수와 countif 함수
sumif 함수도 마찬가지 입니다. 다음을 보시면 sumif 함수를 드래그 한 후 참조 시트명을 모두 변경 해 주어야 합니다.
이제 indirect 적용을 해 봅니다.
이처럼 쉽게 사용이 가능합니다.
4. INDIRECT 함수 활용시 주의사항
이처럼 편리한 함수 이지만 잘못 사용하면 오히려 더 꼬일수도 있으니 아래 사항을 주의해서 사용 하시기 바랍니다.
- indirect는 행이나 열을 추가해도 자동으로 추가한 만큼 연동해서 변경되지 않습니다. 즉, 값이 틀어질수 있습니다.
- 만일 참조시트가 같은 파일이 아닌 다른 파일 (외부 파일)일 경우는 그 파일이 열려 있어야 됩니다. 그렇지 않으면 오류 발생 #REF 표시됨.
- 이 함수는 휘발성 함수라 계산 속도가 느려질 수 있으니 데이터가 많을 경우 주의해 주세요.
- INDIRECT 함수를 타이핑 하다 보면 따옴표를 빠트리거나 & 기호를 잊는 경우도 있으므로 세심히 주의해서 작성 필요 합니다.
- 만일 빠트린다면 수식적용이 안되서 한참 헤멜수도 있습니다.
이처럼 위의 사항들만 주의해서 사용하면 INDIRECT 함수는 아주 도움이 되는 함수 이니 잘 활용 하시기 바랍니다.
이상 저의 엑셀 함수 활용기를 포스팅 하였습니다.
끝까지 읽어 주셔서 감사합니다. 다음에는 좀 더 활용성 좋은 내용을 준비하겠습니다.
※ 본 포스팅은 제가 직접 엑셀을 활용 하면서 알게된 노하우나 유용한 정보를 기준으로 작성 하였습니다.
특히, 엑셀에서 가장 많은 비중을 차지하는 함수활용에 대한 정보는 앞으로도 지속적으로 포스팅 할 예정입니다.
본 포스팅의 이미지는 제각 직접 예시로 작성한 엑셀의 화면을 캡쳐해서 올린 이미지 입니다.
문의사항이 있으실 경우 하단의 이용약관에 있는 메일주소로 연락 하시거나 댓글로 문의해 주시면 감사 하겠습니다.