Introduzione al risolutore L’importanza del modello Il ...ottostorto.it/Excel avanzato (4).pdf ·...

Excel – il risolutore

• Introduzione al risolutore

• L’importanza del modello• L’importanza del modello

• Il problema della simulazione

• Il vantaggio della simulazione

Brugnaro Luca - 2009

Prima di stampare pensa all’ambiente

think to environment before printing


Introduzione al risolutore

• I problemi applicativi normalmente hanno più di due variabili

• E’ necessario utilizzare sistemi di calcolo automatico per trattare grandi quantità di dati e di operazioni logico-aritmetichedi operazioni logico-aritmetiche

• Molte volte non avendo certezza sulle variabili che identificano un problema, si rende necessario poter effettuare delle simulazioni

• Esistono molti software per risolvere problemi simulazione tra questi anche Excel offre validi strumenti


Introduzione al risolutore

• Fa parte di una serie di comandi detti anche strumenti di analisi di simulazione

• Modifica dei valori contenuti nelle celle per verificare in che modo tale operazione influisce sul risultato delle formule nel foglio di lavoro.

• Il Risolutore utilizza un gruppo di celle correlate, direttamente o indirettamente, alla formula contenuta nella cella obiettivo.

• Il Risolutore modifica i valori delle celle variabili specificate in modo che la • Il Risolutore modifica i valori delle celle variabili specificate in modo che la formula contenuta nella cella obiettivo produca il risultato specificato.

• È possibile applicare vincoli (vincoli: Limitazioni poste per un problema del Risolutore. È possibile applicare i vincoli alle celle regolabili, alla cella di destinazione o ad altre celle direttamente o indirettamente correlate alla cella di destinazione.) per limitare i valori che il Risolutore potrà utilizzare nel modello.

• Tali vincoli possono fare riferimento ad altre celle che influenzano la formula contenuta nella cella obiettivo.


Inserimento dati di ingresso

• Si tratta di inserire in una tabella Excel i valori numerici utilizzati nel modello;

• Non è necessario inserirli in una posizione particolare;

• E’ preferibile seguire uno schema di base per favorire un’immediata visualizzazione;un’immediata visualizzazione;

• Ogni cella della tabella è univocamente individuata dalla posizione di colonna (data da una lettera alfabetica) e dalla posizione di riga (data da un numero);

• E’ buona abitudine quella di inserire delle celle di commento per agevolare l’identificazione del tipo di dato (costo, ricavo, ecc)


L’esempio


“Pianificare” lo spuntino pomeridiano

Pianificare i miei spuntini pomeridiani

Amo gli spuntini, ma devo tenere a bada colesterolo e aumento di peso quindi ho deciso di pianificare i miei spuntini in modo meticoloso.

Ho deciso che posso scegliere tra 2 possibili spuntini: merendine o gelati.

Dalla lettura delle etichette ho scoperto che:

• ogni merendina pesa 38 grammi e contiene 124 calorie e 6 grammi di grassi

• Ogni gelato pesa 65 grammi e contiene 180 calorie e 11 grammi di • Ogni gelato pesa 65 grammi e contiene 180 calorie e 11 grammi di grassi.

Secondo il mio “spietato” dietologo, posso introdurre non più di 500 calorie e 30 grammi di grassi dai miei adorati spuntini pomeridiani.

Secondo il mio personale desiderio, non posso fare a meno di uno spuntino di 130 grammi / die.

Inoltre da una personalissima scala di gradimento del gusto ho assegnato 85 ai gelati e 77 alle merendine.

Quesito: come dovrà essere il mio spuntino per massimizzare il gradimento e rispettare tutti i vincoli?


Pensa, pensa, pensa … e risolvi

• Quali sono i dati in input ?

• Quali sono le variabili ?

• Qual’è la funzione obiettivo ?• Qual’è la funzione obiettivo ?

• Quali sono i vincoli ?


Pianificare i miei spuntini pomeridiani

Variabili di input

Tipi di spuntino, calorie, grassi, peso, indice di gradimento, massimo permesso giornaliero, minimo richiesto giornaliero

Variabili su cui ponderare le scelte

Quantità giornaliera per ogni tipo di spuntino consumato

Obiettivo

Indice totale del gusto degli spuntini

Altre variabili calcolate

Quantità totale e totale grammi consumati giornalieri per spuntino

Vincoli

Quantità di spuntini assunti <= massimo permesso

Qunatità in grammi assunti >= minimo richiesto

… (vincolo di non negatività)


Definizione del modello

• Massimizzare:38*77*x1 + 65*85*x2

• Soggetta ai seguenti vincoli:124*x1 + 180*x2 <= 500

6*x1 + 11*x2 <= 30

38*x1 + 65*x2 >= 130

x1, x2 >= 0

x1, x2 devono essere interi?

calorie

grassi

grammi

non negatività


Il modello in excele nostre convenzioni

Sul foglio di calcolo di excel dovremmo avere:

• Celle per l’input (bordo colore blu)

• Celle per le variabili (bordo colore rosso)

• Cella obiettivo (doppio bordo)• Cella obiettivo (doppio bordo)

• Celle per i vincoli

• Celle di commento sono auspicabili


Teorema di Bayes

• Thomas Bayes (Londra, 1702 – Tunbridge Wells, 17 aprile 1761) è stato un matematico e ministro presbiteriano britannico. Deve la sua fama ai suoi studi nel campo della matematica e della filosofia; è noto soprattutto nella statistica per il suo teorema sulla probabilità condizionata, pubblicato postumo nel 1763.

Considerando un insieme di alternative A1,A2,...An (partizione dello spazio degli eventi) si trova la seguente espressione per la probabilità condizionata:

Dove:• P(A) è la probabilità a priori o probabilità marginale di A. "A priori" significa che non tiene conto di

nessuna informazione riguardo E.• P(A|E) è la probabilità condizionata di A, noto E. Viene anche chiamata probabilità a posteriori,

visto che è derivata o dipende dallo specifico valore di E.• P(E|A) è la probabilità condizionata di E, noto A.• P(E) è la probabilità a priori di E, e funge da costante di normalizzazione.Intuitivamente, il teorema descrive il modo in cui le opinioni nell'osservare A siano arricchite dall'aver

osservato l'evento E.


Atleti e il test anti doping

• Un test antidoping può essere positivo o negativo• Il test non è perfetto• Falsi positivi e falsi negativiAssumendo che:

– 5% degli atleti assuma farmaci dopanti – 3% sono i falsi positivi– 7% sono i falsi negativi

Supponiamo ora che un atleta sia testato. Nel caso risulti positivo, siamo sicuri che abbia assunto

farmaci dopanti?E se il test è negativo, siamo sicuri che l’atleta non abbia

assunto farmaci dopanti?


Qual è l’obiettivo?

• Stabilire la probabilità che un atleta ha usato farmaci dopanti data la positività o negatività del test antidoping

Da dove arrivano i numeri utilizzati?– 5% atleti assuma farmaci dopanti (statistiche

nazionali)

– 3% sono i falsi positivi, 7% sono i falsi negativi (ripetute sperimentazioni del test stesso)


Soluzione

• AD = atleta dopato

• AND = atleta non dopato

• T+ = test positivo

• T- = test negativo• T- = test negativo

Dai dati del problema abbiamo:

P(AD)=0.05 ; P(AND)=0.95 [prob. a priori del test]

P(T+|AND)=0.03 P(T-|AD)=0.07 quindi sappiamo anche che P(T-|AND)=0.97 e P(T+|AD)=0.93

[le ultime quattro probabilità sono condizionate]


Soluzione

Quindi date le probabilità a priori e quelle condizionate possiamo calcolare le probabilità a posteriori P(AD|T+) la probabilità che un atleta è dopato sapendo che il test è positivo e atleta è dopato sapendo che il test è positivo e P(AND|T-) … utilizzando il teorema di Bayes.

Le ultime probabilità si chiamano prob. a posteriori perché disponibili solo dopo il risultato del test.


Soluzione

• Usando il teorema di Bayes abbiamo:

P(AD|T+)= P(T+|AD)P(AD)

[P(T+|AD)P(AD) + P(T+|AND)P(AND)]

P(AND|T-)= P(T-|AND)P(AND)

[P(T-|AD)P(AD) + P(T-|AND)P(AND)]

Con i nostri numeri: 0.62 e 0.99


Soluzione 2

• Non tutti capiscono o sanno usare il teorema di Bayes

• Possiamo risolvere lo stesso problema utilizzando il risolutore di excel ragionando in utilizzando il risolutore di excel ragionando in modo più intuitivo…


Soluzione 2

• Supponiamo ci siano 10000 atleti. Il 5% sono dopati (AD) e gli altri no (AND). Decidiamo di sottoporre tutti al test antidoping. Ci aspettiamo un 3% di falsi positivi ( ) e un 93% di veri positivi ( ). Quindi osserviamo un totale di ( ) positivi al ). Quindi osserviamo un totale di ( ) positivi al test. Se scegliamo a caso uno di questi atleti, la probabilità che sia dopato sarà:

dopati con test positivo / tot. test positivi

Il risultato è indipendente dal nr. di atleti

Proviamo ora a rielaborare il tutto con excel


Hub ospedalieri


Quanti e quali ospedali saranno i futuri HUB ?

Hub ospedalieri

• La regione Y ha 12 presidi ospedalieri (osp1,…,osp12) e vuole creare dei poli di riferimento seguendo i seguenti criteri:– Ogni HUB deve distare non più di 20 minuti di

ambulanza dei sui ospedali periferici di riferimentoambulanza dei sui ospedali periferici di riferimento

– Gli HUB creati devono soddisfare tutti i presidi periferici ospedalieri della regione SPOKE

– Il numero di HUB da realizzare deve essere il minimo possibile .

La regione ci fornisce questa mappa delle distanze…


Hub ospedalieri

Tempo limite spostamento da SPOKE a HUB 20 minuti

HUB potenziali

Presidi ospedalieri Osp1 Osp2 Osp3 Osp4 Osp5 Osp6 Osp7 Osp8 Osp9 Osp10 Osp11 Osp12

Osp1 0 18 12 24 14 37 8 15 12 32 42 44

Osp2 18 0 17 33 31 50 26 4 10 40 52 50

Osp3 12 17 0 17 18 35 16 14 8 24 36 34


Osp3 12 17 0 17 18 35 16 14 8 24 36 34

Osp4 24 33 17 0 17 18 22 30 24 9 21 22

Osp5 14 31 18 17 0 26 6 27 22 24 32 38

Osp6 37 50 35 18 26 0 32 47 41 12 7 19

Osp7 8 26 16 22 6 32 0 22 18 29 38 43

Osp8 15 4 14 30 27 47 22 0 7 37 49 47

Osp9 12 10 8 24 22 41 18 7 0 31 43 42

Osp10 32 40 24 9 24 12 29 37 31 0 13 14

Osp11 42 52 36 21 32 7 38 49 43 13 0 14

Osp12 44 50 34 22 38 19 43 47 42 14 14 0

Per caricare la mappa clicca qui

Soluzione

• Variabili di input:

• Variabili decisionali:

• Obiettivo:

• Altre variabili calcolate:• Altre variabili calcolate:

• Vincoli:

Dopo aver risposto alle domande sopra riportate caricare il foglio di excel ove sviluppare il modello (HUB.xls). Usare il risolutore.


Date post:	16-Feb-2019
Category:	Documents
Upload:	vokiet
View:	216 times
Download:	0 times

Introduzione al risolutore L’importanza del modello Il ...ottostorto.it/Excel avanzato (4).pdf ·...

Documents