Macro & Risolutore

[paolino791]

Salve avrei un problema con un foglio excel che sto facendo relativo ai voti per raggiungere un determinato punteggio di laurea. Vorrei automatizzare tutti i procedimenti ma mi trovo abbastanza in difficoltà. Ciò che voglio fare l ho scritto all'interno del foglio; Ringrazio chiunque riuscirà ad aiutarmi
Questo è il link: http://www.mediafire.com/?s1v831ollde1f5u

La prima macro la son riuscita a fare solo bisognerebbe aggiungere qualcosa per far prendere al risolutore ogni volta le celle "voto" mancanti.
La seconda macro non son proprio riuscita a farla..

[paolino791]

Salve, ma nessuno mi può aiutare a creare questa macro che mi faccia partire il risolutore?

[Anthony47]

Ooops... mi sembrava (erroneamente) di aver inviato questo messaggio:

Per tutti i lettori, il quesito che paolino non ha trascritto nel testo e':
"Creare macro che fa partire il risolutore di excel e prende il voto desiderato dalla cella N12 e restituisce i voti mancanti nell'elenco per raggiungere il voto desiderato"
Mi pare pero' che manchi la descrizione di come si vorrebbe procedere, in questa situazione non so che abbozzare.

Ciao

[Anthony47]

Inoltre N12 contiene una formula, quindi forse non e' nemmeno quella cella che contiene il valore desiderato.

Comunque sarebbe utile che descrivessi che cosa e' stato realizzato sul foglio in modo da capire quali sono gli "agganci" da inserire nel risolutore.

Ciao

[paolino791]

Vabbè dal file pensavo si capisse cmq...
Voglio creare due macro:
1)Ho una lista di esami con i voti per quelli fatti, e vorrei per prima cosa una macro che faccia partire il risolutore e seleziona come celle variabili gli esami con le celle dei voti libere(cioè gli esami ancora non fatti), come cella obiettivo la cella del voto di laurea, come vincoli che i numeri siano >=18 e =<30, e prima massimizza l'obiettivo e poi lo minimizza per calcolare infine in un'altra cella il voto medio probabile.
2) Un'altra macro che faccia sempre partire il risolutore ma imposta come obiettivo un numero che è presente in una determinata cella(cioè il voto di laurea desiderato) e modifica le celle dei voti libere sempre con gli stessi vincoli...
Ma a quanto ho capito forse non si può impostare un numero nella cella obiettivo xò se trovate qualche espediente

[Anthony47]

Sono pigro, lo so, ma io leggo la descrizione e la descrizione sul forum era scarsa, sul foglio era quella che ho trascritto.

Vediamo se ora facciamo un passo avanti...
1) Calcolare il "voto medio probabile": da come l' hai descritta basterebbe creare una colonna "Voti minimi" e una "Voti massimi", calcolare due voti di laurea basati su queste colonne e da questi due il risultato che cerchi.
Ogni VotoMinimo lo calcoli con la formula

Codice: Seleziona tutto

=Se(VotoInColonnaD=0;18;VotoInColonnaD)

Analogamente per il voto massimo:

Codice: Seleziona tutto

=Se(VotoInColonnaD=0;30;VotoInColonnaD)

In modo precauzionale e' meglio cambiare anche la formula che calcola la media ponderata in

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(C2:C22;D2:D22)/MATR.SOMMA.PRODOTTO(--VAL.NUMERO(D2:D22);C2:C22)

Questo per svincolarsi dalla colonna Si/No

2) Simulazione esito: fissato un obiettivo di voto, simulare i voti mancanti necessari per ottenerlo
Allo scopo ti suggerisco di creare una ULTERIORE colonna "VotoSimulato"; questa colonna serve a evidenziare i voti reali, quelli di col D nel tuo file, da quelli immaginari.
Supponiamo che questi voti si troviano in col E (colonna da inserire, spostando le colonne compilate verso destra); per prima cosa saranno da variare le formule di VotoMinimo e VotoMassimo modificandole rispettivamente in

Codice: Seleziona tutto

=Se(VotoInColonnaD=0;se(VotoInColonnaE=0;18;VotoInColonnaE);VotoInColonnaD)

=Se(VotoInColonnaD=0;se(VotoInColonnaE=0;30;VotoInColonnaE);VotoInColonnaD)

Avevi gia' calcolato il voto di laurea corrispondente ai VotiMinimi e ai VotiMassimi; per questo calcolo useremo il calcolo basato sui VotiMassimi.
Metti in una cella il voto Desiderato (io ho usato K18), e in un' altra cella la formula

Codice: Seleziona tutto

=Ass(VotoDesiderato - VotoLaureaVotiMax)

Supponiamo che questa formula sia in L18.

A questo punto installa e lancia questa macro:

Codice: Seleziona tutto

Sub Myvotes()
'Vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=96590
Dim mySim As String, Voto, VotoR As String

VotoR = "D2:D22"    '<<< L' area che contiene i voti "guadagnati sul campo"
'    alla Destra dei voti veri si trovera' la colonna VotiSimulati
'
mySim = ""
Range(VotoR).Offset(0, 1).Clear
SolverReset
    SolverOk SetCell:="$L$18", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$20:$E$22", _
        Engine:=3, EngineDesc:="GRG Nonlinear"    '"Evolutionary"

For Each Voto In Range(VotoR)
    If Voto = "" Then
        mySim = mySim & "," & Voto.Offset(0, 1).Address
        SolverAdd CellRef:=Voto.Offset(0, 1).Address, Relation:=1, FormulaText:="30"
        SolverAdd CellRef:=Voto.Offset(0, 1).Address, Relation:=3, FormulaText:="18"
        SolverAdd CellRef:=Voto.Offset(0, 1).Address, Relation:=4, FormulaText:="integer"
    End If
   
Next Voto
mySim = Mid(mySim, 2, 999)
If Len(mySim) < 2 Then Exit Sub
'
    SolverOk SetCell:="$L$18", MaxMinVal:=2, ValueOf:="0", ByChange:= _
        mySim
   
    SolverSolve '(True)
End Sub


La macro imposta una simulazione coerente con le celle vuote in colonna D (i voti reali)
Il risultato e' mostrato in questa immagine:



Uploaded with ImageShack.us
(click sull' immagine per vedere l' immagine completa)

Le frecce mostrano i "precedenti" delle celle coinvolte, limitatamente ai primi 3 livelli; le colonne VotoSimulati, VotoMin e VotoMax corrispondono a quanto detto prima. Il calcolo "Voto di Laurea" e' fatto secondo le formule che avevi scritto tu, non ho motivo di dubitare della loro precisione. La cella gialla e' L18a cui e' agganciato il Risolutore, la cella verde e' quella in cui ho scritto il voto desiderato.
Ho usato la modalita' di simulazione GRG Nonlinear, credo che per gli obiettivi del calcolo sia ampiamente sufficiente; comunque i tempi di ricerca della simulazione sono dipendenti dal numero di voci da simulare e hanno un andamento esponenziale, quindi sii paziente quando chiedi all' oracolo una proiezione molto futura.

Per un infelice "taglio" non si vedono le intestazioni di colonna, che vanno semplicemente dalla A in avanti.

Se sfrutti questi suggerimenti (anche parzialmente) ti prego di rimuovere l' indicazione (C) che hai inserito sul foglio

Ciao, fai sapere.

[paolino791]

Grandeeeee ti ringrazio Funziona Solo tre cose:
1) Ma per la prima e la seconda richiesta devo usare due fogli differenti? Cioè devo avere in totale 2 colonne voto min e 2 voto max??
2) Le celle voto simulato mi restituiscono pure numeri con la virgola, mica si può evitare questa cosa?
3) Quel numero li affianco al voto desiderato rappresenta lo scostamento tra il voto desiderato e quello che puoi riuscir a raggiungere??

Grazie ancora

[Anthony47]

Con le colonne aggiuntive che ho detto di mettere fai tutto sullo stesso foglio.
Devi solo calcolare due volte il voto di laurea, una volta usando i voti di col F (voto minimo) e una volta quelli di col G (voto massimo), poi fai la media tra questi valori e ottieni il "voto medio" che cercavi.

Nell' immagine di ieri, in M10 c' e' il calcolo "Voto di laurea massimo" che poi ho agganciato al simulatore dei voti mancanti.
La macro dovrebbe inserire un vincolo di "Intero" sulle celle da simulare; esegui la macro, poi avvia il risolutore dal menu Dati: dovresti vedere le regole impostate. Quindi non mi torna il discorso dei dati con la virgola (a meno che non intendi che vengono visualizzati mettiamo come 28,00 cioe' con cifre decimali ma di valore zero; se e' cosi' e' solo perche' quelle celle hanno una formattazione con cifre decimali che puo' essere variata come ti piace).

Ciao

[paolino791]

Eh ma a me non da dei numeri interi, cioè li da solo dalla cella B20 in giù, xò se levo un esame tipo alla b19 mi da 23,31....ecc

[paolino791]

Ne approfitto della tua immensa competenza per chiederti altre due cosine...Come mai nel risolutore hai usato GRG non lineare e non ad esempio quello evolutivo??
E volendo si potrebbe fare una userform che modifica le celle "materia" e "cfu" per rendere appunto questo file buono per tutte le facoltà??? (So che magari si potrebbe fare con un altro foglio iniziale xò vorrei farlo con le userform se si può così ho un buon motivo per impararle a fare)

Grazie.

[Anthony47]

Humm... ho scoperto che la regola Integer e' accettata solo se la relativa cella e' gia' inclusa tra le celle variabili; modifica le righe
SolverOk SetCell:="$L$18", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$20:$E$22", _
Engine:=3, EngineDesc:="GRG Nonlinear" ' "Evolutionary"
in
SolverOk SetCell:="$L$18", MaxMinVal:=2, ValueOf:=0, ByChange:=(Range(VotoR).Offset(0, 1).Address), _
Engine:=3, EngineDesc:="GRG Nonlinear" ' "Evolutionary"

Ho usato GRG Nonlinear perche' non si puo' usare LP Simplex (come impostato il problema non e' lineare) e il metodo Evolutionary prende troppo tempo.

In quanto all' uso di una userform per modificare i campi non ne intuisco il vantaggio; se vuoi predisporre il file per piu' facolta' allora il mio suggerimento e' che in un altro foglio crei l' elenco delle materie e del cfu, poi sul foglio principale fai la scelta della facolta' usando o una cella con convalida o una casella di riepilogo, che poi usi come chiave per visualizzare materie e cfu associate.
Comunque se vuoi usare una userform, potresti cominciare cercando nell' help on line di excel la voce "Panoramica di moduli, controlli modulo e controlli ActiveX in un foglio di lavoro"

Ogni sperimentazione e' buona se vuoi imparare.

Ciao

[paolino791]

In quell'altro modo mi ha aumentato troppo i tempi di risposta..Ho risolto inserendo nella macro l'approssimazione a numero intero di tutte le celle interessate Grazie ancora.

[paolino791]

Ah due domandine veloci..come si fa a bloccare delle celle(ad esempio quelle con dentro le formule) in modo che non possono essere modificate?? e che serve "mysim" all'interno del codice vba??

[Anthony47]

In quell'altro modo mi ha aumentato troppo i tempi di risposta..Ho risolto inserendo nella macro l'approssimazione a numero intero di tutte le celle interessate Grazie ancora.

Prego. Pero' non ho capito che ti succedeva e come hai risolto...

Se vuoi proteggere alcune celle allora:
-seleziona le celle che non vuoi proteggere ed elimina la formattazione "Cella bloccata"
-proteggi quindi l' intero foglio di lavoro.
Per ulteriori spunti guarda l' help on line di excel alla voce "Come proteggere una o piu celle in un foglio di Excel"

mySim e' il nome arbitrario che ho dato a una "variabile" in cui inserisco gli indirizzi delle celle su cui va effettuata la simulazione.

Ciao

[paolino791]

Boh i tempi di risposta erano alti mettendo la modifica che mi hai detto tu successivamente e allora ho rimasto la versione iniziale e ho approssimato i numeri del voto simulato(alcuni uscivano decimali) a numeri interi con qualche riga di codice alla fine della macro.. Ora funziona perfettamente

Cmq non riesco a capire come è possibile che nel codice del risolutore tu scrivi

Codice: Seleziona tutto

ByChange:="$E$20:$E$22"

xò le celle che si modificano sono da E2 a E22..Come è possibile ciò??

Un'altra cosa che non ho capito è questa

Codice: Seleziona tutto

MATR.SOMMA.PRODOTTO(--VAL.NUMERO(D2:D22);C2:C22)

Perchè ci sono quei due trattini prima di val.numero?

Ci sta una guida dove si parla di mysim, len e mid?? Questi comandi non li ho mai sentiti...

Grazie per la pazienza

[Anthony47]

Cmq non riesco a capire come è possibile che nel codice del risolutore tu scrivi

Codice: Seleziona tutto

    ByChange:="$E$20:$E$22"

xò le celle che si modificano sono da E2 a E22..Come è possibile ciò??

Quel pezzo di istruzione e' in testa alla macro (anzi, ERA, perche' l' avresti dovuta modificare come detto ieri sera); verso la fine c' e' l' istruzione che imposta realmente le celle da simulare:

SolverOk SetCell:="$L$18", MaxMinVal:=2, ValueOf:="0", ByChange:= _
mySim

Ora che dici che i tempi erano alti ti chiedo: non e' che avevi modificato anche questa seconda istruzione??

I due "meno" servono nella formula a trasformare l' elenco dei Vero/Falso in 1/0 per poter lavorare all' interno della funzione MATR.SOMMA.PRODOTTO; puoi vedere il comportamento usando il comando Valuta formula (disponibile nella scheda Formula, gruppo Verifica formule).

Se vuoi conoscere tutto delle funzioni len e mid (o di altri Proprietà, metodi e funzioni, che sono gli elementi caratterizzanti il linguaggio vba) usa l' help on line del compilatore di macro; come shortcut: metti il cursore dentro quelle parole (1 click), premi F1.
mySim e' un nome arbitrario a una variabile di tipo stringa che mi sono immaginato e creato io; avrei potuto chiamarla "pippo" e andava sempre bene (conta la natura e il valore del suo contenuto, non il nome assegnato al contenitore).

Ciao

[paolino791]

Ho riprovato stamattina a rimettere quel codice lì che mi hai detto e in alcuni casi va proprio in blocco..invece com'era prima va in ogni caso...boh
Il codice adesso è così(gli ho fatto giusto qualche piccola modifica)

Codice: Seleziona tutto

Sub Myvotes()

Dim mySim As String, Voto, VotoR As String

VotoR = "D2:D22"
mySim = ""
Range(VotoR).Offset(0, 1).Clear
SolverReset
    SolverOk SetCell:="$L$18", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$20:$E$22", _
        Engine:=3, EngineDesc:="GRG Nonlinear"    '"Evolutionary"

For Each Voto In Range(VotoR)
    If Voto = "" Then
        mySim = mySim & "," & Voto.Offset(0, 1).Address
        SolverAdd CellRef:=Voto.Offset(0, 1).Address, Relation:=1, FormulaText:="30"
        SolverAdd CellRef:=Voto.Offset(0, 1).Address, Relation:=3, FormulaText:="18"
        SolverAdd CellRef:=Voto.Offset(0, 1).Address, Relation:=4, FormulaText:="integer"
    End If
   
Next Voto
mySim = Mid(mySim, 2, 999)
If Len(mySim) < 2 Then Exit Sub
'
    SolverOk SetCell:="$L$18", MaxMinVal:=2, ValueOf:="0", ByChange:= _
        mySim
       
    SolverSolve UserFinish:=True
    Range("E2:E22").Select
    Selection.NumberFormat = "0"
    Range("L18").Select
    Selection.NumberFormat = "0.00"
    If Range("L18") > 0.5 Then
    MsgBox ("Errore...Non è possibile raggiungere il voto inserito!")
    End If

E poi ci sono alcuni settaggi per i bordi delle celle ma niente di che...Cmq in questo modo mi funziona alla perfezione!

Aggiungendo quel codice come hai detto tut l ho testato in soluzioni un pò più complesse tipo quando non dovrebbe "essere possibile raggiungere il voto inserito" e si impalla...

[Flash30005]

Un buon sistema per ottenere quello che desideri è impegnarti a studiare Pianificazione dei Trasporti, invece di perdere tempo con questa macro, (visto che in Analisi dei dati & Sistemi Informatici prevedi 28), se riesci a prendere 26 (invece di 23) potresti laurearti anche con 98 invece di 97

Ciao e in bocca al lupo!

[paolino791]

Un buon sistema per ottenere quello che desideri è impegnarti a studiare Pianificazione dei Trasporti, invece di perdere tempo con questa macro, (visto che in Analisi dei dati & Sistemi Informatici prevedi 28), se riesci a prendere 26 (invece di 23) potresti laurearti anche con 98 invece di 97

Ciao e in bocca al lupo!

Hhuashaushaus non ho quei voti lì..XD era un esempio E voglio farlo sia per altri che per me per imparare al meglio l'excel...penso che a un ingegnere gestionale potrà servire

[Anthony47]

In effetti il vincolo "intero" complica invece di semplificare, e costringe Solver a valutare tutte le combinazioni prima di dare la risposta. Gia' con 4 esami da risolvere il tempo si avvicina a 30 secondi; quindi 5 esami richiederanno circa 6 minuti, e cosi' via (un aumento pari a un fattore 12 per ogni esame). E' per questo che avevo scritto "quindi sii paziente quando chiedi all' oracolo una proiezione molto futura".

L' uso dei decimali consente di calcolare piu' rapidamente l' arrivo sul target perche' gestisce meglio la curva di avvicinamento al target.

Puo' aiutare l' uso dell' opzione "Tolleranza sugli interi", per default settata su 1%; un settaggio piu' "lasco" non altera tangibilmente la precisione del calcolo ma ne riduce drasticamente la durata: gia' al 5% con 10 esami da valutare il tempo di esecuzione e' inferiore a 3 secondi e la precisione rimane piu' che adeguata. Per questa impostazione aggiungi:

Codice: Seleziona tutto

SolverOptions IntTolerance:=5   '<<  AGGIUNGERE
    SolverSolve (True)     '<< Presente


Comunque eliminando il vincolo dell' intero i tempi sonio 10 volte inferiori.

Infine nota che con le istruzioni che hai inserito cambi la formattazione delle celle, cosi' vedi 26 ma il suo contenuto rimane (ad esempio) 26,456789

Ciao