Récemment, l’un de mes lecteurs m’a demandé s’il était possible d’utiliser des plages dynamiques dans la fonction de croissance dans Google Sheets. Grâce à l’utilisation de la fonction Filtre, il est possible non seulement dans la fonction de croissance, mais aussi dans les fonctions de tendance et de prévision.
Ne laissez pas les noms des fonctions ci-dessus vous confondre. Apprenez à les utiliser grâce à mon guide sur les fonctions de Google Sheets, qui couvre plusieurs fonctions de Google Sheets et leur utilisation.
Cet article concerne la possibilité d’utiliser des plages de données connues_x, connues_y et nouvelles_x « infinies » dans les calculs de croissance exponentielle (CROISSANCE), de tendance linéaire (TENDANCE) et de régression linéaire (PREVISION) dans Google Sheets.
Normalement, vous ne pouvez pas utiliser des plages infinies comme 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 grâce à 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, données_y et nouvelles_x.
Jetez un coup d’œil aux légendes et à l’ensemble de données. Le codage des couleurs est utilisé pour vous aider à comprendre ce que sont les données_x, les données_y et les nouvelles_x dans les fonctions de croissance, de tendance et de prévision.
Il y a des valeurs existantes dans la plage données_x et données_y. Ainsi, vous pouvez l’appeler ou parfois je peux l’appeler connues_x et connues_y. Nous devons trouver les valeurs par rapport à nouvelles_x.
Tout d’abord, comprenez ce qu’est une plage de données fixe dans les trois fonctions susmentionnées.
Croissance exponentielle :
La syntaxe de la fonction CROISSANCE :
CROISSANCE(connues_y, [connues_x], [nouvelles_x], [b])
=GROISSANCE(B2:B10,A2:A10,A11:A13)
Tendance linéaire :
La syntaxe de la fonction TENDANCE :
TENDANCE(connues_y, [connues_x], [nouvelles_x], [b])
=TENDANCE(B2:B10,A2:A10,A11:A13)
Régression linéaire :
La syntaxe de la fonction PREVISION :
ArrayFormula(PREVISION(x, données_y, données_x))
=ArrayFormula(PREVISION(A11:A13,B2:B10,A2:A10))
J’ai utilisé des plages de données fixes dans les trois formules ci-dessus. Maintenant, passons à l’utilisation des plages de données dynamiques dans les fonctions de croissance, de tendance et de prévision de Google Sheets.
Supposons que ma plage de données ci-dessus est le résultat d’une autre formule/des formules ou qu’elle est mise à jour manuellement 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. Étant donné que la plage est fixe, la formule peut ne pas inclure les valeurs dans les 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_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_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_x : A14:A15
Puis-je prendre en compte ces nouvelles modifications sans modifier la formule ?
Oui ! Laissez-moi vous expliquer comment utiliser les plages de données dynamiques dans les fonctions de croissance, de tendance et de prévision dans Google Sheets. Commençons par la fonction de 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 infinies de données_x, données_y et nouvelles_x. Par exemple,
Voyez d’abord la formule de croissance normale.
=GROISSANCE(B2:B10,A2:A10,A11:A13)
Voici la plage dynamique dans la croissance.
=GROISSANCE(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 formule
J’essaie d’expliquer la formule en suivant la syntaxe de la fonction de croissance.
GROISSANCE(connues_y, connues_x, nouvelles_x)
Formule générique utilisant filtre à l’intérieur de la croissance :
=groissance(filtre(connues_y,connues_y<> » »),filtre(connues_x,connues_y<> » »),filtre(connues_x,(N(connues_y)=0)*(connues_x>0)))
Cela signifie ;
connues_y : Filtrez B2:B si B2:B n’est pas égal à vide.
connues_x : Filtrez A2:A si B2:B n’est pas égal à vide.
nouvelles_x : Filtrez A2:A si B2:B est égal à zéro et A2:A est supérieur à 0.
J’espère qu’avec cette explication, vous comprendrez la logique.
Dans le cas de nouvelles_x, vous pouvez avoir des doutes sur la formule de filtre utilisée. J’ai utilisé une logique OU dans Filtre. Suivez ce tutoriel pour connaître l’utilisation de Filtre avec OU – Comment utiliser AND, OR avec la fonction Filtre de Google Sheets.
De cette manière, vous pouvez utiliser des plages de données infinies/dynamiques dans la fonction de croissance de Google Sheets.
Plage dynamique dans la fonction TENDANCE de Google Sheets
La plage dynamique dans des fonctions telles que TENDANCE, CROISSANCE et PREVISION tourne autour de l’utilisation de Filtre dans les plages de données.
Ici, dans TENDANCE également, vous pouvez suivre l’approche de la fonction de croissance. Voici comment utiliser une plage 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(connues_y, connues_x, nouvelles_x)
Ici, seule le nom de la fonction change. Donc, vérifiez l’exemple de plage dynamique dans la croissance pour mieux comprendre cette formule.
Plage dynamique dans la fonction PREVISION dans Google Sheets
Ici aussi, la formule est presque la même, à l’exception de la position des arguments. De plus, j’ai utilisé ArrayFormula, car PREVISION n’est pas une formule matricielle par défaut.
Formule :
=ArrayFormula(PREVISION(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))
Si votre plage de données, c’est-à-dire les connues_x et connues_y, augmente constamment, et qu’il y a également des changements dans les nouvelles_x (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 manière dont vous utilisez la fonction Filtre.
Si vous connaissez la fonction Filtre, vous pouvez facilement utiliser des plages infinies, également appelées plages dynamiques, dans les fonctions susmentionnées. Merci de votre attention. Profitez-en !
Source : Crawlan.com
