Le nombre d’éléments disponibles pour le filtrage est limité. Excel ne peut pas filtrer les colonnes dans lesquelles le nombre d’éléments dépasse 999 (pas le nombre de lignes).

Pour filtrer lorsqu’il y a plus de 999 éléments, utilisez le filtre avancé.

Pour créer un filtre avancé, nous utiliserons les fonctions «OFFSET» et «COUNTA» dans Microsoft Excel.

COUNTA: il renvoie le nombre de cellules contenant des valeurs.

Syntaxe de la fonction “COUNTA”: = COUNTA (valeur1, valeur2, valeur3 …….)

Exemple: _ _Dans la plage A1: A5, les cellules A2, A3 et A5 contiennent les valeurs et les cellules A1 et A4 sont vides. Sélectionnez la cellule A6 et écrivez la formule- _ = COUNTA (A1: A5) _ _ la fonction renverra 3_

img1

OFFSET: il renvoie une référence à une plage qui est décalée d’un certain nombre de lignes et de colonnes à partir d’une autre plage ou cellule.

Syntaxe de la fonction OFFSET: = OFFSET (référence, lignes, cols, hauteur, largeur) Reference: – C’est la cellule ou la plage à partir de laquelle vous voulez décaler.

Rows and Columns to move: _ _- Le nombre de lignes à déplacer à partir du point de départ et les deux peuvent être positives, négatives ou nulles.

_Height and Width _ * : – Il s’agit de la taille de la plage que vous souhaitez renvoyer. Ceci est un champ facultatif . Prenons un exemple pour comprendre la fonction Décalage dans Excel.

Nous avons des données dans la plage A1: D10. La colonne A contient le code produit, la colonne B contient la quantité, la colonne C contient le coût par produit et la colonne D le coût total. Nous devons renvoyer la valeur de la cellule C5 dans la cellule E2.

img2

Pour obtenir le résultat souhaité, nous devons suivre les étapes mentionnées ci-dessous.

  • Sélectionnez la cellule E2 et écrivez la formule.

OFFSET (A1,4,2,1,1) * et appuyez sur Entrée sur le clavier.

  • La fonction retournera la valeur de la cellule C5 .

img3

img4

Dans cet exemple, nous devons obtenir la valeur de la cellule C5 à E2. Notre cellule de référence est la première cellule de la plage qui est A1 et C5 est 4 lignes en dessous et 2 colonnes à droite de A1. Par conséquent, la formule est = OFFSET (A1,4,2,1,1) ou = OFFSET (A1,4,2) (puisque 1,1 est facultatif).

Prenons maintenant un exemple pour récupérer la dernière valeur d’une liste dynamique.

Nous avons des noms de pays dans une gamme. Maintenant, si nous ajoutons plus de pays à cette liste, il devrait être disponible dans la liste déroulante automatiquement.

img5

Pour préparer un filtre avancé, suivez les étapes ci-dessous: – * Sélectionnez la cellule B2.

  • Allez dans l’onglet Données, sélectionnez Validation des données dans le groupe Outils de données.

img6

  • La boîte de dialogue «Validation des données» apparaît. Dans l’onglet «Paramètres», sélectionnez «Personnalisé» dans la liste déroulante Autoriser.

img7

  • La boîte de formule sera activée.

  • Écrivez la formule dans cette case.

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

  • Cliquez sur OK.

img8

  • À ce stade, la dernière cellule mise à jour est A11.

img9

  • Pour vérifier si la validation des données fonctionne correctement, ajoutez un nom de ville dans la cellule A12.

img10

Dès que vous ajoutez une entrée dans A12, elle sera ajoutée à la liste déroulante.

C’est ainsi que nous pouvons ajouter plus d’entrées que 999 éléments dans Microsoft Excel.