엑셀 2007의 해찾기를 이용한 선형계획법 최적해 구하기

소개글

선형계획법(LP: Linear programming)은 일반적으로 특정 제약조건하에서 최대의 효과를 낼 수 있도록 관련 자원을 배분하거나 사용하고자 할 때 적용할 수 있는 기법으로 경영학, 산업공학 등의 학문에서 많이 사용하고 있습니다. 우리가 중•고등학교 수학시간에 배운 특정조건하에서 함수의 최대값, 최소값을 구하는 것과 동일한 과정으로 생각할 수 있습니다.
선형계획법에서 최적해를 구하려면 도해법, 심플렉스법 등을 이용하여 일일이 그래프를 그리거나 표를 만들고 손으로 직접 계산할 수 있습니다. 그러나 대부분의 경우 선형계획법은 앞에서 예를 든 경우처럼 간단하지 않고 많은 변수와 복잡한 목적함수, 제약조건이 존재하므로 수학적인 방법이나 심플렉스법 등으로 풀기에는 무리가 있습니다. 따라서 컴퓨터에서 프로그램을 작성하거나 선형계획법을 풀 수 있는 기능이 포함된 S/W를 이용해야 합니다.
시중에는 이러한 선형계획법 문제를 풀 수 있는 많은 패키지S/W가 나와 있습니다. 이중에서 우리가 표 계산에 많이 이용하고 있는 엑셀 2007에도 선형계획법 문제를 풀어주는 “해찾기(excel solver)”기능이 있습니다. 엑셀의 “해찾기”를 이용하면 복잡한 선형계획법 문제도 간단한 조작으로 최적해를 찾을 수 있습니다. 엑셀의 해찾기(excel solver)기능은 목표값 찾기와 비슷하지만 목표값 찾기는 특정결과값에 대응하는 하나의 과정변수 값만을 구할 수 있는 반면, 해찾기는 특정결과 조건을 만족하는 다수의 변수 값을 구할 수 있습니다.
이제 엑셀 2007의 해찾기 기능이 제공하는 3가지 경우(해의 조건이 최대값, 최소값, 지정값)에 대하여 선형계획법 문제와 과정값 구하기 문제를 가정하고 최적해를 구해보도록 하겠습니다.

목차

1.엑셀 2007의 해찾기를 이용한 선형계획법 최적해 구하기 개요
2.선형계획법에서 최대값 구하기
2.1 최대값 구하기 문제
2.2 엑셀 해찾기를 이용한 최대값 구하기
2.3 선형계획법에서 정수로 최대값 구하기
3.선형계획법에서 최소값 구하기
3.1 최소값 구하기 문제
3.2 엑셀 해찾기를 이용한 최소값 구하기
4.특정 지정값에 대한 해찾기
4.1 특정 값 지정관련 문제
4.2 엑셀 해찾기를 이용한 목표 판매수량 구하기

본문내용

선형계획법(LP: Linear programming)은 일반적으로 특정 제약조건하에서 최대의 효과를 낼 수 있도록 관련 자원을 배분하거나 사용하고자 할 때 적용할 수 있는 기법으로 경영학, 산업공학 등의 학문에서 많이 사용하고 있습니다. 우리가 중•고등학교 수학시간에 배운 특정조건하에서 함수의 최대값, 최소값을 구하는 것과 동일한 과정으로 생각할 수 있습니다.
선형계획법을 적용할 수 있는 경우를 예로 들어 보겠습니다.
어떤 만두가게가 있다고 가정합니다. 이 만두가게에서 만드는 만두의 종류는 고기만두와 일반만두 두가지입니다. 이 가게에서는 고기만두 x개와 일반만두 y개를 만들어 팔면 고기만두 1개당 300원의 이익을, 일반만두 1개당 200원의 이익을 본다고 합니다. 고기만두 1개를 만들기 위해서 밀가루 100g과 고기 150g을 사용하며, 일반만두 1개를 만들기 위해서는 밀가루 150g과 고기 50g을 사용합니다. 현재 가게에는 밀가루 20000g과 고기 10000g만 있으며 이 재료만으로 만두를 만들어야 합니다. 만든 만두는 모두 팔린다는 가정하에서 이익(z)를 최대로 하려면 각각의 만두를 몇 개씩 만들어야 하는지 알아 보고자 합니다.
이 경우를 수학공식으로 정리해보면
1) 결정변수: x - 고기만두 생산량, y - 일반만두 생산량
2) 목적함수(최대화): z(이익) = 300x + 200y
3) 제약조건: - 밀가루제약: 100x + 150y <= 200000
- 고기제약: 150x + 50y <= 10000
- x >= 0, y >= 0 입니다.

이 조건하에서 z의 최대값을 구하는 것이 바로 선형계획법에서의 최대값 구하기 입니다.
최소값을 구하는 경우도 지금 예를 든 최대값을 구하는 경우와 논리흐름은 동일합니다.
선형계획법에서 최적해를 구하려면 도해법, 심플렉스법 등을 이용하여 일일이 그래프를 그리거나 표를 만들고 손으로 직접 계산할 수 있습니다. 그러나 대부분의 경우 선형계획법은 앞에서 예를 든 경우처럼 간단하지 않고 많은 변수와 복잡한 목적함수, 제약조건이 존재하므로 수학적인 방법이나 심플렉스법 등으로 풀기에는 무리가 있습니다. 따라서 컴퓨터에서 프로그램을 작성하거나 선형계획법을 풀 수 있는 기능이 포함된 S/W를 이용해야 합니다.
시중에는 이러한 선형계획법 문제를 풀 수 있는 많은 패키지S/W가 나와 있습니다. 이중에서 우리가 표 계산에 많이 이용하고 있는 엑셀 2007에도 선형계획법 문제를 풀어주는 “해찾기(excel solver)”기능이 있습니다. 엑셀의 “해찾기”를 이용하면 복잡한 선형계획법 문제도 간단한 조작으로 최적해를 찾을 수 있습니다. 엑셀의 해찾기(excel solver)기능은 목표값 찾기와 비슷하지만 목표값 찾기는 특정결과값에 대응하는 하나의 과정변수 값만을 구할 수 있는 반면, 해찾기는 특정결과 조건을 만족하는 다수의 변수 값을 구할 수 있습니다.
이제 엑셀 2007의 해찾기 기능이 제공하는 3가지 경우(해의 조건이 최대값, 최소값, 지정값)에 대하여 선형계획법 문제와 과정값 구하기 문제를 가정하고 최적해를 구해보도록 하겠습니다.

참고 자료

ms office excel 2007 online help file 등...