Isoler les dernières informations d'une liste Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Extraire les N dernières données

Cette formation est la première d'une série destinée à l'exploitation de la fonction Excel Decaler. Celle-ci est particulièrement précieuse pour rendre les feuilles de calcul dynamiques. Nous allons l'étudier dans ses moindres recoins. Et nous l'exploiterons dans des contextes parfois particuliers et souvent originaux.



Dans l'exemple finalisé illustré par la capture, nous travaillons sur un tableau de synthèse des chiffres d'affaires réalisés par une entreprise, pour chaque mois de l'année. Au choix d'une valeur par le biais d'une liste déroulante, nous isolons les N derniers chiffres d'affaires correspondants. Et comme vous le remarquez, ils sont extraits fort logiquement dans l'ordre inverse de leur présentation d'origine. Nous ciblons ainsi une période précise pour évaluer les résultats et tirer les conclusions.

Source et présentation de la problématique
Pour mener à bien cette étude et simplifier la compréhension de la fonction Decaler dans ses spécificités, nous suggérons de débuter à partir d'une source existante.

• Télécharger le classeur dernieres-donnees-extraites.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Un tableau de deux rangées, entre les colonnes B et C, énumère donc les chiffres d'affaires réalisés pour chaque mois de l'année. En cellule F3, une petite liste déroulante permet de choisir une valeur numérique pour déterminer le nombre des derniers mois à isoler. Cette extraction doit se faire à partir de la cellule E7 pour les mois et F7 pour les chiffres. En fonction de ces données isolées, la consolidation du total est attendue en cellule F5. Ce résultat permettra une lecture plus directe des données à synthétiser sur la période prélevée.

Isoler les N derniers mois
La fonction Excel Decaler permet de construire et d'agir sur des plages de cellules dynamiques :

=Decaler(Cellule_de_départ; décalage_ligne; décalage_colonne; [Hauteur]; [Largeur])

Elle supporte donc cinq arguments. Les deux derniers sont facultatifs. Elle offre la possibilité de rendre extensibles ces plages en largeur comme en hauteur (Facultatif). De plus, elle permet de déplacer le point de départ de la plage à l'horizontale comme à la verticale. Lorsque ces leviers sont actionnés dynamiquement, en cohérence avec les contraintes d'une feuille de calcul, nous obtenons des résultats de synthèse adaptés de façon chirurgicale.

Et une fois n'est pas coutume, nous n'allons pas agir sur son quatrième paramètre facultatif pour régler sa hauteur, selon les données contenues dans la base à étudier. Pour prélever et isoler les informations sur les N derniers mois, nous devons décaler son analyse et son pointeur en ligne. Donc, nous allons agir sur son deuxième argument, qui fixera le point de départ de l'étude par rapport à la cellule de référence fournie en premier paramètre.

• A l'aide de la liste déroulante en cellule F3, choisir le chiffre 5,

Ainsi, nous demandons d'isoler les chiffres sur les cinq derniers mois de l'année. Et à ce titre, vous remarquez qu'un format personnalisé est réglé sur cette cellule. Il permet d'expliciter la donnée. Grâce à lui, cette valeur est toujours considérée comme un nombre.

• Sélectionner le premier des derniers chiffres à isoler, soit la cellule F7,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Decaler(,
• Cliquer sur le premier chiffre d'affaires de la source de données, soit la cellule C5,

Nous désignons ainsi le point de départ de la plage de cellules à étudier. Mais, selon le choix, le pointeur doit se déplacer en ligne pour désigner le premier des derniers mois à extraire. Et sachant que la formule est destinée à être répliquée sur les lignes du dessous, nous devons figer cette référence, pour qu'elle ne se déplace pas en même temps que le calcul.

• Enfoncer la touche F4 du clavier pour figer la cellule, ce qui donne : $C$5,
• Taper un point-virgule (;) pour passer dans l'argument du décalage à opérer en ligne,
• Inscrire la fonction pour compter les cellules d'une plage suivie d'une parenthèse, soit : NbVal(,
• Désigner l'intégralité de la colonne des chiffres d'affaires, soit : C:C,

Ainsi, sur cette colonne entière, nous allons compter le nombre de cellules non vides. Il va en résulter le nombre de chiffres d'affaires inscrits, soit 12 ici. Donc, nous allons opérer un décalage de 12 lignes par rapport à la cellule de référence, passée en premier paramètre, soit par rapport au premier chiffre d'affaires. Comme il est lui-même considéré dans la sélection, le pointeur va pour l'instant se positionner sur la première ligne vide en dessous des chiffres. Nous devons donc décrémenter ce décalage pour pointer effectivement sur la dernière valeur. Mais cette décrémentation doit être dynamique pour progresser en même temps que le calcul est répliqué sur les lignes du dessous. Ainsi, nous prélèverons les chiffres des N derniers mois suivants. Pour cela, nous devons exploiter la fonction Excel Ligne. Elle retourne l'indice de ligne d'une cellule. En lui passant une cellule de la première ligne, la décrémentation sera tout d'abord d'une seule unité, puis de deux, de trois etc...

• Fermer la parenthèse de la fonction NbVal,
• Taper le symbole moins (-) pour annoncer la soustraction à suivre,
• Inscrire la fonction pour la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Désigner une cellule de la première ligne, par exemple la cellule A1,

Ainsi, nous allons décrémenter le premier calcul d'une unité. De fait, nous allons extraire le dernier chiffre. Puis, par la logique de réplication sur les lignes du dessous, nous allons incrémenter cette valeur à soustraire. Nous allons donc pointer sur les données du dessus et remonter dans la liste pour les extraire tour à tour dans l'ordre inverse de la source de données.

• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0,

En effet, nous ne souhaitons opérer aucun décalage en colonne. Tous les chiffres sont situés dans cette seule et même colonne unique.

• Fermer la parenthèse de la fonction Decaler,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous conservons active la cellule du résultat pour l'exploiter dans l'enchaînement. Et c'est ce que nous allons faire.

• Tirer la poignée du résultat à la verticale jusqu'en ligne 18,

Ainsi, nous réalisons bien l'extraction des chiffres d'affaires dans l'ordre inverse, originellement proposé par la source de données. Mais pour l'instant, cette extraction n'est pas limitée à la volonté de l'utilisateur soumise par le biais de la liste déroulante.

La formule que nous avons construite pour cette extraction inversée est la suivante :

=DECALER($C$5; NBVAL(C:C)-LIGNE(A1); 0)

Limiter la liste extraite
Pour limiter le nombre de valeurs extraites, il suffit d'inscrire ce précédent calcul dans une formule conditionnelle Si. Son critère consiste à vérifier que le nombre de lignes répliquées n'a pas dépassé le nombre de mois invoqué par l'utilisateur par le biais de la liste déroulante.

• Sélectionner le premier résultat extrait, soit la cellule F7,
• Dans sa barre de formule, adapter le précédent calcul comme suit :

=SI(LIGNE(A1)<=$F$3; DECALER($C$5; NBVAL(C:C)-LIGNE(A1); 0); '')

Nous testons si l'indice de ligne incrémenté avec le calcul répliqué est toujours inférieur au nombre désigné par l'utilisateur pour l'extraction (LIGNE(A1)<=$F$3). La cellule de ce choix est bien entendue figée ($F$3), pour être considérée par chaque calcul reproduit. Si cette condition est honorée, cela signifie que nous n'avons pas encore dépassé les bornes définies. Donc, nous procédons à l'extraction grâce à la syntaxe précédemment construite avec la fonction Decaler. Dans le cas contraire, nous ignorons forcément le calcul grâce à deux guillemets ('') pour garder la cellule vide.

• Valider cette syntaxe par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour reproduire la logique jusqu'en ligne 18,

Comme vous pouvez le voir, l'extraction est cette fois limitée aux N derniers mois définis par le biais de la liste déroulante. Et si vous modifiez cette valeur en cellule F3, vous remarquez que la liste des chiffres d'affaires s'ajuste en conséquence.



Vous notez de même que le quadrillage s'ajuste parfaitement à la limite de l'extraction. C'est une règle de mise en forme conditionnelle qui se déclenche pour affecter ces attributs lorsque la cellule est détectée comme non vide.

Désormais, dans la colonne E voisine, nous devons isoler les mois associés concernés. Il s'agit de produire le même calcul. Cependant, le décalage doit être opéré à partir du premier mois, soit de la cellule B5.

• Sélectionner la cellule E7,
• Inscrire la syntaxe suivante, adaptée du précédent calcul :

=SI(LIGNE(A1)<=$F$3; DECALER($B$5; NBVAL(C:C)-LIGNE(A1); 0); '')

• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, tirer la poignée de la cellule à la verticale jusqu'en ligne 18,

Tous les mois associés sont parfaitement isolés. Et bien entendu, leur énumération se stoppe en même temps que celle des chiffres d'affaires inversés, selon le nombre indiqué par l'utilisateur.

Il reste à offrir le résultat statistique et dynamique en cellule F5. Il s'agit d'une banale somme. Et sachant que la fonction dédiée est capable d'ignorer les cellules vides, nous pouvons désigner l'intégralité potentielle de la plage, soit lorsque les 12 derniers mois sont demandés.

• En cellule F5, inscrire et valider la formule suivante : =SOMME(F7:F18),

Le résultat synthétise parfaitement les chiffres isolés sur la période ciblée.



Et bien entendu, si vous modifiez le nombre de ces mois, la somme se réajuste en même temps que l'extraction se délimite.

Repérer visuellement les chiffres d'affaires
Pour une solution encore plus efficace, nous proposons d'appliquer une mise en forme conditionnelle au tableau source. Son objectif est de mettre automatiquement en valeur les derniers mois et chiffres d'affaires associés. Ainsi, en même temps que la liste extraite s'ajustera, nous délimiterons les données concernées dans le tableau.

• Sélectionner toutes les données du tableau, soit la plage de cellules B5:C16,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, opter pour l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Dans la zone de saisie juste en-dessous, construire la règle suivante :

=ET(LIGNE($C5)<=NBVAL($C:$C) + 4; LIGNE($C5)>NBVAL($C:$C) + 4-$F$3)

Nous vérifions donc une double condition. Nous exerçons l'étude volontairement sur la colonne C. Celle-ci ne propose en effet aucun titre. La ligne de la cellule ne doit pas dépasser les bornes du tableau (LIGNE($C5)<=NBVAL($C:$C) + 4). Pour ce critère, nous ajoutons quatre unités au décompte de la fonction NbVal. Elles correspondent aux quatre cellules vides qui interviennent avant le tableau. Il faut les considérer. La cellule dont la ligne est comparée est figée seulement en colonne. Ainsi, au gré de l'analyse, toutes les cellules des autres lignes seront comparées. Et grâce à la colonne figée, c'est toute la ligne d'un même enregistrement (Les deux colonnes) qui est considérée. Dans le même temps, la ligne de cette même cellule doit forcément être supérieure à la dernière ligne non considérée du fait du choix en F3 (LIGNE($C5)>NBVAL($C:$C) + 4-$F$3).

Lorsque ce double critère est satisfait, nous proposons de parer la cellule d'un fond bleu clair et le texte d'un bleu foncé.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu pâle,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un bleu foncé pour le texte,
• Valider ces attributs de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par Ok,

Comme vous pouvez le voir, au changement de valeur depuis la liste déroulante, en même temps que l'extraction des derniers chiffres s'ajuste, ceux-ci sont surlignés dans le tableau d'origine.