엑셀의 해찾기는 엑셀 2010에서 크게 기능이 향상되었습니다. 가장 크게 바뀐 점은 ‘해법 선택’ 드롭다운에서 해법을 선택할 수 있다는 점입니다. 여러분이 최적화하는 문제에 따라 적절한 해법 엔진을 선택할 수 있습니다. 다음과 같은 옵션이 있습니다. • 단순 LP(Simplex LP) 엔진 : 선형 최적화 문제에 시용합니다. 선형 최적화 문제는 (변수 셀) * (상수) 항을 더해서 목표 셀과 제한 조건을 만들게 됩니다. 대부분 최적값을 찾고자 하는 모델은 비선형입니다. • GRG(Generalized Reduced Gradient) 비선형 엔진 : 목표 셀과 제한 조건의 일부가 모두 비선형 혹은 목표 셀이나 제한 조건의 일부가 비선형인 최적화 문제를 풀기 위해 사용합니다. 계산할 때는 변수 셀을 곱하거나 나누는 등의 단순 계산 혹은 변수 셀을 거듭 제곱에 사용하거나, 지수 함수나 삼각 함수 등에 사용하는 것을 포함합니다. GRG 엔진을 사용하면 옵션에서 ‘Multistart’ 옵션을 사용할 수 있는데 이 옵션을 사용하면 이전 엑셀 버전에서 오답을 내던 문제들을 푸는데 제대로 사용할 수 있습니다. 실제로는 Multistart 옵션을 많이 사용할 것입니다. • Evolutionary 엔진 : 목표 셀과 제한 조건 혹은 목표 셀이나 제한 조건에 변수 셀을 참조하는 완만하지 않은 비선형 함수가 들어있을 때 사용합니다. 예를 들어 여러분의 목표 셀이나 제한 조건에 변수 셀을 참조하는 IF, SUMIF, COUNTIF, SUMIFS, COUNTIFS, AVERAGEIF, AVERAGEIFS, ABS, MAX, MIN 등의 함수가 들어있으면 최적화된 답을 찾아내는데 이 Evolutionary 엔진이 최선의 선택일 것입니다. 많은 영역에서 Evolutionary 엔진 또한 많이 사용합니다. 목표 셀, 변수 셀, 제한 조건을 입력하고 나면 해찾기 프로그램은 무엇을 수행할까요? 만약 변수 셀이 제한 조건을 만족한다면 변수 셀은 기능한 해법이 될 수 있습니다. 그리고 해찾기는 이런 가능한 해법들 중에서 가장 적절한 해법의 집합을 찾습니다(이 값들을 최적값이라고 한다). 이 값은 목표셀에 대한 최선의 값입니다(극대화시킬 때는 가장 크게 만드는 값이고 극소화시킬 때는 가장 작게 만드는 값입니다). 만약 최적화할 수 있는 값이 여러 개이면 해찾기는 가장 처음에 찾은 값에서 해찾기를 멈춥니다. Excel Solver 추가 기능을 사용해 본 적이 있다면 옵션이 많고 다소 압도적일 수 있습니다. 여기에서는 Excel에서 올바른 해결 방법을 선택하여 문제에 대한 최적의 솔루션을 효율적으로 찾는 데 도움이 되는 실용적인 정보를 제공하고자 합니다. Excel에서 Solver를 설정할 때 선택해야하는 것 중 하나는 “해법 선택(solving method)” 입니다. 선택할 수 있는 세 가지 방법 또는 알고리즘이 있습니다.
GRG 비선형과 Evolutionary은 비선형 문제에 대해 최적화되어 있으며 단순 LP는 선형문제에만 제한됩니다  GRG는 “Generalized Reduced Gradient”의 약자입니다. 가장 기본적인 형태로, 이 “해법 선택”은 입력 값 (또는 결정 변수) 변화로 목적 함수의 기울기 또는 기울기를 보고 부분 미분 결과가 0 일 때 최적 솔루션에 도달한 것으로 결정합니다. 두 가지 비선형 해법 선택 중 GRG 비선형이 절충하는 방법을 적용하므로 더 빠릅니다. 단점은 이 알고리즘으로 얻은 솔루션은 초기 조건에 따라 크게 달라지며 최적의 글로벌 최적값이 아닐 수 있습니다. Solver는 초기 조건에 가장 가까운 로컬 최적값에서 정지하여 글로벌적으로 최적화되거나 아닌 솔루션을 제공할 수 있습니다. GRG 비선형 Solver가 좋은 결과를 얻기 위해 필요한 또 다른 요구 사항은 함수가 매끄러워야 한다는 것입니다. 예를 들어 IF, VLOOKUP 또는 ABS 함수로 인한 불연속이 발생되면 이 알고리즘에 문제가 발생할 수 있습니다. Evolutionary 알고리즘은 글로벌하게 최적의 솔루션을 찾을 가능성이 높기 때문에 GRG 비선형보다 강력(robust)합니다. 그러나 이 “해법 선택”은 매우 느립니다. Evolutionary 방법은 최적의 결과가 사전에 정의되었기 때문에 이러한 경우에는 잘 작동되는 자연 선택 이론에 기초합니다. 간단히 말해서, Solver는 입력값 세트의 임의의 “모집단(population)”로 시작합니다. 이러한 입력값 세트가 모델에 연결되고 결과는 목표 값을 기준으로 평가됩니다. 목표 값에 가장 가까운 솔루션을 제공하는 입력값 세트는 “자손(offspring)”이라는 두 번째 모집단을 생성하도록 선택됩니다. 자손은 첫 번째 모집단 입력값 중에서 최적값 집합의 “변이(mutation)”이라 할 수 있습니다. 그렇게 두번째 모집단도 평가되고, 최선의 값은 세번째 모집단을 창조하도록 선택됩니다. 아래 그림은 좀더 명확하게 이를 설명하고 있습니다. 이것은 한 모집담에서 다음 모집단으로 목표값에 대해 거의 변화가 없을 때까지 계속됩니다. 이 과정에 대해 시간이 많이 걸리는 이유는 모집단의 각 구성원을 개별적으로 평가해야 한다는 것입니다. 또한 차후의 “세대(generations)”는 다음 최고의 값의 집합을 찾기 위해 미분이나 목적 함수의 기울기를 사용하는 대신에 임의의 값으로 채워집니다. 이제 Excel에서는 Solver 옵션 창을 통해 알고리즘을 제어할 수 있습니다. 예를 들어, 변이율 및 모집단 크기를 선택하여 솔루션을 잠재적으로 단축할 수 있습니다. 그러나 모집단 크기를 줄이거나 변이율을 증가시키면 수렴에 도달하기에 더 많은 모집단들이 필요할 수 있기 때문에 결과를 약화시킬 수 있습니다. GRG 비선형 알고리즘의 속도와 Evolutionary 알고리즘의 견고성(robustness) 사이의 좋은 타협점은 GRG 비선형 Multistart 입니다. Solver 옵션을 통해 GRG 비선형 탭 아래의 이 옵션을 활성화할 수 있습니다. 이 알고리즘은 기존의 GRG 비선형 알고리즘을 사용하여 각각 평가되는 초기값에 대한 임의로 분포된 모집단을 생성합니다. 서로 다른 초기 조건에서 여러 번 시작하면 발견되는 솔루션이 글로벌 최적값일 가능성이 훨씬 높아집니다. 세 가지 해결 방법 중 단순 LP를 가장 적게 사용합니다. 선형 함수만 포함된 문제에 적용할 수 있으므로 응용에 제한적입니다. 많은 경우에, 해결하고자 하는 문제는 비선형입니다. 그리고 그것들이 선형일 때는 대신 그것들을 행렬 방정식으로 푸는 것을 더 선호합니다. 그러나 해결하려는 문제가 선형인 경우 단순 LP 방법으로 얻은 솔루션이 항상 글로벌한 최적의 솔루션이라는 것을 확신할 수 있기 때문에 매우 강력(robust)합니다.
Excel 2016 Excel 2013 Excel 2010 Excel 2007 더 보기...간단히 참고: 사용자 언어로 가능한 한 빨리 가장 최신의 도움말 콘텐츠를 제공하고자 합니다. 이 페이지는 자동화를 통해 번역되었으며 문법 오류나 부정확한 설명을 포함할 수 있습니다. 이 목적은 콘텐츠가 사용자에게 유용하다는 것입니다. 이 페이지 하단의 정보가 도움이 되었다면 알려주세요. 쉽게 참조할 수 있는 영어 문서가 여기 있습니다. 해 찾기를 사용 하는이 문서에서는 Microsoft Excel 추가 기능 프로그램 사용 하면 가상 분석에 대 한 최적의 제품 조합 결정 해야 합니다. 월별 제품 조합 수익을 최대화 하는 어떻게 확인할 수 있나요?회사 자주 매달 운영 생성 하기 위해 각 제품의 수량을 확인 해야 합니다. 가장 간단한 형태로 제품 조합 문제 수익을 최대화 하 달 동안 생산 해야 하는 각 제품의 크기를 확인 하는 방법을 포함 됩니다. 제품 조합 일반적으로 다음과 같은 제약 조건을 준수 해야 합니다.
다음 제품 조합 문제의 예제 이제 해결 해 보겠습니다. 그림 27-1 Prodmix.xlsx, 파일에이 문제를 해결 방법을 찾을 수 있습니다. 6 개의 서로 다른 제품 공장에 생성 하는 마약 회사에 근무 한다고 가정 하겠습니다. 각 제품의 생산 인력 및 재료 필요합니다. 행 4 그림 27-1 노동력 각 제품의 파운드를 생성 하는 데 필요한 시간을 표시 하 고 5 행 각 제품의 파운드를 생성 하는 데 필요한 재료 파운드 표시 합니다. 노동력의 6 시간 및 3.2 파운드 재료의 예를 들어 필요 제품 1 파운드를 생성 합니다. 각 마약 파운드 당 가격 6 행에 지정 되어, 파운드 당 단가 7 행에 지정 되어 및 파운드 당 수익 기여도 9 행에 지정 됩니다. 예를 들어 제품 2 파운드 당 11.00 달러에 판매 단가 파운드 당 5.70 달러와 파운드 당 $5.30 수익 기여 합니다. 각 마약에 대 한 달의 요구 8 행에 지정 됩니다. 예를 들어, 제품 3에 대 한 요구는 1041 파운드입니다. 이번 달 4500 시간의 노동력 및 재료의 1600 파운드는 사용할 수 있습니다. 이 회사 월간 수익을 최대화 하는 방법은 수 있나요? Excel 해 찾기에 대 한 것을 알고 있으면 제품을 조합 하 여 연결 된 수익을 계산 하 고 자원 배정 현황 추적 하도록 워크시트를 구성 하 여이 문제를 공격 것입니다. 다음 평가판 and 오류 노동력 또는 재료 기능을 사용할 수 보다 더 많은 사용 하지 않고 및 모든 마약 demand 초과 생성 하지 않고 수익을 최적화 하려면 제품 조합 다양 하 게 사용 합니다. 이 과정의 해 찾기 평가판 오류 단계 에서만 사용 됩니다. 기본적으로, 해 찾기 완벽 하 게 평가판 오류 검색을 수행 하는 최적화 엔진입니다. 제품 조합 문제를 해결 하기 위해 키를 효율적으로 자원 배정 현황 및 관련 된 모든 제품 조합 수익을 계산 하기 위해 됩니다. 이 계산이를 사용할 수 있는 중요 한 도구 SUMPRODUCT 함수입니다. SUMPRODUCT 함수 셀 범위에서 해당 값을 곱합니다 및 해당 값의 합계를 반환 합니다. SUMPRODUCT 계산에 사용 되는 각 셀 범위 치수가 같은 두 개의 행 이나 두 개의 열만 하나의 열과 행이 하나 된 하지 SUMPRODUCT를 사용할 수 있다는 의미 되어 있어야 합니다. SUMPRODUCT 함수 제품에서 사용 하는 방법의 예를 들어 조합 예제, 계산 우리 자원 배정 현황 해 보겠습니다. 노동력 사용량 계산 (마약 1 파운드 당 사용 된 노동력) *(Drug 1 pounds produced) + 더 번거로운 방식으로 노동력 사용량을 계산 D2 * D4 + E2 * E4 + f 2 * F4 + G2 * G4 + H2 * H4 + I2 * i 4합니다. 재료 사용 될 수로 계산 된 마찬가지로, D2 * D5 + E2 *E5 + f 2 * f5 키 + g 2 * G5 + H2 * H5 + I2 * i 5합니다. 그러나 6 개 제품에 대 한 워크시트에 다음이 수식을 입력 시간이 소요 됩니다. 가정 얼마나 오래 걸리는 경우 작업 하 던 예를 들어을 만든 회사 50 제품 공장에 합니다. 인력 및 재료 사용량을 계산 하기 위해를 훨씬 쉽게 방법이 수식 SUMPRODUCT($D$2:$I$2,D4:I4)d 15에 D14에서 복사 하는 것입니다. 이 수식은 계산 D2 * D4 + E2 * E4 + f 2 * F4 + G2 * G4 + H2 * H4 + I2 * i 4 (노동력 사용량은)를 입력 하는 것이 더 쉽습니다 하지만! 사용 하는 $ 기호를 d2: i2 범위와 제품 조합 행 2의에서 수식을 복사할 때 여전히 캡처한 되도록 확인 합니다. 셀 d 15의 수식이 재료 사용량을 계산합니다. 비슷한 방식으로 수익에 의해 결정 됩니다. (파운드 당 1 수익 약품) * (마약 1 파운드 생산) + 쉽게 수익 SUMPRODUCT(D9:I9,$D$2:$I$2)수식이 있는 셀 d 12 계산할 수 있습니다. 제품 조합 해 찾기 모델의 세 가지 구성 요소 이제 식별할 수 있습니다.
방법을 보여 대상 셀을 입력 해 찾기에 셀 및 제약 조건을 변경 합니다. 다음을 수행 하는 데 필요한 모든 수익을 최대화 제품 조합을 찾을 수 계산 단추를 클릭입니다. 를 시작 하려면 데이터 탭을 클릭 하 고 분석 그룹에서 해 찾기를 클릭 합니다. 참고: 장 26, "라인 소개에 최적화 된 Excel 해 찾기,"에서 설명한 것 처럼 Microsoft Office 단추를 클릭 한 다음 Excel 옵션을 뒤에 추가 기능으로 해 찾기 설치 됩니다. 관리 목록에서 Excel 추가 기능에서 해 찾기 추가 기능 상자를 한 다음 확인을 클릭 합니다. 그림 27-2와 같이 해 찾기 매개 변수 대화 상자가 나타납니다. 목표 셀 상자를 클릭 한 다음 수익 셀 (셀 d 12)을 선택 합니다. 셀 변경 상자를 클릭 한 다음 d2: i2, 각 마약의 생산 파운드 포함 된 범위를 가리킵니다. 이제 대화 상자의 그림 27-3 표시 합니다. 이제 제약 조건을 모델에 추가할 준비가 되었습니다. 추가 단추를 클릭 합니다. 그림 27-4를 같이 제한 조건 추가 대화 상자가 표시 됩니다. 자원 배정 현황 제약 조건을 추가 하려면 셀 참조 상자를 클릭 한 다음 d14: d15 범위를 선택 합니다. _LT_ 선택 중간 이름 목록에서 = 합니다. 제약 조건 상자를 클릭 하 고 F14:F15 셀 범위를 선택 합니다. 이제 제약 조건 추가 대화 상자의 그림 27-5와 같은 표시 합니다. 해 찾기 변경 하기 위한 다른 값을 시도 하는 경우 셀을 모두 만족 하는 조합만 보장 이제 우리 D14< F14 = (사용 된 노동력은 사용 가능한 노동력 보다 작거나 같음) 및 D15< = F15 (사용 되는 재료 보다 작거나 같음 사용 가능한 원시 재료)으로 간주 됩니다. 필요 시 제한 조건을 입력에 추가 클릭 합니다. 그림 27-6에 나와 있는 것 처럼 제약 조건 추가 대화 상자에 입력 합니다. 이러한 제한 조건을 추가 하면는 해 찾기의 변경 셀 값에 대 한 다양 한 조합을 시도할 때 다음 매개 변수를 충족 하는 조합만으로 간주 됩니다.
제한 조건 추가 대화 상자에서 확인을 클릭 합니다. 해 찾기 창이 그림 27-7 같이 표시 됩니다. 셀을 변경 해야 하는 제약 조건 입력 해 찾기 옵션 대화 상자에서 음수가 합니다. 해 찾기 매개 변수 대화 상자에서 옵션 단추를 클릭 합니다. 다음 페이지에서 그림 27-8에 나와 있는 것 처럼 선형 모델 가정 상자 및 음수 아닌 것으로 가정 상자를 선택 합니다. 확인을 클릭합니다. 음수 아닌 것으로 가정 확인란을 선택 하면 해 찾기의 변경 셀 각 변경 셀 음수가 아닌 값을 얻습니다 조합만 것으로 간주 됩니다. 우리 선형 모델 가정 상자 제품 혼합 문제 때문에 특수 한 유형의 선형 모델이라는 해 찾기 문제 확인 합니다. 기본적으로, 해 찾기 모델은 다음과 같은 조건 선형:
이 해 찾기 문제 선형 이유는 무엇입니까? 목표 셀 (수익)가로 계산 됩니다. (파운드 당 1 수익 약품) * (마약 1 파운드 생산) + 이 계산이 양식의 조건을 추가 하 여 목표 셀 값 파생 됩니다 패턴을 따르는 (상수 변경합니다. 노동력 제한 조건은 (마약 1 파운드 당 사용 된 노동력)에서 파생 된 값을 비교 하 여 계산 되는 * (마약 1 파운드 생산) + (마약 2의 파운드 당 사용 된 노동력) *(Drug 2 pounds produced) +... (사용자 환경 엔지니어마약 6의 파운드 당 교육) * (6 파운드 생산 약품) 사용 가능한 노동력에 있습니다. 따라서 노동력 제한 양식의 조건을 추가 하 여 평가 되 (상수 변경 및 합계를 비교 합니다. 노동력 제한 및 재료 제약 조건 선형 모델 요구 사항을 충족합니다. 우리 demand 제약 조건 형태 (마약 1 생산) < (1 마약 Demand) = 폼의 조건을 추가 하 여 평가 각 되기 때문에 각 demand 제약 조건 선형 모델 요구 사항을 충족 (상수 변경 및 합계를 비교 합니다. 우리 제품 조합 모델은 선형 모델 표시 하는 데 이유 해야 할까요?
해 찾기 옵션 대화 상자에서 확인을 클릭 하면 이전 버전의 그림 27-7 나와 있는 주 해 찾기 대화 상자를 반환 합니다. 계산을 클릭 하면 해 찾기 계산 최적의 솔루션 (있는 경우)이 제품 조합 모델에 대 한 합니다. 장 26에 명시 제품 조합 모델을 최적의 솔루션 집합이 변경 셀 값 (각 마약의 생산 파운드)의 최적 해 집합에 대해 수익을 최대화 하는 것입니다. 다시 적합 한 해 변경 제약 조건을 모두 만족 하는 셀 값의 집합입니다. 그림 27-9 표시 변경 셀 값 적합 한 해 되므로 프로덕션 연관 된 모든 셀은 음수가 아닌, 프로덕션 수준 demand를 초과 하지 및 자원 배정 현황 가능한 자원을 초과 하지 않습니다. 다음 페이지에서 그림 27-10 표시 변경 셀 값은 다음과 같은 이유로 부적합 한 해 를 나타냅니다.
해 찾기를 클릭 한 후 해 찾기 신속 하 게 그림 27-11에 표시 된 최적의 솔루션을 찾습니다. 워크시트에서 최적의 솔루션 값을 유지 하려면 구한 해를 선택 해야 합니다. 제약 회사 마약 4, 마약 5 1084 파운드 및 기타 마약 없음 596.67 파운드 생성 하 여 $ 6, 625.20 수준에서 월간 수익을 최대화 수 있습니다! 낼 수 밖의 방법으로 6, 625.20 달러의 최대 수익을 알 수 없습니다. 신뢰할 수는 모두는 리소스 제한 및 요청, 이번 달 $6,627.20 두 개 이상 만들 수 있는 방법이 없습니다입니다. 해야 각 제품에 대 한 요구를 충족 가정 합니다. (Prodmix.xlsx 파일에서 아니요 적합 한 해 워크시트 참조). 다음이 필요 시 제약 조건에서 변경 해야 D2:I2< = d8: i8 에 D2:I2> = d8: i8합니다. 이렇게 하려면 해 찾기 열기는 D2:I2< 선택 = d8: i8 제약 조건 한 다음 변경을 클릭 합니다. 그림 27-12에 표시 된 제한 조건 변경 대화 상자가 나타납니다. Gt_ 선택 =, 한 다음 확인을 클릭 합니다. 이제 모든 요구 사항을 충족 하는 셀 값만 변경 해 찾기 고려할 예정 보장 했습니다. 계산을 클릭 하면 나타납니다 메시지 "해 찾기 가능한 경우 해결 방법을 찾을 수 없습니다." 이 메시지는 제한 된 자원을 보려면 모델 아니라는에서 실수로 내린, 모든 제품에 대 한 요구 충족 시킬 수 아닙니다. 해 찾기는 단순히 이야기를 각 제품에 대 한 요구를 충족 하려는 경우 할 노동력, 더 많은 원시 자료 또는 둘 다의 더 많은 추가 합니다. 어떻게 되는지 살펴보겠습니다 각 제품에 대 한 제한 없이 요구 수 있도록 허용 하 고 음수 수량 각 마약의 생성 될 수 있도록 허용 합니다. (이 해 찾기 문제 Prodmix.xlsx 파일에 있는 설정 값 수렴 하지 않으면 워크시트에서 볼 수 있습니다.) 이 문제에 대 한 최적의 솔루션을 찾으려고 해 찾기를 열고 옵션 단추를 클릭 음수 아닌 것으로 가정 확인란의 선택을 취소 합니다. 해 찾기 매개 변수 대화 상자에서 선택 demand 제약 조건 D2:I2< = d8: i8 및 제약 조건을 제거 하려면 삭제를 클릭 합니다. 해 찾기를 클릭 하면 해 찾기 반환 메시지 "집합 셀 값이 수렴 하지 않습니다." 이 메시지 대상 셀 (예:이 예에서는) 최대화가 가지 가능한 경우 솔루션 임의의 큰 대상 셀 값을 의미 합니다. (목표 셀을 최소화할 수 있으면 "설정할 셀 값이 수렴 하지 않습니다" 메시지 의미는 임의로 작은 대상 셀 값이 있는 가능한 경우 해결 방법이 있습니다.) 현재 상황에서 음수는 마약 생산 허용 하 여 적용 "만듭니다" 임의로 많은 양의 다른 마약을 생성 하는 사용할 수 있는 리소스입니다. 우리 무제한 demand 제공 되 면 이렇게 하면 수익을 만들 수 있습니다. 실제 상황에서 무제한 일정 금액을 만들 수 없습니다 했습니다. 즉, "설정 값이 수렴 하지 않습니다"를 참조 하 여 모델 오류가 않습니다.
추가 도움이 필요하신가요? |
엑셀 해찾기 알고리즘 - egsel haechajgi algolijeum
관련 게시물
저작권 © 2024 ko.ketajaman Inc.
