Come creare macro di Visual Basic utilizzando il Risolutore di Excel in Excel 97

Traduzione articoli Traduzione articoli
Identificativo articolo: 843304 - Visualizza i prodotti a cui si riferisce l?articolo.
Espandi tutto | Chiudi tutto

In questa pagina

Sommario

in questo articolo descrive come utilizzare Risolutore in Microsoft Excel 97 per creare macro Microsoft Visual Basic. Risolutore Ŕ un componente aggiuntivo di Microsoft Excel.

Inoltre, contiene informazioni su come creare le macro, la progettazione di una macro e come utilizzare i vincoli di una macro. In questo articolo spiega inoltre i metodi utilizzati dal Risolutore e l'algoritmo. Nell'elenco riportato di seguito fornisce tutti gli argomenti trattati in articolo.

INTRODUZIONE

Questo articolo contiene informazioni su Microsoft Excel Risolutore.

Informazioni

Descrizione del Risolutore di Microsoft Excel

Risolutore Ŕ che un verrÓ aggiungere aggiuntivo Risolutore consente di determinare il valore ottimale per una formula in una cella particolare di destinazione in un foglio di lavoro Microsoft Excel. Risolutore consente di regolare i valori di altre celle correlate alla cella di destinazione utilizzando un'equazione. Dopo aver costruire un'equazione e definire un insieme di vincoli per le variabili o parametri nell'equazione, Risolutore tenta di varie soluzioni per arrivare a una risposta che soddisfa tutti i vincoli. Risolutore utilizza i seguenti elementi per "Risoluzione" di un'equazione:
  • cella di destinazione , la cella di destinazione Ŕ l'obiettivo. ╚ la cella nel modello di foglio di lavoro ridotta a icona, ingrandita o impostata su un determinato valore.
  • celle variabili - celle variabili costituiscono le variabili di decisione. Queste celle influisce sul valore della cella di destinazione. Queste celle vengono modificate dal Risolutore per trovare la soluzione ottimale per la cella obiettivo.
  • i vincoli - vincoli sono restrizioni sul contenuto di celle. Ad esempio, una cella in un modello di foglio di lavoro potrebbe essere limitata a valori integer, mentre un'altra cella pu˛ essere limitata a sia minore di un determinato valore.
╚ possibile automatizzare la creazione e la modifica dei modelli del Risolutore utilizzando una di Microsoft Visual Basic, Applications Edition (VBA) macro. In questo articolo viene descritto come utilizzare il linguaggio macro VBA le funzioni del Risolutore Microsoft Excel 97. In questo articolo si presume la conoscenza del linguaggio VBA e Microsoft Visual Basic Editor per Microsoft Excel 97. Gli esempi utilizzati in questo articolo sono disponibili per download dal seguente sito Web:
http://download.microsoft.com/download/excel97win/solverex/1.0/WIN98Me/EN-US/SolverEx.exe
Nota ╚ inoltre possibile utilizzare le macro e gli esempi descritti in questo articolo in Microsoft Excel versioni 5.0 e 7.0.

Come utilizzare le funzioni del Risolutore in una macro VBA

Per utilizzare le funzioni del componente aggiuntivo Risolutore in una macro VBA, Ŕ necessario fare riferimento il componente aggiuntivo dal progetto VBA della cartella di lavoro che contiene le macro. Se non si fa riferimento il componente aggiuntivo Risolutore, verrÓ visualizzato il seguente errore di compilazione quando si tenta di eseguire la macro:
Errore di compilazione: Sub o Function non definita.
Per fare riferimento il componente aggiuntivo Risolutore per le macro nella cartella di lavoro, attenersi alla seguente procedura:
  1. Aprire la cartella di lavoro.
  2. Scegliere macro dal menu Strumenti , quindi Visual Basic Editor .
  3. Nel menu Strumenti , fare clic su riferimenti .
  4. Nell'elenco Riferimenti disponibili , fare clic per selezionare la casella di controllo Solver.xls e quindi fare clic su OK .

    Nota Se non visualizzato Solver.xls nell'elenco Riferimenti disponibili , scegliere Sfoglia . Nella finestra di dialogo Aggiungi riferimento , individuare e selezionare il file Solver.xla e quindi fare clic su Apri . Il file di Solver.xla Ŕ in genere disponibile nella sottocartella C:\Program Files\Microsoft Office\Office\Library\Solver.
Sono pronti per utilizzare le funzioni del Risolutore in una macro VBA.

La progettazione di una macro VBA che crea e risolve un semplice modello del Risolutore

Sebbene Risolutore offre molte funzioni, le tre funzioni seguenti sono fondamentali per la creazione e risoluzione di un modello:
  • La funzione SolverOK
  • La funzione SolverSolve
  • La funzione SolverFinish

La funzione SolverOK

La funzione SolverOK definisce un modello del Risolutore di base. La funzione SolverOK Ŕ in genere la prima funzione sarÓ utilizzato per creare il modello del Risolutore. La funzione SolverOK equivale a scegliere Risolutore Strumenti menu e quindi specificare le opzioni in Parametri del Risolutore nella finestra di dialogo. La sintassi per la funzione SolverOK Ŕ il seguente:
SolverOK(SetCell, MaxMinVal, ValueOf, ByChange)
Le informazioni seguenti viene descritta la sintassi per la funzione SolverOK :
  • SetCell specifica la cella obiettivo.
  • MaxMinVal corrisponde a se si desidera risolvere la cella di destinazione per un valore massimo (1), un valore minimo (2) o un valore specifico (3).
  • ValueOf specifica il valore a cui corrisponde la cella obiettivo. Se si imposta MaxMinVal a 3, Ŕ necessario specificare questo argomento. Se si imposta MaxMinVal su 1 o 2, Ŕ possibile omettere questo argomento.
  • ByChange specifica la cella o l'intervallo di celle che verranno modificate.
Nella figura 1 associa gli argomenti per la funzione SolverOK i parametri nella finestra di dialogo Parametri del Risolutore .

Figura 1. I parametri che sono associati il SolverOK argomenti

Riduci l'immagineEspandi l'immagine
 Figure 1. Parameters that are associated
		  with the SolverOK arguments


La funzione SolverSolve

La funzione SolverSolve risolve il modello utilizzando i parametri specificati con la funzione SolverOK . L'esecuzione della funzione SolverSolve equivale a scegliere Risolvi nella finestra di dialogo Parametri del Risolutore . La sintassi per la funzione SolverSolve Ŕ il seguente:
SolverSolve(UserFinish, ShowRef)
Le informazioni seguenti viene descritta la sintassi per la funzione SolverSolve :
  • UserFinish indica se si desidera che l'utente pu˛ completare il modello di risoluzione.

    Per restituire i risultati senza il Risolutore risultati finestra di dialogo impostare questo argomento su TRUE. Per restituire i risultati e visualizzare la finestra di dialogo Risolutore , impostare questo argomento su FALSE
  • ShowRef identifica la macro che viene chiamata quando il Risolutore restituisce una soluzione intermedia.

    L'argomento di ShowRef deve essere utilizzato solo quando Ŕ TRUE viene passato l'argomento StepThru della funzione SolverOptions .

La funzione SolverFinish

La funzione SolverFinish indica cosa fare con i risultati e il tipo di report per creare una volta completato il processo di soluzione. La sintassi per la funzione SolverFinish Ŕ il seguente:
SolverFinish (KeepFinal, ReportArray)
Le informazioni seguenti viene descritta la sintassi per la funzione SolverFinish :
  • KeepFinal consente di indicare quale azione eseguire con i risultati finali. Se KeepFinal Ŕ uguale a 1, i valori della soluzione finale verranno mantenuti nelle celle variabili, sostituendo i valori. Se KeepFinal Ŕ uguale a 2, i valori della soluzione finale vengono ignorati e verranno ripristinati i valori precedenti.
  • ReportArray specifica una matrice che indica il tipo di report che verrÓ creato quando viene raggiunta la soluzione. Se ReportArray Ŕ impostato su 1, viene creato un report di risposta. Se impostato su 2, verrÓ creato un rapporto sensibilitÓ e se impostato su 3 viene creato un rapporto limiti. Per ulteriori informazioni su questi report, vedere la sezione "How to generate reports for solutions".
Figura 2. Risolutore determina le opzioni che sono associate argomenti SolverFinish

Riduci l'immagineEspandi l'immagine
 Figure 2. Solver results options
		  that are associated with SolverFinish arguments


In questo articolo viene descritto come creare un semplice modello del Risolutore in modo interattivo. Il primo passaggio consiste nel creare il foglio di lavoro per il modello. Il foglio di lavoro contiene alcune celle di dati e almeno una cella che contiene una formula. Questa formula dipende dalle altre celle del foglio di lavoro. Dopo aver impostato il foglio di lavoro, scegliere Risolutore dal menu Strumenti . Nella finestra di dialogo Parametri del Risolutore , specificare la cella obiettivo, il valore che Ŕ risoluzione per, l'intervallo di celle che verranno modificate e i vincoli. Fare clic su Risolvi per avviare il processo di soluzione. Dopo il Risolutore ha trovato una soluzione, i risultati verranno visualizzati nel foglio di lavoro e il Risolutore visualizzato un messaggio che richiede se si desidera mantenere i risultati finali o se si desidera ignorarle. Quando si sceglie una di queste opzioni, il Risolutore termina.

La figura 3 illustra un modello semplice che Ŕ possibile creare attenendosi alla procedura seguente.

Figura 3. Un modello semplice: la radice quadrata modello

Riduci l'immagineEspandi l'immagine
 Figure 3. A
		  simple model: The Square Root model


In questo esempio modifica cella A1, che contiene la formula = A1 ^ 2, su un valore che renda la cella A2 Ŕ uguale a un valore di 50. In altre parole, Ŕ possibile trovare la radice quadrata di 50. Non esistono vincoli nel modello di radice quadrata. La macro Find_Square_Root vengono effettuate le seguenti operazioni:
  • Imposta un modello che risolverÓ il valore della cella A2 per un valore 50 modificando il valore della cella A1.
  • Consente di risolvere il modello.
  • Viene salvato il foglio di lavoro i risultati finali senza visualizzare la finestra di dialogo Risultato del Risolutore .
Questa macro semplice viene creato un modello del Risolutore e Ŕ stato risolto senza l'intervento dell'utente. Il codice riportato di seguito viene descritta la macro Find_Square_Root :
    Sub Find_Square_Root()

    ' Set up the parameters for the model.
    ' Set the target cell A2 to a value of 50 by changing cell A1.
    SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=50, _
         ByChange:=Range("A1")

    ' Solve the model but do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Finish and keep the final results.
    SolverFinish KeepFinal:=1

    End Sub
Find_Square_Root2 macro, Ŕ una versione modificata della macro Find_Square_Root . Se si utilizza la funzione InputBox , la macro Find_Square_Root2 richiesto per il valore che si desidera risolvere per la cella obiettivo. Dopo che un valore di input, la macro Find_Square_Root2 imposta questo parametro come valore dell'argomento valueof SolverOK , Ŕ stato risolto il problema, Salva i risultati nella radice quadrata di variabile e ignora la soluzione e ripristina il valore nel foglio di lavoro lo stato originale. In sostanza, la macro Find_Square_Root2 viene illustrato come possibile salvare i risultati in una o pi¨ variabili e quindi ripristinare le celle variabili al valore originale.

Il codice riportato di seguito viene descritta la macro Find_Square_Root2 :
    Sub Find_Square_Root2()

    Dim val
    Dim sqroot

    ' Request the value for which you want to obtain the square root.
    val = Application.InputBox( _
         prompt:="Please enter the value for which you want " & _
         "to find the square root:", Type:=1)

    ' Set up the parameters for the model.
    SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=val, _
         ByChange:=Range("A1")

    ' Do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Save the value of cell A1 (the changing cell) before you discard 
    ' the results.
    sqroot = Range("a1")

    ' Finish and discard the results.
    SolverFinish KeepFinal:=2

    ' Show the result in a message box.
    MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00")

    End Sub

Come generare report per le soluzioni

Risolutore offre diversi tipi di report che descrivono come i risultati della modifica e come chiudere i vincoli forniti ai valori critici. Ogni report viene collocato su un foglio di lavoro nella cartella di lavoro distinto. I seguenti sono i tipi di report che il Risolutore offre:
  • Report risposta - il report di risposta Elenca la cella obiettivo e le celle variabili con i corrispondenti valori originali e finali, i vincoli e le informazioni sui vincoli.
  • Rapporto sensibilitÓ - il rapporto sensibilitÓ fornisce informazioni sul come riservate la soluzione a piccole variazioni della formula per la cella obiettivo.
  • I limiti di report - il rapporto limiti Elenca la cella obiettivo e le celle variabili con i rispettivi valori, i limiti inferiori e superiore e i valori di destinazione.
Per creare report per i modelli, specificare una matrice di valori per l'argomento ReportArray della funzione SolverFinish . Per ulteriori informazioni sull'argomento ReportArray , vedere la sezione "SolverFinish (KeepFinal, ReportArray) ". Ad esempio, se si desidera generare un report di limiti per il modello che la macro Find_Square_Root2 crea e che Ŕ stato risolto, modificare la funzione di SolverFinish nella macro in modo che appaia simile al seguente codice di esempio:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
per generare pi¨ rapporti, modificare la funzione SolverFinish in modo che appaia simile al seguente codice di esempio:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)

Come utilizzare le funzioni di Microsoft ExcelSolver in una macro di ciclica

In molte situazioni, Ŕ buona norma avere Risolutore risolvere la cella di destinazione per pi¨ valori. ╚ in genere possibile effettuare questa operazione utilizzando una delle strutture di ciclica in cui sono disponibili con VBA.

Nella macro di Create_Square_Root_Table viene illustrato il funzionamento del Risolutore in una macro ciclica. Il Create_Square_Root_Table macro viene creata una tabella in un nuovo foglio di lavoro. Inserisce i numeri di uno tramite dieci e la radice quadrata corrispondente di ogni numero. La macro Create_Square_Root_Table crea la tabella utilizzando un ciclo for per scorrere i numeri da 1 a 10 e la cella obiettivo nel modello di radice quadrata di un valore che corrisponde al numero dell'iterazione Ŕ stato risolto. Il codice riportato di seguito viene descritta la macro Create_Square_Root_Table :
    Sub Create_Square_Root_Table()

    ' Add a new worksheet to the workbook.
    Set w = Worksheets.Add

    ' Put the value 2 in cell C1 and the formula =C1^2 in cell C2.
    w.Range("C1").Value = 2
    w.Range("C2").Formula = "=C1^2"

    ' A loop that will make 10 iterations, starting with the number 1, 
    ' and finishing at the number 10.
    For i = 1 To 10

        ' Set the Solver parameters that indicate that Solver should
        ' solve the cell C2 for the value of i (where i is the number
        ' of the iteration) by changing cell C1.
        SolverOk SetCell:=Range("C2"), ByChange:=Range("C1"), _
            MaxMinVal:=3, ValueOf:=i

        ' Do not display the Solver Results dialog box.
        SolverSolve UserFinish:=True

        ' Save the value of i in column A and the results of the 
        ' changing cell in column B.
        w.Cells(i, 1) = i
        w.Cells(i, 2) = Range("C1")

        ' Finish and discard the final results.
        SolverFinish KeepFinal:=2

    Next

    ' Clear the range C1:C2
    w.Range("C1:C2").Clear

    End Sub

macro Create_Square_Root_Table genera la tabella illustrata nella figura 4.

Figura 4. Output generato dalla macro Create_Square_Root_Table

Riduci l'immagineEspandi l'immagine
Figure 4. Output that is generated by the
		  Create Square Root Table macro


L'utilizzo di vincoli

Un vincolo Ŕ una restrizione sul contenuto di una o pi¨ celle. Un modello pu˛ avere uno o pi¨ vincoli. Il set di vincolo Ŕ un insieme di inequalities o un insieme di equalities che alcune combinazioni di valori per le variabili di decisione di rimuovere dalla soluzione. Ad esempio, un vincolo pu˛ richiedere che una cella maggiore di zero e un'altra cella contiene solo un valore integer.

Il modello di radice quadrata Ŕ illustrato fino a questo punto Ŕ un modello semplice che non contiene i vincoli. La figura 5 illustra un modello che utilizza i vincoli. Lo scopo di questo modello Ŕ trovare la combinazione ottimale di prodotti per il profitto massimo.

Nella figura 5. Prodotto combinare con la riduzione del margine di profitto

Riduci l'immagineEspandi l'immagine
Figure 5. Product mix with
		  diminishing profit margin


Ad esempio, se una societÓ produce televisori, stereo e gli altoparlanti e utilizza un inventario di parti comuni di alimentatori, coni degli altoparlanti e cosý via. Le parti sono in offerta limitata. L'obiettivo consiste nel determinare la combinazione di prodotti per la creazione di pi¨ proficua. Il profitto per unitÓ diminuisce con volume perchÚ sono necessarie ulteriori prezzo incentivi per caricare il canale di distribuzione. L'esponente resa Ŕ 0,9. L'esponente viene utilizzato per calcolare il profitto per il prodotto nell'intervallo G11:I11.

L'obiettivo Ŕ trovare il massimo profitto (cella G14). I valori che verrÓ modificato per trovare il profitto massimo rappresentano il numero di unitÓ che si genera. L'intervallo G9:G11 rappresenta le celle variabili in questo modello. L'unico vincolo Ŕ che il numero di parti che Ŕ utilizzare Ŕ in non Ŕ in grado di superi il numero di parti di che avere a portata di mano. Con il Risolutore, questo vincolo viene visualizzato come E3:E7 < = B3:B7. Se si dovesse generare questo modello del Risolutore in modo interattivo, i parametri del Risolutore risulterÓ simile a quelle nella figura 6.

Figura 6. Combinazione di parametri del Risolutore per il prodotto con margine di profitto renda modello

Riduci l'immagineEspandi l'immagine
Figure 6. Microsoft Excel Solver parameters for
		  the product mix with Diminishing Profit Margin model


Per creare e risolvere il mix di prodotti con margine di profitto renda modello, si utilizzerÓ una nuova funzione, la funzione SolverAdd , oltre a utilizzare le funzioni di Microsoft VBA Risolutore di Excel che sono stati descritti in precedenza. La funzione SolverAdd aggiunge il vincolo il modello. L'esecuzione della funzione SolverAdd equivale a fare clic sul pulsante Aggiungi nella finestra di dialogo Parametri del Risolutore . La funzione di SolverAdd Ŕ la seguente sintassi:
SolverAdd (CellRef, relazione FormulaText)
Le informazioni seguenti viene descritta la sintassi per la funzione SolverAdd :
  • CellRef fa riferimento a una o pi¨ celle che formano il lato sinistro del vincolo.
  • relazione Ŕ la relazione aritmetica tra sinistra e parti a destra di un vincolo.
  • relazione pu˛ essere un un valore compreso tra 1 e 5 come nell'esempio riportato di seguito:
    • Il valore 1 Ŕ minore o uguale a (< =).
    • Il vaue 2 Ŕ uguale a (=).
    • Il valore 3 Ŕ maggiore o uguale a (> =).
    • Il valore 4 Ŕ un valore integer.
    • Il valore 5 Ŕ il file binario (un valore di zero o uno).
  • FormulaText fa riferimento a una o pi¨ celle che formano la parte destra constraint.* *
** Quando si specifica di un intervallo di celle per l'argomento FormulaText della funzione SolverAdd , Ŕ necessario Nota Se il riferimento Ŕ relativo o assoluto. In genere, Ŕ necessario specificare un riferimento assoluto per l'argomento FormulaText . Tuttavia, se si specifica i riferimenti relativi per l'argomento FormulaText , tenere presente che il riferimento sarÓ relativo alla cella di destinazione e non la cella attiva.

Nota In Microsoft Excel versioni 5.0 e 7.0, utilizzare la notazione R1C1 quando si specifica una cella o un intervallo di celle con l'argomento FormulaText . Al contrario, in Microsoft Excel 97, utilizzare lo stile A1 notazione per specificare l'argomento FormulaText .

Figura 7. Campi che sono associati gli argomenti SolverAdd

Riduci l'immagineEspandi l'immagine
 Figure 7. Fields that are associated with
		  the SolverAdd arguments


La macro Maximum_Profit che genera un modello per la combinazione di prodotto con modello di resa. Questa macro esegue le funzioni o argomenti seguenti:

  • La funzione SolverOK imposta la cella di destinazione per un valore massimo e specifica le celle da modificare.
  • La funzione SolverAdd aggiunge il vincolo il modello.
  • La funzione SolverSolve trovato una soluzione senza visualizzare la finestra di dialogo Risultato del Risolutore .
  • La funzione SolverFinish restituisce i risultati finali del foglio di lavoro.
Il codice riportato di seguito viene descritto il Maximum_Profit macro:
    Sub Maximum_Profit()

    ' Set up the parameters for the model.
    ' Determine the maximum value for the sum of profits in cell G14
    ' by changing the number of units to build in cells G9:I9.
    Solverok setcell:=Range("G14"), maxminval:=1, _
        bychange:=Range("G9:I9")

    ' Add the constraint for the model. The only constraint is that the
    ' number of parts used does not exceed the parts on hand-- 
    ' E3:E7<=B3:B7
    SolverAdd CellRef:=Range("E3:E7"), Relation:=1, _
        FormulaText:="$B$3:$B$7"

    ' Do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Finish and keep the final results.
    SolverFinish KeepFinal:=1

    End Sub

Nota In Microsoft Excel versioni 5.0 e 7.0, utilizzare la notazione R1C1, quando si specifica una cella o un intervallo di celle con l'argomento FormulaText . Al contrario, in Microsoft Excel 97, utilizzare lo stile A1 notazione per specificare l'argomento FormulaText .

Quando si esegue la macro Maximum_Profit , Microsoft Excel individuare una soluzione di creazione di set TV 160, 200 stereo e gli 80 altoparlanti per un massimo di 14,917 dollari di profitto.

Come modificare ed eliminare i vincoli

I vincoli del modello possono essere modificati a livello di programmazione o eliminati. I vincoli sono identificati da loro CellRef e argomenti di relazione .

Per modificare a livello di programmazione un vincolo esistente, utilizzare la funzione di SolverChange . Le seguenti operazioni sono la sintassi per il SolverChange funzione:
SolverChange (CellRef, relazione FormulaText)
Si noti che gli argomenti per la funzione SolverChange sono lo stesso quelle che utilizzano con la funzione SolverAdd .

Se si desidera modificare il vincolo in mix di prodotti con modello di resa, si utilizzerÓ la funzione SolverChange . Ad esempio, attualmente il vincolo specificato Ŕ il numero di parti utilizzate minore o uguale al numero di parti a portata di mano (E3:E7 < = B3:B7). Se si desidera modificare il vincolo in modo che il numero di parti utilizzato sia minore o uguale al numero di parti previsto (numero di parti in giacenza) pi¨ numero di parti ordinato. Questo nuovo vincolo sarebbe E3:E7 < = D3:D7. La macro riportata di seguito comporta la modifica E3:E7 il vincolo esistente < = B3:B7 per E3:E7 < = D3:D7 e per una soluzione Ŕ stato risolto.

Il codice riportato di seguito viene descritta la macro Change_Constraint_and_Solve :
    Sub Change_Constraint_and_Solve()

    ' Change the constraint.
    SolverChange CellRef:=Range("E3:E7"), Relation:=1, _
       FormulaText:="$D$3:$D$7"

    ' Return the results and display the Solver Results dialog box.
    SolverSolve UserFinish:=False

    End Sub

poichÚ i vincoli vengono identificati dagli argomenti CellRef e relazione , Ŕ possibile modificare l'argomento FormulaText per il vincolo solo utilizzando la funzione SolverChange . Se il CellRef e i valori di relazione non corrispondono a un vincolo esistente, Ŕ necessario eliminare il vincolo e quindi aggiungere il vincolo modificato. Per eliminare un vincolo, Ŕ necessario utilizzare la funzione SolverDelete . La sintassi per la funzione SolverDelete Ŕ il seguente:

SolverDelete (CellRef, relazione FormulaText)

Si noti che gli argomenti per la funzione SolverDelete sono lo stesso quelli utilizzati con il SolverAdd e le funzioni SolverChange .

La macro riportata di seguito viene illustrato come eliminare e aggiungere un vincolo. In questo esempio, la macro Change_Constraint_and_Solve2 rimuove il vincolo E3:E7 < = B3:B7 da MIX di prodotti con modello di resa e aggiunge un nuovo vincolo. Il nuovo vincolo Ŕ solo una variante del vincolo originale, in cui la sinistra e a destra del vincolo sono invertiti.

Il codice riportato di seguito viene descritta la macro Change_Constraint_and_Solve2 :
    Sub Change_Constraint_and_Solve2()

    ' Reverse the left and right sides of the constraint...
    ' Delete the constraint E3:E7<=B3:B7 and add the
    ' constraint B3:B7>=E3:E7.
    SolverDelete CellRef:=Range("E3:E7"), Relation:=1, _
        FormulaText:="$B$3:$B$7"
    SolverAdd CellRef:=Range("B3:B7"), Relation:=3, _
        FormulaText:="$E$3:$E$7"
    
    ' Return the results and display the Solver Results dialog box.
    SolverSolve UserFinish:=False
    
    End Sub

Nota In Microsoft Excel versioni 5.0 e 7.0, utilizzare la notazione R1C1, quando si specifica una cella o un intervallo di celle con l'argomento FormulaText . Al contrario, in Microsoft Excel 97, utilizzare lo stile A1 notazione per specificare l'argomento FormulaText .

Come caricare e salvare i modelli

Quando si salva la cartella di lavoro, i parametri di ultimi specificato nella finestra di dialogo Parametri del Risolutore vengono salvati con la cartella di lavoro. Di conseguenza, quando si apre la cartella di lavoro, i parametri sono le stesse quando l'ultimo salvataggio della cartella di lavoro.

╚ possibile definire pi¨ di un problema per un foglio di lavoro. Ogni problema Ŕ costituito da celle e i vincoli immessi nei Parametri del Risolutore e le finestre di dialogo Opzioni del Risolutore . PoichÚ il foglio di lavoro viene salvato solo l'ultimo problema, si perderanno tutti gli altri problemi a meno che non in modo esplicito salvati. Per salvare tali, fare clic su Salva modello nel Risolutore opzioni nella finestra di dialogo. In modo analogo, quando si desidera ripristinare i parametri salvati in precedenza, fare clic su Carica modello di finestra di dialogo Opzioni del Risolutore .

Modelli del Risolutore sono memorizzati in un intervallo di celle in un foglio di lavoro. La prima cella dell'intervallo contiene la formula per la cella obiettivo. La seconda cella dell'intervallo contiene la formula che identifica le celle variabili nel modello. L'ultima cella nell'intervallo contiene una matrice che rappresenta le opzioni impostate nella finestra di dialogo Opzioni del Risolutore . Le celle tra la seconda cella e l'ultima cella contengono le formule che rappresentano i vincoli nel modello.

Nella figura 8 viene illustrato un modello per la programmazione del dipendente. Si supponga che si lavora per un produttore di piccole dimensioni. Questa tabella illustra tariffa oraria ?s ogni dipendente di retribuzione, il numero di ore in cui sono pianificati e il numero previsto di unitÓ che ogni dipendente pu˛ produrre in un'ora. L'obiettivo Ŕ per soddisfare una quota specifica per il numero di unitÓ prodotte e minimizzazione dei costi di manodopera in termini del.

Figura 8. Dipendente ModalitÓ di pianificazione
l
Riduci l'immagineEspandi l'immagine
 Figure 8. Employee Scheduling
		  model


Due altri fattori (o vincoli) che Ŕ necessario considerare il numero di minimo/massimo di ore pu˛ utilizzare qualsiasi dipendente di uno e il numero di unitÓ che si desidera produrre. Se per una settimana specificata, Ŕ necessario per produrre 3975 unitÓ e da ogni dipendente per l'utilizzo tra 30 e 45 ore, i parametri del Risolutore risulterÓ simile a quelli indicato nella tabella riportata di seguito:

Riduci questa tabellaEspandi questa tabella
parametro Intervallo di celle Descrizione
Cella di destinazione $ D $ 12 Costo di manodopera.
Celle variabili $ C $ 2: $ C $ 8 Ore di lavoro per ogni dipendente.
Vincoli $ C $ 2: $ C $ 8 < = 45 Ore di massimo al dipendente Ŕ 45.
$ C $ 2: $ C $ 8 > = 30 Minimo di ore per dipendente Ŕ 35.
$ G $ 12 = 3975 Numero di unitÓ Ŕ 3975.


Gli obiettivi sono per costi di manodopera ottimale su base settimanale, per salvare ciascun modello di ogni settimana e per poter caricare qualsiasi modello settimana quando Ŕ necessario Ŕ stato risolto.

In una macro, Ŕ possono salvati e caricati utilizzando il SolverSave e le funzioni SolverLoad rispettivamente i parametri del Risolutore per un modello. Il SolverSave e le funzioni SolverLoad presentano la sintassi seguente:

SolverSave (SaveArea)

solverLoad (LoadArea)

Il SolverSave e le funzioni SolverLoad sono rispettivamente un solo argomento, SaveArea e gli argomenti LoadArea . Gli argomenti specificano un intervallo in un foglio di lavoro in cui le informazioni di modello vengono memorizzate.

Nella macro di New_Employee_Schedule seguente, viene illustrato come creare, risolvere e salvare un modello in base all'input dell'utente. L'utente viene richiesto di immettere la data del modello, il numero di unitÓ per produrre e il numero minimo e massimo di ore per dipendente. Questi dati vengono quindi utilizzati per creare il modello. Il modello viene risolto e quindi salvato con l'input dell'utente.

Il codice riportato di seguito viene descritta la macro New_Employee_Schedule :
    Sub New_Employee_Schedule()
    
    ' Prompt the user for the date of the model, the units to produce,
    ' and the maximum and minimum number of hours per employee.
    ModelDate = Application.InputBox( _
       Prompt:="Date of Model:", Type:=2)
    Units = Application.InputBox( _
       Prompt:="Projected Number of Units:", Type:=1)
    MaxHrs = Application.InputBox( _
       Prompt:="Maximum Number of Hours Per Employee:", Type:=1)
    MinHrs = Application.InputBox( _
       Prompt:="Minimum Number of Hours Per Employee:", Type:=1)
    
    ' Clear any previous Solver settings.
    SolverReset
    
    ' Set the target cell, D12, to a minimum value by changing
    ' the range, C2:C8.
    SolverOk SetCell:=Range("$D$12"), MaxMinVal:=2, _
       ByChange:=Range("C2:C8")
    
    ' Add the constraint that number of hours worked <= MaxHrs.
    SolverAdd CellRef:=Range("C2:C8"), Relation:=1, FormulaText:=MaxHrs
    
    ' Add the constraint that number of hours worked >=MinHrs.
    SolverAdd CellRef:=Range("C2:C8"), Relation:=3, FormulaText:=MinHrs
    
    ' Add the constraint that number of units produced = Units.
    SolverAdd CellRef:=Range("G12"), Relation:=2, FormulaText:=Units
    
    ' Solve the model and keep the final results.
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    
    ' Save the input values for ModelDate, MaxHrs, MinHrs, and Units
    ' in columns I:L.
    Set ModelRange = Range("I2:R2").CurrentRegion.Offset( _
       Range("I2:R2").CurrentRegion.Rows.Count).Resize(1, 1)
    ModelRange.Resize(1, 4) = Array("'" & Format(ModelDate, "m/d/yy"), _
       Units, MaxHrs, MinHrs)
    
    ' Save the model parameters to the range M:R in the worksheet.
    SolverSave SaveArea:=ModelRange.Offset(, 4).Resize(1, 6)
    
    End Sub
Nota In Microsoft Excel versioni 5.0 e 7.0, utilizzare la notazione R1C1, quando si specifica una cella o un intervallo di celle con l'argomento FormulaText . Al contrario, in Microsoft Excel 97, utilizzare lo stile A1 notazione per specificare l'argomento FormulaText .

Nella figura 9 viene illustrato come le informazioni di modello salvati vengono visualizzate nel foglio di lavoro.

Figura 9. Modello informazioni salvate dalla macro New_Employee_Schedule

Riduci l'immagineEspandi l'immagine
Figure 9. Model information that is saved
		  by the New Employee Schedule macro


La macro New_Employee_Schedule Salva ogni nuovo modello di foglio di lavoro. La macro Load_Employee_Schedule possibile caricare uno di questi modelli salvati. La macro richiede per il modello per caricare e quindi cerca colonna I per la data del modello. Se viene trovata la data di modello, la macro Load_Employee_Schedule carica il modello corrispondente, Ŕ stato risolto e quindi consente di mantenere i risultati finali.

Il codice riportato di seguito viene descritta la macro New_Employee_Schedule :
    Sub Load_Employee_Schedule()
    
    ' Prompt for the date of the model. 
    ModelDate = Application.InputBox( _
       Prompt:="Date of Model to Load:", Type:=2)
    
    ' Locate the date in column I.
    Set DateRange = Range("I2").CurrentRegion.Resize(, 1)
    r = Application.Match(ModelDate, DateRange, 0)
    
    If IsError(r) Then
        ' Display a message if the model date is not found
        MsgBox "Cannot find a model with the date " & ModelDate
    Else
        ' If the model date is found, load the model into Solver,
        ' solve the model, and keep the final results.
        SolverLoad LoadArea:=DateRange.Offset(r - 1, 4).Resize(1, 6)
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
    End If
    
    End Sub
la macro New_Employee_Schedule introduce la funzione SolverReset . Il SolverReset funzione pu˛ essere utilizzato per eliminare tutte le selezioni di cella e i vincoli nella finestra di dialogo Parametri del Risolutore e ripristinare tutte le impostazioni in la funzione SolverReset non dispone di argomenti.

Come trovare ulteriori informazioni su Risolutore

Le risorse seguenti forniscono informazioni su come utilizzare il Risolutore aggiuntivo.

  • Per informazioni su messaggi specifici del Risolutore, vedere Frontline Systems.
  • Per suggerimenti sulla creazione di leggibile, gestibili modelli, vedere Frontline Systems.
  • Per ulteriori informazioni per il Risolutore limiti per i vincoli e, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito riportato:
    75714Limiti del Risolutore per vincoli
  • Per esempi di diversi che utilizzano il componente aggiuntivo Risolutore di Microsoft Excel, vedere l'esempio Solvsamp.xls.
  • Di seguito Ŕ riportato il percorso predefinito del file di esempio incluso in Microsoft Excel 97:
    \Program Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
  • Di seguito Ŕ riportato il percorso predefinito del file di esempio incluso in Microsoft Excel 7.0:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • Di seguito Ŕ riportato il percorso predefinito del file di esempio incluso in Microsoft Excel 5.0:
    \Excel\Examples\Solver\SolvSamp.xls

Come per ulteriori informazioni sui metodi utilizzati dal Risolutore e algoritmo

Risolutore utilizza il Generalized Reduced Gradient (GRG2) codice di ottimizzazione non Ŕ stato sviluppato da Leon Lasdon, UniversitÓ del Texas ad Austin e Allan Waren, universitÓ di Cleveland.

Per ulteriori informazioni sull'algoritmo utilizzato dal Risolutore, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito riportato:
82890Il Risolutore utilizza generalizzato ridotto


Problemi lineari e interi utilizzano il metodo simplex con limiti sulle variabili e il metodo di diramazione-bound implementato da John Watson e Dan Fylstra, Frontline Systems, Inc. Per ulteriori informazioni sul processo risolutivo interno adottato dal Risolutore, contattare:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288 
(702) 831-0300
Web site: http://www.frontsys.com
Electronic mail: info@frontsys.com 


Selezioni del Risolutore codice di programma sono copyright 1990, 1991, 1992 e 1995 di Frontline Systems, Inc. parti sono copyright 1989 di Optimal Methods, Inc.

Nota Il componente aggiuntivo Risolutore descritto in questo articolo viene fornito di "cosý com'Ŕ" e non si garantisce che pu˛ essere utilizzato in tutte le situazioni. Sebbene professionisti del supporto tecnico Microsoft con l'installazione e la funzionalitÓ esistenti di questo componente aggiuntivo, non modificherÓ il componente aggiuntivo per fornire nuove funzionalitÓ.

senza alcuna garanzia . Il software Ŕ fornito "come-Ŕ," senza garanzie di alcun tipo e qualsiasi utilizzo di questo software Ŕ prodotto Ŕ a proprio rischio.

ProprietÓ

Identificativo articolo: 843304 - Ultima modifica: mercoledý 11 ottobre 2006 - Revisione: 1.2
Le informazioni in questo articolo si applicano a:
  • Microsoft Excel 97 Standard Edition
Chiavi:á
kbmt kbhowto kbmacroexample kberrmsg kbaddin kbvba kbprogramming kbinfo KB843304 KbMtit
Traduzione automatica articoli
Il presente articolo Ŕ stato tradotto tramite il software di traduzione automatica di Microsoft e non da una persona. Microsoft offre sia articoli tradotti da persone fisiche sia articoli tradotti automaticamente da un software, in modo da rendere disponibili tutti gli articoli presenti nella nostra Knowledge Base nella lingua madre dell?utente. Tuttavia, un articolo tradotto in modo automatico non Ŕ sempre perfetto. Potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli, pi¨ o meno allo stesso modo di come una persona straniera potrebbe commettere degli errori parlando una lingua che non Ŕ la sua. Microsoft non Ŕ responsabile di alcuna imprecisione, errore o danno cagionato da qualsiasi traduzione non corretta dei contenuti o dell?utilizzo degli stessi fatto dai propri clienti. Microsoft, inoltre, aggiorna frequentemente il software di traduzione automatica.
Clicca qui per visualizzare la versione originale in inglese dell?articolo: 843304
LE INFORMAZIONI CONTENUTE NELLA MICROSOFT KNOWLEDGE BASE SONO FORNITE SENZA GARANZIA DI ALCUN TIPO, IMPLICITA OD ESPLICITA, COMPRESA QUELLA RIGUARDO ALLA COMMERCIALIZZAZIONE E/O COMPATIBILITA' IN IMPIEGHI PARTICOLARI. L'UTENTE SI ASSUME L'INTERA RESPONSABILITA' PER L'UTILIZZO DI QUESTE INFORMAZIONI. IN NESSUN CASO MICROSOFT CORPORATION E I SUOI FORNITORI SI RENDONO RESPONSABILI PER DANNI DIRETTI, INDIRETTI O ACCIDENTALI CHE POSSANO PROVOCARE PERDITA DI DENARO O DI DATI, ANCHE SE MICROSOFT O I SUOI FORNITORI FOSSERO STATI AVVISATI. IL DOCUMENTO PUO' ESSERE COPIATO E DISTRIBUITO ALLE SEGUENTI CONDIZIONI: 1) IL TESTO DEVE ESSERE COPIATO INTEGRALMENTE E TUTTE LE PAGINE DEVONO ESSERE INCLUSE. 2) I PROGRAMMI SE PRESENTI, DEVONO ESSERE COPIATI SENZA MODIFICHE, 3) IL DOCUMENTO DEVE ESSERE DISTRIBUITO INTERAMENTE IN OGNI SUA PARTE. 4) IL DOCUMENTO NON PUO' ESSERE DISTRIBUITO A SCOPO DI LUCRO.
Dichiarazione di non responsabilitÓ per articoli della Microsoft Knowledge Base su prodotti non pi¨ supportati
Questo articolo Ŕ stato scritto sui prodotti per cui Microsoft non offre pi¨ supporto. L?articolo, quindi, viene offerto ?cosý come Ŕ? e non verrÓ pi¨ aggiornato.

Invia suggerimenti

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com