| La
funzione Strumenti -> Ricerca Obiettivo (Tools -> GoalSeek) di
Excel permette di ricercare il valore da inserire in una particolare
cella (leva) per ottenere in una seconda cella il valore voluto
(obiettivo).
Questa funzionalità è
estremamente comoda quando la relazione che collega la cella
"leva" con la cella "obiettivo" è complessa ed
è necessario identificare il valore della leva per rispettare un
vincolo imposto sull' "obiettivo".
Lo strumento in Excel permette di
modificare una singola cella "leva" per ottimizzare una
singola cella "obiettivo". Nel momento in cui si hanno
più di una cella "leva" e per ognuna di esse una
corrispondente cella "obiettivo" da ottimizzare è
necessario ripetere la procedura per ogni singola coppia di celle
rendendo la procedura laboriosa e noiosa. Per maggiori informazioni
riguardo alla funzionalità, scaricate
il manuale messo a disposizione su questo stesso sito.
Questa macro Excel (che vi
consiglio di inserire nel vostro foglio personal.xls)
permette di eseguire più procedure "Ricerca Obiettivo" su
più coppie di celle "leve" e "obiettivo" in
modo estremamente veloce. Per rendere la procedura ancora più
comoda e veloce, la macro assume che l'obiettivo sia sempre quello
di porre a zero il valore delle celle "obiettivo". Nel
caso in cui l'obiettivo non sia zero è sufficiente, come nel caso
qui evidenziato, calcolare la differenza tra la cella
"risultato" e il valore effettivo da avere e applicare la
macro alla differenza.
Come vedete in
questo esempio ci sono due "leve" attualmente ai valori 55
e 160. la prima leva influenza il risultato 110 e la seconda leva
influenza il risultato 320. Gli obiettivi da perseguire, cioè i
valori da ottenere nelle stesse celle sono invece 150 e 150. Le
distanze dall'obiettivo sono dunque -40 e 170.
Con la funzione
"Ricerca Obiettivo" è necessario eseguirla due volte
selezionando in totale 4 celle e inserendo due volte il valore
obiettivo da perseguire.
Con questa macro è
sufficiente:
si esegue dunque una
selezione multipla sul foglio dove la prima parte della selezione
contiene le "leve" e la seconda le "differenze dagli
obiettivi":
A questo punto è
sufficiente eseguire la macro ottenendo:
Le differenze sono
state entrambe poste a zero modificando le due "leve".
La macro è la
seguente:
______________________________________________________________
Sub Multiple_GoalSeek()
Dim Change_cell As Range
Dim Target As Range
Dim CCW As Integer
Dim CCH As Integer
Dim TW As Integer
Dim TH As Integer
Dim i As Integer
Dim j As Integer
Dim NumberOfSelectedAreas As Integer
NumberOfSelectedAreas = Selection.Areas.Count
If NumberOfSelectedAreas <> 2 Then
MsgBox ("Devi selezionare due aree, la prima con i dati da variare, la seconda con le celle da porre a zero, le due aree devono avere dimensioni identiche; a ogni cella della prima area deve corrispondere la stessa
cella risultato nella seconda area")
Else
Set Change_cell = Selection.Areas(1)
Set Target = Selection.Areas(2)
CCH = Change_cell.Columns(Change_cell.Columns.Count).Column - Change_cell.Column + 1
CCW = Change_cell.Rows(Change_cell.Rows.Count).Row - Change_cell.Row + 1
TH = Target.Columns(Target.Columns.Count).Column - Target.Column + 1
TW = Target.Rows(Target.Rows.Count).Row - Target.Row + 1
If CCW <> TW Or CCH <> TH
Then
MsgBox ("Le due aree devono avere dimensioni identiche")
Else
i = 1
j = 1
For i = 1
To CCW
For j = 1
To CCH
Target.Cells(i, j).GoalSeek Goal:=0, ChangingCell:=Change_cell.Cells(i, j)
Next
Next
End If
End If
End Sub
_______________________________________________________________
Funzionamento della Macro:
Prima di tutto si verifica che il
range selezionato sia costituito da due aree distinte, anche se le
celle "leva" e "differenza" sono attigue è
necessario selezionarle con la procedura indicata, prima le
"leve", poi premendo il tasto CTRL le
"differenze" in modo da permettere alla macro di
distinguerle. In caso in cui non siano state selezionate due aree
viene presentato un messaggio indicando il problema.
Una volta identificate le due aree vengono assegnate a due variabili
temporanee "Change_cell" e "Target". Per ognuna
di esse vengono verificate le dimensioni e vengono confrontate.
Poiché ad ogni "leva" deve corrispondere una
"differenza" i due range devono essere di forma e
dimensioni identici. A questo punto, per ogni cella "leva"
e la corrispondente cella "differenza" viene eseguito il
GoalSeek ponendo la cella "differenza" al valore zero.
Tenete presente un fatto
importante: la macro esegue il goalseek (cerca obiettivo)
esattamente come fareste voi, automatizzando semplicemente la
procedura di eseguire i goalseek su più celle. Pertanto, nel caso
in cui le celle su cui state eseguendo la macro non sono adatte
alla procedura di goalseek, la macro stessa darà errore.
Scaricate
direttamente il file Excel usato nell'esempio
|
