L’application de la fonction INDIRECT est puissante, mais elle ne fonctionne pas avec ArrayFormula pour renvoyer des résultats sur plusieurs cellules. Cependant, vous pouvez utiliser des fonctions d’aide Lambda (LHF) telles que MAP et REDUCE pour obtenir des résultats similaires.
Les LHFs simplifient les tâches répétitives et améliorent la fonctionnalité des fonctions standard des feuilles de calcul, et INDIRECT ne fait pas exception.
L’application des LHFs avec INDIRECT varie en fonction de votre cas d’utilisation spécifique.
Quels LHFs devrais-je utiliser avec INDIRECT ?
-
MAP : Pour référencer une cellule dans plusieurs feuilles et les récupérer soit individuellement, soit en tant que valeur agrégée. Alternativement, il peut être utilisé pour faire référence à une plage de cellules dans plusieurs feuilles et récupérer des valeurs agrégées de chaque feuille séparément.
-
REDUCE : Pour faire référence à une plage de cellules dans plusieurs feuilles et les empiler.
-
Autres LHFs : Des fonctions telles que SCAN, BYCOL et BYROW sont utiles avec les données extraites à l’aide de REDUCE avec INDIRECT.
Tout au long de ce tutoriel, nous explorerons divers exemples pour améliorer votre compréhension de ces cas d’utilisation.
Quand est-il approprié d’utiliser les alternatives ArrayFormula INDIRECT ?
Cette distinction est cruciale ; elle est particulièrement utile dans deux scénarios :
-
Lorsque vous devez référencer dynamiquement plusieurs noms de feuilles stockés dans une ligne ou une colonne de cellules.
-
Lorsque vous devez faire référence à plusieurs plages nommées indirectement. Dans ce cas, les noms des plages nommées seront répertoriés dans une ligne ou une colonne.
Maintenant, plongeons-nous dans plusieurs exemples illustrant l’application des alternatives ArrayFormula INDIRECT dans Google Sheets.
MAP : Une alternative à ArrayFormula dans INDIRECT pour Google Sheets
Dans un fichier Google Sheets avec quatre feuilles (Dashboard, ABC 12345, ABC 12346 et ABC 12347), des défis peuvent survenir lors de l’extraction et de l’agrégation de données de plusieurs feuilles de manière indirecte.
Les noms des feuilles sont répertoriés dans les cellules A3:A5 de la feuille Dashboard. Dans la cellule B3, nous utiliserons des alternatives ArrayFormula INDIRECT pour extraire des valeurs ou extraire et agréger des valeurs à partir des autres feuilles.
Voici des solutions utilisant la fonction MAP :
Problème 1 : Extraction des totaux
=MAP(A3:A5, LAMBDA(val, INDIRECT(val& »!B7″))) // Extrait les valeurs de la cellule B7 de chaque feuille
Dans cette formule et les formules suivantes, A3:A5 constitue le tableau contenant les noms des feuilles, et B7 est la cellule cible dans chaque feuille pour l’extraction.
Problème 2 : Extraction et somme des totaux
=SUM(MAP(A3:A5, LAMBDA(val, INDIRECT(val& »!B7″)))) // Renvoie 587,50 (198 + 196 + 193,5)
Problème 3 : Extraction et agrégation des plages
=MAP(A3:A5, LAMBDA(val, SUM(INDIRECT(val& »!B2:B6″)))) // Renvoie le total de la plage B2:B6 de chaque feuille séparément
Ces exemples de fonctions MAP résolvent les problèmes où ArrayFormula combiné avec INDIRECT est insuffisant. La fonction itère sur les noms de feuilles spécifiés, offrant une solution dynamique pour l’agrégation de données à partir de plusieurs feuilles dans Google Sheets.
REDUCE + INDIRECT : Améliorez votre expérience avec les feuilles
Lorsque vous recherchez une alternative à INDIRECT avec ArrayFormula dans Google Sheets, il est essentiel de viser une forme de tableau d’INDIRECT sans ArrayFormula en raison de son manque de prise en charge.
Dans l’exemple précédent, nous avons utilisé la fonction INDIRECT avec MAP comme substitut à INDIRECT avec ArrayFormula.
Cependant, bien que MAP soit excellent pour extraire des valeurs individuelles de chaque feuille, nous introduisons la fonction REDUCE pour résoudre les scénarios nécessitant l’extraction et l’empilement de plages.
Comment pouvez-vous extraire une plage de chaque feuille et les empiler verticalement ou horizontalement ? Voici un exemple :
Pour un empilement horizontal :
=REDUCE(TOROW(, 1), A3:A5, LAMBDA(a, val, HSTACK(a, INDIRECT(val& »!B2:B6″)))) // Extrait et empile la plage B2:B6 de chaque feuille horizontalement
Pour un empilement vertical :
=REDUCE(TOCOL(, 1), A3:A5, LAMBDA(a, val, VSTACK(a, INDIRECT(val& »!B2:B6″)))) // Extrait et empile la plage B2:B6 de chaque feuille verticalement
Les deux formules itèrent sur les noms de feuilles dans le tableau A3:A5, extrayant la plage B2:B6. val représente le nom de la feuille actuelle dans le tableau.
La fonction REDUCE stocke les résultats intermédiaires dans a, l’accumulateur. TOROW/TOCOL représente la valeur initiale dans l’accumulateur (essentiellement nulle).
Explorez davantage cette alternative ArrayFormula INDIRECT dans les tutoriels suivants :
- Combinez dynamiquement des données dans plusieurs onglets verticalement dans Google Sheets.
- Combinez dynamiquement plusieurs feuilles horizontalement dans Google Sheets.
INDIRECT avec les alternatives ArrayFormula et les plages nommées
Dans les exemples précédents, nous avons utilisé un tableau de noms de feuilles (A3:A5) dans les fonctions d’aide Lambda (LHF) pour itérer sur chaque nom de feuille. Dans la fonction INDIRECT, nous avons combiné les références de cellules/plages avec le nom de la feuille, comme démontré par :
- INDIRECT(val& »!B2:B6″) pour la référence de plage
- INDIRECT(val& »!B7″) pour la référence de la cellule
Cependant, lorsqu’il s’agit de plages nommées, vous devez spécifier les noms de plage au lieu des noms de feuilles dans A3:A5. Dans ce cas, dans la fonction INDIRECT, vous devez simplement faire référence aux plages nommées directement sans combiner les références de cellules/plages.
Par exemple, si les plages nommées sont vehicle_1, vehicle_2 et vehicle_3, avec les plages correspondantes ‘ABC 12345’!B2:B6, ‘ABC 12346’!B2:B6 et ‘ABC 12347’!B2:B6, respectivement.
Extraction et agrégation des plages :
=MAP(A3:A5, LAMBDA(val, SUM(INDIRECT(val)))) // Renvoie le total des valeurs dans chaque plage nommée séparément
Empilement horizontal :
=REDUCE(TOROW(, 1), A3:A5, LAMBDA(a, val, HSTACK(a, INDIRECT(val)))) // Empile le résultat indirect de chaque plage nommée horizontalement
Ces formules démontrent la flexibilité des alternatives ArrayFormula INDIRECT, permettant l’extraction et l’agrégation sans effort de plages nommées dans Google Sheets.
Ressources
Nous avons exploré des exemples d’alternatives ArrayFormula INDIRECT pour extraire des données de plusieurs feuilles et plages nommées, démontrant leurs applications polyvalentes. Voici quelques tutoriels qui présentent l’utilisation de INDIRECT.
- SUMIF à travers plusieurs feuilles dans Google Sheets
- Rôle de la fonction Indirect dans la mise en forme conditionnelle dans Google Sheets
- Référence à une liste de noms d’onglets dans Query dans Google Sheets
- Mettre en évidence une plage indirecte dans Google Sheets
