Condividi:        

[EXCEL] Classifica con più condizioni

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[EXCEL] Classifica con più condizioni

Postdi Bigger » 09/10/13 09:55

Buongiorno a tutti e complimenti per il forum di grande aiuto... vi leggo da molto ed ora avrei bisogno se fosse possibile di una mano :oops:
Inizio con il caricare un'immagine
Immagine

Io inserisco un numero di prede che si moltiplica per un valore bonus fisso (50) ed il peso in grammi... il risultato finale deve essere una "Classifica Prova" che mi serve a trovare i vincitori della prova stessa e un punteggio "Penalità" che mi servirà poi alla fine per redarre una classifica finale su più prove.
Io ho messo queste formule :
La colonna "Punti" (J) è =SOMMA(G13*$H$9)+I13
La colonna "Classifica Finale" (A) è =RANGO.UG(J10;$J$10:$J$14)
La colonna "Penalità" (K) è =SE(D10="X";SOMMA.SE(J$10:J$14;J10;A$10:A$14)/CONTA.SE(J$10:J$14;J10)+1;SOMMA.SE(J$10:J$14;J10;A$10:A$14)/CONTA.SE(J$10:J$14;J10))

Allora le condizioni devono essere per la "Classifica Prova" colonna A :
- Chi ha più punti (J) avremo un 1 - 2 - 3.....
- A Parità di punti vince chi ha più Prede (G)
- A parità di punti e prede si sorteggia quindi mi va bene un random

Le Condizioni per la colonna "Penalità" (K)
- Al 1-2-3-4-5 in classifica vanno rispettivamente 1-2-3-4-5 penalità
- A parità va data la media delle rispettive penalità quindi per un 3 e 4 posto ((3+4)/2)=3.5 per un 4 e 5 ((4+5)/2)=4.5 per un 2, 3 e 4 posto ((2+3+4)/3)=3 se tutti non prendono nulla ((1+2+3+4+5)/5)=3
- Se qualcuno è "Assente" (D) deve prendere una penalità in più del massimo delle stesse, quindi in un gruppo di 5 il massimo delle penalità è di 5 + 1= 6

Spero di essere stato chiaro, di essere riuscito a spiegarmi e ringrazio in anticipo chi mi volesse dare una mano...
l'unica cosa in più che dico è che i partecipanti in ogni settore potrebbero aumentare (comunque fino ad un massimo di 10) e che anche i settori potrebbero aumentare esempio 100 partecipanti divisi in 10 settori.... questo lo dico perchè magari credo sia più indicata una macro che "forse" è più versatile... comunque come meglio credete voi...
Grazie ancora
Avatar utente
Bigger
Newbie
 
Post: 9
Iscritto il: 09/10/13 08:57

Sponsor
 

Re: [EXCEL] Classifica con più condizioni

Postdi Anthony47 » 09/10/13 11:50

Ciao Bigger, benvenuto nel forum.
Spero di essere stato chiaro, di essere riuscito a spiegarmi
Beh, quasi chiaro; pero' non ho capito che cosa dovrebbe fare la macro...

Ti aspettiamo quindi con un supplemento di spiegazioni.
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [EXCEL] Classifica con più condizioni

Postdi Bigger » 09/10/13 12:04

Prima di tutto grazie per l'attenzione...
Mi servirebbe una macro e/o funzione per le colonne A "Classifica Finale" e (K) "Penalità"...
Quelle che ho messo io ovviamente non sono complete e non soddisfano tutte le condizioni che ho sopra citato
S.O. Windows 8 e Office 2013
Avatar utente
Bigger
Newbie
 
Post: 9
Iscritto il: 09/10/13 08:57

Re: [EXCEL] Classifica con più condizioni

Postdi scossa » 09/10/13 12:37

Bigger ha scritto:Prima di tutto grazie per l'attenzione...
Mi servirebbe una macro e/o funzione per le colonne A "Classifica Finale" e (K) "Penalità"...
Quelle che ho messo io ovviamente non sono complete e non soddisfano tutte le condizioni che ho sopra citato

Ciao,

scusa l'appunto, ma tu hai già il file pronto quindi, anziché obbligare chi ti volesse aiutare a ricostruirselo "a manina", potresti condividerlo postando il link per poterlo scaricare?

A presto.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 427
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [EXCEL] Classifica con più condizioni

Postdi Bigger » 09/10/13 13:37

scossa ha scritto:Ciao,
scusa l'appunto, ma tu hai già il file pronto quindi, anziché obbligare chi ti volesse aiutare a ricostruirselo "a manina", potresti condividerlo postando il link per poterlo scaricare?
A presto.


Hai perfettamente ragione ! Scusatemi !
Ecco qui : http://rapidshare.com/share/51DD5C5AB864CF98D04B1B6E4F3DBEBA
Spero di aver fatto giusto, avendo io office 13 ho allegato i file nei formati per il 13 e per il 07
Ditemi se vi risulta ok il download
S.O. Windows 8 e Office 2013
Avatar utente
Bigger
Newbie
 
Post: 9
Iscritto il: 09/10/13 08:57

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 09/10/13 18:14

Ciao a tutti

per la classifica in A, io proporrei:
in J4: =G4*50+I4+G4*0,000001
in A4: =RANGO(J4;$J$4:$J$8)
da trascinare in basso
per le penalità, ancora non ho capito bene come funzionano
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 09/10/13 18:37

la tua formula in K4 la puoi snellire così: =(D4="X")*1+SOMMA.SE(J$4:J$8;J4;A$4:A$8)/CONTA.SE(J$4:J$8;J4)
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [EXCEL] Classifica con più condizioni

Postdi Bigger » 09/10/13 19:18

Ti ringrazio innanzi tutto...
Tralasciamo un minuto il problema "Penalita"
E tralasciamo il modo di semplificare la mia formula in K4 che mi sembra sostanzialmente non cambi nulla....
Il tuo metodo di contare il "Punteggio" in J4 differisce dal mio per il fatto che tu moltiplichi X 0.000001 e vorrei capirne il motivo... vorrei capire i vari step per imparare ovviamente...
Ed in A4 il "RANGO" nel mio Excel 2013 è stato sostituito da RANGO.UG (non saprei dove stanno le differenze) ad ogni modo con il RANGO a parità di prede e peso ho un rango uguale e questo non va bene
Allora le condizioni devono essere per la "Classifica Prova" colonna A :
- Chi ha più punti (J) il "RANGO" funziona bene e avremo un 1 - 2 - 3.....
- A Parità di punti vince chi ha più Prede (G) qui il RANGO così com'è già si perde
- A parità di punti e prede si sorteggia quindi mi va bene un random
S.O. Windows 8 e Office 2013
Avatar utente
Bigger
Newbie
 
Post: 9
Iscritto il: 09/10/13 08:57

Re: [EXCEL] Classifica con più condizioni

Postdi scossa » 09/10/13 21:53

Ciao,

la formula di ninai (ciao) è perfetta.

Bigger ha scritto:Il tuo metodo di contare il "Punteggio" in J4 differisce dal mio per il fatto che tu moltiplichi X 0.000001 e vorrei capirne il motivo...


Per dare un "peso" al numero delle prede nello stabilire il punteggio;

Bigger ha scritto:- A Parità di punti vince chi ha più Prede (G) qui il RANGO così com'è già si perde


usa la formula di ninai

Bigger ha scritto:---- ad ogni modo con il RANGO a parità di prede e peso ho un rango uguale e questo non va bene
......
- A parità di punti e prede si sorteggia quindi mi va bene un random


Se vuoi risolvere con le sole formule (senza VBA) puoi usare Casuale.Tra() ma devi impostare il ricalcolo del foglio su manuale altrimenti ad ogni ricalcolo automatico la posizione degli ex aequo potrebbe continuamente cambiare.

Riguardo la penalità, ovviamente la tua formula è errata perché fa riferimento alla posizione della classifica già pesata e quindi priva di ex aequo, per cui dovresti utilizzare una colonna con la classifica "non pesata" (classifica solo per punti) e riferirti a quella.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 427
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [EXCEL] Classifica con più condizioni

Postdi Bigger » 09/10/13 23:00

Quindi la moltiplicazione x 0.000001 non condiziona il risultato con l'approssimazione finale che abbiamo ma è abbastanza per creare appunto un indice per il RANGO che ne rileverà la rilevanza appunto.... se ho capito bene... ok ora provo a simulare i vari casi ed a rivedere anche le altre.... intanto grazie mille...
S.O. Windows 8 e Office 2013
Avatar utente
Bigger
Newbie
 
Post: 9
Iscritto il: 09/10/13 08:57

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 10/10/13 00:08

Ringrazio e saluto Scossa per l'attenta disamina della situazione e del mio precedente intervento.
con l'uso del Casuale.tra() , la vedo dura anche con il ricalcolo manuale (dovrà inserire i dati e ricalcolare prima o poi).
Mi chiedo inoltre, quante possibilità ci sono che lo stesso numero di prede pesino esattamente uguali (al grammo).


Per la penalità (come preannunciato da Scossa), una via possibile è l'uso di una colonna di appoggio, es. la "L" , dove scrivi in L4: =H4+I4
ed in K4: =(D4="X")*1+SOMMA.SE(L$4:L$8;L4;A$4:A$8)/CONTA.SE(L$4:L$8;L4)
ovviamente trascinate in basso
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 10/10/13 01:09

ariciao
ho capito che il random serve nel caso di parimeriti a zero punti e dalla necessità di differenziare le penalità.
analizzando meglio la problematica, ci sarebbe anche questa funzione che ti potrebbe assolvere alle penalità:
=RANGO.MEDIA(L4;$L$4:$L$8)

mi resta di capire come gestire lo zero di chi non prende niente e lo zero di chi è assente. Chi è assente, nel caso ci siano altri concorrenti presenti però a punteggio zero , il punto in più, lo prende sempre sul 5??? oppure sulla media??
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [EXCEL] Classifica con più condizioni

Postdi Bigger » 10/10/13 07:39

Rispondo subito ai tuoi quesiti ringraziandoti nuovamente e poi mi vado a provare le tue formule
ninai ha scritto:Mi chiedo inoltre, quante possibilità ci sono che lo stesso numero di prede pesino esattamente uguali (al grammo).

poche, molto poche ma capitano ed ovviamente voi mi potete insegnare che bisogna prevedere il più possibile
ninai ha scritto:ho capito che il random serve nel caso di parimeriti a zero punti e dalla necessità di differenziare le penalità.
mi resta di capire come gestire lo zero di chi non prende niente e lo zero di chi è assente. Chi è assente, nel caso ci siano altri concorrenti presenti però a punteggio zero , il punto in più, lo prende sempre sul 5??? oppure sulla media??

Il "RANDOM" mi serve per trovare un vincitore, fare il sorteggio dei parimerito per trovare il vincitore o comunque stilare una classifica della prova, le penalità invece verranno portate alla fine di un tot di prove e serviranno a stilare la classifica finale, quella per il campione assoluto... ovviamente anche alla fine di tutto ci saranno dei controlli ma ora non mi servono perchè se risolvo la prova singola ho praticamente fatto
Se non ci sono problemi sugli exequo al 1° va 1 penalità al 2° ne vanno 2 e così via
Se uno fa 1 sola preda e altri 4 nulla avremo il vincitore con 1 penalità, gli altri 4 avrebbero la media della somma delle loro penalità quindi (2+3+4+5)/4=3.5 penalità a testa
In questa situazione avrei il vincitore della prova e dovrei sorteggiare le altre posizioni per premiare la prova stessa singola con il numero di penalità mi serviranno per fare classifica alla fine....

Inoltre la differenza di uno assente è che và penalizzato, multato per la sua assenza quindi in ogni caso prende il numero di penalità massime + 1 di multa diciamo.... uno che fa 0 ma è presente concorre normalmente...

Ora vado a vedermi i vostri suggerimenti :lol:
S.O. Windows 8 e Office 2013
Avatar utente
Bigger
Newbie
 
Post: 9
Iscritto il: 09/10/13 08:57

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 10/10/13 08:01

Ciao
Ma classifica per le penalità tiene conto solo del numero di prede?? Se è così non ce bisogno di colonna di appoggio
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [EXCEL] Classifica con più condizioni

Postdi Bigger » 10/10/13 08:18

No no sempre con il punteggio totale dato dal numero di prede che generano il bonus prede sommate al peso
Quando praticamente si saranno fatte 10 prove singole che avranno avuto i loro "campioni" di prova si stilerà una classifica di campionato data dal numero di penalità... a quel punto il campione del mondo (termine improprio) sarà colui che ha meno penalità ed a parimerito di penalità si calcoleranno le prede totali di tutte le prove..... ovviamente tutto questo verrà dopo dove farò un foglio che sarà il riepilogo, la sintesi di tutte le prove... se risolvo appunto il problema in questo foglio è fatta...

Ho provato a fare la colonna in appoggio e ad usare il RANGO.MEDIA ed a primissimo impatto sembra funzionare ma ovviamente non mi fa il controllo sull'assente in pratica l'assente sarebbe al 5° posto e mi mette 5 penalità ma come assente appunto dovrebbe avere la multa addizionale di 1 penalità

La cosa che stavo cercando di fare io era far agire le formule in modo indipendente nel senso che il controllo sulle penalità fino ad ora era incrociato e dipendente alla classifica... bisognerebbe riuscire a ragionare indipendentemente l'una dall'altra come se ci fosse o l'una o l'altra.... non ci sono problemi a fare colonne o righe in appoggio che dopo andrei a nascondere
S.O. Windows 8 e Office 2013
Avatar utente
Bigger
Newbie
 
Post: 9
Iscritto il: 09/10/13 08:57

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 10/10/13 09:44

Per il punto da assenza non è un problema,
(Basta la parte iniziale della mia formula), non ho capito se gli assenti (anche se più di uno e con altri a zero) vanno sempre a 5+1 oppure si fa la media e poi si aggiunge 1.
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 10/10/13 12:02

esempio della seconda ipotesi:
1°: 0 punti
2°: 200 punti
3°: 0 punti
4°: 0 punti
5°: 0 punti ed assente
se le penalità devono essere: 3,5-1-3,5-3,5-4,5 , allora la formula potrebbe essere: =(D4="X")*1+RANGO.MEDIA(L4;$L$4:$L$8)

Se invece gli assenti prendono sempre 6 penalità e gli eventuali altri giocatori a zero vanno in media escludendo gli assenti, la cosa si complica.
nell'esempio precedente sarebbe: 3-1-3-3-6
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [EXCEL] Classifica con più condizioni

Postdi Bigger » 10/10/13 13:20

Allora ad assente vanno sempre 5 + 1
Nel tuo es :
1° : 0 pt 3penalità
2° : 200 pt 1 penalità
3° : 0 pt 3 penalità
4° : 0 pt 3 penalità
5° : 0 pt assente 6 penalità

altro es:
1° : 0 pt 3penalità
2° : 0 pt 3 penalità
3° : 0 pt 3 penalità
4° : 0 pt 3 penalità
5° : 0 pt 3 penalità

altro es :
1° : 0 pt assente 6 penalità
2° : 0 pt assente 6 penalità
3° : 0 pt assente 6 penalità
4° : 0 pt assente 6 penalità
5° : 0 pt assente 6 penalità

altro :
1° : 100 pt 1,5 penalità
2° : 100 pt 1,5 penalità (il primo 1 pen. il secondo 2 pen. quindi (1+2)/2
3° : 0 pt assente 6 penalità
4° : 0 pt assente 6 penalità
5° : 0 pt assente 6 penalità

"Se invece gli assenti prendono sempre 6 penalità e gli eventuali altri giocatori a zero vanno in media escludendo gli assenti, la cosa si complica."

è esatto così

Questo discorso vale solo per le penalità e non per la classifica della prova.... spero di essermi spiegato e di essermi fatto capire sulla differenza tra la classifica (col- A) e le penalità che ripeto a mio avviso dovremmo cercare di trattarle separatamente indipendenti l'una dall'altra perchè effettivamente è così
Non ti ringrazio più ma è sottinteso
S.O. Windows 8 e Office 2013
Avatar utente
Bigger
Newbie
 
Post: 9
Iscritto il: 09/10/13 08:57

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 10/10/13 16:25

un po impastata ma sembra funzionare:
Codice: Seleziona tutto
=SE((D4="X");6;SE(L4=0;5-CONTA.SE($D$4:$D$8;"x")-0,5*(MATR.SOMMA.PRODOTTO(($L$4:$L$8=0)*($D$4:$D$8="")*1)-1);RANGO.MEDIA(L4;$L$4:$L$8)))
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: [EXCEL] Classifica con più condizioni

Postdi ninai » 10/10/13 16:54

a questo punto, in A, la classifica si potrebbe AVVICINARE a quella che vuoi tu, con questa formula: =SE((D4="X");5;SE(L4=0;MATR.SOMMA.PRODOTTO(($L$4:$L$8>0)*($D$4:$D$8="")*1)+1;RANGO(J4;$J$4:$J$8)))

funziona tranne per gli assenti, quando sono più di uno
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "[EXCEL] Classifica con più condizioni":


Chi c’è in linea

Visitano il forum: Nessuno e 87 ospiti