Pour récupérer la dernière valeur d’une liste dynamique, nous utiliserons l’option Validation des données avec les fonctions OFFSET et COUNTA dans Microsoft Excel 2010/2013.

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: _ _- Combien de lignes vous voulez déplacer le 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

Suivez les étapes 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 dans 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).

Maintenant, prenons un exemple pour récupérer la dernière valeur dans une liste dynamique.

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

img5

Pour préparer une liste dynamique, nous devons créer une formule qui récupérera la dernière valeur de la colonne et se mettra à jour automatiquement lorsqu’un nouveau nombre est ajouté.

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 vous pouvez créer une liste dynamique et y insérer automatiquement de nouvelles entrées dans Microsoft Excel 2010 et 2013.

__