Récemment, l’un de mes lecteurs m’a demandé s’il était possible d’utiliser des plages de données dynamiques dans la fonction de croissance de Google Sheets. Grâce à l’utilisation de la fonction Filtre, il est possible non seulement de l’utiliser dans la fonction de croissance, mais aussi dans les fonctions de tendance et de prévision.
Ne vous laissez pas troubler par ces noms de fonctions. Apprenez à les utiliser grâce à mon guide des fonctions de Google Sheets, qui couvre plusieurs fonctions de Google Sheets et leur utilisation.
Ce billet concerne la possibilité d’utiliser des valeurs x et y de données connues ainsi qu’une nouvelle valeur x « infinie » dans les calculs de croissance exponentielle (CROISSANCE), de tendance linéaire (TENDANCE) et de régression linéaire (PRÉVISION) dans Google Sheets.
Normalement, vous ne pouvez pas utiliser des plages infinies telles que A2:A, B2:B dans ces fonctions. Mais vous pouvez utiliser des plages de données infinies/dynamiques dans les fonctions de croissance, de tendance et de prévision avec l’aide de la fonction Filtre dans Google Sheets.
Comprendre les plages de données dynamiques dans la croissance, la tendance et les prévisions dans Google Sheets
Dans la capture d’écran ci-dessous, j’ai mis en évidence les données x, y et les nouvelles données x.
Jetez un coup d’œil aux légendes et à l’ensemble de données. La couleur est utilisée pour vous aider à comprendre ce que représentent les données x, y et les nouvelles données x dans les fonctions de croissance, de tendance et de prévision.
Il y a des valeurs existantes dans la plage de données x et y. On peut les appeler aussi données_x et données_y connues. Nous devons trouver les valeurs correspondantes pour les nouvelles données x.
Tout d’abord, comprenez ce qu’est une plage de données fixe dans ces trois fonctions.
Croissance exponentielle :
La syntaxe de la fonction CROISSANCE :
CROISSANCE(données_y_connu, [données_x_connu], [nouvelles_données_x], [b])=CROISSANCE(B2:B10,A2:A10,A11:A13)
Tendance linéaire :
La syntaxe de la fonction TENDANCE :
TENDANCE(données_y_connu, [données_x_connu], [nouvelles_données_x], [b])=TENDANCE(B2:B10,A2:A10,A11:A13)
Régression linéaire :
La syntaxe de la fonction PRÉVISION :
ArrayFormula(PRÉVISION(x, données_y, données_x))=ArrayFormula(PRÉVISION(A11:A13,B2:B10,A2:A10))
J’ai utilisé des plages de données fixes dans ces trois formules. Maintenant, il est temps de passer à l’utilisation de plages de données dynamiques dans les fonctions de croissance, de tendance et de prévision dans Google Sheets.
Supposons que la plage de données ci-dessus est le résultat d’une autre formule ou mise à jour manuelle par les utilisateurs. Dans ce cas, les plages de données peuvent changer fréquemment. Cela pose un problème dans les formules ci-dessus, car la plage est fixe et la formule peut ne pas inclure les valeurs des nouvelles cellules.
Par exemple, dans les formules précédentes, les plages de données utilisées sont les suivantes :
- données_x : A2: A10
- données_y : B2: B10
- nouvelles_données_x : A11: A13
Maintenant, voyez le même ensemble de données mais avec de nouvelles valeurs dans les colonnes données_x, données_y et nouvelles_données_x.
Maintenant, dans le nouvel ensemble de données, les plages sont les suivantes :
- données_x : A2: A13
- données_y : B2: B13
- nouvelles_données_x : A14: A15
Puis-je prendre en compte ces nouvelles modifications sans changer la formule ?
Oui ! Laissez-moi vous expliquer comment utiliser des plages de données dynamiques dans les fonctions de croissance, de tendance et de prévision dans Google Sheets. Commençons par la fonction Croissance.
Plages étendues/dynamiques/infinies dans la fonction de croissance de Google Sheets
Comme je l’ai mentionné, une plage étendue dans la croissance signifie couvrir des valeurs x, y et de nouvelles données x infinies. Par exemple,
Voyez d’abord la formule de croissance normale.
=CROISSANCE(B2:B10,A2:A10,A11:A13)Voici la plage dynamique dans la croissance.
=CROISSANCE(FILTRE(B2:B,B2:B<>""),FILTRE(A2:A,B2:B<>""),FILTRE(A2:A,(N(B2:B)=0)*(A2:A>0)))Voyez comment la formule prédit la croissance de manière dynamique.
Explication de la formule
J’essaie d’expliquer la formule en suivant la syntaxe de la fonction Croissance.
Croissance(données_y_connu, données_x_connu, nouvelles_données_x)Formule générique utilisant FILTRE dans Croissance :
=CROISSANCE(FILTRE(données_y_connu,données_y_connu<>""),FILTRE(données_x_connu,données_y_connu<>""),FILTRE(données_x_connu,(N(données_y_connu)=0)*(données_x_connu>0)))Cela signifie :
- données_y_connu : Filtrer B2:B si B2:B n’est pas égal à une cellule vide.
- données_x_connu : Filtrer A2:A si B2:B n’est pas égal à une cellule vide.
- nouvelles_données_x : Filtrer A2:A si B2:B est égal à zéro et que A2:A est supérieur à 0.
J’espère que cette explication vous a permis de comprendre la logique.
Dans le cas de nouvelles_données_x, vous pourriez avoir des doutes sur la formule de Filtre utilisée. J’ai utilisé une logique OU dans le Filtre. Consultez ce tutoriel pour connaître l’utilisation de Filtre avec OU – Comment utiliser ET et OU avec la fonction Filtre de Google Sheets.
De cette façon, vous pouvez utiliser des plages de données infinies/dynamiques dans la fonction de croissance de Google Sheets.
Plage dynamique dans la fonction de tendance de Google Sheets
La plage dynamique dans des fonctions comme TENDANCE, CROISSANCE et PRÉVISION repose sur l’utilisation du filtre dans les plages de données.
Ici aussi, dans TENDANCE, vous pouvez suivre l’approche de la fonction CROISSANCE. Voici comment utiliser une plage de données dynamique dans TENDANCE dans Google Sheets.
Formule :
=TENDANCE(FILTRE(B2:B,B2:B<>""),FILTRE(A2:A,B2:B<>""),FILTRE(A2:A,(N(B2:B)=0)*(A2:A>0)))Syntaxe :
TENDANCE(données_y_connu, données_x_connu, nouvelles_données_x)Ici, seule la fonction change. Consultez donc l’exemple de plage dynamique dans la croissance pour mieux comprendre cette formule.
Plage dynamique dans la fonction PRÉVISION des documents Google Sheets
Ici aussi, la formule est presque la même, sauf la position des arguments. De plus, j’ai utilisé ArrayFormula car PRÉVISION n’est pas une formule de tableau par défaut.
Formule :
=ArrayFormula(PRÉVISION(FILTRE(A2:A,(N(B2:B)=0)*(A2:A>0)),FILTRE(B2:B,B2:B<>""),FILTRE(A2:A,B2:B<>"")))Syntaxe :
ArrayFormula(PREVISION(x, données_y, données_x))Conclusion
Si votre plage de données, c’est-à-dire les données_x et données_y connues, continue d’augmenter et que de nouvelles données_x apparaissent (ce qui sera le cas), alors vous pouvez suivre l’approche ci-dessus.
Le secret des plages de données dynamiques dans les fonctions de croissance, de tendance et de prévision dans Google Sheets dépend de la façon dont vous utilisez la fonction Filtre.
Si vous connaissez Filtre, vous pouvez facilement maîtriser l’utilisation de plages infinies, également appelées plages de données dynamiques, dans les fonctions susmentionnées. Merci de m’avoir lu. Profitez-en ! Plein de succès pour votre avenir !
Source : Crawlan.com
