Il numero di elementi disponibili per il filtraggio è limitato. Excel non può filtrare le colonne in cui il numero di elementi supera 999 (non il numero di righe).
Per filtrare quando ci sono più di 999 elementi, usa il filtro avanzato.
Per creare un filtro avanzato, utilizzeremo le funzioni "OFFSET" e "COUNTA" in Microsoft Excel.
CONTA: Restituisce il conteggio del numero di celle che contengono valori.
Sintassi della funzione "CONTA.VALORI": =CONTA.VALORI (valore1, valore2, valore3… .)
Esempio: Nell'intervallo A1:A5, le celle A2, A3 e A5 contengono i valori e le celle A1 e A4 sono vuote. Seleziona la cella A6 e scrivi la formula-
=CONTA.VALORI(A1:A5) la funzione tornerà 3
COMPENSARE: Restituisce un riferimento a un intervallo che è compensato da un numero di righe e colonne da un altro intervallo o cella.
Sintassi della funzione OFFSET: =OFFSET (riferimento, righe, colonne, altezza, larghezza)
Riferimento:- Questa è la cella o l'intervallo da cui desideri eseguire l'offset.
Righe e colonne da spostare: - Il numero di righe che si desidera spostare dal punto di partenza ed entrambe possono essere positive, negative o zero.
Altezza e Larghezza: - Questa è la dimensione dell'intervallo che vuoi restituire. Questo è un campo facoltativo.
Facciamo un esempio per comprendere la funzione Offset in Excel.
Abbiamo dati nell'intervallo A1: D10. La colonna A contiene il codice prodotto, la colonna B contiene la quantità, la colonna C contiene il costo per prodotto e la colonna D contiene il costo totale. Dobbiamo restituire il valore della cella C5 nella cella E2.
Per ottenere il risultato desiderato, dobbiamo seguire i passaggi indicati di seguito.
- Seleziona la cella E2 e scrivi la formula.
- =OFFSET(A1,4,2,1,1)e premi Invio sulla tastiera.
- La funzione restituirà il valore della cella C5.
In questo esempio, dobbiamo ottenere il valore dalla cella C5 a E2. La nostra cella di riferimento è la prima cella nell'intervallo che è A1 e C5 è 4 righe sotto e 2 colonne a destra da A1. Quindi, la formula è =OFFSET(A1,4,2,1,1) o =OFFSET(A1,4,2) (poiché 1,1 è facoltativo).
Ora, facciamo un esempio per recuperare l'ultimo valore in un elenco dinamico.
Abbiamo nomi di paesi in un intervallo. Ora, se aggiungiamo più paesi a questo elenco, dovrebbe essere disponibile automaticamente nell'elenco a discesa.
Per preparare il filtro avanzato, segui i passaggi indicati di seguito: -
- Seleziona la cella B2.
- Vai alla scheda Dati, seleziona Convalida dati dal gruppo Strumenti dati.
- Apparirà la finestra di dialogo "Convalida dati". Nella scheda "Impostazioni", seleziona "Personalizzato" dall'elenco a discesa Consenti.
- Verrà attivata la casella della formula.
- Scrivi la formula in questa casella.
- =OFFSET(A:A,1,0,COUNTA(A:A)-1,1).
- Fare clic su OK.
- In questa fase, l'ultima cella aggiornata è A11.
- Per verificare se la convalida dei dati funziona correttamente, aggiungi un nome di città nella cella A12.
Non appena aggiungi una voce in A12, verrà aggiunta all'elenco a discesa.
Questo è il modo in cui possiamo aggiungere più voci di 999 elementi in Microsoft Excel.