많은 분들이 마이크로소프트 엑셀을 사용하면서도 솔버(Solver)라는 강력한 데이터 분석 도구의 존재를 모르거나 활용하지 못하는 경우가 많습니다. 이 훌륭한 기능을 사용하면 원하는 조건에 따라 다양한 시나리오를 손쉽게 만들어볼 수 있습니다. 하지만 엑셀에서 솔버 최적화를 처음 접하면 어려움을 느끼기 마련입니다. 이 글에서는 솔버를 활용해 데이터를 최적화하는 구체적인 예제를 단계별로 자세히 소개해 드리겠습니다.
솔버(Solver)란 무엇인가?
솔버는 엑셀에 내장된 추가 기능(애드인) 프로그램으로, 데이터 모델에 대한 최적의 해답을 찾아주는 도구입니다. 여러 가지 해결책을 시뮬레이션하고 최적화할 수 있도록 설계되어 있으며, 주로 선형 최적화(linear optimization)와 비매끄러운(non-smooth) 연산 문제에 활용됩니다. 목표가 되는 셀과 변경 가능한 변수 셀만 지정하면, 가능한 모든 해를 탐색하여 최적의 답을 찾아낼 수 있습니다.
엑셀 솔버로 최적화하기: 상세 단계와 예제
이제 실제 예제를 통해 솔버를 사용해 데이터를 최적화하는 전체 과정을 살펴보겠습니다.
예를 들어, 어떤 회사의 재고 수량(Available Products), 단위 원가(Unit Cost), 판매 가격(Selling Price), 인건비(Labor Cost), 광고비(Advertising Cost) 데이터가 있다고 가정해 보겠습니다.
여기에 재고 기준의 총 판매량(Total Sales Volume) 데이터도 포함되어 있습니다. 이 데이터를 바탕으로 총 매출(Total Revenue), 총 비용(Total Expenses), 순이익(Profit)을 계산한 후, 솔버 기능으로 최적화를 진행해 보겠습니다.
1단계: 총 매출, 총 비용, 순이익 계산하기
- 먼저 C11 셀에 아래의 간단한 수식을 입력하여 총 매출을 구합니다.
- 다음으로 Enter 키를 눌러 매출 결과를 확인합니다.
- 이어서 C12 셀을 선택하고 아래 수식을 적용해 총 비용을 계산합니다.
- Enter 키를 누르면 총 비용 결과가 표시됩니다.
- 이번에는 C13 셀에 아래 수식을 입력하여 순이익을 계산합니다.
- Enter 키를 클릭하면 순이익 값을 확인할 수 있습니다.
현재 회사의 순이익은 $7,500입니다. 그런데 만약 회사가 $10,000의 순이익을 달성하고 싶다면 어떻게 해야 할까요? 이럴 때 솔버 기능을 사용해 데이터 시뮬레이션을 진행하면 됩니다. 지금부터 바로 시작해 보겠습니다.
2단계: 솔버 실행하고 목표 설정하기
- 워크시트에서 [데이터] 탭의 [솔버] 옵션을 클릭합니다.
- [목표 값 설정(Set Objective)] 항목에서 C13 셀을 선택합니다.
- [값(Value Of)]을 선택하고 목표값으로 10000을 입력합니다.
- [변경할 변수 셀(By Changing Variable Cells)] 항목에는 C4 셀과 C10 셀을 지정합니다.
3단계: 제약 조건 추가하기
- 이제 선택한 셀들에 제약 조건을 설정합니다. [솔버 파라미터] 창에서 [추가(Add)] 버튼을 클릭하세요.
- [셀 참조(Cell Reference)] 상자에서 C4 셀을 선택하고 원하는 제약 조건을 입력합니다. 여기서는 회사의 재고 용량이 1,000개이므로 1000으로 설정했습니다.
- [추가(Add)] 버튼을 눌러 제약 조건을 더 추가합니다.
- 이번에는 C10 셀에 대한 제약 조건을 설정합니다. [셀 참조] 항목에서 C10 셀을 선택하고 [제약 조건] 상자에 900을 입력합니다. 물론 본인의 상황에 맞게 임의의 값을 지정할 수도 있습니다.
- 다시 [추가] 버튼을 눌러 추가 조건을 입력합니다.
- 제품 수량은 정수 단위로만 존재하므로, C4 셀과 C10 셀 양쪽 모두에 [정수(Integer)] 제약 조건을 추가합니다.
- 마지막으로 [확인(OK)]을 클릭합니다.
4단계: 솔버 실행 및 결과 확인하기
- 이제 [솔버 파라미터] 창에서 설정된 제약 조건들을 확인할 수 있습니다.
- [풀기(Solve)] 버튼을 클릭합니다.
- [솔버 결과(Solver Results)]라는 새 창이 나타납니다.
- 나타난 창에서 [솔버 해 유지(Keep Solver Solution)]를 선택하고 [확인]을 누릅니다.
- 그러면 솔버 도구를 통해 최적화된 결과를 얻을 수 있습니다. 결론적으로, 회사가 $10,000의 순이익을 달성하려면 제품 재고를 688개로 유지하고 최소 616개를 판매해야 한다는 것입니다.
함께 읽으면 좋은 글: 제약 조건이 있는 엑셀 최적화 방법
알아두면 좋은 팁
- [데이터] 탭에서 [솔버] 기능이 보이지 않는다면 [파일] → [옵션] → [추가 기능]으로 이동한 뒤, [솔버 추가 기능(Solver Add-in)]에 체크하세요. 그러면 리본 메뉴에 솔버 옵션이 나타납니다.
- 제약 조건을 너무 많이 설정하거나 논리적으로 충돌하는 조건을 넣으면 솔버가 해를 찾지 못할 수 있으니, 현실적인 범위 내에서 조건을 설정하는 것이 중요합니다.
연습용 워크북 다운로드
글을 읽는 동안 직접 따라 해볼 수 있도록 연습용 워크북을 다운로드해 활용해 보세요.
마치며
이번 글에서는 엑셀 솔버를 활용한 최적화 예제를 거의 모든 단계별로 살펴보았습니다. 연습용 워크북 파일을 다운로드해 직접 실습해 보시길 권장합니다. 이 글이 여러분에게 도움이 되었기를 바라며, 사용 경험을 댓글로 공유해 주세요. 앞으로도 유익한 학습 콘텐츠로 찾아오겠습니다.
관련 글 추천
- 엑셀 솔버로 다목적 최적화 수행하는 방법
- 엑셀에서 경로 최적화(Route Optimization) 수행하기
- 엑셀로 가격 최적화 모델 만들기
- 엑셀에서 네트워크 최적화 모델 풀기
- 엑셀을 활용한 평균-분산 최적화(Mean Variance Optimization)
- 엑셀에서 일정 최적화(Schedule Optimization)하기
- 엑셀 솔버로 포트폴리오 최적화 수행하는 방법