시나리오 세 개를 따로 복사해서 만들다가 데이터 표라는 걸 알게 됐습니다

내년 예산안을 짤 때, 매출 성장률을 5%, 10%, 15%로 각각 가정했을 때 순이익이 어떻게 달라지는지 비교해서 보여달라는 요청을 받은 적이 있습니다. 그때 저는 시트를 세 개 복사해서, 각 시트마다 성장률 값만 다르게 넣고 결과를 따로 캡처해서 보고서에 붙이는 식으로 작업했습니다. 성장률 기준이 하나 더 추가되거나 바뀔 때마다 시트를 또 복사해야 해서 번거로웠는데요. 나중에 재무팀 선배가 "그거 데이터 표 하나로 끝나는데"라고 알려줘서 그 자리에서 배워봤습니다. 오늘은 이 데이터 표(What-If 분석) 기능을 정리해보겠습니다.

데이터 표가 하는 일

데이터 표는 하나의 수식에 여러 입력값을 대입했을 때 나오는 결과들을 한 번에 표로 뽑아주는 기능입니다. 이전 글에서 다룬 목표값 찾기가 "결과 하나를 정해두고 입력값 하나를 거꾸로 찾는" 거였다면, 데이터 표는 반대로 "입력값을 여러 개 바꿔가며 결과가 각각 어떻게 나오는지 한눈에 비교"하는 용도입니다.

사용 순서 (단일 변수, 세로로 나열하는 경우)

1. 성장률 값을 입력하는 셀 하나와, 그 값에 따라 순이익이 계산되는 수식 셀이 이미 있어야 합니다.
2. 성장률 값들(5%, 10%, 15%)을 세로로 나열할 빈 열을 만듭니다.
3. 그 바로 오른쪽 위 칸에, 결과를 참조하는 수식(=순이익이 계산되는 셀)을 넣습니다.
4. 성장률 값과 수식이 포함된 전체 범위를 선택합니다.
5. [데이터] 탭 → [가상 분석] → [데이터 표]를 클릭합니다.
6. "열 입력 셀"에 원래 성장률을 입력하던 셀을 지정합니다. (세로로 나열했으므로 열 입력 셀)
7. 확인을 누르면, 각 성장률에 해당하는 순이익이 자동으로 옆 칸에 채워집니다.

저는 처음 이 단계를 따라 했을 때, 시트 세 개를 복사해서 각각 확인하던 걸 표 하나로 한 번에 볼 수 있다는 게 신기했습니다. 성장률을 하나 더 추가하고 싶으면 그냥 목록에 숫자 하나만 더 적으면 자동으로 계산되니, 시트를 또 복사할 필요가 없어졌습니다.

실무에서 차이 나는 지점

두 가지 변수를 동시에 바꿔야 할 때

제가 다음에 맞닥뜬 상황은 성장률뿐 아니라 비용 절감률도 같이 바꿔가며 비교해야 하는 경우였습니다. 이럴 땐 변수를 가로와 세로에 각각 나열해서 "이중 변수 데이터 표"를 만듭니다.

1. 왼쪽 맨 위 칸에 결과를 참조하는 수식을 넣습니다.
2. 그 아래로 성장률 값들을 세로로 나열합니다.
3. 그 오른쪽으로 비용절감률 값들을 가로로 나열합니다.
4. 전체 범위를 선택하고 [데이터 표]를 실행합니다.
5. "행 입력 셀"에는 비용절감률 입력 셀을, "열 입력 셀"에는 성장률 입력 셀을 각각 지정합니다.

이렇게 하면 성장률과 비용절감률의 모든 조합에 대한 순이익이 격자 형태로 한 번에 채워집니다. 처음 봤을 때는 표가 꽤 복잡해 보였는데, 막상 만들어보니 "성장률이 낮아도 비용을 많이 줄이면 이 정도는 나온다"는 식의 비교가 한눈에 들어와서 보고서에 그대로 활용하기 좋았습니다.

추가 팁

• 데이터 표로 채워진 값들은 수식입니다. 원본 성장률 셀의 값을 바꾸면, 데이터 표의 결과도 전부 자동으로 다시 계산됩니다. 제가 시트를 복사해서 쓰던 방식은 원본이 바뀌면 전부 다시 만들어야 했는데, 이 차이가 가장 크게 느껴졌습니다.
• 데이터가 많아지면 계산이 느려질 수 있습니다. 수식이 복잡하고 변수 조합이 많으면 엑셀이 매번 재계산하느라 느려지는 걸 느꼈습니다. 이럴 땐 [수식] 탭에서 계산 옵션을 "데이터 표 제외하고 자동 계산"으로 바꿔두고, 필요할 때만 수동으로 재계산(F9)하는 방법도 있습니다.
• 입력 셀을 잘못 지정하면 결과가 전부 틀어집니다. 행 입력 셀과 열 입력 셀을 반대로 넣으면 엉뚱한 값이 나오니, 처음 만들 때는 결과가 예상한 방향으로 나오는지 한 번 확인해보는 게 안전합니다.

데이터 표는 "여러 가정을 한 번에 비교해야 하는" 보고서를 만들 때 정말 유용한 기능입니다. 저처럼 시트를 몇 개씩 복사해서 가정마다 따로 확인하고 계셨다면, 표 하나로 모든 조합을 한눈에 비교하는 이 방법을 꼭 써보시길 추천드립니다.