엑셀 피벗(Pivot) 새로고침 쉽게하기(피벗데이터 범위설정 자동화)

  엑셀의 가장 강력한 기능을 뽑으라면 바로 PIVOT이라 말해도 과언이 아닙니다. 그만큼 피벗의 쓰임은 많지만, 이 피벗은 데이터가 고쳐졌을 때 계속 새로고침이 필요합니다. 근데 데이터 부분에 맨 끝 하단 부부분이 추가되면 일일이 다시 범위설정을 해줘야 하지만, 오늘 소개해 드릴 함수를 이용해서 범위를 지정하면 매번 범위지정 필요 없이 새로고침으로 할 수가 있어서 소개하는 글을 써보고자 합니다.

 

 

 초보자 분들을 위한 설명

  보통 피벗은 피벗테이블을 우클릭하고 R 키를 누르면 알아서 데이터의 최신본을 반영해 새로고침(ALT+F5)이 됩니다. 그러나 원본데이터에 중간을 수정하는 것이 아닌 라인을 추가하여 새롭게 데이터가 추가되면 피벗은 원래있던 범위를 벗어나기에 이 데이터를 인식하지 못하여 새로고침을 해도 반영이 되지 않습니다.

 

  그래서 이때는 ALT + J + T + I 를 눌러 피벗테이블이 참조하는 데이터 원본을 전체범위로 다시 씌워주면 문제없이 작동합니다. 근데 여러사람이 작업하거나 엑셀을 잘 모르는 사람이 같이 파일작업을 해야 한다고 할때, 이 매번 범위를 씌워줄 필요없이 이름관리자라는 옵션을 통해서 원본데이터의 라인 추가시에도 문제없이 반영되게 하는 절차입니다.

 

 이름관리자 활용

  범위지정을 매번 하지 않으려면 이름 관리자 옵션을 통해 정의해 줍니다. 아래 화면처럼 수식탭에서 이름 관리자를 클릭하면 아래와 같이 창이 하나 뜨게 되어 있습니다. 여기에서 새로 만들기를 눌러주시면 아래와 같이 이름과 참조대상을 지정하는 창이 하나 뜹니다.

 

이름관리자를 통해서 이름을 지정해야 한

 

  여기서 이름은 데이터 참조대상이 되는 Sheet 명과 동일하게 입력하시는 것이 좋습니다. 이는 나중에 헤깔릴 수도 있는 부분이라서 이렇게 하는 것이고 꼭 같을 필요는 없습니다. 보통 원본데이터 쪽을 피벗의 주 데이터로 사용하는 경우가 많으니 "Raw", "DB", "원본", "Data" 등을 사용하시면 좋을 것 같네요

 

그다음 참조대상에는 수식을 입력해 주셔야 하는데 아래 수식을 입력하시면 됩니다.

=OFFSET(DB!$A$2,0,0,COUNTA(DB!$A:$A),COUNTA(DB!$2:$2))

 

  위에 박스를 붙여넣기 하시고 원데이터가 A2세부터 시작하는 분이 아니라면 약간의 수정이 필요합니다. Offset 함수를 아시는 분들이라면 조금 더 이해가 쉽게 될 수도 있습니다. 수식 안을 차례대로 보면 DB!라고 되어있는 부분은 원데이터가 들어가 있는 sheet를 말하고, A2셀은 데이터의 처음 시작되는 셀이 됩니다. 그다음 첫 번째 Counta A:A부분은 데이터가 A부터 시작한다는 의미, 뒤에 Counta 2:2 부분은 데이터의 맨 윗줄의 헤더 부분들이 2행에 나열되어 있다는 의미입니다.

 

 

  따라서 원 데이터의 경우 A2셀 부터 시작하는 데이터라는 의미이며, 실제로 이 함수를 그대로 활용하고 싶으신 분들은 A2부터 데이터를 작성해 놓으시면 수정 없이 활용 가능합니다.

 

피벗 테이블 원본변경

 

  이제 다 끝났습니다. 피벗에 아무셀이나 선택하고 ALT + J + T + I 를 누르면 위와 같은 테이블 원본변경 옵션이 뜹니다. 이때 피벗 데이터의 원본 변경을 범위지정하는 것이 아니라 위에 사용했던 이름관리자의 이름을 입력해주시면 데이터의 라인이 추가되더라도 항상 포함하여 새로고침이 이루어지기 때문에 편리하게 사용이 가능합니다.

 

 함께 읽으면 좋은 글

엑셀에서 PPT로 붙여넣기 할때 엑셀 서식 유지하는 방법

엑셀 학년, 반, 번호 5자리로 만들기

엑셀(Excel) Offset 함수 사용법과 응용방법

엑셀 드랍다운 및 종속된 드랍다운 목록 생성방법