Excel의 Goal Seek 및 Solver를 사용하여 알 수 없는 변수를 해결하는 방법

Excel의 Goal Seek 및 Solver를 사용하여 알 수 없는 변수를 해결하는 방법

Excel은 계산에 필요한 모든 데이터가 있을 때 매우 유용합니다.





하지만 그럴 수 있다면 좋지 않을까. 미지의 변수를 풀다 ?





Goal Seek 및 Solver 추가 기능을 사용하면 가능합니다. 방법을 알려드리겠습니다. Goal Seek로 단일 셀을 푸는 방법 또는 Solver로 더 복잡한 방정식을 푸는 방법에 대한 전체 가이드를 읽으십시오.





Excel에서 목표 찾기를 사용하는 방법

Goal Seek는 이미 Excel에 내장되어 있습니다. 밑에 있어요 데이터 탭에서 가정 분석 메뉴:

이 예에서는 매우 간단한 숫자 집합을 사용합니다. 3/4분의 매출 수치와 연간 목표가 있습니다. 목표 찾기를 사용하여 목표를 달성하기 위해 4분기에 필요한 숫자를 파악할 수 있습니다.



보시다시피 현재 총 판매량은 114,706대입니다. 연말까지 250,000개를 판매하려면 Q4에 몇 개를 팔아야 합니까? Excel의 Goal Seek가 알려드립니다.

목표 찾기를 단계별로 사용하는 방법은 다음과 같습니다.





  1. 딸깍 하는 소리 데이터 > 가상 분석 > 목표 추구 . 다음 창이 표시됩니다.
  2. 방정식의 '같음' 부분을 셀 설정 들. Excel에서 최적화하려고 하는 숫자입니다. 우리의 경우 B5 셀에 있는 판매 수의 누계입니다.
  3. 목표 값을 입력하십시오. 가치를 위해 들. 총 250,000개의 판매된 단위를 찾고 있으므로 이 필드에 '250,000'을 입력합니다.
  4. Excel에서 해결할 변수를 알려주십시오. 셀을 변경하여 들. 우리는 4분기 매출이 어떻게 되어야 하는지 알고 싶습니다. 따라서 Excel에 D2 셀을 풀도록 지시합니다. 준비가 되면 다음과 같이 표시됩니다.
  5. 때리다 좋아요 당신의 목표를 해결하기 위해. 괜찮아 보이면 그냥 때려 좋아요 . Excel은 Goal Seek가 솔루션을 찾았을 때 알려줍니다.
  6. 딸깍 하는 소리 좋아요 다시 한 번 선택한 셀에서 방정식을 푸는 값을 볼 수 있습니다. 셀을 변경하여 .

우리의 경우 솔루션은 135,294 단위입니다. 물론, 연간 목표에서 누적 합계를 빼면 바로 알 수 있습니다. 그러나 Goal Seek는 다음과 같은 셀에서도 사용할 수 있습니다. 이미 데이터가 있습니다 . 그리고 그것이 더 유용합니다.

Excel은 이전 데이터를 덮어씁니다. 하는 것이 좋습니다 데이터 사본에서 목표 찾기 실행 . 목표 찾기를 사용하여 생성된 복사된 데이터에 메모하는 것도 좋은 생각입니다. 현재의 정확한 데이터와 혼동하고 싶지 않습니다.





Word에서 두 번째 페이지를 삭제하는 방법

그래서 목표 추구는 유용한 엑셀 기능 , 하지만 그렇게 인상적이지는 않습니다. 훨씬 더 흥미로운 도구인 Solver 추가 기능을 살펴보겠습니다.

Excel의 솔버는 무엇을합니까?

간단히 말해서, Solver는 목표 추구의 다변수 버전 . 하나의 목표 변수를 사용하고 원하는 답을 얻을 때까지 다른 여러 변수를 조정합니다.

숫자의 최대값, 숫자의 최소값 또는 정확한 숫자를 풀 수 있습니다.

제약 조건 내에서 작동하므로 하나의 변수를 변경할 수 없거나 지정된 범위 내에서만 변경할 수 있는 경우 Solver는 이를 고려합니다.

Excel에서 여러 알 수 없는 변수를 해결하는 좋은 방법입니다. 그러나 그것을 찾고 사용하는 것은 간단하지 않습니다.

해 찾기 추가 기능을 로드한 다음 Excel 2016에서 해 찾기를 사용하는 방법을 살펴보겠습니다.

솔버 추가 기능을 로드하는 방법

Excel에는 기본적으로 해 찾기가 없습니다. 다른 강력한 Excel 기능과 마찬가지로 추가 기능이므로 먼저 로드해야 합니다. 다행히도 이미 컴퓨터에 있습니다.

향하다 파일 > 옵션 > 추가 기능 . 그런 다음 가다 옆에 관리: Excel 추가 기능 .

이 드롭다운에 'Excel 추가 기능'이 아닌 다른 항목이 표시되면 다음과 같이 변경해야 합니다.

결과 창에 몇 가지 옵션이 표시됩니다. 옆에 있는 상자를 확인하십시오. 솔버 추가 기능 체크되고 히트 좋아요 .

이제 볼 수 있습니다. 솔버 버튼 분석 그룹 데이터 탭:

이미 사용 중이시라면 데이터 분석 툴팩 , 데이터 분석 버튼이 표시됩니다. 그렇지 않은 경우 Solver가 단독으로 나타납니다.

추가 기능을 로드했으므로 이제 사용 방법을 살펴보겠습니다.

Excel에서 솔버를 사용하는 방법

모든 해 찾기 작업에는 목표, 변수 셀 및 제약의 세 부분이 있습니다. 각 단계를 살펴보겠습니다.

  1. 딸깍 하는 소리 데이터 > 솔버 . 아래에 솔버 매개변수 창이 표시됩니다. (해석 단추가 표시되지 않으면 해 찾기 추가 기능을 로드하는 방법에 대한 이전 섹션을 참조하십시오.)
  2. 셀 목표를 설정하고 Excel에 목표를 알려주세요. 목표는 솔버 창 상단에 있으며 목표 셀과 최대화, 최소화 또는 특정 값 선택의 두 부분으로 구성됩니다. 선택하면 최대 , Excel은 변수를 조정하여 목표 셀에서 가능한 한 가장 큰 수를 얻습니다. 반대: Solver는 목적 수를 최소화합니다. 가치 Solver가 찾을 특정 숫자를 지정할 수 있습니다.
  3. Excel에서 변경할 수 있는 변수 셀을 선택합니다. 변수 셀은 다음으로 설정됩니다. 변수 셀을 변경하여 들. 필드 옆에 있는 화살표를 클릭한 다음 클릭하고 끌어서 솔버가 작동할 셀을 선택합니다. 이것들은 모든 세포 다를 수 있습니다. 셀을 변경하지 않으려면 선택하지 마십시오.
  4. 다중 또는 개별 변수에 대한 제약 조건을 설정합니다. 마지막으로 제약 조건에 도달합니다. 이것이 Solver가 정말 강력한 곳입니다. 변수 셀을 원하는 숫자로 변경하는 대신 충족해야 하는 제약 조건을 지정할 수 있습니다. 자세한 내용은 아래의 제약 조건 설정 방법 섹션을 참조하세요.
  5. 이 모든 정보가 준비되면 다음을 누르십시오. 해결하다 당신의 대답을 얻기 위해. Excel은 새 변수를 포함하도록 데이터를 업데이트합니다(이 때문에 먼저 데이터 복사본을 만드는 것이 좋습니다).

보고서를 생성할 수도 있습니다. 아래 Solver 예제에서 간략하게 살펴보겠습니다.

솔버에서 제약 조건을 설정하는 방법

하나의 변수가 200보다 커야 한다고 Excel에 알릴 수 있습니다. 다른 변수 값을 시도할 때 Excel은 해당 특정 변수에 대해 201 미만이 되지 않습니다.

제약 조건을 추가하려면 추가하다 제약 조건 목록 옆에 있는 버튼. 새 창이 나타납니다. 제한할 셀을 선택합니다. 셀 참조 필드를 선택한 다음 연산자를 선택합니다.

사용 가능한 연산자는 다음과 같습니다.

  • <= (보다 작거나 같음)
  • = (동일)
  • => (보다 크거나 같음)
  • 정수 (정수여야 함)
  • 오전 (1 또는 0이어야 함)
  • 모두다르다

모두다르다 조금 혼란 스럽습니다. 선택한 범위의 모든 셀이 셀 참조 다른 숫자여야 합니다. 그러나 또한 1과 셀 수 사이에 있어야 함을 지정합니다. 따라서 세 개의 셀이 있는 경우 숫자 1, 2, 3으로 끝납니다(그러나 반드시 그 순서는 아님)

마지막으로 제약 조건에 대한 값을 추가합니다.

할 수 있다는 것을 기억하는 것이 중요합니다 여러 셀 선택 셀 참조용. 예를 들어 6개의 변수가 10보다 큰 값을 갖도록 하려면 모두 선택하고 Solver에 11보다 크거나 같아야 한다고 알릴 수 있습니다. 각 셀에 대해 제약 조건을 추가할 필요가 없습니다.

또한 기본 솔버 창의 확인란을 사용하여 제약 조건을 지정하지 않은 모든 값이 음수가 아닌지 확인할 수 있습니다. 변수를 음수로 설정하려면 이 상자를 선택 취소합니다.

솔버 예제

이 모든 것이 어떻게 작동하는지 확인하기 위해 Solver 추가 기능을 사용하여 빠르게 계산합니다. 시작하는 데이터는 다음과 같습니다.

그 안에는 각기 다른 요율을 지불하는 5개의 다른 직업이 있습니다. 또한 이론 작업자가 주어진 주에 각 작업에서 일한 시간도 있습니다. Solver 추가 기능을 사용하여 일부 제약 조건 내에서 특정 변수를 유지하면서 총 급여를 최대화하는 방법을 찾을 수 있습니다.

사용할 제약 조건은 다음과 같습니다.

  • 작업 없음 4시간 미만으로 떨어질 수 있습니다.
  • 작업 2는 다음과 같아야 합니다. 8시간 이상 .
  • 작업 5는 다음과 같아야 합니다. 11시간 미만 .
  • 총 근무 시간은 다음과 같아야 합니다. 40과 동일 .

Solver를 사용하기 전에 이와 같이 제약 조건을 작성하는 것이 도움이 될 수 있습니다.

Solver에서 설정하는 방법은 다음과 같습니다.

먼저 주의할 점은 테이블 사본을 만들었습니다. 따라서 현재 근무 시간이 포함된 원본을 덮어쓰지 않습니다.

둘째, 보다 큼 및 보다 작음 제약 조건의 값이 하나 더 높거나 낮은 위에서 언급한 것보다. 보다 크거나 보다 작음 옵션이 없기 때문입니다. 크거나 같음과 작거나 같음만 있습니다.

치자 해결하다 그리고 무슨 일이 일어나는지 보십시오.

솔버가 해결책을 찾았습니다! 위 창의 왼쪽에서 볼 수 있듯이 수입이 0 증가했습니다. 그리고 모든 제약 조건이 충족되었습니다.

무선 헤드폰을 Xbox One에 연결

새 값을 유지하려면 다음을 확인하십시오. 솔버 솔루션 유지 확인하고 명중 좋아요 .

하지만 더 많은 정보를 원하시면 창 오른쪽에서 보고서를 선택할 수 있습니다. 원하는 보고서를 모두 선택하고 개요를 표시할지 여부를 Excel에 지정한 다음(권장) 좋아요 .

보고서는 통합 문서의 새 시트에 생성되며 답을 얻기 위해 해 찾기 추가 기능이 거친 프로세스에 대한 정보를 제공합니다.

우리의 경우 보고서가 그다지 흥미롭지 않고 흥미로운 정보가 많지 않습니다. 그러나 더 복잡한 해 찾기 방정식을 실행하면 이 새 워크시트에서 유용한 보고 정보를 찾을 수 있습니다. 그냥 클릭 + 자세한 정보를 보려면 보고서 측면에 있는 버튼을 클릭하세요.

솔버 고급 옵션

통계에 대해 잘 모르는 경우 Solver의 고급 옵션을 무시하고 그대로 실행할 수 있습니다. 그러나 크고 복잡한 계산을 실행하는 경우 해당 계산을 살펴볼 수 있습니다.

가장 분명한 것은 해결 방법입니다.

GRG Nonlinear, Simplex LP 및 Evolutionary 중에서 선택할 수 있습니다. Excel은 각각을 언제 사용해야 하는지에 대한 간단한 설명을 제공합니다. 더 나은 설명을 위해서는 통계 및 회귀에 대한 약간의 지식이 필요합니다.

추가 설정을 조정하려면 옵션 단추. Excel에 정수 최적성에 대해 알리고, 계산 시간 제약 조건(대량 데이터 세트에 유용)을 설정하고, GRG 및 Evolutionary 해결 방법이 계산을 수행하는 방식을 조정할 수 있습니다.

다시 말하지만, 이것이 무엇을 의미하는지 모르더라도 걱정하지 마십시오. 어떤 해결 방법을 사용해야 하는지 자세히 알고 싶다면 Engineer Excel은 당신을 위해 그것을 배치하는 좋은 기사 . 최대 정확도를 원한다면 Evolutionary가 좋은 방법일 것입니다. 시간이 오래 걸린다는 점만 알아두세요.

Goal Seek 및 Solver: Excel을 한 단계 더 끌어올리기

이제 Excel에서 알 수 없는 변수를 푸는 기본 사항에 익숙해졌으므로 완전히 새로운 스프레드시트 계산의 세계가 열립니다.

Goal Seek는 일부 계산을 더 빠르게 수행하여 시간을 절약하는 데 도움이 되며 Solver는 엑셀의 계산 능력 .

그들과 편안하게 지내는 것이 중요합니다. 더 많이 사용할수록 더 유용해집니다.

스프레드시트에서 Goal Seek 또는 Solver를 사용합니까? 최상의 답변을 얻기 위해 제공할 수 있는 다른 팁은 무엇입니까? 아래 댓글에 여러분의 생각을 공유해주세요!

공유하다 공유하다 트위터 이메일 VirtualBox Linux 시스템을 강화하는 5가지 팁

가상 머신이 제공하는 열악한 성능에 지쳤습니까? VirtualBox 성능을 높이려면 다음을 수행해야 합니다.

다음 읽기
관련 항목
  • 생산력
  • 스프레드시트
  • 마이크로 소프트 엑셀
  • 마이크로소프트 오피스 팁
저자 소개 그럼 올브라이트(게시된 기사 506건)

Dann은 기업이 수요와 리드를 생성하도록 돕는 콘텐츠 전략 및 마케팅 컨설턴트입니다. 그는 또한 dannalbright.com에서 전략 및 콘텐츠 마케팅에 대한 블로그를 운영하고 있습니다.

댄 올브라이트가 참여한 작품 더보기

뉴스레터 구독

뉴스레터에 가입하여 기술 팁, 리뷰, 무료 전자책 및 독점 거래를 확인하십시오!

구독하려면 여기를 클릭하세요.