[Excel] Sommare gli n elementi più alti di un elenco

[rudypale]

ciao a tutti,

avrei bisogno di una dritta, come da titolo del post.

Immaginate una colonna A1:A10 cui sono associati valori B1:B10 (in alcune celle della colonna B nn ci sono valori) non ordinati in alcun modo.

1.
Vorrei una formula che mi restituisse la somma dei 5 valori più alti

2.
Si può fare la stessa cosa ipotizzando che 5 non sia un dato certo, ma sia a sua volta un numero restituito da un'altra funzione (ad esempio una funzione di Minimo di un'altra serie di valori)?


Cercando info in giro mi è parso di capire che potrebbe aiutarmi la funzione RANGO, ma a dire il vero non ho capito bene a cosa serve e come funziona, quindi non son riuscito ad usarla...

[Anthony47]

Ciao rudypale e benvenuto nel forum.
C’ e’ stata solo qualche giorno fa una discussione analoga, vedi http://www.pc-facile.com/forum/viewtopic.php?t=64702
Se non risolvi con quanto scritto lì fatti riposta che approfondiamo.

[rudypale]

Ciao rudypale e benvenuto nel forum.
C’ e’ stata solo qualche giorno fa una discussione analoga, vedi http://www.pc-facile.com/forum/viewtopic.php?t=64702
Se non risolvi con quanto scritto lì fatti riposta che approfondiamo.

Grazie per il benvenuto...

Il topic l'ho letto, è li che ho visto la funzione RANGO, ho provato ad applicarla al mio caso ma non ha funzionato.

Ora, tramite amici, ho risolto, ma il metodo è un po' complicato.
In pratica, con la funzione Grandezza scopro il quinto valore più alto.
Poi creo una nuova colonna di valori dove, con una semplice funzione test, copio il valore (se maggiore del quinto individuato in precedenza) o 0 (zero) se minore.
Conto i valori diversi da zero: se sono 5, allora ci sono. Se sono meno di 5, allora vuol dire che il quinto valore devo sommarlo tante volte quante mi manca ad arrivare a 5 valori, e con apposita formula sistemo tutto. (il problema è che se i valori sono ad esempio 10, 8, 7, 4, 4, 4, 4, 1, 1, 1, il quinto valore è 4, ma questi va sommato due volte ed ignorato altre due volte).
Il metodo è replicabile anche se "5" non è un numero, ma è a sua volta un'altra funzione.

Come vedi, il sistema è contorto, ma forse più a spiegarlo che a prepararlo.
Ora, però, mi rimane la curiosità: esiste un sistema più semplice? Cos'è questo RANGO?

Grazie.

[Anthony47]

In effetti ci sono tanti modi per arrivare a un risultato.

Nel post segnalato c' e' l' uso di RANGO oppure l' uso di Ordina e somma N celle; se N e' un numero variabile, fai (dopo l' ordinamento) la somma con qualcosa come

Codice: Seleziona tutto

=SOMMA(SCARTO;A1;0;0;B1)

dove A1 e' la prima cella utile dei dati e B1 e' il numero di celle da inserire nel calcolo.

Cosa e' la funzione RANGO (oppure SCARTO) te lo dice l' help on line di excel; sull' affermazione "non ha funzionato" non so fare nessun ragionamento: dimmi dove sono i dati, che formula hai usato, che risultato ti ha dato, che cosa ti aspettavi.

Ciao, fatti sentire.

[rudypale]

Con SCARTO funziona, ma c'è l'inconveniente che bisogna ordinare i valori.

La formula con RANGO riportata nell'altro post era:

=SOMMA(A1:A13*(RANGO(A1:A13;$A$1:$A$13)<11))

dove A1:A13 è l'intervallo con i valori da sommare e 11 è il numero di valori da sommare

Io l'ho trasformata in:

=SOMMA(D4:D30*(RANGO(D4:D30;$D$4:$D$30)<A34))

dove D4:D30 è l'intervallo con i valori da sommare (in alcune di queste celle non c'è il valore) e A34 è la cella dove è riportato il numero di valori da sommare ed è a sua volta il risultato di una formula di MINIMO.

Sul RANGO, non mi è proprio chiaro il concetto (forse da giovane, ma adesso...). Cito testualmente dalla guida di excel:

"Restituisce il rango di un numero in un elenco di numeri. Il rango di un numero è la sua dimensione in rapporto agli altri valori presenti nell'elenco. Nel caso in cui fosse necessario ordinare l'elenco, il rango del numero corrisponderebbe alla rispettiva posizione".

In che senso che il RANGO è la sua dimensione in rapporto ad altri valori? E, tornando al caso specifico, perché vado a moltiplicare l'intervallo di valori da sommare per il RANGO?

Credo che si stia scivolando troppo nelo matematico per capire facilmente...

[rudypale]

Rispondo a me stesso per aggiornare...

Credo di aver capito a cosa serve il rango (molto utile, peraltro). In pratica, ti restituisce la "posizione in classifica" di un certo valore rispetto ad un insieme di valori.

La mia domanda, ora, è questa. Dato un insieme di valori D4:D30 (il mio ultimo esempio), per sommare gli "n" valori più alti devo:

1. sommare ogni singolo valore con rango inferiore a "n". In questo modo, diciamo che sommo un numero "k" di valori, dove "k" è per forza inferiore a "n" (perché ho escluso, per ora, di sommare l'n-esimo valore;

2. aggiungere "n-k" volte il valore il cui rango è "n".


Per quanto rigarda il punto 1, la formula dovrebbe essere:

Codice: Seleziona tutto

=SOMMA.SE(D4:D30;"RANGO(D4:D30;D4:D30;0)<$A$34")

dove A34 è la cella che mi restituisce il famoso numero "n". L'errore, cmq, credo sia qui, perché excel non mi considera solo il rango di ogni singolo elemento, ma il rango dell'intera serie...

Per quanto riguarda il punto 2, invece, la formula dovrebbe essere:

Codice: Seleziona tutto

GRANDE(D4:D30;$A$34)*($A$34-RANGO(GRANDE(D4:D30;A34);D4:D30;0))

Cioè, individuato il valore con rango "n", lo moltiplico "n-k". Qui forse sono un po' contorto, spero di non aver cannato.

Cmq, credo che l'errore sia nella prima parte della formula, in cui non riesco a dire di considerare il rango di ogni singolo elemento...

Son riuscito a farmi seguire nel ragionamento?

[Anthony47]

1- Non puoi usare SOMMA.SE, perche' non accetta la sintassi che serve.

2- Ma la formula =SOMMA(D4:D30*(RANGO(D4:D30;$D$4:$D$30)<A34)) perche' non funziona? Hai seguito le istruzioni, in particolare di confermare la formula NON con Enter MA con Contr-Maiusc-Enter?

3- per la domanda "perché vado a moltiplicare l'intervallo di valori da sommare per il RANGO?", non so se e' ancora attuale; comunque la formula non moltiplica per il RANGO ma per VERO (=1) o FALSO (=0) a seconda dell' esito del confronto.

Ciao.

[rudypale]

1- Non puoi usare SOMMA.SE, perche' non accetta la sintassi che serve.

2- Ma la formula =SOMMA(D4:D30*(RANGO(D4:D30;$D$4:$D$30)<A34)) perche' non funziona? Hai seguito le istruzioni, in particolare di confermare la formula NON con Enter MA con Contr-Maiusc-Enter?

3- per la domanda "perché vado a moltiplicare l'intervallo di valori da sommare per il RANGO?", non so se e' ancora attuale; comunque la formula non moltiplica per il RANGO ma per VERO (=1) o FALSO (=0) a seconda dell' esito del confronto.

Ciao.

1 - Ok, farò tesoro

2 - Ho seguito le indicazioni, ma mi dà come risultato #N/D. La cosa dipende dal fatto che in non tutte le celle D4:D30 è inserito un valore.
Ho, quindi, verificato inserendo il valore "0" nelle celle vuote; in questo caso la formula non mi dà errori, ma il risultato è sbagliato. credo che l'errore risieda nel fatto che l'(n-1)-esimo, l'n-esimo e l'(n+1)-esimo coincidono (il valore è 4, e la somma è in eccesso di proprio di 4), quindi hanno lo stesso rango e quindi somma anche l'(n+1)-esimo valore. Come posso correggere?

3 - ok, non capisco il senso, ma grazie per la precisazione.


Già che ci sono, aggiungo una domanda: cosa fa di differente Ctrl+Maiusc+Invio invece di invio? Inoltre, contando che i valori nell'elenco potrebbero cambiare, la formula si aggiornerebbe cmq in automatico (come qls formula di excel) o dovrei ridare Ctrl+Maiusc+Invio?

Grazie e CIAO

[rudypale]

1- Non puoi usare SOMMA.SE, perche' non accetta la sintassi che serve.

2- Ma la formula =SOMMA(D4:D30*(RANGO(D4:D30;$D$4:$D$30)<A34)) perche' non funziona? Hai seguito le istruzioni, in particolare di confermare la formula NON con Enter MA con Contr-Maiusc-Enter?

3- per la domanda "perché vado a moltiplicare l'intervallo di valori da sommare per il RANGO?", non so se e' ancora attuale; comunque la formula non moltiplica per il RANGO ma per VERO (=1) o FALSO (=0) a seconda dell' esito del confronto.

Ciao.

1 - Ok, farò tesoro

2 - Ho seguito le indicazioni, ma mi dà come risultato #N/D. La cosa dipende dal fatto che in non tutte le celle D4:D30 è inserito un valore.
Ho, quindi, verificato inserendo il valore "0" nelle celle vuote; in questo caso la formula non mi dà errori, ma il risultato è sbagliato. credo che l'errore risieda nel fatto che l'(n-1)-esimo, l'n-esimo e l'(n+1)-esimo coincidono (il valore è 4, e la somma è in eccesso di proprio di 4), quindi hanno lo stesso rango e quindi somma anche l'(n+1)-esimo valore. Come posso correggere?

3 - ok, non capisco il senso, ma grazie per la precisazione.


Già che ci sono, aggiungo una domanda: cosa fa di differente Ctrl+Maiusc+Invio invece di invio? Inoltre, contando che i valori nell'elenco potrebbero cambiare, la formula si aggiornerebbe cmq in automatico (come qls formula di excel) o dovrei ridare Ctrl+Maiusc+Invio?

Grazie e CIAO

[Anthony47]

Dopo qualche giorno di assenza mi stava sfuggendo questo argomento.
Nel topic a cui ti avevo rimandato (vedi http://www.pc-facile.com/forum/viewtopic.php?t=64702) ci sono alcune ipotesi su come convivere con ranghi uguali; suggerisco di considerare l’ ipotesi di manipolare i valori di partenza con la tecnica del +RIF.RIGA(); puoi farlo in una colonna di servizio (ch sara’ quella che utilizzerai nella funzione RANGO); cosi’ risolvi anche il problema delle celle vuote.

TUTTAVIA, visto che e’ la seconda volta in poche settimane che viene posto lo stesso problema con le stesse variabili, ho implementato la funzione TopN che appunto calcola la somma dei top N di un elenco; il codice e’:

Codice: Seleziona tutto

Function TopN(Rjj, N)
'calcola la somma dei top N nel range Rjj
Dim Presi As Integer, I As Integer
Presi = 0
For I = 1 To N
For Each Cella In Rjj
If Val(Cella) = 0 Then GoTo Skippa
If Application.WorksheetFunction.Rank(Cella, Rjj) = I And Presi < N Then
TopN = TopN + Val(Cella)
Presi = Presi + 1
End If
Skippa:
Next Cella
If Presi >= N Then Exit Function
Next I
End Function

Va inserito su un Modulo del tuo file: apri il vba editor con Alt-F11; Menu /Inserisci /Modulo; copi il codice e lo incolli nel frame di destra)
Accetta anche celle vuote o contenenti stringhe, purche’ non corrispondano (le stringhe) a un numero.

Uso:
se l’ elenco e’ in D4:D30 e i valori da prendere sono in A34, userai la formula

Codice: Seleziona tutto

=TOPN(D4:D34;A34)

Ovviamente puoi usare sintassi piu’ complesse, es =TOPN(D4:D34;MIN(A1:A100))

Nel tuo messaggio, inoltre, a proposito della mia precisazione “la formula non moltiplica per il RANGO ma per VERO (=1) o FALSO (=0) a seconda dell' esito del confronto” tu rispondi: ok, non capisco il senso, ma grazie per la precisazione.
Ti faccio un esempio: se cerchiamo i primi 5, e (ad esempio) il valore 22 ha come rango 11° non viene fatta l’ operazione 22*11 ma 22*0; e’ piu’ chiaro, vero?
Infine, per l’ uso di Contr-Maiusc-Enter, vedi l’ help on line, inserendo “formule matrice” nel box di Ricerca libera.

Ciao.

[rudypale]

Beh Anthony, complimenti!

Onestamente nn avevo mai sentito di qlcn che "creasse" formule, quindi che dire...

Nel frattempo, io ho approfondito gli argomenti e ci ho capito un po' di più. Sono arrivato, quindi, a troavare la soluzione con questa formula a matrice:

Codice: Seleziona tutto

=SOMMA((C4:C30)*(C4:C30>GRANDE(C4:C30;$A$34)))+GRANDE(C4:C30;$A$34)*(1+$A$34-RANGO(GRANDE(C4:C30;$A$34);C4:C30;0))

dove C4:C30 è l'intervallo dei valori da sommare, mentre A34 il numero da considerare.

Un po' (tanto) macchinosa, ma gira. L'unico dubbio che ho è nel caso in cui ci siano tanti valori uguali che hanno rango immediatamente superiore a quello "di frontiera". Cioè, se devo sommare i primi 18 e ci sono 7 elementi uguali con rango 15, come faccio a recuperarli?

Ad oggi, però, la formula funziona.

Una domanda sulla tua, invece: se inserisco la formula, questa rimane "incorporata nel file"? O nel mio PC? Al lato pratico, se condivido il mio file con la formula (cosa che dovrei fare) con altri, devo dire loro di inserirla?

Comunque, complimenti e grazie.

[rudypale]

Ho provato la formula. L'ho inserita e l'ho salvata, ma non la trovo nell'elenco funzioni.

Se scrivo la formula, mi dà errore #NOME ...

A questo punto sn curioso...

[Anthony47]

Ho provato la formula. L'ho inserita e l'ho salvata, ma non la trovo nell'elenco funzioni.
Se scrivo la formula, mi dà errore #NOME ...

..potresti aver scritto il codice in una "Cartella degli Oggetti excel" invece che in un "Modulo" di codice: cosa leggi nell' intestazione della finestra del Vba che ti visualizza il codice?
Oppure tra il codice di un altro file, es il Personal.xls?

Per la tua formula, non c’ e’ male… e direi che dovrebbe funzionare sempre! In effetti essa somma tutti gli elementi in elenco che hanno un valore “superiore” a quello il cui valore corrisponde al rango impostato (e che quindi hanno un rango piu’ al top rispetto alla soglia), piu’ il valore di soglia per quante volte manca per arrivare al numero di elementi specificati (un po' difficile anche da descrivere, ma scomponendola in due si capisce benino).
In effetti, pur essendo il complemento a RANGO, la funzione GRANDE non soffre del problema degli ex-aequo.

Un ulteriore modo per eseguire il calcolo, se si puo’ utilizzare una colonna di servizio e’ questa:
-nella colonna di servizio (es la E) calcolare il “rango unico” del valore con la formula

Codice: Seleziona tutto

=RANGO(C4;$C$4:$C$30)+CONTA.SE($C$4:C4;C4)-1

-calcolare quindi il risultato cercato con

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(C4:C30;(E4:E30<=A34)+0)

Per quanto riguarda la domanda circa la funzione TopN (se la ritroviamo...), essa rimane nel file; scambiando il file tutti possono usare la funzione.

Ciao.

[rudypale]

Ho provato la formula. L'ho inserita e l'ho salvata, ma non la trovo nell'elenco funzioni.
Se scrivo la formula, mi dà errore #NOME ...

..potresti aver scritto il codice in una "Cartella degli Oggetti excel" invece che in un "Modulo" di codice: cosa leggi nell' intestazione della finestra del Vba che ti visualizza il codice?
Oppure tra il codice di un altro file, es il Personal.xls?

C'è scritto "prova.xls - Modulo2" (Prova è il nome del file). Nei due menu a tendina immediatamente sotto, in uno c'è scritto "generale", nell'altro "TopN".

Un ulteriore modo per eseguire il calcolo, se si puo’ utilizzare una colonna di servizio e’ questa:

e no, niente colonna di servizio, altrimenti troppo facile!
Con la colonna aggiuntiva bastava fare

Codice: Seleziona tutto

 =TEST(C4>GRANDE(C4:C30;$A$34); C4;0)

poi sommare la nuova colonna ed aggiungere (come nella formula precedente) il numero di valore soglia. Però volevo una soluzione senza aggiungere colonne.

[Anthony47]

Hummm...
Do' per scontato che hai "copiato" dal forum e "incollato" nel vba il codice di cui parliamo, da Function TopN(Rjj, N) a End Function, e quindi non ci sono errori di battitura.

Hai mica le macro disabilitate? se fa Menu /Strumenti /Macro /Protezione, che livello ti segnala? e quando apri il file, ti chiede se vuoi abilitare le macro? e gli rispondi Si?

Visto il livello di competenza faccio queste domande con un po' di circospezione, ma sono le uniche idee da cui partire.

Ciao.

[rudypale]

Hummm...
Do' per scontato che hai "copiato" dal forum e "incollato" nel vba il codice di cui parliamo, da Function TopN(Rjj, N) a End Function, e quindi non ci sono errori di battitura.

Hai mica le macro disabilitate? se fa Menu /Strumenti /Macro /Protezione, che livello ti segnala? e quando apri il file, ti chiede se vuoi abilitare le macro? e gli rispondi Si?

Visto il livello di competenza faccio queste domande con un po' di circospezione, ma sono le uniche idee da cui partire.

Ciao.

Il copia incolla è tutto ok, il livello di protezione macro è "Disabilita tutte le macro con notifica". Tieni presente che stiamo parlando di Office 2007.

Cmq non credo sia un problema di macro: ne ho create alcune (quando nn mi ero ancora messo a risolvere il problema con un'unica formula) e funzionano.

Non ti preoccupare per le domande, davvero può trattarsi di una cavolate che non saprei riconoscere...

[Anthony47]

Da quello che succede e che dici, credo che il problema sia che la Funzione TopN sia disabilitata; imposta la protezione su Media (ora e' forse su Molto elevata) e all' apertura del file abilita l' esecuzione delle macro.

Su http://office.microsoft.com/it-it/excel/HP052035251040.aspxho trovato alcune utili informazioni su come gestire la protezione su Excel 2007, dai uno sguardo.

Ciao, facci sapere!

[francescopanerai]

...ho implementato la funzione TopN che appunto calcola la somma dei top N di un elenco; il codice e’:

Codice: Seleziona tutto

Function TopN(Rjj, N)
'calcola la somma dei top N nel range Rjj
Dim Presi As Integer, I As Integer
Presi = 0
For I = 1 To N
For Each Cella In Rjj
If Val(Cella) = 0 Then GoTo Skippa
If Application.WorksheetFunction.Rank(Cella, Rjj) = I And Presi < N Then
TopN = TopN + Val(Cella)
Presi = Presi + 1
End If
Skippa:
Next Cella
If Presi >= N Then Exit Function
Next I
End Function

Va inserito ...

Ho una domanda stupida, ma data la mia incompetenza non riesco a risolvere il problema...
La fiunzione funziona (scusate il gioco di parole), ma solo con i numeri interi. Ad esampio, come somma di 7,5+7 mi da 14 e non 14,5... perchè?

[Anthony47]

Ciao francesco,
la funzione restituisce tutti i decimali che sono negli addendi; non e' che hai una "visualizzazione" senza decimali? Insomma, prova a formattare la cella con TopN con un paio di decimali e dicci cosa vedi.

Ciao.