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 sono presenti più di 999 elementi, utilizza il filtro avanzato.

Per creare un filtro avanzato, utilizzeremo le funzioni “OFFSET” e “COUNTA” in Microsoft Excel.

COUNTA: restituisce il conteggio del numero di celle che contengono valori.

Sintassi della funzione “COUNTA”: = COUNTA (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- _ = COUNTA (A1: A5) _ la funzione restituirà 3

img1

OFFSET: restituisce un riferimento a un intervallo che è spostato di 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 si desidera eseguire l’offset .

Righe e colonne da spostare: _ _- Il numero di righe che vuoi 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 del prodotto e la colonna D contiene il costo totale. Dobbiamo restituire il valore della cella C5 nella cella E2.

img2

Per ottenere il risultato desiderato, è necessario seguire i passaggi indicati di seguito.

  • Seleziona la cella E2 e scrivi la formula.

OFFSET (A1,4,2,1,1) * e premere Invio sulla tastiera.

  • La funzione restituirà il valore della cella C5 .

img3

img4

In questo esempio, dobbiamo ottenere il valore dalla cella C5 a E2. La nostra cella di riferimento è la prima cella dell’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 è opzionale).

Facciamo ora 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.

img5

Per preparare il filtro avanzato, seguire i passaggi indicati di seguito: – * Selezionare la cella B2.

  • Vai alla scheda Dati, seleziona Convalida dati dal gruppo Strumenti dati.

img6

  • Apparirà la finestra di dialogo “Convalida dati”. Nella scheda “Impostazioni”, seleziona “Personalizzato” dall’elenco a discesa Consenti.

img7

  • La casella della formula verrà attivata.

  • Scrivi la formula in questa casella.

* _ = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1) ._

  • Fare clic su OK.

img8

  • In questa fase, l’ultima cella aggiornata è A11.

img9

  • Per verificare se la convalida dei dati funziona correttamente, aggiungi un nome di città nella cella A12.

img10

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.