Il peut arriver que vous assembliez un classeur et que vous souhaitiez conserver un résumé sur une feuille de calcul et des informations détaillées sur une autre.

Par exemple, supposons que Sheet1 soit votre feuille de calcul récapitulative et que vous ayez des informations détaillées sur les comptes bancaires sur Sheet2. En examinant les informations détaillées, vous avez des dates dans la colonne A et des soldes pour différents comptes dans les colonnes B, C et D. Ainsi, les informations détaillées sont un tableau qui montre une progression en cours des soldes bancaires à différentes dates.

En rassemblant vos informations récapitulatives sur la feuille Sheet1, vous vous rendez compte que vous devez référencer les derniers chiffres des colonnes B, C et D. Ces chiffres représentent les derniers soldes, et sont donc parfaits pour votre résumé. Comment faites-vous? Surtout lorsque vous continuez à ajouter des informations à votre feuille de calcul détaillée au fil du temps?

En fait, il existe plusieurs façons d’aborder le problème. (Il existe généralement plusieurs façons de résoudre tout problème Excel.) Une méthode consiste à utiliser la fonction RECHERCHEV. Au point du résumé où vous voulez le dernier solde de la colonne B du détail (feuille 2), vous mettriez la formule suivante:

=VLOOKUP(MAX(Sheet2!$A:$A),Sheet2!$A:$D,2)

Pour modifier les références des deux autres soldes de compte, il vous suffit de changer le dernier chiffre (2) en 3 (pour le compte de la colonne C) ou 4 (pour le compte de la colonne D). La fonction fonctionne car elle recherche la valeur maximale dans la colonne A, qui contient des dates. Il regarde ensuite dans la table de données (Sheet2! $ A: $ D) et trouve le décalage approprié pour la colonne souhaitée.

Cette approche fonctionne bien, à condition qu’il n’y ait pas de dates dans la colonne A après les derniers soldes saisis. S’il y en a, les valeurs renvoyées seront toujours incorrectes.

Une autre façon d’aborder le problème consiste à utiliser la fonction INDEX en conjonction avec COUNT ou COUNTA. Si les colonnes de détail ne contiennent aucun texte (même dans les en-têtes de colonne), vous utiliserez la fonction COUNT. S’il y a du texte, alors COUNTA est préféré. Au moment où vous souhaitez inclure le dernier solde de la colonne B du détail, vous utiliserez la formule suivante:

=INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B))

Il examine le tableau, détermine le nombre de cellules non vides dans la colonne B, puis extrait le chiffre de cette dernière cellule non vide. Pour adapter la formule des colonnes C et D, changez simplement les références B en C ou D.

Encore une autre façon de résoudre le problème consiste à utiliser la fonction OFFSET, comme dans l’exemple suivant:

=OFFSET(Sheet2!B1,COUNTA(Sheet2!B:B)-1,0)

Cette fonction renvoie la valeur d’un décalage de cellule à partir d’une cellule de référence de base. Dans ce cas, la cellule de base est Sheet2! B1. La fonction COUNTA est utilisée pour déterminer le nombre de lignes à décaler par rapport à la base et le 0 spécifie que le décalage doit être dans la même colonne que la référence de base. Pour changer la formule des colonnes C et D, changez simplement toutes les références à B en C ou D.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (12470) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.

Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Referring_to_the_Last_Cell [Se référant à la dernière cellule].