vba filtro "diverso da" con criteri presi da colonna tabella

Question

Ciao,

su un foglio "partecipanti" ho una tabella di 2 colonne:

Partecipante	valore
enrica	658
anna	517
maria	311
Luisa	468
Maria	421
Elisa	427
Sonia	404
Roberta	489
Enrica	996
Gilda	960
Alessandra	894
Maria	843
Elisa	288
Sonia	862
Antonia	477
Luigia	605
Michele	910
Barbara	677
Maria	868
Elisa	607

Ho anche su un secondo foglio nello stesso file: "team"

con una tabella con i nomi delle squadre in header e i membri delle squadre nelle rispettive colonne:

SquadraRossa	SquadraBlu	SquadraVerde
Antonia	Maria	Gilda
Luigia	Elisa	Alessandra
Michele	Sonia
Barbara	Roberta
Enrica

DOMANDA: con una macro vorrei:

1. poter scegliere con un menù a tendina il nome della squadra
2. filtrare la tabella sul foglio "partecipanti"per i valori in colonna "partecipante" DIVERSI da quelli della squadra scelta (es: se ho scelto la SquadraVerde, filtrare per "diverso da" Gilda e Alessandra)
3. Mi piacerebbe (come extra) anche capire come si fa per filtrare per i valori UGUALI a quelli della squadra scelta

Grazie. Carlo

Answer 1

...

@Andrea: non conosco il filtro avanzato, grave pecca per uno che usa excel da tempo. Mi hai offerto l'occasione giusta per studiarmelo e imparare uno strumento (per me) nuovo e potente. La coglierò senz'altro e proverò ovviamente a implementarlo con un po' di codice.

Non avevo soluzioni e ora ho ben due strade da percorrere! Grazie ancora ad entrambi.

...

Ciao,

ne esiste anche una terza, ancora più facile e con tre diversi utilizzi.

Crea i seguenti due nomi definiti, (non indispensabili, ma utili per semplificare la lettura e l'eventuale manipolazione della formula):

squadre=SCARTO(Foglio2!$A$1;;;;CONTA.VALORI(Foglio2!$A$1:$Z$1))

formazioni=SCARTO(Foglio2!$A$2;;; MATR.SOMMA.PRODOTTO(MAX((Foglio2!$A$2:$Z100<>"")*(RIF.RIGA(Foglio2!$A$2:$Z100))))-1;CONTA.VALORI(squadre))

eventualmente adattando gli intervalli alle tue necessità.

Se in A2 si trova il nome del giocatore, la seguente formula  matriciale (da confermarsi con Ctrl+Shift+Enter), trova la squadra di appartenenza:

=INDICE(squadre;PICCOLO(SE(formazioni=A2;RIF.COLONNA(formazioni));1))

Variante 1.

Utilizza la formula in una colonna ed applica a questa il filtro automatico che già conosci ed utilizzi.

Variante 2.

Utilizza la formula per evidenziare direttamente i giocatori non appartenenti alla squadra, qualcosa del tipo:

=SE($A$1=INDICE(squadre;PICCOLO(SE(formazioni=A2;RIF.COLONNA(formazioni));1));"";"Non appartenente")

Variante 3.

Utilizza la formula quale criterio di formattazione condizionale per evidenziare i giocatori appartenenti/non appartenenti alla squadra.

Andrea.

Answer 2

...

DOMANDA: come posso fare la stessa cosa ma con una macro che da sola vada "a vedere" i partecipanti da escludere dal filtro (basandosi sulla tabella "squadre")?

Utilizzando il filtro avanzato! Questa è la strada da percorre, non so come dirtelo altrimenti.

Questo strumento svolge egregiamente il compito per il quale è stato progettato, inutile scrivere codice quando è possibile utilizzare gli strumenti già a disposizione.

Anche il filtro avanzato, come tutto del resto, può essere utilizzato in vba con una decina di righe fai  tutto (vedi registratore di macro).

Andrea.

Answer 3

@AndyCap64

Ciao Andrea, grazie per la tua risposta. me la voglio studiare perchè potrebbe tornarmi utile in futuro :)

Purtroppo in questo caso mi serve codice vba perchè dovrò inserirlo in una macro più complessa che ho già.

@Mauro Gamberini:

Grazie Mauro. Molto interessante. La userò di sicuro in futuro :)

In questo caso, mi sono spiegato proprio male perchè cerco di fare una cosa diversa.

La tabella delle squadre, quella del foglio "team" mi serve come riferimento. Diciamo che la imposto, magari nel tempo poi le faccio qualche sporadica modifica (cambio un partecipante di una squadra, magari aggiungo una nuova squadra e nuovi partecipanti...), ma non è la tabella su cui devo lavorare tutti i giorni.

Quella su cui lavoro tutti i giorni è la tabella dei partecipanti nel foglio "partecipanti".

Questa è in realtà composta da centinaia di righe e devo spesso filtrare per gruppi di partecipanti (le squadre appunto), fare delle modifiche alle righe, rifiltrare per un'altro team, fare nuove modifiche... etc.

Per questo motivo, usare il filtro e ogni volta deselezionare i membri della squadra che voglio tenere mi fa perdere un sacco di tempo. Anche perchè devo andare a rivedermi sempre quali sono i membri della squadra per cui devo filtrare per non dimenticarmi qualcuno. Ho una macro che, una volta filtrata la squadra, mi fa delle operazioni in automatico.

ORA: una delle situazioni in cui mi posso trovare è che devo eliminare dalla tabella "partecipanti" le righe dei NON componenti della squadra che mi interessa. Per questo, devo filtrare la colonna "Partecipante" escludendo i nomi dei componenti la squadra che voglio tenere.

Es:

Sono in tabella Partecipanti.

Voglio tenere solo i partecipanti della SquadraRossa.

Per farlo, devo togliere la spunta dai membri della SquadraRossa.

Infatti, se lo faccio con il registratore di macro, ottengo:

Sheets("partecipanti").Select

    ActiveSheet.Range("$A$1:$B$21").AutoFilter Field:=1, Criteria1:=Array( _

        "Alessandra", "anna", "Elisa", "Gilda", "Luisa", "maria", "Roberta", "Sonia"), _

        Operator:=xlFilterValues

    Rows("3:21").Select

    Selection.Delete Shift:=xlUp

    ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=1

E il risultato finale è:

Partecipanti	valore
enrica	658
Enrica	996
Antonia	477
Luigia	605
Michele	910
Barbara	677

Ora salvo il file (con un nome nuovo così non perdo i dati della tabella completa) e ho finito.

DOMANDA: come posso fare la stessa cosa ma con una macro che da sola vada "a vedere" i partecipanti da escludere dal filtro (basandosi sulla tabella "squadre")?

Answer 4

...

DOMANDA: con una macro vorrei:

1. poter scegliere con un menù a tendina il nome della squadra
2. filtrare la tabella sul foglio "partecipanti"per i valori in colonna "partecipante" DIVERSI da quelli della squadra scelta (es: se ho scelto la SquadraVerde, filtrare per "diverso da" Gilda e Alessandra)
3. Mi piacerebbe (come extra) anche capire come si fa per filtrare per i valori UGUALI a quelli della squadra scelta

Grazie. Carlo

Ciao Carlo,

si può fare tranquillamente ed agevolmente senza macro, utilizzando il filtro avanzato. che, se non lo hai mai utilizzato, ti consiglio di leggere prima di continuare così ti sarà più chiara l'esposizione.

Una semplice cella con Convalida Dati per scegliere il nome della squadra, in base al valore selezionato, con una formula INDICE(), SCARTO() o simili sarà possibile individuare e riportare i componenti della squadra selezionata, con ognuno si costruirà il criterio per filtrare la tabella. I nomi devono trovarsi nella medesima riga uno di fianco all'altro. Per filtrare i valori diversi il filtro dovrà essere impostato così:

="<>"&RiferimentoCellaNomePartecipante.

Facile no?

Andrea.

Answer 5

Ciao,

su un foglio "partecipanti" ho una tabella di 2 colonne:

<cut>

DOMANDA: con una macro vorrei:

1. poter scegliere con un menù a tendina il nome della squadra
2. filtrare la tabella sul foglio "partecipanti"per i valori in colonna "partecipante" DIVERSI da quelli della squadra scelta (es: se ho scelto la SquadraVerde, filtrare per "diverso da" Gilda e Alessandra)
3. Mi piacerebbe (come extra) anche capire come si fa per filtrare per i valori UGUALI a quelli della squadra scelta

Grazie. Carlo

Se ho capito(dubito) vedi qui:

• http://www.maurogsc.eu/excel/xlspiùconvalide.aspx