엑셀은 가상 분석(What-If분석, 조건 분석이라고도 합니다) 기능을 사용할 수 있습니다. 가상 분석을 이용하면 아직 일어나지 않은 일이나, 변수가 있는 상황에서 값을 예측할 수 있습니다. 엑셀의 가상분석은 크게 데이터 표, 목표값 찾기, 해찾기, 시나리오 등 4가지 종류가 있습니다.
이번 강좌에서는 데이터 표를 이용해서 가상 분석을 하는 방법에 대해서 배워보겠습니다.
변수가 하나인 데이터 표
데이터 표는 수식의 특정 값을 변경하면 수식의 결과가 어떻게 달라지는 지를 나타내는 셀 범위입니다. 즉, 여러 상황들을 가정해서 그 결과를 미리 볼 수 있는 기능입니다. 따라서 변수가 필요하게 됩니다. 데이터 표는 변수가 한 개인 표와 변수가 두 개인 표로 나누어 집니다. 먼저 변수가 한 개일 때의 데이터 표를 활용하는 방법에 대해서 알아봅시다. 한 개에 10원짜리 붕어빵이 있습니다. 이 붕어빵 5개를 사면 50원이 되겠지요. 그렇다면 붕어빵을 10개, 20개, 30개를 샀을 때는 각각 얼마씩이 필요할까요. 이런 경우 데이터 표를 만들면 표 하나에 정리할 수 있습니다. 다음 데이터를 입력합니다.
붕어빵의 값은 B2에 지정되어 있고, E2셀에는 붕어빵을 10개를 샀을 경우의 값을 계산(=B1*B2)을 해두었습니다. D3:D5에 걸쳐 가상의 개수를 입력하고, E2에 입력한 수식을 참고로 엑셀이 각각의 개수와 값을 차례로 대입하면서 표를 만들어주는 원리입니다. 구해지는 값은 E3:E5에 걸쳐서 표시될 것입니다.
D2:E5셀 범위를 선택한 후 [데이터-가상분석-데이터 표]를 선택합니다. 그러면 데이터 표 대화상자가 나타나는데 열 입력셀에 B1을 선택합니다. 현재 데이터 구조가 열 구조이므로 열 입력셀을 선택한 것입니다.
[확인]을 누르면 다음 그림처럼 E3:E5에 D3:D5의 값을 각각 대입해서 값이 표시됩니다. 수식 입력줄을 보면 배열 수식이 만들어진 것을 알 수 있습니다.
Tip - 데이터 표의 원리
데이터 테이블이 만들어지는 원리는 다음과 같습니다. 우선 변수들이 열 입력셀인 B1에 차례로 대입됩니다. 그 다음 그 변수가 E2 셀의 수식에 의해서 표에 하나씩 계산되어 출력되는 것입니다. 계산된 값을 선택한 후 수식입력줄을 보면 TABLE이라는 함수를 사용한 배열식이 사용되었음을 알 수 있습니다.
변수가 두 개인 데이터 표
이번에는 변수가 두 개인 데이터 표를 만들어 보도록 하겠습니다. 앞의 예에서는 붕어빵을 사는 개수가 하나의 변수 였습니다. 변수가 두 개인 경우는 변하는 값이 행과 열 두군 데에 위치하게 됩니다. 데이터 표를 만드는 방법은 동일합니다.
아래와 같이 데이터를 입력합니다. 이자율에 따른 대출금 상환 금액을 알아보는 데이터를 입력했습니다. B10:B12에 기본 데이터가 입력되어 있고, D9셀에 이 기본 데이터를 이용한 월별 상환 금액을 PMT 함수를 사용해서 구했습니다. 천만원을 이율 9.5%로 36개월간 상환한다면 월 320,329원을 상환하게 되는군요. 이 경우 상환 기간을 6개월, 12개월, 18개월로 할 경우, 그리고 이율을 9%, 9.25%, 9.5% 로 하면 월별 상환금이 얼마나 될까를 가상으로 표를 만들어보는겁니다. D9셀에는 =PMT(B10/12,B11,-B12) 수식이 입력되어 있습니다.
어떻게 하면 될까요? 방법은 앞의 붕어빵 분석과 동일합니다. 다만, 이 경우 이자율과 상환기간이라는 2개의 변수가 있다는 것이 차이가 있는 것이죠. D9:G12 범위를 모두 선택한 후 역시 [데이터-가상분석-데이터표]를 선택합니다. 그 후 행(기간)과 열(이자율)의 입력값을 각각 선택합니다.
아래와 같이 데이터 표가 완성되었습니다. 역시 TABLE을 이용한 배열 수식이 사용된 것을 알 수 있습니다.
이렇게 데이터 표를 이용하면 변수를 이용해서 일목요연하게 가상의 비교표를 만들 수 있습니다. 우리는 앞서 배열에 대해서 자세히 배웠기 때문에 데이터 표가 만들어지는 원리도 비교적 쉽게 이해할 수 있었습니다.
계속해서 목표값과 해찾기를 통해 다른 가상 분석의 방법으로 배워보도록 하겠습니다.
