Microsoft Excel è uno strumento per fogli di calcolo onnipresente e conveniente.
L'ampia funzionalità rende questo programma il secondo più popolare dopo MS Word tra tutti i programmi per ufficio.
È utilizzato da economisti, contabili, scienziati, studenti e rappresentanti di altre professioni che hanno bisogno di elaborare dati matematici.
Una delle funzioni più comode di questo programma è il filtraggio dei dati. Diamo un'occhiata a come impostare e utilizzare i filtri MS Excel.
Dove nei filtri di Excel: i loro tipi
È facile trovare i filtri in questo programma: devi aprire il menu principale o semplicemente tenere premuti i tasti Ctrl + Maiusc + L.
Funzioni di filtraggio di base in Excel:
- selezione per colore: consente di ordinare i dati per carattere o colore di riempimento,
- filtri di testo in excel: consentono di impostare determinate condizioni per le stringhe, ad esempio: minore di, maggiore di, uguale, non uguale e altri, nonché impostare condizioni logiche - e, o,
- filtri numerici: ordina per condizioni numeriche, ad esempio, sotto la media, primi 10 e altri,
- manuale: la selezione può essere eseguita secondo i criteri che scegli tu stesso.
È facile usarli. È necessario selezionare la tabella e selezionare la sezione con i filtri nel menu, quindi specificare con quale criterio verranno schermati i dati.
Come utilizzare il filtro avanzato in Excel: come personalizzarlo
Il filtro standard presenta uno svantaggio significativo: per ricordare quali criteri di selezione sono stati utilizzati, è necessario aprire il menu. E tanto più provoca inconvenienti quando viene impostato più di un criterio. Da questo punto di vista risulta più conveniente il filtro avanzato, che viene visualizzato in una tabella separata sopra i dati.
ISTRUZIONI VIDEO
Procedura di impostazione:
- Crea una tabella con i dati per lavorare ulteriormente con essa. Non deve contenere righe vuote.
- Crea una tabella con le condizioni di selezione.
- Esegui filtro avanzato.
Diamo un'occhiata a un esempio di personalizzazione.
Abbiamo una tabella con le colonne Prodotto, Quantità e Prezzo.
Ad esempio, è necessario ordinare le righe i cui nomi di prodotto iniziano con la parola "Chiodi". Diverse righe rientrano in questa condizione.
La tabella con le condizioni si troverà nelle celle A1: A2. È importante indicare il nome della colonna dove avverrà la selezione (cella A1) e la parola stessa per la selezione - Chiodi (cella A2).
È più conveniente posizionarlo sopra i dati o sul lato. Inoltre, non è vietato, ma non è sempre conveniente, poiché periodicamente potrebbe essere necessario aggiungere ulteriori righe. Rientra almeno una riga vuota tra le due tabelle.
Allora devi:
- seleziona una delle celle,
- aprire "Filtro avanzato" lungo il percorso: Dati - Ordina e filtra - Avanzato,
- controlla cosa è specificato nel campo "Intervallo iniziale" - l'intera tabella con le informazioni dovrebbe andare lì,
- nel "Range of condition" è necessario impostare i valori delle celle con la condizione di selezione, in questo esempio è l'intervallo A1: A2.
Dopo aver fatto clic sul pulsante "OK", verranno selezionate le informazioni richieste e nella tabella appariranno solo le righe con la parola desiderata, nel nostro caso si tratta di "Chiodi". I restanti numeri di riga diventeranno blu. Per annullare un filtro specificato, basta premere CTRL + MAIUSC + L.
È anche facile impostare il filtro sulle righe che contengono esattamente la parola "Nails", senza distinzione tra maiuscole e minuscole. Nell'intervallo B1: B2, posizioneremo una colonna con il nuovo criterio di selezione, non dimenticando di indicare l'intestazione della colonna in cui verrà eseguito il filtraggio. Nella cella B2, è necessario specificare la seguente formula = "= Nails".
- seleziona una delle celle della tabella,
- apri "Filtro avanzato",
- verificare che l'intera tabella con i dati sia inclusa nell'"Intervallo originale",
- nella "Gamma di condizioni" indicare B1: B2.
Dopo aver fatto clic su "OK", i dati verranno eliminati.
Questi sono gli esempi più semplici. lavorare con i filtri in excel... Nella versione avanzata è conveniente impostare altre condizioni per la selezione, ad esempio dropout con il parametro "OR", dropout con il parametro "Nails" e il valore nella colonna "Quantità" > 40.
Le informazioni in una tabella possono essere filtrate per una o più colonne. Facciamo un esempio di una tabella con le colonne "Città", "Mese" e "Vendite".
Esempio 1
Se è necessario filtrare i dati in base a una colonna con i nomi delle città in ordine alfabetico, è necessario selezionare una delle celle in questa colonna, aprire Ordina e filtra e selezionare il parametro LO. Di conseguenza, le informazioni verranno visualizzate tenendo conto della prima lettera del nome della città.
Per ottenere informazioni sul principio opposto, è necessario utilizzare il parametro "YA".
Esempio 2
È necessario escludere le informazioni per mese e una città con un volume di vendite elevato dovrebbe essere più in alto nella tabella rispetto a una città con un volume di vendite inferiore. Per risolvere il problema, è necessario selezionare il parametro Ordinamento in Ordina e filtra. Nella finestra che appare con le impostazioni, specificare "Ordina per" - "Mese".
Successivamente, è necessario aggiungere un secondo livello di ordinamento. Per fare ciò, selezionare in "Ordinamento" - "Aggiungi livello" e specificare la colonna "Vendite". Nella colonna "Ordine" delle impostazioni, specificare "Discendente". Dopo aver cliccato su "OK", la selezione dei dati avverrà secondo i parametri specificati.
ISTRUZIONI VIDEO
Perché i filtri potrebbero non funzionare in Excel?
Gli utenti hanno spesso difficoltà a lavorare con uno strumento come i filtri. Di solito sono associati alla violazione delle regole per l'utilizzo di determinate impostazioni.
Il problema con il filtraggio per data è uno dei più comuni. Si verifica dopo aver scaricato i dati dal sistema di contabilità sotto forma di array. Quando si tenta di filtrare le righe in base a una colonna che contiene le date, il filtro non si verifica per data, ma per testo.
Soluzione:
- seleziona la colonna con le date,
- aprire la scheda Excel nel menu principale,
- selezionare il pulsante "Celle", nell'elenco a discesa selezionare il parametro "Converti testo in data".
Gli errori comuni degli utenti quando si lavora con questo programma dovrebbero includere anche:
- l'assenza di intestazioni per le colonne (senza di esse il filtraggio, l'ordinamento e una serie di altri parametri importanti non funzioneranno),
- la presenza di righe e colonne vuote nella tabella con i dati (questo confonde il sistema di ordinamento, Excel percepisce le informazioni come due tabelle diverse indipendenti l'una dall'altra),
- posizionare più tabelle su una pagina (è più conveniente posizionare ciascuna tabella su un foglio separato),
- inserendo dati dello stesso tipo in più colonne,
- posizionare i dati su più fogli, ad esempio per mesi o anni (la quantità di lavoro può essere immediatamente moltiplicata per il numero di fogli con le informazioni).
E un altro errore critico che non consente di utilizzare appieno le funzionalità di Excel è l'uso di un prodotto senza licenza.
È impossibile garantire il suo corretto funzionamento, inoltre, gli errori appariranno costantemente. Se intendi utilizzare questo strumento per elaborare informazioni matematiche su base continuativa, acquista la versione completa del programma.
Molti utenti di PC hanno familiarità con una suite di prodotti per lavorare con vari tipi di documenti chiamata Microsoft Office. Tra i programmi di questa azienda c'è MS Excel. Questa utility è progettata per funzionare con i fogli di calcolo.
Il programma contiene il cosiddetto filtro avanzato in Excel. È progettato per la facilità d'uso e la creazione di tabelle. Ne parleremo nel nostro articolo. Di seguito spiegheremo come realizzarlo e come utilizzarlo.
Qual è questa funzione? Descrizione
Cosa significa filtro avanzato in Excel? Si tratta di una funzione che permette di differenziare i dati selezionati (per colonne in Excel) in relazione ai requisiti inseriti.
Ad esempio, se abbiamo un foglio di calcolo con le informazioni su tutti gli studenti della scuola (altezza, peso, classe, sesso, ecc.), allora possiamo facilmente distinguere tra loro, diciamo, tutti i ragazzi con un'altezza di 160 su 8 classe. Questo può essere fatto utilizzando la funzione "Filtro avanzato" in Excel. Ne parleremo in dettaglio di seguito.
Cosa significa filtro automatico?
Quali sono le caratteristiche del filtro normale e avanzato? Innanzitutto, parliamo del primo. Il filtro automatico (normale) è progettato per differenziare i dati secondo criteri predefiniti, il cui numero è determinato. Viene utilizzato quando si dispone di un piccolo foglio di calcolo, per colonne e parametri dati. Se dobbiamo impostare un numero illimitato di criteri di filtraggio, dobbiamo selezionare avanzato.
Come farlo bene?
Come creare un filtro avanzato in Excel? Per chiarire come si svolge e come si svolge la procedura, si consideri un esempio.
Istruzioni per il filtraggio avanzato dei fogli di calcolo:
- Devi creare spazio sopra la tabella principale. I risultati del filtro si troveranno lì. Dovrebbe esserci spazio sufficiente per il tavolo finito. È necessaria anche un'altra riga. Dividerà la tabella filtrata da quella principale.
- Copia l'intera intestazione (nomi di colonna) della tabella principale nella primissima riga dello spazio liberato.
- Immettere i dati richiesti per il filtraggio nella colonna richiesta. Nota che la voce dovrebbe essere simile a questa: = "= valore filtrato".
- Ora devi andare alla sezione "Dati". Nell'area di filtraggio (icona a forma di imbuto) seleziona "Avanzate" (situato alla fine dell'elenco a destra del segno corrispondente).
- Successivamente, nella finestra pop-up, è necessario inserire i parametri del filtro avanzato in Excel. L'"Intervallo di condizioni" e l'"Intervallo di origine" vengono compilati automaticamente se è stata selezionata la cella all'inizio del foglio di lavoro. In caso contrario, dovrai inserirli tu stesso.
- Fare clic su Ok. Uscirai dalle impostazioni dei parametri di filtraggio avanzati.
Dopo i passaggi eseguiti, nella tabella principale rimarranno solo i record per il valore di delimitazione specificato. Per annullare l'ultima azione (filtro), è necessario fare clic sul pulsante "Cancella", che si trova nella sezione "Dati".
Lavorare con il filtro avanzato in Excel
Come utilizzare il filtro avanzato in Excel? Per capire, torneremo su questa funzione. Supponiamo di avere una colonna con le città nel nostro foglio di calcolo. In precedenza, abbiamo già applicato un filtro esteso per la città di Rostov. Ora, nello stesso documento, vogliamo aggiungere a quelli ricevuti per la città di Rostov quei record che corrispondono alla città di Samara.
Questo richiede:
- Inserisci le condizioni di demarcazione (= "- Samara") sotto la query precedente (= "= Rostov").
- Chiama il menu del filtro avanzato (la sezione "Dati", la scheda "Filtro e ordinamento", seleziona "Avanzato" al suo interno).
- Fare clic su OK. Questo chiuderà il filtro avanzato in Excel. Sullo schermo apparirà una tabella già pronta, composta da record in cui è indicata la città di Samara o Rostov.
Quando si lavora, è necessario tenere conto del seguente punto molto importante. Tutti i criteri immessi devono essere trovati solo nelle colonne filtrate. Sopra il tavolo principale.
Una volta appreso il metodo di filtraggio avanzato di base, puoi iniziare a esplorare tutte le funzionalità di questa funzione. Questo si riferisce all'uso di formule.
Filtraggio avanzato. Regole di base per l'utilizzo di Excel durante il lavoro
Termini di utilizzo:
- I criteri di selezione sono i risultati della formula originale.
- Il risultato può essere solo due valori: "TRUE" o "FALSE".
- I riferimenti assoluti indicano l'intervallo originale della tabella filtrata.
- I risultati della formula mostreranno solo le righe che ricevono un totale di TRUE. I valori delle righe che sono state ricevute a seguito della formula "FALSE" non verranno visualizzati.
Utilizzando queste regole, puoi creare qualsiasi formula con operandi e valori validi.
Esempio in Excel 2010
Diamo un'occhiata a un esempio di filtro avanzato in Excel 2010 e all'uso di formule al suo interno. Ad esempio, limitiamo i valori di una colonna con dati numerici dal risultato del valore medio (più o meno).
Istruzioni per lavorare con il filtro avanzato in Excel in base al valore medio di una colonna:
- Innanzitutto, devi scegliere una formula per la media dei dati della colonna. Per fare ciò, è necessario selezionare l'intervallo di risultati dal record iniziale a quello finale. Sommali e dividi per il numero di righe (numero di record).
- Successivamente, seleziona la voce "Avanzate" nella sezione "Filtro e ordinamento". Immettere lì i dati richiesti (per intervalli).
- Quindi fare clic su OK. Il risultato sarà una tabella che contiene record che non superano la media per una determinata colonna.
Pertanto, puoi utilizzare il filtro avanzato in molti modi diversi. Questo può essere fatto elaborando vari tipi di formule.
La cosa principale è non dimenticare di scrivere i criteri di filtro sopra la tabella principale nella colonna in base alla quale verrà effettuata la delimitazione.
Attenzione! Durante l'esecuzione, i record che non hanno superato il criterio di filtro non vengono eliminati. Semplicemente non si mostrano (non si mostrano). Il filtro avanzato è progettato in modo da poter copiare il risultato e incollarlo da qualche altra parte. Successivamente, è necessario fare clic su "Cancella" nella sezione "Filtro e ordinamento". Quindi la tua tabella originale tornerà sullo schermo.
Filtro automatico. Esempio di utilizzo
Un filtro automatico è uno strumento comune. Può essere applicato solo specificando parametri precisi. Ad esempio, visualizza tutti i valori della tabella che superano 1000 (< 1000), или показать точные данные, как было рассмотрено в примере с городами.
È usato molto più spesso di quello avanzato. Tuttavia, le opzioni di filtro offerte da Autofilter sono spesso insufficienti.
Pro e contro del filtro avanzato in Excel
Poiché il filtraggio avanzato è un'aggiunta al filtraggio normale, ci sono vantaggi e svantaggi nell'usarlo. Ora li considereremo.
Pro del filtraggio avanzato:
- puoi usare le formule
Contro del filtraggio avanzato:
- per eseguire questa operazione è necessario dedicare più tempo al procedimento con l'introduzione dell'intervallo e dei dati iniziali;
- per utilizzare filtri avanzati tramite formule, è necessario conoscere ed essere in grado di utilizzare le regole della logica e la formulazione di equazioni in MS Excel.
Nonostante la presenza di inconvenienti, questa funzione ha ancora più possibilità del semplice filtro automatico. Sebbene con quest'ultimo, non è necessario inserire qualcosa di proprio, tranne il criterio in base al quale verrà effettuata l'identificazione dei valori.
Filtraggio in base a due criteri separati. Come farlo bene?
Il filtro avanzato ti consente di utilizzare formule diverse nel tuo foglio di calcolo. Se devi differenziarlo prima per un criterio, poi per un altro (separatamente l'uno per l'altro), devi:
- Crea un luogo per inserire il parametro del filtro. È più conveniente lasciare questo spazio sopra la tabella principale e ricordarsi di copiare l'intestazione (nomi delle colonne) per non confondersi in quale colonna inserire questo criterio.
- Immettere l'indicatore desiderato per il filtraggio. Ad esempio, tutti i record i cui valori di colonna sono maggiori di 1000 (> 1000).
- Vai alla scheda "Dati". Nella sezione "Filtro e ordinamento", seleziona la voce "Avanzate".
- Nella finestra che si apre, specificare gli intervalli dei valori considerati e la cella con il valore del criterio in esame.
- Fare clic su Ok. Successivamente, verrà visualizzata la tabella filtrata in base al criterio specificato.
- Copia il risultato dell'accantonamento. Incolla la tabella filtrata a lato da qualche parte sullo stesso foglio Excel. Puoi usare un'altra pagina.
- Seleziona "Cancella". Questo pulsante si trova nella scheda "Dati" nella sezione "Filtro e ordinamento". Dopo aver fatto clic, la tabella filtrata tornerà alla sua forma originale. E puoi lavorare con lei.
- Successivamente, è necessario riallocare lo spazio libero per la tabella, che verrà filtrata.
- Quindi è necessario copiare l'intestazione (nomi delle colonne) del campo principale e spostarli sulla prima riga dello spazio lasciato libero per la struttura filtrata.
- Vai alla scheda "Dati". Nella sezione "Filtro e ordinamento", seleziona "Avanzate".
- Nella finestra che si apre, seleziona l'intervallo di record (colonne) in base al quale verrà effettuato il filtraggio.
- Aggiungere l'indirizzo della cella in cui è scritto il criterio di differenziazione, ad esempio "città di Odessa".
- Fare clic su Ok. Successivamente, verrà eseguito il filtraggio in base al valore "Odessa".
- Copia la tabella filtrata e incollala su un altro foglio del documento o sulla stessa pagina, ma lontano da quella principale.
- Fare nuovamente clic su "Cancella". Tutto è pronto. Ora hai tre tavoli. Quello principale, filtrato per un valore (> 1000), così come quello filtrato per un altro valore (Odessa).
Una piccola conclusione
Nonostante a prima vista questa funzione possa sembrare complicata, in realtà è abbastanza semplice. Tutto quello che devi sapere è la gamma di condizioni di filtro avanzate in Excel. Tuttavia, non bisogna dimenticare quanto segue. Non confondere i filtri regolari con quelli estesi. Si consiglia inoltre di apprendere le regole della logica e delle formule in Excel, quindi lavorare con il filtro diventa semplice.
Il filtraggio dei dati di Excel include due filtri: Filtro automatico e Filtro avanzato. Supponiamo di avere un set di dati di grandi dimensioni, ma dall'intero array è necessario visualizzare o selezionare i dati relativi a una data specifica, una persona specifica, ecc. Esistono filtri per questo. Per coloro che si imbattono in questo strumento per la prima volta, il filtro non elimina, ma nasconde i record che non soddisfano le condizioni di filtro specificate per loro.
Il primo è un filtro automatico, progettato per le operazioni più semplici: evidenziare i record con un valore specifico (ad esempio, evidenziare solo i record relativi a LeBron James), i dati che si trovano in un determinato intervallo (o sopra la media o i primi dieci) o celle / caratteri di un certo colore (molto conveniente tra l'altro). Di conseguenza, è molto facile da usare. Devi solo selezionare i dati che vuoi vedere filtrati. Quindi il comando "Dati" / "Filtro". Apparirà una casella di riepilogo su ciascuna cella in alto della tabella in alto, lì è già facile capire ogni comando, è semplice da imparare e spiegare, spero, non c'è bisogno di ulteriori, solo le sfumature dell'uso del filtro automatico:
1) Funziona solo con campo non interrotto. Non sarà più possibile filtrare due elenchi diversi su un foglio.
2) La riga più in alto della tabella viene assegnata automaticamente come intestazione e non partecipa al filtro.
3) Puoi applicare qualsiasi filtro in colonne diverse, ma tieni presente che, a seconda dell'ordine in cui vengono applicati i filtri, alcune condizioni potrebbero non essere applicate. i filtri precedenti hanno già nascosto le voci richieste. Non ci sono problemi qui, queste voci sarebbero comunque nascoste, ma se si desidera utilizzare più set di filtri, è meglio iniziare con le condizioni che hanno la minore applicazione.
Applicazione pratica nel lavoro: per esempio, lavori su questo elenco per trovare un errore o controllare i dati. Dopo aver applicato il filtro automatico, puoi scorrere l'intera tabella uno per uno, contrassegnando in sequenza i dati che sono già stati visualizzati. I pulsanti "Cancella" e "Riapplica" determinano l'aspetto della tabella dopo l'applicazione delle condizioni. Quindi, dopo aver terminato il lavoro con la tabella, puoi riportare i caratteri alla loro forma originale senza modificare i dati stessi. A proposito, alcuni sono confusi dal fatto che tutti i record nella tabella scompaiono dopo aver applicato qualsiasi condizione. Bene, dai un'occhiata più da vicino, hai impostato condizioni in base alle quali non ci sono record che corrispondono a queste condizioni. Il fatto che la tabella sia filtrata è che i numeri di riga della tabella sono evidenziati in blu.
Passiamo ora al filtro avanzato. Si differenzia dall'autofiltro per una regolazione più fine, ma anche per una scelta più ampia durante il filtraggio dei dati. In particolare:
1) Imposta tutte le condizioni necessarie.
2) Consente di selezionare celle con dati univoci (non ripetuti). Questo è spesso necessario quando si lavora con i dati e l'opzione fa un ottimo lavoro con il problema.
3) Consente di copiare il risultato del filtro in un luogo separato senza toccare l'array principale.
Quindi, la differenza principale nel lavorare con questo filtro è che dobbiamo prima preparare una tabella delle condizioni. È fatto semplicemente. I titoli della tabella principale vengono copiati e inseriti in un posto comodo per noi (suggerisco sopra la tabella principale). Dovrebbero esserci così tante righe in questa tabella in modo che, dopo aver determinato le condizioni, non entri nella tabella principale.
Esempi di condizioni:
1) 'L *' - celle che iniziano con L
2) '> 5' - dati maggiori di 5
Se elimini le righe dalla tabella filtrata, verranno eliminate senza portare con te i vicini. Quelli. se la tabella è filtrata e mostra le righe 26-29 e 31-25, selezionando tutte le righe ed eliminandole non verrà eliminata la riga 30. Questo è conveniente, personalmente lo uso spesso quando scrivo macro. Qual è il vantaggio di questo: spesso otteniamo tabelle che devono essere portate in forma funzionante, ad es. eliminare, ad esempio le righe vuote. Cosa facciamo: filtriamo la tabella, mostrando solo le righe che non ci servono, quindi cancelliamo l'intera tabella, incluso il titolo. Le righe e il titolo non necessari vengono rimossi e la tabella non contiene spazi ed è un singolo intervallo. È possibile aggiungere una riga di intestazione con semplici operazioni di copia da un'area già preparata. Perché è importante quando si scrivono le macro? Non è noto da quale riga inizino i dati indesiderati e non è chiaro da quale riga iniziare a eliminare, l'eliminazione dell'intera tabella aiuta a risolvere rapidamente questo problema.
Quando si lavora con le tabelle, è spesso necessario "raccogliere in un mucchio" un numero dello stesso tipo di dati, ad esempio tutte le persone con il cognome "Ivanov" o tutti i prezzi nell'intervallo da 500 a 1000 rubli. Naturalmente, MS Excel offre una serie di strumenti per tali operazioni, ma prima di tutto, quando si tratta di ordinare e filtrare, pensiamo a Filtri dati MS Excel.
Come abilitare il filtro dati in MS Excel?
Per impostare un filtro per le colonne della tabella, seleziona l'intestazione della tabella con il mouse, quindi nella scheda "Home" selezionare, e oltre "Filtro"... I pulsanti freccia verso il basso vengono visualizzati nell'angolo destro delle celle dell'intestazione di colonna. Fai clic su di esso e vedrai un elenco molto solido di vari filtri integrati.
Proviamo a capire questa varietà.
- Filtra per colore... Funziona solo se alcune delle celle nella tabella sono evidenziate a colori (ad esempio, se le celle con entrate sono contrassegnate con il verde e con le spese - rosso, possono essere filtrate molto convenientemente per colore).
Il risultato dell'applicazione del filtro per colore
Filtri numerici e di testo di MS Excel
- Numero/Filtri di testo (a seconda che la colonna contenga numeri o testo). Il gruppo di filtri più grande e versatile. Quando selezioni un elemento, viene avviato lo strumento "Filtro automatico personalizzato", che offre opzioni di filtro per ogni gusto e colore. Per capirli è abbastanza semplice, basta guardare gli esempi qui sotto:
Filtra tutti i valori nella tabella che sono inferiori a 160.
Filtra tutti i valori nella tabella con prezzi inferiori a 160 E allo stesso tempo più di 80
Filtra tutti i valori nella tabella che non sono uguali a 180 e 200
- Filtro semplice... Non c'è niente a cui pensare: deseleziona le caselle davanti ai dati che devono essere filtrati e fai clic su "OK". Di conseguenza, sul foglio rimangono solo le informazioni necessarie.
Il filtro più semplice in MS Excel: ciò che scegli viene filtrato
Come disabilitare il filtro nel foglio di calcolo di MS Excel?
Reimpostare il filtro in MS Excel è ancora più semplice che impostarlo: fai nuovamente clic sulla freccia sul lato destro delle celle dell'intestazione della tabella e nel menu di scelta rapida visualizzato, seleziona "Rimuovi filtro dalla colonna".
Se vuoi rimuovere completamente il filtro dalla tabella, ripeti gli stessi passaggi dell'inizio: nella scheda "Home" scegliere Strumento Ordina e filtra dove premi di nuovo "Filtro".
Congratulazioni, i filtri dei dati nel foglio di calcolo di Excel sono ora completamente disabilitati.
Il principale svantaggio della norma ( ) - questa è la mancanza di informazioni visive sul filtro attualmente applicato: è necessario andare ogni volta nel menu del filtro per ricordare i criteri per la selezione dei record. Ciò è particolarmente scomodo quando vengono applicati più criteri. Il filtro esteso non presenta questo inconveniente: tutti i criteri vengono inseriti in una tabella separata sopra i record filtrati.
Algoritmo di creazione Filtro avanzato semplice:
- Creare una tabella a cui applicare il filtro (tabella di origine);
- Creiamo una tabella con criteri (con condizioni di selezione);
- Lanciare Filtro avanzato.
Lascia entrare la gamma UN 7: Do 83 c'è una tabella di origine con un elenco di prodotti contenenti campi (colonne) Prodotto, Quantità e Prezzo(vedi file di esempio). La tabella non deve contenere righe e colonne vuote, altrimenti Filtro avanzato(e normale) non funzionerà correttamente.
Compito 1 (inizia...)
Impostiamo un filtro per la selezione di righe che contengono valori nel nome del Prodotto di partenza dalla parola Unghia... Questa condizione di selezione è soddisfatta dalle righe con merci chiodi 20 mm, Unghie 10 mm, Unghie 10 mm e Unghia.
UN 1 : LA2 la2 indichiamo la parola Unghia.
Nota: Struttura dei criteri per Filtro avanzatoè chiaramente definita e coincide con la struttura dei criteri per le funzioni BDSUMM (), BSCHET (), ecc.
Di solito i criteri Filtro avanzato posizionati sopra il tavolo a cui è applicato il filtro, ma possono essere posizionati anche a lato del tavolo. Evita di posizionare una tabella con criteri sotto la tabella originale, sebbene ciò non sia vietato, ma non è sempre conveniente. nuove righe possono essere aggiunte alla tabella originale.
ATTENZIONE!
Assicurati che ci sia almeno una riga vuota tra la tabella con i valori delle condizioni di selezione e la tabella originale (questo renderà più facile lavorare con Filtro avanzato).
Filtro avanzato:
- chiamata Filtro avanzato();
- in campo Gamma originale UN 7: Do 83 );
- in campo Gamma di condizioni UN1 : LA2 .
Se lo si desidera, le righe selezionate possono essere copiate in un'altra tabella impostando l'interruttore in posizione Copia il risultato in un'altra posizione... Ma non lo faremo qui.
Premi il pulsante OK e il filtro verrà applicato: nella tabella rimarranno solo le righe contenenti il nome nella colonna Prodotto chiodi 20 mm, Unghie 10 mm, Unghie 50 mm e Unghia... Il resto delle linee sarà nascosto.
I numeri di riga selezionati verranno evidenziati in blu.
Per annullare l'azione del filtro, seleziona una cella qualsiasi nella tabella e premi CTRL + MAIUSC + L(il titolo verrà applicato e l'azione Filtro avanzato verrà annullato) o premere il pulsante menu Chiaro ().
Problema 2 (corrisponde esattamente)
Impostiamo un filtro per selezionare le righe che hanno nella colonna Prodotto Esattamente contiene la parola Unghia... Questa condizione di selezione è soddisfatta solo dalle righe con merci. unghia e Unghia(non preso in considerazione). I valori chiodi 20 mm, Unghie 10 mm, Unghie 50 mm non verrà preso in considerazione.
Posizionare la piastra con la condizione di selezione nell'intervallo B1: B2 ... La targa dovrebbe contenere anche il nome dell'intestazione di colonna con cui verrà effettuata la selezione. Come criterio in una cella B2 indichiamo la formula = "= Unghia".
Ora è tutto pronto per essere utilizzato Filtro avanzato:
- seleziona una cella qualsiasi nella tabella (non è necessario, ma velocizzerà il riempimento dei parametri del filtro);
- chiamata Filtro avanzato ( Dati/Ordina e filtra/Avanzato);
- in campo Gamma originale assicurati di includere un intervallo di celle di tabella insieme ai titoli ( UN 7: Do 83 );
- in campo Gamma di condizioni indicare le celle contenenti la tabella con il criterio, es. gamma B1: B2 .
- Fare clic su OK
Applicare Filtro avanzato con criteri così semplici, non ha senso particolare, poiché affronta facilmente questi compiti Filtro automatico... Consideriamo compiti di filtraggio più complessi.
Se specifichi not = "= Nails" come criterio, ma semplicemente Unghia, verranno visualizzati tutti i record contenenti nomi di partenza dalla parola chiodi ( Unghie 80mm, unghie2). Per visualizzare le righe con un prodotto, contenente sulla parola unghia, Per esempio, Unghie nuove, devi specificare = "= * Nails" come criterio o semplicemente * Unghie dove* è e indica qualsiasi sequenza di caratteri.
Problema 3 (condizione OR per una colonna)
Impostiamo un filtro per selezionare le righe che hanno un valore che inizia con la parola nella colonna Prodotto UnghiaO Sfondo.
I criteri di selezione in questo caso dovrebbero essere collocati sotto l'intestazione di colonna appropriata ( Prodotto) e devono trovarsi uno sotto l'altro nella stessa colonna (vedi figura sotto). Posiziona il tavolo nell'intervallo DO1: DO3 .
La finestra con i parametri del filtro avanzato e la tabella con i dati filtrati avrà questo aspetto.
Dopo aver fatto clic su OK, tutti i record contenuti nella colonna Prodotto prodotti Unghia O Sfondo.
Problema 4 (condizione I)
Esattamente contenere in colonna Prodotto prodotti Unghia, e nella colonna Quantità valore > 40. In questo caso i criteri di selezione devono essere inseriti nelle apposite intestazioni (Prodotto e Quantità) e devono trovarsi su un'unica riga. Le condizioni di selezione devono essere scritte in un formato speciale: ="= Unghia" e = "> 40". Posizionare la piastra con la condizione di selezione nell'intervallo MI1: FA2 .
Dopo aver fatto clic sul pulsante OK, verranno visualizzati tutti i record contenuti nella colonna Prodotto prodotti Unghia con quantità > 40.
CONSIGLI: Quando si modificano i criteri di selezione, è meglio creare ogni volta una tabella con i criteri e dopo aver chiamato il filtro, è sufficiente modificare il collegamento ad essi.
Nota: Se dovessi cancellare i parametri del filtro avanzato ( Dati / Ordina e filtra / Cancella), quindi prima di chiamare il filtro, seleziona una qualsiasi cella della tabella - EXCEL inserirà automaticamente un collegamento all'intervallo occupato dalla tabella (se ci sono righe vuote nella tabella, non verrà inserito un collegamento all'intera tabella, ma solo alla prima riga vuota).
Problema 5 (condizione OR per colonne diverse)
I compiti precedenti potrebbero, se lo si desidera, essere risolti nel modo consueto. Lo stesso problema non può essere risolto con un filtro convenzionale.
Selezioniamo solo quelle righe della tabella che Esattamente contenere in colonna Prodotto prodotti Unghia, OR che nella colonna Quantità contenere un valore > 40. I criteri di selezione in questo caso dovrebbero essere collocati sotto le intestazioni appropriate (Prodotto e Quantità) e dovrebbero essere localizzati su linee diverse... Le condizioni di selezione devono essere scritte in un formato speciale: = "> 40" e ="= Unghia". Posizionare la piastra con la condizione di selezione nell'intervallo MI4: FA6 .
Dopo aver fatto clic sul pulsante OK, verranno visualizzati i record contenenti nella colonna Prodotto prodotti Unghia OR valore > 40 (per qualsiasi prodotto).
Attività 6 (condizioni di selezione create come risultato dell'applicazione della formula)
Vero potere Filtro avanzato si manifesta quando viene utilizzato come condizioni di selezione per le formule.
Sono disponibili due opzioni per specificare le condizioni di selezione delle righe:
- inserire direttamente i valori per il criterio (vedi attività sopra);
- formare un criterio basato sui risultati della formula.
Considera i criteri dati dalla formula. La formula specificata come criterio di selezione deve restituire un risultato VERO o FALSO.
Ad esempio, mostriamo le righe che contengono un Prodotto che compare solo una volta nella tabella. Per farlo, entra nella cella H2 formula = CONTA.SE (Foglio1! $ A $ 8: $ A $ 83; A8) = 1 e in H1 invece di intestazione inseriremo un testo esplicativo, ad esempio, Valori non ripetibili... Applicabile Filtro avanzato specificando come l'intervallo di condizioni della cella H1: H2 .
Si noti che l'intervallo di ricerca per i valori viene inserito utilizzando e il criterio nella funzione CONTA.SE () viene inserito con un riferimento relativo. Questo è necessario perché quando si applica Filtro avanzato EXCEL lo vedrà la8 è un riferimento relativo e scorrerà lungo la colonna Prodotto un record alla volta e restituirà VERO o FALSO. Se il valore restituito è TRUE, verrà visualizzata la riga corrispondente nella tabella. Se il valore restituito è FALSE, la stringa non verrà visualizzata dopo l'applicazione del filtro.
Esempi di altre formule dal file di esempio:
- Visualizza le righe con prezzi maggiori del 3° prezzo più alto nella tabella. = C8> GRANDE ($ C $ 8: $ C $ 83,5) Questo esempio dimostra chiaramente la subdola della funzione LARGE(). Se ordini la colonna INSIEME A (prezzi), otteniamo: 750; 700; 700 ; 700; 620, 620, 160, ... Nella comprensione umana, "terzo prezzo più alto" corrisponde a 620 e nella comprensione della funzione PI GRANDE () - 700 ... Di conseguenza, non verranno visualizzate 4 righe, ma solo una (750);
- Uscita stringa sensibile a REGISTER = CONSIGLIO ("chiodi"; A8)... Verranno visualizzate solo le righe in cui si trova l'elemento unghia introdotto utilizzando lettere minuscole;
- Visualizzazione di righe con prezzo superiore alla media = C8> MEDIA ($ C $ 8: $ C $ 83);
ATTENZIONE!
Applicazione Filtro avanzato cancella il filtro applicato alla tabella ( Dati / Ordina e filtra / Filtra).
Problema 7 (le condizioni di selezione contengono formule e criteri usuali)
Considera ora un altro tavolo da file di esempio sul foglio di lavoro Compito 7.
Nella colonna Prodotto viene indicato il nome del prodotto, e nella colonna Tipo di elemento- il suo tipo.
Il compito è quello di visualizzare le merci per un determinato tipo di prodotto con un prezzo inferiore alla media. Cioè, abbiamo 3 criteri: il primo criterio imposta il Prodotto, il secondo - il suo Tipo e il terzo criterio (sotto forma di una formula) imposta il prezzo al di sotto della media.
Inserire i criteri nelle righe 6 e 7. Immettere il prodotto e il tipo di prodotto richiesti. Per un determinato tipo di prodotto, calcoliamo la media e la visualizziamo per chiarezza in una cella separata F7. In linea di principio, la formula può essere inserita direttamente nella formula di prova nella cella C7.
Verranno visualizzati 2 prodotti su 4 (di una data tipologia di prodotto).
Compito 7.1. (I 2 valori sulla stessa riga sono gli stessi?)
C'è una tabella che mostra l'anno di fabbricazione e l'anno di acquisto dell'auto.
Si vogliono visualizzare solo le righe in cui l'Anno di emissione coincide con l'Anno di acquisto. Questo può essere fatto usando la formula elementare = B10 = C10.
Problema 8 (un simbolo è un numero?)
Diciamo che abbiamo una tabella che elenca i diversi tipi di chiodi.
Vuoi filtrare solo quelle righe che hanno unghie da 1 pollice, chiodi da 2 pollici eccetera. merce Chiodi inossidabili, Chiodi cromati eccetera. non deve essere filtrato.
Il modo più semplice per farlo è impostare la condizione che un numero debba andare dopo la parola Nails come filtro. Questo può essere fatto usando la formula = ISNUMBER (- MID (A11; DLSTR ($ A $ 8) +2; 1))
La formula ritaglia dal nome del prodotto 1 carattere dopo la parola Nails (compreso lo spazio). Se questo carattere è un numero (cifra), la formula restituisce VERO e la stringa viene emessa, altrimenti la stringa non viene emessa. La colonna F mostra come funziona la formula, ad es. può essere testato prima del lancio Filtro avanzato.
Attività 9 (Righe di visualizzazione che NON CONTENGONO i Prodotti specificati)
È necessario filtrare solo le righe che NON contengono nella colonna Prodotto: Chiodi, lavagna, colla, carta da parati.
Per fare questo, dovrai usare una semplice formula = UND (CERCA.VERT (A15; $ A $ 8: $ A $ 11; 1; 0))
