Filtro avanzato e un po' di magia

Per la stragrande maggioranza degli utenti di Excel, quando gli viene in mente la parola “filtro dati”, solo il solito classico filtro da tab Dati – Filtro (Dati — Filtro):

Un tale filtro è una cosa familiare, senza dubbio, e nella maggior parte dei casi lo farà. Tuttavia, ci sono situazioni in cui è necessario filtrare in base a un numero elevato di condizioni complesse in più colonne contemporaneamente. Il solito filtro qui non è molto conveniente e voglio qualcosa di più potente. Uno strumento del genere potrebbe essere filtro avanzato, soprattutto con un po' di “finitura con una lima” (secondo la tradizione).

Base

Per iniziare, inserisci alcune righe vuote sopra la tabella dei dati e copia lì l'intestazione della tabella: questo sarà un intervallo con condizioni (evidenziate in giallo per chiarezza):

Deve esserci almeno una riga vuota tra le celle gialle e la tabella originale.

È nelle celle gialle che devi inserire i criteri (condizioni), in base ai quali verrà quindi eseguito il filtraggio. Ad esempio, se devi selezionare le banane nell'"Auchan" di Mosca nel III trimestre, le condizioni saranno le seguenti:

Per filtrare, seleziona qualsiasi cella nell'intervallo con i dati di origine, apri la scheda Dati E clicca il Inoltre (Dati — Avanzato). Nella finestra che si apre, dovrebbe essere già inserito automaticamente un intervallo con i dati e dovremo solo specificare l'intervallo di condizioni, ovvero A1:I2:

Si noti che l'intervallo di condizioni non può essere assegnato "con un margine", ovvero non è possibile selezionare righe gialle extra vuote, perché una cella vuota nell'intervallo di condizioni è percepita da Excel come assenza di un criterio e un vuoto intero line come richiesta di visualizzare tutti i dati indiscriminatamente.

Interruttore Copia il risultato in un'altra posizione ti permetterà di filtrare l'elenco non proprio lì su questo foglio (come con un filtro normale), ma di scaricare le righe selezionate in un altro intervallo, che poi dovrà essere specificato nel campo Metti il ​​risultato nell'intervallo. In questo caso, non utilizziamo questa funzione, ce ne andiamo Elenco filtri in atto e fare clic su OK. Le righe selezionate verranno visualizzate sul foglio:

Aggiunta di una macro

"Beh, dov'è la comodità qui?" chiedi e avrai ragione. Non solo devi inserire le condizioni nelle celle gialle con le mani, ma anche aprire una finestra di dialogo, inserire gli intervalli lì, premere OK. Triste, sono d'accordo! Ma “tutto cambia quando arrivano ©” – le macro!

L'utilizzo di un filtro avanzato può essere notevolmente accelerato e semplificato utilizzando una semplice macro che eseguirà automaticamente il filtro avanzato quando vengono immesse le condizioni, ovvero modificando qualsiasi cella gialla. Fare clic con il tasto destro sulla scheda del foglio corrente e selezionare il comando Testo sorgente (Codice sorgente). Nella finestra che si apre, copia e incolla il seguente codice:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) non è niente Allora in caso di errore Riprendi successivo ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Questa procedura verrà eseguita automaticamente quando viene modificata una cella del foglio di lavoro corrente. Se l'indirizzo della cella modificata rientra nell'intervallo giallo (A2:I5), questa macro rimuove tutti i filtri (se presenti) e riapplica il filtro esteso alla tabella dei dati di origine che inizia con A7, ovvero tutto verrà filtrato istantaneamente, immediatamente dopo aver inserito la condizione successiva:

Quindi va tutto molto meglio, giusto? 🙂

Implementazione di query complesse

Ora che tutto viene filtrato al volo, possiamo approfondire un po' le sfumature e smontare i meccanismi delle query più complesse nel filtro avanzato. Oltre a inserire corrispondenze esatte, puoi utilizzare vari caratteri jolly (* e ?) e segni di disuguaglianza matematica in una serie di condizioni per implementare una ricerca approssimativa. Il caso del personaggio non ha importanza. Per chiarezza ho riassunto tutte le possibili opzioni in una tabella:

Punti sottili:

• Il segno * indica un numero qualsiasi di caratteri e ? – qualsiasi carattere.
• La logica nell'elaborazione di testo e query numeriche è leggermente diversa. Quindi, ad esempio, una cella di condizione con il numero 5 non significa cercare tutti i numeri che iniziano con cinque, ma una cella di condizione con la lettera B è uguale a B*, ovvero cercherà qualsiasi testo che inizi con la lettera B.
• Se la query di testo non inizia con il segno =, puoi inserire mentalmente * alla fine.
• Dates must be entered in the US format month-day-year and through a fraction (even if you have Excel and regional settings).

Connettivi logici AND-OR

Le condizioni scritte in celle diverse, ma nella stessa riga, sono considerate interconnesse da un operatore logico И (E):

Quelli. filtra le banane per me nel terzo trimestre, precisamente a Mosca e contemporaneamente da Auchan.

Se è necessario collegare le condizioni con un operatore logico OR (O), quindi devono solo essere inseriti in righe diverse. Ad esempio, se abbiamo bisogno di trovare tutti gli ordini del manager Volina per le pesche di Mosca e tutti gli ordini per le cipolle nel terzo trimestre a Samara, allora questo può essere specificato in una serie di condizioni come segue:

Se devi imporre due o più condizioni su una colonna, puoi semplicemente duplicare l'intestazione della colonna nell'intervallo dei criteri e inserire la seconda, la terza, ecc. sotto di essa. termini. Quindi, ad esempio, puoi selezionare tutte le transazioni da marzo a maggio:

In generale, dopo aver "finito con un file", un filtro avanzato risulta essere uno strumento abbastanza decente, in alcuni punti non peggiore di un classico filtro automatico.

• Superfiltro sulle macro
• Cosa sono le macro, dove e come inserire codice macro in Visual Basic
• Tabelle intelligenti in Microsoft Excel