Cum se utilizează Căutarea obiectivelor Excel și Solver pentru a rezolva pentru variabile necunoscute

Excel este foarte capabil atunci când aveți toate datele de care aveți nevoie pentru calcule.

Dar n-ar fi frumos dacă ar putea rezolvați pentru variabilele necunoscute ?

Cu Goal Seek și programul de completare Solver, se poate. Și vă vom arăta cum. Citiți mai departe pentru un ghid complet despre cum să rezolvați o singură celulă cu Goal Seek sau o ecuație mai complicată cu Solver.

Cum se folosește Căutarea obiectivelor în Excel

Goal Seek este deja încorporat în Excel. Este sub Date filă, în Analiza What-If meniul:

Pentru acest exemplu, vom folosi un set foarte simplu de numere. Avem cifre de vânzări în valoare de trei sferturi și un obiectiv anual. Putem folosi Goal Seek pentru a afla ce cifre trebuie să fie în Q4 pentru a atinge obiectivul.

După cum puteți vedea, totalul actual al vânzărilor este de 114.706 unități. Dacă vrem să vindem 250.000 până la sfârșitul anului, câți trebuie să vindem în T4? Obiectivul Căutării Excel ne va spune.

Iată cum să utilizați Căutarea obiectivelor, pas cu pas:

Clic Date> Analiză ce se întâmplă> Căutare obiectiv . Veți vedea această fereastră:
Puneți partea „egală” a ecuației în Setați celula camp. Acesta este numărul pe care Excel va încerca să îl optimizeze. În cazul nostru, este numărul total de vânzări din celula B5.
Introduceți valoarea obiectivului în A valorifica camp. Căutăm un total de 250.000 de unități vândute, așa că vom pune „250.000” în acest domeniu.
Spuneți Excel pentru ce variabilă să rezolvați în Prin schimbarea celulei camp. Vrem să vedem care trebuie să fie vânzările noastre în Q4. Deci, îi vom spune Excel să rezolve celula D2. Va arăta așa când va fi gata:
Lovit Bine să-ți rezolvi obiectivul. Când arată bine, atinge Bine . Excel vă va anunța când Goal Seek a găsit o soluție.
Clic Bine din nou și veți vedea valoarea care vă rezolvă ecuația în celula pentru care ați ales Prin schimbarea celulei .

În cazul nostru, soluția este de 135.294 unități. Desigur, am fi putut constata asta scăzând totalul curent din obiectivul anual. Dar Goal Seek poate fi folosit și pe o celulă care are deja date în ea . Și asta este mai util.

Rețineți că Excel suprascrie datele noastre anterioare. Este o idee bună să rulați Goal Seek pe o copie a datelor dvs. . De asemenea, este o idee bună să notați datele copiate că acestea au fost generate folosind Goal Seek. Nu doriți să o confundați pentru date actuale și exacte.

cum să blochezi pe cineva în contracție

Deci Goal Seek este un funcție Excel utilă , dar nu este atât de impresionant. Să aruncăm o privire la un instrument mult mai interesant: programul de completare Solver.

Ce face Solverul Excel?

Pe scurt, Solver este ca un versiune multivariată a obiectivului Seek . Este nevoie de o variabilă de obiectiv și ajustează o serie de alte variabile până când obține răspunsul dorit.

Poate rezolva o valoare maximă a unui număr, o valoare minimă a unui număr sau un număr exact.

Și funcționează în condiții de constrângere, deci dacă o variabilă nu poate fi modificată sau poate varia doar într-un interval specificat, Solver va lua în considerare acest lucru.

Este o modalitate excelentă de a rezolva mai multe variabile necunoscute în Excel. Dar găsirea și utilizarea acestuia nu sunt simple.

Să aruncăm o privire la încărcarea programului de completare Solver, apoi să trecem la modul de utilizare a Solverului în Excel 2016.

Cum se încarcă programul de completare Solver

Excel nu are Solver în mod implicit. Este un supliment, așa că, ca și alte funcții Excel puternice, trebuie să îl încărcați mai întâi. Din fericire, este deja pe computer.

Mergeți spre Fișier> Opțiuni> Suplimente . Apoi faceți clic pe Merge lângă Gestionați: programe de completare Excel .

Dacă această listă verticală spune altceva decât „Completări Excel”, va trebui să o modificați:

În fereastra rezultată, veți vedea câteva opțiuni. Asigurați-vă că caseta de lângă Complet Solver este verificat și lovit Bine .

Veți vedea acum Solver butonul din Analiză grupul de Date filă:

Dacă ați folosit deja fișierul Instrument de analiză a datelor , veți vedea butonul Analiză date. Dacă nu, Solver va apărea de la sine.

Acum că ați încărcat programul de completare, să aruncăm o privire la modul de utilizare.

Cum se folosește Solver în Excel

Există trei părți ale oricărei acțiuni Solver: obiectivul, celulele variabile și constrângerile. Vom parcurge fiecare dintre pași.

Clic Date> Solver . Veți vedea mai jos fereastra Solver Parameters. (Dacă nu vedeți butonul Solver, consultați secțiunea anterioară despre cum să încărcați programul de completare Solver.)
Setați obiectivul celulei și spuneți Excel obiectivului dvs. Obiectivul se află în partea de sus a ferestrei Solver și are două părți: celula obiectivului și o alegere de maximizare, minimizare sau o valoare specifică. Dacă selectați Max , Excel vă va ajusta variabilele pentru a obține cel mai mare număr posibil în celula obiectivului. Min este opusul: Solverul va minimiza numărul obiectivului. Valoarea vă permite să specificați un număr specific pe care Solver să-l caute.
Alegeți celulele variabile pe care Excel le poate modifica. Celulele variabile sunt setate cu Prin schimbarea celulelor variabile camp. Faceți clic pe săgeata de lângă câmp, apoi faceți clic și trageți pentru a selecta celulele cu care Solver ar trebui să funcționeze. Rețineți că acestea sunt toate celulele care poate varia. Dacă nu doriți ca o celulă să se schimbe, nu o selectați.
Setați constrângeri pentru variabile multiple sau individuale. În cele din urmă, ajungem la constrângeri. Aici Solver este cu adevărat puternic. În loc să schimbați oricare dintre celulele variabile la orice număr dorește, puteți specifica constrângeri care trebuie îndeplinite. Pentru detalii, consultați secțiunea despre cum să setați constrângerile de mai jos.
Odată ce toate aceste informații sunt la locul lor, apăsați Rezolva pentru a obține răspunsul tău. Excel vă va actualiza datele pentru a include noile variabile (de aceea vă recomandăm să creați mai întâi o copie a datelor dvs.).

De asemenea, puteți genera rapoarte, pe care le vom analiza pe scurt în exemplul nostru Solver de mai jos.

Cum se setează constrângeri în Solver

S-ar putea să spuneți Excel că o variabilă trebuie să fie mai mare 200. Când încercați diferite valori variabile, Excel nu va intra sub 201 cu acea variabilă specială.

Pentru a adăuga o constrângere, faceți clic pe Adăuga butonul de lângă lista de constrângeri. Veți obține o fereastră nouă. Selectați celula (sau celulele) de restricționat în Referință celulară , apoi alegeți un operator.

Iată operatorii disponibili:

<= (mai mic sau egal cu)
= (egal cu)
=> (mai mare sau egal cu)
int (trebuie să fie un număr întreg)
a.m (trebuie să fie 1 sau 0)
AllDifferent

AllDifferent este puțin confuz. Specifică fiecare celulă din intervalul pentru care selectați Referință celulară trebuie să fie un număr diferit. Dar specifică, de asemenea, că acestea trebuie să fie între 1 și numărul de celule. Deci, dacă aveți trei celule, veți ajunge cu numerele 1, 2 și 3 (dar nu neapărat în această ordine)

În cele din urmă, adăugați valoarea pentru constrângere.

Este important să ne amintim că puteți selectați mai multe celule pentru Cell Reference. Dacă doriți ca șase variabile să aibă valori peste 10, de exemplu, le puteți selecta pe toate și spuneți Solverului că acestea trebuie să fie mai mari sau egale cu 11. Nu trebuie să adăugați o constrângere pentru fiecare celulă.

De asemenea, puteți utiliza caseta de selectare din fereastra principală Solver pentru a vă asigura că toate valorile pentru care nu ați specificat constrângeri sunt non-negative. Dacă doriți ca variabilele dvs. să devină negative, debifați această casetă.

Un exemplu de rezolvare

Pentru a vedea cum funcționează toate acestea, vom folosi programul de completare Solver pentru a face un calcul rapid. Iată datele cu care începem:

În acesta, avem cinci locuri de muncă diferite, fiecare dintre acestea plătind o rată diferită. De asemenea, avem numărul de ore pe care un lucrător teoretic le-a lucrat la fiecare dintre aceste locuri de muncă într-o săptămână dată. Putem utiliza programul de completare Solver pentru a afla cum să maximizăm plata totală, păstrând în același timp anumite variabile în anumite constrângeri.

Iată constrângerile pe care le vom folosi:

Fără locuri de muncă poate scădea sub patru ore.
Locul 2 trebuie să fie mai mare de opt ore .
Locul 5 trebuie să fie mai puțin de unsprezece ore .
Numărul total de ore lucrate trebuie să fie egal cu 40 .

Poate fi util să vă scrieți constrângerile astfel, înainte de a utiliza Solver.

Iată cum ar fi configurat asta în Solver:

În primul rând, rețineți că Am creat o copie a tabelului deci nu suprascriem originalul, care conține programul nostru actual de lucru.

Și în al doilea rând, vedeți că valorile constrângerilor mai mari decât și mai mici decât sunt unul mai sus sau mai jos decât ceea ce am menționat mai sus. Asta pentru că nu există opțiuni mai mari sau mai mici decât. Există doar mai mare decât sau egal cu și mai puțin decât sau egal cu.

Hai să lovim Rezolva și vezi ce se întâmplă.

Solver a găsit o soluție! După cum puteți vedea în stânga ferestrei de mai sus, câștigurile noastre au crescut cu 130 USD. Și toate constrângerile au fost îndeplinite.

ce tip de RAM ar putea încetini performanța sistemului

Pentru a păstra noile valori, asigurați-vă Păstrați soluția Solver este verificat și lovit Bine .

Dacă doriți mai multe informații, puteți selecta un raport din partea dreaptă a ferestrei. Selectați toate rapoartele pe care le doriți, spuneți Excel dacă doriți ca acestea să fie conturate (le recomand) și apăsați Bine .

Rapoartele sunt generate pe foi noi din registrul dvs. de lucru și vă oferă informații despre procesul prin care a trecut programul de completare Solver pentru a obține răspunsul dvs.

În cazul nostru, rapoartele nu sunt foarte interesante și nu există o mulțime de informații interesante acolo. Dar dacă rulați o ecuație Solver mai complicată, este posibil să găsiți câteva informații utile de raportare în aceste noi foi de lucru. Doar faceți clic pe + butonul din partea laterală a oricărui raport pentru a obține mai multe informații:

Opțiuni avansate Solver

Dacă nu știți multe despre statistici, puteți ignora opțiunile avansate ale Solver și rulați-le așa cum este. Dar dacă executați calcule mari și complexe, vă recomandăm să le analizați.

Cea mai evidentă este metoda de rezolvare:

Puteți alege între GRG neliniar, Simplex LP și Evolutionary. Excel oferă o explicație simplă cu privire la momentul în care ar trebui să le utilizați pe fiecare. O explicație mai bună necesită unele cunoștințe despre statistici și regresie.

Pentru a regla setări suplimentare, trebuie doar să apăsați pe Opțiuni buton. Puteți spune Excel despre optimitatea numărului întreg, setați constrângeri de timp de calcul (utile pentru seturi de date masive) și reglați modul în care metodele de rezolvare GRG și Evolutie fac calculele lor.

Din nou, dacă nu știți ce înseamnă asta, nu vă faceți griji. Dacă doriți să aflați mai multe despre ce metodă de soluționare utilizați, Engineer Excel are un articol bun care îți prezintă . Dacă doriți o precizie maximă, Evolutionary este probabil o modalitate bună de urmat. Rețineți că va dura mult timp.

Căutarea obiectivelor și rezolvarea: trecerea Excel la nivelul următor

Acum că vă simțiți confortabil cu noțiunile de bază pentru rezolvarea variabilelor necunoscute în Excel, vă este deschisă o lume complet nouă a calculului foilor de calcul.

Goal Seek vă poate ajuta să economisiți timp făcând unele calcule mai rapid, iar Solver adaugă o cantitate uriașă de energie Abilitățile de calcul ale Excel .

Este doar o chestiune de a te simți confortabil cu ei. Cu cât le folosiți mai mult, cu atât vor deveni mai utile.

Folosiți Goal Seek sau Solver în foile dvs. de calcul? Ce alte sfaturi puteți oferi pentru a obține cele mai bune răspunsuri? Împărtășiți-vă gândurile în comentariile de mai jos!

Acțiune Acțiune Tweet E-mail 5 sfaturi pentru a vă supraîncărca mașinile VirtualBox Linux

V-ați săturat de slaba performanță oferită de mașinile virtuale? Iată ce ar trebui să faceți pentru a vă îmbunătăți performanța VirtualBox.

Citiți în continuare Subiecte asemănătoare

Productivitate
Foaie de calcul
Microsoft Excel
Sfaturi Microsoft Office

Despre autor Apoi Albright(506 articole publicate)

Dann este un consultant în strategie de conținut și marketing care ajută companiile să genereze cerere și clienți potențiali. De asemenea, el blogează despre strategie și marketing de conținut la dannalbright.com.

Mai multe de la Dann Albright

Aboneaza-te la newsletter-ul nostru

Alăturați-vă newsletter-ului pentru sfaturi tehnice, recenzii, cărți electronice gratuite și oferte exclusive!

Faceți clic aici pentru a vă abona