Formule che si aggiornano automaticamente in base ai filtri di una tabella

[marmus...@gmail.com]

Passo subito all'esempio

ho una tabella con le colonne Anno, Età, e un'altra in cui ci sono dei numeri quantitativi.

In una casella ho la media di tutti questi numeri

Vorrei che questa media si aggiornasse nel caso filtrassi la colonna Anno con "2017" (o qualsiasi altra data)

Le righe spariscono ma la media non cambia perchè la formula tipo MEDIA(C2:C58) sente ancora le righe che sono nascoste.

Si può fare in modo che le righe nascoste non siano calcolate nelle formule di una tabella?

[casanmaner]

Usa la funzione subtotale riportando il numero indice della media.

[marmus...@gmail.com]

Il giorno sabato 17 febbraio 2018 15:15:50 UTC+1, casanmaner ha scritto:
> Usa la funzione subtotale riportando il numero indice della media.

ciao sto pronvando e con le formule facilitipo appunto la media, ho capito come fare.
Ma se la media viene fuoi da una funzione MEDIA.PIU.SE?

[casanmaner]

Purtroppo le funzioni SUBTOTALE sono quelle indicate nell'elenco e, per quel che mi risulta, non è possibile inserire "condizioni" all'interno della funzione.
Per gestire eventuali condizioni non mi viene in mente niente di meno complicato di qualcosa del genere

=MATR.SOMMA.PRODOTTO(--(A2:A1001>10)*--(A2:A1001<50)*SUBTOTALE(9;SCARTO(A2:A1001;RIF.RIGA(A2:A1001)-MIN(RIF.RIGA(A2:A1001));;1)))/MATR.SOMMA.PRODOTTO(--(A2:A1001>10)*--(A2:A1001<50)*SUBTOTALE(2;SCARTO(A2:A1001;RIF.RIGA(A2:A1001)-MIN(RIF.RIGA(A2:A1001));;1)))

dove ipotizzando che in A2:A1001 sono presenti dei valori (anche celle vuote) e dove al numeratore viene fatta la somma delle sole celle che contengono valori maggiori di 10 e che contengono valori minori di 50.
Allo stesso modo al denominatore vengono contate le celle con valori sempre a condizione che il valore sia maggiore di 10 e minore di 50.
In questo modo si ottiene la media.

Ma magari qualche guru delle funzioni native di excel ha una soluzione molto più semplice :)

[xxp...@gmail.com]

Ciao Ermanno, quando si effettua un test con più criteri, vedi questo estratto dalla formula:
....(--(A2:A1001>10)*--(A2:A1001<50)*SUBTOTALE(...
non è necessario usare gli operatori unari --, in quanto il prodotto dei criteri restituisce di per sé un risultato numerico 0/1.
Per cui si può scrivere:
.....((A2:A1001>10)*(A2:A1001<50)*SUBTOTALE(....

ciao paoloard

[casanmaner]

Vero :)
Tu che sei un mago vedresti soluzioni alternative alla formula "complessa"?

[buonoc...@gmail.com]

Vorrei un chiarimento dal postante sul significato dei criteri multipli. Intendi valori multipli di criterio nell'ambito di uno steso campo o di campi diversi? Nel primo caso, se per es si vuole la media non ponderata per 2 anni inseriti nelle celle E1:E2 e con i dati in A1:C10 (A1:C1 intestazioni colonne) la formula matriciale:

=MEDIA(MEDIA.PIÙ.SE($C$2:$C$10;$A$2:$A$10;$E$1:$E$2)) restituisce la media del campo numerico per i 2 anni inseriti nelle celle E1:E2
Se invece si vuole una soluzione sensibile alle righe filtrate, ipotizzando un filtro aperto sul campo anno con selezione di più anni la formula seguente inserita dove si vuole restituisce la media delle sole righe filtrate:

=AGGREGA(1;1;C2:C10)
Bisogna però considerare che la funzione AGGREGA ha delle limitazione nel non considerare le righe nascoste e così è importante stabilire sul tipo e criteri dei filtri

Elio

[marmus...@gmail.com]

Diciamo che devo trovare quanti "SI" ci sono in una colonna. Ovviamente il numero di questi "SI" può variare in base ai filtri che applico in altre colonne

Supponiamo che la colonna sia la G e le righe vanno da 4 a 50 (oppure si può usare la tabella computer[PROCESSORE] per esempio)

[xxp...@gmail.com]

Il giorno domenica 18 febbraio 2018 11:44:07 UTC+1, casanmaner ha scritto:
.....

> > Ciao Ermanno, quando si effettua un test con più criteri, vedi questo estratto dalla formula:
> > ....(--(A2:A1001>10)*--(A2:A1001<50)*SUBTOTALE(...
> > non è necessario usare gli operatori unari --, in quanto il prodotto dei criteri restituisce di per sé un risultato numerico 0/1.
> > Per cui si può scrivere:
> > .....((A2:A1001>10)*(A2:A1001<50)*SUBTOTALE(....
> >
> > ciao paoloard
>
> Vero :)
> Tu che sei un mago vedresti soluzioni alternative alla formula "complessa"?

No. Se ti riferisci al calcolo della media secondo criteri matematici, come hai fatto tu, cioè somma del valori diviso il loro numero, ho provato in vari modi di utilizzare la funzione SUBTOTALE nativa con indice 1 (subtotale della media) ma non funziona. Restituisce errore #DIV!0 e non mi spiego il perché.
La funzione MATR.SOMMA.PRODOTTO con SUBTOTALE annidato può essere validamente sostituita dalla nuova funzione AGGREGA (come ha ricordato Elio).
In questo caso però, in cui occorrerebbe applicare ulteriori criteri di filtraggio all'interno della formula, mi risulterebbe difficile applicarla.
ciao paoloard.

[buonoc...@gmail.com]

Il giorno domenica 18 febbraio 2018 15:37:57 UTC+1, marmus...@gmail.com ha scritto:
> Diciamo che devo trovare quanti "SI" ci sono in una colonna. Ovviamente il numero di questi "SI" può variare in base ai filtri che applico in altre colonne
>
> Supponiamo che la colonna sia la G e le righe vanno da 4 a 50 (oppure si può usare la tabella computer[PROCESSORE] per esempio)

Ma un conto è dire che ti serve il conteggio delle righe che presentano SI alle solo righe filtrate per un campo x e un altro è che invece ti serve la media sul corrispondente campo y. Inoltre deve essere chiarito se la formula deve farsi anche carico del controllo che le righe filtrate presentino tutte il valore "SI" al campo x e non ce ne sia invece qualcuna che presenti un valore diverso.
Comunque con la funzione AGGREGA puoi fare tutto (con una sola colonna di appoggio nel caso serva il check dei valori SI), Se chiarisci quanto ti serve (il tuo ultimo intervento sembra contraddire la richiesta iniziale) ti faccio un esempio.
Elio

[xxp...@gmail.com]

Il giorno domenica 18 febbraio 2018 17:42:30 UTC+1, xxp...@gmail.com ha scritto:
.... La funzione MATR.SOMMA.PRODOTTO con SUBTOTALE annidato può essere validamente sostituita dalla nuova funzione AGGREGA (come ha ricordato Elio).

> In questo caso però, in cui occorrerebbe applicare ulteriori criteri di filtraggio all'interno della formula, mi risulterebbe difficile applicarla.
> ciao paoloard.

Vi è poi da dire che ha poco senso, oltre che filtrare con gli strumenti di Excel, filtrare all'interno della formula. Infatti Excel offre tante opzioni di filtraggio da escluderne ulteriori.

ciao paoloard

[marmus...@gmail.com]

Non ho capito cosa hai detto,ma mi pare di aver spiegato chiaramente cosa mi serve
All'inizio ho parlato solo di media perchè pensavo poi poter adattare la stessa formula al resto. Non potevo sapere che la soluzione che mi avreste dato sarebbe andata bene solo per alcune cose perchè solo quelle sono preimpostate?
Certo che si sono valori diversi. c'è il SI il NO, il ? quando non si sa, e le celle vuote.
Non cotraddice nulla. Esistono varie colonne in una tabella,mica solo una

[buonoc...@gmail.com]

Assumendo i dati in C1:C100 con A1:C1 intestazioni di colonne e con colonna B dati numerici sul quale calcolare la media e in colonna C i dati SI, NO, '?', ecc

step 1: creazione colonna calcolata in D2 con formula =SE(C2="SI";1;0)da trascinare fino a fine dati

step 2: formula dove vuoi:
=SE.ERRORE(AGGREGA(1;1;$B$2:$B$100)/AGGREGA(5;1;$D$2:$D$100);"Valore non computabile per presenza di NO nelle righe filtrate")

La formula restituisce la media delle righe filtrate manualmente indipendentemente dai criteri scelti se nelle righe filtrate alla colonna C le righe visibili presentano tutte il valore 'SI' e restituisce un messaggio personalizzato di errore sollevato da una divisione per 0 se le righe visibili presentano anche solo un valore diverso da SI ( anche cella vuota).

Ciao

Elio

[casanmaner]

Ciao Elio,
non ho a disposizione la funzione AGGREGA (Excel2007) ma come si comporta
AGGREGA(5;1;$D$2:$D$100)
se l'intervallo non è filtrato in presenza di "NO, ?, vuote"?
Prendendo il minimo, che dovrebbe essere 0, non darebbe sempre errore?

[buonoc...@gmail.com]

L'errore (appositamente ricercato nella mia formula) viene gestito dalla funzione SE.ERRORE come in
=SE.ERRORE(9/0;"errore di divisione per zero!"
Elio

[casanmaner]

Ah, ecco.
Anche con AGGREGA avresti l'errore.
Il limite allora è che non si ottiene una media se l'intervallo non è filtrato.
Allora, volendo per chi non ha la funzione AGGREGA, credo che lo stesso risultato si otterrebbe con SUBTOTALE.

[buonoc...@gmail.com]

mancava ')' di chiusura.
Aggiungo che nella colonna di appoggio l'utente può anche decidere di introdurre ulteriori criteri con le funzioni O oppure E, basta che il risultato sia 0 in caso negativo ( cioè falso).

Elio

[buonoc...@gmail.com]

In Excel 2007 risolverei con la 'Riga di Totale' della scheda PROGETTAZIONE che appare quando trasformi un elenco in Tabella ( TAB INSERISCI >> Tabella). Ti fai la colonna calcolata che restituisce VERO e FALSO; filtri per VERO e osservi i subtotali al piede delle altre colonne.

[buonoc...@gmail.com]

In effetti SUBTOTALE anche al di fuori di una riga Totale di Tabella aggrega righe filtrate, anche se io preferisco usare sempre le tabelle perchè presentano il vantaggio per la loro sintassi che le formule che puntano alla tabelle tengono conto dinamicamente dell'incremento riduzione delle righe ( le formule fanno riferimento al nome della colonna come da intestazione e non ad un intervallo statico). Andrebbe fatta una valutazione delle differenze tra AGGREGA e SUBTOTALE

[buonoc...@gmail.com]

Il giorno domenica 18 febbraio 2018 21:04:37 UTC+1, buonoc...@gmail.com ha scritto:
> In effetti SUBTOTALE anche al di fuori di una riga Totale di Tabella aggrega righe filtrate, anche se io preferisco usare sempre le tabelle perchè presentano il vantaggio per la loro sintassi che le formule che puntano alla tabelle tengono conto dinamicamente dell'incremento riduzione delle righe ( le formule fanno riferimento al nome della colonna come da intestazione e non ad un intervallo statico). Andrebbe fatta una valutazione delle differenze tra AGGREGA e SUBTOTALE

Il vantaggio principale di AGGREGA è quello che con il secondo argomento della funzione l'utente ha un controllo più flessibile oltre a quello delle righe nascoste, potendo impostare l'argomento anche con l'opzione di ignorare i valori di errore ( guarda la guida in linea di microsoft)

[casanmaner]

Sì, avevo visto.
Per questo ti chiedevo del comportamento che, considerato che avevi impostato l'opzione 1, mi sembrava identica a SUBTOTALE.

Comunque personalmente, a prescindere o meno dall'utilizzo di una tabella, che ha l'indibbuo vantaggio della dinamicità ai suoi riferimenti, utilizzerei i filtri per avere i "SUBTOTALI".
Ad es. anche filtrando le voci SI della colonna "C" si otterrebbe la "submedia" delle voci per cui si ha il filtro con i soli SI, avendo però il "vantaggio" di avere la media di tutti i valori se le voci non sono filtrate.
Se poi non si volessero fare più filtri diversificati su più colonne si potrebbe pensare anche alla colonna di appoggio che restituisca vero/falso inserendo una qualche formula.
In modo che con il solo filtro per VERO si otterebbe il subtotale delle sole voci che rispettano tutte le condizioni.

Però senza sapere quali sono i dati e quali le varie condizioni diventa difficile trovare una soluzione completa ... almeno per me che già non sono un fenomeno :)

[marmus...@gmail.com]

Il giorno domenica 18 febbraio 2018 19:41:45 UTC+1, buonoc...@gmail.com ha scritto:

questa formula non la capisco,la media non c'entra piu,perchè l'hai messa dentro???

metto un file. come lo allego?

[buonoc...@gmail.com]

Ma quale funzione di aggregazione vuoi sulle righe filtrate? Per il file hai Dropbox?

[marmus...@gmail.com]

Io non ho mai parlato di aggregazione,l'avete detto voi pensando fosse una soluzione. Io non so,non conosco la formula

https://www.dropbox.com/s/jq56c024rrnupsl/Cartel1.xlsx?dl=0

[buonoc...@gmail.com]

Non è una formula. Si parla di aggregazione quando si aggrega in un sol valore i dati derivanti da una colonna di dati (detta anche matrice). La somma, la media, il conteggio tra le principali sono tutte funzioni di aggregazione.
Ho scaricato il file. Secondo me fai confusione tra conteggi in base a criteri già presenti nei dati rispetto a criteri dinamici in base all'applicazione di filtri. Per chiarire:

=CONTA.SE(Tabella1[SOSTITUITO];"SI")/MATR.SOMMA.PRODOTTO(CONTA.PIÙ.SE(Tabella1[SOSTITUITO];{"SI"\"NO"}))

da la tua prima percentuale
mentre

=(MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1;(Tabella1[SOSTITUITO]="SI")*1))/MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1; ((Tabella1[SOSTITUITO]="SI")*1)+((Tabella1[SOSTITUITO]="NO")*1))

da la tua seconda percentuale

i risultati sono indipendenti dal fatto che filtri i dati ed è ovvio che ti servono 2 celle per rappresentare il risultato di queste 2 formule.

Se viceversa vuoi che una singola cella rappresenti uno dei 2 risultati in base all'applicazione di un filtro (cosa che mi pare più parente di uno sfizio che di una esigenza reale ) al momento sono riuscito ad elaborare una formula che si limita a sentire che un filtro si stato applicato all'intervallo dei dati e a dare uno dei 2 risultati:

=SE((RIGHE(Tabella1[DOPATO])/SUBTOTALE(3;Tabella1[NOME]))=1;G1;(MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1;(Tabella1[SOSTITUITO]="SI")*1))/MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1; ((Tabella1[SOSTITUITO]="SI")*1)+((Tabella1[SOSTITUITO]="NO")*1)))

è ovvio che però se applichi un filtro che è incoerente con l'esigenza del calcolo avrai comunque il secondo valore.

Ciao
Elio

[buonoc...@gmail.com]

> =SE((RIGHE(Tabella1[DOPATO])/SUBTOTALE(3;Tabella1[NOME]))=1;G1;(MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1;(Tabella1[SOSTITUITO]="SI")*1))/MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1; ((Tabella1[SOSTITUITO]="SI")*1)+((Tabella1[SOSTITUITO]="NO")*1)))
>
> è ovvio che però se applichi un filtro che è incoerente con l'esigenza del calcolo avrai comunque il secondo valore.
>
> Ciao
> Elio

Dimenticavo di sostituire nell'ultima formula il riferimento alla cella G1 con il valore restituito dalla prima formula:

=SE((RIGHE(Tabella1[DOPATO])/SUBTOTALE(3;Tabella1[NOME]))=1;CONTA.SE(Tabella1[SOSTITUITO];"SI")/MATR.SOMMA.PRODOTTO(CONTA.PIÙ.SE(Tabella1[SOSTITUITO];{"SI"\"NO"}));(MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1;(Tabella1[SOSTITUITO]="SI")*1))/MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1; ((Tabella1[SOSTITUITO]="SI")*1)+((Tabella1[SOSTITUITO]="NO")*1)))

[marmus...@gmail.com]

Il giorno lunedì 19 febbraio 2018 00:20:43 UTC+1, buonoc...@gmail.com ha scritto:

> Se viceversa vuoi che una singola cella rappresenti uno dei 2 risultati in base all'applicazione di un filtro (cosa che mi pare più parente di uno sfizio che di una esigenza reale ) al momento sono riuscito ad elaborare una formula che si limita a sentire che un filtro si stato applicato all'intervallo dei dati e a dare uno dei 2 risultati:
>
> =SE((RIGHE(Tabella1[DOPATO])/SUBTOTALE(3;Tabella1[NOME]))=1;G1;(MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1;(Tabella1[SOSTITUITO]="SI")*1))/MATR.SOMMA.PRODOTTO((Tabella1[DOPATO]="SI")*1; ((Tabella1[SOSTITUITO]="SI")*1)+((Tabella1[SOSTITUITO]="NO")*1)))
>
> è ovvio che però se applichi un filtro che è incoerente con l'esigenza del calcolo avrai comunque il secondo valore.
>
> Ciao
> Elio

è ovvio che intendevo avere tutto in una cella,altrimenti ce la faccio da solo
è il titolo del topic,aggiornareil risultato in base ai filtri
non ho capito perchè mi hai rimessso le formule singole. già le avevo fatte io,senza la formula MATRice che non conosco.

L'ultima sembra funzionare ma non ho capito cosa intendi con

"è ovvio che però se applichi un filtro che è incoerente con l'esigenza del calcolo avrai comunque il secondo valore."

quale sarebbe un filtro incoerente?

[buonoc...@gmail.com]

Qualsiasi applicazione di filtro che non comporti la visualizzazione della righe necessarie per il tuo calcolo come mostrato alla fine delle tue istruzioni nel file scaricato tanto per capirci quelle che mostrano che
'solo 2 dopati su 6 sono stati sostituiti'.

[marmus...@gmail.com]

si poi avevo capito. in pratica quella formula funziona solo se mettiamo SI in una colonna,ma io dovrei filtrare in base a 5-6 colonne. Cioè il possibili filtri non arrivano solo da una colonna ma da 5-6 appunto e quella formula serve solo per una....
Adesso stavo giusto provando a fare le formule in base ad ogni colonna che potrebbe filtrare la tabella. Solo che poi non saprei come metterle insieme.

Intanto mi sono già bloccato perchè non so come far apparire l'anno di una data
ci sono 100 righe. 10 per ogni anno dal 2000.
Se nessun fiultro è applicato vedo la scritta "tutti",se invece applico un filtro (ad esempio "2007"),quella casella dovrebbe mostrare 2007

Possibile?

[buonoc...@gmail.com]

I limiti che Excel pone alle formule sensibili ai filtri (SUBTOTALE e AGGREGA e non ne conosco altre) sono in larga parte legati al numero di funzioni di aggregazione che gestiscono e dal fatto che non è possibile passare matrice dipendenti da calcolo. Leggi la guida in linea per SUBTOTALE e AGGREGA se trovi oscuro quello che dico. Tornando alla tua ultima domanda, per situazioni semplici la risposta può essere SI. Per es se hai una colonna ( per es A2:A100) di numeri indicanti anni e filtri per un singolo anno in una qualsiasi cella ti può essere restituito l'anno filtrato con la formula:
=AGGREGA(4;1;$E$2:$E$15) che significa applica all'intervallo E2:E15 la funzione di aggregazione MAX (primo argomento) ignorando le righe nascoste (secondo argomento); è evidente che se il filtro applicato è basato su un singolo valore ( per es anno 2007) tutte le righe visibili saranno fatte da 2007 e la funzioni MAX (massimo valore) coincide con uno qualsiasi dei valori delle righe visibili. In altri casi queste 2 funzioni ( SUBTOTALE e AGGREGA) non risolvono.

[buonoc...@gmail.com]

Il giorno martedì 20 febbraio 2018 00:38:06 UTC+1, buonoc...@gmail.com ha scritto:
> I limiti che Excel pone alle formule sensibili ai filtri (SUBTOTALE e AGGREGA e non ne conosco altre) sono in larga parte legati al numero di funzioni di aggregazione che gestiscono e dal fatto che non è possibile passare matrice dipendenti da calcolo. Leggi la guida in linea per SUBTOTALE e AGGREGA se trovi oscuro quello che dico. Tornando alla tua ultima domanda, per situazioni semplici la risposta può essere SI. Per es se hai una colonna ( per es A2:A100) di numeri indicanti anni e filtri per un singolo anno in una qualsiasi cella ti può essere restituito l'anno filtrato con la formula:
> =AGGREGA(4;1;$E$2:$E$15) che significa applica all'intervallo E2:E15 la funzione di aggregazione MAX (primo argomento) ignorando le righe nascoste (secondo argomento); è evidente che se il filtro applicato è basato su un singolo valore ( per es anno 2007) tutte le righe visibili saranno fatte da 2007 e la funzioni MAX (massimo valore) coincide con uno qualsiasi dei valori delle righe visibili. In altri casi queste 2 funzioni ( SUBTOTALE e AGGREGA) non risolvono.

Aggiungo che in altri casi è meglio farsi una tabella Pivot che offre maggior controllo / flessibilità.

Sulla formula precedente si può costruire un check multiplo:
1 ) è applicato un filtro
2) il filtro lascia un singolo valore
3) o viceversa lascia valori multipli

per anni in A2:A15

la formula diventa:
=SE(CONTA.NUMERI($A$2:$A$15)/AGGREGA(2;1;$A$2:$A$15)= 1;"TUTTI";SE(AGGREGA(4;1;$A$2:$A$15)=AGGREGA(5;1;$A$2:$A$15);"FILTRATO PER ANNO: " & AGGREGA(4;1;$A$2:$A$15); "FILTRO MULTIPLO"))

si assume che tutte le celle contengano un anno (non celle vuote o con testo o con errori)

[Norman Jones]

Ciao Elio,

On 20/02/2018 00:08, buonoc...@gmail.com wrote:
> Aggiungo che in altri casi è meglio farsi una tabella Pivot che offre maggior controllo / flessibilità.

+1



===
Regards,
Norman

[marmus...@gmail.com]

non so cosa sia la tabella pivot,ma diciamo che non mi piace avere roba in giro di appoggio. voglio solo quello che si vede.

Comunque mi sa che ho trovato una soluzione stravagante al quesito iniziale,per avere

in pratica mi sto facendo tutte le formule una per una per ogni filtro che voglio applicare.
Invece di trovare una formula che le metta tutte insieme così da funzionare in ogni occasioni,farò poi delle macro abbinate a dei pulsanti (come era nel foglio di esempio) per far andare solo quel filtro.
Sarà una palla registrarle tutte però dovrebbe funzionare