Cum să automatizezi Excel Solver

În Cum să obțineți mai multe din Excel Solver, am acoperit tehnici pentru a vă îmbunătăți modelul și a vă împuternici analiza. Unul dintre subiectele la care am primit cele mai multe întrebări este automatizarea rezolutorului pentru a rezolva mai multe valori.

A existat o scurtă explicație în articolul precedent, dar nimic dedicat modului de implementare de la început până la sfârșit. În acest articol, vom detalia de la început până la sfârșit conceptele și metodele pas cu pas. Până la sfârșit, veți putea să automatizați oricare dintre propriile modele personale și să vă scalați analiza.

Cum o vom face

Vom folosi macro recorder-ul din Excel pentru a gestiona cea mai mare parte a codului real. Instrumentul de înregistrare macro va permite Excel să convertească pașii noștri manuali în cod VBA.

Vom identifica apoi piesele cheie de cod de schimbat. Apoi vom crea o buclă pentru a selecta și înlocui dinamic aceste valori și vom rula din nou soluția.

Acest lucru va fi semi-tehnic, dar prin acest stil de dezvoltare ne putem concentra asupra procesului și conceptelor în loc să ne concentrăm asupra modului de scriere a codului.

Configurarea reportofonului macro

Funcționalitatea de înregistrare macro se găsește în fila pentru dezvoltatori din panglică. Acest lucru nu este disponibil implicit, dar este ușor de implementat. Dacă navigați la opțiunile de panglică, veți vedea o casetă de selectare pentru fila dezvoltator.

Captura de ecran de mai jos este pentru Mac, dar Excel bazat pe PC este similar.

Dacă nu ați instalat suplimentul de soluționare, îl puteți instala prin meniul Add-ins. Site-ul de asistență Microsoft are instrucțiuni pentru toate platformele, Încărcați programul de completare Solver în Excel.

Înregistrarea macro-ului

Acum că configurarea este completă, suntem gata să înregistrăm! Configurați-vă spațiul de lucru în mod corespunzător cu toate formulele și referințele dvs. Veți dori să configurați totul la obiect înainte de a construi modelul de rezolvare.

Întoarceți-vă la fila pentru dezvoltatori de pe panglică. Veți vedea o bucată de hârtie cu un punct roșu. Faceți clic pe acel buton pentru a deschide reportofonul.

Dați un nume macrocomenzii și completați descrierea dacă doriți.

Odată ce apăsați pe Ok, reportofonul va începe.

Parcurgeți pașii de configurare a solutorului și rezolvați-l. Odată ce acest lucru este complet, reveniți la fila dezvoltator și opriți înregistrarea. Va fi aceeași locație cu butonul pe care l-ați folosit pentru a începe înregistrarea.

Acum că avem codul de bază, îl putem curăța și îl putem pregăti pentru următorii pași.

Curățați codul

Acum putem edita codul, elimina orice părți inutile și ne asigurăm că folosim doar părțile esențiale ale înregistrării macro.

Puteți edita codul prin editorul VBA al Excel. Pentru a accesa codul înregistrat, faceți clic pe butonul Macro-uri.

Navigați la macrocomandă în noul meniu și faceți clic pe Editați.

Acum că avem editorul de cod deschis, putem începe să facem modificări.

Eliminarea codului inutil

Dacă s-a întâmplat să dați clic pe o grămadă de alte lucruri în timpul înregistrării, le puteți șterge acum și rămâne cu ceva similar cu mai jos. Dacă există o mulțime de cod suplimentar și ești îngrijorat să-l spargi, poți oricând să reînregistrezi.

Configurarea referințelor de rezolvare

Un ultim pas înainte de a merge mai departe, mergeți la Instrumente > Referințe și selectați Solver. Dacă nu faceți acest lucru, pachetul nu se va încărca în macro și veți primi o eroare.

Acum vă puteți modifica parametrii, deschideți meniul de macrocomenzi, selectați macrocomandă, apăsați Run și va actualiza rezultatele.

Să aliniem codul cu modelul nostru de rezolvare. Vei vedea cum se aliniază. Limbajul este diferit, dar puteți pune cap la cap ce înseamnă totul.

În continuare, vom izola piesele pe care trebuie să le schimbăm și vom finaliza automatizarea.

Clătiți și repetați - construiți bucla

Acum că avem un rezolvator de lucru. Trebuie să repetăm ​​asta de mai multe ori. Să punem un exemplu împreună și să cimentăm planul.

Am început cu un buget de 1.000 USD în primul model, dar vrem să facem același lucru pentru creșteri de 100 USD până la 2500 USD.

Să ne scufundăm! Avem o listă de valori în coloana B și rezultatul nostru în coloana C. Dacă am scrie procesul, ar arăta așa:

1. Setați cheltuielile totale la valoarea din B10.
2. Rezolvați modelul.
3. Plasați ieșirea în C10.
4. Alegeți următoarea valoare B.
5. Setați rezultatul în rândul C.
6. Repetați pașii 2-5 pentru toate valorile din B.

Începând bucla

Buclele sunt constructe de codare care repetă o acțiune până la atingerea unui punct final logic. O buclă va fi utilă aici pentru a repeta același proces, rulând soluția, pentru fiecare valoare până ajungem la sfârșitul valorii.

Pentru acest exemplu, vom defini punctul final după numărul de rânduri. B10:B25 are 16 valori.

Vom începe bucla și o vom încheia în jurul codului nostru de rezolvare. Vom configura o variabilă pentru a păstra numărul buclei (i) și vom trece prin fiecare valoare, rezolvând bucla de fiecare dată.

Resetarea rezolutorului

Vom adăuga încă o condiție la începutul codului nostru. Deoarece dorim să reluăm solutorul la fiecare iterație, îl vom reseta în fiecare buclă. Acest lucru va șterge setările și va începe un nou model.

Putem face acest lucru prin SolverReset .

Actualizarea referințelor de celule

Acum rezolvăm de șaisprezece ori, dar trebuie să ne actualizăm referințele de celule. Deși inițial ne-am referit la B10, trebuie să deplasăm în jos o celulă la fiecare rulare pentru a actualiza limitele. Putem face acest lucru prin intermediul funcției de offset. Putem actualiza „$B$10” pentru a face referire la celulă, apoi compensată cu i rânduri (0 la prima rulare, 1 rând la a doua și așa mai departe). De fiecare dată când bucla rulează, i crește cu unu.

Colectarea rezultatelor

Ieșirea se va schimba de fiecare dată, așa că vom dori să o salvăm. Putem urma același proces de mai sus, dar depășim de la C10 și îl setăm egal cu valoarea modelului rezolvat. Apoi bucla începe de la capăt.

Vom apela C10 compensat cu numărul corespunzător de rânduri, apoi lipim valoarea rezultatului nostru.

Ocolind notificările de rezolvare

Să ne ferim de o bătaie de cap și să nu învățăm prin exemplu pentru o clipă. Îți amintești când ai rulat soluția și a apărut meniul care te întreba dacă vrei să accepți soluția? Acesta va apărea de fiecare dată dacă nu îl dezactivați. Imaginează-ți dacă ai configura o buclă cu 100 de valori, ar trebui să dai clic pe Accept de fiecare dată!

Continuați și adăugați următoarele rânduri după codul dvs. de rezolvare și adăugați (adevărat) la SolverSolve. Acest lucru vă va imita acceptarea modificărilor.

Acum avem codul terminat!

În funcțiune

Acum putem rula codul selectându-l din lista de macrocomandă și rulând. Dacă doriți să utilizați registrul de lucru ca șablon, puteți atribui macrocomenzii unui buton pentru ușurință de utilizare. Le puteți găsi în fila pentru dezvoltatori.

De asemenea, puteți adăuga orice grafice de comparație. În acest caz, dorim să comparăm rata de creștere a cheltuielilor cu creșterea volumului de conversie. Acest exemplu nu este interesant, dar datele reale pot ajuta la dezvăluirea punctelor de întrerupere a eficienței

Concluzie

Am acoperit destul de mult! Felicitări pentru că ați pus în funcțiune soluția automată. Exemplul pe care l-am acoperit a fost prea simplist, dar puteți lua aceleași concepte și îl puteți aplica oricărui model de rezolvare.

Acest lucru poate economisi o cantitate enormă de timp pentru modelele pe care trebuie să le rezolvați de mai multe ori și încurajează utilizatorii să experimenteze cu diferite scenarii.

Data publicării originale: 30.07.2019