Cum să utilizați scopul obiectivului Excel și Solver să rezolve pentru variabile necunoscute

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

Dar nu ar fi frumos dacă ar putea rezolvați pentru variabile necunoscute?

Cu scopul de a căuta și Solver add-in, se poate. Și vă vom arăta cum. Citiți mai departe pentru un ghid complet despre modul de rezolvare pentru o singură celulă cu obiectivul de căutare sau o ecuație mai complicată cu Solver.

Cum se utilizează scopul de a căuta în Excel

Căutarea obiectivelor este deja integrată în Excel. Este sub Date în tabul Analiza Ce-Dacă meniul:

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

După cum puteți vedea, totalul vânzărilor este de 114.706 unități. Dacă vrem să vindem 250.000 până la sfârșitul anului, câți avem nevoie să vindem în T4? Scopul obiectivului Excel ne va spune.

Iată cum să utilizați obiectivul Căutați, pas cu pas:

1. Clic Date> Analiza Ce-Dacă> Scopul Obiectivului. Veți vedea această fereastră:
   
2. Pune “este egală” parte a ecuației dvs. în Setați Cell camp. Acesta este numărul pe care Excel va încerca să îl optimizeze. În cazul nostru, este numărul total de numere de vânzări din celula B5.
3. 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.
4. Spuneți Excel care variabilă pentru a rezolva pentru în Prin schimbarea celulei camp. Vrem să vedem ce trebuie să fie vânzările noastre în Q4. Așa că îi vom spune Excel să rezolve pentru celula D2. Va arăta astfel când este gata să plece:
   
5. Lovit O.K pentru a rezolva scopul tău. Când arată bine, doar lovit O.K. Excel vă va anunța când Goal Seek a găsit o soluție.
   
6. Clic O.K 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. Bineînțeles, am fi putut găsi tocmai acest lucru scăzând totalul de la obiectivul anual. Dar obiectivul de căutare poate fi, de asemenea, utilizat pe o celulă care are deja date în el. Și asta este mai util.

Rețineți că Excel suprascrie datele noastre anterioare. Este o idee bună rulați obiectivul Căutați pe o copie a datelor dvs.. Este, de asemenea, o idee bună să faceți o notă despre datele copiate pe care a fost generată utilizând modul Căutarea obiectivelor. Nu doriți să o confundați pentru date actuale și exacte.

Deci, obiectivul de căutare este o caracteristică utilă Excel 16 Formule Excel care vă vor ajuta să rezolvați problemele vieții reale 16 Formule Excel care vă vor ajuta să rezolvați problemele legate de viața reală Instrumentul potrivit este jumătate din lucrare. Excel poate rezolva calculele și procesează datele mai repede decât vă puteți găsi calculatorul. Vă arătăm formule Excel cheie și demonstrați cum să le utilizați. Citiți mai multe, dar nu este chiar atât de impresionant. Să aruncăm o privire la un instrument care este mult mai interesant: add-in Solver.

Ce face rezolvatorul Excel?

Pe scurt, Solver este ca un versiunea multivariată a obiectivului de căutare. Este nevoie de o variabilă țintă și ajustează o serie de alte variabile până când primește răspunsul dorit.

Se poate rezolva pentru o valoare maximă a unui număr, a unei valori minime a unui număr sau a unui număr exact.

Și funcționează în constrângeri, 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 foarte bună de a rezolva mai multe variabile necunoscute în Excel. Dar găsirea și folosirea lui nu este simplă.

Să aruncăm o privire asupra încărcării add-in-ului Solver, apoi să sară în modul de utilizare a Solver în Excel 2016.

Cum se încarcă add-in Solver

Excel nu are Solver implicit. Este un add-in, ca și alte funcții Excel Power Up cu 10 add-ins pentru procesarea, analizarea și vizualizarea datelor ca un Pro Power Up Excel cu 10 add-ins pentru procesarea, analiza și vizualizarea datelor ca un Pro Vanilla Excel este uimitor, dar puteți face și mai puternic cu add-ins. Indiferent de datele de care aveți nevoie pentru a procesa, șansele sunt cineva a creat o aplicație Excel pentru ea. Iată o selecție. Citiți mai multe, trebuie să o încărcați mai întâi. Din fericire, este deja pe calculatorul dvs..

Veniți la Fișier> Opțiuni> Adăugări. Apoi faceți clic pe Merge lângă Gestionați: add-ins-uri Excel.

Dacă această meniuri arată altceva decât “Excel Add-Ins,” va trebui să o schimbați:

În fereastra care va rezulta, veți vedea câteva opțiuni. Asigurați-vă că caseta de lângă Solver Add-In este verificată și lovită O.K.

Veți vedea acum Solver buton în Analiză grupul de Date fila:

Dacă ați folosit deja Instrumentul de analiză a datelor Cum se face Analiza datelor de bază în Excel Cum se face Analiza datelor de bază în Excel Excel nu este destinat analizei datelor, dar se poate ocupa și de statistici. Vă vom arăta cum să utilizați add-in-ul Tool Analysis Tool pentru a rula statisticile Excel. Citiți mai multe, veți vedea butonul Analiză date. Dacă nu, solverul va apărea singur.

Acum că ați încărcat add-in-ul, să aruncăm o privire asupra modului de utilizare a acestuia.

Cum se utilizează Solver în Excel

Există trei părți la orice acțiune Solver: obiectivul, celulele variabile și constrângerile. Vom trece prin fiecare pas.

1. Clic Date> Solver. Veți vedea fereastra Solver Parameters de mai jos. (Dacă nu vedeți butonul Solver, consultați secțiunea anterioară despre cum să încărcați add-in Solver.)
   
2. Stabiliți obiectivul dvs. de celule și spuneți Excel scopul. Obiectivul se află în partea de sus a ferestrei Solver și are două părți: celula obiectivă și o alegere de maximizare, minimizare sau valoare specifică.
   
   Dacă selectați Max, Excel vă va ajusta variabilele pentru a obține cel mai mare număr posibil în celula dvs. obiectivă. min este invers: Solverul va minimiza numărul obiectiv. Valoarea vă permite să specificați un număr specific pentru Solver pentru a căuta.
3. Alegeți celulele variabile pe care Excel le poate schimba. 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 pot varia. Dacă nu doriți ca o celulă să se schimbe, nu o selectați.
   
4. Setați constrângerile pe variabilele multiple sau individuale. În sfârșit, ajungem la constrângeri. Aici Solver este foarte puternic. În loc să schimbați oricare dintre celulele variabile la orice număr doriți, puteți specifica constrângerile care trebuie îndeplinite. Pentru detalii, consultați secțiunea privind stabilirea constrângerilor de mai jos.
5. Odată ce toate aceste informații sunt în vigoare, apăsați Rezolva pentru a obține răspunsul dvs. 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 să setați constrângerile î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 merge sub 201 cu acea variabilă particulară.

Pentru a adăuga o constrângere, faceți clic pe Adăuga butonul de lângă lista de constrângeri. Vei primi o fereastră nouă. Selectați celula (sau celulele) care urmează să fie constrânsă în Referința celulelor câmp, 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)
• cos (trebuie să fie 1 sau 0)
• AllDifferent

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

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

Este important să vă amintiți că puteți selectați mai multe celule pentru Referința celulară. Dacă doriți ca șase variabile să aibă valori de peste 10, de exemplu, le puteți selecta pe toate și le puteți spune Solver că ele trebuie să fie mai mari sau egale cu 11. Nu este necesar să adăugați o constrângere pentru fiecare celulă.

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

Un exemplu de Solver

Pentru a vedea cum funcționează toate acestea, vom utiliza extensia Solver pentru a face un calcul rapid. Iată datele de la care pornim:

În ea, avem cinci locuri de muncă diferite, fiecare dintre care plătește o rată diferită. De asemenea, avem numărul de ore pe care un lucrător teoretic la lucrat în fiecare dintre aceste locuri de muncă într-o anumită săptămână. Putem folosi add-in Solver pentru a afla cum să maximizați salariul total, păstrând în același timp anumite variabile în anumite constrângeri.

Iată constrângerile pe care le vom folosi:

• Nu există locuri de muncă poate scădea sub patru ore.
• Locul 2 trebuie să fie mai mult de opt ore.
• Trebuie să fie locul 5 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 de acest gen înainte de a utiliza Solver.

Iată cum am stabilit asta în Solver:

Mai întâi, rețineți că Am creat o copie a tabelului așa că nu suprascrieți versiunea originală, care conține orele noastre de lucru actuale.

Și în al doilea rând, a se vedea că valorile în mai mare decât și mai puțin decât constrângerile sunt unul mai mare sau mai mic decât ceea ce am menționat mai sus. Asta pentru că nu există opțiuni mai mari decât sau mai puțin decât opțiuni. Există doar mai mult decât-sau-egal-și mai puțin decât-sau-egal-la.

Să loviți Rezolva și vezi ce se întâmplă.

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

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

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 subliniate (vă recomandăm) și apăsați O.K.

Rapoartele sunt generate pe foi noi din registrul dvs. de lucru și vă oferă informații despre procesul în care a trecut solverul 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ă executați o ecuație mai solioasă a Solver, este posibil să găsiți câteva informații utile de raportare în aceste foi de lucru noi. Doar faceți clic pe + pe partea laterală a oricărui raport pentru a obține mai multe informații:

Solver Advanced Options

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

Cel mai evident este metoda de rezolvare:

Puteți alege între GRG Nonlinear, Simplex LP și Evolutionary. Excel oferă o explicație simplă cu privire la momentul în care ar trebui să utilizați fiecare. O explicație mai bună necesită o cunoaștere a statisticilor Aflați statistici gratis cu aceste 6 resurse Aflați statistici gratis cu aceste 6 resurse Statisticile au o reputație de subiect care este greu de înțeles. Însă învățarea din resursele potrivite vă va ajuta să înțelegeți în cel mai scurt timp rezultatele sondajului, rapoartele electorale și alocările de clasă pentru statistici. Citește mai mult și regresie.

Pentru a ajusta setările suplimentare, apăsați butonul Opțiuni buton. Puteți să-i spuneți Excel despre optimitatea întregului, să stabilească constrângerile de timp de calcul (utile pentru seturile de date masive) și să ajusteze modul în care metodele de rezolvare GRG și Evolutionary go about făcând calculele.

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 rezolvare să utilizați, Inginer Excel are un articol bun care vă pune problema. Dacă doriți precizie maximă, Evolutionary este probabil o modalitate bună de a merge. Trebuie doar să știți că va dura mult timp.

Căutarea și rezolvarea obiectivelor: luând Excel la nivelul următor

Acum, că vă simțiți confortabil cu bazele de rezolvare a variabilelor necunoscute în Excel, vă este deschisă o lume cu totul nouă de calculare a foilor de calcul.

Obiectivul de căutare vă poate ajuta să economisiți timp făcând unele calcule mai repede, iar Solver adaugă o cantitate imensă de putere abilităților de calcul ale Excel. Cum se calculează statisticile de bază în Excel: Ghidul unui începător Cum se calculează statisticile de bază în Excel: Ghidul începătorului Microsoft Excel poate face statistici! Puteți calcula procentajele, mediile, deviația standard, eroarea standard și testele T ale elevului. Citeste mai mult .

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

Folosești obiectivul Caută sau Solver în foile de calcul? Ce alte sfaturi le puteți oferi pentru obținerea celor mai bune răspunsuri din ele? Împărtășiți-vă gândurile în comentariile de mai jos!

Explorați mai multe despre: Microsoft Excel, Sfaturi Microsoft Office, Spreadsheet.