Récemment, un client m’a demandé quelle formule utiliser dans Excel pour obtenir, en permanence, la valeur de la dernière cellule d’un tableau (comme celui sur l’image ci-bas). Ce tableau évoluait de jour en jour et il ne voulait pas utiliser la fonction recherchev (vlookup) car cela aurait exigé que l’on connaisse la date de la dernière mise à jour, ce qui n’était pas une option. Cet article a donc comme objectif de partager avec vous une fonction Excel imbriquée, qui permet de déterminer la valeur de la dernière cellule active d’un tableau de données.
.
Fonction imbriquée Indirect, Adresse (Address), Nb (Count) et Ligne (Row)
La solution que j’ai proposée à mon client est une fonction imbriquée. L’imbrication se fait à partir des fonctions Indirect, Adresse (Address), Nb (Count) et Ligne (Row), tel qu’illustré sur l’image ci-bas.
Décomposons cette fonction pour mieux la comprendre:
- Row() ou Ligne() indique le numéro de la ligne où l’on se situe, donc dans notre exemple, 4
- Count(C4:C40) ou Nb(C4:C40) renvoie le nombre de valeurs numériques entre les lignes 4 et 40 de la colonne C
- On suppose donc ici que le tableau n’aura pas plus que 36 lignes (40-4)
- Le nombre de valeurs numériques entre les lignes 4 et 40 de la colonne C est de 7
- Address(7+4,3) ou Adresse(7+4,3), donne l’adresse de la cellule à la ligne 11 et à la colonne C)
- Indirect(C11) nous revoie la valeur de la cellule C11, soit 21$
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
Alternative plus intéressante
Au lieu d’utiliser la fonction Count(C4:C40) ou Nb(C4:C40) et de devoir déterminer à l’avance à quelle ligne devrait se terminer la base de données, il peut être préférable d’utiliser la fonctionnalité Mettre sous forme de tableau (Format as table). Ainsi, la fonction Count (ou NB) s’ajustera en fonction de l’évolution du tableau. Pour ce faire, vous n’avez qu’à sélectionner les cellules C4 à C11 et la formule prendra en compte la colonne active Montant (voir image ci-dessous).
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour une introduction aux principes de base et aux meilleures pratiques d’affaires en modélisation financière dans Excel et vous familiariser avec les fonctions et les fonctionnalités qui permettent de faire des prévisions financières, des analyses de sensibilité et des analyses de scénarios, suivez notre formation Excel – Modélisation financière niveau 1.
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 1 :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Bonjour,
On peut aussi simplifier avec la fonction INDEX :
=INDEX(C5:C11,LIGNES(B5:B11))
ou encore, si sous forme de tableau (sous Excel 2013) :
=INDEX(Tableau1[Montant],LIGNES(Tableau1[Montant]))
L’avantage est que la dernière valeur du tableau sera retournée et ce même s’il y a des cellules ou des lignes vides dans le tableau.
Bonjour,
La fonction =INDEX(C5:C11,LIGNES(B5:B11)) fonctionne pour moi, en revanche les dernières valeurs de mon intervalle sont « 0 » et je souhaiterais que la fonction renvoie le dernier nombre qui est plus grand que 0, comment puis-je faire cela ?
Merci d’avance !
C’est vraiment un article très indispensable car on peut cela répond aussi à notre question. Mais surtout les informations sont clairs et brefs
Excellent, comment faire si la valeur de la cellule est une date?
Il faut juste changer le format de ta cellule. 🙂
Par défaut c’est au format standard (les dates sont transformées en nombre).
Le sujet date mais j’ai aussi trouvé cette solution plutôt simple :
=RECHERCHEV(9^9;C:C;1)
*Ne fonctionne que pour les valeurs numériques
Merci beaucoup pour cette suggestion qui répond complètement mon besoin !
Et bravo…