Problema Excel: Predire la Migliore Combinazione di Variabili per un Risultato Noto

Question

Salve a tutti, vorrei porvi un quesito per cui non ho trovato soluzione e non saprei che formula applicare su Excel 2013. 

Supponiamo di avere una lista di studenti che hanno terminato il corso di studi in anni differenti (COLONNA A) e che hanno sostenuto 8 materie diverse (COLONNE B, D, F, H, L, N, P, R) ciascuno  non necessariamente seguendo la stessa combinazione. Ad esempio, uno studente potrà avere sostenuto esami in chimica, biologia, antropologia, letterature classica, recitazione, danza, fisica, e sport; mentre un altro potrà aver sostenuto esami nelle seguenti discipline: psicologia, inglese, spagnolo, chimica, sport, design, legge, e economia.

Ammettiamo inoltre che ogni studente sia stato valutato in ciascuna materia (quindi 8 voti espressi nelle colonne direttamente successive alle rispettive materie; COLONNE C, E, G, I, M, O, Q, S) e abbia ricevuto una valutazione per il completamento dell'intero corso di studi (COLONNA T).

Come posso calcolare la probabilità che uno studente x, avendo sostenuto un esame y ed ottenendo una valutazione z , abbia di ricevere il grado più alto di valutazione per il completamento dell'intero corso di studi?

Ad esempio: se uno studente avrà ottenuto A in Biologia (materia che potrà comparire in una qualsiasi delle 8 colonne B, D, F, H, L, N, P, R per ogni studente) quale sarà la sua probabilità di avere ottenuto 30 e lode al termine del percorso di studi?

Impostando il problema, il numero delle soluzione è derivato dal numero dei soggetti X il numero delle materie/esami X la singola valutazione di ogni materia X il voto finale di tutto il percorso di studi. Data la grande quantità di dati, soprattutto di materie che ogni studente ha incluso nel proprio percorso di studi, mi chiedo se esista una formula, o più formule, per calcolare:

Se in una delle colonne B, D, F, H, L, N, P, R sia presente il nome di una stessa materia y a cui sia stato assegnato una valutazione z, qual è la probabilità di ottenere la votazione finale già nota?

Vi ringrazio in anticipo per i vostri commenti e spero di aver espresso adeguatamente il problema.

Answer 1

Salve Andrea,

Vi ringrazio per la gentile risposta. Ecco il link al file:

...

Ciao,

la probabilità - in senso classico o matematico - che un evento si possa verificare è di per sé facile da calcolare: come ben sappiamo essa è data dal rapporto tra il numero dei casi favorevoli all’evento e il numero dei casi possibili. Questo semplice rapporto, alle volte, nasconde delle insidie, perché non sempre è così facile determinare il numero dei casi favorevoli e/o quelli possibili, in particolar modo nel caso di eventi composti o condizionati.

Ciò premesso, è fondamentale, sommare correttamente le informazioni di cui disponiamo e, la struttura del foglio che hai sottoposto non è certo la migliore per ottenere i risultati desiderati, sebbene, vista la mole dei dati, possa semplificarne la lettura.

In primo luogo ho ottenuto una lista univoca delle materie di esame, delle votazioni e delle valutazioni inserite, che sono state inserite nel nuovo foglio ‘Filters’. E’ servito per valutare l’entità delle informazioni presenti nel foglio ‘Dati’, oltre che in seguito per ottenere una convalida sui dati inseriti (ove ce ne fosse bisogno).

Come dicevo, le informazioni presenti nel foglio ‘Dati’ sono difficilmente totalizzabili ai tuoi scopi, anche utilizzando una tabella pivot., per non dire impossibile avvalendosi di semplici formule. Per questo ho creato un nuovo foglio ‘DataAndy’ che contiene sostanzialmente i dati presenti nel tuo foglio ma in una forma diversa, in modo da renderli più semplicemente totalizzabili anche con l’utilizzo di formule (non fosse per la dimensione degli intervalli sui quali andrebbero a lavorare).

Questo passo è fondamentale e spero di non aver interpretato male le tue esigenze. A te interessa sapere la probabilità di ottenere il massimo nella votazione finale, avendo ottenuto una determinata votazione in uno e solo uno specifico esame, mentre, invece, non fai riferimento alle votazioni di più esami, che per quanto detto sopra sarebbero eventi composti o condizionati e complicherebbero non di poco i calcoli.

In buona sostanza, ho eseguito la fusione delle otto serie di esami, ognuna composta da tre colonne, in un’unica. Le colonne si sono ridotte da 34 a 14, mentre le righe sono conseguentemente aumentate da 570 a 4.550, tieni presente però che oltre 1.700 di queste non contengono alcuna materia e quindi, sebbene io le abbia lasciate per completezza, potrebbero essere, invece, tranquillamente eliminate – utilizzando il filtro - in quanto ininfluenti a fini del calcolo.

Fatto questo, ho creato una tabella pivot, visto e considerato che l’avevi utilizzata anche tu, che dovrebbe fornire i dati di cui hai bisogno per effettuare i calcoli. Nel medesimo foglio ho inserito anche un piccolo strumento per trovare agevolmente, nel foglio ‘DatiAndy’ e direttamente nella tabella pivot i dati di cui potresti aver bisogno. Le formule sono molto semplici ma fanno uso di alcuni nomi definiti.

Ovviamente il risultato finale è una mia personale interpretazione e ti invito a verificarla e controllarla.

Il grosso del lavoro è stato svolto con l'utilizzo delle macro che renderò disponibili nel caso fossero di utilità comune.

Credo di aver detto tutto e con questo ecco il nuovo Entry Quals 2008-2013.

Buon lavoro,

Andrea

Answer 2

Salve Andrea,

Vi ringrazio per la gentile risposta. Ecco il link al file:

http://sdrv.ms/JeOrBb

Le lettere delle colonne riportate nel primo post non coincidono perfettamente a quelle del file poiché nella tavola di lavoro excel sono contenuti altri dati (seppur ininfluenti ai fini del problema). In ogni modo, sotto  le colonne indicanti Qual-1, Qual-2,etc..  sono chiaramente indicate le materie, e nelle colonne direttamente successive il voto ("grade").

Answer 3

...

Vi ringrazio in anticipo per i vostri commenti e spero di aver espresso adeguatamente il problema.

Ciao,

forse hai spiegato abbastanza bene, ma per situazioni complesse come queste, sarebbe utile, per chi intende cimentarsi, usufruire di un file con dati anonimi ma possibilmente reali e un paio di esempi chiarificatori che illustrino il risultato desiderato ottenuto dai dati dell'esempio.

Non è importante che riporti tantissimi dati, ma che questi rispecchino, il più verosimilmente, la situazione reale.

Ecco le istruzioni per pubblicare file e immagini nei forum di Microsoft Community.

Andrea.