Excel Filter è una delle funzionalità più utilizzate quando si lavora con i dati. In questo post del blog, ti mostrerò come creare una casella di ricerca con filtro dinamico di Excel, in modo che filtri i dati in base a ciò che digiti nella casella di ricerca.
Qualcosa come mostrato di seguito:
C'è una doppia funzionalità per questo: puoi selezionare il nome di un paese dall'elenco a discesa, oppure puoi inserire manualmente i dati nella casella di ricerca e ti mostrerà tutti i record corrispondenti. Ad esempio, quando digiti "I" ti dà tutti i nomi dei paesi con l'alfabeto I in esso.
Guarda il video - Creazione di una casella di ricerca con filtro Excel dinamico
Creazione di una casella di ricerca del filtro Excel dinamico
Questo filtro Excel dinamico può essere creato in 3 passaggi:
- Ottenere un elenco univoco di elementi (paesi in questo caso). Questo verrebbe utilizzato nella creazione del menu a discesa.
- Creazione della casella di ricerca. Qui ho usato una casella combinata (controllo ActiveX).
- Impostazione dei dati. Qui userei tre colonne di supporto con formule per estrarre i dati corrispondenti.
Ecco come appaiono i dati grezzi:
SUGGERIMENTO UTILE: È quasi sempre una buona idea convertire i dati in una tabella Excel. Puoi farlo selezionando qualsiasi cella nel set di dati e utilizzando la scorciatoia da tastiera Ctrl + T.
Passaggio 1 - Ottenere un elenco unico di articoli
- Seleziona tutti i Paesi e incollalo in un nuovo foglio di lavoro.
- Seleziona l'elenco dei paesi -> Vai a Dati -> Rimuovi duplicati.
- Nella finestra di dialogo Rimuovi duplicati, seleziona la colonna in cui hai l'elenco e fai clic su Ok. Questo rimuoverà i duplicati e ti darà un elenco univoco come mostrato di seguito:
- Un ulteriore passaggio consiste nel creare un intervallo denominato per questo elenco univoco. Per fare questo:
- Vai alla scheda Formula -> Definisci nome
- Nella finestra di dialogo Definisci nome:
- Nome: CountryList
- Ambito: cartella di lavoro
- Si riferisce a: =UniqueList!$A$2:$A$9 (Ho l'elenco in una scheda separata denominata UniqueList in A2:A9. Puoi fare riferimento a dove risiede il tuo elenco univoco)
NOTA: se si utilizza il metodo "Rimuovi duplicati" e si espandono i dati per aggiungere più record e nuovi paesi, sarà necessario ripetere nuovamente questo passaggio. In alternativa, puoi anche creare una formula per rendere dinamico questo processo.
Passaggio 2: creazione della casella di ricerca del filtro dinamico di Excel
Perché questa tecnica funzioni, dovremmo creare una "Casella di ricerca" e collegarla a una cella.
Possiamo utilizzare la casella combinata in Excel per creare questo filtro della casella di ricerca. In questo modo, ogni volta che inserisci qualcosa nella casella combinata, si rifletterebbe anche in una cella in tempo reale (come mostrato di seguito).
Ecco i passaggi per farlo:
- Vai alla scheda Sviluppatore -> Controlli -> Inserisci -> Controlli ActiveX -> Casella combinata (Controlli ActiveX).
- Se non hai la scheda Sviluppatore visibile, ecco i passaggi per abilitarla.
- Se non hai la scheda Sviluppatore visibile, ecco i passaggi per abilitarla.
- Fare clic in un punto qualsiasi del foglio di lavoro. Inserirà la casella combinata.
- Fare clic con il pulsante destro del mouse su Casella combinata e selezionare Proprietà.
- Nella finestra Proprietà, apportare le seguenti modifiche:
- Cella collegata: K2 (puoi scegliere qualsiasi cella in cui vuoi che mostri i valori di input. Useremo questa cella per impostare i dati).
- ListFillRange: CountryList (questo è l'intervallo denominato che abbiamo creato nel passaggio 1. Questo mostrerebbe tutti i paesi nel menu a discesa).
- MatchEntry: 2-fmMatchEntryNone (questo assicura che una parola non venga completata automaticamente durante la digitazione)
- Con la casella combinata selezionata, vai alla scheda Sviluppatore -> Controlli -> fai clic su Modalità progettazione (questo ti porta fuori dalla modalità di progettazione e ora puoi digitare qualsiasi cosa nella casella combinata. Ora, qualunque cosa digiti si rifletterebbe nella cella K2 in tempo reale)
Passaggio 3 - Impostazione dei dati
Infine, colleghiamo tutto per colonne di supporto. Uso tre colonne di supporto qui per filtrare i dati.
Colonna di supporto 1: Immettere il numero di serie per tutti i record (20 in questo caso). Puoi usare la formula ROWS() per farlo.
Colonna di supporto 2: Nella colonna 2 dell'helper, controlliamo se il testo inserito nella casella di ricerca corrisponde al testo nelle celle nella colonna del paese.
Questa operazione può essere eseguita utilizzando una combinazione di funzioni SE, VAL.NUMERO e RICERCA.
Ecco la formula:
=SE(VAL.NUMERO(RICERCA($K$2,D4)),E4,"")
Questa formula cercherà il contenuto nella casella di ricerca (che è collegata alla cella K2) nella cella che ha il nome del paese.
Se c'è una corrispondenza, questa formula restituisce il numero di riga, altrimenti restituisce uno spazio vuoto. Ad esempio, se la casella combinata ha il valore "USA", tutti i record con il paese "USA" avranno il numero di riga e il resto sarà vuoto ("")
Colonna di supporto 3: Nella colonna helper 3, dobbiamo ottenere tutti i numeri di riga dalla colonna helper 2 impilati insieme. Per fare ciò, possiamo usare una combinazione di formule SE ERRORE e PICCOLO. Ecco la formula:
=SEERRORE(PICCOLO($F$4:$F$23, MI4);"")
Questa formula impila insieme tutti i numeri di riga corrispondenti. Ad esempio, se la casella combinata ha il valore US, tutti i numeri di riga con "US" vengono impilati insieme.
Ora, quando abbiamo i numeri di riga impilati insieme, dobbiamo solo estrarre i dati in questi numeri di riga. Questo può essere fatto facilmente utilizzando la formula dell'indice (inserisci questa formula nel punto in cui desideri estrarre i dati. Copialo nella cella in alto a sinistra in cui desideri estrarre i dati, quindi trascinalo verso il basso e verso destra).
=IFERROR(INDICE($B$4:$D$23,$G4, COLONNE($I$3:I3)),"")
Questa formula ha 2 parti:
INDICE - Questo estrae i dati in base al numero di riga.
SE ERRORE - Questo restituisce vuoto quando non ci sono dati.
Ecco un'istantanea di ciò che finalmente ottieni:
La casella combinata è un menu a discesa e una casella di ricerca. Puoi nascondere i dati originali e le colonne di supporto per mostrare solo i record filtrati. Puoi anche avere i dati grezzi e le colonne di supporto in un altro foglio e creare questo filtro excel dinamico in un altro foglio di lavoro.
Diventa creativo! Prova alcune varianti
Puoi provare a personalizzarlo in base alle tue esigenze. Potresti voler creare più filtri Excel invece di uno. Ad esempio, potresti voler filtrare i record in cui il rappresentante di vendita è Mike e il paese è il Giappone. Questo può essere fatto esattamente seguendo gli stessi passaggi con alcune modifiche nella formula nelle colonne di supporto.
Un'altra variante potrebbe essere quella di filtrare i dati che iniziano con i caratteri che inserisci nella casella combinata. Ad esempio, quando inserisci "I", potresti voler estrarre i paesi che iniziano con I (rispetto al costrutto attuale in cui ti darebbe anche Singapore e Filippine poiché contiene l'alfabeto I).
Come sempre, la maggior parte dei miei articoli si ispira alle domande/risposte dei miei lettori. Mi piacerebbe ricevere il tuo feedback e imparare da te. Lascia i tuoi pensieri nella sezione commenti.
Nota: se utilizzi Office 365, puoi utilizzare la funzione FILTRO per filtrare rapidamente i dati durante la digitazione. È più semplice del metodo mostrato in questo tutorial.
