Téléchargez l'exemple de classeur
Nous avons discuté dans d'autres articles de la manière dont certaines fonctions telles que OFFSET et INDIRECT sont volatiles. Si vous commencez à utiliser un grand nombre d'entre eux dans une feuille de calcul ou si de nombreuses cellules dépendent d'une fonction volatile, votre ordinateur peut passer un temps considérable à recalculer chaque fois que vous essayez de modifier une cellule. Plutôt que d'être frustré par le fait que votre ordinateur n'est pas assez rapide, cet article explorera d'autres moyens de résoudre les situations courantes que les gens utilisent OFFSET et INDIRECT.
Remplacement de OFFSET pour créer une liste dynamique
Après avoir pris connaissance de la fonction OFFSET, on pense souvent à tort que c'est le seul moyen de renvoyer un résultat avec une taille dynamique en utilisant les deux derniers arguments. Regardons une liste dans la colonne A où notre utilisateur pourrait décider plus tard d'ajouter des éléments supplémentaires.
Pour créer une liste déroulante dans la cellule C2, vous pouvez définir une plage nommée avec une formule volatile telle que
=OFFSET($A$2, 0, 0, COUNTA($A:$A)-1, 1)
Avec la configuration actuelle, cela renverrait certainement une référence à la plage A2:A5. Cependant, il existe un autre moyen d'utiliser l'INDEX non volatile. Pour cela, pensez à écrire une référence à la gamme allant de A2 à A5. Lorsque vous écrivez "A2:A5", ne considérez pas cela comme une seule donnée, mais plutôt comme un "StartingPoint" et "EndingPoint" séparés par deux points (par exemple, StartingPoint:EndingPoint). Dans une formule, les points de départ et de fin peuvent être les résultats d'autres fonctions.
Voici la formule que nous utiliserons pour créer une plage dynamique à l'aide de la fonction INDEX :
=$A$2:INDEX($A:$A, COUNTA($A:$A))
Notez que nous avons déclaré que le point de départ pour cette plage sera toujours A2. De l'autre côté des deux points, nous utilisons INDEX pour déterminer où le EndingPoint doit être. Le COUNTA déterminera qu'il y a 5 cellules avec des données dans la colonne A, et donc notre INDEX créera une référence à A5. La formule est donc évaluée comme suit :
=$A$2:INDEX($A:$A, COUNTA($A:$A)) =$A$2:INDEX($A:$A, 5) =$A$2:$A5
En utilisant cette technique, vous pouvez créer dynamiquement une référence à n'importe quelle liste, ou même un tableau à deux dimensions à l'aide de la fonction INDEX. Dans une feuille de calcul avec une abondance de fonctions OFFSET, le remplacement des OFFSETs par INDEX permettra à votre ordinateur de démarrer beaucoup plus rapidement.
Remplacement d'INDIRECT pour les noms de feuille
La fonction INDIRECT est souvent appelée lorsque des classeurs ont été conçus avec des données dispersées sur plusieurs feuilles de calcul. Si vous ne pouvez pas obtenir toutes les données sur une seule feuille, mais que vous ne souhaitez pas utiliser une fonction volatile, vous pourrez peut-être utiliser CHOOSE.
Considérez la mise en page suivante, où nous avons des données de vente sur 3 feuilles de calcul différentes. Sur notre feuille récapitulative, nous avons sélectionné le trimestre pour lequel nous souhaitons afficher les données.
Notre formule en B3 est :
=CHOISIR(ASSOCIER(B2, D2:D4, 0), Automne!A2, Hiver!A2, Printemps!A2)
Dans cette formule, la fonction MATCH va déterminer la zone que nous voulons retourner. Cela indique ensuite à la fonction CHOOSE laquelle des plages suivantes retourner comme résultat.
Vous pouvez également utiliser la fonction CHOOSE pour renvoyer une plage plus large. Dans cet exemple, nous avons un tableau de données de vente sur chacune de nos trois feuilles de calcul.
Plutôt que d'écrire une fonction INDIRECT pour construire le nom de la feuille, vous pouvez laisser CHOOSE déterminer sur quelle table effectuer la recherche. Dans mon exemple, j'ai déjà nommé les trois tables tbFall, tbWinter et tbSpring. La formule en B4 est :
=RECHERCHEV(B3, CHOISIR(MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Dans cette formule, le MATCH va déterminer que nous voulons que les 2sd élément de notre liste. CHOOSE prendra alors ce 2 et renverra la référence à tbWinter. Enfin, notre RECHERCHEV pourra compléter la recherche dans le tableau donné et constatera que les ventes totales de Banane en hiver étaient de 6000 $.
=RECHERCHEV(B3, CHOISIR(MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0) =RECHERCHEV(B3, CHOISIR(2, tbFall, tbWinter, tbSpring), 2, 0) = RECHERCHEV(B3, tbHiver, 2, 0) =6000
Cette technique est limitée par le fait que vous devez remplir la fonction CHOISIR avec toutes les zones à partir desquelles vous souhaitez récupérer une valeur, mais cela vous donne l'avantage d'éviter une formule volatile. Selon le nombre de calculs que vous devez effectuer, cette capacité peut s'avérer très utile.
