153_엑셀 가상분석 데이터 표

153_엑셀 초급 강좌 – 데이터 분석 (4)

솔직히 엑셀 데이터 표는 왜 가상 분석 탭에 있는지를 잘 모르겠습니다. 엑셀의 데이터 표는 물론 익숙해 지면 편하지만 기존의 상대 참조, 절대 참조를 이용하는 것과 비교해서 큰 이점을 못 찾겠더라고요. 엑셀에는 모든 셀에 같은 값을 입력 하려면 영역을 지정하고 컨트롤 + 엔터를 누르면 됩니다. 그러나 같은 수식은 적용이 안됩니다. 표 형태의 데이터에서 모든 셀에 같은 수식을 넣는 것을 데이터 표라고 하고 데이터가 반드시 데이터 표를 이용하기 위한 형태로 되어 있어야 합니다.

이번 포스팅은 엑셀 리본 데이터 탭의 데이터 표에 관한 내용입니다.

 

153연습파일.xlsx

153연습파일_완성본.xlsx

1. PMT 함수와 상대 참조 절대 참조

   PMT함수는 예전 목표값 찾기에서 자세히 소개한 적이 있습니다. 다시 복습하면 PMT함수는 납부 기간과 이자율로 월 납부 금액을 계산해 주는 함수 입니다. 이 함수를 이용해 월 상환액을 구해보고 데이터 표를 적용하지 않고 어떻게 표에 적용하는지 알아 봅시다. 오래간만의 상대 참조 절대 참조 복습편 입니다.

   PMT 함수의 논리 입니다. 연이자율 이기 때문에 5%를 12로 나눈 것이며 납부기간은 36개월, 그리고 원금은 1000000입니다.

   

    

    

   월 29971원씩 3년을 갚으면100만원을 갚습니다.

   

    

   여러 개의 원금과 이자율에 대해서 상환액을 비교해 보고 싶다면 표를 만드는 것이 제일 효과적 이겠죠?

   이렇게 표를 만들어서 PMT함수를 적용한다면 3가지의 절대 참조, 즉 행 절대 참조, 열 절대 참조, 행열 절대 참조가 모두 사용됩니다.

   

    

   연 이율은 행으로 이동하면 안되므로 D4셀은 행 절대 참조 입니다.

   원금은 열 방향으로 이동 하면 안되므로 C5셀은 열 절대 참조 입니다.

   납부기간은 절대 바뀌면 안돼는 셀이므로 숫자로 36을 치거나 셀로 지정할 경우 행 열 절대 참조 입니다.

   만약 행 열 절대 참조가 헛갈리시고 F4를 누르는게 귀찮다면 사용하는 함수가 데이터 표 입니다.

    

   이렇게 채우기 핸들로 채우면 표에 대한 값을 가상 분석 데이터 표를 사용하지 않고 구할 수 있습니다.

   

    

2. 가상 분석 데이터 표 사용하기

   가상 분석 데이터 표를 사용하기 위해서는 반드시 데이터가 지금 보시는 것과 같은 표 행태로 저장되어 있어야 하며 왼쪽 상단의 셀에는 반드시 마스터 수식이 들어가 있어야 합니다. 사진을 보면서 설명 드리겠습니다.

    

   왼쪽 위에 반드시 표 전체에 들어갈 수식이 들어가 있어야 합니다. 그리고 그 수식은 이미 샘플 셀을 참조 하고 있어야 합니다. 표 전체를 선택한 상태에서 데이터 표를 클릭 합니다.

   

    

    

   행 입력 셀의 의미는 지금 우리 표에서 사용되는 행이 마스터 수식에서 어떤 셀에 해당하는 값인지 정해 주는 것 입니다. 마스터 수식의 연이자율은 C1셀 입니다. 열 입력 셀은 원금 즉 C3셀 입니다.

   

    

    

   상대 참조 절대 참조를 사용했을 때와 같은 값을 얻었습니다.

   

    

   무엇이 더 편한 가는 각자 판단할 일이 겠지만 상대 참조 절대 참조에 더 익숙한 제이킴은 거의 사용하지 않는 기능입니다. 왜 이게 가상 분석 툴에 있을까요? 혹시 특별한 활용법을 알고 계신 분은 좀 알려주시면 대단히 고맙겠습니다.