Difficoltà con filtro celle

[Calabar]

Non sono riuscito a riassumere efficacemente il problema nel titolo, per cui l’ho mantenuto generico, spero vada bene.

Ho una tabella di dati, di due colonne, del tipo:
[Nome] [Valore]
I nomi sono una dozzina (ma il loro numero potrebbe variare nel tempo, anche perché ho intenzione di creare un file che si possa utilizzare in futuro con dati simili), di solito ogni nome viene ripetuto 5-10 volte. I valori sono variabili riga per riga. In tutto posso prevedere un centinaio di righe in totale (anche questo numerò può variare, ma l’ordine di grandezza è questo).
Per capirci meglio, i dati iniziali sono di questo tipo:

[Nome]   [Valore]
Tre      10
Sei      18
Due      11
Sei      16
Uno      21
Tre      12
Tre      11
Uno      22
Otto     15
Uno      26
Sei      20
Due      11

Ciò che vorrei fare e ottenere, a partire dalla tabella iniziale (non ordinata), una nuova tabella che contenga nella colonna [Nomi] i nomi senza ripetizioni, nella colonna [Valori] la media dei valori di tutte le voce che hanno quel determinato nome e in una nuova colonna [Conta] il numero di righe accorpate (ossia, per ogni nome, il numero di ricorrenze di quel nome)…
Vorrei anche se possibile che, aggiungendo nuove voci in coda alla tabella iniziale, queste vengano immediatamente calcolate nella nuova tabella.

[Nome]   [Valore]  [Conta]
Due      11        2
Otto     15        1
Sei      18        3
Tre      11        3
Uno      23        3

Quel che sono riuscito a fare finora, aggiungendo diverse colonne e lavorando direttamente sui dati iniziali, è stato calcolare la somma dei valori, il numero di ricorrenze e da li la media per ogni nome distinto utilizzando delle formule. Ho dovuto però ordnare i dati prima di poterlo fare.
Una volta applicato il filtro standard con rimozione dei duplicati però viene mantenuta la riga “media” che contiene il valore della prima ricorrenza di quel nome e non l’ultima che racchiude la media di tutti i valori.

Sto pensando a questo punto di aver completamente sbagliato approccio, per cui chiedo a voi se c’è un modo per ottenere il risultato desiderato, possibilmente mantenendo la struttura del documento semplice e senza l’uso di macro.

Grazie!

[PKG]

Come spesso accade, il modo più semplice per farlo è una tabella pivot (Dati → Tabella pivot).

Pivot.ods (15,1 KB)

[gioh661]

Buondì. Volendo fare con le formule potresti fare così
In D2 per gli univoci metti
=SE.ERRORE(INDICE($A$2:$A$500;CONFRONTA(0;INDICE(CONTA.SE($D$1:D1;$A$2:$A$500););0));"")&""
e tiri in basso per quanto ti può servire.
In E2 per la media
=SE(D2="";"";MEDIA.SE($A$2:$A$500;D2;$B$2:$B$500))
e in F2 per il conteggio
=SE(D2="";"";CONTA.SE($A$2:$A$500;D2))
ti allego esempio
filtra celle.ods (15.5 KB)

[Calabar]

Grazie mille ad entrambi.

Per quanto riguarda la Tabella Pivot ho tentato seguendo la guida, ma non avendole mai usate non sono riuscito ad ottenere risultati. Ora con un esempio specifico spero ci capire meglio.

La soluzione con le formule sembra ottima, ora me la studio bene per capire quali sono i passaggi che mi sono mancati per arrivarci da solo.

Avrei anche un’altra domanda sui filtri: una volta che applico un filtro, dove trovo un elenco dei filtri applicati e come posso modificarne uno senza doverlo rifare da capo?
Finora ho usato la freccia “undo” per annullarlo e ho rifatto da capo in tutti i tentativi.

[Calabar]

Volevo segnare entrambe come soluzioni al problema, ma ho visto che è possibile farlo solo con una, quindi è toccato a caso alla prima che ho cliccato.

La tabella Pivot sembra più facile da fare (scrivo sembra perché non ho ancora capito come si ottengono i campi “Media - Valore” e “Contanumero - Valore”), ma se aggiungo dati devo aggiornare a mano la tabella.
EDIT: visto come ottenere il risultato, in effetti è facile. Ora devo vederla meglio per capirla appieno e poterla usare anche in altre situazioni.

La versione con le formule è più complessa da realizzare (problema secondario una volta realizzata, naturalmente) ma si aggiorna da sola.
Vorrei riuscire ad ottenere il risultato in ordine alfabetico per nome, ma mi sa che mi sto perdendo nella complessità della formula (sembrava facile, ma se ordino normalmente considera la formula e non il risultato, quindi non ordina nulla
EDIT: no, non è quello il problema, bisogna proprio lavorare sulla formula).

[gioh661]

Se li vuoi ordinati alfabeticamente cambia la prima formula con questa
=SE.ERRORE(INDICE(A$2:A$500;CONFRONTA(0;INDICE(CONTA.SE(A$2:A$500;"<"&A$2:A$500)-MATR.SOMMA.PRODOTTO(CONTA.SE($D$1:D1;A$2:A$500)););0));"")

[Calabar]

Grazie ancora.

Vedo che la formula si allunga (e complica) molto, dubito che sarei riuscito a farlo da solo. Non nel tempo che posso dedicargli, per lo meno.
Ora devo solo capire come funziona smantellando un po’ la formula.

[robyros]

prima di fare la pivot seleziona le colonne A e B e dagli un nome, poi fai la pivot e come range seleziona il nome che hai dato alle colonne (non usare il range A:B), così vedrai che quando inserisci altri valori la pivot si aggiornerà senza doverla rifare, ovviamente degli darli il comando Aggiorna

[gioh661]

Se vuoi qui trovi un video che spiega la prima formula

- YouTube

È per excel ma contenuti così per calc non li trovi.

[gianvi60]

Non potresti usare la funzione UNICI() e SOMMA.PIU’.SE() ?
esempio.ods (11.8 KB)

[gioh661]

Ciao. Il problema della funzione UNICI è che non si aggiorna se aggiungi nuovi record.
Probabilmente è un bug che verrà risolto presto…spero.
Ps.: a @robyros serve fare la media, per cui deve usare MEDIA.SE o MEDIA.PIÙ.SE

[Calabar]

Sembra interessante, per la sua semplicità più che altro, ma vedo che c’è da lavorarci un po’. Grazie.

Aggiungere la media è stato facile ma non riesco ad aggiungere nuove voci. Evidentemente mi sfugge qualcosa su come è costruita la tabella, credevo che copiare una nuova riga con la formula avrebbe risolto, ma pare di no, non si può proprio fare, mi dice che non posso modificare solo una parte della matrice, evidentemente non è stata creata in quel modo.
EDIT: ok, ho scoperto le formule di matrice, ora è più chiaro.