Utilizzo del Risolutore per determinare la combinazione di prodotti ottimale

Nota:  Riteniamo importante fornire il contenuto della Guida più recente non appena possibile, nella lingua dell'utente. Questa pagina è stata tradotta automaticamente e potrebbe contenere errori di grammatica o imprecisioni. L'intento è quello di rendere fruibile il contenuto. Nella parte inferiore della pagina è possibile indicare se le informazioni sono risultate utili. Questo è l' articolo in lingua inglese per riferimento.

In questo articolo viene illustrato l'utilizzo del Risolutore, un componente aggiuntivo Strumenti di Microsoft Excel è possibile utilizzare per l'analisi di simulazione, per stabilire una combinazione di prodotti ottimale.

Come è possibile determinare la combinazione di prodotto mensile in grado di ottimizzare redditività?

Società, spesso è necessario determinare la quantità di ogni prodotto per produrre su base mensile. Nella forma più semplice, il problema mix di prodotti implica come determinare la quantità di ogni prodotto deve essere prodotta in un mese per ottimizzare i profitti. Combinazione di prodotti dovrà in genere rispettare le limitazioni seguenti:

  • Combinazione di prodotto non è possibile utilizzare altre risorse quelle disponibili.

  • Esiste una richiesta di limitato per ogni prodotto. È possibile produrre più di un prodotto in un mese rispetto stabilisce richiesta, perché è inutilizzata produzione in eccesso (ad esempio un farmaco deperibile).

Di seguito ora risolvere nell'esempio seguente del problema di combinazione di prodotto. Per risolvere questo problema sono disponibili nel file Prodmix.xlsx, illustrato nella figura 27-1.

Immagine del manuale
Figura 27-1 la combinazione di prodotto

Si supponga di che lavorare in che modo l'azienda che produce sei prodotti diversi. Produzione di ogni prodotto richiede fatica e materia. Riga 4 nella figura 27-1 mostra le ore di lavoro necessarie per produrre una libbra di ogni prodotto riga 5 mostra kg della materia necessari per produrre una libbra di ogni prodotto. Ad esempio, produrre una libbra del prodotto 1 richiede sei ore di lavoro e 3,2 kg della materia. Per ogni farmaco, il prezzo per libbra è indicato nella riga 6, il costo unitario per libbra è indicato nella riga 7 e il contributo di profitto per libbra è indicato nella riga 9. Prodotto 2, ad esempio, viene venduto per $11,00 libbra, costo unitario di 5,70 dollari alla libbra e contribuisce profitto 5,30 dollari alla libbra. Richiesta del mese per ogni farmaco è indicato nella riga 8. Richiesta di prodotto 3, ad esempio è 1041 kg. Questo mese, 4500 ore di lavoro e 1600 kg della materia sono disponibili. In che modo la società può massimizzare il profitto mensile?

Se si conosce nulla del Risolutore di Excel, è necessario attacchi questo problema creando un foglio di lavoro per tenere traccia dell'utilizzo di profitti e delle risorse associata la combinazione di prodotto. È possibile utilizzare tentativi ed errori per modificare la combinazione di prodotto per ottimizzare i profitti senza utilizzare più fatica o materia rispetto a quella disponibile e senza produrre un farmaco che superano la domanda. Serve Risolutore questo processo solo nella fase della versione di valutazione di errore. In pratica, Risolutore è un motore di ottimizzazione che esegue autonomamente il periodo di valutazione ed errore di ricerca.

Una chiave per risolvere il problema di mix prodotto è calcolare in modo efficiente l'utilizzo delle risorse e la profitti associati a qualsiasi combinazione di prodotto specifico. Uno strumento importante è possibile utilizzare per verificare il calcolo è la funzione MATR. Funzione MATR Moltiplica i valori corrispondenti in intervalli di celle e restituisce la somma dei valori. Ogni intervallo di celle utilizzato in una valutazione MATR deve avere le stesse dimensioni, che indica che è possibile utilizzare MATR con due righe o due colonne, ma non con una colonna e una riga.

Come esempio di come è possibile utilizzare la funzione MATR. il prodotto combinazione di esempio, utilizzare calcolare il nostro Uso risorse. Viene calcolato il nostro utilizzo fatica

*(Drug 1 pounds produced) (fatica utilizzata per libbra di farmaco 1) +
(fatica utilizzata per libbra di farmaco 2) * (libbre di farmaco 2 prodotte) +...
(Fatica utilizzata per libbra di farmaco 6) * (libbre di farmaco 6 prodotte)

È possibile calcolare l'utilizzo di lavoro in modo più noioso come D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Analogamente, materia potrebbe essere calcolato come D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. L'immissione di queste formule nei fogli di lavoro per i sei prodotti è lunga e laboriosa. Immaginare come sarebbe tempo se si utilizzava una società che prodotto, ad esempio, 50 prodotti. Per calcolare fatica e l'uso di materia semplice consiste nel copiare da D14 a D15 la formula SUMPRODUCT($D$2:$I$2,D4:I4). Questa formula calcola D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 , che corrisponde di utilizzo di fatica, ma è molto più facile immettere! Si noti che viene utilizzato il segno di $ con l'intervallo D2: I2 in modo che quando copia la formula comunque acquisire la combinazione di prodotto della riga 2. La formula nella cella D15 calcola l'uso di materia.

Analogamente, il profitto dipende dalla

(Profitto del farmaco 1 libbra) * (1 delle kg prodotto) +
(2 il profitto per libbra) * (libbre di farmaco 2 prodotte) +...
(6 il profitto per libbra) * (libbre di farmaco 6 prodotte)

Profitto può essere calcolato nella cella D12 con la formula SUMPRODUCT(D9:I9,$D$2:$I$2).

È ora possibile identificare i tre componenti dell'offerta modello del Risolutore.

  • Cella obiettivo. Il nostro obiettivo è massimo profitto (calcolato nella cella D12).

  • Celle variabili. Numero di kg prodotti di ogni prodotto (riportato nell'intervallo di celle D2: I2)

  • Vincoli. Sono disponibili i vincoli seguenti:

    • Non usare più fatica o materia rispetto a quella disponibile. Ovvero i valori nelle celle D14: D15 (le risorse utilizzate) devono essere minore o uguale ai valori nelle celle F14: F15 (risorse disponibili).

    • Non producono più di un farmaco superare la domanda. Ovvero i valori nelle celle D2: I2 (kg prodotti di ogni farmaco) devono essere minore o uguale alla domanda di ogni farmaco (riportata nelle celle D8: I8).

    • Non è possibile produrre un importo negativo di un farmaco.

Verrà spiegato immettere la cella di destinazione, modificare le celle e i vincoli nel Risolutore. È necessario eseguire solo fare clic sul pulsante Risolvi per trovare una combinazione di prodotto in grado di profitto massimizzare!

Per iniziare, fare clic sulla scheda dati e nel gruppo analisi, fare clic su Risolutore.

Nota: Come illustrato in capitolo 26 "Un'introduzione a ottimizzazione con Risolutore di Excel," Risolutore è installato facendo clic sul pulsante Microsoft Office, quindi Opzioni di Excel, seguito da componenti aggiuntivi. Nell'elenco Gestisci, fare clic su componenti aggiuntivi di Excel, selezionare la casella componente aggiuntivo Risolutore e quindi fare clic su OK.

Verrà visualizzata la finestra di dialogo parametri del Risolutore, come illustrato nella figura 27-2.

Immagine del manuale
Finestra di dialogo parametri del Risolutore la figura 27-2

Fare clic sulla casella Imposta cella obiettivo e quindi selezionare la cella del profitto (cella D12). Fare clic sulla casella modificando le celle e quindi scegliere l'intervallo D2: I2, che contiene kg prodotti di ogni farmaco. Nella finestra di dialogo dovrebbe risultare figura 27-3.

Immagine del manuale
Finestra di dialogo parametri del Risolutore la figura 27-3 con la cella obiettivo e le celle variabili definite

Siamo ora pronti ad aggiungere vincoli al modello. Fare clic sul pulsante Aggiungi. Verrà visualizzato la finestra di dialogo Aggiungi vincolo illustrata nella figura 27-4.

Immagine del manuale
Finestra di dialogo Aggiungi il vincolo figura 27-4

Per aggiungere i vincoli di utilizzo delle risorse, fare clic sulla casella riferimento di cella e quindi selezionare l'intervallo D14:D15. Selezionare < = dall'elenco centrale. Fare clic sulla casella vincolo e quindi selezionare l'intervallo di celle F14:F15. Nella finestra di dialogo Aggiungi vincolo dovrebbe risultare come nella figura 27-5.

Immagine del manuale
Finestra di dialogo Aggiungi il vincolo figura 27-5 con i vincoli di utilizzo delle risorse immesse

In questo modo che quando il Risolutore Cerca valori diversi per la modifica di celle, solo le combinazioni che soddisfano entrambe D14 < = F14 (fatica utilizzato è minore o uguale a fatica disponibile) e D15 < = F15 (materia utilizzato è minore o uguale a verrà considerato materia disponibile). Fare clic su Aggiungi per immettere i vincoli della domanda. Compilare la finestra di dialogo Aggiungi vincolo come illustrato nella figura 27-6.

Immagine del manuale
Finestra di dialogo Aggiungi il vincolo figura 27-6 con i vincoli della domanda immesso

Aggiunta di questi vincoli garantisce quando il Risolutore tenta combinazioni diverse per i valori delle celle variabili, verranno considerate solo le combinazioni che soddisfano i parametri seguenti:

  • D2 < = D8 (la quantità prodotta la 1 è minore o uguale alla domanda per la 1)

  • E2 < = E8 (la quantità di prodotto di 2 la è minore o uguale alla domanda per la 2)

  • F2 < = F8 (la quantità di prodotto di 3 delle apportate è minore o uguale alla domanda per la 3)

  • G2 < = G8 (la quantità di prodotto di 4 delle apportate è minore o uguale alla domanda per la 4)

  • H2 < = H8 (la quantità di prodotto di 5 la apportate è minore o uguale a richiesta per il 5)

  • I2 < = I8 (la quantità di prodotto di 6 delle apportate è minore o uguale alla domanda per la 6)

Fare clic su OK nella finestra di dialogo Aggiungi vincolo. La finestra del Risolutore dovrebbe essere come nella figura 27-7.

Immagine del manuale
Figura 27-7 la finestra di dialogo parametri del Risolutore finale per il problema di combinazione di prodotti

Il vincolo celle variabili deve essere non negativo nella finestra di dialogo Opzioni del Risolutore. Fare clic sul pulsante Opzioni nella finestra di dialogo parametri del Risolutore. Selezionare la casella dal modello lineare e casella presuppongono Non negativo, come illustrato nella figura 27-8 nella pagina successiva. Fare clic su OK.

Immagine del manuale
Impostazioni delle opzioni del Risolutore figura 27-8

Selezionando la casella presuppongono Non negativo garantisce che vengono considerate solo le combinazioni di celle variabili in cui ogni cella variabile un valore non negativo. È selezionata che la casella dal modello lineare perché il prodotto combinare problema è un particolare tipo di problema del Risolutore denominato modello lineare. In pratica, un modello del Risolutore è lineare le seguenti condizioni:

  • La cella obiettivo viene calcolata sommando i termini della maschera (modifica cell)*(constant).

  • Ogni vincolo soddisfa "requisito del modello lineare". Questo significa che ogni vincolo viene valutato sommando i termini della maschera (modifica cell)*(constant) e confrontare le somme a una costante.

Perché è il problema del Risolutore lineare? La cella obiettivo (profitto) viene calcolata come

(Profitto del farmaco 1 libbra) * (1 delle kg prodotto) +
(2 il profitto per libbra) * (libbre di farmaco 2 prodotte) +...
(6 il profitto per libbra) * (libbre di farmaco 6 prodotte)

Questo calcolo segue uno schema derivato sommando i termini del modulo in cui valore della cella obiettivo (modifica cell)*(constant).

Il vincolo fatica viene valutato confrontando il valore derivato dalla (fatica utilizzata per libbra delle 1) * (1 delle kg prodotto) + (lavoro utilizzata per libbra delle 2) *(Drug 2 pounds produced) +... (Manodopera Contattacied per libbra delle 6) * (libbre di farmaco 6 prodotte) a fatica disponibile.

Di conseguenza, di valutare il vincolo di fatica sommando i termini della maschera (modifica cell)*(constant) e confrontare le somme a una costante. Il vincolo fatica e il vincolo materia soddisfano il requisito di modello lineare.

I vincoli della domanda assumono la forma

(Produzione del farmaco 1) < = (domanda del farmaco 1)
(2 il prodotto) < = (domanda del farmaco 2)
sezione
(6 il prodotto) < = (domanda del farmaco 6)

Anche i vincoli relativi alla domanda soddisfano requisito del modello lineare perché ognuno viene valutato sommando i termini della maschera (modifica cell)*(constant) e confrontare le somme a una costante.

Una volta stabilito che il modello di combinazione di prodotto è un modello lineare, quali vantaggi si ottengono?

  • Se un modello del Risolutore è lineare e si seleziona dal modello lineare, il Risolutore è garantito per trovare la soluzione ottimale per il modello del Risolutore. Se un modello del Risolutore non è lineare, Risolutore può o non venga individuata la soluzione ottimale.

  • Se un modello del Risolutore è lineare e si seleziona dal modello lineare, viene utilizzato un algoritmo molto efficace (metodo simplex) per trovare una soluzione ottimale del modello. Se un modello del Risolutore è lineare e non si seleziona dal modello lineare, viene utilizzato un algoritmo di (metodo GRG2) che potrebbero avere difficoltà a trovare una soluzione ottimale del modello.

Dopo aver fatto clic su OK nella finestra di dialogo Opzioni del Risolutore, abbiamo tornare al principale del Risolutore la finestra di dialogo, mostrata in precedenza nella figura 27-7. Quando si fa clic su Risolvi, viene calcolata una soluzione ottimale (se presente) per il modello di combinazione di prodotto. Come descritto nel capitolo 26, una soluzione ottimale per il modello di combinazione di prodotto è un set di valori delle celle variabili (kg prodotti di ogni farmaco) in grado di ottimizzare profitto sul set di tutte le possibili soluzioni. Anche una soluzione accettabile è un insieme di modificare i valori delle celle che soddisfano tutti i vincoli. I valori delle celle variabili mostrati nella figura 27-9 sono una soluzione accettabile perché tutti i livelli di produzione sono non negativi, livelli di produzione non superino richiesta e l'uso delle risorse non superi risorse disponibili.

Immagine del manuale
Figura 27-9 A soluzione accettabile al prodotto mix problema rientra nei vincoli.

I valori delle celle variabili mostrati nella figura 27-10 nella pagina successiva rappresentano una soluzione Impossibile per i motivi seguenti:

  • La produzione del 5 il più la richiesta.

  • Vengono utilizzati più fatica rispetto a quello disponibile.

  • Vengono utilizzati più materia rispetto a quello disponibile.

Immagine del manuale
Figura 27-10 una soluzione al problema del prodotto mix Impossibile non rientra nei vincoli definiti.

Facendo clic su Risolvi viene rapidamente individuata la soluzione ottimale mostrata nella figura 27-11. È necessario selezionare Mantieni soluzione del Risolutore per mantenere i valori della soluzione ottimale nel foglio di lavoro.

Immagine del manuale
Figura 27-11 la soluzione ottimale al problema del mix di prodotti

Il modo l'azienda può massimizzare il profitto mensile un livello di $6,625.20 creando 596,67 kg delle 4, 1084 kg il 5 e nessuno degli altri farmaci! Non è possibile determinare se è possibile ottenere il massimo profitto $6,625.20 in altri modi. Possiamo essere certi di è che con la richiesta e risorse limitate, non c'è alcun modo per rendere più di $6,627.20 questo mese.

Si supponga che la domanda per ogni prodotto deve essere soddisfatta. (Vedere il foglio di lavoro Non sia possibile soluzione nel file Prodmix.xlsx). È necessario modificare i vincoli della domanda da D2: I2 < = D8: I8 a D2: I2 > = D8: I8. A tale scopo, aprire il Risolutore, selezionare la D2: I2 < = D8: I8 vincolo e quindi fare clic su Cambia. Verrà visualizzata la finestra di dialogo Modifica vincolo illustrato nella figura 27-12.

Immagine del manuale
Finestra di dialogo Modifica il vincolo figura 27-12

Selezionare > = e quindi fare clic su OK. In questo modo verranno considerati solo i valori delle celle che soddisfano tutte le richieste di modifica. Quando si fa clic su Risolvi, verrà visualizzato il messaggio "Impossibile trovare una soluzione accettabile Risolutore." Questo messaggio significa che è stato commesso un nostro modello, ma invece che con le risorse limitate, è Impossibile soddisfare la domanda per tutti i prodotti. Risolutore è sufficiente informa se si desidera rispondere alla domanda per ogni prodotto, è necessario aggiungere ulteriori fatica, altre materie prime o più di entrambi.

Di seguito viene illustrato cosa accade se sono consentite domanda illimitata per ogni prodotto e la quantità negative deve essere prodotto per ogni farmaco. (È possibile vedere il problema del Risolutore nel foglio di lavoro Impostare valori non convergono nel file Prodmix.xlsx). Per trovare la soluzione ottimale per questa situazione, aprire il Risolutore, fare clic sul pulsante Opzioni e deselezionare la casella presuppongono Non negativo. Nella finestra di dialogo parametri del Risolutore, selezionare il vincolo di richiesta D2: I2 < = D8: I8 e quindi fare clic su Elimina per rimuovere il vincolo. Quando si fa clic su Risolvi, viene visualizzato il messaggio "I valori delle celle Set non convergono." Questo messaggio indica che se la cella obiettivo deve essere ingrandito (come nell'esempio), sono disponibili soluzioni possibili con i valori delle celle di qualsiasi dimensione di destinazione. (Se la cella obiettivo deve essere ridotta a icona, il messaggio "Impostare cella valori non convergono" indica che sono disponibili soluzioni possibili con i valori delle celle obiettivo arbitrariamente piccoli.) Nel nostro caso, consentendo produzione negativa di un farmaco è attivo "creare" risorse che possono essere utilizzate per produrre anche molto grande quantità di altri farmaci. Data la domanda illimitata, in questo modo di ottenere profitti illimitati. In un contesto reale Impossibile apportare una somma di denaro infinita. In breve, se viene visualizzato "Impostare valori non convergono", il modello contiene un errore.

  1. Si supponga che il nostro modo l'azienda può acquistare fino a 500 ore di lavoro $1 più orarie di costi fatica correnti. Come è possibile ottimizzare profitto?

  2. In un chip produzione stabilimento di produzione, quattro tecnici (A, B, C e D) producono tre prodotti (1, 2 e 3). Questo mese, il produttore di chip può vendere 80 unità del prodotto 1, 50 unità di prodotto 2 e al massimo 50 unità del prodotto 3. Il tecnico inserirvi solo prodotti 1 e 3. Il tecnico B può realizzare solo i prodotti 1 e 2. Il tecnico C può realizzare solo prodotto 3. Il tecnico D può realizzare solo prodotto 2. Per ogni unità prodotta, i prodotti incidono il profitto seguente: prodotto 1, $6; Prodotto 2, $7; e prodotto 3 $10. Il tempo (in ore) ciascun tecnico necessita per produrre il prodotto è il seguente:

    Prodotto

    Tecnico

    Tecnico B

    Tecnico C

    Tecnico D

    1

    2

    2,5

    Non consentite

    Non consentite

    2

    Non consentite

    3

    Non consentite

    3,5

    3

    3

    Non consentite

    4

    Non consentite

  3. Ogni tecnico può lavorare fino a 120 ore al mese. Come il produttore chip può massimizzare il profitto mensile? Si supponga che può ottenere un numero frazionario di unità.

  4. Un computer produzione stabilimento di produzione produce mouse, tastiere e joystick per video. Il profitto per unità, l'uso di fatica per unità, domanda mensile e l'uso di tempo macchina per unità siano corretti nella tabella seguente:

    Mouse

    Tastiere

    Joystick

    Conto profitti/unità

    $8

    $11

    $9

    L'uso/unità di lavoro

    ora.2

    . 3 le ore

    ora.24

    Computer / l'unità di tempo

    ora.04

    ora.055

    ora.04

    Domanda mensile

    15.000

    27.000

    11.000

  5. Ogni mese, un totale di 13.000 ore di lavoro e 3000 ore di computer sono disponibili. Come il produttore può massimizzare il profitto mensile dello stabilimento?

  6. Consente di risolvere il nostro esempio farmaco presupponendo che è necessario soddisfare una domanda di 200 unità per ogni farmaco.

  7. Jason rende orecchini, collane e orecchini. Si desidera usare un massimo di ore 160 al mese. Ha 800 oncia di rombi. Il profitto viene mostrato il tempo di lavoro e oncia di rombi necessari per produrre ogni prodotto indicato di seguito. Se la richiesta per ogni prodotto è un numero illimitata, come Jason ottimizzare il proprio profitto?

    Prodotto

    Margine di unità

    Ore di lavoro per unità

    Oncia di rombi unitario

    Bracelet

    € 300

    .35

    1.2

    Collana

    € 200

    . 15 pollici.

    .75

    Orecchini

    € 100

    5%

    0,5

Amplia le tue competenze su Office
Esplora i corsi di formazione
Ottieni in anticipo le nuove caratteristiche
Partecipa al programma Office Insider

Queste informazioni sono risultate utili?

Grazie per il feedback!

Grazie per il tuo feedback! Potrebbe essere utile metterti in contatto con uno dei nostri operatori del supporto di Office.

×