La fonction Excel Indirect

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


Sujets que vous pourriez aussi aimer :


La fonction Excel Indirect

Cette série de mises en situation démontre l'intérêt et la puissance de la fonction Excel Indirect. On peut l'utiliser pour reconstruire des adresses en interprétant des plages de cellules ou des matrices virtuelles. Grâce à elle, il devient trivial de pointer sur des cellules précises ou sur d'autres feuilles du classeur pour réaliser des opérations comme des cumuls ou des consolidations dynamiques de données. Intégrée dans des raisonnements matriciels, elle permet aussi de scruter chaque ligne d'un tableau comme le ferait un traitement récursif en programmation VBA. Selon la même démarche et toujours grâce à des matrices de nombres à interpréter, elle offre la possibilité de parcourir chaque caractère d'une cellule pour entreprendre des traitements de chaînes très spécifiques.

01/11 : Récolter les données de plusieurs feuilles avec une formule



Astuce Excel pour consolider dans une seule feuille tous les chiffres émanant de plusieurs feuilles grâce à la fonction Indirect pour construire l'adresse. Nous débutons l'étude à partir d'un classeur dans lequel les ventes réalisées par mois sont synthétisées dans des feuilles indépendantes. Chacune de ces feuilles est nommée avec l'intitulé du mois. Et chaque total est livré en entête de chaque feuille dans une cellule portant les mêmes coordonnées. Sur la première feuille de ce classeur, un tableau propose de consolider tous ces résultats ligne à ligne. Pour cela, chaque entête de ligne porte la mention du mois, soit le nom de la feuille correspondante. Dès lors, grâce à la fonction Excel Indirect, nous sommes en mesure d'exploiter cette information pour pointer dynamiquement sur la cellule de synthèse de chaque feuille. De fait, nous parvenons à réunir tous les totaux dispersés dans un même tableau de synthèse.

Lien de la formation | Lien de la vidéo

02/11 : Lister les noms des feuilles par calcul



Astuce Excel pour énumérer dynamiquement tous les noms des feuilles du classeur grâce à la fonction de macro Lire.Classeur exploitée dans un nom de formule. Cette fonction retourne un tableau de tous les noms. Ainsi, en répliquant le nom donné dans une technique matricielle, nous obtenons bien la liste de tous les noms de ces feuilles. Mais elles sont toutes préfixées du nom du classeur entre crochets. De plus, des erreurs sont générées dans la réplication de la formule lorsque la fin de la liste est atteinte. Pour supprimer le nom du classeur en préfixe, nous exploitons les fonctions Excel Stxt et Trouve. La seconde fournit la position du crochet fermant dans la chaîne. La première exploite cette position pour prélever dans la chaîne à partir de cet indice incrémenté d'une unité. Il en résulte les noms des feuilles purgés de tout parasite. Pour neutraliser les erreurs, nous engageons un raisonnement matriciel conditionnel. Grâce à une matrice virtuelle dont la borne inférieure évolue avec le calcul répliqué, nous testons si la dernière ligne du tableau des noms est atteinte. Tant que ce n'est pas le cas, nous restituons le nom de la feuille en fonction de la position concernée dans l'analyse. Lorsque la borne est franchie, nous conservons vide la cellule du résultat. De fait, nous parvenons à restituer dynamiquement et automatiquement tous les noms des feuilles composant le classeur.

Lien de la formation | Lien de la vidéo

03/11 : Sommaire automatique sans code VBA



Créer un sommaire automatique pointant sur les feuilles du classeur au clic, grâce aux fonctions Lire.Classeur et Lien_Hypertexte dans un calcul matriciel. Nous récupérons tout d'abord les travaux de l'épisode précédent. Nous avions mis en oeuvre la fonction de macro Lire.Classeur dans un nom de formule pour restituer tous les noms d'onglets sur une page d'accueil et ce, sans le nom du classeur en préfixe. Nous souhaitons exploiter ces résultats pour que la fonction Lien_Hypertexte puisse pointer sur ces feuilles. Mais une adresse doit nécessairement être préfixée du nom du classeur. Donc, nous passons le nom de formule en premier argument dans la fonction Lien_hypertexte sans purger le préfixe. Nous concaténons le résultat à la première cellule A1 préfixée d'un point d'exclamation, selon la syntaxe Excel. Après la validation, nous retrouvons l'énumération de tous les noms de feuilles. Mais cette fois, ils sont transformés en liens cliquables. Chaque clic dans le sommaire permet d'atteindre directement la feuille désignée par son nom d'onglet.

Lien de la formation | Lien de la vidéo

04/11 : Chercher dans plusieurs tableaux



Astuce Excel pour trouver et extraire l'information correspondant à plusieurs critères recoupés dans un tableau qui n'est pas connu à l'avance avec une formule. Nous travaillons à partir d'un classeur offrant plusieurs tableaux. Ceux-ci recensent des groupes de commerciaux ayant réalisé des chiffres d'affaires sur plusieurs mois de l'année. L'utilisateur peut définir l'un de ces groupes à l'aide d'une première liste déroulante. Avec une deuxième liste déroulante dépendante, il peut désigner un commercial appartenant à ce groupe choisi en amont. Puis, avec une dernière liste déroulante, il peut cibler un mois précis pour extraire le chiffre d'affaires associé. C'est alors une unique formule exploitant les fonctions d'extraction Index et Equiv, mais aussi et surtout la fonction Indirect pointant sur les plages dynamiques judicieusement nommées, qui permettent d'organiser la recherche dans le bon tableau pour isoler instantanément la donnée convoitée.

Lien de la formation | Lien de la vidéo

05/11 : Consolider les données de plusieurs feuilles



Astuce Excel pour réunir les informations de plusieurs feuilles dans un seul tableau avec une seule formule exploitant les fonctions d'extraction et Indirect. Nous travaillons à partir d'un classeur relatant les résultats des commerciaux d'une entreprise dans plusieurs feuilles pour les différents mois. D'une feuille à une autre, l'organisation de ces commerciaux diffère. Pourtant, dans une feuille indépendante, nous devons regrouper tous ces résultats dans un unique tableau de synthèse. Et l'agencement de ce tableau diffère lui aussi de la structure que proposent les tableaux des mois. Mais grâce aux fonctions d'extraction Index et Equiv et à la fonction Excel Indirect pour pointer dynamiquement sur les plages de cellules de la bonne feuille, nous parvenons à construire une unique formule capable de consolider toutes les données éparpillées dans un même tableau.

Lien de la formation | Lien de la vidéo

06/11 : Comparer les résultats de plusieurs feuilles



Astuce Excel d'adressage indirect pour importer dynamiquement les données d'autres feuilles dans des tableaux à comparer avec une mise en forme conditionnelle. Nous travaillons à partir d'une feuille proposant deux tableaux vierges destinés à comparer les résultats de deux commerciaux. Ces commerciaux se désignent par le biais de deux listes déroulantes. Ces noms sont aussi ceux des feuilles hébergeant leurs résultats respectifs. Il est donc question d'importer les données à partir d'autres feuilles désignées dynamiquement. Et c'est la fonction Excel Indirect qui permet de récolter ces informations en pointant sur les bonnes feuilles. Pour cela, elle est utilisée dans une fonction Index d'extraction pour désigner la colonne de recherche en fonction des choix émis avec les listes déroulantes. Mais comme les références à chercher ne sont pas forcément organisées dans le même ordre d'une feuille à une autre, nous imbriquons la fonction Excel Equiv dans la fonction d'extraction Index pour trouver ces positions et réaliser ces importations dynamiques. Enfin, une règle de mise en forme conditionnelle permet de surligner les lignes d'un vendeur pour lequel les résultats sont meilleurs que celui auquel il est confronté. Ces couleurs rendent triviale la comparaison dynamique des données.

Lien de la formation | Lien de la vidéo

07/11 : Consolider les données des feuilles précédentes



Astuce Excel pour cumuler en cascade les valeurs du classeur sur chaque feuille suivante avec une seule formule à répliquer et exploitant la fonction Indirect. Nous travaillons à partir d'un classeur présentant les quantités d'articles vendus pour les six premiers mois de l'année. Chaque mois est représenté dans une feuille indépendante intitulée avec le mot Mois suivi du numéro de ce mois, par exemple Mois01 pour le mois de Janvier. Grâce à la fonction Excel Cellule, nous parvenons à resituer le nom de la feuille en cours. Grâce à la fonction droite, nous isolons le numéro en suffixe. De fait, nous lui retranchons une unité pour désigner le numéro de la feuille précédente. Grâce à la fonction Excel indirect imbriquant ces fonctions Droite et Cellule, nous parvenons à atteindre la cellule cible sur la feuille précédente. Mais pour que la formule puisse être répliquée dynamiquement sur les lignes du dessous et sur les autres feuilles, nous recomposons ses coordonnées avec les fonctions Adresse et Ligne. Après réplication, nous constatons que nous parvenons en effet à cumuler toutes les quantités vendues de mois en mois, soit de feuille à feuille et ce, avec une unique formule.

Lien de la formation | Lien de la vidéo

08/11 : Cumuler les résultats sans connaître les noms des feuilles



Astuce Excel pour consolider sur chaque feuille les données de la feuille précédente et les répliquer sur la feuille suivante grâce à la fonction Indirect. Nous travaillons à partir d'un classeur offrant 6 feuilles. Mais contrairement à l'astuce précédente, elles ne sont pas nommées avec un texte fixe suivi d'un numéro incrémenté. Ici, les feuilles sont toutes intitulées avec les noms des mois. L'enjeu de cette astuce reste malgré tout de pouvoir pointer sur la feuille précédente, par rapport à la feuille active, malgré l'absence de logique dans l'énumération des noms. Nous exploitons tout d'abord la fonction de macro Lire.Classeur dans un nom de formule. Nous utilisons ce nom dans les cellules pour constater qu'elle retourne un tableau horizontal encapsulant tous les noms de feuille dans l'ordre. Mais elles sont préfixées du nom du classeur. Nous purgeons ces résultats grâce à la fonction Stxt imbriquant la fonction Trouve. Nous parvenons ainsi à isoler tous les noms de feuille. L'objectif alors est de pouvoir extraire le nom de la feuille qui précède la feuille active. Donc la position de cette dernière doit être trouvée dans le tableau des noms. Nous engageons la fonction Equiv sur la recherche du nom de la feuille active, renvoyé par la fonction Cellule utilisant le paramètre nomfichier. A cette position retournée, nous retranchons une unité pour pointer sur la feuille précédente. Nous passons cette valeur à la fonction Index pour réaliser l'extraction sur le tableau des noms. Il en résulte effectivement le nom de la feuille précédente. Nous l'exploitons alors dans la fonction Indirect pour pointer dynamiquement sur les cellules à ponctionner, grâce aux fonctions Adresse, Ligne et Colonne. De fait, sur la base d'une seule formule, les valeurs se cumulent feuille à feuille. Et sur le dernier mois, nous obtenons la consolidation de tous les chiffres réalisés au cours du semestre.

Lien de la formation | Lien de la vidéo

09/11 : Planning automatique des semaines feuille à feuille



Astuce Excel pour construire automatiquement le calendrier des semaines de l'année en les disposant sur des feuilles différentes grâce à la fonction Indirect. Nous débutons à partir d'une feuille proposant de choisir une année à partir d'une liste déroulante. Nous répliquons l'astuce du volet précédent pour calculer le premier Lundi de l'année ainsi choisie. Dès lors, pour les quatre jours restants, c'est une formule propagée avec une simple incrémentation qui suffit. Nous dupliquons cette feuille et plaçons la copie à la suite. Nous la renommons avec le même intitulé mais suffixé d'un numéro incrémenté (Semaine_02, Semaine_03 etc...). Une formule déjà en place restitue ce nom dans une cellule de la feuille copiée. Nous retravaillons ce nom par calcul afin de décrémenter le suffixe et de pointer automatiquement sur la feuille précédente grâce à la fonction Indirect. C'est ainsi que nous récupérons la date du Lundi précédent que nous incrémentons de sept unités pour le point de départ de la semaine suivante. Par le jeu des réplications des feuilles, le planning annuel des semaines se recompose automatiquement.

Lien de la formation | Lien de la vidéo

10/11 : Consolider plusieurs feuilles sur des positions variables



Formule matricielle Excel pour réunir dynamiquement dans un seul tableau les données de synthèse issues de plusieurs feuilles sur des emplacements différents. Nous débutons l'étude à partir d'un classeur offrant une feuille de synthèse et de nombreux autres onglets pour les résultats des commerciaux. Cette feuille de synthèse énumère automatiquement tous les noms d'onglets. Pour cela, par le biais d'un nom de formule, elle exploite la fonction de macro XL4 Lire.Classeur. Les tableaux des commerciaux ne sont pas positionnés aux mêmes endroits. De fait, l'emplacement de la donnée de synthèse à récolter pour chacun n'est pas connu à l'avance. Pour résoudre le problème, nous engageons un raisonnement matriciel faisant appel à la fonction d'extraction Index. Il est question de déterminer dynamiquement et pour chaque feuille, l'indice de ligne et l'indice de colonne de l'information à retourner. Celle-ci est annoncée par un titre précis. Dans une plage suffisamment grande, nous testons la présence de ce titre pour toutes les cellules. Pour extraire celle qui répond favorablement, nous imbriquons cette recherche dans la fonction Min. Grâce à elle, seule la valeur positive de test est extraite du tableau de données résultant. Après réplication de la formule, nous constatons que toutes les données de synthèse sont parfaitement consolidées sur la même feuille malgré leurs organisations fluctuantes.

Lien de la formation | Lien de la vidéo

11/11 : Isoler les chiffres au milieu des textes



Formule matricielle Excel pour extraire les nombres des chaînes de textes grâce aux fonctions Equiv et Stxt pour repérer les positions et découper les chiffres. Nous procédons en trois étapes pour la bonne compréhension du mécanisme. Tout d'abord, nous engageons un raisonnement matriciel sur les codes alphanumériques avec la fonction Equiv. Elle teste caractère à caractère les erreurs générées sur la recherche des nombres. Dès qu'aucune erreur n'est retournée, elle en déduit la position du premier chiffre. Ensuite, nous dénombrons les lettres dans la chaîne. Pour cela, selon la même technique, nous réalisons la somme des erreurs retournées sur la recherche des nombres. Nous en déduisons le nombre de caractères non numériques. Nous assemblons ces syntaxes dans une formule matricielle finale exploitant la fonction Stxt sur le code Alphanumérique. La position de départ pour le prélèvement est renvoyée par le résultat du premier calcul. La longueur de découpe est déduite en retranchant le nombre de lettres du deuxième calcul au nombre total de caractères du code alphanumérique et retourné par la fonction NbCar. Après réplication de la formule matricielle, nous constatons que nous sommes parvenus à isoler tous les chiffres regroupés dans une même cellule, indépendamment des lettres.

Lien de la formation | Lien de la vidéo