Programmazione lineare di Excel (sommario)
- Introduzione alla programmazione lineare in Excel
- Metodi per risolvere la programmazione lineare tramite Risolutore Excel
Introduzione alla programmazione lineare in Excel
La programmazione lineare è molto importante e un aspetto affascinante della matematica applicata che aiuta nell'ottimizzazione delle risorse (minimizzando le perdite o massimizzando il profitto con determinate risorse). Se abbiamo vincoli e la funzione obiettivo ben definiti, possiamo usare il sistema per prevedere una soluzione ottimale per un dato problema. In Excel, abbiamo Excel Solver che ci aiuta a risolvere i problemi di programmazione lineare aka LPP. Vedremo in questo articolo come utilizzare Excel Solver per ottimizzare le risorse associate a problemi aziendali con l'aiuto della Programmazione lineare.
Prima cosa. Vediamo come abilitare il Risolutore Excel (un componente chiave di LPP in Excel).
Metodi per risolvere la programmazione lineare tramite Risolutore Excel
Comprendiamo come utilizzare la Programmazione lineare tramite il risolutore di Excel con alcuni metodi.
Puoi scaricare questo modello di Excel di programmazione lineare qui - Modello di Excel di programmazione lineare
Metodo n. 1: abilitazione del Risolutore in Microsoft Excel
In Microsoft Excel, possiamo trovare Risolutore nella scheda Dati che puoi trovare sulla barra multifunzione di Excel posizionata nella parte più in alto come mostrato di seguito:
Se non riesci a vedere questo strumento di utilità lì, devi abilitarlo tramite Opzioni di Excel. Seguire i passaggi seguenti per abilitare il Risolutore in Excel.
Passaggio 1: navigare verso il menu File e fare clic su Opzioni, che è l'ultima cosa in quell'elenco.
Passaggio 2: verrà visualizzata una nuova finestra denominata Opzioni di Excel. Fai clic su Componenti aggiuntivi dall'elenco delle opzioni presenti sul lato sinistro della finestra.
Passaggio 3: Sotto la sezione Gestisci nella parte inferiore della finestra, seleziona Componenti aggiuntivi di Excel dall'elenco a discesa e fai clic sul pulsante Vai … posizionato accanto.
Passaggio 4: Non appena fai clic sul pulsante Vai …, sarai in grado di visualizzare l'elenco di tutti i componenti aggiuntivi disponibili in Excel in una nuova finestra. Spuntare per selezionare il componente aggiuntivo Risolutore in modo da poterlo utilizzare nella scheda Dati per risolvere le equazioni. Fare clic sul pulsante OK dopo aver selezionato il componente aggiuntivo Risolutore.
In questo modo, è possibile abilitare il Risolutore Excel in Microsoft Excel.
Metodo n. 2 - Risoluzione del problema di programmazione lineare mediante Risolutore Excel
Ora proveremo a risolvere il problema di programmazione lineare usando lo strumento Risolutore Excel.
Esempio: un impianto chimico produce due prodotti, ovvero A e B. Questi due prodotti necessitano di materie prime come mostrato di seguito: Il prodotto A ha bisogno di tre tipi di materie prime: Materiale_1 20 kg, Materiale_2 30 kg, Materiale_3 come 5 kg. Su linee simili, il prodotto B richiede 10 kg di materiale_1, 30 kg di materiale_2 e 10 kg di materiale_3. Il produttore richiede un minimo di 460 kg o materiale_1, 960 kg di materiale_2 e 220 kg di materiale_3. Se il costo unitario per il Prodotto A è di $ 30 e il costo del Prodotto B è di $ 35, quanti prodotti il produttore dovrebbe miscelare per soddisfare i requisiti minimi di materiale al minor costo possibile? Usiamo le informazioni fornite in questo esempio per modellare le equazioni.
Passaggio 1: possiamo vedere tutti i vincoli di equazione che possiamo formare usando le informazioni fornite nell'esempio sopra.
Passaggio 2: utilizzare queste equazioni per aggiungere i vincoli per cella sotto Excel in A2: C8 del foglio specificato. Vedi lo screenshot come di seguito:
Passo 3: Ora, dobbiamo usare la formula Quantità * Per costo unitario e sommarla per entrambi i prodotti per ottenere i reali requisiti di materiale. Puoi vedere questo formulato nella colonna D per tutte le celle che contengono vincoli B3, B4, C3). Si prega di vedere lo screenshot allegato di seguito:
Se darai un'occhiata più da vicino a questa formula, abbiamo usato B3 e C3 come membri fissi per ciascuna formula attraverso le diverse celle nella colonna D. Questo perché B3 e C3 sono le celle che indicano rispettivamente le quantità per Prodotto A e Prodotto B. Queste quantità appariranno una volta risolto il sistema di equazioni usando Excel Solver.
Passaggio 4: fare clic sulla scheda Dati e quindi su Risolutore presente nella sezione Analizza nella scheda.
Passaggio 5: Dopo aver fatto clic su Risolutore, si aprirà una nuova scheda denominata "Parametro Risolutore" in base alla quale è necessario impostare i parametri per questo set di equazioni da risolvere.
Step 6: La prima cosa che dobbiamo identificare è Set Object: Dal momento che il nostro obiettivo è capire il costo totale in modo che possa essere minimizzato, impostalo su D4.
Passaggio 7: poiché è necessario ridurre al minimo i costi con la massima produzione possibile, impostare il parametro successivo su Min. Puoi farlo facendo clic sul pulsante di opzione Min.
Passaggio 8: in Modifica delle celle variabili: dobbiamo menzionare B3 e C3 poiché queste celle saranno quelle contenenti rispettivamente le quantità per il prodotto A e B dopo la risoluzione del problema.
Passaggio 9: ora aggiungi i vincoli. Fai clic sul pulsante Aggiungi nella sezione Soggetto a Vincoli: e si aprirà una nuova finestra per aggiungere vincoli. Sotto quella finestra - B3: C3 come riferimento cella, > = e 0 come vincoli. Questo lo stiamo facendo, poiché il vincolo di base in qualsiasi LPP è che X e Y dovrebbero essere maggiori di zero.
Passaggio 10: fare di nuovo clic sul pulsante Aggiungi e questa volta utilizzare B3: C3 come riferimento cella e F6: F8 come vincoli con disuguaglianza come> =. Fare clic sul pulsante OK per aggiungere questo vincolo anche sotto il solutore.
Il Risolutore ora ha tutti i parametri richiesti per risolvere questo insieme di equazioni lineari e appare come di seguito:
Passo 11: Ora, fai clic sul pulsante Risolvi nella parte inferiore della finestra per risolvere questa equazione lineare e raggiungere la soluzione ottimale.
Non appena facciamo clic sul pulsante di risoluzione, il sistema inizia a cercare la soluzione ottimale per il problema che abbiamo fornito e otteniamo i valori per B3, C3 utilizzando i quali otteniamo anche i valori nella colonna F per F4, F6: F8. Quali sono i costi e i valori dei materiali ottimali che possono essere utilizzati per il prodotto A e il prodotto B.
Questa soluzione ci informa che, se dobbiamo ridurre al minimo i costi di produzione per il prodotto A e il prodotto B con un utilizzo ottimale di Material_1, Material_2 e Material_3, dovremmo produrre 14 quantità di prodotto A e 18 quantità di prodotto B.
Questo è da questo articolo. Concludiamo con alcuni punti da ricordare:
Cose da ricordare sulla programmazione lineare in Excel
- È obbligatorio risolvere i problemi di programmazione lineare utilizzando il Risolutore Excel. Non c'è altro metodo che possiamo fare usando.
- Dovremmo sempre avere vincoli e variabili oggetto da impostare pronti con noi.
- Se il Risolutore non è abilitato, è possibile abilitarlo nelle opzioni del componente aggiuntivo di Excel.
Articoli consigliati
Questa è una guida alla programmazione lineare in Excel. Qui discutiamo come usare la programmazione lineare in Excel insieme ad esempi pratici e template Excel scaricabili. Puoi anche consultare i nostri altri articoli suggeriti:
- Interpolare in Excel
- Programmazione in Excel
- Spostamento di colonne in Excel
- Matrice inversa in Excel