Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Formula "indice" in Excel

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

Formula "indice" in Excel

Postdi Beppe16v » 16/04/12 09:15

Ciao a tutti "geni" :D

Domanda sicuramente semplice (per voi), vi spiego prima a grandi linee la situazione:
ho un doc excel con diverse schede. Nella prima c'è una classifica assoluta, con 1° pippo, 2° pluto, 3° paperino, ecc... ma ognuno ha delle sottocategorie (tipo classi, raggruppamenti), ognuno evidenziato con una "A", una "B", ecc, in una colonna a fianco del nome. Per tali categorie nelle altre schede voglio che mi venga riportata la classifica apposta ma che sia via via aggiornata in automatico solo aggiornando la assoluta.

Pertanto, ho usato questo tipo di formula (per non usare le tabelle pivot):
=INDICE(ASSOLUTA!$B:$B;PICCOLO(SE(ASSOLUTA!$C:$C="M";RIF.RIGA(ASSOLUTA!$C:$C));A4))
il tutto racchiuso in parentesi graffe (shift+invio), pertanto mi va a pescare nella scheda "assoluta" chi della lista ha il codice "M" e me lo riporta dal primo all'ultimo nella cella dove mi trovo.

Detta questa premessa giusto per farvi capire, adesso mi trovo che appena non trova più nomi corrispondenti (mettiamo che nella assoluta di 10 nomi ce ne sono solo 5 con la "M") mi riporta nella cella il classico "NUM" (errore). Come faccio per tenere vuote (bianche) quelle celle dove non ci sono più nomi?

Scusate la lunghezza, ma so sicuramente che avrò ancora bisogno di voi anche in futuro, quindi ho preferito spiegare bene... eh eh eh! GRAZIE! ;)
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Sponsor
 

Re: Formula "indice" in Excel

Postdi CANAPONE » 16/04/12 09:34

Ciao,

se usi versioni di Excel precedenti ad Excel 2007

=se(val.errore(tuaformula);"";tuaformula)

Codice: Seleziona tutto
=SE(VAL.ERRORE(INDICE(ASSOLUTA!$B:$B;PICCOLO(SE(ASSOLUTA!$C:$C="M";RIF.RIGA($C:$C));A4)));"";INDICE(ASSOLUTA!$B:$B;PICCOLO(SE(ASSOLUTA!$C:$C="M";RIF.RIGA($C:$C));A4)))


Con Excel 2007 o versioni successive meglio usare SE.ERRORE

=se.errore(tuaformula;"")

Codice: Seleziona tutto
=se.errore(INDICE(ASSOLUTA!$B:$B;PICCOLO(SE(ASSOLUTA!$C:$C="M";RIF.RIGA($C:$C));A4));"")


Spero sia d'aiuto
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: Formula "indice" in Excel

Postdi Beppe16v » 16/04/12 10:39

Senza dubbio!!! Grazie, ignoravo l'esistenza della funzione che controlla l'errore sulla formula! ;)
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Re: Formula "indice" in Excel

Postdi Beppe16v » 16/04/12 14:23

Eh... tanto lo sapevo che "l'appetito vien mangiando"... :lol: Grazie a voi sto riuscendo a completare questo foglio di gestione punti per un campionato quasi totalmente automatico... ma adesso mi è venuta un'altra idea (che ovviamente non mi riesce metterla in pratica!!! :oops: ) ed ho bisogno ancora di voi.

Sempre "pescando" dalla classifica assoluta, ogni soggetto è segnato anche sotto il nome di un gruppo, non tutti diversi: quindi in un altra scheda a parte ho bisogno di estrapolare i primi 3 di ogni gruppo (quelli, anche se tanti presenti, con i 3 punteggi più alti) e riportare a fianco del nome del gruppo la somma di questi tre punteggi. Se ne sono presenti solo 2 prendere la somma di quei due, mentre se presente uno solo, non lo devo nemmeno considerare.

Ho paura di essermi intrecciato nella spiegazione. :undecided: Qualcuno ha capito qualcosa? :roll:
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Re: Formula "indice" in Excel

Postdi Anthony47 » 16/04/12 23:07

Ho paura di essermi intrecciato nella spiegazione. :undecided: Qualcuno ha capito qualcosa? :roll:
Forse che si, forse che no; comunque per ragionarci sarebbe utile un file con dei dati esemplificativi.
Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula "indice" in Excel

Postdi Beppe16v » 17/04/12 08:12

Hai ragione, abbiate pazienza... provo ad allegare una forma "semplificata" del mio foglio e tento di rispiegare cosa ho bisogno di fare. Poi non vi peritate di mandarmi "al gas" :D lo capisco...

Dal file allegato prendiamo in considerazione solo le schede "assoluta" e "scuderie". Come vedete nella "assoluta" ogni nome in classifica ha sulla destra un gruppo di appartenenza, chi lo stesso chi no. Io devo fare nella scheda "scuderie" una sorta di classifica dei gruppi prendendo la somma dei migliori 3 punteggi di ogni rappresentante di gruppo. Se ce ne sono più di 3 si prende sempre i migliori 3, se ce ne sono solo 2 di prende la somma di quei 2, se invece è presente uno solo non deve essere considerato. Pertanto di la avrei bisogno (se possibile in modo automatico), nel caso del mio esempio, che venga una classifica con "gruppo1" 27 punti e "gruppo3" 13 punti e nient'altro.

Inoltre, dopo ciò, sono bloccato anche con altra cosa: sempre nella "assoluta", voglio che nella colonna "punti validi" venga scartato il peggior punteggio di ogni soggetto. Quindi "punti totali" tutti, la somma delle 6 colonne, mentre nei "validi" solo i migliori 5. Questa forse è più semplice, ma mi sono intrecciato pure qua... :oops:

Grazie per qualsiasi cosa possiate fare, anche niente! ;)
Spero riusciate a prendere il file, tramite GoogleDocs:
https://docs.google.com/open?id=0B-ITD_ ... ks5NkRrRE0
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Re: Formula "indice" in Excel

Postdi Anthony47 » 17/04/12 14:08

Intendi che vanno presi i migliori 3 punteggi dalle colonne F:K di ogni concorrente elencato in col B e solo questi vanno sommati per calcolare il punteggio a livello di squadra?
Per quanto riguarda il secondo quesito, dovrebbe bastare che in M10 usi la formula
Codice: Seleziona tutto
=L3-MIN(F3:K3)


Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula "indice" in Excel

Postdi Beppe16v » 17/04/12 16:18

Esatto, come dici tu, il punteggio squadra è formato dalla somma dei migliori 3 del gruppo, con la particolarità che prende anche la somma dei migliori due, in caso fossero presenti solo due dello stesso gruppo. Se presente solo uno invece niente.

Perfetto per la seconda domanda, risolto. ;)

GRAZIE!!! :)
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Re: Formula "indice" in Excel

Postdi Anthony47 » 17/04/12 22:42

Esatto, come dici tu, il punteggio squadra è formato dalla somma dei migliori 3 del gruppo. . .
In realta' io dicevo una cosa diversa, ma quello che dici ha una logica e lo capisco: la classifica di gruppo e' ottenuta sommando solo max 3 punteggi piu' alti indipendentemente dal numero di concorrenti appartenenti al gruppo.
In questo caso:
-sul foglio ASSOLUTA, da O2 verso destra scrivi i nomi delle squadre che sono presenti
-in O3 calcoli il punteggio della prima squadra con la formula
Codice: Seleziona tutto
=SE(CONTA.NUMERI(SE($N$3:$N$100=O$2;SE($F$3:$K$100>0;$F$3:$K$100;"");""))>1;SOMMA(GRANDE(SE($N$3:$N$100=O$2;SE($F$3:$K$100>0;$F$3:$K$100;"");"");{1;2}))+SE(CONTA.NUMERI(SE($N$3:$N$100=O$2;SE($F$3:$K$100>0;$F$3:$K$100;"");""))>2;GRANDE(SE($N$3:$N$100=O$2;SE($F$3:$K$100>0;$F$3:$K$100;"");"");3);0);"")
Da confermare con Contr-Maiusc-Enter
Poi copia verso dx per avere i punteggi delle altre squadre

Il foglio Scuderie e' vuoto, quindi lo puoi compilare come preferisci :D

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula "indice" in Excel

Postdi Beppe16v » 18/04/12 16:27

Eeeehhhh... ottimo Anthony grazie mille, gentilissimo come sempre!!! Adesso provo a "pispolarci" un po' da solo, perchè non è esattamente come dicevo, ma quasi: in verità per la classifica squadre prendiamo la somma dei migliori 3 ma di ogni evento, e quindi il totale punteggio di ogni squadra è la somma del totale di ogni evento (colonne F, G, H, I, J e K). Così invece viene preso solo la somma dei migliori 3 di tutto (F3:K100). Ma penso di cavarmela da solo...

Maaaa... non c'è un modo per far riportare direttamente in automatico tutti i nomi dei gruppi nella scheda "scuderie" già posizionati in ordine di punteggio via via? E anche quelli che devo riportare a mano in orizzontale nella scheda "assoluta" non c'è il modo di riprenderli in automatico magari dall'elenco (eliminando chiaramente i doppioni)?

Ok ok basta, vi sto fracassando effettivamente troppo le bae :D scusate e GRAZIE ancora!!!
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Re: Formula "indice" in Excel

Postdi Anthony47 » 18/04/12 22:28

Esatto, come dici tu, il punteggio squadra è formato dalla somma dei migliori 3 del gruppo
Io ti ho preso alla lettera; adesso invece parli dei "somma dei migliori 3 ma di ogni evento" e non so cosa intendi per "evento".
In quanto a riportare a mano nel foglio "assoluta" l' elenco delle squadre, spero bene che vorrai inserire una "convalida da elenco" sulle celle di col N dove devi inserire la squadra di appartenenza; metti le squadre dove ti ho detto e usa quell' elenco per la convalida.

E' probabile che ti convenga avere l' elenco in verticale invece che in orizzontale (se devi riportare poi i punteggi in un altro foglio); in questo caso, partendo dalle formule e dall' organizzazione che ti ho dato ieri sera:
-inserisci una colonna vuota, spostando quindi in P2 la prima squadra
-giocando con Contr-x /Contr-v sposta l' elenco delle squadre in O3, O4, etc e i punteggi in P3, P4, etc
-poi nella formula in P3 modifica quei O$3 in O3 (4 volte; ricorda di confermare con Contr-Maiusc-Enter), quindi copia P3 nelle celle sottostanti verificando cella per cella che il risultato non cambi

In questo modo ti sara' piu' facile riportare questi punteggi in altro foglio dove puoi provvedere poi all' ordinamento decrescente.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula "indice" in Excel

Postdi Beppe16v » 19/04/12 09:10

Abbi pazienza, penso di averti incasinato anche più del dovuto ed essermi approfittato troppo della tua cortesia. Credo che lascerò perdere quest'ultima volontà di automatizzare, grazie a voi ho fatto già un gran progresso che da solo me lo sarei sognato...

Per evento intendo ogni colonna (da F a K) è un singolo evento; nella classifica squadre si prende i migliori 3 punteggi dei partecipanti sotto lo stesso gruppo per ogni evento, poi alla fine la somma di tutti questi punteggi fa il totale (la somma dei migliori 3 dell'evento F + i migliori 3 dell'evento G + ecc...).

Per riportare "a mano" intendevo si con una convalida ad elenco, ma anche questo lo consideravo come una cosa "manuale", invece anche se nella colonna N c'è l'elenco squadre (quello giustamente riportato via via manualmente), spesso con medesimi nomi (ci potranno essere, che so, una decina di "gruppo bianchi", poi 6 "gruppo rossi", ecc), mi sarebbe piaciuto che in automatico da un altra parte ci fosse il modo di elencarmi solo i nomi senza doppioni per capirsi... invece con l'elenco devo io andare a scegliere un nome, dello stesso magari ce ne sono altri uguali, e non ho la certezza di sceglierli tutti o di lasciarne qualcuno fuori. Una cosa che eliminasse i nomi uguali da un determinato elenco e che li riportasse in colonna, dove poi a fianco ci riportiamo i punteggi. Ma volevo agire meno possibile su comandi excel (anche perchè spesso lo userà chi ci capisce ancora meno di me...!), volevo che solo aggiornando la scheda "assoluta" il resto si compilasse tutto in auto.

Ripeto... basta, non ti preoccupare, lo tengo così che è già ottimo per me! Se passi a Firenze hai una cena pagata, eh eh eh! :)
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Re: Formula "indice" in Excel

Postdi Beppe16v » 19/04/12 14:54

Ok mi autorispondo per dire che il calcolo che dicevo dei migliori 3 per ogni evento l'ho corretto da solo, ma solo grazie all'aiuto di Anthony. Poi per il resto avrei risolto (ok non ridete però, sarà una roba paleolitica per voi... :-? ) con una macro collegata ad un bottone in pagina "scuderie". Ve lo descrivo brevemente per sapere se posso aver fatto bene:

- la formula che mi avevi detto tu che calcolava i migliori 3 però del blocco F3:K100 l'ho riportata in base F3:F100 (G3:G100, ... e così via ognuno per la sua colonna) nella scheda "scuderie", così che mi calcola i migliori 3 di ogni evento e alla fine con una semplice somma mi fa il totalone da solo.
- per riportare i nomi dei gruppi/scuderie quasi automatici avrei quindi fatto una macro che esegue in sequenza queste operazioni:
- copio solo i valori (testo) dei nomi di scuderie dalla colonna N della "assoluta" sulla colonna B della "scuderie";
- da dove ho incollato i nomi con la funzione "rimuovi duplicati" excel mi elimina i doppioni e mi lascia solo uno per tipo;
- le celle a fianco già calcolano i totali (come spiegato nel primo punto) rispetto al nome che leggono nella colonna B;
- ordinamento in base al totale.
E basta, il tutto solo cliccando su un bottone così anche chi non sa fare niente penso sia semplice...

Non sarà bellissimo, ma sembra pratico no? Secondo te c'è qualcosa che posso aver dimenticato o che possa creare qualche errore?

:roll:
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Re: Formula "indice" in Excel

Postdi Anthony47 » 19/04/12 22:16

Ma io dico che i nomi delle squadre che metti in col N vanno scelti da un elenco che deve pre-esistere, utilizzando il meccanismo della "Convalida da elenco", senno' prima poi scriverai Grupo1 e vorresti che Grupo1 diventi automaticamente una squadra di cui calcolare i punti?. Ecco, questo "elenco pre-esistente di squadre" mettilo in col P, usalo per la Dati /Convalida /da Elenco della colonna N

Per quanto riguarda la classifica hai anticipato il mio ragionamento; per come avevo compreso fino a ieri la classifica a squadre il foglio Scuderia non c' entrava, per come lo hai spiegato successivamente e' evidente che esso era l' embrione di calcolo della classifica.
Il procedimento quindi e' che per ogni evento si prendono i migliori 3 (oppure 2) risultati di ogni squadra, indipendentemente dal numero di concorrenti appartenenti alla squadra (se non e' cosi' allora faccio mio il tuo proponimento "lascerò perdere quest'ultima cosa").
Allora, seguendo lo schema sul foglio Scuderie, il mio suggerimento e'
-in B3 e sottostanti metti l' elenco delle squadre, come "link" al noto "elenco pre-esistente di squadre (ad esempio con in B3 la formula =Se(ASSOLUTA!O3="";""; ASSOLUTA!O3) immaginando che l' elenco lo costruisci in O3 e sottostanti di Assoluta)
-in C3 calcolerai il punteggio della squadra con la formula
Codice: Seleziona tutto
=SE(CONTA.NUMERI(SE(ASSOLUTA!$N$3:$N$100=$B3;SE(ASSOLUTA!F$3:F$100>0;ASSOLUTA!F$3:F$100;"");""))>1;SOMMA(GRANDE(SE(ASSOLUTA!$N$3:$N$100=$B3;SE(ASSOLUTA!F$3:F$100>0;ASSOLUTA!F$3:F$100;"");"");{1;2}))+SE(CONTA.NUMERI(SE(ASSOLUTA!$N$3:$N$100=$B3;SE(ASSOLUTA!F$3:F$100>0;ASSOLUTA!F$3:F$100;"");""))>2;GRANDE(SE(ASSOLUTA!$N$3:$N$100=$B3;SE(ASSOLUTA!F$3:F$100>0;ASSOLUTA!F$3:F$100;"");"");3);0);"")
Da confermare con Contr-Maiusc-Enter; l' uso dei segni $ consente la copia della formula.
-copi C3 verso dx per il punteggio degli altri eventi
-copi C3:H3 verso il basso per il punteggio delle altre squadre
In col I hai gia' la formula che calcola la somma dei punti per evento, cioe' il punteggio di squadra
Volendo scrivere una sola volta le date degli eventi, le scriverai in Assoluta, e poi le riporti in C2 e successivi di Scuderie con la formula =SE(ASSOLUTA!F2="";"";ASSOLUTA!F2) e poi formatterai come data.

La macro per ordinare le squadre secondo la classifica la puoi lanciare quando attivi il foglio Scuderie (evento WorkSheet_Activate):
-tasto dx sul tab col nome foglio Scuderie, scegli Visualizza codice
-inserisci questo codice nel frame di dx
Codice: Seleziona tutto
Private Sub Worksheet_Activate()
Call LaMacroCheOrdinaLaClassifica    '<<< Il vero nome della macro, please
End Sub

Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula "indice" in Excel

Postdi Beppe16v » 20/04/12 10:23

Mi odi lo so... :D :D :D

Anthony47 ha scritto:Ma io dico che i nomi delle squadre che metti in col N vanno scelti da un elenco che deve pre-esistere, utilizzando il meccanismo della "Convalida da elenco", senno' prima poi scriverai Grupo1 e vorresti che Grupo1 diventi automaticamente una squadra di cui calcolare i punti?

Non proprio, per il semplice fatto che durante l'anno possono partecipare concorrenti sotto nomi di scuderie che magari ancora non conosco e non posso quindi creare un elenco preventivo. Via via verranno inseriti i nomi nella relativa colonna N. Per questo ho creato quella macro che mi prende tutte le volte l'elenco, me lo copia (solo il contenuto) nell'elenco nomi del foglio "scuderie" e mi toglie i doppioni con la funzione "rimuovi duplicati", e poi ordina in base al totale.

Perfetto per la forumula da mettere nei punteggi del foglio "scuderie", ho controllato ed è esattamente come l'avevo modificata io e funziona benone, grazie cmq!

Non sarebbe male il fatto di mettere la macro direttamente in apertura del foglio e non col bottone, se non che quando ci ho provato mi si intrippa e si blocca tutto il sistema (con finestrina "debug", "fine" e poi la chiusura di excel). Non so come mai, ci guardo meglio...

GRAZIE!!! ;)
Beppe16v
Utente Junior
 
Post: 32
Iscritto il: 12/09/06 13:47

Re: Formula "indice" in Excel

Postdi Anthony47 » 20/04/12 18:40

Non vorrei ferire il tuo orgoglio ma non ti odio: ce ne sono molto piu' testoni di te in giro...

Comunque dopo averti spiegato 3 volte perche' dovresti usare una Convalida e per la terza volta tu a dire che preferisci scrivere 10 volte lo stesso nome squadra e sperare di non sbagliare mai (invece di scriverla una volta e poi sceglierla dall' elenco quante volte vuoi) non lo dico piu'.
Se la macro che hai ti serve per vedere correttamente i risultati sul foglio Scuderie allora la puoi attivare all' interno con la macro Worksheet_Activate(), come ti ho detto ieri sera; perche' se invece la lanci all' apertura del file allora eventuali nuove squadre le vedrai in classifica solo al prossimo lancio del file.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "Formula "indice" in Excel":


Chi c’è in linea

Visitano il forum: Nessuno e 7 ospiti