Cumuler les résultats sans connaître les noms des feuilles

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


Sujets que vous pourriez aussi aimer :


Cumuler les valeurs feuille à feuille

A l'occasion de l'astuce Excel précédente, nous avons appris à cumuler les résultats des feuilles précédentes grâce à une unique formule à répliquer. Ici, l'objectif est le même. Mais cette fois, il n'y a pas de suite logique dans les noms de feuille. Ils ne sont pas suffixés d'un numéro incrémenté.

Classeur source et présentation
Pour la découverte de cette nouvelle astuce, nous proposons de récupérer un classeur Excel multi-feuille.

• Télécharger le classeur cumuler-donnees-feuilles.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons un classeur constitué de six feuilles. L'ordre de l'énumération est tout à fait logique, il s'agit des six premiers mois de l'année. Mais pour une formule, cette logique n'existe pas. Dans le précédent volet, toutes les feuilles présentaient le même nom. C'est un suffixe numérique incrémenté qui permettait de les différencier. Et dans ce contexte, une formule retouchant les numéros peut très facilement atteindre la feuille précédente.

• En bas de la fenêtre Excel, cliquer sur l'onglet Février pour afficher sa feuille,

Les tableaux des six feuilles offrent la même structure. En colonne D (Mois M-1) et à partir de la ligne 4, il est question de récupérer le cumul de la feuille précédente en colonne F (Cumul), là aussi à partir de la ligne 4. Cette restitution doit se faire automatiquement par une formule à répliquer sur toutes les feuilles. Ainsi, le cumul de la feuille en cours réalise déjà l'addition entre le mois M-1 et le mois M. C'est ainsi que les consolidations pourront être opérées.

Lister les noms de feuille
Nous l'avons déjà découvert au cours d'une précédente astuce, il existe une fonction de macro XL 4 qui permet d'énumérer toutes les feuilles d'un classeur dans un tableau horizontal. L'utilisation de cette macro impose l'extension Xlsm donnée à ce fichier. C'est grâce à ce tableau que nous pourrons rechercher la position de la feuille en cours dans l'énumération pour descendre d'une unité afin de pointer sur la feuille précédente et ce une fois encore, malgré l'absence de logique dans l'enchaînement des noms. Cette fonction de macro se nomme Lire.Classeur.

• En bas de la fenêtre Excel, cliquer sur l'onglet Janvier pour revenir sur la première feuille,
• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• En haut de la boîte de dialogue qui suit, cliquer sur le bouton Nouveau,
• Dans la nouvelle boîte, taper l'intitulé nomFeuilles dans la zone Nom,
• Dans la zone Fait référence à, remplacer la syntaxe en cours par la suivante :

=LIRE.CLASSEUR(1)



C'est avec ce paramètre (1) que nous allons obtenir un tableau horizontal des noms de feuille.

• Cliquer sur le bouton Ok pour valider la création de ce nom,
• Puis, cliquer sur le bouton Fermer du gestionnaire pour revenir sur la feuille,
• Sélectionner six cellules à l'horizontale, par exemple la plage C16:H16,
• Taper le symbole égal (=) pour initier un calcul,

Comme vous pouvez le remarquer, c'est la première cellule de la plage qui est active par défaut.

• Taper le nom précédemment créé, soit nomFeuilles,
• Puis, valider cette formule par le raccourci clavier CTRL + MAJ + Entrée,

C'est effectivement un raisonnement matriciel dans la mesure où nous souhaitons que la formule analyse toutes les rangées de son tableau pour en restituer les éléments.

• La plage étant toujours sélectionnée, cliquer sur le bouton Aligner à droite dans la section Alignement du ruban Accueil,

Les informations renvoyées sont effectivement longues. Cette astuce d'alignement permet de conserver l'affichage sur la fin de chaque chaîne. Il s'agit du nom de chaque feuille. Mais comme vous pouvez le voir, chacune est préfixée du nom du classeur. Pour les besoins de notre formule de cumul, nous devons nous séparer du préfixe. En d'autres termes, nous souhaitons conserver uniquement l'information située après le crochet fermant.

Remarque : Il aurait été possible d'obtenir un tableau vertical pour ces noms de feuille. Pour cela, il aurait suffi d'imbriquer la fonction Lire.Classeur dans la fonction Transpose :

=TRANSPOSE(LIRE.CLASSEUR(1))

La fonction Excel Stxt permet de prélever une portion d'une chaîne à partir d'un emplacement à définir. Cet emplacement peut être décelé grâce à la fonction Trouve en lui passant le caractère recherché, soit le crochet fermant.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier,
• Dans la zone Fait référence à de la nouvelle boîte, adapter la précédente syntaxe comme suit :

=STXT(LIRE.CLASSEUR(1); TROUVE("]"; LIRE.CLASSEUR(1))+1; 99)

• Cliquer sur le bouton Ok pour valider la syntaxe,
• Puis, cliquer sur le bouton Fermer du gestionnaire pour revenir sur la feuille Excel,

Grâce à cette adaptation et comme vous pouvez l'apprécier, Il ne reste plus que le nom des feuilles parfaitement énumérés dans l'ordre. Avec la fonction Stxt, sur la chaîne retournée (LIRE.CLASSEUR(1)), nous prélevons l'information placée un caractère après le crochet fermant (+1). C'est la fonction Trouve qui renvoie cette position de départ en exerçant la recherche du crochet fermant sur cette même chaîne. Ensuite et par mesure de sécurité, nous prélevons sur une longueur assez importante (99). Ainsi, nous sommes certains de ne rien omettre. La fonction Excel Stxt s'adapte et s'arrête à la fin de la chaîne.

Nom de la feuille précédente
Maintenant, depuis ce tableau des noms, nous devons extraire le nom de la feuille placé juste avant celui de la feuille en cours. Pour réaliser une extraction, la fonction Index est nécessaire. Et pour trouver la position de la feuille en cours dans l'énumération, c'est la fonction Equiv qui est incontournable.

• En bas de la fenêtre Excel, cliquer sur l'onglet Février pour activer la deuxième feuille,
• Cliquer ensuite sur la cellule D4 pour la sélectionner,
• Puis, taper tout d'abord la formule suivante : =CELLULE("nomfichier"; A1),

La fonction Cellule avec le paramètre filename ou nomfichier retourne notamment le nom de la feuille active. A validation, malgré l'affichage tronqué, vous constatez que nous obtenons en effet le chemin complet jusqu'au nom de la feuille en cours. Et comme précédemment, c'est seulement ce nom que nous souhaitons conserver, juste après le crochet fermant. Donc la fonction Trouve en deuxième argument de la fonction Stxt est de nouveau nécessaire.

• Donc en cellule D4, adapter la précédente syntaxe comme suit :

=STXT(CELLULE("filename"; A1); TROUVE("]"; CELLULE("nomfichier"))+1; 99)

• Puis, valider la formule avec la touche Entrée du clavier,

Comme vous pouvez le voir, seul le nom de la feuille active subsiste. Nous allons donc pouvoir entreprendre sa recherche dans le tableau des noms de feuilles, pour extraire celle située juste avant.

• Sélectionner de nouveau la cellule D4,
• Dans la barre de formule, cliquer après le symbole égal (=) pour y placer le point d'insertion,
• Taper le nom de la fonction d'extraction suivi d'une parenthèse, soit : Index(,
• Puis, désigner la matrice de recherche par son nom, soit : nomFeuilles,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne pour la fonction Index,

Nous l'avons dit, il s'agit d'une matrice horizontale. C'est donc la position en colonne qui nous intéresse. Mais dans ce contexte particulier d'une matrice à une seule rangée, la fonction Index comprendrait et s'adapterait. Néanmoins, nous souhaitons rester cohérents.

• Taper le chiffre 0 suivi d'un point-virgule, soit : 0;, pour passer dans l'argument de la colonne,

C'est cette position que nous devons trouver en recherchant le nom de la feuille active dans l'énumération de la matrice.

• Taper la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,

Nous sommes ainsi placés dans le premier argument de la fonction Equiv. Il s'agit de la valeur cherchée. La syntaxe qui suit et précédemment construite est déjà en place. Elle désigne précisément le nom de la feuille active à trouver.

• Cliquer à la fin de la syntaxe, après la dernière parenthèse pour y placer le point d'insertion,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner de nouveau la matrice par son nom, soit : nomFeuilles,

L'extraction a lieu depuis cette matrice, ça nous le savons. Mais la recherche du nom de la feuille active se fait également dans cette même matrice.

• Taper un point-virgule suivi du chiffre zéro : ;0, pour engager une recherche exacte,
• Fermer alors la parenthèse de la fonction Equiv,

De fait, nous sommes de retour dans les bornes de la fonction Index, plus précisément sur son argument pour l'indice de colonne. Et avant de valider cette extraction, nous devons retrancher une unité à cette position trouvée pour bien retourner le nom de la feuille précédente.

• Retrancher une unité à ce score, soit : -1,
• Fermer la parenthèse de la fonction Index,
• Puis, valider la formule avec la touche Entrée du clavier,

Comme vous le constatez, nous avons parfaitement réussi à extraire le nom de la feuille située juste avant la feuille active. Donc, dans ce type de construction, l'absence de suite logique dans l'énumération des feuilles d'un classeur n'est plus un problème.

La syntaxe complète de la formule que nous avons bâtie est la suivante :

=INDEX(nomFeuilles; 0; EQUIV(STXT(CELLULE("filename"; A1); TROUVE("]"; CELLULE("nomfichier"))+1; 99); nomFeuilles; 0)-1)

Cumuler les données de feuilles
Maintenant, nous allons pouvoir exploiter le fruit de cette extraction pour pointer en cascade sur les précédentes feuilles afin de rapatrier les cumuls et pouvoir consolider les résultats feuille à feuille. Mais comme vous le savez, ce nom extrait est considéré comme un texte. Pour qu'il soit considéré comme une feuille, il doit être interprété avec la fonction Excel Indirect.

• Sélectionner de nouveau la cellule D4,
• Dans la barre de formule, cliquer après le symbole égal pour y placer le point d'insertion,
• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Puis, cliquer à fin de la syntaxe, après la dernière parenthèse pour y placer le point d'insertion,

La fonction Indirect connaît donc d'ores et déjà le nom de la feuille à pointer grâce à nos précédentes constructions. Maintenant, sur cette feuille précédente, nous devons cibler les cellules de la colonne F pour rapatrier les précédents cumuls, dans la colonne en cours pour la feuille active, soit la colonne D. Et comme vous le savez, pour descendre jusqu'aux cellules, le nom de la feuille doit être suivi d'un point d'exclamation.

• Enfoncer la touche 1 en haut à gauche du clavier pour le symbole de concaténation (&),
• Puis, inscrire un point d'exclamation entre guillemets, soit : "!",
• Dès lors, inscrire un nouveau caractère de concaténation (&),

Par réplication à venir de la formule sur les lignes du dessous, nous devons prélever toutes les valeurs des cumuls en colonne F. Il n'est donc pas question de renseigner des coordonnées statiques. Et précisément, c'est la fonction Excel Adresse qui permet de recomposer dynamiquement les coordonnées d'une cellule.

• Inscrire le nom de cette fonction suivi d'une parenthèse, soit : Adresse(,

En premier argument, nous devons lui préciser l'indice de ligne de la cellule pointée sur la feuille précédente. Et cet indice de ligne suit le même rythme que celui de la feuille en cours. En deuxième argument, nous devons lui préciser l'indice de colonne. Cette colonne est située deux rangée plus à droite par rapport à la colonne du calcul.

• Inscrire la fonction pour la position en ligne suivie de deux parenthèse, soit : Ligne(),

Nous avions déjà appris cette astuce, en l'absence d'argument, la fonction Ligne renvoie la position de la cellule active.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,
• Inscrire la fonction pour la position en colonne, suivie de deux parenthèses, soit : Colonne(),

Le raisonnement est le même. En l'absence d'argument, elle renvoie la colonne en cours. Or la colonne active est la colonne D alors que nous souhaitons pointer sur la colonne F. Nous devons donc ajouter deux unités à cette position.

• Taper le symbole plus suivi du chiffre deux, soit : +2,
• Fermer la parenthèse de la fonction Adresse,
• Puis, fermer la parenthèse de la fonction Indirect,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

De cette façon, nous conservons active la cellule du résultat pour l'exploiter dans l'enchaînement. Le premier résultat tombe et il s'agit bien du premier cumul pour le mois précédent.

• Double cliquer sur la poignée du résultat pour répandre la formule sur tout le tableau,

Cette fois, ce sont bien tous les cumuls précédents qui sont parfaitement rapatriés. De fait, les cumuls en cours, consistant en une simple addition, se mettent automatiquement à jour. Il ne nous reste plus qu'à répliquer cette formule en cascade sur toutes les feuilles suivantes.

• Sélectionner la plage D4:D13 du précédent calcul,
• La copier avec le raccourci clavier CTRL + C,
• En bas de la fenêtre Excel, cliquer sur l'onglet Mars pour afficher sa feuille,
• Sélectionner la cellule D4 et coller (CTRL + V) la formule,

Comme vous pouvez le voir, les précédents cumuls sont importés en cascade actualisant automatiquement les cumuls en cours.

• Coller la formule sur toutes les feuilles suivantes jusqu'au mois de Juin,

Nous obtenons bien la consolidation finale sur le dernier mois, grâce à tous ces cumuls en cascade et ce, sur la base d'une unique formule répliquée, capable de ponctionner les informations sur les feuilles précédentes, en faisant référence à des cellules aux coordonnées dynamiques.