Comment Faire une Somme Arithmétique sur Excel ? (Les Formules Excel #6)

Voilà un problème un peu particulier, comment peut-on faire pour créer une somme arithmétiques sur Excel ?

Le problème est simple, il faut faire une suite d’opérations et les additionner, mais la solution est plus complexe, car on ne peut pas utiliser une fonction existante. Là, vous vous êtes surement demandé “Mais pourquoi n’y a-t-il pas de formule pour cette fonction ? “.

Malheureusement, la fonction dépend des opérations que vous avez à faire dans votre Somme. Par conséquent, il faudrait créer autant de formule que d’opérations possibles, ce qui est évidemment impossible !

Pour résoudre on va devoir créer notre formule personnalisée en utilisant le VBA et une composante “classique” de la programmation: les boucles.

Pour bien comprendre tout ça, on va commencer comme d’habitude avec un peu de théorie.

Un peu de théorie, qu’est-ce qu’une “Somme Arithmétique” ?

 Les 3 composantes de la Somme Arithmétique

Les 3 composantes de la somme arithmétique

Alors j’espère qu’aucun enseignant en Mathématiques ne verra ce tutoriel ! Le but est de comprendre grossièrement comment fonctionne la somme pour pouvoir trouver une astuce et la reproduire dans Excel.

On a donc principalement 3 élément:

  • La boucle de somme, c’est à dire que l’on va additionner tout les calculs effectués.
  • Les constantes et/ou calculs: Ici c’est très simple c’est le calcul qui s’effectue dans notre somme, ce calcul est réalisable dans une cellule unique dans Excel.
  • L’incrémentation: Et voilà maintenant une des causes de nos ennuies: l’incrémentation. Cette valeur va augmenter à chaque nouveau calcul, c’est le principe même de la somme arithmétique.

Le problème du passage sur Excel

Le problème du passage de la somme arithmétique sur Excel

Alors pourquoi finalement est-ce-qu’il n’existe pas de formule pour faire une Somme Arithmétique ?

En fait, il nous faudrait une formule qui remplace la fonction d’incrémentation. Or, pour qu’elle fonctionne il faudrait aussi qu’elle agisse sur le calcul effectué.

Ce que l’on pourrait faire est assez simple: Effectuer le calcul pour k=1 en ligne 1, incrémenter sur les lignes jusqu’à la n-ième ligne, puis faire la somme de tout ça.

Oui, cela fonctionne, mais très franchement, ce n’est pas une bonne manière de travailler sur Excel, car ce n’est pas pratique, c’est illisible et c’est très sensible à l’erreur !

La solution: créer sa formule de Somme personnalisée.

La solution va donc consister à créer notre propre formule dans Excel ! Oui, c’est possible et très pratique.

Pour cela, nous allons utiliser le VBA, comme nous l’avons découvert dans l’initiation aux macros, je suis sûr que vous êtes déjà très à l’aise avec ce langage !

Non ? Aucun problème, vous vous en doutez 🙂

Créer une formule personnalisée en VBA sur Excel.

Créer une boucle en VBA

Pour nous permettre de reproduire la notion d’incrémentation, nous allons utiliser une boucle.

La boucle est un des principes les plus importants en programmation, et bonne nouvelle, il est très simple à comprendre. Voici le code de base:

For k = 1 to n

CALCUL

Next k

Ce qui signifie que l’on fera n opérations en commençant par 1 puis 2, etc…

Pour créer notre principe d’incrémentation, va nous suffire  d’intégrer le “k” dans notre calcul.

Reproduire la somme arithmétique

Maintenant que nous avons le principe de boucle, il faut quand même faire la somme de tout ces calculs. pour cela, on va utiliser une constantes “SommeAr”. Au début de la formule, elle est égale à 0 et on va lui ajouter au fur et à mesure tout nos calculs.

Pour cela, on va écrire:

For k = 1 to n

SommeAr = SommeAr + CALCUL

Next k

  • k = 1, on aura SommeAr = 0 + CALCUL1
  • k = 2, on aura SommeAr = CALCUL1 + CALCUL2
  • k = 3, on aura SommeAr = CALCUL1 + CALCUL2 + CALCUL3
  • etc…

On a donc réussi à reproduire notre Somme arithmétique !

Il nous reste plus qu’à comprendre comment en faire une formule !

Créons une formule en VBA

Nous allons donc nous rendre dans l’onglet développeur et cliquer sur “Visual Basic“. Si vous n’avez jamais utiliser le VBA, il y a de grandes chances que nous n’ayez pas cet onglet développeur vous pouvez directement faire ALT + F11 (ou voir mon initiation au VBA).

Dans cette nouvelle fenêtre, vous allez devoir commencer par insérer un module: Insertion -> Module.

Insertion d'un module VBA Excel

Et nous allons commencer la création !

Pour créer une formule, on va commence par écrire:

Function SommeAr(K, n)

 

End Function

Nous avons ici créer une fonction de nom “SommeAr” avec 2 paramètres K et n. Vous pouvez déjà testé en retournant sur votre fichier Excel, si vous tapez =SommeAr( le logiciel doit reconnaître la formule.

Application de la formule

On va maintenant lui donner les opérations à effectuer.  Les deux paramètres vont nous servir, ils seront rentrés par l’utilisateur. Dans notre cas:

  • K sera une constante comme la mensualité de base par exemple.
  • n sera le nombre de mensualité maximum.
  • Dans la formule on utilisera aussi un taux de 15%, soit le calcul d’une mensualité: K1 = 1.15*K ; K2 = 1.15*K1 ; etc…

Function SommeAr(K, n)

   For i = 1 to n

        SommeAr = SommeAr + K * 1.15^(i – 1)

   Next i

End Function 

Et devinez quoi, on a fini ! On a bien créer la formule de somme arithmétique, libre à vous de l’adapter à vos besoins.

Si on retourne dans Excel, on peut taper la formule SommeAr avec les deux arguments, on peut utiliser des noms de cellules exactement comme une formule classique. Dans le premier paramètre il faut rentrer la mensualité de base et dans le deuxième le nombre de mois total pour effectuer le calcul.

Test de la formule créée en VBA

Il ne vous reste plus qu’à bien programmer votre code VBA pour correspondre à votre Somme Arithmétique. Si vous rencontrez des problèmes lors de cette étape, n’hésitez pas à me le faire savoir dans les commentaires, on devrait trouver une solution !

J’espère que ce tutoriel vous a plu et aider, si c’est le cas n’hésitez pas à le partager ou à vous abonner sur Youtube !

Matthieu Langlade Article écrit par Matthieu Langlade - Google +

Passionné de la suite Office et tout particulièrement de PowerPoint, ses possibilités et la "slidologie", je souhaite partager mes connaissances et donner des idées pour sortir des standards que l'on s'impose.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *