La bonne solution pour calculer un cumul sans erreur dans Excel

Aujourd'hui sur Formation Excel, vous allez découvrir 3 méthodes utilisées pour calculer des cumuls sous Excel, et vous allez voir la plus efficace parmi ces trois méthodes qui vous évitera d'avoir des erreurs dans vos cumuls. En plus, et à la fin de cet article, vous allez voir comment calculer des cumuls en pourcentage. 

Méthode 1: Ce qui est habituel!

Regardez l’exemple suivant, c’est la colonne Cumul qui va afficher les cumuls calculés.

La méthode la plus simple est la suivante :

• Dans la cellule C2 tapez =B2. Pour afficher le premier cumul.
• Puis dans la cellule C3 tapez =B3+C2 et validez. Le deuxième cumul additionne le premier cumul contenant dans la cellule C2 à la valeur de la deuxième cellule B3.
• Copiez ensuite la formule précédente dans le reste des cellules de la colonne Cumul:

Mais le problème qui peut survenir c'est que lorsque vous supprimez une ligne de votre tableau, Excel va renvoyer une erreur de référence #REF. (Pour plus de détails sur cette erreur et les erreurs dans Excel consultez l’article Les erreurs dans Excel )

Comment résoudre donc ce problème ?

Méthode 2: Calculer le cumul à l'aide de la fonction SOMME

Le mieux donc est d’utiliser la fonction SOMME pour calculer vos cumuls, et voici comment procéder :

• Sélectionnez la cellule C2 et tapez =SOMME(B2:B2), c’est-à-dire que vous faites la somme de la plage B2:B2, ce qui va vous afficher la valeur contenue dans B2.

Note : quand vous tapez = SOMME (B2  et vous ajoutez les deux points : Excel insère automatiquement B2 !

• Continuons...
• Vous allez maintenant copier cette formule dans le reste de la colonne Cumul, mais avant de le faire, vous devez figer la première référence B2.
• Fixez alors la première référence B2 en y ajoutant des dollars $B$2 ou cliquez sur elle et tapez la touche F4 , et laissez la deuxième B2 telle qu’elle est.
• En effet, la copie de la formule va incrémenter les références relatives des cellules (B3,B4,B5...B12), et la référence $B$2 (référence absolue) restera la même. Regardez l'image suivante :

• Cette méthode vous permet donc de calculer les sommes des plages B2:B3, B2:B4, B2:B5...B2:B12, et de les placer successivement dans les cellules C3,C4,C5...C12.
• Essayez à présent de supprimer une ligne de votre tableau et remarquez que votre cumul s'effectue de nouveau automatiquement.

Or, il se peut qu’un autre problème survienne  encore ! C’est quand vous souhaiterez filtrer vos données !

Par exemple, je veux afficher le cumul des montants des ventes du 2^ème trimestre, je sélectionne donc les 3 mois Avril,Mai et Juin, ce qui donne le résultat suivant :

Excel ne calcule pas automatiquement les cumuls, n’est-ce pas ?!

Voici à mon avis la solution la plus efficace pour éviter ces erreurs de cumul:

Méthode 3: Calculer le cumul en utilisant la fonction SOUS.TOTAL

Vous vous êtes habitués maintenant avec l’utilisation de la fonction SOUS.TOTAL ! Oui, vous l’avez vue dans les deux derniers articles.

Alors abordons-la également ici :

• Sélectionnez la cellule C2 et insérez la formule suivante : =SOUS.TOTAL(109;$B$2:B2)

Note : L’argument 109 spécifie la fonction SOMME et c’est plus avantageux que l’argument 9.

• Copiez la formule dans les autres cellules de C3 à C13.
• Filtrez à présent vos données !
• Essayez même de supprimer une ligne de votre tableau.

• Ne trouvez-vous pas que c’est très efficace ! 

Si vous avez une autre solution n’hésitez pas à me la communiquer J

Vous arrivez maintenant à la fin de cet article, et vous allez voir deux exemples qui vont vous montrer comment calculer le cumul en pourcentage:

Calculer des cumuls en pourcentage ( Exemple 1)

C'est une opération très facile, puisque vous avez déjà calculé les cumuls.

• Commencez par sélectionner la première cellule de la colonne %Cumul qui va afficher le cumul en pourcentage. Dans notre exemple c’est la cellule D2.
• Tapez la formule suivante : =C2/$C$13

• C2 contient le premier cumul et C13 contient le total des cumuls. Notez ici que vous devez figer la cellule C13 parce que vous allez copier la formule.
• Faites la copie donc de la formule dans les autres cellules.
• Sélectionnez à présent la plage de cellules D2:D13 et dans le groupe Nombre sous l’onglet Accueil choisissez le format pourcentage.

• Voici le résultat obtenu:

Calculer le cumul en pourcentage des cellules filtrées (  Exemple 2) 

Dans cet exemple, nous avons filtré notre tableau précédent en sélectionnant les mois du deuxième trimestre:
Remarquez alors que les formules se trouvant dans la colonne %Cumul se mettent à jour automatiquement pour prendre en compte les nouvelles valeurs visibles:
Par exemple: la cellule D5 contient, dans ce cas, la formule =C5/$C$7

PARTAGEZ PARTAGEZ PARTAGEZ