Utilizzo del filtro avanzato in Excel per risolvere il problema di Mort. Sebbene i filtri regolari siano diventati più potenti, ci sono ancora volte in cui il filtro avanzato può fare alcuni trucchi che altri non possono.
Guarda un video
- Il filtro avanzato è più "avanzato" del filtro normale perché:
- 1) Può copiare in un nuovo intervallo
- 2) È possibile creare criteri più complessi come Campo 1 = A o Campo 2 = A
- 3) È veloce
- Mort sta cercando di elaborare 100.000 righe in VBA eseguendo un ciclo tra i record o utilizzando un array
- Sarà sempre più veloce utilizzare le funzionalità incorporate di Excel rispetto alla scrittura del proprio codice.
- È necessario un intervallo di input, quindi un intervallo di criteri e / o un intervallo di output
- Per l'intervallo di input: singola riga di intestazioni sopra i dati
- Aggiungi una riga temporanea per le intestazioni
- Per l'intervallo di output: una riga di intestazioni per le colonne che desideri estrarre
- Per l'intervallo di criteri: intestazioni nella riga 1, valori che iniziano nella riga 2
- Complicazione: le versioni precedenti di Excel non consentivano l'intervallo di output su un altro foglio
- Se stai scrivendo una macro che potrebbe essere eseguita nel 2003, usa un intervallo denominato per aggirare l'intervallo di input
Trascrizione del video
Impara Excel da Podcast, episodio 2060: Excel Advanced Filter
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi inviata da Mort. Mort, ha 100.000 righe di dati ed è interessato alle colonne A, B e D dove la colonna C corrisponde a un determinato anno. Quindi vuole che una persona inserisca un anno e poi ottenga le colonne A, B e D. E Mort ha un po 'di VBA in cui usa gli array per fare questo e io ho detto: "Aspetta un secondo, sai, il filtro avanzato lo farebbe a molto meglio. " Va bene, e ora solo per rivedere, sono tornato indietro, ho guardato indietro nei miei video. Non ho parlato del filtro avanzato da molto tempo, quindi dovremmo parlarne.
Il filtro avanzato richiede un intervallo di input e quindi almeno uno di questi: un intervallo di criteri o un intervallo di output. Anche se oggi useremo entrambi. Bene, quindi l'intervallo di input sono i tuoi dati e devi avere intestazioni sopra i dati. Quindi, Mort non ha intestazioni e quindi inserirò temporaneamente una riga qui sopra e farò come il campo 1. Mort sa quali sono i suoi dati e quindi potrebbe inserire titoli reali lassù. E non stiamo usando nulla di chiamato: questi dati nelle colonne da E a O, quindi non devo aggiungere titoli lì, va bene? Quindi ora, da A1 a D, 100000 diventa il mio intervallo di input. E poi l'intervallo di output e l'intervallo di criteri - Bene, l'intervallo di output è solo un elenco delle intestazioni che desideri. Quindi inserirò l'intervallo di output qui e non abbiamo bisogno del campo 3, quindi "Lo toglierò di lato. Quindi ora, questo intervallo proprio qui, da A1 a C1 diventa il mio intervallo di output che dice a Excel quali campi voglio dall'intervallo di input. E potrebbero essere in un ordine diverso se si desidera riordinare le cose, ad esempio se voglio prima il campo 4 e poi il campo 1 e poi il campo 2. E ancora, queste sarebbero intestazioni reali come il numero di fattura. Non so come siano i dati di Mort.
E poi, l'intervallo di criteri è un'intestazione e il valore che desideri. Quindi diciamo che stavo cercando di ottenere qualcosa nell'anno 2014. Questo diventa l'intervallo di criteri come quello. Va bene, solo una parola di cautela qui. Sono in Excel 2016 ed è possibile fare un filtro avanzato tra due fogli in Excel 2016 ma se torni indietro e non ricordo cosa sia, forse il 2003, non ne sono sicuro. Ad un certo punto in passato, non era possibile fare un filtro avanzato da un foglio a un altro, quindi dovevi venire qui e nominare l'intervallo di input. Dovresti creare un nome qui. MyName o qualcosa del genere, va bene? E questo sarebbe il modo in cui saresti in grado di farcela, va bene. Non necessariamente in Excel 2016 ma, ancora una volta, io 'Non sono sicuro che Mort lo eseguirà nelle versioni precedenti dei dati.
Va bene, quindi di nuovo qui a Data, andiamo a Filtro avanzato, va bene. E andremo a copiare in un'altra posizione che abiliti il nostro intervallo di output lì. Va bene, quindi l'intervallo dell'elenco, dove sono i dati? Poiché sono in Excel 2016, vado a puntare ai dati, invece di usare l'intervallo di nomi, quindi questo è il mio intervallo di input. L'intervallo di criteri è quelle celle proprio lì e poi, dove andremo a - output, saranno solo queste tre celle lì. E poi facciamo clic su OK. Va bene, e BAM! Ecco quanto è veloce, veloce. E se volessimo un anno diverso? Se volessimo un anno diverso, elimineremmo i risultati, inseriremmo il 2015, quindi faremo di nuovo un filtro avanzato, Copia in un'altra posizione, fare clic su OK e ci saranno tutti i record del 2015. Fulmineo.
Va bene ora, mentre sono un fan del filtro avanzato in Excel normale, ero un grande fan del filtro avanzato in VBA, va bene, perché VBA rende il filtro avanzato davvero, davvero, davvero semplice. Va bene, quindi scriveremo del codice qui per Mort, supponendo che i dati di Mort non abbiano intestazioni e dovremo aggiungere temporaneamente le intestazioni, va bene? Quindi, passerò a VBA, Alt + F11 e lo eseguiremo dal foglio di lavoro che contiene i dati. Quindi: Dim WS As Worksheet, Set WS = ActiveSheet. Quindi, inserisci la riga 1 e aggiungi solo alcune intestazioni: A, B, Anno e D.Scopri quante righe di dati abbiamo oggi e poi partendo dalla cella A1 uscendo di 4 colonne fino alla riga finale, nominala a essere intervallo di input. Va bene, e allora questo è in realtà il codice di Mort proprio qui, dove ha chiesto InputBox,ottiene l'anno che vogliono e poi chiede quale anno o come vogliono chiamare il nuovo foglio, va bene. Quindi inserirà effettivamente un foglio al volo e quindi I-Dimension un nuovo foglio, WSN, come ActiveSheet. Quindi so che WS è il foglio originale, WSN è il nuovo foglio che è stato appena aggiunto. Sul nuovo foglio, inserisci l'intervallo di criteri in modo che sotto la colonna E ci sia l'intestazione che corrisponde a questa intestazione qui, e poi, qualunque risposta ci abbiano dato, va in E2. L'intervallo di output sarà i miei altri tre titoli: A, B e D. E ancora, se tu o Mort li cambiate in titoli reali, probabilmente è una cosa migliore da fare di A, B, D, e anche tu cambiarli in titoli reali, va bene? Quindi tutto questo è solo un po 'di pre-lavoro qui. Questa fantastica riga di codice eseguirà l'intero filtro avanzato. Così,da InputRange facciamo un AdvancedFilter, andiamo a copiare. Questo è il nostro filtro di scelta in atto o copia. Il CriteriaRange va da E1 a E2, il CopyToRange va da A a C. Valori univoci -No, vogliamo tutti i valori. Va bene, quell'unica riga di codice lì fa tutta la magia di scorrere tutti i record o sostituire in loop tutti i record o eseguire gli array. E poi abbiamo finito, cancelleremo l'intervallo di criteri e quindi elimineremo la riga 1 sul foglio di lavoro originale.E poi abbiamo finito, cancelleremo l'intervallo di criteri e quindi cancelleremo nuovamente la riga 1 sul foglio di lavoro originale.E poi abbiamo finito, cancelleremo l'intervallo di criteri e quindi cancelleremo nuovamente la riga 1 sul foglio di lavoro originale.
Va bene, quindi torniamo qui ai nostri dati. Lo renderemo facile da eseguire, quindi: Inserisci, una forma e chiamalo Filtro, Home, Centro, Centro, Più grande, Più grande, Più grande, fai clic con il pulsante destro del mouse, Assegna macro e assegnalo a MacroForMort. Va bene, quindi eccoci qui. Faremo un test. Vedi che siamo sulla scheda tecnica, fai clic su Filtro, che anno vogliamo? Vogliamo il 2015. Come voglio chiamarlo? Voglio chiamarlo 2015, va bene. E BAM! Ecco fatto. Ecco quanto è veloce, ecco quanto è veloce.
Ora, poiché i dati originali di Mort non avevano intestazioni, forse questi dati non dovrebbero avere intestazioni. Quindi andiamo Alt + F11, proprio qui vogliamo cancellare l'intervallo dei criteri. Faremo anche Righe (1) .Delete. Va bene, quindi ora la prossima volta che ci occuperemo di questo, elimineremo quei titoli. E diciamo solo - Piuttosto che eseguire l'intera cosa rapidamente, diamo un'occhiata qui con il 2014. Quindi selezionerò una cella su Dati, Alt + F11, e voglio andare fino al punto in cui facciamo il filtro avanzato. Quindi possiamo guardare e vedere cosa sta facendo l'intera macro qui. Quindi faremo clic su Esegui e voglio ottenere il 2014. 2014, va bene. E quindi, premi F8, stiamo per fare il filtro avanzato. Possiamo tornare a Excel qui e vedere cosa è successo.
La prima cosa che è successa - Ora, la prima cosa che è successa è che abbiamo aggiunto una nuova riga temporanea con i titoli. Inserito questo foglio di lavoro, costruito un intervallo di criteri con un'intestazione e in quale anno inseriscono, scelto i campi che vogliamo fare e poi di nuovo in VBA, eseguirò la prossima riga di codici, è F8 che fa il filtro avanzato proprio lì . È incredibilmente veloce e vedrai che questo ci ha portato tutti i record. Da lì, è solo un po 'di pulizia, elimina questo, elimina questo. Tornerò ai dati e cancellerò la riga 1 e saremo a posto. Quindi lascerò correre il resto di quel punto, rimuoverò quel punto di interruzione, va bene? Quindi c'è il VBA. Per me, penso che questo sia il modo più veloce, più veloce da percorrere.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Va bene, ecco qua. Voglio ringraziare Mort per avermi inviato questa domanda. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2060.xlsm
