Tabella Dinamica

[Ale.1989Partecipante Ale.1989]

Ciao a tutti ragazzi sono di nuovo a chiedere un vostro consiglio.

Vorrei sapere se è possibile ricercare in una tabella i valori più simili ad una cella e la cui somma sia il più vicino possibile ad un altra cella.

In questa tabella alcune celle vengono cancellate se il valore è scritto in un intervallo.

Provo ad allegare un esempio 

La somma da trovare è quella in c3, e devo trovarla sommando, in questo caso 3 valori ( valore scritto manualmente nella cella f4 ) ricercandoli in l5:l38 e che verranno scritti in f5:f10

E questi valori devono essere il più vicini possibile al valore di f3

Allego file in cui è già riportato il risultato che dovrei ottenere, in questo caso la somma da trovare è 1.5729 e i valori devono essere vicino a 0.5243 

La somma di valori in f5;f10 da 1.5768

Spero di essermi spiegato il meglio possibile

 

 

[Marius44Moderatore Marius44 Karma: 58 pts]

Ciao

Scusa, per errore ho eliminato l'allegato. Puoi inserirlo di nuovo? Grazie.

Ciao,

Mario

[Ale.1989Partecipante Ale.1989]

eccolo

Allegati:

You must be logged in to view attached files.

[Marius44Moderatore Marius44 Karma: 58 pts]

Ciao

Premesso che credo si possa fare SOLO con VBA mi occorrono delle precisazioni:

a) l'intervallo I5:I38 è vuoto

b) dove hai preso il valore in F7?

c) a cosa servono i valori nelle colonne K:Q?

Ciao,

Mario

[LukeRedsPartecipante LukeReds Karma: 19 pts]

ciao,

ho capito (molto) poco della richiesta, ad cchio direi ci vuole il risolutore ma dovresti charire bene l'argomento

[Ale.1989Partecipante Ale.1989]

Ciao, Mario ti rispondo 

a) l’intervallo è L5-L38

b) il valore in f7 l’ho preso dalla cele L31 

c) i valori delle colonne k e q servono a me per un altra cosa, non hanno a che fare con questa richiesta 

[Ale.1989Partecipante Ale.1989]

Ciao, so che è un po’ complicato da spiegare ho cercato di esprimere quello che dovrei fare … 

[Raffaele53Partecipante Raffaele53 Karma: 23 pts]

Ciao a tutti, solo una domanda per risolvere col VBA
Nell'esempio cerchi tre fili che si avvicinano a 1,5729
I fili devono essere solo tre oppure possono essere 3/4/5 fili?

Edit Eventuale altro esempio, se sarà indicato 4 fili in B4... i fili saranno quattro da cercare?

[LukeRedsPartecipante LukeReds Karma: 19 pts]

soluzione che non tiene conto del 3 che hai messo come esempio

in I1 va messa la formula  =MATR.SOMMA.PRODOTTO(I5:I35;L5:L35)

in I2 =C3-I1

va abilitato il ccomponente aggiuntivo SOLVER

Nel file allegato basta cliccare il pulsante per eseguire la sub

Se allego il file mi viene cancellata la soluzione......

Sub Risolutore()
'aggiungere componente SOLVER
SolverReset
Range("I5:I35") = 1
Range("F5:F11").ClearContents
Dim i As Integer, r As Integer
SolverOk SetCell:="$I$2", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$5:$I$35", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$I$2", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$I$5:$I$35", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$I$5:$I$35", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$I$5:$I$35", Relation:=4, FormulaText:="intero"
SolverOk SetCell:="$I$2", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$5:$I$35", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$I$2", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$5:$I$35", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
  SolverSolve UserFinish:=True, ShowRef:=False

r = 5
For i = 5 To 35
   If Cells(i, "I") = 1 Then
      Cells(r, "F") = Cells(i, "L")
      r = r + 1
   End If
Next i
End Sub`
 

 

 

[LukeRedsPartecipante LukeReds Karma: 19 pts]

provo ad allegare il file in un altro post

Allegati:

You must be logged in to view attached files.

[Raffaele53Partecipante Raffaele53 Karma: 23 pts]

@lukereds, complimenti usare SOLVER mi è sempre stato complesso.
Ps. Nel quesito annuncia che manca il filo 0,8 e nella Tua soluzione proposta lo assegna
In J32 hai eliminato la formula, rimettendola (in L32 sparisce il valore) e Solver trova un'altra soluzione con 4 fili (già richiesto con quanti fili cercare).

Domanda: In Solver come si fa a dirgli di usare pure doppioni di numeri presenti?

[LukeRedsPartecipante LukeReds Karma: 19 pts]

ciao,

non so se ho capito correttamente la domanda, provo a indicare un tutorial che spiega (a mio parere bene) il risolutore

Comunque la soluzione sopra non trova un valore esatto ma il valore approssimato (per difetto) più vicino

[Ale.1989Partecipante Ale.1989]

Ok la soluzione così funziona però mi sono espresso male, nel file che ho allegato all'inizio dicendo di non poter utilizzare il filo di diametro 0.8 cancellava la cella alla sua destra ( 0.5027 ) che è il valore che non si può utilizzare per raggiungere il target.

Per rispondere alla domanda di Raffaele il numero contenuto in F4 indica quanti addendi posso utilizzare per arrivare al risultato finale.

La colonna K indica il " nome del filo " mentre la colonna L indica il valore da usare per raggiungere l'obbiettivo.

Le celle D3;D10 indicano il "nome" del filo che non posso usare, questo "nome" viene ricercato nella colonna K e di conseguenza il valore che non posso utilizzare nella colonna L

[LukeRedsPartecipante LukeReds Karma: 19 pts]

se un valore non si può ulilizzare.....o lo togli dall'elenco (se non puoi usarlo perchè è là?) o nella colonna I corrispondente metti uno 0 a programma al posto di 1

[Ale.1989Partecipante Ale.1989]

Ciao Luke in pratica a seconda dei fili disponibili in magazzino vorrei un programmino che mi calcolasse la combinazione di fili più simile possibile a quella originale 

[Raffaele53Partecipante Raffaele53 Karma: 23 pts]

Usando il files di @lukereds, sono riuscito tramite formule ad escludere eventuali fili mancanti.
Valido per max 49 fili, sono riuscito a far usare più volte lo stesso filo.
Mi manca di mettere un "vincolo" che i fili usati debbano essere uguali a B4 (tre)
Se qualcuno riesce mettere questo "vincolo", penso sia a posto.

Ps. Domani faccio il VBA dei tre fili che pensavo, ma devo sapere se la somma dei tre deve essere inferiore a 1,5729 oppure possa anche superarlo? (Il più vicino possibile)

Allegati:

You must be logged in to view attached files.

[LukeRedsPartecipante LukeReds Karma: 19 pts]

ciao @raffaele53,bisogna aggiungere il vincolo, tenendo i tuoi riferimenti, I3=F4

Il calcolo risulterà molto più lento

[Raffaele53Partecipante Raffaele53 Karma: 23 pts]

Come si deve procedere? Aggiungi la riga subito sotto...
>>>SolverAdd CellRef:="$J$2:$J$" & uR, Relation:=4, FormulaText:="intero" riga già esistente
SolverAdd CellRef:="$I$3", Relation:=2, FormulaText:="$B$4"

Dopo due minuti trova ed estrae 1,5689 =(0,900 + 0,700 + 0,250)

Momentaneamente ho creato il codice per 3-fili, dovrei fare 2-fili, 4-fili, 5-fili, 6-fili
Tramite 3-fili, trovo che il numero più vicino a 1,5729 e diverso (secondo me sarebbe il N°2, poi ci sarebbe la Tua scelta del Tuo allegato ed terzo la scelta di Solver)
1) 0,00400 1,5689 0,25 - 0,75 - 0,9
2)-0,00370 1,5766 0,45 - 0,95 - 0,95
3)-0,00390 1,5768 0,75 - 0,85 - 0,85

Altra differenza sarebbe che avrei una lista per poter scegliere

[Autore]

Articoli