엑셀 - 주차별 기준값 자동으로 변형(시트이름 가져오기)

제목

안녕하세요!

 

이번에는 제가 업무에 사용하는 간단한 팁을 알려 드리고자 합니다.

 

설명하기는 조금 어려운데, 저는 주차별로 여러 사람들의 데이터를 모아서 얼마를 팔 것인가를 데이터로 정리하고 있습니다.

 

물론 사람들은 시스템에 팔고자 하는 수량을 직접 넣고 있고, 저는 매주 입력 해 주신 수량을 다운로드 받아서 엑셀로 보관하면서 주차별로 수량변화 트렌드를 분석하고 있습니다.

 

이 데이타에 대해서 필터를 사용하여 분석하는 경우도 있지만, 양식이 동일한 경우 필터 새로고침 없이 자동으로 리포트가 나오는 방법을 선호하고 있습니다.

 

유사한 예시로 아래와 같이 SUMIFS를 사용하여 주차별로 제품 수량을 집계하고 증감을 체크하고 있습니다.

report

 

제가 알려 드리고자 하는 팁은 빨간색 시트의 이름에 따라서 파란색에 있는 주차의 이름을 자동으로 변경시키는 방법입니다.

 

해당 주차에 자료를 아래쪽에 붙여 넣기를 하면 당주와 과거 3주 Data가 자동으로 집계가 되게 설정하였습니다.

 

W04 주차에서 자료 작성 시 주차에 대한 값을 설정하는 방법입니다.

 

="W"&RIGHT(CELL("filename",A1),2)

 

W04의 경우 가장 앞에선 "W'를 Text로 입력하였고,

CELL("filename",A1)을 사용하여 Sheet이름의 오른쪽 두 글자인 '04'를 가져와서 "W04" 값을 출력하게 하였습니다.

 

="W0"&RIGHT(CELL("filename",A1),2)-1

 

W03의 경우도 W04와 유사한데, Sheet 이름의 '04'를 가져오게 되면 숫자로 인식되어 '4'로 인식이 됩니다.

그래서 가장 앞에 "W0"으로 입력을 하고, '04'에서 '-1'을 실행하여 숫자 '3'을 만든 다음 합쳐서 "W03" 값을 출력하게 하였습니다. 

 

W02, W01 역시 유사하게 -2, -3 을 입력하여 과거 주차로 입력되게 하였습니다.

결과

그런데, 이렇게 셋팅할 경우 W11주차가 되었을 때 한주 전의 결과값이 "W10"이 아니고 "W010" 이 됩니다.

"W0"으로 인하여 결과값이 이상이 생기는 건데, 예전에는 그냥 임의로 "W0" -> "W"로 고쳐서 사용하였습니다.

 

그런데 이 방법은 임시방편으로 새해마다 다시 고쳐줘야 하는 불편함이 있습니다.

 

이러한 불편함을 제거하고자 TEXT 수식을 사용하여 1자리 숫자를 2자리 숫자로 바꾸어 적용하였습니다.

 

아래 변경 후의 수식으로 입력하시면 1년 언제나 바꿀 필요 없이 사용이 가능합니다.

변경

이상으로 조금 복잡하지만 한 번 셋팅 해 두면 편리한 제목 입력 방식을 알아봤습니다.

 

예시는 제품 하나만 적었는데, 저는 실무에서 부서별, 제품상위분류별, 제품별 이렇게 3개의 리포트로 구성하여 주차별 수량변동을 파악하고 변동이 큰 항목에 대해서 원인 파악을 하고 있습니다.

 

물론 제가 하는 방법 말고도 여러 가지 방법이 있을 겁니다.

 

여러 방법을 알아보시고 더 효율적이라고 생각되는 방법을 적용하셨으면 합니다.

 

제 전달 능력이 미흡하여 전달이 잘 안 될 것 같아서 걱정입니다.