Excel에서 데이터 표를 쉽게 만드는 방법을 찾고 계신가요? 그렇다면 잘 찾아오셨습니다. Excel에서 데이터 표(Data Table)를 만드는 기능은 다양한 계산을 훨씬 간편하게 해주는 중요한 도구입니다. 데이터 표를 활용하면 수식에 여러 가지 입력값을 적용해 보고, 입력값의 변화가 결과에 어떤 영향을 미치는지 한눈에 확인할 수 있습니다.
그럼 지금부터 본격적으로 살펴보겠습니다.
예제 파일 내려받기
실습용 통합 문서를 다운로드하여 직접 따라 해볼 수 있습니다.
Excel에서 데이터 표를 만드는 5가지 방법
각 방법을 설명하기 위해 다음과 같은 예제 데이터를 사용하겠습니다. 어떤 사람이 은행에 2,000달러를 투자하고, 연 이자율 5%로 5년 후 총 잔액 2,500달러를 받았다고 가정합니다.
총 잔액을 구하는 수식은 다음과 같습니다.
총 잔액 = 투자금 + 투자금 × 기간(년) × 이자율
총 잔액 = C3+(C3*C4*C5)
이제 데이터 표를 활용해 투자금, 이자율 등 변수가 변할 때 총 잔액이 어떻게 달라지는지 손쉽게 확인해 보겠습니다.
방법 1: 하나의 변수를 사용하는 데이터 표 만들기 (1-Variable Data Table)
케이스 1: 세로 방향(열 기준) 데이터 표
1단계: 먼저 다양한 투자금 값을 나열할 열과, 결과가 표시될 총 잔액 열을 만듭니다.
여기서는 투자금이라는 하나의 변수를 사용합니다. 이때 총 잔액 열의 첫 번째 행 셀을 결과 값이 있는 C7 셀과 연결해야 합니다.
2단계: 전체 범위 E4:F9를 선택한 후, 데이터 탭 >> 예측 그룹 >> 가상 분석(What-If Analysis) >> 데이터 표를 클릭합니다.
3단계: 데이터 표 대화 상자가 나타나면, 열 기준 데이터 표이므로 입력 열의 셀(Column input cell)에 절대 참조 형식으로 $C$3을 입력하고 확인을 누릅니다.
4단계: 이제 총 잔액 열에 투자금별 결과 값들이 자동으로 계산되어 표시됩니다.
케이스 2: 가로 방향(행 기준) 데이터 표
1단계: 이번에는 다양한 투자금 값을 가로로 배열하고, 그 아래에 결과가 표시될 총 잔액 행을 만듭니다. 마찬가지로 총 잔액 행의 첫 번째 열 셀을 결과 값이 있는 C7 셀과 연결합니다.
2단계: 전체 범위 C9:H10을 선택한 후, 데이터 탭 >> 예측 그룹 >> 가상 분석 >> 데이터 표를 클릭합니다.
3단계: 데이터 표 대화 상자에서 행 기준 데이터 표이므로 입력 행의 셀(Row input cell)에 절대 참조로 $C$3을 입력하고 확인을 누릅니다.
4단계: 총 잔액 행에 각 투자금에 대한 결과 값들이 표시됩니다.
방법 2: 두 개의 변수를 사용하는 데이터 표 만들기 (2-Variable Data Table)
1단계: 두 변수 데이터 표는 행 기준과 열 기준 방식을 결합한 형태입니다. 먼저 B9 셀을 C6 셀의 총 잔액 값과 연결합니다. 그런 다음 B9의 바로 아래에는 투자금, 바로 오른쪽에는 이자율 값을 각각 나열합니다.
2단계: 데이터 범위 B9:F13을 선택하고 방법 1의 2단계와 동일하게 진행합니다. 데이터 표 대화 상자가 나타나면 입력 행의 셀과 입력 열의 셀에 각각 해당 참조 셀을 입력한 뒤 확인을 누릅니다.
3단계: 이제 투자금과 이자율 조합에 따른 결과 값들이 표 형태로 한꺼번에 표시됩니다.
방법 3: 데이터 표로 여러 결과 비교하기
이전 방법에서처럼 변수를 두 개 사용하는 대신, 출력 결과를 두 개 이상 비교해야 하는 경우도 있습니다.
먼저 투자금에서 총 잔액을 빼서 이자(C8)라는 또 다른 출력 값을 계산합니다. 그다음 투자금 열 옆에 총 잔액 열과 이자 열을 만듭니다.
1단계: 총 잔액 열과 이자 열의 첫 번째 행을 각각 C7 셀과 C8 셀에 연결합니다.
2단계: 범위 E4:G9를 선택하고 방법 1의 2단계를 따릅니다. 데이터 표 대화 상자가 나타나면 열 기준 데이터 표이므로 입력 열의 셀에 참조를 입력하고 확인을 누릅니다.
3단계: 이제 투자금 변화에 따른 총 잔액과 이자 결과가 동시에 표시되어 손쉽게 비교할 수 있습니다.
방법 4: 파워 쿼리(Power Query)를 활용한 데이터 표 만들기
1단계: 전체 데이터 범위를 선택한 후 데이터 탭 >> 테이블/범위에서를 클릭합니다.
2단계: 테이블 만들기 대화 상자가 나타나면 머리글 포함(My table has headers)에 체크합니다.
3단계: 테이블이 생성되면 총 잔액 열의 첫 번째 행에만 수식을 입력하면 됩니다.
4단계: Enter 키를 누르면 나머지 총 잔액 행에도 수식이 자동으로 채워져 결과가 완성됩니다.
방법 5: 표 서식(Format as Table) 기능으로 데이터 표 만들기
1단계: 전체 데이터 범위를 선택한 후 홈 탭 >> 스타일 그룹 >> 표 서식을 클릭하고 원하는 스타일을 선택합니다.
2단계: 테이블 만들기 대화 상자가 나타나면 머리글 포함에 체크합니다.
3단계: 테이블이 생성되면 총 잔액 열의 첫 번째 행에 수식을 입력합니다.
4단계: Enter 키를 누르면 나머지 행에도 수식이 자동으로 적용되어 결과가 완성됩니다.
마무리
지금까지 Excel에서 데이터 표를 만드는 가장 쉬운 5가지 방법을 알아보았습니다. 이해하기 어려운 부분이 있다면 언제든지 댓글로 질문해 주세요. 또 다른 유용한 방법을 알고 계시다면 함께 공유해 주시면 감사하겠습니다.
함께 읽으면 좋은 글
- Excel 차트에 데이터 표 추가하는 4가지 방법
- Excel 시나리오 분석 완벽 가이드: 실전 예제 2가지 + 템플릿
- Excel 데이터 표가 작동하지 않을 때: 7가지 문제와 해결책
- Excel 데이터 표로 가상 분석(What-If Analysis) 수행하는 방법